示例#1
0
        /// <summary>
        /// 将DataTable数据写入到Excel
        /// </summary>
        /// <param name="sheetName">工作薄名称</param>
        /// <param name="table">数据</param>
        /// <param name="rowTitle">标题数组</param>
        /// <param name="hidColIndexs"></param>
        private void DTExcel(string sheetName, DataTable table, List <ColumnValidItem> rowTitle, params int[] hidColIndexs)
        {
            var sheet1 = hssfworkbook.CreateSheet(sheetName);

            sheet1.CreateFreezePane(0, 1, 0, 1);

            foreach (int index in hidColIndexs)
            {
                sheet1.SetColumnHidden(index, true);
            }

            var sheet2 = hssfworkbook.CreateSheet("ShtDictionary");

            //隐藏ShtDictionary工作薄
            hssfworkbook.SetSheetHidden(1, true);
            //创建标题行且赋值
            this.CreateTitleRow(sheet1, rowTitle);

            var cellStyle = hssfworkbook.CreateCellStyle();

            cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.0");
            if (table != null)
            {
                //填充数据项
                for (int xcount = 0; xcount < table.Rows.Count; xcount++)
                {
                    DataRow row       = table.Rows[xcount];
                    var     hsBodyRow = sheet1.CreateRow(xcount + 1);//数据行+1(第0行是标题)

                    for (int ycBody = 0; ycBody < rowTitle.Count; ycBody++)
                    {
                        if (rowTitle[ycBody] == null)
                        {
                            continue;
                        }

                        var value = string.Empty;
                        if (table.Columns.Contains(rowTitle[ycBody].DataModelName))
                        {
                            value = row[rowTitle[ycBody].DataModelName].ToString();
                        }

                        var cell = hsBodyRow.CreateCell(ycBody);
                        if (!string.IsNullOrEmpty(value))
                        {
                            cell.SetCellValue(value);
                        }

                        if (rowTitle[ycBody].ValidType == EnumValidType.Decimal)
                        {
                            cell.CellStyle = cellStyle;
                        }
                    }
                }
            }
        }
        private void setSheet2(HSSFWorkbook workBook, ISheet sheet)
        {
            //创建表
            ISheet sheet2 = workBook.CreateSheet("岗位数据");

            //隐藏
            workBook.SetSheetHidden(1, true);
            //取数据
            using (var db = DbFactory.Open())
            {
                var builder = db.From <Model.Post.Post>().Where(w => w.PostType == ZZTXEnums.行政村防汛防台工作组.ToString());
                var rlist   = db.Select <PostViewModel>(builder);
                for (int iRowIndex = 0; iRowIndex < rlist.Count; iRowIndex++)
                {
                    sheet2.CreateRow(iRowIndex).CreateCell(0).SetCellValue(rlist[iRowIndex].PostName);
                }
            }
            //设计表名称
            IName range = workBook.CreateName();

            range.RefersToFormula = "岗位数据!$A:$A";
            range.NameName        = "PostDataName";
            //定义下拉框范围
            CellRangeAddressList regions = new CellRangeAddressList(3, 65535, 1, 1);
            //设置数据引用
            DVConstraint       constraint   = DVConstraint.CreateFormulaListConstraint("PostDataName");
            HSSFDataValidation dataValidate = new HSSFDataValidation(regions, constraint);

            sheet.AddValidationData(dataValidate);
        }
示例#3
0
        public static void SetDropDownList(this HSSFSheet sheet, string[] datas, HSSFWorkbook workbook,
                                           CellRangeAddressList addressList, string formulaName)
        {
            var    hiddenSheetName = "HiddenDataSource" + DateTime.Now.ToString("yyyyMMddHHmmss");
            ISheet CourseSheet     = workbook.CreateSheet(hiddenSheetName);

            workbook.SetSheetHidden(workbook.GetSheetIndex(hiddenSheetName), true);
            //CourseSheet.CreateRow(0).CreateCell(0).SetCellValue("");
            IRow  row  = null;
            ICell cell = null;

            for (int i = 0; i < datas.Length; i++)
            {
                row  = CourseSheet.CreateRow(i);
                cell = row.CreateCell(0);
                cell.SetCellValue(datas[i]);
            }

            IName range = workbook.CreateName();

            range.RefersToFormula = string.Format("{0}!$A$1:$A${1}", hiddenSheetName, datas.Length);
            range.NameName        = formulaName;
            DVConstraint       constraint   = DVConstraint.CreateFormulaListConstraint(formulaName);
            HSSFDataValidation dataValidate = new HSSFDataValidation(addressList, constraint);

            sheet.AddValidationData(dataValidate);
        }
        /// <summary>
        /// 设置下拉选项
        /// </summary>
        /// <param name="workBook"></param>
        /// <param name="sheet"></param>
        /// <param name="cellName"></param>
        /// <param name="cellNo"></param>
        /// <param name="list"></param>
        public void setSheet2(HSSFWorkbook workBook, ISheet sheet, string cellName, int cellNo, List <string> list)
        {
            //创建表
            ISheet sheet2 = workBook.CreateSheet(cellName);

            //隐藏
            workBook.SetSheetHidden(1, true);
            //取数据
            for (int i = 0; i < list.Count; i++)
            {
                sheet2.CreateRow(i).CreateCell(0).SetCellValue(list[i]);
            }

            //设计表名称
            IName range = workBook.CreateName();

            range.RefersToFormula = cellName + "!$A:$A";
            range.NameName        = cellName;
            //定义下拉框范围
            CellRangeAddressList regions = new CellRangeAddressList(2, 65535, cellNo, cellNo);
            //设置数据引用
            DVConstraint       constraint   = DVConstraint.CreateFormulaListConstraint(cellName);
            HSSFDataValidation dataValidate = new HSSFDataValidation(regions, constraint);

            sheet.AddValidationData(dataValidate);
        }
示例#5
0
        /// <summary>
        /// WriterExcel
        /// </summary>
        /// <param name="hssfworkbookDown"></param>
        /// <param name="sheetIndex"></param>
        /// <param name="DT"></param>
        public static void WriterExcelP(HSSFWorkbook hssfworkbookDown, int sheetIndex, DataTable DT)
        {
            try
            {
                #region 设置单元格样式
                //字体
                HSSFFont fontS9 = FontS9(hssfworkbookDown);
                //表格
                ICellStyle tableS1 = TableS1(hssfworkbookDown);
                tableS1.SetFont(fontS9);

                ICellStyle tableS2 = TableS2(hssfworkbookDown);
                tableS2.SetFont(fontS9);

                ICellStyle tableS3 = TableS3(hssfworkbookDown);
                tableS3.SetFont(fontS9);
                ICellStyle tableS4 = TableS4(hssfworkbookDown);
                tableS4.SetFont(fontS9);
                #endregion

                HSSFSheet sheet = (HSSFSheet)hssfworkbookDown.GetSheetAt(sheetIndex);
                hssfworkbookDown.SetSheetHidden(sheetIndex, false);
                hssfworkbookDown.SetActiveSheet(sheetIndex);

                int n = 1;//因为模板有表头,所以从第2行开始写
                for (int j = 0; j < DT.Rows.Count; j++)
                {
                    HSSFRow dataRow = (HSSFRow)sheet.CreateRow(j + n);
                    dataRow.CreateCell(0);
                    dataRow.Cells[0].SetCellValue(j + 1);
                    for (int i = 1; i < project_excel.Length; i++)
                    {
                        dataRow.CreateCell(i);
                        dataRow.Cells[i].SetCellValue(DT.Rows[j][project_excel[i]].ToString());
                        dataRow.Cells[i].CellStyle = tableS2;
                        if (project_excel[i].ToString() == "content")
                        {
                            dataRow.Cells[i].CellStyle = tableS3;
                        }
                    }
                    dataRow.CreateCell(project_excel.Length);
                    dataRow.Cells[project_excel.Length].SetCellValue(DT.Rows[j]["progress"].ToString() == "100" ? "已完成" : "");
                    if (DT.Rows[j]["progress"].ToString() == "100")
                    {
                        for (int i = 0; i <= project_excel.Length; i++)
                        {
                            dataRow.Cells[i].CellStyle = tableS1;
                            if (i != project_excel.Length && project_excel[i].ToString() == "content")
                            {
                                dataRow.Cells[i].CellStyle = tableS4;
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                return;
            }
        }
示例#6
0
        //public static void test1()
        //{
        //    HSSFWorkbook hssfworkbook = new HSSFWorkbook();
        //    HSSFSheet sheet1 = hssfworkbook.CreateSheet("Sheet1") as HSSFSheet;
        //    CellRangeAddressList regions = new CellRangeAddressList(0, 65535, 0, 0);
        //    DVConstraint constraint = DVConstraint.CreateExplicitListConstraint(new string[] { "itemA", "itemB", "itemC" });
        //    HSSFDataValidation dataValidate = new HSSFDataValidation(regions, constraint);
        //    sheet1.AddValidationData(dataValidate);
        //    MemoryStream ms = new MemoryStream();
        //    hssfworkbook.Write(ms);
        //    string workbookFile = @"D:\\wulei22.xls";
        //    hssfworkbook = null;
        //    FileStream fs = new FileStream(workbookFile, FileMode.Create, FileAccess.Write);
        //    byte[] data = ms.ToArray();
        //    fs.Write(data, 0, data.Length);
        //    fs.Flush();
        //    fs.Close();
        //}

        public static void setdownlist()
        {
            //创建工作簿
            HSSFWorkbook ssfworkbook = new HSSFWorkbook();
            //创建工作表(页)
            HSSFSheet sheet1 = ssfworkbook.CreateSheet("Sheet1") as HSSFSheet;
            //创建一行
            HSSFRow headerRow = (HSSFRow)sheet1.CreateRow(0);

            //设置表头
            headerRow.CreateCell(0).SetCellValue("ID");
            //设置表头的宽度
            sheet1.SetColumnWidth(0, 15 * 256);
            #region     添加显示下拉列表
            HSSFSheet sheet2 = ssfworkbook.CreateSheet("ShtDictionary") as HSSFSheet;
            ssfworkbook.SetSheetHidden(1, true);                     //隐藏
            sheet2.CreateRow(0).CreateCell(0).SetCellValue("itemA"); //列数据
            sheet2.CreateRow(1).CreateCell(0).SetCellValue("itemB");
            sheet2.CreateRow(2).CreateCell(0).SetCellValue("itemC");
            HSSFName range = ssfworkbook.CreateName() as HSSFName;//创建名称
            // range.Reference = "ShtDictionary!$A$1:$A$3";//格式
            range.NameName = "dicRange";
            #endregion
            headerRow.CreateCell(1).SetCellValue("Selected");
            sheet1.SetColumnWidth(1, 15 * 256);
            //将下拉列表添加
            CellRangeAddressList regions      = new CellRangeAddressList(1, 65535, 1, 1);
            DVConstraint         constraint   = DVConstraint.CreateFormulaListConstraint("dicRange");
            HSSFDataValidation   dataValidate = new HSSFDataValidation(regions, constraint);
            sheet1.AddValidationData(dataValidate);

            headerRow.CreateCell(2).SetCellValue("VALUE");
            sheet1.SetColumnWidth(2, 15 * 256);

            //写入数据
            //创建数据行
            HSSFRow dataRow = (HSSFRow)sheet1.CreateRow(1);
            //填充数据
            dataRow.CreateCell(0).SetCellValue("1"); //id
            dataRow.CreateCell(1).SetCellValue("");  //选择框
            dataRow.CreateCell(2).SetCellValue("值"); //选择框
            System.IO.MemoryStream ms = new System.IO.MemoryStream();
            ssfworkbook.Write(ms);
            string filename     = "Sheet1" + DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString() + DateTime.Now.Day.ToString() + DateTime.Now.Hour.ToString() + DateTime.Now.Minute.ToString() + DateTime.Now.Second.ToString() + ".xls";
            object Response     = null;
            string workbookFile = @"D:\\wulei.xls";

            FileStream fs   = new FileStream(workbookFile, FileMode.Create, FileAccess.Write);
            byte[]     data = ms.ToArray();
            fs.Write(data, 0, data.Length);
            fs.Flush();
            fs.Close();
            //Response.AddHeader("Content-Disposition", string.Format("attachment; filename=" + filename + ""));
            //Response.BinaryWrite(ms.ToArray());
            ms.Close();
            ms.Dispose();
        }
示例#7
0
        /// <summary>
        /// Excel数据列表
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="strfileName"></param>
        /// <param name="dCheck"></param>
        public int WriteWorkSheetException(DataSet ds, string strfileName, string sheetName)
        {
            try
            {
                int count = 0;
                using (FileStream fs = new FileStream(strfileName, FileMode.Open, FileAccess.Read))
                {
                    HSSFWorkbook workbook = new HSSFWorkbook(fs);
                    ISheet       sheet    = null;
                    sheet = workbook.GetSheet(sheetName);

                    //工作簿解密
                    sheet.ProtectSheet(null);

                    ICellStyle style = GetCellBorderException(workbook);
                    using (FileStream fsm = new FileStream(strfileName, FileMode.OpenOrCreate, FileAccess.ReadWrite))
                    {
                        for (int iRow = 0; iRow < ds.Tables[0].Rows.Count; iRow++)
                        {
                            int RowCount = sheet.GetRow(0).LastCellNum - 1;
                            for (int i = 0; i < RowCount; i++)
                            {
                                ICell Cell = sheet.GetRow(iRow + 1).GetCell(i);
                                if (Cell == null)
                                {
                                    Cell = sheet.GetRow(iRow + 1).CreateCell(i);
                                }
                                Cell.SetCellValue(ds.Tables[0].Rows[iRow][i].ToString());
                            }
                        }

                        for (int iRow = 0; iRow < ds.Tables[1].Rows.Count; iRow++)
                        {
                            ICell Cell = sheet.GetRow(iRow + 1).GetCell(4);
                            if (Cell == null)
                            {
                                Cell = sheet.GetRow(iRow + 1).CreateCell(4);
                            }
                            Cell.SetCellValue(ds.Tables[1].Rows[iRow][0].ToString());
                        }

                        //工作簿加密
                        sheet.ProtectSheet(EXCELPWD);

                        workbook.SetSheetHidden(1, SheetState.VeryHidden);
                        workbook.Write(fsm);
                        fsm.Close();
                    }
                }

                return(count);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
示例#8
0
        /// <summary>
        /// 引用另一个工作表的形式 得到下拉
        /// </summary>
        /// <param name="book"></param>
        /// <param name="columnIndex"></param>
        /// <param name="values"></param>
        /// <param name="sheetName"></param>
        /// <returns></returns>
        public static void CreateListConstaint(this HSSFWorkbook book, Int32 columnIndex, IEnumerable <String> values, string sheetName = "")
        {
            if (values == null)
            {
                return;
            }
            if (string.IsNullOrEmpty(sheetName))
            {
                sheetName = "_constraintSheet_";
            }
            //创建下拉数据到新表中
            ISheet sheet          = book.GetSheet(sheetName) ?? book.CreateSheet(sheetName);;
            var    firstRow       = sheet.GetRow(0);
            var    conColumnIndex = firstRow == null ? 0 : firstRow.PhysicalNumberOfCells;
            var    rowIndex       = 0;
            var    lastValue      = string.Empty;

            foreach (var value in values)
            {
                var row = sheet.GetRow(rowIndex) ?? sheet.CreateRow(rowIndex);
                row.CreateCell(conColumnIndex).SetCellValue(value);
                rowIndex++;
                lastValue = value;
            }

            //如果无可选值的话,则增加一个空选项,防止用户填写内容
            if (values.Count() == 0)
            {
                var row = sheet.GetRow(rowIndex) ?? sheet.CreateRow(rowIndex);
                row.CreateCell(conColumnIndex).SetCellValue("    ");
                rowIndex++;
            }

            //给该列所有单元格加上下拉选择
            IName range = book.CreateName();

            range.RefersToFormula = String.Format("{2}!${0}$1:${0}${1}",
                                                  (Char)('A' + conColumnIndex),
                                                  rowIndex.ToString(), sheetName);
            string rangeName = "dicRange" + columnIndex;

            range.NameName = rangeName;
            var cellRegions = new CellRangeAddressList(1, 65535, columnIndex, columnIndex);
            var constraint  = DVConstraint.CreateFormulaListConstraint(rangeName);

            book.SetSheetHidden(book.GetSheetIndex(sheet), SheetState.HIDDEN);

            //创建验证
            HSSFDataValidation valid = new HSSFDataValidation(cellRegions, constraint);

            //关联验证
            HSSFSheet v = book.GetSheetAt(0) as HSSFSheet;

            v.AddValidationData(valid);
        }
示例#9
0
        private void WriteReportWithData()
        {
            var reportdata = _data.ReportData.ToList().AsParallel();

            InitializeWorkbook();
            var sheet = _hssfworkbook.CreateSheet("request");

            _hssfworkbook.CreateSheet("hidden");

            var style = _hssfworkbook.CreateCellStyle();

            style.FillForegroundColor = HSSFColor.DarkBlue.Index;
            style.FillPattern         = FillPattern.SolidForeground;
            var font = _hssfworkbook.CreateFont();

            font.Color = HSSFColor.White.Index;
            style.SetFont(font);

            var rows = reportdata.Count() + 1;

            for (var j = 0; j < rows; j++)
            {
                var row = sheet.CreateRow(j);

                for (var i = 0; i < _data.Columns.Count; i++)
                {
                    var header = _data.Columns.ElementAt(i);
                    if (j == 0)
                    {
                        var cell = row.CreateCell(i);
                        cell.SetCellValue(header);
                        cell.CellStyle = style;
                    }
                    else
                    {
                        var dictionary = reportdata.ElementAt(j - 1);
                        row.CreateCell(i).SetCellValue(dictionary[header]);
                    }
                }
                sheet.AutoSizeColumn(j);
            }
            sheet.SetColumnHidden(0, true);
            var namedcell = _hssfworkbook.CreateName();

            namedcell.NameName = "hidden";
            var constraint  = DVConstraint.CreateFormulaListConstraint("hidden");
            var addressList = new CellRangeAddressList(1, reportdata.Count(), _data.Columns.Count,
                                                       _data.Columns.Count);
            var validation = new HSSFDataValidation(addressList, constraint);

            _hssfworkbook.SetSheetHidden(1, true);
        }
示例#10
0
        public void TestHide()
        {
            wbU.SetSheetHidden(0, true);
            Assert.IsTrue(wbU.IsSheetHidden(0));
            Assert.IsFalse(wbU.IsSheetHidden(1));
            MemoryStream out1 = new MemoryStream();

            wbU.Write(out1);
            out1.Close();
            HSSFWorkbook wb2 = new HSSFWorkbook(new MemoryStream(out1.ToArray()));

            Assert.IsTrue(wb2.IsSheetHidden(0));
            Assert.IsFalse(wb2.IsSheetHidden(1));
        }
示例#11
0
        public void SetSheetHidden(string sheetName)
        {
            int i = _workbook.GetSheetIndex(_workbook.GetSheet(sheetName));

            if (i > -1)
            {
                _workbook.SetSheetHidden(i, true);
            }
            if (i < _workbook.NumberOfSheets - 1)
            {
                _workbook.SetActiveSheet(i + 1);
            }
            else
            {
                _workbook.SetActiveSheet(i - 1);
            }
        }
示例#12
0
        public static HSSFDataValidation CreateListConstraint(this HSSFWorkbook book, int columnIndex, IEnumerable <string> values)
        {
            var    sheetName      = "_constraintSheet_";
            ISheet sheet          = book.GetSheet(sheetName) ?? book.CreateSheet(sheetName);
            var    firstRow       = sheet.GetRow(0);
            var    conColumnIndex = firstRow == null ? 0 : firstRow.PhysicalNumberOfCells;
            var    rowIndex       = 0;
            var    lastValue      = "";

            foreach (var value in values)
            {
                var row = sheet.GetRow(rowIndex) ?? sheet.CreateRow(rowIndex);
                row.CreateCell(conColumnIndex).SetCellValue(value);
                rowIndex++;
                lastValue = value;
            }
                        //如果只有一个可选值,则增加一个相同的选项
                        if (values.Count() == 1)
            {
                var row = sheet.GetRow(rowIndex) ?? sheet.CreateRow(rowIndex);
                row.CreateCell(conColumnIndex).SetCellValue(lastValue);
                rowIndex++;
            }
            IName range = book.CreateName();

            range.RefersToFormula = String.Format("{2}!${0}$1:${0}${1}",
                                                  (Char)('A' + conColumnIndex),
                                                  rowIndex.ToString(), sheetName);

            string rangeName = "dicRange" + columnIndex;

            range.NameName = rangeName;
            var cellRegions = new CellRangeAddressList(1, 65535, columnIndex, columnIndex);
            var constraint  = DVConstraint.CreateFormulaListConstraint(rangeName);

            book.SetSheetHidden(book.GetSheetIndex(sheet), true);

            return(new HSSFDataValidation(cellRegions, constraint));
        }
示例#13
0
        /// <summary>
        /// 生成模板
        /// </summary>
        /// <param name="displayName">文件名</param>
        /// <returns>生成的模版文件</returns>
        public byte[] GenerateTemplate(out string displayName)
        {
            HSSFWorkbook workbook = new HSSFWorkbook();

            InitExcelData();

            CreateDataTable();      //add by dufei
            SetTemplateDataValus(); //add by dufei

            if (!string.IsNullOrEmpty(FileDisplayName))
            {
                displayName = FileDisplayName + "_" + DateTime.Now.ToString("yyyy-MM-dd") + "_" + DateTime.Now.ToString("hh^mm^ss") + ".xls";
            }
            else
            {
                displayName = this.GetType().Name + "_" + DateTime.Now.ToString("yyyy-MM-dd") + "_" + DateTime.Now.ToString("hh^mm^ss") + ".xls";
            }

            //模板sheet页
            HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet();

            workbook.SetSheetName(0, string.IsNullOrEmpty(FileDisplayName) ? this.GetType().Name : FileDisplayName);

            HSSFRow row = (HSSFRow)sheet.CreateRow(0);

            row.HeightInPoints = 20;

            HSSFSheet enumSheet     = (HSSFSheet)workbook.CreateSheet();
            HSSFRow   enumSheetRow1 = (HSSFRow)enumSheet.CreateRow(0);

            enumSheetRow1.CreateCell(0).SetCellValue(Program._localizer?["Yes"]);
            enumSheetRow1.CreateCell(1).SetCellValue(Program._localizer?["No"]);
            enumSheetRow1.CreateCell(2).SetCellValue(this.GetType().Name); //为模板添加标记,必要时可添加版本号

            HSSFSheet dataSheet = (HSSFSheet)workbook.CreateSheet();

            #region 设置excel模板列头
            //默认灰色
            var headerStyle = GetCellStyle(workbook);
            headerStyle.IsLocked = true;

            //黄色
            var yellowStyle = GetCellStyle(workbook, BackgroudColorEnum.Yellow);
            yellowStyle.IsLocked = true;

            //红色
            var redStyle = GetCellStyle(workbook, BackgroudColorEnum.Red);
            redStyle.IsLocked = true;

            //取得所有ExcelPropety
            var propetys = this.GetType().GetFields().Where(x => x.FieldType == typeof(ExcelPropety)).ToList();

            int  _currentColunmIndex = 0;
            bool IsProtect           = false;
            for (int porpetyIndex = 0; porpetyIndex < propetys.Count(); porpetyIndex++)
            {
                ExcelPropety   excelPropety = (ExcelPropety)propetys[porpetyIndex].GetValue(this);
                ColumnDataType dateType     = excelPropety.DataType;
                if (excelPropety.ReadOnly)
                {
                    IsProtect = true;
                }

                //给必填项加星号
                string colName = excelPropety.IsNullAble ? excelPropety.ColumnName : excelPropety.ColumnName + "*";
                row.CreateCell(_currentColunmIndex).SetCellValue(colName);

                //修改列头样式
                switch (excelPropety.BackgroudColor)
                {
                case BackgroudColorEnum.Yellow:
                    row.Cells[_currentColunmIndex].CellStyle = yellowStyle;
                    break;

                case BackgroudColorEnum.Red:
                    row.Cells[_currentColunmIndex].CellStyle = redStyle;
                    break;

                default:
                    row.Cells[_currentColunmIndex].CellStyle = headerStyle;
                    break;
                }

                var dataStyle  = workbook.CreateCellStyle();
                var dataFormat = workbook.CreateDataFormat();

                if (dateType == ColumnDataType.Dynamic)
                {
                    int dynamicColCount = excelPropety.DynamicColumns.Count();
                    for (int dynamicColIndex = 0; dynamicColIndex < dynamicColCount; dynamicColIndex++)
                    {
                        var    dynamicCol     = excelPropety.DynamicColumns.ToList()[dynamicColIndex];
                        string dynamicColName = excelPropety.IsNullAble ? dynamicCol.ColumnName : dynamicCol.ColumnName + "*";
                        row.CreateCell(_currentColunmIndex).SetCellValue(dynamicColName);
                        row.Cells[_currentColunmIndex].CellStyle = headerStyle;
                        if (dynamicCol.ReadOnly)
                        {
                            IsProtect = true;
                        }
                        //设定列宽
                        if (excelPropety.CharCount > 0)
                        {
                            sheet.SetColumnWidth(_currentColunmIndex, excelPropety.CharCount * 256);
                            dataStyle.WrapText = true;
                        }
                        else
                        {
                            sheet.AutoSizeColumn(_currentColunmIndex);
                        }
                        //设置单元格样式及数据类型
                        dataStyle.IsLocked = excelPropety.ReadOnly;
                        dynamicCol.SetColumnFormat(dynamicCol.DataType, _currentColunmIndex, sheet, dataSheet, dataStyle, dataFormat);
                        _currentColunmIndex++;
                    }
                }
                else
                {
                    //设定列宽
                    if (excelPropety.CharCount > 0)
                    {
                        sheet.SetColumnWidth(_currentColunmIndex, excelPropety.CharCount * 256);
                        dataStyle.WrapText = true;
                    }
                    else
                    {
                        sheet.AutoSizeColumn(_currentColunmIndex);
                    }
                    //设置是否锁定
                    dataStyle.IsLocked = excelPropety.ReadOnly;
                    //设置单元格样式及数据类型
                    excelPropety.SetColumnFormat(dateType, _currentColunmIndex, sheet, dataSheet, dataStyle, dataFormat);
                    _currentColunmIndex++;
                }
            }
            #endregion

            #region 添加模版数据 add by dufei
            if (TemplateDataTable.Rows.Count > 0)
            {
                for (int i = 0; i < TemplateDataTable.Rows.Count; i++)
                {
                    DataRow tableRow = TemplateDataTable.Rows[i];
                    HSSFRow dataRow  = (HSSFRow)sheet.CreateRow(1 + i);
                    for (int porpetyIndex = 0; porpetyIndex < propetys.Count(); porpetyIndex++)
                    {
                        string colName = propetys[porpetyIndex].Name;
                        tableRow[colName].ToString();
                        dataRow.CreateCell(porpetyIndex).SetCellValue(tableRow[colName].ToString());
                    }
                }
            }
            #endregion

            //冻结行
            sheet.CreateFreezePane(0, 1, 0, 1);
            //锁定excel
            if (IsProtect)
            {
                sheet.ProtectSheet("password");
            }

            workbook.SetSheetHidden(1, true);
            workbook.SetSheetHidden(2, true);
            MemoryStream ms = new MemoryStream();
            workbook.Write(ms);
            return(ms.ToArray());
        }
        public void CopyForm(string source, string target, string saveas, int maxcols, SqlDataReader reader)
        {
            List <DateTime> DateSheetCollection = new List <DateTime>();
            bool            TopDeptGroupStyle   = (ConfigurationManager.AppSettings["TopDeptGroupStyle"] ?? "0") == "1";

            if (TopDeptGroupStyle)
            {
                try
                {
                    reader.GetSchemaTable().DefaultView.RowFilter = "ColumnName= 'DateSheetCollection'";
                    if (reader.GetSchemaTable().DefaultView.Count > 0 && reader["DateSheetCollection"] != null)
                    {
                        string[] datelist = reader["DateSheetCollection"].ToString().Split(new char[] { ',', ';', '|' });
                        foreach (string str in datelist)
                        {
                            DateTime dt;
                            if (DateTime.TryParse(str, out dt))
                            {
                                DateSheetCollection.Add(dt);
                            }
                        }
                    }
                }
                catch
                { }
            }

            try
            {
                WriteToLog(saveas);
                //string savefolder = ConfigurationManager.AppSettings["savefolder"].ToString();
                string uploadshipdate  = ConfigurationManager.AppSettings["uploadshipdate"].ToString();
                string ExclusiveStyles = ConfigurationManager.AppSettings["ExclusiveStyles"] ?? "0";
                //Open a copy of the template
                //OpenFile(target, "", "target");
                FileStream sourceFileStream = new FileStream(source, FileMode.Open, FileAccess.Read);
                NPOI.POIFS.FileSystem.POIFSFileSystem sourceFile = new NPOI.POIFS.FileSystem.POIFSFileSystem(sourceFileStream);
                //FileStream sourceFile = new FileStream(source, FileMode.Open, FileAccess.Read);
                HSSFWorkbook sourceBook = new HSSFWorkbook(sourceFile);

                FileStream tempFileStream = new FileStream(target, FileMode.Open, FileAccess.Read);
                NPOI.POIFS.FileSystem.POIFSFileSystem tempFile = new NPOI.POIFS.FileSystem.POIFSFileSystem(tempFileStream);
                HSSFWorkbook tempBook = new HSSFWorkbook(tempFile);

                //Migrate the generated sheets into the copy of the template
                WriteToLog("begin move");
                var sheetNum = tempBook.NumberOfSheets;
                //tempBook.CreateSheet("WholesalePrice");
                //tempBook..CopySheets((HSSFSheet)sourceBook.GetSheetAt(2), (HSSFSheet)tempBook.GetSheetAt(sheetNum), true);
                ((HSSFSheet)sourceBook.GetSheetAt(2)).CopyTo(tempBook, "WholesalePrice", true, true);
                sheetNum++;
                int tmpLogoIdx = 0;
                //HSSFSheet ordTmpSheet = (HSSFSheet)tempBook.CreateSheet("Order Template");
                //tempBook.CopySheets((HSSFSheet)sourceBook.GetSheetAt(0), (HSSFSheet)tempBook.GetSheetAt(sheetNum), true);
                ((HSSFSheet)sourceBook.GetSheetAt(0)).CopyTo(tempBook, "Order Template", true, true);
                HSSFSheet ordTmpSheet = (HSSFSheet)tempBook.GetSheetAt(sheetNum);
                sheetNum++;
                List <HSSFSheet> dtSheets = new List <HSSFSheet>();
                for (int i = 0; i < DateSheetCollection.Count; i++)
                {
                    var    logoPath = ConfigurationManager.AppSettings["LogoPath"];
                    byte[] bytes    = System.IO.File.ReadAllBytes(logoPath);
                    int    LogoIdx  = tempBook.AddPicture(bytes, PictureType.JPEG);

                    //HSSFSheet dtSheet = (HSSFSheet)tempBook.CreateSheet(string.Format("{0:ddMMMyyyy}", DateSheetCollection[i]));
                    //tempBook.CopySheets((HSSFSheet)sourceBook.GetSheetAt(0), (HSSFSheet)tempBook.GetSheetAt(sheetNum), true);
                    ((HSSFSheet)sourceBook.GetSheetAt(0)).CopyTo(tempBook, string.Format("{0:ddMMMyyyy}", DateSheetCollection[i]), true, true);
                    HSSFSheet dtSheet = (HSSFSheet)tempBook.GetSheetAt(sheetNum);

                    HSSFPatriarch    patriarch = (HSSFPatriarch)dtSheet.CreateDrawingPatriarch();
                    HSSFClientAnchor anchor    = new HSSFClientAnchor(50 * 1, 50 * 1, 500, 100, 0, 0, 1, 2);
                    HSSFPicture      pict      = (HSSFPicture)patriarch.CreatePicture(anchor, LogoIdx);

                    HSSFRow r      = (HSSFRow)dtSheet.GetRow(0);
                    var     dtcell = r.GetCell(0) == null ? (HSSFCell)r.CreateCell(0) : (HSSFCell)r.GetCell(0);
                    dtcell.SetCellValue(DateSheetCollection[i]);

                    dtSheets.Add(dtSheet);
                    sheetNum++;
                }
                //tempBook.CreateSheet("WebSKU");
                //tempBook.CopySheets((HSSFSheet)sourceBook.GetSheetAt(1), (HSSFSheet)tempBook.GetSheetAt(sheetNum), true);
                ((HSSFSheet)sourceBook.GetSheetAt(1)).CopyTo(tempBook, "WebSKU", true, true);
                sheetNum++;
                //ISheet skuSheet = tempBook.CreateSheet("CellFormats");
                //tempBook.CopySheets((HSSFSheet)sourceBook.GetSheetAt(3), (HSSFSheet)tempBook.GetSheetAt(sheetNum), true);
                ((HSSFSheet)sourceBook.GetSheetAt(3)).CopyTo(tempBook, "CellFormats", true, true);
                ISheet skuSheet = tempBook.GetSheetAt(sheetNum);
                sheetNum++;
                //HSSFSheet atpSheet = (HSSFSheet)tempBook.CreateSheet("ATPDate");
                //tempBook.CopySheets((HSSFSheet)sourceBook.GetSheetAt(4), (HSSFSheet)tempBook.GetSheetAt(sheetNum), true);
                ((HSSFSheet)sourceBook.GetSheetAt(4)).CopyTo(tempBook, "ATPDate", true, true);
                ISheet atpSheet = tempBook.GetSheetAt(sheetNum);
                sheetNum++;
                var upcSheetName = ConfigurationManager.AppSettings["CatalogUPCSheetName"];
                var lastsheet    = 4;
                int exclusiveStylesSheetIndex = 5;
                if (!string.IsNullOrEmpty(upcSheetName))
                {
                    exclusiveStylesSheetIndex = 6;
                    //tempBook.CreateSheet(upcSheetName);
                    //tempBook.CopySheets((HSSFSheet)sourceBook.GetSheetAt(5), (HSSFSheet)tempBook.GetSheetAt(sheetNum), true);
                    ((HSSFSheet)sourceBook.GetSheetAt(5)).CopyTo(tempBook, upcSheetName, true, true);
                    sheetNum++; lastsheet++;
                }

                if (!string.IsNullOrEmpty(ExclusiveStyles) && ExclusiveStyles == "1")
                {
                    //tempBook.CreateSheet("ExclusiveStyles");
                    //tempBook.CopySheets((HSSFSheet)sourceBook.GetSheetAt(exclusiveStylesSheetIndex), (HSSFSheet)tempBook.GetSheetAt(sheetNum), true);
                    ((HSSFSheet)sourceBook.GetSheetAt(exclusiveStylesSheetIndex)).CopyTo(tempBook, "ExclusiveStyles", true, true);
                    tempBook.SetSheetHidden(sheetNum, SheetState.HIDDEN);
                    sheetNum++; lastsheet++;
                }
                // Solo => Add validation and lock for cell on server side.
                PresetForProductSheet(ordTmpSheet, skuSheet);

                if (dtSheets.Count > 0)
                {
                    tempBook.SetSheetHidden(0, SheetState.HIDDEN);

                    string[] ATPLevelBackColors = (ConfigurationManager.AppSettings["ATPLevelBackColors"] ?? "").Split(new char[] { ',', '|' }, StringSplitOptions.RemoveEmptyEntries);
                    short    LockedCellBGColor  = short.Parse(ConfigurationManager.AppSettings["LockedCellBGColor"] ?? "22");
                    bool     allThinBorder      = ConfigurationManager.AppSettings["ThinBorder"] == null ? false : ConfigurationManager.AppSettings["ThinBorder"].ToString() == "1" ? true : false;
                    for (int i = 0; i < dtSheets.Count; i++)
                    {
                        HSSFSheet dtSheet = dtSheets[i];
                        Dictionary <int, ICellStyle> colors = new Dictionary <int, ICellStyle>();
                        ICellStyle lockedStyle = tempBook.CreateCellStyle();
                        lockedStyle.FillForegroundColor = LockedCellBGColor;
                        lockedStyle.FillPattern         = FillPatternType.SOLID_FOREGROUND;
                        lockedStyle.BorderBottom        = lockedStyle.BorderLeft = lockedStyle.BorderRight = lockedStyle.BorderTop = allThinBorder ? CellBorderType.THIN : CellBorderType.MEDIUM;
                        colors.Add(0, lockedStyle);
                        for (int j = 0; j < ATPLevelBackColors.Length / 2; j++)
                        {
                            ICellStyle qtyStyle = tempBook.CreateCellStyle();
                            qtyStyle.IsLocked            = false;
                            qtyStyle.FillForegroundColor = short.Parse(ATPLevelBackColors[j * 2 + 1]);
                            qtyStyle.FillPattern         = FillPatternType.SOLID_FOREGROUND;
                            qtyStyle.BorderBottom        = qtyStyle.BorderLeft = qtyStyle.BorderRight = qtyStyle.BorderTop = allThinBorder ? CellBorderType.THIN : CellBorderType.MEDIUM;
                            colors.Add(int.Parse(ATPLevelBackColors[j * 2]), qtyStyle);
                        }
                        DateTime dt;
                        if (DateTime.TryParse(dtSheet.SheetName, out dt))
                        {
                            PresetForProductSheet(dtSheet, skuSheet, dt, atpSheet, colors);
                        }
                    }
                }

                for (int i = 3; i < 10 + DateSheetCollection.Count; i++)
                {
                    if (i > 6 && i < 7 + DateSheetCollection.Count)
                    {
                        continue;
                    }
                    tempBook.SetSheetHidden(i, SheetState.HIDDEN);
                }

                //tempBook.SetSheetHidden(3, SheetState.Hidden);
                //tempBook.SetSheetHidden(4, SheetState.Hidden);
                //tempBook.SetSheetHidden(5, SheetState.Hidden);
                //tempBook.SetSheetHidden(6, SheetState.Hidden);
                //tempBook.SetSheetHidden(7, SheetState.Hidden);
                //tempBook.SetSheetHidden(8, SheetState.Hidden);
                //tempBook.SetSheetHidden(9, SheetState.Hidden);

                var sourceBookNum = sourceBook.NumberOfSheets;
                for (int i = lastsheet + 1; i < sourceBookNum; i++)
                {
                    HSSFSheet sht = (HSSFSheet)sourceBook.GetSheetAt(i);
                    for (int k = 0; k < 7; k++)
                    {
                        sht.AutoSizeColumn(k);
                    }
                    //tempBook.CreateSheet(sht.SheetName);
                    //tempBook.CopySheets(sht, (HSSFSheet)tempBook.GetSheetAt(sheetNum), true);
                    sht.CopyTo(tempBook, sht.SheetName, true, true);
                    sheetNum++;
                }

                WriteToLog("end move");

                //Insert the hard page breaks for printing
                WriteToLog("begin insert");
                InsertPageBreaks(tempBook, "Order Template");
                if (dtSheets.Count > 0)
                {
                    for (int i = 0; i < dtSheets.Count; i++)
                    {
                        HSSFSheet dtSheet = dtSheets[i];
                        InsertPageBreaks(tempBook, dtSheet.SheetName);
                    }
                }
                WriteToLog("end insert");
                //Set global variables for macro use
                WriteToLog("begin set");
                SetUploadShipDate(tempBook, uploadshipdate, maxcols, reader);
                WriteToLog("end set");
                //Set Cancel After Date
                SetCancelAfterDate(tempBook);
                FileStream orderFile = new FileStream(saveas, FileMode.OpenOrCreate, FileAccess.ReadWrite);
                tempBook.Write(orderFile);

                sourceFileStream.Close();
                tempFileStream.Close();
                orderFile.Close();
                sourceBook = null;
                tempBook   = null;
                System.GC.Collect();
                //if (File.Exists(@savefolder + "test.xls"))
                //    File.Delete(@savefolder + "test.xls");
                if (File.Exists(source))
                {
                    File.Delete(source);
                }
            }
            catch (Exception e)
            {
                string msg = e.Message;
                throw e;
            }
        }