Offer to come, dig friends take it! I am participating in the 2022 Spring Recruitment series of activities – Experience review, click to view the details of the activity.

I. Background introduction

This is the case, before also did excel export with the most original POI method! Need to join the table header, and then fill in the data! Later found encounter complex table header, especially add style, but also merge cells, I wipe! It must be too much trouble! Later I thought I could define a template, then read the template, and finally fill the data on the line!

After having this idea, I began to ask my mother, it is true that POI also has template read, but Ali also made an EasyExcel to the native POI package, also provides template read fill, that also say what must support Ali dad!

The next step is to look at an export that I did with native POI, and then an export that reads the FTP template file! In fact, reading remote files may be common in the project, we can focus on see! Here use FTP download and upload!

Second, the original method

Dependency of use

<dependency>
  <groupId>org.apache.poi</groupId>
  <artifactId>poi</artifactId>
  <version>4.1.1</version>
</dependency>

<dependency>
  <groupId>org.apache.poi</groupId>
  <artifactId>poi-ooxml</artifactId>
  <version>4.1.1</version>
</dependency>
Copy the code

rendering

Bloated code presentation (just a quick look, not the point!) :

 public Map<String,String> expExcelByJzbg(ExcelJzbg excelJzbg){
        // Create a WorkBook instance
        HSSFWorkbook workbook = new HSSFWorkbook();
        // Get the first worksheet
        HSSFSheet sheet = workbook.createSheet("Personnel Situation Template");
        sheet.setColumnWidth(0, (int) Math.ceil((4.73 * 250)));
        sheet.setColumnWidth(1, (int) Math.ceil((5.31 * 250)));
        sheet.setColumnWidth(2, (int) Math.ceil((9.45 * 250)));
        sheet.setColumnWidth(3, (int) Math.ceil((9.45 * 250)));
        sheet.setColumnWidth(4, (int) Math.ceil((9.45 * 250)));
        sheet.setColumnWidth(5, (int) Math.ceil((9.08 * 250)));
        sheet.setColumnWidth(6, (int) Math.ceil((8.2 * 250)));
        sheet.setColumnWidth(7, (int) Math.ceil((6.38 * 250)));
        sheet.setColumnWidth(8, (int) Math.ceil((3.79 * 250)));
        sheet.setColumnWidth(9, (int) Math.ceil((4.18 * 250)));
        sheet.setColumnWidth(10, (int) Math.ceil((14.47 * 250)));
        sheet.setColumnWidth(11, (int) Math.ceil((15.63 * 250)));
        sheet.setColumnWidth(12, (int) Math.ceil((16.36 * 250)));
        sheet.setColumnWidth(13, (int) Math.ceil((21.05 * 250)));
        sheet.setColumnWidth(14, (int) Math.ceil((16.99 * 250)));
        sheet.setColumnWidth(15, (int) Math.ceil((12.09 * 250)));
        sheet.setColumnWidth(16, (int) Math.ceil((18.36 * 250)));
        sheet.setColumnWidth(17, (int) Math.ceil((7.07 * 250)));
        sheet.setColumnWidth(18, (int) Math.ceil((9.96 * 250)));
        sheet.setColumnWidth(19, (int) Math.ceil((13.28 * 250)));
        sheet.setColumnWidth(20, (int) Math.ceil((8.44 * 250)));
        // Create a column header cell style
        /* First line */
        HSSFCellStyle style = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);

        HSSFFont oneFont = workbook.createFont();
        oneFont.setFontName("Founder logo Song Simplified");
        oneFont.setFontHeightInPoints((short) 18);
        oneFont.setBold(true);
        style.setFont(oneFont);
        style.setWrapText(true);


        HSSFRow row1 = sheet.createRow(0);
        HSSFCell cell11 = row1.createCell(0);
        cell11.setCellStyle(style);
        cell11.setCellValue("Centralized Office Staff Basic Information Ledger.");
        row1.setHeight((short) (43 * 20));


        /* Second line */
        HSSFCellStyle styletwo = workbook.createCellStyle();
        styletwo.setAlignment(HorizontalAlignment.CENTER);
        styletwo.setVerticalAlignment(VerticalAlignment.CENTER);
        HSSFFont twoFont = workbook.createFont();
        twoFont.setFontName("Imitation song dynasty style typeface _GB2312");
        twoFont.setFontHeightInPoints((short) 14);
        styletwo.setFont(twoFont);
        styletwo.setAlignment(HorizontalAlignment.LEFT);
        styletwo.setWrapText(true);


        HSSFRow row2 = sheet.createRow(1);
        HSSFCell cell21 = row2.createCell(0);
        cell21.setCellStyle(styletwo);
        cell21.setCellValue("Group:" + excelJzbg.getOtherValues().get("zb"));

        HSSFCell cell22 = row2.createCell(11);
        cell22.setCellStyle(styletwo);
        cell22.setCellValue(excelJzbg.getOtherValues().get("yearMonth"));

        HSSFCell cell23 = row2.createCell(16);
        cell23.setCellStyle(styletwo);
        cell23.setCellValue("Signature of person in charge:");

        row2.setHeight((short) (28 * 20));


        /* Third line */
        HSSFCellStyle styleThree = workbook.createCellStyle();
        styleThree.setAlignment(HorizontalAlignment.CENTER);
        styleThree.setVerticalAlignment(VerticalAlignment.CENTER);
        HSSFFont threeFont = workbook.createFont();
        threeFont.setFontName("Imitation song dynasty style typeface");
        threeFont.setFontHeightInPoints((short) 12);
        styleThree.setFont(threeFont);
        styleThree.setWrapText(true);

        HSSFRow row3 = sheet.createRow(2);

        HSSFCell cell3_0 = row3.createCell(0);
        cell3_0.setCellStyle(styleThree);
        cell3_0.setCellValue("Serial number");

        HSSFCell cell3_1 = row3.createCell(1);
        cell3_1.setCellStyle(styleThree);
        cell3_1.setCellValue("Name");

        HSSFCell cell3_2 = row3.createCell(2);
        cell3_2.setCellStyle(styleThree);
        cell3_2.setCellValue(Date of Birth);

        HSSFCell cell3_3 = row3.createCell(3);
        cell3_3.setCellStyle(styleThree);
        cell3_3.setCellValue("Id Number");

        HSSFCell cell3_4 = row3.createCell(4);
        cell3_4.setCellStyle(styleThree);
        cell3_4.setCellValue("Mobile phone number");

        HSSFCell cell3_5 = row3.createCell(5);
        cell3_5.setCellStyle(styleThree);
        cell3_5.setCellValue("Job/Rank");

        HSSFCell cell3_6 = row3.createCell(6);
        cell3_6.setCellStyle(styleThree);
        cell3_6.setCellValue("Degree");

        HSSFCell cell3_7 = row3.createCell(7);
        cell3_7.setCellStyle(styleThree);
        cell3_7.setCellValue("Degree");

        HSSFCell cell3_8 = row3.createCell(8);
        cell3_8.setCellStyle(styleThree);
        cell3_8.setCellValue("Gender");

        HSSFCell cell3_9 = row3.createCell(9);
        cell3_9.setCellStyle(styleThree);
        cell3_9.setCellValue("Political status");

        HSSFCell cell3_10 = row3.createCell(10);
        cell3_10.setCellStyle(styleThree);
        cell3_10.setCellValue("From centralized office hours");

        HSSFCell cell3_11 = row3.createCell(11);
        cell3_11.setCellStyle(styleThree);
        cell3_11.setCellValue("Closed after Office hours");

        HSSFCell cell3_12 = row3.createCell(12);
        cell3_12.setCellStyle(styleThree);
        cell3_12.setCellValue("Centralized Office Post Name");

        HSSFCell cell3_13 = row3.createCell(13);
        cell3_13.setCellStyle(styleThree);
        cell3_13.setCellValue("Centralized office Dispatch Number");

        HSSFCell cell3_14 = row3.createCell(14);
        cell3_14.setCellStyle(styleThree);
        cell3_14.setCellValue("Work Unit");

        HSSFCell cell3_15 = row3.createCell(15);
        cell3_15.setCellStyle(styleThree);
        cell3_15.setCellValue("Province (city)");

        HSSFCell cell3_16 = row3.createCell(16);
        cell3_16.setCellStyle(styleThree);
        cell3_16.setCellValue("Workplace Address");

        HSSFCell cell3_17 = row3.createCell(17);
        cell3_17.setCellStyle(styleThree);
        cell3_17.setCellValue("Workplace Zip code");

        HSSFCell cell3_18 = row3.createCell(18);
        cell3_18.setCellStyle(styleThree);
        cell3_18.setCellValue("Name of Corporate Addressee");

        HSSFCell cell3_19 = row3.createCell(19);
        cell3_19.setCellStyle(styleThree);
        cell3_19.setCellValue("Unit Recipient's Mobile phone");

        HSSFCell cell3_20 = row3.createCell(20);
        cell3_20.setCellStyle(styleThree);
        cell3_20.setCellValue("Note");

        row3.setHeight((short) (63 * 20));

        / * * / data
        List<Map<String, String>> values = excelJzbg.getValues();
        int indexRow = 0;
        for (int i = 0; i < values.size(); i++) {
            Map<String,String> oneData = values.get(i);
            HSSFRow row4 = sheet.createRow(i + 3);
            HSSFCell cell4_0 = row4.createCell(0);
            cell4_0.setCellStyle(styleThree);
            cell4_0.setCellValue(i + 1);

            HSSFCell cell4_1 = row4.createCell(1);
            cell4_1.setCellStyle(styleThree);
            cell4_1.setCellValue(oneData.get("jdrymc"));

            HSSFCell cell4_2 = row4.createCell(2);
            cell4_2.setCellStyle(styleThree);
            cell4_2.setCellValue(oneData.get("csny").substring(0.7));

            HSSFCell cell4_3 = row4.createCell(3);
            cell4_3.setCellStyle(styleThree);
            cell4_3.setCellValue(oneData.get("sfzhm"));

            HSSFCell cell4_4 = row4.createCell(4);
            cell4_4.setCellStyle(styleThree);
            cell4_4.setCellValue("");

            HSSFCell cell4_5 = row4.createCell(5);
            cell4_5.setCellStyle(styleThree);
            cell4_5.setCellValue(oneData.get("zwzjdm"));

            HSSFCell cell4_6 = row4.createCell(6);
            cell4_6.setCellStyle(styleThree);
            cell4_6.setCellValue(oneData.get("xl"));

            HSSFCell cell4_7 = row4.createCell(7);
            cell4_7.setCellStyle(styleThree);
            cell4_7.setCellValue(oneData.get("xw"));

            HSSFCell cell4_8 = row4.createCell(8);
            cell4_8.setCellStyle(styleThree);
            cell4_8.setCellValue(oneData.get("xb"));

            HSSFCell cell4_9 = row4.createCell(9);
            cell4_9.setCellStyle(styleThree);
            cell4_9.setCellValue(oneData.get("zzmm"));

            HSSFCell cell4_10 = row4.createCell(10);
            cell4_10.setCellStyle(styleThree);
            cell4_10.setCellValue(oneData.get("jzbgsjq").substring(0.10));

            HSSFCell cell4_11 = row4.createCell(11);
            cell4_11.setCellStyle(styleThree);
            cell4_11.setCellValue(oneData.get("jzbgsjz").substring(0.10));

            HSSFCell cell4_12 = row4.createCell(12);
            cell4_12.setCellStyle(styleThree);
            cell4_12.setCellValue(oneData.get("jzbgfwmc"));

            HSSFCell cell4_13 = row4.createCell(13);
            cell4_13.setCellStyle(styleThree);
            cell4_13.setCellValue(oneData.get("jzbgfwwh"));

            HSSFCell cell4_14 = row4.createCell(14);
            cell4_14.setCellStyle(styleThree);
            cell4_14.setCellValue(oneData.get("gzdw"));

            HSSFCell cell4_15 = row4.createCell(15);
            cell4_15.setCellStyle(styleThree);
            cell4_15.setCellValue(oneData.get("sssf"));

            HSSFCell cell4_16 = row4.createCell(16);
            cell4_16.setCellStyle(styleThree);
            cell4_16.setCellValue(oneData.get("gzdwdz"));

            HSSFCell cell4_17 = row4.createCell(17);
            cell4_17.setCellStyle(styleThree);
            cell4_17.setCellValue(oneData.get("gzdwyb"));

            HSSFCell cell4_18 = row4.createCell(18);
            cell4_18.setCellStyle(styleThree);
            cell4_18.setCellValue(oneData.get("dwsjrxm"));

            HSSFCell cell4_19 = row4.createCell(19);
            cell4_19.setCellStyle(styleThree);
            cell4_19.setCellValue(oneData.get("dwsjrsj"));

            HSSFCell cell4_20 = row4.createCell(20);
            cell4_20.setCellStyle(styleThree);
            cell4_20.setCellValue("");

            row4.setHeight((short) (78.75 * 20));
        }

        HSSFRow row4 = sheet.createRow(values.size() + 3);
        HSSFCell cell4_0 = row4.createCell(15);
        cell4_0.setCellStyle(styleThree);
        cell4_0.setCellValue("Signature of Agent:");


        CellRangeAddress regionOne = new CellRangeAddress(0.0.0.20);
        sheet.addMergedRegion(regionOne);
        CellRangeAddress regionTwo1 = new CellRangeAddress(1.1.0.10);
        sheet.addMergedRegion(regionTwo1);
        CellRangeAddress regionTwo2 = new CellRangeAddress(1.1.11.15);
        sheet.addMergedRegion(regionTwo2);
        CellRangeAddress regionTwo3 = new CellRangeAddress(1.1.16.20);
        sheet.addMergedRegion(regionTwo3);



        // Generate a return file
        ByteArrayOutputStream os = new ByteArrayOutputStream();
        try {
            workbook.write(os);
        } catch (IOException e) {
            e.printStackTrace();
        }


        / / upload to FTP
        String expFileName = excelJzbg.getExpFileName();
        if(! expFileName.toLowerCase().endsWith(".xlsx")) expFileName += ".xlsx";
        return fileUploadService.uploadFile(null, toMultipartFile(os.toByteArray(), expFileName), WJLX, excelJzbg.getCzryDm());
    }
Copy the code

Three, upgraded version

Excel template

Depend on the package

<dependency>
  <groupId>com.alibaba</groupId>
  <artifactId>easyexcel</artifactId>
  <version>2.2.10</version>
</dependency>
Copy the code

Entity class

@Data
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class ExcelCommon {
    private String czryDm;
    /** * The template name (note that the template must be an.xlsx suffix) */
    private String templateName;
    /**
     * 导出文件名称
     */
    private String expFileName;

    / * * * * stored data must be stored type * values in the put (" type ", "01"); Put ("mapData",mapData); put("mapData",mapData); * values.put("listData",listData); * /
    private Map<String, Object> values;

}
Copy the code

Method of implementation

@Override
    public Map<String, String> expExcel(ExcelCommon excelCommon) {
        try{
            // 1. Download the document template
            String templateName = excelCommon.getTemplateName();
            if(! templateName.toLowerCase().endsWith(".xlsx")) templateName += ".xlsx";
            byte[] templateBytes = ftpUtil.downLoad(templatePath, templateName);
         
            if (templateBytes == null) {
                Map<String, String> result = new HashMap<>();
                result.put("rtnCode"."1000");
                result.put("rtnMsg"."Template acquisition failed");
                log.info("Failed to get template file [" + templateName + "]");
                return result;
            }

            // 2. Convert to input stream
            InputStream is = new ByteArrayInputStream(templateBytes);

            // 3. Define the output stream
            ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();

            // define easyExcel
            ExcelWriter excelWriter = EasyExcel.write(byteArrayOutputStream).withTemplate(is).build();
            WriteSheet writeSheet = EasyExcel.writerSheet().build();

            // 5. Fill in data according to the data type
            Map<String, Object> values = excelCommon.getValues();
            if (values.containsKey("type") = =false) {
                Map<String, String> result = new HashMap<>();
                result.put("rtnCode"."1000");
                result.put("rtnMsg"."Values does not contain key [type]");
                log.info("Failed to get template file [" + templateName + "]");
                return result;
            }


            String type = (String) values.get("type");
            if ("01".equals(type) == true){
                FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
                if (values.containsKey("mapData") = =true) excelWriter.fill(values.get("mapData"), fillConfig, writeSheet);
                if (values.containsKey("listData") = =true) excelWriter.fill(values.get("listData"), fillConfig, writeSheet);
            }else{
                if (values.containsKey("mapData") = =true) excelWriter.fill(values.get("mapData"), writeSheet);
                if (values.containsKey("listData") = =true) excelWriter.fill(values.get("listData"), writeSheet);
            }

            excelWriter.finish();

            // upload the FTP file
            String expFileName = excelCommon.getExpFileName();
            if(! expFileName.toLowerCase().endsWith(".xlsx")) expFileName += ".xlsx";
            return fileUploadService.uploadFile(null, toMultipartFile(byteArrayOutputStream.toByteArray(), expFileName), "xlsx", excelCommon.getCzryDm());
        }catch (Exception e){
            e.printStackTrace();
        }
        return null;
    }
Copy the code

Effect:

Four,

The purpose of writing this article is to let you understand the easyExcel template read method, and can be remote read export! I hope I can help you, there are some methods about FTP above, if there is a need to private message me, and then this will not post out (not very convenient) ~

Thank you for reading, I am Alson_Code, a programmer who likes to complicate simple problems and simplify complex problems! ❤

mo4tech.com (Moment For Technology) is a global community with thousands techies from across the global hang out!Passionate technologists, be it gadget freaks, tech enthusiasts, coders, technopreneurs, or CIOs, you would find them all here.

Excel export in Vernacular

I. Background introduction

Second, the original method

Three, upgraded version

Four,

Excel export in Vernacular

I. Background introduction

Second, the original method

Three, upgraded version

Four,

Related Posts

Can’t Spring answer an interview question? Don’t panic. Here are 7 tips to help you break down the difficult points

What is a daemon thread? How is it different from a user thread?

Morning ops notes | Ubuntu deployment Consul cluster