关于java excel导出

[我是海亮啊]

java部分代码
@Controller
@RequestMapping("/OhProjectInformation")
public class OhProjectInformationController extends BaseController{
@RequestMapping("/export")
@ResponseBody
public void costExport( HttpServletRequest request,HttpServletResponse response) throws JsonParseException{
//((ServletRequest) response).setCharacterEncoding("UTF-8");
String mapUrl = null;
try {
mapUrl = new String(request.getParameter("url").getBytes("iso-8859-1"),"UTF-8");
} catch (UnsupportedEncodingException e2) {
log.info("请求异常"+e2);
}
List<OhProjectInformation> list = null;
try {
list = OhProjectInformationService.queryAllList(JSON.parseObject(mapUrl, OhProjectInformation.class));
} catch (Exception e) {
log.info("导出获取数据异常"+e);
}
response.setContentType("application/x-msdownload,charset=UTF-8");
String fileName="oh.xlsx";
try( ServletOutputStream ous = response.getOutputStream();) {
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFFont headfont = workbook.createFont();
headfont.setFontHeightInPoints((short) 9);// 字体大小
CellStyle titleStyle = CellStyleUtils.createStyle(workbook, "align:center;valign:middle;font:bold 14;");
CellStyle boldStyle = CellStyleUtils.createStyle(workbook, "align:center;valign:middle;font:bold;border:1 solid;");
CellStyle row11Style = CellStyleUtils.createStyle(workbook, "align:center;valign:middle;borde:thin;border:1 solid;");
CellStyleUtils.Border(boldStyle);
CellStyleUtils.Border(row11Style);
XSSFSheet sheet = workbook.createSheet("合同导出");
XSSFRow row0 = sheet.createRow(0);
XSSFCell cell00 = row0.createCell(0);
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 10));
for(int i=0;i<11;i++){
XSSFCell celles = row0.createCell(i);
celles.setCellStyle(titleStyle);
}
cell00.setCellValue("合同导出");
XSSFRow row1 = sheet.createRow(1);
XSSFCell cell110 = row1.createCell(0);
cell110.setCellValue("序号");
cell110.setCellStyle(boldStyle);
XSSFCell cell10 = row1.createCell(1);
cell10.setCellValue("客户名称");
cell10.setCellStyle(boldStyle);
XSSFCell cell11 = row1.createCell(2);
cell11.setCellValue("合同名称");
cell11.setCellStyle(boldStyle);
XSSFCell cell12 = row1.createCell(3);
cell12.setCellValue("负责人");
cell12.setCellStyle(boldStyle);
XSSFCell cell13 = row1.createCell(4);
cell13.setCellValue("登记年度");
cell13.setCellStyle(boldStyle);
XSSFCell cell14 = row1.createCell(5);
cell14.setCellValue("合同编号");
cell14.setCellStyle(boldStyle);
XSSFCell cell15 = row1.createCell(6);
cell15.setCellValue("合同金额");
cell15.setCellStyle(boldStyle);
XSSFCell cell16 = row1.createCell(7);
cell16.setCellValue("签订日期");
cell16.setCellStyle(boldStyle);
XSSFCell cell17 = row1.createCell(8);
cell17.setCellValue("工期时长");
cell17.setCellStyle(boldStyle);
XSSFCell cell18 = row1.createCell(9);
cell18.setCellValue("开工日期");
cell18.setCellStyle(boldStyle);
XSSFCell cell19 = row1.createCell(10);
cell19.setCellValue("结束日期");
cell19.setCellStyle(boldStyle);
XSSFCell cell20 = row1.createCell(11);
cell20.setCellValue("结算金额");
cell20.setCellStyle(boldStyle);
XSSFCell cell21 = row1.createCell(12);
cell21.setCellValue("操作人");
cell21.setCellStyle(boldStyle);
XSSFCell cell22 = row1.createCell(13);
cell22.setCellValue("备注");
cell22.setCellStyle(boldStyle);
for (int i = 0; i < list.size(); i++) {
XSSFRow rowi = sheet.createRow(2+i);
XSSFCell cell11i = rowi.createCell(0);
cell11i.setCellValue(1+i);
cell11i.setCellStyle(row11Style);
XSSFCell cell0i = rowi.createCell(1);
cell0i.setCellValue(list.get(i).getCustomerName());
cell0i.setCellStyle(row11Style);
XSSFCell cell1i = rowi.createCell(2);
cell1i.setCellValue(list.get(i).getProjectName());
cell1i.setCellStyle(row11Style);
XSSFCell cell2i = rowi.createCell(3);
cell2i.setCellValue(list.get(i).getProjectPeople());
cell2i.setCellStyle(row11Style);
XSSFCell cell3i = rowi.createCell(4);
cell3i.setCellValue(list.get(i).getRegisterYear());
cell3i.setCellStyle(row11Style);
XSSFCell cell4i = rowi.createCell(5);
cell4i.setCellValue(list.get(i).getContractNumber());
cell4i.setCellStyle(row11Style);
XSSFCell cell5i = rowi.createCell(6);
cell5i.setCellValue(list.get(i).getContractAmount());
cell5i.setCellStyle(row11Style);
XSSFCell cell6i = rowi.createCell(7);
cell6i.setCellValue(list.get(i).getSignTime());
cell6i.setCellStyle(row11Style);
XSSFCell cell7i = rowi.createCell(8);
cell7i.setCellValue(list.get(i).getTimeLimitTime());
cell7i.setCellStyle(row11Style);
XSSFCell cell8i = rowi.createCell(9);
cell8i.setCellValue(list.get(i).getStartTime());
cell8i.setCellStyle(row11Style);
XSSFCell cell9i = rowi.createCell(10);
cell9i.setCellValue(list.get(i).getEndTime());
cell9i.setCellStyle(row11Style);
XSSFCell cell10i = rowi.createCell(11);
cell10i.setCellValue(list.get(i).getClosedCost());
cell10i.setCellStyle(row11Style);
XSSFCell cell12i = rowi.createCell(12);
cell12i.setCellValue(list.get(i).getAddUser());
cell12i.setCellStyle(row11Style);
XSSFCell cell13i = rowi.createCell(13);
cell13i.setCellValue(list.get(i).getRemarks());
cell13i.setCellStyle(row11Style);
}
response.setHeader("Content-Disposition", "attachment; fileName="+fileName);
workbook.write(ous);
} catch (Exception e) {
log.info("合同导出异常"+e);
}
前端代码 创建一个合同按钮就可以了，
id:'OhProjectInformation-export',
text:'合同导出 ',
iconCls:'icon-print',
handler:function(){
var map=PM.formMap("searchForm");
var rows = $('#dg').datagrid('getRows');
if(null==rows || undefined==rows || rows.length<1){
$.messager.alert("操作提示", "表格没有数据，请查询数据!","error");
return
}
var url=JSON.stringify(map);
window.open(basePath+"/OhProjectInformation/export?url="+url,'_blank');
}

[路边摆摊的小贩]

req.setCharacterEncoding("utf-8"); resp.setCharacterEncoding("utf-8"); //设置响应的连接的方式(短连接)和响应的文件的类型(excel) resp.setHeader("Connection", "close"); // 设置http头的短连接 resp.setHeader("Content-Type", "application/vnd.ms-excel;charset=UTF-8"); // 设置响应的文件格式为excel //判断当前的浏览器的类型(MSIE和火狐),进一步设置编码集 String fileName="客户信息表.xls"; fileName = this.encodeFileName(req, fileName); // 判断当前的浏览器类型 //设置excel文件下载的方式是为附件 resp.setHeader("Content-Disposition", "attachment;filename=" + fileName); // 将此次需要下载的excel文件以附件形式展示出来 //获取从M层传递过来的数据 List<Guest> list=gsService.queryAll(); //设置表头信息 String[] biaoTi = {"客户编号", "客户姓名", "客户性别", "客户年龄", "身份证号", "房间编号", "房间类型"}; String sheetName = "客户信息表"; HSSFWorkbook wb = new HSSFWorkbook();// 创建一个工作薄 HSSFSheet sheet = wb.createSheet(sheetName);// 创建一个sheet Row biaoTiTouHang = sheet.createRow(0);// 创建第一行 for (int i = 0; i < biaoTi.length; i++) { //标题行 设置信息 biaoTiTouHang.createCell(i).setCellValue(biaoTi[i]); } //设置内容 if(!list.isEmpty()&&list!=null){ for (int i = 0; i < list.size(); i++) { HSSFRow neiRongHang = sheet.createRow(i + 1); Guest g=list.get(i); neiRongHang.createCell(0).setCellValue(g.getG_id()); neiRongHang.createCell(1).setCellValue(g.getG_name()); neiRongHang.createCell(2).setCellValue(g.getG_sex()); neiRongHang.createCell(3).setCellValue(g.getG_age()); neiRongHang.createCell(4).setCellValue(g.getId_card()); neiRongHang.createCell(5).setCellValue(g.getH_id().gethId()); neiRongHang.createCell(6).setCellValue(g.getH_id().gethType()); } OutputStream outStream = resp.getOutputStream();// 获取输出流 wb.write(outStream); // 将输出流写在工作薄上 outStream.close(); // 关闭输出流 } } public String encodeFileName(HttpServletRequest request, String filename){ try { String agent = request.getHeader("USER-AGENT"); // 获取客户端浏览器和操作系统信息 if (null != agent && -1 != agent.indexOf("MSIE")){// 如果是微软的IE return URLEncoder.encode(filename, "UTF-8"); } else if (null != agent && -1 != agent.indexOf("Mozilla")){ return "=?UTF-8" + (new String(filename.getBytes("UTF-8"))) + "?="; } else { return filename; } } catch (Exception e) { // TODO: handle exception return null; } }