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