/// <summary>
/// Gets the value.
/// </summary>
/// <param name="cell">The ICell object.</param>
/// <param name="dataFormatter">The data formatter.</param>
/// <param name="formulaEvaluator">The formula evaluator.</param>
/// <returns>Value string.</returns>
public static string GetValue(ICell cell, DataFormatter dataFormatter, IFormulaEvaluator formulaEvaluator)
{
string ret = string.Empty;
if (null == cell)
{
return ret;
}
ret = dataFormatter.FormatCellValue(cell, formulaEvaluator);
// remove line break
return ret.Replace("\n", " ");
}
public void doTest49928Core(IWorkbook wb)
{
DataFormatter df = new DataFormatter();
ISheet sheet = wb.GetSheetAt(0);
ICell cell = sheet.GetRow(0).GetCell(0);
ICellStyle style = cell.CellStyle;
String poundFmt = "\"\u00a3\"#,##0;[Red]\\-\"\u00a3\"#,##0";
// not expected normally, id of a custom format should be greater
// than BuiltinFormats.FIRST_USER_DEFINED_FORMAT_INDEX
short poundFmtIdx = 6;
Assert.AreEqual(poundFmt, style.GetDataFormatString());
Assert.AreEqual(poundFmtIdx, style.DataFormat);
Assert.AreEqual("\u00a31", df.FormatCellValue(cell));
IDataFormat dataFormat = wb.CreateDataFormat();
Assert.AreEqual(poundFmtIdx, dataFormat.GetFormat(poundFmt));
Assert.AreEqual(poundFmt, dataFormat.GetFormat(poundFmtIdx));
}
public void TestTruthFile()
{
Stream truthFile = HSSFTestDataSamples.OpenSampleFileStream("54686_fraction_formats.txt");
TextReader reader = new StreamReader(truthFile);
IWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("54686_fraction_formats.xls");
ISheet sheet = wb.GetSheetAt(0);
DataFormatter formatter = new DataFormatter();
IFormulaEvaluator Evaluator = wb.GetCreationHelper().CreateFormulaEvaluator();
// Skip over the header row
String truthLine = reader.ReadLine();
String[] headers = truthLine.Split("\t".ToCharArray());
truthLine = reader.ReadLine();
for (int i = 1; i < sheet.LastRowNum && truthLine != null; i++)
{
IRow r = sheet.GetRow(i);
String[] truths = truthLine.Split("\t".ToCharArray());
// Intentionally ignore the last column (tika-1132), for now
for (short j = 3; j < 12; j++)
{
ICell cell = r.GetCell(j, MissingCellPolicy.CREATE_NULL_AS_BLANK);
String truth = Clean(truths[j]);
String testKey = truths[0] + ":" + truths[1] + ":" + headers[j];
String formatted = Clean(formatter.FormatCellValue(cell, Evaluator));
if (truths.Length <= j)
{
continue;
}
Assert.AreEqual(truth, formatted, testKey);
}
truthLine = reader.ReadLine();
}
reader.Close();
}
/**
* Compute width of a single cell
*
* @param cell the cell whose width is to be calculated
* @param defaultCharWidth the width of a single character
* @param formatter formatter used to prepare the text to be measured
* @param useMergedCells whether to use merged cells
* @return the width in pixels
*/
public static double GetCellWidth(ICell cell, int defaultCharWidth, DataFormatter formatter, bool useMergedCells)
{
ISheet sheet = cell.Sheet;
IWorkbook wb = sheet.Workbook;
IRow row = cell.Row;
int column = cell.ColumnIndex;
int colspan = 1;
for (int i = 0; i < sheet.NumMergedRegions; i++)
{
CellRangeAddress region = sheet.GetMergedRegion(i);
if (ContainsCell(region, row.RowNum, column))
{
if (!useMergedCells)
{
// If we're not using merged cells, skip this one and move on to the next.
return -1;
}
cell = row.GetCell(region.FirstColumn);
colspan = 1 + region.LastColumn - region.FirstColumn;
}
}
ICellStyle style = cell.CellStyle;
CellType cellType = cell.CellType;
//IFont defaultIFont = wb.GetFontAt((short)0); // appears unnecessary?
//Font windowsFont = IFont2Font(defaultFont); // this needs to be disposed
// for formula cells we compute the cell width for the cached formula result
if (cellType == CellType.Formula) cellType = cell.CachedFormulaResultType;
IFont font = wb.GetFontAt(style.FontIndex);
//AttributedString str;
//TextLayout layout;
double width = -1;
using (Font windowsFont = IFont2Font(font))
using (Bitmap bmp = new Bitmap(2048, 100))
using (Graphics g = Graphics.FromImage(bmp))
{
if (cellType == CellType.String)
{
IRichTextString rt = cell.RichStringCellValue;
String[] lines = rt.String.Split("\n".ToCharArray());
for (int i = 0; i < lines.Length; i++)
{
String txt = lines[i] + defaultChar;
//str = new AttributedString(txt);
//copyAttributes(font, str, 0, txt.length());
// moved to using clause
if (rt.NumFormattingRuns > 0)
{
// TODO: support rich text fragments
}
//layout = new TextLayout(str.getIterator(), fontRenderContext);
if (style.Rotation != 0)
{
/*
* Transform the text using a scale so that it's height is increased by a multiple of the leading,
* and then rotate the text before computing the bounds. The scale results in some whitespace around
* the unrotated top and bottom of the text that normally wouldn't be present if unscaled, but
* is added by the standard Excel autosize.
*/
//AffineTransform trans = new AffineTransform();
//trans.concatenate(AffineTransform.getRotateInstance(style.Rotation*2.0*Math.PI/360.0));
//trans.concatenate(
// AffineTransform.getScaleInstance(1, fontHeightMultiple)
// );
double angle = style.Rotation*2.0*Math.PI/360.0;
SizeF sf = g.MeasureString(txt, windowsFont);
double x1 = Math.Abs(sf.Height*Math.Sin(angle));
double x2 = Math.Abs(sf.Width*Math.Cos(angle));
double w = Math.Round(x1 + x2, 0, MidpointRounding.ToEven);
width = Math.Max(width, (w/colspan/defaultCharWidth)*2 + cell.CellStyle.Indention);
//width = Math.Max(width,
// ((layout.getOutline(trans).getBounds().getWidth()/colspan)/defaultCharWidth) +
// cell.getCellStyle().getIndention());
}
else
{
//width = Math.Max(width,
// ((layout.getBounds().getWidth()/colspan)/defaultCharWidth) +
// cell.getCellStyle().getIndention());
double w = Math.Round(g.MeasureString(txt, windowsFont).Width, 0,
MidpointRounding.ToEven);
width = Math.Max(width, (w/colspan/defaultCharWidth)*2 + cell.CellStyle.Indention);
}
}
}
else
{
String sval = null;
if (cellType == CellType.Numeric)
{
// Try to get it formatted to look the same as excel
try
{
sval = formatter.FormatCellValue(cell, dummyEvaluator);
}
catch (Exception)
{
sval = cell.NumericCellValue.ToString();
}
}
else if (cellType == CellType.Boolean)
{
sval = cell.BooleanCellValue.ToString().ToUpper();
}
if (sval != null)
{
String txt = sval + defaultChar;
//str = new AttributedString(txt);
//copyAttributes(font, str, 0, txt.length());
//layout = new TextLayout(str.getIterator(), fontRenderContext);
if (style.Rotation != 0)
{
/*
* Transform the text using a scale so that it's height is increased by a multiple of the leading,
* and then rotate the text before computing the bounds. The scale results in some whitespace around
* the unrotated top and bottom of the text that normally wouldn't be present if unscaled, but
* is added by the standard Excel autosize.
*/
//AffineTransform trans = new AffineTransform();
//trans.concatenate(AffineTransform.getRotateInstance(style.getRotation()*2.0*Math.PI/360.0));
//trans.concatenate(
// AffineTransform.getScaleInstance(1, fontHeightMultiple)
// );
//width = Math.max(width,
// ((layout.getOutline(trans).getBounds().getWidth()/colspan)/defaultCharWidth) +
// cell.getCellStyle().getIndention());
double angle = style.Rotation * 2.0 * Math.PI / 360.0;
SizeF sf = g.MeasureString(txt, windowsFont);
double x1 = sf.Height * Math.Sin(angle);
double x2 = sf.Width * Math.Cos(angle);
double w = Math.Round(x1 + x2, 0, MidpointRounding.ToEven);
width = Math.Max(width, (w / colspan / defaultCharWidth) * 2 + cell.CellStyle.Indention);
}
else
{
//width = Math.max(width,
// ((layout.getBounds().getWidth()/colspan)/defaultCharWidth) +
// cell.getCellStyle().getIndention());
double w = Math.Round(g.MeasureString(txt, windowsFont).Width, 0, MidpointRounding.ToEven);
width = Math.Max(width, (w * 1.0 / colspan / defaultCharWidth) * 2 + cell.CellStyle.Indention);
}
}
}
}
return width;
}
/// <summary>
/// Returns a string representation of the cell
/// This method returns a simple representation,
/// anthing more complex should be in user code, with
/// knowledge of the semantics of the sheet being Processed.
/// Formula cells return the formula string,
/// rather than the formula result.
/// Dates are Displayed in dd-MMM-yyyy format
/// Errors are Displayed as #ERR<errIdx>
/// </summary>
public override String ToString()
{
switch (CellType)
{
case CellType.BLANK:
return "";
case CellType.BOOLEAN:
return BooleanCellValue ? "TRUE" : "FALSE";
case CellType.ERROR:
return NPOI.SS.Formula.Eval.ErrorEval.GetText(((BoolErrRecord)record).ErrorValue);
case CellType.FORMULA:
return CellFormula;
case CellType.NUMERIC:
string format = this.CellStyle.GetDataFormatString();
DataFormatter formatter = new DataFormatter();
return formatter.FormatCellValue(this);
case CellType.STRING:
return StringCellValue;
default:
return "Unknown Cell Type: " + CellType;
}
}
public void Test57236()
{
// Having very small numbers leads to different formatting, Excel uses the scientific notation, but POI leads to "0"
/*
DecimalFormat format = new DecimalFormat("#.##########", new DecimalFormatSymbols(Locale.Default));
double d = 3.0E-104;
Assert.AreEqual("3.0E-104", format.Format(d));
*/
DataFormatter formatter = new DataFormatter(true);
XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("57236.xlsx");
for (int sheetNum = 0; sheetNum < wb.NumberOfSheets; sheetNum++)
{
ISheet sheet = wb.GetSheetAt(sheetNum);
for (int rowNum = sheet.FirstRowNum; rowNum < sheet.LastRowNum; rowNum++)
{
IRow row = sheet.GetRow(rowNum);
for (int cellNum = row.FirstCellNum; cellNum < row.LastCellNum; cellNum++)
{
ICell cell = row.GetCell(cellNum);
String fmtCellValue = formatter.FormatCellValue(cell);
//System.out.Println("Cell: " + fmtCellValue);
Assert.IsNotNull(fmtCellValue);
Assert.IsFalse(fmtCellValue.Equals("0"));
}
}
}
}
public void Test50756()
{
HSSFWorkbook wb = OpenSample("50756.xls");
ISheet s = wb.GetSheetAt(0);
IRow r17 = s.GetRow(16);
IRow r18 = s.GetRow(17);
DataFormatter df = new DataFormatter();
Assert.AreEqual(10.0, r17.GetCell(1).NumericCellValue);
Assert.AreEqual(20.0, r17.GetCell(2).NumericCellValue);
Assert.AreEqual(20.0, r17.GetCell(3).NumericCellValue);
Assert.AreEqual("GENERAL", r17.GetCell(1).CellStyle.GetDataFormatString());
Assert.AreEqual("GENERAL", r17.GetCell(2).CellStyle.GetDataFormatString());
Assert.AreEqual("GENERAL", r17.GetCell(3).CellStyle.GetDataFormatString());
Assert.AreEqual("10", df.FormatCellValue(r17.GetCell(1)));
Assert.AreEqual("20", df.FormatCellValue(r17.GetCell(2)));
Assert.AreEqual("20", df.FormatCellValue(r17.GetCell(3)));
Assert.AreEqual(16.0, r18.GetCell(1).NumericCellValue);
Assert.AreEqual(35.0, r18.GetCell(2).NumericCellValue);
Assert.AreEqual(123.0, r18.GetCell(3).NumericCellValue);
Assert.AreEqual("GENERAL", r18.GetCell(1).CellStyle.GetDataFormatString());
Assert.AreEqual("GENERAL", r18.GetCell(2).CellStyle.GetDataFormatString());
Assert.AreEqual("GENERAL", r18.GetCell(3).CellStyle.GetDataFormatString());
Assert.AreEqual("16", df.FormatCellValue(r18.GetCell(1)));
Assert.AreEqual("35", df.FormatCellValue(r18.GetCell(2)));
Assert.AreEqual("123", df.FormatCellValue(r18.GetCell(3)));
}
public void Test48968()
{
HSSFWorkbook wb = OpenSample("48968.xls");
Assert.AreEqual(1, wb.NumberOfSheets);
DataFormatter fmt = new DataFormatter();
// Check the dates
ISheet s = wb.GetSheetAt(0);
ICell cell_d20110325 = s.GetRow(0).GetCell(0);
ICell cell_d19000102 = s.GetRow(11).GetCell(0);
ICell cell_d19000100 = s.GetRow(21).GetCell(0);
Assert.AreEqual(s.GetRow(0).GetCell(3).StringCellValue, fmt.FormatCellValue(cell_d20110325));
Assert.AreEqual(s.GetRow(11).GetCell(3).StringCellValue, fmt.FormatCellValue(cell_d19000102));
// There is no such thing as 00/01/1900...
Assert.AreEqual("00/01/1900 06:14:24", s.GetRow(21).GetCell(3).StringCellValue);
Assert.AreEqual("31/12/1899 06:14:24", fmt.FormatCellValue(cell_d19000100));
// Check the cached values
Assert.AreEqual("HOUR(A1)", s.GetRow(5).GetCell(0).CellFormula);
Assert.AreEqual(11.0, s.GetRow(5).GetCell(0).NumericCellValue);
Assert.AreEqual("MINUTE(A1)", s.GetRow(6).GetCell(0).CellFormula);
Assert.AreEqual(39.0, s.GetRow(6).GetCell(0).NumericCellValue);
Assert.AreEqual("SECOND(A1)", s.GetRow(7).GetCell(0).CellFormula);
Assert.AreEqual(54.0, s.GetRow(7).GetCell(0).NumericCellValue);
// Re-evaulate and check
HSSFFormulaEvaluator.EvaluateAllFormulaCells(wb);
Assert.AreEqual("HOUR(A1)", s.GetRow(5).GetCell(0).CellFormula);
Assert.AreEqual(11.0, s.GetRow(5).GetCell(0).NumericCellValue);
Assert.AreEqual("MINUTE(A1)", s.GetRow(6).GetCell(0).CellFormula);
Assert.AreEqual(39.0, s.GetRow(6).GetCell(0).NumericCellValue);
Assert.AreEqual("SECOND(A1)", s.GetRow(7).GetCell(0).CellFormula);
Assert.AreEqual(54.0, s.GetRow(7).GetCell(0).NumericCellValue);
// Push the time forward a bit and check
double date = s.GetRow(0).GetCell(0).NumericCellValue;
s.GetRow(0).GetCell(0).SetCellValue(date + 1.26);
HSSFFormulaEvaluator.EvaluateAllFormulaCells(wb);
Assert.AreEqual("HOUR(A1)", s.GetRow(5).GetCell(0).CellFormula);
Assert.AreEqual(11.0 + 6.0, s.GetRow(5).GetCell(0).NumericCellValue);
Assert.AreEqual("MINUTE(A1)", s.GetRow(6).GetCell(0).CellFormula);
Assert.AreEqual(39.0 + 14.0 + 1, s.GetRow(6).GetCell(0).NumericCellValue);
Assert.AreEqual("SECOND(A1)", s.GetRow(7).GetCell(0).CellFormula);
Assert.AreEqual(54.0 + 24.0 - 60, s.GetRow(7).GetCell(0).NumericCellValue);
}
public void TestErrors()
{
DataFormatter dfUS = new DataFormatter(System.Globalization.CultureInfo.GetCultureInfo("en-US"), true);
// Create a spreadsheet with some formula errors in it
IWorkbook wb = new HSSFWorkbook();
ISheet s = wb.CreateSheet();
IRow r = s.CreateRow(0);
ICell c = r.CreateCell(0, CellType.Error);
c.SetCellErrorValue(FormulaError.DIV0.Code);
Assert.AreEqual(FormulaError.DIV0.String, dfUS.FormatCellValue(c));
c.SetCellErrorValue(FormulaError.REF.Code);
Assert.AreEqual(FormulaError.REF.String, dfUS.FormatCellValue(c));
}
// /**
// * Drawing context to measure text
// */
//private static FontRenderContext fontRenderContext = new FontRenderContext(null, true, true);
/**
* Compute width of a column and return the result
*
* @param sheet the sheet to calculate
* @param column 0-based index of the column
* @param useMergedCells whether to use merged cells
* @return the width in pixels
*/
public static double GetColumnWidth(ISheet sheet, int column, bool useMergedCells)
{
//AttributedString str;
//TextLayout layout;
IWorkbook wb = sheet.Workbook;
DataFormatter formatter = new DataFormatter();
IFont defaultFont = wb.GetFontAt((short)0);
//str = new AttributedString((defaultChar));
//copyAttributes(defaultFont, str, 0, 1);
//layout = new TextLayout(str.Iterator, fontRenderContext);
//int defaultCharWidth = (int)layout.Advance;
Font font = IFont2Font(defaultFont);
int defaultCharWidth = TextRenderer.MeasureText("" + new String(defaultChar, 1), font).Width;
DummyEvaluator dummyEvaluator = new DummyEvaluator();
double width = -1;
using (Bitmap bmp = new Bitmap(2048, 100))
{
Graphics g = Graphics.FromImage(bmp);
//rows:
bool skipthisrow = false;
for (IEnumerator it = sheet.GetRowEnumerator(); it.MoveNext(); )
{
IRow row = (IRow)it.Current;
ICell cell = row.GetCell(column);
if (cell == null)
{
continue;
}
int colspan = 1;
for (int i = 0; i < sheet.NumMergedRegions; i++)
{
CellRangeAddress region = sheet.GetMergedRegion(i);
if (ContainsCell(region, row.RowNum, column))
{
if (!useMergedCells)
{
// If we're not using merged cells, skip this one and Move on to the next.
//continue rows;
skipthisrow = true;
}
cell = row.GetCell(region.FirstColumn);
colspan = 1 + region.LastColumn - region.FirstColumn;
}
}
if (skipthisrow)
{
continue;
}
ICellStyle style = cell.CellStyle;
NPOI.SS.UserModel.IFont font1 = wb.GetFontAt(style.FontIndex);
CellType cellType = cell.CellType;
// for formula cells we compute the cell width for the cached formula result
if (cellType == CellType.FORMULA) cellType = cell.CachedFormulaResultType;
if (cellType == CellType.STRING)
{
IRichTextString rt = cell.RichStringCellValue;
String[] lines = rt.String.Split("\n".ToCharArray());
for (int i = 0; i < lines.Length; i++)
{
String txt = lines[i] + defaultChar;
//str = new AttributedString(txt);
//copyAttributes(font, str, 0, txt.Length);
font = IFont2Font(font1);
if (rt.NumFormattingRuns > 0)
{
// TODO: support rich text fragments
}
//layout = new TextLayout(str.Iterator, fontRenderContext);
if (style.Rotation != 0)
{
/*
* Transform the text using a scale so that it's height is increased by a multiple of the leading,
* and then rotate the text before computing the bounds. The scale results in some whitespace around
* the unrotated top and bottom of the text that normally wouldn't be present if unscaled, but
* is Added by the standard Excel autosize.
*/
double angle = style.Rotation * 2.0 * Math.PI / 360.0;
//AffineTransform trans = new AffineTransform();
//trans.Concatenate(AffineTransform.GetRotateInstance(style.Rotation*2.0*Math.PI/360.0));
//trans.Concatenate(
//AffineTransform.GetScaleInstance(1, fontHeightMultiple)
//);
SizeF sf = g.MeasureString(txt, font);
double x1 = Math.Abs(sf.Height * Math.Sin(angle));
double x2 = Math.Abs(sf.Width * Math.Cos(angle));
double w = Math.Round(x1 + x2, 0, MidpointRounding.ToEven);
width = Math.Max(width, (w / colspan / defaultCharWidth) * 2 + cell.CellStyle.Indention);
//width = Math.Max(width, ((layout.GetOutline(trans).Bounds.Width / colspan) / defaultCharWidth) + cell.CellStyle.Indention);
}
else
{
//width = Math.Max(width, ((layout.Bounds.Width / colspan) / defaultCharWidth) + cell.CellStyle.Indention);
double w = Math.Round(g.MeasureString(txt, font).Width, 0, MidpointRounding.ToEven);
width = Math.Max(width, (w / colspan / defaultCharWidth) * 2 + cell.CellStyle.Indention);
}
}
}
else
{
String sval = null;
if (cellType == CellType.NUMERIC)
{
// Try to Get it formatted to look the same as excel
try
{
sval = formatter.FormatCellValue(cell, dummyEvaluator);
}
catch (Exception)
{
sval = cell.NumericCellValue.ToString("F", CultureInfo.InvariantCulture);
}
}
else if (cellType == CellType.BOOLEAN)
{
sval = cell.BooleanCellValue.ToString().ToUpper();
}
if (sval != null)
{
String txt = sval + defaultChar;
//str = new AttributedString(txt);
//copyAttributes(font, str, 0, txt.Length);
//layout = new TextLayout(str.Iterator, fontRenderContext);
if (style.Rotation != 0)
{
/*
* Transform the text using a scale so that it's height is increased by a multiple of the leading,
* and then rotate the text before computing the bounds. The scale results in some whitespace around
* the unrotated top and bottom of the text that normally wouldn't be present if unscaled, but
* is Added by the standard Excel autosize.
*/
double angle = style.Rotation * 2.0 * Math.PI / 360.0;
//AffineTransform trans = new AffineTransform();
//trans.Concatenate(AffineTransform.GetRotateInstance(style.Rotation*2.0*Math.PI/360.0));
//trans.Concatenate(
//AffineTransform.GetScaleInstance(1, fontHeightMultiple)
//);
//width = Math.Max(width, ((layout.GetOutline(trans).Bounds.Width / colspan) / defaultCharWidth) + cell.CellStyle.Indention);
SizeF sf = g.MeasureString(txt, font);
double x1 = sf.Height * Math.Sin(angle);
double x2 = sf.Width * Math.Cos(angle);
double w = Math.Round(x1 + x2, 0, MidpointRounding.ToEven);
width = Math.Max(width, (w / colspan / defaultCharWidth) * 2 + cell.CellStyle.Indention);
}
else
{
//width = Math.Max(width, ((layout.Bounds.Width / colspan) / defaultCharWidth) + cell.CellStyle.Indention);
double w = Math.Round(g.MeasureString(txt, font).Width, 0, MidpointRounding.ToEven);
width = Math.Max(width, (w * 1.0 / colspan / defaultCharWidth) * 2 + cell.CellStyle.Indention);
}
}
}
}
}
return width;
}
/// <summary>
/// Returns a string representation of the cell
/// This method returns a simple representation,
/// anthing more complex should be in user code, with
/// knowledge of the semantics of the sheet being Processed.
/// Formula cells return the formula string,
/// rather than the formula result.
/// Dates are Displayed in dd-MMM-yyyy format
/// Errors are Displayed as #ERR<errIdx>
/// </summary>
public override String ToString()
{
switch (CellType)
{
case CellType.Blank:
return "";
case CellType.Boolean:
return BooleanCellValue ? "TRUE" : "FALSE";
case CellType.Error:
return NPOI.SS.Formula.Eval.ErrorEval.GetText(((BoolErrRecord)_record).ErrorValue);
case CellType.Formula:
return CellFormula;
case CellType.Numeric:
string format = this.CellStyle.GetDataFormatString();
DataFormatter formatter = new DataFormatter();
return formatter.FormatCellValue(this);
case CellType.String:
return StringCellValue;
default:
return "Unknown Cell Type: " + CellType;
}
}
public void TestMissingRAttributeBug54288()
{
// workbook with cells missing the R attribute
XSSFWorkbook wb = (XSSFWorkbook)_testDataProvider.OpenSampleWorkbook("54288.xlsx");
// same workbook re-saved in Excel 2010, the R attribute is updated for every cell with the right value.
XSSFWorkbook wbRef = (XSSFWorkbook)_testDataProvider.OpenSampleWorkbook("54288-ref.xlsx");
XSSFSheet sheet = (XSSFSheet)wb.GetSheetAt(0);
XSSFSheet sheetRef = (XSSFSheet)wbRef.GetSheetAt(0);
Assert.AreEqual(sheetRef.PhysicalNumberOfRows, sheet.PhysicalNumberOfRows);
// Test idea: iterate over cells in the reference worksheet, they all have the R attribute set.
// For each cell from the reference sheet find the corresponding cell in the problematic file (with missing R)
// and assert that POI reads them equally:
DataFormatter formater = new DataFormatter();
foreach (IRow r in sheetRef)
{
XSSFRow rowRef = (XSSFRow)r;
XSSFRow row = (XSSFRow)sheet.GetRow(rowRef.RowNum);
Assert.AreEqual(rowRef.PhysicalNumberOfCells, row.PhysicalNumberOfCells, "number of cells in row[" + row.RowNum + "]");
foreach (ICell c in rowRef.Cells)
{
XSSFCell cellRef = (XSSFCell)c;
XSSFCell cell = (XSSFCell)row.GetCell(cellRef.ColumnIndex);
Assert.AreEqual(cellRef.ColumnIndex, cell.ColumnIndex);
Assert.AreEqual(cellRef.GetReference(), cell.GetReference());
if (!cell.GetCTCell().IsSetR())
{
Assert.IsTrue(cellRef.GetCTCell().IsSetR(), "R must e set in cellRef");
String valRef = formater.FormatCellValue(cellRef);
String val = formater.FormatCellValue(cell);
Assert.AreEqual(valRef, val);
}
}
}
}
public void Test47490()
{
XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("GeneralFormatTests.xlsx");
ISheet s = wb.GetSheetAt(1);
IRow r;
DataFormatter df = new DataFormatter();
r = s.GetRow(1);
Assert.AreEqual(1.0, r.GetCell(2).NumericCellValue);
Assert.AreEqual("General", r.GetCell(2).CellStyle.GetDataFormatString());
Assert.AreEqual("1", df.FormatCellValue(r.GetCell(2)));
Assert.AreEqual("1", df.FormatRawCellContents(1.0, -1, "@"));
Assert.AreEqual("1", df.FormatRawCellContents(1.0, -1, "General"));
r = s.GetRow(2);
Assert.AreEqual(12.0, r.GetCell(2).NumericCellValue);
Assert.AreEqual("General", r.GetCell(2).CellStyle.GetDataFormatString());
Assert.AreEqual("12", df.FormatCellValue(r.GetCell(2)));
Assert.AreEqual("12", df.FormatRawCellContents(12.0, -1, "@"));
Assert.AreEqual("12", df.FormatRawCellContents(12.0, -1, "General"));
r = s.GetRow(3);
Assert.AreEqual(123.0, r.GetCell(2).NumericCellValue);
Assert.AreEqual("General", r.GetCell(2).CellStyle.GetDataFormatString());
Assert.AreEqual("123", df.FormatCellValue(r.GetCell(2)));
Assert.AreEqual("123", df.FormatRawCellContents(123.0, -1, "@"));
Assert.AreEqual("123", df.FormatRawCellContents(123.0, -1, "General"));
}
public void Bug57482()
{
foreach (PackageAccess access in new PackageAccess[] {
PackageAccess.READ_WRITE, PackageAccess.READ
})
{
FileInfo file = HSSFTestDataSamples.GetSampleFile("57482-OnlyNumeric.xlsx");
OPCPackage pkg = OPCPackage.Open(file, access);
try
{
XSSFWorkbook wb = new XSSFWorkbook(pkg);
Assert.IsNotNull(wb.GetSharedStringSource());
Assert.AreEqual(0, wb.GetSharedStringSource().Count);
DataFormatter fmt = new DataFormatter();
XSSFSheet s = wb.GetSheetAt(0) as XSSFSheet;
Assert.AreEqual("1", fmt.FormatCellValue(s.GetRow(0).GetCell(0)));
Assert.AreEqual("11", fmt.FormatCellValue(s.GetRow(0).GetCell(1)));
Assert.AreEqual("5", fmt.FormatCellValue(s.GetRow(4).GetCell(0)));
// Add a text cell
s.GetRow(0).CreateCell(3).SetCellValue("Testing");
Assert.AreEqual("Testing", fmt.FormatCellValue(s.GetRow(0).GetCell(3)));
// Try to Write-out and read again, should only work
// in Read-write mode, not Read-only mode
try
{
wb = XSSFTestDataSamples.WriteOutAndReadBack(wb) as XSSFWorkbook;
if (access == PackageAccess.READ)
Assert.Fail("Shouln't be able to write from Read-only mode");
}
catch (InvalidOperationException e)
{
if (access == PackageAccess.READ)
{
// Expected
}
else
{
// Shouldn't occur in Write-mode
throw e;
}
}
// Check again
s = wb.GetSheetAt(0) as XSSFSheet;
Assert.AreEqual("1", fmt.FormatCellValue(s.GetRow(0).GetCell(0)));
Assert.AreEqual("11", fmt.FormatCellValue(s.GetRow(0).GetCell(1)));
Assert.AreEqual("5", fmt.FormatCellValue(s.GetRow(4).GetCell(0)));
Assert.AreEqual("Testing", fmt.FormatCellValue(s.GetRow(0).GetCell(3)));
}
finally
{
pkg.Revert();
}
}
}
public void Bug56702()
{
XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("56702.xlsx");
ISheet sheet = wb.GetSheetAt(0);
// Get wrong cell by row 8 & column 7
ICell cell = sheet.GetRow(8).GetCell(7);
Assert.AreEqual(CellType.Numeric, cell.CellType);
// Check the value - will be zero as it is <c><v/></c>
Assert.AreEqual(0.0, cell.NumericCellValue, 0.001);
// Try to format
DataFormatter formatter = new DataFormatter();
formatter.FormatCellValue(cell);
// Check the formatting
Assert.AreEqual("0", formatter.FormatCellValue(cell));
}
public void Bug54034()
{
IWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("54034.xlsx");
ISheet sheet = wb.GetSheet("Sheet1");
IRow row = sheet.GetRow(1);
ICell cell = row.GetCell(2);
Assert.IsTrue(DateUtil.IsCellDateFormatted(cell));
DataFormatter fmt = new DataFormatter();
Assert.AreEqual("yyyy\\-mm\\-dd\\Thh:mm", cell.CellStyle.GetDataFormatString());
Assert.AreEqual("2012-08-08T22:59", fmt.FormatCellValue(cell));
}
