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