excel导出

maven依赖插件

        <!--工具-->
        <dependency>
            <groupId>commons-lang</groupId>
            <artifactId>commons-lang</artifactId>
            <version>2.6</version>
        </dependency>
        <!--poi-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.14-beta1</version>
        </dependency>
        <!--boot-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
            <version>1.5.10.RELEASE</version>
        </dependency>
        <!--fastjson-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.31</version>
        </dependency>

导出工具类PoiExcel

import com.alibaba.fastjson.JSON;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.http.HttpHeaders;
import org.springframework.http.HttpStatus;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.function.BiConsumer;

/**
 * 开发公司：xx公司
 * 版权：xx公司
 * <p>
 * 类功能描述
 * 生成Excel
 * @author 刘志强
 * @created 2018/5/10.
 */
public class PoiExcel {
    /**
     * 导出
     * @param listHead
     * @param listDataSource
     * @param fileName
     * @return
     */
    public static ResponseEntity<byte[]> exportExcel(List<Map<String,Object>> listHead, List<Objects> listDataSource, String fileName){
        fileName = fileName + ".xls";
        HSSFWorkbook workbook = getHSSFWorkbook(listHead, listDataSource);
        try {
            HttpHeaders headers = new HttpHeaders();
            headers.setContentType(MediaType.valueOf("application/vnd.ms-excel"));
            headers.setContentDispositionFormData("attachment", new String(fileName.getBytes("utf-8"), "ISO8859-1"));
            ByteArrayOutputStream outByteStream = new ByteArrayOutputStream();
            workbook.write(outByteStream);
            return new ResponseEntity(outByteStream.toByteArray(), headers, HttpStatus.OK);
        } catch (IOException e) {
            e.printStackTrace();
        }
        return null;
    }


    public static <E> void forEach(
            Iterable<? extends E> elements, BiConsumer<Integer, ? super E> action) {
        Objects.requireNonNull(elements);
        Objects.requireNonNull(action);

        int index = 0;
        for (E element : elements) {
            action.accept(index++, element);
        }
    }


    public static HSSFWorkbook getHSSFWorkbook(List<Map<String,Object>> listHead, List<Objects> listDataSource) {

        // 创建Excel文档
        HSSFWorkbook workbook = new HSSFWorkbook();

        // 创建一个Excel表单,参数为sheet的名字
        HSSFSheet sheet = workbook.createSheet("sheet1");

        // .创建一行
        HSSFRow headerRow = sheet.createRow(0);

        forEach(listHead, (index, mapHead) -> {
            if (mapHead.get("column") != null) {
                HSSFCell cell = headerRow.createCell(index);
                cell.setCellValue(mapHead.get("title").toString());
            }
        });
        int index = 0;
        for (Object obj: listDataSource){
            index++;
            HSSFRow headerRowL = sheet.createRow(index);
            Map<String, Object> map = (Map) JSON.toJSON(obj);
            int finalIndex1 = index;
            map.forEach((k, v)->{
                int finalIndex = finalIndex1;
                forEach(listHead, (i, mapHead) -> {
                    if (mapHead.get("column") != null) {
                        if (StringUtils.equals(k, mapHead.get("column").toString())) {
                            HSSFCell cell = headerRowL.createCell(i);
                            if(mapHead.get("columnStr") != null) {
                                Map<String,String> columnStrMap = (Map<String, String>) mapHead.get("columnStr");
                                final boolean[] on = {true};
                                columnStrMap.forEach((k1,v1)->{
                                    if (StringUtils.equals(v.toString(),k1)) {
                                        on[0] = false;
                                        cell.setCellValue(v1);
                                    }
                                });
                                if (on[0]) {
                                    cell.setCellValue(v.toString());
                                }
                            } else if (StringUtils.equals(mapHead.get("column").toString(),"id")){
                                cell.setCellValue(finalIndex);
                            } else {
                                cell.setCellValue(v.toString());
                            }
                        }
                    }
                });
            });
        }
        return  workbook;
    }
}

使用

    @GetMapping("/export")
    public ResponseEntity<byte[]> export() {
        String jsonStr = "[" +
                "{title: '注册日期',column: 'createTime'}," +
                "{title: '用户Id',column: 'memberId'}," +
                "{title: '姓名',column: 'userName'}," +
                "{title: '性别',column: 'sex'}," +
                "{title: '手机号',column: 'phone'}," +
                "{title: '分享次数',column: 'num'}" +
                "]";
        List<Map<String,Object>> listHead = JSONArray.parseObject(jsonStr, List.class);
        List listDataSource = new ArrayList();
        Map<String,Object> map = new HashMap<>();
        map.put("createTime","2012-12-12");
        map.put("memberId","ssss");
        map.put("userName","ffff");
        map.put("sex","vvvv");
        map.put("phone","eeee");
        map.put("num","bbbb");
        listDataSource.add(map);
        String fileName = "文件名称";
        return PoiExcel.exportExcel(listHead, listDataSource, fileName);
    }

    @GetMapping("/export2")
    public ResponseEntity<byte[]> export2() {
        String jsonStr = "[" +
                "{title: '注册日期',column: 'createTime'}," +
                "{title: '用户Id',column: 'memberId'}," +
                "{title: '姓名',column: 'userName'}," +
                "{title: '性别',column: 'sex'}," +
                "{title: '手机号',column: 'phone'}," +
                "{title: '状态',column: 'state', columnStr: {0: '正常', '1': '冻结'}}" +
                "]";
        List<Map<String,Object>> listHead = JSONArray.parseObject(jsonStr, List.class);
        List listDataSource = new ArrayList();
        Map<String,Object> map = new HashMap<>();
        map.put("createTime","2012-12-12");
        map.put("memberId","ssss");
        map.put("userName","ffff");
        map.put("sex","vvvv");
        map.put("phone","eeee");
        map.put("state", 1);
        listDataSource.add(map);
        String fileName = "文件名称";
        return PoiExcel.exportExcel(listHead, listDataSource, fileName);
    }