Poi导入excel(合并单元格)

1.引入pom文件

<dependency><groupId>org.apache.poi</groupId><artifactId>poi</artifactId><version>4.1.2</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml</artifactId><version>4.1.2</version></dependency><dependency><groupId>org.apache.poi</groupId><artifactId>poi-ooxml-schemas</artifactId><version>4.1.2</version></dependency>

2.创建util：PoiExcelUtils:

package mon.utils;import java.util.ArrayList;import java.util.List;import org.apache.poi.ss.usermodel.CellType;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;/*** 功能: poi导出excel工具类*/public class PoiExcelUtils {/*** 获取单元格的值** @param fCell* @return*/public static String getCellValue(Cell fCell) {if (fCell == null)return "";return fCell.toString();}/*** 把单元格内容全部转为String* @param cell* @return*/public static String getCellString(Cell cell){if (cell == null ) return "";cell.setCellType(CellType.STRING);return cell.getStringCellValue();}/*** 合并单元格处理,获取合并行** @param sheet* @return List<CellRangeAddress>*/public static 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;}public static 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();//cell.getRowIndex()获取单元格所在行号 cell.getColumnIndex()获取列号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 static 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 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 sheet* @param row* 行下标* @param column* 列下标* @return*/public 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;}}

3.代码

public R importTarget(MultipartFile file, Integer taskId) throws IOException {try {//解析Excel,根据Excel文件创建工作簿//传入输入流的对象InputStream,获取Workbook//Workbook工作簿的高级表现形式，是sheet的上级对象。一个excel就是一个工作簿，一个工作簿含有多个工作表(sheet)Workbook wb = WorkbookFactory.create(file.getInputStream());//1.2.获取SheetSheet sheet = wb.getSheetAt(0);//参数：索引//row函数的含义,返回所选择的某一个单元格的行数。Row row;//合并单元格处理,获取合并行List<CellRangeAddress> cras = PoiExcelUtils.getCombineCell(sheet);//getPhysicalNumberOfRows()获得的实际行数，不一定有数据的行数。for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) {//sheet.getRow(i)获取第i行的所有数据Row BigRow = sheet.getRow(i);if (BigRow == null) {break;}row = sheet.getRow(i);//判断指定的单元格是否是合并单元格if (PoiExcelUtils.isMergedRegion(sheet, i, 0)) {//sheet.getRow(i).getCell(0)获取第i行第1个格int lastRow = PoiExcelUtils.getRowNum(cras, sheet.getRow(i).getCell(0), sheet);for (; i <= lastRow; i++) {row = sheet.getRow(i);// 判断该行第1列是合并单元格if (PoiExcelUtils.isMergedRegion(sheet, i, 0)) {int lastRow2 = PoiExcelUtils.getRowNum(cras, sheet.getRow(i).getCell(4), sheet);for (; i <= lastRow2; i++) {Row nextRow = sheet.getRow(i);//实体类ExcelTarget target = new ExcelTarget();buildTarget(row, BigRow, nextRow, target);targetService.save(target);}} else {ExcelTarget target = new ExcelTarget();buildTarget(row, BigRow, row, target);targetService.save(target);}}i--;} else {row = sheet.getRow(i);ExcelTarget target = new ExcelTarget();buildTarget(row, BigRow, row, target);targetService.save(target);}}return R.ok("导入成功");}catch (IOException e){e.printStackTrace();return R.error("导入失败");}}private void buildTarget(Row row, Row BigRow, Row nextRow, ExcelTarget target) {//下标从0开始读取final String name = PoiExcelUtils.getCellString(BigRow.getCell(1)); // 姓名target.setName(name);String sex = PoiExcelUtils.getCellString(BigRow.getCell(2)); // 性别(1:男2:女)if (sex.equals("男")){target.setSex(1);}else if (sex.equals("女")){target.setSex(2);}target.setCreateTime(new Date());}