public IBaseMessage Execute(IPipelineContext pContext, IBaseMessage pInMsg) { if (DocumentSpecName == null) { throw new ArgumentException("DocSpec must be specified"); } this.WorkBook = WorkbookFactory.Create(pInMsg.BodyPart.Data, ImportOption.SheetContentOnly); IFormulaEvaluator formulaEvaluator = null; if (this.WorkBook is XSSFWorkbook) { formulaEvaluator = new XSSFFormulaEvaluator(this.WorkBook); } else { formulaEvaluator = new HSSFFormulaEvaluator(this.WorkBook); } this.WorkBookSchema = GetWorkBookSchema(pContext, formulaEvaluator); VirtualStream outSstm = ProcessWorkbook(); pInMsg.BodyPart.Data = outSstm; pInMsg.Context.Promote(new ContextProperty(SystemProperties.MessageType), $"{this.WorkBookSchema.Namespace}#{this.WorkBookSchema.Name}"); pInMsg.Context.Write(new ContextProperty(SystemProperties.SchemaStrongName), DocumentSpecName.SchemaName); pContext.ResourceTracker.AddResource(outSstm); return(pInMsg); }
static void EvaluateAll(this IWorkbook workbook) { var evaluator = workbook.GetCreationHelper().CreateFormulaEvaluator(); for (var i = 0; i < workbook.NumberOfSheets; i++) { var sheet = workbook.GetSheetAt(i); foreach (IRow row in sheet) { foreach (var cell in row) { if (cell.CellType != CellType.Formula) { continue; } var cellValue = evaluator.Evaluate(cell); if (cellValue.CellType != CellType.Numeric) { continue; } var cached = cell.NumericCellValue; var evaluated = cellValue.NumberValue; Console.WriteLine($"Cache: {cached} | Evaluated: {evaluated}"); } } } XSSFFormulaEvaluator.EvaluateAllFormulaCells(workbook); }
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> /// 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); } } }
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(); } }
// FIXME: use junit4 parameterization private static void verifyAllFormulasInWorkbookCanBeEvaluated(String sampleWorkbook) { XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook(sampleWorkbook); XSSFFormulaEvaluator.EvaluateAllFormulaCells(wb); wb.Close(); }
//处理excel2007 private static DataTable ExcelToDataTableFirstRowAsHeader(XSSFSheet sheet, XSSFFormulaEvaluator evaluator) { using (DataTable dt = new DataTable()) { XSSFRow firstRow = sheet.GetRow(0) as XSSFRow; 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++) { XSSFRow row = sheet.GetRow(i) as XSSFRow; DataRow dr = dt.NewRow(); FillDataRowByHSSFRow(row, evaluator, ref dr); dt.Rows.Add(dr); } dt.TableName = sheet.SheetName; return(dt); } }
//处理excel2007 private static DataTable ExcelToDataTable(XSSFSheet sheet, XSSFFormulaEvaluator 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++) { XSSFRow row = sheet.GetRow(i) as XSSFRow; DataRow dr = dt.NewRow(); FillDataRowByHSSFRow(row, evaluator, ref dr); dt.Rows.Add(dr); } } dt.TableName = sheet.SheetName; return(dt); } }
public static DataTable ExcelToDataTable(string excelPath, string sheetName, bool firstRowAsHeader) { using (FileStream fileStream = new FileStream(excelPath, FileMode.Open, FileAccess.Read)) { string extName = System.IO.Path.GetExtension(excelPath); //扩展名 if (extName.ToLower() == ".xls") //excel 2003 { HSSFWorkbook workbook = new HSSFWorkbook(fileStream); HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(workbook); HSSFSheet sheet = workbook.GetSheet(sheetName) as HSSFSheet; return(ExcelToDataTable(sheet, evaluator, firstRowAsHeader)); } else//2007 { XSSFWorkbook workbook = new XSSFWorkbook(fileStream); XSSFFormulaEvaluator evaluator = new XSSFFormulaEvaluator(workbook); XSSFSheet sheet = workbook.GetSheet(sheetName) as XSSFSheet; return(ExcelToDataTable(sheet, evaluator, firstRowAsHeader)); } } }
public static DataTable ExcelToDataTable(string excelPath, string sheetName, bool firstRowAsHeader) { using (FileStream fileStream = new FileStream(excelPath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite)) { IWorkbook workbook = null; IFormulaEvaluator evaluator = null; ISheet sheet = null; if (excelPath.EndsWith(".xls")) { workbook = new HSSFWorkbook(fileStream); evaluator = new HSSFFormulaEvaluator(workbook); sheet = workbook.GetSheet(sheetName) as HSSFSheet; if (sheet == null) { sheet = workbook.GetSheetAt(0); } } else { workbook = new XSSFWorkbook(fileStream); evaluator = new XSSFFormulaEvaluator(workbook); sheet = workbook.GetSheet(sheetName) as XSSFSheet; if (sheet == null) { sheet = workbook.GetSheetAt(0); } } return(ExcelToDataTable(sheet, evaluator, firstRowAsHeader)); } }
public static void WriteData(this ISheet sheet, List <ExcelCell> data) { foreach (var item in data) { var row = sheet.GetRow(item.Row); var cell = row.GetCell(item.Column); if (item.Value is double) { cell.SetCellValue((double)item.Value); cell.SetCellType(CellType.Numeric); } else if (item.Value is int) { cell.SetCellValue((int)item.Value); cell.SetCellType(CellType.Numeric); } else { cell.SetCellValue(item.Value.ToString()); } } if (sheet.Workbook is XSSFWorkbook) { XSSFFormulaEvaluator.EvaluateAllFormulaCells(sheet.Workbook); } else { HSSFFormulaEvaluator.EvaluateAllFormulaCells(sheet.Workbook); } }
public List <List <string> > getData(Stream ExcelFileStream, int SheetIndex) { List <List <string> > retData = new List <List <string> >(); IWorkbook workbook = WorkbookFactory.Create(ExcelFileStream); int count = workbook.NumberOfSheets; var sheet = workbook.GetSheetAt(SheetIndex); try { XSSFFormulaEvaluator eva = new XSSFFormulaEvaluator(workbook); var numberOfSheets = workbook.NumberOfSheets; //判断文件是否为空 if (sheet.PhysicalNumberOfRows == 0) { return(retData); } else { int cellCount = 0; for (int i = 0; i <= sheet.LastRowNum; i++) { var row = sheet.GetRow(i); if (row != null && row.Cells.Count > cellCount) { cellCount = row.LastCellNum; } } var rowList = new List <string>(); int RowStart = sheet.FirstRowNum; for (int i = RowStart; i <= sheet.LastRowNum; i++) { var row = sheet.GetRow(i); if (row != null) { rowList = GetOutputInvoiceRowValueList(eva, row, cellCount); retData.Add(rowList); } } } } catch (Exception ex) { } finally { ExcelFileStream.Close(); workbook = null; } return(retData); }
public IBaseMessage Execute(IPipelineContext pContext, IBaseMessage pInMsg) { VirtualStream outStream = new VirtualStream(); if (ExcelTemplate == null) { throw new ArgumentException("Excel template file path must be specified", "Excel template file"); } if (DocumentSpecName == null) { throw new ArgumentException("Document schema must be specified", "DocumentSpecName"); } using (FileStream input = new FileStream(ExcelTemplate, FileMode.Open, FileAccess.Read, FileShare.Read)) { try { this.WorkBook = WorkbookFactory.Create(input); IFormulaEvaluator formulaEvaluator = null; if (this.WorkBook is XSSFWorkbook) { formulaEvaluator = new XSSFFormulaEvaluator(this.WorkBook); } else { formulaEvaluator = new HSSFFormulaEvaluator(this.WorkBook); } this.WorkBookSchema = GetWorkBookSchema(pContext, formulaEvaluator); ProcessWorkbook(pInMsg.BodyPart.GetOriginalDataStream()); this.WorkBookSchema.ResetWorkBookRows(); this.WorkBook.Write(outStream); } finally { if (this.WorkBook != null) { this.WorkBook.Close(); } } } outStream.Position = 0; pContext.ResourceTracker.AddResource(outStream); pInMsg.BodyPart.Data = outStream; return(pInMsg); }
/// <summary> /// 构造函数 /// </summary> public ExcelCalculator() { _workbook = new XSSFWorkbook(); _sheet = _workbook.CreateSheet(); _row = _sheet.CreateRow(0); _formulaEvaluator = new XSSFFormulaEvaluator(_workbook); }
private static string GetCellValue(ICell cell, string type) { if (cell == null) { return(string.Empty); } 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.Numeric: var format = cell.CellStyle.DataFormat; if (format == 14 || format == 31 || format == 57 || format == 58) { var date = cell.DateCellValue; var re = date.ToString("yyy-MM-dd"); return(re); } return(cell.ToString()); case CellType.String: return(cell.StringCellValue); case CellType.Formula: try { if (type == "xls") { var e = new HSSFFormulaEvaluator(cell.Sheet.Workbook); e.EvaluateInCell(cell); return(cell.ToString()); } else { var e = new XSSFFormulaEvaluator(cell.Sheet.Workbook); e.EvaluateInCell(cell); return(cell.ToString()); } } catch { return(cell.NumericCellValue.ToString(CultureInfo.InvariantCulture)); } case CellType.Unknown: return(cell.ToString()); default: return(cell.ToString()); } }
/// <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); }
CustomWorkbook(FileInfo file) { ThreadPool.QueueUserWorkItem(o => { fileName = file.Name; FileStream fs = new FileStream(file.FullName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite); // 表的格式 if (file.Extension == ".xlsx") { var book = new XSSFWorkbook(fs); foreach (var link in book.ExternalLinksTable) { string[] arr = link.LinkedFileName.Split('/'); if (arr.Length > 1) { link.LinkedFileName = arr[arr.Length - 1]; fs.Close(); fs = new FileStream(file.FullName, FileMode.Create); book.Write(fs); } } workbook = book; evaluator = new XSSFFormulaEvaluator(workbook); } else if (file.Extension == ".xls") { workbook = new HSSFWorkbook(fs); evaluator = new HSSFFormulaEvaluator(workbook); } else { // csv workbook = new XSSFWorkbook(); ISheet sheet = workbook.CreateSheet(file.Name.Substring(0, file.Name.Length - 4)); string[] lines = File.ReadAllLines(file.FullName); for (int i = 0; i < lines.Length; i++) { IRow row = sheet.CreateRow(i); string[] values = lines[i].Split(','); row.CreateCell(0).SetCellValue(int.Parse(values[0])); for (int j = 1; j < values.Length; j++) { row.CreateCell(j).SetCellValue(values[j]); } } evaluator = new XSSFFormulaEvaluator(workbook); } fs.Close(); lock (allBooks) { allBooks.Add(this); evaluatorEnv.Add(file.Name, evaluator); } }); }
private void InstalledCapacity(int a, double b, double c, double x, double y, double z) { IWorkbook workbook1 = null; //新建IWorkbook对象 string fileProcess = "工作簿166667.xlsx"; FileStream fileProcessStream1 = new FileStream(fileProcess, FileMode.Open, FileAccess.Read); if (fileProcess.IndexOf(".xlsx") > 0) // 2007版本 { workbook1 = new XSSFWorkbook(fileProcessStream1); //xlsx数据读入workbook fileProcessStream1.Close(); } else if (fileProcess.IndexOf(".xls") > 0) // 2003版本 { workbook1 = new HSSFWorkbook(fileProcessStream1); //xls数据读入workbook fileProcessStream1.Close(); } ISheet sheet1 = workbook1.GetSheetAt(0); sheet1.GetRow(9).GetCell(4).SetCellValue(a); sheet1.GetRow(10).GetCell(4).SetCellValue(b); sheet1.GetRow(11).GetCell(4).SetCellValue(c); sheet1.GetRow(9).GetCell(7).SetCellValue(x); sheet1.GetRow(10).GetCell(7).SetCellValue(y); sheet1.GetRow(11).GetCell(7).SetCellValue(z); for (int i = 18; i < 23; i++) { IRow row = sheet1.GetRow(i); for (int j = 3; j < 10; j++) { ICell cell = row.GetCell(j); if (cell.CellType == CellType.Formula) { IFormulaEvaluator m = null; if (fileProcess.IndexOf(".xlsx") > 0) // 2007版本 { m = new XSSFFormulaEvaluator(cell.Sheet.Workbook); } else if (fileProcess.IndexOf(".xls") > 0) // 2003版本 { m = new HSSFFormulaEvaluator(cell.Sheet.Workbook); } m.EvaluateInCell(cell); } } } FileStream file = new FileStream("C:\\Users\\Administrator\\Desktop\\需求预测-发电.xlsx", FileMode.OpenOrCreate); workbook1.Write(file); file.Close(); workbook1.Close(); }
public void SaveToFile(FileInfo filename) { filename.ThrowIfNull(nameof(filename)); XSSFFormulaEvaluator.EvaluateAllFormulaCells(_workbook); // Write the stream data of workbook to the root directory. using FileStream file = new FileStream(filename.FullName, FileMode.OpenOrCreate); _workbook.Write(file); }
/** * @param startRowIndex row index in the spreadsheet where the first function/operator is found * @param testFocusFunctionName name of a single function/operator to test alone. * Typically pass <code>null</code> to test all functions */ private void ProcessFunctionGroup(int startRowIndex, String testFocusFunctionName) { IFormulaEvaluator evaluator = new XSSFFormulaEvaluator(workbook); int rowIndex = startRowIndex; while (true) { IRow r = sheet.GetRow(rowIndex); // only Evaluate non empty row if (r != null) { String targetFunctionName = GetTargetFunctionName(r); String targetTestName = GetTargetTestName(r); if (targetFunctionName == null) { throw new AssertionException("Test spreadsheet cell empty on row (" + (rowIndex + 1) + "). Expected function name or '" + SS.FUNCTION_NAMES_END_SENTINEL + "'"); } if (targetFunctionName.Equals(SS.FUNCTION_NAMES_END_SENTINEL)) { // found end of functions list break; } if (testFocusFunctionName == null || targetFunctionName.Equals(testFocusFunctionName, StringComparison.CurrentCultureIgnoreCase)) { // expected results are on the row below ICell expectedValueCell = r.GetCell(SS.COLUMN_INDEX_EXPECTED_VALUE); if (expectedValueCell == null) { int missingRowNum = rowIndex + 1; throw new AssertionException("Missing expected values cell for function '" + targetFunctionName + ", test" + targetTestName + " (row " + missingRowNum + ")"); } switch (ProcessFunctionRow(evaluator, targetFunctionName, targetTestName, r, expectedValueCell)) { case Result.ALL_EVALUATIONS_SUCCEEDED: _functionSuccessCount++; break; case Result.SOME_EVALUATIONS_FAILED: _functionFailureCount++; break; default: throw new Exception("unexpected result"); case Result.NO_EVALUATIONS_FOUND: // do nothing break; } } } rowIndex++; } }
//处理excel2007 private static DataTable ExcelToDataTable(XSSFSheet sheet, XSSFFormulaEvaluator evaluator, bool firstRowAsHeader) { if (firstRowAsHeader) { return(ExcelToDataTableFirstRowAsHeader(sheet, evaluator)); } else { return(ExcelToDataTable(sheet, evaluator)); } }
/// <summary> /// Evalua el valor de todas las formulas del documento. /// </summary> public void EvaluarFormulas() { if (tipo == TiposExcel.XLS) { HSSFFormulaEvaluator.EvaluateAllFormulaCells(hssfwb); } else { XSSFFormulaEvaluator.EvaluateAllFormulaCells(xssfwb); } }
private static object RetrivCellVal(IWorkbook wb, bool bNewExcel, ICell cell) { object obj = ""; switch (cell.CellType) { case CellType.Numeric: { if (DateUtil.IsCellDateFormatted(cell)) { obj = cell.DateCellValue; } else { obj = cell.NumericCellValue; } } break; case CellType.Formula: { IFormulaEvaluator e = null; if (bNewExcel) { e = new XSSFFormulaEvaluator(wb); } else { e = new HSSFFormulaEvaluator(wb); } var cellVal = e.Evaluate(cell); switch (cellVal.CellType) { case CellType.Numeric: obj = cellVal.NumberValue; break; default: obj = cellVal.StringValue; break; } } break; default: { // String obj = cell.StringCellValue; } break; } return(obj); }
public void EvaluateInCellReturnsSameDataType() { XSSFWorkbook wb = new XSSFWorkbook(); wb.CreateSheet().CreateRow(0).CreateCell(0); XSSFFormulaEvaluator evaluator = wb.GetCreationHelper().CreateFormulaEvaluator() as XSSFFormulaEvaluator; XSSFCell cell = wb.GetSheetAt(0).GetRow(0).GetCell(0) as XSSFCell; XSSFCell same = evaluator.EvaluateInCell(cell) as XSSFCell; //assertSame(cell, same); Assert.AreSame(cell, same); wb.Close(); }
//处理excel2007 private static void FillDataRowByHSSFRow(XSSFRow row, XSSFFormulaEvaluator evaluator, ref DataRow dr) { if (row != null) { for (int j = 0; j < dr.Table.Columns.Count; j++) { XSSFCell cell = row.GetCell(j) as XSSFCell; 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 XSSFCell; dr[j] = cell.ToString(); break; default: throw new NotSupportedException(string.Format("Catched unhandle CellType[{0}]", cell.CellType)); } } } } }
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> /// 根据Excel列类型获取列的值. /// </summary> /// <param name="cell">cell.</param> /// <returns>值.</returns> public static string GetCellValue(ICell cell) { if (cell == null) { return(string.Empty); } 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.Numeric: return(HSSFDateUtil.IsCellDateFormatted(cell) ? $"{cell.DateCellValue:G}" : cell.NumericCellValue.ToString()); case CellType.Unknown: default: return(cell.ToString()); case CellType.String: return(cell.StringCellValue); case CellType.Formula: try { var da = cell.Sheet.Workbook.GetType().Name; if (da == "HSSFWorkbook") { var e = new HSSFFormulaEvaluator(cell.Sheet.Workbook); e.EvaluateInCell(cell); return(cell.ToString()); } else { var e = new XSSFFormulaEvaluator(cell.Sheet.Workbook); e.EvaluateInCell(cell); return(cell.ToString()); } } catch { return(cell.NumericCellValue.ToString()); } } }
public static Task <DataTable> GetDataTableFromExcelAsync(Stream stream, string type = ".xls") => Task.Run(() => { IWorkbook workbook = null; IFormulaEvaluator eval = null; if (type == ".xlsx") // 2007版本 { workbook = new XSSFWorkbook(stream); eval = new XSSFFormulaEvaluator(workbook); } else // 2003版本 { workbook = new HSSFWorkbook(stream); eval = new HSSFFormulaEvaluator(workbook); } var sheet = workbook.GetSheetAt(0); // zero-based index of your target sheet var dt = new DataTable(sheet.SheetName); // write header row var headerRow = sheet.GetRow(0); foreach (ICell headerCell in headerRow) { dt.Columns.Add(headerCell.ToString().Trim()); } // write the rest var rowIndex = 0; foreach (IRow row in sheet) { // skip header row if (rowIndex++ == 0) { continue; } var dataRow = dt.NewRow(); var array = new string[dt.Columns.Count]; for (var i = 0; i < dt.Columns.Count; i++) { var cell = row.GetCell(i); var val = cell.GetFormattedCellValue(eval); array[i] = val; } dataRow.ItemArray = array; dt.Rows.Add(dataRow); } return(dt); });
/// <summary> /// 获取指定 <see cref="IWorkbook"/> 接口对象的公式计算接口 /// </summary> /// <param name="workBook"></param> /// <returns></returns> public static IFormulaEvaluator GetFormulaEvaluator(IWorkbook workBook) { IFormulaEvaluator formulaEvaluator = null; if (workBook is HSSFWorkbook) { formulaEvaluator = new HSSFFormulaEvaluator(workBook); } else { formulaEvaluator = new XSSFFormulaEvaluator(workBook); } return(formulaEvaluator); }
/// <summary> /// 打开指定的 Excel 文件 /// </summary> /// <param name="fileName">指定要打开的 Excel 文件名</param> /// <param name="sheetIndex">指定要打开的 Sheet 索引</param> /// <returns>Excel 文件数据表格</returns> internal ExcelDataGrid Open(string fileName, int sheetIndex = 0) { if (string.IsNullOrWhiteSpace(fileName)) { throw new ArgumentNullException(nameof(fileName)); } var sourceFs = new FileStream(fileName, FileMode.Open, FileAccess.Read); var extensions = new FileInfo(fileName).Extension; IWorkbook workbook = null; IFormulaEvaluator evaluator = null; if (extensions.Equals(".xlsx", StringComparison.CurrentCultureIgnoreCase)) { workbook = new XSSFWorkbook(sourceFs); evaluator = new XSSFFormulaEvaluator(workbook); } else if (extensions.Equals(".xls", StringComparison.CurrentCultureIgnoreCase)) { workbook = new HSSFWorkbook(sourceFs); evaluator = new HSSFFormulaEvaluator(workbook); } else { throw new ArgumentException("不支持的文件扩展名"); } var sheet = workbook.GetSheetAt(sheetIndex); if (sheet == null) { throw new FileLoadException("打开的 Excel 文件没有 Sheet."); } var result = new ExcelDataGrid(); var rowIndex = -1; var columnIndex = -1; foreach (var row in GetRows(sheet)) { rowIndex++; columnIndex = -1; foreach (var column in row.Cells) { columnIndex++; result[rowIndex, columnIndex] = GetCellValue(evaluator, column); } } sourceFs.Close(); workbook.Close(); return(result); }