NPOI导出Excel

一、项目中用到的

1、前台调用

var billCount = $("#txtBillCount").val();
        var selectBillCount = $("#selectBillCount option:selected").text();

        var areas = $("#txtArea").val();
        var selectArea = $("#selectArea option:selected").text();

        var cName = $("#txtEnteName").val();
        //location.href = "/FishBackManager/UseSituation/GetUseSituationData?time=" + $("#datetimepicker").val() + "&billCount=" + billCount + "&areas=" + areas + "&name=" + cName;
        location.href = "/FishBackManager/UseSituation/UseSituationOutPut?time=" + $("#datetimepicker").val() + "&selectBillCount=" + selectBillCount + "&billCount=" + billCount + "&selectArea=" + selectArea + "&areas=" + areas + "&name=" + cName;

 

2、Controller

public ActionResult UseSituationOutPut(DateTime time, string selectBillCount, int billCount, string selectArea, decimal areas, string name)
        {
            DataTransFormBLL _dataTransService = new DataTransFormBLL();
            UseSituationBLL bll = new UseSituationBLL();
            DataTable data1 = bll.GetUseSituationData(time, (new KeyValue { Key = selectBillCount, Value = billCount }), (new KeyValue { Key = selectArea, Value = areas }), name);
            DataTable data = new DataTable();
            data.Columns.Add("创建时间", typeof(string));
            data.Columns.Add("渔场名称", typeof(string));
            data.Columns.Add("负责人", typeof(string));
            data.Columns.Add("最近操作日期", typeof(string));
            data.Columns.Add("管理塘口数", typeof(string));
            data.Columns.Add("总面积", typeof(string));
            data.Columns.Add("进药", typeof(string));
            data.Columns.Add("用药", typeof(string));
            data.Columns.Add("进料", typeof(string));
            data.Columns.Add("用料", typeof(string));
            data.Columns.Add("放苗", typeof(string));
            data.Columns.Add("损失", typeof(string));
            data.Columns.Add("打样", typeof(string));
            data.Columns.Add("出塘", typeof(string));
            data.Columns.Add("销售额", typeof(string));
            data.Columns.Add("单据总计", typeof(int));
            foreach (DataRow row in data1.Rows)
            {
                DataRow _row = data.NewRow();
                _row["创建时间"] =DateTime.Parse(row["dLastModified"].ToString()).ToString("yyyy-MM-dd");
                _row["渔场名称"] = row["cEnteName"];
                _row["负责人"] = row["cName"];
                DateTime maxDate=new DateTime();
                if (!string.IsNullOrWhiteSpace(row["dMpurchaseDate"].ToString()))
                {
                    maxDate = DateTime.Parse(row["dMpurchaseDate"].ToString());
                }
                if (!string.IsNullOrWhiteSpace(row["dMputDate"].ToString()))
                {
                    DateTime _date = DateTime.Parse(row["dMputDate"].ToString());
                    if (maxDate.Subtract(_date).Days < 0)
                    {
                        maxDate = _date;
                    }
                }
                if (!string.IsNullOrWhiteSpace(row["dFodpurchaseDate"].ToString()))
                {
                    DateTime _date = DateTime.Parse(row["dFodpurchaseDate"].ToString());
                    if (maxDate.Subtract(_date).Days < 0)
                    {
                        maxDate = _date;
                    }
                }
                if (!string.IsNullOrWhiteSpace(row["dFodputDate"].ToString()))
                {
                    DateTime _date = DateTime.Parse(row["dFodputDate"].ToString());
                    if (maxDate.Subtract(_date).Days < 0)
                    {
                        maxDate = _date;
                    }
                }
                if (!string.IsNullOrWhiteSpace(row["dFputDate"].ToString()))
                {
                    DateTime _date = DateTime.Parse(row["dFputDate"].ToString());
                    if (maxDate.Subtract(_date).Days < 0)
                    {
                        maxDate = _date;
                    }
                }
                if (!string.IsNullOrWhiteSpace(row["dFlossDate"].ToString()))
                {
                    DateTime _date = DateTime.Parse(row["dFlossDate"].ToString());
                    if (maxDate.Subtract(_date).Days < 0)
                    {
                        maxDate = _date;
                    }
                }
                if (!string.IsNullOrWhiteSpace(row["dFSampleDate"].ToString()))
                {
                    DateTime _date = DateTime.Parse(row["dFSampleDate"].ToString());
                    if (maxDate.Subtract(_date).Days < 0)
                    {
                        maxDate = _date;
                    }
                }
                if (!string.IsNullOrWhiteSpace(row["dFCatchDate"].ToString()))
                {
                    DateTime _date = DateTime.Parse(row["dFCatchDate"].ToString());
                    if (maxDate.Subtract(_date).Days < 0)
                    {
                        maxDate = _date;
                    }
                }
                _row["最近操作日期"] =(maxDate==DateTime.MinValue)?"": maxDate.ToString("yyyy-MM-dd");
                _row["管理塘口数"] = row["iPondCount"];
                _row["总面积"] = row["cAquacultureArea"];
                _row["进药"] = row["iMpurchaseCount"];
                _row["用药"] = row["iMputCount"];
                _row["进料"] = row["iFodpurchaseCount"];
                _row["用料"] = row["iFodputCount"];
                _row["放苗"] = row["iFputCount"];
                _row["损失"] = row["iFlossCount"];
                _row["打样"] = row["iFSampleCount"];
                _row["出塘"] = row["nFCatchCount"];
                _row["销售额"] = row["nAmount"];
                int count = 0;
                if(!string.IsNullOrWhiteSpace(row["iMpurchaseCount"].ToString()))
                {
                    count+=int.Parse(row["iMpurchaseCount"].ToString());
                }
                if(!string.IsNullOrWhiteSpace(row["iMputCount"].ToString()))
                {
                    count += int.Parse(row["iMputCount"].ToString());
                }
                if (!string.IsNullOrWhiteSpace(row["iFodpurchaseCount"].ToString()))
                {
                    count += int.Parse(row["iFodpurchaseCount"].ToString());
                }
                if (!string.IsNullOrWhiteSpace(row["iFodputCount"].ToString()))
                {
                    count += int.Parse(row["iFodputCount"].ToString());
                }
                if (!string.IsNullOrWhiteSpace(row["iFputCount"].ToString()))
                {
                    count += int.Parse(row["iFputCount"].ToString());
                }
                if (!string.IsNullOrWhiteSpace(row["iFlossCount"].ToString()))
                {
                    count += int.Parse(row["iFlossCount"].ToString());
                }
                if (!string.IsNullOrWhiteSpace(row["iFSampleCount"].ToString()))
                {
                    count += int.Parse(row["iFSampleCount"].ToString());
                }
                if (!string.IsNullOrWhiteSpace(row["nFCatchCount"].ToString()))
                {
                    count += int.Parse(row["nFCatchCount"].ToString());
                }
                _row["单据总计"] = count;
                data.Rows.Add(_row);
            }
            _dataTransService.OutputToXLS(data
                , String.Format("渔场使用概况{0}", DateTime.Now.ToString("yyyyMMddHHmmss"))
                , m => m.Caption != "id_Num");
            return new ContentResult() { Content = "" };
        }

 

3、BLL

/// <summary>
        /// 输出数据到客户端浏览器
        /// </summary>
        /// <param name="data">需要输出的数据</param>
        /// <param name="filename">文档名称（浏览器端）</param>
        /// <param name="columns">列名集合</param>
        /// <param name="expression">筛选</param>
        public void OutputToXLS(DataTable data, String filename,Func<DataColumn, bool> expression)
        {
            ExcelHelper.RenderToExcel(data, HttpContext.Current, String.Format("{0}.xls", filename), expression);
        }

4、Common

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;
using System.Web;

namespace WisdomFish.Common
{
    public class ExcelHelper
    {
        /// <summary>
        /// 输出文件到浏览器
        /// </summary>
        /// <param name="ms">Excel文档流</param>
        /// <param name="context">HTTP上下文</param>
        /// <param name="fileName">文件名</param>
        private static void RenderToBrowser(MemoryStream ms, HttpContext context, string fileName)
        {
            if (context.Request.Browser.Browser == "IE")
                fileName = HttpUtility.UrlEncode(fileName);
            context.Response.AddHeader("Content-Disposition", "attachment;fileName=" + fileName);
            context.Response.Charset = "UTF-8";
            context.Response.BinaryWrite(ms.ToArray());
        }
        /// <summary>
        /// DataReader转换成Excel文档流，并输出到客户端
        /// </summary>
        /// <param name="reader"></param>
        /// <param name="context">HTTP上下文</param>
        /// <param name="fileName">输出的文件名</param>
        public static void RenderToExcel(DataTable table, HttpContext context, string fileName, Func<DataColumn, bool> expression)
        {
            using (MemoryStream ms = RenderToExcel(table, expression))
            {
                RenderToBrowser(ms, context, fileName);
            }
        }
        private static List<String> DoubleTypes = new List<string>() { "Int32", "Decimal" };
        public static MemoryStream RenderToExcel(DataTable table, Func<DataColumn, bool> expression)
        {
            MemoryStream ms = new MemoryStream();

            using (table)
            {
                IWorkbook workbook = new HSSFWorkbook();
                ISheet sheet = workbook.CreateSheet();

                IRow headerRow = sheet.CreateRow(0);

                int ignoreMargin1 = 0;
                for (int i = 0; i < table.Columns.Count; i++)
                {
                    if (expression.Invoke(table.Columns[i]))
                        headerRow.CreateCell(i - ignoreMargin1).SetCellValue(table.Columns[i].Caption);
                    else
                        ignoreMargin1++;
                }

                int rowIndex = 1;

                foreach (DataRow row in table.Rows)
                {
                    IRow dataRow = sheet.CreateRow(rowIndex);
                    int ignoreMargin = 0;
                    for (int i = 0; i < table.Columns.Count; i++)
                    {


                        if (expression.Invoke(table.Columns[i]))
                        {
                            object oValue = row[table.Columns[i].ColumnName];
                            if (DoubleTypes.Contains(table.Columns[i].DataType.Name))
                            {
                                double value = 0;
                                double.TryParse(oValue.ToString(), out value);
                                dataRow.CreateCell(i - ignoreMargin, CellType.Numeric).SetCellValue(value);
                            }
                            else
                                dataRow.CreateCell(i - ignoreMargin).SetCellValue(oValue.ToString());
                        }
                        else
                            ignoreMargin++;
                    }
                    //foreach (DataColumn column in table.Columns)
                    //{
                    //    dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
                    //}

                    rowIndex++;
                }
                AutoSizeColumns(sheet);

                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;

            }
            return ms;
        }
        /// <summary>
        /// 自动设置Excel列宽
        /// </summary>
        /// <param name="sheet">Excel表</param>
        private static void AutoSizeColumns(ISheet sheet)
        {
            if (sheet.PhysicalNumberOfRows > 0)
            {
                IRow headerRow = sheet.GetRow(0);

                for (int i = 0, l = headerRow.LastCellNum; i < l; i++)
                {
                    sheet.AutoSizeColumn(i);
                }
            }
        }
    }
}

 二、

1、生成sheet

private ISheet createSheet(HSSFWorkbook workbook,string sheetName)
        {
            ISheet sheet1 = workbook.CreateSheet(sheetName);
            IRow rowHead = sheet1.CreateRow(0);
            IRow row = sheet1.CreateRow(0);
            row.CreateCell(0).SetCellValue("教师姓名");
            row.CreateCell(1).SetCellValue("学校");
            row.CreateCell(2).SetCellValue("年级平均分");
            row.CreateCell(3).SetCellValue("年级最高分");
            row.CreateCell(4).SetCellValue("年级最低分");
            row.CreateCell(5).SetCellValue("全市所处名次");

            sheet1.SetColumnWidth(1, 5000);
            sheet1.SetColumnWidth(2, 5000);
            sheet1.SetColumnWidth(3, 5000);
            sheet1.SetColumnWidth(4, 5000);
            sheet1.SetColumnWidth(5, 5000);

            for (var i = 0; i < 10; i++)
            {
                IRow row1 = sheet1.CreateRow(i + 1);
                row1.CreateCell(0).SetCellValue(i);
                row1.CreateCell(1).SetCellValue(i);
                row1.CreateCell(2).SetCellValue(i);
                row1.CreateCell(3).SetCellValue(i);
                row1.CreateCell(4).SetCellValue(i);
                row1.CreateCell(5).SetCellValue(i);
            }
            return sheet1;
        }

2、点击按钮

protected void Unnamed1_Click(object sender, EventArgs e)
        {
            HSSFWorkbook workbook = new HSSFWorkbook();
            ISheet sheet1 = createSheet(workbook, "李小纳");
            ISheet sheet2 = createSheet(workbook, "卢婷");
            
            System.IO.MemoryStream ms = new System.IO.MemoryStream();
            workbook.Write(ms);
            ms.Position = 0;

            // 输出Excel
            string filename = "cnblogs.rhythmk.com.导出.xls";
            var context = HttpContext.Current;
            context.Response.ContentType = "application/vnd.ms-excel";
            context.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", context.Server.UrlEncode(filename)));
            context.Response.Clear();


            MemoryStream file = new MemoryStream();
            workbook.Write(file);
            context.Response.BinaryWrite(file.GetBuffer());
            context.Response.End();
        }