数据导出简单处理：Java中导出excel数据!!!

达成的功能：查询出数据集合List，把集合内容导出成excel文件数据。

直接上代码：

1、pom.xml

            <!-- 阿里excel-->
            <dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>easyexcel</artifactId>
                <version>3.0.2</version>
            </dependency>

2、代码

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

public class EasyExcelUtils {
    public static HttpServletResponse getExcelResponse(HttpServletResponse response, String fileName) throws IOException {
        //防止乱码  
        fileName = URLEncoder.encode(fileName, "UTF-8");
        // Access-Control-Expose-Headers 暴露响应头在客户端
        response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");
        response.addHeader("Content-disposition", "attachment;filename=" + fileName + ".xls");
        //设置头
        response.setHeader("Pragma", "No-cache");
        //设置头
        response.setHeader("Cache-Control", "no-cache");
        //设置日期头
        response.setDateHeader("Expires", 0);
        response.setContentType("application/msexcel;charset=UTF-8");
        return response;
    }
}

import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.metadata.data.WriteCellData;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.style.column.AbstractColumnWidthStyleStrategy;
import org.apache.poi.ss.usermodel.Cell;
import java.util.List;

public class CommonWidthHandler extends AbstractColumnWidthStyleStrategy {

    @Override
    protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        if (Boolean.TRUE.equals(isHead)) {
            int columnWidth = cell.getStringCellValue().length();
            //默认10  最大255
            columnWidth = Math.max(columnWidth * 3, 10);
            if (columnWidth > 255) {
                columnWidth = 255;
            }
            writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth * 256);
        }
    }
}

import com.alibaba.excel.annotation.ExcelIgnoreUnannotated;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.format.DateTimeFormat;
import com.alibaba.excel.annotation.write.style.ContentStyle;
import com.alibaba.excel.annotation.write.style.HeadFontStyle;
import com.alibaba.excel.annotation.write.style.HeadRowHeight;
import lombok.Data;
import java.util.Date;

@Data
@HeadFontStyle(fontHeightInPoints = 10)
@HeadRowHeight(20)
@ExcelIgnoreUnannotated
public class ExportVo {
    //@ExcelProperty是关键，这个里面的名字就是导出后的excel文件中的列的名字
    @ExcelProperty("列1")
    private String fieldOne;

    @DateTimeFormat("yyyy/MM/d")
    @ContentStyle(dataFormat = 0xe)
    @ExcelProperty(value = "日期XXX")
    private Date fieldTwo;
}

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.xxxxx.cms.interceptor.easyexcel.CommonWidthHandler;
import com.xxxxx.cms.utils.EasyExcelUtils;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiImplicitParam;
import io.swagger.annotations.ApiOperation;
import org.springframework.web.bind.annotation.*;
import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.util.List;

@RestController
@RequestMapping("/xxxxxxxxxxxx")
@Api(tags = "导出excel")
public class ExportExcelController {
    @Resource
    private ExportService exportService;

    @ApiImplicitParam(name = "参数名字", value = "参数说明", paramType = "body", dataType = "array", required = true)
    @ApiOperation("导入excle数据")
    @PostMapping(value = "/exportExcel")
    public void exportExcel(HttpServletResponse response, @RequestBody List<Integer> ids) {
        String tableName = "导出后的excel文件名字";
        ExcelWriter writer = null;
        try {
            //查询数据，ExportVo 类为业务中的实体类
            List<ExportVo> subtaskExportVoList = exportService.queryXxxx(ids);
            response = EasyExcelUtils.getExcelResponse(response, tableName);
            writer = EasyExcel.write(response.getOutputStream()).registerWriteHandler(new CommonWidthHandler()).build();
            WriteSheet projectSheet = EasyExcel.writerSheet(0, "excel表中sheet名字").head(ExportVo.class).build();
            writer.write(subtaskExportVoList, projectSheet);
        } catch (Exception exception) {
            throw new XxxException(exception.getMessage());
        } finally {
            if (writer != null) {
                writer.finish();
            }
        }
    }
}

效果如下：

success!!!