java导出excel并单元格自动合并
最近公司有一个需求,就是按到模版导出数据报表,并内容相同的单元格实现自动合并.具体业务设计图如下所示
需求分析
- 由于头部是固定不变的,先使用excel创建数据模版
- 单元格合并,前三列需要根据内容相同自动合并单元格
- 根据状态来判断,状态异常的红色字体,并且已红色
×标识 - *情况,都是多选一的,数据库只会记录一种状态,后台需要判断并在指定的单元格添加标识符
√
代码开发
创建*情况对应的魔法值对比索引
代码语言：javascript复制/**
* @ClassName PatrolExportEnum
* @Description 方便单元格选择勾选
* @Author xiongchao
* @Date 2020/10/20 10:02
**/
public class PatrolExportEnum {
// 进水情况
private static Map<String,Integer> waterInCondition;
// 出水情况
private static Map<String,Integer> waterOutCondition;
// 电力情况
private static Map<String,Integer> electryCondition;
// 是否与现场一直
private static Map<String,Integer> liveCondition;
// 管网情况
private static Map<String,Integer> pipeCondition;
// 配水情况
private static Map<String,Integer> 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);
}
}导出数据实体类
代码语言：javascript复制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<equCondition> equConditionList;
private String isSame;
private List<equCondition> 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<equCondition> getPipeCondition() {
return pipeCondition;
}
public void setPipeCondition(List<equCondition> 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<equCondition> getEquConditionList() {
return equConditionList;
}
public void setEquConditionList(List<equCondition> equConditionList) {
this.equConditionList = equConditionList;
}
}对应的服务类
代码语言：javascript复制
/**
* @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);
}接口实现类
代码语言：javascript复制
@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层
@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<String,Object> map = new HashMap<>();
if(StringUtils.isNotEmpty(param)){
map.put("param",param);
}
List<PatrolTaskExport> 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();
}
}
}最终展示结果
Donate
