preface

This is the fourth day of my participation in the November Gwen Challenge. Check out the details: The last Gwen Challenge 2021.

Demand analysis

Export the details of the province to Excel as the data source. The header of the table should be Chinese, the width of all content should be self-adaptive, and the name of the workbook should be set as the province table, and the file name of the export should be set as the province information.

Code implementation

First, analyze what the header field contains and write the corresponding VO object:

/ * * *@description: TODO 
 *@author: HUALEI
 *@date: 2021-11-21
 *@time: do * /
@Data
public class ProvinceExcelVO implements Serializable {

    private static final long serialVersionUID = 877981781678377000L;

    /** ** province */
    private String province;

    /** ** province */
    private String abbr;

    /** * The area of the province (km²) */
    private Integer area;

    / Population of ** * province (ten thousand) */
    private BigDecimal population;

    /** ** ** ** ** ** ** ** *
    private String attraction;

    /** * The postcode of the provincial capital */
    private String postcode;

    /** * The name of the provincial capital */
    private String city;

    /** ** The provincial capital's alias */
    private String nickname;

    /** ** Climate type of provincial capitals */
    private String climate;

    /** ** ** */
    private String carcode;
}
Copy the code

Then, it is necessary to write an SQL query based on the required information. See my article [MP] for details, are you still using QueryWrapper? – Nuggets (juejin. Cn), the specific writing will not be described!

Secondly, to write the Service layer, data source is required for export. First, the detailed information of all provinces must be obtained as the data source for exporting Excel.

/** * Get all province details **@returnExport Excel data source */
List<ProvinceExcelVO> getAllProvinceDetails(a);

/** * Write the province information in Excel form to the client **@paramResponse HttpServletResponse object *@paramDataSource province details dataSource *@paramFileName fileName *@paramSheetName Sheet name * *@throwsIOException I/O flow exception */
void exportProvinceDetailsExcel(HttpServletResponse response, List<ProvinceExcelVO> dataSource, String fileName, String sheetName) throws IOException;
Copy the code

If you have a data source, you have to write it to Excel as an interface, and then the client downloads the file, so you use HttpServletResponse to set the header and body of the response.

Then, implement the method in the interface, this step is very key, export format, exception or data problems are in this place, must be considered!!

@Override
public List<ProvinceExcelVO> getAllProvinceDetails(a) {
    List<Province> provinces = this.provinceMapper.selectByAll(new Province());

    if (CollUtil.isNotEmpty(CollUtil.removeNull(provinces))) {
        return provinces.stream()
                .map(p -> {
                    ProvinceExcelVO provinceExcelVO = new ProvinceExcelVO();
                    BeanUtil.copyProperties(p, provinceExcelVO);
                    BeanUtil.copyProperties(p.getCapital(), provinceExcelVO);
                    return provinceExcelVO;
                }).collect(Collectors.toList());
    }
    return null;
}
Copy the code

Collutil.removenull (provinces) is critical! If there are empty records in the database table, NPE exception will be raised if we do not remove them.

BigWriter is selected instead of Writer because BigWriter is less likely to cause memory overflow and more secure and reliable for the output of large amounts of data. Although the number of data sources is not large, it is comfortable to use BigWriter and does not need to worry about inconsistent usage with ExcelWriter.

@Override
public void exportProvinceDetailsExcel(HttpServletResponse response, List<ProvinceExcelVO> dataSource, String fileName, String sheetName) throws IOException {
    // The XLS format is created by default. If isXlsx => true, the XLSX format is created
    // ExcelUtil.getWriter(true);
    ExcelWriter excelWriter = ExcelUtil.getBigWriter();

    // Set the table header alias
    excelWriter.addHeaderAlias("province"."Province name");
    excelWriter.addHeaderAlias("abbr"."简称");
    excelWriter.addHeaderAlias("area".Area (km²));
    excelWriter.addHeaderAlias("population"."Population number (ten thousand)");
    excelWriter.addHeaderAlias("attraction"."Famous Spots");
    excelWriter.addHeaderAlias("postcode"."Zip code");
    excelWriter.addHeaderAlias("city"."Capital city");
    excelWriter.addHeaderAlias("nickname"."Nickname");
    excelWriter.addHeaderAlias("climate"."Climate type");
    excelWriter.addHeaderAlias("carcode"."License plate"); . . }Copy the code

The ExcelWriter class provides two methods to set the table header alias. One is the above code, and the other is the way to set the key value pair. For details, see Hutool. Note, however, that the unordered HashMap entry header is also unordered, so use LinkedHashMap to preserve order.

After the header alias is set, directly write the data source into the Excel Workbook. After the data is written, it needs to respond to the client and write the file to the client. Therefore, you need to set the response body and response header, and pass the export file name into the response header. Otherwise the file name will be garbled and you will see the download box pop up. Click and you will see the list of exported data successfully.


    excelWriter.write(dataSource, true);
    
    // Response is an HttpServletResponse object
    response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet; charset=utf-8");
    
    // Chinese file name encoding
    fileName = URLEncoder.encode(fileName, CharsetUtil.UTF_8) + ".xlsx";
    response.setHeader("Content-Disposition"."attachment; filename="+ fileName);
    // Set the Sheet name
    excelWriter.renameSheet(sheetName);
    
    ServletOutputStream out = response.getOutputStream();
    excelWriter.flush(out, true);

    // Close Writer to release memory
    excelWriter.close();
    // Close the output Servlet stream
    IoUtil.close(out);
Copy the code

Note: The Workbook with data will remain in memory until the file is actually written out and the Workbook object resources are released by calling flush or close.

Finally, the control layer exposes the interface to the front end for calls:

@GetMapping("provinces/excel/export")
public void provincesExcelExport(HttpServletResponse response) throws IOException {
    // Get the data source
    List<ProvinceExcelVO> provinceExcelList = this.provinceService.getAllProvinceDetails();
    // Export the file name
    String fileName = "Export province information";
    // Sheet Sheet name
    String sheetName = "Province table";
    / / export Excel
    this.provinceService.exportProvinceDetailsExcel(response, provinceExcelList, fileName, sheetName);
}
Copy the code

Excited hearts, trembling hands, click the SEND button:

Don’t see don’t know, a look startled:

How can I solve the problem in front of me?

One line of code excelWriter. AutoSizeColumnAll (); ?? We cannot set all column boxes to automatically adjust according to the content, so we were told by Baidu to enable automatic tracking to automatically adjust the size of all columns, so we first get the current Sheet:


// Get the sheet
SXSSFSheet sheet = (SXSSFSheet) excelWriter.getSheet();
Copy the code

In this case, you need to change the Sheet to SXSSFSheet. The opening method belongs to SXSSFSheet


// Enable tracking of all columns in the worksheet for automatic resizing
sheet.trackAllColumnsForAutoSizing();

// Column width is adaptive and only takes effect when enabled
excelWriter.autoSizeColumnAll();
Copy the code

To solve the problem of insufficient adaptive width in Chinese, set the column width for each defined column:


// Get the header row as the column to which the data belongs
if (sheet.getRow(1) != null) {
    // Get the number of cells defined in the table header
    int physicalNumberOfCells = sheet.getRow(1).getPhysicalNumberOfCells();
    for (int i = 0; i < physicalNumberOfCells; i++) {
        // Set the column width for each defined column to solve the problem of insufficient adaptive width in Chinese
        sheet.setColumnWidth(i, sheet.getColumnWidth(i) * 17 / 10); }}Copy the code

The interface test

Remember, wait, wait, wait

Summary & Reflection

Although realized the expected demand, but I think it is not perfect, the code is not elegant, simple, does not have universal!!

In order to gracefully export a table from a specified data type, you must either re-encapsulate the ExcelUtil in Hutool or build the wheel on top of the POI package. Considering the amount of hair I resolutely choose the first scheme, this wave directly stand on the shoulders of giants for development.

Considering the efficiency of the export, functional expansion and ease of use, on the basis of packaging to write a custom @excel annotation, to achieve a “solution” multi-purpose, Ma Ma no longer need not worry that I will not write Excel export interface (^ – ^)V

Please refer to the detailed implementation code

Common tool classes in the Hutool toolkit (ExcelUtil…) When in doubt, go to Hutool – Digging gold (juejin. Cn)

At the end

Writing is not easy, welcome everyone to like, comment, your attention, like is my unremitting power, thank you to see here! Peace and Love.