在我的poi Struts2运用(一)中,很明显看到 ,这样只能操作一般的简单的报表,如果是要导出的Excel的机构很复杂的话,那代码可想而知。
所以在这里我得用到了Excel模板
意味着我们首先将要到处的Excel 模板话,我们的代码只负责 向模板中塞值即可!
首先要说的Struts2的配置文件还是不变,
以我的会员信息导出Excel为例:
<action name="memberToExcel" class="memberToExcelAction" method="memberToExcel">
<result name="success" type="stream">
<param name="contentType">application/vnd.ms-excel</param>
<param name="inputName">excelStream</param>
<param name="contentDisposition">attachment;filename="${downloadFileName}"</param>
<param name="bufferSize">1024</param>
</result>
<result name="error">/comm/error.jsp</result>
</action>
对应的memberToExcelAction.java
我只写关键代码:
private Integer from1Id;//这是我从js 从跳转的action中获取的参数值
private InputStream excelStream; //输入流变量
public String memberToExcel()throws Exception{
Integer id=from1Id;
MemberDto dto=memberService.getMember(id);
AccountDto accountDto = (AccountDto)session.get(Constants.USER_SESSION_KEY);
String operatorName=accountDto.getName();
String filePath=ServletActionContext.getServletContext().getRealPath("/download/登记表.xls"); //获得绝对路径 主要作用是向Service实现类中传入 模板的路径
excelStream=memberToExcelService.memberToExcel(dto,filePath,operatorName);
return SUCCESS;
}
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 InputStream getExcelStream() {
return excelStream;
}
publicvoidsetExcelStream(InputStream excelStream) {
this.excelStream= excelStream;
}
public Integer getFrom1Id() {
return from1Id;
}
public void setFrom1Id(Integer from1Id) {
this.from1Id = from1Id;
}
我的Service实现类MemberToExcelServiceImpl :
public InputStream memberToExcel(MemberDto dto,String filePath,String operatorName) throws ServiceException{
String fileToBeRead =filePath;
InputStream excelStream=null;
File file=new File(fileToBeRead);
if(file.exists()){
try{
// 创建对Excel工作簿文件的引用
InputStream is=new FileInputStream(file);//注意了这里引用了模板
try{//这个捕捉IOException异常
// 创建一个HSSFWorkbook
HSSFWorkbook wb = new HSSFWorkbook(is);
//由HSSFWorkbook获取第一个HSSFSheet
HSSFSheet sheet = wb.getSheetAt(0);
// 由HSSFSheet创建HSSFRow
HSSFRow row = sheet.createRow((short)2);
//填充第一行
HSSFCell cell = row.createCell((short)2);
cell = row.createCell((short)1);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue(dto.getCardNo().toString());
cell = row.createCell((short)4);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue(dto.getEnterDate()==null?"":getDateToString(dto.getEnterDate()));
cell = row.createCell((short)6);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue(getCheckboxContents(dto.getRemind(),Constants.DIC_TYPE_REMINDS));
cell = row.createCell((short)7);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue(getCheckboxContents(dto.getInterest(),Constants.DIC_TYPE_INTERESTS));
//填充第二行
row = sheet.createRow((short)3);
cell = row.createCell((short)1);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
MemberLevel entity=memberLevelDAO.getMemberLevel(dto.getLevelId());
cell.setCellValue(entity.getName().toString());
cell = row.createCell((short)4);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue(dto.getVisitPeriod().toString()+"天");
//填充第三行
row = sheet.createRow((short)5);
cell = row.createCell((short)1);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
DictionaryDto dictionaryDto1=dictionaryDAO.getDictionary(dto.getCustomer().getLicenceType(), Constants.DIC_TYPE_LICENCETYPES);
cell.setCellValue(dictionaryDto1.getCodeName().toString());
cell = row.createCell((short)3);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue(dto.getCustomer().getNextDate()==null?"":getDateToString(dto.getCustomer().getNextDate()));
cell = row.createCell((short)5);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue(dto.getCustomer().getValidatePeriod().toString()+"年度");
//填充第四行
row = sheet.createRow((short)6);
cell = row.createCell((short)1);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue(dto.getCustomer().getName().toString());
cell = row.createCell((short)3);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
DictionaryDto
dictionaryDto2=dictionaryDAO.getDictionary(dto.getCustomer().getLicenceType(), Constants.DIC_TYPE_GENDORS);
cell.setCellValue(dictionaryDto2.getCodeName().toString());
cell = row.createCell((short)5);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue(dto.getCustomer().getBirthday()==null?"":getDateToString(dto.getCustomer().getBirthday()));
//填充第五行
row = sheet.createRow((short)7);
cell = row.createCell((short)1);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue(dto.getCustomer().getMobile().toString());
cell = row.createCell((short)3);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue(dto.getCustomer().getPhone().toString());
cell = row.createCell((short)5);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue(dto.getCustomer().getFax().toString());
。。。。。。。。(代码一样都是填充省略)。
//使用apache的commons-lang.jar产生随机的字符串作为文件名
String fileName=RandomStringUtils.randomAlphanumeric(10);
//生成xls文件名必须要是随机的,确保每个线程访问都产生不同的文件
StringBuffer sb=new StringBuffer(fileName);
final File fileRandom = new File(sb.append(".xls").toString());
try {
OutputStream os=new FileOutputStream(fileRandom);
try {
wb.write(os);
os.close();
} catch (IOException e) {
e.printStackTrace();
}
} catch (FileNotFoundException e) {
e.printStackTrace();
}
try {
excelStream=new FileInputStream(fileRandom);
} catch (FileNotFoundException e) {
e.printStackTrace();
}
}
catch(IOException ex){
ex.printStackTrace();
}
}
catch(FileNotFoundException e) {
e.printStackTrace();
}
}
return excelStream;
}
/**
* deal with checkbox我这里是由于数据库含有复选框的值 格式x,x,x所以我得转化成数组 在遍历
* @param str
* @param constants
* @return String
*/
public String getCheckboxContents(String str,String constants){
String MixContents="";
String array[]=str.split(",");
if(array[0].equals("")==false){
for(int i=0;i<array.length;i++){
Integer codeId=Integer.parseInt(array[i]);
DictionaryDto dto=dictionaryDAO.getDictionary(codeId, constants);
MixContents+=dto.getCodeName()+",";
}
}
return MixContents;
}
/**
* change date to String
* @param date
* @return String
*/
public String getDateToString(Date date) {
SimpleDateFormat sf = new SimpleDateFormat( "yyyy-MM-dd ");
String date2="";
try{
date2= sf.format(date).toString();
}catch(Exception e){
e.printStackTrace();
}
return date2;
}
我的member.jsp
<a href="#" onClick="Redirect(${member.memberId});">
<img src="<%=ctxPath%>/image/admin/upload.gif"/>导出Excel</a>
<div>
<s:form id="form1" name="form1" >
<input type="hidden" name="from1Id" id="from1Id"/>//作用是将要传递的参数放到form的元素中,这样在action可以获取到。
</s:form>
</div>
我的membe.js(负责form跳转到另一个action)
function Redirect(id){
document.form1.from1Id.value=id;//参数
document.form1.action= "memberToExcel.do";
document.form1.submit();