Excel帮助类

/// <summary>
/// Excel帮助类 XQX
/// </summary>
public class ExcelHelper : IDisposable
{
private string fileName = null;
private IWorkbook workbook = null;
private FileStream fs = null;
private bool disposed;
public ExcelHelper(string fileName)
{
this.fileName = fileName;
this.disposed = false;
}
/// <summary>
/// DataTable导出到Excel
/// </summary>
/// <param name="data"></param>
/// <param name="sheetName"></param>
/// <param name="isColumnWritten"></param>
/// <returns></returns>
public int DataTableToExcel(DataTable data, string sheetName, bool isColumnWritten)
{
this.fs = new FileStream(this.fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite);
bool flag = this.fileName.IndexOf(".xlsx") > 0;
if (flag)
{
this.workbook = new XSSFWorkbook();
}
else
{
bool flag2 = this.fileName.IndexOf(".xls") > 0;
if (flag2)
{
this.workbook = new HSSFWorkbook();
}
}
int result;
try
{
bool flag3 = this.workbook != null;
if (flag3)
{
ISheet sheet = this.workbook.CreateSheet(sheetName);
int num;
if (isColumnWritten)
{
IRow row = sheet.CreateRow(0);
for (int i = 0; i < data.Columns.Count; i++)
{
row.CreateCell(i).SetCellValue(data.Columns[i].ColumnName);
}
num = 1;
}
else
{
num = 0;
}
for (int j = 0; j < data.Rows.Count; j++)
{
IRow row2 = sheet.CreateRow(num);
for (int i = 0; i < data.Columns.Count; i++)
{
row2.CreateCell(i).SetCellValue(data.Rows[j][i].ToString());
//row2.GetCell(i).CellStyle.FillForegroundColor = HSSFColor.Red.Index;
//row2.GetCell(i).CellStyle.FillPattern = FillPattern.SolidForeground;
}
num++;
}
this.workbook.Write(this.fs);
result = num;
}
else
{
result = -1;
}
}
catch (Exception ex)
{
Console.WriteLine("Exception: " + ex.Message);
result = -1;
}
return result;
}
/// <summary>
/// Excel导入DataTable
/// </summary>
/// <param name="sheetName"></param>
/// <param name="isFirstRowColumn"></param>
/// <returns></returns>
public DataTable ExcelToDataTable(string sheetName, bool isFirstRowColumn)
{
DataTable dataTable = new DataTable();
DataTable result;
try
{
this.fs = new FileStream(this.fileName, FileMode.Open, FileAccess.Read);
try
{
this.workbook = new XSSFWorkbook(this.fs);
}
catch
{
this.workbook = new HSSFWorkbook(this.fs);
}
bool flag = sheetName != null;
ISheet sheet;
if (flag)
{
sheet = this.workbook.GetSheet(sheetName);
bool flag2 = sheet == null;
if (flag2)
{
sheet = this.workbook.GetSheetAt(0);
}
}
else
{
sheet = this.workbook.GetSheetAt(0);
}
bool flag3 = sheet != null;
if (flag3)
{
IRow row = sheet.GetRow(0);
int lastCellNum = (int)row.LastCellNum;
int num;
if (isFirstRowColumn)
{
for (int i = (int)row.FirstCellNum; i < lastCellNum; i++)
{
ICell cell = row.GetCell(i);
bool flag4 = cell != null;
if (flag4)
{
string stringCellValue = cell.StringCellValue;
bool flag5 = stringCellValue != null;
if (flag5)
{
DataColumn column = new DataColumn(stringCellValue);
dataTable.Columns.Add(column);
}
}
}
num = sheet.FirstRowNum + 1;
}
else
{
num = sheet.FirstRowNum;
}
int lastRowNum = sheet.LastRowNum;
for (int j = num; j <= lastRowNum; j++)
{
IRow row2 = sheet.GetRow(j);
bool flag6 = row2 == null;
if (!flag6)
{
DataRow dataRow = dataTable.NewRow();
for (int k = (int)row2.FirstCellNum; k < lastCellNum; k++)
{
bool flag7 = row2.GetCell(k) != null;
if (flag7)
{
dataRow[k] = row2.GetCell(k).ToString();
}
}
dataTable.Rows.Add(dataRow);
}
}
}
result = dataTable;
}
catch (Exception ex)
{
Console.WriteLine("Exception: " + ex.Message);
result = null;
}
return result;
}

 

 

public void Dispose()
{
this.Dispose(true);
GC.SuppressFinalize(this);
}
protected virtual void Dispose(bool disposing)
{
bool flag = !this.disposed;
if (flag)
{
if (disposing)
{
bool flag2 = this.fs != null;
if (flag2)
{
this.fs.Close();
}
}
this.fs = null;
this.disposed = true;
}
}
}