手动绘画excel表

需要引入依赖

<dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-web</artifactId>
            <version>4.4.0</version>
        </dependency>
        <dependency>
            <groupId>cn.afterturn</groupId>
            <artifactId>easypoi-annotation</artifactId>
            <version>4.4.0</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.1.1</version>
        </dependency>

 

效果图如下:

 

/**
     * 生成sheet表
     *
     * @author DaBai
     * @date 2022/9/6 14:31
     */
    public static HSSFWorkbook createExcels() {
        // 创建HSSFWorkbook对象(excel的文档对象)
        HSSFWorkbook wb = new HSSFWorkbook();
        // 建立新的sheet对象(excel的表单)
        HSSFSheet sheet = wb.createSheet();
        //样式
        HSSFCellStyle aquaStyle = wb.createCellStyle();
        //下边框
        aquaStyle.setBorderBottom(BorderStyle.THIN);
        //左边框
        aquaStyle.setBorderLeft(BorderStyle.THIN);
        //右边框
        aquaStyle.setBorderRight(BorderStyle.THIN);
        //上边框
        aquaStyle.setBorderTop(BorderStyle.THIN);
        //设置背景颜色
        aquaStyle.setFillForegroundColor(IndexedColors.RED.getIndex());
        //水平居中
        aquaStyle.setAlignment(HorizontalAlignment.CENTER);
        //垂直居中
        aquaStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 封装表内容
        // 在sheet里创建第一行 例如:标题 0代表第一行
        HSSFRow row0 = sheet.createRow(0);
        // 设置行高像素
        row0.setHeightInPoints((float) 25);
        // 创建单元格并设置单元格内容
        HSSFCell createCell = row0.createCell(0);
        //单元格样式
        createCell.setCellStyle(aquaStyle);
        String titleValue = "2023年6月1日-2023年6月30CRM";
        //第一行 内容
        createCell.setCellValue(titleValue + "CRM登录明细");
        // 合并单元格,合并后的内容取决于合并区域的左上角单元格的值
        CellRangeAddress region = new CellRangeAddress(0, 0, 0, 30);
        sheet.addMergedRegion(region);
        //设置上下左右边框底部样式
        RegionUtil.setBorderTop(BorderStyle.THIN, region, sheet);
        RegionUtil.setBorderBottom(BorderStyle.THIN, region, sheet);
        RegionUtil.setBorderLeft(BorderStyle.THIN, region, sheet);
        RegionUtil.setBorderRight(BorderStyle.THIN, region, sheet);

        //第二行
        String[] title2 = {"", "", "", ""};
        HSSFRow row2 = sheet.createRow(1);
        for (int i = 0; i < title2.length; i++) {
            row2.setHeightInPoints((float) 25);
            HSSFCell row2Cell = row2.createCell(i);
            row2Cell.setCellStyle(aquaStyle);
            row2Cell.setCellValue(title2[i]);
        }

        //第三行
        String[] title3 = {"序号", "姓名", "企业名称", "部门"};
        HSSFRow row3 = sheet.createRow(2);
        for (int i = 0; i < title3.length; i++) {
            row3.setHeightInPoints((float) 25);
            HSSFCell row3Cell = row3.createCell(i);
            row3Cell.setCellStyle(aquaStyle);
            row3Cell.setCellValue(title3[i]);
        }
        /*HSSFRow row3 = sheet.createRow(2);
        row2.setHeightInPoints((float) 25);
        HSSFCell row3Cell0 = row3.createCell(0);
        row3Cell0.setCellStyle(aquaStyle);
        row3Cell0.setCellValue("序号");
        HSSFCell row3Cell1 = row3.createCell(1);
        row3Cell1.setCellStyle(aquaStyle);
        row3Cell1.setCellValue("公司");
        HSSFCell row3Cell2 = row3.createCell(2);
        row3Cell2.setCellStyle(aquaStyle);
        row3Cell2.setCellValue("姓名");
        HSSFCell row3Cell3 = row3.createCell(3);
        row3Cell3.setCellStyle(aquaStyle);
        row3Cell3.setCellValue("部门");*/

        // 第二行 列循环添加数据 因为前面添加了四个空的单元格,那就是从第四个单元格后面添加数据
        for(int i = 4; i < 30; i++) {
            HSSFCell cell = row2.createCell(i);
            cell.setCellStyle(aquaStyle);
            cell.setCellValue("07-" + i);
        }

        // 第三行 列循环
        for(int i = 4; i < 30 + 1; i++) {
            HSSFCell cell = row3.createCell(i);
            cell.setCellStyle(aquaStyle);
            cell.setCellValue("使用时长");
            if(i == 30) {
                cell.setCellValue("合计使用次数");
            }
        }

        //表格数据
        for(int r = 3; r < 30; r++) {
            //添加行
            HSSFRow rowR = sheet.createRow(r);
            for(int i = 0; i < 30; i++){
                //添加列
                HSSFCell cell = rowR.createCell(i);
                cell.setCellStyle(aquaStyle);
                cell.setCellValue(r + "-" + 1);
            }
        }

        // 设置宽度
        sheet.setColumnWidth((short) 0, (short) (150 * 35.7));
        sheet.setColumnWidth((short) 1, (short) (150 * 35.7));
        sheet.setColumnWidth((short) 2, (short) (150 * 35.7));
        sheet.setColumnWidth((short) 3, (short) (150 * 35.7));
        return wb;
    }

总结:创建单元格,创建行,创建列,合并单元格,给单元格设置样式、行高,为单元格添加数据

posted @ 2023-07-07 10:10  安详的苦丁茶  阅读(46)  评论(0编辑  收藏  举报