精华内容
下载资源
问答
  • jxl导出excel(合并行、合并列、单元格居中、单元格固定宽度、字体加粗、分页签)

    public ActionForward performExport(ActionMapping mapping, ActionForm form, HttpServletRequest request, HttpServletResponse response) throws IOException {
    
    		String exportType = request.getParameter("exportType");
    		if ("check".equals(exportType)) {
    			request.setAttribute("check", "y");
    		}
    		String tableName = "工单数据表";
    		String excelFileName = new String(("工单数据表" + DateUtil.getSysDateString() + ".xls").getBytes("GBK"), "iso-8859-1");
    		List list = null;
    		
    		PosOrderBO poBO = new PosOrderBO();
    		//2016-06-24修改成根据工单类型分页签导出
    		//String sql = getSelectSQL5(request, (PubForm) form);
    		String sql = getWorkOrderGroupByWorkOrderTypeSQL(request,(PubForm)form)[0];
    		String sqlNumSql = getWorkOrderGroupByWorkOrderTypeSQL(request,(PubForm)form)[1];
    		
    		// 导出所有
    		try {
    			list =poBO.list(sql);
    			List xjNumList = poBO.list(sqlNumSql);
    			response.setContentType("application/vnd.ms-excel");
    			response.setHeader("Content_Length", "10000000");
    			if (request.getHeader("User-Agent").indexOf("MSIE   5.5") != -1) {
    				/** MS IE5.5 */
    				response.setHeader("Content-disposition", "filename=\"" + excelFileName + "\"");
    			} else {
    				/** 非MS IE5.5 */
    				response.setHeader("Content-disposition", "attachment;filename=\"" + excelFileName + "\"");
    			}
    			
    //			ExcelFileUtil.writePosOrderExcel3(list, response.getOutputStream(), tableName);
    			ExcelFileUtil.writePosOrderExcel4(list,xjNumList, response.getOutputStream(), tableName);
    		} catch (Exception ex) {
    			ex.printStackTrace();
    			response.setStatus(HttpServletResponse.SC_OK);
    		} finally {
    			if (response != null && response.getOutputStream() != null) {
    				response.getOutputStream().close();
    			}
    		}
    		return null;
    	}public String[] getWorkOrderGroupByWorkOrderTypeSQL(HttpServletRequest request, ActionForm form){
    
    
    		Manager manager = (Manager) request.getSession().getAttribute(SystemConst.MANAGER);
    		ShopRole role = manager.getRoleObject();
    		String roleName =role.getName();
    		String type = request.getParameter("type");
    		String queryDate = request.getParameter("date");
    		String bussOrgId = "";
    		if(roleName.equals("片区组长")){
    			List list = StoreHelper.getOrgsByManagerId(manager.getId());
    		 	if(list != null && list.size() > 0){
    	    	for(int i=0;i='" + start + "'" + 
    //				" AND po.orderTime <='" + end + "'" +
    				" AND po.orderType like '%15%' "
    				);
    		
    		
    		
    			if (!StringUtil.isEmpty(type) && !StringUtil.isEmpty(queryDate)) {
    				java.sql.Timestamp firstTempDate = null;
    				java.sql.Timestamp lastTempDate = null;
    				String firstDate = "";
    				String lastDate = "";
    				if(type.equals("2")){//月
    					java.sql.Timestamp tempDate = DateUtil.toSqlTimestamp(queryDate);
    					// 得到月底日期
    					firstTempDate = DateUtil.getFirstDayOfMonth(tempDate);
    					lastTempDate = DateUtil.getLastDayOfMonth(tempDate);
    					firstDate = DateUtil.toSqlTimestampString(firstTempDate);
    					lastDate = DateUtil.toSqlTimestampString(lastTempDate);
    					// 如当前时间未到月底,则用当前时间
    					if (DateUtil.isSysDateBefore(lastDate)) {
    						lastDate = DateUtil.getSysDateString();
    					}
    					sql.append(" and  po.orderTime >='" + firstDate + "' and po.orderTime <='" + lastDate + "'" );
    					xjSql.append(" and  po.orderTime >='" + firstDate + "' and po.orderTime <='" + lastDate + "'" );
    				}else if(type.equals("1")){//日
    					firstDate =queryDate;
    					lastDate =queryDate;
    					sql.append(" and  po.orderTime like'" + firstDate + "%'" ); 
    					xjSql.append(" and  po.orderTime like'" + firstDate + "%'" ); 
    				}
    				String resDate = "";
    				if(lastDate != null && lastDate.length() >= 7){
    					resDate = lastDate.substring(0,4) + "年" + lastDate.substring(5,7) + "月";
    				}else{
    					resDate = lastDate;
    				}
    				request.setAttribute("queryDate", queryDate);
    				request.setAttribute("queryType", type);
    				request.setAttribute("resDate", resDate);
    			}
    			String backType = request.getParameter("bean.orderType");
    			String storeNo = request.getParameter("bean.storeNo");
    			String terminalNo = request.getParameter("bean.terminalNo");
    			String searchExecOper = request.getParameter("bean.execOper");
    			List managerIds = PosManagerHelpler.getManagerIdsByName(searchExecOper);
    			
    			String backDeviceSn = request.getParameter("bean.deviceSn");
    			String backStatus = request.getParameter("bean.orderStatus");
    			String startDate = request.getParameter("startDate");
    			String endDate = request.getParameter("endDate");
    			String deviceStatus = request.getParameter("deviceStatus");
    			String deviceType = request.getParameter("deviceType");
    			
    			
    			request.setAttribute("bussOrgId", bussOrgId);
    			request.setAttribute("bussManagerName", bussManagerName);
    			request.setAttribute("searchExecOper", searchExecOper);
    			request.setAttribute("backDeviceSn", backDeviceSn);
    			request.setAttribute("deviceStatus", deviceStatus);
    			request.setAttribute("deviceType", deviceType);
    			if(!StringUtil.isEmpty(deviceType)){
    				sql.append(" and pd.deviceType ='"+deviceType+"'");
    				xjSql.append(" and pd.deviceType ='"+deviceType+"'");
    			}
    			if(!StringUtil.isEmpty(deviceStatus)){
    				sql.append(" and pd.status ='"+deviceStatus+"'");
    				xjSql.append(" and pd.status ='"+deviceStatus+"'");
    			}
    			if(!StringUtil.isEmpty(bussOrgId)){
    				if("9".equals(bussOrgId)){//选择常州,就总计所有片区
    					sql.append("  ");
    					xjSql.append("  ");
    				}else if("33".equals(bussOrgId)){//选择其他区,就统计
    					sql.append(" AND (s.bussOrgId='9' or s.bussOrgId='33') ");
    					xjSql.append(" AND (s.bussOrgId='9' or s.bussOrgId='33') ");
    				}else{
    					sql.append(" AND s.bussOrgId='"+bussOrgId+"'");
    					xjSql.append(" AND s.bussOrgId='"+bussOrgId+"'");
    				}
    			}
    			if(!StringUtil.isEmpty(bussManagerName)){
    				sql.append(" and po.execOperNo like '%"+bussManagerName+"%'");
    				xjSql.append(" and po.execOperNo like '%"+bussManagerName+"%'");
    			}
    			
    			if (!StringUtil.isEmpty(storeNo)) {
    				sql.append(" and s.storeNo like '%" + storeNo + "%'");
    				xjSql.append(" and s.storeNo like '%" + storeNo + "%'");
    			}
    			if (!StringUtil.isEmpty(terminalNo)) {
    				sql.append(" and po.terminalNo like '%" + terminalNo + "%'");
    				xjSql.append(" and po.terminalNo like '%" + terminalNo + "%'");
    			}
    			if(!StringUtil.isEmpty(searchExecOper)){
    				if(managerIds != null && managerIds.size()>0){
    					sql.append(" and (");
    					xjSql.append(" and (");
    					for(int i = 0;i= '" + FormatHelper.parseTimestamp(startDate, true) + "'");
    				xjSql.append(" and po.orderTime >= '" + FormatHelper.parseTimestamp(startDate, true) + "'");
    			}
    			if (!StringUtil.isEmpty(endDate)) {
    				sql.append(" and po.orderTime <= '" + FormatHelper.parseTimestamp(endDate, false) + "'");
    				xjSql.append(" and po.orderTime <= '" + FormatHelper.parseTimestamp(endDate, false) + "'");
    			}
    			if (!StringUtil.isEmpty(backType)) {
    				if(backType.contains("-")){
    					String [] str =backType.split("-");
    					sql.append(" and po.orderType  like '%").append(str[0]).append("%' and po.workType like '%").append(str[1]).append("%'");
    					xjSql.append(" and po.orderType  like '%").append(str[0]).append("%' and po.workType like '%").append(str[1]).append("%'");
    				}else{
    					sql.append(" and po.orderType like '%").append(backType).append( "%'");
    					xjSql.append(" and po.orderType like '%").append(backType).append( "%'");
    				}
    			}else{
    				sql.append(" and (po.orderType like '%10%' or po.orderType like '%11%' or po.orderType like '%14%' or po.orderType like '%15%') ");
    				xjSql.append(" and (po.orderType like '%10%' or po.orderType like '%11%' or po.orderType like '%14%' or po.orderType like '%15%') ");
    			}
    			sql.append("and po.isDelete = '1' order by po.orderTime desc ");
    			xjSql.append("and po.isDelete = '1' order by po.orderTime desc ");
    
    
    			String [] sqlArr = {sql.toString(),xjSql.toString()};
    			
    			return sqlArr;
    	
    	}public static void writePosOrderExcel4(List result,List xjNumList, OutputStream os,
    			String tableName) throws Exception {
    		// excel标题的格式
    		WritableCellFormat titleFormat = new WritableCellFormat(
    				new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD,
    						false, UnderlineStyle.NO_UNDERLINE,
    						jxl.format.Colour.BLACK));
    //		titleFormat.setBackground(Colour.SKY_BLUE); 
    		//设置边框;  
    		titleFormat.setBorder(Border.ALL, BorderLineStyle.MEDIUM);  
    		//设置自动换行;  
    //		titleFormat.setWrap(true);  
    		//设置文字居中对齐方式;  
    		titleFormat.setAlignment(Alignment.CENTRE);  
    		//设置垂直居中;  
    		titleFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
    		WritableFont detFont = new WritableFont(WritableFont.ARIAL, 10,
    				WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE,
    				jxl.format.Colour.BLACK);
    		
    		// excedl数据的格式
    		WritableCellFormat detFormat = new WritableCellFormat(detFont);
    		//设置边框;  
    		detFormat.setBorder(Border.ALL, BorderLineStyle.THIN);  
    		//设置自动换行;  
    		detFormat.setWrap(true);  
    //        //设置文字居中对齐方式;  
    //		detFormat.setAlignment(Alignment.CENTRE);  
    		//设置垂直居中;  
    		detFormat.setVerticalAlignment(VerticalAlignment.CENTRE);
    		//水平居中
    		detFormat.setAlignment(Alignment.CENTRE);
    		
    		WritableWorkbook workbook = Workbook.createWorkbook(os);
    //		WritableSheet sheet = workbook.createSheet(
    //				tableName + DateUtil.getSysDateString(), 0);
    		
    		//跟进
    		WritableSheet sheetGJ = workbook.createSheet(
    				"跟进", 0);
    		//移机
    		WritableSheet sheetYJ = workbook.createSheet(
    				"移机", 0);
    		//切机
    		WritableSheet sheetQJ = workbook.createSheet(
    				"切机", 0);
    		//程序升级
    		WritableSheet sheetSJ = workbook.createSheet(
    				"升级", 0);
    		//换装
    		WritableSheet sheetHZ = workbook.createSheet(
    				"换装", 0);
    		//新装
    		WritableSheet sheetXZ = workbook.createSheet(
    				"新装", 0);
    		//巡检
    		WritableSheet sheetXJ = workbook.createSheet(
    				"巡检", 0);
    		
    		
    		Formatter formatter = new Formatter();
    		
    		//巡检工单详细数据
    		HashMap mapXJ =null;
    		if(xjNumList!=null && xjNumList.size()>0){
    			mapXJ=(HashMap) xjNumList.get(0);
    		}
    		try {
    			Label lableXJ = null;
    			Label lableXZ = null;
    			Label lableHZ = null;
    			Label lableSJ = null;
    			Label lableQJ = null;
    			Label lableYJ = null;
    			Label lableGJ = null;
    			
    			lableXJ = new Label(0,0,"状态",titleFormat);
    			sheetXJ.addCell(lableXJ);
    			lableXJ = new Label(1,0,"细分状态",titleFormat);
    			sheetXJ.addCell(lableXJ);
    			lableXJ = new Label(2,0,"数量",titleFormat);
    			sheetXJ.addCell(lableXJ);
    			lableXJ = new Label(3,0,"总数",titleFormat);
    			sheetXJ.addCell(lableXJ);
    			
    			//复合表格
    			sheetXJ.mergeCells(0, 1, 0, 3);
    			lableXJ = new Label(0,1,"正常",titleFormat);
    			sheetXJ.addCell(lableXJ);
    			sheetXJ.mergeCells(3, 1, 3, 3);
    			lableXJ = new Label(3,1,mapXJ==null?"0":(mapXJ.get("zcZongShu")==null?"0":mapXJ.get("zcZongShu").toString()),detFormat);
    			sheetXJ.addCell(lableXJ);
    			
    			lableXJ = new Label(1,1,"正常适用",titleFormat);
    			sheetXJ.addCell(lableXJ);
    			lableXJ = new Label(2,1,mapXJ==null?"0":(mapXJ.get("zcsy")==null?"0":mapXJ.get("zcsy").toString()),detFormat);
    			sheetXJ.addCell(lableXJ);
    			lableXJ = new Label(1,2,"机具故障",titleFormat);
    			sheetXJ.addCell(lableXJ);
    			lableXJ = new Label(2,2,mapXJ==null?"0":(mapXJ.get("jjgz")==null?"0":mapXJ.get("jjgz").toString()),detFormat);
    			sheetXJ.addCell(lableXJ);
    			lableXJ = new Label(1,3,"线路故障",titleFormat);
    			sheetXJ.addCell(lableXJ);
    			lableXJ = new Label(2,3,mapXJ==null?"0":(mapXJ.get("xlgz")==null?"0":mapXJ.get("xlgz").toString()),detFormat);
    			sheetXJ.addCell(lableXJ);
    			
    			sheetXJ.mergeCells(0, 4, 0, 18);
    			lableXJ = new Label(0,4,"异常",titleFormat);
    			sheetXJ.addCell(lableXJ);
    			sheetXJ.mergeCells(3, 4, 3, 18);
    			lableXJ = new Label(3,4,mapXJ==null?"0":(mapXJ.get("ycZongShu")==null?"0":mapXJ.get("ycZongShu").toString()),detFormat);
    			sheetXJ.addCell(lableXJ);
    			
    			lableXJ = new Label(1,4,"不使用",titleFormat);
    			sheetXJ.addCell(lableXJ);
    			lableXJ = new Label(2,4,mapXJ==null?"0":(mapXJ.get("bsy")==null?"0":mapXJ.get("bsy").toString()),detFormat);
    			sheetXJ.addCell(lableXJ);
    			lableXJ = new Label(1,5,"常连海",titleFormat);
    			sheetXJ.addCell(lableXJ);
    			lableXJ = new Label(2,5,mapXJ==null?"0":(mapXJ.get("clh")==null?"0":mapXJ.get("clh").toString()),detFormat);
    			sheetXJ.addCell(lableXJ);
    			lableXJ = new Label(1,6,"电子银行部",titleFormat);
    			sheetXJ.addCell(lableXJ);
    			lableXJ = new Label(2,6,mapXJ==null?"0":(mapXJ.get("dzyhb")==null?"0":mapXJ.get("dzyhb").toString()),detFormat);
    			sheetXJ.addCell(lableXJ);
    			lableXJ = new Label(1,7,"外区",titleFormat);
    			sheetXJ.addCell(lableXJ);
    			lableXJ = new Label(2,7,mapXJ==null?"0":(mapXJ.get("wq")==null?"0":mapXJ.get("wq").toString()),detFormat);
    			sheetXJ.addCell(lableXJ);
    			lableXJ = new Label(1,8,"移机",titleFormat);
    			sheetXJ.addCell(lableXJ);
    			lableXJ = new Label(2,8,mapXJ==null?"0":(mapXJ.get("yj")==null?"0":mapXJ.get("yj").toString()),detFormat);
    			sheetXJ.addCell(lableXJ);
    			lableXJ = new Label(1,9,"异常(有机具)",titleFormat);
    			sheetXJ.addCell(lableXJ);
    			lableXJ = new Label(2,9,mapXJ==null?"0":(mapXJ.get("ycyjj")==null?"0":mapXJ.get("ycyjj").toString()),detFormat);
    			sheetXJ.addCell(lableXJ);
    			lableXJ = new Label(1,10,"不配合(待定)",titleFormat);
    			sheetXJ.addCell(lableXJ);
    			lableXJ = new Label(2,10,mapXJ==null?"0":(mapXJ.get("bphdd")==null?"0":mapXJ.get("bphdd").toString()),detFormat);
    			sheetXJ.addCell(lableXJ);
    			lableXJ = new Label(1,11,"不配合(有机具)",titleFormat);
    			sheetXJ.addCell(lableXJ);
    			lableXJ = new Label(2,11,mapXJ==null?"0":(mapXJ.get("bphyjj")==null?"0":mapXJ.get("bphyjj").toString()),detFormat);
    			sheetXJ.addCell(lableXJ);
    			lableXJ = new Label(1,12,"遗失",titleFormat);
    			sheetXJ.addCell(lableXJ);
    			lableXJ = new Label(2,12,mapXJ==null?"0":(mapXJ.get("ys")==null?"0":mapXJ.get("ys").toString()),detFormat);
    			sheetXJ.addCell(lableXJ);
    			lableXJ = new Label(1,13,"支行回收",titleFormat);
    			sheetXJ.addCell(lableXJ);
    			lableXJ = new Label(2,13,mapXJ==null?"0":(mapXJ.get("zhhs")==null?"0":mapXJ.get("zhhs").toString()),detFormat);
    			sheetXJ.addCell(lableXJ);
    			lableXJ = new Label(1,14,"注销",titleFormat);
    			sheetXJ.addCell(lableXJ);
    			lableXJ = new Label(2,14,mapXJ==null?"0":(mapXJ.get("zx")==null?"0":mapXJ.get("zx").toString()),detFormat);
    			sheetXJ.addCell(lableXJ);
    			lableXJ = new Label(1,15,"切机",titleFormat);
    			sheetXJ.addCell(lableXJ);
    			lableXJ = new Label(2,15,mapXJ==null?"0":(mapXJ.get("qj")==null?"0":mapXJ.get("qj").toString()),detFormat);
    			sheetXJ.addCell(lableXJ);
    			lableXJ = new Label(1,16,"套码",titleFormat);
    			sheetXJ.addCell(lableXJ);
    			lableXJ = new Label(2,16,mapXJ==null?"0":(mapXJ.get("tm")==null?"0":mapXJ.get("tm").toString()),detFormat);
    			sheetXJ.addCell(lableXJ);
    			lableXJ = new Label(1,17,"地址更新",titleFormat);
    			sheetXJ.addCell(lableXJ);
    			lableXJ = new Label(2,17,mapXJ==null?"0":(mapXJ.get("dzgx")==null?"0":mapXJ.get("dzgx").toString()),detFormat);
    			sheetXJ.addCell(lableXJ);
    			lableXJ = new Label(1,18,"其他",titleFormat);
    			sheetXJ.addCell(lableXJ);
    			lableXJ = new Label(2,18,mapXJ==null?"0":(mapXJ.get("kb")==null?"0":mapXJ.get("kb").toString()),detFormat);
    			sheetXJ.addCell(lableXJ);
    			
    			sheetXJ.mergeCells(0, 19, 2, 19);
    			lableXJ = new Label(0,19,"合计",titleFormat);
    			sheetXJ.addCell(lableXJ);
    //			lableXJ = new Label(3,19,mapXJ==null?"0":(mapXJ.get("hjZongShu")==null?"0":mapXJ.get("hjZongShu").toString()),detFormat);
    			lableXJ = new Label(3,19, String.valueOf(Integer.parseInt(mapXJ==null?"0":(mapXJ.get("zcZongShu")==null?"0":mapXJ.get("zcZongShu").toString()))+Integer.parseInt(mapXJ==null?"0":(mapXJ.get("ycZongShu")==null?"0":mapXJ.get("ycZongShu").toString()))) ,detFormat);
    			sheetXJ.addCell(lableXJ);
    			
    			
    			int columnXJ = 4;
    			int columnXZ = 0;
    			int columnHZ = 0;
    			int columnSJ = 0;
    			int columnQJ = 0;
    			int columnYJ = 0;
    			int columnGJ = 0;
    			
    //			sheet.setRowView(0, 588, false); 
    			lableXJ = new Label(columnXJ++, 0, "设备状态", titleFormat);
    			lableXZ = new Label(columnXZ++, 0, "设备状态", titleFormat);
    			lableHZ = new Label(columnHZ++, 0, "设备状态", titleFormat);
    			lableSJ = new Label(columnSJ++, 0, "设备状态", titleFormat);
    			lableQJ = new Label(columnQJ++, 0, "设备状态", titleFormat);
    			lableYJ = new Label(columnYJ++, 0, "设备状态", titleFormat);
    			lableGJ = new Label(columnGJ++, 0, "设备状态", titleFormat);
    			sheetXJ.addCell(lableXJ);
    			sheetXZ.addCell(lableXZ);
    			sheetHZ.addCell(lableHZ);
    			sheetSJ.addCell(lableSJ);
    			sheetQJ.addCell(lableQJ);
    			sheetYJ.addCell(lableYJ);
    			sheetGJ.addCell(lableGJ);
    //			sheetXJ.setColumnView(1, 9);
    			lableXJ = new Label(columnXJ++, 0, "工单备注", titleFormat);
    			lableXZ = new Label(columnXZ++, 0, "工单备注", titleFormat);
    			lableHZ = new Label(columnHZ++, 0, "工单备注", titleFormat);
    			lableSJ = new Label(columnSJ++, 0, "工单备注", titleFormat);
    			lableQJ = new Label(columnQJ++, 0, "工单备注", titleFormat);
    			lableYJ = new Label(columnYJ++, 0, "工单备注", titleFormat);
    			lableGJ = new Label(columnGJ++, 0, "工单备注", titleFormat);
    			sheetXJ.addCell(lableXJ);
    			sheetXZ.addCell(lableXZ);
    			sheetHZ.addCell(lableHZ);
    			sheetSJ.addCell(lableSJ);
    			sheetQJ.addCell(lableQJ);
    			sheetYJ.addCell(lableYJ);
    			sheetGJ.addCell(lableGJ);
    //			sheet.setColumnView(2, 9);
    			lableXJ = new Label(columnXJ++, 0, "区域", titleFormat);
    			lableXZ = new Label(columnXZ++, 0, "区域", titleFormat);
    			lableHZ = new Label(columnHZ++, 0, "区域", titleFormat);
    			lableSJ = new Label(columnSJ++, 0, "区域", titleFormat);
    			lableQJ = new Label(columnQJ++, 0, "区域", titleFormat);
    			lableYJ = new Label(columnYJ++, 0, "区域", titleFormat);
    			lableGJ = new Label(columnGJ++, 0, "区域", titleFormat);
    			sheetXJ.addCell(lableXJ);
    			sheetXZ.addCell(lableXZ);
    			sheetHZ.addCell(lableHZ);
    			sheetSJ.addCell(lableSJ);
    			sheetQJ.addCell(lableQJ);
    			sheetYJ.addCell(lableYJ);
    			sheetGJ.addCell(lableGJ);
    //			sheet.setColumnView(3, 9);
    			lableXJ = new Label(columnXJ++, 0, "确认人", titleFormat);
    			lableXZ = new Label(columnXZ++, 0, "确认人", titleFormat);
    			lableHZ = new Label(columnHZ++, 0, "确认人", titleFormat);
    			lableSJ = new Label(columnSJ++, 0, "确认人", titleFormat);
    			lableQJ = new Label(columnQJ++, 0, "确认人", titleFormat);
    			lableYJ = new Label(columnYJ++, 0, "确认人", titleFormat);
    			lableGJ = new Label(columnGJ++, 0, "确认人", titleFormat);
    			sheetXJ.addCell(lableXJ);
    			sheetXZ.addCell(lableXZ);
    			sheetHZ.addCell(lableHZ);
    			sheetSJ.addCell(lableSJ);
    			sheetQJ.addCell(lableQJ);
    			sheetYJ.addCell(lableYJ);
    			sheetGJ.addCell(lableGJ);
    //			sheet.setColumnView(4, 9);
    			lableXJ = new Label(columnXJ++, 0, "客户经理", titleFormat);
    			lableXZ = new Label(columnXZ++, 0, "客户经理", titleFormat);
    			lableHZ = new Label(columnHZ++, 0, "客户经理", titleFormat);
    			lableSJ = new Label(columnSJ++, 0, "客户经理", titleFormat);
    			lableQJ = new Label(columnQJ++, 0, "客户经理", titleFormat);
    			lableYJ = new Label(columnYJ++, 0, "客户经理", titleFormat);
    			lableGJ = new Label(columnGJ++, 0, "客户经理", titleFormat);
    			sheetXJ.addCell(lableXJ);
    			sheetXZ.addCell(lableXZ);
    			sheetHZ.addCell(lableHZ);
    			sheetSJ.addCell(lableSJ);
    			sheetQJ.addCell(lableQJ);
    			sheetYJ.addCell(lableYJ);
    			sheetGJ.addCell(lableGJ);
    //			sheet.setColumnView(5, 9);
    			lableXJ = new Label(columnXJ++, 0, "客户经理电话", titleFormat);
    			lableXZ = new Label(columnXZ++, 0, "客户经理电话", titleFormat);
    			lableHZ = new Label(columnHZ++, 0, "客户经理电话", titleFormat);
    			lableSJ = new Label(columnSJ++, 0, "客户经理电话", titleFormat);
    			lableQJ = new Label(columnQJ++, 0, "客户经理电话", titleFormat);
    			lableYJ = new Label(columnYJ++, 0, "客户经理电话", titleFormat);
    			lableGJ = new Label(columnGJ++, 0, "客户经理电话", titleFormat);
    			sheetXJ.addCell(lableXJ);
    			sheetXZ.addCell(lableXZ);
    			sheetHZ.addCell(lableHZ);
    			sheetSJ.addCell(lableSJ);
    			sheetQJ.addCell(lableQJ);
    			sheetYJ.addCell(lableYJ);
    			sheetGJ.addCell(lableGJ);
    //			sheet.setColumnView(6, 15);
    //			sheet.setColumnView(7, cellView);
    			lableXJ = new Label(columnXJ++, 0, "商户代码 ", titleFormat);
    			lableXZ = new Label(columnXZ++, 0, "商户代码", titleFormat);
    			lableHZ = new Label(columnHZ++, 0, "商户代码", titleFormat);
    			lableSJ = new Label(columnSJ++, 0, "商户代码", titleFormat);
    			lableQJ = new Label(columnQJ++, 0, "商户代码", titleFormat);
    			lableYJ = new Label(columnYJ++, 0, "商户代码", titleFormat);
    			lableGJ = new Label(columnGJ++, 0, "商户代码", titleFormat);
    			sheetXJ.addCell(lableXJ);
    			sheetXZ.addCell(lableXZ);
    			sheetHZ.addCell(lableHZ);
    			sheetSJ.addCell(lableSJ);
    			sheetQJ.addCell(lableQJ);
    			sheetYJ.addCell(lableYJ);
    			sheetGJ.addCell(lableGJ);
    //			sheet.setColumnView(8, cellView);
    			lableXJ = new Label(columnXJ++, 0, "终端号", titleFormat);
    			lableXZ = new Label(columnXZ++, 0, "终端号", titleFormat);
    			lableHZ = new Label(columnHZ++, 0, "终端号", titleFormat);
    			lableSJ = new Label(columnSJ++, 0, "终端号", titleFormat);
    			lableQJ = new Label(columnQJ++, 0, "终端号", titleFormat);
    			lableYJ = new Label(columnYJ++, 0, "终端号", titleFormat);
    			lableGJ = new Label(columnGJ++, 0, "终端号", titleFormat);
    			sheetXJ.addCell(lableXJ);
    			sheetXZ.addCell(lableXZ);
    			sheetHZ.addCell(lableHZ);
    			sheetSJ.addCell(lableSJ);
    			sheetQJ.addCell(lableQJ);
    			sheetYJ.addCell(lableYJ);
    			sheetGJ.addCell(lableGJ);
    //			sheet.setColumnView(9, cellView);
    			lableXJ = new Label(columnXJ++, 0, "商户中文名称", titleFormat);
    			lableXZ = new Label(columnXZ++, 0, "商户中文名称", titleFormat);
    			lableHZ = new Label(columnHZ++, 0, "商户中文名称", titleFormat);
    			lableSJ = new Label(columnSJ++, 0, "商户中文名称", titleFormat);
    			lableQJ = new Label(columnQJ++, 0, "商户中文名称", titleFormat);
    			lableYJ = new Label(columnYJ++, 0, "商户中文名称", titleFormat);
    			lableGJ = new Label(columnGJ++, 0, "商户中文名称", titleFormat);
    			sheetXJ.addCell(lableXJ);
    			sheetXZ.addCell(lableXZ);
    			sheetHZ.addCell(lableHZ);
    			sheetSJ.addCell(lableSJ);
    			sheetQJ.addCell(lableQJ);
    			sheetYJ.addCell(lableYJ);
    			sheetGJ.addCell(lableGJ);
    //			sheet.setColumnView(10, 100);
    			lableXJ = new Label(columnXJ++, 0, "经营地址", titleFormat);
    			lableXZ = new Label(columnXZ++, 0, "经营地址", titleFormat);
    			lableHZ = new Label(columnHZ++, 0, "经营地址", titleFormat);
    			lableSJ = new Label(columnSJ++, 0, "经营地址", titleFormat);
    			lableQJ = new Label(columnQJ++, 0, "经营地址", titleFormat);
    			lableYJ = new Label(columnYJ++, 0, "经营地址", titleFormat);
    			lableGJ = new Label(columnGJ++, 0, "经营地址", titleFormat);
    			sheetXJ.addCell(lableXJ);
    			sheetXZ.addCell(lableXZ);
    			sheetHZ.addCell(lableHZ);
    			sheetSJ.addCell(lableSJ);
    			sheetQJ.addCell(lableQJ);
    			sheetYJ.addCell(lableYJ);
    			sheetGJ.addCell(lableGJ);
    //			sheet.setColumnView(11, cellView);
    			lableXJ = new Label(columnXJ++, 0, "商户新址", titleFormat);
    			lableXZ = new Label(columnXZ++, 0, "商户新址", titleFormat);
    			lableHZ = new Label(columnHZ++, 0, "商户新址", titleFormat);
    			lableSJ = new Label(columnSJ++, 0, "商户新址", titleFormat);
    			lableQJ = new Label(columnQJ++, 0, "商户新址", titleFormat);
    			lableYJ = new Label(columnYJ++, 0, "商户新址", titleFormat);
    			lableGJ = new Label(columnGJ++, 0, "商户新址", titleFormat);
    			sheetXJ.addCell(lableXJ);
    			sheetXZ.addCell(lableXZ);
    			sheetHZ.addCell(lableHZ);
    			sheetSJ.addCell(lableSJ);
    			sheetQJ.addCell(lableQJ);
    			sheetYJ.addCell(lableYJ);
    			sheetGJ.addCell(lableGJ);
    //			sheet.setColumnView(12, cellView);
    			lableXJ = new Label(columnXJ++, 0, "商户联系人 ", titleFormat);
    			lableXZ = new Label(columnXZ++, 0, "商户联系人 ", titleFormat);
    			lableHZ = new Label(columnHZ++, 0, "商户联系人 ", titleFormat);
    			lableSJ = new Label(columnSJ++, 0, "商户联系人 ", titleFormat);
    			lableQJ = new Label(columnQJ++, 0, "商户联系人 ", titleFormat);
    			lableYJ = new Label(columnYJ++, 0, "商户联系人 ", titleFormat);
    			lableGJ = new Label(columnGJ++, 0, "商户联系人 ", titleFormat);
    			sheetXJ.addCell(lableXJ);
    			sheetXZ.addCell(lableXZ);
    			sheetHZ.addCell(lableHZ);
    			sheetSJ.addCell(lableSJ);
    			sheetQJ.addCell(lableQJ);
    			sheetYJ.addCell(lableYJ);
    			sheetGJ.addCell(lableGJ);
    //			sheet.setColumnView(13, cellView);
    			lableXJ = new Label(columnXJ++, 0, "商户联系人电话", titleFormat);
    			lableXZ = new Label(columnXZ++, 0, "商户联系人电话", titleFormat);
    			lableHZ = new Label(columnHZ++, 0, "商户联系人电话", titleFormat);
    			lableSJ = new Label(columnSJ++, 0, "商户联系人电话", titleFormat);
    			lableQJ = new Label(columnQJ++, 0, "商户联系人电话", titleFormat);
    			lableYJ = new Label(columnYJ++, 0, "商户联系人电话", titleFormat);
    			lableGJ = new Label(columnGJ++, 0, "商户联系人电话", titleFormat);
    			sheetXJ.addCell(lableXJ);
    			sheetXZ.addCell(lableXZ);
    			sheetHZ.addCell(lableHZ);
    			sheetSJ.addCell(lableSJ);
    			sheetQJ.addCell(lableQJ);
    			sheetYJ.addCell(lableYJ);
    			sheetGJ.addCell(lableGJ);
    //			sheet.setColumnView(14, cellView);
    			lableXJ = new Label(columnXJ++, 0, "开户日期", titleFormat);
    			lableXZ = new Label(columnXZ++, 0, "开户日期", titleFormat);
    			lableHZ = new Label(columnHZ++, 0, "开户日期", titleFormat);
    			lableSJ = new Label(columnSJ++, 0, "开户日期", titleFormat);
    			lableQJ = new Label(columnQJ++, 0, "开户日期", titleFormat);
    			lableYJ = new Label(columnYJ++, 0, "开户日期", titleFormat);
    			lableGJ = new Label(columnGJ++, 0, "开户日期", titleFormat);
    			sheetXJ.addCell(lableXJ);
    			sheetXZ.addCell(lableXZ);
    			sheetHZ.addCell(lableHZ);
    			sheetSJ.addCell(lableSJ);
    			sheetQJ.addCell(lableQJ);
    			sheetYJ.addCell(lableYJ);
    			sheetGJ.addCell(lableGJ);
    //			sheet.setColumnView(15, cellView);
    			lableXJ = new Label(columnXJ++, 0, "安全模块号", titleFormat);
    			lableXZ = new Label(columnXZ++, 0, "安全模块号", titleFormat);
    			lableHZ = new Label(columnHZ++, 0, "安全模块号", titleFormat);
    			lableSJ = new Label(columnSJ++, 0, "安全模块号", titleFormat);
    			lableQJ = new Label(columnQJ++, 0, "安全模块号", titleFormat);
    			lableYJ = new Label(columnYJ++, 0, "安全模块号", titleFormat);
    			lableGJ = new Label(columnGJ++, 0, "安全模块号", titleFormat);
    			sheetXJ.addCell(lableXJ);
    			sheetXZ.addCell(lableXZ);
    			sheetHZ.addCell(lableHZ);
    			sheetSJ.addCell(lableSJ);
    			sheetQJ.addCell(lableQJ);
    			sheetYJ.addCell(lableYJ);
    			sheetGJ.addCell(lableGJ);
    //			sheet.setColumnView(16, cellView);
    			lableXJ = new Label(columnXJ++, 0, "终端序列号", titleFormat);
    			lableXZ = new Label(columnXZ++, 0, "终端序列号", titleFormat);
    			lableHZ = new Label(columnHZ++, 0, "终端序列号", titleFormat);
    			lableSJ = new Label(columnSJ++, 0, "终端序列号", titleFormat);
    			lableQJ = new Label(columnQJ++, 0, "终端序列号", titleFormat);
    			lableYJ = new Label(columnYJ++, 0, "终端序列号", titleFormat);
    			lableGJ = new Label(columnGJ++, 0, "终端序列号", titleFormat);
    			sheetXJ.addCell(lableXJ);
    			sheetXZ.addCell(lableXZ);
    			sheetHZ.addCell(lableHZ);
    			sheetSJ.addCell(lableSJ);
    			sheetQJ.addCell(lableQJ);
    			sheetYJ.addCell(lableYJ);
    			sheetGJ.addCell(lableGJ);
    //			sheet.setColumnView(17, cellView);
    			lableXJ = new Label(columnXJ++, 0, "绑定固定电话号码", titleFormat);
    			lableXZ = new Label(columnXZ++, 0, "绑定固定电话号码", titleFormat);
    			lableHZ = new Label(columnHZ++, 0, "绑定固定电话号码", titleFormat);
    			lableSJ = new Label(columnSJ++, 0, "绑定固定电话号码", titleFormat);
    			lableQJ = new Label(columnQJ++, 0, "绑定固定电话号码", titleFormat);
    			lableYJ = new Label(columnYJ++, 0, "绑定固定电话号码", titleFormat);
    			lableGJ = new Label(columnGJ++, 0, "绑定固定电话号码", titleFormat);
    			sheetXJ.addCell(lableXJ);
    			sheetXZ.addCell(lableXZ);
    			sheetHZ.addCell(lableHZ);
    			sheetSJ.addCell(lableSJ);
    			sheetQJ.addCell(lableQJ);
    			sheetYJ.addCell(lableYJ);
    			sheetGJ.addCell(lableGJ);
    //			sheet.setColumnView(18, cellView);
    			lableXJ = new Label(columnXJ++, 0, "设备类型", titleFormat);
    			lableXZ = new Label(columnXZ++, 0, "设备类型", titleFormat);
    			lableHZ = new Label(columnHZ++, 0, "设备类型", titleFormat);
    			lableSJ = new Label(columnSJ++, 0, "设备类型", titleFormat);
    			lableQJ = new Label(columnQJ++, 0, "设备类型", titleFormat);
    			lableYJ = new Label(columnYJ++, 0, "设备类型", titleFormat);
    			lableGJ = new Label(columnGJ++, 0, "设备类型", titleFormat);
    			sheetXJ.addCell(lableXJ);
    			sheetXZ.addCell(lableXZ);
    			sheetHZ.addCell(lableHZ);
    			sheetSJ.addCell(lableSJ);
    			sheetQJ.addCell(lableQJ);
    			sheetYJ.addCell(lableYJ);
    			sheetGJ.addCell(lableGJ);
    //			sheet.setColumnView(19, cellView);
    			lableXJ = new Label(columnXJ++, 0, "开户行", titleFormat);
    			lableXZ = new Label(columnXZ++, 0, "开户行", titleFormat);
    			lableHZ = new Label(columnHZ++, 0, "开户行", titleFormat);
    			lableSJ = new Label(columnSJ++, 0, "开户行", titleFormat);
    			lableQJ = new Label(columnQJ++, 0, "开户行", titleFormat);
    			lableYJ = new Label(columnYJ++, 0, "开户行", titleFormat);
    			lableGJ = new Label(columnGJ++, 0, "开户行", titleFormat);
    			sheetXJ.addCell(lableXJ);
    			sheetXZ.addCell(lableXZ);
    			sheetHZ.addCell(lableHZ);
    			sheetSJ.addCell(lableSJ);
    			sheetQJ.addCell(lableQJ);
    			sheetYJ.addCell(lableYJ);
    			sheetGJ.addCell(lableGJ);
    //			sheet.setColumnView(20, cellView);
    			lableXJ = new Label(columnXJ++, 0, "确认日期", titleFormat);
    			lableXZ = new Label(columnXZ++, 0, "确认日期", titleFormat);
    			lableHZ = new Label(columnHZ++, 0, "确认日期", titleFormat);
    			lableSJ = new Label(columnSJ++, 0, "确认日期", titleFormat);
    			lableQJ = new Label(columnQJ++, 0, "确认日期", titleFormat);
    			lableYJ = new Label(columnYJ++, 0, "确认日期", titleFormat);
    			lableGJ = new Label(columnGJ++, 0, "确认日期", titleFormat);
    			sheetXJ.addCell(lableXJ);
    			sheetXZ.addCell(lableXZ);
    			sheetHZ.addCell(lableHZ);
    			sheetSJ.addCell(lableSJ);
    			sheetQJ.addCell(lableQJ);
    			sheetYJ.addCell(lableYJ);
    			sheetGJ.addCell(lableGJ);
    
    
    			
    			CellView cellView = new CellView();  
    			cellView.setAutosize(true); //设置自动大小
    			if (result != null && result.size() > 0) {
    //				int row = 0;
    //				int firstRow = 1;
    				int rowXJ=1,rowXZ=1,rowHZ=1,rowSJ=1,rowQJ=1,rowYJ=1,rowGJ=1;
    				String type="";
    				boolean isEnd = true;
    				for (int i = 0; i < result.size(); i++) {
    					columnXJ = 4;
    					columnXZ = 0;
    					columnHZ = 0;
    					columnSJ = 0;
    					columnQJ = 0;
    					columnYJ = 0;
    					columnGJ = 0;
    					Object[] obj = (Object[]) result.get(i);
    					String deviceStatus = (String) obj[0];
    					String bussManagerName = (String) obj[4];
    					String orderType = (String) obj[20];
    					String workType = (String) obj[21];
    					if(orderType.contains(",")){
    						type = "综合工单";
    					}else{
    						type=orderType.equals("14")?PosConst.posServiceOrderType.get(workType):PosConst.posOrderType.get(orderType);
    					}
    					Manager manager = PosManagerHelpler.getManagerById(bussManagerName);
    					String deviceStatus1 = PosConst.deviceStatus.get(deviceStatus);
    					if(orderType.contains("15")){//巡检
    						sheetXJ=getSheet(sheetXJ,lableXJ,columnXJ,rowXJ,obj);
    						rowXJ++;
    					}
    					if(orderType.contains("10")){//新装
    						sheetXZ=getSheet(sheetXZ,lableXZ,columnXZ,rowXZ,obj);
    						rowXZ++;
    					}
    					if(orderType.contains("11")){//换装
    						sheetHZ=getSheet(sheetHZ,lableHZ,columnHZ,rowHZ,obj);
    						rowHZ++;
    					}
    					if(orderType.contains("14") && workType.contains("3")){//程序升级
    						sheetSJ=getSheet(sheetSJ,lableSJ,columnSJ,rowSJ,obj);
    						rowSJ++;
    					}
    					if("18".equals(deviceStatus)){//切机
    						sheetQJ=getSheet(sheetQJ,lableQJ,columnQJ,rowQJ,obj);
    						rowQJ++;
    					}
    					if("07".equals(deviceStatus)){//移机
    						sheetYJ=getSheet(sheetYJ,lableYJ,columnYJ,rowYJ,obj);
    						rowYJ++;
    					}
    					if("22".equals(deviceStatus)){//跟进
    						sheetGJ=getSheet(sheetGJ,lableGJ,columnGJ,rowGJ,obj);
    						rowGJ++;
    					}
    					
    //					row++;
    //					firstRow++;
    				}
    			}
    			
    		} catch (Exception ex) {
    			ex.printStackTrace();
    			throw ex;
    		} finally {
    			if (workbook != null) {
    				workbook.write();
    				workbook.close();
    			}
    			if (os != null) {
    				os.close();
    			}
    		}
    	}
    	
    	public static WritableSheet getSheet(WritableSheet sheet,Label lable,int column,int row,Object[] obj) throws RowsExceededException, WriteException{
    		String deviceStatus = (String) obj[0];
    		String statusExplain = (String) obj[1];
    		String bussOrgId = (String) obj[2];
    		String execOperNo = (String) obj[3];
    		String bussManagerName = (String) obj[4];
    		String storeNo = (String) obj[5];
    		String terminalNo = (String) obj[6];
    		String companyFullName = (String) obj[7];
    		String companyDoor = (String) obj[8];
    		String address = (String) obj[9];
    		String businessName = (String) obj[10];
    		String businessMobile = (String) obj[11];
    		String registrationDate = (String) obj[12];
    		String securityModuleCode = (String) obj[13];
    		String deviceSn = (String) obj[14];
    		String businessPhone = (String) obj[15];
    		String deviceType = (String) obj[16];
    		String openingBank = (String) obj[17];
    		String orderTime = (String) obj[18];
    		String orderStatus = (String) obj[19];
    		String orderType = (String) obj[20];
    		String workType = (String) obj[21];
    		String managerName = (String)obj[22];
    		String managerPhone = (String)obj[23];
    		String remark = (String)obj[24];
    		
    		String type = "";
    		if(orderType.contains(",")){
    			type = "综合工单";
    		}else{
    			type=orderType.equals("14")?PosConst.posServiceOrderType.get(workType):PosConst.posOrderType.get(orderType);
    		}
    		Manager manager = PosManagerHelpler.getManagerById(bussManagerName);
    		String deviceStatus1 = PosConst.deviceStatus.get(deviceStatus);
    		
    		//设备状态	
    	    sheet.setColumnView(column, 10);//根据内容固定列宽
    		lable = new Label(column++, row, (deviceStatus1==null?"":deviceStatus1), detFormat);
    		sheet.addCell(lable);
    		//工单备注	
    		sheet.setColumnView(column, 50);
    		lable = new Label(column++, row, remark, detFormat);
    		sheet.addCell(lable);
    		//区域	
    		sheet.setColumnView(column, 10);
    		lable = new Label(column++, row, StoreHelper.getOrgNameByOrgId(bussOrgId), detFormat);
    		sheet.addCell(lable);
    		//确认人	
    		sheet.setColumnView(column, 10);
    		lable = new Label(column++, row,PosManagerHelpler.getManagerNameById(execOperNo), detFormat);
    		sheet.addCell(lable);
    		//客户经理	
    		sheet.setColumnView(column, 10);
    		lable = new Label(column++, row,managerName, detFormat);
    		sheet.addCell(lable);
    		//客户经理电话	
    		sheet.setColumnView(column, 15);
    		lable = new Label(column++, row, managerPhone, detFormat);
    		sheet.addCell(lable);
    		//商户代码 
    		sheet.setColumnView(column, 25);
    		lable = new Label(column++, row, storeNo, detFormat);
    		sheet.addCell(lable);
    		//终端号	
    		sheet.setColumnView(column, 15);
    		lable = new Label(column++, row, terminalNo, detFormat);
    		sheet.addCell(lable);
    		//商户中文名称	
    		sheet.setColumnView(column, 50);
    		lable = new Label(column++, row, companyFullName, detFormat);
    		sheet.addCell(lable);
    		//经营地址	
    		sheet.setColumnView(column, 50);
    		lable = new Label(column++, row, companyDoor, detFormat);
    		sheet.addCell(lable);
    		//商户新址	
    		sheet.setColumnView(column, 50);
    		lable = new Label(column++, row, address, detFormat);
    		sheet.addCell(lable);
    		//商户联系人 	
    		sheet.setColumnView(column, 10);
    		lable = new Label(column++, row, businessName,detFormat);
    		sheet.addCell(lable);
    		//商户联系人电话	
    		sheet.setColumnView(column, 15);
    		lable = new Label(column++, row, businessMobile, detFormat);
    		sheet.addCell(lable);
    		//开户日期	
    		sheet.setColumnView(column, 20);
    		lable = new Label(column++, row, registrationDate, detFormat);
    		sheet.addCell(lable);
    		//安全模块号	
    		sheet.setColumnView(column, 25);
    		lable = new Label(column++, row, securityModuleCode, detFormat);
    		sheet.addCell(lable);
    		//终端序列号	
    		sheet.setColumnView(column, 20);
    		lable = new Label(column++, row, deviceSn,detFormat);
    		sheet.addCell(lable);
    		//绑定固定电话号码	
    		sheet.setColumnView(column, 15);
    		lable = new Label(column++, row, businessPhone,detFormat);
    		sheet.addCell(lable);
    		//设备类型	
    		sheet.setColumnView(column, 10);
    		lable = new Label(column++, row, (deviceType.equals("0")?"商务通":(deviceType.equals("1")?"POS":"")),detFormat);
    		sheet.addCell(lable);
    		//开户行	
    		sheet.setColumnView(column, 30);
    		lable = new Label(column++, row, openingBank,detFormat);
    		sheet.addCell(lable);
    		//确认日期
    		sheet.setColumnView(column, 20);
    		lable = new Label(column++, row, orderTime,detFormat);
    		sheet.addCell(lable);
    		
    //		lable = new Label(column++, row, PosConst.posOrderStatus.get(orderStatus),detFormat);
    //		sheet.addCell(lable);
    //		lable = new Label(column++, row, type,detFormat);
    //		sheet.addCell(lable);
    //		lable = new Label(column++, row, statusExplain, detFormat);
    //		sheet.addCell(lable);
    		
    		return sheet;
    	}


    展开全文
  • -- 导出EXCEL --> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.17</version> </dependency> &

    1.maven依赖

    <!-- 导出EXCEL -->
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi</artifactId>
                <version>3.17</version>
            </dependency>
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi-ooxml</artifactId>
                <version>3.17</version>
            </dependency>
            <!-- 导出EXCEL -->
            <!-- word读取 -->
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi-scratchpad</artifactId>
                <version>3.17</version>
            </dependency>
    
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>ooxml-schemas</artifactId>
                <version>1.4</version>
            </dependency>
            <!-- word读取 -->
    

    二导出word 横版:3要点

    1. ooxml-schemas 依赖
           <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>ooxml-schemas</artifactId>
                <version>1.4</version>
            </dependency>
    
    1. 设置 :横屏板式
    XWPFDocument doc = new XWPFDocument();
            CTDocument1 document = doc.getDocument();
            CTBody body = document.getBody();
            if (!body.isSetSectPr()) {
                body.addNewSectPr();
            }
            CTSectPr section = body.getSectPr();
    
            if (!section.isSetPgSz()) {
                section.addNewPgSz();
            }
            //1. 设置页面大小  当前A4大小
            CTPageSz pageSize = section.getPgSz();
            //1.1 必须要设置下面两个参数,否则整个的代码是无效的
            pageSize.setW(BigInteger.valueOf(16840));
            pageSize.setH(BigInteger.valueOf(11907));
            pageSize.setOrient(STPageOrientation.LANDSCAPE);
    

    3.设置excel表格布局方式

    		//4 表格内容
            XWPFTable comTable = doc.createTable();
            //4.1表格宽度为指定宽度:STTblWidth.DXA 1
            CTTblPr ctTblPr = comTable.getCTTbl().addNewTblPr();
            CTTblWidth comTableWidth = ctTblPr.addNewTblW();
            comTableWidth.setType(STTblWidth.DXA);
            comTableWidth.setW(BigInteger.valueOf(16000));
    
            //4.1.2设置布局为固定不便方式
            CTTblLayoutType ctTblLayoutType = ctTblPr.isSetTblLayout()?ctTblPr.getTblLayout():ctTblPr.addNewTblLayout();
            ctTblLayoutType.setType(STTblLayoutType.FIXED);
    

    网络通用下下载横版word 横版excel 表格:

    import org.apache.commons.lang.StringUtils;
    import org.apache.poi.xwpf.usermodel.*;
    import org.openxmlformats.schemas.wordprocessingml.x2006.main.*;
    
    
    import javax.servlet.http.HttpServletResponse;
    import java.io.FileOutputStream;
    import java.io.IOException;
    import java.io.OutputStream;
    import java.lang.reflect.Array;
    import java.math.BigInteger;
    import java.net.URLEncoder;
    import java.util.ArrayList;
    import java.util.Arrays;
    import java.util.Collections;
    import java.util.List;
    import java.util.regex.Matcher;
    import java.util.regex.Pattern;
    
    
    public class ExportWord {
    
    
        /**
         * 下载横屏word的excel表格
         *
         * @param filename:文件名
         * @param titleStr:标题名
         * @param description:居中描述:可空
         * @param value_columns:列明
         * @param dataList:数据
         * @param colWidths:列宽度,可空
         * @param response:HttpServletResponse
         * @throws IOException
         */
        public static void downWordTransverse(String filename, String titleStr, String description, String[] value_columns, List<List<String>> dataList, Integer[] colWidths,ParagraphAlignment[] excelAlign, HttpServletResponse response) throws IOException {
    
            XWPFDocument doc = new XWPFDocument();
            CTDocument1 document = doc.getDocument();
            CTBody body = document.getBody();
            if (!body.isSetSectPr()) {
                body.addNewSectPr();
            }
            CTSectPr section = body.getSectPr();
    
            if (!section.isSetPgSz()) {
                section.addNewPgSz();
            }
            //1. 设置页面大小  当前A4大小
            CTPageSz pageSize = section.getPgSz();
            //1.1 必须要设置下面两个参数,否则整个的代码是无效的
            pageSize.setW(BigInteger.valueOf(16840));
            pageSize.setH(BigInteger.valueOf(11907));
            pageSize.setOrient(STPageOrientation.LANDSCAPE);
            //1.2 设置页面为窄边距
            setDocumentMargin(section,"420","720","420","720");
    
            //2. 添加标题
            XWPFParagraph titleParagraph = doc.createParagraph();
            //设置段落居中
            titleParagraph.setAlignment(ParagraphAlignment.CENTER);
    
            XWPFRun titleParagraphRun = titleParagraph.createRun();
            titleParagraphRun.setText(titleStr);
            titleParagraphRun.setColor("000000");
            titleParagraphRun.setFontFamily("方正小标宋简体");
            titleParagraphRun.setFontSize(22);
    
            if (!StringUtils.isEmpty(description)) {
                //3. 添加段落,描述
                XWPFParagraph dateParagraph = doc.createParagraph();
                //3.1 设置段落居中
                dateParagraph.setAlignment(ParagraphAlignment.CENTER);
    
                //3.2设置标题居中
                XWPFRun dateParagraphRun = dateParagraph.createRun();
                dateParagraphRun.setText(description);
                dateParagraphRun.setColor("000000");
                dateParagraphRun.setFontFamily("仿宋");
                dateParagraphRun.setFontSize(12);
            }
    
            //4.表格
            //表格内容
            XWPFTableCell cell;
            CTTcPr cellPr;
            CTTblWidth cellw;
            XWPFTable comTable = doc.createTable();
            //4.1表格宽度为指定宽度:STTblWidth.DXA 1
            CTTblPr ctTblPr = comTable.getCTTbl().addNewTblPr();
            CTTblWidth comTableWidth = ctTblPr.addNewTblW();
            comTableWidth.setType(STTblWidth.DXA);
            comTableWidth.setW(BigInteger.valueOf(16000));
    
            //4.1.2设置布局为固定不便方式
            CTTblLayoutType ctTblLayoutType = ctTblPr.isSetTblLayout()?ctTblPr.getTblLayout():ctTblPr.addNewTblLayout();
            ctTblLayoutType.setType(STTblLayoutType.FIXED);
    
            //4.2 表头
            XWPFTableRow rowHead = comTable.getRow(0);
    
            //4.3设置表格首行单元格
            XWPFParagraph cellParagraph;
            XWPFRun cellParagraphRun;
            for (int i = 0; i < value_columns.length; i++) {
    
                if(i==0){
                    cell = rowHead.getCell(0);
                }else{
                    cell = rowHead.addNewTableCell();
                }
                cell.setVerticalAlignment(XWPFTableCell.XWPFVertAlign.CENTER);//垂直居中
                //设置单元格宽度
                cellPr = cell.getCTTc().addNewTcPr();
                cellw = cellPr.addNewTcW();
                if(colWidths!=null){
                    cellw.setType(STTblWidth.DXA);
                    cellw.setW(BigInteger.valueOf(colWidths[i]));
                }
    
                //设置单元格内容
                cellParagraph = cell.getParagraphs().get(0);
                cellParagraph.setAlignment(ParagraphAlignment.CENTER); //设置表头单元格居中
                cellParagraphRun = cellParagraph.createRun();
    
                cellParagraphRun.setFontFamily("黑体");
                cellParagraphRun.setFontSize(12); //设置表头单元格居中
                cellParagraphRun.setText(value_columns[i]);
            }
    
            int rows = dataList.size();
    
            for (int i = 0; i < rows; i++) {
                XWPFTableRow rowsContent = comTable.createRow();
                for (int j = 0; j < dataList.get(i).size(); j++) {
    
                    //设置单元格宽度
                    cell = rowsContent.getCell(j);
                    cellPr = cell.getCTTc().addNewTcPr();
                    cellw = cellPr.addNewTcW();
                    if(colWidths!=null){
                        cellw.setType(STTblWidth.DXA);
                        cellw.setW(BigInteger.valueOf(colWidths[j]));
                    }
                    cell.setVerticalAlignment(XWPFTableCell.XWPFVertAlign.CENTER);//垂直居中
                    //设置单元格内容
                    XWPFParagraph cellParagraphC = cell.getParagraphs().get(0);
                    if(excelAlign!=null){
                        cellParagraphC.setAlignment(excelAlign[j]);
                    }else{
                        cellParagraphC.setAlignment(ParagraphAlignment.LEFT);
                    }
    
    
                    String textContent = dataList.get(i).get(j);
                    if (textContent.indexOf("\n") == -1) {
                        XWPFRun cellParagraphRunC = cellParagraphC.createRun();
                        cellParagraphRunC.setFontFamily("仿宋");
                        cellParagraphRunC.setFontSize(12); //设置表格内容字号
                        cellParagraphRunC.setText(dataList.get(i).get(j) + ""); //单元格段落加载内容
                    } else {
                        String[] textContentArray = textContent.split("\n");
                        int m = 0;
                        for (String text : textContentArray) {
                            String regex = "^\\*\\*(.*?)\\*\\*";
                            Pattern pattern = Pattern.compile(regex);
                            Matcher matcher = pattern.matcher(text);
                            if(matcher.find()){
                                String contentF = matcher.group().replaceAll("\\*","");
                                XWPFRun cellParagraphRunC = cellParagraphC.createRun();
                                cellParagraphRunC.setFontFamily("仿宋");
                                cellParagraphRunC.setFontSize(12); //设置表格内容字号
                                cellParagraphRunC.setText(contentF);//设置该段内容
                                cellParagraphRunC.setBold(true);//设置加粗
    
                                //取出剩余的内容
                                text = text.replace(matcher.group(),"");
                            }
    
                            m++;
                            XWPFRun cellParagraphRunC = cellParagraphC.createRun();
                            cellParagraphRunC.setFontFamily("仿宋");
                            cellParagraphRunC.setFontSize(12); //设置表格内容字号
                            cellParagraphRunC.setText(text);
                            if(m!=textContentArray.length){
                                cellParagraphRunC.addBreak(BreakType.TEXT_WRAPPING);
                            }
                        }
                    }
                }
            }
    
            OutputStream out = null;
            try {
                response.setContentType("application/force-download");// 设置强制下载不打开
                response.addHeader("Content-Disposition", "attachment;fileName=" + URLEncoder.encode(filename, "utf-8") + ".docx");// 设置文件名
                out = response.getOutputStream();
                doc.write(out);
                out.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    
        /**
         * 设置页边距 (word中1厘米约等于567)
         * @param section
         * @param left
         * @param top
         * @param right
         * @param bottom
         */
        public static void setDocumentMargin(CTSectPr section, String left,String top, String right, String bottom) {
            if(!section.isSetPgMar()){
                section.addNewPgMar();
            }
            CTPageMar ctpagemar = section.getPgMar();
            if (!Util.isNull(left)) {
                ctpagemar.setLeft(new BigInteger(left));
            }
            if (!Util.isNull(top)) {
                ctpagemar.setTop(new BigInteger(top));
            }
            if (!Util.isNull(right)) {
                ctpagemar.setRight(new BigInteger(right));
            }
            if (!Util.isNull(bottom)) {
                ctpagemar.setBottom(new BigInteger(bottom));
            }
        }
    }
    

    三、工具类使用方式及效果

    String titleStr = "标题";
    String description = "描述";
    String[] value_columns = {"列1", "列2", "列3","列4","列5","列6","列7","列8"};
    List<List<String>> dataList = new ArrayList<>();//导出的数据
    Integer[] colWidths = {600,1700,2200,700,900,1500,7350,1050};//导出的excel宽度excel(扣除边距剩余共16000宽)
    ParagraphAlignment[] excelAlign = {ParagraphAlignment.CENTER,ParagraphAlignment.LEFT,ParagraphAlignment.LEFT,ParagraphAlignment.CENTER
    		,ParagraphAlignment.CENTER,ParagraphAlignment.LEFT,ParagraphAlignment.LEFT,ParagraphAlignment.CENTER};//设置水平方向
    		
    downWordTransverse(titleStr,titleStr,description,value_columns,dataList,colWidths,excelAlign,response);
    
    

    在这里插入图片描述

    展开全文
  • excel 固定前几行

    2021-06-15 11:31:27
    问题描述 原本只需要固定第一行,后面因为需要,想固定前2行,百度了一下,一般解决办法,我试了一下,不能用(我的列数比较多,一页不能显示完全) 解决办法 这办法是偶然间发现的。...而且不受页面宽度的限制。 ...

    问题描述

    原本只需要固定第一行,后面因为需要,想固定前2行,百度了一下,一般解决办法,我试了一下,不能用(我的列数比较多,一页不能显示完全)

    解决办法

    这办法是偶然间发现的。

    1、固定第一行

    视图 》 冻结窗口 》冻结首行
    在这里插入图片描述
    此时,第一行固定,不会随滚轮一起滑动了。
    在这里插入图片描述

    2、选中第一行,点击插入

    在这里插入图片描述
    插入成功后原来的第一行上面多了一行,可以编写内容。

    确认

    滑动一下滚轮,发现上面两行都是固定的。而且不受页面宽度的限制。

    展开全文
  • <p>My end result is to have a fixed length record created from either a csv of excel file using ...$data is formatted as ... </div>
  • 如题,Excel台账如何打印宽度固定,长度不固定。内容要打印到一张很长的纸上。网站有说卷筒纸的。但是不知道如何打印,像超市打印购物单、中移动打印话费清单一样的。各位大侠请不吝赐教。
  • 在一个excel 某一个sheet中,拷贝过来的图片 想要打印,但是超出了范围,怎么能过vba 达到设定固定宽度的图片,然后打印,查了一些代码,但是不太懂
  • Ctl.Data为CSV(包括其以制表符分隔,管道分隔等的变体),固定宽度和XLSX文件提供解析器。 尽管我们更喜欢诸如Web服务之类的高科技解决方案,但CSV之类的技术是最简单的入门格式之一,并且已广泛地嵌入到许多行业...
  • // 固定首行,下拉时实现首行固定不动 sheet.createFreezePane( 0, 1, 0, 1 ); // 列宽自适应 // outputList.get(0).size()为首行的列数,根据首行列数循环设置每一列的宽度 for (int columnIndex = 0; columnIndex ...
    // 新建sheet同时往sheet加入数据后,进行列宽设置,代码如下:
    
    // 固定首行,下拉时实现首行固定不动
    sheet.createFreezePane( 0, 1, 0, 1 );
    // 列宽自适应
    // outputList.get(0).size()为首行的列数,根据首行列数循环设置每一列的宽度
    for (int columnIndex = 0; columnIndex < outputList.get(0).size(); columnIndex++) {
    	int columnWidth = sheet.getColumnWidth(columnIndex) / 256;
    	for (int rowIndex = 0; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
    		XSSFRow currentRow;
    		// 当前行未被使用过
    		if (sheet.getRow(rowIndex) == null) {
    			currentRow = sheet.createRow(rowIndex);
    		} else {
    			currentRow = sheet.getRow(rowIndex);
    		}
    		if (currentRow.getCell(columnIndex) != null) {
    			XSSFCell currentCell = currentRow.getCell(columnIndex);
    			if (currentCell.getCellType() == XSSFCell.CELL_TYPE_STRING) {
    				int length = 0;
    				try {
    					length = currentCell.getStringCellValue().getBytes().length;
    				} catch (Exception e) {
    					e.printStackTrace();
    				}
    				if (columnWidth < length) {
    					columnWidth = length;
    				}
    			}
    		}
    	}
    
    	sheet.setColumnWidth(columnIndex, (columnWidth) * 256);
    }

    效果如下

     

    展开全文
  • 打开用户指定的文本文件,根据标题确定数据宽度,并将记录自动保存为 Excel 文件,文件位于文本文件所在目录,文件名相同,后缀以 xlsx 结尾。 压缩文件中包含源码和已编译好的exe文件,直接运行需要 netfx4.0 以上...
  • Excel

    2016-03-11 12:48:35
    首先在txt中两列数据之间必须有分隔(空格、逗号、分号等等)方法如下: 有分隔符的情况:数据--分列--分隔符好--选择对应的分隔符。...无分隔符的情况:数据--分列--固定宽度--在适当的位置添加分隔线。
  • excel_style.js

    2020-04-18 10:41:10
    前端开发需要把table导出为Excel时,可自定义Excel样式,根据xlsx-style改写,包括以下功能:多级表头,合计行,单元格可设置自动宽度或固定宽度,合并单元格,隐藏表头等。
  • Excel功能介绍

    2020-05-26 17:08:31
    选择分隔符或固定宽度 选择分隔符号 选择各列的格式 选择导入位置 数据菜单下的分列功能 类似于导入时的固定宽度 例子: 从身份证号中提取年月日 单元格默认格式是常规,即:你输入的是文本就是文本,输入
  • excel基础操作

    2021-06-09 08:53:55
    如何调取不在功能区中的命令 文件--选项--自定义功能区--左侧:不在功能区中的命令 右侧:新建组(重命名)--添加 ... 选择分隔符-固定宽度 一共三步 选择数据放置位置 数据菜单下:分列功能 ...
  • excel基础常用操作

    2021-06-20 18:45:11
    选择分隔符/固定宽度 选择数据放置的位置 数据菜单下:分列功能 类似于导入数据时的固定宽度 如何改变单元格行高列宽 注意:改变是一整列,一整行一起改变 2、自WEB提取数据 点击自WEB 流程 输入网址—
  • 仿excel可滚动表格代码,表格宽度100%,列宽度设置,css table表格头部固定滚动。
  • Excel的基础操作

    2021-06-08 20:10:14
    1.如何调取不在功能区中的...a、选择分隔符/固定宽度 b、一共三步 c、选择数据放置的位置 (3)数据菜单下:分列功能 类似于导入数据时的固定宽度 (4)如何改变单元格行高列宽 注意:改变的是一整列,一整行一起改变 3
  • 第一步,点击《自文本》,选择一个想要导入的...(如何找自文本命令 查看这篇博客获取不在功能区的命令) 如果选择用 分割符号分割: ... 如果选择 固定宽度分割 进入到这个页面 然后点击下一步 -》完成 就可以了 ...
  • 选择分隔符/固定宽度 一共三步 选择数据放置的位置 数据菜单下:分裂功能 类似导入数据时的固定宽度 如何改变单元格行高列宽 注意:改变的是一整列,一整行,一起改变 自Access数据库 直接导入 自csv文件 ...
  • 第四章 电子表格EXCEL习题答案 一判断题正确填A错误填B 1.Excel电子表格Excel97中...3.EXCEL单元格的宽度固定的为8个字符宽 4Excel没有自动填充和自动保存的功能 5Excel不支持 Internet 6Excel单元格中的数据可以水
  • Excel 电子表格 Excel97 中常用工具栏中的格式刷只能复制数据的格式 不能复制数据 个人收集整理 勿做商业用途 2.Excel 工作表进行保存时只能存为后缀为 .XLS 的文件 3.EXCEL单元格的宽度固定的为 8 个字符宽 4 ...
  • 仿excel可滚动表格代码,表格宽度100%,列宽度设置,css table表格头部固定滚动。
  • excel小技巧-分列

    2019-10-07 01:58:41
    分列:1.选择要分列的单元格;...分列,会弹出文本分列向导;3.文件类型选择分隔符号;4.下一步,勾选要分隔的符号...注意:选择分隔符号和固定宽度的差别在于,分隔符号是会把当做分隔的符号去除,而固定宽度则不会去...
  • Excel 文本转日期

    千次阅读 2020-07-29 10:16:56
    数据 →\to→ 分列 →\to→ 固定宽度 →\to→ 下一步 →\to→ 日期 (YMD) →\to→ 完成
  • Excel应用技巧:分列的妙用

    千次阅读 2017-12-23 00:00:00
    大家好!我是xyz,今天给大家分享excel中一个非常好用的功能:...按固定宽度拆分按固定宽度在身份证号中提取生日,不用公式,快速搞定。按关键字拆分这个应用估计比较少人使用,汉字也可以当做分隔符运用。将数字转换成
  • excel 自TXT中获取数据

    2021-06-19 11:27:21
    将txt类型文件导入到excel 自文本->导入数据->文本导入向导 选择分割符 一共三步 选择数据放置的位置 ...类似于导入数据时的固定宽度 如何改变单元格行高列宽 注意:改变是一整列,一整行一起改变 ...
  • Excel 文本内容拆分

    2019-10-04 17:13:45
    固定宽度 2.数据预览点击下一步 3.最后分好的数据就在 归去来兮,田园将芜胡不归?既自以心为形役,奚惆怅而独悲?悟已往之不谏,知来者之可追。实迷途其未远,觉今是而昨非。舟遥遥以轻飏,风飘飘而吹衣。问...
  • 使用NPOI2 1 3 MyXls对Excel进行导入导出操作 NOPI导入导出均支持 xls与 xlsx格式 支持数据自适应单元格宽度 固定表头 设置行高等一系列基本设置 支持数据类型识别等 注意:myxls仅支持 xls格式导出 没有导入 其中...
  • 我收到了一份非常乱的数据,需要把地块名(字母+数字+符号“-”)和植被(中文)分开,不能用excel的分列功能中的固定宽度和字符分隔来实现 参考知乎用户https://zhuanlan.zhihu.com/p/89321644 首先要在excel中...
  • 功能类似于导入时候的固定宽度例子:从身份证号码中提取到出生日期如何改变单元格的行高和列宽自web获取数据自csv中获取数据自Access数据库Excel中的如何数据清洗与转换(重点) 可视化工具介绍 让我们简单了解一下...

空空如也

空空如也

1 2 3 4 5 ... 11
收藏数 220
精华内容 88
关键字:

excel固定宽度