apache poi 导出

apache poi  单sheet导出

 

注意 poi单sheet 行数限制6W+  具体不清楚了 

数据较多建议多sheet  大量数据建议改成cvs文件

再多的话可以考虑easycode

 

 

话不多说 直接上代码

首先pom文件 引入jar包

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.9</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.9</version>
</dependency>

//spring 注入
@Resource
private ExcelExportUtil excelExportUtil;

@RequestMapping(value = "/administrativePenaltyExport")
public void  administrativePenaltyExport(String json,HttpServletRequest request, HttpServletResponse response) throws Exception {
    JSONObject jsonObject = JSONObject.parseObject(json);
    try {
        //处理数据 
        List<List<String>> rowList=getAdministrativePenaltyData(jsonObject);
        if(!CollectionUtils.isEmpty(rowList)){
            Map map=new HashMap();
            map.getOrDefault("fileName","数据报表");
            String fileName= Optional.ofNullable(jsonObject.getString("fileName")).orElse("数据报表");
            String headTitle= Optional.ofNullable(jsonObject.getString("headTitle")).orElse("数据报表");
            List<String> titles= (List<String>) jsonObject.get("titles");
            if（titles == null || titles .size() == 0）{
               titles =Arrays.asList("检查文书号","单位名称","地址","检查人","检查开始时间","检查结束时间")
            }
            String sheetName= Optional.ofNullable(jsonObject.getString("sheetName")).orElse("sheet");
            excelExportUtil.responseExcel(request,response,fileName,sheetName,headTitle,titles,rowList);
        }
    } catch (Exception e) {
        e.printStackTrace();
    }
}

 

package com.example.util.common;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.stereotype.Component;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.List;
import java.util.concurrent.atomic.AtomicInteger;

/**
 * @author LiYuhang
 * @version 0.1
 * @application poi 单sheet导出
 * @Date 2020/6/4 8:42
 */

@Component
public class ExcelExportUtil {


    /**
     * 常规导出 封装数据
     * @param request
     * @param response
     * @param fileName 文件名称
     * @param sheetName 工作簿名称
     * @param headTitle 大标题
     * @param titles 列头标题
     * @param data 数据
     */
    public void responseExcel(HttpServletRequest request, HttpServletResponse response, String fileName, String sheetName, String headTitle, List<String> titles, List<List<String>> data){
        try {
            HSSFWorkbook workbook = new HSSFWorkbook();
            Sheet sheet = workbook.createSheet(sheetName);
            int index = 0;
            /***********大标题 跨行跨列****/
            CellRangeAddress region = new CellRangeAddress(0, 0, 0, data.get(0).size() - 1);
            sheet.addMergedRegion(region);
            Row titleRow = sheet.createRow(index++);
            //行高
            titleRow.setHeightInPoints(30F);

            Cell titleCell = titleRow.createCell(0);
            CellStyle topStyle = this.getTopCell(workbook);
            titleCell.setCellStyle(topStyle);
            titleCell.setCellValue(headTitle);
            //列头
            Row headerRow = sheet.createRow(index++);
            headerRow.setHeightInPoints(25F);
            CellStyle titleStyle = this.getHeadCell(workbook);
            for(int j = 0; j < titles.size(); j++) {
                headerRow.createCell(j).setCellStyle(titleStyle);
                headerRow.getCell(j).setCellValue(titles.get(j));
            }

            this.creatBodyCell(index,workbook,data,sheet,titles.size());

            this.responseBrowser(request,response,fileName,workbook);

        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    /**
     * 返回数据到浏览器
     * @param request
     * @param response
     * @param fileName
     * @param workbook
     */
    public void responseBrowser(HttpServletRequest request,HttpServletResponse response ,String fileName, HSSFWorkbook workbook){
        try {
            String userAgent = request.getHeader("user-agent");
            if (userAgent != null && userAgent.indexOf("Firefox") >= 0
                    || userAgent.indexOf("Chrome") >= 0 || userAgent.indexOf("Safari") >= 0) {
                fileName = new String(fileName.getBytes(), "ISO8859-1");
            } else {
                fileName = URLEncoder.encode(fileName, "UTF8");
            }
            response.setCharacterEncoding("UTF-8");
            response.setContentType("application/vnd.ms-excel;charset=UTF-8");
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName+".xlsx");
            response.addHeader("Pargam", "no-cache");
            response.addHeader("Cache-Control", "no-cache");
            OutputStream out = response.getOutputStream();
            workbook.write(out);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }


    /**
     * 生产内容数据
     * @param nowRowIndex
     * @param workbook
     * @param data
     * @param sheet
     * @param columnSize
     */
    public void creatBodyCell(int nowRowIndex,HSSFWorkbook workbook ,List<List<String>> data, Sheet sheet,int columnSize){
        // 创建数据行
        AtomicInteger rowIndex = new AtomicInteger(nowRowIndex);
        CellStyle bodyStyle = this.getBodyCell(workbook);
        data.forEach(rowList -> {
            Row row = sheet.createRow(rowIndex.getAndIncrement());
            row.setHeightInPoints(20F);
            AtomicInteger x = new AtomicInteger();
            rowList.forEach((cellValue) ->{
                Cell cell=row.createCell(x.getAndIncrement());
                cell.setCellStyle(bodyStyle);
                cell.setCellValue(cellValue);
            });
        });
        /***列宽自适应**/
        for(int j = 0; j < columnSize; j++) {
            sheet.autoSizeColumn(j);
        }
    }



    /**************************单元格样式*****************************************/

    public CellStyle getTopCell(HSSFWorkbook workbook) {
        HSSFCellStyle cellStyle=this.getCellStyle(workbook);
        HSSFFont headFont=this.getFont(workbook,(short) 18,true);
        cellStyle.setFont(headFont);
        return cellStyle;
    }

    public CellStyle getHeadCell(HSSFWorkbook workbook) {
        HSSFCellStyle cellStyle=this.getCellStyle(workbook);
        HSSFFont headFont=this.getFont(workbook,(short) 16,true);
        cellStyle.setFont(headFont);
        return cellStyle;
    }

    public CellStyle getBodyCell(HSSFWorkbook workbook) {
        HSSFCellStyle cellStyle=this.getCellStyle(workbook);
        HSSFFont headFont=this.getFont(workbook,(short) 12,false);
        cellStyle.setFont(headFont);
        return cellStyle;
    }

    public HSSFFont getFont(HSSFWorkbook workbook ,short fontSize,boolean bold){
        // 设置字体
        HSSFFont font = workbook.createFont();
        //设置字体大小
        font.setFontHeightInPoints(fontSize);
        if(bold){
            font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        }
        //设置字体名字
        font.setFontName("宋体");
        return font;
    }

    public HSSFCellStyle getCellStyle(HSSFWorkbook workbook) {
        //设置样式;
        HSSFCellStyle style = workbook.createCellStyle();
        //设置底边框;
        style.setBorderBottom(HSSFCellStyle.BORDER_NONE);
        //设置左边框;
        style.setBorderLeft(HSSFCellStyle.BORDER_NONE);
        //设置右边框;
        style.setBorderRight(HSSFCellStyle.BORDER_NONE);
        //设置顶边框;
        style.setBorderTop(HSSFCellStyle.BORDER_NONE);
        //设置水平对齐的样式为居中对齐;
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        //设置垂直对齐的样式为居中对齐;
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        return style;
    }
}

最后 贴出excel导出图片