public void TestReadComments()
{
IWorkbook wb = _testDataProvider.OpenSampleWorkbook("SimpleWithComments." + _testDataProvider.StandardFileNameExtension);
ISheet sheet = wb.GetSheetAt(0);
ICell cell;
IRow row;
IComment comment;
for (int rownum = 0; rownum < 3; rownum++)
{
row = sheet.GetRow(rownum);
cell = row.GetCell(0);
comment = cell.CellComment;
Assert.IsNull(comment, "Cells in the first column are not commented");
Assert.IsNull(sheet.GetCellComment(rownum, 0));
}
for (int rownum = 0; rownum < 3; rownum++)
{
row = sheet.GetRow(rownum);
cell = row.GetCell(1);
comment = cell.CellComment;
Assert.IsNotNull(comment, "Cells in the second column have comments");
Assert.IsNotNull(sheet.GetCellComment(rownum, 1), "Cells in the second column have comments");
Assert.AreEqual("Yegor Kozlov", comment.Author);
Assert.IsFalse(comment.String.String == string.Empty, "cells in the second column have not empyy notes");
Assert.AreEqual(rownum, comment.Row);
Assert.AreEqual(cell.ColumnIndex, comment.Column);
}
}
protected void ChangeSheetNameWithSharedFormulas(String sampleFile)
{
IWorkbook wb = _testDataProvider.OpenSampleWorkbook(sampleFile);
IFormulaEvaluator Evaluator = wb.GetCreationHelper(/*getter*/).CreateFormulaEvaluator();
ISheet sheet = wb.GetSheetAt(0);
for (int rownum = 1; rownum <= 40; rownum++)
{
ICell cellA = sheet.GetRow(1).GetCell(0);
ICell cellB = sheet.GetRow(1).GetCell(1);
Assert.AreEqual(cellB.StringCellValue, Evaluator.Evaluate(cellA).StringValue);
}
wb.SetSheetName(0, "Renamed by POI");
Evaluator.ClearAllCachedResultValues();
for (int rownum = 1; rownum <= 40; rownum++)
{
ICell cellA = sheet.GetRow(1).GetCell(0);
ICell cellB = sheet.GetRow(1).GetCell(1);
Assert.AreEqual(cellB.StringCellValue, Evaluator.Evaluate(cellA).StringValue);
}
wb.Close();
}
public void BaseTestSharedFormulas(string sampleFile)
{
IWorkbook wb = _testDataProvider.OpenSampleWorkbook(sampleFile);
ISheet sheet = wb.GetSheetAt(0);
IFormulaEvaluator Evaluator = wb.GetCreationHelper().CreateFormulaEvaluator();
ICell cell;
cell = sheet.GetRow(1).GetCell(0);
Assert.AreEqual("B2", cell.CellFormula);
Assert.AreEqual("ProductionOrderConfirmation", Evaluator.Evaluate(cell).StringValue);
cell = sheet.GetRow(2).GetCell(0);
Assert.AreEqual("B3", cell.CellFormula);
Assert.AreEqual("RequiredAcceptanceDate", Evaluator.Evaluate(cell).StringValue);
cell = sheet.GetRow(3).GetCell(0);
Assert.AreEqual("B4", cell.CellFormula);
Assert.AreEqual("Header", Evaluator.Evaluate(cell).StringValue);
cell = sheet.GetRow(4).GetCell(0);
Assert.AreEqual("B5", cell.CellFormula);
Assert.AreEqual("UniqueDocumentNumberID", Evaluator.Evaluate(cell).StringValue);
}
public void TestGetRepeatingRowsAndColumns()
{
IWorkbook wb = _testDataProvider.OpenSampleWorkbook(
"RepeatingRowsCols."
+ _testDataProvider.StandardFileNameExtension);
CheckRepeatingRowsAndColumns(wb.GetSheetAt(0), null, null);
CheckRepeatingRowsAndColumns(wb.GetSheetAt(1), "1:1", null);
CheckRepeatingRowsAndColumns(wb.GetSheetAt(2), null, "A:A");
CheckRepeatingRowsAndColumns(wb.GetSheetAt(3), "2:3", "A:B");
}
public void TestBorderStyle()
{
String ext = _testDataProvider.StandardFileNameExtension;
IWorkbook wb = _testDataProvider.OpenSampleWorkbook("59264." + ext);
ISheet sh = wb.GetSheetAt(0);
AssertBorderStyleEquals(BorderStyle.None, GetDiagonalCell(sh, 0));
AssertBorderStyleEquals(BorderStyle.Thin, GetDiagonalCell(sh, 1));
AssertBorderStyleEquals(BorderStyle.Medium, GetDiagonalCell(sh, 2));
AssertBorderStyleEquals(BorderStyle.Dashed, GetDiagonalCell(sh, 3));
AssertBorderStyleEquals(BorderStyle.Dotted, GetDiagonalCell(sh, 4));
AssertBorderStyleEquals(BorderStyle.Thick, GetDiagonalCell(sh, 5));
AssertBorderStyleEquals(BorderStyle.Double, GetDiagonalCell(sh, 6));
AssertBorderStyleEquals(BorderStyle.Hair, GetDiagonalCell(sh, 7));
AssertBorderStyleEquals(BorderStyle.MediumDashed, GetDiagonalCell(sh, 8));
AssertBorderStyleEquals(BorderStyle.DashDot, GetDiagonalCell(sh, 9));
AssertBorderStyleEquals(BorderStyle.MediumDashDot, GetDiagonalCell(sh, 10));
AssertBorderStyleEquals(BorderStyle.DashDotDot, GetDiagonalCell(sh, 11));
AssertBorderStyleEquals(BorderStyle.MediumDashDotDot, GetDiagonalCell(sh, 12));
AssertBorderStyleEquals(BorderStyle.SlantedDashDot, GetDiagonalCell(sh, 13));
wb.Close();
}
public void TestShiftRows()
{
// Read Initial file in
String sampleName = "SimpleMultiCell." + _testDataProvider.StandardFileNameExtension;
IWorkbook wb = _testDataProvider.OpenSampleWorkbook(sampleName);
ISheet s = wb.GetSheetAt(0);
// Shift the second row down 1 and write to temp file
s.ShiftRows(1, 1, 1);
{
Console.WriteLine("Shift the second row down 1");
var msg = string.Format("1a {0}-{1}-{2}-{3}-{4}-{5}", GetRowValue(s, 0), GetRowValue(s, 1), GetRowValue(s, 2), GetRowValue(s, 3), GetRowValue(s, 4), GetRowValue(s, 5));
Console.WriteLine(msg);
}
wb = _testDataProvider.WriteOutAndReadBack(wb);
// Read from temp file and check the number of cells in each
// row (in original file each row was unique)
s = wb.GetSheetAt(0);
{
var msg = string.Format("1b {0}-{1}-{2}-{3}-{4}-{5}", GetRowValue(s, 0), GetRowValue(s, 1), GetRowValue(s, 2), GetRowValue(s, 3), GetRowValue(s, 4), GetRowValue(s, 5));
Console.WriteLine(msg);
}
Assert.AreEqual(s.GetRow(0).PhysicalNumberOfCells, 1);
ConfirmEmptyRow(s, 1);
Assert.AreEqual(s.GetRow(2).PhysicalNumberOfCells, 2);
Assert.AreEqual(s.GetRow(3).PhysicalNumberOfCells, 4);
Assert.AreEqual(s.GetRow(4).PhysicalNumberOfCells, 5);
// Shift rows 1-3 down 3 in the current one. This Tests when
// 1 row is blank. Write to a another temp file
s.ShiftRows(0, 2, 3);
{
Console.WriteLine("Shift rows 1-3 down 3 in the current one");
var msg = string.Format("2a {0}-{1}-{2}-{3}-{4}-{5}", GetRowValue(s, 0), GetRowValue(s, 1), GetRowValue(s, 2), GetRowValue(s, 3), GetRowValue(s, 4), GetRowValue(s, 5));
Console.WriteLine(msg);
}
wb = _testDataProvider.WriteOutAndReadBack(wb);
// Read and ensure things are where they should be
s = wb.GetSheetAt(0);
{
var msg = string.Format("2b {0}-{1}-{2}-{3}-{4}-{5}", GetRowValue(s, 0), GetRowValue(s, 1), GetRowValue(s, 2), GetRowValue(s, 3), GetRowValue(s, 4), GetRowValue(s, 5));
Console.WriteLine(msg);
}
ConfirmEmptyRow(s, 0);
ConfirmEmptyRow(s, 1);
ConfirmEmptyRow(s, 2);
Assert.AreEqual(s.GetRow(3).PhysicalNumberOfCells, 1);
ConfirmEmptyRow(s, 4);
Assert.AreEqual(s.GetRow(5).PhysicalNumberOfCells, 2);
// Read the first file again
wb = _testDataProvider.OpenSampleWorkbook(sampleName);
s = wb.GetSheetAt(0);
// Shift rows 3 and 4 up and write to temp file
s.ShiftRows(2, 3, -2);
{
Console.WriteLine("Shift rows 3 and 4 up");
var msg = string.Format("3a {0}-{1}-{2}-{3}-{4}-{5}", GetRowValue(s, 0), GetRowValue(s, 1), GetRowValue(s, 2), GetRowValue(s, 3), GetRowValue(s, 4), GetRowValue(s, 5));
Console.WriteLine(msg);
}
wb = _testDataProvider.WriteOutAndReadBack(wb);
s = wb.GetSheetAt(0);
{
var msg = string.Format("3b {0}-{1}-{2}-{3}-{4}-{5}", GetRowValue(s, 0), GetRowValue(s, 1), GetRowValue(s, 2), GetRowValue(s, 3), GetRowValue(s, 4), GetRowValue(s, 5));
Console.WriteLine(msg);
}
Assert.AreEqual(s.GetRow(0).PhysicalNumberOfCells, 3);
Assert.AreEqual(s.GetRow(1).PhysicalNumberOfCells, 4);
ConfirmEmptyRow(s, 2);
ConfirmEmptyRow(s, 3);
Assert.AreEqual(s.GetRow(4).PhysicalNumberOfCells, 5);
}
/**
* [Bug 49928] formatCellValue returns incorrect value for \u00a3 formatted cells
*/
public virtual void Test49928()
{
IWorkbook wb = _testDataProvider.OpenSampleWorkbook("49928.xls");
doTest49928Core(wb);
}
/**
* Open a given workbook.
*
* @param workbookName The workbook name. This is presumed to live in the
* "spreadsheets" directory under the directory named in
* the Java property "POI.testdata.path".
*
* @throws IOException
*/
protected void OpenWorkbook(String workbookName)
{
workbook = _testDataProvider.OpenSampleWorkbook(workbookName);
workbook.MissingCellPolicy = MissingCellPolicy.CREATE_NULL_AS_BLANK;//Row.CREATE_NULL_AS_BLANK);
testFile = workbookName;
}
public void SetUp()
{
wbH = _testDataProvider.OpenSampleWorkbook(_file1);
wbU = _testDataProvider.OpenSampleWorkbook(_file2);
}
//
public void TestRead(string sampleFile)
{
IWorkbook wb = _testDataProvider.OpenSampleWorkbook(sampleFile);
ISheet sh = wb.GetSheet("CF");
ISheetConditionalFormatting sheetCF = sh.SheetConditionalFormatting;
Assert.AreEqual(3, sheetCF.NumConditionalFormattings);
IConditionalFormatting cf1 = sheetCF.GetConditionalFormattingAt(0);
Assert.AreEqual(2, cf1.NumberOfRules);
CellRangeAddress[] regions1 = cf1.GetFormattingRanges();
Assert.AreEqual(1, regions1.Length);
Assert.AreEqual("A1:A8", regions1[0].FormatAsString());
// CF1 has two rules: values less than -3 are bold-italic red, values greater than 3 are green
IConditionalFormattingRule rule1 = cf1.GetRule(0);
Assert.AreEqual(ConditionType.CellValueIs, rule1.ConditionType);
Assert.AreEqual(ComparisonOperator.GreaterThan, rule1.ComparisonOperation);
Assert.AreEqual("3", rule1.Formula1);
Assert.IsNull(rule1.Formula2);
// Fills and borders are not Set
Assert.IsNull(rule1.GetPatternFormatting());
Assert.IsNull(rule1.GetBorderFormatting());
IFontFormatting fmt1 = rule1.GetFontFormatting();
// Assert.AreEqual(HSSFColor.GREEN.index, fmt1.FontColorIndex);
Assert.IsTrue(fmt1.IsBold);
Assert.IsFalse(fmt1.IsItalic);
IConditionalFormattingRule rule2 = cf1.GetRule(1);
Assert.AreEqual(ConditionType.CellValueIs, rule2.ConditionType);
Assert.AreEqual(ComparisonOperator.LessThan, rule2.ComparisonOperation);
Assert.AreEqual("-3", rule2.Formula1);
Assert.IsNull(rule2.Formula2);
Assert.IsNull(rule2.GetPatternFormatting());
Assert.IsNull(rule2.GetBorderFormatting());
IFontFormatting fmt2 = rule2.GetFontFormatting();
// Assert.AreEqual(HSSFColor.Red.index, fmt2.FontColorIndex);
Assert.IsTrue(fmt2.IsBold);
Assert.IsTrue(fmt2.IsItalic);
IConditionalFormatting cf2 = sheetCF.GetConditionalFormattingAt(1);
Assert.AreEqual(1, cf2.NumberOfRules);
CellRangeAddress[] regions2 = cf2.GetFormattingRanges();
Assert.AreEqual(1, regions2.Length);
Assert.AreEqual("B9", regions2[0].FormatAsString());
IConditionalFormattingRule rule3 = cf2.GetRule(0);
Assert.AreEqual(ConditionType.Formula, rule3.ConditionType);
Assert.AreEqual(ComparisonOperator.NoComparison, rule3.ComparisonOperation);
Assert.AreEqual("$A$8>5", rule3.Formula1);
Assert.IsNull(rule3.Formula2);
IFontFormatting fmt3 = rule3.GetFontFormatting();
// Assert.AreEqual(HSSFColor.Red.index, fmt3.FontColorIndex);
Assert.IsTrue(fmt3.IsBold);
Assert.IsTrue(fmt3.IsItalic);
IPatternFormatting fmt4 = rule3.GetPatternFormatting();
// Assert.AreEqual(HSSFColor.LIGHT_CORNFLOWER_BLUE.index, fmt4.FillBackgroundColor);
// Assert.AreEqual(HSSFColor.Automatic.index, fmt4.FillForegroundColor);
Assert.AreEqual((short)FillPattern.NoFill, fmt4.FillPattern);
// borders are not Set
Assert.IsNull(rule3.GetBorderFormatting());
IConditionalFormatting cf3 = sheetCF.GetConditionalFormattingAt(2);
CellRangeAddress[] regions3 = cf3.GetFormattingRanges();
Assert.AreEqual(1, regions3.Length);
Assert.AreEqual("B1:B7", regions3[0].FormatAsString());
Assert.AreEqual(2, cf3.NumberOfRules);
IConditionalFormattingRule rule4 = cf3.GetRule(0);
Assert.AreEqual(ConditionType.CellValueIs, rule4.ConditionType);
Assert.AreEqual(ComparisonOperator.LessThanOrEqual, rule4.ComparisonOperation);
Assert.AreEqual("\"AAA\"", rule4.Formula1);
Assert.IsNull(rule4.Formula2);
IConditionalFormattingRule rule5 = cf3.GetRule(1);
Assert.AreEqual(ConditionType.CellValueIs, rule5.ConditionType);
Assert.AreEqual(ComparisonOperator.Between, rule5.ComparisonOperation);
Assert.AreEqual("\"A\"", rule5.Formula1);
Assert.AreEqual("\"AAA\"", rule5.Formula2);
}
public void TestRead()
{
IWorkbook wb = _testDataProvider.OpenSampleWorkbook("WithConditionalFormatting.xls");
ISheet sh = wb.GetSheet("CF");
ISheetConditionalFormatting sheetCF = sh.SheetConditionalFormatting;
Assert.AreEqual(3, sheetCF.NumConditionalFormattings);
IConditionalFormatting cf1 = sheetCF.GetConditionalFormattingAt(0);
Assert.AreEqual(2, cf1.NumberOfRules);
CellRangeAddress[] regions1 = cf1.GetFormattingRanges();
Assert.AreEqual(1, regions1.Length);
Assert.AreEqual("A1:A8", regions1[0].FormatAsString());
// CF1 has two rules: values less than -3 are bold-italic red, values greater than 3 are green
IConditionalFormattingRule rule1 = cf1.GetRule(0);
Assert.AreEqual(ConditionType.CELL_VALUE_IS, rule1.ConditionType);
Assert.AreEqual(ComparisonOperator.GT, rule1.ComparisonOperation);
Assert.AreEqual("3", rule1.Formula1);
Assert.IsNull(rule1.Formula2);
// Fills and borders are not Set
Assert.IsNull(rule1.GetPatternFormatting());
Assert.IsNull(rule1.GetBorderFormatting());
IFontFormatting fmt1 = rule1.GetFontFormatting();
// Assert.AreEqual(IndexedColors.GREEN.index, fmt1.FontColorIndex);
Assert.IsTrue(fmt1.IsBold);
Assert.IsFalse(fmt1.IsItalic);
IConditionalFormattingRule rule2 = cf1.GetRule(1);
Assert.AreEqual(ConditionType.CELL_VALUE_IS, rule2.ConditionType);
Assert.AreEqual(ComparisonOperator.LT, rule2.ComparisonOperation);
Assert.AreEqual("-3", rule2.Formula1);
Assert.IsNull(rule2.Formula2);
Assert.IsNull(rule2.GetPatternFormatting());
Assert.IsNull(rule2.GetBorderFormatting());
IFontFormatting fmt2 = rule2.GetFontFormatting();
// Assert.AreEqual(IndexedColors.RED.index, fmt2.FontColorIndex);
Assert.IsTrue(fmt2.IsBold);
Assert.IsTrue(fmt2.IsItalic);
IConditionalFormatting cf2 = sheetCF.GetConditionalFormattingAt(1);
Assert.AreEqual(1, cf2.NumberOfRules);
CellRangeAddress[] regions2 = cf2.GetFormattingRanges();
Assert.AreEqual(1, regions2.Length);
Assert.AreEqual("B9", regions2[0].FormatAsString());
IConditionalFormattingRule rule3 = cf2.GetRule(0);
Assert.AreEqual(ConditionType.FORMULA, rule3.ConditionType);
Assert.AreEqual(ComparisonOperator.NO_COMPARISON, rule3.ComparisonOperation);
Assert.AreEqual("$A$8>5", rule3.Formula1);
Assert.IsNull(rule3.Formula2);
IFontFormatting fmt3 = rule3.GetFontFormatting();
// Assert.AreEqual(IndexedColors.RED.index, fmt3.FontColorIndex);
Assert.IsTrue(fmt3.IsBold);
Assert.IsTrue(fmt3.IsItalic);
IPatternFormatting fmt4 = rule3.GetPatternFormatting();
// Assert.AreEqual(IndexedColors.LIGHT_CORNFLOWER_BLUE.index, fmt4.FillBackgroundColor);
// Assert.AreEqual(IndexedColors.AUTOMATIC.index, fmt4.FillForegroundColor);
Assert.AreEqual(PatternFormatting.NO_Fill, fmt4.FillPattern);
// borders are not Set
Assert.IsNull(rule3.GetBorderFormatting());
IConditionalFormatting cf3 = sheetCF.GetConditionalFormattingAt(2);
CellRangeAddress[] regions3 = cf3.GetFormattingRanges();
Assert.AreEqual(1, regions3.Length);
Assert.AreEqual("B1:B7", regions3[0].FormatAsString());
Assert.AreEqual(2, cf3.NumberOfRules);
IConditionalFormattingRule rule4 = cf3.GetRule(0);
Assert.AreEqual(ConditionType.CELL_VALUE_IS, rule4.ConditionType);
Assert.AreEqual(ComparisonOperator.LE, rule4.ComparisonOperation);
Assert.AreEqual("\"AAA\"", rule4.Formula1);
Assert.IsNull(rule4.Formula2);
IConditionalFormattingRule rule5 = cf3.GetRule(1);
Assert.AreEqual(ConditionType.CELL_VALUE_IS, rule5.ConditionType);
Assert.AreEqual(ComparisonOperator.BETWEEN, rule5.ComparisonOperation);
Assert.AreEqual("\"A\"", rule5.Formula1);
Assert.AreEqual("\"AAA\"", rule5.Formula2);
}