java 导出 Excel 表格
import jxl.Workbook;
import jxl.write.*; //导入的两个包
<dependency>
<groupId>net.sourceforge.jexcelapi</groupId>
<artifactId>jxl</artifactId>
<version>2.6.12</version>
</dependency>
//导出excel @Override public void getExportExcel(HttpServletRequest request, HttpServletResponse response, SearchParams params) throws Exception { List<BillOtherAccount> list = findByCondition(params); //查询到数据对象封装到集合中 if(list.size()>0){ //开始excel导出 String fname = "第三方对账查询"; //表名 OutputStream os = response.getOutputStream();//取得输出流 response.reset();//清空输出流 //下面是对中文文件名的处理 response.setCharacterEncoding("UTF-8");//设置相应内容的编码格式 //fname = java.net.URLEncoder.encode(fname,"UTF-8"); //response.setHeader("Content-Disposition","attachment;filename="+new String(fname.getBytes("UTF-8"),"GBK")+".xls"); response.setHeader("Content-Disposition", "attachment;filename=" + new String(fname.getBytes(),"iso-8859-1") + ".xls"); response.setContentType("application/msexcel");//定义输出类型 //创建工作薄 WritableWorkbook workbook = Workbook.createWorkbook(os); //创建新的一页 WritableSheet sheet = workbook.createSheet("First Sheet",0); //设置字体种类和格式 WritableFont bold = new WritableFont(WritableFont.createFont("宋体"), 12, WritableFont.NO_BOLD); WritableCellFormat wcfFormat = new WritableCellFormat(bold); //wcfFormat.setAlignment(jxl.format.Alignment.CENTRE);//单元格中的内容水平 //设置第3列单元格宽 //sheet.setColumnView(4, 50); //设置第三行单元格高 //sheet.setRowView(4, 50); //创建要显示的内容,创建一个单元格,第一个参数为列坐标,第二个参数为行坐标,第三个参数为内容 /* //创建表头 Label billIdColumn = new Label(0,0,"id",wcfFormat); sheet.addCell(billIdColumn); //设置某个单元格宽 sheet.setColumnView(0, 20);*/ Label billTimeColumn = new Label(0,0,"订单日期",wcfFormat); sheet.addCell(billTimeColumn); //设置某个单元格宽 sheet.setColumnView(0, 25); Label serialNumberColumn = new Label(1,0,"支付平台流水号",wcfFormat); sheet.addCell(serialNumberColumn); //设置某个单元格宽 sheet.setColumnView(1, 40); Label otherSerialNumberColumn = new Label(2,0,"第三方流水号",wcfFormat); sheet.addCell(otherSerialNumberColumn); //设置某个单元格宽 sheet.setColumnView(2, 25); Label appIdColumn = new Label(3,0,"商户ID ",wcfFormat); sheet.addCell(appIdColumn); //设置某个单元格宽 sheet.setColumnView(3, 20); Label orderTypeColumn = new Label(4,0,"订单类型",wcfFormat); sheet.addCell(orderTypeColumn); //设置某个单元格宽 sheet.setColumnView(4, 20); Label orderMoneyColumn = new Label(5,0,"订单金额",wcfFormat); sheet.addCell(orderMoneyColumn); //设置某个单元格宽 sheet.setColumnView(5, 20); Label ext2Column = new Label(6,0,"支付方式",wcfFormat); sheet.addCell(ext2Column); //设置某个单元格宽 sheet.setColumnView(6, 20); Label refundColumn = new Label(7,0,"退款金额",wcfFormat); sheet.addCell(refundColumn); //设置某个单元格宽 sheet.setColumnView(7, 20); Label orderStateColumn = new Label(8,0,"账单状态",wcfFormat); sheet.addCell(orderStateColumn); //设置某个单元格宽 sheet.setColumnView(8, 20); Label ext1Column = new Label(9,0,"对账状态",wcfFormat); sheet.addCell(ext1Column); //设置某个单元格宽 sheet.setColumnView(9, 20); Label ext3Column = new Label(10,0,"差账原因",wcfFormat); sheet.addCell(ext3Column); //设置某个单元格宽 sheet.setColumnView(10, 20); //加载每行数据 for (int i = 1; i <= list.size(); i++) { /* Label billId = new Label(0,i,list.get(i-1).getBillId(),wcfFormat); sheet.addCell(billId);*/ Date besDate = list.get(i-1).getBillTime(); if(besDate !=null){ Label bespeakDate = new Label(0,i,formatDate(besDate),wcfFormat); sheet.addCell(bespeakDate); } Label serialNumber = new Label(1,i,list.get(i-1).getSerialNumber(),wcfFormat); sheet.addCell(serialNumber); Label OtherSerialNumber = new Label(2,i,list.get(i-1).getOtherSerialNumber(),wcfFormat); sheet.addCell(OtherSerialNumber); Label appId = new Label(3,i,list.get(i-1).getAppId(),wcfFormat); sheet.addCell(appId); Label orderType = new Label(4,i,list.get(i-1).getOrderType(),wcfFormat); sheet.addCell(orderType); Label orderMoney = new Label(5,i,list.get(i-1).getOrderMoney(),wcfFormat); sheet.addCell(orderMoney); Label ext2 = new Label(6,i,list.get(i-1).getExt2(),wcfFormat); sheet.addCell(ext2); Label refund = new Label(7,i,list.get(i-1).getRefund(),wcfFormat); sheet.addCell(refund); Label orderState = new Label(8,i,list.get(i-1).getOrderState(),wcfFormat); sheet.addCell(orderState); String ext1 = list.get(i-1).getExt1(); String checkState = ""; if (ext1 == null){ ext1 = ""; } if (ext1.equals("1")){ checkState = "异常账单"; }else { checkState = "正常账单"; } Label label = new Label(9, i, checkState, wcfFormat); sheet.addCell(label); Label ext3 = new Label(10,i,list.get(i-1).getExt3(),wcfFormat); sheet.addCell(ext3); } //把创建的内容写入到输出流中,并关闭输出流 workbook.write(); workbook.close(); os.close(); } } public String formatDate(Date date){ SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); String dateStr = sdf.format(date); return dateStr; } public String formatHourAndMinute(Date date){ SimpleDateFormat sdf = new SimpleDateFormat("HH:mm"); String dateStr = sdf.format(date); return dateStr; }
只提供例了核心业务层代码,需要有对应的实体类对象