[移动开发]使用EasyExcel导出Excel-字体居中

使用EasyExcel导出Excel-字体居中

引入maven 依赖

<!-- 阿里开源EXCEL-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.2.0-beta2</version>
        </dependency>
 <!-- fastjson -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.58</version>
        </dependency>

实体类

package com.ph.rfwg.entity;

import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.ColumnWidth;
import com.alibaba.excel.annotation.write.style.ContentRowHeight;
import com.alibaba.excel.annotation.write.style.HeadRowHeight;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import io.swagger.annotations.ApiModel;
import lombok.Data;
import java.io.Serializable;

@Data
@TableName("xj_proj") //mapper表名
@ContentRowHeight(15) //内容行高
@HeadRowHeight(23)//表头行高
public class Proj implements Serializable {
    private static final long serialVersionUID = 312449932801745800L;
    @ExcelIgnore
    @TableId(type = IdType.ASSIGN_UUID)
    private String uuid;
    @ExcelProperty(value = {"工程台账","工程名称"}, index = 1)
    @ColumnWidth(35)
    private String gcmc;
    @ExcelProperty(value = {"工程台账","区域"}, index = 2)
    private String district;
    @ExcelIgnore
    private String address;
    @ExcelIgnore
    private String symc;
    @ExcelIgnore
    private String jgsj;
    @ExcelIgnore
    private String jzmj;
    @ExcelIgnore
    private String symj;
    @ExcelIgnore
    private String ypj;
    @ExcelIgnore
    private String qsqk;
    @ExcelIgnore
    private String whfl;
    @ExcelProperty(value = {"工程台账","最近巡查时间"}, index = 5)
    @ColumnWidth(21)
    private String zjxcsj;
    @ExcelProperty(value = {"工程台账","车位应配建"}, index = 3)
    @ColumnWidth(13)
    private Integer cwypj;
    @ExcelProperty(value = {"工程台账","车位其它"}, index = 4)
    @ColumnWidth(13)
    private Integer cwqt;
    @ExcelIgnore
    private String sfsy;
    @ExcelIgnore
    private String syr;
    @ExcelIgnore
    private String ffsyz;
    @ExcelIgnore
    private String syzqx;
    @ExcelIgnore
    private String syfsq;
    @ExcelIgnore
    private String bz;
    @ExcelProperty(value = {"工程台账","序号"}, index = 0)
    @ColumnWidth(8)
    private Integer number;
}



注： 
 @ExcelProperty(value = {“工程台账”,“序号”}, index = 0)
value第一个是标题，第二个是列的字段名称，index是显示的顺序
@ExcelIgnore 不导出该字段
@ColumnWidth(8) 该字段列的宽度

service层

package com.ph.rfwg.service;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
public interface ExcelDownloadService {
    /**
     * 导出项目excel
     */
    void proExcelDown(HttpServletResponse response) throws IOException;
}

-- 注： 要返回void不然会报错

实现层

package com.ph.rfwg.service.impl;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.alibaba.fastjson.JSON;
import com.ph.rfwg.entity.Proj;
import com.ph.rfwg.mapper.CheckMapper;
import com.ph.rfwg.mapper.ProjMapper;
import com.ph.rfwg.service.ExcelDownloadService;
import com.ph.rfwg.vo.CheckExcelVo;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Service
public class ExcelDownloadServiceImpl implements ExcelDownloadService {

    @Autowired
    private CheckMapper checkMapper;

    @Autowired
    private ProjMapper projMapper;


    // 导出项目excel
    @Override
    public void proExcelDown(HttpServletResponse response) throws IOException {
        List<Proj> projs = projMapper.selectList(null);
        for (int i = 0; i < projs.size(); i++) {
            projs.get(i).setNumber(i + 1);
        }
        try {
            WriteCellStyle headWriteCellStyle = new WriteCellStyle();
            //设置背景颜色
            headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
            //设置头字体
            WriteFont headWriteFont = new WriteFont();
            headWriteFont.setFontHeightInPoints((short)13);
            headWriteFont.setBold(true);
            headWriteCellStyle.setWriteFont(headWriteFont);
            //设置头居中
            headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);

            //内容策略
            WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
            //设置 水平居中
            contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);

            HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);

            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
            String fileName = URLEncoder.encode("工程台账", "UTF-8");
            response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
            // 这里需要设置不关闭流
            EasyExcel.write(response.getOutputStream(), Proj.class)
                    .registerWriteHandler(horizontalCellStyleStrategy).sheet("sheet1")
                    .doWrite(projs);

        } catch (Exception e) {
            // 重置response
            response.reset();
            response.setContentType("application/json");
            response.setCharacterEncoding("utf-8");
            Map<String, String> map = new HashMap<String, String>();
            map.put("status", "failure");
            map.put("message", "下载文件失败" + e.getMessage());
            response.getWriter().println(JSON.toJSONString(map));
        }
    }
}

controller层

ackage com.ph.rfwg.cmcontroller;

import com.ph.rfwg.service.ExcelDownloadService;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;

@RestController
@RequestMapping("/excel")
public class ExcelDownloadController {
    @Autowired
    private ExcelDownloadService excel;
   @RequestMapping(value = "/excelDownByProj",method = {RequestMethod.GET,RequestMethod.POST})
    @ApiOperation(value = "项目台账excel导出")
    public void excelDownByProj(HttpServletResponse response) throws IOException {
        excel.proExcelDown(response);
    }
}