最近项目中有数据的导入导出Excel的需求，这里做简单整理。
公司用的是Infragistics的产品，付费，不需要本地安装Office。
有需要的朋友可以下载 Infragistics.2013.2.2098 ，提取密码：5u17
本文完整代码下载Demo.Excel.zip
当然，我知道还有其他开源的类库来操作Excel，希望有资源的博友可以一起共享一下。
Infragistics安装使用
直接安装Infragistics_WinForms_20132.msi后再项目Reference中引用既可。操作Excel的话引用Infragistics4.Documents.Excel.v13.2.dll 足矣。
导出Excel
支持的格式
- Excel97To2003
- Excel97To2003Template
- Excel2007
- Excel2007MacroEnabled
- Excel2007MacroEnabledTemplate
- Excel2007Template
- StrictOpenXml
如果用 JustCompile 查看源码，在不设置任何格式的情况下，默认是保存成Excel 97-2003 Workbook （*.xls）格式的，所以想要导出其他格式的Excel，需要调用 SetCurrentFormat(WorkbookFormat format) 方法。
使用 SaveFileDialog ，设置Filter，根据文件的后缀名映射Format。
public Format GetFormat(string sExtension)
{
switch (sExtension)
{
case ".xls":
return Format.Excel97To2003;
case ".xlt":
return Format.Excel97To2003Template;
case ".xlsx":
return Format.Excel2007;
case ".xltx":
return Format.Excel2007Template;
case ".xlsm":
return Format.Excel2007MacroEnabled;
case ".xltm":
return Format.Excel2007MacroEnabledTemplate;
default:
return Format.Excel97To2003;
}
}
创建Worksheet
定义数据类型
定义Attribute
- DisplayNameAttribute: 显示Excel的Header
- WorksheetHeaderAttribute: 定义Header的背景色和前景色
用泛型来填充Worksheet
反射获取属性值
public bool CreateSheet<T>(string sSheetName, List<T> lstRowData, bool bCreateHeader)
{
ExcelProcessEvent("CreateSheet Start, sSheetName - " + sSheetName + ", lstRowData.Count - " + lstRowData.Count + ", bCreateHeader - " + bCreateHeader);
try
{
Worksheet aWorksheet = _Workbook.Worksheets.Add(sSheetName);
Type aType = typeof(T);
if(bCreateHeader) setSheetHeader(aWorksheet, aType);
int rowIndex = bCreateHeader ? 1 : 0;
foreach (var rowdata in lstRowData) {
for (int i = 0; i < aType.GetProperties().Length; i++)
{
var prop = aType.GetProperties()[i];
aWorksheet.Rows[rowIndex].Cells[i].Value = prop.GetValue(rowdata);
//ExcelProcessEvent("CreateSheet InProgress, " + rowIndex + " - " + prop.Name + ": " + prop.GetValue(rowdata).ToString());
}
rowIndex++;
}
ExcelProcessEvent("CreateSheet End, Success");
return true;
}
catch(Exception ex)
{
ExcelErrorEvent("CreateSheet Failed, Error - " + ex.Message);
return false;
}
}
private void setSheetHeader(Worksheet oWorksheet, Type oType) {
ExcelProcessEvent("setSheetHeader Start");
for (int i = 0; i < oType.GetProperties().Length; i++)
{
var prop = oType.GetProperties()[i];
string displayName = prop.Name;
try
{
var customAttr = prop.GetCustomAttribute<DisplayNameAttribute>();
displayName = customAttr.DisplayName;
}
catch
{
}
ExcelProcessEvent("setSheetHeader InProgress, displayName - " + displayName);
Color backgroundcolor = Color.White;
Color forecolor = Color.Black;
try
{
var customAttr = prop.GetCustomAttribute<WorksheetHeaderAttribute>();
backgroundcolor = ColorTranslator.FromHtml(customAttr.BackgroundColor);
forecolor = ColorTranslator.FromHtml(customAttr.ForeColor);
}
catch
{
}
ExcelProcessEvent("setSheetHeader InProgress, backgroundcolor - " + backgroundcolor + ", forecolor - " + forecolor);
oWorksheet.Rows[0].Cells[i].Value = displayName;
oWorksheet.Rows[0].Cells[i].CellFormat.Fill = CellFill.CreateSolidFill(backgroundcolor);
oWorksheet.Rows[0].Cells[i].CellFormat.Font.ColorInfo = new WorkbookColorInfo(forecolor);
}
ExcelProcessEvent("setSheetHeader End");
}
保存Workbook
保存成本地文件
保存成字节流（想着做成服务，提供Excel下载）
public bool Save(string sFileName)
{
ExcelProcessEvent("Save Start, sFileName - " + sFileName);
WorkbookFormat? format = Workbook.GetWorkbookFormat(sFileName);
if (!format.HasValue)
{
ExcelErrorEvent("Save Failed, Error - No matched Workbook format found");
return false;
}
try
{
_Workbook.SetCurrentFormat(format.Value);
if (_Workbook.Worksheets.Count <= 0) _Workbook.Worksheets.Add("Sheet1");
_Workbook.Save(sFileName);
ExcelProcessEvent("Save End, Success");
return true;
}
catch (Exception ex)
{
ExcelErrorEvent("Save Failed, Error - " + ex.Message);
return false;
}
}
public bool Save(out byte[] fileBytes, Format eFormat = Format.Excel97To2003)
{
ExcelProcessEvent("Save Start");
fileBytes = new byte[0];
WorkbookFormat? format = formatMap(eFormat);
if (!format.HasValue)
{
ExcelErrorEvent("Save Failed, Error - No matched Workbook format found");
return false;
}
try
{
using (MemoryStream ms = new MemoryStream())
{
_Workbook.SetCurrentFormat(format.Value);
if (_Workbook.Worksheets.Count <= 0) _Workbook.Worksheets.Add("Sheet1");
_Workbook.Save(ms);
ms.Seek(0, SeekOrigin.Begin);
fileBytes = new byte[(int)ms.Length];
ms.Read(fileBytes, 0, fileBytes.Length);
ExcelProcessEvent("Save End, Success");
return true;
}
}
catch (Exception ex)
{
ExcelErrorEvent("Save Failed, Error - " + ex.Message);
return false;
}
}
导入Excel
加载文件
public bool Load(string sFileName)
{
ExcelProcessEvent("Load Start, sFileName - " + sFileName);
try
{
_Workbook = Workbook.Load(sFileName);
ExcelProcessEvent("Load End, Success");
return true;
}
catch (Exception ex)
{
ExcelErrorEvent("Load Failed, Error - " + ex.Message);
return false;
}
}
解析Worksheet
泛型更通用
反射动态创建类实例
public List<T> ReadSheet<T>(string sSheetName)
{
ExcelProcessEvent("ReadSheet Start, sSheetName - " + sSheetName);
List<T> lst = new List<T>();
Worksheet aWorksheet = null;
try
{
aWorksheet = _Workbook.Worksheets[sSheetName];
if (aWorksheet == null)
{
ExcelProcessEvent("ReadSheet Failed, Error - No Worksheet found");
return lst;
}
var lstHeaders = aWorksheet.Rows[0].Cells.Select(o => o.Value.ToString()).ToList();
Type rowdataType = typeof(T);
List<KeyValuePair<string, int>> lstHeadersOrder = new List<KeyValuePair<string, int>>();
for (int i = 1; i < aWorksheet.Rows.Count(); i++)
{
var row = aWorksheet.Rows[i];
T obj = (T)Activator.CreateInstance(rowdataType);
foreach (var prop in rowdataType.GetProperties())
{
var displayNameAttr = prop.GetCustomAttribute<DisplayNameAttribute>();
string displayName = displayNameAttr.DisplayName;
int cellIndex = lstHeaders.IndexOf(displayName);
prop.SetValue(obj, row.Cells[cellIndex].Value);
}
lst.Add(obj);
}
return lst;
}
catch (Exception ex)
{
ExcelErrorEvent("ReadSheet Failed, Error - " + ex.Message);
}
return lst;
}
客户端调用
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.ComponentModel;
using System.Windows.Media;
using Service.Excel;
namespace Demo.Excel.Client
{
public class Book
{
[DisplayName("Book Number")]
[WorksheetHeader("#006699", "#ffffff")]
public string Id { get; set; }
[DisplayName("Book Name")]
[WorksheetHeader("#006699", "#ffffff")]
public string Name { get; set; }
[DisplayName("Price")]
[WorksheetHeader("#006699", "#ffffff")]
public string Price { get; set; }
[DisplayName("Author Name")]
[WorksheetHeader("#006699", "#ffffff")]
public string Author { get; set; }
[DisplayName("Book Description")]
[WorksheetHeader("#006699", "#ffffff")]
public string Description { get; set; }
}
}
private void test_SaveAs()
{
ExcelHelper oExcelHelper = new ExcelHelper();
SaveFileDialog saveFileDialog = new SaveFileDialog();
saveFileDialog.Filter = "Excel Workbook|*.xlsx|Excel Macro-Enabled Workbook|*.xlsm|Excel 97-2003 Workbook|*.xls|Excel Template|*.xltx|Excel Macro-Enabled Template|*.xltm|Excel 97-2003 Template|*.xlt|Static Open XML Spreadsheet|*.xlsx";
saveFileDialog.FilterIndex = 1;
saveFileDialog.AddExtension = true;
saveFileDialog.FileName = "TestExcel." + DateTime.Now.ToString("yyyyMMddHHmmss");
if (saveFileDialog.ShowDialog() == true)
{
List<Book> lstBook = new List<Book>();
for (int i = 0; i < 2000; i++) {
Book aBook = new Book();
aBook.Id = i.ToString();
aBook.Name = "Book - " + i.ToString();
aBook.Price = i.ToString();
aBook.Author = "Cad-Capture";
aBook.Description = "This is a famous book around the world";
lstBook.Add(aBook);
}
oExcelHelper.CreateSheet<Book>("Book", lstBook, true);
byte[] fileBytes = new byte[0];
if (oExcelHelper.Save(saveFileDialog.FileName))
{
}
}
}
private void test_SaveAsBinary()
{
ExcelHelper oExcelHelper = new ExcelHelper();
SaveFileDialog saveFileDialog = new SaveFileDialog();
saveFileDialog.Filter = "Excel Workbook|*.xlsx|Excel Macro-Enabled Workbook|*.xlsm|Excel 97-2003 Workbook|*.xls|Excel Template|*.xltx|Excel Macro-Enabled Template|*.xltm|Excel 97-2003 Template|*.xlt|Static Open XML Spreadsheet|*.xlsx";
saveFileDialog.FilterIndex = 1;
saveFileDialog.AddExtension = true;
saveFileDialog.FileName = "TestExcel." + DateTime.Now.ToString("yyyyMMddHHmmss");
if (saveFileDialog.ShowDialog() == true)
{
byte[] fileBytes = new byte[0];
if (oExcelHelper.Save(out fileBytes, oExcelHelper.GetFormat(System.IO.Path.GetExtension(saveFileDialog.SafeFileName))))
{
try
{
using (FileStream fileStream = File.OpenWrite(saveFileDialog.FileName))
{
fileStream.Write(fileBytes, 0, fileBytes.Length);
}
}
catch(Exception ex)
{
}
}
}
}
private void test_Load()
{
ExcelHelper oExcelHelper = new ExcelHelper();
OpenFileDialog openFileDialog = new OpenFileDialog();
openFileDialog.Filter = "Excel Workbook|*.xlsx|Excel Macro-Enabled Workbook|*.xlsm|Excel 97-2003 Workbook|*.xls|Excel Template|*.xltx|Excel Macro-Enabled Template|*.xltm|Excel 97-2003 Template|*.xlt|Static Open XML Spreadsheet|*.xlsx";
openFileDialog.FilterIndex = 1;
openFileDialog.AddExtension = true;
if (openFileDialog.ShowDialog() == true)
{
List<Book> lstBook = new List<Book>();
if (oExcelHelper.Load(openFileDialog.FileName)) {
lstBook = oExcelHelper.ReadSheet<Book>("Book");
}
int count = lstBook.Count;
}
}
小结
本文只是简单的数据封装然后导入导出，用了下泛型、反射、数据流、自定义特性，下一篇会搞一搞简单的Excel样式，Excel下载。
另外如何让ExcelHelper类更加的一劳永逸，各位博友有什么更好地想法，欢迎分享。