- public static DataTable ExcelToDataTable(string filePath)
- {
- DataTable dt = new DataTable();
- HSSFWorkbook hssfworkbook;
- using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
- {
- hssfworkbook = new HSSFWorkbook(file);
- }
- ISheet sheet = hssfworkbook.GetSheetAt(0);
- System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
- IRow headerRow = sheet.GetRow(0);
- int cellCount = headerRow.LastCellNum;
- for (int j = 0; j < cellCount; j++)
- {
- ICell cell = headerRow.GetCell(j);
- dt.Columns.Add(cell.ToString());
- }
- for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
- {
- IRow row = sheet.GetRow(i);
- DataRow dataRow = dt.NewRow();
- if (row == null)
- {
- break;
- }
- for (int j = row.FirstCellNum; j < cellCount; j++)
- {
- if (row.GetCell(j) != null)
- dataRow[j] = row.GetCell(j).ToString();
- }
- dt.Rows.Add(dataRow);
- }
- return dt;
- }
复制代码 导出到excel
- public static MemoryStream DataToExcel(DataTable dt)
- {
- MemoryStream ms = new MemoryStream();
- using (dt)
- {
- IWorkbook workbook = new HSSFWorkbook();//创建excel工作簿
- ISheet sheet = workbook.CreateSheet();//在该表中创建工作表
- IRow headerRow = sheet.CreateRow(0); //在表中添加一行
- foreach (DataColumn column in dt.Columns)
- headerRow.CreateCell(column.Ordinal).SetCellValue(column.Caption);
- int rowIndex = 1;
- foreach (DataRow row in dt.Rows)
- {
- IRow dataRow = sheet.CreateRow(rowIndex);
- foreach (DataColumn column in dt.Columns)
- {
- dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
- }
- rowIndex++;
- }
- workbook.Write(ms);
- ms.Flush();
- ms.Position = 0;
- }
- return ms;
- }
复制代码 接着
- MemoryStream ms = ExcelHelper.DataToExcel(dt);
- FileStream fs = new FileStream("e:\\2.xls", FileMode.Create);
- ms.WriteTo(fs);
- fs.Close();
- ms.Close();
复制代码
|
|