java获取合并单元格数据_POI-java读取Excel(包含合并单元格)

importorg.apache.poi.hssf.usermodel.HSSFWorkbook;importorg.apache.poi.ss.usermodel.Cell;importorg.apache.poi.ss.usermodel.Row;importorg.apache.poi.ss.usermodel.Sheet;importorg.apache.poi.ss.usermodel.Workbook;importorg.apache.poi.ss.util.CellRangeAddress;importorg.apache.poi.xssf.usermodel.XSSFWorkbook;importorg.springframework.beans.factory.annotation.Autowired;importorg.springframework.stereotype.Service;importorg.springframework.transaction.annotation.Transactional;importjava.io.InputStream;importjava.util.ArrayList;importjava.util.List;public class ExcelImportServiceImpl{

@Overridepublic String importExcel(InputStream inputStream, String fileName) throwsException{

String message= "Import success";boolean isE2007 = false;//判断是否是excel2007格式

if(fileName.endsWith("xlsx")){

isE2007= true;

}int rowIndex = 0;try{

InputStream input= inputStream; //建立输入流

Workbook wb;//根据文件格式(2003或者2007)来初始化

if(isE2007){

wb= newXSSFWorkbook(input);

}else{

wb= newHSSFWorkbook(input);

}

Sheet sheet= wb.getSheetAt(0); //获得第一个表单

int rowCount = sheet.getLastRowNum()+1;for(int i = 1; i < rowCount;i++){

rowIndex=i;

Row row ;for(int j = 0;j<26;j++){if(isMergedRegion(sheet,i,j)){

System.out.print(getMergedRegionValue(sheet,i,j)+"\t");

}else{

row=sheet.getRow(i);

System.out.print(row.getCell(j)+"\t");

}

}

System.out.print("\n");

}

}catch(Exception ex) {

message= "Import failed, please check the data in "+rowIndex+" rows ";

}returnmessage;

}/*** 获取单元格的值

*@paramcell

*@return

*/

publicString getCellValue(Cell cell){if(cell == null) return "";returncell.getStringCellValue();

}/*** 合并单元格处理,获取合并行

*@paramsheet

*@returnList*/

public ListgetCombineCell(Sheet sheet)

{

List list = new ArrayList<>();//获得一个 sheet 中合并单元格的数量

int sheetmergerCount =sheet.getNumMergedRegions();//遍历所有的合并单元格

for(int i = 0; i

{//获得合并单元格保存进list中

CellRangeAddress ca =sheet.getMergedRegion(i);

list.add(ca);

}returnlist;

}private int getRowNum(ListlistCombineCell,Cell cell,Sheet sheet){int xr = 0;int firstC = 0;int lastC = 0;int firstR = 0;int lastR = 0;for(CellRangeAddress ca:listCombineCell)

{//获得合并单元格的起始行, 结束行, 起始列, 结束列

firstC =ca.getFirstColumn();

lastC=ca.getLastColumn();

firstR=ca.getFirstRow();

lastR=ca.getLastRow();if(cell.getRowIndex() >= firstR && cell.getRowIndex() <=lastR)

{if(cell.getColumnIndex() >= firstC && cell.getColumnIndex() <=lastC)

{

xr=lastR;

}

}

}returnxr;

}/*** 判断单元格是否为合并单元格，是的话则将单元格的值返回

*@paramlistCombineCell 存放合并单元格的list

*@paramcell 需要判断的单元格

*@paramsheet sheet

*@return

*/

public String isCombineCell(ListlistCombineCell,Cell cell,Sheet sheet)throwsException{int firstC = 0;int lastC = 0;int firstR = 0;int lastR = 0;

String cellValue= null;for(CellRangeAddress ca:listCombineCell)

{//获得合并单元格的起始行, 结束行, 起始列, 结束列

firstC =ca.getFirstColumn();

lastC=ca.getLastColumn();

firstR=ca.getFirstRow();

lastR=ca.getLastRow();if(cell.getRowIndex() >= firstR && cell.getRowIndex() <=lastR)

{if(cell.getColumnIndex() >= firstC && cell.getColumnIndex() <=lastC)

{

Row fRow=sheet.getRow(firstR);

Cell fCell=fRow.getCell(firstC);

cellValue=getCellValue(fCell);break;

}

}else{

cellValue= "";

}

}returncellValue;

}/*** 获取合并单元格的值

*@paramsheet

*@paramrow

*@paramcolumn

*@return

*/

public String getMergedRegionValue(Sheet sheet ,int row , intcolumn){int sheetMergeCount =sheet.getNumMergedRegions();for(int i = 0 ; i < sheetMergeCount ; i++){

CellRangeAddress ca=sheet.getMergedRegion(i);int firstColumn =ca.getFirstColumn();int lastColumn =ca.getLastColumn();int firstRow =ca.getFirstRow();int lastRow =ca.getLastRow();if(row >= firstRow && row <=lastRow){if(column >= firstColumn && column <=lastColumn){

Row fRow=sheet.getRow(firstRow);

Cell fCell=fRow.getCell(firstColumn);returngetCellValue(fCell) ;

}

}

}return null;

}/*** 判断指定的单元格是否是合并单元格

*@paramsheet

*@paramrow 行下标

*@paramcolumn 列下标

*@return

*/

private boolean isMergedRegion(Sheet sheet,int row ,intcolumn) {int sheetMergeCount =sheet.getNumMergedRegions();for (int i = 0; i < sheetMergeCount; i++) {

CellRangeAddress range=sheet.getMergedRegion(i);int firstColumn =range.getFirstColumn();int lastColumn =range.getLastColumn();int firstRow =range.getFirstRow();int lastRow =range.getLastRow();if(row >= firstRow && row <=lastRow){if(column >= firstColumn && column <=lastColumn){return true;

}

}

}return false;

}

}