public XMLCellController(CT_Cell cell, XMLWorksheetModel sheet, PartManager manager)
{
this._cell = cell;
this._workbook = (XMLWorkbookModel)sheet.Workbook;
this._sheet = sheet;
this._manager = manager;
}
private void updateRowFormulas(XSSFRow row, FormulaShifter Shifter)
{
foreach (XSSFCell xssfCell in row)
{
CT_Cell ctCell = xssfCell.GetCTCell();
if (ctCell.IsSetF())
{
CT_CellFormula f = ctCell.f;
string formula1 = f.Value;
if (formula1.Length > 0)
{
string str = XSSFRowShifter.ShiftFormula(row, formula1, Shifter);
if (str != null)
{
f.Value = str;
}
}
if (f.isSetRef())
{
string formula2 = f.@ref;
string str = XSSFRowShifter.ShiftFormula(row, formula2, Shifter);
if (str != null)
{
f.@ref = str;
}
}
}
}
}
/**
* update cell references when Shifting rows
*
* @param n the number of rows to move
*/
internal void Shift(int n)
{
int rownum = RowNum + n;
CalculationChain calcChain = ((XSSFWorkbook)_sheet.Workbook).GetCalculationChain();
int sheetId = (int)_sheet.sheet.sheetId;
String msg = "Row[rownum=" + RowNum + "] contains cell(s) included in a multi-cell array formula. " +
"You cannot change part of an array.";
foreach (ICell c in this)
{
XSSFCell cell = (XSSFCell)c;
if (cell.IsPartOfArrayFormulaGroup)
{
cell.NotifyArrayFormulaChanging(msg);
}
//remove the reference in the calculation chain
if (calcChain != null)
{
calcChain.RemoveItem(sheetId, cell.GetReference());
}
CT_Cell CT_Cell = cell.GetCTCell();
String r = new CellReference(rownum, cell.ColumnIndex).FormatAsString();
CT_Cell.r = r;
}
RowNum = rownum;
}
private void updateRowFormulas(XSSFRow row, FormulaShifter Shifter)
{
foreach (ICell c in row)
{
XSSFCell cell = (XSSFCell)c;
CT_Cell ctCell = cell.GetCTCell();
if (ctCell.IsSetF())
{
CT_CellFormula f = ctCell.f;
String formula = f.Value;
if (formula.Length > 0)
{
String ShiftedFormula = ShiftFormula(row, formula, Shifter);
if (ShiftedFormula != null)
{
f.Value = (ShiftedFormula);
}
}
if (f.isSetRef())
{ //Range of cells which the formula applies to.
String ref1 = f.@ref;
String ShiftedRef = ShiftFormula(row, ref1, Shifter);
if (ShiftedRef != null)
{
f.@ref = ShiftedRef;
}
}
}
}
}
public void TestFormulaString()
{
XSSFWorkbook wb = new XSSFWorkbook();
XSSFCell cell = (XSSFCell)wb.CreateSheet().CreateRow(0).CreateCell(0);
CT_Cell ctCell = cell.GetCTCell(); //low-level bean holding cell's xml
cell.SetCellFormula("A2");
Assert.AreEqual(CellType.Formula, cell.CellType);
Assert.AreEqual("A2", cell.CellFormula);
//the value is not Set and cell's type='N' which means blank
Assert.AreEqual(ST_CellType.n, ctCell.t);
//set cached formula value
cell.SetCellValue("t='str'");
//we are still of 'formula' type
Assert.AreEqual(CellType.Formula, cell.CellType);
Assert.AreEqual("A2", cell.CellFormula);
//cached formula value is Set and cell's type='STR'
Assert.AreEqual(ST_CellType.str, ctCell.t);
Assert.AreEqual("t='str'", cell.StringCellValue);
//now remove the formula, the cached formula result remains
cell.SetCellFormula(null);
Assert.AreEqual(CellType.String, cell.CellType);
Assert.AreEqual(ST_CellType.str, ctCell.t);
//the line below failed prior to fix of Bug #47889
Assert.AreEqual("t='str'", cell.StringCellValue);
//revert to a blank cell
cell.SetCellValue((String)null);
Assert.AreEqual(CellType.Blank, cell.CellType);
Assert.AreEqual(ST_CellType.n, ctCell.t);
Assert.AreEqual("", cell.StringCellValue);
}
/// <summary>
/// Blanks this cell. Blank cells have no formula or value but may have styling.
/// This method erases all the data previously associated with this cell.
/// </summary>
private void SetBlank()
{
CT_Cell blank = new CT_Cell();
blank.r = (_cell.r);
if (_cell.IsSetS())
{
blank.s = (_cell.s);
}
_cell.Set(blank);
}
/**
* Construct a XSSFCell.
*
* @param row the parent row.
* @param cell the xml bean Containing information about the cell.
*/
public XSSFCell(XSSFRow row, CT_Cell cell)
{
_cell = cell;
_row = row;
if (cell.r != null)
{
_cellNum = new CellReference(cell.r).Col;
}
_sharedStringSource = ((XSSFWorkbook)row.Sheet.Workbook).GetSharedStringSource();
_stylesSource = ((XSSFWorkbook)row.Sheet.Workbook).GetStylesSource();
}
private void SetBlank()
{
CT_Cell cell = new CT_Cell();
cell.r = this._cell.r;
if (this._cell.IsSetS())
{
cell.s = this._cell.s;
}
this._cell.Set(cell);
}
public ICellModel getCell(int col)
{
if (col >= 0 && col <= 16383)
{
ICellModel cellModel = default(ICellModel);
if (this.CellsMap.TryGetValue(col, out cellModel))
{
return(cellModel);
}
CT_Cell cT_Cell = new CT_Cell();
cT_Cell.R_Attr = CellPair.Name(this.RowNumber, col);
cellModel = new XMLCellModel(this._worksheetModel, this._manager, cT_Cell);
this.CellsMap.Add(col, cellModel);
return(cellModel);
}
throw new FatalException();
}
public ICellModel getCell(int col)
{
if (col < 0 || col > 16383)
{
throw new FatalException();
}
if (CellsMap.TryGetValue(col, out ICellModel value))
{
return(value);
}
CT_Cell cT_Cell = new CT_Cell();
cT_Cell.R_Attr = CellPair.Name(RowNumber, col);
value = new XMLCellModel(_worksheetModel, _manager, cT_Cell);
CellsMap.Add(col, value);
return(value);
}
/**
* Construct a XSSFCell.
*
* @param row the parent row.
* @param cell the xml bean Containing information about the cell.
*/
public XSSFCell(XSSFRow row, CT_Cell cell)
{
_cell = cell;
_row = row;
if (cell.r != null)
{
_cellNum = new CellReference(cell.r).Col;
}
else
{
int prevNum = row.LastCellNum;
if (prevNum != -1)
{
_cellNum = (row as XSSFRow).GetCell(prevNum - 1, MissingCellPolicy.RETURN_NULL_AND_BLANK).ColumnIndex + 1;
}
}
_sharedStringSource = ((XSSFWorkbook)row.Sheet.Workbook).GetSharedStringSource();
_stylesSource = ((XSSFWorkbook)row.Sheet.Workbook).GetStylesSource();
}
/// <summary>
/// Update the formulas in specified row using the formula shifting policy specified by shifter
/// </summary>
/// <param name="row">the row to update the formulas on</param>
/// <param name="Shifter">the formula shifting policy</param>
public override void UpdateRowFormulas(IRow row, FormulaShifter Shifter)
{
XSSFSheet sheet = (XSSFSheet)row.Sheet;
foreach (ICell c in row)
{
XSSFCell cell = (XSSFCell)c;
CT_Cell ctCell = cell.GetCTCell();
if (ctCell.IsSetF())
{
CT_CellFormula f = ctCell.f;
String formula = f.Value;
if (formula.Length > 0)
{
String ShiftedFormula = ShiftFormula(row, formula, Shifter);
if (ShiftedFormula != null)
{
f.Value = (ShiftedFormula);
if (f.t == ST_CellFormulaType.shared)
{
int si = (int)f.si;
CT_CellFormula sf = sheet.GetSharedFormula(si);
sf.Value = (ShiftedFormula);
}
}
}
if (f.isSetRef())
{ //Range of cells which the formula applies to.
String ref1 = f.@ref;
String ShiftedRef = ShiftFormula(row, ref1, Shifter);
if (ShiftedRef != null)
{
f.@ref = ShiftedRef;
}
}
}
}
}
private static void ConfirmArrayFormulaCell(ICell c, String cellRef, String formulaText, String arrayRangeRef)
{
if (c == null)
{
throw new AssertionException("Cell should not be null.");
}
CT_Cell ctCell = ((XSSFCell)c).GetCTCell();
Assert.AreEqual(cellRef, ctCell.r);
if (formulaText == null)
{
Assert.IsFalse(ctCell.IsSetF());
Assert.IsNull(ctCell.f);
}
else
{
CT_CellFormula f = ctCell.f;
Assert.AreEqual(arrayRangeRef, f.@ref);
Assert.AreEqual(formulaText, f.Value);
Assert.AreEqual(ST_CellFormulaType.array, f.t);
}
}
/**
* Fired when the document is written to an output stream.
*
* @see NPOI.XSSF.usermodel.XSSFSheet#Write(java.io.OutputStream) ()
*/
internal void OnDocumentWrite()
{
// check if cells in the CT_Row are ordered
bool isOrdered = true;
if (_row.SizeOfCArray() != _cells.Count)
{
isOrdered = false;
}
else
{
int i = 0;
foreach (XSSFCell cell in _cells.Values)
{
CT_Cell c1 = cell.GetCTCell();
CT_Cell c2 = _row.GetCArray(i++);
String r1 = c1.r;
String r2 = c2.r;
if (!(r1 == null ? r2 == null : r1.Equals(r2)))
{
isOrdered = false;
break;
}
}
}
if (!isOrdered)
{
CT_Cell[] cArray = new CT_Cell[_cells.Count];
int i = 0;
foreach (XSSFCell c in _cells.Values)
{
cArray[i++] = c.GetCTCell();
}
_row.SetCArray(cArray);
}
}
internal void OnDocumentWrite()
{
bool flag = true;
if (this._row.SizeOfCArray() != this._cells.Count)
{
flag = false;
}
else
{
int num = 0;
foreach (XSSFCell xssfCell in this._cells.Values)
{
CT_Cell ctCell = xssfCell.GetCTCell();
CT_Cell carray = this._row.GetCArray(num++);
string r1 = ctCell.r;
string r2 = carray.r;
if ((r1 == null ? (r2 == null ? 1 : 0) : (r1.Equals(r2) ? 1 : 0)) == 0)
{
flag = false;
break;
}
}
}
if (flag)
{
return;
}
CT_Cell[] array = new CT_Cell[this._cells.Count];
int num1 = 0;
foreach (XSSFCell xssfCell in this._cells.Values)
{
array[num1++] = xssfCell.GetCTCell();
}
this._row.SetCArray(array);
}
public XMLCellModel(XMLWorksheetModel sheet, PartManager manager, CT_Cell cell)
{
this._cell = cell;
this._controller = new XMLCellController(cell, sheet, manager);
this.FixCellValue();
}
private void WriteSheet(string sheetPath, Sheet sheet)
{
var sharedFormulas = new Dictionary <SharedFormula, int>();
var mergedCells = new Mapper <Range, CT_MergeCell>(ConvertRangeToMergedCell);
bool[] autoFit = new bool[256];
int[] width = new int[256];
bool calcAutoFit = (Options & XlsxFileWriterOptions.AutoFit) != 0;
var sheetFormat = new Lazy <CT_SheetFormatPr>();
if (sheet.DefaultRowHeight.HasValue)
{
sheetFormat.Value.defaultRowHeight = sheet.DefaultRowHeight.Value;
sheetFormat.Value.customHeight = true;
}
if (sheet.DefaultColumnWidth.HasValue)
{
sheetFormat.Value.defaultColWidth = sheet.DefaultColumnWidth.Value;
sheetFormat.Value.defaultColWidthSpecified = true;
}
var sheetView = new CT_SheetView();
if (sheet.ActiveCell != null)
{
sheetView.selection = new[] { new CT_Selection {
activeCell = sheet.ActiveCell.ToString(), sqref = new[] { sheet.ActiveCell.ToString() }
} }
}
;
sheetView.showGridLines = sheet.ShowGridLines;
var rows = new List <CT_Row>();
foreach (var row in sheet.Data)
{
var cells = new List <CT_Cell>();
foreach (var cd in row.Value)
{
bool skipData = false;
var c = new Cell {
Col = cd.Key, Row = row.Key
};
if (cd.Value.IsMerged)
{
if (cd.Value.MergedRange.Cell1 == c)
{
mergedCells.GetIndex(cd.Value.MergedRange);
}
else
{
skipData = true;
}
}
ST_CellType ct;
var data = cd.Value;
var cell = new CT_Cell
{
r = c.ToString()
};
if (!skipData)
{
if (data.Formula != null)
{
if (data.Formula.StartsWith("{"))
{
cell.f = new CT_CellFormula
{
t = ST_CellFormulaType.array,
Value = data.Formula.Substring(1, data.Formula.Length - 2)
};
}
else
{
cell.f = new CT_CellFormula {
Value = data.Formula
}
};
}
else if (data.SharedFormula != null)
{
int si;
if (!sharedFormulas.TryGetValue(data.SharedFormula, out si))
{
si = sharedFormulas.Count; //zero based
sharedFormulas.Add(data.SharedFormula, si);
cell.f = new CT_CellFormula
{
t = ST_CellFormulaType.shared,
Value = data.SharedFormula.Formula,
@ref = data.SharedFormula.Range.ToString(),
si = (uint)si,
siSpecified = true,
};
}
else
{
cell.f = new CT_CellFormula
{
si = (uint)si,
siSpecified = true,
t = ST_CellFormulaType.shared
}
};
}
else
{
String sv, format;
var v = WriteCellValue(data, out ct, out sv, out format);
if (format != null && (data.style == null || data.style.format == null))
{
data.Style.Format = format;
}
cell.v = v;
cell.t = ct;
if (calcAutoFit && sv != null && !cd.Value.IsMerged && cd.Key < 256)
{
autoFit[cd.Key] = true;
if (sv.Length > width[cd.Key])
{
width[cd.Key] = sv.Length;
}
}
}
}
if (data.style != null)
{
cell.s = RegisterStyle(data.style);
}
cells.Add(cell);
}
var r = new CT_Row()
{
c = cells.ToArray(),
r = (uint)row.Key + 1,
rSpecified = true
};
if (row.Value.Height.HasValue)
{
r.ht = row.Value.Height.Value;
r.htSpecified = true;
r.customHeight = true;
}
r.hidden = row.Value.Hidden;
r.collapsed = row.Value.Collapsed;
r.ph = row.Value.Phonetic;
if (row.Value.style != null)
{
r.s = RegisterStyle(row.Value.style);
r.customFormat = r.s > 0;
}
rows.Add(r);
}
var cols = new List <CT_Col>();
if (calcAutoFit)
{
for (int i = 0; i < width.Length; i++)
{
if (width[i] > 0)
{
if (!sheet.Columns[i].Width.HasValue)
{
sheet.Columns[i].Width = width[i];
sheet.Columns[i].BestFit = true;
}
}
}
}
if (sheet.Columns.data.Count > 0)
{
foreach (var node in sheet.Columns.data)
{
var col = new CT_Col
{
min = (uint)(node.FirstColumn + 1),
max = (uint)(node.LastColumn + 1),
bestFit = node.Data.BestFit,
hidden = node.Data.Hidden,
outlineLevel = node.Data.OutlineLevel,
phonetic = node.Data.Phonetic,
width = node.Data.Width ?? 10,
widthSpecified = true,
customWidth = node.Data.Width.HasValue
};
if (node.Data.style != null)
{
col.style = RegisterStyle(node.Data.style);
}
cols.Add(col);
}
}
var ws = new CT_Worksheet
{
sheetData = rows.ToArray(),
cols = cols.Count > 0 ? cols.ToArray() : null,
sheetFormatPr = sheetFormat.Data,
sheetViews = new CT_SheetViews {
sheetView = new[] { sheetView }
},
pageSetup = new CT_PageSetup()
{
scale = (uint)sheet.Page.Scale
}
};
switch (sheet.Page.Orientation)
{
case PageOrientation.Landscape:
ws.pageSetup.orientation = ST_Orientation.landscape;
break;
case PageOrientation.Portrait:
ws.pageSetup.orientation = ST_Orientation.portrait;
break;
}
if (sheet.Page.Margins != null)
{
ws.pageMargins = new CT_PageMargins
{
bottom = sheet.Page.Margins.Bottom,
footer = sheet.Page.Margins.Footer,
header = sheet.Page.Margins.Header,
left = sheet.Page.Margins.Left,
right = sheet.Page.Margins.Right,
top = sheet.Page.Margins.Top
};
}
if (sheet.ColumnBreaks != null && sheet.ColumnBreaks.Count > 0)
{
ws.colBreaks = new CT_PageBreak
{
count = (uint)sheet.ColumnBreaks.Count,
manualBreakCount = (uint)sheet.ColumnBreaks.Count,
brk = sheet.ColumnBreaks.Select(id => new CT_Break
{
id = (uint)id + 1,
man = true,
//max = (uint)sheet.Data.LastRow + 1
}).ToArray()
};
}
if (sheet.RowBreaks != null && sheet.RowBreaks.Count > 0)
{
ws.rowBreaks = new CT_PageBreak
{
count = (uint)sheet.RowBreaks.Count,
manualBreakCount = (uint)sheet.RowBreaks.Count,
brk = sheet.RowBreaks.Select(id => new CT_Break
{
id = (uint)id + 1,
man = true,
//max = 1048575U
}).ToArray()
};
}
if (!mergedCells.Empty)
{
ws.mergeCells = new CT_MergeCells {
mergeCell = mergedCells.ToArray()
}
}
;
//conditional formatting
CT_ConditionalFormatting[] cf;
WriteConditionalFormatting(sheet, out cf);
ws.conditionalFormatting = cf;
WriteFile(sheetPath, ws, SpreadsheetNs(false));
}
public void TestFormulaString()
{
XSSFWorkbook wb = new XSSFWorkbook();
XSSFCell cell = (XSSFCell)wb.CreateSheet().CreateRow(0).CreateCell(0);
CT_Cell ctCell = cell.GetCTCell(); //low-level bean holding cell's xml
cell.SetCellFormula("A2");
Assert.AreEqual(CellType.Formula, cell.CellType);
Assert.AreEqual(cell.CellFormula, "A2");
//the value is not Set and cell's type='N' which means blank
Assert.AreEqual(ST_CellType.n, ctCell.t);
//set cached formula value
cell.SetCellValue("t='str'");
//we are still of 'formula' type
Assert.AreEqual(CellType.Formula, cell.CellType);
Assert.AreEqual(cell.CellFormula, "A2");
//cached formula value is Set and cell's type='STR'
Assert.AreEqual(ST_CellType.str, ctCell.t);
Assert.AreEqual(cell.StringCellValue, "t='str'");
//now remove the formula, the cached formula result remains
cell.SetCellFormula(null);
Assert.AreEqual(CellType.String, cell.CellType);
Assert.AreEqual(ST_CellType.str, ctCell.t);
//the line below failed prior to fix of Bug #47889
Assert.AreEqual(cell.StringCellValue, "t='str'");
//revert to a blank cell
cell.SetCellValue((String)null);
Assert.AreEqual(CellType.Blank, cell.CellType);
Assert.AreEqual(ST_CellType.n, ctCell.t);
Assert.AreEqual(cell.StringCellValue, "");
// check behavior with setCellFormulaValidation
String invalidFormula = "A", validFormula = "A2";
FormulaParseException fpe = null;
// check that default is true
Assert.IsTrue(wb.CellFormulaValidation);
// check that valid formula does not throw exception
try
{
cell.SetCellFormula(validFormula);
}
catch (FormulaParseException e)
{
fpe = e;
}
Assert.IsNull(fpe);
// check that invalid formula does throw exception
try
{
cell.SetCellFormula(invalidFormula);
}
catch (FormulaParseException e)
{
fpe = e;
}
Assert.IsNotNull(fpe);
fpe = null;
// set cell formula validation to false
wb.CellFormulaValidation = false;
Assert.IsFalse(wb.CellFormulaValidation);
// check that neither valid nor invalid formula throw an exception
try
{
cell.SetCellFormula(validFormula);
cell.SetCellFormula(invalidFormula);
}
catch (FormulaParseException e)
{
fpe = e;
}
Assert.IsNull(fpe);
}
