public void TestModifyArrayCells_setFormulaResult()
{
IWorkbook workbook = _testDataProvider.CreateWorkbook();
ISheet sheet = workbook.CreateSheet();
//Single-cell array formula
ICellRange <ICell> srange =
sheet.SetArrayFormula("SUM(A4:A6,B4:B6)", CellRangeAddress.ValueOf("B5"));
ICell scell = srange.TopLeftCell;
Assert.AreEqual(CellType.Formula, scell.CellType);
Assert.AreEqual(0.0, scell.NumericCellValue);
scell.SetCellValue(1.1);
Assert.AreEqual(1.1, scell.NumericCellValue);
//multi-cell array formula
ICellRange <ICell> mrange =
sheet.SetArrayFormula("A1:A3*B1:B3", CellRangeAddress.ValueOf("C1:C3"));
foreach (ICell mcell in mrange)
{
Assert.AreEqual(CellType.Formula, mcell.CellType);
Assert.AreEqual(0.0, mcell.NumericCellValue);
double fmlaResult = 1.2;
mcell.SetCellValue(fmlaResult);
Assert.AreEqual(fmlaResult, mcell.NumericCellValue);
}
}
public void TestAddRemoveArrayFormulas_recordUpdates()
{
IWorkbook wb = new HSSFWorkbook();
ISheet s = wb.CreateSheet("Sheet1");
ICellRange <ICell> cr = s.SetArrayFormula("123", CellRangeAddress.ValueOf("B5:C6"));
Record[] recs;
int ix;
recs = RecordInspector.GetRecords(s, 0);
ix = FindRecordOfType(recs, typeof(ArrayRecord), 0);
ConfirmRecordClass(recs, ix - 1, typeof(FormulaRecord));
ConfirmRecordClass(recs, ix + 1, typeof(FormulaRecord));
ConfirmRecordClass(recs, ix + 2, typeof(FormulaRecord));
ConfirmRecordClass(recs, ix + 3, typeof(FormulaRecord));
// just one array record
Assert.IsTrue(FindRecordOfType(recs, typeof(ArrayRecord), ix + 1) < 0);
s.RemoveArrayFormula(cr.TopLeftCell);
// Make sure the array formula has been Removed properly
recs = RecordInspector.GetRecords(s, 0);
Assert.IsTrue(FindRecordOfType(recs, typeof(ArrayRecord), 0) < 0);
Assert.IsTrue(FindRecordOfType(recs, typeof(FormulaRecord), 0) < 0);
RowRecordsAggregate rra = ((HSSFSheet)s).Sheet.RowsAggregate;
SharedValueManager svm = TestSharedValueManager.ExtractFromRRA(rra);
if (svm.GetArrayRecord(4, 1) != null)
{
throw new AssertionException("Array record was not cleaned up properly.");
}
}
/**
* Use Excel conditional formatting to highlight duplicate entries in a column.
*/
static void FormatDuplicates(ISheet sheet)
{
sheet.CreateRow(0).CreateCell(0).SetCellValue("Code");
sheet.CreateRow(1).CreateCell(0).SetCellValue(4);
sheet.CreateRow(2).CreateCell(0).SetCellValue(3);
sheet.CreateRow(3).CreateCell(0).SetCellValue(6);
sheet.CreateRow(4).CreateCell(0).SetCellValue(3);
sheet.CreateRow(5).CreateCell(0).SetCellValue(5);
sheet.CreateRow(6).CreateCell(0).SetCellValue(8);
sheet.CreateRow(7).CreateCell(0).SetCellValue(0);
sheet.CreateRow(8).CreateCell(0).SetCellValue(2);
sheet.CreateRow(9).CreateCell(0).SetCellValue(8);
sheet.CreateRow(10).CreateCell(0).SetCellValue(6);
ISheetConditionalFormatting sheetCF = sheet.SheetConditionalFormatting;
// Condition 1: Formula Is =A2=A1 (White Font)
IConditionalFormattingRule rule1 = sheetCF.CreateConditionalFormattingRule("COUNTIF($A$2:$A$11,A2)>1");
IFontFormatting font = rule1.CreateFontFormatting();
font.SetFontStyle(false, true);
font.FontColorIndex = (IndexedColors.Blue.Index);
CellRangeAddress[] regions =
{
CellRangeAddress.ValueOf("A2:A11")
};
sheetCF.AddConditionalFormatting(regions, rule1);
sheet.GetRow(2).CreateCell(1).SetCellValue("<== Duplicates numbers in the column are highlighted. " +
"Condition: Formula Is =COUNTIF($A$2:$A$11,A2)>1 (Blue Font)");
}
/**
* Use Excel conditional formatting to highlight payments that are due in the next thirty days.
* In this example, Due dates are entered in cells A2:A4.
*/
static void Expiry(ISheet sheet)
{
ICellStyle style = sheet.Workbook.CreateCellStyle();
style.DataFormat = (short)BuiltinFormats.GetBuiltinFormat("d-mmm");
sheet.CreateRow(0).CreateCell(0).SetCellValue("Date");
sheet.CreateRow(1).CreateCell(0).SetCellFormula("TODAY()+29");
sheet.CreateRow(2).CreateCell(0).SetCellFormula("A2+1");
sheet.CreateRow(3).CreateCell(0).SetCellFormula("A3+1");
for (int rownum = 1; rownum <= 3; rownum++)
{
sheet.GetRow(rownum).GetCell(0).CellStyle = style;
}
ISheetConditionalFormatting sheetCF = sheet.SheetConditionalFormatting;
// Condition 1: Formula Is =A2=A1 (White Font)
IConditionalFormattingRule rule1 = sheetCF.CreateConditionalFormattingRule("AND(A2-TODAY()>=0,A2-TODAY()<=30)");
IFontFormatting font = rule1.CreateFontFormatting();
font.SetFontStyle(false, true);
font.FontColorIndex = IndexedColors.Blue.Index;
CellRangeAddress[] regions =
{
CellRangeAddress.ValueOf("A2:A4")
};
sheetCF.AddConditionalFormatting(regions, rule1);
sheet.GetRow(0).CreateCell(1).SetCellValue("Dates within the next 30 days are highlighted");
}
/**
* Use Excel conditional formatting to highlight items that are in a list on the worksheet.
*/
static void InList(ISheet sheet)
{
sheet.CreateRow(0).CreateCell(0).SetCellValue("Codes");
sheet.CreateRow(1).CreateCell(0).SetCellValue("AA");
sheet.CreateRow(2).CreateCell(0).SetCellValue("BB");
sheet.CreateRow(3).CreateCell(0).SetCellValue("GG");
sheet.CreateRow(4).CreateCell(0).SetCellValue("AA");
sheet.CreateRow(5).CreateCell(0).SetCellValue("FF");
sheet.CreateRow(6).CreateCell(0).SetCellValue("XX");
sheet.CreateRow(7).CreateCell(0).SetCellValue("CC");
sheet.GetRow(0).CreateCell(2).SetCellValue("Valid");
sheet.GetRow(1).CreateCell(2).SetCellValue("AA");
sheet.GetRow(2).CreateCell(2).SetCellValue("BB");
sheet.GetRow(3).CreateCell(2).SetCellValue("CC");
ISheetConditionalFormatting sheetCF = sheet.SheetConditionalFormatting;
// Condition 1: Formula Is =A2=A1 (White Font)
IConditionalFormattingRule rule1 = sheetCF.CreateConditionalFormattingRule("COUNTIF($C$2:$C$4,A2)");
IPatternFormatting fill1 = rule1.CreatePatternFormatting();
fill1.FillBackgroundColor = (IndexedColors.LightBlue.Index);
fill1.FillPattern = FillPattern.SolidForeground;
CellRangeAddress[] regions =
{
CellRangeAddress.ValueOf("A2:A8")
};
sheetCF.AddConditionalFormatting(regions, rule1);
sheet.GetRow(2).CreateCell(3).SetCellValue("<== Use Excel conditional formatting to highlight items that are in a list on the worksheet");
}
public void TestRepeatingColsRows()
{
HSSFWorkbook wb1 = new HSSFWorkbook();
HSSFSheet sheet = wb1.CreateSheet("Test Print Titles") as HSSFSheet;
HSSFRow row = sheet.CreateRow(0) as HSSFRow;
HSSFCell cell = row.CreateCell(1) as HSSFCell;
cell.SetCellValue(new HSSFRichTextString("hi"));
CellRangeAddress cra = CellRangeAddress.ValueOf("A1:B1");
sheet.RepeatingColumns = (cra);
sheet.RepeatingRows = (cra);
HSSFWorkbook wb2 = HSSFTestDataSamples.WriteOutAndReadBack(wb1);
sheet = wb2.GetSheetAt(0) as HSSFSheet;
Assert.AreEqual("A:B", sheet.RepeatingColumns.FormatAsString());
Assert.AreEqual("1:1", sheet.RepeatingRows.FormatAsString());
wb2.Close();
wb1.Close();
}
public void TestSetArrayFormula_multiCell()
{
IWorkbook workbook = _testDataProvider.CreateWorkbook();
ISheet sheet = workbook.CreateSheet();
// multi-cell formula
// rows 3-5 don't exist yet
Assert.IsNull(sheet.GetRow(3));
Assert.IsNull(sheet.GetRow(4));
Assert.IsNull(sheet.GetRow(5));
CellRangeAddress range = CellRangeAddress.ValueOf("C4:C6");
ICell[] cells = sheet.SetArrayFormula("SUM(A1:A3*B1:B3)", range).FlattenedCells;
Assert.AreEqual(3, cells.Length);
// sheet.SetArrayFormula Creates rows and cells for the designated range
Assert.AreSame(cells[0], sheet.GetRow(3).GetCell(2));
Assert.AreSame(cells[1], sheet.GetRow(4).GetCell(2));
Assert.AreSame(cells[2], sheet.GetRow(5).GetCell(2));
foreach (ICell acell in cells)
{
Assert.IsTrue(acell.IsPartOfArrayFormulaGroup);
Assert.AreEqual(CellType.Formula, acell.CellType);
Assert.AreEqual("SUM(A1:A3*B1:B3)", acell.CellFormula);
//retrieve the range and check it is the same
Assert.AreEqual(range.FormatAsString(), acell.ArrayFormulaRange.FormatAsString());
}
}
/**
* Use Excel conditional formatting to check for errors,
* and change the font colour to match the cell colour.
* In this example, if formula result is #DIV/0! then it will have white font colour.
*/
static void Errors(ISheet sheet)
{
sheet.CreateRow(0).CreateCell(0).SetCellValue(84);
sheet.CreateRow(1).CreateCell(0).SetCellValue(0);
sheet.CreateRow(2).CreateCell(0).SetCellFormula("ROUND(A1/A2,0)");
sheet.CreateRow(3).CreateCell(0).SetCellValue(0);
sheet.CreateRow(4).CreateCell(0).SetCellFormula("ROUND(A6/A4,0)");
sheet.CreateRow(5).CreateCell(0).SetCellValue(41);
ISheetConditionalFormatting sheetCF = sheet.SheetConditionalFormatting;
// Condition 1: Formula Is =ISERROR(C2) (White Font)
IConditionalFormattingRule rule1 = sheetCF.CreateConditionalFormattingRule("ISERROR(A1)");
IFontFormatting font = rule1.CreateFontFormatting();
font.FontColorIndex = (IndexedColors.White.Index);
CellRangeAddress[] regions =
{
CellRangeAddress.ValueOf("A1:A6")
};
sheetCF.AddConditionalFormatting(regions, rule1);
sheet.GetRow(2).CreateCell(1).SetCellValue("<== The error in this cell is hidden. Condition: Formula is =ISERROR(C2) (White Font)");
sheet.GetRow(4).CreateCell(1).SetCellValue("<== The error in this cell is hidden. Condition: Formula is =ISERROR(C2) (White Font)");
}
public void MergedCells()
{
IWorkbook workbook = _testDataProvider.CreateWorkbook();
FixFonts(workbook);
ISheet sheet = workbook.CreateSheet();
TrackColumnsForAutoSizingIfSXSSF(sheet);
IRow row = sheet.CreateRow(0);
sheet.AddMergedRegion(CellRangeAddress.ValueOf("A1:B1"));
ICell cell0 = row.CreateCell(0);
cell0.SetCellValue("Apache Software Foundation");
int defaulWidth = sheet.GetColumnWidth(0);
sheet.AutoSizeColumn(0);
// column is unChanged if merged regions are ignored (Excel like behavior)
Assert.AreEqual(defaulWidth, sheet.GetColumnWidth(0));
sheet.AutoSizeColumn(0, true);
Assert.IsTrue(sheet.GetColumnWidth(0) > defaulWidth);
workbook.Close();
}
public void ShouldNotBeAbleToCreateArrayFormulaOnPreexistingMergedRegion()
{
/*
* m = merged region
* f = array formula
* fm = cell belongs to a merged region and an array formula (illegal, that's what this tests for)
*
* A B C
* 1 f f
* 2 fm fm
* 3 f f
*/
IWorkbook workbook = _testDataProvider.CreateWorkbook();
ISheet sheet = workbook.CreateSheet();
CellRangeAddress mergedRegion = CellRangeAddress.ValueOf("B2:C2");
sheet.AddMergedRegion(mergedRegion);
CellRangeAddress arrayFormula = CellRangeAddress.ValueOf("C1:C3");
Assume.That(mergedRegion.Intersects(arrayFormula));
Assume.That(arrayFormula.Intersects(mergedRegion));
try
{
sheet.SetArrayFormula("SUM(A1:A3)", arrayFormula);
Assert.Fail("expected exception: should not be able to create an array formula that intersects with a merged region");
}
catch (InvalidOperationException e)
{
// expected
}
workbook.Close();
}
/**
* Highlight cells based on their values
*/
static void SameCell(ISheet sheet)
{
sheet.CreateRow(0).CreateCell(0).SetCellValue(84);
sheet.CreateRow(1).CreateCell(0).SetCellValue(74);
sheet.CreateRow(2).CreateCell(0).SetCellValue(50);
sheet.CreateRow(3).CreateCell(0).SetCellValue(51);
sheet.CreateRow(4).CreateCell(0).SetCellValue(49);
sheet.CreateRow(5).CreateCell(0).SetCellValue(41);
ISheetConditionalFormatting sheetCF = sheet.SheetConditionalFormatting;
// Condition 1: Cell Value Is greater than 70 (Blue Fill)
IConditionalFormattingRule rule1 = sheetCF.CreateConditionalFormattingRule(ComparisonOperator.GreaterThan, "70");
IPatternFormatting fill1 = rule1.CreatePatternFormatting();
fill1.FillBackgroundColor = (IndexedColors.Blue.Index);
fill1.FillPattern = FillPattern.SolidForeground;
// Condition 2: Cell Value Is less than 50 (Green Fill)
IConditionalFormattingRule rule2 = sheetCF.CreateConditionalFormattingRule(ComparisonOperator.LessThan, "50");
IPatternFormatting fill2 = rule2.CreatePatternFormatting();
fill2.FillBackgroundColor = (IndexedColors.Green.Index);
fill2.FillPattern = FillPattern.SolidForeground;
CellRangeAddress[] regions =
{
CellRangeAddress.ValueOf("A1:A6")
};
sheetCF.AddConditionalFormatting(regions, rule1, rule2);
sheet.GetRow(0).CreateCell(2).SetCellValue("<== Condition 1: Cell Value is greater than 70 (Blue Fill)");
sheet.GetRow(4).CreateCell(2).SetCellValue("<== Condition 2: Cell Value is less than 50 (Green Fill)");
}
/// <summary>
/// Creates a non shared formula from the shared formula counterpart
/// </summary>
/// <param name="si">Shared Group Index</param>
/// <param name="fpb"></param>
/// <returns>non shared formula created for the given shared formula and this cell</returns>
private String ConvertSharedFormula(int si, XSSFEvaluationWorkbook fpb)
{
XSSFSheet sheet = (XSSFSheet)Sheet;
CT_CellFormula f = sheet.GetSharedFormula(si);
if (f == null)
{
throw new InvalidOperationException(
"Master cell of a shared formula with sid=" + si + " was not found");
}
String sharedFormula = f.Value;
//Range of cells which the shared formula applies to
String sharedFormulaRange = f.@ref;
CellRangeAddress ref1 = CellRangeAddress.ValueOf(sharedFormulaRange);
int sheetIndex = sheet.Workbook.GetSheetIndex(sheet);
SharedFormula sf = new SharedFormula(SpreadsheetVersion.EXCEL2007);
Ptg[] ptgs = FormulaParser.Parse(sharedFormula, fpb, FormulaType.Cell, sheetIndex, RowIndex);
Ptg[] fmla = sf.ConvertSharedFormulas(ptgs,
RowIndex - ref1.FirstRow, ColumnIndex - ref1.FirstColumn);
return(FormulaRenderer.ToFormulaString(fpb, fmla));
}
public void TestFormulaCache()
{
IWorkbook wb = new XSSFWorkbook();
ISheet sheet = new SheetBuilder(wb, plotData).Build();
IDrawing Drawing = sheet.CreateDrawingPatriarch();
IClientAnchor anchor = Drawing.CreateAnchor(0, 0, 0, 0, 1, 1, 10, 30);
IChart chart = Drawing.CreateChart(anchor);
IChartAxis bottomAxis = chart.ChartAxisFactory.CreateValueAxis(AxisPosition.Bottom);
IChartAxis leftAxis = chart.ChartAxisFactory.CreateValueAxis(AxisPosition.Left);
IScatterChartData scatterChartData =
chart.ChartDataFactory.CreateScatterChartData();
DataMarker xMarker = new DataMarker(sheet, CellRangeAddress.ValueOf("A1:E1"));
DataMarker yMarker = new DataMarker(sheet, CellRangeAddress.ValueOf("A2:E2"));
IScatterChartSerie serie = scatterChartData.AddSerie(xMarker, yMarker);
chart.Plot(scatterChartData, bottomAxis, leftAxis);
XSSFScatterChartData.Serie xssfScatterSerie =
(XSSFScatterChartData.Serie)serie;
XSSFNumberCache yCache = xssfScatterSerie.LastCalculatedYCache;
Assert.AreEqual(5, yCache.PointCount);
Assert.AreEqual(4.0, yCache.GetValueAt(3), 0.00001);
Assert.AreEqual(16.0, yCache.GetValueAt(5), 0.00001);
}
void ApplyOutcomeFormattingRules(int i)
{
string range = String.Format("D2:D{0}", i);
var region = new CellRangeAddress[] { CellRangeAddress.ValueOf(range) };
sheet.SheetConditionalFormatting.AddConditionalFormatting(region, outcomeFormattingRules);
}
void ApplyFailedFormattingRules(int rowFromInclusive, int rowtoInclusive)
{
string range = String.Format("I{0}:I{1}", rowFromInclusive, rowtoInclusive);
var region = new CellRangeAddress[] { CellRangeAddress.ValueOf(range) };
sheet.SheetConditionalFormatting.AddConditionalFormatting(region, failedFormattingRules);
}
public void TestIobExceptionOnInvalidIndex()
{
IWorkbook wb = new HSSFWorkbook();
ISheet sheet = new SheetBuilder(wb, numericCells).Build();
CellRangeAddress rangeAddress = CellRangeAddress.ValueOf("A2:E2");
IChartDataSource <double> numDataSource = DataSources.FromNumericCellRange(sheet, rangeAddress);
IndexOutOfRangeException exception = null;
try
{
numDataSource.GetPointAt(-1);
}
catch (IndexOutOfRangeException e)
{
exception = e;
}
Assert.IsNotNull(exception);
exception = null;
try
{
numDataSource.GetPointAt(numDataSource.PointCount);
}
catch (IndexOutOfRangeException e)
{
exception = e;
}
Assert.IsNotNull(exception);
}
public void TestSetRepeatingRowsAndColumns()
{
IWorkbook wb = _testDataProvider.CreateWorkbook();
ISheet sheet1 = wb.CreateSheet("Sheet1");
ISheet sheet2 = wb.CreateSheet("Sheet2");
ISheet sheet3 = wb.CreateSheet("Sheet3");
CheckRepeatingRowsAndColumns(sheet1, null, null);
sheet1.RepeatingRows = (CellRangeAddress.ValueOf("4:5"));
sheet2.RepeatingColumns = (CellRangeAddress.ValueOf("A:C"));
sheet3.RepeatingRows = (CellRangeAddress.ValueOf("1:4"));
sheet3.RepeatingColumns = (CellRangeAddress.ValueOf("A:A"));
CheckRepeatingRowsAndColumns(sheet1, "4:5", null);
CheckRepeatingRowsAndColumns(sheet2, null, "A:C");
CheckRepeatingRowsAndColumns(sheet3, "1:4", "A:A");
// write out, read back, and test refrain...
wb = _testDataProvider.WriteOutAndReadBack(wb);
sheet1 = wb.GetSheetAt(0);
sheet2 = wb.GetSheetAt(1);
sheet3 = wb.GetSheetAt(2);
CheckRepeatingRowsAndColumns(sheet1, "4:5", null);
CheckRepeatingRowsAndColumns(sheet2, null, "A:C");
CheckRepeatingRowsAndColumns(sheet3, "1:4", "A:A");
// check removing repeating rows and columns
sheet3.RepeatingRows = (null);
CheckRepeatingRowsAndColumns(sheet3, null, "A:A");
sheet3.RepeatingColumns = (null);
CheckRepeatingRowsAndColumns(sheet3, null, null);
}
public void TestFindBuiltInNameRecord()
{
// TestRRaC has multiple (3) built-in name records
// The second print titles name record has SheetNumber==4
HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("TestRRaC.xls");
NameRecord nr;
Assert.AreEqual(3, wb.Workbook.NumNames);
nr = wb.Workbook.GetNameRecord(2);
// TODO - render full row and full column refs properly
Assert.AreEqual("Sheet2!$A$1:$IV$1", HSSFFormulaParser.ToFormulaString(wb, nr.NameDefinition)); // 1:1
try
{
wb.GetSheetAt(3).RepeatingRows = (CellRangeAddress.ValueOf("9:12"));
wb.GetSheetAt(3).RepeatingColumns = (CellRangeAddress.ValueOf("E:F"));
}
catch (Exception e)
{
if (e.Message.Equals("Builtin (7) already exists for sheet (4)"))
{
// there was a problem in the code which locates the existing print titles name record
throw new Exception("Identified bug 45720b");
}
throw e;
}
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
Assert.AreEqual(3, wb.Workbook.NumNames);
nr = wb.Workbook.GetNameRecord(2);
Assert.AreEqual("Sheet2!E:F,Sheet2!$A$9:$IV$12", HSSFFormulaParser.ToFormulaString(wb, nr.NameDefinition)); // E:F,9:12
}
/**
* Use Excel conditional formatting to hide the duplicate values,
* and make the list easier to read. In this example, when the table is sorted by Region,
* the second (and subsequent) occurences of each region name will have white font colour.
*/
static void HideDupplicates(ISheet sheet)
{
sheet.CreateRow(0).CreateCell(0).SetCellValue("City");
sheet.CreateRow(1).CreateCell(0).SetCellValue("Boston");
sheet.CreateRow(2).CreateCell(0).SetCellValue("Boston");
sheet.CreateRow(3).CreateCell(0).SetCellValue("Chicago");
sheet.CreateRow(4).CreateCell(0).SetCellValue("Chicago");
sheet.CreateRow(5).CreateCell(0).SetCellValue("New York");
ISheetConditionalFormatting sheetCF = sheet.SheetConditionalFormatting;
// Condition 1: Formula Is =A2=A1 (White Font)
IConditionalFormattingRule rule1 = sheetCF.CreateConditionalFormattingRule("A2=A1");
IFontFormatting font = rule1.CreateFontFormatting();
font.FontColorIndex = IndexedColors.White.Index;
CellRangeAddress[] regions =
{
CellRangeAddress.ValueOf("A2:A6")
};
sheetCF.AddConditionalFormatting(regions, rule1);
sheet.GetRow(1).CreateCell(1).SetCellValue("<== the second (and subsequent) " +
"occurences of each region name will have white font colour. " +
"Condition: Formula Is =A2=A1 (White Font)");
}
public void TestXSSFSetArrayFormula_multiCell()
{
ICellRange <ICell> cells;
String formula2 = "456";
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)workbook.CreateSheet();
CellRangeAddress range = CellRangeAddress.ValueOf("C4:C6");
cells = sheet.SetArrayFormula(formula2, range);
Assert.AreEqual(3, cells.Size);
// sheet.SetArrayFormula Creates rows and cells for the designated range
/*
* From the spec:
* For a multi-cell formula, the c elements for all cells except the top-left
* cell in that range shall not have an f element;
*/
// Check that each cell exists and that the formula text is Set correctly on the first cell
XSSFCell firstCell = (XSSFCell)cells.TopLeftCell;
ConfirmArrayFormulaCell(firstCell, "C4", formula2, "C4:C6");
ConfirmArrayFormulaCell(cells.GetCell(1, 0), "C5");
ConfirmArrayFormulaCell(cells.GetCell(2, 0), "C6");
Assert.AreSame(firstCell, sheet.GetFirstCellInArrayFormula(firstCell));
workbook.Close();
}
protected void ApplyPercentageFormatting(int column, int rowFromInclusive, int rowtoInclusive)
{
string colString = CellReference.ConvertNumToColString(column);
string range = String.Format("{0}{1}:{0}{2}", colString, rowFromInclusive, rowtoInclusive);
var region = new CellRangeAddress[] { CellRangeAddress.ValueOf(range) };
sheet.SheetConditionalFormatting.AddConditionalFormatting(region, PercentageFormattingRules);
}
public static void CopyRange(ISheet source, String range, ISheet dest, EPoint start)
{
CellRangeAddress rangeAddress = CellRangeAddress.ValueOf(range);
for (Int32 i = rangeAddress.FirstRow; i < rangeAddress.LastRow; ++i)
{
// source.GetMergedRegion
}
}
public void TestMergeCellRanges()
{
CellRangeAddress cr1 = CellRangeAddress.ValueOf("A1:B1");
CellRangeAddress cr2 = CellRangeAddress.ValueOf("A2:B2");
CellRangeAddress[] cr3 = CellRangeUtil.MergeCellRanges(new CellRangeAddress[] { cr1, cr2 });
Assert.AreEqual(1, cr3.Length);
Assert.AreEqual("A1:B2", cr3[0].FormatAsString());
}
public void TestAutoFilter()
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sh = (HSSFSheet)wb.CreateSheet();
InternalWorkbook iwb = wb.Workbook;
InternalSheet ish = sh.Sheet;
Assert.IsNull(iwb.GetSpecificBuiltinRecord(NameRecord.BUILTIN_FILTER_DB, 1));
Assert.IsNull(ish.FindFirstRecordBySid(AutoFilterInfoRecord.sid));
CellRangeAddress range = CellRangeAddress.ValueOf("A1:B10");
sh.SetAutoFilter(range);
NameRecord name = iwb.GetSpecificBuiltinRecord(NameRecord.BUILTIN_FILTER_DB, 1);
Assert.IsNotNull(name);
// The built-in name for auto-filter must consist of a single Area3d Ptg.
Ptg[] ptg = name.NameDefinition;
Assert.AreEqual(1, ptg.Length, "The built-in name for auto-filter must consist of a single Area3d Ptg");
Assert.IsTrue(ptg[0] is Area3DPtg, "The built-in name for auto-filter must consist of a single Area3d Ptg");
Area3DPtg aref = (Area3DPtg)ptg[0];
Assert.AreEqual(range.FirstColumn, aref.FirstColumn);
Assert.AreEqual(range.FirstRow, aref.FirstRow);
Assert.AreEqual(range.LastColumn, aref.LastColumn);
Assert.AreEqual(range.LastRow, aref.LastRow);
// verify AutoFilterInfoRecord
AutoFilterInfoRecord afilter = (AutoFilterInfoRecord)ish.FindFirstRecordBySid(AutoFilterInfoRecord.sid);
Assert.IsNotNull(afilter);
Assert.AreEqual(2, afilter.NumEntries); //filter covers two columns
HSSFPatriarch dr = (HSSFPatriarch)sh.DrawingPatriarch;
Assert.IsNotNull(dr);
HSSFSimpleShape comboBoxShape = (HSSFSimpleShape)dr.Children[0];
Assert.AreEqual(comboBoxShape.ShapeType, HSSFSimpleShape.OBJECT_TYPE_COMBO_BOX);
Assert.IsNull(ish.FindFirstRecordBySid(ObjRecord.sid)); // ObjRecord will appear after serializetion
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
sh = (HSSFSheet)wb.GetSheetAt(0);
ish = sh.Sheet;
ObjRecord objRecord = (ObjRecord)ish.FindFirstRecordBySid(ObjRecord.sid);
IList subRecords = objRecord.SubRecords;
Assert.AreEqual(3, subRecords.Count);
Assert.IsTrue(subRecords[0] is CommonObjectDataSubRecord);
Assert.IsTrue(subRecords[1] is FtCblsSubRecord); // must be present, see Bug 51481
Assert.IsTrue(subRecords[2] is LbsDataSubRecord);
}
public void TestBug55723d_RowsOver65k()
{
HSSFWorkbook wb = new HSSFWorkbook();
ISheet sheet = wb.CreateSheet();
CellRangeAddress range = CellRangeAddress.ValueOf("A4:B75000");
IAutoFilter filter = sheet.SetAutoFilter(range);
Assert.IsNotNull(filter);
}
// public void testResolveRangeOverlap() {
// resolveRangeOverlapTest("C1:D2", "C2:C3");
// }
private void CellRangeTest(string[] input, params string[] expectedOutput)
{
CellRangeAddress[] inputArr = new CellRangeAddress[input.Length];
for (int i = 0; i < input.Length; i++)
{
inputArr[i] = CellRangeAddress.ValueOf(input[i]);
}
CellRangeAddress[] result = CellRangeUtil.MergeCellRanges(inputArr);
VerifyExpectedResult(result, expectedOutput);
}
public void testModifyArrayCells_mergeCellsMulti()
{
IWorkbook workbook = _testDataProvider.CreateWorkbook();
ISheet sheet = workbook.CreateSheet();
int expectedNumMergedRegions = 0;
Assert.AreEqual(expectedNumMergedRegions, sheet.NumMergedRegions);
// we cannot merge cells included in an array formula
sheet.SetArrayFormula("A1:A4*B1:B4", CellRangeAddress.ValueOf("C2:F5"));
foreach (String ref1 in Arrays.AsList(
"C2:F5", // identity
"D3:E4", "B1:G6", // contains
"B1:C2", "F1:G2", "F5:G6", "B5:C6", // 1x1 corner intersection
"B1:C6", "B1:G2", "F1:G6", "B5:G6", // 1-row/1-column intersection
"B1:D3", "E1:G3", "E4:G6", "B4:D6", // 2x2 corner intersection
"B1:D6", "B1:G3", "E1:G6", "B4:G6" // 2-row/2-column intersection
))
{
CellRangeAddress cra = CellRangeAddress.ValueOf(ref1);
try
{
sheet.AddMergedRegion(cra);
Assert.Fail("expected exception with ref " + ref1);
}
catch (InvalidOperationException e)
{
String msg = "The range " + cra.FormatAsString() + " intersects with a multi-cell array formula. You cannot merge cells of an array.";
Assert.AreEqual(msg, e.Message);
}
}
//the number of merged regions remains the same
Assert.AreEqual(expectedNumMergedRegions, sheet.NumMergedRegions);
// we can merge non-intersecting cells
foreach (String ref1 in Arrays.AsList(
"C1:F1", //above
"G2:G5", //right
"C6:F6", //bottom
"B2:B5", "H7:J9"))
{
CellRangeAddress cra = CellRangeAddress.ValueOf(ref1);
try
{
sheet.AddMergedRegion(cra);
expectedNumMergedRegions++;
Assert.AreEqual(expectedNumMergedRegions, sheet.NumMergedRegions);
}
catch (InvalidOperationException e)
{
Assert.Fail("did not expect exception with ref: " + ref1 + "\n" + e.Message);
}
}
workbook.Close();
}
public void TestFormatAsString()
{
CellRangeAddress ref1 = new CellRangeAddress(1, 2, 3, 4);
Assert.AreEqual("D2:E3", ref1.FormatAsString());
Assert.AreEqual("D2:E3", CellRangeAddress.ValueOf(ref1.FormatAsString()).FormatAsString());
Assert.AreEqual("sheet1!$D$2:$E$3", ref1.FormatAsString("sheet1", true));
Assert.AreEqual("sheet1!$D$2:$E$3", CellRangeAddress.ValueOf(ref1.FormatAsString()).FormatAsString("sheet1", true));
Assert.AreEqual("sheet1!$D$2:$E$3", CellRangeAddress.ValueOf(ref1.FormatAsString("sheet1", true)).FormatAsString("sheet1", true));
Assert.AreEqual("sheet1!D2:E3", ref1.FormatAsString("sheet1", false));
Assert.AreEqual("sheet1!D2:E3", CellRangeAddress.ValueOf(ref1.FormatAsString()).FormatAsString("sheet1", false));
Assert.AreEqual("sheet1!D2:E3", CellRangeAddress.ValueOf(ref1.FormatAsString("sheet1", false)).FormatAsString("sheet1", false));
Assert.AreEqual("D2:E3", ref1.FormatAsString(null, false));
Assert.AreEqual("D2:E3", CellRangeAddress.ValueOf(ref1.FormatAsString()).FormatAsString(null, false));
Assert.AreEqual("D2:E3", CellRangeAddress.ValueOf(ref1.FormatAsString(null, false)).FormatAsString(null, false));
Assert.AreEqual("$D$2:$E$3", ref1.FormatAsString(null, true));
Assert.AreEqual("$D$2:$E$3", CellRangeAddress.ValueOf(ref1.FormatAsString()).FormatAsString(null, true));
Assert.AreEqual("$D$2:$E$3", CellRangeAddress.ValueOf(ref1.FormatAsString(null, true)).FormatAsString(null, true));
ref1 = new CellRangeAddress(-1, -1, 3, 4);
Assert.AreEqual("D:E", ref1.FormatAsString());
Assert.AreEqual("sheet1!$D:$E", CellRangeAddress.ValueOf(ref1.FormatAsString()).FormatAsString("sheet1", true));
Assert.AreEqual("sheet1!$D:$E", CellRangeAddress.ValueOf(ref1.FormatAsString("sheet1", true)).FormatAsString("sheet1", true));
Assert.AreEqual("$D:$E", CellRangeAddress.ValueOf(ref1.FormatAsString()).FormatAsString(null, true));
Assert.AreEqual("$D:$E", CellRangeAddress.ValueOf(ref1.FormatAsString(null, true)).FormatAsString(null, true));
Assert.AreEqual("sheet1!D:E", CellRangeAddress.ValueOf(ref1.FormatAsString()).FormatAsString("sheet1", false));
Assert.AreEqual("sheet1!D:E", CellRangeAddress.ValueOf(ref1.FormatAsString("sheet1", false)).FormatAsString("sheet1", false));
ref1 = new CellRangeAddress(1, 2, -1, -1);
Assert.AreEqual("2:3", ref1.FormatAsString());
Assert.AreEqual("sheet1!$2:$3", CellRangeAddress.ValueOf(ref1.FormatAsString()).FormatAsString("sheet1", true));
Assert.AreEqual("sheet1!$2:$3", CellRangeAddress.ValueOf(ref1.FormatAsString("sheet1", true)).FormatAsString("sheet1", true));
Assert.AreEqual("$2:$3", CellRangeAddress.ValueOf(ref1.FormatAsString()).FormatAsString(null, true));
Assert.AreEqual("$2:$3", CellRangeAddress.ValueOf(ref1.FormatAsString(null, true)).FormatAsString(null, true));
Assert.AreEqual("sheet1!2:3", CellRangeAddress.ValueOf(ref1.FormatAsString()).FormatAsString("sheet1", false));
Assert.AreEqual("sheet1!2:3", CellRangeAddress.ValueOf(ref1.FormatAsString("sheet1", false)).FormatAsString("sheet1", false));
ref1 = new CellRangeAddress(1, 1, 2, 2);
Assert.AreEqual("C2", ref1.FormatAsString());
Assert.AreEqual("sheet1!$C$2", CellRangeAddress.ValueOf(ref1.FormatAsString()).FormatAsString("sheet1", true));
Assert.AreEqual("sheet1!$C$2", CellRangeAddress.ValueOf(ref1.FormatAsString("sheet1", true)).FormatAsString("sheet1", true));
Assert.AreEqual("$C$2", CellRangeAddress.ValueOf(ref1.FormatAsString()).FormatAsString(null, true));
Assert.AreEqual("$C$2", CellRangeAddress.ValueOf(ref1.FormatAsString(null, true)).FormatAsString(null, true));
Assert.AreEqual("sheet1!C2", CellRangeAddress.ValueOf(ref1.FormatAsString()).FormatAsString("sheet1", false));
Assert.AreEqual("sheet1!C2", CellRangeAddress.ValueOf(ref1.FormatAsString("sheet1", false)).FormatAsString("sheet1", false));
// is this a valid Address?
ref1 = new CellRangeAddress(-1, -1, -1, -1);
Assert.AreEqual(":", ref1.FormatAsString());
}
public void EachRowShouldHaveSameNumberOfCellsAsColumns()
{
var rows = this.excelRowParser.ParseRows(this.columns, this.workbook[0], CellRangeAddress.ValueOf(this.name.RefersToFormula));
var columnCount = this.columns.Count;
foreach (var row in rows)
{
Assert.AreEqual(columnCount, row.Count);
}
}
/**
* @return array of <tt>CellRangeAddress</tt>s. Never <code>null</code>
*/
public CellRangeAddress[] GetFormattingRanges()
{
List <CellRangeAddress> lst = new List <CellRangeAddress>();
String[] regions = _cf.sqref.Split(new char[] { ' ' });
for (int i = 0; i < regions.Length; i++)
{
lst.Add(CellRangeAddress.ValueOf(regions[i]));
}
return(lst.ToArray());
}