/// <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); }
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); }
/// <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; } }
//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(); }
/// <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; } }
/// <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); }
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); }
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)); }
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); } }
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)); }
/// <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; } }