How to use NPOI to read excel into DataSet in C #

This article mainly introduces how to use NPOI to read excel into DataSet in C#, the content is detailed and easy to understand, the operation is simple and fast, and it has a certain reference value. I believe you will have something to gain after reading this article on how to use NPOI to read excel into DataSet in C#. Let's take a look.

NPOI reads excel to DataSet

/ read Execl data into DataTable (DataSet) / specify the Execl file path / set whether the first line is a column name / return an DataTable dataset public static DataSet ExcelToDataSet (string filePath, bool isFirstLineColumnName) {DataSet dataSet = new DataSet (); int startRow = 0 Try {using (FileStream fs = File.OpenRead (filePath)) {IWorkbook workbook = null; / / if it is the 2007 + Excel version if (filePath.IndexOf (".xlsx") > 0) {workbook = new XSSFWorkbook (fs) } / / if it is the 2003-Excel version else if (filePath.IndexOf (".xls") > 0) {workbook = new HSSFWorkbook (fs) } if (workbook! = null) {/ / Loop reads each sheet of Excel, each sheet page is converted to a DataTable, and for (int p = 0; p) is placed in DataSet.

< workbook.NumberOfSheets; p++) { ISheet sheet = workbook.GetSheetAt(p); DataTable dataTable = new DataTable(); dataTable.TableName = sheet.SheetName; if (sheet != null) { int rowCount = sheet.LastRowNum;//获取总行数 if (rowCount >

0) {IRow firstRow = sheet.GetRow (0); / / get the first line int cellCount = firstRow.LastCellNum / / get the total number of columns / / build the column if (isFirstLineColumnName) {startRow = 1 of datatable / / if the first row is a column name, read for (int I = firstRow.FirstCellNum; I < cellCount; + + I) {ICell cell = firstRow.GetCell (I) from the second line. If (cell! = null) {if (cell.StringCellValue! = null) { DataColumn column = new DataColumn (cell.StringCellValue) DataTable.Columns.Add (column) }} else {for (int I = firstRow.FirstCellNum) I < cellCount; + + I) {DataColumn column = new DataColumn ("column" + (I + 1)); dataTable.Columns.Add (column) }} / / fill the line for (int I = startRow; I 0) {ISheet sheet = workbook.CreateSheet (string.IsNullOrEmpty (dt.TableName)? ("sheet" + sheetIndex): dt.TableName); / / create a table named Sheet0 int rowCount = dt.Rows.Count;// rows int columnCount = dt.Columns.Count;// columns / / set column headers IRow row = sheet.CreateRow (0) / / the first line of excel is set to the column header for (int c = 0; c < columnCount; C++) {ICell cell = row.CreateCell (c); cell.SetCellValue (dt.Columns [c] .ColumnName) } / / set the cell for each column in each row, for (int I = 0; I < rowCount; icolumn +) {row = sheet.CreateRow (I + 1); for (int j = 0; j < columnCount) ICell cell +) {ICell cell = row.CreateCell (j); / / the second line of excel starts writing data cell.SetCellValue (dt.Rows [I] [j] .ToString ()) }} / / output data using (FileStream fs = File.OpenWrite (Outpath)) {workbook.Write (fs) to outPath / / write data result = true;} return result;} catch (Exception ex) {return false;}} to the open xls file

Call method

DataSet set = ExcelHelper.ExcelToDataTable ("test.xlsx", true); / / Excel import bool b = ExcelHelper.DataTableToExcel (set, "test2.xlsx"); / / Export Excel on "how to use NPOI to read excel to DataSet" this article is introduced here, thank you for reading! I believe you all have a certain understanding of "how to use NPOI to read excel into DataSet" in C#. If you want to learn more, you are welcome to follow the industry information channel.