POI获取合并单元格时,如果是遍历获取合并单元格的所有子单元格的值,office的excel都会有值,wps的excel只会第一个子单元格有值,其他子单元格都没值,即cell=null。故意凡是获取合并单元格的值,都获取第一个子单元格的值即可
package com.oop.createObj;
import org.apache.poi.hssf.usermodel.HSSFCell;
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.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import java.io.FileInputStream;
import java.util.Arrays;
import java.util.stream.Stream;
/**
* @ProjectName: spring-boot-lantian
* @Package: com.oop.createObj
* @ClassName: SendFactoryTest
* @Author: zhangs
* @Email: 853632587@qq.com
* @Description: 工厂模式测试
* @Date: 2020/11/4 15:53
* @Version: 1.0
*/
public class SendFactoryTest {
public static void main(String[] args) throws Exception {
//Sender mail= SendFactory.produceMail();
//mail.send();
HSSFWorkbook wk=new HSSFWorkbook(new FileInputStream("E://订单导出.xls"));
HSSFSheet sheet=wk.getSheetAt(0);
int rows=sheet.getPhysicalNumberOfRows();
int cells=sheet.getRow(0).getPhysicalNumberOfCells();
for(int r=0;r<rows;r++){
HSSFRow row=sheet.getRow(r);
StringBuilder str=new StringBuilder();
for(int c=0;c<cells;c++){
HSSFCell cell=row.getCell(c);
if(isMergedRegion(sheet,r,c)){
str.append(">>>"+getMergedRegionValue(sheet,r,c)+"||");
}else{
str.append(">>>"+row.getCell(c)+"||");
}
}
System.out.println(r+"str"+str);
str.setLength(0);
}
}
/**
* 判断指定的单元格是否是合并单元格
* @param sheet
* @param row 行下标
* @param column 列下标
* @return
*/
private static boolean isMergedRegion(Sheet sheet, int row , int column) {
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;
}
/**
* 获取合并单元格的值
* @param sheet
* @param row
* @param column
* @return
*/
public static String getMergedRegionValue(Sheet sheet ,int row , int column){
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);
return getCellValue(fCell) ;
}
}
}
return null ;
}
/**
* 获取单元格的值
* @param cell
* @return
*/
public static String getCellValue(Cell cell){
if(cell == null) return "";
return cell.toString();
}
public static void main1(String[] args){
Object[][] rowName = new Object[][]{
{"序号",2000,"NM"},
{"分组编号",2000,"NM"},
{"订单编号",6500,"NM"},
{"第三方订单号",6500,"CM"},
{"SKU",2500,"NM"},
{"订单金额",2500,"CM"},
{"运费",2500,"CM"},
{"商品名称",12000,"NM"},
{"规格",7000,"NM"},
{"单位",2500,"NM"},
{"数量",2500,"NM"},
{"单价",2500,"NM"},
{"金额",2500,"NM"}, {"税率",2500,"NM"},{"税额",2500,"NM"},{"结算主体",7000,"M"},{"发票抬头",12000,"M"},{"发票类型",2500,"M"},{"客户名称",7000,"M"},{"项目名称",5000,"M"},{"采购员",5000,"M"},
{"收件人",6500,"M"},{"详细地址",7000,"M"},{"店铺",7000,"M"},{"订单状态",2500,"CM"},{"订单标签",4500,"CM"},{"结算状态",4500,"CM"},{"发票编号",5000,"CM"},{"订单开始时间",5000,"CM"},{"审批完成时间",5000,"CM"},{"订单完成时间",5000,"CM"},
{"下单月",5000,"CM"}, {"订单完成月",5000,"CM"},{"商品一级分类",5000,"NM"}, {"商品二级分类",5000,"NM"}, {"商品三级分类",5000,"NM"}};
Stream.iterate(0, i -> i + 1).limit(rowName.length).forEach(i -> System.out.println(rowName[i][2]));
}
}