java中poi实现Excel导出

第一步：引入所需依赖

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

第二步：构建数据（模拟）代码如下

package com.mongo.controller;
import com.mongo.entity.Meals;
import org.apache.poi.hssf.usermodel.*;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;

/**
 * @author: songgt
 * @date: 2018-07-11 8:52
 */
@RestController
@RequestMapping(path = "/test")
public class TestController {
    @GetMapping(path = "/export")
    public void export(HttpServletResponse response) throws Exception{
        //构建数据
        List<Meals> mealsList = new ArrayList<>();
        Meals meals = new Meals();
        meals.setTcqd("0001");
        meals.setTcbm("000254");
        meals.setWxbs("asdfadxoiwnhFSAK");
        meals.setYhdd("FDSDFK00125");
        meals.setSjxm("liumin");
        meals.setSjdz("shengtangdasha");
        meals.setLxfs("12398432498");
        meals.setSjjg("6541");
        meals.setSjlx("ddddddd");
        meals.setSbkh("555555");

        Meals meals1 = new Meals();
        meals1.setTcqd("0002");
        meals1.setTcbm("0002232323");
        meals1.setWxbs("asdfadxoiwnhFSAK");
        meals1.setYhdd("FDSDFK00125");
        meals1.setSjxm("liumin");
        meals1.setSjdz("shengtangdasha");
        meals1.setLxfs("12398432498");
        meals1.setSjjg("6541");
        meals1.setSjlx("啊啊啊啊啊啊啊啊啊啊啊啊啊");
        meals1.setSbkh("666666666666");
        mealsList.add(meals);
        mealsList.add(meals1);

        //Excel表头
        String []title = {"套餐渠道","套餐编码","微信标识","用户订单","收件姓名","收件地址","联系方式","收件价格","收件类型","设备卡号"};
        //sheet名
        String sheetName = "套餐信息";
        String fileName = new String("套餐信息列表".getBytes(),"ISO-8859-1");

        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet(sheetName);
        HSSFRow row = sheet.createRow(0);
        //这里用了两次独立的for循环，我觉得这样思路更清晰，你也可以用嵌套循环
        for (int i=0;i<title.length;i++){
            HSSFCell cell = row.createCell(i);
            cell.setCellValue(title[i]);
            //列宽自适应好像不生效
            sheet.autoSizeColumn(i);
            //解决了列头自适应
            sheet.setColumnWidth(i,title[i].getBytes().length*256);
        }
        for (int i=0;i<mealsList.size();i++){
            row = sheet.createRow(i+1);
            Meals m = mealsList.get(i);
            row.createCell(0).setCellValue(m.getTcqd());
            row.createCell(1).setCellValue(m.getTcbm());
            row.createCell(2).setCellValue(m.getWxbs());
            row.createCell(3).setCellValue(m.getYhdd());
            row.createCell(4).setCellValue(m.getSjxm());
            row.createCell(5).setCellValue(m.getSjdz());
            row.createCell(6).setCellValue(m.getLxfs());
            row.createCell(7).setCellValue(m.getSjjg());
            row.createCell(8).setCellValue(m.getSjlx());
            row.createCell(9).setCellValue(m.getSbkh());
        }
        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Content-disposition", "attachment;charset=UTF-8;filename="+fileName+".xls");
        OutputStream outputStream = response.getOutputStream();
        workbook.write(outputStream);
        outputStream.flush();
        outputStream.close();
    }
}

第三步：访问测试http://localhost:8888/test/export
访问下载如下

image.png
文件打开
image.png