不依赖Excel是否安装的Excel导入导出类

本文利用第三方开源库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;        }    }}