jxls的简单使用 集成uitl

excel模板的常用公式

// 划定区域
jx:area(lastCell="S3")
// 合并单元格
jx:mergeCells(lastCell="C2" cols="2")
// 循环
jx:each(items="streamList" var='str' lastCell="AS3" direction="RIGHT")
// if判断
jx:if(condition="str.code =='500' or str.code =='250'" lastCell= "AS4" areas=["C3:O3","P3:AB3"]) ( and or)

uitl方法

package cc.vace.cloud.doc.util;

import com.google.common.base.Strings;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
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.common.Size;
import org.jxls.expression.JexlExpressionEvaluator;
import org.jxls.formula.StandardFormulaProcessor;
import org.jxls.transform.Transformer;
import org.jxls.transform.poi.PoiContext;
import org.jxls.transform.poi.PoiTransformer;
import org.jxls.util.JxlsHelper;
import org.jxls.util.TransformerFactory;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.text.NumberFormat;
import java.util.*;
import java.util.stream.IntStream;

/**
 * jxls 导入导出工具类
 * @author vace
 */
public class ExcelUtil {

    /**
     * 导出表格带公式- 返回文件流
     *
     * @param map       参数
     * @param modelName 模板地址
     * @param fileName  文件名称
     * @param response  httpResponse
     */
    public static void exportTemplate(Map<String, Object> map, String modelName, String fileName, HttpServletResponse response) {
        Context context = new Context(map);
        context.getConfig().setIsFormulaProcessingRequired(false);
        response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
        response.setContentType("application/vnd.ms-excel");
        try (InputStream inputStream = new BufferedInputStream(Files.newInputStream(Paths.get(modelName)));
             OutputStream outputStream = response.getOutputStream()) {
            JxlsHelper.getInstance().processTemplate(inputStream, outputStream, context);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 导出表格带公式- 返回文件流 公式在第50 列之后
     *
     * @param map       参数
     * @param modelName 模板地址
     * @param fileName  文件名称
     * @param response  httpResponse
     */
    public static void exportTemplate(Map<String, Object> map, String modelName, String fileName, HttpServletResponse response, Integer lastColumn) {
        Context context = new Context(map);
        response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
        response.setContentType("application/vnd.ms-excel");
        try (InputStream inputStream = new BufferedInputStream(Files.newInputStream(Paths.get(modelName)));
             OutputStream outputStream = response.getOutputStream()) {
            Transformer transformer = TransformerFactory.createTransformer(inputStream, outputStream);
            ((PoiTransformer) transformer).setLastCommentedColumn(lastColumn);
            JxlsHelper.getInstance().processTemplate(context, transformer);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }


    /**
     * 生成指定文件地址
     *
     * @param map       参数
     * @param modelName 模板地址
     * @param fileName  生成文件名称路径
     */
    public static void exportTemplate(Map<String, Object> map, String modelName, String fileName) {
        Context context = new Context(map);
        try (InputStream inputStream = Files.newInputStream(Paths.get(modelName));
             OutputStream outputStream = Files.newOutputStream(Paths.get(fileName))) {
            JxlsHelper.getInstance().processTemplate(inputStream, outputStream, context);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * excel 读取文件流返回
     *
     * @param path     文件路径
     * @param fileName 文件名
     * @param response httpResponse
     */
    public static void downloadFile(String path, String fileName, HttpServletResponse response) {
        response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
        response.setContentType("application/vnd.ms-excel");
        try (FileInputStream fileOutputStream = new FileInputStream(path + fileName)) {
            OutputStream outputStream = response.getOutputStream();
            int len;
            byte[] b = new byte[1024];
            while ((len = fileOutputStream.read(b)) != -1) {
                outputStream.write(b, 0, len);
            }
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 填写异常信息
     *
     * @param workbook   工作簿
     * @param sheetIndex sheet页
     * @param colIndex   行
     * @param rowIndex   列
     * @param errMsg     错误信息
     */
    public static void writeErrorExcel(Workbook workbook, int sheetIndex, int rowIndex, int colIndex, String errMsg) {
        Cell cell = workbook.getSheetAt(sheetIndex).getRow(rowIndex).createCell(colIndex);
        CellStyle style = workbook.createCellStyle();
        if (!Strings.isNullOrEmpty(errMsg)) {
            style.setFillForegroundColor(IndexedColors.RED.getIndex());
            style.setFillBackgroundColor(IndexedColors.WHITE.getIndex());
        }
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cell.setCellStyle(style);
        cell.setCellValue(errMsg);
    }

    /**
     * 下载错误excel
     *
     * @param workbook 工作簿
     * @param fileName 文件名
     * @param response httpResponse
     */
    public static void downloadErrorExcel(Workbook workbook, String fileName, HttpServletResponse response) {
        response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
        response.setContentType("application/vnd.ms-excel");
        try (ServletOutputStream outputStream = response.getOutputStream()) {
            workbook.write(outputStream);
            outputStream.flush();
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * excel 携带公式，自动计算
     * @param cell 单元格
     * @return String
     */
    public String formulaString(Cell cell) {
        String formulaValue = "";
        if (!Objects.isNull(cell)) {
            try {
                CellType resultType = cell.getCachedFormulaResultType();
                switch (resultType) {
                    case STRING:
                        formulaValue = cell.getStringCellValue();
                        break;
                    case NUMERIC:
                        NumberFormat instance = NumberFormat.getInstance();
                        instance.setGroupingUsed(false);
                        formulaValue = String.valueOf(instance.format(cell.getNumericCellValue()));
                        break;
                    case BOOLEAN:
                        formulaValue = String.valueOf(cell.getBooleanCellValue());
                        break;
                    case ERROR:
                        formulaValue = String.valueOf(cell.getErrorCellValue());
                        break;
                    case FORMULA:
                        formulaValue = String.valueOf(cell.getCellFormula());
                        break;
                    default:
                        formulaValue = cell.getCellFormula();
                }
            } catch (Exception e) {
                formulaValue = String.valueOf(cell);
            }
        }
        return formulaValue;
    }

    /**
     * excel 单元格复制
     * @param newCell
     * @param oldCell
     */
    public void cloneCell(Cell newCell,Cell oldCell) {
        newCell.setCellComment(oldCell.getCellComment());
        newCell.setCellStyle(oldCell.getCellStyle());
        switch (oldCell.getCellType()) {
            case STRING:
                newCell.setCellValue(oldCell.getStringCellValue());
                break;
            case NUMERIC:
                //数字 时间日期，计算公式值
                newCell.setCellValue(oldCell.getNumericCellValue());
                break;
            case BOOLEAN:
                newCell.setCellValue(oldCell.getBooleanCellValue());
                break;
            case ERROR:
                newCell.setCellValue(oldCell.getErrorCellValue());
                break;
            case FORMULA:
                newCell.setCellFormula(oldCell.getCellFormula());
                break;
            case BLANK:
                // TODO newCell.setCellValue(null);
                break;
            default:
                // TODO newCell.setCellValue("");

        }
    }

    public void addColumn(Workbook workbook, int sheetNum, int rowNum, int columnNum) {
        // 获取公式 程序
        FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();

        evaluator.clearAllCachedResultValues();
        Sheet sheetAt = workbook.getSheetAt(sheetNum);
        List<CellRangeAddress> mergeList = getMergeList(sheetAt, rowNum, columnNum);

        for (int i =0; i < sheetAt.getLastRowNum(); i ++) {
            Row row = sheetAt.getRow(i);
            if (row == null) {
                continue;
            }
            short lastCellNum = row.getLastCellNum();
            for (int j = lastCellNum; j >= columnNum; j--) {
                Cell oldCell = row.getCell(j);
                Cell newCell = row.createCell(j + 1);
                if (oldCell == null) {
                    continue;
                }
                cloneCell(newCell,oldCell);
                row.createCell(j, CellType.BLANK);
            }

        }
//        sheetAt
//        CellRangeAddress region = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
        mergeList.forEach(x -> sheetAt.addMergedRegion(x));

    }

    public List<CellRangeAddress> getMergeList (Sheet sheet, int rowNum, int columnNum) {
        int regions = sheet.getNumMergedRegions();
        List<CellRangeAddress> list = new ArrayList<>();
        for (int i =0; i < regions; i ++) {
            CellRangeAddress rangeAddress = sheet.getMergedRegion(i);
            int firstColumn = rangeAddress.getFirstColumn();
            int lastColumn = rangeAddress.getLastColumn();
            int firstRow = rangeAddress.getFirstRow();
            int lastRow = rangeAddress.getLastRow();
            if (firstColumn < columnNum && lastColumn > columnNum) {
                lastColumn ++;
            }
            if (firstColumn > columnNum) {
                firstColumn ++;
                lastColumn ++;
            }
            if (firstRow < rowNum && lastRow > rowNum) {
                lastRow ++;
            }
            if (firstRow > rowNum) {
                firstRow ++;
                lastRow ++;
            }
            CellRangeAddress region = new CellRangeAddress(firstRow, lastRow, firstColumn, lastColumn);
            list.add(region);
        }
        return list;
    }
}