制作execl表格心得（二）

@RequestMapping("/exportExecl")
    public void importExecl(String billNo, HttpServletRequest request, HttpServletResponse response) throws Exception {
        InputStream imageInputStream = Thread.currentThread().getContextClassLoader().getResourceAsStream("pay.jpg");
        byte[] imageBytes = Util.toByteArray(imageInputStream);
        SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
        Map map=cmSaleMianBillService.findSaleBillDetailAny(billNo);
        Double wips = Double.parseDouble(map.get("wips").toString());
        List<SaleBillExecle> list=  cmSaleMianBillService.findSaleBillDetailExecl(billNo);
        Double d=Double.valueOf(map.get("balance").toString());
        // Double d=Double.valueOf(map.get("balance").toString())-(Double.valueOf(map.get("creditLine").toString())-Double.valueOf(map.get("creditLineBalance").toString()));
        Map<String , Object> model=new HashMap<String , Object>();
        model.put("list", list);
        model.put("billDate",formatter.format(map.get("billDate")));
        model.put("payMoney",map.get("payMoney"));
        Double cb = Double.valueOf(map.get("creditLine").toString())-Double.valueOf(map.get("creditLineBalance").toString());
        if(d==0d){
            model.put("balance",-cb);
        }else{
            model.put("balance",d);
        }

        model.put("allQty",list.get(0).getAllQty());
        model.put("allMoney",list.get(0).getAllMoney());
        model.put("img",imageBytes);
       
//        if(cb ==0){
//            model.put("surMoney",list.get(0).getAllMoney() - Double.parseDouble(map.get("payMoney").toString()));
//        }else{
//            model.put("surMoney",Double.valueOf(list.get(0).getAllMoney())-Double.valueOf(map.get("payMoney").toString())+d);
//        }
//        String path="E:\\";
//        String url="E:\\";
        String path = CacheManager2.getConfigArgByKey("imagePath");
        String url = CacheManager2.getConfigArgByKey("imageUrl");
        Double sur= list.get(0).getAllMoney() - Double.parseDouble(map.get("payMoney").toString())-wips;
        if(d>=(sur+cb)) {
           model.put("surMoney",0);
        }else {
           model.put("surMoney",(sur+cb)-d);
        }
        File temp=new File(path+"/saleBillDetail.xlsx");
        if(!temp.exists()) {
           InputStream reportInputStream = Thread.currentThread().getContextClassLoader().getResourceAsStream("saleBillDetail.xlsx");
            OutputStream outputStream = new FileOutputStream(path+"/saleBillDetail.xlsx");
            int bytesWritten = 0;
            int byteCount = 0;
            byte[] bytes = new byte[1024];
            while ((byteCount = reportInputStream.read(bytes)) != -1)
            {
                outputStream.write(bytes, bytesWritten, byteCount);
                outputStream.flush();
            }
            reportInputStream.close();
            outputStream.close();
        }

        File file=new File(path+"/xls");
        if(!file.exists()){//目录不存在就创建
            file.mkdirs();
        }
        String filName= DateUtil.getCurrentTimeStr(DateUtil.FORMAT_YYYYMMDDHHMMSSSSS);
        String save= path+"/xls/"+ filName+".xlsx";
        OutputStream os = new FileOutputStream(save);
        JxlsUtilskey.exportExcel(path+"/saleBillDetail.xlsx", os, model);
        os.close();
        
      //1.读取Excel文档对象
        XSSFWorkbook hssfWorkbook = new XSSFWorkbook(new FileInputStream(save));
        //2.获取要解析的表格（第一个表格）
        XSSFSheet sheet = hssfWorkbook.getSheetAt(0);
        CellRangeAddress region = new CellRangeAddress(2, // first row
                list.size(), // last row
                14, // first column
                 15// last column
        );
        sheet.addMergedRegion(region);
        hssfWorkbook.write(new FileOutputStream(path+"/xls/"+ filName+".xlsx"));
        hssfWorkbook.close();
        // 取得文件名。
        // 以流的形式下载文件。
        InputStream fis = new BufferedInputStream(new FileInputStream(path+"/xls/"+ filName+".xlsx"));
        byte[] buffer = new byte[fis.available()];
        fis.read(buffer);
        fis.close();
        // 清空response
        response.reset();
        // 设置response的Header
        response.setContentType("application/msexcel;charset=UTF-8");
        response.addHeader("Content-Disposition",
                "attachment;filename=" + new String((filName + "-out.xlsx").getBytes("GBK"), "ISO8859_1"));
        response.addHeader("fileName", new String((filName + "-out.xlsx")));
        response.setHeader("Access-control-Allow-Origin", request.getHeader("Origin"));
        response.setHeader("Access-Control-Allow-Methods", "POST, GET, OPTIONS, DELETE");
        response.setHeader("Access-Control-Max-Age", "3600");
        response.setHeader("Access-Control-Allow-Headers", request.getHeader("Access-Control-Request-Headers"));
        response.setHeader("Access-Control-Allow-Credentials", "true");
        response.setHeader("Access-Control-Expose-Headers", "x-requested-with, Content-Type, Content-Disposition, fileName"); // or *

        OutputStream toClient = new BufferedOutputStream(
                response.getOutputStream());
        toClient.write(buffer);
        toClient.flush();
        toClient.close();


    }

 

public class JxlsUtilskey {
    static{
        //添加自定义指令（可覆盖jxls原指令）
        //合并单元格(模板已经做过合并单元格操作的单元格无法再次合并)
        XlsCommentAreaBuilder.addCommandMapping("merge", MergeCommand.class);
    }

    public static void exportExcel(InputStream is, OutputStream os, Map<String, Object>
            model) throws IOException{
        Context context = PoiTransformer.createInitialContext();
        if (model != null) {
            for (Map.Entry<String, Object> entry : model.entrySet()){
                context.putVar(entry.getKey(), entry.getValue());
            }
        }
        JxlsHelper jxlsHelper = JxlsHelper.getInstance();
        Transformer transformer  = jxlsHelper.createTransformer(is, os);
        //获得配置
        JexlExpressionEvaluator evaluator = (JexlExpressionEvaluator)transformer.getTransformationConfig().getExpressionEvaluator();
        //设置静默模式，不报警告
        evaluator.getJexlEngine().setSilent(true);
        //函数强制，自定义功能
        Map<String, Object> funcs = new HashMap<String, Object>();
        funcs.put("jx", new JxlsUtils());    //添加自定义功能
        evaluator.getJexlEngine().setFunctions(funcs);
        //必须要这个，否者表格函数统计会错乱
        jxlsHelper.setUseFastFormulaProcessor(false).processTemplate(context, transformer);
    }

    public static void exportExcel(File xls, File out, Map<String, Object> model) throws FileNotFoundException, IOException {
        exportExcel(new FileInputStream(xls), new FileOutputStream(out), model);
    }

    public static void exportExcel(String templatePath, OutputStream os, Map<String, Object> model) throws Exception {
        File template = getTemplate(templatePath);
        if(template != null){
            exportExcel(new FileInputStream(template), os, model);
        } else {
            throw new Exception("Excel 模板未找到。");
        }
    }

    //获取jxls模版文件
    public static File getTemplate(String path){
        File template = new File(path);
        if(template.exists()){
            return template;
        }
        return null;
    }

    // 日期格式化
    public String dateFmt(Date date, String fmt) {
        if (date == null) {
            return "";
        }
        try {
            SimpleDateFormat dateFmt = new SimpleDateFormat(fmt);
            return dateFmt.format(date);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return "";
    }

    // if判断
    public Object ifelse(boolean b, Object o1, Object o2) {
        return b ? o1 : o2;
    }

 

 

public class MergeCommand extends AbstractCommand {
    private String cols;        //合并的列数
    private String rows;        //合并的行数
    private String minCols;     //最小合并的列数
    private String minRows;     //最小合并的行数
    private CellStyle cellStyle;//第一个单元格的样式

    private Area area;

    @Override
    public String getName() {
        return "merge";
    }

    @Override
    public Command addArea(Area area) {
        if (super.getAreaList().size() >= 1) {
            throw new IllegalArgumentException("You can add only a single area to 'merge' command");
        }
        this.area = area;
        return super.addArea(area);
    }

    @Override
    public Size applyAt(CellRef cellRef, Context context) {
        int rows = area.getSize().getHeight();
        int cols = area.getSize().getWidth();
        rows = Math.max(getVal(this.rows, context), rows);
        cols = Math.max(getVal(this.cols, context), cols);
        rows = Math.max(getVal(this.minRows, context), rows);
        cols = Math.max(getVal(this.minCols, context), cols);
        if(rows > 1 || cols > 1){
            Transformer transformer = this.getTransformer();
            if(transformer instanceof PoiTransformer){
                poiMerge(cellRef, context, (PoiTransformer)transformer, rows, cols);
            }else if(transformer instanceof JexcelTransformer){
                jexcelMerge(cellRef, context, (JexcelTransformer)transformer, rows, cols);
            }
        }
        area.applyAt(cellRef, context);
        return new Size(cols, rows);
    }

    protected Size poiMerge(CellRef cellRef, Context context, PoiTransformer transformer, int rows, int cols){
        Sheet sheet = transformer.getWorkbook().getSheet(cellRef.getSheetName());
        CellRangeAddress region = new CellRangeAddress(
                cellRef.getRow(),
                cellRef.getRow() + rows - 1,
                cellRef.getCol(),
                cellRef.getCol() + cols - 1);
        sheet.addMergedRegion(region);

        //合并之后单元格样式会丢失，以下操作将合并后的单元格恢复成合并前第一个单元格的样式
        area.applyAt(cellRef, context);
        if(cellStyle == null){
            PoiCellData cellData = (PoiCellData)transformer.getCellData(area.getStartCellRef());
            if(cellData != null){
                cellStyle = cellData.getCellStyle();
            }
        }
        setRegionStyle(cellStyle, region, sheet);
        return new Size(cols, rows);
    }

    protected Size jexcelMerge(CellRef cellRef, Context context, JexcelTransformer transformer, int rows, int cols){
        try {
            transformer.getWritableWorkbook().getSheet(cellRef.getSheetName())
                    .mergeCells(
                            cellRef.getRow(),
                            cellRef.getCol(),
                            cellRef.getRow() + rows - 1 ,
                            cellRef.getCol() + cols - 1);
            area.applyAt(cellRef, context);
        } catch (WriteException e) {
            throw new IllegalArgumentException("合并单元格失败");
        }
        return new Size(cols, rows);
    }

    private static void setRegionStyle(CellStyle cs, CellRangeAddress region, Sheet sheet) {
        for (int i = region.getFirstRow(); i <= region.getLastRow(); i++) {
            Row row = sheet.getRow(i);
            if (row == null) {
                row = sheet.createRow(i);
            }for (int j = region.getFirstColumn(); j <= region.getLastColumn(); j++) {
                Cell cell = row.getCell(j);
                if (cell == null) {
                    cell = row.createCell(j);
                }
                if (cs == null){
                    cell.getCellStyle().setAlignment(CellStyle.ALIGN_CENTER);
                    cell.getCellStyle().setVerticalAlignment(CellStyle.VERTICAL_CENTER);
                }else {
                    cell.setCellStyle(cs);
                }
            }
        }
    }

    private int getVal(String expression, Context context){
        if(StringUtils.isNotBlank(expression)){
            Object obj = getTransformationConfig().getExpressionEvaluator().evaluate(expression, context.toMap());
            try {
                return Integer.parseInt(obj.toString());
            } catch (NumberFormatException e) {
                throw new IllegalArgumentException("表达式：" + expression + " 解析失败");
            }
        }
        return 0;
    }

    public String getCols() {
        return cols;
    }

    public void setCols(String cols) {
        this.cols = cols;
    }

    public String getRows() {
        return rows;
    }

    public void setRows(String rows) {
        this.rows = rows;
    }

    public String getMinCols() {
        return minCols;
    }

    public void setMinCols(String minCols) {
        this.minCols = minCols;
    }

    public String getMinRows() {
        return minRows;
    }

    public void setMinRows(String minRows) {
        this.minRows = minRows;
    }