投稿
  1. 首页
  2. java

POI导出

code • 2022-5-5 • java • 阅读 27

POI导出,第1张

package com.aistarfish.damo.web.lion.rest.utils;

import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import javax.servlet.http.HttpServletResponse;

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.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.CollectionUtils;


/**
 * Excel导出
 * @author 17040365
 */
public class ExcelExporter1 {

    private static final Logger LOGGER = LoggerFactory.getLogger(ExcelExporter1.class);

    public static final int PAGE_SIZE = 1000;

    public static final int EXPORT_TOTAL_SIZE = 20000;

    /**
     * 导出excel
     *
     * @param excelData
     * @return
    */
    public static void writeDetailExcel(XSSFSheet sheet,XSSFWorkbook workbook, List excelData) {
        //开始遍历源数据进行表格数据组装
        for (int m = 0; m < excelData.size(); m++) {
//            String sheetName = excelData.get(m).getSheetName()
            List titleData = excelData.get(m).getTitleData();
            //2)在workbook中获取第一个Sheet
//            XSSFSheet sheet = workbook.createSheet()


            //3)创建标题,在sheet中添加表头第0行
            XSSFRow row = sheet.createRow(0);

            //首行冻结
            sheet.createFreezePane(0,1);

            CellStyle cellStyle = workbook.createCellStyle();
            //边框样式
            cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
            cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
            cellStyle.setBorderRight(CellStyle.BORDER_THIN);
            cellStyle.setBorderTop(CellStyle.BORDER_THIN);
            //水平居中
            cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
            //垂直居中
            cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
            //设置单元格颜色
            cellStyle.setFillForegroundColor(IndexedColors.BLUE_GREY.getIndex());
            //设置填充样式（实心填充），不设置填充样式不会有颜色
            cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
            //设置字体
            Font font = workbook.createFont();
            font.setFontName("宋体");
            // 设置字体大小
            font.setFontHeightInPoints((short) 12);
            //字体颜色
            font.setColor(IndexedColors.WHITE.getIndex());
            // 加粗
            font.setBoldweight(Font.BOLDWEIGHT_BOLD);
            cellStyle.setFont(font);
            //行高
            short height = 500;
            row.setHeight(height);


            for (int i = 0; i < titleData.size(); i++) {
                XSSFCell cell = row.createCell(i);
                cell.setCellStyle(cellStyle);
                cell.setCellValue(titleData.get(i));
                //设置自动列宽
//                sheet.autoSizeColumn(i);
                //设置固定列宽
                sheet.setColumnWidth(i, 5000);
            }
            CellStyle detailCellStyle = workbook.createCellStyle();
            detailCellStyle.setBorderBottom(CellStyle.BORDER_THIN);
            detailCellStyle.setBorderLeft(CellStyle.BORDER_THIN);
            detailCellStyle.setBorderRight(CellStyle.BORDER_THIN);
            detailCellStyle.setBorderTop(CellStyle.BORDER_THIN);
            //创建内容
            List>> cellValue = excelData.get(m).getCellValue();
            for (List> rowList : cellValue) {
                //默认第一行为标题
                XSSFRow dataRow = sheet.createRow(sheet.getLastRowNum() + 1);
                for (int i = 0; i < rowList.size(); i++) {
                    //将内容按顺序赋给对应的列对象
                    Map rowMap = rowList.get(i);
                    Entry rowEntry = rowMap.entrySet().iterator().next();
                    XSSFCell cell = dataRow.createCell(i);
                    cell.setCellStyle(detailCellStyle);
                    cell.setCellValue(rowEntry.getValue());
                }
            }
        }
    }

    public static void writeExcel(Workbook workbook,String fileName,HttpServletResponse response) {
        OutputStream outputStream = null;
        try {
            // 设置response头信息
            response.reset();
            // 改成输出excel文件
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("UTF-8");
            response.setHeader("Content-Disposition", "attachment;filename=" +
                    URLEncoder.encode(fileName, "utf-8")+".xlsx");
            outputStream = response.getOutputStream();
            workbook.write(outputStream);
        } catch (IOException e) {
            LOGGER.error("导出excel失败：", e);
        } finally {
            if (null != outputStream) {
                try {
                    outputStream.flush();
                    outputStream.close();
                } catch (IOException e) {
                    LOGGER.error("导出excel失败：", e);
                }
            }
        }
    }

    /**
     * 订单导出
     * @param exportFields 导出标题和对象字段
     * @param mapList  导出数据
     * @param
     */
    public static void export(XSSFSheet sheet,XSSFWorkbook workbook,List> exportFields,List> mapList){
        if(CollectionUtils.isEmpty(exportFields)){
           return;
        }
        List titleData = new ArrayList<>();
        List titleEnData = new ArrayList<>();
        for (Map exportField : exportFields) {
            for (Entry entry : exportField.entrySet()) {
                titleEnData.add(entry.getKey());
                titleData.add(entry.getValue());
            }
        }

        List excelData = new ArrayList<>();
        ExcelData data = new ExcelData();
        data.setSheetName("支付订单");
        data.setTitleData(titleData);
        List>> cellValue = new ArrayList<>();
        for (Map map : mapList) {
            List> maps = new ArrayList<>();
            for (String key : titleEnData) {
                for (String s : map.keySet()) {
                    if(s.equals(key)){
                        Map map1 = new HashMap<>();
                        map1.put(key,map.get(key));
                        maps.add(map1);
                        break;
                    }
                }
            }
            cellValue.add(maps);
        }
        data.setCellValue(cellValue);
        excelData.add(data);
        writeDetailExcel(sheet,workbook,excelData);
    }

}
 

=======================================================================

package com.aistarfish.damo.web.lion.rest.utils;

import java.util.List;
import java.util.Map;

/**
 * excel实体
 */
public class ExcelData {
    /**
     * 表头
     */
    private List titleData;
    /**
     * sheet名称
     */
    private String sheetName;
    /**
     * 行
     */
    private List>> cellValue;

    public List getTitleData() {
        return titleData;
    }

    public void setTitleData(List titleData) {
        this.titleData = titleData;
    }

    public String getSheetName() {
        return sheetName;
    }

    public void setSheetName(String sheetName) {
        this.sheetName = sheetName;
    }

    public List>> getCellValue() {
        return cellValue;
    }

    public void setCellValue(List>> cellValue) {
        this.cellValue = cellValue;
    }
}
======================================================================

@PostMapping("/export")
public void export(@RequestBody UserPageInfoV2 userPageInfo, HttpServletResponse response) {
    AssertUtils.notNull(userPageInfo);
    try {
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet();
        userPageInfo.setCurrent(1);
        userPageInfo.setSize(ExcelExporter.PAGE_SIZE);
        UserPageInfoV2 pageInfoV2 = patientPayService.exportPayOrder(userPageInfo);
        int total = pageInfoV2.getTotal();
        if(total > ExcelExporter.EXPORT_TOTAL_SIZE){
            throw new DamoException(JsonCodeEnum.EXPORT_EXCEL_FAIL, "最多只可导出20000条数据，请筛选后再试。");
        }
        ExcelExporter.export(sheet,workbook,userPageInfo.getExportFields(),pageInfoV2.getRecords());
        if(total > ExcelExporter.PAGE_SIZE){
            int y = total % ExcelExporter.PAGE_SIZE > 0 ? 1 : 0;
            int t = total / ExcelExporter.PAGE_SIZE + y;
            for(int i = 1; i < t ;i++){
                userPageInfo.setCurrent(i+1);
                UserPageInfoV2 pageInfo = patientPayService.exportPayOrder(userPageInfo);
                if(null != pageInfo){
                    ExcelExporter.export(sheet,workbook,userPageInfo.getExportFields(),pageInfo.getRecords());
                }
            }
        }

        String exportFileName = "exportOrder"+ DateUtils.getCurrentTime();
        ExcelExporter.writeExcel(workbook,exportFileName,response);
    } catch (Throwable e) {
        throw new DamoException(JsonCodeEnum.EXPORT_EXCEL_FAIL, e);
    }
}

======================================================================

public class UserPageInfoV2 extends Paginate {
    private String productId;
    private String payNo;
    private String orderStatus;
    private String startTime;
    private String endTime;
    private List userIds;
    private String userKeyWord;
    List> exportFields;

。。。

}

======================================================================

//
// Source code recreated from a .class file by IntelliJ IDEA
// (powered by FernFlower decompiler)
//

package com.aistarfish.zeus.common.facade.model;

import java.util.List;

public class Paginate {
    public static final int DEFAULT_PAGE_SIZE = 10;
    private int current = 1;
    private int pages;
    private int size = 10;
    private int total;
    private List records;

    public Paginate() {
    }

    public Paginate(Paginate paginate) {
        this.current = paginate.getCurrent();
        this.size = paginate.getSize();
        this.total = paginate.getTotal();
    }

    public Paginate(int current, int size) {
        this.size = size;
        this.current = current;
    }

    public int getCurrent() {
        return this.current;
    }

    public void setCurrent(int current) {
        if (current <= 0) {
            this.current = 1;
        } else {
            this.current = current;
        }

    }

    public int getPages() {
        if (this.size == 0) {
            this.pages = 0;
        } else {
            this.pages = this.total / this.size;
            if (this.total % this.size != 0) {
                ++this.pages;
            }
        }

        return this.pages;
    }

    public int getSize() {
        return this.size;
    }

    public void setSize(int size) {
        if (size <= 0) {
            this.size = 10;
        } else {
            this.size = size;
        }

    }

    public int getTotal() {
        return this.total;
    }

    public void setTotal(int total) {
        if (total < 0) {
            this.total = 0;
        } else {
            this.total = total;
        }

    }

    public List getRecords() {
        return this.records;
    }

    public void setRecords(List records) {
        this.records = records;
    }
}

====================================================================




    
    test
    
    


<#--
-->
<#--    
-->
<#--    
-->
<#--    
-->
<#--    
-->
<#--    
-->
<#--    
-->
<#--    
-->
<#--    
-->
<#--    
-->
<#--    
-->
<#--    
-->
<#--    
-->
<#--    
-->
<#--    
-->
<#--    
-->
<#--    
-->
<#--    
-->

<#--    -->


<#--
-->

欢迎分享，转载请注明来源：内存溢出

原文地址: http://outofmemory.cn/langs/791383.html

赞 (0)
打赏 微信扫一扫 微信扫一扫 支付宝扫一扫 支付宝扫一扫
code code 管理员组
0 0
上一篇 2022-05-05
下一篇 2022-05-05

发表评论

登录后才能评论

评论列表（0条）

保存