java使用poi生成Excel文件

1. maven导入poi包：

　　

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

 

2. 新建测试数据实体类：

　　

package com.clz.testexportexcel;

public class ExcelModel {

    private String name;
    private int age;
    private String phone;
    private String address;
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public int getAge() {
        return age;
    }
    public void setAge(int age) {
        this.age = age;
    }
    public String getPhone() {
        return phone;
    }
    public void setPhone(String phone) {
        this.phone = phone;
    }
    public String getAddress() {
        return address;
    }
    public void setAddress(String address) {
        this.address = address;
    }
    public ExcelModel(String name, int age, String phone, String address) {
        super();
        this.name = name;
        this.age = age;
        this.phone = phone;
        this.address = address;
    }
    public ExcelModel() {
        super();
    }
    
}

3. 新建工具类及测试

package com.clz.testexportexcel;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.HorizontalAlignment;

/**
 * 生成导出excel
 * @author Administrator
 *
 */
public class TestExportExcel {

    public static void main(String[] args) {
        
        String exportFileName = "testExcel.xls";
        String exportFilePath = "E:\\testExcel";
        List<ExcelModel> excelModels = new ArrayList<ExcelModel>();
        ExcelModel em1 = new ExcelModel("张三", 18, "123456", "上海");
        ExcelModel em2 = new ExcelModel("李四", 20, "456789", "南京");
        ExcelModel em3 = new ExcelModel("王五", 22, "987412", "杭州");
        excelModels.add(em1);
        excelModels.add(em2);
        excelModels.add(em3);
        String exportExcel = exportExcel(excelModels, exportFilePath, exportFileName);
        System.out.println(exportExcel);
        
    }
    
    /**
     * 导出excel
     * @param excelModel        数据实体类
     * @param exportFilePath    导出文件路径
     * @param exportFileName    导出文件名称
     * @return 生成文件路径
     */
    public static String exportExcel(List<ExcelModel> excelModels, String exportFilePath, String exportFileName) {
        
        String fileUrl = null;
        HSSFWorkbook wb = null;
        OutputStream os = null;
        try {
            // 判断文件夹是否存在，不存在就创建
            File fileDir = new File(exportFilePath);
            if(!fileDir.exists()) {
                fileDir.mkdirs();
            }
            /**
             *  生成excel文件
             */
            // 第一步，创建一个webbook，对应一个Excel文件
            wb = new HSSFWorkbook();
            // 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
            HSSFSheet sheet = wb.createSheet("用户信息导出");
            // 第三步，在sheet中添加表头第0行
            HSSFRow row = sheet.createRow((int) 0);
            // 第四步，创建单元格，并设置值表头 设置表头居中
            HSSFCellStyle style = wb.createCellStyle();
            style.setAlignment(HorizontalAlignment.CENTER); // 居中格式
            HSSFFont font = wb.createFont();
            font.setColor(HSSFFont.COLOR_RED);//HSSFColor.VIOLET.index //字体颜色
            //把字体应用到当前的样式
            style.setFont(font);
            HSSFCell cell=null;
            //表头(与实体类数据一一对应)
            String[] headers={"姓名", "年龄", "电话", "地址"};
            for(int i=0;i<headers.length;i++){
                cell = row.createCell((short) i);
                cell.setCellValue(headers[i]);
                cell.setCellStyle(style);
            }
            // 写数据到excel
            for (int i = 0; i < excelModels.size(); i++) {
                ExcelModel em = excelModels.get(i);
                // 创建行（从第二行开始）
                row = sheet.createRow((int) i + 1); 
                // 创建列并赋值
                row.createCell(0).setCellValue(em.getName());
                row.createCell(1).setCellValue(em.getAge());
                row.createCell(2).setCellValue(em.getPhone());
                row.createCell(3).setCellValue(em.getAddress());
            }
            // 写入文件
            fileUrl = exportFilePath + File.separator + exportFileName;
            os = new FileOutputStream(fileUrl);
            wb.write(os);
            os.flush();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if(os != null) {
                try {
                    os.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            if(wb != null) {
                try {
                    wb.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        if(fileUrl != null && new File(fileUrl).exists()) {
            return fileUrl;
        }
        return null;
    }
    
}