Npoi导入导出Excel操作

之前公司的一个物流商系统需要实现对订单的批量导入和导出，翻阅了一些资料，最后考虑使用NPOI实现这个需求。

在winform上面实现excel操作:http://www.cnblogs.com/CallmeYhz/p/4920293.html,NPOI的主页：http://npoi.codeplex.com/

简介

NPOI 是 POI 项目的 .NET 版本。POI是一个开源的Java读写Excel、WORD等微软OLE2组件文档的项目, 使用 NPOI 你就可以在没有安装 Office 或者相应环境的机器上对 WORD/EXCEL 文档进行读写。NPOI是构建在POI 3.x版本之上的，它可以在没有安装Office的情况下对Word/Excel文档进行读写操作。

使用NPOI的优势

1、您可以完全免费使用该框架

2、包含了大部分EXCEL的特性(单元格样式、数据格式、公式等等)

3、专业的技术支持服务(24*7全天候) (非免费)

4、支持处理的文件格式包括xls, xlsx, docx.

5、采用面向接口的设计架构( 可以查看 NPOI.SS 的命名空间)

6、同时支持文件的导入和导出

7、基于.net 2.0 也支持xlsx 和 docx格式(当然也支持.net 4.0)

8、来自全世界大量成功且真实的测试Cases

9、大量的实例代码

11、你不需要在服务器上安装微软的Office，可以避免版权问题。

12、使用起来比Office PIA的API更加方便，更人性化。

13、你不用去花大力气维护NPOI，NPOI Team会不断更新、改善NPOI，绝对省成本。

程序集构成

Assembly名称	模块/命名空间	说明
NPOI.DLL	NPOI.POIFS	OLE2/ActiveX文档属性读写库
NPOI.DLL	NPOI.DDF	微软Office Drawing读写库
NPOI.DLL	NPOI.HPSF	OLE2/ActiveX文档读写库
NPOI.DLL	NPOI.HSSF	微软Excel BIFF(Excel 97-2003, doc)格式读写库
NPOI.DLL	NPOI.SS	Excel公用接口及Excel公式计算引擎
NPOI.DLL	NPOI.Util	基础类库，提供了很多实用功能，可用于其他读写文件格式项目的开发
NPOI.OOXML.DLL	NPOI.XSSF	Excel 2007(xlsx)格式读写库
NPOI.OOXML.DLL	NPOI.XWPF	Word 2007(docx)格式读写库
NPOI.OpenXml4Net.DLL	NPOI.OpenXml4Net	OpenXml底层zip包读写库
NPOI.OpenXmlFormats.DLL	NPOI.OpenXmlFormats	微软Office OpenXml对象关系库

类库

使用NuGet引入包，也可以手动导入

再添加一个ExcelHelper操作类，网络上很多，我优化了一些细节，并且自测没问题，附上ExcelHelper操作类:

using System;
using System.Data;
using System.IO;
using System.Text;
using System.Web;
using NPOI.HPSF;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel; 

namespace ExcelOperate
{
    public class ExcelHelper
    {
        /// <summary>
        /// DataTable导出到Excel文件
        /// </summary>
        /// <param name="dtSource">源DataTable</param>
        /// <param name="strHeaderText">表头文本</param>
        /// <param name="strFileName">保存位置</param>
        /// <param name="strSheetName">工作表名称</param>
        /// <Author>CallmeYhz 2015-11-26 10:13:09</Author>
        public static void Export(DataTable dtSource, string strHeaderText, string strFileName, string strSheetName, string[] oldColumnNames, string[] newColumnNames)
        {
            if (strSheetName == "")
            {
                strSheetName = "Sheet";
            }
            using (MemoryStream ms = Export(dtSource, strHeaderText, strSheetName, oldColumnNames, newColumnNames))
            {
                using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
                {
                    byte[] data = ms.ToArray();
                    fs.Write(data, , data.Length);
                    fs.Flush();
                }
            }
        }

        /// <summary>
        /// DataTable导出到Excel文件（无表头）另外的是有表头的
        /// </summary>
        /// <param name="dtSource">源DataTable</param>
        /// <param name="strHeaderText">表头文本</param>
        /// <param name="strFileName">保存位置</param>
        /// <param name="strSheetName">工作表名称</param>
        /// <Author>CallmeYhz 2015-11-26 10:13:09</Author>
        public static void MyExport(DataTable dtSource, string strHeaderText, string strFileName, string strSheetName, string[] oldColumnNames, string[] newColumnNames)
        {
            if (strSheetName == "")
            {
                strSheetName = "Sheet";
            }
            MemoryStream getms = new MemoryStream();

            #region 为getms赋值
            if (oldColumnNames.Length != newColumnNames.Length)
            {
                getms= new MemoryStream();
            }
            HSSFWorkbook workbook = new HSSFWorkbook();
            //HSSFSheet sheet = workbook.CreateSheet();// workbook.CreateSheet();
            ISheet sheet = workbook.CreateSheet(strSheetName);

            #region 右击文件 属性信息
            {
                DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
                dsi.Company = "http://....../";
                workbook.DocumentSummaryInformation = dsi;

                SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
                if (HttpContext.Current.Session["realname"] != null)
                {
                    si.Author = HttpContext.Current.Session["realname"].ToString();
                }
                else
                {
                    if (HttpContext.Current.Session["username"] != null)
                    {
                        si.Author = HttpContext.Current.Session["username"].ToString();
                    }
                }                                       //填加xls文件作者信息
                si.ApplicationName = "NPOI";            //填加xls文件创建程序信息
                si.LastAuthor = "OA系统";           //填加xls文件最后保存者信息
                si.Comments = "OA系统自动创建文件";      //填加xls文件作者信息
                si.Title = strHeaderText;               //填加xls文件标题信息
                si.Subject = strHeaderText;              //填加文件主题信息
                si.CreateDateTime = DateTime.Now;
                workbook.SummaryInformation = si;
            }
            #endregion

            ICellStyle dateStyle = workbook.CreateCellStyle();
            IDataFormat format = workbook.CreateDataFormat();
            dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");

            #region 取得列宽
            int[] arrColWidth = new int[oldColumnNames.Length];
            for (int i = ; i < oldColumnNames.Length; i++)
            {
                arrColWidth[i] = Encoding.GetEncoding().GetBytes(newColumnNames[i]).Length;
            }
            /*
            foreach (DataColumn item in dtSource.Columns)
            {
                arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
            }
             * */

            for (int i = ; i < dtSource.Rows.Count; i++)
            {
                for (int j = ; j < oldColumnNames.Length; j++)
                {
                    int intTemp = Encoding.GetEncoding().GetBytes(dtSource.Rows[i][oldColumnNames[j]].ToString()).Length;
                    if (intTemp > arrColWidth[j])
                    {
                        arrColWidth[j] = intTemp;
                    }
                }
                /*
                for (int j = 0; j < dtSource.Columns.Count; j++)
                {
                    int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
                    if (intTemp > arrColWidth[j])
                    {
                        arrColWidth[j] = intTemp;
                    }
                }
                 * */
            }
            #endregion
            int rowIndex = ;

            foreach (DataRow row in dtSource.Rows)
            {
                #region 新建表，填充表头，填充列头，样式
                if (rowIndex ==  || rowIndex == )
                {
                    if (rowIndex != )
                    {
                        sheet = workbook.CreateSheet(strSheetName + ((int)rowIndex / ).ToString());
                    }               

                    #region 列头及样式
                    {
                        //HSSFRow headerRow = sheet.CreateRow(1);
                        IRow headerRow = sheet.CreateRow();

                        ICellStyle headStyle = workbook.CreateCellStyle();
                        headStyle.Alignment = HorizontalAlignment.Center;
                        IFont font = workbook.CreateFont();
                        font.FontHeightInPoints = ;
                        font.Boldweight = ;
                        headStyle.SetFont(font);

                        for (int i = ; i < oldColumnNames.Length; i++)
                        {
                            headerRow.CreateCell(i).SetCellValue(newColumnNames[i]);
                            headerRow.GetCell(i).CellStyle = headStyle;
                            //设置列宽
                            sheet.SetColumnWidth(i, (arrColWidth[i] + ) * );
                        }
                        /*
                        foreach (DataColumn column in dtSource.Columns)
                        {
                            headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
                            headerRow.GetCell(column.Ordinal).CellStyle = headStyle; 

                            //设置列宽
                            sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
                        }
                         * */
                    }
                    #endregion

                    rowIndex = ;
                }
                #endregion

                #region 填充内容
                IRow dataRow = sheet.CreateRow(rowIndex);
                //foreach (DataColumn column in dtSource.Columns)
                for (int i = ; i < oldColumnNames.Length; i++)
                {
                    ICell newCell = dataRow.CreateCell(i);

                    string drValue = row[oldColumnNames[i]].ToString();

                    switch (dtSource.Columns[oldColumnNames[i]].DataType.ToString())
                    {
                        case "System.String"://字符串类型
                            newCell.SetCellValue(drValue);
                            break;
                        case "System.DateTime"://日期类型
                            DateTime dateV;
                            DateTime.TryParse(drValue, out dateV);
                            newCell.SetCellValue(dateV);

                            newCell.CellStyle = dateStyle;//格式化显示
                            break;
                        case "System.Boolean"://布尔型
                            bool boolV = false;
                            bool.TryParse(drValue, out boolV);
                            newCell.SetCellValue(boolV);
                            break;
                        case "System.Int16"://整型
                        case "System.Int32":
                        case "System.Int64":
                        case "System.Byte":
                            int intV = ;
                            int.TryParse(drValue, out intV);
                            newCell.SetCellValue(intV);
                            break;
                        case "System.Decimal"://浮点型
                        case "System.Double":
                            double doubV = ;
                            double.TryParse(drValue, out doubV);
                            newCell.SetCellValue(doubV);
                            break;
                        case "System.DBNull"://空值处理
                            newCell.SetCellValue("");
                            break;
                        default:
                            newCell.SetCellValue("");
                            break;
                    }

                }
                #endregion

                rowIndex++;
            }

            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                ms.Flush();
                ms.Position = ;

                //sheet.Dispose();
                sheet = null;
                workbook = null;
                //workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet
                getms= ms;
            }

            #endregion

            using (MemoryStream ms = getms)
            {
                using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
                {
                    byte[] data = ms.ToArray();
                    fs.Write(data, , data.Length);
                    fs.Flush();
                }
            }
        }

        /// <summary>
        /// DataTable导出到Excel的MemoryStream
        /// </summary>
        /// <param name="dtSource">源DataTable</param>
        /// <param name="strHeaderText">表头文本</param>
        /// <param name="strSheetName">工作表名称</param>
        /// <Author>CallmeYhz 2015-11-26 10:13:09</Author>
        public static MemoryStream Export(DataTable dtSource, string strHeaderText, string strSheetName, string[] oldColumnNames, string[] newColumnNames)
        {
            if (oldColumnNames.Length != newColumnNames.Length)
            {
                return new MemoryStream();
            }
            HSSFWorkbook workbook = new HSSFWorkbook();
            //HSSFSheet sheet = workbook.CreateSheet();// workbook.CreateSheet();
            ISheet sheet = workbook.CreateSheet(strSheetName);

            #region 右击文件 属性信息
            {
                DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
                dsi.Company = "http://....../";
                workbook.DocumentSummaryInformation = dsi;

                SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
                if (HttpContext.Current.Session["realname"] != null)
                {
                    si.Author = HttpContext.Current.Session["realname"].ToString();
                }
                else
                {
                    if (HttpContext.Current.Session["username"] != null)
                    {
                        si.Author = HttpContext.Current.Session["username"].ToString();
                    }
                }                                       //填加xls文件作者信息
                si.ApplicationName = "NPOI";            //填加xls文件创建程序信息
                si.LastAuthor = "OA系统";           //填加xls文件最后保存者信息
                si.Comments = "OA系统自动创建文件";      //填加xls文件作者信息
                si.Title = strHeaderText;               //填加xls文件标题信息
                si.Subject = strHeaderText;              //填加文件主题信息
                si.CreateDateTime = DateTime.Now;
                workbook.SummaryInformation = si;
            }
            #endregion

            ICellStyle dateStyle = workbook.CreateCellStyle();
            IDataFormat format = workbook.CreateDataFormat();
            dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");

            #region 取得列宽
            int[] arrColWidth = new int[oldColumnNames.Length];
            for (int i = ; i < oldColumnNames.Length; i++)
            {
                arrColWidth[i] = Encoding.GetEncoding().GetBytes(newColumnNames[i]).Length;
            }
            /*
            foreach (DataColumn item in dtSource.Columns)
            {
                arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
            }
             * */

            for (int i = ; i < dtSource.Rows.Count; i++)
            {
                for (int j = ; j < oldColumnNames.Length; j++)
                {
                    int intTemp = Encoding.GetEncoding().GetBytes(dtSource.Rows[i][oldColumnNames[j]].ToString()).Length;
                    if (intTemp > arrColWidth[j])
                    {
                        arrColWidth[j] = intTemp;
                    }
                }
                /*
                for (int j = 0; j < dtSource.Columns.Count; j++)
                {
                    int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
                    if (intTemp > arrColWidth[j])
                    {
                        arrColWidth[j] = intTemp;
                    }
                }
                 * */
            }
            #endregion
            int rowIndex = ;

            foreach (DataRow row in dtSource.Rows)
            {
                #region 新建表，填充表头，填充列头，样式
                if (rowIndex ==  || rowIndex == )
                {
                    if (rowIndex != )
                    {
                        sheet = workbook.CreateSheet(strSheetName + ((int)rowIndex / ).ToString());
                    }

                    #region 表头及样式
                    {
                        IRow headerRow = sheet.CreateRow();
                        headerRow.HeightInPoints = ;
                        headerRow.CreateCell().SetCellValue(strHeaderText);

                        ICellStyle headStyle = workbook.CreateCellStyle();
                        headStyle.Alignment = HorizontalAlignment.Center;
                        IFont font = workbook.CreateFont();
                        font.FontHeightInPoints = ;
                        font.Boldweight = ;
                        headStyle.SetFont(font);

                        headerRow.GetCell().CellStyle = headStyle;
                        //sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1));
                        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(, , , dtSource.Columns.Count - ));
                    }
                    #endregion

                    #region 列头及样式
                    {
                        //HSSFRow headerRow = sheet.CreateRow(1);
                        IRow headerRow = sheet.CreateRow();

                        ICellStyle headStyle = workbook.CreateCellStyle();
                        headStyle.Alignment = HorizontalAlignment.Center;
                        IFont font = workbook.CreateFont();
                        font.FontHeightInPoints = ;
                        font.Boldweight = ;
                        headStyle.SetFont(font);

                        for (int i = ; i < oldColumnNames.Length; i++)
                        {
                            headerRow.CreateCell(i).SetCellValue(newColumnNames[i]);
                            headerRow.GetCell(i).CellStyle = headStyle;
                            //设置列宽
                            sheet.SetColumnWidth(i, (arrColWidth[i] + ) * );
                        }
                        /*
                        foreach (DataColumn column in dtSource.Columns)
                        {
                            headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
                            headerRow.GetCell(column.Ordinal).CellStyle = headStyle; 

                            //设置列宽
                            sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
                        }
                         * */
                    }
                    #endregion

                    rowIndex = ;
                }
                #endregion

                #region 填充内容
                IRow dataRow = sheet.CreateRow(rowIndex);
                //foreach (DataColumn column in dtSource.Columns)
                for (int i = ; i < oldColumnNames.Length; i++)
                {
                    ICell newCell = dataRow.CreateCell(i);

                    string drValue = row[oldColumnNames[i]].ToString();

                    switch (dtSource.Columns[oldColumnNames[i]].DataType.ToString())
                    {
                        case "System.String"://字符串类型
                            newCell.SetCellValue(drValue);
                            break;
                        case "System.DateTime"://日期类型
                            DateTime dateV;
                            DateTime.TryParse(drValue, out dateV);
                            newCell.SetCellValue(dateV);

                            newCell.CellStyle = dateStyle;//格式化显示
                            break;
                        case "System.Boolean"://布尔型
                            bool boolV = false;
                            bool.TryParse(drValue, out boolV);
                            newCell.SetCellValue(boolV);
                            break;
                        case "System.Int16"://整型
                        case "System.Int32":
                        case "System.Int64":
                        case "System.Byte":
                            int intV = ;
                            int.TryParse(drValue, out intV);
                            newCell.SetCellValue(intV);
                            break;
                        case "System.Decimal"://浮点型
                        case "System.Double":
                            double doubV = ;
                            double.TryParse(drValue, out doubV);
                            newCell.SetCellValue(doubV);
                            break;
                        case "System.DBNull"://空值处理
                            newCell.SetCellValue("");
                            break;
                        default:
                            newCell.SetCellValue("");
                            break;
                    }

                }
                #endregion

                rowIndex++;
            }

            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                ms.Flush();
                ms.Position = ;

                //sheet.Dispose();
                sheet = null;
                workbook = null;
                //workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet
                return ms;
            }
        }

        /// <summary>
        /// WEB导出DataTable到Excel
        /// </summary>
        /// <param name="dtSource">源DataTable</param>
        /// <param name="strHeaderText">表头文本</param>
        /// <param name="strFileName">文件名</param>
        /// <Author>CallmeYhz 2015-11-26 10:13:09</Author>
        public static void ExportByWeb(DataTable dtSource, string strHeaderText, string strFileName)
        {
            ExportByWeb(dtSource, strHeaderText, strFileName, "sheet");
        }

        /// <summary>
        /// WEB导出DataTable到Excel
        /// </summary>
        /// <param name="dtSource">源DataTable</param>
        /// <param name="strHeaderText">表头文本</param>
        /// <param name="strFileName">输出文件名，包含扩展名</param>
        /// <param name="oldColumnNames">要导出的DataTable列数组</param>
        /// <param name="newColumnNames">导出后的对应列名</param>
        public static void ExportByWeb(DataTable dtSource, string strHeaderText, string strFileName, string[] oldColumnNames, string[] newColumnNames)
        {
            ExportByWeb(dtSource, strHeaderText, strFileName, "sheet", oldColumnNames, newColumnNames);
        }

        /// <summary>
        /// WEB导出DataTable到Excel
        /// </summary>
        /// <param name="dtSource">源DataTable</param>
        /// <param name="strHeaderText">表头文本</param>
        /// <param name="strFileName">输出文件名</param>
        /// <param name="strSheetName">工作表名称</param>
        public static void ExportByWeb(DataTable dtSource, string strHeaderText, string strFileName, string strSheetName)
        {
            HttpContext curContext = HttpContext.Current;

            // 设置编码和附件格式
            curContext.Response.ContentType = "application/vnd.ms-excel";
            curContext.Response.ContentEncoding = Encoding.UTF8;
            curContext.Response.Charset = "";
            curContext.Response.AppendHeader("Content-Disposition",
                "attachment;filename=" + HttpUtility.UrlEncode(strFileName, Encoding.UTF8));

            //生成列
            string columns = "";
            for (int i = ; i < dtSource.Columns.Count; i++)
            {
                if (i > )
                {
                    columns += ",";
                }
                columns += dtSource.Columns[i].ColumnName;
            }

            curContext.Response.BinaryWrite(Export(dtSource, strHeaderText, strSheetName, columns.Split(','), columns.Split(',')).GetBuffer());
            curContext.Response.End();

        }

        /// <summary>
        /// 导出DataTable到Excel
        /// </summary>
        /// <param name="dtSource">要导出的DataTable</param>
        /// <param name="strHeaderText">标题文字</param>
        /// <param name="strFileName">文件名，包含扩展名</param>
        /// <param name="strSheetName">工作表名</param>
        /// <param name="oldColumnNames">要导出的DataTable列数组</param>
        /// <param name="newColumnNames">导出后的对应列名</param>
        public static void ExportByWeb(DataTable dtSource, string strHeaderText, string strFileName, string strSheetName, string[] oldColumnNames, string[] newColumnNames)
        {
            HttpContext curContext = HttpContext.Current;

            // 设置编码和附件格式
            curContext.Response.ContentType = "application/vnd.ms-excel";
            curContext.Response.ContentEncoding = Encoding.UTF8;
            curContext.Response.Charset = "";
            curContext.Response.AppendHeader("Content-Disposition",
                "attachment;filename=" + HttpUtility.UrlEncode(strFileName, Encoding.UTF8));

            curContext.Response.BinaryWrite(Export(dtSource, strHeaderText, strSheetName, oldColumnNames, newColumnNames).GetBuffer());
            curContext.Response.End();
        }

        /// <summary>读取excel
        /// 默认第一行为表头，导入第一个工作表
        /// </summary>
        /// <param name="strFileName">excel文档路径</param>
        /// <returns></returns>
        public static DataTable Import(string strFileName)
        {
            DataTable dt = new DataTable();

            HSSFWorkbook hssfworkbook;
            using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
            {
                hssfworkbook = new HSSFWorkbook(file);
            }
            ISheet sheet = hssfworkbook.GetSheetAt();
            System.Collections.IEnumerator rows = sheet.GetRowEnumerator();

            IRow headerRow = sheet.GetRow();
            int cellCount = headerRow.LastCellNum;

            for (int j = ; j < cellCount; j++)
            {
                ICell cell = headerRow.GetCell(j);
                dt.Columns.Add(cell.ToString());
            }

            for (int i = (sheet.FirstRowNum + ); i <= sheet.LastRowNum; i++)
            {
                IRow row = sheet.GetRow(i);
                DataRow dataRow = dt.NewRow();

                for (int j = row.FirstCellNum; j < cellCount; j++)
                {
                    if (row.GetCell(j) != null)
                        dataRow[j] = row.GetCell(j).ToString();
                }
                dt.Rows.Add(dataRow);
            }
            return dt;
        }
        /// <summary>
        /// 从Excel中获取数据到DataTable
        /// </summary>
        /// <param name="strFileName">Excel文件全路径(服务器路径)</param>
        /// <param name="SheetName">要获取数据的工作表名称</param>
        /// <param name="HeaderRowIndex">工作表标题行所在行号(从0开始)</param>
        /// <returns></returns>
        public static DataTable RenderDataTableFromExcel(string strFileName, string SheetName, int HeaderRowIndex)
        {
            using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
            {
                IWorkbook workbook = new HSSFWorkbook(file);
                ISheet sheet = workbook.GetSheet(SheetName);
                return RenderDataTableFromExcel(workbook, SheetName, HeaderRowIndex);
            }
        }

        /// <summary>
        /// 从Excel中获取数据到DataTable
        /// </summary>
        /// <param name="strFileName">Excel文件全路径(服务器路径)</param>
        /// <param name="SheetIndex">要获取数据的工作表序号(从0开始)</param>
        /// <param name="HeaderRowIndex">工作表标题行所在行号(从0开始)</param>
        /// <returns></returns>
        public static DataTable RenderDataTableFromExcel(string strFileName, int SheetIndex, int HeaderRowIndex)
        {
            using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
            {
                IWorkbook workbook = new HSSFWorkbook(file);
                string SheetName = workbook.GetSheetName(SheetIndex);
                return RenderDataTableFromExcel(workbook, SheetName, HeaderRowIndex);
            }
        }

        /// <summary>
        /// 从Excel中获取数据到DataTable
        /// </summary>
        /// <param name="ExcelFileStream">Excel文件流</param>
        /// <param name="SheetName">要获取数据的工作表名称</param>
        /// <param name="HeaderRowIndex">工作表标题行所在行号(从0开始)</param>
        /// <returns></returns>
        public static DataTable RenderDataTableFromExcel(Stream ExcelFileStream, string SheetName, int HeaderRowIndex)
        {
            IWorkbook workbook = new HSSFWorkbook(ExcelFileStream);
            ExcelFileStream.Close();
            return RenderDataTableFromExcel(workbook, SheetName, HeaderRowIndex);
        }

        /// <summary>
        /// 从Excel中获取数据到DataTable
        /// </summary>
        /// <param name="ExcelFileStream">Excel文件流</param>
        /// <param name="SheetIndex">要获取数据的工作表序号(从0开始)</param>
        /// <param name="HeaderRowIndex">工作表标题行所在行号(从0开始)</param>
        /// <returns></returns>
        public static DataTable RenderDataTableFromExcel(Stream ExcelFileStream, int SheetIndex, int HeaderRowIndex)
        {
            IWorkbook workbook = new HSSFWorkbook(ExcelFileStream);
            ExcelFileStream.Close();
            string SheetName = workbook.GetSheetName(SheetIndex);
            return RenderDataTableFromExcel(workbook, SheetName, HeaderRowIndex);
        }

        /// <summary>
        /// 从Excel中获取数据到DataTable
        /// </summary>
        /// <param name="workbook">要处理的工作薄</param>
        /// <param name="SheetName">要获取数据的工作表名称</param>
        /// <param name="HeaderRowIndex">工作表标题行所在行号(从0开始)</param>
        /// <returns></returns>
        public static DataTable RenderDataTableFromExcel(IWorkbook workbook, string SheetName, int HeaderRowIndex)
        {
            ISheet sheet = workbook.GetSheet(SheetName);
            DataTable table = new DataTable();
            try
            {
                IRow headerRow = sheet.GetRow(HeaderRowIndex);
                int cellCount = headerRow.LastCellNum;

                for (int i = headerRow.FirstCellNum; i < cellCount; i++)
                {
                    DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
                    table.Columns.Add(column);
                }

                int rowCount = sheet.LastRowNum;

                #region 循环各行各列,写入数据到DataTable
                for (int i = (sheet.FirstRowNum + ); i <=sheet.LastRowNum; i++)
                {
                    IRow row = sheet.GetRow(i);
                    DataRow dataRow = table.NewRow();
                    for (int j = row.FirstCellNum; j < cellCount; j++)
                    {
                        ICell cell = row.GetCell(j);
                        if (cell == null)
                        {
                            dataRow[j] = null;
                        }
                        else
                        {
                            //dataRow[j] = cell.ToString();
                            switch (cell.CellType)
                            {
                                case CellType.Blank:
                                    dataRow[j] = null;
                                    break;
                                case CellType.Boolean:
                                    dataRow[j] = cell.BooleanCellValue;
                                    break;
                                case CellType.Numeric:
                                    dataRow[j] = cell.ToString();
                                    break;
                                case CellType.String:
                                    dataRow[j] = cell.StringCellValue;
                                    break;
                                case CellType.Error:
                                    dataRow[j] = cell.ErrorCellValue;
                                    break;
                                case CellType.Formula:
                                default:
                                    dataRow[j] = "=" + cell.CellFormula;
                                    break;
                            }
                        }
                    }
                    table.Rows.Add(dataRow);
                    //dataRow[j] = row.GetCell(j).ToString();
                }
                #endregion
            }
            catch (System.Exception ex)
            {
                table.Clear();
                table.Columns.Clear();
                table.Columns.Add("出错了");
                DataRow dr = table.NewRow();
                dr[] = ex.Message;
                table.Rows.Add(dr);
                return table;
            }
            finally
            {
                //sheet.Dispose();
                workbook = null;
                sheet = null;
            }
            #region 清除最后的空行
            for (int i = table.Rows.Count - ; i > ; i--)
            {
                bool isnull = true;
                for (int j = ; j < table.Columns.Count; j++)
                {
                    if (table.Rows[i][j] != null)
                    {
                        if (table.Rows[i][j].ToString() != "")
                        {
                            isnull = false;
                            break;
                        }
                    }
                }
                if (isnull)
                {
                    table.Rows[i].Delete();
                }
            }
            #endregion
            return table;
        }
    }
}

UI的设计

为了让自己印象深刻，设计了一个前端页面仅供参考

代码如下：

<html>
<head>
    <title>
        ASP.MVC+Bootstrap+NPOI
    </title>
    <script src="~/Scripts/jquery-2.1.4.min.js"></script>
    <link href="~/Content/bootstrap.min.css" rel="stylesheet" />
    <script src="~/Scripts/bootstrap.min.js"></script>
    <style>
        .container {
            padding: 20px 0;
        }
    </style>
</head>
<body>
    <div class="container">
        <form class="form-horizontal" action="Upload" role="form" method="post" enctype="multipart/form-data">
            <div class="form-group">
                <div class="col-md-3">
                    <label class="control-label" style="float:right">上传文件</label>
                </div>
                <div class="col-md-3">
                    <input id="fileUpload" name="fileUpload" type="file" style="display:none" />

                    <input id="fileText" type="text" class="form-control" disabled />
                </div>
                <div class="col-md-0">
                    <button type="button" class=" btn btn-primary" onclick="$('#fileUpload').click()">浏览</button>
                </div>
            </div>

            <script>
                $("#fileUpload").change(function () {
                    $("#fileText").val($(this).val());
                })
            </script>

            <div class="form-group">
                <div class="col-md-3 col-md-offset-3">
                    <button type="submit" class=" btn btn-primary">导入</button>
                    <div class="btn-group">
                        <div class="btn-group">
                            <button type="button" class="btn btn-primary dropdown-toggle" data-toggle="dropdown">
                                导出模板
                            </button>
                            <ul class="dropdown-menu" role="menu">
                                <li><a href="GetExportExcelUrl?ExportTypeIndex=1">导出到.csv</a></li>
                                <li><a href="GetExportExcelUrl?ExportTypeIndex=2">导出到.xls</a></li>
                                <li><a href="GetExportExcelUrl?ExportTypeIndex=3">导出到.xlsx</a></li>
                            </ul>
                        </div>
                    </div>
                </div>
            </div>
        </form>
    </div>
</body>
</html>

EXCEL导入

导入支持.csv、.xls、.xlsx三种格式读出数据到DataTable，接下来可以进行一系列操作

        /// <summary>
        /// 说明：导入的方法
        /// 作者：CallmeYhz
        /// 时间：2015-11-26 14:23:15
        /// </summary>
        /// <param name="fileUpload"></param>
        /// <returns></returns>
        public string Upload(HttpPostedFileBase fileUpload)
        {
            if (fileUpload == null)
            {
                return "文件为空";
            }
            try
            {

                //将硬盘路径转化为服务器路径的文件流
                string fileName = Path.Combine(Request.MapPath("~/SaveFile"), Path.GetFileName(fileUpload.FileName));
                //NPOI得到EXCEL的第一种方法
                fileUpload.SaveAs(fileName);
                DataTable dtData = ExcelHelper.Import(fileName);
                //得到EXCEL的第二种方法(第一个参数是文件流,第二个是excel标签名,第三个是第几行开始读0算第一行)
                DataTable dtData2 = ExcelHelper.RenderDataTableFromExcel(fileName, "Sheet", );
                return "导入成功";
            }
            catch
            {
                return "导入失败";
            }
        }

第一种方法是默认文件的第一行是列名，第二行是数据。第二种方法可以指定标签，行头等参数。

导出Excel并且下载

思路是用NPOI创建文件存放在服务器上然后返回URL开始下载，借助一些方法可以方便进行以下操作

利用反射获得实体的所有属性（一个表的所有列）:

 /// <summary>
        /// 说明：获得一个对象的所有属性
        /// </summary>
        /// <returns></returns>
        private string[] GetPropertyNameArray()
        {
            PropertyInfo[] props = null;
            try
            {
                Type type = typeof(Student);
                object obj = Activator.CreateInstance(type);
                props = type.GetProperties(BindingFlags.Public | BindingFlags.Instance);

                string[] array = props.Select(t => t.Name).ToArray();
                return array;
            }
            catch (Exception ex)
            {
                return null;
            }

        }

将List集合转化成DataTable:

        /// <summary>
        /// 将泛类型集合List类转换成DataTable
        /// </summary>
        /// <param name="list">泛类型集合</param>
        /// <returns></returns>
        public DataTable ListToDataTable<T>(List<T> entitys)
        {
            //检查实体集合不能为空
            if (entitys == null || entitys.Count < )
            {
                throw new Exception("需转换的集合为空");
            }
            //取出第一个实体的所有Propertie
            Type entityType = entitys[].GetType();
            PropertyInfo[] entityProperties = entityType.GetProperties();

            //生成DataTable的structure
            //生产代码中，应将生成的DataTable结构Cache起来，此处略
            DataTable dt = new DataTable();
            for (int i = ; i < entityProperties.Length; i++)
            {
                //dt.Columns.Add(entityProperties[i].Name, entityProperties[i].PropertyType);
                dt.Columns.Add(entityProperties[i].Name);
            }
            //将所有entity添加到DataTable中
            foreach (object entity in entitys)
            {
                //检查所有的的实体都为同一类型
                if (entity.GetType() != entityType)
                {
                    throw new Exception("要转换的集合元素类型不一致");
                }
                object[] entityValues = new object[entityProperties.Length];
                for (int i = ; i < entityProperties.Length; i++)
                {
                    entityValues[i] = entityProperties[i].GetValue(entity, null);
                }
                dt.Rows.Add(entityValues);
            }
            return dt;
        }

导出：

        /// <summary>
        /// 获得导出的url并导出
        /// </summary>
        /// <param name="ExportTypeIndex">导出EXCEL类型索引</param>
        /// <returns>URL提供用户下载</returns>
        public FileResult GetExportExcelUrl(int ExportTypeIndex)
        {
            //构造导出的集合
            List<Student> StudentList = new List<Student>()
            {
                new Student()
                {
                  SnoID="",
                  SAge=,
                  SName="yhz",
                  SDateTime=DateTime.Now
                },
                  new Student()
                {
                  SnoID="",
                  SAge=,
                  SName="kq",
                  SDateTime=DateTime.Now
                }
            };

            string[] oldColumn = GetPropertyNameArray();
            string[] newColumn = new string[] { "学号", "姓名", "年龄", "现在时间" };

            //类型转换（将List转化为DataTable）
            DataTable ExportDt = this.ListToDataTable<Student>(StudentList);
            //可以考虑读取配置文件
            string path = "/SaveFile/";
            string fileName = "";
            if(ExportTypeIndex==)
            {
                 fileName = "导出CSV.csv";
            }
            else if (ExportTypeIndex == )
            {
                fileName = "导出XLS.xls";
            }
            else
            {
                fileName = "导出XLSX.xlsx";
            }

            string streamFileName = Path.Combine(Request.MapPath(path), Path.GetFileName(fileName));

            //调用改写的NPOI方法
            ExcelHelper.MyExport(ExportDt, "大家好我是表头",streamFileName, "", oldColumn, newColumn);
            if (ExportTypeIndex == )
            {
                return File(path + fileName, "application/zip-x-compressed", "物流订单模板导出.csv");
            }
            else if (ExportTypeIndex == )
            {
                return new FilePathResult(path + fileName, "application/vnd.ms-excel");
            }
            else
            {
                return new FilePathResult(path+fileName, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet ");
            }
        }

效果演示

源代码下载

http://files.cnblogs.com/files/CallmeYhz/MVCForNPOI.rar

简单的导出EXCEL方法：

    public ActionResult Export()
        {
            string table = "<table border='1px solid black'><tr><td>编号</td><td>真实姓名</td><td>昵称</td><td>电话</td><td>数量</td><td>参与时间</td><td>状态</td><td>备注</td></tr>";
            table += "<tr><td>1</td><td>2</td><td>3</td><td>4<td>5</td><td>6</td><td>6</td><td>6</td></tr>";
            table += "</table>";
            string fileName = "用户列表" + DateTime.Now.ToString("yyyyMMddhhmmss") + ".xls";
            Response.AddHeader("Content-Disposition", "attachment;filename=" + fileName);
            Response.ContentType = "application/excel";
            Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
            Response.Write(table);
            Response.End();
            return View();
        }