The function of exporting Excel needs to be used in work. The traditional method is complex and difficult to modify. Here is an encapsulated tool class writing method. You only need to set the entity class, write the corresponding column name in the way of annotation, and then pass in the data set found from the database to export.
Let's introduce the code first. If you don't want to see it, you can directly click the download link, which has all the relevant codes.
First, pom introduces the jar package:
<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.13</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-scratchpad</artifactId> <version>3.13</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.13</version> </dependency>
FileUtils.java:
/**
* Export Excel files directly from browser
*
* @param filename File name
* @param list File content List
* @param clazz List Object types in
* @return ResponseBo
*/
public static ResponseBo createExcelByPOIKit(String filename, List<?> list, Class<?> clazz,HttpServletResponse response) {
if (list.isEmpty()) {
return ResponseBo.warn("Export data is empty!");
} else {
boolean operateSign = false;
String fileName = filename + ".xlsx";
operateSign = ExcelUtils.export(clazz, response)
// Set the maximum number of records for each sheet. The default value is 10000, which is optional
// .setMaxSheetRecords(10000)
.toExcel(list,"Test data", null);
if (operateSign) {
return ResponseBo.ok(fileName);
} else {
return ResponseBo.error("export Excel Failed, please contact the website administrator!");
}
}
}This method is mainly used to export Excel files directly from the browser. filename is the file name, list is the data collection, clazz is the type of objects in the collection, and response is the response passed in from the Controller.
ExcelUtils.java:
/**
* For browser export
*
* @param clazz Entity Class object
* @param response Native HttpServletResponse object
* @return ExcelUtils
*/
public static ExcelUtils export(Class<?> clazz, HttpServletResponse response) {
return new ExcelUtils(clazz, response);
}The export method is used to create an ExcelUtils class, which is actually a constructor.
public boolean toExcel(List<?> data,String sheetName, OutputStream out) {
return toExcel(data,sheetName, new ExportHandler() {
@Override
public CellStyle headCellStyle(SXSSFWorkbook wb) {
CellStyle cellStyle = wb.createCellStyle();
Font font = wb.createFont();
cellStyle.setFillForegroundColor((short) 12);
cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);// Fill mode
cellStyle.setBorderTop(CellStyle.BORDER_THIN);// The top border is a thin border
cellStyle.setBorderRight(CellStyle.BORDER_THIN);// The right border is a thin border
cellStyle.setBorderBottom(CellStyle.BORDER_THIN);// The bottom border is a thin border
cellStyle.setBorderLeft(CellStyle.BORDER_THIN);// The left border is a thin border
cellStyle.setAlignment(CellStyle.ALIGN_LEFT);// alignment
cellStyle.setFillForegroundColor(HSSFColor.GREEN.index);
cellStyle.setFillBackgroundColor(HSSFColor.GREEN.index);
font.setBoldweight(Font.BOLDWEIGHT_NORMAL);
// font.setFontHeightInPoints((short) 12);// font size
font.setColor(HSSFColor.WHITE.index);
// Apply title font to Title Style
cellStyle.setFont(font);
return cellStyle;
}
@Override
public String exportFileName(String sheetName) {
return String.format("export-%s-%s", sheetName, System.currentTimeMillis());
}
}, out);
}The toExcel method calls another overloaded method, passes in an anonymous inner class of ExportHandler, and overloads two methods.
public boolean toExcel(List<?> data,String sheetName, ExportHandler handler, OutputStream out) {
requiredbuilderParams();
if (data == null || data.isEmpty()) {
return false;
}
// Export column query.
ExportConfig currentExportConfig;
ExportItem currentExportItem;
List<ExportItem> exportItems = new ArrayList<>();
for (Field field : mClass.getDeclaredFields()) {
currentExportConfig = field.getAnnotation(ExportConfig.class);
if (currentExportConfig != null) {
currentExportItem = new ExportItem().setField(field.getName())
.setDisplay("field".equals(currentExportConfig.value()) ? field.getName()
: currentExportConfig.value())
.setWidth(currentExportConfig.width()).setConvert(currentExportConfig.convert())
.setColor(currentExportConfig.color()).setReplace(currentExportConfig.replace());
exportItems.add(currentExportItem);
}
}
// Create a new workbook.
SXSSFWorkbook wb = POIUtils.newSXSSFWorkbook();
double sheetNo = Math.ceil((double) data.size() / mMaxSheetRecords);// Take out the total number of sheet s
// =====Generate filling data from multiple sheet s=====
int index = 0;
while (index <= (sheetNo == 0.0 ? sheetNo : sheetNo - 1)) {
SXSSFSheet sheet = POIUtils.newSXSSFSheet(wb, sheetName + (index == 0 ? "" : "_" + index));
// Create header
SXSSFRow headerRow = POIUtils.newSXSSFRow(sheet, 0);
for (int i = 0; i < exportItems.size(); i++) {
SXSSFCell cell = POIUtils.newSXSSFCell(headerRow, i);
POIUtils.setColumnWidth(sheet, i, exportItems.get(i).getWidth(), exportItems.get(i).getDisplay());
cell.setCellValue(exportItems.get(i).getDisplay());
CellStyle style = handler.headCellStyle(wb);
if (style != null) {
cell.setCellStyle(style);
}
}
SXSSFRow bodyRow;
String cellValue;
SXSSFCell cell;
CellStyle style = wb.createCellStyle();
Font font = wb.createFont();
style.setFont(font);
// Generate data row
if (!data.isEmpty()) {
int startNo = index * mMaxSheetRecords;
int endNo = Math.min(startNo + mMaxSheetRecords, data.size());
int i = startNo;
while (i < endNo) {
bodyRow = POIUtils.newSXSSFRow(sheet, i + 1 - startNo);
for (int j = 0; j < exportItems.size(); j++) {
// Process cell values
cellValue = exportItems.get(j).getReplace();
if ("".equals(cellValue)) {
try {
cellValue = BeanUtils.getProperty(data.get(i), exportItems.get(j).getField());
} catch (Exception e) {
log.error(e.getMessage());
}
}
// Cell width
POIUtils.setColumnWidth(sheet, j, exportItems.get(j).getWidth(), cellValue);
cell = POIUtils.newSXSSFCell(bodyRow, j);
// fix: when the value is' ', the cell of the current index will become invalid
cell.setCellValue("".equals(cellValue) ? null : cellValue);
cell.setCellStyle(style);
}
i++;
}
}
index++;
}
try {
// Generate an Excel file and download it (determine whether to use the browser to download or write directly to the output by whether the response object is empty)
POIUtils.writeByLocalOrBrowser(mResponse, handler.exportFileName(sheetName), wb, out);
} catch (Exception e) {
log.error(e.getMessage());
return false;
}
return true;
}This toExcel method is the main code that really calls poi to generate excel files.
POIUtils.java:
public class POIUtils {
private static Logger log = LoggerFactory.getLogger(POIUtils.class);
// excel
public static final String XLSX_SUFFIX = ".xlsx";
public static final String XLSX_CONTENT_TYPE = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
//public static final String XLSX_CONTENT_TYPE = "application/vnd.ms-excel;charset=UTF-8";
protected POIUtils() {
}
private static final int MDEFAULTROWACCESSWINDOWSIZE = 100;
private static SXSSFWorkbook newSXSSFWorkbook(int rowAccessWindowSize) {
return new SXSSFWorkbook(rowAccessWindowSize);
}
static SXSSFWorkbook newSXSSFWorkbook() {
return newSXSSFWorkbook(MDEFAULTROWACCESSWINDOWSIZE);
}
static SXSSFSheet newSXSSFSheet(SXSSFWorkbook wb, String sheetName) {
return wb.createSheet(sheetName);
}
static SXSSFRow newSXSSFRow(SXSSFSheet sheet, int index) {
return sheet.createRow(index);
}
static SXSSFCell newSXSSFCell(SXSSFRow row, int index) {
return row.createCell(index);
}
/**
* Set cell width (manual / automatic)
*
* @param sheet Workbook object
* @param index Cell index
* @param width Specify the width, - 1 is adaptive
* @param value Adaptation requires cell content to be calculated
*/
static void setColumnWidth(SXSSFSheet sheet, int index, short width, String value) {
if (width == -1 && value != null && !"".equals(value)) {
sheet.setColumnWidth(index, (short) (value.length() * 512));
} else {
width = width == -1 ? 200 : width;
sheet.setColumnWidth(index, (short) (width * 35.7));
}
}
public static String getResourceBasePath() {
// Get the following directory
File path = null;
try {
path = new File(ResourceUtils.getURL("classpath:").getPath());
} catch (FileNotFoundException e) {
// nothing to do
}
if (path == null || !path.exists()) {
path = new File("");
}
String pathStr = path.getAbsolutePath();
// If it is running in eclipse, it is at the same level as the target directory. If jar is deployed to the server, it is at the same level as the jar package by default
pathStr = pathStr.replace("\\target\\classes", "");
return pathStr;
}
static void writeByLocalOrBrowser(HttpServletResponse response, String fileName, SXSSFWorkbook wb, OutputStream out) {
try {
ZipSecureFile.setMinInflateRatio(0L);
if (response != null) {
// The response object is not empty. The response should be downloaded from the browser
response.setContentType(XLSX_CONTENT_TYPE);
response.setHeader("Content-Disposition", "attachment; filename="
+ URLEncoder.encode(String.format("%s%s", fileName, XLSX_SUFFIX), "UTF-8"));
if (out == null) {
out = response.getOutputStream();
}
}
wb.write(out);
out.flush();
out.close();
} catch (Exception e) {
log.error(e.getMessage());
}
}
public static void checkExcelFile(File file) {
if (file == null || !file.exists()) {
throw new IllegalArgumentException("excel file does not exist.");
}
checkExcelFile(file.getAbsolutePath());
}
private static void checkExcelFile(String file) {
if (!file.endsWith(XLSX_SUFFIX)) {
throw new IllegalArgumentException("i 'm sorry,at present ExcelKit Only supported.xlsx Format file.");
}
}
}This is a POI tool class that calls the api of POI.
ExportHandler.java:
public interface ExportHandler {
/**
* Set header style
*
* @param wb
* Current Wordbook object
* @return Processed style
*/
CellStyle headCellStyle(SXSSFWorkbook wb);
/**
* Set the exported file name (no suffix processing required)
*
* @param sheetName
* sheetName
* @return Processed file name
*/
String exportFileName(String sheetName);
}This ExportHandler is an interface that defines two methods.
ResponseBo.java:
public class ResponseBo extends HashMap<String, Object> {
private static final long serialVersionUID = -8713837118340960775L;
// success
private static final Integer SUCCESS = 0;
// warning
private static final Integer WARN = 1;
// Abnormal failure
private static final Integer FAIL = 500;
// Not certified
private static final Integer UNAUTHORIZED = 401;
// Overclocking
private static final Integer OVERCLOCKING = 666;
public ResponseBo() {
put("result", true);
put("code", SUCCESS);
put("msg", "");
}
public static ResponseBo error(Object msg) {
ResponseBo responseBo = new ResponseBo();
responseBo.put("result", false);
responseBo.put("code", FAIL);
responseBo.put("msg", msg);
return responseBo;
}
public static ResponseBo warn(Object msg) {
ResponseBo responseBo = new ResponseBo();
responseBo.put("result", true);
responseBo.put("code", WARN);
responseBo.put("msg", msg);
return responseBo;
}
public static ResponseBo ok(Object msg) {
ResponseBo responseBo = new ResponseBo();
responseBo.put("result",true);
responseBo.put("code", SUCCESS);
responseBo.put("msg", msg);
return responseBo;
}
public static ResponseBo unAuthorized(Object msg) {
ResponseBo responseBo = new ResponseBo();
responseBo.put("result", true);
responseBo.put("code", UNAUTHORIZED);
responseBo.put("msg", msg);
return responseBo;
}
public static ResponseBo overClocking(Object msg) {
ResponseBo responseBo = new ResponseBo();
responseBo.put("result", true);
responseBo.put("code", OVERCLOCKING);
responseBo.put("msg", msg);
return responseBo;
}
public static ResponseBo ok() {
return new ResponseBo();
}
public static ResponseBo error() {
return ResponseBo.error("");
}
@Override
public ResponseBo put(String key, Object value) {
super.put(key, value);
return this;
}
}
It encapsulates an entity class that stores the returned results. You can also change it to the return class in your own project.
DetectInfo.java:
@Data
public class DetectInfo {
public DetectInfo() {
}
private String id;
@ExportConfig(value = "Detection time",convert = "c:com.tycho.tpds.utils.poi.convert.TimeConvert")
private String dtOfPass;
@ExportConfig(value = "license number")
private String carNo;
@ExportConfig(value = "speed(km/h)")
private String trainSpeed;
@ExportConfig(value = "Total weight(t)")
private String totalWeight;
@ExportConfig(value = "Vehicle eccentric load",width = 100)
private String carPartialLoad;
@ExportConfig(value = "Vehicle running status",width=100)
private String runningStatus;
}This is the incoming entity class. Annotated with ExportConfig, value is the corresponding column name in Excel, width is the column width, and if it is not filled in, it is automatic. Convert can customize a time converter to convert the time format of the database into the time format you want to display in Excel.
Let's see how this annotation is defined, exportconfig java:
/**
* Excel Export item configuration
*/
@Retention(RetentionPolicy.RUNTIME)
@Target({ ElementType.FIELD })
public @interface ExportConfig {
/**
* @return The header display name (for example, the id field is displayed as "number") defaults to the field name
*/
String value() default "field";
/**
* @return Cell width default - 1 (automatically calculate column width)
*/
short width() default -1;
/**
* Convert cell values before exporting: < br / >
* Currently, the following scenarios are supported: < br / >
* 1. Convert a fixed value into a string value (e.g. 1 for male and 2 for female) < br / >
* <b>Expression: < / b > "s: 1 = male, 2 = female" < br / >
*
* 2. The value corresponding to the value can only be mapped by querying the database (implementing the com.tycho.tads.utils.poi.convert.ExportConvert interface) < br / >
*
* @return Not enabled by default
*/
String convert() default "";
/**
* @return Font color of the current cell (default HSSFColor.BLACK.index)
*/
short color() default HSSFColor.BLACK.index;
/**
* Replace the cell value with the currently configured value: < br / >
* Application scenario: < br / >
* The password field is exported as: *******
*
* @return Default true
*/
String replace() default "";
}Let's see how TimeConvert is defined:
public class TimeConvert implements ExportConvert {
private Logger log = LoggerFactory.getLogger(this.getClass());
@Override
public String handler(Object val) {
try {
if (val == null)
return "";
else {
return DateUtil.formatCSTTime(val.toString(), "yyyy-MM-dd HH:mm:ss");
}
} catch (Exception e) {
log.error("Time conversion exception", e);
return "";
}
}
}In fact, it is a class with handler method, which receives the time object of the element in the queried data set and returns the converted String for display in Excel.
Add its interface ExportConvert:
public interface ExportConvert {
String handler(Object val);
}Finally, let's see how the front end requests. I use Vue+axois to implement it here. If you are ajax, you can change it accordingly:
downloadFileRequest("/file/exportReport",param).then(resp=> {
let data = resp.data;
if (!data) {
return;
}
let url = window.URL.createObjectURL(new Blob([data]));
let link = document.createElement('a');
link.style.display = 'none';
link.href = url;
link.setAttribute('download', _global.reportTitle+'.pdf');
document.body.appendChild(link);
link.click();
});The main thing is to return to the, wrap data with Blob object, and then create a url. Then put the url in the created hidden a tag, and finally click the a tag to automatically pop up the download box. If the exported data is large, the page will have obvious waiting time. It is suggested to add a mask layer and load animation, which is more user-friendly.
Let's see how downloadFileRequest is encapsulated:
export const downloadFileRequest = (url, params) => {
return axios({
method: 'post',
url: `${base}${url}`,
data: params,
responseType:'blob'
});
};Just change the responseType to blob.
The introduction is over!
If you think the article is well written, you might as well like it. If you have any doubts or mistakes, you can leave a comment and I will reply in time. All attention will be turned back!