java通过POI导入合并区域的excel

java通过POI导入合并区域的excel

引入poi相关jar包

<dependencies>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.14</version>
        </dependency>

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.14</version>
        </dependency>
    </dependencies>

创建excel工具类

1. 用于获取excel中的值
2. 获取合并区域的值

package com.ch.poi.utils;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.ss.usermodel.Cell;

import java.util.ArrayList;

/**
 * 文件上传下载常用方法工具类
 * @author chen_
 *
 */
public class UploadUtil {

	/**
	 * 获取单元格的值
	 * @param cell
	 * @return
	 */
	public static String getCellValue(Cell cell) {
		if (cell == null)
			return null;
		String retValue = null;
		cell.setCellType(Cell.CELL_TYPE_STRING);
		switch (cell.getCellType()) {
			case Cell.CELL_TYPE_NUMERIC: //数字
				retValue = String.valueOf(cell.getNumericCellValue()).trim();
				break;
			case Cell.CELL_TYPE_STRING: //字符串
				retValue = cell.getStringCellValue().trim();
				break;
			case Cell.CELL_TYPE_BOOLEAN: //Boolean
				retValue = String.valueOf(cell.getBooleanCellValue()).trim();
				break;
			case Cell.CELL_TYPE_FORMULA: //公式
				retValue = String.valueOf(cell.getNumericCellValue()).trim();
				break;
			case HSSFCell.CELL_TYPE_BLANK://空值
				retValue = "";
				break;
			case HSSFCell.CELL_TYPE_ERROR://故障
				break;
			default:
				break;
		}
		return retValue;
	}

	/**
	 * 获取excel合并区域中的值
	 * @param
	 * @return
	 */
	public static String getExcelValue(ArrayList<ExcelMergeVo2> mergers, Integer rowNum, Integer columnNum){
		for (ExcelMergeVo2 merger :  mergers) {
			Integer startRow = merger.getStartRow();
			Integer endRow = merger.getEndRow();
			Integer startColumn = merger.getStartColumn();
			Integer endColumn = merger.getEndColumn();
			if((rowNum >= startRow && rowNum<= endRow) && (columnNum >= startColumn && columnNum <=endColumn) ){
				return merger.getExcelValue();
			}
		}
		return "";
	}
}

创建一个接收实体，获取合并区域中的值

1. 存储合并区域范围
2. 存储合并区域值

package com.ch.poi.utils;

public class ExcelMergeVo2 {
	
	//区域值
	private String excelValue ;
	
	//开始行
	private Integer startRow;
	
	//结束行
	private Integer endRow;
	
	//开始列
	private Integer startColumn;
	
	//结束列
	private Integer endColumn;

	public String getExcelValue() {
		return excelValue;
	}

	public void setExcelValue(String excelValue) {
		this.excelValue = excelValue;
	}

	public Integer getStartRow() {
		return startRow;
	}

	public void setStartRow(Integer startRow) {
		this.startRow = startRow;
	}

	public Integer getEndRow() {
		return endRow;
	}

	public void setEndRow(Integer endRow) {
		this.endRow = endRow;
	}

	public Integer getStartColumn() {
		return startColumn;
	}

	public void setStartColumn(Integer startColumn) {
		this.startColumn = startColumn;
	}

	public Integer getEndColumn() {
		return endColumn;
	}

	public void setEndColumn(Integer endColumn) {
		this.endColumn = endColumn;
	}

	/**
	 * 构造方法1
	 */
	public ExcelMergeVo2() {
		super();
		// TODO 自动生成的构造函数存根
	}

	/**
	 * 构造方法2
	 */
	public ExcelMergeVo2(String excelValue, Integer startRow, Integer endRow,
			Integer startColumn, Integer endColumn) {
		super();
		this.excelValue = excelValue;
		this.startRow = startRow;
		this.endRow = endRow;
		this.startColumn = startColumn;
		this.endColumn = endColumn;
	}
	
	

}

测试方法

1. 将aa.xlsx合并中的区域复制的bb.xlsx

package com.ch.poi.test;

import com.ch.poi.utils.ExcelMergeVo2;
import com.ch.poi.utils.UploadUtil;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.util.ArrayList;

/**
 * excel合并单元格
 */
public class ExcelMerger {


    public static void main(String[] args) throws IOException {
        //文件路径
        String filePath = "C:\\Users\\chenhu\\Desktop\\aaa.xlsx";
        String filePath2 = "C:\\Users\\chenhu\\Desktop\\bbb.xlsx";

        //文件输入流
        InputStream in = new FileInputStream(filePath);
        InputStream in2 = new FileInputStream(filePath2);


        //接收合并区域范围
        ArrayList<ExcelMergeVo2> arrColumn4 = new ArrayList<ExcelMergeVo2>();

        XSSFWorkbook wb = new XSSFWorkbook(in);
        XSSFSheet sheet = wb.getSheetAt(0);


        XSSFWorkbook wb2 = new XSSFWorkbook(in2);
        XSSFSheet sheet2 = wb2.getSheetAt(0);

        //获取合并区域
        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();
            //获取合并区域值
            String cellValue = UploadUtil.getCellValue(sheet.getRow(firstRow).getCell(firstColumn));
            //合并区域添加到集合中
            arrColumn4.add(new ExcelMergeVo2(cellValue, firstRow, lastRow, firstColumn, lastColumn));

        }

        int lastRowNum = sheet.getLastRowNum();
        int physicalNumberOfCells = sheet.getRow(0).getPhysicalNumberOfCells();
        //将合并区域写入到另一个excel中
        for (int i = 0; i < lastRowNum; i++) {
            XSSFRow row = sheet.getRow(i);
            XSSFRow row2 = sheet2.createRow(i);
            for (int j = 0; j < physicalNumberOfCells; j++) {
                XSSFCell cell = row.getCell(j);

                String cellValue = UploadUtil.getCellValue(cell);

                if(cellValue == null||"".equals(cellValue)){
                    cellValue = UploadUtil.getExcelValue(arrColumn4, i, j);
                }
                System.out.println(cellValue);
                row2.createCell(j).setCellValue(cellValue);
            }

        }
        //文件输出流
        FileOutputStream out = new FileOutputStream(filePath2);


        wb2.write(out);
        wb.close();
        in.close();
        out.close();

    }


}

需要导入的aaa.xlsx

生成之后bbb.xlsx

总结： 这样就方便存入数据库了