c# npoi html,c# NPOI 方式读取 EXCEL表 类

namespaceNPOIClass

{

public classNPOIC

{private static int sheetCellNumMax = 12;///

///获取sheet表名///

///

///

public static string[] GetSheetName(stringfilePath)

{int sheetNumber = 0;var file = newFileStream(filePath, FileMode.Open, FileAccess.Read);if (filePath.IndexOf(".xlsx") > 0)

{//2007版本

var xssfworkbook = newXSSFWorkbook(file);

sheetNumber=xssfworkbook.NumberOfSheets;string[] sheetNames = new string[sheetNumber];for (int i = 0; i < sheetNumber; i++)

{

sheetNames[i]=xssfworkbook.GetSheetName(i);

}returnsheetNames;

}else if (filePath.IndexOf(".xls") > 0)

{//2003版本

var hssfworkbook = newHSSFWorkbook(file);

sheetNumber=hssfworkbook.NumberOfSheets;string[] sheetNames = new string[sheetNumber];for (int i = 0; i < sheetNumber; i++)

{

sheetNames[i]=hssfworkbook.GetSheetName(i);

}returnsheetNames;

}return null;

}///

///根据表名获取表///

///

///

///

public static DataTable ExcelToDataTable(string filePath, stringsheetName)

{string outMsg = "";var dt = newDataTable();string fileType =Path.GetExtension(filePath).ToLower();try{

ISheet sheet= null;

FileStream fs= newFileStream(filePath, FileMode.Open, FileAccess.Read);if (fileType == ".xlsx")

{//2007版

XSSFWorkbook workbook = newXSSFWorkbook(fs);

sheet=workbook.GetSheet(sheetName);if (sheet != null)

{

dt= GetSheetDataTable(sheet, outoutMsg);

}

}else if (fileType == ".xls")

{//2003版

HSSFWorkbook workbook = newHSSFWorkbook(fs);

sheet=workbook.GetSheet(sheetName);if (sheet != null)

{

dt= GetSheetDataTable(sheet, outoutMsg);

}

}

}catch(Exception e)

{

Console.WriteLine(e.Message);

}returndt;

}///

///获取sheet表对应的DataTable///

/// Excel工作表

///

///

private static DataTable GetSheetDataTable(ISheet sheet, out stringstrMsg)

{

strMsg= "";

DataTable dt= newDataTable();string sheetName =sheet.SheetName;int startIndex = 0;//sheet.FirstRowNum;

int lastIndex =sheet.LastRowNum;//最大列数

int cellCount = 0;

IRow maxRow= sheet.GetRow(0);for (int i = startIndex; i <= lastIndex; i++)

{

IRow row=sheet.GetRow(i);if (row != null && cellCount

{

cellCount=row.LastCellNum;

maxRow=row;

}

}//列名设置

try{//maxRow.LastCellNum = 12//L

for (int i = 0; i < sheetCellNumMax; i++)//maxRow.FirstCellNum

{

dt.Columns.Add(Convert.ToChar(((int)'A') +i).ToString());//DataColumn column = new DataColumn("Column" + (i + 1).ToString());//dt.Columns.Add(column);

}

}catch{

strMsg= "工作表" + sheetName + "中无数据";return null;

}//数据填充

for (int i = startIndex; i <= lastIndex; i++)

{

IRow row=sheet.GetRow(i);

DataRow drNew=dt.NewRow();if (row != null)

{for (int j = row.FirstCellNum; j < row.LastCellNum; ++j)

{if (row.GetCell(j) != null)

{

ICell cell=row.GetCell(j);switch(cell.CellType)

{caseCellType.Blank:

drNew[j]= "";break;caseCellType.Numeric:short format =cell.CellStyle.DataFormat;//对时间格式(2015.12.5、2015/12/5、2015-12-5等)的处理

if (format == 14 || format == 31 || format == 57 || format == 58)

drNew[j]=cell.DateCellValue;elsedrNew[j]=cell.NumericCellValue;if (cell.CellStyle.DataFormat == 177 || cell.CellStyle.DataFormat == 178 || cell.CellStyle.DataFormat == 188)

drNew[j]= cell.NumericCellValue.ToString("#0.00");break;caseCellType.String:

drNew[j]=cell.StringCellValue;break;caseCellType.Formula:try{

drNew[j]=cell.NumericCellValue;if (cell.CellStyle.DataFormat == 177 || cell.CellStyle.DataFormat == 178 || cell.CellStyle.DataFormat == 188)

drNew[j]= cell.NumericCellValue.ToString("#0.00");

}catch{try{

drNew[j]=cell.StringCellValue;

}catch{ }

}break;default:

drNew[j]=cell.StringCellValue;break;

}

}

}

}

dt.Rows.Add(drNew);

}returndt;

}

}

}