EXCEL导出.doc

转自：/Item/NPOI-MyXls-DataTable-To-Excel-From-Excel.htmlNPOI开源地址：/NPOI中文文档：/tonyqus/archive/2009/04/12/1434209.htmlMyXls开源地址：/projects/myxls/NPOI 快速入门例子:1. / 2. / NPOI简单Demo，快速入门代码 3. / 4. / 5. / 6. / NPOI认为Excel的第一个单元格是：(0，0) 7. / 柳永法 / 2010-5-8 22:21:41 8. public static void ExportEasy(DataTable dtSource, string strFileName) 9. 10. HSSFWorkbook workbook = new HSSFWorkbook(); 11. HSSFSheet sheet = workbook.CreateSheet(); 12. 13. /填充表头 14. HSSFRow dataRow = sheet.CreateRow(0); 15. foreach (DataColumn column in dtSource.Columns) 16. 17. dataRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName); 18. 19. 20. 21. /填充内容 22. for (int i = 0; i dtSource.Rows.Count; i+) 23. 24. dataRow = sheet.CreateRow(i + 1); 25. for (int j = 0; j dtSource.Columns.Count; j+) 26. 27. dataRow.CreateCell(j).SetCellValue(dtSource.Rowsij.ToString(); 28. 29. 30. 31. 32. /保存 33. using (MemoryStream ms = new MemoryStream() 34. 35. using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write) 36. 37. workbook.Write(fs); 38. 39. 40. workbook.Dispose(); 41. 接下来是柳永法(yongfa365)Blog封装的可以用在实际项目中的类，实现的功能有（仅NPOI）：1. 支持web及winform从DataTable导出到Excel。 2. 生成速度很快。 3. 准确判断数据类型，不会出现身份证转数值等上面提到的一系列问题。 4. 如果单页条数大于65535时会新建工作表。 5. 列宽自适应。 6. 支持读取Excel。 7. 调用方便，只一调用一个静态类就OK了。 因为测试期间发现MyXls导出速度要比NPOI慢3倍，而NPOI既能满足我们的导出需求，又能很好的满足我们的导入需求，所以只针对NPOI进行全方位功能实现及优化。NPOI导入导出相关类：NPOI导入导出相关类：using System; using System.Collections.Generic; using System.Data; using System.IO; using System.Text; using System.Web; using NPOI; using NPOI.HPSF; using NPOI.HSSF; using NPOI.HSSF.UserModel; using NPOI.HSSF.Util; using NPOI.POIFS; using NPOI.Util; public class ExcelHelper / / DataTable导出到Excel文件 / / 源DataTable / 表头文本 / 保存位置 / 柳永法 / 2010-5-8 22:21:41 public static void Export(DataTable dtSource, string strHeaderText, string strFileName) using (MemoryStream ms = Export(dtSource, strHeaderText) using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write) byte data = ms.ToArray(); fs.Write(data, 0, data.Length); fs.Flush(); / / DataTable导出到Excel的MemoryStream / / 源DataTable / 表头文本 / 柳永法 / 2010-5-8 22:21:41 public static MemoryStream Export(DataTable dtSource, string strHeaderText) HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.CreateSheet(); #region 右击文件 属性信息 DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation(); dsi.Company = /; workbook.DocumentSummaryInformation = dsi; SummaryInformation si = PropertySetFactory.CreateSummaryInformation(); si.Author = 柳永法; /填加xls文件作者信息 si.ApplicationName = NPOI测试程序; /填加xls文件创建程序信息 si.LastAuthor = 柳永法2; /填加xls文件最后保存者信息 si.Comments = 说明信息; /填加xls文件作者信息 si.Title = NPOI测试; /填加xls文件标题信息 si.Subject = NPOI测试Demo;/填加文件主题信息 si.CreateDateTime = DateTime.Now; workbook.SummaryInformation = si; #endregion HSSFCellStyle dateStyle = workbook.CreateCellStyle(); HSSFDataFormat format = workbook.CreateDataFormat(); dateStyle.DataFormat = format.GetFormat(yyyy-mm-dd); /取得列宽 int arrColWidth = new intdtSource.Columns.Count; foreach (DataColumn item in dtSource.Columns) arrColWidthitem.Ordinal = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString().Length; for (int i = 0; i dtSource.Rows.Count; i+) for (int j = 0; j arrColWidthj) arrColWidthj = intTemp; int rowIndex = 0; foreach (DataRow row in dtSource.Rows) #region 新建表，填充表头，填充列头，样式 if (rowIndex = 65535 | rowIndex = 0) if (rowIndex != 0) sheet = workbook.CreateSheet(); #region 表头及样式 HSSFRow headerRow = sheet.CreateRow(0); headerRow.HeightInPoints = 25; headerRow.CreateCell(0).SetCellValue(strHeaderText); HSSFCellStyle headStyle = workbook.CreateCellStyle(); headStyle.Alignment = CellHorizontalAlignment.CENTER; HSSFFont font = workbook.CreateFont(); font.FontHeightInPoints = 20; font.Boldweight = 700; headStyle.SetFont(font); headerRow.GetCell(0).CellStyle = headStyle; sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1); headerRow.Dispose(); #endregion #region 列头及样式 HSSFRow headerRow = sheet.CreateRow(1); HSSFCellStyle headStyle = workbook.CreateCellStyle(); headStyle.Alignment = CellHorizontalAlignment.CENTER; HSSFFont font = workbook.CreateFont(); font.FontHeightInPoints = 10; font.Boldweight = 700; headStyle.SetFont(font); foreach (DataColumn column in dtSource.Columns) headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName); headerRow.GetCell(column.Ordinal).CellStyle = headStyle; /设置列宽 sheet.SetColumnWidth(column.Ordinal, (arrColWidthcolumn.Ordinal + 1) * 256); headerRow.Dispose(); #endregion rowIndex = 2; #endregion #region 填充内容 HSSFRow dataRow = sheet.CreateRow(rowIndex); foreach (DataColumn column in dtSource.Columns) HSSFCell newCell = dataRow.CreateCell(column.Ordinal); string drValue = rowcolumn.ToString(); switch (column.DataType.ToString() case System.String:/字符串类型 newCell.SetCellValue(drValue); break; case System.DateTime:/日期类型 DateTime dateV; DateTime.TryParse(drValue, out dateV); newCell.SetCellValue(dateV); newCell.CellStyle = dateStyle;/格式化显示 break; case System.Boolean:/布尔型 bool boolV = false; bool.TryParse(drValue, out boolV); newCell.SetCellValue(boolV); break; case System.Int16:/整型 case System.Int32: case System.Int64: case System.Byte: int intV = 0; int.TryParse(drValue, out intV); newCell.SetCellValue(intV); break; case System.Decimal:/浮点型 case System.Double: double doubV = 0; double.TryParse(drValue, out doubV); newCell.SetCellValue(doubV); break; case System.DBNull:/空值处理 newCell.SetCellValue(); break; default: newCell.SetCellValue(); break; #endregion rowIndex+; using (MemoryStream ms = new MemoryStream() workbook.Write(ms); ms.Flush(); ms.Position = 0; sheet.Dispose(); /workbook.Dispose();/一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet return ms; / / 用于Web导出 / / 源DataTable / 表头文本 / 文件名 / 柳永法 / 2010-5-8 22:21:41 public static void ExportByWeb(DataTable dtSource, string strHeaderText, string strFileName) HttpContext curContext = HttpContext.Current; / 设置编码和附件格式 curContext.Response.ContentType = application/vnd.ms-excel; curContext.Response.ContentEncoding = Encoding.UTF8; curContext.Response.Charset = ; curContext.Response.AppendHeader(Content-Disposition, attachment;filename= + HttpUtility.UrlEncode(strFileName, Encoding.UTF8); curContext.Response.BinaryWrite(Export(dtSource, strHeaderText).GetBuffer(); curContext.Response.End(); / 读取excel / 默认第一行为标头 / / excel文档路径 / public static DataTable Import(string strFileName) DataTable dt = new DataTable(); HSSFWorkbook hssfworkbook; using (FileStream file = new FileStream(strFileName, 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) dataRowj = row.GetCell(j).ToString(); dt.Rows.Add(dataRow); return dt; 第二种方法：public void WriteToExcel(DataTable table) try strin