本文利用第三方开源库NPOI实现Excel97-2003，Excel2007+的数据导入导出操作。
不依赖Office是否安装。NPOI开源项目地址：http://npoi.codeplex.com/。
库文件下载：http://npoi.codeplex.com/releases/view/115353
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Reflection;
using System.Text;
using System.Windows.Forms;
using NPOI.HPSF;
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
using NPOI.XSSF.Util; namespace Youwei.Common
{
/// <summary>
/// Excel操作类。利用第三方开源库NPOI实现Excel97-2003。Excel2007+的数据导入导出操作。 不依赖Office是否安装。 /// NPOI开源项目地址：http://npoi.codeplex.com/
/// </summary>
public class ExcelHelper
{
private static String FilterExcel = "Excel文件 (*.xls;*.xlsx)|*.xls;*.xlsx"; /// <summary>
/// 从网格将数据导出到Excel，支持Excel97-2003(.xls)、Excel2007+(.xlsx)
/// </summary>
/// <param name="dgv">网格</param>
/// <param name="ignoredColumns">要忽略导出的列名称集合</param>
/// <param name="fileName">导出到的文件名称。为空时将弹出保存对话框</param>
public static void ExportToExcel(DataGridView dgv, List<string> ignoredColumns = null, string fileName = "")
{
if (String.IsNullOrEmpty(fileName))
fileName = Dialog.SaveFileDialog(FilterExcel); if (String.IsNullOrEmpty(fileName))
return; bool isSuccess = false;
IWorkbook workBook = null;
ISheet sheet = null;
IRow dataRow = null;
try
{
//不同格式实例化不同工作薄
if (fileName.EndsWith(".xls"))
workBook = new HSSFWorkbook();
else if (fileName.EndsWith(".xlsx"))
workBook = new XSSFWorkbook(); sheet = workBook.CreateSheet();
dataRow = sheet.CreateRow(0); //表头样式
ICellStyle headerStyle = workBook.CreateCellStyle();
headerStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
headerStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
headerStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
headerStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; headerStyle.BottomBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index;
headerStyle.LeftBorderColor = HSSFColor.Black.Index;
headerStyle.RightBorderColor = HSSFColor.Black.Index;
headerStyle.TopBorderColor = HSSFColor.Black.Index; IFont font = workBook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headerStyle.SetFont(font);
headerStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; int i = 0, j = 0;
int ignoreCnt = 0;
//填充表头
for (i = 0; i < dgv.Columns.Count; i++)
{
if ((ignoredColumns != null && ignoredColumns.Contains(dgv.Columns[i].Name)) || !dgv.Columns[i].Visible || string.IsNullOrEmpty(dgv.Columns[i].HeaderText))
{
ignoreCnt++;
continue;
}
dataRow.CreateCell(i - ignoreCnt).SetCellValue(dgv.Columns[i].HeaderText);
dataRow.Cells[i - ignoreCnt].CellStyle = headerStyle;
} //内容样式
ICellStyle cellStyle = headerStyle;
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
font = workBook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 100;
cellStyle.SetFont(font); //填充内容
DataGridViewCell cell = null;
for (i = 0; i < dgv.Rows.Count; i++)
{
dataRow = sheet.CreateRow(i + 1);
ignoreCnt = 0;
for (j = 0; j < dgv.Columns.Count; j++)
{
if ((ignoredColumns != null && ignoredColumns.Contains(dgv.Columns[j].Name)) || !dgv.Columns[j].Visible || string.IsNullOrEmpty(dgv.Columns[j].HeaderText))
{
ignoreCnt++;
continue;
}
cell = dgv[j, i];
if (cell is DataGridViewComboBoxCell)
dataRow.CreateCell(j - ignoreCnt).SetCellValue(ConvertHelper.ToString(cell.FormattedValue));
else
dataRow.CreateCell(j - ignoreCnt).SetCellValue(ConvertHelper.ToString(cell.Value));
dataRow.Cells[j - ignoreCnt].CellStyle = cellStyle;
}
} //写文件
using (MemoryStream ms = new MemoryStream())
{
using (FileStream fs = new FileStream(fileName, FileMode.Create, FileAccess.Write))
{
workBook.Write(ms);
ms.Flush();
ms.Position = 0;
byte[] data = ms.ToArray();
fs.Write(data, 0, data.Length);
fs.Flush();
data = null;
isSuccess = true;
}
} //打开文件
if (isSuccess && Dialog.Confirm("数据已经导出到Excel成功，你要打开吗？") == DialogResult.Yes)
{
Util.OpenFile(fileName);
}
}
catch (Exception ex)
{
Dialog.Error(ex, System.Reflection.MethodBase.GetCurrentMethod(), true);
}
finally
{
if (dataRow != null)
dataRow = null; if (sheet != null)
sheet = null; if (workBook != null)
{
workBook.Clear();
workBook = null;
}
}
} /// <summary>
/// 从Excel导入数据到实体类集合。支持Excel97-2003(.xls)、Excel2007+(.xlsx)
/// </summary>
/// <typeparam name="T">实体类</typeparam>
/// <param name="fileName">要导入的Excel文件名称。为空时将弹出保存对话框</param>
/// <param name="propertyTextNamePair">实体类属性的名称及属性名相应键值对</param>
/// <param name="startSheet">导入的起始Excel表单序号</param>
/// <param name="startRow">导入的起始Excel行号</param>
/// <param name="startColumn">导入的起始Excel列号</param>
/// <returns>泛型实体类集合</returns>
public static List<T> ImportFromExcel<T>(string fileName, Dictionary<string, string> propertyTextNamePair, int startSheet = 0, int startRow = 0, int startColumn = 0) where T : new()
{
List<T> list = new List<T>();
if (String.IsNullOrEmpty(fileName) || !System.IO.File.Exists(fileName))
fileName = Dialog.OpenFileDialog(FilterExcel); if (String.IsNullOrEmpty(fileName) || !System.IO.File.Exists(fileName))
return list; IWorkbook workBook = null;
ISheet sheet = null;
IRow row = null; try
{
//载入文档
using (FileStream fileStream = new FileStream(fileName, FileMode.Open))
{
//不同格式实例化不同工作薄
if (fileName.EndsWith(".xls"))
workBook = new HSSFWorkbook(fileStream);
else if (fileName.EndsWith(".xlsx"))
workBook = new XSSFWorkbook(fileStream);
} //载入指定工作薄
sheet = workBook.GetSheetAt(startSheet);
//workBook.NumberOfSheets //工作薄的表单数 //载入表头
IRow headerRow = sheet.GetRow(startRow);
ICell cellHeader = null;
ICell cell = null;
int cellCount = headerRow.LastCellNum; //获取实体类属性
Type type = typeof(T);
PropertyInfo[] ps = type.GetProperties();
T t = default(T);
PropertyInfo p = null; //遍历行列，赋值到实体类，并加入到实体类集合
for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)
{
row = sheet.GetRow(i);
t = new T();
for (int j = 0; j < cellCount; j++)
{
cellHeader = headerRow.GetCell(j);
cell = row.GetCell(j); if (propertyTextNamePair != null && propertyTextNamePair.ContainsKey(cellHeader.ToString()))
p = type.GetProperty(propertyTextNamePair[cellHeader.ToString()]);
else
p = type.GetProperty(cellHeader.ToString()); if (p != null)
p.SetValue(t, Convert.ChangeType(cell.ToString(), p.PropertyType), null);
}
list.Add(t);
} //回收资源
ps = null;
cellHeader = null;
headerRow = null;
}
catch (Exception ex)
{
Dialog.Error(ex, System.Reflection.MethodBase.GetCurrentMethod(), true);
}
finally
{
if (row != null)
row = null; if (sheet != null)
sheet = null; if (workBook != null)
{
workBook.Clear();
workBook = null;
}
} return list;
}
}
}