最近项目中有数据的导入导出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 = http://www.mamicode.com/prop.GetValue(rowdata);"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 = http://www.mamicode.com/displayName;"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类更加的一劳永逸，各位博友有什么更好地想法，欢迎分享。

C#用Infragistics 导入导出Excel（一）