Как редактировать файл Excel с большими данными с помощью SXSSF изменить цвет ячейки
Я прочитал все предыдущие вопросы, но нашел решение.
Как отредактировать существующий большой файл Excel с помощью API потоковой передачи SXSSF
Как записать в существующий файл с помощью SXSSF?
У меня есть существующий файл, который мне нужен для обновления цвета ячейки изменения содержимого для некоторого содержимого.
Мне нужно изменить файл Excel с большими данными более 40000 строк.
Шаги, выполненные в приведенном ниже коде.
- Создайте новый файл для результата, скопируйте файл 2 для результата, чтобы выделить не равную ячейку
- Создать книгу для 2 сравнить файлы Excel Temp
- XSSFWorkbook XSSF cellStyleRed как SXSSFWorkbook не может иметь цвет стиля ячейки
- сохранить 100 строк в памяти, превышающие строки будут сброшены на диск
- compareTwoRows из обоих файлов excel, не равных, изменит цвет стиля ячейки на красный в файле результатов.
Проблема в приведенном ниже коде. Файл результата пуст, нет содержимого. Я понимаю, что мой код неверен, поскольку SXSSFWorkbook создает новый лист.
КАК я могу обновить файл результатов, изменив цвет ячейки?
package pageobjects;
import java.awt.Color;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.nio.file.FileSystem;
import java.nio.file.FileSystems;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.StandardCopyOption;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.sl.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.testng.Reporter;
import property.IHomePage;
import utility.SeleniumUtils;
public class Excelcom2try extends SeleniumUtils implements IHomePage {
public static FileOutputStream opstr = null;
XSSFCellStyle cellStyleRed = null;
SXSSFWorkbook sxssfWorkbook = null;
SXSSFSheet sheet = null;
SXSSFRow row3edit = null;
SXSSFCell Cell = null;
@SuppressWarnings("resource")
public void compare() {
try {
// Create new file for Result
XSSFWorkbook workbook = new XSSFWorkbook();
FileOutputStream fos = new FileOutputStream(new File("\\\\sd\\comparisonfile\\ResultFile.xlsx"));
workbook.write(fos);
workbook.close();
Thread.sleep(2000);
// get input for 2 compare excel files
FileInputStream excellFile1 = new FileInputStream(new File("new File("\\\\sd\\comparisonfile\\UAT_Relationship.xlsx"));
FileInputStream excellFile2 = new FileInputStream(new File(""\\\\sd\\comparisonfile\\Prod_Relationship.xlsx"));
// Copy file 2 for result to highlight not equal cell
FileSystem system = FileSystems.getDefault();
Path original = system.getPath(""\\\\sd\\comparisonfile\\Prod_Relationship.xlsx");
Path target = system.getPath(""\\\\sd\\comparisonfile\\ResultFile.xlsx");
try {
// Throws an exception if the original file is not found.
Files.copy(original, target, StandardCopyOption.REPLACE_EXISTING);
Reporter.log("Successfully Copy File 2 for result to highlight not equal cell");
Add_Log.info("Successfully Copy File 2 for result to highlight not equal cell");
} catch (IOException ex) {
Reporter.log("Unable to Copy File 2 ");
Add_Log.info("Unable to Copy File 2 ");
}
Thread.sleep(2000);
FileInputStream excelledit3 = new FileInputStream(new File("\\\\sd\\comparisonfile\\ResultFile.xlsx"));
// Create Workbook for 2 compare excel files
XSSFWorkbook workbook1 = new XSSFWorkbook(excellFile1);
XSSFWorkbook workbook2 = new XSSFWorkbook(excellFile2);
// Temp workbook
XSSFWorkbook workbook3new = new XSSFWorkbook();
//XSSF cellStyleRed as SXSSFWorkbook cannot have cellstyle color
cellStyleRed = workbook3new.createCellStyle();
cellStyleRed.setFillForegroundColor(IndexedColors.RED.getIndex());
cellStyleRed.setFillPattern(FillPatternType.SOLID_FOREGROUND);
// Get first/desired sheet from the workbook to compare both excel sheets
XSSFSheet sheet1 = workbook1.getSheetAt(0);
XSSFSheet sheet2 = workbook2.getSheetAt(0);
//XSSFWorkbook workbook3new temp convert to SXSSFWorkbook
// keep 100 rows in memory, exceeding rows will be flushed to disk
sxssfWorkbook = new SXSSFWorkbook(100);
sxssfWorkbook.setCompressTempFiles(true);
sheet = sxssfWorkbook.createSheet();
// Compare sheets
if (compareTwoSheets(sheet1, sheet2, sheet)) {
Reporter.log("\\n\\nThe two excel sheets are Equal");
Add_Log.info("\\n\\nThe two excel sheets are Equal");
} else {
Reporter.log("\\n\\nThe two excel sheets are Not Equal");
Add_Log.info("\\n\\nThe two excel sheets are Not Equal");
}
// close files
excellFile1.close();
excellFile2.close();
// excelledit3.close();
opstr.close();
// dispose of temporary files backing this workbook on disk
}catch (Exception e) {
e.printStackTrace();
}
Reporter.log("Successfully Close All files");
Add_Log.info("Successfully Close All files");
}
// Compare Two Sheets
public boolean compareTwoSheets(XSSFSheet sheet1, XSSFSheet sheet2, SXSSFSheet sheet) throws IOException {
int firstRow1 = sheet1.getFirstRowNum();
int lastRow1 = sheet1.getLastRowNum();
boolean equalSheets = true;
for (int i = firstRow1; i <= lastRow1; i++) {
Reporter.log("\n\nComparing Row " + i);
Add_Log.info("\n\nComparing Row " + i);
XSSFRow row1 = sheet1.getRow(i);
XSSFRow row2 = sheet2.getRow(i);
//row3edit = sheet.getRow(i);
for(int rownum = 0; rownum < 100; rownum++){
row3edit= sheet.createRow(rownum);
}
if (!compareTwoRows(row1, row2, row3edit)) {
equalSheets = false;
// Write if not equal
// Get error here java.lang.NullPointerException for row3edit.setRowStyle(cellStyleRed);
//if disable test is completed Successfully without writing result file
row3edit.setRowStyle(cellStyleRed);
Reporter.log("Row " + i + " - Not Equal");
Add_Log.info("Row " + i + " - Not Equal");
// break;
} else {
Reporter.log("Row " + i + " - Equal");
Add_Log.info("Row " + i + " - Equal");
}
}
// Write if not equal
opstr = new FileOutputStream(""\\\\sd\\comparisonfile\\ResultFile.xlsx");
sxssfWorkbook.write(opstr);
opstr.close();
return equalSheets;
}
// Compare Two Rows
public boolean compareTwoRows(XSSFRow row1, XSSFRow row2, SXSSFRow row3edit) throws IOException {
if ((row1 == null) && (row2 == null)) {
return true;
} else if ((row1 == null) || (row2 == null)) {
return false;
}
int firstCell1 = row1.getFirstCellNum();
int lastCell1 = row1.getLastCellNum();
boolean equalRows = true;
// Compare all cells in a row
for (int i = firstCell1; i <= lastCell1; i++) {
XSSFCell cell1 = row1.getCell(i);
XSSFCell cell2 = row2.getCell(i);
for(int cellnum = 0; cellnum < 10; cellnum++){
Cell = row3edit.createCell(cellnum);
String address = new CellReference(Cell).formatAsString();
Cell.setCellValue(address);
}
if (!compareTwoCells(cell1, cell2)) {
equalRows = false;
Reporter.log(" Cell " + i + " - NOt Equal " + cell1 + " === " + cell2);
Add_Log.info(" Cell " + i + " - NOt Equal " + cell1 + " === " + cell2);
break;
} else {
Reporter.log(" Cell " + i + " - Equal " + cell1 + " === " + cell2);
Add_Log.info(" Cell " + i + " - Equal " + cell1 + " === " + cell2);
}
}
return equalRows;
}
// Compare Two Cells
@SuppressWarnings("deprecation")
public static boolean compareTwoCells(XSSFCell cell1, XSSFCell cell2) {
if ((cell1 == null) && (cell2 == null)) {
return true;
} else if ((cell1 == null) || (cell2 == null)) {
return false;
}
boolean equalCells = false;
int type1 = cell1.getCellType();
int type2 = cell2.getCellType();
if (type2 == type1) {
if (cell1.getCellStyle().equals(cell2.getCellStyle())) {
// Compare cells based on its type
switch (cell1.getCellType()) {
case HSSFCell.CELL_TYPE_FORMULA:
if (cell1.getCellFormula().equals(cell2.getCellFormula())) {
equalCells = true;
} else {
}
break;
case HSSFCell.CELL_TYPE_NUMERIC:
if (cell1.getNumericCellValue() == cell2.getNumericCellValue()) {
equalCells = true;
} else {
}
break;
case HSSFCell.CELL_TYPE_STRING:
if (cell1.getStringCellValue().equals(cell2.getStringCellValue())) {
equalCells = true;
} else {
}
break;
case HSSFCell.CELL_TYPE_BLANK:
if (cell2.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
equalCells = true;
} else {
}
break;
case HSSFCell.CELL_TYPE_BOOLEAN:
if (cell1.getBooleanCellValue() == cell2.getBooleanCellValue()) {
equalCells = true;
} else {
}
break;
case HSSFCell.CELL_TYPE_ERROR:
if (cell1.getErrorCellValue() == cell2.getErrorCellValue()) {
equalCells = true;
} else {
}
break;
default:
if (cell1.getStringCellValue().equals(cell2.getStringCellValue())) {
equalCells = true;
} else {
}
break;
}
} else {
return false;
}
} else {
return false;
}
return equalCells;
}
}
1 ответ
Решение
В общем, 40000 строк - это очень небольшой объем данных, и я не хотел бы предлагать использовать потоковую передачу в этом случае. Вместо этого вы можете легко удерживать 40000 строк в книге XSSF, если просто предоставите достаточно памяти. Используя книгу XSSF, вы можете напрямую изменять содержимое, как вы пытались.
Однако, если вы работаете с действительно большими данными 1 Милл. строк и многих столбцов, тогда поможет следующий подход:
1) задействовать Excel Streaming Reader 2) прочитать оба файла F1 и F2 одновременно и сравнить строка за строкой и ячейка за ячейкой 3) на основе найденного различия, создать новую строку с новыми ячейками и записать это в свой файл результатов F3
Вы не можете изменять существующие ячейки в SXSSFWorkbook.