java导出到excel通用版

我会带着你远行 2022-11-07 04:11 201阅读 0赞

## 一、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);
        }

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5ODk4MTkx_size_16_color_FFFFFF_t_70]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5ODk4MTkx_size_16_color_FFFFFF_t_70]: /images/20221023/639cc7126123445590f032e37f5f9f94.png