java读取Excel并返回合并单元格所占行数和列数

╰+哭是因爲堅強的太久メ 2022-03-28 11:54 385阅读 0赞

关于java生成excel和读取有规律的excel返回list在这篇文章[https://blog.csdn.net/lianzhang861/article/details/83092835][https_blog.csdn.net_lianzhang861_article_details_83092835]

### 1.需求 ###

这次需求是将有合并单元格的Excel数据读取出来并保存，难点是要将合并的单元格所占的行列数都保存下来，以便到时候可以完成还原Excel的表现格式。

excel模板：

![20190110152153942.png][]

要求进入数据库的格式：

![20190110152252859.png][]

每张表示一条记录，每一行的数据变成一个字段，有合并单元格的用数字记录占行列数，不是合并的用 “数据，1,1；” 表示

### 2.思路 ###

用poi组件读取excel，其关键思想就是先循环sheet数量，再循环每个sheet的每一行，在循环每行row的每一列cell，循环的时候判断此cell是否属于合并单元格区域，（关于一大片的合并区域，poi只会返回左上角第一个cell的数据，其余的全都返回空），如果属于合并区域，则判断是否返回了值，如果返回了，则说明是左上角第一个cell，记录此cell的值和占据行列数，如果不属于合并区域，直接返回值即可，最后再将返回值处理一下字符串

### 3.实现 ###

先写个工具类 ExcelUtil.java(关于引包看最上面的上篇文章)

package com.mobileBass.utils.util;
    
    import com.alibaba.fastjson.JSON;
    import org.apache.poi.hssf.usermodel.*;
    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 org.springframework.orm.jpa.vendor.OpenJpaDialect;
    
    import java.io.File;
    import java.io.FileInputStream;
    import java.io.InputStream;
    import java.text.DecimalFormat;
    import java.text.SimpleDateFormat;
    import java.util.ArrayList;
    import java.util.HashMap;
    import java.util.List;
    import java.util.Map;
    
    public class ExcelUtil {
    
    	private final static String excel2003L = ".xls"; // 2003- 版本的excel
    	private final static String excel2007U = ".xlsx"; // 2007+ 版本的excel
    
    	/**
    	 * 导出Excel
    	 * @param sheetName sheet名称
    	 * @param title 标题
    	 * @param values 内容
    	 * @param wb HSSFWorkbook对象
    	 * @return
    	 */
    	public static HSSFWorkbook getHSSFWorkbook(String sheetName,String []title,String [][]values, HSSFWorkbook wb){
    
    		// 第一步，创建一个HSSFWorkbook，对应一个Excel文件
    		if(wb == null){
    			wb = new HSSFWorkbook();
    		}
    
    		// 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
    		HSSFSheet sheet = wb.createSheet(sheetName);
    
    		// 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
    		HSSFRow row = sheet.createRow(0);
    
    		// 第四步，创建单元格，并设置值表头 设置表头居中
    		HSSFCellStyle style = wb.createCellStyle();
    		style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
    
    		//声明列对象
    		HSSFCell cell = null;
    
    		//创建标题
    		for(int i=0;i<title.length;i++){
    			cell = row.createCell(i);
    			cell.setCellValue(title[i]);
    			cell.setCellStyle(style);
    		}
    
    		//创建内容
    		for(int i=0;i<values.length;i++){
    			row = sheet.createRow(i + 1);
    			if(values[i]!=null){
    				for(int j=0;j<values[i].length;j++){
    					//将内容按顺序赋给对应的列对象
    					row.createCell(j).setCellValue(values[i][j]);
    				}
    			}else{
    				continue;
    			}
    
    		}
    		return wb;
    	}
    
    
    	/**
    	 * 将流中的Excel数据转成List<Map>(读取Excel)
    	 *
    	 * @param in
    	 *            输入流
    	 * @param fileName
    	 *            文件名（判断Excel版本）
    	 * @return
    	 * @throws Exception
    	 */
    	public static List<Map<String,Object>> readExcel(InputStream in, String fileName) throws Exception {
    		// 根据文件名来创建Excel工作薄
    		Workbook work = getWorkbook(in, fileName);
    		if (null == work) {
    			throw new Exception("创建Excel工作薄为空！");
    		}
    		Sheet sheet = null;
    		Row row = null;
    		Cell cell = null;
    		// 返回数据
    		List<Map<String,Object>> resultList=new ArrayList<>();
    		//循环多个工作表
    		for (int i = 0; i < work.getNumberOfSheets(); i++) {
    			Map<String,Object> result=new HashMap<>();
    			sheet = work.getSheetAt(i);
    			if (sheet == null)
    				continue;
    			//获取有合并单元格的区域
    			List<CellRangeAddress> combineCellList=getCombineCellList(sheet);
    			// 测试有几行数据是有表头数据的
    			Boolean flag=true;
    			int h=0;
    			for (int j = sheet.getFirstRowNum(); j < sheet.getLastRowNum() + 1; j++) {
    				row = sheet.getRow(j);
    				// 遍历所有的列
    				if(flag) {
    					for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
    						cell = row.getCell(y);
    						Object v = getCellValue(cell);
    						flag = false;
    						if (v != null && !v.toString().equals("")) {
    							flag = true;
    							break;
    						}
    					}
    					h++;
    				}
    			}
    			//列数
    			int colNum=0;
    			//表头行数
    			int h1=h-1;
    			//表头数据
    			List<List> list1=new ArrayList<>();
    			//循环行，不要从0开始，防止前几行为空的情况
    			for (int k = sheet.getFirstRowNum(); k < sheet.getFirstRowNum()+h1; k++) {
    				List<Map<String,Object>> list=new ArrayList<>();
    				row = sheet.getRow(k);
    				//获取列数
    				colNum=row.getLastCellNum()-row.getFirstCellNum();
    				// 遍历所有的列
    				for (int x = row.getFirstCellNum(); x < row.getLastCellNum(); x++) {
    					Map<String,Object> map=new HashMap<>();
    					cell = row.getCell(x);
    					//表格cell的数据，合并的只有左上角一个有数据，其余全为空
    					String v = getCellValue(cell).toString();
    					//判断该cell是否为合同单元格中的一个
    					Map<String,Object> isCombined=isCombineCell(combineCellList,cell,sheet);
    					//如果是，则判断是否有值，有值的才添加到list中
    					if((Boolean) isCombined.get("flag")){
    						if(v!=null&&!v.equals("")){
    							map.put("name",v);
    							map.put("x",isCombined.get("mergedCol"));
    							map.put("y",isCombined.get("mergedRow"));
    							list.add(map);
    						}
    					//如果不是，则直接插入
    					}else{
    						map.put("name",v);
    						map.put("x",1);
    						map.put("y",1);
    						list.add(map);
    					}
    				}
    				list1.add(list);
    			}
    
    			int rowIndex=1;
    			//处理数据拼接字符串
    			for(int c=0;c<list1.size();c++){
    				String s="";
    				for(int d=0;d<list1.get(c).size();d++){
    					Map<String,Object> map2= (Map<String, Object>) list1.get(c).get(d);
    					s+=map2.get("name")+","+map2.get("x")+","+map2.get("y")+";";
    				}
    				result.put("row"+rowIndex++,s);
    			}
    			
    			System.out.print(result);
    			resultList.add(result);
    		}
    
    		//work.close();
    		return resultList;
    	}
    
    	//获取合并单元格集合
    	public static List<CellRangeAddress> getCombineCellList(Sheet sheet)
    	{
    		List<CellRangeAddress> list = new ArrayList<>();
    		//获得一个 sheet 中合并单元格的数量
    		int sheetmergerCount = sheet.getNumMergedRegions();
    		//遍历所有的合并单元格
    		for(int i = 0; i<sheetmergerCount;i++)
    		{
    			//获得合并单元格保存进list中
    			CellRangeAddress ca = sheet.getMergedRegion(i);
    			list.add(ca);
    		}
    		return list;
    	}
    
    	/**
    	 * 判断cell是否为合并单元格，是的话返回合并行数和列数（只要在合并区域中的cell就会返回合同行列数，但只有左上角第一个有数据）
    	 * @param listCombineCell  上面获取的合并区域列表
    	 * @param cell
    	 * @param sheet
    	 * @return
         * @throws Exception
         */
    	public  static Map<String,Object> 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;
    		Boolean flag=false;
    		int mergedRow=0;
    		int mergedCol=0;
    		Map<String,Object> result=new HashMap<>();
    		result.put("flag",flag);
    		for(CellRangeAddress ca:listCombineCell)
    		{
    			//获得合并单元格的起始行, 结束行, 起始列, 结束列
    			firstC = ca.getFirstColumn();
    			lastC = ca.getLastColumn();
    			firstR = ca.getFirstRow();
    			lastR = ca.getLastRow();
    			//判断cell是否在合并区域之内，在的话返回true和合并行列数
    			if(cell.getRowIndex() >= firstR && cell.getRowIndex() <= lastR)
    			{
    				if(cell.getColumnIndex() >= firstC && cell.getColumnIndex() <= lastC)
    				{
    					flag=true;
    					mergedRow=lastR-firstR+1;
    					mergedCol=lastC-firstC+1;
    					result.put("flag",true);
    					result.put("mergedRow",mergedRow);
    					result.put("mergedCol",mergedCol);
    					break;
    				}
    			}
    		}
    		return result;
    	}
    
    	/**
    	 * 描述：根据文件后缀，自适应上传文件的版本
    	 *
    	 * @param inStr
    	 *            ,fileName
    	 * @return
    	 * @throws Exception
    	 */
    	public static Workbook getWorkbook(InputStream inStr, String fileName) throws Exception {
    		Workbook wb = null;
    		String fileType = fileName.substring(fileName.lastIndexOf("."));
    		if (excel2003L.equals(fileType)) {
    			wb = new HSSFWorkbook(inStr); // 2003-
    		} else if (excel2007U.equals(fileType)) {
    			wb = new XSSFWorkbook(inStr); // 2007+
    		} else {
    			throw new Exception("解析的文件格式有误！");
    		}
    		return wb;
    	}
    
    	/**
    	 * 描述：对表格中数值进行格式化
    	 *
    	 * @param cell
    	 * @return
    	 */
    	public static Object getCellValue(Cell cell) {
    		Object value = null;
    		DecimalFormat df = new DecimalFormat("0"); // 格式化number String字符
    		SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd"); // 日期格式化
    		DecimalFormat df2 = new DecimalFormat("0"); // 格式化数字
    		if(cell!=null){
    			switch (cell.getCellType()) {
    				case Cell.CELL_TYPE_STRING:
    					value = cell.getRichStringCellValue().getString();
    					break;
    				case Cell.CELL_TYPE_NUMERIC:
    					if ("General".equals(cell.getCellStyle().getDataFormatString())) {
    						value = df.format(cell.getNumericCellValue());
    					} else if ("m/d/yy".equals(cell.getCellStyle().getDataFormatString())) {
    						value = sdf.format(cell.getDateCellValue());
    					} else {
    						value = df2.format(cell.getNumericCellValue());
    					}
    					break;
    				case Cell.CELL_TYPE_BOOLEAN:
    					value = cell.getBooleanCellValue();
    					break;
    				case Cell.CELL_TYPE_BLANK:
    					value = "";
    					break;
    				case Cell.CELL_TYPE_ERROR:
    					value="";
    					break;
    				default:
    					break;
    			}
    		}else{
    			value="";
    		}
    		return value;
    	}
    
    	public static void main(String[] args) throws Exception {
    		File file = new File("D:\\通报模板(增加两列黄色内容)new.xlsx");
    		FileInputStream fis = new FileInputStream(file);
    		List<Map<String, Object>> ls = readExcel(fis, file.getName());
    		//System.out.println(JSON.toJSONString(ls));
    	}
    
    
    
    
    
    }

controller中调用

@RequestMapping(value = "/insertTable", produces = "text/html;charset=utf-8")
    @ResponseBody
    public String insertTable(@RequestParam(value = "file") MultipartFile file) {
        Map<String,Object> result=new HashMap<>();
        List<Map<String,Object>> list=new ArrayList<>();
        try {
            String fileName = file.getOriginalFilename();
            //将multipartFile转为inputstream
            CommonsMultipartFile cFile = (CommonsMultipartFile) file;
            DiskFileItem fileItem = (DiskFileItem) cFile.getFileItem();
            InputStream inputStream = fileItem.getInputStream();
            Map<String,Object> params=new HashMap<>();
            list= ExcelUtil.readExcel(inputStream,fileName);
    
                for(int i=0;i<list.size();i++){
                    commonService.insertTable(list.get(i));
                }
    
            result.put("msg", "成功！");
            result.put("success", true);
        } catch (Exception e) {
            e.printStackTrace();
            result.put("msg", "失败！");
            result.put("success", false);
        }
        return JSON.toJSONString(result);
    }

### 4.总结 ###

1.要判断是否属于合并单元格要先获取合并单元格集合，这个poi可以返回，方法是我的工具类的getCombineCellList方法

2.循环每个cell时都要判断一下是否在合并单元格集合内，如果是需要判断其值是否为空，空的话不要保存，就保存左上角那一个唯一有值的

3.如果不需要行列信息，在处理字符串时不拼行列数即可

[https_blog.csdn.net_lianzhang861_article_details_83092835]: https://blog.csdn.net/lianzhang861/article/details/83092835
[20190110152153942.png]: /images/20220328/b0fb9560737f4f55aa7d4af73fdc816a.png
[20190110152252859.png]: /images/20220328/7e1b5a1f882d4e3089e1bc2fbd944118.png