java 数据导出excel

怼烎@ 2022-01-14 01:17 337阅读 0赞

public String doExcel(List trainSignList) { //传入你需要导出的LIST
    		String msg = "";
    		// /导出到EXCEL
    		HttpServletResponse response = (HttpServletResponse) ActionContext.getContext().get(org.apache.struts2.StrutsStatics.HTTP_RESPONSE);
    		response.reset();
    		try {
    			response.setContentType("application/vnd.ms-excel;charset=UTF-8");
    			response.setHeader("Content-disposition", "attachment;filename=\""+ new String(("服务项目.xls").getBytes("GBK"),"ISO-8859-1") + "\"");    //这里的是要导出的文件显示的名字
    		} catch (UnsupportedEncodingException e1) {
    			msg = "不支持编码异常！";
    		}
    		String filePath = Platform.getInstance().getRealPath()+ "/templateFile/";
    		Workbook rwb = null;
    		try {
    			InputStream is = new FileInputStream(filePath+ "/服务项目.xls");//这里是文件的模板
    			rwb = Workbook.getWorkbook(is);
    			WritableWorkbook wwb = Workbook.createWorkbook(response.getOutputStream(), rwb);
    			WritableSheet ws = wwb.getSheet(0);
    			int rowNum = 1; // 行excel中从0开始  第0行显示的是你的大标题 所以从第一行开始显示数据
    			int colNum = 0; // 列 
    			WritableFont wFont = new WritableFont(WritableFont.ARIAL, 10);
    			WritableCellFormat wcf = new WritableCellFormat(wFont);
    			wcf.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
    			wcf.setBorder(jxl.format.Border.ALL, BorderLineStyle.THIN);
    			wcf.setAlignment(jxl.format.Alignment.CENTRE);
    			wcf.setWrap(true);
    			for (int iRow = 0; iRow < trainSignList.size(); iRow++) {
    				colNum = 0; //从第一列开始
    				OmpViewServiceExcelModel view = (OmpViewServiceExcelModel) trainSignList.get(iRow);//这里获取你的list
    				// 服务名称
    				String serviceName = view.getServiceName();
    				ws.addCell(new Label(colNum, rowNum, serviceName, wcf));
    				colNum++; //放入一个数据就让列+1
    				//服务分类大类
    				String serviceTypeName1 =view.getServiceTypeName1();
    				ws.addCell(new Label(colNum, rowNum, serviceTypeName1, wcf));
    				colNum++;
    				//服务分类中类
    				String serviceTypeName2 =view.getServiceTypeName2();
    				ws.addCell(new Label(colNum, rowNum, serviceTypeName2, wcf));
    				colNum++;
    				//服务分类小类
    				String serviceTypeName3 =view.getServiceTypeName3();
    				ws.addCell(new Label(colNum, rowNum, serviceTypeName3, wcf));
    				colNum++;
    				 /**服务介绍**/
    				String serviceProcess =view.getServiceProcess();
    				ws.addCell(new Label(colNum, rowNum, serviceProcess, wcf));
    				colNum++;
    			    /**服务特色**/
    				String serviceTe=view.getServiceTe();
    				ws.addCell(new Label(colNum, rowNum, serviceTe, wcf));
    				colNum++;
    			    /**收费方式**/
    				String serviceCharge =view.getServiceCharge();
    				ws.addCell(new Label(colNum, rowNum, serviceCharge, wcf));
    				colNum++;
    			    /**服务资质**/
    				String serviceAbility =view.getServiceAbility();
    				ws.addCell(new Label(colNum, rowNum, serviceAbility, wcf));
    				colNum++;
    			    /**创建时间**/
    				Timestamp auditTime =view.getAuditTime();
    				DateFormat forMat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss"); //时间格式的话 需要转换一下
    				if(auditTime!=null&&!auditTime.equals("")){
    					String  tsStr = forMat.format(auditTime);  
    					ws.addCell(new Label(colNum, rowNum, tsStr, wcf));
    				}else{
    					ws.addCell(new Label(colNum, rowNum, "", wcf));
    				}
    				colNum++;
    			    /**联系人**/
    				String responsiblemenName =view.getResponsiblemenName();
    				ws.addCell(new Label(colNum, rowNum, responsiblemenName, wcf));
    				colNum++;
    			    /**联系电话**/
    				String responsiblemenPhone =view.getResponsiblemenPhone();
    				ws.addCell(new Label(colNum, rowNum, responsiblemenPhone, wcf));
    				colNum++;
    			    /**联系邮箱**/
    				String responsiblemenEmail =view.getResponsiblemenEmail();
    				ws.addCell(new Label(colNum, rowNum, responsiblemenEmail, wcf));
    				colNum++;
    			    /**创建单位**/
    				String winName =view.getWinName();
    				ws.addCell(new Label(colNum, rowNum, winName, wcf));
    				rowNum++;//最后的时候要使行+1 移动到下一行
    			}
    			wwb.write();
    			wwb.close();
    		}
    
    		catch (Exception e) {
    			msg = "数据没法写入excel中！！";
    		} finally {
    			rwb.close();
    		}
    		return msg;
    	}

其中需要合并单元格的可以用

ws.mergeCells(0, rowNum-1, 2,rowNum-1); //合并单元格，参数格式（开始列，开始行，结束列，结束行）