public static int GetSheetNumber(string outputFile) { int number = 0; try { FileStream readfile = new FileStream(outputFile, FileMode.Open, FileAccess.Read); HSSFWorkbook hssfworkbook = new HSSFWorkbook(readfile); number = hssfworkbook.NumberOfSheets; } catch (Exception ex) { NBCMSLoggerManager.Error(ex.ToString()); } return(number); }
/// <summary> /// 更新Excel表格 /// </summary> /// <param name="outputFile">需更新的excel表格路径</param> /// <param name="sheetname">sheet名</param> /// <param name="updateData">需更新的数据</param> /// <param name="coluids">需更新的列号</param> /// <param name="rowid">需更新的开始行号</param> public static void UpdateExcel(string outputFile, string sheetname, double[][] updateData, int[] coluids, int rowid) { FileStream readfile = new FileStream(outputFile, FileMode.Open, FileAccess.Read); HSSFWorkbook hssfworkbook = new HSSFWorkbook(readfile); readfile.Close(); ISheet sheet1 = hssfworkbook.GetSheet(sheetname); for (int j = 0; j < coluids.Length; j++) { for (int i = 0; i < updateData[j].Length; i++) { try { if (sheet1.GetRow(i + rowid) == null) { sheet1.CreateRow(i + rowid); } if (sheet1.GetRow(i + rowid).GetCell(coluids[j]) == null) { sheet1.GetRow(i + rowid).CreateCell(coluids[j]); } sheet1.GetRow(i + rowid).GetCell(coluids[j]).SetCellValue(updateData[j][i]); } catch (Exception ex) { NBCMSLoggerManager.Error(ex.ToString()); } } } try { FileStream writefile = new FileStream(outputFile, FileMode.Create); hssfworkbook.Write(writefile); writefile.Close(); } catch (Exception ex) { NBCMSLoggerManager.Error(ex.ToString()); } }
/// <summary> /// 更新Excel表格 /// </summary> /// <param name="outputFile">需更新的excel表格路径</param> /// <param name="sheetname">sheet名</param> /// <param name="dt">需更新的数据</param> public static void ExportDTtoExcel(string outputFile, string sheetname, DataTable dt) { var readfile = new FileStream(outputFile, FileMode.Open, FileAccess.Read); var hssfworkbook = new XSSFWorkbook(readfile); readfile.Close(); var sheet1 = hssfworkbook.GetSheet(sheetname); for (int j = 0; j < dt.Columns.Count; j++) { for (int i = 0; i < dt.Rows.Count; i++) { try { if (sheet1.GetRow(i) == null) { sheet1.CreateRow(i); } if (sheet1.GetRow(i).GetCell(j) == null) { sheet1.GetRow(i).CreateCell(j); } sheet1.GetRow(i).GetCell(j).SetCellValue(dt.Rows[i][j].ToString()); } catch (Exception ex) { NBCMSLoggerManager.Error(ex.ToString()); } } } try { var writefile = new FileStream(outputFile, FileMode.Create); hssfworkbook.Write(writefile); writefile.Close(); } catch (Exception ex) { NBCMSLoggerManager.Error(ex.ToString()); } }
public static ArrayList GetSheetName(string outputFile) { ArrayList arrayList = new ArrayList(); try { FileStream readfile = new FileStream(outputFile, FileMode.Open, FileAccess.Read); HSSFWorkbook hssfworkbook = new HSSFWorkbook(readfile); for (int i = 0; i < hssfworkbook.NumberOfSheets; i++) { arrayList.Add(hssfworkbook.GetSheetName(i)); } } catch (Exception ex) { NBCMSLoggerManager.Error(ex.ToString()); } return(arrayList); }
/// <summary> /// 将制定sheet中的数据导出到datatable中 /// </summary> /// <param name="sheet">需要导出的sheet</param> /// <param name="headerRowIndex">列头所在行号,-1表示没有列头</param> /// <param name="needHeader"></param> /// <returns></returns> static DataTable ImportDt(ISheet sheet, int headerRowIndex, bool needHeader) { if (sheet == null) { throw new ArgumentNullException("sheet"); } var table = new DataTable(); try { IRow headerRow; var cellCount = 0; if (headerRowIndex < 0 || !needHeader) { headerRow = sheet.GetRow(0) as XSSFRow; if (headerRow != null) { cellCount = headerRow.LastCellNum; for (int i = headerRow.FirstCellNum; i <= cellCount; i++) { var column = new DataColumn(Convert.ToString(i)); table.Columns.Add(column); } } } else { headerRow = sheet.GetRow(headerRowIndex) as XSSFRow; if (headerRow != null) { cellCount = headerRow.LastCellNum; for (int i = headerRow.FirstCellNum; i < cellCount; i++) { if (i == 118) { } if (headerRow.GetCell(i) == null) { if (table.Columns.IndexOf(Convert.ToString(i)) > 0) { var column = new DataColumn(Convert.ToString("重复列名" + i)); table.Columns.Add(column); } else { var column = new DataColumn(Convert.ToString(i)); table.Columns.Add(column); } } else if (table.Columns.IndexOf(headerRow.GetCell(i).ToString()) > 0) { var column = new DataColumn(Convert.ToString("重复列名" + i)); table.Columns.Add(column); } else { var column = new DataColumn(headerRow.GetCell(i).ToString()); table.Columns.Add(column); } } } } for (var i = (headerRowIndex + 1); i <= sheet.LastRowNum; i++) { try { IRow row; if (sheet.GetRow(i) == null) { row = sheet.CreateRow(i) as XSSFRow; } else { row = sheet.GetRow(i) as XSSFRow; } var dataRow = table.NewRow(); for (int j = row.FirstCellNum; j <= cellCount; j++) { try { if (row.GetCell(j) == null) { continue; } switch (row.GetCell(j).CellType) { case CellType.String: var str = row.GetCell(j).StringCellValue; if (!string.IsNullOrEmpty(str)) { dataRow[j] = str; } else { dataRow[j] = null; } break; case CellType.Numeric: dataRow[j] = Convert.ToString(row.GetCell(j).NumericCellValue); break; case CellType.Boolean: dataRow[j] = Convert.ToString(row.GetCell(j).BooleanCellValue); break; case CellType.Error: dataRow[j] = ErrorEval.GetText(row.GetCell(j).ErrorCellValue); break; default: dataRow[j] = ""; break; } } catch (Exception exception) { NBCMSLoggerManager.Error(exception.ToString()); } } table.Rows.Add(dataRow); } catch (Exception exception) { NBCMSLoggerManager.Error(exception.ToString()); } } } catch (Exception exception) { NBCMSLoggerManager.Error(exception.ToString()); } return(table); }