“This article has participated in the call for good writing activities, click to view: the back end, the big front end double track submission, 20,000 yuan prize pool waiting for you to challenge!”
Apache POI
Apache POI is an Open source project that handles a variety of file formats based on the Office Open XML standard (OOXML) and Microsoft’s OLE2 Composite Document Format (OLE2). Simply put, it can read and write Excel through Java programs.
The module
HSSF – Provides the ability to read and write Microsoft Excel XLS files (Microsoft Excel 97 (-2003)).
XSSF – Provides the ability to read and write Microsoft Excel OOXML XLSX format (Microsoft Excel XML (2007+)) files. SXSSF – Provides low memory usage for reading and writing Microsoft Excel OOXML XLSX files. HWPF – Reads and writes Microsoft Word DOC97 files (Microsoft Word 97 (-2003)). XWPF – Provides the ability to read and write Microsoft Word DOC2003 format (Word ProcessingML (2007+)) files. HSLF/XSLF – Provides the function of reading and writing Microsoft PowerPoint files. HDGF/XDGF – Provides the ability to read Microsoft Visio files. HPBF – Provides the ability to read files in Microsoft Publisher format. HSMF – Provides the function of reading Microsoft Outlook files.
This article mainly introduces XSSF(table) import and export, the most commonly used in the work
SpringBoot + Swagger + MyBatis-Plus
- Pom files introduce POI dependencies
<! -- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>5.0. 0</version>
</dependency>
Copy the code- The tables used in this article
create table boot_user
(
user_id int auto_increment comment 'user ID' primary key,
user_name varchar(32) null comment 'Username',
user_sex int default 0 null comment 'Gender (0: male,1: female)',
user_age int null comment 'age',
user_password varchar(32) null comment 'password',
user_status int default 0 null comment 'Status (0: enabled,1 disabled)'
)
comment 'User table';
Copy the code- Export utility class
/** * Export tool class *@Author: CTW
* @Date: create in 2021/7/24
*/
public class ExportUtils {
public static void ExportBootUser(HttpServletResponse response, String sheetName, String[] title, List<BootUser> bootUserList) {
// Create a new document instance
XSSFWorkbook workbook = new XSSFWorkbook();
// Add the form to the document
XSSFSheet sheet = workbook.createSheet(sheetName);
// Create a cell format and center it
XSSFCellStyle style = workbook.createCellStyle();
XSSFCellStyle style2 = workbook.createCellStyle();
style.setAlignment(HorizontalAlignment.CENTER);
style2.setAlignment(HorizontalAlignment.CENTER);
// Title border
/ / bottom border
style.setBorderBottom(BorderStyle.THICK);
/ / the left margin
style.setBorderLeft(BorderStyle.THICK);
/ / right border
style.setBorderRight(BorderStyle.THICK);
/ / on the border
style.setBorderTop(BorderStyle.THICK);
// Plain border
/ / bottom border
style2.setBorderBottom(BorderStyle.THIN);
/ / the left margin
style2.setBorderLeft(BorderStyle.THIN);
/ / right border
style2.setBorderRight(BorderStyle.THIN);
/ / on the border
style2.setBorderTop(BorderStyle.THIN);
// Title font
XSSFFont font = workbook.createFont();
font.setFontName("Imitation song dynasty style typeface _GB2312");
font.setBold(true);
font.setFontHeightInPoints((short) 14);
font.setColor(Font.COLOR_RED);
style.setFont(font);
// Create the first line to populate the title
XSSFRow titleRow = sheet.createRow(0);
// Fill in the header title
for (int i = 0; i < title.length; i++) {
sheet.setColumnWidth(i, 18 * 256);
// Create a cell
XSSFCell cell = titleRow.createCell(i);
// Set cell contents
cell.setCellValue(title[i]);
// Set the cell style
cell.setCellStyle(style);
}
// Fill in the content
/ / line number
int i = 1;
XSSFRow row;
for (BootUser bootUser : bootUserList) {
/ / create a line
row = sheet.createRow(i);
// Create a cell
XSSFCell cell0 = row.createCell(0);
// Set cell contents
cell0.setCellValue(bootUser.getUserName());
// Set the cell style
cell0.setCellStyle(style2);
// Create a cell
XSSFCell cell1 = row.createCell(1);
// Set cell contents
cell1.setCellValue(bootUser.getUserSex() == 0 ? "Male" : "Female");
// Set the cell style
cell1.setCellStyle(style2);
// Create a cell
XSSFCell cell2 = row.createCell(2);
// Set cell contents
cell2.setCellValue(bootUser.getUserAge());
// Set the cell style
cell2.setCellStyle(style2);
// Create a cell
XSSFCell cell3 = row.createCell(3);
// Set cell contents
cell3.setCellValue(bootUser.getUserPassword());
// Set the cell style
cell3.setCellStyle(style2);
// Create a cell
XSSFCell cell4 = row.createCell(4);
// Set cell contents
cell4.setCellValue(bootUser.getUserStatus() == 0 ? "Enable" : "Disabled");
// Set the cell style
cell4.setCellStyle(style2);
i++;
}
// Declare the output stream
OutputStream outputStream = null;
// Respond to the client
try {
// Table file name
String fileName = sheetName + ".xlsx";
// Set the response header
response.setContentType("application/octet-stream; charset=UTF-8");
response.setHeader("Content-Disposition"."attachment; filename=" + URLEncoder.encode(fileName, "UTF-8"));
// Get the output stream
outputStream = response.getOutputStream();
// Write the output stream with the document
workbook.write(outputStream);
// Refresh the output stream
outputStream.flush();
} catch (Exception e) {
e.printStackTrace();
} finally {
// Close the output stream
if(outputStream ! =null) {
try {
outputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
}
Copy the code- Importing utility classes
/** * Import tool class *@Author: CTW
* @Date: create in 2021/7/24
*/
public class ImportUtils {
public static void ImportBootUser(MultipartFile file, BootUserService bootUserService) {
XSSFWorkbook workBook = null;
try (InputStream inputStream = file.getInputStream()) {
// Read the file stream
workBook = new XSSFWorkbook(inputStream);
// Read the worksheet
XSSFSheet sheet = workBook.getSheetAt(0);
for (int i = 1; i <= sheet.getLastRowNum(); i++) {
/ / read
XSSFRow row = sheet.getRow(i);
// Read the cell
XSSFCell cell0 = row.getCell(0);
XSSFCell cell1 = row.getCell(1);
XSSFCell cell2 = row.getCell(2);
XSSFCell cell3 = row.getCell(3);
XSSFCell cell4 = row.getCell(4);
// Set the cell type
cell2.setCellType(CellType.STRING);
cell3.setCellType(CellType.STRING);
BootUser bootUser = new BootUser();
bootUser.setUserName(cell0.getStringCellValue());
bootUser.setUserSex("Male".equals(cell1.getStringCellValue().trim()) ? 0 : 1);
bootUser.setUserAge((int) Float.parseFloat(cell2.getStringCellValue()));
bootUser.setUserPassword(cell3.getStringCellValue());
bootUser.setUserStatus("Enable".equals(cell4.getStringCellValue().trim()) ? 0 : 1);
// Save user databootUserService.save(bootUser); }}catch (IOException e) {
e.printStackTrace();
} finally {
if(workBook ! =null) {
try {
workBook.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
}
Copy the code- Control layer
@API (tags = "user interface ")
@RestController
@RequestMapping("/boot-user")
public class BootUserController {
@Resource
private BootUserService bootUserService;
@apiOperation (" Query all users ")
@GetMapping("bootUserList")
public R<List<BootUser>> bootUserList() {
return R.ok(bootUserService.list());
}
@apiOperation (" Export user data ")
@GetMapping("exportBootUser")
public void exportBootUser(HttpServletResponse response) {
String sheetName = "User Data";
String[] title = {"Username"."Gender"."Age"."Password"."State"};
List<BootUser> bootUserList = bootUserService.list();
ExportUtils.ExportBootUser(response, sheetName, title, bootUserList);
}
@apiOperation (" Import user data ")
@PostMapping("importBootUser")
public void importBootUser(@RequestParam("file") MultipartFile file) { ImportUtils.ImportBootUser(file, bootUserService); }}Copy the code- Export effect
