英文:
XLSX file: Unable to write to the same workbook
问题
我已经编写了一段代码，用于从输入的 Excel 中挑选特定区域的数据，然后转置并粘贴到输出的 Excel 中。这个读取和写入是两个独立的方法，我从主方法中调用它们。然而，一旦代码运行并且文件生成了，它只显示了最后一次写入方法调用所写入的数据。根据我的理解，这可能是因为我的代码每次都创建了一个新的工作簿对象。尽管我尝试使用代码中的静态关键字来解决这个问题。请参阅下面的代码。
public class DataExtractor {
static FileOutputStream output = null;
static XSSFWorkbook workbook = null;
static XSSFSheet sheet = null;
static XSSFWorkbook outputWorkbook = null;
static XSSFSheet outputSheet = null;
static XSSFRow row = null;
DataFormatter dataFormatter = null;
public DataExtractor(XSSFWorkbook workbook, XSSFSheet sheet, XSSFWorkbook outputWorkbook, XSSFSheet outputSheet) {
DataExtractor.workbook = workbook;
DataExtractor.sheet = sheet;
DataExtractor.outputWorkbook = outputWorkbook;
DataExtractor.outputSheet = outputSheet;
}
private ArrayList<ArrayList<String>> fetchDataFromInputExcel() {
// 从 Excel 中提取数据的代码/仅读取某些网格的 Excel。
}
private ArrayList<ArrayList<String>> pasteTransposedDataIntoOutputExcel() {
// 转置数据并将其粘贴到输出的 Excel 中/写入到 Excel 的代码。
}
public static void main(String args[]) {
File file = new File("xlsx 文件的路径");
workbook = new XSSFWorkbook(new FileInputStream(file));
sheet = workbook.getSheet("SHEETNAME");
outputWorkbook = new XSSFWorkbook();
outputSheet = outputWorkbook.createSheet("OUTPUT");
DataExtractor obj = new DataExtractor(workbook, sheet, outputWorkbook, outputSheet);
ArrayList<ArrayList<String>> header = dataExtractor.fetchDataFromInputExcel(//传递用于指定网格区域行和列的参数);
ArrayList<ArrayList<String>> data = dataExtractor.fetchDataFromInputExcel(//传递用于指定网格区域行和列的参数);
dataExtractor.pasteTransposedDataIntoOutputExcel(dataExtractor, data, 1, 8);
dataExtractor.pasteTransposedDataIntoOutputExcel(dataExtractor, header, 1, 1);
output = new FileOutputStream(new File("输出 xlsx 文件的路径"));
outputWorkbook.write(output);
}
}
“dataExtractor.pasteTransposedDataIntoOutputExcel(dataExtractor, header, 1, 1)`” 操作只执行了，我看不到前一个操作的数据。请提供建议。
“transposeAndPasteData” 方法如下：
private boolean transposeAndPasteData(DataExtractor dataExtractor, ArrayList<ArrayList<String>> data, int startRowNum, int startColNum) {
XSSFCell cell = null;
XSSFRow row = null;
outputWorkbook = dataExtractor.getOutputWorkbook();
outputSheet = dataExtractor.getOutputSheet();
try {
int startRowIndex = startRowNum - 1;
int startColIndex = startColNum - 1;
Iterator<ArrayList<String>> colItr = data.iterator();
while (colItr.hasNext()) {
row = outputSheet.createRow(startRowIndex++);
ArrayList<String> colData = colItr.next();
Iterator<String> rowItr = colData.iterator();
while (rowItr.hasNext()) {
cell = row.createCell(startColIndex++);
cell.setCellValue(rowItr.next());
}
startColIndex = startColNum - 1;
}
dataExtractor.setOutputSheet(outputSheet);
return true;
} catch (Exception e) {
e.printStackTrace();
return false;
}
}
谢谢，
阿曼
英文:
I have written a code to pick data for certain area in the input excel and the transpose and paste it in the output excel. This read and write are 2 separate methods, which i call from main method. However, once the code runs and the file is generated, it only shows the data written for the last write method call. As per my understanding, it has to be because, my code is creating a new workbook object every time. Though i have tried to overcome this by using static keyword in my code. Please see below for the code.
public class DataExtractor {
static FileOutputStream output = null;
static XSSFWorkbook workbook = null;
static XSSFSheet sheet = null;
static XSSFWorkbook outputWorkbook = null;
static XSSFSheet outputSheet = null;
static XSSFRow row = null;
DataFormatter dataFormatter = null;
public DataExtractor(XSSFWorkbook workbook, XSSFSheet sheet, XSSFWorkbook outputWorkbook, XSSFSheet outputSheet) {
DataExtractor.workbook = workbook;
DataExtractor.sheet = sheet;
DataExtractor.outputWorkbook = outputWorkbook;
DataExtractor.outputSheet = outputSheet;
}
private ArrayList<ArrayList<String>> fetchDataFromInputExcel(){
//code for fetching data from excel/reading excel only for some grids.
}
private ArrayList<ArrayList<String>> pasteTransposedDataIntoOutputExcel(){
//code for transposing data and pasting it into output excel/writing to excel.
}
public static void main(String args[]){
File file = new File("path of xlsx file");
workbook = new XSSFWorkbook(new FileInputStream(file));
sheet = workbook.getSheet("SHEETNAME");
outputWorkbook = new XSSFWorkbook();
outputSheet = outputWorkbook.createSheet("OUTPUT");
DataExtractor obj = new DataExtractor(workbook, sheet, outputWorkbook, outputSheet);
ArrayList<ArrayList<String>> header = dataExtractor.fetchDataFromInputExcel(//args for mentioning grid area row and col are passed here);
ArrayList<ArrayList<String>> data = dataExtractor.fetchDataFromInputExcel(//args for mentioning grid area row and col are passed here);
dataExtractor.pasteTransposedDataIntoOutputExcel(dataExtractor, data, 1, 8);
dataExtractor.pasteTransposedDataIntoOutputExcel(dataExtractor, header, 1, 1);
output = new FileOutputStream(new File("path of output.xlsx file"));
outputWorkbook.write(output);
}
}
Only dataExtractor.pasteTransposedDataIntoOutputExcel(dataExtractor, header, 1, 1) operation is performed and i do not see the data for the previous one. Please suggest.
Method for transposeAndPaste:
private boolean transposeAndPasteData(DataExtractor dataExtractor, ArrayList<ArrayList<String>> data, int startRowNum, int startColNum){
XSSFCell cell = null;
XSSFRow row = null;
outputWorkbook = dataExtractor.getOutputWorkbook();
outputSheet = dataExtractor.getOutputSheet();
try {
int startRowIndex = startRowNum - 1;
int startColIndex = startColNum - 1;
//paste data row wise
//for(int rowNum = startColNum-1; rowNum<startColNum+data.size(); rowNum++) {}
Iterator<ArrayList<String>> colItr = data.iterator();
while(colItr.hasNext()) {
row = outputSheet.createRow(startRowIndex++);
ArrayList<String> colData = colItr.next();
Iterator<String> rowItr = colData.iterator();
while(rowItr.hasNext()) {
cell = row.createCell(startColIndex++);
cell.setCellValue(rowItr.next());
}
startColIndex = startColNum - 1;
}
dataExtractor.setOutputSheet(outputSheet);
return true;
}catch(Exception e) {
e.printStackTrace();
return false;
}
Thanks,
Aman
专注分享java语言的经验与见解，让所有开发者获益！
评论