用NPOI把数据库内容导入到Excel中实例（NPOI版本2.0)

来源：互联网发布：慕课网java 百度云盘编辑：程序博客网时间：2024/04/30 02:20

注意 NPOI版本2.0

</pre><pre name="code" class="csharp">namespace HLS.PCS.WebSite.Pages{    public class NPOIHelper    {        #region 变量初始化        private HSSFWorkbook hssfworkbook;        private CellStyle titlestyle;        private CellStyle normalstylename;        private CellStyle headerstyle;        private CellStyle normalstylecenter;        private CellStyle normalstyleleft;        /// <summary>        /// /// 初始化        /// /// </summary>                protected void InitializeWorkbook()        {            hssfworkbook = new HSSFWorkbook();            //create a entry of DocumentSummaryInformation            DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();            dsi.Company = "NPOI Team";            hssfworkbook.DocumentSummaryInformation = dsi;            //create a entry of SummaryInformation            SummaryInformation si = PropertySetFactory.CreateSummaryInformation();            si.Subject = "NPOI SDK Example";            hssfworkbook.SummaryInformation = si;        }        /// <summary>        /// 设置字体        /// </summary>        protected void SetFont()        {            #region 字体格式            //标题            Font titlefont = hssfworkbook.CreateFont();            titlefont.FontHeight = 20 * 20;            titlefont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.BOLD;            titlestyle = hssfworkbook.CreateCellStyle();            titlestyle.Alignment = HorizontalAlignment.CENTER;            titlestyle.VerticalAlignment = VerticalAlignment.CENTER;            titlestyle.SetFont(titlefont);            //表头            Font headerfont = hssfworkbook.CreateFont();            headerfont.FontHeight = 14 * 14;            headerfont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.BOLD;            headerstyle = hssfworkbook.CreateCellStyle();            headerstyle.Alignment = HorizontalAlignment.CENTER;            headerstyle.VerticalAlignment = VerticalAlignment.CENTER;            headerstyle.BorderBottom = CellBorderType.THIN;            headerstyle.BorderLeft = CellBorderType.THIN;            headerstyle.BorderRight = CellBorderType.THIN;            headerstyle.BorderTop = CellBorderType.THIN;            headerstyle.SetFont(headerfont);            //一般            Font normalfont = hssfworkbook.CreateFont();            normalfont.FontHeight = 14 * 14;            normalstylename = hssfworkbook.CreateCellStyle();            normalstylename.Alignment = HorizontalAlignment.LEFT;            normalstylename.VerticalAlignment = VerticalAlignment.CENTER;            normalstylename.SetFont(normalfont);            normalstylecenter = hssfworkbook.CreateCellStyle();            normalstylecenter.Alignment = HorizontalAlignment.CENTER;            normalstylecenter.VerticalAlignment = VerticalAlignment.CENTER;            normalstylecenter.BorderBottom = CellBorderType.THIN;            normalstylecenter.BorderLeft = CellBorderType.THIN;            normalstylecenter.BorderRight = CellBorderType.THIN;            normalstylecenter.BorderTop = CellBorderType.THIN;            normalstylecenter.SetFont(normalfont);            normalstyleleft = hssfworkbook.CreateCellStyle();            normalstyleleft.Alignment = HorizontalAlignment.LEFT;            normalstyleleft.VerticalAlignment = VerticalAlignment.CENTER;            normalstyleleft.BorderBottom = CellBorderType.THIN;            normalstyleleft.BorderLeft = CellBorderType.THIN;            normalstyleleft.BorderRight = CellBorderType.THIN;            normalstyleleft.BorderTop = CellBorderType.THIN;            normalstyleleft.SetFont(normalfont);            #endregion        }        /// <summary>        /// 写入到excel文件        /// </summary>        /// <param name="hssfworkbook"></param>        /// <returns></returns>        protected MemoryStream WriteToStream(HSSFWorkbook hssfworkbook)        {            //Write the stream data of workbook to the root directory            MemoryStream file = new MemoryStream();            hssfworkbook.Write(file);            return file;        }        /// <summary>        /// 文件下载        /// </summary>        /// <param name="filename"></param>        protected void Download(string filename)        {            filename = HttpUtility.UrlEncode(filename, System.Text.Encoding.UTF8);            System.Web.HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";            System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", filename));            System.Web.HttpContext.Current.Response.Clear();            Open(filename);            System.Web.HttpContext.Current.Response.BinaryWrite(WriteToStream(hssfworkbook).GetBuffer());            System.Web.HttpContext.Current.Response.End();        }        /// <summary>        /// 文件打开        /// </summary>        /// <param name="FullFileName"></param>        protected void Open(string FullFileName)        {            try            {                //FileName--要下载的文件名                 FileInfo DownloadFile = new FileInfo(FullFileName);                if (DownloadFile.Exists)                {                    System.Web.HttpContext.Current.Response.Clear();                    System.Web.HttpContext.Current.Response.ClearHeaders();                    System.Web.HttpContext.Current.Response.Buffer = false;                    System.Web.HttpContext.Current.Response.ContentType = "application/octet-stream";                    System.Web.HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(DownloadFile.FullName, System.Text.Encoding.ASCII));                    System.Web.HttpContext.Current.Response.AppendHeader("Content-Length", DownloadFile.Length.ToString());                    System.Web.HttpContext.Current.Response.WriteFile(DownloadFile.FullName);                    System.Web.HttpContext.Current.Response.Flush();                    System.Web.HttpContext.Current.Response.End();                }                else                {                    //文件不存在                }            }            catch            {                //打开时异常了            }        }        #endregion        /// <summary>        /// 创建标题与列对应关系        /// </summary>        /// <returns></returns>        public Dictionary<string, string> createDic()        {            Dictionary<string, string> dic = new Dictionary<string, string>();            if (!dic.ContainsKey("RowNumber"))            {                dic.Add("RowNumber", "序号");            }            if (!dic.ContainsKey("PCMonth"))            {                dic.Add("PCMonth", "回款月份");            }            if (!dic.ContainsKey("PCCompany"))            {                dic.Add("PCCompany", "公司");            }            if (!dic.ContainsKey("ContractNumber"))            {                dic.Add("ContractNumber", "合同号");            }            if (!dic.ContainsKey("ContractName"))            {                dic.Add("ContractName", "合同名称");            }            if (!dic.ContainsKey("FirstParty"))            {                dic.Add("FirstParty", "甲方");            }            if (!dic.ContainsKey("SignDate"))            {                dic.Add("SignDate", "签订日期");            }            if (!dic.ContainsKey("SignPerson"))            {                dic.Add("SignPerson", "签订人");            }            if (!dic.ContainsKey("PCDepartment"))            {                dic.Add("PCDepartment", "部门");            }            if (!dic.ContainsKey("ResponsiblePersonId"))            {                dic.Add("ResponsiblePersonId", "回款责任人工号");            }            if (!dic.ContainsKey("ResponsiblePersonName"))            {                dic.Add("ResponsiblePersonName", "回款责任人");            }            if (!dic.ContainsKey("ContractMoney"))            {                dic.Add("ContractMoney", "合同额");            }            if (!dic.ContainsKey("Currency"))            {                dic.Add("Currency", "币别");            }            if (!dic.ContainsKey("ReceivedMoney"))            {                dic.Add("ReceivedMoney", "已收款");            }            if (!dic.ContainsKey("TransitAccount"))            {                dic.Add("TransitAccount", "未收款");            }            if (!dic.ContainsKey("PCPossibility"))            {                dic.Add("PCPossibility", "回款可能性");            }            if (!dic.ContainsKey("MonthPromisePC"))            {                dic.Add("MonthPromisePC", "本月承诺回款");            }            if (!dic.ContainsKey("NoPromiseReason"))            {                dic.Add("NoPromiseReason", "未承诺原因");            }            if (!dic.ContainsKey("Remark"))            {                dic.Add("Remark", "备注");            }                return dic;        }        /// <summary>        /// 导出到excel        /// </summary>        public void Export(DataSet ds)        {            InitializeWorkbook();            SetFont();            Sheet sheet = hssfworkbook.CreateSheet("单据信息");            sheet.DisplayGridlines = false;            #region 设置列宽            sheet.SetColumnWidth(0, 6 * 256);            sheet.SetColumnWidth(1, 24 * 256);            sheet.SetColumnWidth(2, 16 * 256);            sheet.SetColumnWidth(3, 16 * 256);            sheet.SetColumnWidth(4, 16 * 256);            sheet.SetColumnWidth(5, 16 * 256);            sheet.SetColumnWidth(6, 30 * 256);            sheet.SetColumnWidth(7, 30 * 256);            sheet.SetColumnWidth(8, 10 * 256);            sheet.SetColumnWidth(9, 16 * 256);            sheet.SetColumnWidth(10, 50 * 256);            #endregion            Row r;            Cell cell;            CellRangeAddress region;            Dictionary<string, string> dic = createDic();                    r = sheet.CreateRow(0);            r.HeightInPoints = 18;                      int itt = 0;            foreach (var item in dic.Values)            {                cell = r.CreateCell(itt);                cell.SetCellValue(item);                cell.CellStyle = headerstyle;                itt++;            }            if (ds != null && ds.Tables.Count != 0)            {                DataTable dt = ds.Tables[0];                for (int i = 0; i < dt.Rows.Count; i++)                {                    r = sheet.CreateRow(i + 1);                    r.HeightInPoints = 18;                    int j = 0;                    string typename = "";                    foreach (KeyValuePair<string, string> keyValue in dic)                    {                        cell = r.CreateCell(j); //序号                        string colname = keyValue.Key;                        if (colname == "Type")                        {                            typename = dt.Rows[i][colname].ToString();                        }                        if (typename == "补考勤单" || typename == "撤销单" || typename == "销假单")                        {                            if (colname == "HStart" || colname == "HEnd")                            {                                cell.SetCellValue(dt.Rows[i][colname].ToString().Split(' ')[0]);                            }                            else if (colname == "daydiff")                            {                                cell.SetCellValue("");                            }                            else                            {                                cell.SetCellValue(dt.Rows[i][colname].ToString());                            }                        }                        else                        {                            cell.SetCellValue(dt.Rows[i][colname].ToString());                        }                        cell.CellStyle = normalstylecenter;                        j++;                    }                }            }            string filename = "单据查询结果.xls";            Download(filename);        }    }}

0 0