public void TestHSSFSetArrayFormula_SingleCell()
{
IWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet("Sheet1");
CellRangeAddress range = new CellRangeAddress(2, 2, 2, 2);
ICell[] cells = sheet.SetArrayFormula("SUM(C11:C12*D11:D12)", range).FlattenedCells;
Assert.AreEqual(1, cells.Length);
// sheet.SetArrayFormula Creates rows and cells for the designated range
Assert.IsNotNull(sheet.GetRow(2));
ICell cell = sheet.GetRow(2).GetCell(2);
Assert.IsNotNull(cell);
Assert.IsTrue(cell.IsPartOfArrayFormulaGroup);
//retrieve the range and check it is the same
Assert.AreEqual(range.FormatAsString(), cell.ArrayFormulaRange.FormatAsString());
FormulaRecordAggregate agg = (FormulaRecordAggregate)(((HSSFCell)cell).CellValueRecord);
Assert.AreEqual(range.FormatAsString(), agg.GetArrayFormulaRange().FormatAsString());
Assert.IsTrue(agg.IsPartOfArrayFormula);
}
public void Test_47701()
{
byte[] data = HexRead.ReadFromString(
"15, 00, 12, 00, 12, 00, 02, 00, 11, 20, " +
"00, 00, 00, 00, 80, 3D, 03, 05, 00, 00, " +
"00, 00, 0C, 00, 14, 00, 00, 00, 00, 00, " +
"00, 00, 00, 00, 00, 00, 01, 00, 0A, 00, " +
"00, 00, 10, 00, 01, 00, 13, 00, EE, 1F, " +
"10, 00, 09, 00, 40, 9F, 74, 01, 25, 09, " +
"00, 0C, 00, 07, 00, 07, 00, 07, 04, 00, " +
"00, 00, 08, 00, 00, 00");
RecordInputStream in1 = TestcaseRecordInputStream.Create(ObjRecord.sid, data);
// check read OK
ObjRecord record = new ObjRecord(in1);
Assert.AreEqual(3, record.SubRecords.Count);
SubRecord sr = record.SubRecords[(2)];
Assert.IsTrue(sr is LbsDataSubRecord);
LbsDataSubRecord lbs = (LbsDataSubRecord)sr;
Assert.AreEqual(4, lbs.NumberOfItems);
Assert.IsTrue(lbs.Formula is AreaPtg);
AreaPtg ptg = (AreaPtg)lbs.Formula;
CellRangeAddress range = new CellRangeAddress(
ptg.FirstRow, ptg.LastRow, ptg.FirstColumn, ptg.LastColumn);
Assert.AreEqual("H10:H13", range.FormatAsString());
// check that it re-Serializes to the same data
byte[] ser = record.Serialize();
TestcaseRecordInputStream.ConfirmRecordEncoding(ObjRecord.sid, data, ser);
}
/// <summary>
/// Ghi danh mục vào một column trong sheet
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sheet">Sheet cần ghi danh mục</param>
/// <param name="lst">Danh sách danh mục</param>
/// <param name="textField">Tên cột nội dung</param>
/// <param name="col">Cột ghi, Bắt đầu từ 0</param>
/// <param name="nameText">Tên của vùng text</param>
/// <exception cref="Field Not Found"></exception>
/// <modified>
/// Author Date comment
/// anhhn 15/07/2012 Tạo mới
/// </modified>
public static void WriteDanhMucByCol <T>(this ISheet sheet, List <T> lst, string textField, int col, string nameText)
{
if (lst == null || lst.Count == 0)
{
return;
}
PropertyInfo[] p = typeof(T).GetProperties();
PropertyInfo pText = p.First(a => a.Name == textField);
if (pText == null)
{
throw new Exception("TextField không tồn tại");
}
for (int i = 0; i < lst.Count; i++)
{
GetCreateRow(sheet, i).CreateCell(col).SetCellValue(pText.GetValue(lst[i], null).ToString());
}
IName hsName = sheet.Workbook.GetName(nameText);
if (hsName == null)
{
hsName = sheet.Workbook.CreateName();
hsName.NameName = nameText;
}
CellRangeAddress cellRange = new CellRangeAddress(0, lst.Count, col, col);
hsName.RefersToFormula = cellRange.FormatAsString(sheet.SheetName, true);
}
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());
}
}
public override String ToString()
{
StringBuilder buffer = new StringBuilder();
buffer.Append("[HYPERLINK RECORD]\n");
buffer.Append(" .range = ").Append(_range.FormatAsString()).Append("\n");
buffer.Append(" .guid = ").Append(_guid.FormatAsString()).Append("\n");
buffer.Append(" .linkOpts = ").Append(HexDump.IntToHex(this._linkOpts)).Append("\n");
buffer.Append(" .label = ").Append(Label).Append("\n");
if ((_linkOpts & HLINK_TARGET_FRAME) != 0)
{
buffer.Append(" .targetFrame= ").Append(TargetFrame).Append("\n");
}
if ((_linkOpts & HLINK_URL) != 0 && _moniker != null)
{
buffer.Append(" .moniker = ").Append(_moniker.FormatAsString()).Append("\n");
}
if ((_linkOpts & HLINK_PLACE) != 0)
{
buffer.Append(" .targetFrame= ").Append(TextMark).Append("\n");
}
buffer.Append(" .address = ").Append(Address).Append("\n");
buffer.Append("[/HYPERLINK RECORD]\n");
return(buffer.ToString());
}
internal void SetCellArrayFormula(String formula, CellRangeAddress range)
{
SetFormula(formula, FormulaType.Array);
CT_CellFormula cellFormula = _cell.f;
cellFormula.t = (ST_CellFormulaType.array);
cellFormula.@ref = (range.FormatAsString());
}
internal void SetCellArrayFormula(string formula, CellRangeAddress range)
{
this.SetFormula(formula, FormulaType.ARRAY);
CT_CellFormula f = this._cell.f;
f.t = ST_CellFormulaType.array;
f.@ref = range.FormatAsString();
}
public void TestShiftWithMergedRegions()
{
IWorkbook wb = _testDataProvider.CreateWorkbook();
ISheet sheet = wb.CreateSheet();
IRow row = sheet.CreateRow(0);
row.CreateCell(0).SetCellValue(1.1);
row.CreateCell(1).SetCellValue(2.2);
CellRangeAddress region = new CellRangeAddress(0, 0, 0, 2);
Assert.AreEqual("A1:C1", region.FormatAsString());
sheet.AddMergedRegion(region);
sheet.ShiftRows(0, 1, 2);
region = sheet.GetMergedRegion(0);
Assert.AreEqual("A3:C3", region.FormatAsString());
}
private static void assertNotIntersects(CellRangeAddress regionA, CellRangeAddress regionB)
{
if ((regionA.Intersects(regionB) || regionB.Intersects(regionA)))
{
String A = regionA.FormatAsString();
String B = regionB.FormatAsString();
Assert.Fail("expected: regions " + A + " and " + B + " do not intersect");
}
}
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 TestRemoveArrayFormula()
{
IWorkbook workbook = _testDataProvider.CreateWorkbook();
ISheet sheet = workbook.CreateSheet();
CellRangeAddress range = new CellRangeAddress(3, 5, 2, 2);
Assert.AreEqual("C4:C6", range.FormatAsString());
ICellRange <ICell> cr = sheet.SetArrayFormula("SUM(A1:A3*B1:B3)", range);
Assert.AreEqual(3, cr.Size);
// remove the formula cells in C4:C6
ICellRange <ICell> dcells = sheet.RemoveArrayFormula(cr.TopLeftCell);
// RemoveArrayFormula should return the same cells as SetArrayFormula
Assert.IsTrue(Arrays.Equals(cr.FlattenedCells, dcells.FlattenedCells));
foreach (ICell acell in cr)
{
Assert.IsFalse(acell.IsPartOfArrayFormulaGroup);
Assert.AreEqual(CellType.Blank, acell.CellType);
}
// cells C4:C6 are not included in array formula,
// invocation of sheet.RemoveArrayFormula on any of them throws ArgumentException
foreach (ICell acell in cr)
{
try
{
sheet.RemoveArrayFormula(acell);
Assert.Fail("expected exception");
}
catch (ArgumentException e)
{
String ref1 = new CellReference(acell).FormatAsString();
Assert.AreEqual("Cell " + ref1 + " is not part of an array formula.", e.Message);
}
}
workbook.Close();
}
/// <summary>
/// Ghi danh mục vào một row trong sheet
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sheet">Sheet cần ghi danh mục</param>
/// <param name="lst">Danh sách danh mục</param>
/// <param name="textField">Tên cột nội dung</param>
/// <param name="valueField">Tên cột dữ liệu</param>
/// <param name="row">Dòng ghi, Bắt đầu từ 0</param>
/// <param name="nameText">Tên của vùng text</param>
/// <param name="nameValue">Tên của vùng value</param>
/// <exception cref="Field Not Found"></exception>
/// <modified>
/// Author Date comment
/// anhhn 15/07/2012 Tạo mới
/// </modified>
public static void WriteDanhMucByRow <T>(this ISheet sheet, List <T> lst, string textField, string valueField, int row, string nameText, string nameValue)
{
if (lst == null || lst.Count == 0)
{
return;
}
PropertyInfo[] p = typeof(T).GetProperties();
PropertyInfo pText = p.First(a => a.Name == textField);
PropertyInfo pValue = p.First(a => a.Name == valueField);
if (pText == null || pValue == null)
{
throw new Exception("TextField hoạc ValueField không tồn tại");
}
IRow rowText = GetCreateRow(sheet, row);
IRow rowID = GetCreateRow(sheet, row + 1);
for (int i = 0; i < lst.Count; i++)
{
rowText.CreateCell(i).SetCellValue(pText.GetValue(lst[i], null).ToString());
rowID.CreateCell(i).SetCellValue(pValue.GetValue(lst[i], null).ToString());
}
IName hsName = sheet.Workbook.GetName(nameText);
if (hsName == null)
{
hsName = sheet.Workbook.CreateName();
hsName.NameName = nameText;
}
CellRangeAddress cellRange = new CellRangeAddress(row, row, 0, lst.Count);
hsName.RefersToFormula = cellRange.FormatAsString(sheet.SheetName, true);
hsName = sheet.Workbook.GetName(nameValue);
if (hsName == null)
{
hsName = sheet.Workbook.CreateName();
hsName.NameName = nameValue;
}
cellRange = new CellRangeAddress(row, row + 1, 0, lst.Count);
hsName.RefersToFormula = cellRange.FormatAsString(sheet.SheetName, true);
}
/**
* Shift the Hyperlink anchors (not the hyperlink text, even if the hyperlink
* is of type LINK_DOCUMENT and refers to a cell that was shifted). Hyperlinks
* do not track the content they point to.
*
* @param shifter
*/
public override void UpdateHyperlinks(FormulaShifter shifter)
{
XSSFSheet xsheet = (XSSFSheet)sheet;
int sheetIndex = xsheet.GetWorkbook().GetSheetIndex(sheet);
List <IHyperlink> hyperlinkList = sheet.GetHyperlinkList();
foreach (IHyperlink hyperlink1 in hyperlinkList)
{
XSSFHyperlink hyperlink = hyperlink1 as XSSFHyperlink;
String cellRef = hyperlink.CellRef;
CellRangeAddress cra = CellRangeAddress.ValueOf(cellRef);
CellRangeAddress shiftedRange = ShiftRange(shifter, cra, sheetIndex);
if (shiftedRange != null && shiftedRange != cra)
{
// shiftedRange should not be null. If shiftedRange is null, that means
// that a hyperlink wasn't deleted at the beginning of shiftRows when
// identifying rows that should be removed because they will be overwritten
hyperlink.SetCellReference(shiftedRange.FormatAsString());
}
}
}
public void TestXSSFSetArrayFormula_SingleCell()
{
ICellRange <ICell> cells;
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)workbook.CreateSheet();
// 1. Single-cell array formula
String formula1 = "123";
CellRangeAddress range = CellRangeAddress.ValueOf("C3:C3");
cells = sheet.SetArrayFormula(formula1, range);
Assert.AreEqual(1, cells.Size);
// check GetFirstCell...
XSSFCell firstCell = (XSSFCell)cells.TopLeftCell;
Assert.AreSame(firstCell, sheet.GetFirstCellInArrayFormula(firstCell));
//retrieve the range and check it is the same
Assert.AreEqual(range.FormatAsString(), firstCell.GetArrayFormulaRange().FormatAsString());
ConfirmArrayFormulaCell(firstCell, "C3", formula1, "C3");
}
public void TestSetArrayFormula_SingleCell()
{
ICell[] cells;
IWorkbook workbook = _testDataProvider.CreateWorkbook();
ISheet sheet = workbook.CreateSheet();
ICell cell = sheet.CreateRow(0).CreateCell(0);
Assert.IsFalse(cell.IsPartOfArrayFormulaGroup);
try
{
CellRangeAddress c = cell.ArrayFormulaRange;
Assert.Fail("expected exception");
}
catch (InvalidOperationException e)
{
Assert.AreEqual("Cell A1 is not part of an array formula.", e.Message);
}
// row 3 does not yet exist
Assert.IsNull(sheet.GetRow(2));
CellRangeAddress range = new CellRangeAddress(2, 2, 2, 2);
cells = sheet.SetArrayFormula("SUM(C11:C12*D11:D12)", range).FlattenedCells;
Assert.AreEqual(1, cells.Length);
// sheet.SetArrayFormula Creates rows and cells for the designated range
Assert.IsNotNull(sheet.GetRow(2));
cell = sheet.GetRow(2).GetCell(2);
Assert.IsNotNull(cell);
Assert.IsTrue(cell.IsPartOfArrayFormulaGroup);
//retrieve the range and check it is the same
Assert.AreEqual(range.FormatAsString(), cell.ArrayFormulaRange.FormatAsString());
//check the formula
Assert.AreEqual("SUM(C11:C12*D11:D12)", cell.CellFormula);
workbook.Close();
}
private void CreateTotalColumn(ISheet sheet, IList <IRow> employesLeavesRows, IRow dayNameRow, IRow dayNumberRow, int lastCellIndex)
{
CellRangeAddress range = new CellRangeAddress(1, 2, lastCellIndex, lastCellIndex);
XSSFCellStyle style = exportStyleManager.GetStyle("workday");
sheet.AddMergedRegion(range);
ICell totalCell = dayNameRow.CreateCell(lastCellIndex);
totalCell.SetCellValue("Totale");
SetTitleBorders(sheet, range);
totalCell.CellStyle = style;
dayNumberRow.CreateCell(lastCellIndex).CellStyle = style;
for (int i = 0; i < employes.Count; i++)
{
style = GetStyle("workday", i);
ICell cell = employesLeavesRows[i].CreateCell(lastCellIndex);
cell.CellStyle = style;
range = new CellRangeAddress(i + 3, i + 3, 1, lastCellIndex - 1);
String formula = "SUM(" + range.FormatAsString() + ")";
cell.SetCellType(CellType.Formula);
cell.CellFormula = formula;
}
}
public void TestModifyArrayCells_mergeCells()
{
IWorkbook workbook = _testDataProvider.CreateWorkbook();
ISheet sheet = workbook.CreateSheet();
Assert.AreEqual(0, sheet.NumMergedRegions);
//Single-cell array formulas behave just like normal cells
ICellRange <ICell> srange =
sheet.SetArrayFormula("SUM(A4:A6,B4:B6)", CellRangeAddress.ValueOf("B5"));
ICell scell = srange.TopLeftCell;
sheet.AddMergedRegion(CellRangeAddress.ValueOf("B5:C6"));
//we are still an array formula
Assert.AreEqual(CellType.Formula, scell.CellType);
Assert.IsTrue(scell.IsPartOfArrayFormulaGroup);
Assert.AreEqual(1, sheet.NumMergedRegions);
//we cannot merge cells included in an array formula
ICellRange <ICell> mrange =
sheet.SetArrayFormula("A1:A3*B1:B3", CellRangeAddress.ValueOf("C1:C3"));
CellRangeAddress cra = CellRangeAddress.ValueOf("C1:C3");
try
{
sheet.AddMergedRegion(cra);
Assert.Fail("expected exception");
}
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(1, sheet.NumMergedRegions);
}
/* (non-Javadoc)
* @see NPOI.ss.usermodel.DataValidationHelper#CreateValidation(NPOI.ss.usermodel.DataValidationConstraint, NPOI.ss.util.CellRangeAddressList)
*/
public IDataValidation CreateValidation(IDataValidationConstraint constraint, CellRangeAddressList cellRangeAddressList)
{
XSSFDataValidationConstraint dataValidationConstraint = (XSSFDataValidationConstraint)constraint;
CT_DataValidation newDataValidation = new CT_DataValidation();
int validationType = constraint.GetValidationType();
switch (validationType)
{
case ValidationType.LIST:
newDataValidation.type = (ST_DataValidationType.list);
newDataValidation.formula1 = (constraint.Formula1);
break;
case ValidationType.ANY:
newDataValidation.type = ST_DataValidationType.none;
break;
case ValidationType.TEXT_LENGTH:
newDataValidation.type = ST_DataValidationType.textLength;
break;
case ValidationType.DATE:
newDataValidation.type = ST_DataValidationType.date;
break;
case ValidationType.INTEGER:
newDataValidation.type = ST_DataValidationType.whole;
break;
case ValidationType.DECIMAL:
newDataValidation.type = ST_DataValidationType.@decimal;
break;
case ValidationType.TIME:
newDataValidation.type = ST_DataValidationType.time;
break;
case ValidationType.FORMULA:
newDataValidation.type = ST_DataValidationType.custom;
break;
default:
newDataValidation.type = ST_DataValidationType.none;
break;
}
if (validationType != ValidationType.ANY && validationType != ValidationType.LIST)
{
newDataValidation.@operator = XSSFDataValidation.operatorTypeMappings[constraint.Operator];
if (constraint.Formula1 != null)
{
newDataValidation.formula1 = (constraint.Formula1);
}
if (constraint.Formula2 != null)
{
newDataValidation.formula2 = (constraint.Formula2);
}
}
CellRangeAddress[] cellRangeAddresses = cellRangeAddressList.CellRangeAddresses;
List <String> sqref = new List <String>();
for (int i = 0; i < cellRangeAddresses.Length; i++)
{
CellRangeAddress cellRangeAddress = cellRangeAddresses[i];
sqref.Add(cellRangeAddress.FormatAsString());
}
newDataValidation.sqref = (sqref);
return(new XSSFDataValidation(dataValidationConstraint, cellRangeAddressList, newDataValidation));
}
public IDataValidation CreateValidation(IDataValidationConstraint constraint, CellRangeAddressList cellRangeAddressList)
{
XSSFDataValidationConstraint constraint1 = (XSSFDataValidationConstraint)constraint;
CT_DataValidation ctDataValidation = new CT_DataValidation();
int validationType = constraint.GetValidationType();
switch (validationType)
{
case 0:
ctDataValidation.type = ST_DataValidationType.none;
break;
case 1:
ctDataValidation.type = ST_DataValidationType.whole;
break;
case 2:
ctDataValidation.type = ST_DataValidationType.@decimal;
break;
case 3:
ctDataValidation.type = ST_DataValidationType.list;
ctDataValidation.formula1 = constraint.Formula1;
break;
case 4:
ctDataValidation.type = ST_DataValidationType.date;
break;
case 5:
ctDataValidation.type = ST_DataValidationType.time;
break;
case 6:
ctDataValidation.type = ST_DataValidationType.textLength;
break;
case 7:
ctDataValidation.type = ST_DataValidationType.custom;
break;
default:
ctDataValidation.type = ST_DataValidationType.none;
break;
}
if (validationType != 0 && validationType != 3)
{
ctDataValidation.@operator = ST_DataValidationOperator.between;
if (XSSFDataValidation.operatorTypeMappings.ContainsKey(constraint.Operator))
{
ctDataValidation.@operator = XSSFDataValidation.operatorTypeMappings[constraint.Operator];
}
if (constraint.Formula1 != null)
{
ctDataValidation.formula1 = constraint.Formula1;
}
if (constraint.Formula2 != null)
{
ctDataValidation.formula2 = constraint.Formula2;
}
}
CellRangeAddress[] cellRangeAddresses = cellRangeAddressList.CellRangeAddresses;
List <string> stringList = new List <string>();
for (int index = 0; index < cellRangeAddresses.Length; ++index)
{
CellRangeAddress cellRangeAddress = cellRangeAddresses[index];
stringList.Add(cellRangeAddress.FormatAsString());
}
ctDataValidation.sqref = stringList;
return((IDataValidation) new XSSFDataValidation(constraint1, cellRangeAddressList, ctDataValidation));
}
private void ValidateArrayFormulas(CellRangeAddress region)
{
int firstRow = region.FirstRow;
int firstColumn = region.FirstColumn;
int lastRow = region.LastRow;
int lastColumn = region.LastColumn;
for (int rowIn = firstRow; rowIn <= lastRow; rowIn++)
{
for (int colIn = firstColumn; colIn <= lastColumn; colIn++)
{
HSSFRow row = (HSSFRow)GetRow(rowIn);
if (row == null) continue;
HSSFCell cell = (HSSFCell)row.GetCell(colIn);
if (cell == null) continue;
if (cell.IsPartOfArrayFormulaGroup)
{
CellRangeAddress arrayRange = cell.ArrayFormulaRange;
if (arrayRange.NumberOfCells > 1 &&
(arrayRange.IsInRange(region.FirstRow, region.FirstColumn) ||
arrayRange.IsInRange(region.FirstRow, region.FirstColumn)))
{
String msg = "The range " + region.FormatAsString() + " intersects with a multi-cell array formula. " +
"You cannot merge cells of an array.";
throw new InvalidOperationException(msg);
}
}
}
}
}
public void UpdateConditionalFormatting(FormulaShifter Shifter)
{
IWorkbook workbook = this.sheet.Workbook;
int sheetIndex = workbook.GetSheetIndex((ISheet)this.sheet);
XSSFEvaluationWorkbook evaluationWorkbook = XSSFEvaluationWorkbook.Create(workbook);
List <CT_ConditionalFormatting> conditionalFormatting1 = this.sheet.GetCTWorksheet().conditionalFormatting;
for (int index1 = 0; conditionalFormatting1 != null && index1 < conditionalFormatting1.Count; ++index1)
{
CT_ConditionalFormatting conditionalFormatting2 = conditionalFormatting1[index1];
List <CellRangeAddress> cellRangeAddressList1 = new List <CellRangeAddress>();
foreach (object obj in conditionalFormatting2.sqref)
{
string str = obj.ToString();
char[] chArray = new char[1] {
' '
};
foreach (string reference in str.Split(chArray))
{
cellRangeAddressList1.Add(CellRangeAddress.ValueOf(reference));
}
}
bool flag = false;
List <CellRangeAddress> cellRangeAddressList2 = new List <CellRangeAddress>();
for (int index2 = 0; index2 < cellRangeAddressList1.Count; ++index2)
{
CellRangeAddress cra = cellRangeAddressList1[index2];
CellRangeAddress cellRangeAddress = XSSFRowShifter.ShiftRange(Shifter, cra, sheetIndex);
if (cellRangeAddress == null)
{
flag = true;
}
else
{
cellRangeAddressList2.Add(cellRangeAddress);
if (cellRangeAddress != cra)
{
flag = true;
}
}
}
if (flag)
{
if (cellRangeAddressList2.Count == 0)
{
conditionalFormatting1.RemoveAt(index1);
continue;
}
List <string> stringList = new List <string>();
foreach (CellRangeAddress cellRangeAddress in cellRangeAddressList2)
{
stringList.Add(cellRangeAddress.FormatAsString());
}
conditionalFormatting2.sqref = stringList;
}
foreach (CT_CfRule ctCfRule in conditionalFormatting2.cfRule)
{
List <string> formula = ctCfRule.formula;
for (int index2 = 0; index2 < formula.Count; ++index2)
{
Ptg[] ptgs = FormulaParser.Parse(formula[index2], (IFormulaParsingWorkbook)evaluationWorkbook, FormulaType.CELL, sheetIndex);
if (Shifter.AdjustFormula(ptgs, sheetIndex))
{
string formulaString = FormulaRenderer.ToFormulaString((IFormulaRenderingWorkbook)evaluationWorkbook, ptgs);
formula[index2] = formulaString;
}
}
}
}
}
/* (non-Javadoc)
* @see NPOI.ss.usermodel.DataValidationHelper#CreateValidation(NPOI.ss.usermodel.DataValidationConstraint, NPOI.ss.util.CellRangeAddressList)
*/
public IDataValidation CreateValidation(IDataValidationConstraint constraint, CellRangeAddressList cellRangeAddressList)
{
XSSFDataValidationConstraint dataValidationConstraint = (XSSFDataValidationConstraint)constraint;
CT_DataValidation newDataValidation = new CT_DataValidation();
int validationType = constraint.GetValidationType();
switch (validationType)
{
case ValidationType.LIST:
newDataValidation.type = (ST_DataValidationType.list);
newDataValidation.formula1 = (constraint.Formula1);
break;
case ValidationType.ANY:
newDataValidation.type = ST_DataValidationType.none;
break;
case ValidationType.TEXT_LENGTH:
newDataValidation.type = ST_DataValidationType.textLength;
break;
case ValidationType.DATE:
newDataValidation.type = ST_DataValidationType.date;
break;
case ValidationType.INTEGER:
newDataValidation.type = ST_DataValidationType.whole;
break;
case ValidationType.DECIMAL:
newDataValidation.type = ST_DataValidationType.@decimal;
break;
case ValidationType.TIME:
newDataValidation.type = ST_DataValidationType.time;
break;
case ValidationType.FORMULA:
newDataValidation.type = ST_DataValidationType.custom;
break;
default:
newDataValidation.type = ST_DataValidationType.none;
break;
}
if (validationType != ValidationType.ANY && validationType != ValidationType.LIST)
{
newDataValidation.@operator = ST_DataValidationOperator.between;
if (XSSFDataValidation.operatorTypeMappings.ContainsKey(constraint.Operator))
{
newDataValidation.@operator = XSSFDataValidation.operatorTypeMappings[constraint.Operator];
}
if (constraint.Formula1 != null)
{
newDataValidation.formula1 = (constraint.Formula1);
}
if (constraint.Formula2 != null)
{
newDataValidation.formula2 = (constraint.Formula2);
}
}
CellRangeAddress[] cellRangeAddresses = cellRangeAddressList.CellRangeAddresses;
string sqref = string.Empty;
for (int i = 0; i < cellRangeAddresses.Length; i++)
{
CellRangeAddress cellRangeAddress = cellRangeAddresses[i];
if (sqref.Length == 0)
{
sqref = cellRangeAddress.FormatAsString();
}
else
{
sqref = " " + cellRangeAddress.FormatAsString();
}
}
newDataValidation.sqref = sqref;
newDataValidation.allowBlank = (true);
newDataValidation.errorStyle = ST_DataValidationErrorStyle.stop;
return(new XSSFDataValidation(dataValidationConstraint, cellRangeAddressList, newDataValidation));
}