NPOI导出Excel - 自动适应中文宽度

NPOI导出Excel - 自动适应中文宽度

时间 2014-08-07 11:05:00 博客园精华区

原文  http://www.cnblogs.com/likeli/p/3896667.html

主题 .Net

    /// <summary>
    /// 创建工作簿
    /// </summary>
    /// <param name="fileName">下载文件名</param>
    /// <param name="dt">数据源</param>
    public static void CreateSheet(string fileName, DataTable dt)
    {
      HSSFWorkbook workbook = new HSSFWorkbook();
      MemoryStream ms = new MemoryStream();

      //创建一个名称为Payment的工作表
      ISheet paymentSheet = workbook.CreateSheet("Payment");

      //数据源
      DataTable tbPayment = dt;

      //头部标题
      IRow paymentHeaderRow = paymentSheet.CreateRow(0);

      //循环添加标题
      foreach (DataColumn column in tbPayment.Columns)
        paymentHeaderRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);

      // 内容
      int paymentRowIndex = 1;

      foreach (DataRow row in tbPayment.Rows)
      {
        IRow newRow = paymentSheet.CreateRow(paymentRowIndex);

        //循环添加列的对应内容
        foreach (DataColumn column in tbPayment.Columns)
        {
          newRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
        }

        paymentRowIndex++;
      }

      //列宽自适应，只对英文和数字有效
      for (int i = 0; i <= dt.Rows.Count; i++)
      {
        paymentSheet.AutoSizeColumn(i);
      }
      //获取当前列的宽度，然后对比本列的长度，取最大值
      for (int columnNum = 0; columnNum <= dt.Rows.Count; columnNum++)
      {
        int columnWidth = paymentSheet.GetColumnWidth(columnNum) / 256;
        for (int rowNum = 1; rowNum <= paymentSheet.LastRowNum; rowNum++)
        {
          IRow currentRow;
          //当前行未被使用过
          if (paymentSheet.GetRow(rowNum) == null)
          {
            currentRow = paymentSheet.CreateRow(rowNum);
          }
          else
          {
            currentRow = paymentSheet.GetRow(rowNum);
          }

          if (currentRow.GetCell(columnNum) != null)
          {
            ICell currentCell = currentRow.GetCell(columnNum);
            int length = Encoding.Default.GetBytes(currentCell.ToString()).Length;
            if (columnWidth < length)
            {
              columnWidth = length;
            }
          }
        }
        paymentSheet.SetColumnWidth(columnNum, columnWidth * 256);
      }

      //将表内容写入流 通知浏览器下载
      workbook.Write(ms);
      System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", fileName));
      System.Web.HttpContext.Current.Response.BinaryWrite(ms.ToArray()); //进行二进制流下在

      workbook = null;
      ms.Close();
      ms.Dispose();
    }

    /// <summary>
    /// 虚拟 DataTable内容
    /// </summary>
    /// <returns></returns>
    public static DataTable CreatTable()
    {
      //创建DataTable 将数据库中没有的数据放到这个DT中
      DataTable datatable = new DataTable();
      datatable.Columns.Add("列1", typeof(string));
      datatable.Columns.Add("列2", typeof(string));
      datatable.Columns.Add("列3", typeof(string));
      //创建DatatTable 结束---------------------------

      //开始给临时datatable赋值
      for (int i = 0; i < 10; i++)
      {
        DataRow row = datatable.NewRow();
        row["列1"] = "列111111111111111111111111111111";
        row["列2"] = "列222222222222222222222222222222222222222";
        row["列3"] = "列3333333322222222222211111111111111111111111113";
        datatable.Rows.Add(row);
      }
      return datatable;
    }