/// <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 TestColours()
{
//DataFormatter dfUS = new DataFormatter(Locale.US);
DataFormatter dfUS = new DataFormatter(System.Globalization.CultureInfo.GetCultureInfo("en-US"));
String[] formats = new String[] {
"##.##",
"[WHITE]##.##",
"[BLACK]##.##;[RED]-##.##",
"[COLOR11]##.##;[COLOR 43]-##.00",
};
foreach (String format in formats)
{
Assert.AreEqual(
"12.34",
dfUS.FormatRawCellContents(12.343, -1, format),
"Wrong format for: " + format
);
Assert.AreEqual(
"-12.34",
dfUS.FormatRawCellContents(-12.343, -1, format),
"Wrong format for: " + format
);
}
// Ensure that random square brackets remain
Assert.AreEqual("12.34[a]", dfUS.FormatRawCellContents(12.343, -1, "##.##[a]"));
Assert.AreEqual("[ab]12.34[x]", dfUS.FormatRawCellContents(12.343, -1, "[ab]##.##[x]"));
}
//
// Initialize from a stream of Excel file
//
protected void InitializeMembers(Stream excelFileStream)
{
this.workbook = WorkbookFactory.Create(excelFileStream);
if (this.workbook != null)
{
this.dataFormatter = new DataFormatter(CultureInfo.InvariantCulture);
this.formulaEvaluator = WorkbookFactory.CreateFormulaEvaluator(this.workbook);
}
}
public void TestLocale()
{
DataFormatter dfUS = new DataFormatter(System.Globalization.CultureInfo.GetCultureInfo("en-US"));
DataFormatter dfFR = new DataFormatter(System.Globalization.CultureInfo.GetCultureInfo("fr-FR"));
Assert.AreEqual("1234", dfUS.FormatRawCellContents(1234, -1, "@"));
Assert.AreEqual("1234", dfFR.FormatRawCellContents(1234, -1, "@"));
Assert.AreEqual("12.34", dfUS.FormatRawCellContents(12.34, -1, "@"));
Assert.AreEqual("12,34", dfFR.FormatRawCellContents(12.34, -1, "@"));
}
public void TestAMPM()
{
DataFormatter dfUS = new DataFormatter(System.Globalization.CultureInfo.GetCultureInfo("en-US"));
Assert.AreEqual("06:00", dfUS.FormatRawCellContents(0.25, -1, "hh:mm"));
Assert.AreEqual("18:00", dfUS.FormatRawCellContents(0.75, -1, "hh:mm"));
Assert.AreEqual("06:00 AM", dfUS.FormatRawCellContents(0.25, -1, "hh:mm AM/PM"));
Assert.AreEqual("06:00 PM", dfUS.FormatRawCellContents(0.75, -1, "hh:mm AM/PM"));
Assert.AreEqual("1904-01-01 06:00:00 AM", dfUS.FormatRawCellContents(0.25, -1, "yyyy-mm-dd hh:mm:ss AM/PM", true));
Assert.AreEqual("1904-01-01 06:00:00 PM", dfUS.FormatRawCellContents(0.75, -1, "yyyy-mm-dd hh:mm:ss AM/PM", true));
}
public void SetUp()
{
wb = new HSSFWorkbook();
ISheet sheet = wb.CreateSheet("new sheet");
style = wb.CreateCellStyle();
IDataFormat fmt = wb.CreateDataFormat();
style.DataFormat=(fmt.GetFormat("hh:mm:ss"));
cell11 = sheet.CreateRow(0).CreateCell(0);
form = new DataFormatter();
Evaluator = new HSSFFormulaEvaluator(wb);
}
public void TestLocaleBasedFormats()
{
DataFormatter dfUS = new DataFormatter(System.Globalization.CultureInfo.GetCultureInfo("en-US"));
// Standard formats
Assert.AreEqual("63", dfUS.FormatRawCellContents(63.0, -1, "[$-1010409]General"));
Assert.AreEqual("63", dfUS.FormatRawCellContents(63.0, -1, "[$-1010409]@"));
// Regular numeric style formats
Assert.AreEqual("63", dfUS.FormatRawCellContents(63.0, -1, "[$-1010409]##"));
Assert.AreEqual("63", dfUS.FormatRawCellContents(63.0, -1, "[$-1010409]00"));
}
/**
* TODO Fix these so that they work
*/
public void DISABLEDtestCustomFormats()
{
DataFormatter dfUS = new DataFormatter(System.Globalization.CultureInfo.GetCultureInfo("en-US"), true);
String fmt;
fmt = "\"At\" H:MM AM/PM \"on\" DDDD MMMM D\",\" YYYY";
Assert.AreEqual(
"At 4:20 AM on Thursday May 17, 2007",
dfUS.FormatRawCellContents(39219.1805636921, -1, fmt)
);
fmt = "0 \"dollars and\" .00 \"cents\"";
Assert.AreEqual("19 dollars and .99 cents", dfUS.FormatRawCellContents(19.99, -1, fmt));
}
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();
}
public void TestBug54786()
{
DataFormatter formatter = new DataFormatter();
String format = "[h]\"\"h\"\" m\"\"m\"\"";
Assert.IsTrue(DateUtil.IsADateFormat(-1, format));
Assert.IsTrue(DateUtil.IsValidExcelDate(_15_MINUTES));
Assert.AreEqual("1h 0m", formatter.FormatRawCellContents(_15_MINUTES, -1, format, false));
Assert.AreEqual("0.041666667", formatter.FormatRawCellContents(_15_MINUTES, -1, "[h]'h'", false));
Assert.AreEqual("1h 0m\"", formatter.FormatRawCellContents(_15_MINUTES, -1, "[h]\"\"h\"\" m\"\"m\"\"\"", false));
Assert.AreEqual("1h", formatter.FormatRawCellContents(_15_MINUTES, -1, "[h]\"\"h\"\"", false));
Assert.AreEqual("h1", formatter.FormatRawCellContents(_15_MINUTES, -1, "\"\"h\"\"[h]", false));
Assert.AreEqual("h1", formatter.FormatRawCellContents(_15_MINUTES, -1, "\"\"h\"\"h", false));
Assert.AreEqual(" 60", formatter.FormatRawCellContents(_15_MINUTES, -1, " [m]", false));
Assert.AreEqual("h60", formatter.FormatRawCellContents(_15_MINUTES, -1, "\"\"h\"\"[m]", false));
Assert.AreEqual("m1", formatter.FormatRawCellContents(_15_MINUTES, -1, "\"\"m\"\"h", false));
try
{
Assert.AreEqual("1h 0m\"", formatter.FormatRawCellContents(_15_MINUTES, -1, "[h]\"\"h\"\" m\"\"m\"\"\"\"", false));
Assert.Fail("Catches exception because of invalid format, i.e. trailing quoting");
}
catch (Exception e)
{
//Assert.IsTrue(e.Message.Contains("Cannot format given Object as a Number"));
}
}
public void TestMMMMM()
{
//DataFormatter dfUS = new DataFormatter(Locale.US);
DataFormatter dfUS = new DataFormatter(System.Globalization.CultureInfo.GetCultureInfo("en-US"));
DateTime c = new DateTime(2010, 6, 1, 2, 0, 0, 0);
System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.GetCultureInfo("en-US");
Assert.AreEqual("2010-J-1 2:00:00", dfUS.FormatRawCellContents(
DateUtil.GetExcelDate(c, false), -1, "YYYY-MMMMM-D h:mm:ss"
));
}
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));
}
public void TestExcelStyleDateFormatterStringOnMillis()
{
// Test directly with the .000 style
SimpleDateFormat formatter1 = new ExcelStyleDateFormatter("ss.000");
CultureInfo culture = System.Globalization.CultureInfo.GetCultureInfo("en-US");
DateTime dt = DateTime.Now.Date;
Assert.AreEqual("00.001", formatter1.Format(dt.AddMilliseconds(1L), culture));
Assert.AreEqual("00.010", formatter1.Format(dt.AddMilliseconds(10L), culture));
Assert.AreEqual("00.100", formatter1.Format(dt.AddMilliseconds(100L), culture));
Assert.AreEqual("01.000", formatter1.Format(dt.AddMilliseconds(1000L), culture));
Assert.AreEqual("01.001", formatter1.Format(dt.AddMilliseconds(1001L), culture));
Assert.AreEqual("10.000", formatter1.Format(dt.AddMilliseconds(10000L), culture));
Assert.AreEqual("10.001", formatter1.Format(dt.AddMilliseconds(10001L), culture));
// Test directly with the .SSS style
SimpleDateFormat formatter2 = new ExcelStyleDateFormatter("ss.fff");
Assert.AreEqual("00.001", formatter2.Format(dt.AddMilliseconds(1L), culture));
Assert.AreEqual("00.010", formatter2.Format(dt.AddMilliseconds(10L), culture));
Assert.AreEqual("00.100", formatter2.Format(dt.AddMilliseconds(100L), culture));
Assert.AreEqual("01.000", formatter2.Format(dt.AddMilliseconds(1000L), culture));
Assert.AreEqual("01.001", formatter2.Format(dt.AddMilliseconds(1001L), culture));
Assert.AreEqual("10.000", formatter2.Format(dt.AddMilliseconds(10000L), culture));
Assert.AreEqual("10.001", formatter2.Format(dt.AddMilliseconds(10001L), culture));
// Test via DataFormatter
DataFormatter dfUS = new DataFormatter(culture, true);
Assert.AreEqual("01.010", dfUS.FormatRawCellContents(0.0000116898, -1, "ss.000"));
}
private FormatBase GetFormat(double cellValue, int formatIndex, String formatStrIn)
{
// // Might be better to separate out the n p and z formats, falling back to p when n and z are not set.
// // That however would require other code to be re factored.
// String[] formatBits = formatStrIn.split(";");
// int i = cellValue > 0.0 ? 0 : cellValue < 0.0 ? 1 : 2;
// String formatStr = (i < formatBits.length) ? formatBits[i] : formatBits[0];
String formatStr = formatStrIn;
// Excel supports positive/negative/zero, but java
// doesn't, so we need to do it specially
int firstAt = formatStr.IndexOf(';');
int lastAt = formatStr.LastIndexOf(';');
// p and p;n are ok by default. p;n;z and p;n;z;s need to be fixed.
if (firstAt != -1 && firstAt != lastAt)
{
int secondAt = formatStr.IndexOf(';', firstAt + 1);
if (secondAt == lastAt)
{ // p;n;z
if (cellValue == 0.0)
{
formatStr = formatStr.Substring(lastAt + 1);
}
else
{
formatStr = formatStr.Substring(0, lastAt);
}
}
else
{
if (cellValue == 0.0)
{ // p;n;z;s
formatStr = formatStr.Substring(secondAt + 1, lastAt - (secondAt + 1));
}
else
{
formatStr = formatStr.Substring(0, secondAt);
}
}
}
// Excel's # with value 0 will output empty where Java will output 0. This hack removes the # from the format.
if (emulateCsv && cellValue == 0.0 && formatStr.Contains("#") && !formatStr.Contains("0"))
{
formatStr = formatStr.Replace("#", "");
}
FormatBase format = (FormatBase)formats[formatStr];
if (format != null)
{
return(format);
}
// Is it one of the special built in types, General or @?
if (formatStr.Equals("General", StringComparison.CurrentCultureIgnoreCase) || "@".Equals(formatStr))
{
if (DataFormatter.IsWholeNumber(cellValue))
{
return(generalWholeNumFormat);
}
return(generalDecimalNumFormat);
}
// Build a formatter, and cache it
format = CreateFormat(cellValue, formatIndex, formatStr);
formats[formatStr] = format;
return(format);
}
public void TestOther()
{
//DataFormatter dfUS = new DataFormatter(Locale.US, true);
DataFormatter dfUS = new DataFormatter(System.Globalization.CultureInfo.GetCultureInfo("en-US"), true);
Assert.AreEqual(" 12.34 ", dfUS.FormatRawCellContents(12.34, -1, "_-* #,##0.00_-;-* #,##0.00_-;_-* \"-\"??_-;_-@_-"));
Assert.AreEqual("-12.34 ", dfUS.FormatRawCellContents(-12.34, -1, "_-* #,##0.00_-;-* #,##0.00_-;_-* \"-\"??_-;_-@_-"));
Assert.AreEqual(" - ", dfUS.FormatRawCellContents(0.0, -1, "_-* #,##0.00_-;-* #,##0.00_-;_-* \"-\"??_-;_-@_-"));
Assert.AreEqual(" $- ", dfUS.FormatRawCellContents(0.0, -1, "_-$* #,##0.00_-;-$* #,##0.00_-;_-$* \"-\"??_-;_-@_-"));
}
/**
* Compute width of a column based on a subset of the rows 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
* @param firstRow 0-based index of the first row to consider (inclusive)
* @param lastRow 0-based index of the last row to consider (inclusive)
* @return the width in pixels
*/
public static double GetColumnWidth(ISheet sheet, int column, bool useMergedCells, int firstRow, int lastRow)
{
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;
int defaultCharWidth = 0;
using (Font font = IFont2Font(defaultFont))
{
defaultCharWidth = TextRenderer.MeasureText("" + new String(defaultChar, 1), font).Width;
}
double width = -1;
for (int rowIdx = firstRow; rowIdx <= lastRow; ++rowIdx)
{
IRow row = sheet.GetRow(rowIdx);
if (row != null)
{
ICell cell = row.GetCell(column);
if (cell == null)
{
continue;
}
double cellWidth = GetCellWidth(cell, defaultCharWidth, formatter, useMergedCells);
width = Math.Max(width, cellWidth);
}
}
return width;
}
/**
* 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;
}
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 TestPaddingSpacesCSV()
{
//DataFormatter dfUS = new DataFormatter(Locale.US);
DataFormatter dfUS = new DataFormatter(System.Globalization.CultureInfo.GetCultureInfo("en-US"), true);
Assert.AreEqual("12.34 ", dfUS.FormatRawCellContents(12.343, -1, "##.##_ "));
Assert.AreEqual("-12.34 ", dfUS.FormatRawCellContents(-12.343, -1, "##.##_ "));
Assert.AreEqual(". ", dfUS.FormatRawCellContents(0.0, -1, "##.##_ "));
Assert.AreEqual("12.34 ", dfUS.FormatRawCellContents(12.343, -1, "##.##_1"));
Assert.AreEqual("-12.34 ", dfUS.FormatRawCellContents(-12.343, -1, "##.##_1"));
Assert.AreEqual(". ", dfUS.FormatRawCellContents(0.0, -1, "##.##_1"));
Assert.AreEqual("12.34 ", dfUS.FormatRawCellContents(12.343, -1, "##.##_)"));
Assert.AreEqual("-12.34 ", dfUS.FormatRawCellContents(-12.343, -1, "##.##_)"));
Assert.AreEqual(". ", dfUS.FormatRawCellContents(0.0, -1, "##.##_)"));
Assert.AreEqual(" 12.34", dfUS.FormatRawCellContents(12.343, -1, "_-##.##"));
Assert.AreEqual("- 12.34", dfUS.FormatRawCellContents(-12.343, -1, "_-##.##"));
Assert.AreEqual(" .", dfUS.FormatRawCellContents(0.0, -1, "_-##.##"));
Assert.AreEqual("12.34", dfUS.FormatRawCellContents(12.343, -1, "##.##* "));
Assert.AreEqual("-12.34", dfUS.FormatRawCellContents(-12.343, -1, "##.##* "));
Assert.AreEqual(".", dfUS.FormatRawCellContents(0.0, -1, "##.##* "));
Assert.AreEqual("12.34", dfUS.FormatRawCellContents(12.343, -1, "##.##*1"));
Assert.AreEqual("-12.34", dfUS.FormatRawCellContents(-12.343, -1, "##.##*1"));
Assert.AreEqual(".", dfUS.FormatRawCellContents(0.0, -1, "##.##*1"));
Assert.AreEqual("12.34", dfUS.FormatRawCellContents(12.343, -1, "##.##*)"));
Assert.AreEqual("-12.34", dfUS.FormatRawCellContents(-12.343, -1, "##.##*)"));
Assert.AreEqual(".", dfUS.FormatRawCellContents(0.0, -1, "##.##*)"));
Assert.AreEqual("12.34", dfUS.FormatRawCellContents(12.343, -1, "*-##.##"));
Assert.AreEqual("-12.34", dfUS.FormatRawCellContents(-12.343, -1, "*-##.##"));
Assert.AreEqual(".", dfUS.FormatRawCellContents(0.0, -1, "*-##.##"));
}
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 TestElapsedTime()
{
//DataFormatter dfUS = new DataFormatter(Locale.US);
DataFormatter dfUS = new DataFormatter(System.Globalization.CultureInfo.GetCultureInfo("en-US"));
double hour = 1.0 / 24.0;
Assert.AreEqual("01:00", dfUS.FormatRawCellContents(1 * hour, -1, "hh:mm"));
Assert.AreEqual("05:00", dfUS.FormatRawCellContents(5 * hour, -1, "hh:mm"));
Assert.AreEqual("20:00", dfUS.FormatRawCellContents(20 * hour, -1, "hh:mm"));
Assert.AreEqual("23:00", dfUS.FormatRawCellContents(23 * hour, -1, "hh:mm"));
Assert.AreEqual("00:00", dfUS.FormatRawCellContents(24 * hour, -1, "hh:mm"));
Assert.AreEqual("02:00", dfUS.FormatRawCellContents(26 * hour, -1, "hh:mm"));
Assert.AreEqual("20:00", dfUS.FormatRawCellContents(44 * hour, -1, "hh:mm"));
Assert.AreEqual("02:00", dfUS.FormatRawCellContents(50 * hour, -1, "hh:mm"));
Assert.AreEqual("01:00", dfUS.FormatRawCellContents(1 * hour, -1, "[hh]:mm"));
Assert.AreEqual("05:00", dfUS.FormatRawCellContents(5 * hour, -1, "[hh]:mm"));
Assert.AreEqual("20:00", dfUS.FormatRawCellContents(20 * hour, -1, "[hh]:mm"));
Assert.AreEqual("23:00", dfUS.FormatRawCellContents(23 * hour, -1, "[hh]:mm"));
Assert.AreEqual("24:00", dfUS.FormatRawCellContents(24 * hour, -1, "[hh]:mm"));
Assert.AreEqual("26:00", dfUS.FormatRawCellContents(26 * hour, -1, "[hh]:mm"));
Assert.AreEqual("44:00", dfUS.FormatRawCellContents(44 * hour, -1, "[hh]:mm"));
Assert.AreEqual("50:00", dfUS.FormatRawCellContents(50 * hour, -1, "[hh]:mm"));
Assert.AreEqual("01", dfUS.FormatRawCellContents(1 * hour, -1, "[hh]"));
Assert.AreEqual("05", dfUS.FormatRawCellContents(5 * hour, -1, "[hh]"));
Assert.AreEqual("20", dfUS.FormatRawCellContents(20 * hour, -1, "[hh]"));
Assert.AreEqual("23", dfUS.FormatRawCellContents(23 * hour, -1, "[hh]"));
Assert.AreEqual("24", dfUS.FormatRawCellContents(24 * hour, -1, "[hh]"));
Assert.AreEqual("26", dfUS.FormatRawCellContents(26 * hour, -1, "[hh]"));
Assert.AreEqual("44", dfUS.FormatRawCellContents(44 * hour, -1, "[hh]"));
Assert.AreEqual("50", dfUS.FormatRawCellContents(50 * hour, -1, "[hh]"));
double minute = 1.0 / 24.0 / 60.0;
Assert.AreEqual("01:00", dfUS.FormatRawCellContents(1 * minute, -1, "[mm]:ss"));
Assert.AreEqual("05:00", dfUS.FormatRawCellContents(5 * minute, -1, "[mm]:ss"));
Assert.AreEqual("20:00", dfUS.FormatRawCellContents(20 * minute, -1, "[mm]:ss"));
Assert.AreEqual("23:00", dfUS.FormatRawCellContents(23 * minute, -1, "[mm]:ss"));
Assert.AreEqual("24:00", dfUS.FormatRawCellContents(24 * minute, -1, "[mm]:ss"));
Assert.AreEqual("26:00", dfUS.FormatRawCellContents(26 * minute, -1, "[mm]:ss"));
Assert.AreEqual("44:00", dfUS.FormatRawCellContents(44 * minute, -1, "[mm]:ss"));
Assert.AreEqual("50:00", dfUS.FormatRawCellContents(50 * minute, -1, "[mm]:ss"));
Assert.AreEqual("59:00", dfUS.FormatRawCellContents(59 * minute, -1, "[mm]:ss"));
Assert.AreEqual("60:00", dfUS.FormatRawCellContents(60 * minute, -1, "[mm]:ss"));
Assert.AreEqual("61:00", dfUS.FormatRawCellContents(61 * minute, -1, "[mm]:ss"));
Assert.AreEqual("119:00", dfUS.FormatRawCellContents(119 * minute, -1, "[mm]:ss"));
Assert.AreEqual("120:00", dfUS.FormatRawCellContents(120 * minute, -1, "[mm]:ss"));
Assert.AreEqual("121:00", dfUS.FormatRawCellContents(121 * minute, -1, "[mm]:ss"));
Assert.AreEqual("01", dfUS.FormatRawCellContents(1 * minute, -1, "[mm]"));
Assert.AreEqual("05", dfUS.FormatRawCellContents(5 * minute, -1, "[mm]"));
Assert.AreEqual("20", dfUS.FormatRawCellContents(20 * minute, -1, "[mm]"));
Assert.AreEqual("23", dfUS.FormatRawCellContents(23 * minute, -1, "[mm]"));
Assert.AreEqual("24", dfUS.FormatRawCellContents(24 * minute, -1, "[mm]"));
Assert.AreEqual("26", dfUS.FormatRawCellContents(26 * minute, -1, "[mm]"));
Assert.AreEqual("44", dfUS.FormatRawCellContents(44 * minute, -1, "[mm]"));
Assert.AreEqual("50", dfUS.FormatRawCellContents(50 * minute, -1, "[mm]"));
Assert.AreEqual("59", dfUS.FormatRawCellContents(59 * minute, -1, "[mm]"));
Assert.AreEqual("60", dfUS.FormatRawCellContents(60 * minute, -1, "[mm]"));
Assert.AreEqual("61", dfUS.FormatRawCellContents(61 * minute, -1, "[mm]"));
Assert.AreEqual("119", dfUS.FormatRawCellContents(119 * minute, -1, "[mm]"));
Assert.AreEqual("120", dfUS.FormatRawCellContents(120 * minute, -1, "[mm]"));
Assert.AreEqual("121", dfUS.FormatRawCellContents(121 * minute, -1, "[mm]"));
double second = 1.0 / 24.0 / 60.0 / 60.0;
Assert.AreEqual("86400", dfUS.FormatRawCellContents(86400 * second, -1, "[ss]"));
Assert.AreEqual("01", dfUS.FormatRawCellContents(1 * second, -1, "[ss]"));
Assert.AreEqual("05", dfUS.FormatRawCellContents(5 * second, -1, "[ss]"));
Assert.AreEqual("20", dfUS.FormatRawCellContents(20 * second, -1, "[ss]"));
Assert.AreEqual("23", dfUS.FormatRawCellContents(23 * second, -1, "[ss]"));
Assert.AreEqual("24", dfUS.FormatRawCellContents(24 * second, -1, "[ss]"));
Assert.AreEqual("26", dfUS.FormatRawCellContents(26 * second, -1, "[ss]"));
Assert.AreEqual("44", dfUS.FormatRawCellContents(44 * second, -1, "[ss]"));
Assert.AreEqual("50", dfUS.FormatRawCellContents(50 * second, -1, "[ss]"));
Assert.AreEqual("59", dfUS.FormatRawCellContents(59 * second, -1, "[ss]"));
Assert.AreEqual("60", dfUS.FormatRawCellContents(60 * second, -1, "[ss]"));
Assert.AreEqual("61", dfUS.FormatRawCellContents(61 * second, -1, "[ss]"));
Assert.AreEqual("119", dfUS.FormatRawCellContents(119 * second, -1, "[ss]"));
Assert.AreEqual("120", dfUS.FormatRawCellContents(120 * second, -1, "[ss]"));
Assert.AreEqual("121", dfUS.FormatRawCellContents(121 * second, -1, "[ss]"));
//boolean jdk_1_5 = System.getProperty("java.vm.version").startsWith("1.5");
//if(!jdk_1_5) {
// YK: the tests below were written under JDK 1.6 and assume that
// the rounding mode in the underlying decimal formatters is HALF_UP
// It is not so JDK 1.5 where the default rounding mode is HALV_EVEN and cannot be changed.
Assert.AreEqual("27:18:08", dfUS.FormatRawCellContents(1.1376, -1, "[h]:mm:ss"));
Assert.AreEqual("28:48:00", dfUS.FormatRawCellContents(1.2, -1, "[h]:mm:ss"));
Assert.AreEqual("29:31:12", dfUS.FormatRawCellContents(1.23, -1, "[h]:mm:ss"));
Assert.AreEqual("31:26:24", dfUS.FormatRawCellContents(1.31, -1, "[h]:mm:ss"));
Assert.AreEqual("27:18:08", dfUS.FormatRawCellContents(1.1376, -1, "[hh]:mm:ss"));
Assert.AreEqual("28:48:00", dfUS.FormatRawCellContents(1.2, -1, "[hh]:mm:ss"));
Assert.AreEqual("29:31:12", dfUS.FormatRawCellContents(1.23, -1, "[hh]:mm:ss"));
Assert.AreEqual("31:26:24", dfUS.FormatRawCellContents(1.31, -1, "[hh]:mm:ss"));
Assert.AreEqual("57:07.2", dfUS.FormatRawCellContents(.123, -1, "mm:ss.0;@"));
Assert.AreEqual("57:41.8", dfUS.FormatRawCellContents(.1234, -1, "mm:ss.0;@"));
Assert.AreEqual("57:41.76", dfUS.FormatRawCellContents(.1234, -1, "mm:ss.00;@"));
Assert.AreEqual("57:41.760", dfUS.FormatRawCellContents(.1234, -1, "mm:ss.000;@"));
Assert.AreEqual("24:00.0", dfUS.FormatRawCellContents(123456.6, -1, "mm:ss.0"));
//}
}
/// <summary>
/// Lazily the load workbook.
/// </summary>
private void LazyLoadWorkbook()
{
// Check to lazily initialize workbook object
if (_workbook == null)
{
// Check if file path is valid
if (!string.IsNullOrWhiteSpace(_dashboardFile))
{
_workbook = this.LoadWorkbook(_dashboardFile);
_formulaEvaluator = new HSSFFormulaEvaluator(_workbook);
_dataFormatter = new HSSFDataFormatter(new CultureInfo("en-US"));
}
}
}
public void TestDateWindowing()
{
//DataFormatter dfUS = new DataFormatter(Locale.US);
DataFormatter dfUS = new DataFormatter(System.Globalization.CultureInfo.GetCultureInfo("en-US"));
Assert.AreEqual("1899-12-31 00:00:00", dfUS.FormatRawCellContents(0.0, -1, "yyyy-mm-dd hh:mm:ss"));
Assert.AreEqual("1899-12-31 00:00:00", dfUS.FormatRawCellContents(0.0, -1, "yyyy-mm-dd hh:mm:ss", false));
Assert.AreEqual("1904-01-01 00:00:00", dfUS.FormatRawCellContents(0.0, -1, "yyyy-mm-dd hh:mm:ss", true));
}
/// <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 TestScientificNotation()
{
//DataFormatter dfUS = new DataFormatter(Locale.US);
DataFormatter dfUS = new DataFormatter(System.Globalization.CultureInfo.GetCultureInfo("en-US"));
Assert.AreEqual("1.23E+01", dfUS.FormatRawCellContents(12.343, -1, "0.00E+00"));
Assert.AreEqual("-1.23E+01", dfUS.FormatRawCellContents(-12.343, -1, "0.00E+00"));
Assert.AreEqual("0.00E+00", dfUS.FormatRawCellContents(0.0, -1, "0.00E+00"));
}
// /**
// * 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;
int defaultCharWidth = 0;
using (Font font = IFont2Font(defaultFont))
{
defaultCharWidth = TextRenderer.MeasureText("" + new String(defaultChar, 1), font).Width;
}
//DummyEvaluator dummyEvaluator = new DummyEvaluator();
double width = -1;
foreach (IRow row in sheet)
{
ICell cell = row.GetCell(column);
if (cell == null)
{
continue;
}
double cellWidth = GetCellWidth(cell, defaultCharWidth, formatter, useMergedCells);
width = Math.Max(width, cellWidth);
}
return width;
}
public void TestInvalidDate()
{
//DataFormatter df1 = new DataFormatter(Locale.US);
DataFormatter df1 = new DataFormatter(System.Globalization.CultureInfo.GetCultureInfo("en-US"));
//Assert.AreEqual("-1.0", df1.FormatRawCellContents(-1, -1, "mm/dd/yyyy"));
//in java -1.toString() is "-1.0", but in C# -1.ToString() is "-1".
Assert.AreEqual("-1", df1.FormatRawCellContents(-1, -1, "mm/dd/yyyy"));
//DataFormatter df2 = new DataFormatter(Locale.US);
DataFormatter df2 = new DataFormatter(System.Globalization.CultureInfo.GetCultureInfo("en-US"), true);
Assert.AreEqual("###############################################################################################################################################################################################################################################################",
df2.FormatRawCellContents(-1, -1, "mm/dd/yyyy"));
}
/**
* Processes the given sheet
*/
public void ProcessSheet(
SheetContentsHandler sheetContentsExtractor,
StylesTable styles,
ReadOnlySharedStringsTable strings,
InputStream sheetInputStream)
{
DataFormatter formatter;
if (locale == null)
{
formatter = new DataFormatter();
}
else
{
formatter = new DataFormatter(locale);
}
InputSource sheetSource = new InputSource(sheetInputStream);
SAXParserFactory saxFactory = SAXParserFactory.newInstance();
try
{
SAXParser saxParser = saxFactory.newSAXParser();
XMLReader sheetParser = saxParser.GetXMLReader();
ContentHandler handler = new XSSFSheetXMLHandler(
styles, strings, sheetContentsExtractor, formatter, formulasNotResults);
sheetParser.SetContentHandler(handler);
sheetParser.Parse(sheetSource);
}
catch (ParserConfigurationException e)
{
throw new RuntimeException("SAX Parser appears to be broken - " + e.GetMessage());
}
}
public void TestEscapes()
{
DataFormatter dfUS = new DataFormatter(System.Globalization.CultureInfo.GetCultureInfo("en-US"));
Assert.AreEqual("1901-01-01", dfUS.FormatRawCellContents(367.0, -1, "yyyy-mm-dd"));
Assert.AreEqual("1901-01-01", dfUS.FormatRawCellContents(367.0, -1, "yyyy\\-mm\\-dd"));
Assert.AreEqual("1901.01.01", dfUS.FormatRawCellContents(367.0, -1, "yyyy.mm.dd"));
Assert.AreEqual("1901.01.01", dfUS.FormatRawCellContents(367.0, -1, "yyyy\\.mm\\.dd"));
Assert.AreEqual("1901/01/01", dfUS.FormatRawCellContents(367.0, -1, "yyyy/mm/dd"));
Assert.AreEqual("1901/01/01", dfUS.FormatRawCellContents(367.0, -1, "yyyy\\/mm\\/dd"));
}
