ASP.NET使用NPOI类库导出Excel
public class DataTableRenderToExcel { public static Stream RenderDataTableToExcel(DataTable SourceTable) { HSSFWorkbook workbook = new HSSFWorkbook(); MemoryStream ms = new MemoryStream(); HSSFSheet sheet = workbook.CreateSheet(); HSSFRow headerRow = sheet.CreateRow(0); // handling header. foreach (DataColumn column in SourceTable.Columns) headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName); // handling value. int rowIndex = 1; foreach (DataRow row in SourceTable.Rows) { HSSFRow dataRow = sheet.CreateRow(rowIndex); foreach (DataColumn column in SourceTable.Columns) { dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString()); } rowIndex++; } workbook.Write(ms); ms.Flush(); ms.Position = 0; sheet = null; headerRow = null; workbook = null; return ms; } /// <summary> /// /// </summary> /// <param name="SourceTable">数据源</param> /// <param name="ht">key-字段名称;value-excel标题</param> /// <returns></returns> public static Stream RenderDataTableToExcel(DataTable SourceTable, Dictionary<String, String> ht) { HSSFWorkbook workbook = new HSSFWorkbook(); MemoryStream ms = new MemoryStream(); HSSFSheet sheet = workbook.CreateSheet(); HSSFRow headerRow = sheet.CreateRow(0); // handling header. int index = 0; foreach (string item in ht.Values) { headerRow.CreateCell(index).SetCellValue(item); index++; } // handling value. int rowIndex = 1; foreach (DataRow row in SourceTable.Rows) { HSSFRow dataRow = sheet.CreateRow(rowIndex); index = 0; foreach (string item in ht.Keys) { dataRow.CreateCell(index).SetCellValue(row[item].ToString()); index++; } rowIndex++; } workbook.Write(ms); ms.Flush(); ms.Position = 0; sheet = null; headerRow = null; workbook = null; return ms; } public static void RenderDataTableToExcel(DataTable SourceTable, string FileName,string FilePath) { if (!Directory.Exists(FilePath)) Directory.CreateDirectory(FilePath); MemoryStream ms = RenderDataTableToExcel(SourceTable) as MemoryStream; FileStream fs = new FileStream(FilePath+"\\"+FileName, FileMode.Create, FileAccess.Write); byte[] data = ms.ToArray(); fs.Write(data, 0, data.Length); fs.Flush(); fs.Close(); data = null; ms = null; fs = null; } /// <summary> /// 下载文件 /// </summary> /// <param name="fs"></param> /// <param name="FileName"></param> public static void RenderDataTableToExcel(Stream fs, string FileName) { HttpContext curContext = HttpContext.Current; byte[] bytes = new byte[(int)fs.Length]; fs.Read(bytes, 0, bytes.Length); fs.Close(); curContext.Response.ContentType = "application/octet-stream"; //通知浏览器下载文件而不是打开 curContext.Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(FileName, System.Text.Encoding.UTF8)); curContext.Response.BinaryWrite(bytes); curContext.Response.Flush(); curContext.Response.End(); } public static DataTable RenderDataTableFromExcel(Stream ExcelFileStream, string SheetName, int HeaderRowIndex) { HSSFWorkbook workbook = new HSSFWorkbook(ExcelFileStream); HSSFSheet sheet = workbook.GetSheet(SheetName); DataTable table = new DataTable(); HSSFRow headerRow = sheet.GetRow(HeaderRowIndex); int cellCount = headerRow.LastCellNum; for (int i = headerRow.FirstCellNum; i < cellCount; i++) { DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue); table.Columns.Add(column); } int rowCount = sheet.LastRowNum; for (int i = (sheet.FirstRowNum + 1); i < sheet.LastRowNum; i++) { HSSFRow row = sheet.GetRow(i); DataRow dataRow = table.NewRow(); for (int j = row.FirstCellNum; j < cellCount; j++) dataRow[j] = row.GetCell(j).ToString(); } ExcelFileStream.Close(); workbook = null; sheet = null; return table; } public static DataTable RenderDataTableFromExcel(Stream ExcelFileStream, int SheetIndex, int HeaderRowIndex) { HSSFWorkbook workbook = new HSSFWorkbook(ExcelFileStream); HSSFSheet sheet = workbook.GetSheetAt(SheetIndex); DataTable table = new DataTable(); HSSFRow headerRow = sheet.GetRow(HeaderRowIndex); int cellCount = headerRow.LastCellNum; for (int i = headerRow.FirstCellNum; i < cellCount; i++) { DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue); table.Columns.Add(column); } int rowCount = sheet.LastRowNum; for (int i = (sheet.FirstRowNum + 1); i < sheet.LastRowNum; i++) { HSSFRow row = sheet.GetRow(i); DataRow dataRow = table.NewRow(); for (int j = row.FirstCellNum; j < cellCount; j++) { if (row.GetCell(j) != null) dataRow[j] = row.GetCell(j).ToString(); } table.Rows.Add(dataRow); } ExcelFileStream.Close(); workbook = null; sheet = null; return table; } /// <summary>读取excel /// 默认第一行为标头 /// </summary> /// <param name="path">excel文档路径</param> /// <returns></returns> public static DataTable RenderDataTableFromExcel(string path) { DataTable dt = new DataTable(); HSSFWorkbook hssfworkbook; using (FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read)) { hssfworkbook = new HSSFWorkbook(file); } HSSFSheet sheet = hssfworkbook.GetSheetAt(0); System.Collections.IEnumerator rows = sheet.GetRowEnumerator(); HSSFRow headerRow = sheet.GetRow(0); int cellCount = headerRow.LastCellNum; for (int j = 0; j < cellCount; j++) { HSSFCell cell = headerRow.GetCell(j); dt.Columns.Add(cell.ToString()); } for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++) { HSSFRow row = sheet.GetRow(i); DataRow dataRow = dt.NewRow(); for (int j = row.FirstCellNum; j < cellCount; j++) { if (row.GetCell(j) != null) dataRow[j] = row.GetCell(j).ToString(); } dt.Rows.Add(dataRow); } //while (rows.MoveNext()) //{ // HSSFRow row = (HSSFRow)rows.Current; // DataRow dr = dt.NewRow(); // for (int i = 0; i < row.LastCellNum; i++) // { // HSSFCell cell = row.GetCell(i); // if (cell == null) // { // dr[i] = null; // } // else // { // dr[i] = cell.ToString(); // } // } // dt.Rows.Add(dr); //} return dt; } }