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

注意 [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);

        }
    }
}