.net 将数据库中的数据导出到Excel表格中

来源：互联网发布：python cmdb 编辑：程序博客网时间：2024/05/20 11:48

需要先在项目中引用此文件：http://pan.baidu.com/s/1ntjEFdn

代码如下：

using DocumentFormat.OpenXml.Packaging;using System;using System.Collections.Generic;using System.Data;using System.Linq;using System.Web;using System.Web.UI;using System.Web.UI.WebControls;using ZhongHong.BLL;using ZhongHong.Public.Tool;  //此处为引用文件的地方namespace ZhongHongWeb.WebSystem.Customer{    public partial class GetXueYa : System.Web.UI.Page    {        protected void Page_Load(object sender, EventArgs e)        {        }        protected void Button1_Click(object sender, EventArgs e)        {            var fileTemplatePath = Server.MapPath("~/Template/xueya.xlsx");  //导出数据的模板            var filePath = Server.MapPath(string.Format("~/TempFile/{0}.xlsx", Guid.NewGuid().ToString()));  //返回文件的物理路径            this.ExcelOut(filePath, fileTemplatePath);  //导出Excel表格            DownLoadExcelFile(filePath, "血压");  //下载文件        }        /// <summary>        /// 导出Excel        /// </summary>        /// <param name="filePath">        /// The file path.        /// </param>        /// <param name="fileTemplatePath">        /// The file template path.        /// </param>        /// <exception cref="Exception">        /// </exception>        private void ExcelOut(string filePath, string fileTemplatePath)        {            try            {                System.IO.File.Copy(fileTemplatePath, filePath);            }            catch (Exception ex)            {                throw new Exception("复制Excel文件出错" + ex.Message);            }            using (SpreadsheetDocument document = SpreadsheetDocument.Open(filePath, true))            {                var sheetData = document.GetFirstSheetData();                OpenXmlHelper.CellStyleIndex = 1;                // 字幕A,B,C......对应Excel表格中的列，常量名对应数据库中的字段。                const string userName = "A", pwd = "B", trueName = "C", phone = "D", sex = "E", age = "F", gy1 = "G", dy1 = "H", xl1 = "I", sj1 = "J", gy2 = "K", dy2 = "L", xl2 = "M", sj2 = "N", gy3 = "O", dy3 = "P", xl3 = "Q", sj3 = "R";                const int StartRowIndex = 3;  //从第3行开始填充数据。                //获取要导出的数据                DataTable dt = new ZhongHong.BLL.ExportExcelBLL().SelectPressure();                //将数据填充到Excel的单元格                for (int i = 0; i < dt.Rows.Count; i++)                {                    var rowIndex = StartRowIndex + i;                    sheetData.SetCellValue(userName + rowIndex, dt.Rows[i]["UserLoginName"].ToString());                    sheetData.SetCellValue(pwd + rowIndex, dt.Rows[i]["PassWord"].ToString());                    sheetData.SetCellValue(trueName + rowIndex, dt.Rows[i]["Name"].ToString());                    sheetData.SetCellValue(phone + rowIndex, dt.Rows[i]["TelPhone"].ToString());                    sheetData.SetCellValue(sex + rowIndex, dt.Rows[i]["sex"].ToString());                    sheetData.SetCellValue(age + rowIndex, dt.Rows[i]["age"].ToString());                    sheetData.SetCellValue(gy1 + rowIndex, dt.Rows[i]["SBP"].ToString());                    sheetData.SetCellValue(dy1 + rowIndex, dt.Rows[i]["DBP"].ToString());                    sheetData.SetCellValue(xl1 + rowIndex, dt.Rows[i]["HeartRate"].ToString());                    sheetData.SetCellValue(sj1 + rowIndex, dt.Rows[i]["CollectTime"].ToString());                                    }            }        }        /// <summary>        /// 下载文件        /// </summary>        /// <param name="filePath">        /// The file path.        /// </param>        private void DownLoadExcelFile(string filePath, string fileName)        {            Response.ContentType = "application/ms-excel";            Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", Server.UrlEncode(fileName + ".xlsx")));            Response.TransmitFile(filePath);            this.Response.Flush();            this.Response.End();        }    }}

如果下载的文件名称存在乱码可尝试下面的方法：

 /// <summary>        /// 下载文件        /// </summary>         /// <param name="filePath">        /// The file path.        /// </param>        private void DownLoadExcelFile(string filePath, HttpContext context)        {                        string paramFileName = "医嘱列表.xlsx";            HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;            paramFileName = HttpUtility.UrlEncode(paramFileName, System.Text.Encoding.UTF8);//防止中文出现乱码            HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=\"" + paramFileName + "\"");            HttpContext.Current.Response.WriteFile(filePath);        }

0 0