The project needs to read excel, get in touch with poi, and use it. Summarize yourself
Here, a small demo is written, which is a SpringBoot project. Its main function is to read a total curriculum and then generate the curriculum of the specified class we need
Let's take a look at our master schedule first
easyexcel
When the amount of excel data is large, the speed has obvious advantages
Key codes:
Guide Package:
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.1.1</version>
</dependency>
<!--xls-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
Data entity class (add @ ExcelProperty annotation to the attributes that need to participate in import and export, where the value of the value attribute represents the column name at the time of export, and the value of the index is the subscript of the column, starting from 0. There is an interval between morning and afternoon, so it must be set here in order to read accurately):
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;
import lombok.experimental.Accessors;
@Data
//@Accessors(chain = true)
public class DemoData {
//Set excel header name
@ExcelProperty(value = "date",index = 0)
private String DateTime;
@ExcelProperty(value = "week",index = 1)
private String week;
@ExcelProperty(value = "place",index = 2)
private String AMClassroom;
@ExcelProperty(value = "1,2 class hour",index = 3)
private String hourOneAndTwo;
@ExcelProperty(value = "3,4 class hour",index = 4)
private String hourThreeAndFour;
@ExcelProperty(value = "teacher",index = 5)
private String AMTeacher;
@ExcelProperty(value = "class",index = 6)
private String AMGrade;
@ExcelProperty(value = "place",index = 8)
private String PMClassroom;
@ExcelProperty(value = "5,6 class hour",index = 9)
private String hourFiveAndSix;
@ExcelProperty(value = "7,8 class hour",index = 10)
private String hourSevenAndEight;
@ExcelProperty(value = "teacher",index = 11)
private String PMTeacher;
@ExcelProperty(value = "class",index = 12)
private String PMGrade;
@ExcelProperty(value = "Evening (18:30)-20:10)",index = 13)
private String night;
}
To create an excel event analyzer:
import javax.swing.filechooser.FileSystemView;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class ExcelListener extends AnalysisEventListener<DemoData> {
//A collection of class timetables to print
private List<DemoData> list = new ArrayList<>();
//The ID that must be saved in the first row is the column header
private boolean flag = true;
//Class name in the schedule to generate the schedule
public static String grade;
//Header
private String head;
//Week temporary storage
private String week;
//The class is temporarily stored in the evening self-study classroom
private Map<String,String> night=new HashMap<>();
//Read header content
@Override
public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
//Header, there is only one header of the timetable here
System.out.println("Header:" + headMap);
head = headMap.get(0).substring(0, headMap.get(0).length() - 3);
}
//Read excel content line by line
@Override
public void invoke(DemoData data, AnalysisContext analysisContext) {
System.out.println("****" + data);
//Read of column header
if ((!StringUtils.isEmpty(data.getWeek())) && flag) {
list.add(data);
flag = false;
return;
}
//Temporary storage of week and assignment of week in data
if (StringUtils.isEmpty(data.getWeek())) {
data.setWeek(week);
} else {
week = data.getWeek();
}
//Read the evening self-study classroom of this class on this day of the week
if (!StringUtils.isEmpty(data.getNight())) {
if (data.getNight().contains(grade) || grade.contains(data.getNight())) {
night.put(data.getWeek(), data.getAMClassroom());
}
}
data.setNight(null);//After reading, there is no need to exist. Fill in the method after reading as a whole
//Just look for the class schedule we need
if (grade.equals(data.getAMGrade()) || grade.equals(data.getPMGrade())) {
//The distinction between half day and half day. If there is this class on this day, but a half day is not this class, the half day will be emptied directly
if (!grade.equals(data.getAMGrade())) {
data.setAMClassroom(null);
data.setHourOneAndTwo(null);
data.setHourThreeAndFour(null);
data.setAMTeacher(null);
data.setAMGrade(null);
}
if (!grade.equals(data.getPMGrade())) {
data.setPMClassroom(null);
data.setHourFiveAndSix(null);
data.setHourSevenAndEight(null);
data.setPMTeacher(null);
data.setPMGrade(null);
}
//If the date is the same as the previous data, merge with the previous data
if (list.get(list.size()-1).getWeek().equals(data.getWeek())) {
DemoData oldData = list.get(list.size() - 1);//Merged target data
//Merge into the morning
if (StringUtils.isEmpty(oldData.getAMGrade())) {
oldData.setAMClassroom(data.getAMClassroom());
oldData.setHourOneAndTwo(data.getHourOneAndTwo());
oldData.setHourThreeAndFour(data.getHourThreeAndFour());
oldData.setAMTeacher(data.getAMTeacher());
oldData.setAMGrade(data.getAMGrade());
}else {//Merge into the afternoon
oldData.setPMClassroom(data.getPMClassroom());
oldData.setHourFiveAndSix(data.getHourFiveAndSix());
oldData.setHourSevenAndEight(data.getHourSevenAndEight());
oldData.setPMTeacher(data.getPMTeacher());
oldData.setPMGrade(data.getPMGrade());
}
list.set(list.size() - 1, oldData);
return;
}
list.add(data);
}
}
//After reading
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
//Write after reading
// Get desktop path
FileSystemView fsv = FileSystemView.getFileSystemView();
String desktop = fsv.getHomeDirectory().getPath();
String filePath = desktop + "\\" + grade + " " + head + ".xls";//C:\Users\fuck\Desktop\T119 on 9.13-9.18.xls
//Fill in the evening self-study
for (String wk : night.keySet()) {
for (DemoData data : list) {
if (data.getWeek().equals(wk)) {
data.setNight(night.get(wk));
}
}
}
System.out.println(filePath);
list.forEach(list -> System.out.println(list));
//write in
EasyExcel.write(filePath, DemoData.class).sheet("Student list").doWrite(list);
}
}
Summary: the reading of excel file header, the reading of conventional lines and the writing of excel files are all implemented in the rewriting method in this class. The characteristics of reading data: reading line by line
excelpoi
Implementation mode
The dependency of the import has changed, and the annotation used has also changed
It is as like as two peas in the general schedule.
Moreover, I don't know why I can't read the data of some columns. If I use the template to generate, if there is a one to many relationship, which involves the consolidation of cells, the exported table will report an error even when it is opened, and finally give up. I'm sorry to omit it here
poi
The most primitive method has a high degree of freedom. It should be the most appropriate choice for this complex table
Import dependency:
<!-- poi -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
Read (idea: get the worksheet, get the sheet, and traverse the row and cell of the sheet):
/**
* Read the master timetable
*
* @param schedule
* @param list
* @throws Exception
*/
public String timeTableRead(Schedule schedule, List<ScheduleVo> list) throws Exception {
//read
if (!ObjectUtils.isEmpty(schedule.getMultipartFile())) {
//One day's course
ScheduleVo scheduleVo = new ScheduleVo();
//file name
String fileName = "";
//MultipartFile to inputStream
InputStream inputStream = schedule.getMultipartFile().getInputStream();
Workbook sheets = new XSSFWorkbook(inputStream);//Create worksheet
//Get a worksheet (sheet page) with subscripts starting from 0
Sheet sheet = sheets.getSheetAt(0);
//When traversing the row, the subscripts of sheet, row and cell start from 0. The number of rows is 313. sheet.getLastRowNum() also gets 313, so i can be equal to rowNum
for (int i = 0, rowNum = sheet.getLastRowNum(); i <= rowNum; i++) {
// Get row content
Row row = sheet.getRow(i);
if (ObjectUtils.isEmpty(row)) {
//If the knowledge has only one behavior null, continue to read the next line
continue;
}
//Prevent row missing column! = null, but read the specified column with a null pointer and a row missing cell
if (row.getPhysicalNumberOfCells() < 13) {
continue;
}
//Read column
//Read header
if (i == 0) {
String title = row.getCell(0).getStringCellValue();
fileName = schedule.getName() + " " + title.substring(0, title.length() - 3) + "Timetable";
} else if (i > 1) {
//There is nothing to read at the head of the second row and column. Read directly from the third row
//If you have time, it means that this is the beginning of a new day. Rewrite and instantiate the timetable object
String dateTime = row.getCell(0).getStringCellValue();
if (!StringUtils.isEmpty(dateTime)) {
if (!StringUtils.isEmpty(scheduleVo.getDateTime())) {//The new day starts to add the data read from the old day to the collection
list.add(scheduleVo);
}
scheduleVo = new ScheduleVo();
scheduleVo.setDateTime(dateTime.substring(5));//Set time
scheduleVo.setWeek(row.getCell(1).getStringCellValue());//Set week
}
//If column 7 is the class to be searched, add the data of the specified column in this row to the object
String clazz = row.getCell(6).getStringCellValue();
if (clazz.equals(schedule.getName())) {
Cell cell = row.getCell(2);
cell.setCellType(CellType.STRING);
scheduleVo.setAmClassroom(cell.getStringCellValue());//Read morning locations
scheduleVo.setAmCourse(row.getCell(3).getStringCellValue());//Read the morning course
scheduleVo.setAmTeacher(row.getCell(5).getStringCellValue());//Read the morning teacher
}
//If column 13 is the class to be searched, add the data of the specified column in this row to the object
clazz = row.getCell(12).getStringCellValue();
if (clazz.equals(schedule.getName())) {
Cell cell = row.getCell(8);
cell.setCellType(CellType.STRING);
scheduleVo.setPmClassroom(cell.getStringCellValue());//Read afternoon locations
scheduleVo.setPmCourse(row.getCell(9).getStringCellValue());//Read the afternoon course
scheduleVo.setPmTeacher(row.getCell(11).getStringCellValue());//Read the afternoon teacher
}
//If column 14 is the class to be searched, add the data of the specified column in this row to the object
clazz = row.getCell(13).getStringCellValue();
if ((!StringUtils.isEmpty(clazz)) && schedule.getName().contains(clazz)) {//There is a night self-study on this day
Cell cell = row.getCell(2);
cell.setCellType(CellType.STRING);
scheduleVo.setNight(cell.getStringCellValue());//Save the evening study location to the evening study field
}
//If it is the last row, it will be directly added to the collection after reading
if (i == rowNum) {
list.add(scheduleVo);
}
}
}
//Closed flow
sheets.close();
inputStream.close();
return fileName;
} else {
throw new Exception("The table is empty,Please select a table!");
}
}
The table shows that the classroom was originally arranged to be separated from Monday and Tuesday for beauty:
However, the table I encountered did not know what the table did, which led to the lack of cell
This is a pit encountered. I don't know the cause. Those who know are welcome to give advice.
The temporary solution is to judge the number of cell s in row. If the weight is less than two, read the next line:
//Prevent row missing column! = null, but read the specified column with a null pointer and a row missing cell
if (row.getPhysicalNumberOfCells() < 13) {
continue;
}
Write (idea: create worksheet, create sheet, create row of sheet, create cell of row):
/**
* Generate lesson schedule on desktop
*
* @param schedule
* @param list
*/
public void timeTableWrite(Schedule schedule, List<ScheduleVo> list) throws IOException {
//Creating a workbook is similar to creating an Excel file
HSSFWorkbook workbook = new HSSFWorkbook();
//Create a sheetname page name, and directly use the time of the timetable set to make a splice to make the page name
HSSFSheet sheet = workbook.createSheet(list.get(0).getDateTime() + "-" + list.get(list.size() - 1).getDateTime());
//Create a header row with subscripts starting from 0
HSSFRow row = sheet.createRow(0);
//Cell merging - merging columns (merging does not require the creation of rows or columns as a prerequisite)
CellRangeAddress region = new CellRangeAddress(0, 0, 0, 9);
sheet.addMergedRegion(region);
//Create a column in this row and assign a direct value to it. The header row has only one column
row.createCell(0).setCellValue(schedule.getName() + " Timetable");
//However, setting the cell style requires the creation of a cell as a prerequisite, otherwise an error will be reported
for (int i = 1; i <= 9; i++) {
row.createCell(i);
}
//Create a style for the schedule Title row
CellStyle cellStyle = workbook.createCellStyle();
cellStyle.setAlignment(HorizontalAlignment.CENTER);//horizontally
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//Vertical center
cellStyle.setBorderBottom(BorderStyle.THIN);//Set borders up, down, left and right one by one, with thin lines
cellStyle.setBorderLeft(BorderStyle.THIN);
cellStyle.setBorderTop(BorderStyle.THIN);
cellStyle.setBorderRight(BorderStyle.THIN);//The border color of the cell defaults to black
//Set background color
// cellStyle.setFillForegroundColor(IndexedColors.BLUE1.getIndex());
// Cellstyle. Setfillpattern (fillpatterntype. Solid_foreround); / / if the filling mode is not set, the background color will not go up
//To add a style to a column, not only the first cell needs a border, but all cells participating in the merge need a border
for (Cell cell : row) {
cell.setCellStyle(cellStyle);
}
//Create column header row
row = sheet.createRow(1);
row.createCell(0).setCellValue("date");
row.createCell(1).setCellValue("week");
row.createCell(2).setCellValue("place");
row.createCell(3).setCellValue("morning");
row.createCell(4).setCellValue("teacher");
row.createCell(5).setCellValue("");
row.createCell(6).setCellValue("place");
row.createCell(7).setCellValue("afternoon");
row.createCell(8).setCellValue("teacher");
row.createCell(9).setCellValue("Evening self study classroom");
//Set column header style
cellStyle = workbook.createCellStyle();
cellStyle.setAlignment(HorizontalAlignment.CENTER);//horizontally
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//Vertical center
cellStyle.setBorderBottom(BorderStyle.THIN);//Set borders up, down, left and right one by one, with thin lines
cellStyle.setBorderLeft(BorderStyle.THIN);
cellStyle.setBorderTop(BorderStyle.THIN);
cellStyle.setBorderRight(BorderStyle.THIN);//The border color of the cell defaults to black
//To add a style to a column, not only the first cell needs a border, but all cells participating in the merge need a border
for (Cell cell : row) {
cell.setCellStyle(cellStyle);
}
//Start traversing the timetable information set to create columns
for (int i = 0; i < list.size(); i++) {
row = sheet.createRow(i + 2);//The header behavior is 0 and the column header is 1, so i+2
row.createCell(0).setCellValue(list.get(i).getDateTime());
row.createCell(1).setCellValue(list.get(i).getWeek());
row.createCell(2).setCellValue(list.get(i).getAmClassroom());
row.createCell(3).setCellValue(list.get(i).getAmCourse());
row.createCell(4).setCellValue(list.get(i).getAmTeacher());
//If you are traversing the course collection for the first time, you need to merge rows, that is, the lunch break column in the timetable
if (i == 0) {
region = new CellRangeAddress(2, list.size() + 1, 5, 5);
sheet.addMergedRegion(region);
row.createCell(5).setCellValue("noon break");
}
row.createCell(6).setCellValue(list.get(i).getPmClassroom());
row.createCell(7).setCellValue(list.get(i).getPmCourse());
row.createCell(8).setCellValue(list.get(i).getPmTeacher());
row.createCell(9).setCellValue(list.get(i).getNight());
//Add special styles to traverse columns. Different styles need to be set for different columns
//Style, common to all columns
cellStyle = workbook.createCellStyle();
cellStyle.setAlignment(HorizontalAlignment.CENTER);//horizontally
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//Vertical center
cellStyle.setBorderBottom(BorderStyle.THIN);//Set borders up, down, left and right one by one, with thin lines
cellStyle.setBorderLeft(BorderStyle.THIN);
cellStyle.setBorderTop(BorderStyle.THIN);
cellStyle.setBorderRight(BorderStyle.THIN);//The border color of the cell defaults to black
for (Cell cell : row) {
cell.setCellStyle(cellStyle);
}
}
//Set path
// Get desktop path
FileSystemView fsv = FileSystemView.getFileSystemView();
String desktop = fsv.getHomeDirectory().getPath();
String filePath = desktop + "/" + fileName + ".xls";
FileOutputStream stream = new FileOutputStream(new File(filePath));
// Write worksheet
workbook.write(stream);
//Closed flow
stream.close();
}
}
Final effect:
