private void exportAssetExcel(HttpServletRequest request, HttpServletResponse response) throws IOException { String ogid = RequestUtil.getRequestBytes(request, "ogid").trim(); String objectgroupname = RequestUtil.getRequestBytes(request, "objectgroupname").trim(); String excelTitle = new String(objectgroupname.getBytes("ISO-8859-1"), "utf-8"); if(objectgroupname==null || objectgroupname==""){ objectgroupname = "备播单"; } ObjectGroupRObjectMgr ogroMgr = new ObjectGroupRObjectMgr(); BaseDAO basedao = ogroMgr.getBasedao(); String strCondition= "select " + " ogro.new_assetname," + " a.assetname," + " m.filepath," + " a.programtype," + " a.videotype," + " a.duration," + " ogro.asset_price," + " ogro.online_time," + " ogro.asset_path" + " from " + " Objectgrouprobject ogro," + " movie m," + " asset a " + " where " + " ogro.objectid = a.assetid" + " and a.assetid = m.objectguid" + " and ogro.ogid = ?" + " order by ogro.ordernum asc"; List<Comparable> paramlist = new ArrayList(); paramlist.add(ogid); // 关联objectgrouprobject asset查询 List<Object[]> list = basedao.SQLQuery(strCondition, paramlist); //创建一个workbook 对应一个excel文件 HSSFWorkbook book = new HSSFWorkbook(); //创建工作簿，对应一个sheet HSSFSheet sheet = book.createSheet(excelTitle); sheet.setColumnWidth((short)0,(short)1000); sheet.setColumnWidth((short)1,(short)4000); sheet.setColumnWidth((short)2,(short)4000); sheet.setColumnWidth((short)3,(short)10000); sheet.setColumnWidth((short)4,(short)4000); sheet.setColumnWidth((short)5,(short)4000); sheet.setColumnWidth((short)6,(short)4000); sheet.setColumnWidth((short)7,(short)2000); sheet.setColumnWidth((short)8,(short)4000); sheet.setColumnWidth((short)9,(short)10000); sheet.setColumnWidth((short)10,(short)10000); //设置格式 HSSFCellStyle style_1 = book.createCellStyle(); style_1.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFFont f = book.createFont(); f.setFontHeightInPoints((short) 14);//字号 f.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);//加粗 style_1.setFont(f); //创建第一行 合并单元格 HSSFRow firstRow = sheet.createRow(0); sheet.addMergedRegion(new Region(0, (short)0, 0, (short)10)); //组织第一行的数据 HSSFCell cell = firstRow.createCell((short) 0); cell.setCellValue(excelTitle); cell.setCellStyle(style_1); // cell.setCellType(HSSFCell.ENCODING_UTF_16); //创建第二行 合并单元格 HSSFRow secondeRow = sheet.createRow(1); sheet.addMergedRegion(new Region(1, (short)0, 1, (short)8)); //组织第二行的数据 Calendar c = Calendar.getInstance(); int year = c.get(Calendar.YEAR); int month = c.get(Calendar.MONTH); int day = c.get(Calendar.DAY_OF_MONTH); String calendarStr = year + "年" + month + "月" + day + "日"; cell = secondeRow.createCell((short)0); cell.setCellValue("制表时间: "+ calendarStr); cell = secondeRow.createCell((short)9); cell.setCellValue("制表部门：内容（集成）中心"); cell = secondeRow.createCell((short)10); cell.setCellValue("节目单类型：上片单"); //组织第三行数据 HSSFRow thirdRow = sheet.createRow(2); HSSFCellStyle style_2 = book.createCellStyle(); style_2.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFFont f2 = book.createFont(); f2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);//加粗 style_2.setFont(f2); HSSFCell third_cell = thirdRow.createCell((short)0); third_cell = thirdRow.createCell((short)1); third_cell.setCellValue("节目名称"); third_cell.setCellStyle(style_2); third_cell = thirdRow.createCell((short)2); third_cell.setCellValue("正题名称"); third_cell.setCellStyle(style_2); third_cell = thirdRow.createCell((short)3); third_cell.setCellValue("文件名称"); third_cell.setCellStyle(style_2); third_cell = thirdRow.createCell((short)4); third_cell.setCellValue("节目类型"); third_cell.setCellStyle(style_2); third_cell = thirdRow.createCell((short)5); third_cell.setCellValue("高标清"); third_cell.setCellStyle(style_2); third_cell = thirdRow.createCell((short)6); third_cell.setCellValue("节目时长"); third_cell.setCellStyle(style_2); third_cell = thirdRow.createCell((short)7); third_cell.setCellValue("价格"); third_cell.setCellStyle(style_2); third_cell = thirdRow.createCell((short)8); third_cell.setCellValue("上片时间"); third_cell = thirdRow.createCell((short)9); third_cell.setCellValue("节目路径"); third_cell.setCellStyle(style_2); third_cell = thirdRow.createCell((short)10); third_cell.setCellValue("备注"); third_cell.setCellStyle(style_2); int listSize = 0; if(list!=null && list.size()>0){ listSize = list.size(); HSSFCellStyle style_3 = book.createCellStyle(); style_3.setAlignment(HSSFCellStyle.ALIGN_CENTER); style_3.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //组织节目数据 for(int i=0; i<listSize; i++){ Object[] obj = list.get(i); HSSFRow row = sheet.createRow(i+3); row.setHeight((short)800); HSSFCell i_cell = row.createCell((short)0); i_cell.setCellValue(""+(i+1)); i_cell.setCellStyle(style_3); i_cell = row.createCell((short)1); i_cell.setCellValue(String.valueOf(obj[0]).trim()); i_cell.setCellStyle(style_3); i_cell = row.createCell((short)2); i_cell.setCellValue(String.valueOf(obj[1]).trim()); i_cell.setCellStyle(style_3); i_cell = row.createCell((short)3); i_cell.setCellValue(String.valueOf(obj[2]).trim()); i_cell.setCellStyle(style_3); i_cell = row.createCell((short)4); i_cell.setCellValue(String.valueOf(obj[3]).trim()); i_cell.setCellStyle(style_3); i_cell = row.createCell((short)5); i_cell.setCellValue(String.valueOf(obj[4]).trim()); i_cell.setCellStyle(style_3); i_cell = row.createCell((short)6); i_cell.setCellValue(String.valueOf(obj[5]).trim()); i_cell.setCellStyle(style_3); i_cell = row.createCell((short)7); i_cell.setCellValue(String.valueOf(obj[6]).trim()); i_cell.setCellStyle(style_3); i_cell = row.createCell((short)8); i_cell.setCellValue(String.valueOf(obj[7]).trim()); i_cell.setCellStyle(style_3); i_cell = row.createCell((short)9); i_cell.setCellValue(String.valueOf(obj[8]).trim()); i_cell.setCellStyle(style_3); i_cell = row.createCell((short)10); i_cell.setCellValue(""); i_cell.setCellStyle(style_3); } } HSSFRow row_one = sheet.createRow(3+listSize); HSSFCell cell_one = row_one.createCell((short)0); cell_one.setCellValue("编辑:"); cell_one = row_one.createCell((short)9); cell_one.setCellValue("审核:"); sheet.addMergedRegion(new Region(3+listSize, (short)0, 3+listSize, (short)8)); sheet.addMergedRegion(new Region(3+listSize, (short)9, 3+listSize, (short)10)); HSSFRow row_two = sheet.createRow(4+listSize); HSSFCell cell_two = row_two.createCell((short)0); cell_two.setCellValue("制作:"); sheet.addMergedRegion(new Region(4+listSize, (short)0, 4+listSize, (short)10)); ByteArrayOutputStream os = new ByteArrayOutputStream(); try { book.write(os); } catch (IOException e) { e.printStackTrace(); } byte[] content = os.toByteArray(); InputStream is = new ByteArrayInputStream(content); // 设置response参数，可以打开下载页面 response.reset(); response.setContentType("application/vnd.ms-excel;charset=utf-8"); String filename = objectgroupname + ".xls";
filename = encodeFilename(filename, request); response.setHeader("Content-Disposition", "attachment;filename="+ filename); response.setCharacterEncoding("utf-8"); ServletOutputStream out = response.getOutputStream(); BufferedInputStream bis = null; BufferedOutputStream bos = null; try { bis = new BufferedInputStream(is); bos = new BufferedOutputStream(out); byte[] buff = new byte[2048]; int bytesRead; // Simple read/write loop. while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) { bos.write(buff, 0, bytesRead); } } catch (final IOException e) { throw e; } finally { if (bis != null) bis.close(); if (bos != null) bos.close(); } }
public static String encodeFilename(String filename, HttpServletRequest request) {
/**
* 获取客户端浏览器和操作系统信息
* 在IE浏览器中得到的是：User-Agent=Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; Maxthon; Alexa Toolbar)
* 在Firefox中得到的是：User-Agent=Mozilla/5.0 (Windows; U; Windows NT 5.1; zh-CN; rv:1.7.10) Gecko/20050717 Firefox/1.0.6
*/
String agent = request.getHeader("USER-AGENT");
try {
if ((agent != null) && (-1 != agent.indexOf("MSIE"))) {
String newFileName = URLEncoder.encode(filename, "UTF-8");
newFileName = StringUtils.replace(newFileName, "+", "%20");
if (newFileName.length() > 150) {
newFileName = new String(filename.getBytes("GB2312"), "ISO8859-1");
newFileName = StringUtils.replace(newFileName, " ", "%20");
}
return newFileName;
}
if ((agent != null) && (-1 != agent.indexOf("Mozilla")))
return MimeUtility.encodeText(filename, "UTF-8", "B");
return filename;
} catch (Exception ex) {
return filename;
}
}
以上是java部分的代码， 导出按照workbook-->sheet-->row-->cell的大体流程走， 再加上格式设置， 加上值等等
前端需要弹出下载框，可以用：
window.open("objectgroup.do"+"?action=exportAssetExcel&ogid="+ogid+"&objectgroupname="+objectgroupname);
或者用form提交
jsp页面
<form action="exportExcel_program" method="post" id="exportExcel_program_form">
<input type="hidden" name="searchXML" id="searchXML"/>
</form>
js处理
$("#searchXML").val(searchXML);
$("#exportExcel_program_form").submit();
这里参数可能有中文，所以java部分要处理，可以参见上面java代码
775
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
