将数据导出到Excel2007格式。

增加数据格式

 

        public static void TableToExcel2(DataTable table, string filename, string sheetname)
        {
               
                XSSFWorkbook workbook = new XSSFWorkbook();
                IFont font = workbook.CreateFont();
                font.FontName = "微软雅黑";
                font.FontHeightInPoints = 9;
            
                IFont fonthader = workbook.CreateFont();
                fonthader.FontName = "微软雅黑";
                fonthader.FontHeightInPoints = 9;
                fonthader.Boldweight = (short)FontBoldWeight.Bold;


                ICellStyle style1 = workbook.CreateCellStyle();
                style1.SetFont(font);

                ICellStyle stylehead = workbook.CreateCellStyle();
                stylehead.SetFont(fonthader);


                var sheet = workbook.CreateSheet(sheetname);
              

                var headerRow = sheet.CreateRow(0);

                //head
                foreach (DataColumn column in table.Columns)
                {

                    var cellhead = headerRow.CreateCell(column.Ordinal);//
                    cellhead.CellStyle = stylehead;
                    cellhead.SetCellValue(column.Caption);

                }
             

                //设置大类限制              
                ArrayList cat1 = new ArrayList();
                DataSet ds = DBHelper.Instance.ExeDataSet(" select  catname from purchase_categories where iscat=1 and parentid=0 ");
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    cat1.Add(dr["catname"]);
                }


                IDataValidationHelper dvHelper = sheet.GetDataValidationHelper();  
                NPOI.SS.Util.CellRangeAddressList regCat1 = new NPOI.SS.Util.CellRangeAddressList(1, 8000, 0, 0);
                IDataValidationConstraint constraint = dvHelper.CreateFormulaListConstraint("CHOICES");
                constraint.ExplicitListValues = (string[])cat1.ToArray(typeof(string));
                IDataValidation validation = dvHelper.CreateValidation(constraint, regCat1);             
                sheet.AddValidationData(validation);






                //小类
                regCat1 = new NPOI.SS.Util.CellRangeAddressList(1, 8000, 1, 1);
                cat1 = new ArrayList();
                ds = DBHelper.Instance.ExeDataSet(" select  catname from purchase_categories where iscat=1 and parentid!=0 ");
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    cat1.Add(dr["catname"]);
                }

                constraint = dvHelper.CreateFormulaListConstraint("CHOICES");
                constraint.ExplicitListValues = (string[])cat1.ToArray(typeof(string));
                validation = dvHelper.CreateValidation(constraint, regCat1);
                sheet.AddValidationData(validation);



                ////单位
                regCat1 = new NPOI.SS.Util.CellRangeAddressList(1, 8000, 6, 6);
                cat1 = new ArrayList();
                DataView dv = Purchase.BLL.PurchaseHelper.GetParam("unit");
                foreach (DataRowView dvr in dv)
                {
                    cat1.Add(dvr["paramname"]);
                }
                constraint = dvHelper.CreateFormulaListConstraint("CHOICES");
                constraint.ExplicitListValues = (string[])cat1.ToArray(typeof(string));
                validation = dvHelper.CreateValidation(constraint, regCat1);
                sheet.AddValidationData(validation);





                ////货币单位
                regCat1 = new NPOI.SS.Util.CellRangeAddressList(1, 65535, 8, 8);
                cat1 = new ArrayList();
                dv = Purchase.BLL.PurchaseHelper.GetParam("currency");
                foreach (DataRowView dvr in dv)
                {
                    cat1.Add(dvr["paramname"]);
                }
                constraint = dvHelper.CreateFormulaListConstraint("CHOICES");
                constraint.ExplicitListValues = (string[])cat1.ToArray(typeof(string));
                validation = dvHelper.CreateValidation(constraint, regCat1);
                sheet.AddValidationData(validation);




                //////货币单位
                regCat1 = new NPOI.SS.Util.CellRangeAddressList(1, 65535, 11, 11);
                cat1 = new ArrayList();
                dv = Purchase.BLL.PurchaseHelper.GetParam("shipmethod");
                foreach (DataRowView dvr in dv)
                {
                    cat1.Add(dvr["paramname"]);
                }
                constraint = dvHelper.CreateFormulaListConstraint("CHOICES");
                constraint.ExplicitListValues = (string[])cat1.ToArray(typeof(string));
                validation = dvHelper.CreateValidation(constraint, regCat1);
                sheet.AddValidationData(validation);






                //body
                var rowIndex = 1;
                foreach (DataRow row in table.Rows)
                {
                    var dataRow = sheet.CreateRow(rowIndex);
                    dataRow.HeightInPoints = 20;
                    foreach (DataColumn column in table.Columns)
                    {

                        ICell cell = dataRow.CreateCell(column.Ordinal);
                        cell.SetCellValue(row[column].ToString());

                        cell.CellStyle = style1;
                    }
                    rowIndex++;
                }
               
            
            FileStream sw = File.Create(HttpContext.Current.Server.MapPath(filename));
            workbook.Write(sw);
            sw.Close();

         

        }