.net 读取excel表格数据

 

文件格式.xls

代码展示：

//集合列表

IList<ProjectStatuStat> projectStatuStats = new List<ProjectStatuStat>();

//取根目录下的excel

string absPath = HttpContext.Current.Server.MapPath("~/TemplateFiles/台账.xls");

//取excel的sheet的命名
DataTable dt = ExcelNPOIHelper.Import(absPath, "总清单");

//循环excel表格数据
foreach (DataRow row in dt.Rows)
{
ProjectStatuStat pss = new ProjectStatuStat();
pss.ProjectNumber = row[0].ToString().Trim();excel第一列
pss.ProjectName = row[1].ToString().Trim();excel第二列

projectStatuStats.Add(pss);

}

 

/// <summary>读取excel
/// 默认第一行为标头
/// </summary>
/// <param name="strFileName">文件全路径</param>
/// <param name="sheetName">sheetName</param>
/// <returns></returns>
public static DataTable Import(string strFileName,string sheetName)
{
DataTable dt = new DataTable();

HSSFWorkbook hssfworkbook;
using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
{
hssfworkbook = new HSSFWorkbook(file);
}
ISheet sheet = hssfworkbook.GetSheet(sheetName);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();

IRow headerRow = (HSSFRow)sheet.GetRow(0);
int cellCount = headerRow.LastCellNum;

for (int j = 0; j < cellCount; j++)
{
ICell cell = headerRow.GetCell(j);
dt.Columns.Add(cell.ToString());
}

for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
{
IRow row = (HSSFRow)sheet.GetRow(i);
DataRow dataRow = dt.NewRow();

for (int j = row.FirstCellNum; j < cellCount; j++)
{
if (row.GetCell(j) != null)
dataRow[j] = row.GetCell(j).ToString();
}

dt.Rows.Add(dataRow);
}
return dt;
}

 

 

文件格式不支持.xlsx  .若读取该格式可参考：https://www.cnblogs.com/wobuchifanqie/p/7685038.html