Apache POI导出Excel工具

使用Apache POI导出Excel报表

以下工具使用的是最新的POI-3.15版本，使用其它版本POI时可能需要修改部分代码（可自行修改表头部分的样式）。直接上代码吧！

1、 Maven 依赖：

    <!-- Poi包 -->    <dependency>        <groupId>org.apache.poi</groupId>        <artifactId>poi</artifactId>        <version>3.15</version>    </dependency>

2、工具源码（包含测试代码）

    import java.beans.PropertyDescriptor;    import java.io.FileNotFoundException;    import java.io.FileOutputStream;    import java.io.IOException;    import java.io.OutputStream;    import java.text.SimpleDateFormat;    import java.util.ArrayList;    import java.util.Date;    import java.util.List;    import org.apache.commons.beanutils.BeanUtils;    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.hssf.util.HSSFColor;    import org.apache.poi.ss.usermodel.BorderStyle;    import org.apache.poi.ss.usermodel.FillPatternType;    import org.apache.poi.ss.usermodel.HorizontalAlignment;    public class ExcelHssfDemo {    public static <T> void writeExcel(List<T> dataList, OutputStream output, String[] headNames, String[] fields, String datePattern)            throws FileNotFoundException {        SimpleDateFormat FORMAT = new SimpleDateFormat(datePattern);        HSSFWorkbook workbook = new HSSFWorkbook();        HSSFSheet sheet = workbook.createSheet();        HSSFRow row = sheet.createRow((int) 0);        getHeadStyle(workbook, row, headNames);        try {            T bean = null;            for (int i = 0; i < dataList.size(); i++) {                row = sheet.createRow((int) (i + 1));                bean = dataList.get(i);                for (int j = 0; j < headNames.length; j++) {                    Object value = new PropertyDescriptor(fields[j], bean.getClass()).getReadMethod().invoke(bean, new Object[]{});                    if (value instanceof Date) {                        row.createCell(j).setCellValue(FORMAT.format((Date) value));                    } else if (value instanceof Boolean) {                        row.createCell(j).setCellValue((Boolean) value ? "是" : "否");                    } else {                        row.createCell(j).setCellValue(value);                    }                }            }            for (int i = 0; i < headNames.length; i++) {                sheet.autoSizeColumn(i); // 自动调整列宽度            }            workbook.write(output);        } catch (Exception e) {            e.printStackTrace();        } finally {            try {                if (output != null) {                    output.close();                }            } catch (IOException e) {                e.printStackTrace();            }        }    }    // 需要传入表头字段数组    private static void getHeadStyle(HSSFWorkbook workbook, HSSFRow row, String[] headNames) {        HSSFCellStyle style = workbook.createCellStyle();        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);        style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);        style.setAlignment(HorizontalAlignment.CENTER);        style.setBorderTop(BorderStyle.THIN);        style.setBorderBottom(BorderStyle.THIN);        style.setBorderLeft(BorderStyle.THIN);        style.setBorderRight(BorderStyle.THIN);        HSSFFont font = workbook.createFont();        font.setFontName("宋体");        font.setFontHeightInPoints((short) 11);        font.setBold(true);        style.setFont(font);        HSSFCell cell = null;        for (int i = 0; i < headNames.length; i++) {            cell = row.createCell(i);// 生成表头,并定义样式            cell.setCellValue(headNames[i]);            cell.setCellStyle(style);        }    }    public static void main(String[] args) {        try {            List<User> userList = new ArrayList<User>();            for (int i = 0; i < 20; i++) {                User user = new User("test:" + i, "passwd:" + i, 20 + i, new Date(), "I am test object", true);                userList.add(user);            }            String path = "/home/felix/Desktop/";            String[] userHeads = new String[] { "用户名", "密码", "年龄", "注册日期", "说明", "测试布尔变量" };            String[] userFields = new String[] { "user_name", "password", "age", "time", "desri", "is" };            writeExcel(userList, new FileOutputStream(path + "user.xls"), userHeads, userFields, "yyyy-MM-dd HH:mm:ss");        } catch (Exception e) {            e.printStackTrace();        }    }}

3、测试实体类

public class User {    public String user_name;    public String password;    public Integer age;    public Date time;    public String desri;    public boolean is;    public User(String user_name, String password, Integer age, Date time, String desri, boolean is) {        super();        this.user_name = user_name;        this.password = password;        this.age = age;        this.time = time;        this.desri = desri;        this.is = is;    }    public boolean isIs() {        return is;    }    public void setIs(boolean is) {        this.is = is;    }    public String getUser_name() {        return user_name;    }    public void setUser_name(String user_name) {        this.user_name = user_name;    }    public String getPassword() {        return password;    }    public void setPassword(String password) {        this.password = password;    }    public Integer getAge() {        return age;    }    public void setAge(Integer age) {        this.age = age;    }    public Date getTime() {        return time;    }    public void setTime(Date time) {        this.time = time;    }    public String getDesri() {        return desri;    }    public void setDesri(String desri) {        this.desri = desri;    }}

 
工作需要只写了导出部分，导入部分有时间再补上。