最近写的一个小程序:
需求:遍历数据库的数据,然后生成Excel表 ,导出数据(利用jxl.jar包)
有兴趣的同学可以参考一下(其他同事介绍使用poi.jar,但没试过,听说比jxl.jar好)
生成excel表
@RequestMapping(value="/xixi/xuan/createExcel",method={RequestMethod.GET,RequestMethod.POST})
public String createExcel(HttpServletRequest request,HttpServletResponse response) throws IOException, RowsExceededException, WriteException{
OutputStream os = response.getOutputStream();
ParamUtil pu = new ParamUtil(request);
String qid=pu.getString("qid");
String fileName="VisitorData";
response.setContentType("application/vnd.ms-excel");
response.addHeader("Content-Disposition", "attachment;filename=\"" + fileName + ".xls" + "\"");
List<QuserInfoRecord> list=quserInfoRecordService.findQuserInfoRecord(qid);
if(list.size()>0){
/* String excelPath = "D:\\data.xls";
File excelFile = new File(excelPath);
if (!excelFile.exists()) {
excelFile.createNewFile();
}*/
System.out.println("=====kaisi");
///打开文件
WritableWorkbook book = Workbook.createWorkbook(os);
///生成一个名为"用户信息"的工作表
WritableSheet ws= book.createSheet(fileName, 0); //新建一个sheet
//在Label对象的构造子中指名单元格位置是第一列第一行(0,0)
//以及单元格内容为test
//Label label=new Label(0,0,"test");
//往表中添加内容
//将定义好的单元格添加到工作表中
JSONArray jsonArray=JSONArray.fromObject(list.get(0).getExt());
int lenght=jsonArray.size();
WritableCellFormat wcf = null;
//字体样式
WritableFont wf = new WritableFont(WritableFont.TIMES,12, WritableFont.BOLD,false);//最后一个为是否italic
wf.setColour(Colour.BLACK);
wcf = new WritableCellFormat(wf);
//对齐方式
wcf.setAlignment(Alignment.CENTRE);
wcf.setVerticalAlignment(VerticalAlignment.CENTRE);
//边框
wcf.setBorder(Border.ALL,BorderLineStyle.THIN);
for(int i=0;i<list.size();i++){
JSONArray jsonlist=JSONArray.fromObject(list.get(i).getExt());
Label label11=new Label(0, 0, "日期",wcf);
ws.setColumnView(0,20);
ws.addCell(label11);
Label label1=new Label(0, i+1, list.get(i).getDate());
ws.addCell(label1);
Label label12=new Label(1, 0, "来源标题",wcf);
ws.setColumnView(1,30);
ws.addCell(label12);
Label label2=new Label(1, i+1, list.get(i).getSourcetitle());
ws.addCell(label2);
Label label13=new Label(2, 0, "页面链接",wcf);
ws.setColumnView(2,50);
ws.addCell(label13);
Label label3=new Label(2, i+1, list.get(i).getUserhref());
ws.addCell(label3);
Label label14=new Label(3, 0, "IP来源",wcf);
ws.setColumnView(3,20);
ws.addCell(label14);
Label label4=new Label(3, i+1, list.get(i).getIp());
ws.addCell(label4);
for(int j=0; j<lenght; j++){
JSONObject jsonObject=JSONObject.fromObject(jsonlist.getString(j));
Label label15=new Label(j+4, 0, jsonObject.getString("name"),wcf);
ws.setColumnView(j+4,20);
ws.addCell(label15);
Label label=new Label(j+4, i+1, jsonObject.getString("value"));
ws.addCell(label);
}
}
//写入数据并关闭文件
//将数据写入所建的excel
book.write();
book.close();
os.close();
response.flushBuffer();
}
return null;
}