JAVA导出EXCEL表格

废话不多说，直接上代码

1.pom.xml中添加依赖

<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.17</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.17</version>
        </dependency>

2.创建工具类

package com.ieou.capsule.util;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.HorizontalAlignment;

import static org.apache.poi.ss.usermodel.VerticalAlignment.CENTER;

public class ExcelUtil {

    /**
     * 导出Excel
     * @param sheetName sheet名称
     * @param title 标题
     * @param values 内容
     * @param wb HSSFWorkbook对象
     * @return
     */
    public static HSSFWorkbook getHSSFWorkbook(String sheetName, String []title, String [][]values, int listSize, HSSFWorkbook wb){

        // 第一步，创建一个HSSFWorkbook，对应一个Excel文件
        if(wb == null){
            wb = new HSSFWorkbook();
        }

        // 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = wb.createSheet(sheetName);

        sheet.setColumnWidth(0,20 * 256);// 设置第一行为30个字符
        sheet.setColumnWidth(1,25 * 256);// 设置第一行为30个字符
        sheet.setColumnWidth(3,20 * 256);// 设置第一行为30个字符
        sheet.setColumnWidth(6,13 * 256);// 设置第一行为30个字符


        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
        HSSFRow row = sheet.createRow(0);

        // 第四步，创建单元格，并设置值表头 设置表头居中
        HSSFCellStyle style = wb.createCellStyle();
//        style.setAlignment(CellStyle); // 创建一个居中格式
        style.setVerticalAlignment(CENTER); //垂直
        style.setAlignment(HorizontalAlignment.CENTER);

        //声明列对象
        HSSFCell cell = null;

        //创建标题
        for(int i=0;i<title.length;i++){
            cell = row.createCell(i);
            cell.setCellValue(title[i]);
            cell.setCellStyle(style);

        }

        //创建内容
        for(int i=0;i<listSize;i++){
            row = sheet.createRow(i + 1);
            for(int j=0;j<values[i].length;j++){
                //将内容按顺序赋给对应的列对象
                row.createCell(j).setCellValue(values[i][j]);
            }
        }
        return wb;
    }
}

3.业务代码

/**
     * 领取记录导出excel
     * @return
     * @throws Exception
     */
    @Override
    public String exportExcel(List<Integer> receiveRecordIdList) throws Exception {
        //获取数据
        ReceiveRecordExample receiveRecordExample = new ReceiveRecordExample();
        receiveRecordExample.or().andIdIn(receiveRecordIdList);
        List<ReceiveRecord> list = receiveRecordMapper.selectByExample(receiveRecordExample);

        //标题
        String [] title = {"时间","公司","物料编号","商品名称","价格","领取人","手机号","数量"};
        //excel文件名
        SimpleDateFormat sdf = new SimpleDateFormat("YYYY-MM-dd HH:mm:ss SSS");
        String format = sdf.format(new Date());
        String fileName = "领取记录表"+format+".xls";
        //sheet名
        String sheetName = "领取记录表";

        sdf = new SimpleDateFormat("YYYY-MM-dd HH:mm:ss");

//        String [][] content = new String[list.size() > title.length ? list.size() : title.length][];

        String [][] content = new String[list.size()][title.length];

        for (int i = 0; i < list.size(); i++) {
            //定义每行有几列
//            content[i] = new String[list.size() > title.length ? list.size() : title.length];

            ReceiveRecord obj = list.get(i);
            content[i][0] = sdf.format(obj.getCreateTime());
            content[i][1] = obj.getCompanyName();
            content[i][2] = obj.getGoodsNo();
            content[i][3] = obj.getGoodsName();
            content[i][4] = obj.getPrice().toString();
            content[i][5] = obj.getName();
            content[i][6] = obj.getMobilePhone();
            content[i][7] = obj.getNum().toString();
        }

        //创建HSSFWorkbook
        HSSFWorkbook wb = ExcelUtil.getHSSFWorkbook(sheetName, title, content, list.size(),null);

        ByteArrayOutputStream stream = new ByteArrayOutputStream();
        wb.write(stream);

        String upload = qiNiuService.upload(stream,fileName);
        return upload;
    }