excel导出bbs数据

最新推荐文章于 2024-08-07 15:13:16 发布

wtfpewfn

最新推荐文章于 2024-08-07 15:13:16 发布

阅读量696

点赞数

分类专栏： PM

本文链接：https://blog.youkuaiyun.com/wtfpewfn/article/details/39642211

版权

PM 专栏收录该内容

2 篇文章

订阅专栏

一。前端页面：

$(function(){
	var parm = Util.getParm();
	var url = "../../base/report/butianInformationListReportAction.action?"+parm.ls_parm;
	$("#btn_qd").click(function(){
		var checkVal = $('input[name="exl"]:checked').val();
		url += "&banben="+checkVal;
		window.open(url);
		Util.closeWindow();
	});
	
	$("#btn_qx").click(function(){
		Util.closeWindow();
	});
});

二。strusts文件：Report-Struts2

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE struts PUBLIC
        "-//Apache Software Foundation//DTD Struts Configuration 2.3//EN"
        "http://struts.apache.org/dtds/struts-2.3.dtd">
<struts>
	<package name="base-pm-report" namespace="/base/report" extends="json-default">
      <action name="*ReportAction" method="{1}" class="com.sctf.pm.action.ReportAction">
          <result name="{1}" type="json"></result>
      </action>
    </package>
</struts>

三：Action类：ReportAction

/**
	  *<功能简述>
	  * 导出不填项目信息
	  *<功能详细描述>
	  * @return [参数说明]
	  * 
	  * @return String [返回类型说明]
	  * @exception throws [异常类型] [异常说明]
	  * @see [类、类#方法、类#成员]
	 */
	public String butianInformationList(){
		try {
			Map parm = getQueryMap();
			User user = Util.getInstance().getUserInfo(getRequest());
			String str = user.getAuthStr();
		    if(str.indexOf("pm_selectAll") < 0){//如果有查询全部的权限
		    	parm.put("depart", user.getOrgName());
	        }
//			if(!"9".equals(user.getOrgID())){
//				parm.put("depart", user.getOrgName());
//			}
			//parm.put("gj_sch", parm.get("gjsch"));
			HttpServletResponse response = ServletActionContext.getResponse();
			proList = iproservice.queryUpdateProject(parm);
			ClassPathResource cpr = null;
			OutputStream out = null;
            if("2007".equals(parm.get("banben").toString())){
                cpr = new ClassPathResource("/report/ImportTemplate_btxx.xlsx");
            }else{
                cpr = new ClassPathResource("/report/ImportTemplate_btxx.xls");
            }
			if("2007".equals(parm.get("banben").toString())){
				out = queryOutStream(response, "项目补填信息"+StringUtil.getNowTime("yyyy-MM-dd")+".xlsx");
			}else{
				out = queryOutStream(response, "项目补填信息"+StringUtil.getNowTime("yyyy-MM-dd")+".xls");
			}
			File file = cpr.getFile();
			String fileExtension = file.getName();
			List<List<Map>> vals = new ArrayList<List<Map>>();
			vals.add(0, proList);	//导出的补填基本信息
            List<Map> bssList = iproservice.querybssList(parm); //Bss业务号码
            vals.add(1,bssList);
            List<Map> pmsList = pcs.selectPms(parm); //PMS项目编码
            vals.add(2,pmsList);
			if(fileExtension.indexOf(".xlsx") > -1){
				ExportExcel2007 ex = new ExportExcel2007();
//				ex.createExcel(file,out,proList,0,null,1);
				ex.exportExcel(file, out, vals, null);
				excelVersion = "2007";
			}else if(fileExtension.indexOf(".xls") > -1){
				ExportExcel2003 ex = new ExportExcel2003();
//				ex.createExcel(file,out,proList,0,null,1);
				ex.exportExcel(file, out, vals, null);
				excelVersion = "2003";
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

/**
	 * 导出补填信息
	  *<功能简述>
	  *<功能详细描述>
	  * @param templet
	  * @param out
	  * @param dataSet
	  * @param 'sheetindex
	  * @param style
	  * @param 'isSimple [参数说明] 如果为1 插入简单模板数据，如果为0插入复杂模板数据
	  * 
	  * @return void [返回类型说明]
	  * @exception throws [异常类型] [异常说明]
	  * @see [类、类#方法、类#成员]
	 */
	public static void exportExcel(File templet,OutputStream out,List<List<Map>> dataSet,XSSFCellStyle style){
		FileInputStream fis,configfis;
		try {
			fis = new FileInputStream(templet);
			//如果配置文件有，那么按照配置文件写入，如果没有，那么自动遍历每行找到空行和表头
			XSSFWorkbook workbook = new XSSFWorkbook(fis);
            User user = com.sctf.core.util.Util.getInstance().getUserInfo(ServletActionContext.getRequest());
            boolean isJhb = false;  //是否有计划部权限
            boolean isHtb = false;  //是否有行拓部权限
            boolean isWjb = false;  //是否有网建部权限
            if(user.getAuthStr().indexOf("pm_selectAll") != -1){
                isJhb = true;
                isHtb = true;
                isWjb = true;
            }if(user.getAuthStr().indexOf("jk_jhb_update") != -1){
                isJhb = true;
            }if(user.getAuthStr().indexOf("jk_htb_update") != -1){
                isHtb = true;
            }if(user.getAuthStr().indexOf("jk_wjb_update") != -1){
                isWjb = true;
            }
			XSSFSheet sheet;
            if(isHtb == true){  //如果有行拓部权限，则导出所有（基本信息，BSS业务号码，PMS项目编码）
                for(int i = 0 ; i < dataSet.size() ; i++){
                    sheet = workbook.getSheetAt(i);
                    if(i == 0){
                        style = workbook.createCellStyle();	//只给补填信息添加颜色控制
                        style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
                        style.setFillForegroundColor(IndexedColors.AQUA.getIndex());	//设置前景色
//					style.setFillBackgroundColor(IndexedColors.BLUE.index);	//设置背景色
                    }else{
                        style = null;
                    }
                    insertDataSetWithBSS(sheet,dataSet.get(i),style);
                }
            }else if(isHtb == false){   //如果没有行拓部权限，则导出基本信息，PMS项目编码
                sheet = workbook.getSheetAt(0);
                style = workbook.createCellStyle();
                style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
                style.setFillForegroundColor(IndexedColors.AQUA.getIndex());
                insertDataSetWithBSS(sheet,dataSet.get(0),style);

                XSSFSheet pmsSheet = workbook.getSheetAt(2);
                insertDataSetWithBSS(pmsSheet,dataSet.get(2),null);

                workbook.removeSheetAt(1);  //无BSS业务号码导入权限，则删除BSS业务号码sheet页
            }


//            if(isJhb == true && isHtb == true){ //导出项目基本信息、BSS业务号码，PMS项目编码
//                for(int i = 0 ; i < dataSet.size() ; i++){
//                    sheet = workbook.getSheetAt(i);
//                    if(i == 0){
//                        style = workbook.createCellStyle();	//只给补填信息添加颜色控制
//                        style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
//                        style.setFillForegroundColor(IndexedColors.AQUA.getIndex());	//设置前景色
					style.setFillBackgroundColor(IndexedColors.BLUE.index);	//设置背景色
//                    }else{
//                        style = null;
//                    }
//                    insertDataSetWithBSS(sheet,dataSet.get(i),style);
//                }
//            }else if(isHtb == true && isJhb == false){    //导出项目基本信息，BSS业务号码
//                for(int j = 0 ; j < 2; j++){
//                    sheet = workbook.getSheetAt(j);
//                    if(j == 0){
//                        style = workbook.createCellStyle();
//                        style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
//                        style.setFillForegroundColor(IndexedColors.AQUA.getIndex());
//                    }else{
//                        style = null;
//                    }
//                    insertDataSetWithBSS(sheet,dataSet.get(j),style);
//
//                }
//                workbook.removeSheetAt(2);  //无PMS项目编码导入权限，则删除PMS项目编码sheet页
//            }else if(isJhb == true && isHtb == false){    //导出项目基本信息，PMS项目编码
//                sheet = workbook.getSheetAt(0);
//                style = workbook.createCellStyle();
//                style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
//                style.setFillForegroundColor(IndexedColors.AQUA.getIndex());
//                insertDataSetWithBSS(sheet,dataSet.get(0),style);
//
//                XSSFSheet pmsSheet = workbook.getSheetAt(2);
//                insertDataSetWithBSS(pmsSheet,dataSet.get(2),null);
//
//                workbook.removeSheetAt(1);  //无BSS业务号码导入权限，则删除BSS业务号码sheet页
//            }else if(isJhb == false && isHtb == false && isWjb == true){
//                sheet = workbook.getSheetAt(0);
//                style = workbook.createCellStyle();
//                style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
//                style.setFillForegroundColor(IndexedColors.AQUA.getIndex());
//                insertDataSetWithBSS(sheet,dataSet.get(0),style);
//
//                workbook.removeSheetAt(1);  //无BSS业务号码导入权限，则删除BSS业务号码sheet页
//                workbook.removeSheetAt(1);  //无PMS项目编码导入权限，则删除PMS项目编码sheet页
//            }

			workbook.write(out);
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}finally{
			if(out!=null){
				try {
					out.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
	}

/**
	 * @param response 响应
	 * @param fileName 自定义下载文件名
	 * @return out 输出流
	 */
	public static OutputStream queryOutStream(HttpServletResponse response,String fileName){
		OutputStream out = null;
		try {
			fileName = URLEncoder.encode(fileName,"UTF-8");
			response.setContentType("octets/stream");
			response.addHeader("Content-Disposition", "attachment;filename="+fileName);
			out = response.getOutputStream();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return out;
	}

/**
	 * 获取当前时间
	 * @param format 时间格式 （例如 yyyy-MM-dd hh:mm ）
	 * @return
	 */
	public static String getNowTime(String format){
		String nowtime = null;
		try {
			if(format == null || format == ""){
				return null;
			}else{
				Calendar c = Calendar.getInstance(Locale.CHINESE);
				SimpleDateFormat sdf = new SimpleDateFormat(format);
				nowtime = sdf.format(c.getTime());
			}
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
		
		return nowtime;
	}

	
	/**
	 * 项目补填信息时根据权限判断设置背景色
	  *<功能简述>
	  *<功能详细描述>
	  * @param sheet
	  * @param dataSet
	  * @param style [参数说明]
	  * 
	  * @return void [返回类型说明]
	  * @exception throws [异常类型] [异常说明]
	  * @see [类、类#方法、类#成员]
	 */
	public static void insertDataSetWithBSS(XSSFSheet sheet,List<Map> dataSet,XSSFCellStyle style){
		//找出第一个空行,准备填写数据
		int rowindex = sheet.getLastRowNum();
		// 新建Map 参数读取模板列
		List<String> parm = new ArrayList();
		// 得到参数行
		XSSFRow parmrow = sheet.getRow(rowindex);
		for (int rownum = 0; rownum < parmrow.getLastCellNum(); rownum++) {
			XSSFCell cell = parmrow.getCell(rownum);
			if(cell==null){
				parm.add("");
				continue;
			}
			String key = cell.getStringCellValue();
			parm.add(key);
		}
		//删除模板参数行
		sheet.removeRow(sheet.getRow(rowindex));
		for (Map rowdata : dataSet) {
			XSSFRow datarow = sheet.createRow(rowindex);
			//此处以数组长度做限制，防止越界
			for(int cellnum = 0;cellnum<parm.size();cellnum++){
				XSSFCell cell = datarow.createCell(cellnum);
				if(rowdata.get(parm.get(cellnum)) == null){
					cell.setCellValue("");
				}else{
					String val = "";
					if(rowdata.get(parm.get(cellnum)) instanceof CLOB){
						try {
							oracle.sql.CLOB clob = (oracle.sql.CLOB)rowdata.get(parm.get(cellnum));
							val = clob.stringValue();
						} catch (SQLException e) {
							e.printStackTrace();
						}
					}else{
						val = String.valueOf(rowdata.get(parm.get(cellnum)));
					}
					cell.setCellValue(val);
				}
				if(style != null){
					if(cellnum > 6){	//判断该单元格是否添加背景色
						String isshow = isShow();
						String[] iscellShowId = isshow.split(",");
						for(int i = 0 ;i<iscellShowId.length; i++){
							if(String.valueOf(cellnum).equals(iscellShowId[i])){
								cell.setCellStyle(style);
								break;
							}
						}
					}
				}
			}
			rowindex++;
		}
	}