read excels and write in one excel
There is a faulty channel file, so not quite an update.
There is a work around.
1. Boot Windows into Safe Mode or WRE,
2. Go to C:\Windows\System32\drivers\CrowdStrike
3. Locate and delete file matching "C-00000291*.sys"
4. Boot normally
Test code
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.io.IOException;
public class ExcelFileHandler {
public static void main(String[] args) {
// Paths to your source Excel files
String[] sourceFiles = {
"path/to/first/source.xlsx",
"path/to/second/source.xlsx"
};
// Path to the new Excel file
String outputFile = "path/to/output.xlsx";
try (XSSFWorkbook newWorkbook = new XSSFWorkbook()) {
for (String sourceFile : sourceFiles) {
try (FileInputStream fis = new FileInputStream(new File(sourceFile));
XSSFWorkbook sourceWorkbook = new XSSFWorkbook(fis)) {
// Iterate over all sheets in the source workbook
for (int i = 0; i < sourceWorkbook.getNumberOfSheets(); i++) {
Sheet sourceSheet = sourceWorkbook.getSheetAt(i);
String sheetName = sourceSheet.getSheetName();
// Create a new sheet in the new workbook
Sheet newSheet = newWorkbook.createSheet(sheetName);
// Copy all rows and cells from source sheet to new sheet
for (int rowIndex = 0; rowIndex <= sourceSheet.getLastRowNum(); rowIndex++) {
Row sourceRow = sourceSheet.getRow(rowIndex);
Row newRow = newSheet.createRow(rowIndex);
if (sourceRow != null) {
for (int colIndex = 0; colIndex < sourceRow.getLastCellNum(); colIndex++) {
Cell sourceCell = sourceRow.getCell(colIndex);
Cell newCell = newRow.createCell(colIndex);
if (sourceCell != null) {
copyCell(sourceCell, newCell);
}
}
}
}
}
}
}
// Write the new workbook to the file system
try (FileOutputStream fos = new FileOutputStream(new File(outputFile))) {
newWorkbook.write(fos);
}
} catch (IOException e) {
e.printStackTrace();
}
}
private static void copyCell(Cell sourceCell, Cell newCell) {
newCell.setCellStyle(sourceCell.getCellStyle());
switch (sourceCell.getCellType()) {
case STRING:
newCell.setCellValue(sourceCell.getStringCellValue());
break;
case NUMERIC:
if (DateUtil.isCellDateFormatted(sourceCell)) {
newCell.setCellValue(sourceCell.getDateCellValue());
} else {
newCell.setCellValue(sourceCell.getNumericCellValue());
}
break;
case BOOLEAN:
newCell.setCellValue(sourceCell.getBooleanCellValue());
break;
case FORMULA:
newCell.setCellFormula(sourceCell.getCellFormula());
break;
case BLANK:
newCell.setBlank();
break;
case ERROR:
newCell.setCellErrorValue(sourceCell.getErrorCellValue());
break;
default:
break;
}
}
}
test 2
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.io.IOException;
import java.util.HashMap;
import java.util.Map;
public class ExcelFileHandler {
public static void main(String[] args) {
// Paths to your source Excel files
String[] sourceFiles = {
"path/to/first/source.xlsx",
"path/to/second/source.xlsx"
};
// Path to the new Excel file
String outputFile = "path/to/output.xlsx";
try (XSSFWorkbook newWorkbook = new XSSFWorkbook()) {
for (String sourceFile : sourceFiles) {
try (FileInputStream fis = new FileInputStream(new File(sourceFile));
XSSFWorkbook sourceWorkbook = new XSSFWorkbook(fis)) {
// Iterate over all sheets in the source workbook
for (int i = 0; i < sourceWorkbook.getNumberOfSheets(); i++) {
Sheet sourceSheet = sourceWorkbook.getSheetAt(i);
String sheetName = sourceSheet.getSheetName();
// Create a new sheet in the new workbook
Sheet newSheet = newWorkbook.createSheet(sheetName);
// Map to hold new styles created in the new workbook
Map<CellStyle, CellStyle> styleMap = new HashMap<>();
// Copy all rows and cells from source sheet to new sheet
for (int rowIndex = 0; rowIndex <= sourceSheet.getLastRowNum(); rowIndex++) {
Row sourceRow = sourceSheet.getRow(rowIndex);
Row newRow = newSheet.createRow(rowIndex);
if (sourceRow != null) {
for (int colIndex = 0; colIndex < sourceRow.getLastCellNum(); colIndex++) {
Cell sourceCell = sourceRow.getCell(colIndex);
Cell newCell = newRow.createCell(colIndex);
if (sourceCell != null) {
if (sourceCell.getCellStyle() != null) {
if (!styleMap.containsKey(sourceCell.getCellStyle())) {
CellStyle newCellStyle = newWorkbook.createCellStyle();
newCellStyle.cloneStyleFrom(sourceCell.getCellStyle());
styleMap.put(sourceCell.getCellStyle(), newCellStyle);
}
newCell.setCellStyle(styleMap.get(sourceCell.getCellStyle()));
}
copyCell(sourceCell, newCell);
}
}
}
}
}
}
}
// Write the new workbook to the file system
try (FileOutputStream fos = new FileOutputStream(new File(outputFile))) {
newWorkbook.write(fos);
}
} catch (IOException e) {
e.printStackTrace();
}
}
private static void copyCell(Cell sourceCell, Cell newCell) {
switch (sourceCell.getCellType()) {
case STRING:
newCell.setCellValue(sourceCell.getStringCellValue());
break;
case NUMERIC:
if (DateUtil.isCellDateFormatted(sourceCell)) {
newCell.setCellValue(sourceCell.getDateCellValue());
} else {
newCell.setCellValue(sourceCell.getNumericCellValue());
}
break;
case BOOLEAN:
newCell.setCellValue(sourceCell.getBooleanCellValue());
break;
case FORMULA:
newCell.setCellFormula(sourceCell.getCellFormula());
break;
case BLANK:
newCell.setBlank();
break;
case ERROR:
newCell.setCellErrorValue(sourceCell.getErrorCellValue());
break;
default:
break;
}
}
}
test 3
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.io.IOException;
import java.util.Iterator;
public class ExcelMerger {
public static void main(String[] args) {
String directoryPath = "path_to_your_directory";
String outputFile = "recon_report.xlsx";
try (XSSFWorkbook finalWorkbook = new XSSFWorkbook()) {
File directory = new File(directoryPath);
File[] files = directory.listFiles((dir, name) -> name.endsWith(".xlsx"));
if (files != null) {
for (File file : files) {
try (FileInputStream fis = new FileInputStream(file);
Workbook workbook = WorkbookFactory.create(fis)) {
for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
Sheet sheet = workbook.getSheetAt(i);
Sheet newSheet = finalWorkbook.createSheet(sheet.getSheetName() + "_" + file.getName());
copySheetContent(sheet, newSheet);
}
} catch (IOException e) {
e.printStackTrace();
}
}
}
try (FileOutputStream fos = new FileOutputStream(outputFile)) {
finalWorkbook.write(fos);
}
System.out.println("Merged Excel file created: " + outputFile);
} catch (IOException e) {
e.printStackTrace();
}
}
private static void copySheetContent(Sheet sourceSheet, Sheet targetSheet) {
Iterator<Row> rowIterator = sourceSheet.iterator();
while (rowIterator.hasNext()) {
Row sourceRow = rowIterator.next();
Row targetRow = targetSheet.createRow(sourceRow.getRowNum());
Iterator<Cell> cellIterator = sourceRow.cellIterator();
while (cellIterator.hasNext()) {
Cell sourceCell = cellIterator.next();
Cell targetCell = targetRow.createCell(sourceCell.getColumnIndex());
copyCellValue(sourceCell, targetCell);
}
}
}
private static void copyCellValue(Cell sourceCell, Cell targetCell) {
switch (sourceCell.getCellType()) {
case STRING:
targetCell.setCellValue(sourceCell.getStringCellValue());
break;
case NUMERIC:
if (DateUtil.isCellDateFormatted(sourceCell)) {
targetCell.setCellValue(sourceCell.getDateCellValue());
} else {
targetCell.setCellValue(sourceCell.getNumericCellValue());
}
break;
case BOOLEAN:
targetCell.setCellValue(sourceCell.getBooleanCellValue());
break;
case FORMULA:
targetCell.setCellFormula(sourceCell.getCellFormula());
break;
default:
targetCell.setCellValue(sourceCell.getStringCellValue());
break;
}
}
}
Explanation:
Directory Path: Update
directoryPathwith the path to your directory containing Excel files.Output File: The merged Excel file will be named
recon_report.xlsx.Apache POI: The code uses Apache POI to read and write Excel files.
Steps:
The code lists all
.xlsxfiles in the specified directory.It reads each sheet from each Excel file.
Each sheet is copied to a new workbook (
recon_report.xlsx), with the sheet names being appended with the source file name to ensure uniqueness.The new workbook is then saved as
recon_report.xlsx.
This code will create a new Excel file containing all sheets from all Excel files in the specified directory.
t
rstcomplete