/// <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", " ");
}
private void buildWorkbook(IWorkbook wb)
{
ISheet sh = wb.CreateSheet();
IRow row1 = sh.CreateRow(0);
IRow row2 = sh.CreateRow(1);
row3 = sh.CreateRow(2);
row1.CreateCell(0, CellType.Numeric);
row1.CreateCell(1, CellType.Numeric);
row2.CreateCell(0, CellType.Numeric);
row2.CreateCell(1, CellType.Numeric);
row3.CreateCell(0);
row3.CreateCell(1);
CellReference a1 = new CellReference("A1");
CellReference a2 = new CellReference("A2");
CellReference b1 = new CellReference("B1");
CellReference b2 = new CellReference("B2");
sh.GetRow(a1.Row).GetCell(a1.Col).SetCellValue(35);
sh.GetRow(a2.Row).GetCell(a2.Col).SetCellValue(0);
sh.GetRow(b1.Row).GetCell(b1.Col).CellFormula = (/*setter*/"A1/A2");
sh.GetRow(b2.Row).GetCell(b2.Col).CellFormula = (/*setter*/"NA()");
Evaluator = wb.GetCreationHelper().CreateFormulaEvaluator();
}
public void TestValidXSSF()
{
XSSFWorkbook wb = new XSSFWorkbook();
Evaluator = new XSSFFormulaEvaluator(wb);
Confirm(wb);
}
//
// 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 SetUp()
{
wb = HSSFTestDataSamples.OpenSampleWorkbook("TestRandBetween.xls");
Evaluator = wb.GetCreationHelper().CreateFormulaEvaluator();
ISheet sheet = wb.CreateSheet("RandBetweenSheet");
IRow row = sheet.CreateRow(0);
bottomValueCell = row.CreateCell(0);
topValueCell = row.CreateCell(1);
formulaCell = row.CreateCell(2, CellType.FORMULA);
}
private static void TestPlainValueCache(HSSFWorkbook wb, int numberOfSheets)
{
IRow row;
ICell cell;
//create summary sheet
ISheet summary = wb.CreateSheet("summary");
wb.SetActiveSheet(wb.GetSheetIndex(summary));
//formula referring all sheets Created below
row = summary.CreateRow(0);
ICell summaryCell = row.CreateCell(0);
summaryCell.CellFormula = ("SUM(A2:A" + (numberOfSheets + 2) + ")");
//create sheets with cells having (different) numbers
// and add a row to summary
for (int i = 1; i < numberOfSheets; i++)
{
ISheet sheet = wb.CreateSheet("new" + i);
row = sheet.CreateRow(0);
cell = row.CreateCell(0);
cell.SetCellValue(i);
row = summary.CreateRow(i);
cell = row.CreateCell(0);
cell.CellFormula = ("new" + i + "!A1");
}
//calculate
IFormulaEvaluator Evaluator = wb.GetCreationHelper().CreateFormulaEvaluator();
Evaluator.EvaluateFormulaCell(summaryCell);
}
public static IWorkbook CreateExeclFile(string templatePath)
{
if (!File.Exists(templatePath))
{
NXOpen.UI.GetUI().NXMessageBox.Show("错误", NXMessageBox.DialogType.Error, "没找到模板");
return(null);
}
FileStream fs1 = new FileStream(templatePath, FileMode.Open, FileAccess.Read);
IWorkbook workBook = null;
IFormulaEvaluator formulaEvaluator = null;
if (templatePath.IndexOf(".xlsx") > 0 || templatePath.IndexOf(".xlsm") > 0) // 2007版本
{
workBook = new XSSFWorkbook(fs1);
formulaEvaluator = new XSSFFormulaEvaluator(workBook);
}
else if (templatePath.IndexOf(".xls") > 0) // 2003版本
{
workBook = new HSSFWorkbook(fs1);
formulaEvaluator = new HSSFFormulaEvaluator(workBook);
}
return(workBook);
}
public void TestRounding_bug51339()
{
IWorkbook wb = _testDataProvider.CreateWorkbook();
ISheet sheet = wb.CreateSheet("Sheet1");
IRow row = sheet.CreateRow(0);
ICell cellA1 = row.CreateCell(0);
cellA1.SetCellValue(2162.615d);
ICell cellB1 = row.CreateCell(1);
cellB1.CellFormula = (/*setter*/ "round(a1,2)");
ICell cellC1 = row.CreateCell(2);
cellC1.CellFormula = (/*setter*/ "roundup(a1,2)");
ICell cellD1 = row.CreateCell(3);
cellD1.CellFormula = (/*setter*/ "rounddown(a1,2)");
IFormulaEvaluator fe = wb.GetCreationHelper().CreateFormulaEvaluator();
Assert.AreEqual(2162.62, fe.EvaluateInCell(cellB1).NumericCellValue, 0.0);
Assert.AreEqual(2162.62, fe.EvaluateInCell(cellC1).NumericCellValue, 0.0);
Assert.AreEqual(2162.61, fe.EvaluateInCell(cellD1).NumericCellValue, 0.0);
}
/**
* Test invoking saved ATP functions
*
* @param TestFile either atp.xls or atp.xlsx
*/
public void BaseTestInvokeATP(string testFile)
{
IWorkbook wb = _testDataProvider.OpenSampleWorkbook(testFile);
IFormulaEvaluator Evaluator = wb.GetCreationHelper().CreateFormulaEvaluator();
ISheet sh = wb.GetSheetAt(0);
// these two are not imlemented in r
Assert.AreEqual("DELTA(1.3,1.5)", sh.GetRow(0).GetCell(1).CellFormula);
Assert.AreEqual("COMPLEX(2,4)", sh.GetRow(1).GetCell(1).CellFormula);
ICell cell2 = sh.GetRow(2).GetCell(1);
Assert.AreEqual("ISODD(2)", cell2.CellFormula);
Assert.AreEqual(false, Evaluator.Evaluate(cell2).BooleanValue);
Assert.AreEqual(CellType.Boolean, Evaluator.EvaluateFormulaCell(cell2));
ICell cell3 = sh.GetRow(3).GetCell(1);
Assert.AreEqual("ISEVEN(2)", cell3.CellFormula);
Assert.AreEqual(true, Evaluator.Evaluate(cell3).BooleanValue);
Assert.AreEqual(CellType.Boolean, Evaluator.EvaluateFormulaCell(cell3));
}
private static void checkAndFillDataRow(int rowIndex, IRow rowObj, IFormulaEvaluator evaluator, DataTable dt)
{
if (rowObj != null)
{
bool hasValue = false;
List <object> cells = new List <object>();
for (int j = 0; j < dt.Columns.Count; j++)
{
object objValue = null;
ICell cell = rowObj.GetCell(j);
if (cell != null)
{
if (cell is HSSFCell)
{
objValue = getValueTypeForXLS((HSSFCell)cell);
}
else if (cell is XSSFCell)
{
objValue = getValueTypeForXLSX((XSSFCell)cell);
}
}
cells.Add(objValue != null ? objValue.ToString().Trim() : string.Empty);
if (objValue != null && !string.IsNullOrEmpty(objValue.ToString().Trim()))
{
hasValue = true;
}
}
if (hasValue)
{
dt.Rows.Add(cells.ToArray());
}
}
}
public string tableStyle = "border-collapse: collapse;";//font-family: helvetica, arial, sans-serif;
public string GetHtMLNPOI(string excelfilePath)
{
Workbook = GetWorkbook(excelfilePath);
var numberofSheets = Workbook.NumberOfSheets;
var worksheet = Workbook.GetSheetAt(0);
IFormulaEvaluator evaluator = Workbook.GetCreationHelper().CreateFormulaEvaluator();
var rowbreaks = worksheet.RowBreaks;
StringBuilder builder = new StringBuilder();
for (int i = 0; i <= worksheet.LastRowNum; i++)
{
var row = worksheet.GetRow(i);
if (row == null)
{
builder.AppendLine("<tr></tr>");
continue;
}
if (row.Hidden.HasValue)
{
if (!row.Hidden.Value)
{
builder.AppendLine("<tr>");
foreach (var cell in row)
{
var columnWidth = worksheet.GetColumnWidthInPixels(cell.ColumnIndex);
var style = GetCellStyle(cell, columnWidth);
builder.AppendLine($"<td style=\"{style}\">{cell.GetFormattedCellValue(evaluator)}</td>");
}
builder.AppendLine("</tr>");
}
}
}
return($"<table style=\"{tableStyle}\">{builder}</table>");
}
/// <summary>
/// 获取单元格的显示值
/// </summary>
/// <param name="evaluator">单元格公式计算器</param>
/// <param name="cell">单元格</param>
/// <returns>单元格显示的值</returns>
private string GetCellValue(IFormulaEvaluator evaluator, ICell cell)
{
switch (cell.CellType)
{
case CellType.Blank:
return(string.Empty);
case CellType.Boolean:
return(cell.BooleanCellValue.ToString());
case CellType.Error:
return(cell.ErrorCellValue.ToString());
case CellType.Formula:
cell = evaluator.EvaluateInCell(cell);
return(GetCellValue(evaluator, cell));
case CellType.Numeric:
if (DateUtil.IsCellDateFormatted(cell))
{
return(cell.DateCellValue.ToString());
}
else
{
return(cell.NumericCellValue.ToString());
}
case CellType.String:
return(cell.StringCellValue);
case CellType.Unknown:
return("Unknow");
default:
return("");
}
}
/// <summary>
/// Get the index value of the last row with actual data in it from the specified sheet.
/// </summary>
/// <param name="sheet"> the sheet containing the data. </param>
/// <returns> index of the last row with actual data in it.</returns>
public static int GetLastRowWithData(ISheet sheet)
{
IFormulaEvaluator evaluator = sheet.Workbook.GetCreationHelper().CreateFormulaEvaluator();
DataFormatter formatter = new DataFormatter(true);
int lastRowIndex = -1;
if (sheet.PhysicalNumberOfRows > 0)
{
// getLastRowNum() actually returns an index, not a row number
lastRowIndex = sheet.LastRowNum;
// now, start at end of spreadsheet and work our way backwards until we find a row having data
for ( ; lastRowIndex >= 0; lastRowIndex--)
{
IRow row = sheet.GetRow(lastRowIndex);
if (!IsRowEmpty(row, evaluator, formatter))
{
break;
}
}
}
return(lastRowIndex);
}
public void TestFullColumnRefs()
{
IWorkbook wb = _testDataProvider.CreateWorkbook();
ISheet sheet = wb.CreateSheet("Sheet1");
IRow row = sheet.CreateRow(0);
ICell cell0 = row.CreateCell(0);
cell0.CellFormula = (/*setter*/ "sum(D:D)");
ICell cell1 = row.CreateCell(1);
cell1.CellFormula = (/*setter*/ "sum(D:E)");
// some values in column D
SetValue(sheet, 1, 3, 5.0);
SetValue(sheet, 2, 3, 6.0);
SetValue(sheet, 5, 3, 7.0);
SetValue(sheet, 50, 3, 8.0);
// some values in column E
SetValue(sheet, 1, 4, 9.0);
SetValue(sheet, 2, 4, 10.0);
SetValue(sheet, 30000, 4, 11.0);
// some other values
SetValue(sheet, 1, 2, 100.0);
SetValue(sheet, 2, 5, 100.0);
SetValue(sheet, 3, 6, 100.0);
IFormulaEvaluator fe = wb.GetCreationHelper().CreateFormulaEvaluator();
Assert.AreEqual(26.0, fe.Evaluate(cell0).NumberValue, 0.0);
Assert.AreEqual(56.0, fe.Evaluate(cell1).NumberValue, 0.0);
wb.Close();
}
private void TestIFEqualsFormulaEvaluation_evaluateInCell(
String formula, CellType cellType, String expectedFormula, double expectedResult)
{
IWorkbook wb = TestIFEqualsFormulaEvaluation_setup(formula, cellType);
ICell D1 = wb.GetSheet("IFEquals").GetRow(0).GetCell(3);
IFormulaEvaluator eval = wb.GetCreationHelper().CreateFormulaEvaluator();
ICell result = eval.EvaluateInCell(D1);
// Call should modify the contents and replace the formula with the result
Assert.AreSame(D1, result); // returns the same cell that was provided as an argument so that calls can be chained.
try
{
string tmp = D1.CellFormula;
Assert.Fail("cell formula should be overwritten with formula result");
}
catch (InvalidOperationException)
{
}
Assert.AreEqual(CellType.Numeric, D1.CellType);
Assert.AreEqual(expectedResult, D1.NumericCellValue, EPSILON);
TestIFEqualsFormulaEvaluation_teardown(wb);
}
/**
* Builds new numeric cache Container.
* @param marker data marker to use for cache Evaluation
* @param ctNumRef parent number reference
* @return numeric cache instance
*/
internal static XSSFNumberCache BuildCache(DataMarker marker, CT_NumRef ctNumRef)
{
CellRangeAddress range = marker.Range;
int numOfPoints = range.NumberOfCells;
if (numOfPoints == 0)
{
// Nothing to do.
return(null);
}
XSSFNumberCache cache = new XSSFNumberCache(ctNumRef.AddNewNumCache());
cache.SetPointCount(numOfPoints);
IWorkbook wb = marker.Sheet.Workbook;
IFormulaEvaluator Evaluator = wb.GetCreationHelper().CreateFormulaEvaluator();
CellWalk cellWalk = new CellWalk(marker);
NumCacheCellHandler numCacheHandler = new NumCacheCellHandler(Evaluator, cache.ctNumData);
cellWalk.Traverse(numCacheHandler);
return(cache);
}
public void TestSimpleArithmetic()
{
IWorkbook wb = _testDataProvider.CreateWorkbook();
ISheet s = wb.CreateSheet();
IRow r = s.CreateRow(0);
ICell c1 = r.CreateCell(0);
c1.CellFormula = (/*setter*/ "1+5");
Assert.AreEqual(0.0, c1.NumericCellValue, 0.0);
ICell c2 = r.CreateCell(1);
c2.CellFormula = (/*setter*/ "10/2");
Assert.AreEqual(0.0, c2.NumericCellValue, 0.0);
IFormulaEvaluator fe = wb.GetCreationHelper().CreateFormulaEvaluator();
fe.EvaluateFormulaCell(c1);
fe.EvaluateFormulaCell(c2);
Assert.AreEqual(6.0, c1.NumericCellValue, 0.0001);
Assert.AreEqual(5.0, c2.NumericCellValue, 0.0001);
}
private static DataTable ExcelToDataTableFirstRowAsHeader(ISheet sheet, IFormulaEvaluator evaluator)
{
using (DataTable dt = new DataTable())
{
IRow firstRow = sheet.GetRow(0) as IRow; int cellCount = GetCellCount(sheet);
for (int i = 0; i < cellCount; i++)
{
if (firstRow.GetCell(i) != null)
{
dt.Columns.Add(firstRow.GetCell(i).StringCellValue ?? string.Format("F{0}", i + 1), typeof(string));
}
else
{
dt.Columns.Add(string.Format("F{0}", i + 1), typeof(string));
}
}
for (int i = 1; i <= sheet.LastRowNum; i++)
{
IRow row = sheet.GetRow(i) as IRow; DataRow dr = dt.NewRow();
FillDataRowByHSSFRow(row, evaluator, ref dr); dt.Rows.Add(dr);
}
dt.TableName = sheet.SheetName; return(dt);
}
}
public void TestSetTypeStringOnFormulaCell()
{
ICell cellA1 = CreateACell();
IFormulaEvaluator fe = cellA1.Sheet.Workbook.GetCreationHelper().CreateFormulaEvaluator();
cellA1.CellFormula = ("\"DEF\"");
fe.ClearAllCachedResultValues();
fe.EvaluateFormulaCell(cellA1);
Assert.AreEqual("DEF", cellA1.StringCellValue);
cellA1.SetCellType(CellType.String);
Assert.AreEqual("DEF", cellA1.StringCellValue);
cellA1.CellFormula = ("25.061");
fe.ClearAllCachedResultValues();
fe.EvaluateFormulaCell(cellA1);
ConfirmCannotReadString(cellA1);
Assert.AreEqual(25.061, cellA1.NumericCellValue, 0.0);
cellA1.SetCellType(CellType.String);
Assert.AreEqual("25.061", cellA1.StringCellValue);
cellA1.CellFormula = ("TRUE");
fe.ClearAllCachedResultValues();
fe.EvaluateFormulaCell(cellA1);
ConfirmCannotReadString(cellA1);
Assert.AreEqual(true, cellA1.BooleanCellValue);
cellA1.SetCellType(CellType.String);
Assert.AreEqual("TRUE", cellA1.StringCellValue);
cellA1.CellFormula = ("#NAME?");
fe.ClearAllCachedResultValues();
fe.EvaluateFormulaCell(cellA1);
ConfirmCannotReadString(cellA1);
Assert.AreEqual(ErrorConstants.ERROR_NAME, cellA1.ErrorCellValue);
cellA1.SetCellType(CellType.String);
Assert.AreEqual("#NAME?", cellA1.StringCellValue);
}
public void TestSheetLevelFormulas()
{
IWorkbook wb = _testDataProvider.CreateWorkbook();
IRow row;
ISheet sh1 = wb.CreateSheet("Sheet1");
IName nm1 = wb.CreateName();
nm1.NameName = (/*setter*/ "sales_1");
nm1.SheetIndex = (/*setter*/ 0);
nm1.RefersToFormula = (/*setter*/ "Sheet1!$A$1");
row = sh1.CreateRow(0);
row.CreateCell(0).SetCellValue(3);
row.CreateCell(1).CellFormula = (/*setter*/ "sales_1");
row.CreateCell(2).CellFormula = (/*setter*/ "sales_1*2");
ISheet sh2 = wb.CreateSheet("Sheet2");
IName nm2 = wb.CreateName();
nm2.NameName = (/*setter*/ "sales_1");
nm2.SheetIndex = (/*setter*/ 1);
nm2.RefersToFormula = (/*setter*/ "Sheet2!$A$1");
row = sh2.CreateRow(0);
row.CreateCell(0).SetCellValue(5);
row.CreateCell(1).CellFormula = (/*setter*/ "sales_1");
row.CreateCell(2).CellFormula = (/*setter*/ "sales_1*3");
IFormulaEvaluator Evaluator = wb.GetCreationHelper().CreateFormulaEvaluator();
Assert.AreEqual(3.0, Evaluator.Evaluate(sh1.GetRow(0).GetCell(1)).NumberValue, 0.0);
Assert.AreEqual(6.0, Evaluator.Evaluate(sh1.GetRow(0).GetCell(2)).NumberValue, 0.0);
Assert.AreEqual(5.0, Evaluator.Evaluate(sh2.GetRow(0).GetCell(1)).NumberValue, 0.0);
Assert.AreEqual(15.0, Evaluator.Evaluate(sh2.GetRow(0).GetCell(2)).NumberValue, 0.0);
}
public void TestEvaluate()
{
TestCases.CultureShim.SetCurrentCulture("en-US");
HSSFWorkbook wb = new HSSFWorkbook();
ISheet sh = wb.CreateSheet();
ICell cell1 = sh.CreateRow(0).CreateCell(0);
cell1.CellFormula = ("MROUND(10, 3)");
ICell cell2 = sh.CreateRow(0).CreateCell(0);
cell2.CellFormula = ("MROUND(-10, -3)");
ICell cell3 = sh.CreateRow(0).CreateCell(0);
cell3.CellFormula = ("MROUND(1.3, 0.2)");
ICell cell4 = sh.CreateRow(0).CreateCell(0);
cell4.CellFormula = ("MROUND(5, -2)");
ICell cell5 = sh.CreateRow(0).CreateCell(0);
cell5.CellFormula = ("MROUND(5, 0)");
double accuracy = 1E-9;
IFormulaEvaluator Evaluator = wb.GetCreationHelper().CreateFormulaEvaluator();
Assert.AreEqual(9.0, Evaluator.Evaluate(cell1).NumberValue, accuracy, "Rounds 10 to a nearest multiple of 3 (9)");
Assert.AreEqual(-9.0, Evaluator.Evaluate(cell2).NumberValue, accuracy, "Rounds -10 to a nearest multiple of -3 (-9)");
Assert.AreEqual(1.4, Evaluator.Evaluate(cell3).NumberValue, accuracy, "Rounds 1.3 to a nearest multiple of 0.2 (1.4)");
Assert.AreEqual(ErrorEval.NUM_ERROR.ErrorCode, Evaluator.Evaluate(cell4).ErrorValue, "Returns an error, because -2 and 5 have different signs (#NUM!)");
Assert.AreEqual(0.0, Evaluator.Evaluate(cell5).NumberValue, "Returns 0 because the multiple is 0");
}
private static DataTable ExcelToDataTable(ISheet sheet, IFormulaEvaluator evaluator)
{
using (DataTable dt = new DataTable())
{
if (sheet.LastRowNum != 0)
{
int cellCount = GetCellCount(sheet);
for (int i = 0; i < cellCount; i++)
{
dt.Columns.Add(string.Format("F{0}", i), typeof(string));
}
for (int i = 0; i < sheet.FirstRowNum; ++i)
{
DataRow dr = dt.NewRow();
dt.Rows.Add(dr);
}
for (int i = sheet.FirstRowNum; i <= sheet.LastRowNum; i++)
{
IRow row = sheet.GetRow(i);
//skip hidden rows, shiqin, 20141121
if (row != null && !row.ZeroHeight)
{
DataRow dr = dt.NewRow();
FillDataRowByHSSFRow(row, evaluator, ref dr, sheet);
dt.Rows.Add(dr);
}
}
}
dt.TableName = sheet.SheetName;
return(dt);
}
}
/**
* Loops over all cells in all sheets of the supplied
* workbook.
* For cells that contain formulas, their formulas are
* evaluated, and the results are saved. These cells
* remain as formula cells.
* For cells that do not contain formulas, no changes
* are made.
* This is a helpful wrapper around looping over all
* cells, and calling evaluateFormulaCell on each one.
*/
public static void EvaluateAllFormulaCells(IWorkbook wb)
{
IFormulaEvaluator evaluator = wb.GetCreationHelper().CreateFormulaEvaluator();
EvaluateAllFormulaCells(wb, evaluator);
}
private static void EvaluateAllFormulaCells(IWorkbook wb, IFormulaEvaluator evaluator)
{
for (int i = 0; i < wb.NumberOfSheets; i++)
{
ISheet sheet = wb.GetSheetAt(i);
for (IEnumerator it = sheet.GetRowEnumerator(); it.MoveNext(); )
{
IRow r = (IRow)it.Current;
foreach (ICell c in r.Cells)
{
if (c.CellType == CellType.FORMULA)
{
evaluator.EvaluateFormulaCell(c);
}
}
}
}
}
public static DataTable ExcelImportToDataTable(FileStream file)
{
DataTable dt = new DataTable();
//.xlsx //.xlsm
#region .xlsx文件处理:XSSFWorkbook
try
{
IWorkbook wb = WorkbookFactory.Create(file);
ISheet sheet = wb.GetSheetAt(0);//.GetSheet(sheetName);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
IRow headerRow = sheet.GetRow(0);
//一行最后一个方格的编号 即总的列数
for (int j = 0; j < sheet.GetRow(0).LastCellNum; j++)
{
//SET EVERY COLUMN NAME
ICell cell = headerRow.GetCell(j);
if (cell != null && cell.ToString() != "")
{
dt.Columns.Add(cell.ToString());
}
else
{
continue;
}
}
int colCount = dt.Columns.Count;
while (rows.MoveNext())
{
//IRow row = (XSSFRow)sheet.GetRow(j);
IRow row = (IRow)rows.Current;
DataRow dr = dt.NewRow();
bool addDR = false;
if (row.RowNum == 0)
{
continue;//The firt row is title,no need import
}
for (int i = 0; i < colCount; i++)
{
//cell count>column count,then break //每条记录的单元格数量不能大于表格栏位数量 20140213
//cell count>column count,then break //每条记录的单元格数量不能大于DataTable的title
if (i >= colCount)
{
break;
}
ICell cell = row.GetCell(i);
if ((i == 0) && (string.IsNullOrEmpty(cell.ToString()) == true))//每行第一个cell为空,break
{
break;
}
if (cell != null)
{
object o = cell;
//读取Excel格式，根据格式读取数据类型
switch (cell.CellType)
{
case CellType.Blank: //空数据类型处理
o = "";
break;
case CellType.String: //字符串类型
o = cell.StringCellValue;
break;
case CellType.Numeric: //数字类型
if (DateUtil.IsCellDateFormatted(cell))
{
o = cell.DateCellValue;
}
else
{
o = cell.ToString();
}
break;
case CellType.Formula:
//HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(hssfworkbook);
IFormulaEvaluator e = WorkbookFactory.CreateFormulaEvaluator(wb);
o = e.Evaluate(cell).StringValue;
break;
default:
o = "";
break;
}
dr[i] = Convert.ToString(o);//row.GetCell(j).StringCellValue;
addDR = true;
}
}
if (addDR)
{
dt.Rows.Add(dr);
}
}
}
catch (Exception e)
{
throw e;
}
#endregion
return(dt);
}
/**
*
* @return a constant from the local Result class denoting whether there were any Evaluation
* cases, and whether they all succeeded.
*/
private int ProcessFunctionRow(IFormulaEvaluator Evaluator, String targetFunctionName,
IRow formulasRow, IRow expectedValuesRow)
{
int result = Result.NO_EVALUATIONS_FOUND; // so far
short endcolnum = formulasRow.LastCellNum;
// iterate across the row for all the Evaluation cases
for (short colnum = SS.COLUMN_INDEX_FIRST_TEST_VALUE; colnum < endcolnum; colnum++)
{
ICell c = formulasRow.GetCell(colnum);
if (c == null || c.CellType != CellType.Formula)
{
continue;
}
if (IsIgnoredFormulaTestCase(c.CellFormula))
{
continue;
}
CellValue actualValue;
try
{
actualValue = Evaluator.Evaluate(c);
}
catch (RuntimeException e)
{
_EvaluationFailureCount++;
PrintshortStackTrace(System.Console.Error, e);
result = Result.SOME_EVALUATIONS_FAILED;
continue;
}
ICell expectedValueCell = GetExpectedValueCell(expectedValuesRow, colnum);
try
{
ConfirmExpectedResult("Function '" + targetFunctionName + "': Formula: " + c.CellFormula + " @ " + formulasRow.RowNum + ":" + colnum,
expectedValueCell, actualValue);
_EvaluationSuccessCount++;
if (result != Result.SOME_EVALUATIONS_FAILED)
{
result = Result.ALL_EVALUATIONS_SUCCEEDED;
}
}
catch (AssertionException e)
{
_EvaluationFailureCount++;
PrintshortStackTrace(System.Console.Error, e);
result = Result.SOME_EVALUATIONS_FAILED;
}
}
return result;
}
public NumCacheCellHandler(IFormulaEvaluator Evaluator, CT_NumData ctnumdata)
{
this.Evaluator = Evaluator;
this.ctNumData = ctnumdata;
}
/**
*
* @return a constant from the local Result class denoting whether there were any Evaluation
* cases, and whether they all succeeded.
*/
private int ProcessFunctionRow(IFormulaEvaluator Evaluator, String targetFunctionName,
String targetTestName, IRow formulasRow, ICell expectedValueCell)
{
int result = Result.NO_EVALUATIONS_FOUND; // so far
ICell c = formulasRow.GetCell(SS.COLUMN_INDEX_ACTUAL_VALUE);
if (c == null || c.CellType != CellType.Formula)
{
return result;
}
CellValue actualValue = Evaluator.Evaluate(c);
try
{
ConfirmExpectedResult("Function '" + targetFunctionName + "': Test: '" + targetTestName + "' Formula: " + c.CellFormula
+ " @ " + formulasRow.RowNum + ":" + SS.COLUMN_INDEX_ACTUAL_VALUE,
expectedValueCell, actualValue);
_EvaluationSuccessCount++;
if (result != Result.SOME_EVALUATIONS_FAILED)
{
result = Result.ALL_EVALUATIONS_SUCCEEDED;
}
}
catch (Exception e)
{
_EvaluationFailureCount++;
//printshortStackTrace(System.err, e);
Console.WriteLine(e.StackTrace);
result = Result.SOME_EVALUATIONS_FAILED;
}
return result;
}
private void ExportSheet(ISheet sheet, StreamWriter sw, IFormulaEvaluator formulaEvaluator)
{
int cellCount = sheet.GetRow(3).LastCellNum;
CellInfo[] cellInfos = new CellInfo[cellCount];
for (int i = 2; i < cellCount; i++)
{
string fieldDesc = GetCellString(sheet, 2, i);
string fieldName = GetCellString(sheet, 3, i);
string fieldType = GetCellString(sheet, 4, i);
cellInfos[i] = new CellInfo() { Name = fieldName, Type = fieldType, Desc = fieldDesc };
}
for (int i = 5; i <= sheet.LastRowNum; ++i)
{
if (GetCellString(sheet, i, 2, formulaEvaluator) == "")
{
continue;
}
StringBuilder sb = new StringBuilder();
sb.Append("{");
IRow row = sheet.GetRow(i);
for (int j = 2; j < cellCount; ++j)
{
string desc = cellInfos[j].Desc.ToLower();
if (desc.StartsWith("#"))
{
continue;
}
// s开头表示这个字段是服务端专用
if (desc.StartsWith("s") && this.isClient)
{
continue;
}
// c开头表示这个字段是客户端专用
if (desc.StartsWith("c") && !this.isClient)
{
continue;
}
string fieldValue = GetCellString(row, j, formulaEvaluator);
//if (fieldValue == "")
//{
// throw new Exception($"sheet: {sheet.SheetName} 中有空白字段 {GetCellPosition(i, j)}");
//}
if (j > 2)
{
sb.Append(",");
}
string fieldName = cellInfos[j].Name;
if (fieldName == "Id" || fieldName == "_id")
{
if (this.isClient)
{
fieldName = "Id";
}
else
{
fieldName = "_id";
}
}
string fieldType = cellInfos[j].Type;
sb.Append($"\"{fieldName}\":{Convert(fieldType, fieldValue)}");
}
sb.Append("}");
sw.WriteLine(sb.ToString());
}
}
private object GetValue(ICell cell, Type type, IFormulaEvaluator evaluator)
{
if (cell == null)
{
return(null);
}
if (cell.CellType == CellType.Blank)
{
return(null);
}
if (type == typeof(string))
{
if (cell.CellType == CellType.Numeric)
{
double cellVal = cell.NumericCellValue;
int convertedVal = Convert.ToInt32(cell.NumericCellValue);
if (convertedVal != cellVal)
{
return(cellVal.ToString());
}
return(convertedVal.ToString());
}
cell.SetCellType(CellType.String);
if (string.IsNullOrEmpty(cell.StringCellValue))
{
return("");
}
return(cell.StringCellValue);
}
if (type == typeof(float))
{
if (cell.CellType == CellType.Numeric)
{
return(Convert.ToSingle(cell.NumericCellValue));
}
try
{
return(Convert.ToSingle(this.GetStringVal(cell, evaluator)));
}
catch (Exception e)
{
this.DebugLogICell(cell, type, e);
return(0);
}
}
if (type == typeof(int))
{
if (cell.CellType == CellType.Numeric)
{
return(Convert.ToInt32(cell.NumericCellValue));
}
try
{
return(Convert.ToInt32(this.GetStringVal(cell, evaluator)));
}
catch (Exception e)
{
this.DebugLogICell(cell, type, e);
return(0);
}
}
if (type == typeof(double))
{
return(Convert.ToDouble(this.GetStringVal(cell, evaluator)));
}
if (type == typeof(long))
{
return(Convert.ToDouble(this.GetStringVal(cell, evaluator)));
}
if (type == typeof(bool))
{
return(Convert.ToBoolean(this.GetStringVal(cell, evaluator)));
}
if (type.IsEnum)
{
//return 5;
//return System.Convert.ToInt32(System.Convert.ToDouble(GetStringVal(cell, evaluator)));
cell.SetCellType(CellType.String);
return(Convert.ToInt32(Enum.Parse(type, cell.StringCellValue)));
}
return(null);
}
private static void ConfirmExpectedResult(IFormulaEvaluator Evaluator, String msg, ICell cell, double expected)
{
CellValue value = Evaluator.Evaluate(cell);
if (value.ErrorValue != 0)
throw new Exception(msg + ": " + value.FormatAsString());
Assert.AreEqual(expected, value.NumberValue, msg);
}
public object GetValor(ICell celda) {
if (celda == null)
return null;
switch (celda.CellType) {
case CellType.STRING:
//return celda.RichStringCellValue;
return celda.StringCellValue;
case CellType.NUMERIC:
if (DateUtil.IsCellDateFormatted(celda))
return celda.DateCellValue;
if (celda.CellStyle != null) {
var format = celda.CellStyle.GetDataFormatString();
//if (Regex.IsMatch(format, @"h+:mm(:ss)?"))
if (_timeRegex.IsMatch(format))
return celda.DateCellValue;
}
return celda.NumericCellValue;
/*var iformat = celda.CellStyle.DataFormat;
if(DateUtil.IsADateFormat(iformat, format)){
Console.Write("fecha");
}
if (DateUtil.IsInternalDateFormat(iformat))
return celda.DateCellValue;*/
/*if (DateUtil.IsCellDateFormatted(celda) || DateUtil.IsCellInternalDateFormatted(celda)) {
if (DateUtil.IsValidExcelDate(celda.NumericCellValue)) {
var dt = celda.DateCellValue;
return dt;
}
}
return celda.NumericCellValue;*/
case CellType.BOOLEAN:
return celda.BooleanCellValue;
case CellType.FORMULA:
try {
if (_evaluator == null)
_evaluator = Book.GetCreationHelper().CreateFormulaEvaluator();
var valorCelda = _evaluator.Evaluate(celda);
return GetValorCelda(valorCelda, celda);
} catch (Exception ex) {
return "Error en formula " + celda.CellFormula;
}
default:
break;
}
return null;
}
private static void Confirm(IFormulaEvaluator fe, ICell cell, String formula,
double expectedResult)
{
fe.ClearAllCachedResultValues();
cell.CellFormula = (formula);
CellValue cv = fe.Evaluate(cell);
if (cv.CellType != CellType.Numeric)
{
throw new AssertionException("expected numeric cell type but got " + cv.FormatAsString());
}
Assert.AreEqual(expectedResult, cv.NumberValue, 0.0);
}
public void TestSetSheetName()
{
IWorkbook wb1 = newSetSheetNameTestingWorkbook();
ISheet sh1 = wb1.GetSheetAt(0);
IName sale_2 = wb1.GetNameAt(1);
IName sale_3 = wb1.GetNameAt(2);
IName sale_4 = wb1.GetNameAt(3);
Assert.AreEqual("sale_2", sale_2.NameName);
Assert.AreEqual("'Testing 47100'!$A$1", sale_2.RefersToFormula);
Assert.AreEqual("sale_3", sale_3.NameName);
Assert.AreEqual("'Testing 47100'!$B$1", sale_3.RefersToFormula);
Assert.AreEqual("sale_4", sale_4.NameName);
Assert.AreEqual("'To be Renamed'!$A$3", sale_4.RefersToFormula);
IFormulaEvaluator Evaluator = wb1.GetCreationHelper(/*getter*/).CreateFormulaEvaluator();
ICell cell0 = sh1.GetRow(0).GetCell(0);
ICell cell1 = sh1.GetRow(1).GetCell(0);
ICell cell2 = sh1.GetRow(2).GetCell(0);
Assert.AreEqual("SUM('Testing 47100'!A1:C1)", cell0.CellFormula);
Assert.AreEqual("SUM('Testing 47100'!A1:C1,'To be Renamed'!A1:A5)", cell1.CellFormula);
Assert.AreEqual("sale_2+sale_3+'Testing 47100'!C1", cell2.CellFormula);
Assert.AreEqual(6.0, Evaluator.Evaluate(cell0).NumberValue);
Assert.AreEqual(21.0, Evaluator.Evaluate(cell1).NumberValue);
Assert.AreEqual(6.0, Evaluator.Evaluate(cell2).NumberValue);
wb1.SetSheetName(1, "47100 - First");
wb1.SetSheetName(2, "47100 - Second");
Assert.AreEqual("sale_2", sale_2.NameName);
Assert.AreEqual("'47100 - First'!$A$1", sale_2.RefersToFormula);
Assert.AreEqual("sale_3", sale_3.NameName);
Assert.AreEqual("'47100 - First'!$B$1", sale_3.RefersToFormula);
Assert.AreEqual("sale_4", sale_4.NameName);
Assert.AreEqual("'47100 - Second'!$A$3", sale_4.RefersToFormula);
Assert.AreEqual("SUM('47100 - First'!A1:C1)", cell0.CellFormula);
Assert.AreEqual("SUM('47100 - First'!A1:C1,'47100 - Second'!A1:A5)", cell1.CellFormula);
Assert.AreEqual("sale_2+sale_3+'47100 - First'!C1", cell2.CellFormula);
Evaluator.ClearAllCachedResultValues();
Assert.AreEqual(6.0, Evaluator.Evaluate(cell0).NumberValue);
Assert.AreEqual(21.0, Evaluator.Evaluate(cell1).NumberValue);
Assert.AreEqual(6.0, Evaluator.Evaluate(cell2).NumberValue);
IWorkbook wb2 = _testDataProvider.WriteOutAndReadBack(wb1);
wb1.Close();
sh1 = wb2.GetSheetAt(0);
sale_2 = wb2.GetNameAt(1);
sale_3 = wb2.GetNameAt(2);
sale_4 = wb2.GetNameAt(3);
cell0 = sh1.GetRow(0).GetCell(0);
cell1 = sh1.GetRow(1).GetCell(0);
cell2 = sh1.GetRow(2).GetCell(0);
Assert.AreEqual("sale_2", sale_2.NameName);
Assert.AreEqual("'47100 - First'!$A$1", sale_2.RefersToFormula);
Assert.AreEqual("sale_3", sale_3.NameName);
Assert.AreEqual("'47100 - First'!$B$1", sale_3.RefersToFormula);
Assert.AreEqual("sale_4", sale_4.NameName);
Assert.AreEqual("'47100 - Second'!$A$3", sale_4.RefersToFormula);
Assert.AreEqual("SUM('47100 - First'!A1:C1)", cell0.CellFormula);
Assert.AreEqual("SUM('47100 - First'!A1:C1,'47100 - Second'!A1:A5)", cell1.CellFormula);
Assert.AreEqual("sale_2+sale_3+'47100 - First'!C1", cell2.CellFormula);
Evaluator = wb2.GetCreationHelper(/*getter*/).CreateFormulaEvaluator();
Assert.AreEqual(6.0, Evaluator.Evaluate(cell0).NumberValue);
Assert.AreEqual(21.0, Evaluator.Evaluate(cell1).NumberValue);
Assert.AreEqual(6.0, Evaluator.Evaluate(cell2).NumberValue);
wb2.Close();
}
/// <summary>
/// 填充数据
/// </summary>
/// <param name="row"></param>
/// <param name="evaluator"></param>
/// <param name="dr"></param>
private static void FillDataRowByRow(IRow row, IFormulaEvaluator evaluator, ref DataRow dr)
{
if (row != null)
{
for (int j = 0; j < dr.Table.Columns.Count; j++)
{
ICell cell = row.GetCell(j);
if (cell != null)
{
switch (cell.CellType)
{
case CellType.Blank:
{
dr[j] = DBNull.Value;
break;
}
case CellType.Boolean:
{
dr[j] = cell.BooleanCellValue;
break;
}
case CellType.Numeric:
{
if (DateUtil.IsCellDateFormatted(cell))
{
dr[j] = cell.DateCellValue;
}
else
{
dr[j] = cell.NumericCellValue;
}
break;
}
case CellType.String:
{
dr[j] = cell.StringCellValue;
break;
}
case CellType.Error:
{
dr[j] = cell.ErrorCellValue;
break;
}
case CellType.Formula:
{
cell = evaluator.EvaluateInCell(cell) as HSSFCell;
dr[j] = cell.ToString();
break;
}
default:
throw new NotSupportedException(string.Format("Unsupported format type:{0}", cell.CellType));
}
}
}
}
}
/// <summary>
/// GetCellValue
/// </summary>
/// <param name="cell">cell</param>
/// <param name="propertyType">propertyType</param>
/// <param name="formulaEvaluator">formulaEvaluator</param>
/// <returns>cellValue</returns>
public static object GetCellValue([CanBeNull] this ICell cell, Type propertyType, IFormulaEvaluator formulaEvaluator = null)
{
if (cell == null || cell.CellType == CellType.Blank || cell.CellType == CellType.Error)
{
return propertyType.GetDefaultValue();
}
switch (cell.CellType)
{
case CellType.Numeric:
if (DateUtil.IsCellDateFormatted(cell))
{
if (propertyType == typeof(DateTime))
{
return cell.DateCellValue;
}
return cell.DateCellValue.ToOrDefault(propertyType);
}
if (propertyType == typeof(double))
{
return cell.NumericCellValue;
}
return cell.NumericCellValue.ToOrDefault(propertyType);
case CellType.String:
return cell.StringCellValue.ToOrDefault(propertyType);
case CellType.Boolean:
if (propertyType == typeof(bool))
{
return cell.BooleanCellValue;
}
return cell.BooleanCellValue.ToOrDefault(propertyType);
case CellType.Formula:
try
{
var evaluatedCellValue = formulaEvaluator?.Evaluate(cell);
if (evaluatedCellValue != null)
{
if (evaluatedCellValue.CellType == CellType.Blank
|| evaluatedCellValue.CellType == CellType.Error)
{
return propertyType.GetDefaultValue();
}
if (evaluatedCellValue.CellType == CellType.Numeric)
{
if (DateUtil.IsCellDateFormatted(cell))
{
if (propertyType == typeof(DateTime))
{
return cell.DateCellValue;
}
return cell.DateCellValue.ToOrDefault(propertyType);
}
if (propertyType == typeof(double))
{
return cell.NumericCellValue;
}
return evaluatedCellValue.NumberValue.ToOrDefault(propertyType);
}
if (evaluatedCellValue.CellType == CellType.Boolean)
{
if (propertyType == typeof(bool))
{
return cell.BooleanCellValue;
}
return evaluatedCellValue.BooleanValue.ToOrDefault(propertyType);
}
if (evaluatedCellValue.CellType == CellType.String)
{
return evaluatedCellValue.StringValue.ToOrDefault(propertyType);
}
return evaluatedCellValue.FormatAsString().ToOrDefault(propertyType);
}
}
catch (Exception e)
{
InvokeHelper.OnInvokeException?.Invoke(e);
}
return cell.ToString().ToOrDefault(propertyType);
default:
return cell.ToString().ToOrDefault(propertyType);
}
}
private static void ConfirmFormulaWithUnknownUDF(String expectedFormula, ICell cell, IFormulaEvaluator evaluator)
{
Assert.AreEqual(expectedFormula, cell.CellFormula);
try
{
evaluator.Evaluate(cell);
Assert.Fail("Expected NotImplementedFunctionException/NotImplementedException");
}
catch (NotImplementedException)
{
// expected
}
}
/// <summary> /// GetCellValue /// </summary> /// <typeparam name="T">Type</typeparam> /// <param name="cell">cell</param> /// <param name="formulaEvaluator"></param> /// <returns></returns> public static T GetCellValue<T>([CanBeNull] this ICell cell, IFormulaEvaluator formulaEvaluator = null) => (T)cell.GetCellValue(typeof(T), formulaEvaluator);
/// <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"));
}
}
}
private static void Confirm(IFormulaEvaluator fe, ICell cell, String formula,
ErrorEval expectedResult)
{
fe.ClearAllCachedResultValues();
cell.CellFormula=(formula);
CellValue cv = fe.Evaluate(cell);
if (cv.CellType != CellType.Error)
{
throw new AssertionException("expected error cell type but got " + cv.FormatAsString());
}
int expCode = expectedResult.ErrorCode;
if (cv.ErrorValue != expCode)
{
throw new AssertionException("Expected error '" + ErrorEval.GetText(expCode)
+ "' but got '" + cv.FormatAsString() + "'.");
}
}
public ExcelLoader(string excel_fpath)
{
this._reader = this.GetExcelDataReader(excel_fpath);
this._evaluator = this._reader.GetCreationHelper().CreateFormulaEvaluator();
}
public void Setup()
{
wb = new HSSFWorkbook();
cell = wb.CreateSheet().CreateRow(0).CreateCell(0);
fe = new HSSFFormulaEvaluator(wb);
}
/**
* Should be able to write then read formulas with references
* to cells in other files, eg '[refs/airport.xls]Sheet1'!$A$2
* or 'http://192.168.1.2/[blank.xls]Sheet1'!$A$1 .
* Additionally, if a reference to that file is provided, it should
* be possible to Evaluate them too
* TODO Fix this to Evaluate for XSSF
* TODO Fix this to work at all for HSSF
*/
// [Test]
public void bug46670()
{
IWorkbook wb = _testDataProvider.CreateWorkbook();
ISheet s = wb.CreateSheet();
IRow r1 = s.CreateRow(0);
// References to try
String ext = "xls";
if (!(wb is HSSFWorkbook))
{
ext += "x";
}
String refLocal = "'[test." + ext + "]Sheet1'!$A$2";
String refHttp = "'[http://example.com/test." + ext + "]Sheet1'!$A$2";
String otherCellText = "In Another Workbook";
// Create the references
ICell c1 = r1.CreateCell(0, CellType.Formula);
c1.CellFormula = (/*setter*/ refLocal);
ICell c2 = r1.CreateCell(1, CellType.Formula);
c2.CellFormula = (/*setter*/ refHttp);
// Check they were Set correctly
Assert.AreEqual(refLocal, c1.CellFormula);
Assert.AreEqual(refHttp, c2.CellFormula);
// Reload, and ensure they were serialised and read correctly
wb = _testDataProvider.WriteOutAndReadBack(wb);
s = wb.GetSheetAt(0);
r1 = s.GetRow(0);
c1 = r1.GetCell(0);
c2 = r1.GetCell(1);
Assert.AreEqual(refLocal, c1.CellFormula);
Assert.AreEqual(refHttp, c2.CellFormula);
// Try to Evalutate, without giving a way to Get at the other file
try
{
EvaluateCell(wb, c1);
Assert.Fail("Shouldn't be able to Evaluate without the other file");
}
catch (Exception) { }
try
{
EvaluateCell(wb, c2);
Assert.Fail("Shouldn't be able to Evaluate without the other file");
}
catch (Exception) { }
// Set up references to the other file
IWorkbook wb2 = _testDataProvider.CreateWorkbook();
wb2.CreateSheet().CreateRow(1).CreateCell(0).SetCellValue(otherCellText);
Dictionary <String, IFormulaEvaluator> evaluators = new Dictionary <String, IFormulaEvaluator>();
evaluators.Add(refLocal, wb2.GetCreationHelper().CreateFormulaEvaluator());
evaluators.Add(refHttp, wb2.GetCreationHelper().CreateFormulaEvaluator());
IFormulaEvaluator Evaluator = wb.GetCreationHelper().CreateFormulaEvaluator();
Evaluator.SetupReferencedWorkbooks(/*setter*/ evaluators);
// Try to Evaluate, with the other file
Evaluator.EvaluateFormulaCell(c1);
Evaluator.EvaluateFormulaCell(c2);
Assert.AreEqual(otherCellText, c1.StringCellValue);
Assert.AreEqual(otherCellText, c2.StringCellValue);
}
/// <summary>
/// 将单元格的值转化为字符串
/// </summary>
/// <param name="tCell"></param>
/// <returns></returns>
private string getCellStringValueAllCase(ICell tCell)
{
string tempValue = "";
switch (tCell.CellType)
{
case CellType.Blank:
break;
case CellType.Boolean:
tempValue = tCell.BooleanCellValue.ToString();
break;
case CellType.Error:
break;
case CellType.Formula:
IFormulaEvaluator fe = WorkbookFactory.CreateFormulaEvaluator(tCell.Sheet.Workbook);
var cellValue = fe.Evaluate(tCell);
switch (cellValue.CellType)
{
case CellType.Blank:
break;
case CellType.Boolean:
tempValue = cellValue.BooleanValue.ToString();
break;
case CellType.Error:
break;
case CellType.Formula:
break;
case CellType.Numeric:
tempValue = cellValue.NumberValue.ToString();
break;
case CellType.String:
tempValue = cellValue.StringValue.ToString();
break;
case CellType.Unknown:
break;
default:
break;
}
break;
case CellType.Numeric:
if (DateUtil.IsCellDateFormatted(tCell))
{
tempValue = tCell.DateCellValue.ToString("yyyy-MM-dd");
}
else
{
tempValue = tCell.NumericCellValue.ToString();
}
break;
case CellType.String:
tempValue = tCell.StringCellValue.Trim();
break;
case CellType.Unknown:
break;
default:
break;
}
return(tempValue);
}
/**
*
* Returns the Formatted value of a cell as a <c>String</c> regardless
* of the cell type. If the Excel FormatBase pattern cannot be Parsed then the
* cell value will be Formatted using a default FormatBase.
*
* When passed a null or blank cell, this method will return an empty
* String (""). Formula cells will be evaluated using the given
* {@link HSSFFormulaEvaluator} if the evaluator is non-null. If the
* evaluator is null, then the formula String will be returned. The caller
* is responsible for setting the currentRow on the evaluator
*
*
* @param cell The cell (can be null)
* @param evaluator The HSSFFormulaEvaluator (can be null)
* @return a string value of the cell
*/
public String FormatCellValue(ICell cell, IFormulaEvaluator evaluator)
{
if (cell == null)
{
return "";
}
CellType cellType = cell.CellType;
if (evaluator != null && cellType == CellType.FORMULA)
{
try
{
cellType = evaluator.EvaluateFormulaCell(cell);
}
catch (Exception e)
{
throw new Exception("Did you forget to set the current" +
" row on the HSSFFormulaEvaluator?", e);
}
}
switch (cellType)
{
case CellType.FORMULA:
// should only occur if evaluator is null
return cell.CellFormula;
case CellType.NUMERIC:
if (DateUtil.IsCellDateFormatted(cell))
{
return GetFormattedDateString(cell);
}
return GetFormattedNumberString(cell);
case CellType.STRING:
return cell.RichStringCellValue.String;
case CellType.BOOLEAN:
return cell.BooleanCellValue.ToString().ToUpper();
case CellType.BLANK:
return "";
}
throw new Exception("Unexpected celltype (" + cellType + ")");
}
/**
*
* Returns the Formatted value of a cell as a <c>String</c> regardless
* of the cell type. If the Excel FormatBase pattern cannot be Parsed then the
* cell value will be Formatted using a default FormatBase.
*
* When passed a null or blank cell, this method will return an empty
* String (""). Formula cells will be evaluated using the given
* {@link HSSFFormulaEvaluator} if the evaluator is non-null. If the
* evaluator is null, then the formula String will be returned. The caller
* is responsible for setting the currentRow on the evaluator
*
*
* @param cell The cell (can be null)
* @param evaluator The HSSFFormulaEvaluator (can be null)
* @return a string value of the cell
*/
public String FormatCellValue(ICell cell, IFormulaEvaluator evaluator)
{
if (cell == null)
{
return "";
}
CellType cellType = cell.CellType;
if (evaluator != null && cellType == CellType.Formula)
{
if (evaluator == null)
{
return cell.CellFormula;
}
cellType = evaluator.EvaluateFormulaCell(cell);
}
switch (cellType)
{
case CellType.Formula:
// should only occur if evaluator is null
return cell.CellFormula;
case CellType.Numeric:
if (DateUtil.IsCellDateFormatted(cell))
{
return GetFormattedDateString(cell);
}
return GetFormattedNumberString(cell);
case CellType.String:
return cell.RichStringCellValue.String;
case CellType.Boolean:
return cell.BooleanCellValue.ToString().ToUpper();
case CellType.Blank:
return "";
case CellType.Error:
return FormulaError.ForInt(cell.ErrorCellValue).String;
}
throw new Exception("Unexpected celltype (" + cellType + ")");
}
