usingNpoi. HSSF. Usermodel;usingNpoi. Ss. Usermodel;usingNpoi. XSSF. Usermodel;usingSystem;usingSystem.Collections.Generic;usingSystem.Data;usingSystem.Globalization;usingSystem.IO;usingSystem.Linq;usingSystem.Text;usingSystem.Threading;namespaceEasy Storage Program { Public classExclecore {PrivateIworkbook _workbook; Private string_filepath; Publiclist<string> Getsheetnames {Get;Set; } Publiclist<string> Sheetnames {Get;Set; } PublicExclecore (stringFilePath) {Sheetnames=Newlist<string>(); _filepath=FilePath; LoadFile (); } #regionExcel Information/// <summary> ///Get Excel Information/// </summary> /// <param name= "FilePath" ></param> Publiclist<string>LoadFile () {varPrevculture =Thread.CurrentThread.CurrentCulture; Thread.CurrentThread.CurrentCulture=CultureInfo.InvariantCulture; Sheetnames=Newlist<string>(); stringFileext =path.getextension (_filepath); using(varFS =NewFileStream (_filepath, FileMode.Open, FileAccess.Read, Fileshare.readwrite)) { //Create Hssfworkbook, the entire Excel document, based on the path through existing Excel//depending on the suffix, decide to instantiate the object if(Fileext = =". xls") {Hssfworkbook workbook=NewHssfworkbook (FS); _workbook=workbook; intx =workbook. Workbook.numsheets; List<string> sheetnames =Newlist<string>(); for(inti =0; i < x; i++) {sheetnames.add (workbook. Workbook.getsheetname (i)); } } Else if(Fileext = =". xlsx") {Xssfworkbook workbook=NewXssfworkbook (FS); _workbook=workbook; intx =workbook. Numberofsheets;; List<string> sheetnames =Newlist<string>(); for(inti =0; i < x; i++) {sheetnames.add (workbook. Getsheetname (i)); } }} getsheetnames=Sheetnames; returnGetsheetnames; } #endregion #regionGet Data source/// <summary> ///get all the data and convert all sheet data into a DataTable. /// </summary> /// <param name= "Isfirstrowcoumn" >whether to use the first row as the column header</param> /// <returns></returns> PublicDataSet Getalltables (BOOLisfirstrowcoumn) { varStopTime =NewSystem.Diagnostics.Stopwatch (); Stoptime.start (); varDS =NewDataSet (); foreach(varSheetNameinchsheetnames) {ds. Tables.add (Exceltodatatable (SheetName, Isfirstrowcoumn)); } stoptime.stop (); Console.WriteLine ("GetData:"+ Stoptime.elapsedmilliseconds/ ++"S"); returnds; } /// <summary> ///get the first<paramref name= "idx"/>the data of the sheet/// </summary> /// <param name= "idx" >the sheet table for the Excel file</param> /// <param name= "Isfirstrowcoumn" >whether to use the first row as the column header</param> /// <returns></returns> PublicDataTable GetTable (intIdxBOOLisfirstrowcoumn) { if(idx >= Sheetnames.count | | idx <0) Throw NewException ("Do don't Get this Sheet"); returnexceltodatatable (Sheetnames[idx], isfirstrowcoumn); } /// <summary> ///gets the sheet name called<paramref name= "SheetName"/>of Data/// </summary> /// <param name= "SheetName" >Sheet name</param> /// <param name= "Isfirstrowcolumn" >whether to use the first row as the column header</param> /// <returns></returns> PublicDataTable GetTable (stringSheetName,BOOLisfirstrowcolumn) { returnexceltodatatable (SheetName, Isfirstrowcolumn); } #endregion #regionMethod/// <summary> ///import data from Excel into a DataTable/// </summary> /// <param name= "SheetName" >the name of the Excel workbook sheet</param> /// <param name= "Isfirstrowcolumn" >whether the first row is a column name for a DataTable</param> /// <returns>the DataTable returned</returns> PublicDataTable exceltodatatable (stringSheetName,BOOLisfirstrowcolumn) {Isheet sheet=NULL; vardata =NewDataTable (); Data. TableName=SheetName; intStartRow =0; Try{Sheet= SheetName! =NULL? _workbook. Getsheet (sheetname): _workbook. Getsheetat (0); if(Sheet! =NULL) { varFirstRow = sheet. GetRow (0); if(FirstRow = =NULL) returndata; intCellcount = Firstrow.lastcellnum;//the number of the last cell in a row is the total number of columnsStartRow = Isfirstrowcolumn? Sheet. Firstrownum +1: Sheet. Firstrownum; for(inti = Firstrow.firstcellnum; i < Cellcount; ++i) {//. Stringcellvalue; varColumn =NewDataColumn (Convert.tochar ((int)'A') +i). ToString ()); if(isfirstrowcolumn) {varColumnName =Firstrow.getcell (i). Stringcellvalue; Column=NewDataColumn (columnName); } data. Columns.Add (column); } //the label of the last column intRowCount =sheet. Lastrownum; for(inti = StartRow; I <= RowCount; ++i) {IRow row=sheet. GetRow (i); if(Row = =NULL)Continue;//rows with no data default is nullDataRow DataRow=data. NewRow (); for(intj = row. Firstcellnum; J < Cellcount; ++j) {if(Row. Getcell (j)! =NULL)//Similarly, cells that do not have data are null by defaultDATAROW[J] =row. Getcell (J, Missingcellpolicy.return_null_and_blank). ToString (); } data. Rows.Add (DataRow); } } Else Throw NewException ("Don not has this Sheet"); returndata; } Catch(Exception ex) {Console.WriteLine ("Exception:"+Ex. Message); return NULL; } } #endregion }}
Can read. XLS and. xlsx files.
Npoi Auxiliary class