自定义Excel表格样式导出

最新推荐文章于 2024-09-29 11:08:44 发布

qq_43141779

最新推荐文章于 2024-09-29 11:08:44 发布

阅读量296

点赞数

文章标签： java

本文链接：https://blog.csdn.net/qq_43141779/article/details/126517776

版权

本文介绍了如何使用Java来创建并导出自定义样式的Excel表格，详细步骤包括添加相关依赖、逐行绘制表格并设置Excel样式，以及在业务层调用方法进行导出。最终展示了导出效果。

摘要由CSDN通过智能技术生成

1. 添加依赖

        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.2.6</version>
        </dependency>

2. 画表（一行行画的）

包括Excel样式

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;

@Log4j2
@Configuration
public class HandleExcelUtil {

    @Resource
    HttpServletResponse response;

//GetHandelExcelReqVo 页面传参数据
    public void handleExcel(GetHandelExcelReqVo getHandelExcelReqVo) throws IOException {
        //准备报表用参数

        // Excel表开始
        String sheetname = "审批表";
        //创建一个XSSFWorkbook，对应一个Excel文件
        XSSFWorkbook wb = new XSSFWorkbook();
        //添加一个sheet,对应Excel文件中的sheet
        XSSFSheet sheet = wb.createSheet(sheetname);


        //设置宽度
        int[] width = {22, 30, 15, 30};
        for (int columnIndex = 0; columnIndex < 4; columnIndex++) {
            sheet.setColumnWidth(columnIndex, width[columnIndex] * 256);
        }
        //设置样式集合
        Map<String, XSSFCellStyle> styles = addStyle(wb);


        int colIndex;


        /**
         * 添加标题行
         *
         */
        XSSFRow row0 = sheet.createRow(0);
        row0.setHeight((short) (20 * 20));
        for (colIndex = 0; colIndex < 4; colIndex++) {
            row0.createCell(colIndex);
            row0.getCell(colIndex).setCellStyle(styles.get("title01"));
        }
        row0.getCell(0).setCellValue("个人表");
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 3));


        XSSFRow row1 = sheet.createRow(1);
        for (colIndex = 0; colIndex < 4; colIndex++) {
            row1.createCell(colIndex);
            if (colIndex % 2 == 0) {
                row1.getCell(colIndex).setCellStyle(styles.get("header_center01"));
            } else {
                row1.getCell(colIndex).setCellStyle(styles.get("header_center05"));
            }
        }
        row1.getCell(0).setCellValue("姓名");
        row1.getCell(1).setCellValue(getHandelExcelReqVo.getApplicantName());
        row1.getCell(2).setCellValue("电话");
        row1.getCell(3).setCellValue(getHandelExcelReqVo.getApplicantNumber1());
        XSSFRow row2 = sheet.createRow(2);
        for (colIndex = 0; colIndex < 4; colIndex++) {
            row2.createCell(colIndex);
            if (colIndex == 1) {
                row2.getCell(colIndex).setCellStyle(styles.get("header_center09"));
            }
            if (colIndex == 3) {
                row2.getCell(colIndex).setCellStyle(styles.get("header_center05"));
            } else {
                row2.getCell(colIndex).setCellStyle(styles.get("header_center01"));
            }
        }
        row2.getCell(0).setCellValue("证件类型");
        row2.getCell(1).setCellValue("身份证");
        row2.getCell(2).setCellValue("证件号码");
        row2.getCell(3).setCellValue(getHandelExcelReqVo.getApplicantNumber());

        /**
         * 内容
         */
        XSSFRow row3 = sheet.createRow(3);
        row3.setHeight((short) (20 * 20));
        for (colIndex = 0; colIndex < 4; colIndex++) {
            row3.createCell(colIndex);
            row3.getCell(colIndex).setCellStyle(styles.get("title02"));
        }
        row3.getCell(0).setCellValue("内容");
        sheet.addMergedRegion(new CellRangeAddress(3, 3, 0, 3));


        XSSFRow row4 = sheet.createRow(4);
        for (colIndex = 0; colIndex < 4; colIndex++) {
            row4.createCell(colIndex);
            if (colIndex % 2 == 0) {
                row4.getCell(colIndex).setCellStyle(styles.get("header_center01"));
            } else {
                row4.getCell(colIndex).setCellStyle(styles.get("header_center05"));
            }
        }
        row4.getCell(0).setCellValue("渠道");
        row4.getCell(1).setCellValue(getHandelExcelReqVo.getAcceptanceChannel());页面抓取的数据
        row4.getCell(2).setCellValue("业务号");
        row4.getCell(3).setCellValue(getHandelExcelReqVo.getBusinessNumber());

        XSSFRow row5 = sheet.createRow(5);
        for (colIndex = 0; colIndex < 4; colIndex++) {
            row5.createCell(colIndex);
            if (colIndex == 0) {
                row5.getCell(colIndex).setCellStyle(styles.get("header_center01"));