java导出excel并单元格自动合并
最近公司有一个需求,就是按到模版导出数据报表,并内容相同的单元格实现自动合并.具体业务设计图如下所示
需求分析
由于头部是固定不变的,先使用excel创建数据模版单元格合并,前三列需要根据内容相同自动合并单元格根据状态来判断,状态异常的红色字体,并且已红色×标识*情况,都是多选一的,数据库只会记录一种状态,后台需要判断并在指定的单元格添加标识符√
代码开发
创建*情况对应的魔法值对比索引
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);
}
}
复制代码
导出数据实体类
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;
}
}
复制代码
对应的服务类
public interface ExcelExportService {
FileData exportFile(String fileName, String rootPath, List<PatrolTaskExport> list);
List<PatrolTaskExport> getExportData(Map<String,Object> param);
}
复制代码
接口实现类
@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;
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();
}
}
}
复制代码
最终展示结果
