public void TestLastAndFirstColumns()
{
IWorkbook workbook = _testDataProvider.CreateWorkbook();
ISheet sheet = workbook.CreateSheet();
IRow row = sheet.CreateRow(0);
Assert.AreEqual(-1, row.FirstCellNum);
Assert.AreEqual(-1, row.LastCellNum);
//getting cells from an empty row should returns null
for (int i = 0; i < 10; i++)
{
Assert.IsNull(row.GetCell(i));
}
row.CreateCell(2);
Assert.AreEqual(2, row.FirstCellNum);
Assert.AreEqual(3, row.LastCellNum);
row.CreateCell(1);
Assert.AreEqual(1, row.FirstCellNum);
Assert.AreEqual(3, row.LastCellNum);
// check the exact case reported in 'bug' 43901 - notice that the cellNum is '0' based
row.CreateCell(3);
Assert.AreEqual(1, row.FirstCellNum);
Assert.AreEqual(4, row.LastCellNum);
}
public void TestIndexFormula()
{
IWorkbook wb = _testDataProvider.CreateWorkbook();
ISheet sheet = wb.CreateSheet("Sheet1");
int colB = 1;
sheet.CreateRow(0).CreateCell(colB).SetCellValue(1);
sheet.CreateRow(1).CreateCell(colB).SetCellValue(2);
sheet.CreateRow(2).CreateCell(colB).SetCellValue(3);
IRow row4 = sheet.CreateRow(3);
ICell testCell = row4.CreateCell(0);
// This formula should Evaluate to the contents of B2,
testCell.SetCellFormula("INDEX(A1:B4,2,2)");
// However the range A1:B4 also includes the current cell A4. If the other parameters
// were 4 and 1, this would represent a circular reference. Prior to v3.2 POI would
// 'fully' Evaluate ref arguments before invoking operators, which raised the possibility of
// cycles / StackOverflowErrors.
CellValue cellValue = EvaluateWithCycles(wb, testCell);
Assert.IsTrue(cellValue.CellType == CellType.Numeric);
Assert.AreEqual(2, cellValue.NumberValue, 0);
wb.Close();
}
public void TestBuiltinFormats()
{
IWorkbook wb = _testDataProvider.CreateWorkbook();
IDataFormat df = wb.CreateDataFormat();
List <String> formats = HSSFDataFormat.GetBuiltinFormats();
for (int idx = 0; idx < formats.Count; idx++)
{
String fmt = formats[idx];
Assert.AreEqual(idx, df.GetFormat(fmt));
}
//default format for new cells is General
ISheet sheet = wb.CreateSheet();
ICell cell = sheet.CreateRow(0).CreateCell(0);
Assert.AreEqual(0, cell.CellStyle.DataFormat);
Assert.AreEqual("General", cell.CellStyle.GetDataFormatString());
//create a custom data format
String customFmt = "#0.00 AM/PM";
//check it is not in built-in formats
AssertNotBuiltInFormat(customFmt);
int customIdx = df.GetFormat(customFmt);
//The first user-defined format starts at 164.
Assert.IsTrue(customIdx >= HSSFDataFormat.FIRST_USER_DEFINED_FORMAT_INDEX);
//read and verify the string representation
Assert.AreEqual(customFmt, df.GetFormat((short)customIdx));
wb.Close();
}
public void TestBasicTypes()
{
IWorkbook wb1 = _testDataProvider.CreateWorkbook();
ICreationHelper CreateHelper = wb1.GetCreationHelper();
ICell cell;
IHyperlink link;
ISheet sheet = wb1.CreateSheet("Hyperlinks");
//URL
cell = sheet.CreateRow(0).CreateCell((short)0);
cell.SetCellValue("URL Link");
link = CreateHelper.CreateHyperlink(HyperlinkType.Url);
link.Address = ("http://poi.apache.org/");
cell.Hyperlink = (link);
//link to a file in the current directory
cell = sheet.CreateRow(1).CreateCell((short)0);
cell.SetCellValue("File Link");
link = CreateHelper.CreateHyperlink(HyperlinkType.File);
link.Address = ("hyperinks-beta4-dump.txt");
cell.Hyperlink = (link);
//e-mail link
cell = sheet.CreateRow(2).CreateCell((short)0);
cell.SetCellValue("Email Link");
link = CreateHelper.CreateHyperlink(HyperlinkType.Email);
//note, if subject Contains white spaces, make sure they are url-encoded
link.Address = ("mailto:[email protected]?subject=Hyperlinks");
cell.Hyperlink = (link);
//link to a place in this workbook
//create a target sheet and cell
ISheet sheet2 = wb1.CreateSheet("Target Sheet");
sheet2.CreateRow(0).CreateCell((short)0).SetCellValue("Target Cell");
cell = sheet.CreateRow(3).CreateCell((short)0);
cell.SetCellValue("Worksheet Link");
link = CreateHelper.CreateHyperlink(HyperlinkType.Document);
link.Address = ("'Target Sheet'!A1");
cell.Hyperlink = (link);
IWorkbook wb2 = _testDataProvider.WriteOutAndReadBack(wb1);
sheet = wb2.GetSheetAt(0);
link = sheet.GetRow(0).GetCell(0).Hyperlink;
Assert.AreEqual("http://poi.apache.org/", link.Address);
link = sheet.GetRow(1).GetCell(0).Hyperlink;
Assert.AreEqual("hyperinks-beta4-dump.txt", link.Address);
link = sheet.GetRow(2).GetCell(0).Hyperlink;
Assert.AreEqual("mailto:[email protected]?subject=Hyperlinks", link.Address);
link = sheet.GetRow(3).GetCell(0).Hyperlink;
Assert.AreEqual("'Target Sheet'!A1", link.Address);
wb2.Close();
}
public void TestShiftRow()
{
IWorkbook b = _testDataProvider.CreateWorkbook();
ISheet s = b.CreateSheet();
s.CreateRow(0).CreateCell(0).SetCellValue("TEST1");
s.CreateRow(3).CreateCell(0).SetCellValue("TEST2");
s.ShiftRows(0, 4, 1);
}
public void TestSimpleArithmetic()
{
IWorkbook wb = _testDataProvider.CreateWorkbook();
ISheet s = wb.CreateSheet();
IRow r = s.CreateRow(0);
ICell c1 = r.CreateCell(0);
c1.CellFormula = (/*setter*/ "1+5");
Assert.AreEqual(0.0, c1.NumericCellValue, 0.0);
ICell c2 = r.CreateCell(1);
c2.CellFormula = (/*setter*/ "10/2");
Assert.AreEqual(0.0, c2.NumericCellValue, 0.0);
IFormulaEvaluator fe = wb.GetCreationHelper().CreateFormulaEvaluator();
fe.EvaluateFormulaCell(c1);
fe.EvaluateFormulaCell(c2);
Assert.AreEqual(6.0, c1.NumericCellValue, 0.0001);
Assert.AreEqual(5.0, c2.NumericCellValue, 0.0001);
wb.Close();
}
public void TestCloneSheetBasic()
{
IWorkbook b = _testDataProvider.CreateWorkbook();
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");
b.Close();
}
public void TestFind()
{
IWorkbook book = _testDataProvider.CreateWorkbook();
ISheet sheet = book.CreateSheet();
Assert.IsNull(sheet.GetCellComment(0, 0));
IRow row = sheet.CreateRow(0);
ICell cell = row.CreateCell(0);
Assert.IsNull(sheet.GetCellComment(0, 0));
Assert.IsNull(cell.CellComment);
}
public void TestDataValidation()
{
Log("\nTest no. 2 - Test Excel's Data validation mechanism");
IWorkbook wb = _testDataProvider.CreateWorkbook();
WorkbookFormatter wf = new WorkbookFormatter(wb);
Log(" Create sheet for Data Validation's number types ... ");
AddSimpleNumericValidations(wf);
Log("done !");
Log(" Create sheet for 'List' Data Validation type ... ");
AddListValidations(wf, wb);
Log("done !");
Log(" Create sheet for 'Date' and 'Time' Data Validation types ... ");
AddDateTimeValidations(wf, wb);
Log("done !");
Log(" Create sheet for 'Text length' Data Validation type... ");
AddTextLengthValidations(wf);
Log("done !");
// Custom Validation type
Log(" Create sheet for 'Custom' Data Validation type ... ");
AddCustomValidations(wf);
Log("done !");
wb = _testDataProvider.WriteOutAndReadBack(wb);
}
public void TestResizeNoColumns()
{
IWorkbook wb = _testDataProvider.CreateWorkbook();
try
{
ISheet sheet = wb.CreateSheet();
IRow row = sheet.CreateRow(0);
handleResize(wb, sheet, row);
}
finally
{
//wb.Close();
}
}
public void BaseTestDefaultFont(string defaultName, short defaultSize, short defaultColor)
{
//get default font and check against default value
IWorkbook workbook = _testDataProvider.CreateWorkbook();
IFont fontFind = workbook.FindFont((short)FontBoldWeight.Normal, defaultColor, defaultSize, defaultName, false, false, FontSuperScript.None, FontUnderlineType.None);
Assert.IsNotNull(fontFind);
//get default font, then change 2 values and check against different values (height Changes)
IFont font = workbook.CreateFont();
font.Boldweight = (short)(FontBoldWeight.Bold);
Assert.AreEqual((short)FontBoldWeight.Bold, font.Boldweight);
font.Underline = FontUnderlineType.Double;
Assert.AreEqual(FontUnderlineType.Double, font.Underline);
font.FontHeightInPoints = ((short)15);
Assert.AreEqual(15 * 20, font.FontHeight);
Assert.AreEqual(15, font.FontHeightInPoints);
fontFind = workbook.FindFont((short)FontBoldWeight.Bold, defaultColor, (short)(15 * 20), defaultName, false, false, FontSuperScript.None, FontUnderlineType.Double);
Assert.IsNotNull(fontFind);
}
public void BaseTestDefaultFont(String defaultName, short defaultSize, short defaultColor)
{
//get default font and check against default value
IWorkbook workbook = _testDataProvider.CreateWorkbook();
IFont fontFind = workbook.FindFont((short)FontBoldWeight.NORMAL, defaultColor, defaultSize, defaultName, false, false, FontFormatting.SS_NONE, (byte)FontUnderlineType.NONE);
Assert.IsNotNull(fontFind);
//get default font, then change 2 values and check against different values (height Changes)
IFont font = workbook.CreateFont();
font.Boldweight = (short)(FontBoldWeight.BOLD);
Assert.AreEqual((short)FontBoldWeight.BOLD, font.Boldweight);
font.Underline = (byte)(FontUnderlineType.DOUBLE);
Assert.AreEqual((byte)FontUnderlineType.DOUBLE, font.Underline);
font.FontHeightInPoints = ((short)15);
Assert.AreEqual(15 * 20, font.FontHeight);
Assert.AreEqual(15, font.FontHeightInPoints);
fontFind = workbook.FindFont((short)FontBoldWeight.BOLD, defaultColor, (short)(15 * 20), defaultName, false, false, FontFormatting.SS_NONE, (byte)FontUnderlineType.DOUBLE);
Assert.IsNotNull(fontFind);
}
public void NumericCells()
{
System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.GetCultureInfo("en-US");
IWorkbook workbook = _testDataProvider.CreateWorkbook();
FixFonts(workbook);
IDataFormat df = workbook.GetCreationHelper().CreateDataFormat();
ISheet sheet = workbook.CreateSheet();
TrackColumnsForAutoSizingIfSXSSF(sheet);
IRow row = sheet.CreateRow(0);
row.CreateCell(0).SetCellValue(0); // GetCachedFormulaResult() returns 0 for not Evaluated formula cells
row.CreateCell(1).SetCellValue(10);
row.CreateCell(2).SetCellValue("10");
row.CreateCell(3).CellFormula = (/*setter*/ "(A1+B1)*1.0"); // a formula that returns '10'
ICell cell4 = row.CreateCell(4); // numeric cell with a custom style
ICellStyle style4 = workbook.CreateCellStyle();
style4.DataFormat = (/*setter*/ df.GetFormat("0.0000"));
cell4.CellStyle = (/*setter*/ style4);
cell4.SetCellValue(10); // formatted as '10.0000'
row.CreateCell(5).SetCellValue("10.0000");
// autosize not-Evaluated cells, formula cells are sized as if the result is 0
for (int i = 0; i < 6; i++)
{
sheet.AutoSizeColumn(i);
}
Assert.IsTrue(sheet.GetColumnWidth(0) < sheet.GetColumnWidth(1)); // width of '0' is less then width of '10'
Assert.AreEqual(sheet.GetColumnWidth(1), sheet.GetColumnWidth(2)); // 10 and '10' should be sized Equally
Assert.AreEqual(sheet.GetColumnWidth(3), sheet.GetColumnWidth(0)); // formula result is unknown, the width is calculated for '0'
Assert.AreEqual(sheet.GetColumnWidth(4), sheet.GetColumnWidth(5)); // 10.0000 and '10.0000'
// Evaluate formulas and re-autosize
EvaluateWorkbook(workbook);
for (int i = 0; i < 6; i++)
{
sheet.AutoSizeColumn(i);
}
Assert.IsTrue(sheet.GetColumnWidth(0) < sheet.GetColumnWidth(1)); // width of '0' is less then width of '10'
Assert.AreEqual(sheet.GetColumnWidth(1), sheet.GetColumnWidth(2)); // columns 1, 2 and 3 should have the same width
Assert.AreEqual(sheet.GetColumnWidth(2), sheet.GetColumnWidth(3)); // columns 1, 2 and 3 should have the same width
Assert.AreEqual(sheet.GetColumnWidth(4), sheet.GetColumnWidth(5)); // 10.0000 and '10.0000'
workbook.Close();
}
public void TestSheetHidden()
{
IWorkbook wb = _testDataProvider.CreateWorkbook();
wb.CreateSheet("MySheet");
Assert.IsFalse(wb.IsSheetHidden(0));
Assert.IsFalse(wb.IsSheetVeryHidden(0));
wb.SetSheetHidden(0, SheetState.Hidden);
Assert.IsTrue(wb.IsSheetHidden(0));
Assert.IsFalse(wb.IsSheetVeryHidden(0));
wb.SetSheetHidden(0, SheetState.VeryHidden);
Assert.IsFalse(wb.IsSheetHidden(0));
Assert.IsTrue(wb.IsSheetVeryHidden(0));
wb.SetSheetHidden(0, SheetState.Visible);
Assert.IsFalse(wb.IsSheetHidden(0));
Assert.IsFalse(wb.IsSheetVeryHidden(0));
try
{
wb.SetSheetHidden(0, -1);
Assert.Fail("expectd exception");
}
catch (ArgumentException)
{
// ok
}
try
{
wb.SetSheetHidden(0, 3);
Assert.Fail("expectd exception");
}
catch (ArgumentException)
{
// ok
}
wb.Close();
}
public void TestSheetHidden()
{
IWorkbook wb = _testDataProvider.CreateWorkbook();
ISheet sh = wb.CreateSheet("MySheet");
Assert.IsFalse(wb.IsSheetHidden(0));
Assert.IsFalse(wb.IsSheetVeryHidden(0));
wb.SetSheetHidden(0, SheetState.HIDDEN);
Assert.IsTrue(wb.IsSheetHidden(0));
Assert.IsFalse(wb.IsSheetVeryHidden(0));
wb.SetSheetHidden(0, SheetState.VERY_HIDDEN);
Assert.IsFalse(wb.IsSheetHidden(0));
Assert.IsTrue(wb.IsSheetVeryHidden(0));
wb.SetSheetHidden(0, SheetState.VISIBLE);
Assert.IsFalse(wb.IsSheetHidden(0));
Assert.IsFalse(wb.IsSheetVeryHidden(0));
try
{
wb.SetSheetHidden(0, -1);
Assert.Fail("expectd exception");
}
catch (ArgumentException)
{
// ok
}
try
{
wb.SetSheetHidden(0, 3);
Assert.Fail("expectd exception");
}
catch (ArgumentException)
{
// ok
}
}
public void BaseTestResize(IClientAnchor referenceAnchor)
{
IWorkbook wb = _testDataProvider.CreateWorkbook();
ISheet sh1 = wb.CreateSheet();
IDrawing p1 = sh1.CreateDrawingPatriarch();
ICreationHelper factory = wb.GetCreationHelper();
byte[] pictureData = _testDataProvider.GetTestDataFileContent("logoKarmokar4.png");
int idx1 = wb.AddPicture(pictureData, PictureType.PNG);
IPicture picture = p1.CreatePicture(factory.CreateClientAnchor(), idx1);
picture.Resize();
IClientAnchor anchor1 = picture.GetPreferredSize();
//assert against what would BiffViewer print if we insert the image in xls and dump the file
Assert.AreEqual(referenceAnchor.Col1, anchor1.Col1);
Assert.AreEqual(referenceAnchor.Row1, anchor1.Row1);
Assert.AreEqual(referenceAnchor.Col2, anchor1.Col2);
Assert.AreEqual(referenceAnchor.Row2, anchor1.Row2);
Assert.AreEqual(referenceAnchor.Dx1, anchor1.Dx1);
Assert.AreEqual(referenceAnchor.Dy1, anchor1.Dy1);
Assert.AreEqual(referenceAnchor.Dx2, anchor1.Dx2);
Assert.AreEqual(referenceAnchor.Dy2, anchor1.Dy2);
}
public void TestCreateRow()
{
IWorkbook workbook = _testDataProvider.CreateWorkbook();
ISheet sheet = workbook.CreateSheet();
Assert.AreEqual(0, sheet.PhysicalNumberOfRows);
//Test that we Get null for undefined rownumber
Assert.IsNull(sheet.GetRow(1));
// Test row creation with consecutive indexes
IRow row1 = sheet.CreateRow(0);
IRow row2 = sheet.CreateRow(1);
Assert.AreEqual(0, row1.RowNum);
Assert.AreEqual(1, row2.RowNum);
IEnumerator it = sheet.GetRowEnumerator();
Assert.IsTrue(it.MoveNext());
Assert.AreSame(row1, it.Current);
Assert.IsTrue(it.MoveNext());
Assert.AreSame(row2, it.Current);
Assert.AreEqual(1, sheet.LastRowNum);
// Test row creation with non consecutive index
IRow row101 = sheet.CreateRow(100);
Assert.IsNotNull(row101);
Assert.AreEqual(100, sheet.LastRowNum);
Assert.AreEqual(3, sheet.PhysicalNumberOfRows);
// Test overwriting an existing row
IRow row2_ovrewritten = sheet.CreateRow(1);
ICell cell = row2_ovrewritten.CreateCell(0);
cell.SetCellValue(100);
IEnumerator it2 = sheet.GetRowEnumerator();
Assert.IsTrue(it2.MoveNext());
Assert.AreSame(row1, it2.Current);
Assert.IsTrue(it2.MoveNext());
IRow row2_ovrewritten_ref = (IRow)it2.Current;
Assert.AreSame(row2_ovrewritten, row2_ovrewritten_ref);
Assert.AreEqual(100.0, row2_ovrewritten_ref.GetCell(0).NumericCellValue, 0.0);
}
public void Test23094()
{
IWorkbook wb = _testDataProvider.CreateWorkbook();
ISheet s = wb.CreateSheet();
IRow r = s.CreateRow(0);
r.CreateCell(0).CellFormula = "HYPERLINK(\"http://jakarta.apache.org\",\"Jakarta\")";
r.CreateCell(1).CellFormula = "HYPERLINK(\"http://google.com\",\"Google\")";
wb = _testDataProvider.WriteOutAndReadBack(wb);
r = wb.GetSheetAt(0).GetRow(0);
ICell cell_0 = r.GetCell(0);
Assert.AreEqual("HYPERLINK(\"http://jakarta.apache.org\",\"Jakarta\")", cell_0.CellFormula);
ICell cell_1 = r.GetCell(1);
Assert.AreEqual("HYPERLINK(\"http://google.com\",\"Google\")", cell_1.CellFormula);
}
public void TestSetValues()
{
IWorkbook book = _testDataProvider.CreateWorkbook();
ISheet sheet = book.CreateSheet("test");
IRow row = sheet.CreateRow(0);
ICreationHelper factory = book.GetCreationHelper();
ICell cell = row.CreateCell(0);
cell.SetCellValue(1.2);
Assert.AreEqual(1.2, cell.NumericCellValue, 0.0001);
Assert.AreEqual(CellType.Numeric, cell.CellType);
AssertProhibitedValueAccess(cell, CellType.Boolean, CellType.String,
CellType.Formula, CellType.Error);
cell.SetCellValue(false);
Assert.AreEqual(false, cell.BooleanCellValue);
Assert.AreEqual(CellType.Boolean, cell.CellType);
cell.SetCellValue(true);
Assert.AreEqual(true, cell.BooleanCellValue);
AssertProhibitedValueAccess(cell, CellType.Numeric, CellType.String,
CellType.Formula, CellType.Error);
cell.SetCellValue(factory.CreateRichTextString("Foo"));
Assert.AreEqual("Foo", cell.RichStringCellValue.String);
Assert.AreEqual("Foo", cell.StringCellValue);
Assert.AreEqual(CellType.String, cell.CellType);
AssertProhibitedValueAccess(cell, CellType.Numeric, CellType.Boolean,
CellType.Formula, CellType.Error);
cell.SetCellValue("345");
Assert.AreEqual("345", cell.RichStringCellValue.String);
Assert.AreEqual("345", cell.StringCellValue);
Assert.AreEqual(CellType.String, cell.CellType);
AssertProhibitedValueAccess(cell, CellType.Numeric, CellType.Boolean,
CellType.Formula, CellType.Error);
DateTime dt = DateTime.Now.AddMilliseconds(123456789);
cell.SetCellValue(dt);
Assert.IsTrue((dt.Ticks - cell.DateCellValue.Ticks) >= -20000);
Assert.AreEqual(CellType.Numeric, cell.CellType);
AssertProhibitedValueAccess(cell, CellType.Boolean, CellType.String,
CellType.Formula, CellType.Error);
cell.SetCellValue(dt);
Assert.IsTrue((dt.Ticks - cell.DateCellValue.Ticks) >= -20000);
Assert.AreEqual(CellType.Numeric, cell.CellType);
AssertProhibitedValueAccess(cell, CellType.Boolean, CellType.String,
CellType.Formula, CellType.Error);
cell.SetCellErrorValue(FormulaError.NA.Code);
Assert.AreEqual(FormulaError.NA.Code, cell.ErrorCellValue);
Assert.AreEqual(CellType.Error, cell.CellType);
AssertProhibitedValueAccess(cell, CellType.Numeric, CellType.Boolean,
CellType.Formula, CellType.String);
}
public void TestBasic()
{
IWorkbook wb = _testDataProvider.CreateWorkbook();
ISheet sh = wb.CreateSheet();
ISheetConditionalFormatting sheetCF = sh.SheetConditionalFormatting;
Assert.AreEqual(0, sheetCF.NumConditionalFormattings);
try
{
Assert.IsNull(sheetCF.GetConditionalFormattingAt(0));
Assert.Fail("expected exception");
}
catch (ArgumentException e)
{
Assert.IsTrue(e.Message.StartsWith("Specified CF index 0 is outside the allowable range"));
}
try
{
sheetCF.RemoveConditionalFormatting(0);
Assert.Fail("expected exception");
}
catch (ArgumentException e)
{
Assert.IsTrue(e.Message.StartsWith("Specified CF index 0 is outside the allowable range"));
}
IConditionalFormattingRule rule1 = sheetCF.CreateConditionalFormattingRule("1");
IConditionalFormattingRule rule2 = sheetCF.CreateConditionalFormattingRule("2");
IConditionalFormattingRule rule3 = sheetCF.CreateConditionalFormattingRule("3");
IConditionalFormattingRule rule4 = sheetCF.CreateConditionalFormattingRule("4");
try
{
sheetCF.AddConditionalFormatting(null, rule1);
Assert.Fail("expected exception");
}
catch (ArgumentException e)
{
Assert.IsTrue(e.Message.StartsWith("regions must not be null"));
}
try
{
sheetCF.AddConditionalFormatting(
new CellRangeAddress[] { CellRangeAddress.ValueOf("A1:A3") },
(IConditionalFormattingRule)null);
Assert.Fail("expected exception");
}
catch (ArgumentException e)
{
Assert.IsTrue(e.Message.StartsWith("cfRules must not be null"));
}
try
{
sheetCF.AddConditionalFormatting(
new CellRangeAddress[] { CellRangeAddress.ValueOf("A1:A3") },
new IConditionalFormattingRule[0]);
Assert.Fail("expected exception");
}
catch (ArgumentException e)
{
Assert.IsTrue(e.Message.StartsWith("cfRules must not be empty"));
}
try
{
sheetCF.AddConditionalFormatting(
new CellRangeAddress[] { CellRangeAddress.ValueOf("A1:A3") },
new IConditionalFormattingRule[] { rule1, rule2, rule3, rule4 });
Assert.Fail("expected exception");
}
catch (ArgumentException e)
{
Assert.IsTrue(e.Message.StartsWith("Number of rules must not exceed 3"));
}
}
public void TestCreate()
{
// Create a new workbook
IWorkbook wb = _testDataProvider.CreateWorkbook();
ISheet sheet1 = wb.CreateSheet("Test1");
ISheet sheet2 = wb.CreateSheet("Testing Named Ranges");
IName name1 = wb.CreateName();
name1.NameName = ("testOne");
//setting a duplicate name should throw ArgumentException
IName name2 = wb.CreateName();
try
{
name2.NameName = ("testOne");
Assert.Fail("expected exception");
}
catch (ArgumentException e)
{
Assert.AreEqual("The workbook already contains this name: testOne", e.Message);
}
//the check for duplicates is case-insensitive
try
{
name2.NameName = ("TESTone");
Assert.Fail("expected exception");
}
catch (ArgumentException e)
{
Assert.AreEqual("The workbook already contains this name: TESTone", e.Message);
}
name2.NameName = ("testTwo");
String ref1 = "Test1!$A$1:$B$1";
name1.RefersToFormula = (ref1);
Assert.AreEqual(ref1, name1.RefersToFormula);
Assert.AreEqual("Test1", name1.SheetName);
String ref2 = "'Testing Named Ranges'!$A$1:$B$1";
name1.RefersToFormula = (ref2);
Assert.AreEqual("'Testing Named Ranges'!$A$1:$B$1", name1.RefersToFormula);
Assert.AreEqual("Testing Named Ranges", name1.SheetName);
Assert.AreEqual(-1, name1.SheetIndex);
name1.SheetIndex = (-1);
Assert.AreEqual(-1, name1.SheetIndex);
try
{
name1.SheetIndex = (2);
Assert.Fail("should throw ArgumentException");
}
catch (ArgumentException e)
{
Assert.AreEqual("Sheet index (2) is out of range (0..1)", e.Message);
}
name1.SheetIndex = (1);
Assert.AreEqual(1, name1.SheetIndex);
//-1 means the name applies to the entire workbook
name1.SheetIndex = (-1);
Assert.AreEqual(-1, name1.SheetIndex);
//names cannot be blank and must begin with a letter or underscore and not contain spaces
String[] invalidNames = { "", "123", "1Name", "Named Range" };
foreach (String name in invalidNames)
{
try
{
name1.NameName = (name);
Assert.Fail("should have thrown exceptiuon due to invalid name: " + name);
}
catch (ArgumentException)
{
// expected during successful Test
}
}
}
public void SheetIterator_forEach()
{
IWorkbook wb = _testDataProvider.CreateWorkbook();
wb.CreateSheet("Sheet0");
wb.CreateSheet("Sheet1");
wb.CreateSheet("Sheet2");
int i = 0;
foreach (ISheet sh in wb)
{
Assert.AreEqual("Sheet" + i, sh.SheetName);
i++;
}
wb.Close();
}
public void TestCreateSheet()
{
IWorkbook wb = _testDataProvider.CreateWorkbook();
Assert.AreEqual(0, wb.NumberOfSheets);
//getting a sheet by invalid index or non-existing name
Assert.IsNull(wb.GetSheet("Sheet1"));
try
{
wb.GetSheetAt(0);
Assert.Fail("should have thrown exceptiuon due to invalid sheet index");
}
catch (ArgumentException)
{
// expected during successful Test
}
ISheet sheet0 = wb.CreateSheet();
ISheet sheet1 = wb.CreateSheet();
Assert.AreEqual("Sheet0", sheet0.SheetName);
Assert.AreEqual("Sheet1", sheet1.SheetName);
Assert.AreEqual(2, wb.NumberOfSheets);
//fetching sheets by name is case-insensitive
ISheet originalSheet = wb.CreateSheet("Sheet3");
ISheet fetchedSheet = wb.GetSheet("sheet3");
if (fetchedSheet == null)
{
throw new AssertionException("Identified bug 44892");
}
Assert.AreEqual("Sheet3", fetchedSheet.SheetName);
Assert.AreEqual(3, wb.NumberOfSheets);
Assert.AreSame(originalSheet, fetchedSheet);
try
{
wb.CreateSheet("sHeeT3");
Assert.Fail("should have thrown exceptiuon due to duplicate sheet name");
}
catch (ArgumentException e)
{
// expected during successful Test
Assert.AreEqual("The workbook already contains a sheet of this name", e.Message);
}
//names cannot be blank or contain any of /\*?[]
String[] invalidNames = { "", "Sheet/", "Sheet\\",
"Sheet?", "Sheet*", "Sheet[", "Sheet]", "'Sheet'",
"My:Sheet" };
foreach (String sheetName in invalidNames)
{
try
{
wb.CreateSheet(sheetName);
Assert.Fail("should have thrown exception due to invalid sheet name: " + sheetName);
}
catch (ArgumentException)
{
// expected during successful Test
}
}
//still have 3 sheets
Assert.AreEqual(3, wb.NumberOfSheets);
//change the name of the 3rd sheet
wb.SetSheetName(2, "I Changed!");
//try to assign an invalid name to the 2nd sheet
try
{
wb.SetSheetName(1, "[I'm invalid]");
Assert.Fail("should have thrown exceptiuon due to invalid sheet name");
}
catch (ArgumentException)
{
// expected during successful Test
}
//try to assign an invalid name to the 2nd sheet
try
{
wb.CreateSheet(null);
Assert.Fail("should have thrown exceptiuon due to invalid sheet name");
}
catch (ArgumentException)
{
// expected during successful Test
}
try
{
wb.SetSheetName(2, null);
Assert.Fail("should have thrown exceptiuon due to invalid sheet name");
}
catch (ArgumentException)
{
// expected during successful Test
}
//check
Assert.AreEqual(0, wb.GetSheetIndex("sheet0"));
Assert.AreEqual(1, wb.GetSheetIndex("sheet1"));
Assert.AreEqual(2, wb.GetSheetIndex("I Changed!"));
Assert.AreSame(sheet0, wb.GetSheet("sheet0"));
Assert.AreSame(sheet1, wb.GetSheet("sheet1"));
Assert.AreSame(originalSheet, wb.GetSheet("I Changed!"));
Assert.IsNull(wb.GetSheet("unknown"));
//serialize and read again
wb = _testDataProvider.WriteOutAndReadBack(wb);
Assert.AreEqual(3, wb.NumberOfSheets);
Assert.AreEqual(0, wb.GetSheetIndex("sheet0"));
Assert.AreEqual(1, wb.GetSheetIndex("sheet1"));
Assert.AreEqual(2, wb.GetSheetIndex("I Changed!"));
}
public void SetCellStyleProperty()
{
IWorkbook wb = _testDataProvider.CreateWorkbook();
ISheet s = wb.CreateSheet();
IRow r = s.CreateRow(0);
ICell c = r.CreateCell(0);
// Add a border should create a new style
int styCnt1 = wb.NumCellStyles;
CellUtil.SetCellStyleProperty(c, CellUtil.BORDER_BOTTOM, BorderStyle.Thin);
int styCnt2 = wb.NumCellStyles;
Assert.AreEqual(styCnt1 + 1, styCnt2);
// Add same border to another cell, should not create another style
c = r.CreateCell(1);
CellUtil.SetCellStyleProperty(c, CellUtil.BORDER_BOTTOM, BorderStyle.Thin);
int styCnt3 = wb.NumCellStyles;
Assert.AreEqual(styCnt2, styCnt3);
wb.Close();
}
public void TestAutoCreateOtherCells()
{
IWorkbook workbook = _testDataProvider.CreateWorkbook();
ISheet sheet = workbook.CreateSheet("Sheet1");
IRow row1 = sheet.CreateRow(0);
ICell cellA1 = row1.CreateCell(0);
ICell cellB1 = row1.CreateCell(1);
String formula = "42";
sheet.SetArrayFormula(formula, CellRangeAddress.ValueOf("A1:B2"));
Assert.AreEqual(formula, cellA1.CellFormula);
Assert.AreEqual(formula, cellB1.CellFormula);
IRow row2 = sheet.GetRow(1);
Assert.IsNotNull(row2);
Assert.AreEqual(formula, row2.GetCell(0).CellFormula);
Assert.AreEqual(formula, row2.GetCell(1).CellFormula);
}
