Npoi Read-Write excel--supplement

usingSystem;usingSystem.Collections.Generic;usingSystem.Text;usingSystem.Data;usingSystem.IO;usingNpoi. HSSF. Usermodel;usingNpoi. Ss. Usermodel;namespacecommon.excel{ Public Static classNpoihandler { Public StaticDataSet Exceltodataset (stringExcelpath) {            returnExceltodataset (Excelpath,true); }         Public StaticDataSet Exceltodataset (stringExcelpath,BOOLFirstrowasheader) {            intSheetcount; returnExceltodataset (Excelpath, Firstrowasheader, outsheetcount); }         Public StaticDataSet Exceltodataset (stringExcelpath,BOOLFirstrowasheader, out intSheetcount) {            using(DataSet ds =NewDataSet ()) {                using(FileStream FileStream =NewFileStream (Excelpath, FileMode.Open, FileAccess.Read)) {Hssfworkbook Workbook=NewHssfworkbook (FileStream); Hssfformulaevaluator Evaluator=Newhssfformulaevaluator (Workbook); Sheetcount=workbook.                    Numberofsheets;  for(inti =0; i < Sheetcount; ++i) {hssfsheet sheet= Workbook. Getsheetat (i) asHssfsheet; DataTable DT=exceltodatatable (sheet, evaluator, firstrowasheader); Ds.                    Tables.add (DT); }                    returnds; }            }        }         Public StaticDataTable exceltodatatable (stringExcelpath,stringsheetname) {            returnExceltodatatable (Excelpath, SheetName,true); }         Public StaticDataTable exceltodatatable (stringExcelpath,stringSheetName,BOOLFirstrowasheader) {            using(FileStream FileStream =NewFileStream (Excelpath, FileMode.Open, FileAccess.Read)) {Hssfworkbook Workbook=NewHssfworkbook (FileStream); Hssfformulaevaluator Evaluator=Newhssfformulaevaluator (Workbook); Hssfsheet sheet= Workbook. Getsheet (SheetName) asHssfsheet; returnexceltodatatable (sheet, evaluator, firstrowasheader); }        }        Private StaticDataTable exceltodatatable (hssfsheet sheet, Hssfformulaevaluator evaluator,BOOLFirstrowasheader) {            if(firstrowasheader) {returnExceltodatatablefirstrowasheader (sheet, evaluator); }            Else            {                returnexceltodatatable (sheet, evaluator); }        }        Private StaticDataTable Exceltodatatablefirstrowasheader (hssfsheet sheet, Hssfformulaevaluator evaluator) {using(DataTable dt =NewDataTable ()) {Hssfrow FirstRow= Sheet. GetRow (0) asHssfrow; intCellcount =Getcellcount (sheet);  for(inti =0; i < Cellcount; i++)                {                    if(Firstrow.getcell (i)! =NULL) {dt. Columns.Add (Firstrow.getcell (i). Stringcellvalue??string. Format ("f{0}", i +1),typeof(string)); }                    Else{dt. Columns.Add (string. Format ("f{0}", i +1),typeof(string)); }                }                 for(inti =1; I <= sheet. Lastrownum; i++) {Hssfrow row= Sheet. GetRow (i) asHssfrow; DataRow Dr=dt.                    NewRow (); Filldatarowbyhssfrow (row, evaluator,refDR); Dt.                Rows.Add (DR); } dt. TableName=sheet.                SheetName; returnDT; }        }        Private StaticDataTable exceltodatatable (hssfsheet sheet, Hssfformulaevaluator evaluator) {using(DataTable dt =NewDataTable ()) {                if(Sheet. Lastrownum! =0)                {                    intCellcount =Getcellcount (sheet);  for(inti =0; i < Cellcount; i++) {dt. Columns.Add (string. Format ("f{0}", i),typeof(string)); }                     for(inti =0; I < sheet. Firstrownum; ++i) {DataRow Dr=dt.                        NewRow (); Dt.                    Rows.Add (DR); }                     for(inti = sheet. Firstrownum; I <= sheet. Lastrownum; i++) {Hssfrow row= Sheet. GetRow (i) asHssfrow; DataRow Dr=dt.                        NewRow (); Filldatarowbyhssfrow (row, evaluator,refDR); Dt.                    Rows.Add (DR); }} dt. TableName=sheet.                SheetName; returnDT; }        }        Private Static voidFilldatarowbyhssfrow (Hssfrow row, Hssfformulaevaluator evaluator,refDataRow DR) {            if(Row! =NULL)            {                 for(intj =0; J < Dr. Table.Columns.Count; J + +) {Hssfcell cell= row. Getcell (j) asHssfcell; if(Cell! =NULL)                    {                        Switch(cell. Celltype) { CaseCelltype.blank:dr[j]=DBNull.Value;  Break;  CaseCelltype.boolean:dr[j]=cell.                                Booleancellvalue;  Break;  CaseCelltype.numeric:if(dateutil.iscelldateformatted (cell)) {Dr[j]=cell.                                Datecellvalue; }                                Else{Dr[j]=cell.                                Numericcellvalue; }                                 Break;  CaseCelltype.string:dr[j]=cell.                                Stringcellvalue;  Break;  CaseCelltype.error:dr[j]=cell.                                Errorcellvalue;  Break;  CaseCellType.FORMULA:cell= Evaluator. Evaluateincell (cell) asHssfcell; DR[J]=cell.                                ToString ();  Break; default:                                Throw NewNotSupportedException (string. Format ("catched unhandle celltype[{0}]", Cell.                        Celltype)); }                    }                }            }        }        Private Static intgetcellcount (hssfsheet sheet) {intFirstrownum =sheet.            Firstrownum; intCellcount =0;  for(inti = sheet. Firstrownum; I <= sheet. Lastrownum; ++i) {hssfrow row= Sheet. GetRow (i) asHssfrow; if(Row! =NULL&& row. Lastcellnum >Cellcount) {Cellcount=row.                Lastcellnum; }            }            returnCellcount; }    }}

View Code

Excelpath: The absolute or relative path to the server-side file that needs to be passed in.

It is important to note that the sheet. Lastrownum = sheet. PhysicalNumberOfRows-1, there may be a bug: Sheet.lastrownum 0,physicalnumberofrows behaves normally when there is no data or only one row of data.

Npoi Read-Write excel--supplement