public class ExcelHelper
{
/// <summary>
/// 读取Excel文件数据到DataSet，一个Sheet对应一个DataTable
/// </summary>
/// <param name="strExcelFilePath">Excel文件的物理路径</param>
/// <returns></returns>
public static DataSet GetDataFromExcel(string strExcelPhysicalPath, out string strError)
{
try
{
DataSet dsResult = new DataSet();
strError = "";
IWorkbook wbook = null;
using (FileStream fs = new FileStream(strExcelPhysicalPath, FileMode.Open, FileAccess.Read))
{
if (strExcelPhysicalPath.IndexOf(".xlsx") > 0)
{
wbook = new XSSFWorkbook(fs);
}
else
{
wbook = new HSSFWorkbook(fs);
}
}
for (int i = 0; i < wbook.NumberOfSheets; i++)
{
ISheet wsheet = wbook.GetSheetAt(i);
if (wsheet == null) continue;
DataTable dtSheet = GetDataFromSheet(wsheet, out strError);
if (dtSheet != null)
{
dtSheet.TableName = wsheet.SheetName.Trim();
dsResult.Tables.Add(dtSheet);
}
else
{
dsResult = null;
break;
}
}
return dsResult;
}
catch (Exception ex)
{
strError = ex.Message.ToString();
return null;
}
}
private static DataTable GetDataFromSheet(ISheet wsheet, out string strError)
{
try
{
DataTable dtResult = new DataTable();
strError = "";
//取sheet最大列数
int max_column = 0;
for (int i = wsheet.FirstRowNum; i <= wsheet.LastRowNum; i++)
{
IRow rsheet = wsheet.GetRow(i);
if (rsheet != null && rsheet.LastCellNum > max_column)
{
max_column = rsheet.LastCellNum;
}
}
//给DataTable添加列
for (int i = 0; i < max_column; i++)
{
dtResult.Columns.Add("A" + i.ToString());
}
for (int i = wsheet.FirstRowNum; i <= wsheet.LastRowNum; i++)
{
DataRow dRow = dtResult.NewRow();
IRow rsheet = wsheet.GetRow(i);
if (rsheet == null) continue;
for (int j = rsheet.FirstCellNum; j < rsheet.LastCellNum; j++)
{
ICell csheet = rsheet.GetCell(j);
if (csheet == null) continue;
switch (csheet.CellType)
{
case CellType.Blank:
dRow[j] = "";
break;
case CellType.Boolean:
dRow[j] = csheet.BooleanCellValue;
break;
case CellType.Error:
dRow[j] = csheet.ErrorCellValue;
break;
case CellType.Formula:
try
{
dRow[j] = csheet.NumericCellValue;
short format1 = csheet.CellStyle.DataFormat;
if (format1 == 177 || format1 == 178 || format1 == 188)
{
dRow[j] = csheet.NumericCellValue.ToString("#0.00");
}
}
catch
{
dRow[j] = csheet.StringCellValue.Trim();
}
break;
case CellType.Numeric:
try
{
short format2 = csheet.CellStyle.DataFormat;
if (format2 == 14 || format2 == 31 || format2 == 57 || format2 == 58)
{
dRow[j] = csheet.DateCellValue;
}
else
{
dRow[j] = csheet.NumericCellValue;
}
if (format2 == 177 || format2 == 178 || format2 == 188)
{
dRow[j] = csheet.NumericCellValue.ToString("#0.00");
}
}
catch
{
dRow[j] = csheet.StringCellValue.Trim();
}
break;
case CellType.String:
dRow[j] = csheet.StringCellValue.Trim();
break;
default:
dRow[j] = csheet.StringCellValue.Trim();
break;
}
}
dtResult.Rows.Add(dRow);
}
return dtResult;
}
catch (Exception ex)
{
strError = ex.Message.ToString();
return null;
}
}
}
亚马逊测评www.yisuping.com
