1. What is POI
POI’s full name is PoorObfuscation Implementation. It is an open source project of Apache components that can read and write a series of Microsoft Office office software. The POI version corresponding to the examples in this article is 3.9.
2. Generate Excel tool class
package org.zhao.component;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.collections4.MapUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.*;
import java.util.*;
/**
* Generate Excel
*
* @author Administrator
* @date March 11, 2022
*/
public class WriteExcelComponent {
private static String getFileExtname(String filename) {
return filename.substring(filename.lastIndexOf(".") + 1).toLowerCase();
}
@SuppressWarnings("unchecked")
public static void writeExcel(String filePath, List<Map<String, Object>> dataList) throws Exception {
File file = new File(filePath);
if (file.exists()) {
return;
}
String fileName = file.getName();
String fileSuffix = getFileExtname(fileName);
Workbook workbook = null;
if ("xls".equals(fileSuffix)) {
workbook = new HSSFWorkbook();
}
if ("xlsx".equals(fileSuffix)) {
workbook = new XSSFWorkbook();
}
if (workbook == null) {
return;
}
for (int sheetIndex = 0; sheetIndex < dataList.size(); sheetIndex + + ) {
Sheet sheet = workbook.createSheet();
Map<String, Object> data = dataList.get(sheetIndex);
String sheetName = "Sheet" + (sheetIndex + 1);
String dataSheetName = MapUtils.getString(data, "sheetName", "");
if (dataSheetName != null & amp; & amp; dataSheetName.length() != 0) {
sheetName = dataSheetName;
}
workbook.setSheetName(sheetIndex, sheetName);
List<String> sheetHeader = (List<String>) MapUtils.getObject(data, "sheetHeader", new ArrayList<>());
int startRowIndex = 0;
if (CollectionUtils.isNotEmpty(sheetHeader)) {
CellStyle cellStyle = workbook.createCellStyle();
Font font = workbook.createFont();
font.setBoldweight(Font.BOLDWEIGHT_BOLD);
cellStyle.setFont(font);
Row row = sheet.createRow(0);
for (int headerIndex = 0; headerIndex < sheetHeader.size(); headerIndex + + ) {
Cell cell = row.createCell(headerIndex, Cell.CELL_TYPE_STRING);
cell.setCellValue(String.valueOf(sheetHeader.get(headerIndex)));
cell.setCellStyle(cellStyle);
}
startRowIndex = 1;
}
List<List<String>> rows = (List<List<String>>) MapUtils.getObject(data, "sheetRows", new ArrayList<>());
for (int rowIndex = 0; rowIndex < rows.size(); rowIndex + + ) {
Row row = sheet.createRow(startRowIndex + rowIndex);
List<String> cellItems = rows.get(rowIndex);
for (int cellIndex = 0; cellIndex < cellItems.size(); cellIndex + + ) {
Cell cell = row.createCell(cellIndex, Cell.CELL_TYPE_STRING);
cell.setCellValue(cellItems.get(cellIndex));
sheet.setColumnWidth(cellIndex, 80 * 50);
}
}
}
FileOutputStream fos = new FileOutputStream(filePath);
workbook.write(fos);
fos.close();
}
}
3. Reading Excel tool class
package org.zhao.component;
import org.apache.commons.collections4.MapUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.*;
import java.util.*;
/**
* Excel reads data in excel
*
* @author Administrator
* @date March 11, 2022
*/
public class ReadExcelComponent {
private static String getFileExtname(String filename) {
return filename.substring(filename.lastIndexOf(".") + 1).toLowerCase();
}
/**
* Read data in a sheet
*
* @param path file path
* @param sheetNum sheet number
*/
public static Map<String, Object> readExcelData(String path, int sheetNum) {
List<Map<String, Object>> dataList = readExcelDatas(path);
if (dataList.size() == 0) {
return null;
}
return dataList.stream().filter(map -> MapUtils.getIntValue(map, "sheetIndex", 0) == sheetNum).findAny().orElse(null);
}
/**
* Read all data in Excel multiple sheets
*
* @param path file path
*/
public static List<Map<String, Object>> readExcelDatas(String path) {
List<Map<String, Object>> dataList = new ArrayList<>();
if (path == null || path.length() == 0) {
return dataList;
}
File file = new File(path);
String fileName = file.getName();
String fileSuffix = getFileExtname(fileName);
Workbook workbook = null;
try {
if ("xls".equals(fileSuffix)) {
workbook = new HSSFWorkbook(new FileInputStream(file));
}
if ("xlsx".equals(fileSuffix)) {
workbook = new XSSFWorkbook(new FileInputStream(file));
}
} catch (IOException e) {
e.printStackTrace();
}
if (workbook == null) {
return dataList;
}
List<Sheet> workbookSheets = getWorkbookSheetList(workbook);
for (int sheetIndex = 0; sheetIndex < workbookSheets.size(); sheetIndex + + ) {
Sheet sheet = workbookSheets.get(sheetIndex);
List<Row> sheetRowList = getSheetRowList(sheet);
String sheetName = sheet.getSheetName();
Map<String, Object> sheetDataList = new LinkedHashMap<>();
List<List<String>> rows = new ArrayList<>();
sheetRowList.forEach(row -> {
short firstCellNum = row.getFirstCellNum();
short lastCellNum = row.getLastCellNum();
List<String> cellDataList = new ArrayList<>();
for (int cellIndex = firstCellNum; cellIndex < lastCellNum; cellIndex + + ) {
Cell cell = row.getCell(cellIndex);
cellDataList.add(getCellValue(cell));
}
rows.add(cellDataList);
});
sheetDataList.put("sheetName", sheetName);
sheetDataList.put("sheetIndex", sheetIndex + 1);
sheetDataList.put("sheetRows", rows);
dataList.add(sheetDataList);
}
return dataList;
}
private static String getCellValue(Cell cell) {
String cellValue = "";
if (cell == null) {
return "";
}
try {
int cellType = cell.getCellType();
/*Numeric numeric type*/
if (HSSFCell.CELL_TYPE_NUMERIC == cellType) {
cellValue = String.valueOf(cell.getNumericCellValue());
}
/*string*/
if (HSSFCell.CELL_TYPE_STRING == cellType) {
cellValue = cell.getStringCellValue();
}
/*Formula*/
if (HSSFCell.CELL_TYPE_FORMULA == cellType) {
cellValue = cell.getCellFormula();
}
/*blank line*/
if (HSSFCell.CELL_TYPE_BLANK == cellType) {
cellValue = "";
}
/*Boolean type*/
if (HSSFCell.CELL_TYPE_BOOLEAN == cellType) {
cellValue = String.valueOf(cell.getBooleanCellValue());
}
/*Error Cell type */
if (HSSFCell.CELL_TYPE_ERROR == cellType) {
cellValue = String.valueOf(cell.getErrorCellValue());
}
} catch (Exception e) {
e.printStackTrace();
}
return cellValue;
}
/*Get the row data in the sheet*/
private static List<Row> getSheetRowList(Sheet sheet) {
List<Row> items = new ArrayList<>();
if (sheet == null) {
return items;
}
int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();
for (int rowIndex = 0; rowIndex < physicalNumberOfRows; rowIndex + + ) {
Row row = sheet.getRow(rowIndex);
items.add(row);
}
return items;
}
/*Get sheet in excel*/
private static List<Sheet> getWorkbookSheetList(Workbook workbook) {
List<Sheet> items = new ArrayList<>();
if (workbook == null) {
return items;
}
int numberOfSheets = workbook.getNumberOfSheets();
for (int sheetIndex = 0; sheetIndex < numberOfSheets; sheetIndex + + ) {
Sheet sheet = workbook.getSheetAt(sheetIndex);
items.add(sheet);
}
return items;
}
}
4. Run the instance
package org.zhao;
import org.zhao.component.ReadExcelComponent;
import org.zhao.component.WriteExcelComponent;
import java.util.*;
/**
* @author Administrator
* @date March 11, 2022
*/
public class Main {
public static void writeDataToExcel() {
List<Map<String, Object>> dataList = new ArrayList<>();
Map<String, Object> sheet_1 = new HashMap<>();
sheet_1.put("sheetName", "Chinese scores");
sheet_1.put("sheetHeader", Arrays.asList("student number", "name", "score"));
List<List<String>> rows = new ArrayList<>();
rows.add(Arrays.asList("0001", "Xiao Zhang", "60"));
rows.add(Arrays.asList("0002", "Xiao Ming", "80"));
rows.add(Arrays.asList("0003", "Xiao Wang", "90"));
sheet_1.put("sheetRows", rows);
dataList.add(sheet_1);
Map<String, Object> sheet_2 = new HashMap<>();
sheet_2.put("sheetName", "Math Score");
sheet_2.put("sheetHeader", Arrays.asList("student number", "name", "score"));
List<List<String>> rows2 = new ArrayList<>();
rows2.add(Arrays.asList("0001", "Xiao Zhang", "90"));
rows2.add(Arrays.asList("0002", "Xiao Ming", "86"));
rows2.add(Arrays.asList("0003", "Xiao Wang", "95"));
sheet_2.put("sheetRows", rows2);
dataList.add(sheet_2);
try {
WriteExcelComponent.writeExcel("D:/Transcript.xls", dataList);
System.out.println("Created successfully");
} catch (Exception e) {
e.printStackTrace();
}
}
public static void readExcelData() {
List<Map<String, Object>> allItems = ReadExcelComponent.readExcelDatas("D:/Transcript.xls");
System.out.println("allItems = " + allItems);
Map<String, Object> sheet2Items = ReadExcelComponent.readExcelData("D:/Transcript.xls", 2);
System.out.println("sheet2Items = " + sheet2Items);
}
public static void main(String[] args) {
// writeDataToExcel();
readExcelData();
}
}
The knowledge points of the article match the official knowledge files, and you can further learn relevant knowledge. Java skill treeUsing JDBC to operate databasesJDBC Overview 139,228 people are learning the system