Using Adams Excel for Excel merging and splitting: flexible handling of large amounts of data
Using the Apache POI library to flexibly handle Excel merging and splitting of large amounts of data
Overview:
Microsoft Excel is one of the most commonly used tools in data processing, but manual merging and splitting work is cumbersome and error prone when dealing with large amounts of data. To solve this problem, you can use the Apache POI library, especially the XSSFWorkbook and HSSFWorkbook classes, to programmatically merge and split Excel files.
Merge Excel files:
To merge Excel files, you first need to create a new Workbook object and create a Sheet object within it. Then, by using the methods provided in the XSSFWorkbook or HSSFWorkbook classes, copy the contents of multiple Excel files one by one into the new sheet. Here is a simple example code:
import org.apache.poi.ss.usermodel.*;
import java.io.*;
import java.util.*;
public class ExcelMerge {
public static void main(String[] args) {
//Source Excel File List
List<String> sourceFiles = Arrays.asList("file1.xlsx", "file2.xlsx", "file3.xlsx");
//Create a new Workbook object
Workbook mergedWorkbook = new XSSFWorkbook();
//Create a new Sheet object
Sheet mergedSheet = mergedWorkbook.createSheet("Merged Sheet");
//Copy the contents of the source Excel file to a new sheet
for (String sourceFile : sourceFiles) {
try (InputStream inputStream = new FileInputStream(sourceFile)) {
Workbook sourceWorkbook = WorkbookFactory.create(inputStream);
Sheet sourceSheet = sourceWorkbook.getSheetAt(0);
for (Row sourceRow : sourceSheet) {
Row mergedRow = mergedSheet.createRow(mergedSheet.getLastRowNum() + 1);
for (Cell sourceCell : sourceRow) {
Cell mergedCell = mergedRow.createCell(mergedRow.getLastCellNum());
mergedCell.setCellValue(getCellValue(sourceCell));
}
}
}Catch (IOException | InvalidFormatException e){
e.printStackTrace();
}
}
//Write the merged Workbook into a new Excel file
try (OutputStream outputStream = new FileOutputStream("merged.xlsx")) {
mergedWorkbook.write(outputStream);
} catch (IOException e) {
e.printStackTrace();
}
}
private static String getCellValue(Cell cell) {
String cellValue = "";
switch (cell.getCellType()) {
case STRING:
cellValue = cell.getStringCellValue();
break;
case NUMERIC:
cellValue = String.valueOf(cell.getNumericCellValue());
break;
case BOOLEAN:
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
}
return cellValue;
}
}
Split Excel file:
To split an Excel file, you first need to load the source Excel file. Then, based on the splitting rules (such as splitting by column or row), create multiple Workbook objects and copy the content from the source Excel file one by one into the sheets of these Workbook objects. Here is a simple example code:
import org.apache.poi.ss.usermodel.*;
import java.io.*;
import java.util.*;
public class ExcelSplit {
public static void main(String[] args) {
//Excel file to split
String sourceFile = "source.xlsx";
//Split Rule: Split by Column
int splitByColumnIndex = 1;
try (InputStream inputStream = new FileInputStream(sourceFile)) {
Workbook sourceWorkbook = WorkbookFactory.create(inputStream);
Sheet sourceSheet = sourceWorkbook.getSheetAt(0);
//Obtain a list of Unique IDs required for splitting
Set<String> uniqueValues = new HashSet<>();
for (Row row : sourceSheet) {
uniqueValues.add(getCellValue(row.getCell(splitByColumnIndex)));
}
//Create a new Workbook object based on Unique ID
Map<String, Workbook> splitWorkbooks = new HashMap<>();
for (String uniqueValue : uniqueValues) {
Workbook splitWorkbook = new XSSFWorkbook();
splitWorkbooks.put(uniqueValue, splitWorkbook);
}
//Copy the contents of the source Excel file to a new Workbook object
for (Row sourceRow : sourceSheet) {
String uniqueValue = getCellValue(sourceRow.getCell(splitByColumnIndex));
Workbook splitWorkbook = splitWorkbooks.get(uniqueValue);
Sheet splitSheet = splitWorkbook.createSheet();
Row splitRow = splitSheet.createRow(splitSheet.getLastRowNum() + 1);
for (Cell sourceCell : sourceRow) {
Cell splitCell = splitRow.createCell(splitRow.getLastCellNum());
splitCell.setCellValue(getCellValue(sourceCell));
}
}
//Write each split workbook into a new Excel file
for (Map.Entry<String, Workbook> entry : splitWorkbooks.entrySet()) {
try (OutputStream outputStream = new FileOutputStream(entry.getKey() + ".xlsx")) {
entry.getValue().write(outputStream);
}
}
}Catch (IOException | InvalidFormatException e){
e.printStackTrace();
}
}
private static String getCellValue(Cell cell) {
String cellValue = "";
switch (cell.getCellType()) {
case STRING:
cellValue = cell.getStringCellValue();
break;
case NUMERIC:
cellValue = String.valueOf(cell.getNumericCellValue());
break;
case BOOLEAN:
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
}
return cellValue;
}
}
The above example code uses the XSSFWorkbook and HSSFWorkbook classes of Apache POI to process Excel files, and includes the basic logic for merging and splitting. It is necessary to modify and optimize the code according to actual needs in order to adapt to Excel merge and split operations in different scenarios.
