poi ,Struts2 导出Excel运用（一）

来源：互联网发布：重庆市网络问政平台编辑：程序博客网时间：2024/04/20 07:46

最近忙于实现导出Excel功能，在boss的建议下，决定使用poi导出excel文件

在的我的应用中我导入的poi-2.5.1.jar 存放路径lib下即可。

Poi与Struts2的联合使用我觉的最重要的是Struts2的文件配置了

下面的导出方法：没有使用Excel模板

我的配置文件是：

<!-- Result返回结果为InputSream对象，并处理下载文件的类型—->

<param name="contentType">application/vnd.ms-excel</param>

<param name="inputName">excelStream</param>

<!-- attachment 这个位置的参数挺特殊的，可以设置成下载时，是否出现个下载提示框，或者直接下载之类的，怎么设置忘了，那个小本子找不到了 filename指定生成的文件名字(这种方式合适动态生成文件名，比如做报表时，一般都要说是几月的统计数据之类)为action中变量

${downloadFileName} 可以处理中文问题详见Blog http://www.360doc.com/content/09/1122/22/144699_9566346.shtml -->

<param name="contentDisposition">attachment;filename="${downloadFileName}"

</param>

</result>

<result name="error">/comm/error.jsp</result>

</action>

整个配置文件中最重要的两个变量就是 excelStream、downloadFileName，

inputName 流对象名 —— 比如这里写excelStream，它就会自动去找Action中的getInputStream方法。contentDisposition 使用经过转码的文件名作为下载文件名 —— 默认格式是attachment;filename="${ downloadFileName }"，将调用该Action中的ge tDownloadFileName方法。

这两个变量一定要在action中有定义，否则会报错

我的jsp文件：

<img src='<%=ctxPath%>/image/admin/btn_search.gif'/>导出Excel</a>

对应的js文件：

function Redirect(){

document.mainForm.action= "pointDetailsToExcel.do";

document.mainForm.submit();

}

对应的action.java文件：

private InputStream excelStream; //输入流变量

private String downloadFileName;

public InputStream getExcelStream() {

return excelStream;

}

public void setExcelStream(InputStream excelStream) {

this.excelStream = excelStream;

}

//处理下载时文件名是中文的方法：

public String getDownloadFileName() {

SimpleDateFormat sf = new SimpleDateFormat( "yyyy-MM-dd ");

String downloadFileName= (sf.format(new Date()).toString())+"会员消费明细统计.xls";

try{

downloadFileName=new String(downloadFileName.getBytes(),"ISO8859-1");

}catch(UnsupportedEncodingException e){

e.printStackTrace();

}

return downloadFileName;

}

public void setDownloadFileName(String downloadFileName) {

this.downloadFileName = downloadFileName;

}

//action中的方法

public String listBillDetailsToExcel() throws Exception {

try{

page = new Page();

page.setPageSize(10000);

page.setStartIndex(0);

AccountDto accountDto = (AccountDto)session.get(Constants.USER_SESSION_KEY);

orgs = organizationService.getOrganizationTree(accountDto.getOrgCode());

if (orgId==0)

{

orgId=accountDto.getOrgId();

}

page = pointDetailsService.getPointDetailsPage(page, orgId,Begin,End);

list=page.getResult();//生成的list

excelStream=pointDetailsToExcelService.exportPointDetails(list);

return SUCCESS;

}catch (ServiceException e) {

e.printStackTrace();

return ERROR;

}

对应的Service实现类

package com.dbw.mps.service.impl;

import com.dbw.core.exception.ServiceException;

import com.dbw.core.service.impl.ServiceImpl;

import com.dbw.mps.dto.PointBillDto;

import java.io.File;

import java.io.FileInputStream;

import java.io.FileNotFoundException;

import java.io.FileOutputStream;

import java.io.IOException;

import java.io.InputStream;

import java.io.OutputStream;

import java.util.List;

import org.apache.commons.lang.RandomStringUtils;

import org.apache.poi.hssf.usermodel.HSSFCell;

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

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

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

import com.dbw.mps.service.IPointDetailsToExcelService;

public class PointDetailsToExcelServiceImpl extends ServiceImpl

implements IPointDetailsToExcelService {

public InputStream exportPointDetails(List list) throws ServiceException{

// 创建一个HSSFWorkbook

HSSFWorkbook wb = new HSSFWorkbook();

// 由HSSFWorkbook创建一个HSSFSheet

HSSFSheet sheet = wb.createSheet();

// 由HSSFSheet创建HSSFRow

HSSFRow row = sheet.createRow((short)0);

HSSFCell cell = row.createCell((short) 0);

cell.setEncoding(HSSFCell.ENCODING_UTF_16);

cell.setCellValue("消费日期");

cell = row.createCell((short) 1);

cell.setEncoding(HSSFCell.ENCODING_UTF_16);

cell.setCellValue("标题2");

cell = row.createCell((short) 2);

cell.setEncoding(HSSFCell.ENCODING_UTF_16);

cell.setCellValue("标题3");

cell = row.createCell((short) 3);

cell.setEncoding(HSSFCell.ENCODING_UTF_16);

cell.setCellValue("标题4");

cell = row.createCell((short) 4);

cell.setEncoding(HSSFCell.ENCODING_UTF_16);

cell.setCellValue("标题5");

cell = row.createCell((short) 5);

cell.setEncoding(HSSFCell.ENCODING_UTF_16);

cell.setCellValue("标题6");

cell = row.createCell((short) 6);

cell.setEncoding(HSSFCell.ENCODING_UTF_16);

cell.setCellValue("标题7");

cell = row.createCell((short) 7);

cell.setEncoding(HSSFCell.ENCODING_UTF_16);

cell.setCellValue("标题8");

cell = row.createCell((short) 8);

cell.setEncoding(HSSFCell.ENCODING_UTF_16);

cell.setCellValue("标题9");

cell = row.createCell((short) 9);

cell.setEncoding(HSSFCell.ENCODING_UTF_16);

cell.setCellValue("标题10");

cell = row.createCell((short) 10);

cell.setEncoding(HSSFCell.ENCODING_UTF_16);

cell.setCellValue("标题11");

cell = row.createCell((short) 11);

cell.setEncoding(HSSFCell.ENCODING_UTF_16);

cell.setCellValue("标题12");//上面是导出Excel的表头

///下面的是根据list 进行遍历循环想下面的单元格塞值（这篇笔记之前发表不了，后来发现是我的表头汉字有敏感字，故改成标题）

for (int i = 1; i < list.size() + 1; i++) {

PointBillDto dto =(PointBillDto) list.get(i-1);;

row = sheet.getRow(i);

if(row == null)

row = sheet.createRow((short)i);

cell = row.getCell((short)0);

if(cell == null)

cell = row.createCell((short)0);

cell.setEncoding(HSSFCell.ENCODING_UTF_16);

cell.setCellValue(dto.getAddTime()== null?"":dto.getAddTime().toString());

cell = row.getCell((short)1);

if(cell == null)

cell = row.createCell((short)1);

cell.setEncoding(HSSFCell.ENCODING_UTF_16);

cell.setCellValue((String)dto.getOperatorName());

cell = row.getCell((short)2);

if(cell == null)

cell = row.createCell((short)2);

cell.setEncoding(HSSFCell.ENCODING_UTF_16);

cell.setCellValue((String)dto.getBillNo());

cell = row.getCell((short)3);

if(cell == null)

cell = row.createCell((short)3);

cell.setEncoding(HSSFCell.ENCODING_UTF_16);

cell.setCellValue((String)dto.getCardNo());

cell = row.getCell((short)4);

if(cell == null)

cell = row.createCell((short)4);

cell.setEncoding(HSSFCell.ENCODING_UTF_16);

cell.setCellValue((String)dto.getCustomerName());

cell = row.getCell((short)5);

if(cell == null)

cell = row.createCell((short)5);

cell.setEncoding(HSSFCell.ENCODING_UTF_16);

cell.setCellValue((String)dto.getCarNo());

cell = row.getCell((short)6);

if(cell == null)

cell = row.createCell((short)6);

cell.setEncoding(HSSFCell.ENCODING_UTF_16);

cell.setCellValue(dto.getCashValue().toString());

cell = row.getCell((short)7);

if(cell == null)

cell = row.createCell((short)7);

cell.setEncoding(HSSFCell.ENCODING_UTF_16);

cell.setCellValue((Integer)dto.getPointValue());

cell = row.getCell((short)8);

if(cell == null)

cell = row.createCell((short)8);

cell.setEncoding(HSSFCell.ENCODING_UTF_16);

cell.setCellValue((Integer)dto.getGainedPoint());

cell = row.getCell((short)9);

if(cell == null)

cell = row.createCell((short)9);

cell.setEncoding(HSSFCell.ENCODING_UTF_16);

cell.setCellValue(dto.getTotalValue().toString());

cell = row.getCell((short)10);

if(cell == null)

cell = row.createCell((short)10);

cell.setEncoding(HSSFCell.ENCODING_UTF_16);

cell.setCellValue((Integer)dto.getRemainPoint());

cell = row.getCell((short)11);

if(cell == null)

cell = row.createCell((short)11);

cell.setEncoding(HSSFCell.ENCODING_UTF_16);

cell.setCellValue((Integer)dto.getAccumulatePoint());

}

//使用apache的commons-lang.jar产生随机的字符串作为文件名

String fileName=RandomStringUtils.randomAlphanumeric(10);

//生成xls文件名必须要是随机的，确保每个线程访问都产生不同的文件

StringBuffer sb=new StringBuffer(fileName);

final File file = new File(sb.append(".xls").toString());

try {

OutputStream os=new FileOutputStream(file);

try {

wb.write(os);

os.close();

} catch (IOException e) {

}

} catch (FileNotFoundException e) {

e.printStackTrace();

}

InputStream is=null;

try {

is=new FileInputStream(file);

} catch (FileNotFoundException e) {

e.printStackTrace();

}

return is;//返回的是一个输入流

}