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