java 利用 poi 生成 Excel文件与spring使用文件流形式下载文件

本文为结合参考资料整合而来。
第一步导包：
三个jar：
poi
poi-ooxml
poi-ooxml-schemas

maven：

<properties>
        <poi.version>3.12</poi.version>
    </properties>
<dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>${poi.version}</version>
            <type>pom</type>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>${poi.version}</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>${poi.version}</version>
        </dependency>

公用类：ExcelUtil

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellRangeAddress;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;

/**
 * Excel util, create excel sheet, cell and style.
 * @param <T> generic class.
 */
public class ExcelUtil<T> {

    public HSSFCellStyle getCellStyle(HSSFWorkbook workbook,boolean isHeader){
        HSSFCellStyle style = workbook.createCellStyle();
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style.setLocked(true);
        if (isHeader) {
            style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
            style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
            HSSFFont font = workbook.createFont();
            font.setColor(HSSFColor.BLACK.index);
            font.setFontHeightInPoints((short) 12);
            font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
            style.setFont(font);
        }
        return style;
    }


    public  void generateHeader(HSSFWorkbook workbook,HSSFSheet sheet,String[] headerColumns){
        HSSFCellStyle style = getCellStyle(workbook, true);
        Row row = sheet.createRow(0);
        row.setHeightInPoints(30);
        for(int i=0;i<headerColumns.length;i++){
            Cell cell = row.createCell(i);
            String[] column = headerColumns[i].split("_#_");
            sheet.setColumnWidth(i, Integer.valueOf(column[1]));
            cell.setCellValue(column[0]);
            cell.setCellStyle(style);
        }
    }

    public static InputStream writeExcelToStream(HSSFWorkbook workbook,HSSFSheet sheet) {
        ByteArrayOutputStream bos = new ByteArrayOutputStream();
        try {
            workbook.write(bos);
        } catch (IOException e) {
            e.printStackTrace();
        }
        try {
            workbook.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
        //数据在bos中
        ByteArrayInputStream bis = new ByteArrayInputStream(bos.toByteArray());
        return bis;
    }


    @SuppressWarnings({ "rawtypes", "unchecked" })
    public  HSSFSheet creatAuditSheet(HSSFWorkbook workbook,String sheetName,
                                          List<T> dataset,String[] headerColumns,String[] fieldColumns) throws NoSuchMethodException, IllegalAccessException, IllegalArgumentException, InvocationTargetException {

        HSSFSheet sheet = workbook.createSheet(sheetName);
        //sheet.protectSheet(""); //保护生成Excel文档，设置密码访问.
        //自动对生成的Excel 文档第一行标题栏设置成filter 过滤形式, 方便用户使用
        char[] endChar = Character.toChars( 'A' + (headerColumns.length - 1) );
        String rangeAddress = "A1:" + String.valueOf(endChar) + "1";
        sheet.setAutoFilter(CellRangeAddress.valueOf(rangeAddress));

        generateHeader(workbook,sheet,headerColumns);
        HSSFCellStyle style = getCellStyle(workbook,false);
        SimpleDateFormat sd = new SimpleDateFormat("yyyy-MM-dd");
        int rowNum = 0;
        for(T t:dataset){
            rowNum++ ;
            Row row = sheet.createRow(rowNum);
            row.setHeightInPoints(25);
            for(int i = 0; i < fieldColumns.length; i++){
                String fieldName = fieldColumns[i] ;

                String getMethodName = "get" + fieldName.substring(0,1).toUpperCase() + fieldName.substring(1);
                try {
                    Class clazz = t.getClass();
                    Method getMethod;
                    getMethod = clazz.getMethod(getMethodName, new Class[]{} );
                    Object value = getMethod.invoke(t, new Object[]{});
                    String cellValue = "";
                    if (value instanceof Date){
                        Date date = (Date)value;
                        cellValue = sd.format(date);
                    }else{
                        cellValue = null != value ? value.toString() : "";
                    }
                    Cell cell = row.createCell(i);
                    cell.setCellStyle(style);
                    cell.setCellValue(cellValue);

                } catch (Exception e) {

                }
            }
        }
        return sheet;
    }
}

下载提供流的形式：

import org.springframework.util.FileCopyUtils;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
/**
 * Download Excel Util
 */
public class ExcelOperateUtil {
    public static void downloadExcel(InputStream inputStream, HttpServletResponse response, String excelName){
        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Content-Disposition", "attachment;" + "filename=" + excelName);
        try {
            FileCopyUtils.copy(inputStream, response.getOutputStream());
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

测试类：
UserTest

package ExcelTest;

public  class UserTest {

    private String name;
    private int age;
    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 getAddress() {
        return address;
    }
    public void setAddress(String address) {
        this.address = address;
    }

    public UserTest(String name, int age, String address) {
        this.name = name;
        this.age = age;
        this.address = address;
    }
}

PoiTest ：

package ExcelTest;
import java.io.*;
import java.lang.reflect.InvocationTargetException;
import java.util.ArrayList;
import java.util.List;
import com.fx.util.ExcelUtil;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class PoiTest {
    /*excel column formate:column_#_width, excel中每一列的名称*/
    public static final String[] USER_RECORES_COLUMNS = new String[]{
            "User Name_#_3000",
            "Address_#_7000"
    };
    /*the column will display on xls files. must the same as the entity fields.对应上面的字段.*/
    public static final String[] USER_RECORES_FIELDS = new String[]{
            "name","address"
    };
    public static void main(String[] args) throws NoSuchMethodException, IllegalAccessException, IllegalArgumentException, InvocationTargetException, IOException {
        List<UserTest> users = new ArrayList<UserTest>();
        for(int i=0; i<10;i++){
            UserTest u = new UserTest("name: " + i, i, "address: " + i);
            users.add(u);
        }
        //实际项目中，这个list 是从数据库中得到的
        HSSFWorkbook workbook = new HSSFWorkbook();
        ExcelUtil<UserTest> UserTestSheet = new ExcelUtil<UserTest>();
        HSSFSheet sheet = UserTestSheet.creatAuditSheet(workbook, "UserTest sheet xls",
                users, USER_RECORES_COLUMNS, USER_RECORES_FIELDS);
       /* FileOutputStream fileOut = new FileOutputStream("d:/user_test.xls");
        workbook.write(fileOut);
        fileOut.close();*/

        ByteArrayInputStream inputStream = (ByteArrayInputStream) ExcelUtil.writeExcelToStream(workbook, sheet);

        FileOutputStream fileOut = new FileOutputStream("d:/user_test1.xls");
        int data=inputStream.read();
        while(data!=-1){
            fileOut.write(data);
            data=inputStream.read();
        }
        fileOut.close();
    }
}

运行PoiTest，可以看到声称的excel效果。

参考完全复制粘贴，文件下载参考：
http://ju.outofmemory.cn/entry/67140
http://blog.csdn.net/lhjlhj123123/article/details/7638457