Java读取excel文件的内容包含合并单元格

一.需要读取的excel格式如下图

 二需要读取的excel格式如下图：

Java实现方式：

1.在pom中添加依赖

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

2.使用Excel工具类

import org.apache.poi.ss.usermodel.*; import org.apache.poi.ss.util.CellRangeAddress; import java.util.ArrayList; import java.util.List; public class ReadExcelUtil { /** * 获取单元格的值 * @param cell * @return */ public static 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 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(); 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 */ 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.编写实现一中读取excel的类

import com.apply.ism.entity.TSystem; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.*; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import java.io.File; import java.io.FileInputStream; import java.io.InputStream; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; public class TestReadExcel { public static void main(String[] args) throws Exception { File file=new File("C:\\Users\\Administrator\\Desktop\\测试.xlsx"); InputStream is = new FileInputStream(file); addReportByExcel(is,"测试.xlsx"); } public static String addReportByExcel(InputStream inputStream,String fileName) throws Exception{ String message = "Import success"; boolean isE2007 = false; //判断是否是excel2007格式 if(fileName.endsWith("xlsx")){ isE2007 = true; } int rowIndex = 0; int columnIndex = 0; try { InputStream input = inputStream; //建立输入流 Workbook wb = null; //根据文件格式(2003或者2007)来初始化 if(isE2007){ wb = new XSSFWorkbook(input); }else{ wb = new HSSFWorkbook(input); } Sheet sheet = wb.getSheetAt(0); //获得第一个表单 List<CellRangeAddress> cras = ReadExcelUtil.getCombineCell(sheet); int count = sheet.getLastRowNum()+1;//总行数 List<TSystem> irs = new ArrayList<>(); Map<String,List<TSystem>> datas = new HashMap<>(); String sysName = ReadExcelUtil.getCellValue(sheet.getRow(0).getCell(0)); String category = ReadExcelUtil.getCellValue(sheet.getRow(1).getCell(0)); List<TSystem> items = new ArrayList<>(); List<TSystem> objs = new ArrayList<>(); TSystem obj = new TSystem(); obj.setSysName(sysName); obj.setCategory(category); objs.add(obj); for(int i = 3; i < count;i++){ rowIndex = i; Row row = sheet.getRow(i); TSystem ir = new TSystem(); String projectName = ReadExcelUtil.getCellValue(row.getCell(0)).replaceAll("\n",""); ir.setProjectName(projectName); ir.setSysName(sysName); ir.setCategory(category); if(ReadExcelUtil.isMergedRegion(sheet,i,0)){ int lastRow = ReadExcelUtil.getRowNum(cras,sheet.getRow(i).getCell(0),sheet); for(;i<=lastRow;i++){ row = sheet.getRow(i); TSystem item = new TSystem(); item.setProjectName(projectName); item.setSysName(sysName); item.setCategory(category); item.setStandard(ReadExcelUtil.getCellValue(row.getCell(1)).replaceAll("\n","")); item.setContents(ReadExcelUtil.getCellValue(row.getCell(2)).replaceAll("\n","")); item.setScoreStandard(ReadExcelUtil.getCellValue(row.getCell(3)).replaceAll("\n","")); item.setFraction(ReadExcelUtil.getCellValue(row.getCell(4))); items.add(item); } i--; }else{ row = sheet.getRow(i); TSystem item = new TSystem(); item.setProjectName(projectName); item.setSysName(sysName); item.setCategory(category); item.setStandard(ReadExcelUtil.getCellValue(row.getCell(1))); item.setContents(ReadExcelUtil.getCellValue(row.getCell(2))); item.setScoreStandard(ReadExcelUtil.getCellValue(row.getCell(3))); item.setFraction(ReadExcelUtil.getCellValue(row.getCell(4))); items.add(item); } irs.add(ir); } datas.put("project",irs); datas.put("item",items); datas.put("objs",objs); System.out.println(datas.toString()); /*JSONArray js= new JSONArray(); js.addAll(irs); System.out.println(js.toJSONString());*/ } catch (Exception ex) { //xr.setMessage("Import failed, please check the data in "+rowIndex+" rows "+columnIndex+" columns "); message = "Import failed, please check the data in "+rowIndex+" rows "; } return message; } }

4.编写实现二中读取excel的类

 

public static Map<String, List<TQuestion>> addReportByExcel(InputStream inputStream, String fileName){ boolean isE2007 = false; //判断是否是excel2007格式 if(fileName.endsWith("xlsx")){ isE2007 = true; } try { InputStream input = inputStream; //建立输入流 Workbook wb = null; //根据文件格式(2003或者2007)来初始化 if(isE2007){ wb = new XSSFWorkbook(input); }else{ wb = new HSSFWorkbook(input); } Sheet sheet = wb.getSheetAt(0); //获得第一个表单 int count = sheet.getLastRowNum()+1;//总行数 Map<String,List<TQuestion>> datas = new HashMap<>(); List<TQuestion> data = new ArrayList<>(); for(int i = 1; i < count;i++){ Row row = sheet.getRow(i); TQuestion question = new TQuestion(); String score = null; String title = ReadExcelUtil.getCellValue(row.getCell(0)); String optionA = ReadExcelUtil.getCellValue(row.getCell(1)); String optionB = ReadExcelUtil.getCellValue(row.getCell(2)); String optionC = ReadExcelUtil.getCellValue(row.getCell(3)); String optionD = ReadExcelUtil.getCellValue(row.getCell(4)); String rightAnswer = ReadExcelUtil.getCellValue(row.getCell(5)); if(StringUtils.isNumeric(ReadExcelUtil.getCellValue(row.getCell(6)))){ score = ReadExcelUtil.getCellValue(row.getCell(6)); }else { score = "0"; } String type = ReadExcelUtil.getCellValue(row.getCell(7)); String nd = ReadExcelUtil.getCellValue(row.getCell(8)); question.setTitle(title); question.setOptiona(optionA); question.setOptionb(optionB); question.setOptionc(optionC); question.setOptiond(optionD); question.setRightAnswer(rightAnswer); question.setScore(Double.valueOf(score)); question.setType(type); question.setNd(nd); data.add(question); } datas.put("objs",data); return datas; } catch (Exception ex) { return null; } public static void main(String[] args) throws FileNotFoundException { String filename = "123.pdf"; String filePath = "E:/ideaWorkSpace"; File dest = new File(filePath+"/"+filename); InputStream is = new FileInputStream(dest); addReportByExcel(is,filename); } }