java poi应用，导出数据时合并单元格、居中、单元格背景色

@日常记录java poi

java poi应用，导出数据时合并单元格、居中、单元格背景色

poi版本

poi-3.17

合并单元格

	// 合并日期占两行(4个参数，分别为起始行，结束行，起始列，结束列)；
    // 行和列都是从0开始计数，且起始结束都会合并
     CellRangeAddress region = new CellRangeAddress(0, 0, 0, 2);
     sheet.addMergedRegion(region);
     region = new CellRangeAddress(1, 1, 0, 2);
     sheet.addMergedRegion(region);

内容居中

	HSSFCellStyle style = workbook.createCellStyle();
    style.setAlignment(HorizontalAlignment.CENTER);	//左右居中
    //style.setVerticalAlignment(VerticalAlignment.CENTER);	//上下居中

单元格背景色

	style1.setFillForegroundColor(IndexedColors.GREEN.getIndex()); //背景色
    //旧版 CellStyle.SOLID_FOREGROUND
    style1.setFillPattern(FillPatternType.SOLID_FOREGROUND); //新版

详细代码

	//原始数据
    Map<String, Integer> map = new HashMap<String, Integer>();
    Map<String, Map<String, Integer>> subMap = new HashMap<String, Map<String, Integer>>();
    map.put("奥迪", 15);
    map.put("大众", 35);
    Map<String, Integer> sm = new HashMap<String, Integer>();
    sm.put("奥迪A4", 10);
    sm.put("奥迪A6", 5);
    subMap.put("奥迪", sm);
    sm = new HashMap<String, Integer>();
    sm.put("CC", 10);
    sm.put("大众1", 10);
    sm.put("大众2", 15);
    subMap.put("大众", sm);
	
	//格式及数据填充
	HSSFWorkbook workbook = new HSSFWorkbook();
    HSSFCellStyle style = workbook.createCellStyle();
    style.setAlignment(HorizontalAlignment.CENTER);	//左右居中
    //style.setVerticalAlignment(VerticalAlignment.CENTER);	//上下居中
    style.setFillForegroundColor(IndexedColors.RED.getIndex()); //全局背景色
    //旧版 CellStyle.SOLID_FOREGROUND
    style.setFillPattern(FillPatternType.SOLID_FOREGROUND); //新版
    HSSFSheet sheet = workbook.createSheet("sheet");

    HSSFCellStyle style1 = workbook.createCellStyle();
	style1.setAlignment(HorizontalAlignment.CENTER);	//左右居中
    //style.setVerticalAlignment(VerticalAlignment.CENTER);	//上下居中
    style1.setFillForegroundColor(IndexedColors.GREEN.getIndex()); //全局背景色
    //旧版 CellStyle.SOLID_FOREGROUND
    style1.setFillPattern(FillPatternType.SOLID_FOREGROUND); //新版
    
    HSSFRow row = sheet.createRow(0);
    HSSFRow row1 = sheet.createRow(1);
    HSSFRow row2 = sheet.createRow(2);
    HSSFRow row3 = sheet.createRow(3);
    HSSFCell cell = null;
    HSSFCell cell1 = null;
    HSSFCell cell2 = null;
    HSSFCell cell3 = null;
    int index = -1;
    int beforeIndex = 0;
    int subIndex = -1;
    for(Map.Entry<String, Map<String, Integer>> entry : subMap.entrySet()){
    	Map<String, Integer> value = entry.getValue();
    	if(value != null && !value.isEmpty()){
			cell = row.createCell(index+1);
			cell1 = row1.createCell(index+1);
			beforeIndex = index+1;
			index += value.size();
    		cell.setCellStyle(style1);
            cell.setCellValue(entry.getKey());
            cell1.setCellStyle(style);
            cell1.setCellValue(map.get(entry.getKey()));
            for(Map.Entry<String, Integer> subEntry : value.entrySet()){
            	subIndex++;
            	cell2 = row2.createCell(subIndex);
            	cell2.setCellStyle(style);
                cell2.setCellValue(subEntry.getKey());
                cell3 = row3.createCell(subIndex);
            	cell3.setCellStyle(style);
                cell3.setCellValue(subEntry.getValue());
            }
            // 合并日期占两行(4个参数，分别为起始行，结束行，起始列，结束列)
            // 行和列都是从0开始计数，且起始结束都会合并
            CellRangeAddress region = new CellRangeAddress(0, 0, beforeIndex, index);
            sheet.addMergedRegion(region);
            region = new CellRangeAddress(1, 1, beforeIndex, index);
            sheet.addMergedRegion(region);
    	}else{
    		cell = row.createCell(index+1);
			cell1 = row1.createCell(index+1);
			index += 1;
    		cell.setCellStyle(style);
            cell.setCellValue(entry.getKey());
            cell1.setCellStyle(style);
            cell1.setCellValue(map.get(entry.getKey()));
            subIndex ++;
    	}
    }
    File file = new File("D:\\demo.xls");
    FileOutputStream fout = new FileOutputStream(file);
    workbook.write(fout);
    fout.close();

运行结果