.Net NPOI 根据excel模板导出excel

一、根据excel模板导出excel

1、导入npoi.dll 

2、dal中添加类exportexcel.cs

using npoi.ss.usermodel;
using system;
using system.collections.generic;
using system.io;
using system.linq;
using system.text;
using system.threading.tasks;
using system.web;
namespace dal.assessment
{
    public class exportexcel
    {
        npoiexcel excel;

        //构造 采用模板方式
        public exportexcel(string temppath)
        {
            excel = new npoiexcel(temppath);
        }

        /// <summary>
        /// 流的形式
        /// </summary>
        /// <returns></returns>
        private memorystream writetostream()
        {
            return excel.save();
        }

        /// <summary>
        /// ppk 导出
        /// </summary>
        /// <param name="list"></param>
        /// <param name="filename"></param>
        public void ppktoexcel(string productinfo,string avgweightstr, string divweightstr, int[][] dataarray, string filename)
        {
            httpcontext.current.response.contenttype = "application/vnd.ms-excel";
            httpcontext.current.response.addheader("content-disposition", string.format("attachment;filename={0}", httputility.urlencode(filename)));
            httpcontext.current.response.contentencoding = system.text.encoding.getencoding("utf-8");
            httpcontext.current.response.clear();
            isheet sheet;
            sheet = excel.activesheet;
            sheet.forceformularecalculation = true;   //允许excel里的公式生效

            icellstyle style = excel.createcellstyle();
            style.borderbottom = borderstyle.thin;
            style.borderleft = borderstyle.thin;
            style.borderright = borderstyle.thin;
            style.bordertop = borderstyle.thin;
            style.wraptext = true;
            style.verticalalignment = verticalalignment.center;
            ///////////////
            icellstyle stylegray = excel.createcellstyle();
            stylegray.fillforegroundcolor = npoi.hssf.util.hssfcolor.grey_40_percent.index;
            stylegray.fillpattern = fillpatterntype.solid_foreground;//设置背景是否填充
            stylegray.fillbackgroundcolor = npoi.hssf.util.hssfcolor.grey_40_percent.index;
            stylegray.verticalalignment = verticalalignment.center;

           
            excel.setvalue(3, 16, productinfo);
            excel.setvalue(3, 25, convert.todouble(avgweightstr));
            excel.setvalue(3, 28, convert.todouble(divweightstr));
            excel.setvalue(4, 26, datetime.now);

            int rowindex = 13;
            for (int i = 0; i < 6; i++)
            {
                int colindex = 2;
                for (int j = 0; j < dataarray[i].length; j++)
                {
                    excel.setvalue(rowindex, colindex, convert.todouble(dataarray[i][j]) / 100);  //给excel里格子赋值
          　　　　　　//excel.setstyle(rowindex, colindex, stylegray); 设置格式
                    colindex++;
                }
                rowindex++;
            }
            byte[] fs;
            fs = writetostream().toarray();
            httpcontext.current.response.binarywrite(writetostream().getbuffer());
            httpcontext.current.response.end();
        }


    }
}
3、使用
前台调用 传入参数，获取数据

[httppost]
public string ppkexport(string id)
{
var productinfo = "";

var avgweightstr = "";

var divweightstr = "";
int[][] dataarray = new int[8][];

string templatefile = "~/exceltemp/ppktemplate.xls";      //模板位置
 ex = new exportexcel(server.mappath(templatefile));
 string filename = "xbar-r控制图.xls";                      //导出的excel命名
 ex.ppktoexcel(productinfo,avgweightstr, divweightstr, dataarray, filename);     //传入参数
  return filename;
｝

 4、效果

界面导出按钮

点击导出按钮，选择位置保存即可。