1. Some knowledge about Excel version
Two versions are mainly used in the project work. The file name of version 03 ends with ". xls", and the maximum number of processing lines is 65536. The file name of version 07 ends with ". xlsx", and the maximum number of processing lines is 1048576.
2. Related concepts of Excel
Workbook sheet row cells
3. The POI version uses 3.11 to create a maven project to facilitate the import of jar packages
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi --> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.11</version> </dependency> <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml --> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.11</version> </dependency>
4. Three implementation classes of POI operation EXCEL Workbook (implementation interface Workbook)
POI handles Excel operations with different requirements with different implementation classes. xls in version 03 corresponds to HSSFWorkbook, and xlsx in version 07 corresponds to XSSFWorkbook. SXSSFWorkbook is used for writing operations with large amount of data. The general difference is that the maximum number of rows read by HSSFWorkbook is 65536. The speed of reading and writing is relatively fast, but there are disadvantages. More rows of data cannot be written. At this time, XSSFWorkbook comes on stage, but XSSFWorkbook writes data at one time, If the amount of data is large and the memory is not enough, it is easy to cause OOM exceptions. However, SXSSFWorkbook uses the concept of sliding window. By default, it retains up to 100 rows of data in memory. When more than 100 rows, it will write the row with the smallest index to the temporary file, so as to avoid OOM exceptions as much as possible. We can choose the corresponding implementation class according to the actual needs. This explanation uses HSSFWorkbook
5. Correspondence between hssfworkbook common classes and Excel objects
HSSFWorkbook workbook object
HSSFSheet worksheet object
HSSFRow row object
HSSFCell cell object
HSSFCellStyle cell style object
HSSFFont font object
6. Generate header without cell border
In Excel, the default border line is gray. The logic of generating cells without border line is to change the color of border line to white. Because it is demonstration code, some redundant codes are not extracted, so we can deal with them according to our needs.
public static void setSheetHead() {
// File path, the file name of version 03 starts with xls ends, and the file name of version 07 starts with xlsx end
String filePath = "/Users/lavined/Desktop/test01.xls";
FileOutputStream os = null;
// Maximum number of columns
int cellNum = 10;
int cellNumTemp = cellNum - 1;
// Worksheet name
String sheetName = "Student information form";
// Create workbook object
Workbook workbook = new HSSFWorkbook();
// Create worksheet object
Sheet sheet = workbook.createSheet(sheetName);
// Set column width
sheet.setDefaultColumnWidth(13);
// format cell
CellStyle cellStyle = workbook.createCellStyle();
// Border color white
cellStyle.setTopBorderColor(IndexedColors.WHITE.getIndex());
cellStyle.setLeftBorderColor(IndexedColors.WHITE.getIndex());
cellStyle.setRightBorderColor(IndexedColors.WHITE.getIndex());
cellStyle.setBottomBorderColor(IndexedColors.WHITE.getIndex());
// Border Linetype
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //bottom
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//border-left
cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//border-top
cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//border-right
// Because the right border of the last cell will disappear after using the borderless style, set the style for the next cell of the last cell
CellStyle leftCellStyle = workbook.createCellStyle();
leftCellStyle.setLeftBorderColor(IndexedColors.GREY_25_PERCENT.getIndex());
leftCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
// Content centered
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
// Set font
Font font = workbook.createFont();
font.setBold(true);// bold
font.setFontHeightInPoints((short) 14);// Set font size
cellStyle.setFont(font);
// Create row object
Row row = sheet.createRow(0);
for (int i = 0; i < cellNum; i++) {
// Create cell object
Cell cell = row.createCell(i);
if (i == 0) {
cell.setCellValue(sheetName);
}
// Set cell style
cell.setCellStyle(cellStyle);
}
row.createCell(cellNum).setCellStyle(leftCellStyle);
// merge cell
CellRangeAddress region1 = new CellRangeAddress(0, 0, 0, cellNumTemp);
sheet.addMergedRegion(region1);
// One blank line
sheet.createRow(1);
CellRangeAddress region2 = new CellRangeAddress(1, 1, 0, cellNumTemp);
sheet.addMergedRegion(region2);
// Second level header
Row sheetRow2 = sheet.createRow(2);
int lastRemarkCellNum = cellNum - 3;
for (int i = 0; i < cellNum; i++) {
Cell cell = sheetRow2.createCell(i);
if (i == 0) {
cell.setCellValue("Class: Class 1, grade 3");
}
if (i == 3) {
cell.setCellValue("Number of people: 10");
}
if (i == lastRemarkCellNum) {
cell.setCellValue("Created on: 2021-03-01 17:09:47");
}
// Set cell style
cell.setCellStyle(cellStyle);
}
sheetRow2.createCell(cellNum).setCellStyle(leftCellStyle);
// merge cell
CellRangeAddress region3 = new CellRangeAddress(2, 2, 0, 2);
sheet.addMergedRegion(region3);
CellRangeAddress region4 = new CellRangeAddress(2, 2, 3, lastRemarkCellNum -1);
sheet.addMergedRegion(region4);
CellRangeAddress region5 = new CellRangeAddress(2, 2, lastRemarkCellNum, cellNumTemp);
sheet.addMergedRegion(region5);
sheet.createRow(3);
CellRangeAddress region6 = new CellRangeAddress(3, 3, 0, cellNumTemp);
sheet.addMergedRegion(region6);
// Write out files in a stream
try {
os = new FileOutputStream(filePath);
workbook.write(os);
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}finally {
if (os != null) {
try {
os.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
The result code is as follows:
7. Generate data header with background color
public static void setDataHead(){
String filePath = "/Users/lavined/Desktop/test01.xls";
FileOutputStream os = null;
// Worksheet name
String sheetName = "Student information form";
// Create workbook object
Workbook workbook = new HSSFWorkbook();
// Create worksheet object
Sheet sheet = workbook.createSheet(sheetName);
// Set column width
sheet.setDefaultColumnWidth(13);
// format cell
CellStyle cellStyle = workbook.createCellStyle();
// Content centered
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
// Background color
cellStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
// Border color black
cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
cellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
cellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
// Border Linetype
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //bottom
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//border-left
cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//border-top
cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//border-right
// Data header field
Map<Integer,String> dataHeadMap = new HashMap<>();
dataHeadMap.put(0, "class");
dataHeadMap.put(1, "full name");
dataHeadMap.put(2, "Gender");
dataHeadMap.put(3, "Age");
dataHeadMap.put(4, "height");
dataHeadMap.put(5, "weight");
dataHeadMap.put(6, "Home address");
dataHeadMap.put(7, "Meal card balance");
dataHeadMap.put(8, "Note 1");
dataHeadMap.put(9, "Note 2");
// Report data item header style
Row row = sheet.createRow(0);
if (dataHeadMap != null) {
for (Map.Entry<Integer, String> headMap : dataHeadMap.entrySet()) {
Cell cell = row.createCell(headMap.getKey());
cell.setCellValue(headMap.getValue());
if (null != cellStyle) {
cell.setCellStyle(cellStyle);
}
}
}
// Write out files in a stream
try {
os = new FileOutputStream(filePath);
workbook.write(os);
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}finally {
if (os != null) {
try {
os.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
Code result
8. Generate data content
8.1 when generating data content, if it is similar to the form of data header, you can use map < integer, string > to keep the order of key value and column consistent. Value is the content of cell and refers to the content of data header;
8.2 data content can be generated by reflection. Define an object and provide set and get methods. The type of attribute should be string type as much as possible (when using reflection, if the attribute content is empty, there will be default value, which is inconsistent with the actual situation). The order of object attributes and Excel columns should be consistent.
8.2.1 definition object
public class Student {
private String className;
private String name;
private String sex;
private int age;
private double height;
private double weight;
private String address;
private BigDecimal amount;
private String remark1;
private String remark2;
public String getClassName() {
return className;
}
public void setClassName(String className) {
this.className = className;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public double getHeight() {
return height;
}
public void setHeight(double height) {
this.height = height;
}
public double getWeight() {
return weight;
}
public void setWeight(double weight) {
this.weight = weight;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
public BigDecimal getAmount() {
return amount;
}
public void setAmount(BigDecimal amount) {
this.amount = amount;
}
public String getRemark1() {
return remark1;
}
public void setRemark1(String remark1) {
this.remark1 = remark1;
}
public String getRemark2() {
return remark2;
}
public void setRemark2(String remark2) {
this.remark2 = remark2;
}
}
8.2.2 setting public data method
/**
* Set cell content List
*
* @param sheet Worksheet object
* @param beginRow Which row does the data start from
* @param dataList Data set
* @param dataStyle Data style
* @return
*/
public static void setDataList(Sheet sheet, int beginRow, Collection dataList, CellStyle dataStyle) {
if (dataList != null) {
Iterator iterator = dataList.iterator();
while (iterator.hasNext()) {
Row row = sheet.createRow(beginRow);
beginRow++;
Object t = iterator.next();
// Using reflection, according to the order of javabean attributes, the getXxx() method is dynamically called to get the attribute value
Field[] fields = t.getClass().getDeclaredFields();
try {
for (short i = 0; i < fields.length; i++) {
Cell cell = row.createCell(i);
Field field = fields[i];
field.setAccessible(true);
String fieldName = field.getName();
String getMethodName = "get"
+ fieldName.substring(0, 1).toUpperCase()
+ fieldName.substring(1);
Class clazz = t.getClass();
Method method = clazz.getMethod(getMethodName, null);
Object cellValue = method.invoke(t, null);
if (cellValue instanceof BigDecimal) {
DecimalFormat df1 = new DecimalFormat("##,##0.00");
cell.setCellValue(df1.format(cellValue));
} else if (cellValue instanceof Integer) {
cell.setCellValue(Integer.valueOf(cellValue.toString()));
} else if(cellValue instanceof Double){
cell.setCellValue(Double.parseDouble(String.valueOf(cellValue)));
}else {
cell.setCellValue(String.valueOf(cellValue));
}
cell.setCellStyle(dataStyle);
}
} catch (NoSuchMethodException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
}
}
}
}
8.2.3 data header and overall data code
public static void setDataHead(){
String filePath = "/Users/lavined/Desktop/test01.xls";
FileOutputStream os = null;
// Worksheet name
String sheetName = "Student information form";
// Create workbook object
Workbook workbook = new HSSFWorkbook();
// Create worksheet object
Sheet sheet = workbook.createSheet(sheetName);
// Set column width
sheet.setDefaultColumnWidth(13);
// format cell
CellStyle cellStyle = workbook.createCellStyle();
// Content centered
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
// Background color
cellStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
// Border color black
cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
cellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
cellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
// Border Linetype
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //bottom
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//border-left
cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//border-top
cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//border-right
// Data header field
Map<Integer,String> dataHeadMap = new HashMap<>();
dataHeadMap.put(0, "class");
dataHeadMap.put(1, "full name");
dataHeadMap.put(2, "Gender");
dataHeadMap.put(3, "Age");
dataHeadMap.put(4, "height");
dataHeadMap.put(5, "weight");
dataHeadMap.put(6, "Home address");
dataHeadMap.put(7, "Meal card balance");
dataHeadMap.put(8, "Note 1");
dataHeadMap.put(9, "Note 2");
// Report data item header style
Row row = sheet.createRow(0);
if (dataHeadMap != null) {
for (Map.Entry<Integer, String> headMap : dataHeadMap.entrySet()) {
Cell cell = row.createCell(headMap.getKey());
cell.setCellValue(headMap.getValue());
if (null != cellStyle) {
cell.setCellStyle(cellStyle);
}
}
}
// Data content
List dataList = new ArrayList();
Student student = new Student();
student.setClassName("Class three, grade one");
student.setName("full name");
student.setSex("male");
student.setAge(10);
student.setHeight(120.3);
student.setWeight(40.2);
student.setAddress("xx province xx city xx county");
student.setAmount(new BigDecimal("1000.3"));
student.setRemark1("Remarks 111111");
student.setRemark2("Remarks 222222");
for (int i = 0; i < 20; i++) {
dataList.add(student);
}
// Data center
CellStyle dataCellStyle = workbook.createCellStyle();
dataCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
dataCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
setDataList(sheet,1,dataList,dataCellStyle);
// Write out files in a stream
try {
os = new FileOutputStream(filePath);
workbook.write(os);
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}finally {
if (os != null) {
try {
os.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
8.2.3 results
9. Overall code (there are redundant codes in it, which can be extracted according to needs)
public static void writeToExcel(){
// File path, the file name of version 03 starts with xls ends, and the file name of version 07 starts with xlsx end
String filePath = "/Users/lavined/Desktop/test01.xls";
FileOutputStream os = null;
// Maximum number of columns
int cellNum = 10;
int cellNumTemp = cellNum - 1;
// Worksheet name
String sheetName = "Student information sheet";
// Create workbook object
Workbook workbook = new HSSFWorkbook();
// Create worksheet object
Sheet sheet = workbook.createSheet(sheetName);
// Set column width
sheet.setDefaultColumnWidth(13);
// format cell
CellStyle cellStyle = workbook.createCellStyle();
// Border color white
cellStyle.setTopBorderColor(IndexedColors.WHITE.getIndex());
cellStyle.setLeftBorderColor(IndexedColors.WHITE.getIndex());
cellStyle.setRightBorderColor(IndexedColors.WHITE.getIndex());
cellStyle.setBottomBorderColor(IndexedColors.WHITE.getIndex());
// Border Linetype
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //bottom
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//border-left
cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//border-top
cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//border-right
// Because the right border of the last cell will disappear after using the borderless style, set the style for the next cell of the last cell
CellStyle leftCellStyle = workbook.createCellStyle();
leftCellStyle.setLeftBorderColor(IndexedColors.GREY_25_PERCENT.getIndex());
leftCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
// Content centered
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
// format cell
CellStyle cellStyle1 = workbook.createCellStyle();
// Content centered
cellStyle1.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cellStyle1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
// Background color
cellStyle1.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
cellStyle1.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
// Border color black
cellStyle1.setTopBorderColor(IndexedColors.BLACK.getIndex());
cellStyle1.setLeftBorderColor(IndexedColors.BLACK.getIndex());
cellStyle1.setRightBorderColor(IndexedColors.BLACK.getIndex());
cellStyle1.setBottomBorderColor(IndexedColors.BLACK.getIndex());
// Border Linetype
cellStyle1.setBorderBottom(HSSFCellStyle.BORDER_THIN); //bottom
cellStyle1.setBorderLeft(HSSFCellStyle.BORDER_THIN);//border-left
cellStyle1.setBorderTop(HSSFCellStyle.BORDER_THIN);//border-top
cellStyle1.setBorderRight(HSSFCellStyle.BORDER_THIN);//border-right
// Set font
Font font = workbook.createFont();
font.setBold(true);// bold
font.setFontHeightInPoints((short) 14);// Set font size
cellStyle.setFont(font);
// Create row object
Row row = sheet.createRow(0);
for (int i = 0; i < cellNum; i++) {
// Create cell object
Cell cell = row.createCell(i);
if (i == 0) {
cell.setCellValue(sheetName);
}
// Set cell style
cell.setCellStyle(cellStyle);
}
row.createCell(cellNum).setCellStyle(leftCellStyle);
// merge cell
CellRangeAddress region1 = new CellRangeAddress(0, 0, 0, cellNumTemp);
sheet.addMergedRegion(region1);
// One blank line
sheet.createRow(1);
CellRangeAddress region2 = new CellRangeAddress(1, 1, 0, cellNumTemp);
sheet.addMergedRegion(region2);
// Second level header
Row sheetRow2 = sheet.createRow(2);
int lastRemarkCellNum = cellNum - 3;
for (int i = 0; i < cellNum; i++) {
Cell cell = sheetRow2.createCell(i);
if (i == 0) {
cell.setCellValue("Class: Class 1, grade 3");
}
if (i == 3) {
cell.setCellValue("Number of people: 10");
}
if (i == lastRemarkCellNum) {
cell.setCellValue("Created on: 2021-03-01 17:09:47");
}
// Set cell style
cell.setCellStyle(cellStyle);
}
sheetRow2.createCell(cellNum).setCellStyle(leftCellStyle);
// merge cell
CellRangeAddress region3 = new CellRangeAddress(2, 2, 0, 2);
sheet.addMergedRegion(region3);
CellRangeAddress region4 = new CellRangeAddress(2, 2, 3, lastRemarkCellNum -1);
sheet.addMergedRegion(region4);
CellRangeAddress region5 = new CellRangeAddress(2, 2, lastRemarkCellNum, cellNumTemp);
sheet.addMergedRegion(region5);
sheet.createRow(3);
CellRangeAddress region6 = new CellRangeAddress(3, 3, 0, cellNumTemp);
sheet.addMergedRegion(region6);
// Data header field
Map<Integer,String> dataHeadMap = new HashMap<>();
dataHeadMap.put(0, "class");
dataHeadMap.put(1, "full name");
dataHeadMap.put(2, "Gender");
dataHeadMap.put(3, "Age");
dataHeadMap.put(4, "height");
dataHeadMap.put(5, "weight");
dataHeadMap.put(6, "Home address");
dataHeadMap.put(7, "Meal card balance");
dataHeadMap.put(8, "Note 1");
dataHeadMap.put(9, "Note 2");
// Report data item header style
Row row1 = sheet.createRow(4);
if (dataHeadMap != null) {
for (Map.Entry<Integer, String> headMap : dataHeadMap.entrySet()) {
Cell cell = row1.createCell(headMap.getKey());
cell.setCellValue(headMap.getValue());
if (null != cellStyle) {
cell.setCellStyle(cellStyle1);
}
}
}
// Data content
List dataList = new ArrayList();
Student student = new Student();
student.setClassName("Class three, grade one");
student.setName("full name");
student.setSex("male");
student.setAge(10);
student.setHeight(120.3);
student.setWeight(40.2);
student.setAddress("xx province xx city xx county");
student.setAmount(new BigDecimal("1000.3"));
student.setRemark1("Remarks 111111");
student.setRemark2("Remarks 222222");
for (int i = 0; i < 20; i++) {
dataList.add(student);
}
// Data center
CellStyle dataCellStyle = workbook.createCellStyle();
dataCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
dataCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
setDataList(sheet,5,dataList,dataCellStyle);
// Write out files in a stream
try {
os = new FileOutputStream(filePath);
workbook.write(os);
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}finally {
if (os != null) {
try {
os.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
9.1 overall effect
10 test comparison
10.1 maximum rows and columns in different versions
10.1.2 if it exceeds the maximum row, an error will be reported
10.2XSSFWorkbook and SXSSFWorkbook efficiency comparison
07 version 500000 pieces, 10 seconds
07S version 500000 pieces, 1 second
There are differences.
