This is the 16th day of my participation in the August More Text Challenge
Goal:
Export the data in the table to Excel
Take measures:
Excel is operated through a third party open source tool called POI (an obscure Implementation of the Poor Obfuscation Implementation compact version). Apache POI is an open source cross-platform Java API written in Java, It provides apis for Java to read and write Files in Microsoft Office format.
Steps:
1. Import the dependency in pom.xml
<! -- POI dependency -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.13</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.13</version>
</dependency>
Copy the code2. Custom bean required by entity class (Excel property title, location, etc.)
Create a new Annotation class for ExcelColumn under Config.
package com.tjm.config;
import java.lang.annotation.*;
// Define the bean required by the entity class (Excel property title, location, etc.)
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface ExcelColumn {
/ / Excel title
String value(a) default "";
//Excel arranges positions from left to right
int col(a) default 0;
}
Copy the code3. Create a core Excel export tool class
Create a package for utils in the same layer as config and write one below it
ExcelUtils
The following code does not need to be modified:
package com.tjm.utils;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Constructor;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Comparator;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.stream.Collectors;
import java.util.stream.Stream;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.BooleanUtils;
import org.apache.commons.lang3.CharUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.math.NumberUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.http.MediaType;
import org.springframework.web.multipart.MultipartFile;
import com.tjm.config.ExcelColumn;
public class ExcelUtils {
private final static Logger log = LoggerFactory.getLogger(ExcelUtils.class);
private final static String EXCEL2003 = "xls";
private final static String EXCEL2007 = "xlsx";
public static <T> List<T> readExcel(String path, Class<T> cls,MultipartFile file){
String fileName = file.getOriginalFilename();
if(! fileName.matches("^. + \ \. (? i)(xls)$") && !fileName.matches("^. + \ \. (? i)(xlsx)$")) {
log.error("Upload file format is incorrect");
}
List<T> dataList = new ArrayList<>();
Workbook workbook = null;
try {
InputStream is = file.getInputStream();
if (fileName.endsWith(EXCEL2007)) {
// FileInputStream is = new FileInputStream(new File(path));
workbook = new XSSFWorkbook(is);
}
if (fileName.endsWith(EXCEL2003)) {
// FileInputStream is = new FileInputStream(new File(path));
workbook = new HSSFWorkbook(is);
}
if(workbook ! =null) {
// Class mapping annotation value-->bean columns
Map<String, List<Field>> classMap = new HashMap<>();
List<Field> fields = Stream.of(cls.getDeclaredFields()).collect(Collectors.toList());
fields.forEach(
field -> {
ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);
if(annotation ! =null) {
String value = annotation.value();
if (StringUtils.isBlank(value)) {
return;// Return serves the same syntax as continue
}
if(! classMap.containsKey(value)) { classMap.put(value,new ArrayList<>());
}
field.setAccessible(true); classMap.get(value).add(field); }});/ / index - - > the columns
Map<Integer, List<Field>> reflectionMap = new HashMap<>(16);
// Read the first sheet by default
Sheet sheet = workbook.getSheetAt(0);
boolean firstRow = true;
for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) {
Row row = sheet.getRow(i);
// The first line extracts the annotation
if (firstRow) {
for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {
Cell cell = row.getCell(j);
String cellValue = getCellValue(cell);
if (classMap.containsKey(cellValue)) {
reflectionMap.put(j, classMap.get(cellValue));
}
}
firstRow = false;
} else {
// Ignore blank lines
if (row == null) {
continue;
}
try {
T t = cls.newInstance();
// Determine if there is a blank line
boolean allBlank = true;
for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {
if (reflectionMap.containsKey(j)) {
Cell cell = row.getCell(j);
String cellValue = getCellValue(cell);
if (StringUtils.isNotBlank(cellValue)) {
allBlank = false;
}
List<Field> fieldList = reflectionMap.get(j);
fieldList.forEach(
x -> {
try {
handleField(t, cellValue, x);
} catch (Exception e) {
log.error(String.format("reflect field:%s value:%s exception!", x.getName(), cellValue), e); }}); }}if(! allBlank) { dataList.add(t); }else {
log.warn(String.format("row:%s is blank ignore!", i)); }}catch (Exception e) {
log.error(String.format("parse row:%s exception!", i), e);
}
}
}
}
} catch (Exception e) {
log.error(String.format("parse excel exception!"), e);
} finally {
if(workbook ! =null) {
try {
workbook.close();
} catch (Exception e) {
log.error(String.format("parse excel exception!"), e); }}}return dataList;
}
private static <T> void handleField(T t, String value, Field field) throws Exception { Class<? > type = field.getType();if (type == null || type == void.class || StringUtils.isBlank(value)) {
return;
}
if (type == Object.class) {
field.set(t, value);
// Number type
} else if (type.getSuperclass() == null || type.getSuperclass() == Number.class) {
if (type == int.class || type == Integer.class) {
field.set(t, NumberUtils.toInt(value));
} else if (type == long.class || type == Long.class) {
field.set(t, NumberUtils.toLong(value));
} else if (type == byte.class || type == Byte.class) {
field.set(t, NumberUtils.toByte(value));
} else if (type == short.class || type == Short.class) {
field.set(t, NumberUtils.toShort(value));
} else if (type == double.class || type == Double.class) {
field.set(t, NumberUtils.toDouble(value));
} else if (type == float.class || type == Float.class) {
field.set(t, NumberUtils.toFloat(value));
} else if (type == char.class || type == Character.class) {
field.set(t, CharUtils.toChar(value));
} else if (type == boolean.class) {
field.set(t, BooleanUtils.toBoolean(value));
} else if (type == BigDecimal.class) {
field.set(t, newBigDecimal(value)); }}else if (type == Boolean.class) {
field.set(t, BooleanUtils.toBoolean(value));
} else if (type == Date.class) {
//
field.set(t, value);
} else if (type == String.class) {
field.set(t, value);
} else {
Constructor<?> constructor = type.getConstructor(String.class);
field.set(t, constructor.newInstance(value));
}
}
private static String getCellValue(Cell cell) {
if (cell == null) {
return "";
}
if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
if (HSSFDateUtil.isCellDateFormatted(cell)) {
return HSSFDateUtil.getJavaDate(cell.getNumericCellValue()).toString();
} else {
return newBigDecimal(cell.getNumericCellValue()).toString(); }}else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
return StringUtils.trimToEmpty(cell.getStringCellValue());
} else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
return StringUtils.trimToEmpty(cell.getCellFormula());
} else if (cell.getCellType() == Cell.CELL_TYPE_BLANK) {
return "";
} else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
return String.valueOf(cell.getBooleanCellValue());
} else if (cell.getCellType() == Cell.CELL_TYPE_ERROR) {
return "ERROR";
} else {
returncell.toString().trim(); }}public static <T> void writeExcel(HttpServletResponse response, List<T> dataList, Class<T> cls){
Field[] fields = cls.getDeclaredFields();
List<Field> fieldList = Arrays.stream(fields)
.filter(field -> {
ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);
if(annotation ! =null && annotation.col() > 0) {
field.setAccessible(true);
return true;
}
return false;
}).sorted(Comparator.comparing(field -> {
int col = 0;
ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);
if(annotation ! =null) {
col = annotation.col();
}
return col;
})).collect(Collectors.toList());
Workbook wb = new XSSFWorkbook();
Sheet sheet = wb.createSheet("Sheet1");
AtomicInteger ai = new AtomicInteger();
{
Row row = sheet.createRow(ai.getAndIncrement());
AtomicInteger aj = new AtomicInteger();
// Write the header
fieldList.forEach(field -> {
ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);
String columnName = "";
if(annotation ! =null) {
columnName = annotation.value();
}
Cell cell = row.createCell(aj.getAndIncrement());
CellStyle cellStyle = wb.createCellStyle();
cellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
Font font = wb.createFont();
font.setBoldweight(Font.BOLDWEIGHT_NORMAL);
cellStyle.setFont(font);
cell.setCellStyle(cellStyle);
cell.setCellValue(columnName);
});
}
if (CollectionUtils.isNotEmpty(dataList)) {
dataList.forEach(t -> {
Row row1 = sheet.createRow(ai.getAndIncrement());
AtomicInteger aj = newAtomicInteger(); fieldList.forEach(field -> { Class<? > type = field.getType(); Object value ="";
try {
value = field.get(t);
} catch (Exception e) {
e.printStackTrace();
}
Cell cell = row1.createCell(aj.getAndIncrement());
if(value ! =null) {
if (type == Date.class) {
cell.setCellValue(value.toString());
} else{ cell.setCellValue(value.toString()); } cell.setCellValue(value.toString()); }}); }); }// Freeze the pane
wb.getSheet("Sheet1").createFreezePane(0.1.0.1);
// The browser downloads Excel
buildExcelDocument("Logging.xlsx",wb,response);
// Generate an Excel file
// buildExcelFile(".\\default.xlsx",wb);
}
/** * Download Excel for browser *@param fileName
* @param wb
* @param response
*/
private static void buildExcelDocument(String fileName, Workbook wb,HttpServletResponse response){
try {
response.setContentType(MediaType.APPLICATION_OCTET_STREAM_VALUE);
response.setHeader("Content-Disposition"."attachment; filename="+URLEncoder.encode(fileName, "utf-8"));
response.flushBuffer();
wb.write(response.getOutputStream());
} catch(IOException e) { e.printStackTrace(); }}/** * Generate excel file *@paramPath Generates the Excel path *@param wb
*/
private static void buildExcelFile(String path, Workbook wb){
File file = new File(path);
if (file.exists()) {
file.delete();
}
try {
wb.write(new FileOutputStream(file));
} catch(Exception e) { e.printStackTrace(); }}}Copy the code4. Define the exported entity class
Add an @excelcolum annotation to each field, for example:
@ExcelColumn(value = "username", col = 2)
String username;
Copy the codeValue corresponds to the exported Excel column name, and COL corresponds to the exported column sort.
5. Code writing of Controller layer
At the Controller level, you only need to call all the data you need to export. For example, if you want to export all the user information, you need to call the queryUserList method in UserService.
After obtaining the data resultList you need, call the writeExcel method in ExcelUtils in 3 and pass in the current response, query result resultList and query data type User.class as parameters to export Excel. The specific code is as follows:
// Export logs to Excle
@RequestMapping(value = "/exportExcel", method = RequestMethod.GET)
public void exportExcel(a) throws IOException {
ServletRequestAttributes servletRequestAttributes =
(ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
HttpServletResponse response = servletRequestAttributes.getResponse();;
// Find all data methods of the corresponding class
List<XXX> resultList = XXXService.queryList();
long t1 = System.currentTimeMillis();
// Core code
ExcelUtils.writeExcel(response, resultList, XXX.class);
long t2 = System.currentTimeMillis();
System.out.println(String.format("write over! cost:%sms", (t2 - t1)));
}
Copy the code6. Front-end code
<a th:href="@{/exportExcel}" class="out">export</a>
Copy the code