public void Bug60158()
{
try
{
FromFile(POIDataSamples.GetDocumentInstance(), "60158.docm");
POITestCase.TestPassesNow(60158);
}
catch (IndexOutOfRangeException e)
{
POITestCase.SkipTest(e);
}
}
public void ShiftWithMergedRegions_bug56454()
{
IWorkbook wb = _testDataProvider.CreateWorkbook();
ISheet sheet = wb.CreateSheet();
// populate sheet cells
for (int i = 0; i < 10; i++)
{
IRow row = sheet.CreateRow(i);
for (int j = 0; j < 10; j++)
{
ICell cell = row.CreateCell(j, CellType.String);
cell.SetCellValue(i + "x" + j);
}
}
CellRangeAddress A4_B7 = CellRangeAddress.ValueOf("A4:B7");
CellRangeAddress C4_D7 = CellRangeAddress.ValueOf("C4:D7");
sheet.AddMergedRegion(A4_B7);
sheet.AddMergedRegion(C4_D7);
Assume.That(sheet.LastRowNum > 8);
// Insert a row in the middle of both merged regions.
sheet.ShiftRows(4, sheet.LastRowNum, 1);
// all regions should still start at row 3, and elongate by 1 row
List <CellRangeAddress> expectedMergedRegions = new List <CellRangeAddress>();
CellRangeAddress A4_B8 = CellRangeAddress.ValueOf("A4:B8"); //A4:B7 should be elongated by 1 row
CellRangeAddress C4_D8 = CellRangeAddress.ValueOf("C4:D8"); //C4:B7 should be elongated by 1 row
expectedMergedRegions.Add(A4_B8);
expectedMergedRegions.Add(C4_D8);
// This test is written as expected-to-fail and should be rewritten
// as expected-to-pass when the bug is fixed.
// FIXME: remove try, catch, and testPassesNow, skipTest when test passes
try
{
Assert.AreEqual(expectedMergedRegions, sheet.MergedRegions);
POITestCase.TestPassesNow(56454);
}
catch (AssertionException e)
{
POITestCase.SkipTest(e);
}
wb.Close();
}
public void HelpfulExceptionOnOldFiles()
{
Stream excel4 = POIDataSamples.GetSpreadSheetInstance().OpenResourceAsStream("testEXCEL_4.xls");
try
{
new HSSFWorkbook(excel4);
Assert.Fail("Shouldn't be able to load an Excel 4 file");
}
catch (OldExcelFormatException e)
{
POITestCase.AssertContains(e.Message, "BIFF4");
}
excel4.Close();
Stream excel5 = POIDataSamples.GetSpreadSheetInstance().OpenResourceAsStream("testEXCEL_5.xls");
try
{
new HSSFWorkbook(excel5);
Assert.Fail("Shouldn't be able to load an Excel 5 file");
}
catch (OldExcelFormatException e)
{
POITestCase.AssertContains(e.Message, "BIFF8");
}
excel5.Close();
Stream excel95 = POIDataSamples.GetSpreadSheetInstance().OpenResourceAsStream("testEXCEL_95.xls");
try
{
new HSSFWorkbook(excel95);
Assert.Fail("Shouldn't be able to load an Excel 95 file");
}
catch (OldExcelFormatException e)
{
POITestCase.AssertContains(e.Message, "BIFF5");
}
excel95.Close();
}
public void ShiftRowsWithMergedRegionsThatDoNotContainColumnZero()
{
IWorkbook wb = _testDataProvider.CreateWorkbook();
ISheet sheet = wb.CreateSheet("test");
// populate sheet cells
for (int i = 0; i < 10; i++)
{
IRow row = sheet.CreateRow(i);
for (int j = 0; j < 12; j++)
{
ICell cell = row.CreateCell(j);
cell.SetCellValue(i + "x" + j);
}
}
CellRangeAddress A4_B7 = new CellRangeAddress(3, 6, 0, 1);
CellRangeAddress C5_D7 = new CellRangeAddress(4, 6, 2, 3);
sheet.AddMergedRegion(A4_B7);
sheet.AddMergedRegion(C5_D7);
// A4:B7 will elongate vertically
// C5:D7 will be shifted down with same size
sheet.ShiftRows(4, sheet.LastRowNum, 1);
// This test is written as expected-to-fail and should be rewritten
// as expected-to-pass when the bug is fixed.
// FIXME: remove try, catch, and testPassesNow, skipTest when test passes
try
{
Assert.AreEqual(2, sheet.NumMergedRegions);
Assert.AreEqual(CellRangeAddress.ValueOf("A4:B8"), sheet.GetMergedRegion(0));
Assert.AreEqual(CellRangeAddress.ValueOf("C5:D8"), sheet.GetMergedRegion(1));
POITestCase.TestPassesNow(56454);
}
catch (AssertionException e)
{
POITestCase.SkipTest(e);
}
wb.Close();
}
public void UseSharedStringsTable()
{
SXSSFWorkbook wb = new SXSSFWorkbook(null, 10, false, true);
SharedStringsTable sss = POITestCase.GetFieldValue <SharedStringsTable, SXSSFWorkbook>(typeof(SXSSFWorkbook), wb, typeof(SharedStringsTable), "_sharedStringSource");
Assert.IsNotNull(sss);
IRow row = wb.CreateSheet("S1").CreateRow(0);
row.CreateCell(0).SetCellValue("A");
row.CreateCell(1).SetCellValue("B");
row.CreateCell(2).SetCellValue("A");
XSSFWorkbook xssfWorkbook = SXSSFITestDataProvider.instance.WriteOutAndReadBack(wb) as XSSFWorkbook;
sss = POITestCase.GetFieldValue <SharedStringsTable, SXSSFWorkbook>(typeof(SXSSFWorkbook), wb, typeof(SharedStringsTable), "_sharedStringSource");
Assert.AreEqual(2, sss.UniqueCount);
Assert.IsTrue(wb.Dispose());
ISheet sheet1 = xssfWorkbook.GetSheetAt(0);
Assert.AreEqual("S1", sheet1.SheetName);
Assert.AreEqual(1, sheet1.PhysicalNumberOfRows);
row = sheet1.GetRow(0);
Assert.IsNotNull(row);
ICell cell = row.GetCell(0);
Assert.IsNotNull(cell);
Assert.AreEqual("A", cell.StringCellValue);
cell = row.GetCell(1);
Assert.IsNotNull(cell);
Assert.AreEqual("B", cell.StringCellValue);
cell = row.GetCell(2);
Assert.IsNotNull(cell);
Assert.AreEqual("A", cell.StringCellValue);
xssfWorkbook.Close();
wb.Close();
}
public void Bug59733()
{
IWorkbook workbook = new XSSFWorkbook();
ISheet sheet = workbook.CreateSheet();
for (int r = 0; r <= 3; r++)
{
sheet.CreateRow(r);
}
// Shift the 2nd row on top of the 0th row
sheet.ShiftRows(2, 2, -2);
/*
* The following error is thrown when shifting the 3rd row on top of the 0th row
* If the rows are not created, the error does not occur
*
* org.apache.xmlbeans.impl.values.XmlValueDisconnectedException
* at org.apache.xmlbeans.impl.values.XmlObjectBase.check_orphaned(XmlObjectBase.java:1258)
* at org.Openxmlformats.schemas.spreadsheetml.x2006.main.impl.CTRowImpl.GetR(Unknown Source)
* at org.apache.poi.xssf.usermodel.XSSFRow.GetRowNum(XSSFRow.java:363)
* at org.apache.poi.xssf.usermodel.XSSFSheet.shiftRows(XSSFSheet.java:2926)
* at org.apache.poi.xssf.usermodel.XSSFSheet.shiftRows(XSSFSheet.java:2901)
* at org.apache.poi.xssf.usermodel.TestXSSFSheetShiftRows.bug59733(TestXSSFSheetShiftRows.java:393)
*/
try
{
sheet.RemoveRow(sheet.GetRow(0));
Assert.AreEqual(1, sheet.GetRow(1).RowNum);
POITestCase.TestPassesNow(59733);
}
catch (Exception e)
{
Console.WriteLine(e.Message);
Console.WriteLine(e.StackTrace);
POITestCase.SkipTest(e);
}
workbook.Close();
}
public void Bug59858()
{
try
{
FromFile(POIDataSamples.GetSpreadSheetInstance(), "59858.xls");
POITestCase.TestPassesNow(59858);
}
catch (IOException e)
{
if (Regex.Match(e.Message, "Module offset for '.+' was never Read.").Success)
{
//e.PrintStackTrace();
// NPE when Reading module.offset in VBAMacroReader.ReadMacros (approx line 258)
POITestCase.SkipTest(e);
}
else
{
// something unexpected failed
throw e;
}
}
}
protected void AssertMacroContents(POIDataSamples samples, VBAMacroReader r)
{
Assert.IsNotNull(r);
Dictionary <String, String> contents = r.ReadMacros();
Assert.IsNotNull(contents);
Assert.IsFalse(contents.Count == 0, "Found 0 macros");
/*
* Assert.AreEqual(5, contents.Size());
*
* // Check the ones without scripts
* String[] noScripts = new String[] { "ThisWorkbook",
* "Sheet1", "Sheet2", "Sheet3" };
* foreach (String entry in noScripts) {
* Assert.IsTrue(entry, contents.ContainsKey(entry));
*
* String content = contents.Get(entry);
* assertContains(content, "Attribute VB_Exposed = True");
* assertContains(content, "Attribute VB_Customizable = True");
* assertContains(content, "Attribute VB_TemplateDerived = False");
* assertContains(content, "Attribute VB_GlobalNameSpace = False");
* assertContains(content, "Attribute VB_Exposed = True");
* }
*/
// Check the script one
POITestCase.AssertContains(contents, "Module1");
String content = contents["Module1"];
Assert.IsNotNull(content);
POITestCase.AssertContains(content, "Attribute VB_Name = \"Module1\"");
//assertContains(content, "Attribute TestMacro.VB_Description = \"This is a test macro\"");
// And the macro itself
String testMacroNoSub = expectedMacroContents[samples];
POITestCase.AssertContains(content, testMacroNoSub);
}
public void TestSharedFormulas()
{
XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("TestShiftRowSharedFormula.xlsx");
XSSFSheet sheet = wb.GetSheetAt(0) as XSSFSheet;
Assert.AreEqual("SUM(C2:C4)", getCellFormula(sheet, "C5"));
Assert.AreEqual("SUM(D2:D4)", getCellFormula(sheet, "D5"));
Assert.AreEqual("SUM(E2:E4)", getCellFormula(sheet, "E5"));
sheet.ShiftRows(3, sheet.LastRowNum, 1);
try
{
Assert.AreEqual("SUM(C2:C5)", getCellFormula(sheet, "C6"));
Assert.AreEqual("SUM(D2:D5)", getCellFormula(sheet, "D6"));
Assert.AreEqual("SUM(E2:E5)", getCellFormula(sheet, "E6"));
POITestCase.TestPassesNow(59983);
}
catch (AssertionException e)
{
POITestCase.SkipTest(e);
}
wb.Close();
}
