POI获取excle合并单元格以及没有合并单元格的数据 5C

问题描述:如果excle表里最后一行数据是没有合并的,就取不到值,如果最后一行是合并过的数据,就都能取得到了(合并未合并的都可以获取到)。有大神可以帮忙看一下代码解决一下吗,谢谢啦。

package service;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.Set;

import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import entity.Table;

public class ExcelOp {
public String excelPath;

public ExcelOp(String excelPath) {
    this.excelPath = excelPath;
}

public String getExcelPath() {
    return excelPath;
}

public void setExcelPath(String excelPath) {
    this.excelPath = excelPath;
}


/**
 * 
 * @param excelPath:excel路径
 * @param args:需要操作的列
 * @throws IOException 
 */
@SuppressWarnings("resource")
public ArrayList<Table>  readExcel() throws IOException{
    File excelFile = new File(excelPath);
    InputStream is = new FileInputStream(excelFile); // 获取文件输入流  

    ArrayList<Table> list = null;

    String suffix = excelPath.substring(excelPath.lastIndexOf(".")+1);
    if(suffix.equals("xls")){
        HSSFWorkbook workbook = null;
        workbook = new HSSFWorkbook(is);

        if(workbook != null){  

            HSSFSheet sheet = workbook.getSheetAt(1);//获取第二张表
            if(sheet == null){
                System.out.println();
                return null;
            }
            list = new ArrayList<Table>(); 

            int combineLines = 1;
            int number = 1;
            //读取行
            for (int rowNum = 3; rowNum < sheet.getLastRowNum(); rowNum+=combineLines) {
                HSSFRow row = sheet.getRow(rowNum);

                if (row == null) continue;
                Table tb = new Table(); 
                tb.setNumber("3.1." + number);
//              tb.setTestData(row.getCell(0).toString());
                tb.setTitle(row.getCell(1).toString());
                //Word换行主要有两大类,一类是表格单元格文本的换行,另一类是表格之外的文本的换行。对于表格外的文本我们可以使用“\r”或者“(char)11”来换行,然而对于表格内的文本我们只能使用“(char)11”来进行换行操作。

// tb.setFunctionPoint(row.getCell(2).toString().replace('\n', (char)11));
tb.setFunctionPoint(row.getCell(2).toString());
combineLines = isMergedRegion(sheet, rowNum, 1);
String process = "";
String conclusion = "";
Set s = new HashSet();
for (int i = rowNum; i< rowNum + combineLines; i++) {
//步骤
HSSFRow r1 = sheet.getRow(i);
// process += r1.getCell(5).toString() + (char)11 + (char)11;
process += r1.getCell(5).toString() + "\n" + "\n";
//结果
String result = r1.getCell(7).toString();
s.add(result);
}

                tb.setProcess(process);
                tb.setConclusion(conclusion);
                number++;
                list.add(tb);
            }
        }
    }

return list;
}

//返回合并单元格占用行数
public int isMergedRegion(Sheet sheet, int row, int column) {
    int sheetMergeCount = sheet.getNumMergedRegions();
    for (int i = 0; i < sheetMergeCount; i++) {
        CellRangeAddress range = sheet.getMergedRegion(i);
        int firstRow = range.getFirstRow();
        int lastRow = range.getLastRow();
        int firstColumn = range.getFirstColumn(); 
        int lastColumn = range.getLastColumn();   
        if (row >= firstRow && row <= lastRow) {
            if(column >= firstColumn && column <= lastColumn){ 
                return lastRow - firstRow + 1;
            }
        }
    }
    return 1;
}

}

2个回答

没有理解你需要解决的是什么

已经解决了,在getLastRowNum()+1即可

Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!