java导出excel文件

1、工具类

import java.io.OutputStream;
import java.util.List;

import javax.servlet.http.Cookie;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class ExcelExport {

/**
 * excel(xls)写出
 * @param sheetName 工作簿名称
 * @param excelName 文件名称
 * @param style 列宽度数组，如果传入为null或空数组，则不设置宽度
 * @param headText 表头信息
 * @param content 内容
 * @param response
 * @throws Exception
 */
public void writeExcel(String sheetName, String excelName, int[] style, String[] headText, List<List<String>> content, HttpServletResponse response) {
    try{
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet excelSheet = workbook.createSheet(sheetName);
        setLayout(excelSheet, style);
        setExcelHeader(excelSheet, headText);
        setExcelRows(excelSheet, content);
        Cookie cookie = new Cookie("author", excelName);

        response.addCookie(cookie);

// response.setHeader(“content-disposition”, “attachment;filename=” + excelName);
response.setHeader(“content-disposition”, “attachment;filename=” + new String(excelName.getBytes(“UTF-8”), “ISO8859-1”));
// response.setHeader(“content-disposition”, “attachment;filename=aa.xls”);
OutputStream outputStream = response.getOutputStream();
workbook.write(outputStream);
outputStream.flush();
outputStream.close();
} catch (Exception e) {
e.printStackTrace();
}
}

public void getExcelOutput(String sheetName, String excelName, int[] style, String[] headText, List<List<String>> content, OutputStream out) {
    try{
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet excelSheet = workbook.createSheet(sheetName);

        setLayout(excelSheet, style);
        setExcelHeader(excelSheet, headText);
        setExcelRows(excelSheet, content);
        workbook.write(out);
    } catch (Exception e) {
        e.printStackTrace();
    }
}

/**
 * 获取excel流信息
 * @param sheetName 工作簿名称
 * @param style 列宽度数组，如果传入为null或空数组，则不设置宽度
 * @param headText 表头信息
 * @param content 内容
 * @return
 */
public void getExcelStream(String sheetName, int[] style, String[] headText, List<List<String>> content, OutputStream outputStream){
    try{
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet excelSheet = workbook.createSheet(sheetName);

        setLayout(excelSheet, style);
        setExcelHeader(excelSheet, headText);
        setExcelRows(excelSheet, content);

        workbook.write(outputStream);
        outputStream.flush();
    } catch (Exception e) {
        e.printStackTrace();
    }
}

/**
 * 样式设置，这里只设置了列的宽度
 * @param excelSheet
 * @param style
 */
private void setLayout(HSSFSheet excelSheet,int[] style){
    if(style==null || style.length==0){
        return;
    }
    for(int i=0; i<style.length; i++){
        if(style[i]>0){
            excelSheet.setColumnWidth(i, style[i]);
        }
    }
}

/**
 * 填充表头信息
 * @param excelSheet
 * @param headText
 */
private void setExcelHeader(HSSFSheet excelSheet, String[] headText) {
    HSSFRow excelHeader = excelSheet.createRow(0);
    for(int i=0; i<headText.length; i++){
        excelHeader.createCell(i).setCellValue(headText[i]);
    }
}

/**
 * 填充内容
 * @param excelSheet
 * @param content
 */
private void setExcelRows(HSSFSheet excelSheet, List<List<String>> content) {
    int index=1;
    for(List<String> list: content){
        HSSFRow excelRow = excelSheet.createRow(index++);
        for(int i=0; i<list.size(); i++){
            excelRow.createCell(i).setCellValue(list.get(i));
        }
    }
}

}

2、使用

/**
* 导出磁盘使用情况
* @param response
* @param request
* @throws Exception
*
*/
@RequestMapping(value = “exportDiscInfo”)
@ResponseBody
public Map exportDiscInfo(HttpServletRequest request, HttpServletResponse response) throws Exception {
Map<String,Object> outMap = new HashMap<>();

    List<Map<String, Object>> resultList = faultStatistics.discInfoList();

    List<List<String>> content = new ArrayList<List<String>>();
    for (Map<String, Object> map : resultList) {
        List<String> row = new ArrayList<String>();

        row.add(String.valueOf(map.get("CREATTIME") == null ? "" : map.get("CREATTIME")));
        row.add(String.valueOf(map.get("DISC") == null ? "" : map.get("DISC")));
        row.add(String.valueOf(map.get("USER_DISC_MEMORY") == null ? "" : map.get("USER_DISC_MEMORY")));
        row.add(String.valueOf(map.get("UNUSER_DISC_MEMORY") == null ? "" : map.get("UNUSER_DISC_MEMORY")));

        content.add(row);
    }
    //为excel的第一行，头部
    String[] headText = { "日期","磁盘名称", "已使用内存（GB）", "未使用内存（GB）"};
    //每一列占的宽度
    int[] style = { 4000, 3000, 3000, 3000};
    //excel的名字
    String excelName = "磁盘使用情况";

    new ExcelExport().writeExcel("磁盘使用情况", excelName + ".xls", style, headText, content, response);
    return null;
}