public string readCellExcel(string filePath, string isheetname, int irow, int icolumn)
{
string result = "";
try
{
using (StreamReader input = new StreamReader(filePath))
{
NPOI.HSSF.UserModel.HSSFWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook(new NPOI.POIFS.FileSystem.POIFSFileSystem(input.BaseStream));
if (null == workbook)
{
result = "";
}
NPOI.HSSF.UserModel.HSSFFormulaEvaluator formulaEvaluator = new NPOI.HSSF.UserModel.HSSFFormulaEvaluator(workbook);
NPOI.HSSF.UserModel.HSSFDataFormatter dataFormatter = new NPOI.HSSF.UserModel.HSSFDataFormatter(new CultureInfo("vi-VN"));
NPOI.SS.UserModel.ISheet sheet = workbook.GetSheet(isheetname);
NPOI.SS.UserModel.IRow row = sheet.GetRow(irow);
if (row != null)
{
short minColIndex = row.FirstCellNum;
short maxColIndex = row.LastCellNum;
if (icolumn >= minColIndex || icolumn <= maxColIndex)
{
NPOI.SS.UserModel.ICell cell = row.GetCell(icolumn);
if (cell != null)
{
result = dataFormatter.FormatCellValue(cell, formulaEvaluator);
}
}
}
}
}
catch (Exception ex)
{
string test = ex.Message;
}
return result;
}
/*
* 合并单元格并且设置边框不要用以下代码，很费时间，并且多次设置就出错了，根本不能用。
* ((HSSFSheet)sheet).SetEnclosedBorderOfRegion(region, NPOI.SS.UserModel.BorderStyle.THIN, NPOI.HSSF.Util.HSSFColor.BLACK.index);
* 下面是我临时解决方案，希望有好的想法的可以互相交流
* protected void SetBorderOfRegion(ISheet sheet, int firstRowIndex, int lastRowIndex, int firstColIndex, int lastColIndex, bool hasBorder, ICellStyle cellStyleBoder)
* {
*
* CellRangeAddress region = new CellRangeAddress(firstRowIndex, lastRowIndex, firstColIndex, lastColIndex);
* if (hasBorder)
* {
* int temp = 1;
* for (int i = firstRowIndex; i <= lastRowIndex; i++)
* {
* IRow row = sheet.GetRow(i);
* for (int j = firstColIndex; j 1)
* {
* ICell cell = row.CreateCell(j);
* cell.CellStyle = cellStyleBoder;
* }
* else
* {
* temp++;
* }
* }
* }
* }
* sheet.AddMergedRegion(region);
* }
* }
*/
/// <summary>读取excel
/// 默认第一行为表头
/// </summary>
/// <param name="strFileName">excel文档绝对路径</param>
/// <param name="rowIndex">内容行偏移量，第一行为表头，内容行从第二行开始则为1</param>
/// <returns></returns>
public static System.Data.DataTable Import(string strFileName, int rowIndex)
{
var dt = new System.Data.DataTable();
NPOI.SS.UserModel.IWorkbook hssfworkbook;
using (var file = new System.IO.FileStream(strFileName, System.IO.FileMode.Open, System.IO.FileAccess.Read))
{
hssfworkbook = NPOI.SS.UserModel.WorkbookFactory.Create(file);
}
var sheet = hssfworkbook.GetSheetAt(0);
var 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++)
{
var 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 NPOI.SS.UserModel.CellType.Numeric:
if (NPOI.HSSF.UserModel.HSSFDateUtil.IsCellDateFormatted(row.GetCell(j))) //日期类型
{
itemArray[j] = row.GetCell(j).DateCellValue.ToString("yyyy-MM-dd");
}
else //其他数字类型
{
itemArray[j] = row.GetCell(j).NumericCellValue;
}
break;
case NPOI.SS.UserModel.CellType.Blank:
itemArray[j] = string.Empty;
break;
case NPOI.SS.UserModel.CellType.Formula:
if (System.IO.Path.GetExtension(strFileName).ToLower().Trim() == ".xlsx")
{
var eva = new NPOI.XSSF.UserModel.XSSFFormulaEvaluator(hssfworkbook);
if (eva.Evaluate(row.GetCell(j)).CellType == NPOI.SS.UserModel.CellType.Numeric)
{
itemArray[j] = eva.Evaluate(row.GetCell(j)).NumberValue;
}
else
{
itemArray[j] = eva.Evaluate(row.GetCell(j)).StringValue;
}
}
else
{
var eva = new NPOI.HSSF.UserModel.HSSFFormulaEvaluator(hssfworkbook);
if (eva.Evaluate(row.GetCell(j)).CellType == NPOI.SS.UserModel.CellType.Numeric)
{
itemArray[j] = eva.Evaluate(row.GetCell(j)).NumberValue;
}
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 static DataSet parse(string ExcelFilePath, string ZipPassword, string FirstColumnName)
{
//OleDbConnection cnnxls = null;
DataSet ds = new DataSet();
if (System.IO.Path.GetExtension(ExcelFilePath).Equals(".zip", StringComparison.CurrentCultureIgnoreCase))
{
string strTmpFolder = ExcelFilePath + ".dir";
try
{
zip.ExtractAll(ExcelFilePath, strTmpFolder, ZipPassword);
System.IO.DirectoryInfo rootDir = new System.IO.DirectoryInfo(strTmpFolder);
foreach (System.IO.FileInfo fileInfo in rootDir.GetFiles("*", System.IO.SearchOption.AllDirectories))
{
ds.Merge(parse(fileInfo.FullName, ZipPassword, FirstColumnName));
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
System.IO.Directory.Delete(strTmpFolder, true);
}
//string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strTmpFolder + ";Extended Properties=\"text;HDR=YES;IMEX=1;FMT=Delimited;\"";
//cnnxls = new OleDbConnection(strConn);
//cnnxls.Open();
}
else if (System.IO.Path.GetExtension(ExcelFilePath).Equals(".csv", StringComparison.CurrentCultureIgnoreCase))
{
System.IO.FileInfo fileInfo = new System.IO.FileInfo(ExcelFilePath);
DataTable table = CSVReader.parse(fileInfo.OpenRead(), true, ",", "\"");
table.TableName = System.IO.Path.GetFileNameWithoutExtension(fileInfo.FullName);
ds.Tables.Add(table);
}
else
{
NPOI.HSSF.UserModel.HSSFWorkbook workBook = new NPOI.HSSF.UserModel.HSSFWorkbook(new System.IO.FileStream(ExcelFilePath, System.IO.FileMode.Open)); // ExcelLibrary.SpreadSheet.Workbook.Load(Filename);
for (int sheetIndex = 0; sheetIndex < workBook.NumberOfSheets; sheetIndex++)
{
if (!workBook.IsSheetHidden(sheetIndex))
{
int intHeaderRow = 0;
NPOI.HSSF.UserModel.HSSFSheet workSheet = (NPOI.HSSF.UserModel.HSSFSheet)workBook.GetSheetAt(sheetIndex);
NPOI.HSSF.UserModel.HSSFRow headerRow = null; //= (NPOI.HSSF.UserModel.HSSFRow)workSheet.GetRow(intHeaderRow);
if (!string.IsNullOrEmpty(FirstColumnName))
{
for (int tmpRowIdx = intHeaderRow; tmpRowIdx <= workSheet.LastRowNum; tmpRowIdx++)
{
headerRow = (NPOI.HSSF.UserModel.HSSFRow)workSheet.GetRow(tmpRowIdx);
if (headerRow == null)
{
continue;
}
bool columnNameMatch = false;
for (int tmpColumnIndex = 0; tmpColumnIndex <= headerRow.LastCellNum; tmpColumnIndex++)
{
if (headerRow.GetCell(tmpColumnIndex) != null)
{
string columnName = headerRow.GetCell(tmpColumnIndex).ToString().Trim();
if (FirstColumnName.Equals(columnName))
{
intHeaderRow = tmpRowIdx;
columnNameMatch = true;
break;
}
}
}
if (columnNameMatch)
{
break;
}
}
}
else
{
headerRow = (NPOI.HSSF.UserModel.HSSFRow)workSheet.GetRow(intHeaderRow);
}
if (headerRow == null)
{
continue;
}
string tableName = workSheet.SheetName.Trim();
DataTable table = new DataTable(tableName);
int intColumnIndex = 0;
while (intColumnIndex <= headerRow.LastCellNum)
{
if (headerRow.GetCell(intColumnIndex) != null)
{
string columnName = headerRow.GetCell(intColumnIndex).ToString().Trim();
if (string.IsNullOrEmpty(columnName))
{
columnName = "Column_" + intColumnIndex;
}
if (table.Columns.Contains(columnName))
{
columnName = "Column_" + intColumnIndex;
}
table.Columns.Add(columnName, typeof(string));
// resign new value of column name to Excel for below part of import
headerRow.GetCell(intColumnIndex).SetCellValue(columnName);
}
intColumnIndex++;
}
int rowCount = 1;
while (intHeaderRow + rowCount <= workSheet.LastRowNum)
{
int colCount = 0;
NPOI.HSSF.UserModel.HSSFRow row = (NPOI.HSSF.UserModel.HSSFRow)workSheet.GetRow(intHeaderRow + rowCount);
if (row == null)
{
rowCount++;
continue;
}
DataRow dataRow = table.NewRow();
while (colCount <= headerRow.LastCellNum)
{
if (headerRow.GetCell(colCount) != null)
{
string columnName = headerRow.GetCell(colCount).ToString();
if (table.Columns.Contains(columnName))
{
NPOI.HSSF.UserModel.HSSFCell cell = (NPOI.HSSF.UserModel.HSSFCell)row.GetCell(colCount);
if (cell != null)
{
if (cell.CellType.Equals(NPOI.SS.UserModel.CellType.FORMULA))
{
NPOI.HSSF.UserModel.HSSFFormulaEvaluator e = new NPOI.HSSF.UserModel.HSSFFormulaEvaluator(workBook);
cell = (NPOI.HSSF.UserModel.HSSFCell)e.EvaluateInCell(cell);
}
string fieldValue = cell.ToString();
if (cell.CellType.Equals(NPOI.SS.UserModel.CellType.NUMERIC))
{
string format = string.Empty;
//bool IsBuildinformat = false;
// Not sure whether workBook.CreateDataFormat().GetFormat(index) can obtain all the build-in format
try
{
format = NPOI.HSSF.UserModel.HSSFDataFormat.GetBuiltinFormat(cell.CellStyle.DataFormat);
//IsBuildinformat = true;
}
catch
{
format = workBook.CreateDataFormat().GetFormat(cell.CellStyle.DataFormat);
}
// [h]:mm:ss handle NOT support
int midBlanketStartPos = format.IndexOf('[');
while (midBlanketStartPos >= 0)
{
int midBlanketEndPos = format.IndexOf(']', midBlanketStartPos);
format = format.Substring(0, midBlanketStartPos) + format.Substring(midBlanketStartPos + 1, midBlanketEndPos - midBlanketStartPos - 1) + format.Substring(midBlanketEndPos + 1);
midBlanketStartPos = format.IndexOf('[');
}
if (format.IndexOf("y", StringComparison.CurrentCultureIgnoreCase) >= 0 || format.IndexOf("d", StringComparison.CurrentCultureIgnoreCase) >= 0)
{
if (format.IndexOf("h", StringComparison.CurrentCultureIgnoreCase) >= 0)
{
fieldValue = cell.DateCellValue.ToString("yyyy-MM-dd HH:mm:ss");
}
else
{
DateTime date = cell.DateCellValue;
if (date.TimeOfDay.TotalSeconds > 0)
{
fieldValue = date.ToString("yyyy-MM-dd HH:mm:ss");
}
else
{
fieldValue = date.ToString("yyyy-MM-dd");
}
}
}
else if (format.IndexOf("h", StringComparison.CurrentCultureIgnoreCase) >= 0)
{
DateTime date = cell.DateCellValue;
// default date of "Time Only" field is 1899-12-31
if (!date.Date.Ticks.Equals(new DateTime(1899, 12, 31).Ticks))
{
fieldValue = cell.DateCellValue.ToString("yyyy-MM-dd HH:mm:ss");
}
else
{
fieldValue = cell.DateCellValue.ToString("HH:mm:ss");
}
}
else
{
fieldValue = cell.NumericCellValue.ToString();
}
}
dataRow[columnName] = fieldValue;
}
}
}
colCount++;
}
table.Rows.Add(dataRow);
rowCount++;
}
ds.Tables.Add(table);
}
}
//string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + ExcelFilePath + ";Extended Properties=\"Excel 12.0 Xml;IMEX=1;HDR=YES;\"";
//cnnxls = new OleDbConnection(strConn);
//try
//{
// cnnxls.Open();
//}
//catch
//{
// cnnxls.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ExcelFilePath + ";Extended Properties=\"Excel 8.0;IMEX=1;HDR=YES;\"";
// cnnxls.Open();
//}
//DataTable schemaTable = cnnxls.GetSchema("Tables");
//foreach (DataRow schemaRow in schemaTable.Rows)
//{
// string tableName = schemaRow["Table_Name"].ToString().Trim();
// if (tableName.EndsWith("$"))
// {
// OleDbDataAdapter oda = new OleDbDataAdapter("select * from [" + tableName + "]", cnnxls);
// try
// {
// //DataTable[] tables = oda.FillSchema(ds, SchemaType.Mapped);//
// //tables[0].TableName = schemaRow["Table_Name"].ToString().Replace("$", "").Replace("#csv", "");
// //if (tables[0].Columns.Contains("Emp No*"))
// // tables[0].Columns["Emp No*"].DataType = typeof(string);
// //OleDbDataReader dr = oda.SelectCommand.ExecuteReader();
// //while (dr.Read())
// //{
// // DataRow row = tables[0].NewRow();
// // for (int i = 0; i < tables[0].Columns.Count; i++)
// // row[i] = dr[i];
// // tables[0].Rows.Add(row);
// //}
// //// oda.Fill(tables[0]);
// //if (ds.Tables.Contains(tableName) && tableName.ToString().EndsWith("$"))
// // ds.Tables.Remove(tableName);
// string actualTableName = tableName.Substring(0, tableName.Length - 1);
// if (!ds.Tables.Contains(actualTableName))
// oda.Fill(ds, actualTableName);
// }
// catch
// {
// // unknown error caused by hidden sheet
// }
// // oda.Fill(ds);
// }
//}
//cnnxls.Close();
}
foreach (DataTable tempTable in ds.Tables)
{
for (int rowIdx = tempTable.Rows.Count - 1; rowIdx >= 0; rowIdx--)
{
DataRow row = tempTable.Rows[rowIdx];
bool isEmptyRow = true;
foreach (DataColumn tempColumn in tempTable.Columns)
{
if (!row.IsNull(tempColumn))
{
if (!string.IsNullOrEmpty(row[tempColumn].ToString().Trim()))
{
isEmptyRow = false;
break;
}
}
}
if (isEmptyRow)
{
tempTable.Rows.Remove(row);
}
else
{
break;
}
}
}
foreach (DataTable tempTable in ds.Tables)
{
foreach (DataColumn tempColumn in tempTable.Columns)
{
string tempColumnName = tempColumn.ColumnName;
tempColumnName = tempColumnName.Trim().Replace("*", "");
tempColumnName = tempColumnName.Trim().Replace("#", "");
tempColumn.ColumnName = tempColumnName;
}
}
return(ds);
}
public string readCellExcel(string filePath, string isheetname, int irow, int icolumn)
{
string result = "";
try
{
using (StreamReader input = new StreamReader(filePath))
{
NPOI.SS.UserModel.IWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook(new NPOI.POIFS.FileSystem.POIFSFileSystem(input.BaseStream));
if (null == workbook)
{
result = "";
}
NPOI.SS.UserModel.IFormulaEvaluator formulaEvaluator = new NPOI.HSSF.UserModel.HSSFFormulaEvaluator(workbook);
NPOI.SS.UserModel.DataFormatter dataFormatter = new NPOI.HSSF.UserModel.HSSFDataFormatter(new CultureInfo("vi-VN"));
NPOI.SS.UserModel.ISheet sheet = workbook.GetSheet(isheetname);
NPOI.SS.UserModel.IRow row = sheet.GetRow(irow);
result = dataFormatter.FormatCellValue(row.Cells[icolumn], formulaEvaluator);
/*foreach (NPOI.SS.UserModel.ISheet sheet in workbook)
{
foreach (NPOI.SS.UserModel.IRow row in sheet)
{
foreach (NPOI.SS.UserModel.ICell cell in row)
{
string value = dataFormatter.FormatCellValue(cell, formulaEvaluator);
}
}
}*/
}
}
catch { }
return result;
}
public object ReadCell(int rowIndex, int colIndex, string cellType)
{
Object itemobject;
NPOI.SS.UserModel.ICell cell = newsheet.GetRow(rowIndex).GetCell(colIndex);
NPOI.HSSF.UserModel.HSSFFormulaEvaluator e = new NPOI.HSSF.UserModel.HSSFFormulaEvaluator(excelWorkbook);
cell = e.EvaluateInCell(cell);
switch (cellType.ToLower())
{
case "string":
itemobject = cell.StringCellValue; break;
case "int":
case "float":
case "double":
itemobject = cell.NumericCellValue; break;
case "bool":
itemobject = cell.BooleanCellValue; break;
case "datetime":
itemobject = cell.DateCellValue; break;
default:
itemobject = cell.ToString(); break;
}
return itemobject;
}
