Excel导出表格工具类

前段时间公司要求做一个Excel模板样式填充数据导出，就写了一个Excel的工具类 方便以后使用

 

Excel 表格样式工具类

 

package com.ht.probation.web.excel;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;

import java.util.HashMap;
import java.util.Map;

public class ExcelUtil {

    private final HSSFWorkbook book;

    public ExcelUtil(HSSFWorkbook book) {
        super();
        this.book = book;
    }

    public ExcelUtil() {
        super();
        this.book = new HSSFWorkbook();
    }

    public static class StyleInfo {
        private HorizontalAlignment horizontalAlignment;
        private VerticalAlignment verticalAlignment;
        private short color;
        private int fontHeigth;
        private boolean bold;
        private FillPatternType fillPatternType;

        public StyleInfo(HorizontalAlignment horizontalAlignment, VerticalAlignment verticalAlignment, int fontHeigth,
                boolean bold, short color, FillPatternType fillPatternType) {
            super();
            this.horizontalAlignment = horizontalAlignment;
            this.verticalAlignment = verticalAlignment;
            this.color = color;
            this.fontHeigth = fontHeigth;
            this.bold = bold;
            this.fillPatternType = fillPatternType;
        }

        public StyleInfo(HorizontalAlignment horizontalAlignment, VerticalAlignment verticalAlignment, int fontHeigth,
                boolean bold, short color) {
            super();
            this.horizontalAlignment = horizontalAlignment;
            this.verticalAlignment = verticalAlignment;
            this.color = color;
            this.fontHeigth = fontHeigth;
            this.bold = bold;
        }

        public StyleInfo(HorizontalAlignment horizontalAlignment, VerticalAlignment verticalAlignment) {
            super();
            this.horizontalAlignment = horizontalAlignment;
            this.verticalAlignment = verticalAlignment;
        }

        public StyleInfo(short color, int fontHeigth) {
            super();
            this.color = color;
            this.fontHeigth = fontHeigth;
        }

        public HorizontalAlignment getHorizontalAlignment() {
            return horizontalAlignment;
        }

        public void setHorizontalAlignment(HorizontalAlignment horizontalAlignment) {
            this.horizontalAlignment = horizontalAlignment;
        }

        public VerticalAlignment getVerticalAlignment() {
            return verticalAlignment;
        }

        public void setVerticalAlignment(VerticalAlignment verticalAlignment) {
            this.verticalAlignment = verticalAlignment;
        }

        public short getColor() {
            return color;
        }

        public void setColor(short color) {
            this.color = color;
        }

        public int getFontHeigth() {
            return fontHeigth;
        }

        public void setFontHeigth(int fontHeigth) {
            this.fontHeigth = fontHeigth;
        }

        public boolean isBold() {
            return bold;
        }

        public void setBold(boolean bold) {
            this.bold = bold;
        }

        public FillPatternType getFillPatternType() {
            return fillPatternType;
        }

        public void setFillPatternType(FillPatternType fillPatternType) {
            this.fillPatternType = fillPatternType;
        }
    }

    public CellStyle createCellStyle(StyleInfo styleInfo) {
        HSSFCellStyle cellStyle = book.createCellStyle();
        if (styleInfo.getFontHeigth() > 0) {
            HSSFFont font = book.createFont();
            font.setFontHeight((short) styleInfo.getFontHeigth());
            font.setBold(styleInfo.isBold());
            cellStyle.setFont(font);
        }
        if (styleInfo.getHorizontalAlignment() != null) {
            cellStyle.setAlignment(styleInfo.getHorizontalAlignment());
        }
        if (styleInfo.getVerticalAlignment() != null) {
            cellStyle.setVerticalAlignment(styleInfo.getVerticalAlignment());
        }
        if (styleInfo.getColor() > 0) {
            cellStyle.setFillForegroundColor(styleInfo.getColor());
            if (styleInfo.getFillPatternType() != null) {
                cellStyle.setFillPattern(styleInfo.getFillPatternType());
            } else {
                cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            }
        }
        return cellStyle;
    }

    private final Map<String, CellStyle> styleMap = new HashMap<String, CellStyle>();

    public CellStyle createCellStyle(String name, StyleInfo styleInfo) {
        CellStyle cellStyle = createCellStyle(styleInfo);
        styleMap.put(name, cellStyle);
        return cellStyle;
    }

    public CellStyle getCellStyle(String name) {
        return styleMap.get(name);
    }

    public HSSFWorkbook getBook() {
        return book;
    }

    public SheetUtil createSheet(String sheetname) {
        return new SheetUtil(book, sheetname);
    }

}

 

Excel表格操作工具类（合并 插入 拆分等）

 

package com.ht.probation.web.excel;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.util.CellRangeAddress;

public class SheetUtil {

    private final HSSFSheet sheet;

    public SheetUtil(HSSFSheet sheet) {
        super();
        this.sheet = sheet;
    }

    public SheetUtil(HSSFWorkbook book, String sheetname) {
        super();
        this.sheet = book.createSheet(sheetname);
    }

    public void addCell(int r, int c, String content) {
        addCell(r, c, content, null);
    }

    public void addCells(int r, String... contents) {
        for (int i = 0; i < contents.length; i++) {
            addCell(r, i, contents[i]);
        }
    }

    public void addCells(int r, CellStyle style, String... contents) {
        for (int i = 0; i < contents.length; i++) {
            addCell(r, i, contents[i], style);
        }
    }

    public void addCell(int r, int c, String content, CellStyle style) {
        HSSFCell cell = getCell(r, c);
        if (style != null) {
            cell.setCellStyle(style);
        }
        cell.setCellValue(content);
    }

    public HSSFCell getCell(int rowIndex, int cellIndex) {
        HSSFRow row = getRow(rowIndex);
        HSSFCell cell = row.getCell(cellIndex);
        if (cell == null) {
            cell = row.createCell(cellIndex);
        }
        return cell;
    }

    public HSSFRow getRow(int rowIndex) {
        HSSFRow row = sheet.getRow(rowIndex);
        if (row == null) {
            row = sheet.createRow(rowIndex);
        }
        return row;
    }

    public void mergeColumn(int rs, int re, int cs, int ce) {
        sheet.addMergedRegion(new CellRangeAddress(rs, re, cs, ce));
    }

    public void setColumnWidth(int c, int w) {
        sheet.setColumnWidth(c, w * 50);
    }

    public void setColumnWidth(int... w) {
        for (int i = 0; i < w.length; i++) {
            setColumnWidth(i, w[i]);
        }
    }

    public HSSFSheet getSheet() {
        return sheet;
    }
}

 

项目模板导出代码示例

package com.ht.probation.web.excel;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.ht.probation.mapper.StageMapper;
import com.ht.probation.mapper.SumEvaluateMapper;
import com.ht.probation.mapper.UserMapper;
import com.ht.probation.mapper.UserProjectMapper;
import com.ht.probation.mapper.base.entity.SumEvaluate;
import com.ht.probation.mapper.base.entity.User;
import com.ht.probation.mapper.base.entity.UserProject;
import com.ht.probation.service.intf.EvaluateService;
import com.ht.probation.service.intf.InquiryService;
import com.ht.probation.service.intf.bean.InquiryInfo;
import com.ht.probation.service.intf.bean.InquiryStageInfo;
import com.ht.probation.web.bean.ContextBean;
import com.ht.probation.web.bean.EvaBean;
import com.ht.probation.web.bean.StageBean;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.VerticalAlignment;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;

import java.io.File;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;

@Service
public class RoleDaoTest2 {

    @Autowired
    private StageMapper stageMapper;

    @Autowired
    private InquiryService inquiryService;

    @Autowired
    private EvaluateService evaluateService;

    @Autowired
    private UserMapper userMapper;

    @Autowired
    private UserProjectMapper  userProjectMapper;
    @Autowired
    private SumEvaluateMapper sumEvaluateMapper;


    public void testDao() {
        System.out.println(stageMapper.selectStagesByType(1));
    }


    public void exportExcel(String userCode) throws Exception {
        ExcelUtil excel = new ExcelUtil();
        QueryWrapper<User> userQueryWrapper = new QueryWrapper<User>()
                .eq(!StringUtils.isEmpty(userCode), "code", userCode);
        User user = userMapper.selectOne(userQueryWrapper);
        QueryWrapper<UserProject> userProjectQueryWrapper = new QueryWrapper<UserProject>()
                .eq("project_name",user.getProjectName())
                .eq("role_id",2);
        UserProject userProject = userProjectMapper.selectOne(userProjectQueryWrapper);
        QueryWrapper<SumEvaluate> sumEvaluateQueryWrapper = new QueryWrapper<SumEvaluate>()
                .eq(!StringUtils.isEmpty(userCode),"evaluate_user_code",userCode);
        SumEvaluate sumEvaluate = sumEvaluateMapper.selectOne(sumEvaluateQueryWrapper);

        SheetUtil sheet = excel.createSheet("新员工调研表");

        int row = 0;
        sheet.addCell(0, 4, "回答");
        sheet.addCell(0, 5, "打分");
        row++;

        sheet.addCells(row, "员工编号", "员工姓名", "项目经理/导师名字", "", "是/否", "1-5分");

        sheet.setColumnWidth(50, 50, 100, 400, 50, 50);

        int maxLvl = inquiryService.getMaxLvlByUser(userCode);

        for (int i = 0; i < maxLvl; i++) {
            InquiryStageInfo stages = inquiryService.queryStageInfo(userCode, String.valueOf(i + 1));
            sheet.addCell(row, 3, stages.getStageName());

            row++;

            for (int j = 0; j < stages.getInquiryList().size(); j++) {
                InquiryInfo il = stages.getInquiryList().get(j);
                sheet.addCells(row, il.getUserCode(), il.getUserName(), il.getName(), il.getQuestion(), il.getFlag(),
                        il.getScore());
                row++;
            }

            row++;
        }

        excel.createCellStyle("title",
                new ExcelUtil.StyleInfo(HorizontalAlignment.CENTER, VerticalAlignment.CENTER, 240, true, (short) 0));
        excel.createCellStyle("head",
                new ExcelUtil.StyleInfo(HorizontalAlignment.CENTER, VerticalAlignment.CENTER, 180, true, (short) 0));
        excel.createCellStyle("sign",
                new ExcelUtil.StyleInfo(HorizontalAlignment.RIGHT, VerticalAlignment.CENTER, 180, true, (short) 0));
        excel.createCellStyle("headBack", new ExcelUtil.StyleInfo(HorizontalAlignment.CENTER, VerticalAlignment.CENTER, 180, true,
                IndexedColors.GREY_25_PERCENT.getIndex()));
        excel.createCellStyle("cell", new ExcelUtil.StyleInfo(HorizontalAlignment.LEFT, VerticalAlignment.CENTER));

        SheetUtil sheet1 = excel.createSheet("转正评估表");

        sheet1.addCell(0, 0, "新员工试用期转正评估表", excel.getCellStyle("title"));
        sheet1.mergeColumn(0, 0, 0, 9);

        ContextBean contextBean = (ContextBean) evaluateService.queryEvainfo(userCode).getResult();
        sheet1.addCell(1, 0, "试用期考核指标：");
        sheet1.addCell(1, 8, "员工编号：");
        sheet1.addCell(1, 9, userCode);

        sheet1.addCell(2, 8, "员工姓名：");
        sheet1.addCell(2, 9, contextBean.getEuserBean().getEvaluateUserName());

        sheet1.addCell(3, 8, "部门：");
        sheet1.addCell(3, 9, user.getBranchName2());
        sheet1.addCell(4, 8, "职位：");
        sheet1.addCell(4, 9, user.getRoleId() == 1 ? "员工" : "项目经理");
        sheet1.addCell(5, 8, "预计转正时间：");
        sheet1.addCell(5, 9, subMonth(user.getEntrytime()));
        sheet1.addCell(6, 8, "项目经理/导师姓名：");
        sheet1.addCell(6, 9,userProject.getUserName());

        sheet1.addCell(7, 0, " 工作计划目标", excel.getCellStyle("head"));
        sheet1.addCell(7, 5, "工作达成情况", excel.getCellStyle("head"));
        sheet1.mergeColumn(7, 7, 0, 4);
        sheet1.mergeColumn(7, 7, 5, 9);

        sheet1.addCells(8, excel.getCellStyle("headBack"), "沟通阶段", "试用期目标工作项目", "", "预期完成内容", "权重", "自我评价", "上级评价");
        sheet1.addCells(9, excel.getCellStyle("headBack"), "", "", "", "", "", "实际完成结果自述", "总体评价", "得分范围", "上级评分",
                "权重分数");

        sheet1.mergeColumn(8, 9, 0, 0);
        sheet1.mergeColumn(8, 9, 1, 2);
        sheet1.mergeColumn(8, 9, 3, 3);
        sheet1.mergeColumn(8, 9, 4, 4);

        sheet1.mergeColumn(8, 8, 6, 9);

        row = 10;

        for (StageBean stage : contextBean.getStageBeans()) {
            for (EvaBean ev : stage.getEvaBeans()) {
                sheet1.addCells(row, excel.getCellStyle("cell"), stage.getStageName(), ev.getWorkId(),
                        ev.getProjectName(), ev.getWorkCode(), ev.getProportion() + "%", ev.getSelf(),
                        ev.getEvaluation(), ev.getRanges(),
                        ev.getLevScore() == null ? "" : String.valueOf(ev.getLevScore()),
                        ev.getScore() == null ? "" : String.valueOf(ev.getScore()));
                row++;
            }

            if (stage.getEvaBeans().size() > 1) {
                sheet1.mergeColumn(row - stage.getEvaBeans().size(), row - 1, 0, 0);
            }
        }

        sheet1.addCell(row, 0, "权重合计 (注：权重之和应等于100%）", excel.getCellStyle("head"));
        sheet1.addCell(row, 4, "100%", excel.getCellStyle("head"));
        sheet1.addCell(row, 6, "考核评价总得分", excel.getCellStyle("head"));
        sheet1.addCell(row, 9, String.valueOf(sumEvaluate.getSumScore()), excel.getCellStyle("head"));

        sheet1.mergeColumn(row, row, 0, 3);
        sheet1.mergeColumn(row, row, 6, 8);

        row++;
        sheet1.addCell(row, 0, "指导人/直属领导"+ "\r\n"+ "评价和建议:", excel.getCellStyle("head"));
        HSSFRow row1= sheet1.getRow(16);
        row1.setHeight((short) 800);

        sheet1.mergeColumn(row, row, 0, 2);
        sheet1.mergeColumn(row, row, 3, 9);
        sheet1.addCell(row, 3,sumEvaluate.getSumEvaluation(), excel.getCellStyle("cell"));

        row++;
        sheet1.addCell(row, 0, "考核结果确认签字栏", excel.getCellStyle("head"));

        sheet1.mergeColumn(row, row, 0, 1);
        sheet1.mergeColumn(row, row, 3, 9);
        row++;

        sheet1.mergeColumn(row, row, 0, 9);

        row++;

        sheet1.addCell(row, 0, "被考核者签字：", excel.getCellStyle("sign"));
        sheet1.addCell(row, 2, "日期：", excel.getCellStyle("sign"));
        sheet1.addCell(row, 4, "评价者签字：", excel.getCellStyle("sign"));
        sheet1.addCell(row, 6, "日期：", excel.getCellStyle("sign"));

        sheet1.mergeColumn(row, row, 7, 9);

        sheet1.setColumnWidth(100, 50, 150, 150, 50, 150, 50, 50, 100, 50);

        excel.getBook().write(new File("test.xlsx"));
        excel.getBook().close();

    }

    /****
     * 传入入职日期 ，返回具体日期增加三个月。
     * @param date 日期
     * @return
     * @throws ParseException
     */
    private static String subMonth(String date) throws ParseException {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        Date dt = sdf.parse(date);
        Calendar rightNow = Calendar.getInstance();
        rightNow.setTime(dt);
        rightNow.add(Calendar.MONTH, 3);
        Date dt1 = rightNow.getTime();
        String reStr = sdf.format(dt1);
        return reStr;


    }
}