/// <summary>
/// 复制sheet
/// </summary>
/// <param name="bjDt">sheet名集合</param>
/// <param name="modelfilename">模板附件名</param>
/// <param name="tpath">生成文件路径</param>
/// <returns></returns>
public static HSSFWorkbook SheetCopy(DataTable bjDt, string templetfilepath, string tpath)
{
FileInfo ff = new FileInfo(tpath);
if (ff.Exists)
{
ff.Delete();
}
FileStream fs = File.Create(tpath);//创建中间excel
HSSFWorkbook x1 = new HSSFWorkbook();
x1.Write(fs);
fs.Close();
FileStream fileRead = new FileStream(templetfilepath, FileMode.Open, FileAccess.Read);
HSSFWorkbook hssfworkbook = new HSSFWorkbook(fileRead);
FileStream fileSave2 = new FileStream(tpath, FileMode.Open, FileAccess.Read);
HSSFWorkbook book2 = new HSSFWorkbook(fileSave2);
HSSFWorkbook[] book = new HSSFWorkbook[2] {
book2, hssfworkbook
};
HSSFSheet CPS = hssfworkbook.GetSheet("Sheet1") as HSSFSheet;//获得模板sheet
string rsbh = bjDt.Rows[0]["name"].ToString();
CPS.CopyTo(book2, rsbh, true, true); //将模板sheet复制到目标sheet
HSSFSheet sheet = book2.GetSheet(bjDt.Rows[0]["name"].ToString()) as HSSFSheet; //获得当前sheet
for (int i = 1; i < bjDt.Rows.Count; i++)
{
sheet.CopySheet(bjDt.Rows[i]["name"].ToString(), true);//将sheet复制到同一excel的其他sheet上
}
return(book2);
}
public void TestImageCopy()
{
HSSFWorkbook srcBook = HSSFTestDataSamples.OpenSampleWorkbook("Images.xls");
HSSFWorkbook destBook = new HSSFWorkbook();
HSSFSheet sheet1 = srcBook.GetSheetAt(0) as HSSFSheet;
sheet1.CopyTo(destBook, "First Sheet", true, true);
using (MemoryStream ms = new MemoryStream())
{
destBook.Write(ms);
ms.Position = 0;
HSSFWorkbook sanityCheck = new HSSFWorkbook(ms);
//Assert that only one image got copied, because only one image was used on the first page
Assert.IsTrue(sanityCheck.GetAllPictures().Count == 1);
}
HSSFSheet sheet2 = srcBook.GetSheetAt(1) as HSSFSheet;
sheet2.CopyTo(destBook, "Second Sheet", true, true);
using (MemoryStream ms = new MemoryStream())
{
destBook.Write(ms);
ms.Position = 0;
HSSFWorkbook sanityCheck = new HSSFWorkbook(ms);
//2nd sheet copied, make sure we have two images now, because sheet 2 had one image
Assert.IsTrue(sanityCheck.GetAllPictures().Count == 2);
}
}
public void SplitSheetsXls(string fileName, string excelPrefixName)
{
HSSFWorkbook workbook = null;
using (FileStream stream = File.OpenRead(fileName))
{
workbook = new HSSFWorkbook(stream);
}
for (int i = 0; i < workbook.Count; i++)
{
HSSFSheet sheet = workbook.GetSheetAt(i) as HSSFSheet;
HSSFWorkbook copy = new HSSFWorkbook();
sheet.CopyTo(copy, sheet.SheetName, true, true);
copy.SetActiveSheet(0);
string name = excelPrefixName + sheet.SheetName + ".xlsx";
using (FileStream fs = new FileStream(name, FileMode.Create, FileAccess.ReadWrite, FileShare.ReadWrite))
{
copy.Write(fs);
fs.Close();
}
}
}
/// <summary>
/// 复制excelsheet页
/// </summary>
/// <param name="originPath"></param>
/// <param name="originSheet"></param>
/// <param name="targetPath"></param>
/// <param name="targetSheet"></param>
public static void CopySheet(string originPath, string originSheetName, string targetPath, string targetSheetName)
{
try
{
string oFilePath = HttpContext.Current.Server.MapPath("\\" + originPath); // 文件路径
string tFilePath = HttpContext.Current.Server.MapPath("\\" + targetPath); // 文件路径
HSSFWorkbook oWorkbook = new HSSFWorkbook(new FileStream(oFilePath, FileMode.Open));
HSSFWorkbook tWorkbook = new HSSFWorkbook();
if (File.Exists(tFilePath))
{
tWorkbook = new HSSFWorkbook(new FileStream(tFilePath, FileMode.Open));
//File.Delete(tFilePath);
}
//ISheet targetSheet = tWorkbook.CreateSheet(targetSheetName); // 工作表
HSSFSheet originShee = oWorkbook.GetSheet(originSheetName) as HSSFSheet;
var row = originShee.GetRow(1);
originShee.CopyTo(tWorkbook, targetSheetName, true, true);
using (FileStream fileSave = new FileStream(tFilePath, FileMode.Open, FileAccess.Write))
{
tWorkbook.Write(fileSave);
fileSave.Close();
}
}
catch (Exception ex)
{
Logger.Log.Error(ex.StackTrace + $"\n{ex.Message}");
}
}
static void Main(string[] args)
{
//Excel worksheet combine example
//You will be prompted to select two Excel files. test.xls will be created that combines the sheets
//Note: This example does not check for duplicate sheet names. Your test files should have different sheet names.
OpenFileDialog ofd = new OpenFileDialog();
ofd.Filter = "Excel document (*.xls)|*.xls";
ofd.Title = "Select first Excel document";
if (ofd.ShowDialog() == DialogResult.OK)
{
HSSFWorkbook book1 = new HSSFWorkbook(new FileStream(ofd.FileName, FileMode.Open));
ofd.Title = "Select second Excel document";
if (ofd.ShowDialog() == DialogResult.OK)
{
HSSFWorkbook book2 = new HSSFWorkbook(new FileStream(ofd.FileName, FileMode.Open));
HSSFWorkbook product = new HSSFWorkbook();
for (int i = 0; i < book1.NumberOfSheets; i++)
{
HSSFSheet sheet1 = book1.GetSheetAt(i) as HSSFSheet;
sheet1.CopyTo(product, sheet1.SheetName, true, true);
}
for (int j = 0; j < book2.NumberOfSheets; j++)
{
HSSFSheet sheet2 = book2.GetSheetAt(j) as HSSFSheet;
sheet2.CopyTo(product, sheet2.SheetName, true, true);
}
product.Write(new FileStream("test.xls", FileMode.Create, FileAccess.ReadWrite));
}
}
}
/// <summary>加入指定 EXCEL (*.xls) 檔案指定的索引 Sheet</summary>
/// <param name="buffer">檔案位元陣列</param>
/// <param name="sheetIndex">複製的工作表索引</param>
/// <param name="sheetName">工作表名稱</param>
public void Add(byte[] buffer, int sheetIndex, string sheetName)
{
using (MemoryStream _stream = new MemoryStream(buffer))
{
HSSFWorkbook _workbook = new HSSFWorkbook(_stream);
HSSFSheet _sheet = _workbook.GetSheetAt(sheetIndex) as HSSFSheet;
_sheet.CopyTo(this._workbook, sheetName, true, true);
}
}
public void TestColorStyleCopy()
{
HSSFWorkbook bookA = new HSSFWorkbook();
HSSFWorkbook bookB = new HSSFWorkbook();
bookA.Workbook.CustomPalette.ClearColors();
bookA.Workbook.CustomPalette.SetColor(0x8, 12, 15, 255); //0x8 is blueish
bookA.Workbook.CustomPalette.SetColor(0x9, 200, 200, 200); //0x9 is light gray
bookB.Workbook.CustomPalette.ClearColors();
bookB.Workbook.CustomPalette.SetColor(0x8, 192, 168, 0); //Throw a color into the destination book so we can see color merge working.
HSSFSheet sheetA = bookA.CreateSheet("Sheet A") as HSSFSheet;
ICell cell = sheetA.CreateRow(0).CreateCell(0);
cell.SetCellValue("I'm a stylish cell!");
IFont myFont = bookA.CreateFont();
myFont.FontName = "Times New Roman";
myFont.IsItalic = true;
myFont.FontHeightInPoints = 12;
myFont.Color = 0x8;
ICellStyle myStyle = bookA.CreateCellStyle();
myStyle.SetFont(myFont);
myStyle.FillForegroundColor = 0x9;
cell.CellStyle = myStyle;
HSSFSheet sheetB = bookB.CreateSheet("BookB Sheet") as HSSFSheet;
ICell beeCell = sheetB.CreateRow(0).CreateCell(0);
ICellStyle styleB = bookB.CreateCellStyle();
styleB.FillForegroundColor = 0x8;
beeCell.CellStyle = styleB;
beeCell.SetCellValue("Hello NPOI");
//Copy the sheet, and make sure the color, style, and font is correct
sheetA.CopyTo(bookB, "Copied Sheet A", true, true);
HSSFSheet theCopy = bookB.GetSheetAt(1) as HSSFSheet;
ICell copiedCell = theCopy.GetRow(0).GetCell(0);
//Check that the fill color got copied
byte[] srcColor = bookA.Workbook.CustomPalette.GetColor(0x9);
byte[] destColor = bookB.Workbook.CustomPalette.GetColor(copiedCell.CellStyle.FillForegroundColor);
Assert.IsTrue(srcColor[0] == destColor[0] && srcColor[1] == destColor[1] && srcColor[2] == destColor[2]);
//Check that the font color got copied
srcColor = bookA.Workbook.CustomPalette.GetColor(0x8);
destColor = bookB.Workbook.CustomPalette.GetColor(copiedCell.CellStyle.GetFont(bookB).Color);
Assert.IsTrue(srcColor[0] == destColor[0] && srcColor[1] == destColor[1] && srcColor[2] == destColor[2]);
//Check that the fill color of the cell originally in the destination book is still intact
srcColor = bookB.Workbook.CustomPalette.GetColor(0x8);
Assert.IsTrue(srcColor[0] == 192 && srcColor[1] == 168 && srcColor[2] == 0);
//Check that the font made it over okay
Assert.AreEqual(copiedCell.CellStyle.GetFont(bookB).FontName, myFont.FontName);
}
public void TestBasicCopyTo()
{
HSSFWorkbook bookA = new HSSFWorkbook();
HSSFWorkbook bookB = new HSSFWorkbook();
HSSFWorkbook bookC = new HSSFWorkbook();
HSSFSheet sheetA = bookA.CreateSheet("Sheet A") as HSSFSheet;
sheetA.CreateRow(0).CreateCell(0).SetCellValue("Data in the first book");
HSSFSheet sheetB = bookB.CreateSheet("Sheet B") as HSSFSheet;
sheetB.CreateRow(0).CreateCell(0).SetCellValue("Data in the second book");
//Ensure that we can copy into a book that already has a sheet, as well as one that doesn't.
sheetA.CopyTo(bookB, "Copied Sheet A", false, false);
sheetA.CopyTo(bookC, "Copied Sheet A", false, false);
//Ensure the sheet was copied to the 2nd sheet of Book B, not the 1st sheet.
Assert.AreNotEqual(sheetA.GetRow(0).GetCell(0).StringCellValue, bookB.GetSheetAt(0).GetRow(0).GetCell(0).StringCellValue);
Assert.AreEqual(sheetA.GetRow(0).GetCell(0).StringCellValue, bookB.GetSheetAt(1).GetRow(0).GetCell(0).StringCellValue);
//Ensure the sheet was copied to the 1st sheet in Book C
Assert.AreEqual(sheetA.GetRow(0).GetCell(0).StringCellValue, bookC.GetSheetAt(0).GetRow(0).GetCell(0).StringCellValue);
}
public void splitSheets()
{
// XSSFWorkbook xlsx
// HSSFWorkbook xls
HSSFSheet cs = null;
//cs.CopyTo();
string fileName = "./documents/WP03.xlsx";
HSSFWorkbook workbook;
using (FileStream stream = File.OpenRead(fileName))
{
workbook = new HSSFWorkbook(stream);
}
//XSSFSheet
HSSFSheet sheet = workbook.GetSheetAt(0) as HSSFSheet; //获取名称是“菜鸟”的表。
HSSFWorkbook copy = new HSSFWorkbook();
//ISheet copySheet = sheet.CopySheet(sheet.SheetName, true);
sheet.CopyTo(copy, "sheet0", true, true);
//List<ISheet> copyList = new List<ISheet>();
//copyList.Add(copySheet);
for (int i = sheet.FirstRowNum; i <= sheet.LastRowNum; i++)
{
for (int j = sheet.GetRow(i).FirstCellNum; j <= sheet.GetRow(i).LastCellNum; j++)
{
// value.CellStyle.IsLocked 是否被锁定
var value = sheet.GetRow(i).GetCell(j);
if (value.CellType == CellType.Formula)
{
bool isLock = value.CellStyle.IsLocked;
}
}
}
string copyFile = "./documents/万能模板" + Guid.NewGuid().ToString() + ".xls";
using (FileStream fs = new FileStream(copyFile, FileMode.Create, FileAccess.ReadWrite, FileShare.ReadWrite))
{
copy.Write(fs);
fs.Close();
}
}
public void Main()
{
//Excel worksheet combine example
//You will be prompted to select two Excel files. test.xls will be created that combines the sheets
//Note: This example does not check for duplicate sheet names. Your test files should have different sheet names.
var firstExcel = "first.xlsx";
HSSFWorkbook book1 = new HSSFWorkbook(new FileStream(firstExcel, FileMode.Open));
var secodExcel = "secod.xlsx";
HSSFWorkbook book2 = new HSSFWorkbook(new FileStream(secodExcel, FileMode.Open));
HSSFWorkbook product = new HSSFWorkbook();
for (int i = 0; i < book1.NumberOfSheets; i++)
{
HSSFSheet sheet1 = book1.GetSheetAt(i) as HSSFSheet;
sheet1.CopyTo(product, sheet1.SheetName, true, true);
}
for (int j = 0; j < book2.NumberOfSheets; j++)
{
HSSFSheet sheet2 = book2.GetSheetAt(j) as HSSFSheet;
sheet2.CopyTo(product, sheet2.SheetName, true, true);
}
product.Write(new FileStream("test.xls", FileMode.Create, FileAccess.ReadWrite));
}
/// <summary>
/// 根据模板将数据导出到Excel(多个工作簿)
/// </summary>
/// <param name="templatePath">excel模板路径(全路径)</param>
/// <param name="dicColumns">要导出的列集合(如果为空默认导出所有已配置的列,字典键：类别)</param>
/// <param name="dicDicData">要导出的数据集(字典键值：类别)</param>
/// <param name="sheetName">要导出的工作簿名称</param>
/// <param name="fileName">导出的文件名称(包括路径)</param>
/// <returns></returns>
public virtual bool ExportDataToExcelByTemplate(string templatePath, Dictionary <string, List <ExportColumn> > dicColumns, Dictionary <string, Dictionary <string, DataTable> > dicDicData, string sheetName, string fileName)
{
//创建excel模板对象
HSSFWorkbook workbook = LoadWorkbook(templatePath);
if (workbook == null)
{
return(false);
}
//获取模板工作簿
HSSFSheet sheetTemplate = (HSSFSheet)workbook.GetSheetAt(0);
foreach (KeyValuePair <string, Dictionary <string, DataTable> > itemSheet in dicDicData)
{
sheetTemplate.CopyTo(workbook, itemSheet.Key, true, true);//复制模板工作簿为新的工作簿
}
//变量
string cellTemplate = "";
object cellValue = "";
int lineCount = 0; //明细行总数
int lineHeaderRowIndex = 0; //明细行头行索引
int k = 0;
DataRow dr = null;
//多个工作簿
ISheet sheet = null;
Dictionary <string, DataTable> dicData = null;
foreach (KeyValuePair <string, Dictionary <string, DataTable> > itemSheet in dicDicData)
{
sheet = workbook.GetSheet(itemSheet.Key); //新的工作簿
dicData = itemSheet.Value; //工作簿数据字典
#region 替换模板值
cellTemplate = "";
cellValue = "";
lineCount = 0; //明细行总数
lineHeaderRowIndex = 0; //明细行头行索引
k = 0;
dr = null;
//替换模板的值
foreach (KeyValuePair <string, List <ExportColumn> > item in dicColumns)
{
#region 验证模板内容
//如果数据不存在,跳过
if (!dicData.ContainsKey(item.Key))
{
continue;
}
#endregion
#region 分类进行替换
//分类进行替换
if (item.Key.EndsWith("Line"))
{
#region 替换明细值
//变量
lineHeaderRowIndex = GetTemplateRowIndexByCellCommentString(sheet, item.Key);
if (lineHeaderRowIndex < 0)
{
lineHeaderRowIndex = GetTemplateRowIndexByCellValue(sheet, "行号");
}
if (lineHeaderRowIndex < 0)
{
continue;
}
lineCount = dicData[item.Key].Rows.Count;
//创建明细行模板
CreateLineTemplate(sheet, lineHeaderRowIndex + 2, lineCount);
//替换明细行的值
k = 0;
dr = null;
for (int i = lineHeaderRowIndex + 1; i < lineHeaderRowIndex + lineCount + 1; i++)
{
dr = dicData[item.Key].Rows[k];
//替换相应的值
foreach (ExportColumn col in dicColumns[item.Key])
{
cellTemplate = "{$" + col.ColumnName + "}";
cellValue = dr[col.ColumnName];
ReplaceCellValueByTemplateStr(sheet, cellTemplate, cellValue, col.DataType, i, i);
}
k++;
}
#endregion
}
else
{
#region 替换非明细值
//替换非明细值
foreach (ExportColumn col in item.Value)
{
cellTemplate = "{$" + col.ColumnName + "}";
cellValue = GetTemplateStrValue(dicData[item.Key], col.ColumnName);
ReplaceCellValueByTemplateStr(sheet, cellTemplate, cellValue, col.DataType, sheet.FirstRowNum, sheet.LastRowNum);
}//end foreach (ExportColumn col in item.Value)
#endregion
}
#endregion
}
#endregion
}
workbook.RemoveSheetAt(0);//删除模板工作簿
#region 生成Excel文件
using (FileStream file = new FileStream(fileName, FileMode.Create))
{
workbook.Write(file);
file.Close();
}
#endregion
return(true);
}
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;
}
}