java导出excel并单元格自动合并
最近公司有一个需求,就是按到模版导出数据报表,并内容相同的单元格实现自动合并.具体业务设计图如下所示
需求分析
- 由于头部是固定不变的,先使用excel创建数据模版
- 单元格合并,前三列需要根据内容相同自动合并单元格
- 根据状态来判断,状态异常的红色字体,并且已红色
×
标识 - *情况,都是多选一的,数据库只会记录一种状态,后台需要判断并在指定的单元格添加标识符
√
代码开发
创建情况对应的魔法值对比索引 ```java /*
- @ClassName PatrolExportEnum
- @Description 方便单元格选择勾选
- @Author xiongchao
- @Date 2020/10/20 10:02 **/ public class PatrolExportEnum {
// 进水情况 private static Map waterInCondition; // 出水情况 private static Map waterOutCondition; // 电力情况 private static Map electryCondition; // 是否与现场一直 private static Map liveCondition; // 管网情况 private static Map pipeCondition; // 配水情况 private static Map waterCondition;
static {
waterInCondition = new HashMap<>(); waterOutCondition = new HashMap<>(); electryCondition = new HashMap<>(); liveCondition = new HashMap<>(); pipeCondition = new HashMap<>(); waterCondition = new HashMap<>(); waterInCondition.put("无",0); waterInCondition.put("少",1); waterInCondition.put("正常",2); waterInCondition.put("有溢流",3); waterOutCondition.put("正常",0); waterOutCondition.put("无水",1); electryCondition.put("正常",0); electryCondition.put("故障",1); liveCondition.put("一致",0); liveCondition.put("不一致",1); pipeCondition.put("管道",0); pipeCondition.put("井盖",1); pipeCondition.put("沉沙井",2); waterCondition.put("均匀",0); waterCondition.put("堵塞",1);
}
public static Integer getWaterInCondition(String waterIn) { return waterInCondition.get(waterIn); }
public static Integer getWaterOutCondition(String waiterOut) { return waterOutCondition.get(waiterOut); }
public static Integer getElectryCondition(String electry) { return electryCondition.get(electry); }
public static Integer getLiveCondition(String live) { return liveCondition.get(live); }
public static Integer getPipeCondition(String pipe) { return pipeCondition.get(pipe); }
public static Integer getWaterCondition(String water) { return waterCondition.get(water); }
}
```
导出数据实体类 ```java public class PatrolTaskExport {
private String street;
private String xVillage;
private String zVlillage;
private String patrolTime;
private String patrolPerson;
private String content;
private String waterInCondition;
private String waterOutCondition;
private String electryCondition;
private List equConditionList;
private String isSame;
private List pipeCondition;
private String waterSupport;
private String afforestCondition;
public String getStreet() { return street; }
public void setStreet(String street) { this.street = street; }
public String getxVillage() { return xVillage; }
public void setxVillage(String xVillage) { this.xVillage = xVillage; }
public String getzVlillage() { return zVlillage; }
public void setzVlillage(String zVlillage) { this.zVlillage = zVlillage; }
public String getPatrolTime() { return patrolTime; }
public void setPatrolTime(String patrolTime) { this.patrolTime = patrolTime; }
public String getPatrolPerson() { return patrolPerson; }
public void setPatrolPerson(String patrolPerson) { this.patrolPerson = patrolPerson; }
public String getContent() { return content; }
public void setContent(String content) { this.content = content; }
public String getWaterInCondition() { return waterInCondition; }
public void setWaterInCondition(String waterInCondition) { this.waterInCondition = waterInCondition; }
public String getWaterOutCondition() { return waterOutCondition; }
public void setWaterOutCondition(String waterOutCondition) { this.waterOutCondition = waterOutCondition; }
public String getElectryCondition() { return electryCondition; }
public void setElectryCondition(String electryCondition) { this.electryCondition = electryCondition; }
public String getIsSame() { return isSame; }
public void setIsSame(String isSame) { this.isSame = isSame; }
public List getPipeCondition() { return pipeCondition; }
public void setPipeCondition(List pipeCondition) { this.pipeCondition = pipeCondition; }
public String getWaterSupport() { return waterSupport; }
public void setWaterSupport(String waterSupport) { this.waterSupport = waterSupport; }
public String getAfforestCondition() { return afforestCondition; }
public void setAfforestCondition(String afforestCondition) { this.afforestCondition = afforestCondition; }
public static class equCondition {
public equCondition (String name,String value){ this.name = name; this.value = value; } private String name; private String value; public String getName() { return name; } public void setName(String name) { this.name = name; } public String getValue() { return value; } public void setValue(String value) { this.value = value; }
}
public List getEquConditionList() { return equConditionList; }
public void setEquConditionList(List equConditionList) { this.equConditionList = equConditionList; } } ```
对应的服务类 ```java
/* * @ClassName ExcelExportService * @Description TODO * @Author xiongchao * @Date 2020/10/20 10:42 */ public interface ExcelExportService {
/**
* 数据导出,格式转化
* @param fileName
* @param rootPath
* @param list
* @return
*/
FileData exportFile(String fileName, String rootPath, List<PatrolTaskExport> list);
/**
* 获取导出数据
* @param param
* @return
*/
List<PatrolTaskExport> getExportData(Map<String,Object> param);
}
```
- 接口实现类 ```java
@Service public class ExcelExportServiceImpl implements ExcelExportService {
private static final String yes ="√";
private static final String no ="×";
private Logger log = LoggerFactory.getLogger(ExcelExportServiceImpl.class);
private static SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
@Override
public FileData exportFile(String fileName, String rootPath, List<PatrolTaskExport> list) {
if (CollectionUtils.isEmpty(list)) {
log.error("导出数据不能为空!");
return null;
}
FileData fileData = null;
Sheet sheet = null;
Workbook workbook = null;
Row row = null;
try {
workbook= ExcelUtil.readExcel(rootPath);
CellStyle cellStyle = workbook.createCellStyle();
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
Font font = workbook.createFont();
font.setColor(HSSFColor.RED.index);
//导出月份时间
String month = getMonth();
if(workbook != null) {
sheet = workbook.getSheetAt(0);
}
if(sheet != null){
//开始日期
row= sheet.getRow(1);
row.getCell(0).setCellValue(month);
// 第四行开始写入数据
int rowNum = 4;
int index = 1;
//明确只有 2,3,4 需要根据重复内容合并
//上一个街镇名称
String perPurpose = "";
String perxVillage = "";
String perzVliiage= "";
//要融合的第一行
int startMergeCol = 4;
int xStartMergeCol = 4;
int zStartMergeCol = 4;
int xendMergeCol = 3;
int zendMergeCol = 3;
//要融合的最后行
int endMergeCol = 3;
//勾选的数字
Integer selectIndex = null;
//用来记录是否是第一次循环
boolean flag = true;
int cellNum = sheet.getRow(3).getLastCellNum();
//先建单元格
for(PatrolTaskExport vo :list){
row = sheet.createRow(rowNum);
for(int i=0;i<cellNum;i++){
row.createCell(i);
}
rowNum++;
}
rowNum =4;
for(PatrolTaskExport vo :list){
row = sheet.getRow(rowNum);
//开始写入数据
createCell(row,cellStyle,0,String.valueOf(index));
createCell(row,cellStyle,1,vo.getStreet());
if(flag){
perPurpose = vo.getStreet();
perzVliiage = vo.getzVlillage();
perxVillage =vo.getxVillage();
flag = false;
}
if(perzVliiage.equalsIgnoreCase(vo.getzVlillage())){
zendMergeCol ++;
} else {
if(zendMergeCol > zStartMergeCol){
sheet.addMergedRegion(new CellRangeAddress(zStartMergeCol,zendMergeCol,3,3));
}
zStartMergeCol = rowNum;
zendMergeCol = zStartMergeCol;
perzVliiage = vo.getzVlillage();
}
if(perxVillage.equalsIgnoreCase(vo.getxVillage())){
xendMergeCol ++;
} else {
if(xendMergeCol > xStartMergeCol){
sheet.addMergedRegion(new CellRangeAddress(xStartMergeCol,xendMergeCol,2,2));
}
xStartMergeCol = rowNum;
xendMergeCol = xStartMergeCol;
perxVillage = vo.getxVillage();
}
if(perPurpose.equals(vo.getStreet())){
endMergeCol ++;
} else {
//并且之前相同的单元格
if(startMergeCol < endMergeCol){
sheet.addMergedRegion(new CellRangeAddress(startMergeCol,endMergeCol,1,1));
}
startMergeCol = rowNum;
endMergeCol = startMergeCol;
perPurpose = vo.getStreet();
}
createCell(row,cellStyle,2,vo.getxVillage());
createCell(row,cellStyle,3,vo.getzVlillage());
createCell(row,cellStyle,4,vo.getPatrolTime());
selectIndex = PatrolExportEnum.getWaterInCondition(vo.getWaterInCondition());
if(selectIndex != null) {
if(selectIndex.equals(3)){
CellStyle style = colorRed(workbook,font);
createCell(row,style,5 + selectIndex,no);
}else{
createCell(row,cellStyle,5 + selectIndex,yes);
}
selectIndex = null;
}
selectIndex = PatrolExportEnum.getWaterOutCondition(vo.getWaterOutCondition());
if(selectIndex != null){
if(selectIndex.equals(1)){
CellStyle style = colorRed(workbook,font);
createCell(row,style,9 + selectIndex,no);
}else {
createCell(row,cellStyle,9 + selectIndex,yes);
}
selectIndex = null;
}
selectIndex = PatrolExportEnum.getElectryCondition(vo.getElectryCondition());
if(selectIndex != null) {
if(selectIndex.equals(1)){
CellStyle style = colorRed(workbook,font);
createCell(row,style,11 + selectIndex,no);
}else{
createCell(row,cellStyle,11 + selectIndex,yes);
}
selectIndex = null;
}
List<PatrolTaskExport.equCondition> conditions = vo.getEquConditionList();
if(!CollectionUtils.isEmpty(conditions)){
for(PatrolTaskExport.equCondition e : conditions){
String value = e.getValue();
String name = e.getName();
if ("格栅".equalsIgnoreCase(name)) {
CellStyle style = setColor(value,workbook,font);
createCell(row,style,13,value);
}
if ("水泵".equalsIgnoreCase(name)) {
CellStyle style = setColor(value,workbook,font);
createCell(row,style,14 ,value);
}
if ("风机".equalsIgnoreCase(name)) {
CellStyle style = setColor(value,workbook,font);
createCell(row,style,15 ,value);
}
if ("液位计".equalsIgnoreCase(name)) {
CellStyle style = setColor(value,workbook,font);
createCell(row,style,16 ,value);
}
}
}
selectIndex = PatrolExportEnum.getLiveCondition(vo.getIsSame());
if(selectIndex != null) {
if (selectIndex.equals(1)) {
CellStyle style = colorRed(workbook,font);
createCell(row,style,17 + selectIndex,no);
}else {
createCell(row,cellStyle,17 + selectIndex,yes);
}
selectIndex = null;
}
List<PatrolTaskExport.equCondition> pipeCodintion = vo.getPipeCondition();
if(!CollectionUtils.isEmpty(pipeCodintion)){
for(PatrolTaskExport.equCondition e : pipeCodintion){
String value = e.getValue();
String name = e.getName();
if ("管道".equalsIgnoreCase(name)) {
CellStyle style = setColor(value,workbook,font);
createCell(row,style,19,value);
}
if ("井盖".equalsIgnoreCase(name)) {
CellStyle style = setColor(value,workbook,font);
createCell(row,style,20 ,value);
}
if ("沉沙井".equalsIgnoreCase(name)) {
CellStyle style = setColor(value,workbook,font);
createCell(row,style,21 ,value);
}
}
}
selectIndex = PatrolExportEnum.getWaterCondition(vo.getWaterSupport());
if(selectIndex != null){
if (selectIndex.equals(1)) {
CellStyle style = colorRed(workbook,font);
createCell(row,style,22 + selectIndex,no);
}else {
createCell(row,cellStyle,22 + selectIndex,yes);
}
selectIndex = null;
}
createCell(row,cellStyle,24 ,vo.getAfforestCondition());
createCell(row,cellStyle,25 ,vo.getPatrolPerson());
createCell(row,cellStyle,26 ,vo.getContent());
rowNum++;
index ++;
}
if(startMergeCol < endMergeCol){
sheet.addMergedRegion(new CellRangeAddress(startMergeCol,endMergeCol,1,1));
}
if(xendMergeCol > xStartMergeCol){
sheet.addMergedRegion(new CellRangeAddress(xStartMergeCol,xendMergeCol,2,2));
}
if(zendMergeCol > zStartMergeCol){
sheet.addMergedRegion(new CellRangeAddress(zStartMergeCol,zendMergeCol,3,3));
}
}
ByteArrayOutputStream os = new ByteArrayOutputStream();
fileData = new FileData();
workbook.write(os);
os.flush();
fileData.setBytes(os.toByteArray());
fileData.setFileName(fileName);
} catch (IOException e) {
log.error("partrol_report_export_error");
e.printStackTrace();
}
return fileData;
}
@Override
public List<PatrolTaskExport> getExportData(Map<String, Object> param) {
List<PatrolTaskExport> list = new ArrayList<>();
for (int i = 0; i < 100 ; i++) {
PatrolTaskExport pa = new PatrolTaskExport();
if(i == 6) {
pa.setStreet("公孙街道");
}else{
pa.setStreet("关山街道");
}
pa.setzVlillage("黄龙山村"+i);
pa.setPatrolTime("2020-12-" + i);
if(i%2 == 0){
pa.setWaterInCondition("正常");
pa.setWaterOutCondition("无水");
pa.setIsSame("一致");
pa.setPatrolPerson("张三" + i);
pa.setxVillage("木槿村委会");
}else{
pa.setWaterInCondition("少");
pa.setWaterOutCondition("正常");
pa.setIsSame("不一致");
pa.setPatrolPerson("张三" + i);
pa.setxVillage("轰隆声村委会");
}
pa.setElectryCondition("故障");
List<PatrolTaskExport.equCondition> conditions = new ArrayList<>();
conditions.add(new PatrolTaskExport.equCondition("格栅","正常"));
conditions.add(new PatrolTaskExport.equCondition("水泵","异常"));
conditions.add(new PatrolTaskExport.equCondition("风机","正常"));
conditions.add(new PatrolTaskExport.equCondition("液位计","异常"));
pa.setEquConditionList(conditions);
List<PatrolTaskExport.equCondition> pipeconditions = new ArrayList<>();
pipeconditions.add(new PatrolTaskExport.equCondition("管道","正常"));
pipeconditions.add(new PatrolTaskExport.equCondition("井盖","异常"));
pipeconditions.add(new PatrolTaskExport.equCondition("沉沙井","正常"));
pa.setPipeCondition(pipeconditions);
pa.setWaterSupport("堵塞");
pa.setAfforestCondition("除杂草");
pa.setContent("这是备注信息");
list.add(pa);
}
return list;
}
private String getMonth(){
Calendar date = Calendar.getInstance();
String year = String.valueOf(date.get(Calendar.YEAR));
String month = String.valueOf(date.get(Calendar.MONTH) + 1);
return year + "年" + month + "月";
}
private void createCell(Row row,CellStyle cellStyle ,int rowNum,String value){
Cell cell = row.getCell(rowNum);
cell.setCellStyle(cellStyle);
cell.setCellValue(value);
}
private CellStyle setColor(String value ,Workbook workbook,Font font){
CellStyle cellStyle = workbook.createCellStyle();
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
if(value.equalsIgnoreCase("异常")){
cellStyle.setFont(font);
return cellStyle;
}
return cellStyle;
}
private CellStyle colorRed(Workbook workbook,Font font){
CellStyle cellStyle = workbook.createCellStyle();
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
cellStyle.setFont(font);
return cellStyle;
}
}
```
暴露接口并测试
controller层
```java @Api(tags = "巡检任务报表导出") @RequestMapping("/api") @RestController public class PatrolTaskResource {@Autowired private ExcelExportService exportService;
@ApiOperation(value = "巡检任务报表导出") @GetMapping("/partorl-task/export") public void patrolReportExport( @ApiParam(value = "查询条件") @RequestParam(value = "param",required = false) String param, HttpServletRequest request, HttpServletResponse response ){ long start =System.currentTimeMillis(); Map map = new HashMap<>(); if(StringUtils.isNotEmpty(param)){ map.put("param",param); } List list = exportService.getExportData(map); String rootPath = ImportTypeEnum.REPORT.getFilePath(); String fileName = ImportTypeEnum.REPORT.getFileName();
FileData file = exportService.exportFile(fileName,rootPath,list); try { ExcelUtil.setResponse(request, response,file); } catch (Exception e) { e.printStackTrace(); }
} } ```