@日常记录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();