JxlsUtils.java

package pwc.taxtech.atms.common.util;

import org.jxls.area.Area;
import org.jxls.builder.AreaBuilder;
import org.jxls.builder.xls.XlsCommentAreaBuilder;
import org.jxls.common.CellRef;
import org.jxls.common.Context;
import org.jxls.expression.JexlExpressionEvaluator;
import org.jxls.transform.Transformer;
import org.jxls.transform.poi.WritableCellValue;
import org.jxls.transform.poi.WritableHyperlink;
import org.jxls.util.TransformerFactory;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.core.io.ClassPathResource;
import pwc.taxtech.atms.dto.export.ExportDto;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.util.*;

/**
 * Jxls导出Excel工具
 * author kevin
 */
public class JxlsUtils {
    private static final Logger log = LoggerFactory.getLogger(JxlsUtils.class);
    public  static String sheetName = "汇总表";

    public static void toPackageOs(HttpServletResponse response , String fileName) {
        ResponseUtil.response(response, fileName, null);
    }


    /**
     * 生成模板输入流
     * @param temPath
     * @return
     * @throws Exception
     */
    public static InputStream toPackageIn(String temPath){
        try {
            return new ClassPathResource((temPath)).getInputStream();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return  null;
    }


    public static <T> void export(ExportDto exportDto){
        toPackageOs(exportDto.getResponse(), exportDto.getFileName());
        try {
            exportExcel(exportDto.getList(), exportDto.getResponse().getOutputStream(), toPackageIn(exportDto.getTemplateUrl()), exportDto.getRelation(),exportDto.getSheetNameTemp());
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    public static <T> void   exportExcel(List<T> list, ServletOutputStream os ,
                                         InputStream in, Object relation ){
        exportExcel( list,  os ,in,  relation , null);
    }
    public static <T> void   exportExcel(List<T> list, ServletOutputStream os ,
                                       InputStream in, Object relation, String sheetName1 ){
        String sn = sheetName;
        if(sheetName1 != null)
            sn = sheetName1;

        long exportExcelBegin = System.currentTimeMillis();
        log.warn("exportExcel begin: " + exportExcelBegin);
        Context context = new Context();
        context.putVar("list", list);
        if(relation != null) context.putVar("relation", relation);

        Map<String , Object> myFunction = new HashMap<String , Object>();

        myFunction.put("util", new JxlsUtils());

        Transformer trans = TransformerFactory.createTransformer(in, os);

        JexlExpressionEvaluator evaluator = (JexlExpressionEvaluator) trans.getTransformationConfig().getExpressionEvaluator();

        evaluator.getJexlEngine().setFunctions(myFunction);

        //载入模板、处理导出

        AreaBuilder areaBuilder = new XlsCommentAreaBuilder(trans);

        List<Area> areaList = areaBuilder.build();
        //"汇总表!A1"

        areaList.get(0).applyAt(new CellRef( sn + "!A1"), context);

        try {
            trans.write();
            //释放资源

            os.flush();

            os.close();

            in.close();
            long endExcel = System.currentTimeMillis();
            log.warn("exportExcel begin: " + endExcel);
        } catch (IOException e) {
            e.printStackTrace();
        }


    }

    //返回大的数

    public Integer max(Integer x , Integer y){

        return x > y ? x : y;

    }



    //给金额前面显示个货币符号

    public Object formatMoney(Object a){

        Object result = null;

        if(a != null){

            return "￥" + a;

        }

        return result;

    }



    //超链接

    public WritableCellValue myHyperlink(String address, String title) {

        return new WritableHyperlink(address, title);

    }




    private Map<String,Integer> countMap = new HashMap<>(); //用于jxls的foreach循环计数
    //为Excel添加序号
    public Integer count(String var){
        if (var == null) return null;
        if(countMap.containsKey(var)){
            Integer t = countMap.get(var);
            t += 1;
            countMap.replace(var,t);
            return t;
        }else{
            countMap.put(var,1);
        }
        return 1;
    }





}