EXCEL导出.doc

1、转自：http:/ 开源地址：http:/ 中文文档：http:/ 开源地址：http:/ 快速入门例子:1. / 2. / NPOI简单 Demo，快速入门代码 3. / 4. / 5. / 6. / NPOI认为 Excel的第一个单元格是：(0，0) 7. / 柳永法 http:/ 2010-5-8 22:21:41 8. public static void ExportEasy(DataTable dtSource, string strFileName) 9. 10. HSSFWorkbook workbook = new HSSFWorkbook(); 11. HSSFSheet

2、 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 / DataTable导出到 Excel文件 / / 源 DataTable

3、/ 表头文本 / 保存位置 / 柳永法 http:/ 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()

4、; fs.Write(data, 0, data.Length); fs.Flush(); / / DataTable导出到 Excel的 MemoryStream / / 源 DataTable / 表头文本 / 柳永法 http:/ 2010-5-8 22:21:41 public static MemoryStream Export(DataTable dtSource, string strHeaderText) HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.CreateSheet(); #

5、region 右击文件 属性信息 DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation(); dsi.Company = “http:/ workbook.DocumentSummaryInformation = dsi; SummaryInformation si = PropertySetFactory.CreateSummaryInformation(); si.Author = “柳永法“; /填加 xls文件作者信息 si.ApplicationName = “NPOI

6、测试程序“; /填加 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.CreateCellStyl

7、e(); 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(

8、).Length; for (int i = 0; i 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.

9、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); h

10、eaderRow.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.

11、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; /

12、设置列宽 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);

13、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; cas

14、e “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.Deci

15、mal“:/浮点型 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.Wr

16、ite(ms); ms.Flush(); ms.Position = 0; sheet.Dispose(); /workbook.Dispose();/一般只用写这一个就 OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放 sheet return ms; / / 用于 Web导出 / / 源 DataTable / 表头文本 / 文件名 / 柳永法 http:/ 2010-5-8 22:21:41 public static void ExportByWeb(DataTable dtSource, string strHeaderText, string strFileName) H

17、ttpContext 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.Ur

18、lEncode(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 (File

19、Stream 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; f

20、or (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) != nu

21、ll) dataRowj = row.GetCell(j).ToString(); dt.Rows.Add(dataRow); return dt; 第二种方法：public void WriteToExcel(DataTable table)trystring tempImagePath = Application.StartupPath;/软件安装目录string temp = tempImagePath + “Execl“;/目录下的 Execl 文件夹Directory.CreateDirectory(temp);string strFilePath = Application.Sta

22、rtupPath + “Execl“ +名字+ “.xls“; /赋给文件的名字System.IO.StreamWriter sw = new System.IO.StreamWriter(strFilePath,true, System.Text.Encoding.Default); /写入流object values = new objecttable.Columns.Count;for (int i = 0; i table.Columns.Count; +i)if (table.Columnsi.Caption.ToString() = “列名“)table.Columnsi.Capt

23、ion = “自己给列起的名字 “;sw.Write(table.Columnsi.Caption.ToString();sw.Write(t);sw.Write(“rn“);for (int i = 0; i table.Rows.Count; i+)for (int j = 0; j values.Length; +j)sw.Write(table.Rowsij.ToString();sw.Write(t);sw.Write(“rn“);sw.Flush();sw.Close();MessageBox.Show(“成功导出“ + ds.Tables0.Rows.Count.ToString() + “行到 Execl！“);catchMessageBox.Show(“导出 Execl 失败！“);