1. Introduce dependencies
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
2. Simple export implementation
package cn.sto.station.twin.common.excel;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.MapUtils;
import org.apache.commons.lang3.ArrayUtils;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.util.IOUtils;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import java.io.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
/**
* SXSSFWorkbook export
*
* @date 2022-05-30 10:50
*/
@Slf4j
public class SXSSFWorkbookExporter {<!-- -->
/**
* Header
*/
private String[] headerNames;
/**
* work log
*/
private Workbook workBook;
/**
* excel worksheet
*/
private sheet;
/**
*
* @param headerNames header
* @param sheetName The name of the sheet
*/
public SXSSFWorkbookExporter(String[] headerNames, String sheetName) {<!-- -->
this.headerNames = headerNames;
//Create a workbook, keep 100 pieces of data in the memory, and refresh the excess to the disk
workBook = new SXSSFWorkbook(100);
//Create a worksheet
sheet = workBook.createSheet(sheetName);
initHeader();
}
/**
* Initialize header information
*/
private void initHeader() {<!-- -->
//Create the first row
Row row = sheet.createRow(0);
Cell cell = null;
//Create header
for (int i = 0; i < headerNames.length; i + + ) {<!-- -->
cell = row.createCell(i);
cell.setCellValue(headerNames[i]);
setHeaderCellStyle(cell);
}
}
/**
* Set header cell style
*
* @param cell cell
*/
public void setHeaderCellStyle(Cell cell) {<!-- -->
//Set style
CellStyle cellStyle = workBook.createCellStyle();
// Set the font to center
cellStyle.setAlignment(HorizontalAlignment.CENTER);
//Set font
Font font = workBook.createFont();
font.setFontName(HSSFFont.FONT_ARIAL);
// Set bold
font.setBold(true);
//Set font size
font.setFontHeightInPoints((short) 13);
cellStyle.setFont(font);
cell.setCellStyle(cellStyle);
}
/**
*
* @param datas data, each map is a row
* @param keys key[i] represents getting the value of keys[i] from the map as the value of the i-th column. If null is passed, the table header will be taken by default.
*/
public void createTableRows(List<Map<String, Object>> datas, String[] keys) {<!-- -->
for (int i = 0, length_1 = datas.size(); i < length_1; i + + ) {<!-- -->
if (ArrayUtils.isEmpty(keys)) {<!-- -->
keys = headerNames;
}
// Create rows (starting from the second row)
Map<String, Object> data = datas.get(i);
Row row = sheet.createRow(i + 1);
Cell cell = null;
for (int j = 0, length_2 = keys.length; j < length_2; j + + ) {<!-- -->
//The cell gets the key in the map
String key = keys[j];
String value = MapUtils.getString(data, key, "");
cell = row.createCell(j);
cell.setCellType(CellType.STRING);
cell.setCellValue(value);
}
}
}
/**
* Automatically adjust column width according to header
*/
public void autoAllSizeColumn() {<!-- -->
// If it is SXSSFSheet, you need to call the trackAllColumnsForAutoSizing method once
if (sheet instanceof SXSSFSheet) {<!-- -->
SXSSFSheet tmpSheet = (SXSSFSheet) sheet;
tmpSheet.trackAllColumnsForAutoSizing();
}
for (int i = 0, length = headerNames.length; i < length; i + + ) {<!-- -->
sheet.autoSizeColumn(i);
}
}
/**
* Write data to excel
*
* @param outputStream
*/
public void exportExcel(OutputStream outputStream) {<!-- -->
// Automatically set column width before exporting
this.autoAllSizeColumn();
try {<!-- -->
workBook.write(outputStream);
} catch (IOException e) {<!-- -->
log.error(" exportExcel error", e);
} finally {<!-- -->
IOUtils.closeQuietly(outputStream);
}
}
/**
* Write data to excel
*
* @param outputFilePath
*/
public void exportExcel(String outputFilePath) {<!-- -->
// Automatically set column width before exporting
this.autoAllSizeColumn();
FileOutputStream outputStream = null;
try {<!-- -->
outputStream = new FileOutputStream(outputFilePath);
workBook.write(outputStream);
} catch (IOException e) {<!-- -->
log.error(" exportExcel error", e);
} finally {<!-- -->
IOUtils.closeQuietly(outputStream);
}
}
public static void main(String[] args) {<!-- -->
test();
}
private static void test() {<!-- -->
SXSSFWorkbookExporter hssfWorkExcel = new SXSSFWorkbookExporter(new String[] {<!-- --> "Name", "Age" }, "Basic information of personnel");
List<Map<String, Object>> datas = new ArrayList<>();
for (int i = 0; i < 10; i + + ) {<!-- -->
Map data = new HashMap<>();
data.put("name", "name" + i);
data.put("age", "age" + i);
datas.add(data);
}
hssfWorkExcel.createTableRows(datas, new String[] {<!-- --> "name", "age" });
try {<!-- -->
hssfWorkExcel.exportExcel(new FileOutputStream(new File("e:/test1.xlsx")));
} catch (FileNotFoundException e) {<!-- -->
e.printStackTrace();
}
}
}
3. Customized template export
public void customizationExcel(Map<String, String> dateMap, List<Map<String, String>> dateMapList, HttpServletResponse response) {<!-- -->
try {<!-- -->
//New workbook
SXSSFWorkbook workbook = new SXSSFWorkbook(100);
Sheet sheet = workbook.createSheet();//workbook name
//Set the style of each grid of data
Font ParamFontStyle = workbook.createFont();
CellStyle cellParamStyle = workbook.createCellStyle();
cellParamStyle.setAlignment(HorizontalAlignment.CENTER);//Vertically centered
cellParamStyle.setVerticalAlignment(VerticalAlignment.CENTER);//Horizontally centered
cellParamStyle.setWrapText(false);//Automatically wrap lines
ParamFontStyle.setFontHeightInPoints((short) 11);//Font size
ParamFontStyle.setFontName("宋体");
cellParamStyle.setFont(ParamFontStyle);
//Set the header style
Font ParamFontStyle1 = workbook.createFont();
CellStyle cellParamStyle1 = workbook.createCellStyle();
cellParamStyle1.setAlignment(HorizontalAlignment.LEFT);
cellParamStyle1.setVerticalAlignment(VerticalAlignment.DISTRIBUTED);
cellParamStyle1.setWrapText(false);//Automatically wrap lines
ParamFontStyle1.setFontHeightInPoints((short) 15);
ParamFontStyle1.setFontName("黑体");
ParamFontStyle1.setBold(true);//Whether to turn on bold
cellParamStyle1.setFont(ParamFontStyle1);
//Set the style of the title
Font ParamFontStyle2 = workbook.createFont();
CellStyle cellParamStyle2 = workbook.createCellStyle();
cellParamStyle2.setAlignment(HorizontalAlignment.CENTER);//Vertically centered
cellParamStyle2.setVerticalAlignment(VerticalAlignment.CENTER);//Horizontally centered
cellParamStyle2.setWrapText(true);//Automatically wrap lines
ParamFontStyle2.setFontHeightInPoints((short) 11);
ParamFontStyle2.setFontName("黑体");
ParamFontStyle2.setBold(true);
cellParamStyle2.setFont(ParamFontStyle2);
//Set the style of the title
Font ParamFontStyle3 = workbook.createFont();
CellStyle cellParamStyle3 = workbook.createCellStyle();
cellParamStyle3.setAlignment(HorizontalAlignment.CENTER);//Vertically centered
cellParamStyle3.setVerticalAlignment(VerticalAlignment.CENTER);//Horizontally centered
cellParamStyle3.setWrapText(true);//Automatically wrap lines
ParamFontStyle3.setFontHeightInPoints((short) 18);
ParamFontStyle3.setFontName("黑体");
ParamFontStyle3.setBold(true);
cellParamStyle3.setFont(ParamFontStyle3);
//Define the width of the column
//sheet.setDefaultColumnWidth(40 * 1024);Default sheet1.CreateRow(0).Height = 200*20;//Width
Short height = 390;
sheet.setDefaultRowHeight((Short) height);
sheet.setColumnWidth(0, 5000);
sheet.setColumnWidth(1, 5000);
sheet.setColumnWidth(2, 5000);
sheet.setColumnWidth(3, 5000);
sheet.setColumnWidth(4, 5000);
sheet.setColumnWidth(5, 5000);
sheet.setColumnWidth(6, 5000);
sheet.setColumnWidth(7, 5000);
sheet.setColumnWidth(8, 5000);
sheet.setColumnWidth(9, 5000);
sheet.setColumnWidth(10, 5000);
sheet.setColumnWidth(11, 5000);
sheet.setColumnWidth(12, 5000);
//date
Row rows1 = sheet.createRow(0);
Cell cell1 = rows1.createCell(0);
Cell cell7 = rows1.createCell(1);
//Department
Cell cell2 = rows1.createCell(2);
Cell cell8 = rows1.createCell(3);
//Shift
Cell cell3 = rows1.createCell(6);
Cell cell9 = rows1.createCell(7);
//time
Cell cell4 = rows1.createCell(8);
Cell cell10 = rows1.createCell(9);
//Ships throughout the day
Row rows5 = sheet.createRow(1);
Cell cell5 = rows5.createCell(0);
Cell cell11 = rows5.createCell(1);
//day shift
Row rows6 = sheet.createRow(2);
Cell cell6 = rows6.createCell(0);
Cell cell12 = rows6.createCell(1);
//Merge Cells
CellRangeAddress region = new CellRangeAddress(0, 0, 3, 5);
sheet.addMergedRegion(region);
CellRangeAddress region1 = new CellRangeAddress(1, 1, 1, 12);
sheet.addMergedRegion(region1);
CellRangeAddress region2 = new CellRangeAddress(2, 2, 1, 12);
sheet.addMergedRegion(region2);
CellRangeAddress region3 = new CellRangeAddress(0, 0, 9, 12);
sheet.addMergedRegion(region3);
//Title cell style
cell1.setCellStyle(cellParamStyle2);
cell2.setCellStyle(cellParamStyle2);
cell3.setCellStyle(cellParamStyle2);
cell4.setCellStyle(cellParamStyle2);
cell5.setCellStyle(cellParamStyle2);
cell6.setCellStyle(cellParamStyle2);
//Corresponding value cell style
cell7.setCellStyle(cellParamStyle);
cell8.setCellStyle(cellParamStyle);
cell9.setCellStyle(cellParamStyle);
cell10.setCellStyle(cellParamStyle);
cell11.setCellStyle(cellParamStyle);
cell12.setCellStyle(cellParamStyle);
//Title value
cell1.setCellValue("Shift handover date:");
cell2.setCellValue("Department:");
cell3.setCellValue("Shift:");
cell4.setCellValue("Time:");
cell5.setCellValue("Shifts throughout the day:");
cell6.setCellValue("Shift system:");
//TODO fill data
cell7.setCellValue(dateMap.get("shift date"));
cell8.setCellValue(dateMap.get("Department"));
cell9.setCellValue(dateMap.get("Shift"));
cell10.setCellValue((Objects.equals(dateMap.get("Start Time"), null) ? "" : dateMap.get("Start Time") + " - " + (Objects.equals(dateMap.get("End Time"), null) ? "" : dateMap.get("End Time"))));
cell11.setCellValue(dateMap.get("All-day shift"));
cell12.setCellValue(dateMap.get("Day shift"));
//column name
Row row1 = sheet.createRow(3);
Cell row1Cell0 = row1.createCell(0);
row1Cell0.setCellValue("Category");
row1Cell0.setCellStyle(cellParamStyle2);
Cell row1Cell = row1.createCell(1);
row1Cell.setCellValue("bed number");
row1Cell.setCellStyle(cellParamStyle2);
Cell row1Cell1 = row1.createCell(2);
row1Cell1.setCellValue("name");
row1Cell1.setCellStyle(cellParamStyle2);
Cell row1Cell2 = row1.createCell(3);
row1Cell2.setCellValue("date of birth");
row1Cell2.setCellStyle(cellParamStyle2);
Cell row1Cell3 = row1.createCell(4);
row1Cell3.setCellValue("Admission Date");
row1Cell3.setCellStyle(cellParamStyle2);
Cell row1Cell4 = row1.createCell(5);
row1Cell4.setCellValue("Diagnosis");
row1Cell4.setCellStyle(cellParamStyle2);
Cell row1Cell5 = row1.createCell(6);
row1Cell5.setCellValue("current situation");
row1Cell5.setCellStyle(cellParamStyle2);
Cell row1Cell6 = row1.createCell(7);
row1Cell6.setCellValue("Background");
row1Cell6.setCellStyle(cellParamStyle2);
Cell row1Cell7 = row1.createCell(8);
row1Cell7.setCellValue("Evaluation");
row1Cell7.setCellStyle(cellParamStyle2);
Cell row1Cell8 = row1.createCell(9);
row1Cell8.setCellValue("Suggestion");
row1Cell8.setCellStyle(cellParamStyle2);
Cell row1Cell9 = row1.createCell(10);
row1Cell9.setCellValue("P class");
row1Cell9.setCellStyle(cellParamStyle2);
Cell row1Cell10 = row1.createCell(11);
row1Cell10.setCellValue("Class N");
row1Cell10.setCellStyle(cellParamStyle2);
Cell row1Cell11 = row1.createCell(12);
row1Cell11.setCellValue("Handover/replacement nurse");
row1Cell11.setCellStyle(cellParamStyle2);
//TODO fill data
int sheet1Colume = 0;
for (Map<String, String> hospitalExcel : dateMapList) {<!-- -->
Row row2 = sheet.createRow(sheet1Colume + 4);//The subscript starts from 0, corresponding to excel inserting the list data value from row 4
Cell row2Cell = row2.createCell(0);
row2Cell.setCellStyle(cellParamStyle);
row2Cell.setCellValue(hospitalExcel.get("Category"));
Cell row2Cell1 = row2.createCell(1);
row2Cell1.setCellStyle(cellParamStyle);
row2Cell1.setCellValue(hospitalExcel.get("cw"));
Cell row2Cell2 = row2.createCell(2);
row2Cell2.setCellStyle(cellParamStyle);
row2Cell2.setCellValue(hospitalExcel.get("xm"));
Cell row2Cell3 = row2.createCell(3);
row2Cell3.setCellStyle(cellParamStyle);
row2Cell3.setCellValue(hospitalExcel.get("bir"));
Cell row2Cell4 = row2.createCell(4);
row2Cell4.setCellStyle(cellParamStyle);
row2Cell4.setCellValue(hospitalExcel.get("rysj"));
Cell row2Cell5 = row2.createCell(5);
row2Cell5.setCellStyle(cellParamStyle);
row2Cell5.setCellValue(hospitalExcel.get("zd"));
Cell row2Cell6 = row2.createCell(6);
row2Cell6.setCellStyle(cellParamStyle);
row2Cell6.setCellValue(hospitalExcel.get("xz"));
Cell row2Cell7 = row2.createCell(7);
row2Cell7.setCellStyle(cellParamStyle);
row2Cell7.setCellValue(hospitalExcel.get("bj"));
Cell row2Cell8 = row2.createCell(8);
row2Cell8.setCellStyle(cellParamStyle);
row2Cell8.setCellValue(hospitalExcel.get("pg"));
Cell row2Cell9 = row2.createCell(9);
row2Cell9.setCellStyle(cellParamStyle);
row2Cell9.setCellValue(hospitalExcel.get("jy"));
Cell row2Cell10 = row2.createCell(10);
row2Cell10.setCellStyle(cellParamStyle);
row2Cell10.setCellValue(hospitalExcel.get("Pb"));
Cell row2Cell11 = row2.createCell(11);
row2Cell11.setCellStyle(cellParamStyle);
row2Cell11.setCellValue(hospitalExcel.get("Nb"));
Cell row2Cell12 = row2.createCell(12);
row2Cell12.setCellStyle(cellParamStyle);
row2Cell12.setCellValue((Objects.equals(hospitalExcel.get("jbhs"), null) ? "" : (hospitalExcel.get("jbhs")) + "/" + (Objects.equals(hospitalExcel.get("sbhs "), null) ? "" : hospitalExcel.get("sbhs"))));
sheet1Colume + + ;
}
String fileName = new String(DateUtil.now().getBytes(), "UTF-8") + ".xls";
response.setContentType("application/vnd.ms-excel;charset=UTF-8");
response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
response.setCharacterEncoding("UTF-8");
OutputStream os = response.getOutputStream();
workbook.write(os);
os.flush();
try {<!-- -->
os.close();
} catch (Exception e) {<!-- -->
e.printStackTrace();
}
} catch (Exception ex) {<!-- -->
ex.printStackTrace();
}
}
Export effects!
