在java web开发中,很多情况下都会用到导出excel数据的功能,下面我对我做的一个例子做下简单的介绍,希望对大家有所帮助。
java导出excel的方式有很多种,如利用poi导出,如利用jxl导出,现在我要说的就是利用jxl方式导出
首先,需要添加jar包:jxl.jar,其他java需要的包自行添加;
然后,请看下面代码:
/**
* excel 文件下载操作
* @param result List数组,需要导出的对象列表
* @param response
* @throws RowsExceededException
* @throws WriteException
* @throws IOException
*/
public static void downloadFile(List result, HttpServletResponse response) throws RowsExceededException, WriteException, IOException{
String xlsFileName = "subapplyinfo.xls";
WritableWorkbook book=
Workbook.createWorkbook(new File(xlsFileName));
//生成名为"第一页"的工作表,参数0表示这是第一页
WritableSheet sheet=book.createSheet("第一页",0);
//设置字体为宋体,11号字,加粗,颜色为黑色
WritableFont font1=new WritableFont(WritableFont.createFont("宋体"),11,WritableFont.BOLD);
font1.setColour(Colour.BLACK);
WritableCellFormat format1=new WritableCellFormat(font1);
format1.setAlignment(jxl.format.Alignment.CENTRE);
format1.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
Label labelA = new Label(0,0,"申请类型",format1);
Label labelB = new Label(1,0,"活动名称",format1);
Label labelC = new Label(2,0,"姓名",format1);
Label labelD = new Label(3,0,"性别",format1);
Label labelE = new Label(4,0,"联系电话",format1);
Label labelF = new Label(5,0,"意向车型",format1);
Label labelG = new Label(6,0,"预约时间",format1);
Label labelH = new Label(7,0,"邮箱地址",format1);
Label labelI = new Label(8,0,"所在城市",format1);
Label labelJ = new Label(9,0,"提交时间",format1);
Label labelK = new Label(10,0,"备注",format1);
//将定义好的单元格添加到工作表中
sheet.addCell(labelA);
sheet.addCell(labelB);
sheet.addCell(labelC);
sheet.addCell(labelD);
sheet.addCell(labelE);
sheet.addCell(labelF);
sheet.addCell(labelG);
sheet.addCell(labelH);
sheet.addCell(labelI);
sheet.addCell(labelJ);
sheet.addCell(labelK);
for (int ii = 0; ii < result.size(); ii++) {
SubApplyInfos vo = (SubApplyInfos)result.get(ii);
Label labelA1 = new Label(0,ii+1,vo.getApplyType());
Label labelB1 = new Label(1,ii+1,vo.getApplyTitle());
Label labelC1 = new Label(2,ii+1,vo.getApplyName());
Label labelD1 = new Label(3,ii+1,vo.getApplySex());
Label labelE1 = new Label(4,ii+1,vo.getApplyTel());
Label labelF1 = new Label(5,ii+1,vo.getApplyCar());
Label labelG1 = new Label(6,ii+1,vo.getApplyTime());
Label labelH1 = new Label(7,ii+1,vo.getApplyEmail());
Label labelI1 = new Label(8,ii+1,vo.getApplyCity());
SimpleDateFormat f1 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
Label labelJ1 = null;
if(vo.getCreateTime() != null && !"".equals(vo.getCreateTime())){
labelJ1 = new Label(9,ii+1,f1.format(vo.getCreateTime()));
}else{
labelJ1 = new Label(9,ii+1,"");
}
Label labelK1 = new Label(10,ii+1,vo.getApplyRemark());
sheet.addCell(labelA1);
sheet.addCell(labelB1);
sheet.addCell(labelC1);
sheet.addCell(labelD1);
sheet.addCell(labelE1);
sheet.addCell(labelF1);
sheet.addCell(labelG1);
sheet.addCell(labelH1);
sheet.addCell(labelI1);
sheet.addCell(labelJ1);
sheet.addCell(labelK1);
}
//写入数据并关闭文件
book.write();
book.close();
System.out.println("创建文件成功!");
OutputStream outputS = response.getOutputStream();
response.reset();
// response.setContentType("application/octet-stream; charset=GB2312");
response.setContentType("application/vnd.ms-excel; charset=GB2312");
response.setHeader("Content-Disposition", "attachment;filename=\""
+ xlsFileName.substring(xlsFileName.lastIndexOf("/") + 1)
+ "\"");
InputStream in = new FileInputStream(new File(xlsFileName));
if (in != null) {
byte[] b = new byte[2048];
int len = 0;
while ((len = in.read(b)) > 0) {
outputS.write(b, 0, len); //
outputS.flush();
}
outputS.close();
in.close();
}
}
代码中便是导出excel数据的具体操作方法,其原理便是:先将数据存储到临时excel文件中,然后再将该excel文件以文件下载的方式下载到客户端,从而实现了导出excel数据的功能。
注意,这段代码并不是通用,如果有需要实现该功能的朋友,请复制后自行修改,当然,有心的朋友可以将它修改成通用方法,相信也不是件困难的事情。
******************************************************************************************************************************************************************************************************************************************
上面的那种方式,需要先把数据放到一个excel文件中,然后再利用下载的方式将excel文件下载下来,下面是一种更直接的方式,可以将数据直接通过io流输出到excel文件中
public ActionForward exportExcel(ActionMapping mapping, ActionForm form,
HttpServletRequest request, HttpServletResponse response){
try {
// 取得输出流
OutputStream os = response.getOutputStream();
// 清空输出流
response.reset();
//设定输出文件头
response.setHeader("Content-disposition", "attachment; filename=" + path);
// 定义输出类型
response.setContentType("application/msexcel");
if (result.size() > 0) {
// 调用生成excel文件
writeExcel2(os , result);
}
os.close();
} catch (Exception e) {
log.error(e.getMessage(), e);
}
}
下面的代码,跟一开始的代码基本相同,没有多大差别
public void writeExcel2(InputStream os , List result){
WritableWorkbook book=
Workbook.createWorkbook(os);
//生成名为"第一页"的工作表,参数0表示这是第一页
WritableSheet sheet=book.createSheet("第一页",0);
//设置字体为宋体,11号字,加粗,颜色为黑色
WritableFont font1=new WritableFont(WritableFont.createFont("宋体"),11,WritableFont.BOLD);
font1.setColour(Colour.BLACK);
WritableCellFormat format1=new WritableCellFormat(font1);
format1.setAlignment(jxl.format.Alignment.CENTRE);
format1.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
Label labelA = new Label(0,0,"申请类型",format1);
Label labelB = new Label(1,0,"活动名称",format1);
Label labelC = new Label(2,0,"姓名",format1);
Label labelD = new Label(3,0,"性别",format1);
Label labelE = new Label(4,0,"联系电话",format1);
Label labelF = new Label(5,0,"意向车型",format1);
Label labelG = new Label(6,0,"预约时间",format1);
Label labelH = new Label(7,0,"邮箱地址",format1);
Label labelI = new Label(8,0,"所在城市",format1);
Label labelJ = new Label(9,0,"提交时间",format1);
Label labelK = new Label(10,0,"备注",format1);
//将定义好的单元格添加到工作表中
sheet.addCell(labelA);
sheet.addCell(labelB);
sheet.addCell(labelC);
sheet.addCell(labelD);
sheet.addCell(labelE);
sheet.addCell(labelF);
sheet.addCell(labelG);
sheet.addCell(labelH);
sheet.addCell(labelI);
sheet.addCell(labelJ);
sheet.addCell(labelK);
for (int ii = 0; ii < result.size(); ii++) {
SubApplyInfos vo = (SubApplyInfos)result.get(ii);
Label labelA1 = new Label(0,ii+1,vo.getApplyType());
Label labelB1 = new Label(1,ii+1,vo.getApplyTitle());
Label labelC1 = new Label(2,ii+1,vo.getApplyName());
Label labelD1 = new Label(3,ii+1,vo.getApplySex());
Label labelE1 = new Label(4,ii+1,vo.getApplyTel());
Label labelF1 = new Label(5,ii+1,vo.getApplyCar());
Label labelG1 = new Label(6,ii+1,vo.getApplyTime());
Label labelH1 = new Label(7,ii+1,vo.getApplyEmail());
Label labelI1 = new Label(8,ii+1,vo.getApplyCity());
SimpleDateFormat f1 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
Label labelJ1 = null;
if(vo.getCreateTime() != null && !"".equals(vo.getCreateTime())){
labelJ1 = new Label(9,ii+1,f1.format(vo.getCreateTime()));
}else{
labelJ1 = new Label(9,ii+1,"");
}
Label labelK1 = new Label(10,ii+1,vo.getApplyRemark());
sheet.addCell(labelA1);
sheet.addCell(labelB1);
sheet.addCell(labelC1);
sheet.addCell(labelD1);
sheet.addCell(labelE1);
sheet.addCell(labelF1);
sheet.addCell(labelG1);
sheet.addCell(labelH1);
sheet.addCell(labelI1);
sheet.addCell(labelJ1);
sheet.addCell(labelK1);
}
//写入数据并关闭文件
book.write();
book.close();
System.out.println("创建文件成功!");
os.close();
}