NPOI-Excel File Import and Export

 1  public static DataTable ExcelToDataTable(string filePath) 2         { 3             DataTable dt = new DataTable(); 4  5             HSSFWorkbook hssfworkbook; 6             using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read)) 7             { 8                 hssfworkbook = new HSSFWorkbook(file); 9             }10             ISheet sheet = hssfworkbook.GetSheetAt(0);11             System.Collections.IEnumerator rows = sheet.GetRowEnumerator();12 13             IRow headerRow = sheet.GetRow(0);14             int cellCount = headerRow.LastCellNum;15 16             for (int j = 0; j < cellCount; j++)17             {18                 ICell cell = headerRow.GetCell(j);19                 dt.Columns.Add(cell.ToString());20             }21 22             for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)23             {24                 IRow row = sheet.GetRow(i);25                 DataRow dataRow = dt.NewRow();26                 if (row == null)27                 {28                     break;29                 }30                 for (int j = row.FirstCellNum; j < cellCount; j++)31                 {32                     if (row.GetCell(j) != null)33                         dataRow[j] = row.GetCell(j).ToString();34                 }35 36                 dt.Rows.Add(dataRow);37             }38             return dt;39         }

 

 

Export to excel

1 public static MemoryStream DataToExcel (DataTable dt) 2 {3 MemoryStream MS = new MemoryStream (); 4 using (dt) 5 {6 IWorkbook workbook = new HSSFWorkbook (); // create an excel workbook 7 ISheet sheet = workbook. createSheet (); // create a worksheet in this table 8 IRow headerRow = sheet. createRow (0); // Add a row 9 foreach (DataColumn column in dt. columns) 10 headerRow. createCell (column. ordinal ). setCellValue (column. caption); 11 int rowIndex = 1; 12 foreach (DataRow row in dt. rows) 13 {14 IRow dataRow = sheet. createRow (rowIndex); 15 foreach (DataColumn column in dt. columns) 16 {17 dataRow. createCell (column. ordinal ). setCellValue (row [column]. toString (); 18} 19 rowIndex ++; 20} 21 workbook. write (ms); 22 ms. flush (); 23 ms. position = 0; 24} 25 return MS; 26}

Next

1 MemoryStream ms = ExcelHelper.DataToExcel(dt);2 FileStream fs = new FileStream("e:\\2.xls", FileMode.Create);3 ms.WriteTo(fs);4 fs.Close();5 ms.Close();