1. Import related dependencies
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
<exclusions>
<exclusion>
<artifactId>commons-codec</artifactId>
<groupId>commons-codec</groupId>
</exclusion>
</exclusions>
</dependency>
<dependency>
<groupId>org.apache.logging.log4j</groupId>
<artifactId>log4j-core</artifactId>
<version>2.12.1</version>
</dependency>
<dependency>
<groupId>org.apache.logging.log4j</groupId>
<artifactId>log4j-api</artifactId>
<version>2.12.1</version>
</dependency>
2. Encapsulate excel processing class
package com.mytest.utils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.List;
/**
* ClassName: ExcelWriter
* Description:
*
* @author Weiwan
* @date 2023/5/23 14:11
*/
public class ExcelWriter {
/*
* Read the content of the use case table, and copy the content of the use case table to the result table to complete the creation of the result table, and realize writing data to the result table
*
* @method excelWriter constructor completes the creation of the result table setStyle specifies the format when writing content to the result table
* writeFailCell writes in red font when the use case fails writeCell normally writes the cell in black font useSheet specifies the sheet to use
* writeLine writes the content of the entire line. save saves the written content to the excel file, and must be called after the operation is completed, otherwise the excel file has no content.
*/
// workbook for storing result files
private Workbook resultworkbook = null;
// working sheet page
private Sheet sheet;
// Used to read the content of the use case table and copy it to the output stream of the result target file
private FileOutputStream stream = null;
// A member variable used to store the path of the result file, which is convenient to call when saving the result
private String path = null;
// cell format
private CellStyle style = null;
// The total number of rows of the sheet page
private int rows = 0;
/*
* According to the use case table path1, create the result table path2, copy the content in path1 to path2
*
* @param path1 use case table path path2 result table path
*/
public ExcelWriter(String caseFilePath, String resultFileName) {
//Read the contents of the excel file into the memory as the read workbook object
// Intercept the suffix name of the use case table
String Origintype = caseFilePath. substring(caseFilePath. lastIndexOf("."));
// Determine whether it is in xls or xlsx format, and complete the workbook that creates the use case table in memory
Workbook casebook = null;
try {
if (Origintype. equals(".xlsx")) {
casebook = new XSSFWorkbook(new File(caseFilePath));
} else if (Origintype. equals(".xls")) {
casebook = new HSSFWorkbook(new FileInputStream(new File(caseFilePath)));
}
} catch (Exception e) {
e.printStackTrace();
}
// If the two formats do not match, the file opening fails and the method execution stops.
if (casebook == null) {
System.out.println("Excel use case file failed to open!");
return;
}
//Copy the content in the read workbook to the result file.
//stitching result file name
String resultFilepath = resultFileName + Origintype;
try {
// According to the file name of the result table, open up space for the file in memory
File resultfile = new File(resultFilepath);
try {
// Create the file on disk
resultfile. createNewFile();
} catch (Exception e1) {
// The creation fails, indicating that the path is illegal, and the method execution is stopped.
System.out.println("The result file path is invalid, please check!");
e1.printStackTrace();
return;
}
// Based on the result table, create a file output stream stream
stream = new FileOutputStream(resultfile);
// Write the content in the use case table to the file output stream stream
casebook. write(stream);
// Close the file stream that has written the content of the use case table
stream. close();
// Based on the result table, create a file input stream, open the result table, and all subsequent write operations are based on the result table,
//The current object of the member variable is the content in the result table
FileInputStream in = new FileInputStream(resultfile);
// Determine whether the suffix of the result file is Excel version 03 or version 07
try {
if (Origintype. equals(".xlsx")) {
resultworkbook = new XSSFWorkbook(in);
} else if (Origintype. equals(".xls")) {
resultworkbook = new HSSFWorkbook(in);
}
// Initialize the sheet page
sheet = resultworkbook. getSheetAt(0);
// Get the maximum number of rows
rows = sheet. getPhysicalNumberOfRows();
} catch (Exception e) {
e.printStackTrace();
}
// close the file input stream
in. close();
// Assign the member variable result file path to path2, indicating that the result table has been created successfully.
path = resultFilepath;
// Set the default style to the style of the first cell
setStyle(0, 0);
// System.out.println(path);
} catch (Exception e) {
e.printStackTrace();
}
}
// Get the number of all sheet pages of the current Excel
public int getTotalSheetNo() {
int sheets = 0;
if (resultworkbook != null) {
sheets = resultworkbook. getNumberOfSheets();
}
return sheets;
}
// Get the maximum number of rows in the current sheet.
public int getRowNo() {
return rows;
}
// Get the name of the current sheet page
public String getSheetName(int sheetIndex) {
String sheetname = "";
if (resultworkbook != null)
sheetname = resultworkbook. getSheetName(sheetIndex);
return sheetname;
}
// Use the sheet page according to the sheet name.
public void useSheet(String sheetName) {
if (resultworkbook != null) {
// According to the type of open workbook, initialize the sheet page in it
sheet=resultworkbook. getSheet(sheetName);
rows = sheet. getPhysicalNumberOfRows();
} else {
System.out.println("Excel file not opened!");
}
}
public void userSheetBySheetNmae(String sheetname){
if (resultworkbook != null){
sheet = resultworkbook. getSheet( sheetname );
rows = sheet. getPhysicalNumberOfRows();
} else{
System.out.println("The excel file is not opened!");
}
}
//Specify the sheet used according to the sheet number
public void useSheetByIndex(int sheetIndex) {
if (resultworkbook != null) {
sheet=resultworkbook.getSheetAt(sheetIndex);
rows = sheet. getPhysicalNumberOfRows();
} else {
System.out.println("error:: Excel file not opened!");
}
}
// Set the style to the style of the specified cell in Excel
public void setStyle(int rowNo, int column) {
Row row = null;
Cell cell = null;
try {
// Get the specified row
row = sheet. getRow(rowNo);
// Get the specified column
cell = row. getCell(column);
// System.out.println(cell.getStringCellValue());
// Save the specified cell style
style = cell. getCellStyle();
} catch (Exception e) {
e.printStackTrace();
}
}
// Write content to the cell in the default format, the parameter usage is consistent with writeFailCell
public void writeCell(int rowNo, int column, String value) {
Row row = null;
try {
// Get the specified row
row = sheet. getRow(rowNo);
} catch (Exception e) {
e.printStackTrace();
}
// row does not exist, create it
if (row == null) {
row = sheet.createRow(rowNo);
}
// In this row, create a new cell in the specified column
Cell cell = row.createCell(column);
// set cell value
cell.setCellValue(value);
// set the cell style
cell.setCellStyle(style);
}
/*
* When the execution result of the use case fails, use this method to write excel in red font
*
* @param r cell row number l cell column number value input value
*/
public void writeFailCell(int rowNo, int column, String value) {
Row row = null;
try {
// Get the specified row
row = sheet. getRow(rowNo);
} catch (Exception e) {
e.printStackTrace();
}
// row does not exist, create it
if (row == null) {
row = sheet.createRow(rowNo);
}
// In this row, create a new cell in the specified column
Cell cell = row.createCell(column);
// set cell value
cell.setCellValue(value);
// set the cell style
CellStyle failStyle = null;
// new font style
Font font = null;
// Instantiate according to different excel versions
failStyle = resultworkbook. createCellStyle();
// Set font color to red
font = resultworkbook. createFont();
font.setColor(IndexedColors.RED.index);
// use font color as cell style
failStyle. setFont(font);
//set cell border
failStyle.setBorderLeft(BorderStyle.THIN);
failStyle.setBorderRight(BorderStyle.THIN);
failStyle.setBorderTop(BorderStyle.THIN);
failStyle.setBorderBottom(BorderStyle.THIN);
// Set the corresponding cell style
cell.setCellStyle(failStyle);
}
// write the content of a whole line
public void writeLine(int rowNo, List<String> list) {
Row row = null;
try {
// Get the specified row
row = sheet. getRow(rowNo);
} catch (Exception e) {
e.printStackTrace();
}
// row does not exist, create it
if (row == null) {
row = sheet.createRow(rowNo);
}
Cell cell = null;
for (int i = 0; i < list. size(); i ++ ) {
// In this row, create a new cell in the specified column
cell = row. createCell(i);
// set cell value
cell.setCellValue(list.get(i));
// set the cell style
cell.setCellStyle(style);
}
}
// Save the in-memory workbook content of the results table to a disk file
public void save() {
// If the result table file has not been created, it will not be saved
if (path != null) {
try {
// Create a file output stream based on the result table path
stream = new FileOutputStream(new File(path));
// Write the content of the workbook workbook of the result table to the output stream, that is, write to the file
if (resultworkbook != null) {
resultworkbook.write(stream);
resultworkbook. close();
} else {
System.out.println("Excel file not opened!");
}
// Close the output stream, complete the process of writing the workbook in memory to the file, and save the file.
stream. close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
3. Call the excel processing class to generate test data in batches
package com.mytest.utils;
import org.apache.logging.log4j.LogManager;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
/**
* ClassName: ExcelTest
* Description:
*
* @author Weiwan
* @date 2023/5/23 14:10
*/
public class ExcelTest {
private static String casefile = "G:\ent_item.xlsx";
private static String resultfile = "G:";
public static org.apache.logging.log4j.Logger logger = LogManager.getLogger(ExcelTest.class);
public static List<String> getRowList(int num) {
List<String> rowList = new ArrayList<>();
rowList.add(0, "DD"); //Consignor code
rowList.add(1, "DD"); //Shipper name
rowList.add(2, "PQSKU" + num); //commodity code
rowList.add(3, "PQSKU" + num); //product name
rowList.add(4, "STD"); //packaging code
rowList.add(5, "No"); //Whether to combine products
rowList.add(6, "");
rowList.add(7, "");
rowList.add(8, "");
rowList.add(9, "");
rowList.add(10, "");
rowList.add(11, "");
rowList.add(12, "");
rowList.add(13, "");
rowList.add(14, "");
rowList.add(15, "");
rowList.add(16, "");
rowList.add(17, "");
rowList.add(18, "");
rowList.add(19, "");
rowList.add(20, "");
rowList.add(21, "");
rowList.add(22, "");
rowList.add(23, "");
rowList.add(24, "");
rowList.add(25, "");
rowList.add(26, "");
rowList.add(27, "");
rowList.add(28, "");
rowList.add(29, "");
rowList.add(30, "");
rowList.add(31, "");
rowList.add(32, "");
rowList.add(33, "");
rowList.add(34, "");
rowList.add(35, "");
rowList.add(36, "");
rowList.add(37, "");
rowList.add(38, "");
rowList.add(39, "");
rowList.add(40, "");
rowList.add(41, "");
rowList.add(42, "");
rowList.add(43, "No"); //Serial number management or not
rowList.add(44, "No"); //Whether the serial number contains the product barcode
rowList.add(45, "No"); //Whether to collect the whole process
rowList.add(46, "No"); //whether the serial number inventory
rowList.add(47, "No"); //whether to put into storage
rowList.add(48, "No"); //Whether to collect from the library
rowList.add(49, "No"); //Whether to collect attribute transfer order
return rowList;
}
public static void main(String[] args) {
Date date = new Date();
SimpleDateFormat sdf = new SimpleDateFormat("MMdd-HHmmss");
resultfile + = "SKUResult" + sdf. format( date );
ExcelWriter results = new ExcelWriter(casefile, resultfile);
results. useSheetByIndex(0);
logger.info("================================================================================================================================================= == ===================");
List<String> rowList = null;
for (int i = 1; i < 1000; i ++ ) {
logger.info("**************************Execute line " + i + " and write *********** *******************");
rowList = getRowList(i);
results.writeLine(i, rowList);
// if(i % 5000 == 0){
// results. save();
// }
}
results. save();
System.out.println("=========================================================================================================================================== == =====================");
}
}