.net 数据导出

　　安装npoi，下面是具体的C#代码：

　　

public static XSSFWorkbook BuildWorkbook(DataTable dt)
        {
            var book = new XSSFWorkbook();
            ISheet sheet = book.CreateSheet("Sheet1");
            IRow first_drow = sheet.CreateRow(0);
             
            string import_title = ConfigurationManager.AppSettings["import_title"];
            if (!import_title.IsEmpty())
            {
                string[] temps = import_title.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
                for (int i = 0; i < temps.Length; i++)
                {
                    ICell cell = first_drow.CreateCell(i, CellType.String);
                    cell.SetCellValue(temps[i]);
                }
            }
            //Data Rows
            int index = 0;
            for (int i = 1; i <= dt.Rows.Count; i++)
            {
                IRow drow = sheet.CreateRow(i);
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    ICell cell = drow.CreateCell(j, CellType.String);
                    cell.SetCellValue(dt.Rows[index][j].ToString());
                }
                index++;
            }
            //自动列宽
            for (int i = 0; i <= dt.Columns.Count; i++)
                sheet.AutoSizeColumn(i, true);

            return book;
        }
        
 
        public static void ExportExcel(string idcard_no, string name, string fileName = "ExamInfoExcel")
        {
            //生成Excel
            IWorkbook book = BuildWorkbook(ExcelData(idcard_no, name));

            //web 下载
            if (fileName == "")
                fileName = string.Format("{0:yyyyMMddHHmmssffff}", DateTime.Now);
            fileName = fileName.Trim();
            string ext = Path.GetExtension(fileName);

            if (ext.ToLower() == ".xls" || ext.ToLower() == ".xlsx")
                fileName = fileName.Replace(ext, string.Empty);

            HttpResponse httpResponse = HttpContext.Current.Response;
            httpResponse.Clear();
            httpResponse.Buffer = true;
            httpResponse.Charset = Encoding.UTF8.BodyName;
            //Remarks:xls是03版excel所用格式，xlsx是07版所用格式，这里默认导出07版的，如果电脑上是03版打不开这个文件，把后缀名xlsx改成xls即可。
            httpResponse.AppendHeader("Content-Disposition", "attachment;filename=" + fileName + ".xlsx");
            httpResponse.ContentEncoding = Encoding.UTF8;
            httpResponse.ContentType = "application/vnd.ms-excel; charset=UTF-8";
            book.Write(httpResponse.OutputStream);
            httpResponse.End();
        }
        public static DataTable ExcelData(string idcard_no, string name)
        {
            string where = " 1=1 ";
            if (!string.IsNullOrEmpty(idcard_no))
            {
                idcard_no = idcard_no.TrimStart().TrimEnd();
                where += " and idcard_no like '%" + idcard_no + "%'";
            }
            if (!string.IsNullOrEmpty(name))
            {
                idcard_no = idcard_no.TrimStart().TrimEnd();
                name = name.TrimStart().TrimEnd();
                where += " and name like '%" + name + "%'";
            }
            string import_files = ConfigurationManager.AppSettings["import_files"];
            string sql = string.Format(@"SELECT {0} FROM ExamInfo where {1} ORDER BY id", import_files, where);
            using (var connection = ConnectionFactory.CreateSqlConnection())
            {
                DataTable dt = new DataTable();
                dt.Load(connection.ExecuteReader(sql));
                return dt;
            }
        }

　　控制器层调用如下：

　　

        public void ExcelImport(string idcard_no, string name)
        {
            Application.ExportExcel(idcard_no, name, "ExamInfoExcel");
        }

　　JQ页面调用如下：

　　

　　　　　　　$("#import").click(function () {
                var href = "/Home/ExcelImport";
                var idcard_no = $("#IDNumber").val();
                var name = $("#username").val();
                href += "?idcard_no=" + idcard_no + "&name=" + name;
                $("#import_a").attr("href", href);
                $("#download").click();
            });

　　html：

<button class="layui-btn" id="import">导出数据</button>
<a href="/Home/ExcelImport" id="import_a" style="display:none;" ><span id="download">隐藏的导出下载地址</span></a>

　　页面之所以在按钮的基础上加了一个隐藏的a标签，原因在于通过window.open打开的地址容易被浏览器拦截，而通过模拟触发a标签进行点击的则不会。

 

　　关于配置，需要导出的列以及excel的表头我控制在了webconfig里：

　　　　!--导出配置-->
　　　　<add key="import_title" value="序号,姓名"/>
　　　　<add key="import_files" value="id,name"/>

 

　　以上，是一个较为完整的导出数据到excel示例。

转载于:https://www.cnblogs.com/sunshine-wy/p/11113015.html