Java导出数据到excel

HSSFWorkbook workbook = new HSSFWorkbook();// 声明一个工作薄
HSSFSheet sheet = workbook.createSheet("表格名称");// 生成一个表格
HSSFRow row = sheet.createRow(0);//下标为0的行数，第一行
HSSFCell cell = row.createCell(0);//第一行下标为0的格子
cell.setCellValue("内容");//给第一行下标为0的格子设置内容

// 获取第一个实际行的下标
// getFirstRowNum: 8
System.out.println("getFirstRowNum: " + sheet.getFirstRowNum());
// 获取最后一个实际行的下标
// getLastRowNum: 11
System.out.println("getLastRowNum: " + sheet.getLastRowNum());

Row row=sheet.getRow(sheet.getLastRowNum());
// 获取在某行第一个单元格的下标
// getFirstCellNum: 4
System.out.println("getFirstCellNum: " + row.getFirstCellNum());
// 获取在某行的列数
// getLastCellNum: 8
System.out.println("getLastCellNum: " + row.getLastCellNum()); 

Controller

 @GetMapping("/exportExcel")
    @ApiOperation("导出")
    public R<String> exportExcel(String dataName, String status, String dataNode, String warehousingScheme, Integer pageNum, Integer pageSize) {

        JsonPage<FlowArchive> jsonPage = normalizationTaskService.getResult(dataName, status, dataNode, warehousingScheme, pageNum, pageSize);

        FlowArchive flowArchive = null;
        List<FlowArchive> list = jsonPage.getList();
        for (FlowArchive l : list){
            System.out.println(l);
            Long id = l.getId();
            flowArchive = new FlowArchive();
            flowArchive.setId(id);
            if("".equals(flowArchive.getDataName())){
                flowArchive.setDataName("/");
            }else {
                flowArchive.setDataName(l.getDataName());
            }

            if("".equals(flowArchive.getDataSize())){
                flowArchive.setDataSize("/");
            }else {
                flowArchive.setDataSize(l.getDataSize());
            }

            flowArchive.setScanTime(l.getScanTime());


            flowArchive.setWarehousingFinishTime(l.getWarehousingFinishTime());

            if("".equals(flowArchive.getNodeIp())){
                flowArchive.setNodeIp("/");
            }else {
                flowArchive.setNodeIp(l.getNodeIp());
            }
            if("".equals(flowArchive.getNodePort())){
                flowArchive.setNodePort("/");
            }else {
                flowArchive.setNodePort(l.getNodePort());
            }
            if("".equals(flowArchive.getDataNode())){
                flowArchive.setDataNode("/");
            }else {
                flowArchive.setDataNode(l.getDataNode());
            }
            if("".equals(flowArchive.getWarehousingScheme())){
                flowArchive.setWarehousingScheme("/");
            }else {
                flowArchive.setWarehousingScheme(l.getWarehousingScheme());
            }
            if("".equals(flowArchive.getStatus())){
                flowArchive.setStatus("/");
            }else {
                flowArchive.setStatus(l.getStatus());
            }
//            flowArchive.setDataSize(l.getDataSize());
//            flowArchive.setScanTime(l.getScanTime());
//            flowArchive.setWarehousingFinishTime(l.getWarehousingFinishTime());
//            flowArchive.setNodeIp(l.getNodeIp());
//            flowArchive.setNodePort(l.getNodePort());
//            flowArchive.setDataNode(l.getDataNode());
//            flowArchive.setWarehousingScheme(l.getWarehousingScheme());
//            flowArchive.setStatus(l.getStatus());
        }

        Map<String,String> map =new HashMap<>();
        map.put("数据名",flowArchive.getDataName());
        map.put("数据大小",flowArchive.getDataSize());
        map.put("扫描时间",flowArchive.getScanTime().toString());
        map.put("入库完成时间",flowArchive.getWarehousingFinishTime().toString());
        map.put("节点IP",flowArchive.getNodeIp());
        map.put("节点端口",flowArchive.getNodePort());
        map.put("数据节点",flowArchive.getDataNode());
        map.put("入库方案",flowArchive.getWarehousingScheme());
        map.put("执行状态",flowArchive.getStatus());

        //"D:\\excel.xlsx"
        File file = new File("D:\\temp\\excel.xlsx");
        if(!file.exists()){
            try {
                file.createNewFile();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        String address = "excel.xlsx";

        FileOutputStream out = null;
        try {
            out = new FileOutputStream(file);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }

        try {
            ExcelUtils excelUtils = new ExcelUtils();
            Collection<JsonPage<FlowArchive>> collection = new ArrayList<>();
            collection.add(jsonPage);
            excelUtils.export("常态化任务",map,collection,"yyyy-MM-dd HH:mm:ss",out);

        } catch (Exception e) {
            e.printStackTrace();
            return R.fail();
        }
        return R.ok(address);

    }

ExcelUtils

/**
 * Copyright © 2021 航天宏图信息技术股份有限公司 版权所有
 *
 * @接口方法说明: 导出帮助工具
 * @Prject:
 * @Package: com.piesat.common.util.ExcelUtil
 * @ClassName: ExcelUtil.java
 * @author: EDZ
 * @date: 2021/4/22
 * @version: V1.0
 */
package com.dbm.assetcatalog.utils;

import com.alibaba.fastjson.JSONObject;
import org.apache.poi.hssf.usermodel.*;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.Map.Entry;

@SuppressWarnings({"deprecation", "rawtypes"})
public class ExcelUtils<T> {
    /**
     * @功能: exportFile
     * @接口方法说明: 导出日志
     * @param: httpResponse
     * @param: head
     * @return: int
     */
    public int exportFile(Map<String, String> head, List<T> resultlist, HttpServletResponse httpResponse) {
        try {
            httpResponse.setContentType("application/x-download");
            httpResponse.setCharacterEncoding("gbk");
            httpResponse.setHeader("Content-Disposition", "attachment;filename=export.xls");
            export(head, resultlist, httpResponse.getOutputStream());
            httpResponse.getOutputStream().close();
            return 200;
        } catch (Exception e) {
            return 500;
        }
    }

    public void export(Collection<T> dataset, OutputStream out) throws IOException {
        export("sheet", null, dataset, "yyyy-MM-dd", out);
    }

    /**
     * @throws IOException
     * @功能: export
     * @接口方法说明: 导出
     * @param: Collection<T> dataset
     * @param: OutputStream out
     */
    public void export(Map<String, String> headers, Collection<T> dataset, OutputStream out) throws IOException {
        export("sheet", headers, dataset, "yyyy-MM-dd", out);
    }

    public void export(Map<String, String> headers, Collection<T> dataset, String pattern, OutputStream out) throws IOException {
        export("sheet", headers, dataset, pattern, out);
    }

    /**
     * 将数据导出到Excel文件
     *
     * @param title   表格标题名
     * @param headers 表格属性列名数组
     * @param dataSet 需要导出的数据集合
     * @param out     输出的流对象，EXCEL文档可写路径
     * @param pattern 设定时间输出格式，默认为yyy-MM-dd
     * @throws IOException
     */
    public void export(String title, Map<String, String> headers, Collection<T> dataSet, String pattern, OutputStream out) throws IOException {
        HSSFWorkbook workbook = new HSSFWorkbook();// 声明一个工作薄
        HSSFSheet sheet = workbook.createSheet(title);// 生成一个表格
        HSSFRow row = sheet.createRow(0);
        int index = 0;
        for (Entry<String, String> ent : headers.entrySet()) {
            HSSFCell cell = row.createCell(index);
            cell.setCellValue(new HSSFRichTextString(ent.getKey()));
            index++;
            System.out.println(cell);
        }


        HSSFRow row2 = sheet.createRow(sheet.getLastRowNum() + 1);
        int index2 = 0;
        for (Entry<String, String> ent : headers.entrySet()) {
            HSSFCell cell = row2.createCell(index2);
            cell.setCellValue(new HSSFRichTextString(ent.getValue()));
            index2 ++;
            System.out.println(cell);
        }

        workbook.write(out);
        out.close();
        workbook.close();
    }

    public static List<String> getMethodList(Class c) {
        List<String> methodList = new ArrayList<>();
        Method[] methods = c.getMethods();
        for (Method method : methods) {
            methodList.add(method.getName());
        }
        return methodList;
    }

    private void convert(T t, Map<String, String> headers, Map<String, Object> map) {
        List<String> getMethodList = getMethodList(t.getClass());
        for (Entry<String, String> header : headers.entrySet()) {
            JSONObject o = JSONObject.parseObject(JSONObject.toJSONString(t));
            Object val = o.get(header.getKey());
            if (getMethodList.contains(header.getKey())) {
                try {
                    val = t.getClass().getMethod(header.getKey()).invoke(t);
                } catch (NoSuchMethodException e) {
                    e.printStackTrace();
                } catch (IllegalAccessException e) {
                    e.printStackTrace();
                } catch (InvocationTargetException e) {
                    e.printStackTrace();
                }
            }
            map.put(header.getKey(), val);
        }
    }

    private String valueFormat(Object value, String pattern) {
        if (value == null)
            return null;
        if (value instanceof String)
            return value.toString();
        if (value instanceof Boolean)
            return ((Boolean) value) ? "是" : "不是";
        if (value instanceof Date)
            return new SimpleDateFormat(pattern).format((Date) value);
        if (value instanceof byte[])
            return null;
        return value.toString();
    }
}

main方法单独测试

import java.io.File;
import java.io.FileOutputStream;
 
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
 
public class ExcelUtil {
	public static void main(String[] args) throws Exception {
		HSSFWorkbook mWorkbook = new HSSFWorkbook();
 
		HSSFSheet mSheet = mWorkbook.createSheet("Student");
 
		// 创建Excel标题行，第一行。
		HSSFRow headRow = mSheet.createRow(0);
		headRow.createCell(0).setCellValue("id");
		headRow.createCell(1).setCellValue("name");
		headRow.createCell(2).setCellValue("gender");
		headRow.createCell(3).setCellValue("age");
 
		// 往Excel表中写入3行测试数据。
		createCell(1, "zhang", "男", 18, mSheet);
		createCell(2, "phil", "男", 19, mSheet);
		createCell(3, "fly", "男", 20, mSheet);
 
		File xlsFile = new File("f.xls");
		mWorkbook.write(xlsFile);// 或者以流的形式写入文件 mWorkbook.write(new FileOutputStream(xlsFile));
		mWorkbook.close();
	}
 
	// 创建Excel的一行数据。
	private static void createCell(int id, String name, String gender, int age, HSSFSheet sheet) {
		HSSFRow dataRow = sheet.createRow(sheet.getLastRowNum() + 1);
 
		dataRow.createCell(0).setCellValue(id);
		dataRow.createCell(1).setCellValue(name);
		dataRow.createCell(2).setCellValue(gender);
		dataRow.createCell(3).setCellValue(age);
	}
}