asp.net 使用NPOI读取excel文件
程序员文章站
2023-11-22 08:15:52
asp.net 使用NPOI读取excel文件内容 NPOI下载地址:NPOI public class ExcelHelper { /// /// 读取Excel文件数据到DataSet,一个Sheet对应一个DataTable /// ///
asp.net 使用npoi读取excel文件内容
npoi下载地址:npoi
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; } } }
推荐阅读
-
C#使用NPOI导入Excel的方法详解
-
使用Python脚本从文件读取数据代码实例
-
ASP.NET中读取XML文件信息的4种方法与示例代码
-
ASP.NET core Web中使用appsettings.json配置文件的方法
-
java读取word-excel-ppt文件代码
-
Asp.net MVC下使用Bundle合并、压缩js与css文件详解
-
php读取EXCEL文件 php excelreader读取excel文件
-
ASP.NET插件uploadify批量上传文件完整使用教程
-
使用pandas模块读取csv文件和excel表格,并用matplotlib画图的方法
-
PHP读取大文件的类SplFileObject使用介绍