导出多级表头表格到Excel

方法一：用NPOI定义多级表头导出：

 

引用头：

using NPOI.DDF;
using NPOI.OpenXmlFormats.Wordprocessing;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Threading;
using System.Threading.Tasks;
using System.Web;
using System.Web.UI.WebControls;

 

定义方法：

  1 public bool OutPutIMOrEXRSTExcel(DataTable dt)
  2         {
  3             bool Result = false;
  4             //在内存中生成一个Excel文件：
  5             HSSFWorkbook book = new HSSFWorkbook();
  6             ISheet sheet = book.CreateSheet("进出口退补税款统计表");
  7 
  8             sheet.DefaultRowHeight = 20 * 10;
  9 
 10             IRow row;
 11             ICell cell;
 12             int rowIndex = 0;
 13             int StartColIndex = 0;
 14             int colIndex = StartColIndex;
 15 
 16             //创建表头样式
 17             ICellStyle style = book.CreateCellStyle();
 18             style.Alignment = HorizontalAlignment.Center;
 19             style.WrapText = true;
 20             IFont font = book.CreateFont();
 21             font.FontHeightInPoints = 16;
 22             font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
 23             font.FontName = "简体中文";
 24             style.SetFont(font);//HEAD 样式
 25 
 26 
 27             #region 定义表头
 28             //合并单元格
 29             int InOrEx = sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex + 2, colIndex, colIndex));//进出口标志
 30             var cellRangeAddress1 = sheet.GetMergedRegion(InOrEx);
 31             for (int i = cellRangeAddress1.FirstRow; i <= cellRangeAddress1.LastRow; i++)
 32             {
 33                 row = sheet.CreateRow(rowIndex);
 34                 //row.GetCell(0).CellStyle = style;
 35 
 36                 cell = row.CreateCell(colIndex);
 37             }
 38             row = sheet.GetRow(rowIndex);
 39             cell = row.GetCell(colIndex);
 40             cell.SetCellValue("进出口标志");
 41 
 42             colIndex++;
 43             sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, colIndex, colIndex + 2));
 44             cell = row.CreateCell(colIndex);
 45             cell.SetCellValue("纠错项数");
 46 
 47             colIndex = colIndex + 3;
 48             sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, colIndex, colIndex + 9));
 49             cell = row.CreateCell(colIndex);
 50             cell.SetCellValue("退补税额");
 51 
 52             rowIndex++;
 53 
 54             colIndex = StartColIndex + 1;
 55             int involSup = sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex + 1, colIndex, colIndex));//涉及补税
 56             var cellRangeAddress4 = sheet.GetMergedRegion(involSup);
 57             for (int j = cellRangeAddress4.FirstRow; j <= cellRangeAddress4.LastRow; j++)
 58             {
 59                 row = sheet.CreateRow(rowIndex);
 60                 //row.GetCell(0).CellStyle = style;
 61 
 62                 cell = row.CreateCell(colIndex);
 63             }
 64             row = sheet.GetRow(rowIndex);
 65             cell = row.GetCell(colIndex);
 66             cell.SetCellValue("涉及补税");
 67 
 68             colIndex++;
 69             int involRef = sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex + 1, colIndex, colIndex));//涉及退税
 70             var cellRangeAddress5 = sheet.GetMergedRegion(involRef);
 71             for (int j = cellRangeAddress5.FirstRow; j <= cellRangeAddress5.LastRow; j++)
 72             {
 73                 cell = row.CreateCell(colIndex);
 74             }
 75             row = sheet.GetRow(rowIndex);
 76             cell = row.GetCell(colIndex);
 77             cell.SetCellValue("涉及退税");
 78 
 79             colIndex++;
 80             int NoRefSup = sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex + 1, colIndex, colIndex));//无退补税
 81             var cellRangeAddress6 = sheet.GetMergedRegion(NoRefSup);
 82             for (int j = cellRangeAddress6.FirstRow; j <= cellRangeAddress6.LastRow; j++)
 83             {
 84                 cell = row.CreateCell(colIndex);
 85             }
 86             row = sheet.GetRow(rowIndex);
 87             cell = row.GetCell(colIndex);
 88             cell.SetCellValue("无退补税");
 89 
 90 
 91             colIndex++;
 92             sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, colIndex, colIndex + 4));
 93             cell = row.CreateCell(colIndex);
 94             cell.SetCellValue("补税");
 95 
 96             colIndex = colIndex + 5;
 97             sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, colIndex, colIndex + 4));
 98             cell = row.CreateCell(colIndex);
 99             cell.SetCellValue("退税");
100 
101             //补税
102             rowIndex++;
103             row = sheet.CreateRow(rowIndex);
104             //row.GetCell(0).CellStyle = style;
105 
106             colIndex = StartColIndex + 4;
107             cell = row.CreateCell(colIndex);
108             cell.SetCellValue("关税");
109 
110             colIndex++;
111             cell = row.CreateCell(colIndex);
112             cell.SetCellValue("增值税");
113 
114             colIndex++;
115             cell = row.CreateCell(colIndex);
116             cell.SetCellValue("消费税");
117 
118             colIndex++;
119             cell = row.CreateCell(colIndex);
120             cell.SetCellValue("反倾销税");
121 
122             colIndex++;
123             cell = row.CreateCell(colIndex);
124             cell.SetCellValue("协定关税");
125 
126             //退税
127             colIndex++;
128             cell = row.CreateCell(colIndex);
129             cell.SetCellValue("关税");
130 
131             colIndex++;
132             cell = row.CreateCell(colIndex);
133             cell.SetCellValue("增值税");
134 
135             colIndex++;
136             cell = row.CreateCell(colIndex);
137             cell.SetCellValue("消费税");
138 
139             colIndex++;
140             cell = row.CreateCell(colIndex);
141             cell.SetCellValue("反倾销税");
142 
143             colIndex++;
144             cell = row.CreateCell(colIndex);
145             cell.SetCellValue("协定关税");
146             colIndex++;
147 
148             #endregion
149 
150             #region 定义表体并赋值
151             rowIndex++;
152             foreach (DataRow dr in dt.Rows)
153             {
154                 colIndex = StartColIndex;
155                 row = sheet.CreateRow(rowIndex);
156                 foreach (DataColumn dc in dt.Columns)
157                 {
158                     cell = row.CreateCell(colIndex);
159                     cell.SetCellValue(dr[colIndex].ToString());
160 
161                     colIndex++;
162                 }
163                 rowIndex++;
164             }
165             #endregion
166 
167             //Excel 输出
168             string fileName = @"ExitClassifiedCorrectionReport.xls";
169             try
170             {
171                 HttpResponse rs = System.Web.HttpContext.Current.Response;
172                 rs.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
173                 rs.AppendHeader("Content-Disposition", "attachment;filename=" + fileName);
174                 rs.ContentType = "application/ms-excel";
175                 using (MemoryStream ms = new MemoryStream())
176                 {
177                     book.Write(ms);
178                     rs.BinaryWrite(ms.ToArray());
179                     ms.Flush();
180                 }
181             }
182             catch (Exception ex)
183             {
184                 LogHelper.Write(ex);
185             }
186             return Result;
187         }

方法二：直接调用html代码导出table（适用于数据量较少的table）：

JS代码：

//导出table到Excel
function OutPutTab() {
        var html = document.getElementById("myTable").outerHTML;
        var shtml = htmlEncode(html);

        $("input[name='hHtml']").val(shtml);
        //表单提交
        document.getElementById("OutPutTab").submit();
}
//html代码编码（否则报字符错误）
function htmlEncode(value) {
    return $('<div/>').text(value).html();
}

后台导出代码：



复制代码




 /// <summary>
        /// 下载统计表数据
        /// </summary>
        /// <param name="form"></param>
        /// <returns></returns>
        [HttpPost]
        public FileResult ExportExcel(FormCollection form)
        {
            //第一种:使用FileContentResult
            string content = Request.Form["hHtml"];
            string strHtml = form["hHtml"];
            strHtml = HttpUtility.HtmlDecode(strHtml);//Html解码
            byte[] fileContents = Encoding.UTF8.GetBytes(strHtml);
            string filename = DateTime.Now.ToString("yyyyMMddHHmmss");
            return File(fileContents, "application/ms-excel", "进出口退补税额统计表" + filename + ".xls");

            //第二种:使用FileStreamResult
            var fileStream = new MemoryStream(fileContents);
            return File(fileStream, "application/ms-excel", "fileStream.xls");

            //第三种:使用FilePathResult
            //服务器上首先必须要有这个Excel文件,然会通过Server.MapPath获取路径返回.
            var fileName = Server.MapPath("~/uploads/选题信息导入模板.xls");
            return File(fileName, "application/ms-excel", "fileName.xls");

        }


 




 


遇到的问题及解决方案：


1、中文字符变成乱码：


导出的Excel中文字符变成乱码，网上查询到可能是编码格式的问题，通过查看网页源码发现是“UTF-8”的格式。所以我一直认为解码的默认格式就是“UTF-8”，


所以在转成byte[] 流时就一直用的默认的编码方式byte[] fileContents = Encoding.Default.GetBytes(strHtml); 


转码成GB2312的byte[]方式：byte[] buffer= Encoding.GetEncoding("GB2312").GetBytes(strHtml);  


或者转成字符串：string str=Encoding.GetEncoding("GB2312").GetString(buffer);


 


2、IE8下文件名丢失。后缀名丢失。


ie不支持中文文件名输出。将文件名变成英文就可以了。






        

        

            posted on 
2015-04-21 11:15 
小呀么小二郎 
阅读(2115) 
评论(2) 
编辑 
收藏 
举报

        

    










    
    

    
    

    

        

    

        

            
                
                
            
        

    

        

            
                
            
        

    

    

    





            

        

        

            

导航