public static void SavePoExcel(List<po> poList, List<List<PoItemContentAndState>> poitemsListList)
{
if (poList.Count() != poitemsListList.Count())
{
MessageBox.Show("Internal Error. Please send the log file to the Author");
Logger.Error(poList.Count() + "," + poitemsListList.Count());
return;
}
FileStream file;
try
{
file = new FileStream(@"PoTemplate.dll", FileMode.Open, FileAccess.Read);
}
catch (Exception)
{
MessageBox.Show("Please check the PoTemplate.dll.");
return;
}
hssfworkbook = new HSSFWorkbook(file);
WriteDsiInfo();
for (int i = 0; i < poList.Count(); i++)
{
ISheet sheet = hssfworkbook.CloneSheet(0);
FillThePoSheet(sheet, poList[i], poitemsListList[i]);
hssfworkbook.SetSheetName(3 + i, "PO" + i.ToString());
}
hssfworkbook.RemoveSheetAt(0);
hssfworkbook.RemoveSheetAt(0);
hssfworkbook.RemoveSheetAt(0);
WriteToFile();
}
public void TestCloneSheetBasic()
{
HSSFWorkbook b = new HSSFWorkbook();
ISheet s = b.CreateSheet("Test");
s.AddMergedRegion(new CellRangeAddress(0, 1, 0, 1));
ISheet clonedSheet = b.CloneSheet(0);
Assert.AreEqual(1, clonedSheet.NumMergedRegions, "One merged area");
}
public void TestPageBreakClones()
{
HSSFWorkbook b = new HSSFWorkbook();
ISheet s = b.CreateSheet("Test");
s.SetRowBreak(3);
s.SetColumnBreak((short)6);
ISheet clone = b.CloneSheet(0);
Assert.IsTrue(clone.IsRowBroken(3), "Row 3 not broken");
Assert.IsTrue(clone.IsColumnBroken((short)6), "Column 6 not broken");
s.RemoveRowBreak(3);
Assert.IsTrue(clone.IsRowBroken(3), "Row 3 still should be broken");
}
public void TestSheetClone()
{
// First up, try a simple file
HSSFWorkbook b = new HSSFWorkbook();
Assert.AreEqual(0, b.NumberOfSheets);
b.CreateSheet("Sheet One");
b.CreateSheet("Sheet Two");
Assert.AreEqual(2, b.NumberOfSheets);
b.CloneSheet(0);
Assert.AreEqual(3, b.NumberOfSheets);
// Now try a problem one with drawing records in it
b = OpenSample("SheetWithDrawing.xls");
Assert.AreEqual(1, b.NumberOfSheets);
b.CloneSheet(0);
Assert.AreEqual(2, b.NumberOfSheets);
}
[Ignore] //this test was not found in poi 3.8beta4
public void Test22720()
{
HSSFWorkbook workBook = new HSSFWorkbook();
workBook.CreateSheet("TEST");
ISheet template = workBook.GetSheetAt(0);
template.AddMergedRegion(new CellRangeAddress(0, 1, 0, 2));
template.AddMergedRegion(new CellRangeAddress(1, 2, 0, 2));
ISheet clone = workBook.CloneSheet(0);
int originalMerged = template.NumMergedRegions;
Assert.AreEqual(2, originalMerged, "2 merged regions");
// Remove merged regions from clone
for (int i = template.NumMergedRegions - 1; i >= 0; i--)
{
clone.RemoveMergedRegion(i);
}
Assert.AreEqual(originalMerged, template.NumMergedRegions, "Original Sheet's Merged Regions were Removed");
// Check if template's merged regions are OK
if (template.NumMergedRegions > 0)
{
// fetch the first merged region...EXCEPTION OCCURS HERE
template.GetMergedRegion(0);
}
//make sure we dont exception
}
[Ignore] ///not found in poi 3.8beat4
public void Test18800()
{
HSSFWorkbook book = new HSSFWorkbook();
book.CreateSheet("TEST");
ISheet sheet = book.CloneSheet(0);
book.SetSheetName(1, "CLONE");
sheet.CreateRow(0).CreateCell(0).SetCellValue(new HSSFRichTextString("Test"));
book = WriteOutAndReadBack(book);
sheet = book.GetSheet("CLONE");
IRow row = sheet.GetRow(0);
ICell cell = row.GetCell(0);
Assert.AreEqual("Test", cell.RichStringCellValue.String);
}
public void TestClone()
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)wb.CreateSheet();
String formula = "7";
HSSFSheetConditionalFormatting sheetCF = (HSSFSheetConditionalFormatting)sheet.SheetConditionalFormatting;
HSSFConditionalFormattingRule rule1 = (HSSFConditionalFormattingRule)sheetCF.CreateConditionalFormattingRule(formula);
HSSFFontFormatting fontFmt = (HSSFFontFormatting)rule1.CreateFontFormatting();
fontFmt.SetFontStyle(true, false);
HSSFPatternFormatting patternFmt = (HSSFPatternFormatting)rule1.CreatePatternFormatting();
patternFmt.FillBackgroundColor = (HSSFColor.Yellow.Index);
HSSFConditionalFormattingRule rule2 = (HSSFConditionalFormattingRule)sheetCF.CreateConditionalFormattingRule(ComparisonOperator.Between, "1", "2");
HSSFConditionalFormattingRule[] cfRules =
{
rule1, rule2
};
short col = 1;
CellRangeAddress[] regions = {
new CellRangeAddress(0, 65535, col, col)
};
sheetCF.AddConditionalFormatting(regions, cfRules);
try
{
wb.CloneSheet(0);
}
catch (Exception e)
{
if (e.Message.IndexOf("needs to define a clone method") > 0)
{
throw new AssertionException("Indentified bug 45682");
}
throw e;
}
Assert.AreEqual(2, wb.NumberOfSheets);
}
public void Test52272()
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sh = wb.CreateSheet() as HSSFSheet;
HSSFPatriarch p = sh.CreateDrawingPatriarch() as HSSFPatriarch;
HSSFSimpleShape s = p.CreateSimpleShape(new HSSFClientAnchor());
s.ShapeType = (HSSFSimpleShape.OBJECT_TYPE_LINE);
HSSFSheet sh2 = wb.CloneSheet(0) as HSSFSheet;
Assert.IsNotNull(sh2.DrawingPatriarch);
}
public void TestCloneComment()
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sh = wb.CreateSheet() as HSSFSheet;
HSSFPatriarch p = sh.CreateDrawingPatriarch() as HSSFPatriarch;
HSSFComment c = p.CreateComment(new HSSFClientAnchor(0, 0, 100, 100, (short)0, 0, (short)5, 5));
c.Column=(1);
c.Row=(2);
c.String=(new HSSFRichTextString("qwertyuio"));
HSSFSheet sh2 = wb.CloneSheet(0) as HSSFSheet;
HSSFPatriarch p2 = sh2.DrawingPatriarch as HSSFPatriarch;
HSSFComment c2 = (HSSFComment)p2.Children[0];
Assert.AreEqual(c.String, c2.String);
Assert.AreEqual(c.Row, c2.Row);
Assert.AreEqual(c.Column, c2.Column);
// The ShapeId is not equal?
// assertEquals(c.getNoteRecord().getShapeId(), c2.getNoteRecord().getShapeId());
Assert.IsTrue(Arrays.Equals(c2.GetTextObjectRecord().Serialize(), c.GetTextObjectRecord().Serialize()));
// ShapeId is different
CommonObjectDataSubRecord subRecord = (CommonObjectDataSubRecord)c2.GetObjRecord().SubRecords[0];
subRecord.ObjectId = (1025);
Assert.IsTrue(Arrays.Equals(c2.GetObjRecord().Serialize(), c.GetObjRecord().Serialize()));
// ShapeId is different
c2.NoteRecord.ShapeId = (1025);
Assert.IsTrue(Arrays.Equals(c2.NoteRecord.Serialize(), c.NoteRecord.Serialize()));
//everything except spRecord.shapeId must be the same
Assert.IsFalse(Arrays.Equals(c2.GetEscherContainer().Serialize(), c.GetEscherContainer().Serialize()));
EscherSpRecord sp = (EscherSpRecord)c2.GetEscherContainer().GetChild(0);
sp.ShapeId=(1025);
Assert.IsTrue(Arrays.Equals(c2.GetEscherContainer().Serialize(), c.GetEscherContainer().Serialize()));
wb.Close();
}
public new void Test35084()
{
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.ISheet s = wb.CreateSheet("Sheet1");
IRow r = s.CreateRow(0);
r.CreateCell(0).SetCellValue(1);
r.CreateCell(1).CellFormula = ("A1*2");
NPOI.SS.UserModel.ISheet s1 = wb.CloneSheet(0);
r = s1.GetRow(0);
Assert.AreEqual(r.GetCell(0).NumericCellValue, 1, 1); // sanity Check
Assert.IsNotNull(r.GetCell(1));
Assert.AreEqual(r.GetCell(1).CellFormula, "A1*2");
}
public void TestCloneWithProtect()
{
String passwordA = "secrect";
int expectedHashA = -6810;
String passwordB = "admin";
int expectedHashB = -14556;
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet hssfSheet = (HSSFSheet)workbook.CreateSheet();
Assert.IsFalse(hssfSheet.ObjectProtect);
hssfSheet.ProtectSheet(passwordA);
Assert.IsTrue(hssfSheet.ObjectProtect);
Assert.AreEqual(expectedHashA, hssfSheet.Password);
Assert.AreEqual(expectedHashA, hssfSheet.Sheet.ProtectionBlock.PasswordHash);
// Clone the sheet, and make sure the password hash is preserved
HSSFSheet sheet2 = (HSSFSheet)workbook.CloneSheet(0);
Assert.IsTrue(hssfSheet.ObjectProtect);
Assert.AreEqual(expectedHashA, sheet2.Sheet.ProtectionBlock.PasswordHash);
// change the password on the first sheet
hssfSheet.ProtectSheet(passwordB);
Assert.IsTrue(hssfSheet.ObjectProtect);
Assert.AreEqual(expectedHashB, hssfSheet.Sheet.ProtectionBlock.PasswordHash);
Assert.AreEqual(expectedHashB, hssfSheet.Password);
// but the cloned sheet's password should remain unchanged
Assert.AreEqual(expectedHashA, sheet2.Password);
}
