实例功能概述：
1、支持Excel2003以及2007
2、支持Excel读取到DataTable（TableToExcel）
3、支持DataTable导出到Excel（TableToExcel）
4、支持WPF DataGrid导出到Excel（SelectedRowToExcel，AllRowToExcel）
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Data;
using Microsoft.Win32;
using Microsoft.Windows.Controls;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
namespace LT.Utility
{
/// <summary>
/// FileName: NpoiExcelHelper.cs
/// CLRVersion: 4.0.30319.18052
/// Author: ZhangLei
/// Corporation: Litu
/// Description:Npoi Excel操作类封装
/// DateTime: 2016/1/24 23:01:21
/// </summary>
public class NpoiExcelHelper
{
#region Public Interface
/// <summary>
/// 读取Excel到DataTable<see cref="System.Data.DataTable"/>
/// </summary>
/// <param name="file">Excel路径，如"E:\hello.xls"</param>
/// <returns></returns>
public static DataTable ExcelToTable(string file)
{
var type = GetFileType(file);
if ( type== FileType.Excel2003)
{
return ExcelToTableForXLS(file);
}
else if (type == FileType.Excel2007)
{
return ExcelToTableForXLSX(file);
}
else
{
return null;
}
}
/// <summary>
/// DataTable 导出到 Excel
/// </summary>
/// <param name="dt"></param>
public static void TableToExcel(DataTable dt)
{
SaveFileDialog saveFileDialog = new SaveFileDialog();
saveFileDialog.Filter = "Excel 2003(*.xls)|*.xls|Excel 2007(*.xlsx)|*.xlsx";
saveFileDialog.FilterIndex = 1;
if (saveFileDialog.ShowDialog() == true)
{
if (saveFileDialog.FilterIndex == 1)
{
TableToExcelForXLS(dt,saveFileDialog.FileName);
}
else
{
TableToExcelForXLSX(dt, saveFileDialog.FileName);
}
}
}
/// <summary>
/// DataTable 导出到 Excel
/// </summary>
/// <param name="dt"></param>
/// <param name="file">Excel路径，如"E:\hello.xls"</param>
public static void TableToExecel(DataTable dt, string file)
{
var type = GetFileType(file);
if (type == FileType.Excel2003)
{
TableToExcelForXLS(dt, file);
}
else if (type == FileType.Excel2007)
{
TableToExcelForXLSX(dt, file);
}
else
{
throw new Exception("暂不支持此种文件类型的输出");
}
}
/// <summary>
/// WPF DataGrid选中行数据导出到Excel
/// </summary>
/// <param name="dataGrid"></param>
public static void SelectedRowToExcel(DataGrid dataGrid)
{
var dataView = dataGrid.ItemsSource as DataView;
if (dataView != null)
{
DataTable dt = dataView.ToTable();
dt.Rows.Clear();
int sRowCount = dataGrid.SelectedItems.Count;
int col = dataGrid.Columns.Count;
var sItems = dataGrid.SelectedItems;
DataRow newRow = null;
DataRowView drv = null;
for (int i = 0; i < sRowCount; i++)
{
newRow = dt.NewRow();
for (int j = 0; j < col; j++)
{
drv = sItems[i] as DataRowView;
newRow[j] = drv[j];
}
dt.Rows.Add(newRow);
}
TableToExcel(dt);
}
else
{
throw new Exception("DataGrid 数据源为null，无法导出到Excel");
}
}
/// <summary>
/// WPF DataGrid全部数据导出到Excel
/// </summary>
/// <param name="dataGrid"></param>
public static void AllRowToExcel(DataGrid dataGrid)
{
var dataView = dataGrid.ItemsSource as DataView;
if (dataView != null)
{
DataTable dt = dataView.ToTable();
TableToExcel(dt);
}
else
{
throw new Exception("DataGrid 数据源为null，无法导出到Excel");
}
}
#endregion
#region Common Function
private static FileType GetFileType(string file)
{
string extention = Path.GetExtension(file).ToLower();
if(extention.Equals(".xls"))
{
return FileType.Excel2003;
}
else if (extention.Equals(".xlsx"))
{
return FileType.Excel2007;
}
else
{
return FileType.Other;
}
}
enum FileType
{
Excel2003,
Excel2007,
Other
}
#endregion
#region Excel2003
/// <summary>
/// 将Excel文件中的数据读出到DataTable中(xls)
/// </summary>
/// <param name="file"></param>
/// <returns></returns>
private static DataTable ExcelToTableForXLS(string file)
{
DataTable dt = new DataTable();
using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read))
{
HSSFWorkbook hssfworkbook = new HSSFWorkbook(fs);
ISheet sheet = hssfworkbook.GetSheetAt(0);
//表头
IRow header = sheet.GetRow(sheet.FirstRowNum);
List<int> columns = new List<int>();
for (int i = 0; i < header.LastCellNum; i++)
{
object obj = GetValueTypeForXLS(header.GetCell(i) as HSSFCell);
if (obj == null || obj.ToString() == string.Empty)
{
dt.Columns.Add(new DataColumn("Columns" + i.ToString()));
//continue;
}
else
dt.Columns.Add(new DataColumn(obj.ToString()));
columns.Add(i);
}
//数据
for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)
{
DataRow dr = dt.NewRow();
bool hasValue = false;
foreach (int j in columns)
{
dr[j] = GetValueTypeForXLS(sheet.GetRow(i).GetCell(j) as HSSFCell);
if (dr[j] != null && dr[j].ToString() != string.Empty)
{
hasValue = true;
}
}
if (hasValue)
{
dt.Rows.Add(dr);
}
}
}
return dt;
}
/// <summary>
/// 将DataTable数据导出到Excel文件中(xls)
/// </summary>
/// <param name="dt"></param>
/// <param name="file"></param>
private static void TableToExcelForXLS(DataTable dt, string file)
{
NPOI.HSSF.UserModel.HSSFWorkbook hssfworkbook = new NPOI.HSSF.UserModel.HSSFWorkbook();
ISheet sheet = hssfworkbook.CreateSheet("Test");
//表头
IRow row = sheet.CreateRow(0);
for (int i = 0; i < dt.Columns.Count; i++)
{
ICell cell = row.CreateCell(i);
cell.SetCellValue(dt.Columns[i].ColumnName);
}
//数据
for (int i = 0; i < dt.Rows.Count; i++)
{
IRow row1 = sheet.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
ICell cell = row1.CreateCell(j);
cell.SetCellValue(dt.Rows[i][j].ToString());
}
}
//转为字节数组
MemoryStream stream = new MemoryStream();
hssfworkbook.Write(stream);
var buf = stream.ToArray();
//保存为Excel文件
using (FileStream fs = new FileStream(file, FileMode.Create, FileAccess.Write))
{
fs.Write(buf, 0, buf.Length);
fs.Flush();
}
}
/// <summary>
/// 获取单元格类型(xls)
/// </summary>
/// <param name="cell"></param>
/// <returns></returns>
private static object GetValueTypeForXLS(HSSFCell cell)
{
if (cell == null)
return null;
switch (cell.CellType)
{
case CellType.Blank: //BLANK:
return null;
case CellType.Boolean: //BOOLEAN:
return cell.BooleanCellValue;
case CellType.Numeric: //NUMERIC:
return cell.NumericCellValue;
case CellType.String: //STRING:
return cell.StringCellValue;
case CellType.Error: //ERROR:
return cell.ErrorCellValue;
case CellType.Formula: //FORMULA:
default:
return "=" + cell.CellFormula;
}
}
#endregion
#region Excel2007及以上版本
/// <summary>
/// 将Excel文件中的数据读出到DataTable中(xlsx)
/// </summary>
/// <param name="file"></param>
/// <returns></returns>
private static DataTable ExcelToTableForXLSX(string file)
{
DataTable dt = new DataTable();
using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read))
{
//NPOI.XSSF.UserModel
XSSFWorkbook xssfworkbook = new XSSFWorkbook(fs);
ISheet sheet = xssfworkbook.GetSheetAt(0);
//表头
IRow header = sheet.GetRow(sheet.FirstRowNum);
List<int> columns = new List<int>();
for (int i = 0; i < header.LastCellNum; i++)
{
object obj = GetValueTypeForXLSX(header.GetCell(i) as XSSFCell);
if (obj == null || obj.ToString() == string.Empty)
{
dt.Columns.Add(new DataColumn("Columns" + i.ToString()));
//continue;
}
else
dt.Columns.Add(new DataColumn(obj.ToString()));
columns.Add(i);
}
//数据
for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)
{
DataRow dr = dt.NewRow();
bool hasValue = false;
foreach (int j in columns)
{
dr[j] = GetValueTypeForXLSX(sheet.GetRow(i).GetCell(j) as XSSFCell);
if (dr[j] != null && dr[j].ToString() != string.Empty)
{
hasValue = true;
}
}
if (hasValue)
{
dt.Rows.Add(dr);
}
}
}
return dt;
}
/// <summary>
/// 将DataTable数据导出到Excel文件中(xlsx)
/// </summary>
/// <param name="dt"></param>
/// <param name="file"></param>
private static void TableToExcelForXLSX(DataTable dt, string file)
{
XSSFWorkbook xssfworkbook = new XSSFWorkbook();
ISheet sheet = xssfworkbook.CreateSheet("Test");
//表头
IRow row = sheet.CreateRow(0);
for (int i = 0; i < dt.Columns.Count; i++)
{
ICell cell = row.CreateCell(i);
cell.SetCellValue(dt.Columns[i].ColumnName);
}
//数据
for (int i = 0; i < dt.Rows.Count; i++)
{
IRow row1 = sheet.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
ICell cell = row1.CreateCell(j);
cell.SetCellValue(dt.Rows[i][j].ToString());
}
}
//转为字节数组
MemoryStream stream = new MemoryStream();
xssfworkbook.Write(stream);
var buf = stream.ToArray();
//保存为Excel文件
using (FileStream fs = new FileStream(file, FileMode.Create, FileAccess.Write))
{
fs.Write(buf, 0, buf.Length);
fs.Flush();
}
}
/// <summary>
/// 获取单元格类型(xlsx)
/// </summary>
/// <param name="cell"></param>
/// <returns></returns>
private static object GetValueTypeForXLSX(XSSFCell cell)
{
if (cell == null)
return null;
switch (cell.CellType)
{
case CellType.Blank: //BLANK:
return null;
case CellType.Boolean: //BOOLEAN:
return cell.BooleanCellValue;
case CellType.Numeric: //NUMERIC:
return cell.NumericCellValue;
case CellType.String: //STRING:
return cell.StringCellValue;
case CellType.Error: //ERROR:
return cell.ErrorCellValue;
case CellType.Formula: //FORMULA:
default:
return "=" + cell.CellFormula;
}
}
#endregion
}
}
