【推荐】.NET(webform,MVC)使用NPOI组件将数据导出、导入Excel

一、导出EXcel

【推荐】.NET使用NPOI组件将数据导出Excel

1、NPOI官方网站：http://npoi.codeplex.com/

   可以到此网站上去下载最新的NPOI组件版本

2、NPOI在线学习教程（中文版）：

      感谢Tony Qu分享出NPOI组件的使用方法

3、.NET调用NPOI组件导入导出Excel的操作类
　　此NPOI操作类的优点如下：
   （1）支持web及winform从DataTable导出到Excel； 
   （2）生成速度很快； 
   （3）准确判断数据类型，不会出现身份证转数值等问题； 
   （4）如果单页条数大于65535时会新建工作表； 
   （5）列宽自适应；

 

using System;using System.Data;using System.Configuration;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.HtmlControls;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.IO;using System.Text;using NPOI;using NPOI.HPSF;using NPOI.HSSF;using NPOI.HSSF.UserModel;using NPOI.HSSF.Util;using NPOI.POIFS;using NPOI.Util;namespace Comm{    public class NPOIHelper    {        /// <summary>        /// DataTable导出到Excel文件        /// </summary>        /// <param name="dtSource">源DataTable</param>        /// <param name="strHeaderText">表头文本</param>        /// <param name="strFileName">保存位置</param>        public static void Export(DataTable dtSource, string strHeaderText, string strFileName)        {            using (MemoryStream ms = Export(dtSource, strHeaderText))            {                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>        public static MemoryStream Export(DataTable dtSource, string strHeaderText)        {            HSSFWorkbook workbook = new HSSFWorkbook();            HSSFSheet sheet = workbook.CreateSheet();            #region 右击文件 属性信息            {                DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();                dsi.Company = "NPOI";                workbook.DocumentSummaryInformation = dsi;                SummaryInformation si = PropertySetFactory.CreateSummaryInformation();                si.Author = "文件作者信息"; //填加xls文件作者信息                si.ApplicationName = "创建程序信息"; //填加xls文件创建程序信息                si.LastAuthor = "最后保存者信息"; //填加xls文件最后保存者信息                si.Comments = "作者信息"; //填加xls文件作者信息                si.Title = "标题信息"; //填加xls文件标题信息                si.Subject = "主题信息";//填加文件主题信息                si.CreateDateTime = DateTime.Now;                workbook.SummaryInformation = si;            }            #endregion            HSSFCellStyle dateStyle = workbook.CreateCellStyle();            HSSFDataFormat format = workbook.CreateDataFormat();            dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");            //取得列宽            int[] arrColWidth = new int[dtSource.Columns.Count];            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 < dtSource.Columns.Count; j++)                {                    int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;                    if (intTemp > arrColWidth[j])                    {                        arrColWidth[j] = intTemp;                    }                }            }            int rowIndex = 0;            foreach (DataRow row in dtSource.Rows)            {                #region 新建表，填充表头，填充列头，样式                if (rowIndex == 65535 || rowIndex == 0)                {                    if (rowIndex != 0)                    {                        sheet = workbook.CreateSheet();                    }                    #region 表头及样式                    {                        HSSFRow headerRow = sheet.CreateRow(0);                        headerRow.HeightInPoints = 25;                        headerRow.CreateCell(0).SetCellValue(strHeaderText);                        HSSFCellStyle headStyle = workbook.CreateCellStyle();                        headStyle.Alignment = CellHorizontalAlignment.CENTER;                        HSSFFont 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));                        headerRow.Dispose();                    }                    #endregion                    #region 列头及样式                    {                        HSSFRow headerRow = sheet.CreateRow(1);                        HSSFCellStyle headStyle = workbook.CreateCellStyle();                        headStyle.Alignment = CellHorizontalAlignment.CENTER;                        HSSFFont font = workbook.CreateFont();                        font.FontHeightInPoints = 10;                        font.Boldweight = 700;                        headStyle.SetFont(font);                        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);                        }                        headerRow.Dispose();                    }                    #endregion                    rowIndex = 2;                }                #endregion                #region 填充内容                HSSFRow dataRow = sheet.CreateRow(rowIndex);                foreach (DataColumn column in dtSource.Columns)                {                    HSSFCell newCell = dataRow.CreateCell(column.Ordinal);                    string drValue = row[column].ToString();                    switch (column.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();                //workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet                return ms;            }        }        /// <summary>        /// 用于Web导出        /// </summary>        /// <param name="dtSource">源DataTable</param>        /// <param name="strHeaderText">表头文本</param>        /// <param name="strFileName">文件名</param>        public static void ExportByWeb(DataTable dtSource, string strHeaderText, string strFileName)        {            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).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);            }            HSSFSheet sheet = hssfworkbook.GetSheetAt(0);            System.Collections.IEnumerator rows = sheet.GetRowEnumerator();            HSSFRow headerRow = sheet.GetRow(0);            int cellCount = headerRow.LastCellNum;            for (int j = 0; j < cellCount; j++)            {                HSSFCell cell = headerRow.GetCell(j);                dt.Columns.Add(cell.ToString());            }            for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)            {                HSSFRow 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        /// </summary>        /// <param name="strFileName">FileUpload控件</param>        /// <returns></returns>        public static DataTable Import2(FileUpload strFileName)        {            DataTable table = new DataTable();            if (strFileName.HasFile)            {                //根据路径通过已存在的excel来创建HSSFWorkbook，即整个excel文档                HSSFWorkbook workbook = new HSSFWorkbook(strFileName.FileContent);                //获取excel的第一个sheet                HSSFSheet sheet = workbook.GetSheetAt(0);                                //获取sheet的首行                HSSFRow headerRow = sheet.GetRow(0);                //一行最后一个方格的编号 即总的列数                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;                for (int i = (sheet.FirstRowNum + 1); i <=sheet.LastRowNum; i++)                {                    HSSFRow row = sheet.GetRow(i);                    DataRow dataRow = table.NewRow();                    for (int j = row.FirstCellNum; j < cellCount; j++)                    {                        if (row.GetCell(j) != null)                            dataRow[j] = row.GetCell(j).ToString();                    }                    table.Rows.Add(dataRow);                }                workbook = null;                sheet = null;                return table;            }            else            {                return table;            }        }    }}

4、NPOI操作类的调用方法
   DataTable dt_Grade = Tools.Data.GetDataTable(strSQL);
   filename += "绩效考核结果分数统计表";
   PMS.Common.NPOIHelper.ExportByWeb(dt_Grade, filename, filename+".xls");

                //重新组装表                DataTable dt2 = new DataTable();//表                DataColumn dc = null;//列集合                dc = dt2.Columns.Add("序号", Type.GetType("System.String"));                dc = dt2.Columns.Add("用户姓名", Type.GetType("System.String"));                for (int i = 0; i < dt.Rows.Count; i++)                {                    DataRow newRow = dt2.NewRow();   //新行                    newRow["序号"] = dt.Rows[i]["id"];                    newRow["用户姓名"] = dt.Rows[i]["yhxm"];                    dt2.Rows.Add(newRow);                }

 

二、导入Excel

1、方法一

             （1）上次Excel

               (2)  读取Excel

                      DataTable dt = NPOIHelper.Import(Request.ServerVariables[3] + "Uploads/student.xls");

              (3) 导入数据库

2、方法二

           （1）读取Excel
                  DataTable dt = NPOIHelper.Import2(FileUpload1);

            (2)  导入数据库

 

NPOI 类库下载：http://download.csdn.net/detail/tiz198183/5597953

 

Asp.Net MVC 使用FileResult导出Excel数据文件

        public FileResult ExportExcel()        {            var sbHtml = new StringBuilder();            sbHtml.Append("<table border='1' cellspacing='0' cellpadding='0'>");            sbHtml.Append("<tr>");            var lstTitle = new List<string> { "编号", "姓名", "年龄", "创建时间" };            foreach (var item in lstTitle)            {                sbHtml.AppendFormat("<td style='font-size: 14px;text-align:center;background-color: #DCE0E2; font-weight:bold;' height='25'>{0}</td>", item);            }            sbHtml.Append("</tr>");            for (int i = 0; i < 1000; i++)            {                sbHtml.Append("<tr>");                sbHtml.AppendFormat("<td style='font-size: 12px;height:20px;'>{0}</td>", i);                sbHtml.AppendFormat("<td style='font-size: 12px;height:20px;'>屌丝{0}号</td>", i);                sbHtml.AppendFormat("<td style='font-size: 12px;height:20px;'>{0}</td>", new Random().Next(20, 30) + i);                sbHtml.AppendFormat("<td style='font-size: 12px;height:20px;'>{0}</td>", DateTime.Now);                sbHtml.Append("</tr>");            }            sbHtml.Append("</table>");            //第一种:使用FileContentResult            byte[] fileContents = Encoding.Default.GetBytes(sbHtml.ToString());            return File(fileContents, "application/ms-excel", "fileContents.xls");            //第二种:使用FileStreamResult            //var fileStream = new MemoryStream(fileContents);            //return File(fileStream, "application/ms-excel", "fileStream.xls");            //第三种:使用FilePathResult            //服务器上首先必须要有这个Excel文件,然会通过Server.MapPath获取路径返回.            //var fileName = Server.MapPath("~/Files/fileName.xls");            //return File(fileName, "application/ms-excel", "fileName.xls");        }