C#用Infragistics 导入导出Excel

最近项目中有数据的导入导出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类更加的一劳永逸，各位博友有什么更好地想法，欢迎分享。

转载于:https://www.cnblogs.com/sjqq/p/6889299.html