POI导出_Java

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;

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;

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);
}
}
}
}

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;

public class ExcelData {

private List titleData;

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
    
    


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

<#--    -->


<#---->

POI导出

Java相关栏目本月热门文章