java导出到excel通用版

最新推荐文章于 2024-09-06 10:52:17 发布

旭旭1998

最新推荐文章于 2024-09-06 10:52:17 发布

阅读量5.2w

点赞数

本文链接：https://blog.csdn.net/qq_39898191/article/details/114647060

版权

一、Maven依赖

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.17</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.17</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml-schemas</artifactId>
    <version>3.17</version>
</dependency>
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <version>1.18.18</version>
    <scope>provided</scope>
</dependency>

二、实体类

package com.zx.consumer;

import lombok.Data;

/**
 * @author: zhaoxu
 * @date: 2021/3/10 20:46
 */
@Data
public class Persion {
    Integer id;
    String name;
    String sex;
    String place;
}

三、导出工具类

package com.zx.consumer;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.logging.Level;
import java.util.logging.Logger;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;

/**
 * @author: zhaoxu
 * @date: 2021/3/10 20:43
 */
public class ExcelUtil {
    public static Boolean exportToExcel(String title, String[] headers, List dossierList, OutputStream out) throws IllegalAccessException {
        boolean flag = true;
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet(title);
        sheet.setDefaultColumnWidth((short) 50);
        sheet.setDefaultRowHeight((short) 50);
        HSSFCellStyle style = workbook.createCellStyle();
        style.setFillBackgroundColor((short) 12);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        style.setAlignment(HorizontalAlignment.CENTER);
        Font font = workbook.createFont();
        font.setBold(true);
        font.setColor((short) 15);
        font.setFontHeightInPoints((short) 50);
        font.setFontName("宋体");
        style.setFont(font);
        //创建表头
        HSSFRow row = sheet.createRow(0);
        row.setHeightInPoints(20);//行高

        HSSFCell cell = row.createCell(0);
        cell.setCellValue(title);

        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, (headers.length - 1)));

        //创建标题
        HSSFRow rowTitle = sheet.createRow(1);
        rowTitle.setHeightInPoints(20);

        HSSFCell hssfCell;
        for (int i = 0; i < headers.length; i++) {
            hssfCell = rowTitle.createCell(i);
            hssfCell.setCellValue(headers[i]);
        }
        Field[] fields;
        int i = 2;
        for (Object obj : dossierList) {
            fields = obj.getClass().getDeclaredFields();
            HSSFRow rowBody = sheet.createRow(i);
            int j = 0;
            for (Field field : fields) {
                field.setAccessible(true);
                Object value = field.get(obj);
                if (null == value) {
                    value = "";
                }

                hssfCell = rowBody.createCell(j);
                hssfCell.setCellStyle(style);
                hssfCell.setCellValue(value.toString());
                j++;
            }

            i++;
        }
        try {
            workbook.write(out);
        } catch (IOException e) {
            e.printStackTrace();
            flag = false;
        } finally {
            //清理资源
            try {
                if (out != null) {
                    out.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return flag;
    }
        public static XSSFCellStyle createBaseStyle(XSSFWorkbook workbook) {
        XSSFCellStyle style = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        //设置字体
        Font font = workbook.createFont();
        font.setFontHeightInPoints((short) 12);
        font.setFontName("黑体");
        style.setFont(font);
        style.setWrapText(true);
        return style;
    }
}

四、测试

public static void main(String[] args) throws IOException, IllegalAccessException {
        Field[] fields = Persion.class.getDeclaredFields();
        String[] fieldsString = new String[fields.length];
        for (int i = 0; i < fields.length; i++) {
            fieldsString[i] = fields[i].getName();
        }
        List persions = new ArrayList();
        for (int i = 0; i < 1000; i++) {
            Persion user = new Persion();
            user.setId(i);
            user.setName("zx" + i);
            user.setPlace("yl" + i);
            user.setSex("男" + i);
            persions.add(user);
        }
        File f = new File("D:\\idea_workspace\\are-oms-tankInfo20210206\\are-oms-tankInfo\\src\\main\\resources\\template\\zx.xls");
        OutputStream out = new FileOutputStream(f);
        if (!f.exists()) {
            f.createNewFile();
        }
        ExcelUtil.exportToExcel("zx", fieldsString, persions, out);
    }