ExcelWriter

Origin

Just as Excel has reading capabilities, Hutool also provides encapsulation for writing data to Excel.

Principle

Hutool encapsulates Excel writing as ExcelWriter, which wraps the Workbook object. After each call to the merge (merge cells) or write (write data) method, the data is only written to the Workbook and not to the file. The file is only actually written when the flush or close method is called.

Due to its mechanism, it is necessary to close the ExcelWriter object after writing is completed. Calling the close method will close it and release the Workbook object’s resources. Otherwise, the Workbook with data will reside in memory.

Usage Examples

1. Writing Row and Column Objects to Excel

First, we define a nested List, where each element of the List is also a List. The inner List represents a row of data, with four cells per row. The list object represents multiple rows of data.

List<String> row1 = CollUtil.newArrayList("aa", "bb", "cc", "dd");
List<String> row2 = CollUtil.newArrayList("aa1", "bb1", "cc1", "dd1");
List<String> row3 = CollUtil.newArrayList("aa2", "bb2", "cc2", "dd2");
List<String> row4 = CollUtil.newArrayList("aa3", "bb3", "cc3", "dd3");
List<String> row5 = CollUtil.newArrayList("aa4", "bb4", "cc4", "dd4");

List<List<String>> rows = CollUtil.newArrayList(row1, row2, row3, row4, row5);

    
  

Then we create an ExcelWriter object and write the data:

// Create writer through utility class
ExcelWriter writer = ExcelUtil.getWriter("d:/writeTest.xlsx");
// Create writer through constructor
// ExcelWriter writer = new ExcelWriter("d:/writeTest.xls");

// Skip the current row, not necessary, used here for demonstration purposes
writer.passCurrentRow();

// Merged cell title row, using default title style
writer.merge(row1.size() - 1, "Test Title");
// Write content all at once, force output of title
writer.write(rows, true);
// Close writer, release memory
writer.close();

    
  

Effect: Writing Effect Diagram

2. Writing Map Data

Constructing data:

Map<String, Object> row1 = new LinkedHashMap<>();
row1.put("Name", "Zhang San");
row1.put("Age", 23);
row1.put("Score", 88.32);
row1.put("Qualified", true);
row1.put("Exam Date", DateUtil.date());

Map<String, Object> row2 = new LinkedHashMap<>();
row2.put("Name", "Li Si");
row2.put("Age", 33);
row2.put("Score", 59.50);
row2.put("Qualified", false);
row2.put("Exam Date", DateUtil.date());

ArrayList<Map<String, Object>> rows = CollUtil.newArrayList(row1, row2);

    
  

Writing Data:

// Create writer through utility class
ExcelWriter writer = ExcelUtil.getWriter("d:/writeMapTest.xlsx");
// Merged cell title row, using default title style
writer.merge(row1.size() - 1, "Class 1 Grades");
// Write content all at once, using default style, force output of title
writer.write(rows, true);
// Close writer, release memory
writer.close();

    
  

Effect: Writing Effect

3. Writing Bean Data

Define Bean:

public class TestBean {
 private String name;
 private int age;
 private double score;
 private boolean isPass;
 private Date examDate;

 public String getName() {
 return name;
 }

 public void setName(String name) {
 this.name = name;
 }

 public int getAge() {
 return age;
 }

 public void setAge(int age) {
 this.age = age;
 }

 public double getScore() {
 return score;
 }

 public void setScore(double score) {
 this.score = score;
 }

 public boolean isPass() {
 return isPass;
 }

 public void setPass(boolean isPass) {
 this.isPass = isPass;
 }

 public Date getExamDate() {
 return examDate;
 }

 public void setExamDate(Date examDate) {
 this.examDate = examDate;
 }
}

    
  

Construct Data:

TestBean bean1 = new TestBean();
bean1.setName("Zhang San");
bean1.setAge(22);
bean1.setPass(true);
bean1.setScore(66.30);
bean1.setExamDate(DateUtil.date());

TestBean bean2 = new TestBean();
bean2.setName("Li Si");
bean2.setAge(28);
bean2.setPass(false);
bean2.setScore(38.50);
bean2.setExamDate(DateUtil.date());

List<TestBean> rows = CollUtil.newArrayList(bean1, bean2);

    
  

Writing Data:

// Create writer through utility class
ExcelWriter writer = ExcelUtil.getWriter("d:/writeBeanTest.xlsx");
// Merged cell title row, using default title style
writer.merge(4, "Class 1 Grades");
// Write content all at once, using default style, force output of title
writer.write(rows, true);
// Close writer, release memory
writer.close();

    
  

Effect: Writing Bean Data

4. Customizing Bean Key Aliases (Sorting Titles)

When writing out Beans, we can call the addHeaderAlias method of the ExcelWriter object to customize the alias of the key in the Bean, thus allowing us to write out custom titles (such as Chinese).

Writing Data:

// Create writer through utility class
ExcelWriter writer = ExcelUtil.getWriter("d:/writeBeanTest.xlsx");

// Customize title aliases
writer.addHeaderAlias("name", "Name");
writer.addHeaderAlias("age", "Age");
writer.addHeaderAlias("score", "Score");
writer.addHeaderAlias("isPass", "Passed");
writer.addHeaderAlias("examDate", "Exam Date");

// By default, properties that haven't added aliases will also be written out. If you want to only write out fields with aliases, call this method to exclude them.
writer.setOnlyAlias(true);

// Merged cell title row, using default title style
writer.merge(4, "Class 1 Grades");
// Write content all at once, using default style, force output of title
writer.write(rows, true);
// Close writer, release memory
writer.close();

    
  

Annotation support for aliases is also available. You can add the @Alias annotation to the field.

@Alias(value = "Name")
private String name;

Effect:

Tip (since 4.1.5) By default, the order of Bean fields written out in Excel cannot be guaranteed. In this case, you can use the addHeaderAlias method to set the title alias, and the order of the Bean’s written out will be sorted according to the order in which the title aliases are added. If you don’t need to set a title but want to sort the fields, call writer.addHeaderAlias("age", "age") to set the same alias and the title will not be changed. Fields that haven’t set a title alias will not be sorted and will be placed at the front by default.

5. Writing to a Stream

// Create writer through utility class, defaulting to xls format
ExcelWriter writer = ExcelUtil.getWriter();
// Create xlsx format
//ExcelWriter writer = ExcelUtil.getWriter(true);
// Write content all at once, using default style, force output of title
writer.write(rows, true);
// out is an OutputStream, the target stream to be written to
writer.flush(out);
// Close writer, release memory
writer.close();

    
  

6. Writing to Client for Download (Writing to Servlet)

Writing xls

// Create writer through utility class, defaulting to xls format
ExcelWriter writer = ExcelUtil.getWriter();
// Write content all at once, using default style, force output of title
writer.write(rows, true);
// out is an OutputStream, the target stream to be written to

// response is a HttpServletResponse object
response.setContentType("application/vnd.ms-excel;charset=utf-8"); 
// test.xls is the file name that will appear in the download dialog box, it cannot be in Chinese, please encode it if necessary
response.setHeader("Content-Disposition","attachment;filename=test.xls"); 
ServletOutputStream out=response.getOutputStream(); 

writer.flush(out, true);
// Close writer, release memory
writer.close();
// Remember to close the Servlet output stream here
IoUtil.close(out);

    
  

Writing xlsx

ExcelWriter writer = ExcelUtil.getWriter(true);
writer.write(rows, true);

response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8"); 
response.setHeader("Content-Disposition","attachment;filename=test.xlsx"); 
ServletOutputStream out=response.getOutputStream(); 

writer.flush(out, true);
writer.close();
IoUtil.close(out);

    
  

Note: ExcelUtil.getWriter() defaults to creating xls format Excel, so when writing to the client, the file name should also be customized as XXX.xls, otherwise a file corruption prompt will appear. To generate xlsx format, please use ExcelUtil.getWriter(true) to create it.

Solving the Problem of File Corruption Prompt During Download

Some users have reported that the generated Excel file cannot be opened after download, and it prompts file corruption. After investigation, it may be due to several issues:

(1) The writer and out streams are not correctly closed. Please add closing in the finally block at the end of the code.
(2) The file extension does not match. getWriter defaults to generating xls, and Content-Disposition should also be xls. Only when using getWriter(true) can xlsx be used.
(3) In Maven projects, if the Excel file is saved in the ClassPath (under src/main/resources), macro replacement may cause it to be corrupted. The solution is to add filtering (refer to: https://blog.csdn.net/qq_42270377/article/details/92771349)
(4) Excel prompts file corruption when opening, but WPS can open it. This is caused by Excel’s security controls. For a solution, see: https://blog.csdn.net/zm9898/article/details/99677626

Customizing Excel

1. Setting Cell Background Color

ExcelWriter writer = ...;

// Define cell background color
StyleSet style = writer.getStyleSet();
// The second parameter indicates whether to set the header cell background as well
style.setBackgroundColor(IndexedColors.RED, false);

2. Custom Fonts

ExcelWriter writer = ...;
// Set content font
Font font = writer.createFont();
font.setBold(true);
font.setColor(Font.COLOR_RED); 
font.setItalic(true); 
// The second parameter indicates whether to ignore the header style
writer.getStyleSet().setFont(font, true);

    
  

3. Writing Multiple Sheets

// Define sheet name when initializing
ExcelWriter writer = new ExcelWriter("d:/aaa.xls", "Sheet1");
// Switch to another sheet, writing starts from row 0
writer.setSheet("Sheet2");
...
writer.setSheet("Sheet3");
...

    
  

4. More Detailed Style Definition

In Excel, due to the limited number of style objects, Hutool divides them into four style objects based on the type of style, so that cells of the same type can share style objects. Styles are stored in StyleSet according to their categories, including:

Header cell style: headCellStyle
Ordinary cell style: cellStyle
Numeric cell style: cellStyleForNumber
Date cell style: cellStyleForDate

Among them, cellStyleForNumber and cellStyleForDate are used to control the display of numbers and dates.

Therefore, we can use the following method to get a CellStyle object to customize a specific type of style:

StyleSet style = writer.getStyleSet();
CellStyle cellStyle = style.getHeadCellStyle();
...

5. Custom Written Values

You can implement the CellSetter interface to customize the value written to a cell. This interface has only one method: setValue(Cell cell), which exposes the Cell object so that users can customize the content and even the style of the output cell.

// Use lambda here to customize the output content
List<Object> row = ListUtil.of((CellSetter) cell -> cell.setCellValue("Custom content"));

ExcelWriter writer = ExcelUtil.getWriter("/test/test.xlsx");
writer.writeRow(row);
writer.close();

Note: Some special characters can cause Excel to automatically escape, such as strings in the format of _xXXXX_, which will be treated as Unicode escape sequences and will be reversed. In this case, you can use Hutool’s built-in EscapeStrCellSetter.

List<Object> row = ListUtil.of(new EscapeStrCellSetter("_x5116_"));

ExcelWriter writer = ExcelUtil.getWriter("/test/test.xlsx");
writer.writeRow(row);
writer.close();

More detail, visit：https://gitee.com/dromara/hutool/issues/I466ZZ

Excel07SaxReader BigExcelWriter