我想读Excel表格使用NPOI 2010 XLSX，然后将数据导出到数据表，但不知道如何使用它。 谁能告诉我一步一步如何导出Excel中的数据表？ 我已经下载NPOI.dll，加入参考，但不知道是什么...进一步
Answer 1:
以下是关于您可以使用Excel文件转换为使用NPOI一个DataSet中的最小码：
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);
}
Answer 2:
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;
}
}
Answer 3:
你可以这样做，完成你的任务。
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;
}
Answer 4:
您可以尝试通过这种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;
}
Answer 5:
在CodePlex网站上这里在下载部分有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));
}
}
}
Answer 6:
您可以尝试在ToxySpreadsheet项目Toxy - toxy.codeplex.com。 ToxySpreadsheet.ToDataSet（）是你想要的。
Answer 7:
NPOI是读取Excel文件伟大而自由的方式，现在在2版本，你可以同时读取XLS和XLSX文件类型。
- 获取最新版本NPOI在他们的网站： https://npoi.codeplex.com/
- 在你的项目中添加引用到下列文件：NPOI.dll，NPOI.OOXML.dll和NPOI.OpenXml4Net.dll。
- 添加以下Usings：使用NPOI.SS.UserModel; 使用NPOI.HSSF.UserModel; 使用NPOI.XSSF.UserModel;
现在，下面的代码是卓有成效的，评论是在西班牙，对于:-P对不起
private DataTable Excel_To_DataTable(string pRutaArchivo, int pHojaIndex)
{
// --------------------------------- //
/* REFERENCIAS:
* NPOI.dll
* NPOI.OOXML.dll
* NPOI.OpenXml4Net.dll */
// --------------------------------- //
/* USING:
* using NPOI.SS.UserModel;
* using NPOI.HSSF.UserModel;
* using NPOI.XSSF.UserModel; */
// --------------------------------- //
DataTable Tabla = null;
try
{
if (System.IO.File.Exists(pRutaArchivo))
{
IWorkbook workbook = null; //IWorkbook determina se es xls o xlsx
ISheet worksheet = null;
string first_sheet_name = "";
using (FileStream FS = new FileStream(pRutaArchivo, FileMode.Open, FileAccess.Read))
{
workbook = WorkbookFactory.Create(FS); //Abre tanto XLS como XLSX
worksheet = workbook.GetSheetAt(pHojaIndex); //Obtener Hoja por indice
first_sheet_name = worksheet.SheetName; //Obtener el nombre de la Hoja
Tabla = new DataTable(first_sheet_name);
Tabla.Rows.Clear();
Tabla.Columns.Clear();
// Leer Fila por fila desde la primera
for (int rowIndex = 0; rowIndex <= worksheet.LastRowNum; rowIndex++)
{
DataRow NewReg = null;
IRow row = worksheet.GetRow(rowIndex);
IRow row2 = null;
if (row != null) //null is when the row only contains empty cells
{
if (rowIndex > 0) NewReg = Tabla.NewRow();
//Leer cada Columna de la fila
foreach (ICell cell in row.Cells)
{
object valorCell = null;
string cellType = "";
if (rowIndex == 0) //Asumo que la primera fila contiene los titlos:
{
row2 = worksheet.GetRow(rowIndex + 1); //Si es la rimera fila, obtengo tambien la segunda para saber los tipos:
ICell cell2 = row2.GetCell(cell.ColumnIndex);
switch (cell2.CellType)
{
case CellType.Boolean: cellType = "System.Boolean"; break;
case CellType.String: cellType = "System.String"; break;
case CellType.Numeric:
if (HSSFDateUtil.IsCellDateFormatted(cell2)) { cellType = "System.DateTime"; }
else { cellType = "System.Double"; } break;
case CellType.Formula:
switch (cell2.CachedFormulaResultType)
{
case CellType.Boolean: cellType = "System.Boolean"; break;
case CellType.String: cellType = "System.String"; break;
case CellType.Numeric:
if (HSSFDateUtil.IsCellDateFormatted(cell2)) { cellType = "System.DateTime"; }
else { cellType = "System.Double"; } break;
}
break;
default:
cellType = "System.String"; break;
}
//Agregar los campos de la tabla:
DataColumn codigo = new DataColumn(cell.StringCellValue, System.Type.GetType(cellType));
Tabla.Columns.Add(codigo);
}
else
{
//Las demas filas son registros:
switch (cell.CellType)
{
case CellType.Blank: valorCell = DBNull.Value; break;
case CellType.Boolean: valorCell = cell.BooleanCellValue; break;
case CellType.String: valorCell = cell.StringCellValue; break;
case CellType.Numeric:
if (HSSFDateUtil.IsCellDateFormatted(cell)) { valorCell = cell.DateCellValue; }
else { valorCell = cell.NumericCellValue; } break;
case CellType.Formula:
switch (cell.CachedFormulaResultType)
{
case CellType.Blank: valorCell = DBNull.Value; break;
case CellType.String: valorCell = cell.StringCellValue; break;
case CellType.Boolean: valorCell = cell.BooleanCellValue; break;
case CellType.Numeric:
if (HSSFDateUtil.IsCellDateFormatted(cell)) { valorCell = cell.DateCellValue; }
else { valorCell = cell.NumericCellValue; }
break;
}
break;
default: valorCell = cell.StringCellValue; break;
}
NewReg[cell.ColumnIndex] = valorCell;
}
}
}
if (rowIndex > 0) Tabla.Rows.Add(NewReg);
}
Tabla.AcceptChanges();
}
}
else
{
throw new Exception("ERROR 404: El archivo especificado NO existe.");
}
}
catch (Exception ex)
{
throw ex;
}
return Tabla;
}
上面的代码假定片材的第一行具有的列名。 该代码还确定每个单元的数据类型，并试图将其转换为一个ADO数据类型。 空白单元被转换为空。
希望这有助于你和其他人在同样的情况。
Answer 8:
我编辑@Saeb阿米尼代码，允许空白单元格。
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;
Answer 9:
立足@Sean最伟大的答案和例子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();
}
文章来源: export Excel to DataTable using NPOI
