提供转换类 希望对大家有帮助
/// <summary>
/// 生成excel辅助类
/// </summary>
public class NPOIExcelHelper
{
/// <summary>
/// 最大数据行数
/// </summary>
private readonly int rowMax = 65535;
/// <summary>
/// 将DataTable转换为excel2003格式。
/// </summary>
/// <param name="dt">数据</param>
/// <returns>返回数据</returns>
public byte[] DataTable2Excel(DataTable dt, string sheetName)
{
IWorkbook book = new HSSFWorkbook();
if (dt.Rows.Count < rowMax)
dataWrite2Sheet(dt, 0, dt.Rows.Count - 1, book, sheetName);
else
{
int page = dt.Rows.Count / rowMax;
for (int i = 0; i < page; i++)
{
int start = i * rowMax;
int end = (i * rowMax) + rowMax - 1;
dataWrite2Sheet(dt, start, end, book, sheetName + i.ToString());
}
///剩余行数
int lastPageItemCount = dt.Rows.Count % rowMax;
dataWrite2Sheet(dt, dt.Rows.Count - lastPageItemCount, dt.Rows.Count - 1, book, sheetName + page.ToString());
}
MemoryStream ms = new MemoryStream();
book.Write(ms);
return ms.ToArray();
}
/// <summary>
/// 导出excel
/// </summary>
/// <param name="dt">数据</param>
/// <param name="file">保存文件 xls</param>
/// <param name="sheetName">表单名字</param>
/// <param name="msg">执行消息</param>
/// <returns>执行是否成功</returns>
public bool ExportExcel(DataTable dt, string file, string sheetName, ref string msg)
{
try
{
byte[] data = DataTable2Excel(dt, sheetName);
if (!File.Exists(file))
{
FileStream fs = new FileStream(file, FileMode.CreateNew);
fs.Write(data, 0, data.Length);
fs.Close();
}
else
{
msg = "文件已经存在";
return false;
}
return true;
}
catch (Exception ex)
{
//写入自己日志
msg = ex.Message;
return false;
}
}
/// <summary>
/// 读取excel
/// </summary>
/// <param name="fileExcel">excel .xls</param>
/// <param name="总的数据表">数据表</param>
/// <param name="msg">执行消息</param>
/// <returns>是否读取成功</returns>
public bool ReadExcel(string fileExcel, bool isFirstRowColumn, ref DataTable sumDataTable, ref string msg)
{
IWorkbook workbook = null;
FileStream fs = null;
try
{
fs = new FileStream(fileExcel, FileMode.Open, FileAccess.Read);
if (fileExcel.IndexOf(".xlsx") > 0) // 2007版本
workbook = new XSSFWorkbook(fs);
else if (fileExcel.IndexOf(".xls") > 0) // 2003版本
workbook = new HSSFWorkbook(fs);
int num = workbook.NumberOfSheets;
for (int i = 0; i < num; i++)
{
ISheet sheet = workbook.GetSheetAt(i);
if (sheet == null)
{
msg = "表单中存在空页";
return false;
}
DataTable dataTable= getSheetData(sheet, isFirstRowColumn);
if (dataTable == null)
{
msg = "表单数据获取异常";
return false;
}
if (sumDataTable != null)
{
foreach (DataRow item in dataTable.Rows)
{
sumDataTable.Rows.Add(item.ItemArray);
}
}
else
{
sumDataTable = dataTable;
}
}
fs.Close();
workbook.Close();
return true;
}
catch (Exception ex)
{
//写入自己日志
msg = ex.Message;
//关闭资源
if (fs != null)
{
fs.Close();
}
if (workbook != null)
{
workbook.Close();
}
return false;
}
}
/// <summary>
/// 获得表单数据
/// </summary>
/// <param name="sheet">表单</param>
/// <param name="isFirstRowColumn">第一行是否列明</param>
/// <returns>表格</returns>
private DataTable getSheetData(ISheet sheet, bool isFirstRowColumn)
{
try
{
int startRow = -1;
DataTable dataTable = new DataTable();
if (sheet != null)
{
IRow firstRow = sheet.GetRow(0);
//一行最后一个cell的编号 即总的列数
int cellCount = firstRow.LastCellNum;
if (isFirstRowColumn)
{
for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
{
ICell cell = firstRow.GetCell(i);
if (cell != null)
{
string cellValue = cell.StringCellValue;
if (cellValue != null)
{
DataColumn column = new DataColumn(cellValue);
dataTable.Columns.Add(column);
}
}
}
startRow = sheet.FirstRowNum + 1;
}
else
{
startRow = sheet.FirstRowNum;
}
//最后一列的标号
int rowCount = sheet.LastRowNum;
for (int i = startRow; i <= rowCount; ++i)
{
IRow row = sheet.GetRow(i);
//没有数据的行默认是null
if (row == null) continue;
DataRow dataRow = dataTable.NewRow();
for (int j = row.FirstCellNum; j < cellCount; ++j)
{
//同理,没有数据的单元格都默认是null
if (row.GetCell(j) != null)
{
dataRow[j] = row.GetCell(j).ToString();
}
}
dataTable.Rows.Add(dataRow);
}
}
return dataTable;
}
catch (Exception)
{
//此处有日志
return null;
}
}
/// <summary>
/// 生成表单(一表单最多 65536行 第一行为列)
/// </summary>
/// <param name="dt">数据</param>
/// <param name="startRow">开始索引</param>
/// <param name="endRow">结束索引</param>
/// <param name="book">书对象</param>
/// <param name="sheetName">表单名</param>
private void dataWrite2Sheet(DataTable dt, int startRow, int endRow, IWorkbook book, string sheetName)
{
ISheet sheet = book.CreateSheet(sheetName);
IRow header = sheet.CreateRow(0);
for (int i = 0; i < dt.Columns.Count; i++)
{
ICell cell = header.CreateCell(i);
string val = dt.Columns[i].Caption ?? dt.Columns[i].ColumnName;
cell.SetCellValue(val);
}
int rowIndex = 1;
for (int i = startRow; i <= endRow; i++)
{
DataRow dtRow = dt.Rows[i];
IRow excelRow = sheet.CreateRow(rowIndex++);
for (int j = 0; j < dtRow.ItemArray.Length; j++)
{
excelRow.CreateCell(j).SetCellValue(dtRow[j].ToString());
}
}
}
}