使用正在复制到主文件中的 Java POI 跳过后续工作表中的第一行

Question

我有一个合并过程，其中包含多个工作表的多个工作簿合并到一个主文件中。 工作表按名称组合在一起。

复制第一个工作簿内容(工作表(后，后续(第 2、3、第 4 个等(工作簿内容(工作表(应跳过每个工作表中的第一行并复制到主文件。

• 我将如何在没有的情况下循环访问后续工作簿工作表 复制第一行(标题(？

• 简而言之，我想保留标题(第一行(从第一行开始 迭代，然后复制之后的每个工作簿工作表。应跳过第一行 (标题(

我在下面包含了示例代码。

private static void copySheets( XSSFSheet newSheet, XSSFSheet sheet, boolean copyStyle){     
int newRownumber = newSheet.getLastRowNum() + 1;
int maxColumnNum = 0;     
Map<Integer, XSSFCellStyle> styleMap = (copyStyle) ? new HashMap<Integer, XSSFCellStyle>() : null;    



for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) {     
XSSFRow srcRow = sheet.getRow(i);     
XSSFRow destRow = newSheet.createRow(i + newRownumber); 

if (srcRow != null) {     
//copyRow(newWorkbook, sheet, newSheet, srcRow, destRow, styleMap);  
copyRow(sheet, newSheet, srcRow, destRow, styleMap); 
if (srcRow.getLastCellNum() > maxColumnNum) {     
maxColumnNum = srcRow.getLastCellNum();     
}     
}     
}   
for (int i = 0; i <= maxColumnNum; i++) {     
newSheet.setColumnWidth(i, sheet.getColumnWidth(i));     
}     
}     

public static void merge(File file) throws IOException {
XSSFWorkbook book = new XSSFWorkbook();
String directoryName = "C: users\documents";
File directory = new File(directoryName);
File[] fList = directory.listFiles();
for (File file1 : fList){
if (file1.isFile()){
String ParticularFile = file1.getName();
File(directoryName+"\"+ParticularFile));
FileInputStream fin = new FileInputStream(new File(directoryName+"\"+ParticularFile));
XSSFWorkbook b = new XSSFWorkbook(fin);


INTERGRATED CODE
public static void merge(File file) throws IOException {
XSSFWorkbook book = new XSSFWorkbook();
System.out.println(file.getName());
String directoryName = "C:\ Users\Documents";
File directory = new File(directoryName);
File[] fList = directory.listFiles();
Boolean doNotReadHeaders = false;
Integer fileCount = 0;
for (File file1 : fList){
if (file1.isFile()){
doNotReadHeaders = (fileCount == 0) ? false : true;
String ParticularFile = file1.getName();
XSSFSheet sheet = b.getSheetAt(0);
for (int i = 1; i < (doNotReadHeaders ? sheet2.getPhysicalNumberOfRows() - 1 : sheet.getPhysicalNumberOfRows()); i++) { //Skipping headers only for the first workbook
Row row = sheet.getRow(ii);
if (row != null) { 

for (int i = 0; i < b.getNumberOfSheets(); i++) {
XSSFSheet sheet = book.getSheet(b.getSheetName(i)); 
if (sheet == null) 
sheet = book.createSheet(b.getSheetName(i));
for(int worksheetIndex = 0; worksheetIndex<b.getNumberOfSheets(); worksheetIndex++)
{    
b.setSheetName(worksheetIndex, worksheetName);
System.out.println("test "+ worksheetIndex+ " " + worksheetName);
}
copySheets(sheet, b.getSheetAt(i));
}
}
try {
writeFile(book, file);
}catch(Exception e) {
e.printStackTrace();
}
} 
} 

Answer 1

for(int i = 1; i < sheet.getPhysicalNumberOfRows() - 1 ; i++){
Row row = sheet.getRow(i);
if(row != null){
// process your row
}
}

这样，您可以跳过标题，最多可以阅读不为空的行！

编辑- 如果您有要读取的工作簿列表，则可以管理检查以仅读取第一个工作簿的标题，而不读取其余工作簿的标题。

Boolean doNotReadHeaders = false;
for (int f = 0; f < filesToReadCount; f++) {
doNotReadHeaders = (f == 0) ? false : true;
XSSFWorkbook workbook = new XSSFWorkbook(inputStream); // retrieve input stream acc. to your file lists
XSSFSheet sheet = workbook.getSheetAt(0);
for (int i = 1; i < (doNotReadHeaders ? sheet.getPhysicalNumberOfRows() - 1 : sheet.getPhysicalNumberOfRows()); i++) { //Skipping headers only for the first workbook
Row row = sheet.getRow(i);
if (row != null) {
// process your row
}
}
}

编辑-

public static void merge(File file) throws IOException {
XSSFWorkbook book = new XSSFWorkbook();
String directoryName = "C: users\documents";
File directory = new File(directoryName);
File[] fList = directory.listFiles();
Boolean doNotReadHeaders = false;
Integer fileCount = 0;
for (File file1: fList) {
if (file1.isFile()) {
doNotReadHeaders = (fileCount == 0) ? false : true;
String ParticularFile = file1.getName();
FileInputStream fin = new FileInputStream(new File(directoryName + "\" + ParticularFile));
XSSFWorkbook workbook = new XSSFWorkbook(fin);
XSSFSheet sheet = workbook.getSheetAt(0);
for (int i = 1; i < (doNotReadHeaders ? sheet.getPhysicalNumberOfRows() - 1 : sheet.getPhysicalNumberOfRows()); i++) { //Skipping headers only for the first workbook
Row row = sheet.getRow(i);
if (row != null) {
// process your row
}
}
fileCount++;
}
}
}

我刚刚将您的代码与我的代码集成在一起。

编辑 -最终代码

private static void copySheets(XSSFSheet newSheet, XSSFSheet sheet, boolean doNotReadHeaders, boolean copyStyle) {
int newRownumber = newSheet.getLastRowNum() + 1;
int maxColumnNum = 0;
Map< Integer, XSSFCellStyle > styleMap = (copyStyle) ? new HashMap< Integer, XSSFCellStyle >() : null;
int FIRST_ROW_TO_GET = doNotReadHeaders ? 1 : 0;
for (int ii = FIRST_ROW_TO_GET; ii < sheet.getLastRowNum(); ii++) {
XSSFRow row = sheet.getRow(ii);
if (row != null) {
// The whole row is blank
//continue;
} else {
for (int i = (doNotReadHeaders ? sheet.getFirstRowNum() + 1 : sheet.getFirstRowNum()); i < (doNotReadHeaders ? sheet.getPhysicalNumberOfRows() -1 : sheet.getPhysicalNumberOfRows()); i++) {
XSSFRow srcRow = sheet.getRow(i);
XSSFRow destRow = newSheet.createRow(i + newRownumber);
if (srcRow != null) {
//copyRow(newWorkbook, sheet, newSheet, srcRow, destRow, styleMap);  
//copyRow(sheet, newSheet, srcRow, destRow, styleMap);
if (srcRow.getLastCellNum() > maxColumnNum) {
maxColumnNum = srcRow.getLastCellNum();
}
}
}
}
}
for (int i = 0; i <= maxColumnNum; i++) {
newSheet.setColumnWidth(i, sheet.getColumnWidth(i));
}
}
public static void merge(File file) throws IOException {
XSSFWorkbook book = new XSSFWorkbook();
String directoryName = "C: users\documents";
File directory = new File(directoryName);
File[] fList = directory.listFiles();
Boolean doNotReadHeaders = false;
Integer fileCount = 0;
for (File file1: fList) {
if (file1.isFile()) {
doNotReadHeaders = (fileCount == 0) ? false : true;
String ParticularFile = file1.getName();
FileInputStream fin = new FileInputStream(new File(directoryName + "\" + ParticularFile));
XSSFWorkbook workbook = new XSSFWorkbook(fin);
XSSFSheet sheet = workbook.getSheetAt(0);
for (int j = 0; j < workbook.getNumberOfSheets(); j++) {
XSSFSheet innerSheet = workbook.getSheet(workbook.getSheetName(j));
if (innerSheet == null)
innerSheet = workbook.createSheet(workbook.getSheetName(j));
String worksheetName = "Sample sheet ";
for (int worksheetIndex = 0; worksheetIndex < workbook.getNumberOfSheets(); worksheetIndex++) {
workbook.setSheetName(worksheetIndex, worksheetName + worksheetIndex);
System.out.println("test " + worksheetIndex + " " + worksheetName);
}
copySheets(sheet, workbook.getSheetAt(j),doNotReadHeaders,false);
}
fileCount++;
}
try {
writeFile(workbook, file1);
} catch (Exception e) {
e.printStackTrace();
}
}
}

现在运行这个并让我知道。