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);
}
