.net 数据导出

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

public static XSSFWorkbook BuildWorkbook(DataTable dt)

        {

            var book = new XSSFWorkbook();

            ISheet sheet = book.CreateSheet("Sheet1");

            IRow first_drow = sheet.CreateRow();

            string import_title = ConfigurationManager.AppSettings["import_title"];

            if (!import_title.IsEmpty())

            {

                string[] temps = import_title.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);

                for (int i = ; i < temps.Length; i++)

                {

                    ICell cell = first_drow.CreateCell(i, CellType.String);

                    cell.SetCellValue(temps[i]);

                }

            }

            //Data Rows

            int index = ;

            for (int i = ; i <= dt.Rows.Count; i++)

            {

                IRow drow = sheet.CreateRow(i);

                for (int j = ; j < dt.Columns.Count; j++)

                {

                    ICell cell = drow.CreateCell(j, CellType.String);

                    cell.SetCellValue(dt.Rows[index][j].ToString());

                }

                index++;

            }

            //自动列宽

            for (int i = ; 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示例。

巴特西

.net 数据导出

最新文章

热门文章