POI 导出excel

1.jar包之类的步骤省略。（框架ssh）

2.导出工具类。

package com.iotlab.fw.util;


import java.io.ByteArrayOutputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;


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 com.iotlab.fw.bean.PageInfo;
/**
 * excel导出工具类
 * @author maolina
 *
 */
public class ExcelDownload {


public static byte[] download(String fileName,String[] oneColumn,String[] oneColumnData,PageInfo pageInfo) throws Exception{

ByteArrayOutputStream out = new ByteArrayOutputStream();
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet();
List<String []> listData = getExcelDataList(pageInfo.getSearchResult(),oneColumn,oneColumnData);
for (int i = 0; i < listData.get(0).length; i++) {// 设置列宽
sheet.setColumnWidth((short) i, (short) 5500);
}
/** ——————————BEGIN 动态数据———————————— */
if(null != listData && listData.size() > 0 ){
for (int i = 0; i < listData.size(); i++)
{
//获取封装的每一行记录
String[] strArr = listData.get(i);
//设置单元格对齐方式
//居左
HSSFCellStyle cellStyle_left = workbook.createCellStyle();
cellStyle_left.setAlignment(HSSFCellStyle.ALIGN_LEFT); // 指定单元格居中对齐
cellStyle_left.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 指定单元格垂直居中对齐
cellStyle_left.setWrapText(true);// 指定单元格自动换行
cellStyle_left.setBorderBottom(HSSFCellStyle.BORDER_THIN);
cellStyle_left.setBorderLeft(HSSFCellStyle.BORDER_THIN);
cellStyle_left.setBorderRight(HSSFCellStyle.BORDER_THIN);
cellStyle_left.setBorderTop(HSSFCellStyle.BORDER_THIN);
//居中
HSSFCellStyle cellStyle_center = workbook.createCellStyle();
cellStyle_center.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 指定单元格居中对齐
cellStyle_center.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 指定单元格垂直居中对齐
cellStyle_center.setWrapText(true);// 指定单元格自动换行
cellStyle_center.setBorderBottom(HSSFCellStyle.BORDER_THIN);
cellStyle_center.setBorderLeft(HSSFCellStyle.BORDER_THIN);
cellStyle_center.setBorderRight(HSSFCellStyle.BORDER_THIN);
cellStyle_center.setBorderTop(HSSFCellStyle.BORDER_THIN);
//居右
HSSFCellStyle cellStyle_right = workbook.createCellStyle();
cellStyle_right.setAlignment(HSSFCellStyle.ALIGN_RIGHT); // 指定单元格居中对齐
cellStyle_right.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 指定单元格垂直居中对齐
cellStyle_right.setWrapText(true);// 指定单元格自动换行
cellStyle_right.setBorderBottom(HSSFCellStyle.BORDER_THIN);
cellStyle_right.setBorderLeft(HSSFCellStyle.BORDER_THIN);
cellStyle_right.setBorderRight(HSSFCellStyle.BORDER_THIN);
cellStyle_right.setBorderTop(HSSFCellStyle.BORDER_THIN);
//设置标题字体
HSSFFont font = workbook.createFont();
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
font.setFontName("宋体");
font.setFontHeight((short) 250);
cellStyle_center.setFont(font);
if(strArr != null && strArr.length > 0 ){
HSSFRow row = sheet.createRow(i);
//设置行高
row.setHeight((short)400);
for (int j = 0; j < listData.get(0).length; j++)
    {
    HSSFCell cell = row.createCell((short) j);
    sheet.getRow(i).getCell((short)j).setEncoding(HSSFCell.ENCODING_UTF_16);//设置单元格编码方式
    cell.setCellValue(strArr[j]);
    if(i<1){
    cell.setCellStyle(cellStyle_center);
    }else{
    cell.setCellStyle(cellStyle_right);
    }
    }
}
}
}
/** ——————————END 动态数据———————————— */


workbook.write(out);
/** 将excel写入字节输出流...end **/
return out.toByteArray();

}


/**
* 获取导出excel的数据
* @param data
* @param head
* @param size
* @return
*/
public static List<String[]> getExcelDataList(List<Map<String,Object>> data,String[] oneColumn,String[] oneColumnData){
List<String[]> resultList = new ArrayList<String[]>();
int columnSize = oneColumn.length;
//第一行标题：
resultList.add(oneColumn);
//第二行：数据
if(data!=null && data.size() > 0){
for(int i=0;i<data.size();i++){
String[] dataString = new String[columnSize];
Map o = (Map)data.get(i);
for(int j = 0;j< dataString.length;j++){
dataString[j] = (String)o.get(oneColumnData[j]);
}
resultList.add(dataString);
}
}
return resultList;
}


}

3.action

/**
* 导出excel
*/
public String downloadDataByExcel() throws Exception {
String fileName = "品名管理";
setInitData(fileName);
String[] oneColumn = { "编码", "名称", "英文名称", "排序号", "状态" };
String[] oneColumnData = { "CODE", "NAME", "EN_NAME", "ORDER_NUM", "CARGO_STATUS_NAME" };
super.pageInfo = new PageInfo(100);
super.execute();
inputStream = new ByteArrayInputStream(ExcelDownload.download(fileName, oneColumn, oneColumnData, pageInfo));
return "downloadExcel";
}

4.

/**
* @Fields downFileName: 文件名
*/
protected String downFileName;
/**
* @Fields inputStream: 输入流
*/
protected InputStream inputStream;

public void setInitData(String fileName) throws IOException {
try {
// response.setHeader("Content-Disposition","attachment;filename="+fileName);
setDownFileName(URLEncoder.encode(fileName, "UTF8") + "-"
+ new SimpleDateFormat("yyyyMMddHHmmss").format(new Date()) + ".xls");
setFileType("application/vnd.ms-excel");// 文件类型
super.encapType = QueryService.MAP_ENCAPSULATION;
// OutputStream os=response.getOutputStream();
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
}
}

5.配置文件

<action name="cargoExcel_*" class="cargoExcelAction" method="{1}">
<result name="downloadExcel" type="stream">
<param name="contentType">${fileType}</param>
<param name="contentDisposition">attachment;filename=${downFileName}</param>
<param name="inputName">inputStream</param>
<param name="bufferSize">4096</param>
</result>
</action>