java读取含有合并行的excel

最新推荐文章于 2024-06-25 15:47:58 发布

qq_36043775

最新推荐文章于 2024-06-25 15:47:58 发布

阅读量2.6k

点赞数 4

分类专栏： java 文章标签： poi excel java

本文链接：https://blog.csdn.net/qq_36043775/article/details/118189954

版权

java 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

该博客详细介绍了如何使用Java的Apache POI库解析Excel文件，包括识别不同版本的Excel格式、读取单元格数据、处理合并单元格以及创建和填充实体类对象。示例代码展示了如何从Excel中提取报表信息，如班次、生产线、检查项目等，并存储到 InspectionReport 和 InspectionItem 实体中。

摘要由CSDN通过智能技术生成

excel格式如下：

代码如下：

package com.example.demo.excel;

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.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

public class ExcelTest {


    public String addReportByExcel(InputStream inputStream, String fileName)
            {
        String message = "Import success";

        boolean isE2007 = false;    //格式判断
        if(fileName.endsWith("xlsx")){
            isE2007 = true;
        }

        int rowIndex = 0;
        int columnIndex = 0;
        try {
            InputStream input = inputStream;  //建立输入流
            Workbook wb  = null;
            if(isE2007){
                wb = new XSSFWorkbook(input);
            }else{
                wb = new HSSFWorkbook(input);
            }
            Sheet sheet = wb.getSheetAt(0);    //获得第一个表单

            //System.out.println("总行数:"+sheet.getLastRowNum());

            List<CellRangeAddress> cras = getCombineCell(sheet);
            int count = sheet.getLastRowNum()+1;//总行数

            List<InspectionReport> irs = new ArrayList<>();
            for(int i = 1; i < count;i++){
                rowIndex = i;
                Row row = sheet.getRow(i);
                InspectionReport ir = new InspectionReport();

                ir.setReportName(getCellValue(row.getCell(0)));
                ir.setShift(Double.valueOf(getCellValue(row.getCell(1))).intValue());
                ir.setLine(getCellValue(row.getCell(2)));
                ir.setStationCode(getCellValue(row.getCell(3)));
                ir.setArea(Double.valueOf(getCellValue(row.getCell(4))).intValue());
                ir.setReportStatus(Double.valueOf(getCellValue(row.getCell(5))).intValue());

                List<InspectionItem> items = new ArrayList<>();
                if(isMergedRegion(sheet,i,0)){
                    int lastRow = getRowNum(cras,sheet.getRow(i).getCell(0),sheet);

                    for(;i<=lastRow;i++){
                        row = sheet.getRow(i);
                        InspectionItem item = new InspectionItem();
                        item.setItem(getCellValue(row.getCell(6)));
                        item.setMethod(getCellValue(row.getCell(7)));
                        item.setMode(getCellValue(row.getCell(8)));
                        item.setStandardValue(getCellValue(row.getCell(9)));
                        item.setDeviationValue(getCellValue(row.getCell(10)));
                        String pinci = getCellValue(row.getCell(11));
                        Double d = Double.valueOf(pinci);
                        item.setFrequency(d.intValue());
                        items.add(item);
                    }
                    i--;
                }else{
                    row = sheet.getRow(i);
                    InspectionItem item = new InspectionItem();
                    item.setItem(getCellValue(row.getCell(6)));
                    item.setMethod(getCellValue(row.getCell(7)));
                    item.setMode(getCellValue(row.getCell(8)));
                    item.setStandardValue(getCellValue(row.getCell(9)));
                    item.setDeviationValue(getCellValue(row.getCell(10)));
                    String pinci = getCellValue(row.getCell(11));
                    Double d = Double.valueOf(pinci);
                    item.setFrequency(d.intValue());
                    items.add(item);
                }
                ir.setItems(items);
                irs.add(ir);

            }

            System.out.println(irs);


        } catch (Exception ex) {
            return "error"
        }
        return message;
    }

    /**
     * 获取单元格的值
     * @param cell
     * @return
     */
    public String getCellValue(Cell cell){
        if(cell == null) return "";
        if(cell.getCellType() == Cell.CELL_TYPE_STRING){
            return cell.getStringCellValue();
        }else if(cell.getCellType() == Cell.CELL_TYPE_BOOLEAN){
            return String.valueOf(cell.getBooleanCellValue());
        }else if(cell.getCellType() == Cell.CELL_TYPE_FORMULA){
            return cell.getCellFormula() ;
        }else if(cell.getCellType() == Cell.CELL_TYPE_NUMERIC){
            return String.valueOf(cell.getNumericCellValue());
        }
        return "";
    }
    /**
     * 合并单元格处理,获取合并行
     * @param sheet
     * @return List<CellRangeAddress>
     */
    public List<CellRangeAddress> getCombineCell(Sheet sheet)
    {
        List<CellRangeAddress> list = new ArrayList<CellRangeAddress>();
        //获得一个 sheet 中合并单元格的数量
        int sheetmergerCount = sheet.getNumMergedRegions();
        //遍历所有的合并单元格
        for(int i = 0; i<sheetmergerCount;i++)
        {
            //获得合并单元格保存进list中
            CellRangeAddress ca = sheet.getMergedRegion(i);
            list.add(ca);
        }
        return list;
    }

    private int getRowNum(List<CellRangeAddress> listCombineCell,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;
                }
            }

        }
        return xr;

    }
    /**
     * 判断单元格是否为合并单元格，是的话则将单元格的值返回
     * @param listCombineCell 存放合并单元格的list
     * @param cell 需要判断的单元格
     * @param sheet sheet
     * @return
     */
    public String isCombineCell(List<CellRangeAddress> listCombineCell,Cell cell,Sheet sheet)
            throws Exception{
        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 = "";
            }
        }
        return cellValue;
    }

    /**
     * 获取合并单元格的值
     * @param sheet
     * @param row
     * @param column
     * @return
     */
    public 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 sheet
     * @param row 行下标
     * @param column 列下标
     * @return
     */
    private 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;
    }

}

实体类如下

package com.example.demo.excel;

import lombok.Data;

import java.util.List;
@Data
public class InspectionReport {

    private String reportName;

    private Integer shift;

    private String line;

    private String stationCode;

    private Integer area;

    private Integer reportStatus;

    private List<InspectionItem> items;
}

package com.example.demo.excel;

import lombok.Data;

@Data
public class InspectionItem {

    private String item;

    private String method;

    private String mode;

    private String standardValue;

    private String deviationValue;

    private Integer frequency;
}

pom文件引入poi：

        <!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.14</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.14</version>
        </dependency>

由于上传不了表格，我复制了贴上来给你们

报表名称	班次	生产线	站点编号	设备区域	报表状态	检查项目	检查方法	填报方式	标准值	偏差值	频次
import1	1	lc	123	2	1	检查项目1	目测	数值	1	1	1
						检查项目2	填报	文本	2	2	2
						检查项目3	目测	数值	3	3	3
import2	2	lc	456	2	1	检查项目4	目测	数值	4	4	4
						检查项目5	填报	文本	5	5	5
						检查项目6	目测	数值	6	6	6
import3	3	lk	7	2	1	检查项目7	目测	数值	7	7	7
import4	4	lc	789	2	1	检查项目8	目测	数值	8	8	8
						检查项目9	填报	文本	9	9	9
						检查项目10	目测	数值	10	10	10