/// <summary>
/// Calculate range of entity
/// </summary>
/// <param name="rangeFirst">Range in configuration</param>
/// <param name="idx">Index of entity</param>
/// <param name="isVertical">Whether entities arranged vertically</param>
/// <returns></returns>
public static String CalculateEntityRange(ExcelOpenXMLRange collectionRange, String rangeFirst, int idx, bool isVertical = true,ExcelOpenXMLRange endBefore = null)
{
var tl = new CellReference();
var br = new CellReference();
RangeReference.ParseRange(rangeFirst, ref tl, ref br);
int height = br.Row - tl.Row + 1;
int width = br.Col - tl.Col + 1;
int rangeH = collectionRange.Height;
int rangeW = collectionRange.Width;
if (height > rangeH) height = rangeH; // Make sure height of entity not bigger than collection
if (width > rangeW) width = rangeW; // Make sure width of entity not bigger than collection
int c = 0, r = 0;
if (isVertical)
{ // Entities arranged by rows
int entPerRow = rangeW / width; // How many entities could be in one row
int rowIdx = idx / entPerRow;
int colIdx = idx % entPerRow;
r = rowIdx * height;
c = colIdx * width;
}
else
{ // Entities arranged by columns
int entPerCol = rangeH / height; // How many entities could be in one column
int colIdx = idx / entPerCol;
int rowIdx = idx % entPerCol;
r = rowIdx * height;
c = colIdx * width;
}
tl.Col = c + 1;
tl.Row = r + 1;
// Check abort flag
if (endBefore != null)
{
if (isVertical)
{ // Vertical
if (tl.Row + collectionRange.Top - 1 >= endBefore.Top)
return null;
}
else
{ // Horizontal
if (tl.Col + collectionRange.Left - 1 >= endBefore.Left)
return null;
}
}
br = tl.Offset(width - 1, height - 1);
return String.Format("{0}:{1}", tl, br);
}
public void TestShiftSharedFormulasBug54206()
{
IWorkbook wb = _testDataProvider.OpenSampleWorkbook("54206." + _testDataProvider.StandardFileNameExtension);
ISheet sheet = wb.GetSheetAt(0);
Assert.AreEqual(sheet.GetRow(3).GetCell(6).CellFormula, "SUMIF($B$19:$B$82,$B4,G$19:G$82)");
Assert.AreEqual(sheet.GetRow(3).GetCell(7).CellFormula, "SUMIF($B$19:$B$82,$B4,H$19:H$82)");
Assert.AreEqual(sheet.GetRow(3).GetCell(8).CellFormula, "SUMIF($B$19:$B$82,$B4,I$19:I$82)");
Assert.AreEqual(sheet.GetRow(14).GetCell(6).CellFormula, "SUMIF($B$19:$B$82,$B15,G$19:G$82)");
Assert.AreEqual(sheet.GetRow(14).GetCell(7).CellFormula, "SUMIF($B$19:$B$82,$B15,H$19:H$82)");
Assert.AreEqual(sheet.GetRow(14).GetCell(8).CellFormula, "SUMIF($B$19:$B$82,$B15,I$19:I$82)");
// now the whole block G4L:15
for (int i = 3; i <= 14; i++)
{
for (int j = 6; j <= 8; j++)
{
String col = CellReference.ConvertNumToColString(j);
String expectedFormula = "SUMIF($B$19:$B$82,$B" + (i + 1) + "," + col + "$19:" + col + "$82)";
Assert.AreEqual(expectedFormula, sheet.GetRow(i).GetCell(j).CellFormula);
}
}
Assert.AreEqual(sheet.GetRow(23).GetCell(9).CellFormula, "SUM(G24:I24)");
Assert.AreEqual(sheet.GetRow(24).GetCell(9).CellFormula, "SUM(G25:I25)");
Assert.AreEqual(sheet.GetRow(25).GetCell(9).CellFormula, "SUM(G26:I26)");
sheet.ShiftRows(24, sheet.LastRowNum, 4, true, false);
Assert.AreEqual(sheet.GetRow(3).GetCell(6).CellFormula, "SUMIF($B$19:$B$86,$B4,G$19:G$86)");
Assert.AreEqual(sheet.GetRow(3).GetCell(7).CellFormula, "SUMIF($B$19:$B$86,$B4,H$19:H$86)");
Assert.AreEqual(sheet.GetRow(3).GetCell(8).CellFormula, "SUMIF($B$19:$B$86,$B4,I$19:I$86)");
Assert.AreEqual(sheet.GetRow(14).GetCell(6).CellFormula, "SUMIF($B$19:$B$86,$B15,G$19:G$86)");
Assert.AreEqual(sheet.GetRow(14).GetCell(7).CellFormula, "SUMIF($B$19:$B$86,$B15,H$19:H$86)");
Assert.AreEqual(sheet.GetRow(14).GetCell(8).CellFormula, "SUMIF($B$19:$B$86,$B15,I$19:I$86)");
// now the whole block G4L:15
for (int i = 3; i <= 14; i++)
{
for (int j = 6; j <= 8; j++)
{
String col = CellReference.ConvertNumToColString(j);
String expectedFormula = "SUMIF($B$19:$B$86,$B" + (i + 1) + "," + col + "$19:" + col + "$86)";
Assert.AreEqual(expectedFormula, sheet.GetRow(i).GetCell(j).CellFormula);
}
}
Assert.AreEqual(sheet.GetRow(23).GetCell(9).CellFormula, "SUM(G24:I24)");
// shifted rows
Assert.IsTrue(sheet.GetRow(24) == null || sheet.GetRow(24).GetCell(9) == null);
Assert.IsTrue(sheet.GetRow(25) == null || sheet.GetRow(25).GetCell(9) == null);
Assert.IsTrue(sheet.GetRow(26) == null || sheet.GetRow(26).GetCell(9) == null);
Assert.IsTrue(sheet.GetRow(27) == null || sheet.GetRow(27).GetCell(9) == null);
Assert.AreEqual(sheet.GetRow(28).GetCell(9).CellFormula, "SUM(G29:I29)");
Assert.AreEqual(sheet.GetRow(29).GetCell(9).CellFormula, "SUM(G30:I30)");
}
/**
* Opens the sheet we wrote out by BinomialOperator and makes sure the formulas
* all Match what we expect (x operator y)
*/
private static void OperationalRefVerify(String operator1, HSSFWorkbook wb)
{
NPOI.SS.UserModel.ISheet s = wb.GetSheetAt(0);
IRow r = null;
ICell c = null;
//get our minimum values
r = s.GetRow(0);
c = r.GetCell(1);
//get our minimum values
Assert.IsTrue(("A2" + operator1 + "A3").Equals(c.CellFormula), "minval Formula is as expected A2" + operator1 + "A3 != " + c.CellFormula);
for (int x = 1; x < short.MaxValue && x > 0; x = (short)(x * 2))
{
r = s.GetRow(x);
for (int y = 1; y < 256 && y > 0; y++)
{
int refx1;
int refy1;
int refx2;
int refy2;
if (x + 50 < short.MaxValue)
{
refx1 = x + 50;
refx2 = x + 46;
}
else
{
refx1 = x - 4;
refx2 = x - 3;
}
if (y + 50 < 255)
{
refy1 = y + 50;
refy2 = y + 49;
}
else
{
refy1 = y - 4;
refy2 = y - 3;
}
c = r.GetCell(y);
CellReference cr = new CellReference(refx1, refy1, false, false);
String ref1 = cr.FormatAsString();
ref1 = cr.FormatAsString();
cr = new CellReference(refx2, refy2, false, false);
String ref2 = cr.FormatAsString();
Assert.IsTrue((
("" + ref1 + operator1 + ref2).Equals(c.CellFormula)
), "loop Formula is as expected " + ref1 + operator1 + ref2 + "!=" + c.CellFormula
);
}
}
//Test our maximum values
r = s.GetRow(0);
c = r.GetCell(0);
Assert.AreEqual("B1" + operator1 + "IV255", c.CellFormula);
}
public CellRangeAddress GetArrayFormulaRange()
{
if (cellType != CellType.FORMULA)
{
String ref1 = new CellReference(this).FormatAsString();
throw new InvalidOperationException("Cell " + ref1
+ " is not part of an array formula.");
}
return ((FormulaRecordAggregate)record).GetArrayFormulaRange();
}
public static void WriteDMToTable(this ISheet sheet, List <DefineSelectItem> lst, string tableName, int numCol)
{
if (lst == null || lst.Count == 0)
{
return;
}
XSSFSheet s = (XSSFSheet)sheet;
XSSFTable tbl = s.GetTables().Where(a => a.Name == tableName).FirstOrDefault();
if (tbl == null)
{
return;
}
CT_Table ctTBl = tbl.GetCTTable();
if (numCol < 2 || ctTBl.tableColumns.count < numCol)
{
return;
}
CellReference cellRefStart = tbl.GetStartCellReference();
int rowStart = cellRefStart.Row + 1;
short colStart = cellRefStart.Col;
CellReference cellRefEnd = tbl.GetEndCellReference();
AreaReference reference = new AreaReference(cellRefStart, new CellReference(cellRefStart.Row + lst.Count, cellRefEnd.Col));
ctTBl.insertRow = true;
ctTBl.insertRowShift = true;
ctTBl.@ref = reference.FormatAsString();
IRow row = GetCreateRow(sheet, rowStart);
switch (numCol)
{
case 3:
foreach (DefineSelectItem item in lst)
{
row = GetCreateRow(sheet, rowStart++);
row.GetCreateCell(colStart).SetCellValue(item.Value);
row.GetCreateCell(colStart + 1).SetCellValue(item.Value2);
row.GetCreateCell(colStart + 2).SetCellValue(item.Text);
}
break;
case 4:
foreach (DefineSelectItem item in lst)
{
row = GetCreateRow(sheet, rowStart++);
row.GetCreateCell(colStart).SetCellValue(item.Value);
row.GetCreateCell(colStart + 1).SetCellValue(item.Value2);
row.GetCreateCell(colStart + 2).SetCellValue(item.Value3);
row.GetCreateCell(colStart + 3).SetCellValue(item.Text);
}
break;
case 2:
default:
foreach (DefineSelectItem item in lst)
{
row = GetCreateRow(sheet, rowStart++);
row.GetCreateCell(colStart).SetCellValue(item.Value);
row.GetCreateCell(colStart + 1).SetCellValue(item.Text);
}
break;
}
}
protected void SetCellReference(CellReference ref1)
{
SetCellReference(ref1.FormatAsString());
}
/**
* @return never <c>null</c>, never {@link BlankEval}
*/
private ValueEval EvaluateAny(IEvaluationCell srcCell, int sheetIndex,
int rowIndex, int columnIndex, EvaluationTracker tracker)
{
bool shouldCellDependencyBeRecorded = _stabilityClassifier == null ? true
: !_stabilityClassifier.IsCellFinal(sheetIndex, rowIndex, columnIndex);
ValueEval result;
if (srcCell == null || srcCell.CellType != CellType.Formula)
{
result = GetValueFromNonFormulaCell(srcCell);
if (shouldCellDependencyBeRecorded)
{
tracker.AcceptPlainValueDependency(_workbookIx, sheetIndex, rowIndex, columnIndex, result);
}
return(result);
}
FormulaCellCacheEntry cce = _cache.GetOrCreateFormulaCellEntry(srcCell);
if (shouldCellDependencyBeRecorded || cce.IsInputSensitive)
{
tracker.AcceptFormulaDependency(cce);
}
IEvaluationListener evalListener = _evaluationListener;
if (cce.GetValue() == null)
{
if (!tracker.StartEvaluate(cce))
{
return(ErrorEval.CIRCULAR_REF_ERROR);
}
OperationEvaluationContext ec = new OperationEvaluationContext(this, _workbook, sheetIndex, rowIndex, columnIndex, tracker);
try
{
Ptg[] ptgs = _workbook.GetFormulaTokens(srcCell);
if (evalListener == null)
{
result = EvaluateFormula(ec, ptgs);
}
else
{
evalListener.OnStartEvaluate(srcCell, cce);
result = EvaluateFormula(ec, ptgs);
evalListener.OnEndEvaluate(cce, result);
}
tracker.UpdateCacheResult(result);
}
catch (NotImplementedException e)
{
throw AddExceptionInfo(e, sheetIndex, rowIndex, columnIndex);
}
catch (RuntimeException re)
{
if (re.InnerException is WorkbookNotFoundException && _ignoreMissingWorkbooks)
{
LogInfo(re.InnerException.Message + " - Continuing with cached value!");
switch (srcCell.CachedFormulaResultType)
{
case CellType.Numeric:
result = new NumberEval(srcCell.NumericCellValue);
break;
case CellType.String:
result = new StringEval(srcCell.StringCellValue);
break;
case CellType.Blank:
result = BlankEval.instance;
break;
case CellType.Boolean:
result = BoolEval.ValueOf(srcCell.BooleanCellValue);
break;
case CellType.Error:
result = ErrorEval.ValueOf(srcCell.ErrorCellValue);
break;
case CellType.Formula:
default:
throw new RuntimeException("Unexpected cell type '" + srcCell.CellType + "' found!");
}
}
else
{
throw re;
}
}
finally
{
tracker.EndEvaluate(cce);
}
}
else
{
if (evalListener != null)
{
evalListener.OnCacheHit(sheetIndex, rowIndex, columnIndex, cce.GetValue());
}
return(cce.GetValue());
}
if (IsDebugLogEnabled())
{
String sheetName = GetSheetName(sheetIndex);
CellReference cr = new CellReference(rowIndex, columnIndex);
LogDebug("Evaluated " + sheetName + "!" + cr.FormatAsString() + " To " + cce.GetValue());
}
// Usually (result === cce.getValue())
// But sometimes: (result==ErrorEval.CIRCULAR_REF_ERROR, cce.getValue()==null)
// When circular references are detected, the cache entry is only updated for
// the top evaluation frame
//return cce.GetValue();
return(result);
}
private static String FormatTestCaseDetails(String sheetName, int rowIndex, ICell c, String currentGroupComment,
String rowComment)
{
StringBuilder sb = new StringBuilder();
CellReference cr = new CellReference(sheetName, rowIndex, c.ColumnIndex, false, false);
sb.Append(cr.FormatAsString());
sb.Append(" {=").Append(c.CellFormula).Append("}");
if (currentGroupComment != null)
{
sb.Append(" '");
sb.Append(currentGroupComment);
if (rowComment != null)
{
sb.Append(" - ");
sb.Append(rowComment);
}
sb.Append("' ");
}
else
{
if (rowComment != null)
{
sb.Append(" '");
sb.Append(rowComment);
sb.Append("' ");
}
}
return sb.ToString();
}
public void CreateTest()
{
string outFile = Path.Combine(TestContext.DeploymentDirectory, ExcelExporter.GetFileNameWithExtension("test"));
CellContent[][] data = new CellContent[1][];
ExcelExporter file = null;
try
{
file = ExcelExporter.Create(outFile);
}
catch (ExcelExporter.FileAlreadyInUseExeption)
{
return; // Une notification a déjà été levée dans ce cas
}
var sheet = file.CreateSheet("Test");
var cellRef = new CellReference();
// String
file.SetCellValue(sheet, cellRef, "string");
cellRef.MoveRight();
file.SetCellValue(sheet, cellRef, "");
cellRef.MoveRight();
file.SetCellValue(sheet, cellRef, new CellContent(null, CellDataType.String));
cellRef.MoveRight();
cellRef.NewLine();
// Number
file.SetCellValue(sheet, cellRef, 4);
cellRef.MoveRight();
file.SetCellValue(sheet, cellRef, 1.02);
cellRef.MoveRight();
file.SetCellValue(sheet, cellRef, 1.54E-12);
cellRef.MoveRight();
file.SetCellValue(sheet, cellRef, -12544.561681);
cellRef.MoveRight();
cellRef.NewLine();
// Percentage
file.SetCellValue(sheet, cellRef, CellContent.Percentage(.1));
cellRef.MoveRight();
file.SetCellValue(sheet, cellRef, CellContent.Percentage(.4656848468));
cellRef.MoveRight();
file.SetCellValue(sheet, cellRef, CellContent.Percentage(3));
cellRef.MoveRight();
file.SetCellValue(sheet, cellRef, CellContent.Percentage(-2));
cellRef.MoveRight();
cellRef.NewLine();
// Timespan
file.SetCellValue(sheet, cellRef, TimeSpan.FromMinutes(34.652155648));
cellRef.MoveRight();
file.SetCellValue(sheet, cellRef, TimeSpan.FromDays(42.656));
cellRef.MoveRight();
file.SetCellValue(sheet, cellRef, TimeSpan.FromHours(-1.6548));
cellRef.MoveRight();
file.SetCellValue(sheet, cellRef, CellContent.TimeSpan(TimeSpan.FromHours(5.6586).Ticks));
cellRef.MoveRight();
cellRef.NewLine();
// Date
file.SetCellValue(sheet, cellRef, new DateTime(1970, 1, 1));
cellRef.MoveRight();
file.SetCellValue(sheet, cellRef, new DateTime(2013, 4, 5, 6, 52, 34, 658));
cellRef.MoveRight();
file.SetCellValue(sheet, cellRef, new DateTime(1900, 10, 10));
cellRef.MoveRight();
cellRef.NewLine();
// Hyperlink
file.SetCellValue(sheet, cellRef, new CellContent("hyperlink", CellDataType.Hyperlink));
cellRef.MoveRight();
file.SetCellValue(sheet, cellRef, new CellContent("", CellDataType.Hyperlink));
cellRef.MoveRight();
file.SetCellValue(sheet, cellRef, new CellContent(null, CellDataType.Hyperlink));
cellRef.MoveRight();
cellRef.NewLine();
file.SaveAndClose();
Assert.IsTrue(File.Exists(outFile));
//System.Diagnostics.Process.Start(outFile);
TestContext.AddResultFile(outFile);
}
/**
* @return never <c>null</c>, never {@link BlankEval}
*/
private ValueEval EvaluateAny(IEvaluationCell srcCell, int sheetIndex,
int rowIndex, int columnIndex, EvaluationTracker tracker)
{
bool shouldCellDependencyBeRecorded = _stabilityClassifier == null ? true
: !_stabilityClassifier.IsCellFinal(sheetIndex, rowIndex, columnIndex);
ValueEval result;
if (srcCell == null || srcCell.CellType != CellType.FORMULA)
{
result = GetValueFromNonFormulaCell(srcCell);
if (shouldCellDependencyBeRecorded)
{
tracker.AcceptPlainValueDependency(_workbookIx, sheetIndex, rowIndex, columnIndex, result);
}
return(result);
}
FormulaCellCacheEntry cce = _cache.GetOrCreateFormulaCellEntry(srcCell);
if (shouldCellDependencyBeRecorded || cce.IsInputSensitive)
{
tracker.AcceptFormulaDependency(cce);
}
IEvaluationListener evalListener = _evaluationListener;
if (cce.GetValue() == null)
{
if (!tracker.StartEvaluate(cce))
{
return(ErrorEval.CIRCULAR_REF_ERROR);
}
OperationEvaluationContext ec = new OperationEvaluationContext(this, _workbook, sheetIndex, rowIndex, columnIndex, tracker);
try
{
Ptg[] ptgs = _workbook.GetFormulaTokens(srcCell);
if (evalListener == null)
{
result = EvaluateFormula(ec, ptgs);
}
else
{
evalListener.OnStartEvaluate(srcCell, cce);
result = EvaluateFormula(ec, ptgs);
evalListener.OnEndEvaluate(cce, result);
}
tracker.UpdateCacheResult(result);
}
catch (NotImplementedException e)
{
throw AddExceptionInfo(e, sheetIndex, rowIndex, columnIndex);
}
finally
{
tracker.EndEvaluate(cce);
}
}
else
{
if (evalListener != null)
{
evalListener.OnCacheHit(sheetIndex, rowIndex, columnIndex, cce.GetValue());
}
return(cce.GetValue());
}
if (IsDebugLogEnabled())
{
String sheetName = GetSheetName(sheetIndex);
CellReference cr = new CellReference(rowIndex, columnIndex);
LogDebug("Evaluated " + sheetName + "!" + cr.FormatAsString() + " To " + cce.GetValue().ToString());
}
// Usually (result === cce.getValue())
// But sometimes: (result==ErrorEval.CIRCULAR_REF_ERROR, cce.getValue()==null)
// When circular references are detected, the cache entry is only updated for
// the top evaluation frame
//return cce.GetValue();
return(result);
}
public ActionResult <WorkTicket> ExportReport(string queryStr)
{
ActionResult <TableData> queryData = Query(queryStr);
var templatePath = @"./ReportTemplate/MonthReportTemplate.xlsx";
XSSFWorkbook workBook;
using (var fileStream = new FileStream(templatePath, FileMode.Open, FileAccess.Read))
{
workBook = new XSSFWorkbook(fileStream);
fileStream.Close();
}
workBook.SetSheetName(0, DateTime.Now.ToString("yyyyMMdd"));
var sheet = workBook.GetSheetAt(0);
var dateRef = new CellReference("P2");
var dateCell = sheet.GetRow(dateRef.Row).GetCell(dateRef.Col);
dateCell.SetCellValue(DateTime.Now.ToString("yyyy年MM月dd日"));
List <WorkTicketView> list = queryData.Value.Data;
int i = 0;
foreach (WorkTicketView ticket in list)
{
var cellIndex = 1;
var cellRef = new CellReference($"B{5 + i}");
var row = sheet.GetRow(cellRef.Row);
var cell = row.GetCell(cellIndex++);
cell.SetCellValue(ticket.CreateTime.ToString("yyyy-MM-dd"));
cell = row.GetCell(cellIndex++);
cell.SetCellValue(ticket.LoadStationDistrict);
cell = row.GetCell(cellIndex++);
cell.SetCellValue(ticket.TruckCompany);
cell = row.GetCell(cellIndex++);
cell.SetCellValue(ticket.TruckNo);
cell = row.GetCell(cellIndex++);
cell.SetCellValue(ticket.LevelBeginLoad.ToString("F2"));
cell = row.GetCell(cellIndex++);
cell.SetCellValue(ticket.LevelAfterLoad.ToString("F2"));
cell = row.GetCell(cellIndex++);
cell.SetCellValue(ticket.OilLoaded.ToString("F2"));
cell = row.GetCell(cellIndex++);
cell.SetCellValue(ticket.LoadingActualBeginTime?.ToString("HH:mm:ss"));
cell = row.GetCell(cellIndex++);
cell.SetCellValue(ticket.LoadingActualEndTime?.ToString("HH:mm:ss"));
cell = row.GetCell(cellIndex++);
cell.SetCellValue(ticket.UnloadingBeginTime?.ToString("HH:mm:ss"));
cell = row.GetCell(cellIndex++);
cell.SetCellValue(ticket.UnloadingEndTime?.ToString("HH:mm:ss"));
cell = row.GetCell(cellIndex++);
cell.SetCellValue(ticket.SerialNumber);
cell = row.GetCell(cellIndex++);
cell.SetCellValue(ticket.SubSerialNumber);
cell = row.GetCell(cellIndex++);
cell.SetCellValue(ticket.OilLoader);
cell = row.GetCell(cellIndex++);
cell.SetCellValue(ticket.OilUnloader);
cell = row.GetCell(cellIndex++);
cell.SetCellValue(ticket.DrvierName);
i++;
}
using (var memoryStream = new MemoryStream())
{
workBook.Write(memoryStream);
memoryStream.Seek(0, SeekOrigin.Begin);
return(File(memoryStream.ToArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "报表.xlsx"));
}
}
public ValueEval Evaluate(ValueEval[] args, int srcRowIndex,
int srcColumnIndex)
{
if (args.Length < 2 || args.Length > 5)
{
return(ErrorEval.VALUE_INVALID);
}
try
{
bool pAbsRow, pAbsCol;
int row = (int)NumericFunction.SingleOperandEvaluate(args[0], srcRowIndex, srcColumnIndex);
int col = (int)NumericFunction.SingleOperandEvaluate(args[1], srcRowIndex, srcColumnIndex);
int refType;
if (args.Length > 2)
{
refType = (int)NumericFunction.SingleOperandEvaluate(args[2], srcRowIndex, srcColumnIndex);
}
else
{
refType = REF_ABSOLUTE;
}
switch (refType)
{
case REF_ABSOLUTE:
pAbsRow = true;
pAbsCol = true;
break;
case REF_ROW_ABSOLUTE_COLUMN_RELATIVE:
pAbsRow = true;
pAbsCol = false;
break;
case REF_ROW_RELATIVE_RELATIVE_ABSOLUTE:
pAbsRow = false;
pAbsCol = true;
break;
case REF_RELATIVE:
pAbsRow = false;
pAbsCol = false;
break;
default:
throw new EvaluationException(ErrorEval.VALUE_INVALID);
}
bool a1;
if (args.Length > 3)
{
ValueEval ve = OperandResolver.GetSingleValue(args[3], srcRowIndex, srcColumnIndex);
// TODO R1C1 style is not yet supported
a1 = ve == MissingArgEval.instance ? true : OperandResolver.CoerceValueToBoolean(ve, false).Value;
}
else
{
a1 = true;
}
String sheetName;
if (args.Length == 5)
{
ValueEval ve = OperandResolver.GetSingleValue(args[4], srcRowIndex, srcColumnIndex);
sheetName = ve == MissingArgEval.instance ? null : OperandResolver.CoerceValueToString(ve);
}
else
{
sheetName = null;
}
CellReference ref1 = new CellReference(row - 1, col - 1, pAbsRow, pAbsCol);
StringBuilder sb = new StringBuilder(32);
if (sheetName != null)
{
SheetNameFormatter.AppendFormat(sb, sheetName);
sb.Append('!');
}
sb.Append(ref1.FormatAsString());
return(new StringEval(sb.ToString()));
}
catch (EvaluationException e)
{
return(e.GetErrorEval());
}
}
private void Store(IRow row, object value, PropertyMapping mapping)
{
if (value == null)
{
return;
}
var cellIndex = CellReference.ConvertColStringToIndex(mapping.Column);
var cell = row.GetCell(cellIndex) ?? row.CreateCell(cellIndex);
//set column style to cell
cell.CellStyle = row.Sheet.GetColumnStyle(cellIndex);
//simplify any eventual enumeration into a single string
var enumVal = value as IEnumerable;
if (enumVal != null && !(value is string))
{
var strValue = string.Join(Mapping.ListSeparator, enumVal.Cast <object>());
cell.SetCellType(CellType.String);
cell.SetCellValue(strValue);
return;
}
//string value
var str = value as string;
if (str != null)
{
cell.SetCellType(CellType.String);
cell.SetCellValue(str);
return;
}
//numeric point types
if (value is double || value is float || value is int || value is long || value is short || value is byte || value is uint || value is ulong ||
value is ushort)
{
cell.SetCellType(CellType.Numeric);
cell.SetCellValue(Convert.ToDouble(value));
return;
}
//boolean value
if (value is bool)
{
cell.SetCellType(CellType.Boolean);
cell.SetCellValue((bool)value);
return;
}
//enumeration
if (value is Enum)
{
var eType = value.GetType();
var eValue = Enum.GetName(eType, value);
cell.SetCellType(CellType.String);
//try to get alias from configuration
var alias = GetEnumAlias(eType, eValue);
cell.SetCellValue(alias ?? eValue);
return;
}
throw new NotSupportedException("Only base types are supported");
}
public void SetTitle(CellReference titleReference)
{
titleType = TitleType.CellReference;
titleRef = titleReference;
}
private static ValueEval CreateRefEval(String refStr)
{
CellReference cr = new CellReference(refStr);
return(new MockRefEval(cr.Row, cr.Col));
}
public void TestAbsRef2()
{
CellReference cf = new CellReference(4, 1, true, true);
ConfirmCell(cf, null, 4, 1, true, true, "$B$5");
}
private static SharedFormulaGroup FindFormulaGroup(SharedFormulaGroup[] groups, CellReference firstCell)
{
int row = firstCell.Row;
int column = firstCell.Col;
// Traverse the list of shared formulas and try to find the correct one for us
// perhaps this could be optimised to some kind of binary search
for (int i = 0; i < groups.Length; i++)
{
SharedFormulaGroup svg = groups[i];
if (svg.IsFirstCell(row, column))
{
return(svg);
}
}
// TODO - fix file "15228.xls" so it opens in Excel after rewriting with POI
throw new Exception("Failed to find a matching shared formula record");
}
protected Ref2DPtgBase(CellReference cr) : base(cr)
{
}
public void TestAddValidations()
{
XSSFWorkbook workbook = XSSFTestDataSamples.OpenSampleWorkbook("DataValidations-49244.xlsx");
ISheet sheet = workbook.GetSheetAt(0);
List <IDataValidation> dataValidations = ((XSSFSheet)sheet).GetDataValidations();
/**
* For each validation type, there are two cells with the same validation. This Tests
* application of a single validation defInition to multiple cells.
*
* For list ( 3 validations for explicit and 3 for formula )
* - one validation that allows blank.
* - one that does not allow blank.
* - one that does not show the drop down arrow.
* = 2
*
* For number validations ( integer/decimal and text length ) with 8 different types of operators.
* = 50
*
* = 52 ( Total )
*/
Assert.AreEqual(52, dataValidations.Count);
IDataValidationHelper dataValidationHelper = sheet.GetDataValidationHelper();
int[] validationTypes = new int[] { ValidationType.INTEGER, ValidationType.DECIMAL, ValidationType.TEXT_LENGTH };
int[] SingleOperandOperatorTypes = new int[] {
OperatorType.LESS_THAN, OperatorType.LESS_OR_EQUAL,
OperatorType.GREATER_THAN, OperatorType.GREATER_OR_EQUAL,
OperatorType.EQUAL, OperatorType.NOT_EQUAL
};
int[] doubleOperandOperatorTypes = new int[] {
OperatorType.BETWEEN, OperatorType.NOT_BETWEEN
};
decimal value = (decimal)10, value2 = (decimal)20;
double dvalue = (double)10.001, dvalue2 = (double)19.999;
int lastRow = sheet.LastRowNum;
int offset = lastRow + 3;
int lastKnownNumValidations = dataValidations.Count;
IRow row = sheet.CreateRow(offset++);
ICell cell = row.CreateCell(0);
IDataValidationConstraint explicitListValidation = dataValidationHelper.CreateExplicitListConstraint(new String[] { "MA", "MI", "CA" });
CellRangeAddressList cellRangeAddressList = new CellRangeAddressList();
cellRangeAddressList.AddCellRangeAddress(cell.RowIndex, cell.ColumnIndex, cell.RowIndex, cell.ColumnIndex);
IDataValidation dataValidation = dataValidationHelper.CreateValidation(explicitListValidation, cellRangeAddressList);
SetOtherValidationParameters(dataValidation);
sheet.AddValidationData(dataValidation);
lastKnownNumValidations++;
row = sheet.CreateRow(offset++);
cell = row.CreateCell(0);
cellRangeAddressList = new CellRangeAddressList();
cellRangeAddressList.AddCellRangeAddress(cell.RowIndex, cell.ColumnIndex, cell.RowIndex, cell.ColumnIndex);
ICell firstCell = row.CreateCell(1); firstCell.SetCellValue("UT");
ICell secondCell = row.CreateCell(2); secondCell.SetCellValue("MN");
ICell thirdCell = row.CreateCell(3); thirdCell.SetCellValue("IL");
int rowNum = row.RowNum + 1;
String listFormula = new StringBuilder("$B$").Append(rowNum).Append(":").Append("$D$").Append(rowNum).ToString();
IDataValidationConstraint formulaListValidation = dataValidationHelper.CreateFormulaListConstraint(listFormula);
dataValidation = dataValidationHelper.CreateValidation(formulaListValidation, cellRangeAddressList);
SetOtherValidationParameters(dataValidation);
sheet.AddValidationData(dataValidation);
lastKnownNumValidations++;
offset++;
offset++;
for (int i = 0; i < validationTypes.Length; i++)
{
int validationType = validationTypes[i];
offset = offset + 2;
IRow row0 = sheet.CreateRow(offset++);
ICell cell_10 = row0.CreateCell(0);
cell_10.SetCellValue(validationType == ValidationType.DECIMAL ? "Decimal " : validationType == ValidationType.INTEGER ? "int" : "Text Length");
offset++;
for (int j = 0; j < SingleOperandOperatorTypes.Length; j++)
{
int operatorType = SingleOperandOperatorTypes[j];
IRow row1 = sheet.CreateRow(offset++);
//For int (> and >=) we add 1 extra cell for validations whose formulae reference other cells.
IRow row2 = i == 0 && j < 2 ? sheet.CreateRow(offset++) : null;
cell_10 = row1.CreateCell(0);
cell_10.SetCellValue(XSSFDataValidation.operatorTypeMappings[operatorType].ToString());
ICell cell_11 = row1.CreateCell(1);
ICell cell_21 = row1.CreateCell(2);
ICell cell_22 = i == 0 && j < 2 ? row2.CreateCell(2) : null;
ICell cell_13 = row1.CreateCell(3);
cell_13.SetCellType(CellType.Numeric);
cell_13.SetCellValue(validationType == ValidationType.DECIMAL ? dvalue : (double)value);
//First create value based validation;
IDataValidationConstraint constraint = dataValidationHelper.CreateNumericConstraint(validationType, operatorType, value.ToString(), null);
cellRangeAddressList = new CellRangeAddressList();
cellRangeAddressList.AddCellRangeAddress(new CellRangeAddress(cell_11.RowIndex, cell_11.RowIndex, cell_11.ColumnIndex, cell_11.ColumnIndex));
IDataValidation validation = dataValidationHelper.CreateValidation(constraint, cellRangeAddressList);
SetOtherValidationParameters(validation);
sheet.AddValidationData(validation);
Assert.AreEqual(++lastKnownNumValidations, ((XSSFSheet)sheet).GetDataValidations().Count);
//Now create real formula based validation.
String formula1 = new CellReference(cell_13.RowIndex, cell_13.ColumnIndex).FormatAsString();
constraint = dataValidationHelper.CreateNumericConstraint(validationType, operatorType, formula1, null);
if (i == 0 && j == 0)
{
cellRangeAddressList = new CellRangeAddressList();
cellRangeAddressList.AddCellRangeAddress(new CellRangeAddress(cell_21.RowIndex, cell_21.RowIndex, cell_21.ColumnIndex, cell_21.ColumnIndex));
validation = dataValidationHelper.CreateValidation(constraint, cellRangeAddressList);
SetOtherValidationParameters(validation);
sheet.AddValidationData(validation);
Assert.AreEqual(++lastKnownNumValidations, ((XSSFSheet)sheet).GetDataValidations().Count);
cellRangeAddressList = new CellRangeAddressList();
cellRangeAddressList.AddCellRangeAddress(new CellRangeAddress(cell_22.RowIndex, cell_22.RowIndex, cell_22.ColumnIndex, cell_22.ColumnIndex));
validation = dataValidationHelper.CreateValidation(constraint, cellRangeAddressList);
SetOtherValidationParameters(validation);
sheet.AddValidationData(validation);
Assert.AreEqual(++lastKnownNumValidations, ((XSSFSheet)sheet).GetDataValidations().Count);
}
else if (i == 0 && j == 1)
{
cellRangeAddressList = new CellRangeAddressList();
cellRangeAddressList.AddCellRangeAddress(new CellRangeAddress(cell_21.RowIndex, cell_21.RowIndex, cell_21.ColumnIndex, cell_21.ColumnIndex));
cellRangeAddressList.AddCellRangeAddress(new CellRangeAddress(cell_22.RowIndex, cell_22.RowIndex, cell_22.ColumnIndex, cell_22.ColumnIndex));
validation = dataValidationHelper.CreateValidation(constraint, cellRangeAddressList);
SetOtherValidationParameters(validation);
sheet.AddValidationData(validation);
Assert.AreEqual(++lastKnownNumValidations, ((XSSFSheet)sheet).GetDataValidations().Count);
}
else
{
cellRangeAddressList = new CellRangeAddressList();
cellRangeAddressList.AddCellRangeAddress(new CellRangeAddress(cell_21.RowIndex, cell_21.RowIndex, cell_21.ColumnIndex, cell_21.ColumnIndex));
validation = dataValidationHelper.CreateValidation(constraint, cellRangeAddressList);
SetOtherValidationParameters(validation);
sheet.AddValidationData(validation);
Assert.AreEqual(++lastKnownNumValidations, ((XSSFSheet)sheet).GetDataValidations().Count);
}
}
for (int j = 0; j < doubleOperandOperatorTypes.Length; j++)
{
int operatorType = doubleOperandOperatorTypes[j];
IRow row1 = sheet.CreateRow(offset++);
cell_10 = row1.CreateCell(0);
cell_10.SetCellValue(XSSFDataValidation.operatorTypeMappings[operatorType].ToString());
ICell cell_11 = row1.CreateCell(1);
ICell cell_21 = row1.CreateCell(2);
ICell cell_13 = row1.CreateCell(3);
ICell cell_14 = row1.CreateCell(4);
String value1String = validationType == ValidationType.DECIMAL ? dvalue.ToString() : value.ToString();
cell_13.SetCellType(CellType.Numeric);
cell_13.SetCellValue(validationType == ValidationType.DECIMAL ? dvalue : (int)value);
String value2String = validationType == ValidationType.DECIMAL ? dvalue2.ToString() : value2.ToString();
cell_14.SetCellType(CellType.Numeric);
cell_14.SetCellValue(validationType == ValidationType.DECIMAL ? dvalue2 : (int)value2);
//First create value based validation;
IDataValidationConstraint constraint = dataValidationHelper.CreateNumericConstraint(validationType, operatorType, value1String, value2String);
cellRangeAddressList = new CellRangeAddressList();
cellRangeAddressList.AddCellRangeAddress(new CellRangeAddress(cell_11.RowIndex, cell_11.RowIndex, cell_11.ColumnIndex, cell_11.ColumnIndex));
IDataValidation validation = dataValidationHelper.CreateValidation(constraint, cellRangeAddressList);
SetOtherValidationParameters(validation);
sheet.AddValidationData(validation);
Assert.AreEqual(++lastKnownNumValidations, ((XSSFSheet)sheet).GetDataValidations().Count);
//Now create real formula based validation.
String formula1 = new CellReference(cell_13.RowIndex, cell_13.ColumnIndex).FormatAsString();
String formula2 = new CellReference(cell_14.RowIndex, cell_14.ColumnIndex).FormatAsString();
constraint = dataValidationHelper.CreateNumericConstraint(validationType, operatorType, formula1, formula2);
cellRangeAddressList = new CellRangeAddressList();
cellRangeAddressList.AddCellRangeAddress(new CellRangeAddress(cell_21.RowIndex, cell_21.RowIndex, cell_21.ColumnIndex, cell_21.ColumnIndex));
validation = dataValidationHelper.CreateValidation(constraint, cellRangeAddressList);
SetOtherValidationParameters(validation);
sheet.AddValidationData(validation);
Assert.AreEqual(++lastKnownNumValidations, ((XSSFSheet)sheet).GetDataValidations().Count);
}
}
workbook = (XSSFWorkbook)XSSFTestDataSamples.WriteOutAndReadBack(workbook);
ISheet sheetAt = workbook.GetSheetAt(0);
Assert.AreEqual(lastKnownNumValidations, ((XSSFSheet)sheetAt).GetDataValidations().Count);
}
/**
* Also collects any loose MergeCellRecords and puts them in the supplied
* mergedCellsTable
*/
public RowBlocksReader(RecordStream rs)
{
ArrayList plainRecords = new ArrayList();
ArrayList shFrmRecords = new ArrayList();
ArrayList arrayRecords = new ArrayList();
ArrayList tableRecords = new ArrayList();
ArrayList mergeCellRecords = new ArrayList();
List <CellReference> firstCellRefs = new List <CellReference>();
Record prevRec = null;
while (!RecordOrderer.IsEndOfRowBlock(rs.PeekNextSid()))
{
// End of row/cell records for the current sheet
// Note - It is important that this code does not inadvertently add any sheet
// records from a subsequent sheet. For example, if SharedFormulaRecords
// are taken from the wrong sheet, this could cause bug 44449.
if (!rs.HasNext())
{
throw new InvalidOperationException("Failed to find end of row/cell records");
}
Record rec = rs.GetNext();
ArrayList dest;
switch (rec.Sid)
{
case MergeCellsRecord.sid:
dest = mergeCellRecords;
break;
case SharedFormulaRecord.sid:
dest = shFrmRecords;
if (!(prevRec is FormulaRecord))
{
throw new Exception("Shared formula record should follow a FormulaRecord");
}
FormulaRecord fr = (FormulaRecord)prevRec;
firstCellRefs.Add(new CellReference(fr.Row, fr.Column));
break;
case ArrayRecord.sid:
dest = arrayRecords;
break;
case TableRecord.sid:
dest = tableRecords;
break;
default: dest = plainRecords;
break;
}
dest.Add(rec);
prevRec = rec;
}
SharedFormulaRecord[] sharedFormulaRecs = new SharedFormulaRecord[shFrmRecords.Count];
List <ArrayRecord> arrayRecs = new List <ArrayRecord>(arrayRecords.Count);
List <TableRecord> tableRecs = new List <TableRecord>(tableRecords.Count);
sharedFormulaRecs = (SharedFormulaRecord[])shFrmRecords.ToArray(typeof(SharedFormulaRecord));
CellReference[] firstCells = new CellReference[firstCellRefs.Count];
firstCells = firstCellRefs.ToArray();
arrayRecs = new List <ArrayRecord>((ArrayRecord[])arrayRecords.ToArray(typeof(ArrayRecord)));
tableRecs = new List <TableRecord>((TableRecord[])tableRecords.ToArray(typeof(TableRecord)));
_plainRecords = plainRecords;
_sfm = SharedValueManager.Create(sharedFormulaRecs, firstCells, arrayRecs, tableRecs);
_mergedCellsRecords = new MergeCellsRecord[mergeCellRecords.Count];
_mergedCellsRecords = (MergeCellsRecord[])mergeCellRecords.ToArray(typeof(MergeCellsRecord));
}
public Ref3DPxg(SheetIdentifier sheetName, CellReference c)
: this(-1, sheetName, c)
{
}
/**
* Resolves a cell or area reference dynamically.
* @param workbookName the name of the workbook Containing the reference. If <code>null</code>
* the current workbook is assumed. Note - to Evaluate formulas which use multiple workbooks,
* a {@link CollaboratingWorkbooksEnvironment} must be set up.
* @param sheetName the name of the sheet Containing the reference. May be <code>null</code>
* (when <c>workbookName</c> is also null) in which case the current workbook and sheet is
* assumed.
* @param refStrPart1 the single cell reference or first part of the area reference. Must not
* be <code>null</code>.
* @param refStrPart2 the second part of the area reference. For single cell references this
* parameter must be <code>null</code>
* @param isA1Style specifies the format for <c>refStrPart1</c> and <c>refStrPart2</c>.
* Pass <c>true</c> for 'A1' style and <c>false</c> for 'R1C1' style.
* TODO - currently POI only supports 'A1' reference style
* @return a {@link RefEval} or {@link AreaEval}
*/
public ValueEval GetDynamicReference(String workbookName, String sheetName, String refStrPart1,
String refStrPart2, bool isA1Style)
{
if (!isA1Style)
{
throw new Exception("R1C1 style not supported yet");
}
SheetRefEvaluator sre = CreateExternSheetRefEvaluator(workbookName, sheetName);
if (sre == null)
{
return(ErrorEval.REF_INVALID);
}
// ugly typecast - TODO - make spReadsheet version more easily accessible
SpreadsheetVersion ssVersion = ((IFormulaParsingWorkbook)_workbook).GetSpreadsheetVersion();
NameType part1refType = ClassifyCellReference(refStrPart1, ssVersion);
switch (part1refType)
{
case NameType.BAD_CELL_OR_NAMED_RANGE:
return(ErrorEval.REF_INVALID);
case NameType.NAMED_RANGE:
IEvaluationName nm = ((IFormulaParsingWorkbook)_workbook).GetName(refStrPart1, _sheetIndex);
if (!nm.IsRange)
{
throw new Exception("Specified name '" + refStrPart1 + "' is not a range as expected.");
}
return(_bookEvaluator.EvaluateNameFormula(nm.NameDefinition, this));
}
if (refStrPart2 == null)
{
// no ':'
switch (part1refType)
{
case NameType.COLUMN:
case NameType.ROW:
return(ErrorEval.REF_INVALID);
case NameType.CELL:
CellReference cr = new CellReference(refStrPart1);
return(new LazyRefEval(cr.Row, cr.Col, sre));
}
throw new InvalidOperationException("Unexpected reference classification of '" + refStrPart1 + "'.");
}
NameType part2refType = ClassifyCellReference(refStrPart1, ssVersion);
switch (part2refType)
{
case NameType.BAD_CELL_OR_NAMED_RANGE:
return(ErrorEval.REF_INVALID);
case NameType.NAMED_RANGE:
throw new Exception("Cannot Evaluate '" + refStrPart1
+ "'. Indirect Evaluation of defined names not supported yet");
}
if (part2refType != part1refType)
{
// LHS and RHS of ':' must be compatible
return(ErrorEval.REF_INVALID);
}
int firstRow, firstCol, lastRow, lastCol;
switch (part1refType)
{
case NameType.COLUMN:
firstRow = 0;
lastRow = ssVersion.LastRowIndex;
firstCol = ParseColRef(refStrPart1);
lastCol = ParseColRef(refStrPart2);
break;
case NameType.ROW:
firstCol = 0;
lastCol = ssVersion.LastColumnIndex;
firstRow = ParseRowRef(refStrPart1);
lastRow = ParseRowRef(refStrPart2);
break;
case NameType.CELL:
CellReference cr;
cr = new CellReference(refStrPart1);
firstRow = cr.Row;
firstCol = cr.Col;
cr = new CellReference(refStrPart2);
lastRow = cr.Row;
lastCol = cr.Col;
break;
default:
throw new InvalidOperationException("Unexpected reference classification of '" + refStrPart1 + "'.");
}
return(new LazyAreaEval(firstRow, firstCol, lastRow, lastCol, sre));
}
public Cell GetCell(string cellIndex)
{
CellReference cellRef = new CellReference(cellIndex);
return(GetCell(cellRef.Row, cellRef.Col));
}
private static int ParseRowRef(String refStrPart)
{
return(CellReference.ConvertColStringToIndex(refStrPart));
}
/**
* Called when this cell is modified.
* The purpose of this method is to validate the cell state prior to modification.
*/
internal void NotifyArrayFormulaChanging()
{
CellReference ref1 = new CellReference(this);
String msg = "Cell " + ref1.FormatAsString() + " is part of a multi-cell array formula. " +
"You cannot change part of an array.";
NotifyArrayFormulaChanging(msg);
}
private static void ConfirmResolveCellRef(HSSFWorkbook wb, CellReference cref)
{
NPOI.SS.UserModel.ISheet s = wb.GetSheet(cref.SheetName);
IRow r = s.GetRow(cref.Row);
ICell c = r.GetCell((int)cref.Col);
Assert.IsNotNull(c);
}
private static void OperationRefTest(String operator1)
{
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.ISheet s = wb.CreateSheet();
IRow r = null;
ICell c = null;
//get our minimum values
r = s.CreateRow(0);
c = r.CreateCell(1);
c.CellFormula = ("A2" + operator1 + "A3");
for (int x = 1; x < short.MaxValue && x > 0; x = (short)(x * 2))
{
r = s.CreateRow(x);
for (int y = 1; y < 256 && y > 0; y++)
{
String ref1 = null;
String ref2 = null;
short refx1 = 0;
short refy1 = 0;
short refx2 = 0;
short refy2 = 0;
if (x + 50 < short.MaxValue)
{
refx1 = (short)(x + 50);
refx2 = (short)(x + 46);
}
else
{
refx1 = (short)(x - 4);
refx2 = (short)(x - 3);
}
if (y + 50 < 255)
{
refy1 = (short)(y + 50);
refy2 = (short)(y + 49);
}
else
{
refy1 = (short)(y - 4);
refy2 = (short)(y - 3);
}
c = r.GetCell(y);
CellReference cr = new CellReference(refx1, refy1, false, false);
ref1 = cr.FormatAsString();
cr = new CellReference(refx2, refy2, false, false);
ref2 = cr.FormatAsString();
c = r.CreateCell(y);
c.CellFormula = ("" + ref1 + operator1 + ref2);
}
}
//make sure we do the maximum value of the Int operator
if (s.LastRowNum < short.MaxValue)
{
r = s.GetRow(0);
c = r.CreateCell(0);
c.CellFormula = ("" + "B1" + operator1 + "IV255");
}
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
OperationalRefVerify(operator1, wb);
}
private static void ConfirmCell(CellReference cell, int row, int col, bool isRowAbs,
bool isColAbs)
{
Assert.AreEqual(row, cell.Row);
Assert.AreEqual(col, cell.Col);
Assert.AreEqual(isRowAbs, cell.IsRowAbsolute);
Assert.AreEqual(isColAbs, cell.IsColAbsolute);
}
public Ptg Get3DReferencePtg(CellReference cr, SheetIdentifier sheet)
{
int extIx = GetSheetExtIx(sheet);
return(new Ref3DPtg(cr, extIx));
}
public Ref3DPtg(CellReference cr, int externIdx) : base(cr)
{
ExternSheetIndex = externIdx;
}
private void ConfirmNameType(String ref1, NameType expectedResult)
{
NameType actualResult = CellReference.ClassifyCellReference(ref1, SpreadsheetVersion.EXCEL97);
Assert.AreEqual(expectedResult, actualResult);
}
public RefPtg(CellReference cr) : base(cr)
{
}
public void TestAbsRef3()
{
CellReference cf = new CellReference("B$5");
ConfirmCell(cf, null, 4, 1, true, false, "B$5");
}
public void WriteCell(int columnIndex, ICell cell)
{
if (cell == null)
{
return;
}
string cellRef = new CellReference(RowNum, columnIndex).FormatAsString();
WriteAsBytes(OutputStream, "<c r=\"" + cellRef + "\"");
ICellStyle cellStyle = cell.CellStyle;
if (cellStyle.Index != 0)
{
// need to convert the short to unsigned short as the indexes can be up to 64k
// ideally we would use int for this index, but that would need changes to some more
// APIs
WriteAsBytes(OutputStream, " s=\"" + (cellStyle.Index & 0xffff) + "\"");
}
switch (cell.CellType)
{
case CellType.Blank:
{
WriteAsBytes(OutputStream, ">");
break;
}
case CellType.Formula:
{
WriteAsBytes(OutputStream, ">");
WriteAsBytes(OutputStream, "<f>");
OutputQuotedString(cell.CellFormula);
WriteAsBytes(OutputStream, "</f>");
switch (cell.GetCachedFormulaResultTypeEnum())
{
case CellType.Numeric:
double nval = cell.NumericCellValue;
if (!Double.IsNaN(nval))
{
WriteAsBytes(OutputStream, "<v>" + nval + "</v>");
}
break;
default:
break;
}
break;
}
case CellType.String:
{
if (_sharedStringSource != null)
{
XSSFRichTextString rt = new XSSFRichTextString(cell.StringCellValue);
int sRef = _sharedStringSource.AddEntry(rt.GetCTRst());
WriteAsBytes(OutputStream, " t=\"" + ST_CellType.s + "\">");
WriteAsBytes(OutputStream, "<v>");
WriteAsBytes(OutputStream, sRef.ToString());
WriteAsBytes(OutputStream, "</v>");
}
else
{
WriteAsBytes(OutputStream, " t=\"inlineStr\">");
WriteAsBytes(OutputStream, "<is><t");
if (HasLeadingTrailingSpaces(cell.StringCellValue))
{
WriteAsBytes(OutputStream, " xml:space=\"preserve\"");
}
WriteAsBytes(OutputStream, ">");
OutputQuotedString(cell.StringCellValue);
WriteAsBytes(OutputStream, "</t></is>");
}
break;
}
case CellType.Numeric:
{
WriteAsBytes(OutputStream, " t=\"n\">");
WriteAsBytes(OutputStream, "<v>" + cell.NumericCellValue + "</v>");
break;
}
case CellType.Boolean:
{
WriteAsBytes(OutputStream, " t=\"b\">");
WriteAsBytes(OutputStream, "<v>" + (cell.BooleanCellValue ? "1" : "0") + "</v>");
break;
}
case CellType.Error:
{
FormulaError error = FormulaError.ForInt(cell.ErrorCellValue);
WriteAsBytes(OutputStream, " t=\"e\">");
WriteAsBytes(OutputStream, "<v>" + error.String + "</v>");
break;
}
default:
{
throw new InvalidOperationException("Invalid cell type: " + cell.CellType);
}
}
WriteAsBytes(OutputStream, "</c>");
OutputStream.Flush();
}
/// <summary>
/// Sets the print area.
/// </summary>
/// <param name="sheetIndex">Zero-based sheet index (0 = First Sheet)</param>
/// <param name="startColumn">Column to begin printarea</param>
/// <param name="endColumn">Column to end the printarea</param>
/// <param name="startRow">Row to begin the printarea</param>
/// <param name="endRow">Row to end the printarea</param>
public void SetPrintArea(int sheetIndex, int startColumn, int endColumn,
int startRow, int endRow)
{
//using absolute references because they don't Get copied and pasted anyway
CellReference cell = new CellReference(startRow, startColumn, true, true);
String reference = cell.FormatAsString();
cell = new CellReference(endRow, endColumn, true, true);
reference = reference + ":" + cell.FormatAsString();
SetPrintArea(sheetIndex, reference);
}
public ValueEval Evaluate(String formula, CellReference target, CellRangeAddressBase region)
{
return(Evaluate(formula, target, region, FormulaType.Cell));
}
/// <summary>
/// Remove a Array Formula from this sheet. All cells contained in the Array Formula range are removed as well
/// </summary>
/// <param name="cell">any cell within Array Formula range</param>
/// <returns>the <see cref="ICellRange{ICell}"/> of cells affected by this change</returns>
public ICellRange<ICell> RemoveArrayFormula(ICell cell)
{
if (cell.Sheet != this)
{
throw new ArgumentException("Specified cell does not belong to this sheet.");
}
CellValueRecordInterface rec = ((HSSFCell)cell).CellValueRecord;
if (!(rec is FormulaRecordAggregate))
{
String ref1 = new CellReference(cell).FormatAsString();
throw new ArgumentException("Cell " + ref1 + " is not part of an array formula.");
}
FormulaRecordAggregate fra = (FormulaRecordAggregate)rec;
CellRangeAddress range = fra.RemoveArrayFormula(cell.RowIndex, cell.ColumnIndex);
ICellRange<ICell> result = GetCellRange(range);
// clear all cells in the range
foreach (ICell c in result)
{
c.SetCellType(CellType.Blank);
}
return result;
}
public void TestGetCellRefParts()
{
CellReference cellReference;
string[] parts;
string cellRef = "A1";
cellReference = new CellReference(cellRef);
Assert.AreEqual(0, cellReference.Col);
parts = cellReference.CellRefParts;
Assert.IsNotNull(parts);
Assert.AreEqual(null, parts[0]);
Assert.AreEqual("1", parts[1]);
Assert.AreEqual("A", parts[2]);
cellRef = "AA1";
cellReference = new CellReference(cellRef);
Assert.AreEqual(26, cellReference.Col);
parts = cellReference.CellRefParts;
Assert.IsNotNull(parts);
Assert.AreEqual(null, parts[0]);
Assert.AreEqual("1", parts[1]);
Assert.AreEqual("AA", parts[2]);
cellRef = "AA100";
cellReference = new CellReference(cellRef);
Assert.AreEqual(26, cellReference.Col);
parts = cellReference.CellRefParts;
Assert.IsNotNull(parts);
Assert.AreEqual(null, parts[0]);
Assert.AreEqual("100", parts[1]);
Assert.AreEqual("AA", parts[2]);
cellRef = "AAA300";
cellReference = new CellReference(cellRef);
Assert.AreEqual(702, cellReference.Col);
parts = cellReference.CellRefParts;
Assert.IsNotNull(parts);
Assert.AreEqual(null, parts[0]);
Assert.AreEqual("300", parts[1]);
Assert.AreEqual("AAA", parts[2]);
cellRef = "ZZ100521";
cellReference = new CellReference(cellRef);
Assert.AreEqual(26 * 26 + 25, cellReference.Col);
parts = cellReference.CellRefParts;
Assert.IsNotNull(parts);
Assert.AreEqual(null, parts[0]);
Assert.AreEqual("100521", parts[1]);
Assert.AreEqual("ZZ", parts[2]);
cellRef = "ZYX987";
cellReference = new CellReference(cellRef);
Assert.AreEqual(26 * 26 * 26 + 25 * 26 + 24 - 1, cellReference.Col);
parts = cellReference.CellRefParts;
Assert.IsNotNull(parts);
Assert.AreEqual(null, parts[0]);
Assert.AreEqual("987", parts[1]);
Assert.AreEqual("ZYX", parts[2]);
cellRef = "AABC10065";
cellReference = new CellReference(cellRef);
parts = cellReference.CellRefParts;
Assert.IsNotNull(parts);
Assert.AreEqual(null, parts[0]);
Assert.AreEqual("10065", parts[1]);
Assert.AreEqual("AABC", parts[2]);
}
