通过ssh-ajax—poi技术实现excel文件的下载功能

--整个流程如下：具体内容根据需要导出的内容确定

页面部分代码如下：

function exportexcel(){
$.ajax({
type:"post",
url:"/admin/activity/extendExportTel.action",
data:{
roadType:roadType,
receiveType:receiveType,
accOpName:accOpName,
validBuName:validBuName,
endDate:endDate,
beginDate:beginDate
},
dataType:"json",
success:function(data){
//alert("8888888888888888");
if(data.excelFlag=="0")
{
waiting = commentWaitTips("读取中...");
var o =setInterval(function(){
$.ajax({
type : "post",
url : "/admin/activity/extendExportFlag.action",
data:{
},
dataType : "text",
success : function(data) {
if(data=="1"){
waiting.close();
clearInterval(o);
var iframe = document.createElement("iframe");
iframe.src = "/admin/activity/extendExportDownload.action";
iframe.style.display = "none";
document.body.appendChild(iframe);
}
}
});
},2000);
}
}
});
}

action的实现内容如下：

//导出手机号文件内容
@org.apache.struts2.convention.annotation.Action(value = "/admin/activity/extendExportTel",results = {
})
public String extendExportTel() {
final HttpSession session = request.getSession(true);
JSONObject excelFlag=new JSONObject();
String flag ="";
Thread thread = new Thread(new Runnable() {
@Override
public void run() {
List<Object[]> orderList=new ArrayList<Object[]>();
StringBuffer sql=new StringBuffer();
HSSFWorkbook wb=null;
ExportOrderExcel exportExcel=new ExportOrderExcel();
try{
orderList=provideRecordService.findBySql(sql.toString());//service层实现查询内容
wb=exportExcel.exportExtendExcel(orderList);//通过poi写入excel格式及内容
session.setAttribute("excelFlag",1);
session.setAttribute("orderResult",wb);
}catch(Exception e)
{
session.setAttribute("excelFlag","2");
session.setAttribute("orderResult",wb);
e.printStackTrace();
}

}
}
);
thread.start();
excelFlag.put("excelFlag","0");
try {
response.getWriter().print(excelFlag);
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return null;
}

@org.apache.struts2.convention.annotation.Action(value = "/admin/activity/extendExportFlag",results = {
})
public String extendExportFlag() throws Exception {
String flag = request.getSession().getAttribute("excelFlag").toString();
if("1".equals(flag)){
request.getSession().removeAttribute("excelFlag");
response.getWriter().print("1");
}
else{
response.getWriter().print("0");
}
return null;
}

@org.apache.struts2.convention.annotation.Action(value = "/admin/activity/extendExportDownload",results = {
})
public String downloadExcel() throws Exception {
try{
response.reset();
response.setContentType("application/octet-stream");
response.setHeader("Content-Disposition","attachment; filename="" + URLEncoder.encode("充值账单.xls","UTF-8") + """);
OutputStream out = new BufferedOutputStream(response.getOutputStream());
HSSFWorkbook wb=null;
wb=(HSSFWorkbook)request.getSession().getAttribute("orderResult");
wb.write(out);
request.getSession().removeAttribute("orderResult");
out.close();
}
catch(Exception e){
e.printStackTrace();
}
return null;
}

--poi实现内容的写出

public HSSFWorkbook exportExtendExcel(List<Object[]> orderList)
{
// TODO Auto-generated method stub
if (orderList.size()==0)
{
Object[] obj={"2012-11-13 17:02:30","222","22222222221","ok"};
orderList.add(obj);
}
HSSFWorkbook wb = null;
// 创建Excel的工作书册 Workbook,对应到一个excel文档
try{
wb = new HSSFWorkbook();
// 创建Excel的工作sheet,对应到一个excel文档的tab
HSSFSheet sheet = wb.createSheet("sheet1");

// 设置excel每列宽度
sheet.setColumnWidth(0,6000);
sheet.setColumnWidth(1,6000);
sheet.setColumnWidth(2,6000);
// // 创建字体样式
HSSFFont font = wb.createFont();
font.setFontName("Verdana");
font.setBoldweight((short) 100);
font.setFontHeight((short) 300);
font.setColor(HSSFColor.BLACK.index);

// 创建单元格样式1
HSSFCellStyle style = wb.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
style.setFillForegroundColor(HSSFColor.ORANGE.index);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style.setFont(font);// 设置字体
// 创建单元格样式2
HSSFCellStyle style1 = wb.createCellStyle();
style1.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
style1.setFillForegroundColor(HSSFColor.TURQUOISE.index);
style1.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style1.setFont(font);
// 创建单元格样式3
HSSFCellStyle style2 = wb.createCellStyle();
style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
style2.setFillForegroundColor(HSSFColor.LIGHT_TURQUOISE.index);
style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style2.setFont(font);
// 创建单元格样式4
HSSFCellStyle style3 = wb.createCellStyle();
style3.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style3.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
// 创建单元格样式5
HSSFCellStyle style4 = wb.createCellStyle();
style4.setDataFormat(HSSFDataFormat.getBuiltinFormat("h:mm:ss"));
style4.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style4.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
HSSFRow row ;
HSSFCell cell;
//头标题1
String HeadOne="222";
row=sheet.createRow(0);
cell=row.createCell(0);
cell.setCellStyle(style);
cell.setCellValue(HeadOne);
sheet.addMergedRegion(new CellRangeAddress(0,2));
//标题2
String HeadTwo="111";
row=sheet.createRow(1);
cell=row.createCell(0);
cell.setCellStyle(style2);
cell.setCellValue(HeadTwo);
sheet.addMergedRegion(new CellRangeAddress(1,1,2));
//标题3
String HeadThree="xxxxx";
row=sheet.createRow(2);
cell=row.createCell(0);
cell.setCellStyle(style1);
cell.setCellValue(HeadThree);
sheet.addMergedRegion(new CellRangeAddress(2,2,2));
//标题4
String HeadFour_01="111";
String HeadFour_02="222";
String HeadFour_03="33";
row=sheet.createRow(3);
cell=row.createCell(0);
cell.setCellStyle(style2);
cell.setCellValue(HeadFour_01);
cell=row.createCell(1);
cell.setCellStyle(style2);
cell.setCellValue(HeadFour_02);
cell=row.createCell(2);
cell.setCellStyle(style2);
cell.setCellValue(HeadFour_03);
//标题5
String HeadFive_01="时间";
String HeadFive_02="卡号/手机号";
String HeadFive_03="数量";
row=sheet.createRow(4);
cell=row.createCell(0);
cell.setCellStyle(style1);
cell.setCellValue(HeadFive_01);
cell=row.createCell(1);
cell.setCellStyle(style1);
cell.setCellValue(HeadFive_02);
cell=row.createCell(2);
cell.setCellStyle(style1);
cell.setCellValue(HeadFive_03);
//对账单内容
int rowFNum=5;
System.out.println(orderList.get(0).length+"==="+orderList.size());
for(int i=0;i<orderList.size();i++)
{
row = sheet.createRow(rowFNum+i);
row.setHeight((short) 500);// 设定行的高度
int j=0;
System.out.println(orderList.get(i)[1].toString()+i+"=="+j);
// // 创建一个Excel的单元格
cell = row.createCell(0);

cell.setCellStyle(style4);
cell.setCellValue(orderList.get(i)[0].toString());
cell = row.createCell(1);
cell.setCellStyle(style3);
cell.setCellValue(orderList.get(i)[1].toString());
cell = row.createCell(2);
cell.setCellStyle(style3);
cell.setCellValue(orderList.get(i)[2].toString());

} // FileOutputStream os; //os = new FileOutputStream("e://workbookTest.xls"); // wb.write(os); // os.close(); return wb; } catch (Exception e) { e.printStackTrace(); return wb; } }

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!