public void TestBug55843b() { XSSFWorkbook wb = new XSSFWorkbook(); try { XSSFSheet sheet = wb.CreateSheet("test") as XSSFSheet; XSSFRow row = sheet.CreateRow(0) as XSSFRow; XSSFRow row2 = sheet.CreateRow(1) as XSSFRow; XSSFCell cellA2 = row2.CreateCell(0, CellType.Formula) as XSSFCell; XSSFCell cellB1 = row.CreateCell(1, CellType.Numeric) as XSSFCell; cellB1.SetCellValue(10); XSSFFormulaEvaluator formulaEvaluator = wb.GetCreationHelper().CreateFormulaEvaluator() as XSSFFormulaEvaluator; cellA2.SetCellFormula("IF(B1=0,\"\",((ROW())))"); CellValue Evaluate = formulaEvaluator.Evaluate(cellA2); System.Console.WriteLine(Evaluate); Assert.AreEqual("2", Evaluate.FormatAsString()); cellA2.CellFormula = (/*setter*/ "IF(NOT(B1=0),((ROW())),\"\")"); CellValue EvaluateN = formulaEvaluator.Evaluate(cellA2); System.Console.WriteLine(EvaluateN); Assert.AreEqual(Evaluate.ToString(), EvaluateN.ToString()); Assert.AreEqual("2", EvaluateN.FormatAsString()); } finally { wb.Close(); } }
/// <summary> /// Obtiene el valor de la celda con las coordenadas indicadas. /// </summary> /// <param name="coordenadas">Coordenadas completas en forma de texto.</param> public string ObtenerValorCoordenadas(string coordenadas) { CellReference cr = new CellReference(coordenadas); var row = sheet.GetRow(cr.Row); var cell = row.GetCell(cr.Col); if (tipo == TiposExcel.XLS) { HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(hssfwb); CellValue cellValue = evaluator.Evaluate(cell); if (cellValue.CellType == CellType.Numeric) { return(cellValue.NumberValue.ToString()); } else { return(cellValue.StringValue); } } else { XSSFFormulaEvaluator evaluator = new XSSFFormulaEvaluator(xssfwb); CellValue cellValue = evaluator.Evaluate(cell); if (cellValue.CellType == CellType.Numeric) { return(cellValue.NumberValue.ToString()); } else { return(cellValue.StringValue); } } }
private static string handleFormual(ICell cell) { string s = string.Empty; if (cell.Sheet.Workbook is NPOI.XSSF.UserModel.XSSFWorkbook) { XSSFFormulaEvaluator e = new XSSFFormulaEvaluator(cell.Sheet.Workbook); CellValue c = e.Evaluate(cell); if (c.CellType == CellType.Numeric) { s = c.NumberValue.ToString(); } else { s = c.StringValue.Trim(); } } else { HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(cell.Sheet.Workbook); CellValue c = e.Evaluate(cell); if (c.CellType == CellType.Numeric) { s = c.NumberValue.ToString(); } else { s = c.StringValue.Trim(); } } return(s); }
/// <summary> /// 返回单行内容根据长度 基于NPOI /// </summary> /// <param name="sheet">ISheet</param> /// <param name="nRow">行数</param> /// <param name="Length">长度</param> /// <returns></returns> public List <String> GetRowValues(ISheet sheet, int nRow, int Length) { List <String> sRowValues = new List <String>(); IRow cells = sheet.GetRow(nRow); if (cells != null) { for (int i = 0; i < Length; i++) { if (cells.GetCell(i) == null) { sRowValues.Add("\"\""); } else { //如果是含有公式的格子 if (cells.GetCell(i).CellType == CellType.Formula) { XSSFFormulaEvaluator eva = new XSSFFormulaEvaluator(workbook); if (eva.Evaluate(cells.GetCell(i)).CellType == CellType.Numeric) { sRowValues.Add(eva.Evaluate(cells.GetCell(i)).NumberValue.ToString()); } else { sRowValues.Add("\"" + eva.Evaluate(cells.GetCell(i)).StringValue + "\""); } } else { MyRegularExpression mre = new MyRegularExpression(); if (mre.IsNumber(cells.GetCell(i).ToString())) { sRowValues.Add(cells.GetCell(i).ToString()); } else { sRowValues.Add("\"" + cells.GetCell(i).ToString() + "\""); } } } } } return(sRowValues); }
/// <summary> /// 得到公式单元格的值 /// </summary> /// <param name="formulaValue"></param> /// <param name="cell"></param> /// <returns></returns> public virtual object GetCellValue(CellValue formulaValue, ICell cell) { if (formulaValue == null || cell == null) { return(formulaValue); } object value = null; switch (formulaValue.CellType) { case CellType.Blank: value = null; break; case CellType.Unknown: case CellType.String: value = formulaValue.StringValue; break; case CellType.Boolean: value = formulaValue.BooleanValue.ToString(CultureInfo.CurrentCulture); break; case CellType.Error: try { value = ErrorConstant.ValueOf(cell.ErrorCellValue).Text; } catch { value = cell.ErrorCellValue.ToString(); } break; case CellType.Numeric: value = formulaValue.NumberValue.ToString(CultureInfo.CurrentCulture); break; case CellType.Formula: try { HSSFFormulaEvaluator eva = new HSSFFormulaEvaluator(cell.Sheet.Workbook); value = GetCellValue(eva.Evaluate(cell), cell); } catch { XSSFFormulaEvaluator e = new XSSFFormulaEvaluator(cell.Sheet.Workbook); value = GetCellValue(e.Evaluate(cell), cell); } break; default: value = formulaValue.StringValue; break; } return(value); }
private List <Dictionary <string, string> > GetTable(ISheet sheet, int rowBegin, int colBegin, DataFormatter objDefaultFormat, XSSFFormulaEvaluator objFormulaEvaluator) { List <Dictionary <string, string> > table = new List <Dictionary <string, string> >(); string[] colNames = new string[sheet.GetRow(rowBegin).LastCellNum - colBegin]; // get the names of the columns for (int i = colBegin; i < colNames.Length + colBegin; i++) { if (sheet.GetRow(rowBegin).GetCell(i) != null) { var coll = sheet.GetRow(rowBegin).GetCell(i); objFormulaEvaluator.Evaluate(coll); String cellValueStr = objDefaultFormat.FormatCellValue(coll, objFormulaEvaluator); colNames[i] = cellValueStr; } else { return(null); } } // get the data rows from the sheet for (int row = rowBegin + 1; row <= sheet.LastRowNum; row++) { Dictionary <string, string> tableRow = new Dictionary <string, string>(); if (sheet.GetRow(row) == null) { // empty row continue; } for (int col = colBegin; col < colNames.Length + colBegin; col++) { // read the column value var coll = sheet.GetRow(row).GetCell(col); objFormulaEvaluator.Evaluate(coll); String cellValueStr = objDefaultFormat.FormatCellValue(coll, objFormulaEvaluator); tableRow[colNames[col - colBegin]] = cellValueStr; } table.Add(tableRow); } return(table); }
public static string GetCellForamtValue(ICell cell, IWorkbook workbook) { try { string value = ""; switch (cell.CellType) { case CellType.Blank: //空数据类型处理 value = ""; break; case CellType.String: //字符串类型 value = cell.StringCellValue; break; case CellType.Numeric: //数字类型 if (Utils.IsDouble(cell.NumericCellValue.ToString())) { value = cell.NumericCellValue.ToString(); } else { value = cell.DateCellValue.ToString(); } break; case CellType.Formula: if (workbook.GetType() == typeof(XSSFWorkbook)) { XSSFFormulaEvaluator e = new XSSFFormulaEvaluator(workbook); value = e.Evaluate(cell).NumberValue.ToString(); } else { HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(workbook); value = e.Evaluate(cell).NumberValue.ToString(); } //value = cell.CellFormula; break; default: value = ""; break; } return(value); } catch (Exception) { return(cell.ToString()); } }
/// <summary> /// 获取格子值 /// </summary> public string GetCellValue(ICell cell) { // 注意:内容为空的单元格有时候会为空对象 if (cell == null) { return(string.Empty); } if (cell.CellType == CellType.Blank) { return(string.Empty); } else if (cell.CellType == CellType.Numeric) { return(cell.NumericCellValue.ToString()); } else if (cell.CellType == CellType.String) { return(cell.StringCellValue); } else if (cell.CellType == CellType.Boolean) { return(cell.BooleanCellValue.ToString().ToLower()); } else if (cell.CellType == CellType.Formula) { // 注意:公式只支持数值和字符串类型 var formulaValue = _evaluator.Evaluate(cell); if (formulaValue.CellType == CellType.Numeric) { return(formulaValue.NumberValue.ToString()); } else if (formulaValue.CellType == CellType.String) { return(formulaValue.StringValue); } else { throw new Exception($"未支持的公式类型 : {formulaValue.CellType}"); } } else { throw new Exception($"未支持的单元格类型 : {cell.CellType}"); } }
private String getStringCellValue(XSSFFormulaEvaluator eva, ICell cell) { // 获取单元格数据内容为字符串类型的数据 String strCell = ""; switch (cell.CellType) { case CellType.Numeric: if (HSSFDateUtil.IsCellDateFormatted(cell)) { if (cell.DateCellValue != null) { //lynn:因为有些数据是有时分秒的,并且vourcher需要这个去判断时间 strCell = cell.DateCellValue.ToString("yyyy-MM-dd HH:mm:ss"); } else { strCell = null; } } else { //var kk = cell.ToString(); strCell = cell.NumericCellValue.ToString(); } break; case CellType.Formula: var tempType = eva.Evaluate(cell); if (tempType.CellType == CellType.Numeric) { strCell = tempType.NumberValue.ToString(); } else { strCell = tempType.StringValue; } break; default: strCell = cell.ToString(); break; } return(strCell); }
private static string ProcessFormula(ICell icel, XSSFFormulaEvaluator eval) { string value; CellValue cv = eval.Evaluate(icel); value = string.Empty; switch (cv.CellType) { case CellType.Unknown: break; case CellType.Numeric: value = cv.NumberValue.ToString(); break; case CellType.String: value = cv.StringValue; break; case CellType.Formula: break; case CellType.Blank: break; case CellType.Boolean: break; case CellType.Error: break; default: break; } return(value); }
/// <summary> /// 取一个单元格的值 /// </summary> /// <param name="row"></param> /// <param name="column"></param> /// <param name="cell"></param> /// <param name="value"></param> /// <returns></returns> private bool GetCellValue(IRow row, int column, out ICell cell, out string value) { cell = row.GetCell(column); if (cell == null) { value = null; return(false); } var style = Sheet.Workbook.CreateCellStyle(); style.CloneStyleFrom(cell.CellStyle); style.FillForegroundColor = HSSFColor.White.Index; cell.CellStyle = style; switch (cell.CellType) { case CellType.Error: WriteCellState(row, column, "数据错误,使用默认值", false); value = null; return(false); case CellType.Blank: value = null; break; case CellType.Numeric: value = GetNumericValue(cell, cell.NumericCellValue); break; case CellType.String: value = cell.StringCellValue; break; case CellType.Boolean: value = cell.BooleanCellValue.ToString(); break; case CellType.Formula: //公式列 var e = new XSSFFormulaEvaluator(Sheet.Workbook); var vc = e.Evaluate(cell); switch (vc.CellType) { case CellType.Error: WriteCellState(row, column, "数据错误,使用默认值", false); value = null; return(false); case CellType.Blank: value = null; return(false); case CellType.Numeric: value = GetNumericValue(cell, vc.NumberValue); break; case CellType.String: value = vc.StringValue; break; case CellType.Boolean: value = vc.BooleanValue.ToString(); break; default: value = vc.StringValue; break; } break; default: cell.SetCellType(CellType.String); value = cell.StringCellValue; break; } return(true); }
/// <summary> /// 将excel中的多个SHEET的数据导入到DataSet中 /// </summary> /// <param name="Content">Stream</param> /// <param name="sheetNames">多个sheet名称</param> /// <returns>返回的DataSet</returns> public DataSet GetExcelToSet07(Stream Content, string[] sheetNames) //2007 Excel { XSSFWorkbook workbook = new XSSFWorkbook(Content); DataSet dataset = new DataSet(); foreach (string sheetname in sheetNames) { int index = workbook.GetSheetIndex(sheetname); if (index == -1) { return(null); } XSSFSheet sheet = (XSSFSheet)workbook.GetSheet(sheetname); DataTable table = new DataTable(sheetname); //获取sheet的首行 XSSFRow headerRow = (XSSFRow)sheet.GetRow(0); //一行最后一个方格的编号 即总的列数 int cellCount = headerRow.LastCellNum; for (int i = headerRow.FirstCellNum; i < cellCount; i++) { DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue); table.Columns.Add(column); } //最后一列的标号 即总的行数 int rowCount = sheet.LastRowNum + 1; for (int i = (sheet.FirstRowNum + 1); i < rowCount; i++) { XSSFRow row = (XSSFRow)sheet.GetRow(i); if (row == null || row.Cells.Count == 0) { continue; } DataRow dataRow = table.NewRow(); for (int j = row.FirstCellNum; j < cellCount; j++) { if (row.GetCell(j) != null) { //dataRow[j] = row.GetCell(j).ToString(); if (row.GetCell(j).CellType == CellType.Numeric) { //NPOI中数字和日期都是NUMERIC类型的,这里对其进行判断是否是日期类型 if (HSSFDateUtil.IsCellDateFormatted(row.GetCell(j)))//日期类型 { dataRow[j] = row.GetCell(j).DateCellValue.ToString("yyyy年MM月"); } else//其他数字类型 { if (!string.IsNullOrEmpty(row.GetCell(j).CellStyle.GetDataFormatString()) && row.GetCell(j).CellStyle.GetDataFormatString().Contains("%")) { dataRow[j] = row.GetCell(j).NumericCellValue * 100; } else { dataRow[j] = row.GetCell(j).NumericCellValue; } } } else if (row.GetCell(j).CellType == CellType.Blank)//空数据类型 { dataRow[j] = ""; } else if (row.GetCell(j).CellType == CellType.Formula)//公式类型 { XSSFFormulaEvaluator eva = new XSSFFormulaEvaluator(workbook); dataRow[j] = eva.Evaluate(row.GetCell(j)).StringValue; } else //其他类型都按字符串类型来处理 { dataRow[j] = row.GetCell(j).StringCellValue; } } } table.Rows.Add(dataRow); } dataset.Tables.Add(table); } return(dataset); }
/// <summary> /// 读取excel2007 /// 当string.IsNullOrEmpty(sheetName)为TRUE时,则按sheetIndex读取 /// 否则,按sheetName读取 /// </summary> /// <param name="Content"></param> /// <returns></returns> public DataTable GetExcelToTable07(Stream Content, string sheetName, int sheetIndex = 0) //2007 Excel { XSSFWorkbook workbook = new XSSFWorkbook(Content); //获取excel的第一个sheet XSSFSheet sheet; if (string.IsNullOrEmpty(sheetName)) { sheet = (XSSFSheet)workbook.GetSheetAt(sheetIndex); } else { int index = workbook.GetSheetIndex(sheetName); if (index == -1) { return(null); } sheet = (XSSFSheet)workbook.GetSheet(sheetName); } sheet.ForceFormulaRecalculation = true; DataTable table = new DataTable(); /*try * {*/ //获取sheet的首行 XSSFRow headerRow = (XSSFRow)sheet.GetRow(0); //一行最后一个方格的编号 即总的列数 int cellCount = headerRow.LastCellNum; ICell cell = null; for (int i = headerRow.FirstCellNum; i < cellCount; i++) { cell = headerRow.GetCell(i); DataColumn column; if (headerRow.GetCell(i).CellType == CellType.Numeric) { //NPOI中数字和日期都是NUMERIC类型的,这里对其进行判断是否是日期类型 if (HSSFDateUtil.IsCellDateFormatted(cell))//日期类型 { column = new DataColumn(cell.DateCellValue.ToString("yyyy年MM月")); } else//其他数字类型 { column = new DataColumn(cell.NumericCellValue.ToString()); } } else if (cell.CellType == CellType.Blank)//空数据类型 { column = new DataColumn(""); } else //其他类型都按字符串类型来处理 { column = new DataColumn(cell.StringCellValue); } cell = null; table.Columns.Add(column); } //最后一列的标号 即总的行数 int rowCount = sheet.LastRowNum + 1; for (int i = (sheet.FirstRowNum + 1); i < rowCount; i++) { XSSFRow row = (XSSFRow)sheet.GetRow(i); if (row == null || row.Cells.Count == 0) { continue; } DataRow dataRow = table.NewRow(); for (int j = row.FirstCellNum; j < cellCount; j++) { if (row.GetCell(j) != null) { cell = row.GetCell(j); if (cell.CellType == CellType.Numeric) { //NPOI中数字和日期都是NUMERIC类型的,这里对其进行判断是否是日期类型 if (HSSFDateUtil.IsCellDateFormatted(cell))//日期类型 { if (cell.CellStyle.GetDataFormatString().Contains("日") || cell.CellStyle.GetDataFormatString().Contains("d")) { dataRow[j] = cell.DateCellValue.ToString("yyyy年MM月dd日"); } else { dataRow[j] = cell.DateCellValue.ToString("yyyy年MM月"); } } else//其他数字类型 { if (!string.IsNullOrEmpty(cell.CellStyle.GetDataFormatString()) && cell.CellStyle.GetDataFormatString().Contains("%")) { dataRow[j] = cell.NumericCellValue * 100; } else if (cell.CellStyle.DataFormat.GetHashCode() == 3735609) { dataRow[j] = cell.DateCellValue.ToString("yyyy年MM月"); } else if (cell.CellStyle.DataFormat.GetHashCode() == 2031647) { dataRow[j] = cell.DateCellValue.ToString("yyyy年MM月dd日"); } else { dataRow[j] = cell.NumericCellValue; } // 处理科学计数 if (dataRow[j].ToString().Contains("E")) { Decimal dData = 0.0M; dData = Convert.ToDecimal(Decimal.Parse(dataRow[j].ToString(), System.Globalization.NumberStyles.Float)); dataRow[j] = dData; //FileLog.WriteLog("科学计数:" + dataRow[j].ToString()); } } } else if (cell.CellType == CellType.Blank)//空数据类型 { dataRow[j] = ""; } else if (cell.CellType == CellType.Formula)//公式类型 { XSSFFormulaEvaluator eva = new XSSFFormulaEvaluator(workbook); CellValue cv = eva.Evaluate(cell); switch (cv.CellType) { case CellType.Numeric: dataRow[j] = cv.NumberValue; break; case CellType.String: dataRow[j] = cv.StringValue; break; case CellType.Error: dataRow[j] = cv.ErrorValue; break; } //FileLog.WriteLog("公式:" + dataRow[j]); } else //其他类型都按字符串类型来处理 { cell.SetCellType(CellType.String); dataRow[j] = cell.StringCellValue; } } cell = null; } table.Rows.Add(dataRow); } workbook = null; sheet = null; /*} * catch (Exception ex) * { * FileLog.WriteLog("Exception: " + ex); * table = null; * }*/ return(table); }
/// <summary>读取excel /// 默认第一行为表头 /// </summary> /// <param name="strFileName">excel文档绝对路径</param> /// <returns></returns> public DataTable ExcelToDataTable(HttpPostedFileBase file) { System.IO.Stream MyStream; int FileLen; FileLen = file.ContentLength; // 读取文件的 byte[] //byte[] bytes = new byte[FileLen]; MyStream = file.InputStream; //MyStream.Read(bytes, 0, FileLen); DataTable dt = new DataTable(); HSSFWorkbook hssfworkbook; if (file.FileName.IndexOf(".xlsx") > 0) { hssfworkbook = new HSSFWorkbook(file.InputStream); } else if (file.FileName.IndexOf(".xls") > 0) { hssfworkbook = new HSSFWorkbook(file.InputStream); } else { throw new Exception("Excel格式错误"); } ISheet sheet = hssfworkbook.GetSheetAt(0); IRow headRow = sheet.GetRow(0); int colCount = headRow.LastCellNum; List <POSs> posList = new List <POSs>(); if (headRow != null) { for (int i = 1; i <= colCount; i++) { ICell cell = headRow.GetCell(i); if (cell != null) { if (dt.Columns.Contains(cell.ToString())) { throw new Exception("Excel存在重复列,列名为:" + cell.ToString()); } dt.Columns.Add(cell.ToString()); } else { dt.Columns.Add("column" + i); } } } for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++) { IRow row = sheet.GetRow(i); bool emptyRow = true; object[] itemArray = null; if (row != null) { itemArray = new object[colCount]; if (row.FirstCellNum != -1) { for (int j = row.FirstCellNum; j < colCount; j++) { if (row.GetCell(j) != null) { switch (row.GetCell(j).CellType) { case CellType.Numeric: if (HSSFDateUtil.IsCellDateFormatted(row.GetCell(j))) //日期类型 { itemArray[j] = row.GetCell(j).DateCellValue.ToString("yyyy-MM-dd"); } else //其他数字类型 { itemArray[j] = row.GetCell(j).NumericCellValue; } break; case CellType.Blank: itemArray[j] = string.Empty; break; case CellType.Formula: if (Path.GetExtension(file.FileName).ToLower().Trim() == ".xlsx") { XSSFFormulaEvaluator eva = new XSSFFormulaEvaluator(hssfworkbook); if (eva.Evaluate(row.GetCell(j)).CellType == CellType.Numeric) { if (HSSFDateUtil.IsCellDateFormatted(row.GetCell(j))) //日期类型 { itemArray[j] = row.GetCell(j).DateCellValue.ToString("yyyy-MM-dd"); } else //其他数字类型 { itemArray[j] = row.GetCell(j).NumericCellValue; } } else { itemArray[j] = eva.Evaluate(row.GetCell(j)).StringValue; } } else { HSSFFormulaEvaluator eva = new HSSFFormulaEvaluator(hssfworkbook); if (eva.Evaluate(row.GetCell(j)).CellType == CellType.Numeric) { if (HSSFDateUtil.IsCellDateFormatted(row.GetCell(j))) //日期类型 { itemArray[j] = row.GetCell(j).DateCellValue.ToString("yyyy-MM-dd"); } else //其他数字类型 { itemArray[j] = row.GetCell(j).NumericCellValue; } } else { itemArray[j] = eva.Evaluate(row.GetCell(j)).StringValue; } } break; default: itemArray[j] = row.GetCell(j).StringCellValue; break; } if (itemArray[j] != null && !string.IsNullOrEmpty(itemArray[j].ToString().Trim())) { emptyRow = false; } } } } } //非空数据行数据添加到DataTable if (!emptyRow) { dt.Rows.Add(itemArray); } } return(dt); }
public void TestReferencesToOtherWorkbooks() { XSSFWorkbook wb = (XSSFWorkbook)_testDataProvider.OpenSampleWorkbook("ref2-56737.xlsx"); XSSFFormulaEvaluator evaluator = wb.GetCreationHelper().CreateFormulaEvaluator() as XSSFFormulaEvaluator; XSSFSheet s = wb.GetSheetAt(0) as XSSFSheet; // References to a .xlsx file IRow rXSLX = s.GetRow(2); ICell cXSLX_cell = rXSLX.GetCell(4); ICell cXSLX_sNR = rXSLX.GetCell(6); ICell cXSLX_gNR = rXSLX.GetCell(8); Assert.AreEqual("[1]Uses!$A$1", cXSLX_cell.CellFormula); Assert.AreEqual("[1]Defines!NR_To_A1", cXSLX_sNR.CellFormula); Assert.AreEqual("[1]!NR_Global_B2", cXSLX_gNR.CellFormula); Assert.AreEqual("Hello!", cXSLX_cell.StringCellValue); Assert.AreEqual("Test A1", cXSLX_sNR.StringCellValue); Assert.AreEqual(142.0, cXSLX_gNR.NumericCellValue, 0); // References to a .xls file IRow rXSL = s.GetRow(4); ICell cXSL_cell = rXSL.GetCell(4); ICell cXSL_sNR = rXSL.GetCell(6); ICell cXSL_gNR = rXSL.GetCell(8); Assert.AreEqual("[2]Uses!$C$1", cXSL_cell.CellFormula); Assert.AreEqual("[2]Defines!NR_To_A1", cXSL_sNR.CellFormula); Assert.AreEqual("[2]!NR_Global_B2", cXSL_gNR.CellFormula); Assert.AreEqual("Hello!", cXSL_cell.StringCellValue); Assert.AreEqual("Test A1", cXSL_sNR.StringCellValue); Assert.AreEqual(142.0, cXSL_gNR.NumericCellValue, 0); // Try to Evaluate without references, won't work // (At least, not unit we fix bug #56752 that is1) try { evaluator.Evaluate(cXSL_cell); Assert.Fail("Without a fix for #56752, shouldn't be able to Evaluate a " + "reference to a non-provided linked workbook"); } catch (Exception) { } // Setup the environment Dictionary <String, IFormulaEvaluator> evaluators = new Dictionary <String, IFormulaEvaluator>(); evaluators.Add("ref2-56737.xlsx", evaluator); evaluators.Add("56737.xlsx", _testDataProvider.OpenSampleWorkbook("56737.xlsx").GetCreationHelper().CreateFormulaEvaluator()); evaluators.Add("56737.xls", HSSFTestDataSamples.OpenSampleWorkbook("56737.xls").GetCreationHelper().CreateFormulaEvaluator()); evaluator.SetupReferencedWorkbooks(evaluators); // Try Evaluating all of them, ensure we don't blow up foreach (IRow r in s) { foreach (ICell c in r) { // TODO Fix and enable evaluator.Evaluate(c); } } // And evaluate the other way too evaluator.EvaluateAll(); // Static evaluator won't work, as no references passed in try { XSSFFormulaEvaluator.EvaluateAllFormulaCells(wb); Assert.Fail("Static method lacks references, shouldn't work"); } catch (Exception) { // expected here } // Evaluate specific cells and check results Assert.AreEqual("\"Hello!\"", evaluator.Evaluate(cXSLX_cell).FormatAsString()); Assert.AreEqual("\"Test A1\"", evaluator.Evaluate(cXSLX_sNR).FormatAsString()); //Assert.AreEqual("142.0", evaluator.Evaluate(cXSLX_gNR).FormatAsString()); Assert.AreEqual("142", evaluator.Evaluate(cXSLX_gNR).FormatAsString()); Assert.AreEqual("\"Hello!\"", evaluator.Evaluate(cXSL_cell).FormatAsString()); Assert.AreEqual("\"Test A1\"", evaluator.Evaluate(cXSL_sNR).FormatAsString()); //Assert.AreEqual("142.0", evaluator.Evaluate(cXSL_gNR).FormatAsString()); Assert.AreEqual("142", evaluator.Evaluate(cXSL_gNR).FormatAsString()); // Add another formula referencing these workbooks ICell cXSL_cell2 = rXSL.CreateCell(40); cXSL_cell2.CellFormula = (/*setter*/ "[56737.xls]Uses!$C$1"); // TODO Shouldn't it become [2] like the others? Assert.AreEqual("[56737.xls]Uses!$C$1", cXSL_cell2.CellFormula); Assert.AreEqual("\"Hello!\"", evaluator.Evaluate(cXSL_cell2).FormatAsString()); // Now add a formula that refers to yet another (different) workbook // Won't work without the workbook being linked ICell cXSLX_nw_cell = rXSLX.CreateCell(42); try { cXSLX_nw_cell.CellFormula = (/*setter*/ "[alt.xlsx]Sheet1!$A$1"); Assert.Fail("New workbook not linked, shouldn't be able to Add"); } catch (Exception) { } // Link and re-try IWorkbook alt = new XSSFWorkbook(); try { alt.CreateSheet().CreateRow(0).CreateCell(0).SetCellValue("In another workbook"); // TODO Implement the rest of this, see bug #57184 /* * wb.linkExternalWorkbook("alt.xlsx", alt); * * cXSLX_nw_cell.setCellFormula("[alt.xlsx]Sheet1!$A$1"); * // Check it - TODO Is this correct? Or should it become [3]Sheet1!$A$1 ? * Assert.AreEqual("[alt.xlsx]Sheet1!$A$1", cXSLX_nw_cell.getCellFormula()); * * // Evaluate it, without a link to that workbook * try { * evaluator.evaluate(cXSLX_nw_cell); * fail("No cached value and no link to workbook, shouldn't evaluate"); * } catch(Exception e) {} * * // Add a link, check it does * evaluators.put("alt.xlsx", alt.getCreationHelper().createFormulaEvaluator()); * evaluator.setupReferencedWorkbooks(evaluators); * * evaluator.evaluate(cXSLX_nw_cell); * Assert.AreEqual("In another workbook", cXSLX_nw_cell.getStringCellValue()); */ } finally { alt.Close(); } wb.Close(); }
/// <summary> /// 获取单元格的值 /// </summary> /// <param name="cell">单元格</param> /// <returns></returns> public virtual object GetCellValue(ICell cell) { if (cell == null) { return(null); } try { object value = null; switch (cell.CellType) { case CellType.Blank: //空值 3 value = null; break; case CellType.Unknown: //未知 -1 case CellType.String: //字符串型 1 value = cell.StringCellValue; break; case CellType.Boolean: //布尔 4 value = cell.BooleanCellValue; break; case CellType.Error: //错误 5 try { value = ErrorConstant.ValueOf(cell.ErrorCellValue).Text; } catch { value = cell.ErrorCellValue; } break; case CellType.Numeric: //数值型 0 if (DateUtil.IsCellDateFormatted(cell) || DateUtil.IsCellInternalDateFormatted(cell)) { value = DateTime.FromOADate(cell.NumericCellValue); } else { value = cell.NumericCellValue; } break; case CellType.Formula: //公式型 2 try { HSSFFormulaEvaluator eva = new HSSFFormulaEvaluator(cell.Sheet.Workbook); value = GetCellValue(eva.Evaluate(cell), cell); } catch { XSSFFormulaEvaluator e = new XSSFFormulaEvaluator(cell.Sheet.Workbook); value = GetCellValue(e.Evaluate(cell), cell); } break; default: value = cell.StringCellValue; break; } return(value); } catch (Exception e) { throw new Exception($"获取单元格值出错[{new CellReference(cell).FormatAsString()}]: {e.Message}", e); } }
public static void ExcelToDataSet(this DataSet ds, MemoryStream ms, List <int> sheets = null) { XSSFWorkbook workbook = new XSSFWorkbook(ms); for (int k = 0; k < workbook.NumberOfSheets; k++) { DataTable dt = new DataTable(); ISheet sheet = workbook.GetSheetAt(k); if (sheets != null && !sheets.Contains(k)) { continue; } IRow headerRow = sheet.GetRow(0); int cellCount = headerRow.LastCellNum; for (int i = headerRow.FirstCellNum; i < cellCount; i++) { DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue); dt.Columns.Add(column); } int rowCount = sheet.LastRowNum + 1; for (int i = (sheet.FirstRowNum + 1); i < rowCount; i++) { IRow row = sheet.GetRow(i); if (row != null) { bool isContinue = false; for (int j = row.FirstCellNum; j < cellCount; j++) { if (row.GetCell(j) != null) { isContinue = true; break; } } if (!isContinue) { break; } DataRow dataRow = dt.NewRow(); bool isAddToTable = false; for (int j = row.FirstCellNum; j < cellCount; j++) { //if (row.GetCell(j) != null) // dataRow[j] = row.GetCell(j).ToString(); ICell cell = row.GetCell(j); if (cell == null) { continue; } if (cell.CellType == CellType.Numeric) { //判斷是否是日期型別 if (NPOI.HSSF.UserModel.HSSFDateUtil.IsCellDateFormatted(cell))//日期型別 { dataRow[j] = cell.DateCellValue.ToString("yyyy-MM-dd HH:mm:ss"); } else//其他數字型別 { dataRow[j] = cell.NumericCellValue.ToString(); } } else if (cell.CellType == CellType.Blank)//空數據型別 { dataRow[j] = ""; } else if (cell.CellType == CellType.Formula)//公式型別 { XSSFFormulaEvaluator eva = new XSSFFormulaEvaluator(workbook); dataRow[j] = eva.Evaluate(cell).StringValue; } else if (cell.CellType == CellType.Boolean)//Boolean { dataRow[j] = cell.BooleanCellValue.ToString(); } else //其他型別按字串型別來處理 { dataRow[j] = cell.StringCellValue; } if (!string.IsNullOrWhiteSpace(((string)dataRow[j]))) { isAddToTable = true; } } if (isAddToTable) { dt.Rows.Add(dataRow); } } } sheet = null; ds.Tables.Add(dt); } workbook = null; }
/// <summary> /// /// </summary> /// <param name="strFileName"></param> /// <param name="rowIndex"></param> /// <returns></returns> public static DataTable ReadFile(string strFileName, string sheetName, int rowIndex) { DataTable dt = new DataTable(); IWorkbook hssfworkbook; using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read)) { hssfworkbook = WorkbookFactory.Create(file); } ISheet sheet = hssfworkbook.GetSheet(sheetName); IEnumerator rows = sheet.GetRowEnumerator(); rows.MoveNext(); XSSFRow row = (XSSFRow)rows.Current; for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++) { if (string.IsNullOrEmpty(row.GetCell(j).ToString())) { break; } //将第一列作为列表头 dt.Columns.Add(row.GetCell(j).ToString()); } while (rows.MoveNext()) { row = (XSSFRow)rows.Current; int num = row.RowNum; DataRow dr = dt.NewRow(); bool isEmpty = false; bool flag = false; foreach (ICell temp in row.Cells) { if (!string.IsNullOrEmpty(temp.ToString())) { flag = true; } } if (!flag) { break; } for (int i = 0; i < dt.Columns.Count; i++) { NPOI.SS.UserModel.ICell cell = row.GetCell(i); if (cell == null) { flag = false; break; } switch (cell.CellType) { case CellType.Numeric: if (HSSFDateUtil.IsCellDateFormatted(cell)) //日期类型 { dr[i] = cell.DateCellValue.ToString("yyyy-MM-dd"); } else //其他数字类型 { dr[i] = cell.NumericCellValue; } break; case CellType.Blank: dr[i] = string.Empty; break; case CellType.Formula: if (Path.GetExtension(strFileName).ToLower().Trim() == ".xlsx") { XSSFFormulaEvaluator eva = new XSSFFormulaEvaluator(hssfworkbook); CellValue v = eva.Evaluate(cell); if (eva.Evaluate(cell).CellType == CellType.Numeric) { if (HSSFDateUtil.IsCellDateFormatted(cell)) //日期类型 { dr[i] = cell.DateCellValue.ToString("yyyy-MM-dd"); } else //其他数字类型 { dr[i] = cell.NumericCellValue; } } else { dr[i] = eva.Evaluate(cell).StringValue; } } else { HSSFFormulaEvaluator eva = new HSSFFormulaEvaluator(hssfworkbook); if (eva.Evaluate(cell).CellType == CellType.Numeric) { if (HSSFDateUtil.IsCellDateFormatted(cell)) //日期类型 { dr[i] = cell.DateCellValue.ToString("yyyy-MM-dd"); } else //其他数字类型 { dr[i] = cell.NumericCellValue; } } else { dr[i] = eva.Evaluate(cell).StringValue; } } break; default: dr[i] = cell.StringCellValue; break; } } if (flag) { dt.Rows.Add(dr); } } return(dt); }
/// <summary> /// 使用Npoi方式导入 代码生成工具专用的DataSet /// </summary> /// <param name="fileFullName">文件名</param> /// <param name="tableComment">表注释</param> /// <param name="excludeSheets">需要排除的sheet名称</param> /// <returns> /// DataTable. /// </returns> public static Result <DataSet> InputExcelTableByNpoi(string fileFullName, out Dictionary <string, string> tableComment, List <string> excludeSheets = null) { var ret = new Result <DataSet>(); tableComment = new Dictionary <string, string>(); try { DataSet ds = new DataSet(); IWorkbook workbook; List <ISheet> iSheetLst = new List <ISheet>(); #region Excel版本判断 if (fileFullName.ToLower().EndsWith(".xlsx")) { using (FileStream file = new FileStream(fileFullName, FileMode.Open, FileAccess.Read)) { workbook = new XSSFWorkbook(file); } for (int k = 0; k < workbook.NumberOfSheets; k++) { if (excludeSheets != null && excludeSheets.Contains(workbook.GetSheetAt(k).SheetName)) { continue; } iSheetLst.Add(workbook.GetSheetAt(k)); } } else { using (FileStream file = new FileStream(fileFullName, FileMode.Open, FileAccess.Read)) { workbook = new HSSFWorkbook(file); } for (int k = 0; k < workbook.NumberOfSheets; k++) { iSheetLst.Add(workbook.GetSheetAt(k)); } } #endregion #region Excel处理 for (int k = 0; k < iSheetLst.Count; k++) { DataTable dt = new DataTable(); ISheet sheet = iSheetLst[k];// hssfworkbook.GetSheetAt(k); try { System.Collections.IEnumerator rows = sheet.GetRowEnumerator(); IRow headerRow = sheet.GetRow(1); int cellCount = headerRow.LastCellNum; for (int j = 0; j < cellCount; j++) { ICell cell = headerRow.GetCell(j); dt.Columns.Add(cell.ToString()); } #region 处理第一行的表名和注释 IRow firstRow = sheet.GetRow(0); var tableName = firstRow.GetCell(0).ToString(); while (tableComment.ContainsKey(tableName)) { tableName += "_r"; } dt.TableName = tableName; tableComment.Add(tableName, firstRow.GetCell(1).ToString()); #endregion for (int i = (sheet.FirstRowNum + 2); i <= sheet.LastRowNum; i++) { IRow row = sheet.GetRow(i); if (row == null) { break; } DataRow dataRow = dt.NewRow(); bool isDataRow = false;//是否为有效数据行,如果每列的值都为空 则为无效行,需要过滤掉 for (int j = row.FirstCellNum; j < cellCount; j++) { var cell = row.GetCell(j); if (cell != null) { #region 数据格式判断读取 if (cell.CellType == CellType.Numeric) { isDataRow = true; if (DateUtil.IsCellDateFormatted(cell)) { dataRow[j] = cell.DateCellValue; } else { dataRow[j] = cell.NumericCellValue; } } else if (cell.CellType == CellType.Blank) { dataRow[j] = ""; } else if (cell.CellType == CellType.Formula) { isDataRow = true; if (fileFullName.ToLower().EndsWith(".xlsx")) { var eva = new XSSFFormulaEvaluator(workbook); dataRow[j] = eva.Evaluate(cell).StringValue; } else { var eva = new HSSFFormulaEvaluator(workbook); dataRow[j] = eva.Evaluate(cell).StringValue; } } else if (cell.CellType == CellType.String) { isDataRow = true; dataRow[j] = cell.StringCellValue; } else { isDataRow = true; dataRow[j] = row.GetCell(j).ToString(); } #endregion } } if (isDataRow) { dt.Rows.Add(dataRow); } } ds.Tables.Add(dt); } catch (Exception ep) { //LogHelper.WriteLog(ep, "文件导入异常index:" + k.ToString() + "sheetName:" + sheet.SheetName); } } #endregion ret.Data = ds; ret.IsSucceed = true; ret.Message = "导入成功"; } catch (Exception ex) { ret.Message = ex.Message; } return(ret); }
/// <summary>读取excel /// 默认第一行为表头 /// </summary> /// <param name="strFileName">excel文档绝对路径</param> /// <param name="rowIndex">内容行偏移量,第一行为表头,内容行从第二行开始则为1</param> /// <returns></returns> public static DataTable Import(string strFileName, int rowIndex) { DataTable dt = new DataTable(); IWorkbook hssfworkbook; using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read)) { hssfworkbook = WorkbookFactory.Create(file); } ISheet sheet = hssfworkbook.GetSheetAt(0); // IRow headRow = sheet.GetRow(0); if (headRow != null) { int colCount = headRow.LastCellNum; for (int i = 0; i < colCount; i++) { dt.Columns.Add("COL_" + i); } } for (int i = (sheet.FirstRowNum + rowIndex); i <= sheet.LastRowNum; i++) { IRow row = sheet.GetRow(i); bool emptyRow = true; object[] itemArray = null; if (row != null) { itemArray = new object[row.LastCellNum]; for (int j = row.FirstCellNum; j < row.LastCellNum; j++) { if (row.GetCell(j) != null) { switch (row.GetCell(j).CellType) { case CellType.Numeric: if (HSSFDateUtil.IsCellDateFormatted(row.GetCell(j))) //日期类型 { itemArray[j] = row.GetCell(j).DateCellValue.ToString("yyyy-MM-dd"); } else //其他数字类型 { itemArray[j] = row.GetCell(j).NumericCellValue; } break; case CellType.Blank: itemArray[j] = string.Empty; break; case CellType.Formula: if (Path.GetExtension(strFileName).ToLower().Trim() == ".xlsx") { XSSFFormulaEvaluator eva = new XSSFFormulaEvaluator(hssfworkbook); if (eva.Evaluate(row.GetCell(j)).CellType == CellType.Numeric) { if (HSSFDateUtil.IsCellDateFormatted(row.GetCell(j))) //日期类型 { itemArray[j] = row.GetCell(j).DateCellValue.ToString("yyyy-MM-dd"); } else //其他数字类型 { itemArray[j] = row.GetCell(j).NumericCellValue; } } else { itemArray[j] = eva.Evaluate(row.GetCell(j)).StringValue; } } else { HSSFFormulaEvaluator eva = new HSSFFormulaEvaluator(hssfworkbook); if (eva.Evaluate(row.GetCell(j)).CellType == CellType.Numeric) { if (HSSFDateUtil.IsCellDateFormatted(row.GetCell(j))) //日期类型 { itemArray[j] = row.GetCell(j).DateCellValue.ToString("yyyy-MM-dd"); } else //其他数字类型 { itemArray[j] = row.GetCell(j).NumericCellValue; } } else { itemArray[j] = eva.Evaluate(row.GetCell(j)).StringValue; } } break; default: itemArray[j] = row.GetCell(j).StringCellValue; break; } if (itemArray[j] != null && !string.IsNullOrEmpty(itemArray[j].ToString().Trim())) { emptyRow = false; } } } } //非空数据行数据添加到DataTable if (!emptyRow) { dt.Rows.Add(itemArray); } } if (dt.Columns.Count > 0) { for (int j = 0; j < dt.Columns.Count; j++) { dt.Columns[j].ColumnName = dt.Rows[0][j].ToString(); } dt.Rows.Remove(dt.Rows[0]); } return(dt); }