欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

用NPOI把数据库内容导入到Excel中实例(NPOI版本2.0) 博客分类: asp.net开发  

程序员文章站 2024-03-21 21:38:58
...
注意 [url=http://dl.iteye.com/topics/download/add2e1a2-f4a9-339c-889f-acc1f6162031]NPOI版本2.0 [/url]
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);

        }
    }
}