分类

链接

2016 年 11 月
一 二 三 四 五 六 日
 123456
78910111213
14151617181920
21222324252627
282930  

近期文章

热门标签

新人福利，免费薅羊毛

现在位置:    首页 ＞ .NET ＞ 正文
共享办公室出租
NPOI导出EXCEL C#（winform）版
 2016-11-07 23:02 .NET 暂无评论 阅读(946)
  1. /// <summary>
  2.          /// DataTable导出到Excel文件
  3.          /// </summary>
  4.          /// <param name="dtSource">源DataTable</param>
  5.          /// <param name="strHeaderText">表头文本</param>
  6.          /// <param name="strFileName">保存位置</param>
  7.          public static void DataTableToExcel(DataTable dtSource, string strHeaderText, string strFileName)
  8.          {
  9.              using (MemoryStream ms = DataTableToExcel(dtSource, strHeaderText))
  10.              {
  11.                  using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
  12.                  {
  13.                      byte[] data = ms.ToArray();
  14.                      fs.Write(data, 0, data.Length);
  15.                      fs.Flush();
  16.                  }
  17.              }
  18.          }
  19.  
  20.  
  21.  
  22. /// <summary>
  23.          /// DataTable导出到Excel的MemoryStream
  24.          /// </summary>
  25.          /// <param name="dtSource">源DataTable</param>
  26.          /// <param name="strHeaderText">表头文本</param>
  27.          public static MemoryStream DataTableToExcel(DataTable dtSource, string strHeaderText)
  28.          {
  29.              HSSFWorkbook workbook = new HSSFWorkbook();
  30.              HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet();
  31.  
  32.              #region 右击文件 属性信息
  33.              {
  34.                  DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
  35.                  dsi.Company = "NPOI";
  36.                  workbook.DocumentSummaryInformation = dsi;
  37.  
  38.                  SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
  39.                  si.Author = "文件作者信息"; //填加xls文件作者信息
  40.                  si.ApplicationName = "创建程序信息"; //填加xls文件创建程序信息
  41.                  si.LastAuthor = "最后保存者信息"; //填加xls文件最后保存者信息
  42.                  si.Comments = "作者信息"; //填加xls文件作者信息
  43.                  si.Title = "标题信息"; //填加xls文件标题信息
  44.                  si.Subject = "主题信息";//填加文件主题信息
  45.                  si.CreateDateTime = System.DateTime.Now;
  46.                  workbook.SummaryInformation = si;
  47.              }
  48.              #endregion
  49.  
  50.              HSSFCellStyle dateStyle = (HSSFCellStyle)workbook.CreateCellStyle();
  51.              HSSFDataFormat format = (HSSFDataFormat)workbook.CreateDataFormat();
  52.              dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
  53.  
  54.              //取得列宽
  55.              int[] arrColWidth = new int[dtSource.Columns.Count];
  56.              foreach (DataColumn item in dtSource.Columns)
  57.              {
  58.                  arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
  59.              }
  60.              for (int i = 0; i < dtSource.Rows.Count; i++)
  61.              {
  62.                  for (int j = 0; j < dtSource.Columns.Count; j++)
  63.                  {
  64.                      int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
  65.                      if (intTemp > arrColWidth[j])
  66.                      {
  67.                          arrColWidth[j] = intTemp;
  68.                      }
  69.                  }
  70.              } 
  71.              int rowIndex = 0; 
  72.              foreach (DataRow row in dtSource.Rows)
  73.              {
  74.                  #region 新建表，填充表头，填充列头，样式
  75.                  if (rowIndex == 65535 || rowIndex == 0)
  76.                  {
  77.                      if (rowIndex != 0)
  78.                      {
  79.                          sheet = (HSSFSheet)workbook.CreateSheet();
  80.                      }
  81.  
  82.                      #region 表头及样式
  83.                      {
  84.                          HSSFRow headerRow = (HSSFRow)sheet.CreateRow(0);
  85.                          headerRow.HeightInPoints = 25;
  86.                          headerRow.CreateCell(0).SetCellValue(strHeaderText);
  87.  
  88.                          HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
  89.                        //  headStyle.Alignment = CellHorizontalAlignment.CENTER;
  90.                          HSSFFont font = (HSSFFont)workbook.CreateFont();
  91.                          font.FontHeightInPoints = 20;
  92.                          font.Boldweight = 700;
  93.                          headStyle.SetFont(font);
  94.                          headerRow.GetCell(0).CellStyle = headStyle;
  95.                         // sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1));
  96.                          //headerRow.Dispose();
  97.                      }
  98.                      #endregion
  99.  
  100.  
  101.                      #region 列头及样式
  102.                      {
  103.                          HSSFRow headerRow = (HSSFRow)sheet.CreateRow(1); 
  104.                          HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
  105.                          //headStyle.Alignment = CellHorizontalAlignment.CENTER;
  106.                          HSSFFont font = (HSSFFont)workbook.CreateFont();
  107.                          font.FontHeightInPoints = 10;
  108.                          font.Boldweight = 700;
  109.                          headStyle.SetFont(font); 
  110.                          foreach (DataColumn column in dtSource.Columns)
  111.                          {
  112.                              headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
  113.                              headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
  114.  
  115.                              //设置列宽
  116.                              sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256); 
  117.                          }
  118.                         // headerRow.Dispose();
  119.                      }
  120.                      #endregion
  121.  
  122.                      rowIndex = 2;
  123.                  }
  124.                  #endregion
  125.  
  126.  
  127.                  #region 填充内容
  128.                  HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex);
  129.                  foreach (DataColumn column in dtSource.Columns)
  130.                  {
  131.                      HSSFCell newCell =(HSSFCell) dataRow.CreateCell(column.Ordinal);
  132.  
  133.                      string drValue = row[column].ToString();
  134.  
  135.                      switch (column.DataType.ToString())
  136.                      {
  137.                          case "System.String"://字符串类型
  138.                              newCell.SetCellValue(drValue);
  139.                              break;
  140.                          case "System.DateTime"://日期类型
  141.                             System.DateTime dateV;
  142.                             System.DateTime.TryParse(drValue, out dateV);
  143.                              newCell.SetCellValue(dateV);
  144.  
  145.                              newCell.CellStyle = dateStyle;//格式化显示
  146.                              break;
  147.                          case "System.Boolean"://布尔型
  148.                              bool boolV = false;
  149.                              bool.TryParse(drValue, out boolV);
  150.                              newCell.SetCellValue(boolV);
  151.                              break;
  152.                          case "System.Int16"://整型
  153.                          case "System.Int32":
  154.                          case "System.Int64":
  155.                          case "System.Byte":
  156.                              int intV = 0;
  157.                              int.TryParse(drValue, out intV);
  158.                              newCell.SetCellValue(intV);
  159.                              break;
  160.                          case "System.Decimal"://浮点型
  161.                          case "System.Double":
  162.                              double doubV = 0;
  163.                              double.TryParse(drValue, out doubV);
  164.                              newCell.SetCellValue(doubV);
  165.                              break;
  166.                          case "System.DBNull"://空值处理
  167.                              newCell.SetCellValue("");
  168.                              break;
  169.                          default:
  170.                              newCell.SetCellValue("");
  171.                              break;
  172.                      }
  173.  
  174.                  }
  175.                  #endregion
  176.  
  177.                  rowIndex++;
  178.              } 
  179.              using (MemoryStream ms = new MemoryStream())
  180.              {
  181.                  workbook.Write(ms);
  182.                  ms.Flush();
  183.                  ms.Position = 0;
  184.  
  185.                  sheet.Dispose();
  186.                  //workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet
  187.                  return ms;
  188.              } 
  189.          }

============ 欢迎各位老板打赏~ ===========

关键字: , , ,
【上篇】
【下篇】

与本文相关的文章

该日志由 Bruce 于2016年11月07日发表在.NET分类下

本文版权归Bruce's Blog所有，转载引用请完整注明以下信息：
本文作者：Bruce
本文地址：NPOI导出EXCEL C#（winform）版 | Bruce's Blog

发表评论

留言无头像?