preface
This is the fifth day of my participation in the November Gwen Challenge. Check out the details: The last Gwen Challenge 2021. Not much to say, to say is to fill the hole left by the previous article [ExcelUtil] implementation file write to the client download process – nuggets (juejin. Cn).
Demand analysis
In addition to the most basic header name column widths, headers and content adaptive in China and foreign countries, still need to increase the position of the specified sequence of header, specify the date of export data time date format, motor, motor for the enumeration content want to be able to read with a specified delimiter writing values, in addition, for countless according to cell can according to the demand to the default value…
Finally, give a flag whether or not to export data to meet the need: sometimes we need to export a template, which is required for the title but requires the user to fill in the content manually.
I:
Code implementation
Custom annotations
First, customize an annotation as required, where each attribute corresponds to a function:
/ * * *@description: Custom export Excel data annotations *@author: HUALEI
* @date: 2021-11-19
* @time: and * /
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface Excel {
/** * Export to Excel header alias */
String headerAlias(a) default "";
/** * Export in Excel */
int sort(a) default Integer.MAX_VALUE;
/** * The date format is YYYY-MM-DD */
String dateFormat(a) default "";
/** * reads the content based on the delimiter expression (e.g., 0= male,1= female,2= unknown) */
String readConverterExp(a) default "";
/** * delimiter (default is "," comma), reads the contents of the string group (note: some special delimiter characters need to be escaped with "\\sparator" or" [sparator]", otherwise the delimiter will fail) */
String separator(a) default ",";
/** * The default value of the field */ when the value is null
String defaultValue(a) default "";
/** * Whether to export data */
boolean isExport(a) default true;
enum Type {
/** Export import */
ALL(0),
/** only */ is exported
EXPORT(1),
/** Imports only */
IMPORT(2);
private final int value;
Type(int value) {
this.value = value;
}
public int value(a) {
return this.value; }}/** * field type (0: export import; 1: export only. 2: Import only) */
Type type(a) default Type.ALL;
}
Copy the codeThe annotation has an internal enumeration class of Type to distinguish between an import and an export of the annotated identified fields, although the requirements here are just exported to help you stay on top of the requirements.
Toolclass encapsulation
Through the new ExcelUtil < > (XXX. Class); The ExcelUtil
class contains the file name, worksheet name and other basic attributes. The annotation Field list is used to store the annotation object identified by the annotation through reflection and the corresponding annotation properties. The internal storage structure is: [[Field, Excel],… .
/ * * *@description: ExcelUtil tool class secondary encapsulation *@author: HUALEI
* @date: 2021-11-20
* @time: inquire * /
public class ExcelUtil<T> {
private static final Logger logger = LoggerFactory.getLogger(ExcelUtil.class);
/** * Excel file name */
private String fileName;
/** * Worksheet name */
private String sheetName;
/** * Export type */
private Excel.Type type;
/** * File name suffix */
private String fileNameSuffix;
/** * Import/export data source list */
private List<T> sourceList;
/** * Annotated Field list [[Field, Excel]...] * /
private List<Object[]> fields;
/** * entity object */
public Class<T> clazz;
/** * Excel writer */
public ExcelWriter excelWriter;
public ExcelUtil(Class<T> clazz) {
this.clazz = clazz; }... . }Copy the codeIn addition to member variables in the encapsulation class, the most important is the member method. Considering that the exported file may sometimes need.xls format, so I override the export Excel method, the default is.xlsx format.
/** * Write the data source list to an Excel file **@paramResponse HttpServletResponse object *@paramList Data source list *@paramFileName Excel fileName *@paramSheetName sheetName in Excel */
public void exportExcel(HttpServletResponse response, List
list, String fileName, String sheetName )
throws Exception {
this.excelWriter = cn.hutool.poi.excel.ExcelUtil.getBigWriter();
logger.info("=============== initialize Excel ===============");
init(list, fileName, sheetName, Excel.Type.EXPORT);
exportExcel(response, null);
logger.info("=============== Excel export success ===============");
}
/** * Write the data source list to an Excel file **@paramResponse HttpServletResponse object *@paramList Data source list *@paramFileName Excel fileName *@paramFileNameSuffix Excel fileNameSuffix *@paramSheetName sheetName in Excel */
public void exportExcel(HttpServletResponse response, List
list, String fileName, String fileNameSuffix, String sheetName )
throws Exception {
this.excelWriter = cn.hutool.poi.excel.ExcelUtil.getBigWriter();
logger.info("=============== initialize Excel ===============");
init(list, fileName, sheetName, Excel.Type.EXPORT);
exportExcel(response, fileNameSuffix);
logger.info("=============== Excel export success ===============");
}
Copy the codeIn the export method, we initialize the writer first, and then initialize the class property value:
/** * Initializes the class attribute **@paramList Data source list *@paramFileName Specifies the name of the export file *@paramSheetName sheetName *@paramType Export type */
public void init(List<T> list, String fileName, String sheetName, Excel.Type type) throws Exception {
this.sourceList = Optional.ofNullable(list).orElseGet(ArrayList<T>::new);
this.fileName = fileName;
this.sheetName = sheetName;
// Set the Sheet name
this.excelWriter.renameSheet(sheetName);
this.type = type;
// Create the header
createExcelField();
// Process the data source
handleDataSource();
}
Copy the codeAfter initializing the partial member variable, create the specified sequence header and set the header alias:
/** * creates the specified sequence header and sets the header alias */
private void createExcelField(a) {
this.fields = new ArrayList<Object[]>();
// Temporarily store variables
List<Field> tempFields = new ArrayList<>();
// Get the list of all declared fields of the target entity object and place them in temporary storage variables
tempFields.addAll(Arrays.asList(clazz.getSuperclass().getDeclaredFields()));
tempFields.addAll(Arrays.asList(clazz.getDeclaredFields()));
// Filter out the fields marked by @excel from the list of declared fields
tempFields.stream()
.filter(field -> field.isAnnotationPresent(Excel.class))
.forEach(field -> {
// Get the annotation property object
Excel attr = field.getAnnotation(Excel.class);
// Filter the target export type
if(attr ! =null && (attr.type() == Excel.Type.ALL || attr.type() == this.type)) {
// Populate the list of annotations [[Field, Excel]]
this.fields.add(newObject[]{ field, attr }); }});// Sort in ascending order by the value of the sort attribute in the annotation
this.fields.stream()
.sorted(Comparator.comparing( arr -> ((Excel) arr[1]).sort() ))
.collect(Collectors.toList())
// Set the table header aliases in sequence
.forEach(arr -> {
String fieldName = ((Field) arr[0]).getName();
Excel attr = (Excel) arr[1];
this.excelWriter.addHeaderAlias(fieldName, StrUtil.isBlank(attr.headerAlias()) ? fieldName : attr.headerAlias());
});
}
Copy the codeGet the parent class of the target entity object and all of its declaration fields, store them in a temporary list of fields, then loop through and filter out the fields identified by the @excel annotation, and then build an array of size 2 by filtering the target export type into the annotation field list this.fields.
Second, sort in ascending order by the value of the sort attribute in the annotation, or by default by the order in which the field is defined if no order value is set. After sorting, set the table header aliases in sequence. If not, keep the default field names.
Once the header is created, the next step is to process the data source list according to the annotation property object on each field in the annotation field list Fields:
/** * Process the data source list according to the annotation properties **@throwsException gets the Exception that a class attribute value may throw */
private void handleDataSource(a) throws Exception {
for (Object[] arr : this.fields) {
// Annotate identified fields
Field field = (Field) arr[0];
// Annotate the property object
Excel attr = (Excel) arr[1];
// Make the entity class private property accessible
field.setAccessible(true);
for (T object: this.sourceList) {
// Get the property value of the current field
Object value = field.get(object);
if (attr.isExport()) {
if(value ! =null) {
// Set the time format
if (StrUtil.isNotBlank(attr.dateFormat())) {
field.set(object, cn.hutool.core.convert.Convert.convert(field.getType(), DateUtil.format(new DateTime(value.toString()), attr.dateFormat())));
}
// Set the conversion value
if(StrUtil.isNotBlank(attr.readConverterExp())) { String convertResult = convertByExp(Convert.toStr(value), attr.readConverterExp(), attr.separator()); field.set(object, convertResult); }}else {
// Set the default value
if(StrUtil.isNotBlank(attr.defaultValue())) { field.set(object, attr.defaultValue()); }}}else {
field.set(object, null); }}}}Copy the codeThe above code mainly obtains the property value of the field field under the current object through Java reflection principle, and determines whether the current column data needs to be exported. If necessary, it further determines whether the corresponding value of the attribute in the annotation has a value. If there is a value and the field attribute value is not null, it changes the original value. Values that have a field attribute value of NULL can be set to the specified default value. Otherwise, no export is required, all cells of the column are empty.
Simple understanding of the text may not be as intuitive and clear as a flow chart, which gives you an arrangement:
For the method convertByExp() to parse the exported value, split the translated annotation string by delimiter, and parse it according to the principle that the left side of the “=” equals sign is the key and the right side is the value. The specific code is as follows:
/** * Parse the exported value **@paramPropertyValue Parameter value *@paramConverterExp translation notes *@paramSeparator Separator *@returnParsed value */
public static String convertByExp(String propertyValue, String converterExp, String separator) {
StringBuilder propertyString = new StringBuilder();
String[] convertSource = converterExp.split(separator);
for (String item : convertSource) {
String[] itemArray = item.split("=");
if (StringUtils.containsAny(separator, propertyValue)) {
for (String value : propertyValue.split(separator)) {
if (itemArray[0].equals(value)) {
propertyString.append(itemArray[1]).append(separator);
break; }}}else {
if (itemArray[0].equals(propertyValue)) {
return itemArray[1]; }}}return StringUtils.stripEnd(propertyString.toString(), separator);
}
Copy the codeNow that all the initialization is done, you can happily write data into Excel and finally write files to the client for download.
/** * write to the client to download **@paramResponse HttpServletResponse object *@paramSuffix Export Excel file name suffix */
public void exportExcel(HttpServletResponse response, String suffix) throws IOException {
/ / the output stream
ServletOutputStream out = response.getOutputStream();
this.excelWriter.write(this.sourceList, true);
cellWidthSelfAdaption();
initResponse(response, suffix);
this.excelWriter.flush(out, true);
// Close Writer to release memory
this.excelWriter.close();
// Close the output Servlet stream
IoUtil.close(out);
}
Copy the code-
The cellWidthSelfAdaption() method is used for the Chinese width adaptation, the code will not be pasted here, and the code will be better understood, please click here and the portal will be better understood
-
InitResponse () initializes the HttpServletResponse object based on the exported Excel filename suffix for the response body and response type.
/** * Initializes the HttpServletResponse object ** based on the exported Excel filename suffix@paramResponse HttpServletResponse object *@paramSuffix File name suffix *@throwsUnsupportedEncodingException doesn't support coding abnormal * /
public void initResponse(HttpServletResponse response, String suffix) throws UnsupportedEncodingException {
// The default export file name suffix
this.fileNameSuffix = ".xlsx";
if(suffix ! =null) {
switch (suffix.toLowerCase()) {
case "xls":
case ".xls":
this.fileNameSuffix = ".xls";
response.setContentType("application/vnd.ms-excel; charset=utf-8");
break;
case "xlsx":
case ".xlsx":
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet; charset=utf-8");
break;
default:
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet; charset=utf-8"); }}else {
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet; charset=utf-8");
}
// The file name is in Chinese
String encodingFilename = encodingFilename(this.fileName);
response.setHeader("Content-Disposition"."attachment; filename="+ encodingFilename);
}
Copy the codeThe default export file format is.xlsx, but it can also be specified as.xls by setting different content types. As for the exported file name, add a suffix and a code to the response head.
/** * encoding file name **@paramFilename indicates the filename */
public String encodingFilename(String filename) throws UnsupportedEncodingException {
filename = filename + this.fileNameSuffix;
return URLEncoder.encode(filename, CharsetUtil.UTF_8);
}
Copy the codeAt this point, the code for the annotation + ExcelUtil secondary wrapping is complete.
Expose interfaces
Entity objects
As usual, entity objects give it the @Excel annotation and add a property Buff:
@Data
public class ProvinceCustomAnnotationExcelVO implements Serializable {
private static final long serialVersionUID = 877981781678377000L;
/** ** province */
@excel (headerAlias = "province ")
private String province;
/** ** province */
@excel (headerAlias = "for short ")
private String abbr;
/** * The area of the province (km²) */
@excel (headerAlias = "area (km²) ")
private Integer area;
/ Population of ** * province (ten thousand) */
@excel (headerAlias = "Population (ten thousand) ")
private BigDecimal population;
/** ** ** ** ** ** ** ** *
@excel (headerAlias = "Famous spot ")
private String attraction;
/** * The postcode of the provincial capital */
@ Excel (headerAlias = "postal code", readConverterExp = 100 = "cow force is finished | = 050000 ha ha ha", the separator = "\ \ |")
private String postcode;
/** * The name of the provincial capital */
@excel (headerAlias = "default ", defaultValue =" default ")
private String city;
/** ** The provincial capital's alias */
@excel (headerAlias = "alias ", isExport = false)
private String nickname;
/** ** Climate type of provincial capitals */
@excel (headerAlias = "Climate type ")
private String climate;
/** ** ** */
@excel (headerAlias = "license number ", defaultValue =" data not yet available ")
private String carcode;
/** * test time */
@excel (headerAlias = "create time ", dateFormat = "yyyy ")
private String createTime;
}
Copy the codeControl layer
Service layer getAllProvinceDetails() method code implementation please refer to [ExcelUtil] implementation file write to the client download process – dig gold (juejin. Cn).
@GetMapping("provinces/custom/excel/export/{fileNameSuffix}")
public void customAnnotationExcelExport(HttpServletResponse response, @PathVariable("fileNameSuffix") String fileNameSuffix) throws Exception {
// Get the province details
List<ProvinceExcelVO> provinceExcelList = this.provinceService.getAllProvinceDetails();
// The Bean object is converted to get the data source list
List<ProvinceCustomAnnotationExcelVO> provinceCustomAnnotationExcelList = BeanUtil.copyToList(provinceExcelList, ProvinceCustomAnnotationExcelVO.class);
// To test export time formatting, add point random date time
provinceCustomAnnotationExcelList.forEach(p -> p.setCreateTime(RandomUtil.randomDate(new Date(), DateField.SECOND, 0.24*60*60).toString()));
// Create an ExcelUtil object with a parameter construct (required)
ExcelUtil<ProvinceCustomAnnotationExcelVO> excelUtil = new ExcelUtil<>(ProvinceCustomAnnotationExcelVO.class);
// File name (date of the day _ province information)
String fileName = StrUtil.format("{}{} province information", DateUtil.today(), StrUtil.UNDERLINE);
// Sheet Sheet name
String sheetName = "List of Provinces";
if (StrUtil.isBlank(fileNameSuffix)) {
// Test the export default format
excelUtil.exportExcel(response, provinceCustomAnnotationExcelList, fileName, sheetName);
} else {
// Tests the export format specifiedexcelUtil.exportExcel(response, provinceCustomAnnotationExcelList, fileName, fileNameSuffix, sheetName); }}Copy the codeThe exported file name suffix is placed on the path mainly for testing convenience, which is not necessary in actual development for Duck!
The interface test
Start testing:
GET: http://localhost:8088/file/provinces/custom/excel/export/xls
GET: http://localhost:8088/file/provinces/custom/excel/export/.xlsx
GET: http://localhost:8088/file/provinces/custom/excel/export/""
GET: http://localhost:8088/file/provinces/custom/excel/export/HUALEI
All tests passed, can be called perfect, pit filling success!! ✿ Remember, (° °) Blue ✿
conclusion
The overall implementation is not too difficult, the use of annotations drive is not too sweet, it is very convenient to use, even if the small white did not learn programming will also use, one or two lines of code can complete a list of data sources export.
The only deficiency is that the data import is not integrated into, but the focus of this article is not import, ha ha ha, interested partners can try oh ヾ(◍°∇°◍) Ada
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.