最近忙于实现导出Excel功能，在boss的建议下，决定使用poi导出excel文件
在的我的应用中我导入的poi-<chsdate isrocdate="False" islunardate="False" day="30" month="12" year="1899" w:st="on">2.5.1</chsdate>.jar 存放路径lib下即可。
Poi与Struts2的联合使用 我觉的最重要的是Struts2的文件配置了
下面的导出方法：没有使用Excel模板
我的配置文件是：
<action name="pointDetailsToExcel" class="pointDetailsAction" method="listBillDetailsToExcel">
<!-- Result返回结果为InputSream对象，并处理下载文件的类型—->
<result name="success" type="stream">
<!-- contentType 指定下载文件的文件类型 —— application/vnd.ms-excel --->
<param name="contentType">application/vnd.ms-excel</param>
<!--返回流 excelStream为action中的流变量名称 -->
<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>
<param name="bufferSize">1024</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文件：
<a href="#" onClick="Redirect();">
<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;//返回的是一个输入流
}
}
扫一扫
117
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
