IWorkbook workbook;
using (FileStream stream = new FileStream(excelFilePath, FileMode.Open, FileAccess.Read))
{
workbook = new HSSFWorkbook(stream);
}
ISheet sheet = workbook.GetSheetAt(0); // zero-based index of your target sheet
DataTable dt = new DataTable(sheet.SheetName);
// write header row
IRow headerRow = sheet.GetRow(0);
foreach (ICell headerCell in headerRow)
{
dt.Columns.Add(headerCell.ToString());
}
// write the rest
int rowIndex = 0;
foreach (IRow row in sheet)
{
// skip header row
if (rowIndex++ == 0) continue;
DataRow dataRow = dt.NewRow();
dataRow.ItemArray = row.Cells.Select(c => c.ToString()).ToArray();
dt.Rows.Add(dataRow);
}
0
private static ISheet GetFileStream(string fullFilePath)
{
var fileExtension = Path.GetExtension(fullFilePath);
string sheetName;
ISheet sheet = null;
switch (fileExtension)
{
case ".xlsx":
using (var fs = new FileStream(fullFilePath, FileMode.Open, FileAccess.Read))
{
var wb = new XSSFWorkbook(fs);
sheetName = wb.GetSheetAt(0).SheetName;
sheet = (XSSFSheet) wb.GetSheet(sheetName);
}
break;
case ".xls":
using (var fs = new FileStream(fullFilePath, FileMode.Open, FileAccess.Read))
{
var wb = new HSSFWorkbook(fs);
sheetName = wb.GetSheetAt(0).SheetName;
sheet = (HSSFSheet) wb.GetSheet(sheetName);
}
break;
}
return sheet;
}
private static DataTable GetRequestsDataFromExcel(string fullFilePath)
{
try
{
var sh = GetFileStream(fullFilePath);
var dtExcelTable = new DataTable();
dtExcelTable.Rows.Clear();
dtExcelTable.Columns.Clear();
var headerRow = sh.GetRow(0);
int colCount = headerRow.LastCellNum;
for (var c = 0; c < colCount; c++)
dtExcelTable.Columns.Add(headerRow.GetCell(c).ToString());
var i = 1;
var currentRow = sh.GetRow(i);
while (currentRow != null)
{
var dr = dtExcelTable.NewRow();
for (var j = 0; j < currentRow.Cells.Count; j++)
{
var cell = currentRow.GetCell(j);
if (cell != null)
switch (cell.CellType)
{
case CellType.Numeric:
dr[j] = DateUtil.IsCellDateFormatted(cell)
? cell.DateCellValue.ToString(CultureInfo.InvariantCulture)
: cell.NumericCellValue.ToString(CultureInfo.InvariantCulture);
break;
case CellType.String:
dr[j] = cell.StringCellValue;
break;
case CellType.Blank:
dr[j] = string.Empty;
break;
}
}
dtExcelTable.Rows.Add(dr);
i++;
currentRow = sh.GetRow(i);
}
return dtExcelTable;
}
catch (Exception e)
{
throw;
}
}
1
这样做可以完成任务 .
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using NPOI.Util.Collections;
using NPOI;
using System.Collections.Generic;
using NPOI.OpenXmlFormats.Spreadsheet;
using NPOI.HSSF.UserModel;
using NPOI.SS.Util;
public DataTable xlsxToDT(Stream str)
{
XSSFWorkbook hssfworkbook = new XSSFWorkbook(str);
ISheet sheet = hssfworkbook.GetSheetAt(0);
str.Close();
DataTable dt = new DataTable();
IRow headerRow = sheet.GetRow(0);
IEnumerator rows = sheet.GetRowEnumerator();
int colCount = headerRow.LastCellNum;
int rowCount = sheet.LastRowNum;
for (int c = 0; c < colCount; c++)
dt.Columns.Add(headerRow.GetCell(c).ToString());
while (rows.MoveNext())
{
IRow row = (XSSFRow)rows.Current;
DataRow dr = dt.NewRow();
for (int i = 0; i < colCount; i++)
{
ICell cell = row.GetCell(i);
if (cell != null)
dr[i] = cell.ToString();
}
dt.Rows.Add(dr);
}
return dt;
}
0
您可以通过NPOI轻松尝试：
private DataTable GetDataTableFromExcel(String Path)
{
XSSFWorkbook wb;
XSSFSheet sh;
String Sheet_name;
using (var fs = new FileStream(Path, FileMode.Open, FileAccess.Read))
{
wb = new XSSFWorkbook(fs);
Sheet_name= wb.GetSheetAt(0).SheetName; //get first sheet name
}
DataTable DT = new DataTable();
DT.Rows.Clear();
DT.Columns.Clear();
// get sheet
sh = (XSSFSheet)wb.GetSheet(Sheet_name);
int i = 0;
while (sh.GetRow(i) != null)
{
// add neccessary columns
if (DT.Columns.Count < sh.GetRow(i).Cells.Count)
{
for (int j = 0; j < sh.GetRow(i).Cells.Count; j++)
{
DT.Columns.Add("", typeof(string));
}
}
// add row
DT.Rows.Add();
// write row value
for (int j = 0; j < sh.GetRow(i).Cells.Count; j++)
{
var cell = sh.GetRow(i).GetCell(j);
if (cell != null)
{
// TODO: you can add more cell types capatibility, e. g. formula
switch (cell.CellType)
{
case NPOI.SS.UserModel.CellType.Numeric:
DT.Rows[i][j] = sh.GetRow(i).GetCell(j).NumericCellValue;
//dataGridView1[j, i].Value = sh.GetRow(i).GetCell(j).NumericCellValue;
break;
case NPOI.SS.UserModel.CellType.String:
DT.Rows[i][j] = sh.GetRow(i).GetCell(j).StringCellValue;
break;
}
}
}
i++;
}
return DT;
}
3
在Codeplex网站here的下载部分中有 example package - 一包C＃示例 . 尝试一下，如果你还没有 .
这是最简单的例子 -
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
//.....
private void button1_Click(object sender, EventArgs e)
{
HSSFWorkbook hssfwb;
using (FileStream file = new FileStream(@"c:\test.xls", FileMode.Open, FileAccess.Read))
{
hssfwb= new HSSFWorkbook(file);
}
ISheet sheet = hssfwb.GetSheet("Arkusz1");
for (int row = 0; row <= sheet.LastRowNum; row++)
{
if (sheet.GetRow(row) != null) //null is when the row only contains empty cells
{
MessageBox.Show(string.Format("Row {0} = {1}", row, sheet.GetRow(row).GetCell(0).StringCellValue));
}
}
}
IWorkbook workbook;
using (FileStream stream = new FileStream(excelFilePath, FileMode.Open, FileAccess.Read))
{
workbook = new HSSFWorkbook(stream);
}
ISheet sheet = workbook.GetSheetAt(0);
DataTable dt = new DataTable(sheet.SheetName);
// write header row
IRow headerRow = sheet.GetRow(0);
foreach (ICell headerCell in headerRow)
{
dt.Columns.Add(headerCell.ToString());
}
// write the rest
int rowIndex = 0;
foreach (IRow row in sheet)
{
// skip header row
if (rowIndex++ == 0) continue;
// add row into datatable
var cells = new List<ICell>();
for (int i = 0; i < dt.Columns.Count; i++)
{
cells.Add(row.GetCell(i, MissingCellPolicy.CREATE_NULL_AS_BLANK));
}
// Columns formatted as DateTime will be printed as '01-Jul-2005',
// which can be converted to datetime in the SQL server.
// select cast('01-Jul-2005' as DateTime).
// In SQL Server we can convert DateTime to whatever string we want, for example
// select convert(nvarchar(255), cast('01-Jul-2005' as datetime), 112) will print '20050701'.
// http://www.sqlusa.com/bestpractices/datetimeconversion/
dt.Rows.Add(cells.Select(c => c.ToString()).ToArray());
// Datetimes also can be reformatted directly like this :
// dt.Rows.Add(cells.Select(c =>
// c.CellType == CellType.Numeric && DateUtil.IsCellDateFormatted(c)
// ? c.DateCellValue.ToString("yyyyMMdd")
// : c.ToString()
// ).ToArray());
}
//return dt;
9
基于@Sean的GREAT答案和example of NPOI，我将方法编写为 Convert all worksheets in xls file to DataSet 和 Convert DataSet back to xls file ：
public static DataSet GetDataSetFromXls(string excelFilePath)
{
IWorkbook workbook;
using (FileStream stream = new FileStream(excelFilePath, FileMode.Open, FileAccess.Read))
{
workbook = new HSSFWorkbook(stream); //2003 xls
//workbook = new XSSFWorkbook(); //2007 xlsx
}
DataSet ds = new DataSet();
for (int i = 0; i < workbook.NumberOfSheets; i++)
{
ISheet sheet = workbook.GetSheetAt(i); // zero-based index of your target sheet
DataTable dt = new DataTable(sheet.SheetName);
// write header row
IRow headerRow = sheet.GetRow(0);
foreach (ICell headerCell in headerRow)
{
dt.Columns.Add(headerCell.ToString());
}
// write the rest
int rowIndex = 0;
foreach (IRow row in sheet)
{
// skip header row
if (rowIndex++ == 0) continue;
DataRow dataRow = dt.NewRow();
dataRow.ItemArray = row.Cells.Select(c => c.ToString()).ToArray();
dt.Rows.Add(dataRow);
}
ds.Tables.Add(dt);
}
return ds;
}
public static void SaveDataSetToXls(DataSet ds, string savedExcelFilePath)
{
//IWorkbook workbook = new XSSFWorkbook();
IWorkbook workbook = new HSSFWorkbook();
foreach (DataTable dt in ds.Tables)
{
ISheet sheet = workbook.CreateSheet(dt.TableName);
var row0 = sheet.CreateRow(0);//header
for (int j = 0; j < dt.Columns.Count; j++)
{
row0.CreateCell(j).SetCellValue(dt.Columns[j].ColumnName);
}
for (int i = 0; i < dt.Rows.Count; i++)//rest
{
var row = sheet.CreateRow(1+i);
for (int j = 0; j < dt.Columns.Count; j++)
row.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString());
}
}
FileStream sw = File.Create(savedExcelFilePath);
workbook.Write(sw);
sw.Close();
}
9 回答
以下是使用NPOI将Excel文件转换为DataSet的最小代码：
这样做可以完成任务 .
}
您可以通过NPOI轻松尝试：
在Codeplex网站here的下载部分中有
example package- 一包C＃示例 . 尝试一下，如果你还没有 .这是最简单的例子 -
您可以在Toxy项目中尝试ToxySpreadsheet - toxy.codeplex.com . ToxySpreadsheet.ToDataSet（）就是你想要的 .
NPOI是一种很好的免费阅读Excel文件的方法，现在在版本2中，您可以读取XLS和XLSX文件类型 .
在他们的网站上获取最新版NPOI：https://npoi.codeplex.com/
在项目中添加对以下文件的引用：NPOI.dll，NPOI.OOXML.dll和NPOI.OpenXml4Net.dll .
添加以下用法：使用NPOI.SS.UserModel;使用NPOI.HSSF.UserModel;使用NPOI.XSSF.UserModel;
现在，下面的代码可以解决问题，评论是西班牙语，对不起:-p
上面的代码假定工作表的第一行有列名 . 该代码还确定每个单元格的数据类型，并尝试将其转换为ADO数据类型 . 空白单元格将转换为null .
希望这可以帮助您和其他人处于相同的情况 .
我编辑了@Saeb Amini代码以允许空白单元格 .
基于@Sean的GREAT答案和example of NPOI，我将方法编写为
Convert all worksheets in xls file to DataSet和Convert DataSet back to xls file：