ASP.MVC+Npoi+Bootrstrap实现3种格式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 ，绝对省成本。

程序集构成

类库

使用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, 0, 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 = 0; i < oldColumnNames.Length; i++)    {     arrColWidth[i] = Encoding.GetEncoding(936).GetBytes(newColumnNames[i]).Length;    }    /*     foreach (DataColumn item in dtSource.Columns)     {      arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;     }      * */    for (int i = 0; i < dtSource.Rows.Count; i++)    {     for (int j = 0; j < oldColumnNames.Length; j++)     {      int intTemp = Encoding.GetEncoding(936).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 = 0;    foreach (DataRow row in dtSource.Rows)    {     #region 新建表，填充表头，填充列头，样式     if (rowIndex == 65535 || rowIndex == 0)     {      if (rowIndex != 0)      {       sheet = workbook.CreateSheet(strSheetName + ((int)rowIndex / 65535).ToString());      }            #region 列头及样式      {       //HSSFRow headerRow = sheet.CreateRow(1);          IRow headerRow = sheet.CreateRow(0);       ICellStyle headStyle = workbook.CreateCellStyle();       headStyle.Alignment = HorizontalAlignment.Center;       IFont font = workbook.CreateFont();       font.FontHeightInPoints = 10;       font.Boldweight = 700;       headStyle.SetFont(font);       for (int i = 0; i < oldColumnNames.Length; i++)       {        headerRow.CreateCell(i).SetCellValue(newColumnNames[i]);        headerRow.GetCell(i).CellStyle = headStyle;        //设置列宽           sheet.SetColumnWidth(i, (arrColWidth[i] + 1) * 256);       }       /*        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 = 1;     }     #endregion     #region 填充内容     IRow dataRow = sheet.CreateRow(rowIndex);     //foreach (DataColumn column in dtSource.Columns)        for (int i = 0; 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 = 0;        int.TryParse(drValue, out intV);        newCell.SetCellValue(intV);        break;       case "System.Decimal"://浮点型          case "System.Double":        double doubV = 0;        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 = 0;     //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, 0, 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 = 0; i < oldColumnNames.Length; i++)    {     arrColWidth[i] = Encoding.GetEncoding(936).GetBytes(newColumnNames[i]).Length;    }    /*     foreach (DataColumn item in dtSource.Columns)     {      arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;     }      * */    for (int i = 0; i < dtSource.Rows.Count; i++)    {     for (int j = 0; j < oldColumnNames.Length; j++)     {      int intTemp = Encoding.GetEncoding(936).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 = 0;    foreach (DataRow row in dtSource.Rows)    {     #region 新建表，填充表头，填充列头，样式     if (rowIndex == 65535 || rowIndex == 0)     {      if (rowIndex != 0)      {       sheet = workbook.CreateSheet(strSheetName + ((int)rowIndex / 65535).ToString());      }      #region 表头及样式      {       IRow headerRow = sheet.CreateRow(0);       headerRow.HeightInPoints = 25;       headerRow.CreateCell(0).SetCellValue(strHeaderText);       ICellStyle headStyle = workbook.CreateCellStyle();       headStyle.Alignment = HorizontalAlignment.Center;       IFont font = workbook.CreateFont();       font.FontHeightInPoints = 20;       font.Boldweight = 700;       headStyle.SetFont(font);       headerRow.GetCell(0).CellStyle = headStyle;       //sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1));          sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));      }      #endregion      #region 列头及样式      {       //HSSFRow headerRow = sheet.CreateRow(1);          IRow headerRow = sheet.CreateRow(1);       ICellStyle headStyle = workbook.CreateCellStyle();       headStyle.Alignment = HorizontalAlignment.Center;       IFont font = workbook.CreateFont();       font.FontHeightInPoints = 10;       font.Boldweight = 700;       headStyle.SetFont(font);       for (int i = 0; i < oldColumnNames.Length; i++)       {        headerRow.CreateCell(i).SetCellValue(newColumnNames[i]);        headerRow.GetCell(i).CellStyle = headStyle;        //设置列宽           sheet.SetColumnWidth(i, (arrColWidth[i] + 1) * 256);       }       /*        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 = 2;     }     #endregion     #region 填充内容     IRow dataRow = sheet.CreateRow(rowIndex);     //foreach (DataColumn column in dtSource.Columns)        for (int i = 0; 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 = 0;        int.TryParse(drValue, out intV);        newCell.SetCellValue(intV);        break;       case "System.Decimal"://浮点型          case "System.Double":        double doubV = 0;        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 = 0;     //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 = 0; i < dtSource.Columns.Count; i++)    {     if (i > 0)     {      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(0);    System.Collections.IEnumerator rows = sheet.GetRowEnumerator();    IRow headerRow = sheet.GetRow(0);    int cellCount = headerRow.LastCellNum;    for (int j = 0; j < cellCount; j++)    {     ICell cell = headerRow.GetCell(j);     dt.Columns.Add(cell.ToString());    }    for (int i = (sheet.FirstRowNum + 1); 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 + 1); 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[0] = ex.Message;     table.Rows.Add(dr);     return table;    }    finally    {     //sheet.Dispose();        workbook = null;     sheet = null;    }    #region 清除最后的空行    for (int i = table.Rows.Count - 1; i > 0; i--)    {     bool isnull = true;     for (int j = 0; 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;   }  }   } View Code 

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> View Code 

/// <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", 0);  return "导入成功";     }     catch     {  return "导入失败";     } } 

/// <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;    }   } View Code 

/// <summary>   /// 将泛类型集合List类转换成DataTable   /// </summary>   /// <param name="list">泛类型集合</param>   /// <returns></returns>   public DataTable ListToDataTable<T>(List<T> entitys)   {    //检查实体集合不能为空    if (entitys == null || entitys.Count < 1)    {     throw new Exception("需转换的集合为空");    }    //取出第一个实体的所有Propertie    Type entityType = entitys[0].GetType();    PropertyInfo[] entityProperties = entityType.GetProperties();    //生成DataTable的structure    //生产代码中，应将生成的DataTable结构Cache起来，此处略    DataTable dt = new DataTable();    for (int i = 0; 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 = 0; i < entityProperties.Length; i++)     {      entityValues[i] = entityProperties[i].GetValue(entity, null);     }     dt.Rows.Add(entityValues);    }    return dt;   } View Code 

/// <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="1",     SAge=25,     SName="yhz",     SDateTime=DateTime.Now   },     new Student()   {     SnoID="2",     SAge=26,     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==1)  {    fileName = "导出CSV.csv";  }  else if (ExportTypeIndex == 2)  {   fileName = "导出XLS.xls";  }  else  {   fileName = "导出XLSX.xlsx";  }  string streamFileName = Path.Combine(Request.MapPath(path), Path.GetFileName(fileName));  //调用改写的NPOI方法  ExcelHelper.MyExport(ExportDt, "大家好我是表头",streamFileName, "1", oldColumn, newColumn);  if (ExportTypeIndex == 1)  {   return File(path + fileName, "application/zip-x-compressed", "物流订单模板导出.csv");  }  else if (ExportTypeIndex == 2)  {   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