NPOI ExcelHelper

ExcelHelper

using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CMS.Common
{
    public class ExcelHelper
    {
        /// <summary>  
        /// 将Excel表导入DataTable  
        /// </summary>  
        /// <param name="filePath">目标Excel文件的物理路径(xxx.xls)</param>  
        /// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param>
        /// <returns></returns>  
        public static DataTable ImportExcelFile(string filePath, bool isFirstRowColumn)
        {
            HSSFWorkbook hssfworkbook;
            try
            {
                using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
                {
                    hssfworkbook = new HSSFWorkbook(file);
                }
            }
            catch (Exception e)
            {
                throw e;
            }
            NPOI.SS.UserModel.ISheet sheet = hssfworkbook.GetSheetAt(0);
            System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
            DataTable dt = new DataTable();

            #region 添加列
            if (isFirstRowColumn)
            {
                for (int j = sheet.GetRow(0).FirstCellNum; j < (sheet.GetRow(0).LastCellNum); ++j)
                {
                    ICell cell = sheet.GetRow(0).GetCell(j);
                    if (cell != null)
                    {
                        string cellValue = cell.StringCellValue;//将列的值设为列名称
                        if (cellValue != null)
                        {
                            DataColumn column = new DataColumn(cellValue);
                            dt.Columns.Add(column);
                        }
                    }
                }
                rows.MoveNext();//如果Excel表的第一行是表名称，则往下推进一行（数据的遍历从第二行开始）           
            }
            #endregion

            while (rows.MoveNext())
            {
                HSSFRow row = (HSSFRow)rows.Current;
                DataRow dr = dt.NewRow();
                for (int i = 0; i < row.LastCellNum; i++)
                {
                    NPOI.SS.UserModel.ICell cell = row.GetCell(i);
                    if (cell == null)
                    {
                        dr[i] = null;
                    }
                    else
                    {
                        if (cell.CellType == CellType.Numeric && DateUtil.IsCellDateFormatted(cell)) //如果单元格是日期格式  
                        {
                            dr[i] = cell.DateCellValue.ToString("yyyy-MM-dd");
                        }
                        else
                        {
                            dr[i] = cell.ToString();
                        }
                    }
                }
                dt.Rows.Add(dr);
            }
            RemoveEmpty(dt);//删除DataTable中的空白行
            return dt;
        }

        /// <summary>  
        /// 将DataTable导出到Excel表：  
        /// </summary>  
        /// <param name="dt">数据源</param>  
        /// <param name="filePath">写入到目标Excel文件的路径(xxx.xls)</param>  
        public static void DataTableToExcel(DataTable dt, string filePath)
        {
            if (dt.TableName == null || dt.TableName == "")
            {
                dt.TableName = DateTime.Now.ToString();
            }
            if (!string.IsNullOrEmpty(filePath) && null != dt && dt.Rows.Count > 0)
            {
                NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
                NPOI.SS.UserModel.ISheet sheet = book.CreateSheet();
                

                NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);

                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    row.CreateCell(i).SetCellValue(dt.Columns[i].ColumnName);
                }
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(i + 1);
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        row2.CreateCell(j).SetCellValue(Convert.ToString(dt.Rows[i][j]));
                    }
                }
                sheet.CreateFreezePane(0, 1, 0, 1);//冻结第一行（第一行不能拖动）

                // 写入到客户端    
                using (System.IO.MemoryStream ms = new System.IO.MemoryStream())
                {
                    book.Write(ms);
                    using (FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite))
                    {
                        byte[] data = ms.ToArray();
                        fs.Write(data, 0, data.Length);
                        fs.Flush();
                    }
                    book = null;
                }
            }
        }

        /// <summary>  
        /// 删除DataTable中的空白行  
        /// </summary>  
        /// <param name="dt"></param>  
        protected static void RemoveEmpty(DataTable dt)
        {
            List<DataRow> removelist = new List<DataRow>();
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                bool IsNull = true;
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    if (!string.IsNullOrEmpty(dt.Rows[i][j].ToString().Trim()))
                    {
                        IsNull = false;
                    }
                }
                if (IsNull)
                {
                    removelist.Add(dt.Rows[i]);
                }
            }
            for (int i = 0; i < removelist.Count; i++)
            {
                dt.Rows.Remove(removelist[i]);
            }
        }
    }
}

文件下载

public class HomeController : Controller
{
    public ActionResult Index()
    {
        //SqlHelper.SqlBulkCopy();
        DataTable table = SqlHelper.ExecuteDataTable("select * from Table_1", null);
        var filePath = Server.MapPath("Excel/abc.xls");//设定要文件要保存的路径
        
        ExcelHelper.WriteExcel(table,filePath); //将DataTable表的数据写入到Excel文件中
        FileInfo info = new FileInfo(filePath); //读取刚刚保存的Excel文件
        long fileSize = info.Length; 
        Response.Clear(); 
        Response.ContentType = "application/octet-stream";
        Response.AddHeader("Content-Disposition", "attachement;filename=" + "abc.xls"); 
        //指定文件大小   
        Response.AddHeader("Content-Length", fileSize.ToString());  
        Response.WriteFile(filePath, 0, fileSize);  //将文件响应到浏览器
        Response.Flush();  
        Response.Close();

        return View();
    }
}