总结一下,方便以后查看。
说下自己的这个能够把页面表格导出成excel文件的总体印象:
1需要导入jxl包,我的是jxl-2.6.12.jar
2.写一个工具类,传三个参数:OutputStream os,String[] labels,List<String[]> rows;
OutputStream os:要写入的地方,如果是放浏览器下载,os=response.getOutputStream(),如果是一个固定的地方,os=new FileOutputStream(new File("D:/a.excel").
List<String[]> rows:是下面正式的数据,一个String[]数组一行row,数组大小和labels的一样。
方法代码:
public static void createExport(OutputStream os,String[] labels,List<String[]> cells) throws Exception{
WritableWorkbook wwb = Workbook.createWorkbook(os); // 建立excel文件
WritableSheet ws = wwb.createSheet("Sheet1", 10); // 创建一个工作表
WritableFont wf = new WritableFont(WritableFont.ARIAL,10,WritableFont.NO_BOLD,false,//设置单元格的文字格式
UnderlineStyle.NO_UNDERLINE,Colour.BLACK);
WritableCellFormat wcf = new WritableCellFormat(wf);
wcf.setVerticalAlignment(VerticalAlignment.CENTRE);
wcf.setAlignment(Alignment.CENTRE);
ws.setRowView(0, 500);
//设置列头名
for (int j=0;j<labels.length;j++){
ws.addCell(new Label(j, 0, labels[j], wcf));
}
for (int i = 0; i <cells.size();i++){
String[] arys=cells.get(i);
ws.setRowView(i+1, 600);
for (int j=0;j<arys.length;j++){
ws.addCell(new Label(j, i+1,arys[j], wcf));
if(arys[j]!=null && arys[j].length()>0){
ws.setColumnView(j,arys[j].length()+7);
}else{
ws.setColumnView(j,30);
}
}
}
wwb.write();
wwb.close();
}
}
3写一个方法 去生成数据,把String[] labels和String[] rows填满,传给createExport()方法。我就以用户列表的形式为例。
public void exportUsersListToExcel(HttpServletResponse response,StringBuilder sql) {
// TODO Auto-generated method stub
String[] labels={"用户编号","用户名","真实姓名"};
List<String[]> cells=new ArrayList<String[]>();
List<UserProfile> users =this.findUserByNativeSql(sql, -1, -1);//查询方法,自己定义的
SimpleDateFormat format=new SimpleDateFormat("yyyy-MM-dd HH:mm");
try {
for (UserProfile user : users) {
String[] cell=newString[labels.length];
cell[0]=String.valueOf(user.getId());
cell[1]=user.getUserName();
cell[2]=user.getRealName();
cells.add(cell);
}
ExcelExportUtil.createExport(response.getOutputStream(), labels, cells);
} catch (Exception e) {
e.printStackTrace();
}
}
4页面发请求,进入Servlet.我用的是spring mvc,进入Controller,写个方法,调第三步的方法,返回给浏览器,生成数据。
@RequestMapping(value = "/customer_base_manage/exportToExcel", produces = { "text/html;charset=UTF-8" }) public void exportUsersListToExcel( HttpServletResponse response, @RequestParam(value = "condition", defaultValue = "all") String condition, @RequestParam(value = "context", defaultValue = "") String context) { response.reset(); try { response.setHeader("Content-disposition", "p_w_upload;filename=" + new String("用户信息列表".getBytes("GB2312"), "8859_1") + ".xls"); response.setContentType("application/msexcel"); customerService.exportUsersListToExcel(response, getClauseSql(condition, context)); } catch (UnsupportedEncodingException e) { // TODO Auto-generated catch block e.printStackTrace(); } }
转载于:https://blog.51cto.com/javafanpeng/1290220