to avoid detours! Thoroughly learn the practical application of spring source code

Introduction

I believe that everyone can often encounter the export of various excels on projects. Simple excel formats can be exported using simple poi, easyExcel and other tools. However, for complex Excel, there are various requirements such as fixed styles, merged cells, dynamic columns, etc., resulting in Excel export requiring a lot of effort to write code. jxls solves the above problems to a large extent.

Here is a brief introduction to jxls. JXLS is a simple, lightweight excel export library abroad. Link: JXLS official website. Here is a detailed documentation tutorial (English version). For your convenience, I will give some common excel examples. Template configuration is convenient for everyone to use.

  • jxls.sourceforge.net/

Introduce maven dependencies

<!-- Please see the official website Release for version details, here we use 2.11.0 -->
<dependency>
    <groupId>org.jxls</groupId>
    <artifactId>jxls</artifactId>
    <version>2.11.0</version>
</dependency>
<dependency>
    <groupId>org.jxls</groupId>
    <artifactId>jxls-poi</artifactId>
    <version>2.11.0</version>
</dependency>
 
<!-- To use the converter implementation based on JavaExcelAPI, please add the following dependencies -->
<dependency>
    <groupId>org.jxls</groupId>
    <artifactId>jxls-jexcel</artifactId>
    <version>${jxlsJexcelVersion}</version>
</dependency>

Background code

Tool class: JxlsUtils, export static methods

public static void exportExcel(InputStream is, OutputStream os, Map<String, Object> model) throws IOException {
  Context context = new Context();
  if (model != null) {
   for (String key : model.keySet()) {
    context.putVar(key, model.get(key));
   }
  }
  JxlsHelper jxlsHelper = JxlsHelper.getInstance();
  Transformer transformer = jxlsHelper.createTransformer(is, os);
  JexlExpressionEvaluator evaluator = (JexlExpressionEvaluator) transformer.getTransformationConfig()
    .getExpressionEvaluator();
  Map<String, Object> funcs = new HashMap<String, Object>();
  funcs.put("utils", new JxlsUtils()); // Add custom functions
  evaluator.setJexlEngine(new JexlBuilder().namespaces(funcs).create());
  jxlsHelper.processTemplate(context, transformer);
 }

Export controller

//Export sample Controller
@PostMapping("/export/exportTradeCreditData")
@ResponseBody
public void exportTradeCreditData(HttpServletRequest request, HttpServletResponse response, Date countDate) {
  String templatePath = "template/excel/trade_credit_data.xlsx";
        //Find the template file path. The PathTools class here is the system's internal encapsulation class. Please pay attention to the copy.
  URL templateResource = PathTools.findResource(templatePath);
  try (OutputStream out = response.getOutputStream();
    InputStream templateStream = templateResource.openStream();) {
            //Business data query
   List<CindaTradeCreditDto> list = countingReportService.queryTradeCreditData(countDate);
 
            //In excel template, data assembly
   Map<String, Object> map = new HashMap<String, Object>();
   map.put("year", getYear(countDate));
   map.put("contracts", list);
   JxlsUtils.exportExcel(templateStream, out, map);
   out.close();
  } catch (Exception e) {
   e.printStackTrace();
   log.error("Export excel exception, {}", JxlsUtils.executeException(e));
  }
 }

Create template

?

Note: The excel template worksheet must use xlsx format, do not use xls format, to prevent data conversion errors during export

?

  • Create a new excel template in xlsx format
  • Set the table style in the worksheet according to the export requirements
  • Limited to simple table headers and row and column widths and heights
  • Write an expression, right-click on the worksheet to insert a comment, and add the comment shortcut key (Shit + F2) in the office
  • Set areas, data rows, merged cells, dynamic columns, etc.

XLS Expression

A brief list of several commonly used expressions

jx:area

jx:area(lastCell = "H3")

XLS Area is an important concept in JxlsPlus. It indicates the rectangular area in the excel template that needs to be parsed, represented by A1 to the last cell, which is helpful to speed up parsing. It needs to be defined in the first cell (A1) of the excel template.

Sample image:

jx:each is the most commonly used xls expression

jx:each(items="contracts" var="contract" lastCell="H3")

  • items: variable name of the collection in the context;
  • var: the variable name of each record when traversing the collection;
  • area: the parsing area of the XLS Command;
  • direction: the direction in which data is filled in excel, the default (DOWN) is downward;
  • select: its value is an expression, used to filter data

?

Note: If it involves dynamic columns and horizontal traversal, you need to pay attention to its usage, especially the data display issues of dynamic columns, which will be discussed below.

?

jx:each(items="countMonths" var="month" lastCell="C3" direction="RIGHT")

Simple example image:

Complex example diagram:

jx:mergeCells merge cells

jx:mergeCells(lastCell="Merge cell range"
[, cols="Number of columns to merge"]
[, rows="Number of merged rows"]
[, minCols="Minimum number of columns to merge"]
[, minRows="Minimum number of rows to merge"]
)
  • lastCell: merge cell range;
  • cols: number of columns to be merged;
  • rows: number of merged rows;
  • minCols: the minimum number of columns to be merged;
  • minRows: the minimum number of rows to be merged;

?

Note: This command can only be used on cells that have not yet been merged.

?

Sample image:

Dynamic column-comprehensive use

jx:each(items="countMonths" var="month" lastCell="C3" direction="RIGHT")

It is still used here through jx:each. The difference is that the value of the direction attribute is: RIGHT (right), and the default is: DOWN (downward).

Example screenshot:

Description of several parameters in the screenshot above:

  • countMonths: dynamic column collection, month is the entity of the collection cycle, the value is: ${month}
  • contracts: row data collection, contract, colData are entities of the collection cycle, the values are: ${contract.custName}, etc.
  • colData.monthData.get(month): Dynamic column data, matching entity fields based on column names
  • ${empty()}: Determine whether the dynamic column data corresponding to the set is empty, make a good judgment, and write the data

Data acquisition of dynamic column data rows:

${empty(colData.monthData.get(month) ) ? 0 : colData.monthData.get(month)}

Summary

The above are some of the more commonly used operations during my use.

The knowledge points of the article match the official knowledge files, and you can further learn related knowledge. Java Skill TreeHomepageOverview 138175 people are learning the system