导出到 excel

最新推荐文章于 2024-09-03 17:18:42 发布

autumnWan

最新推荐文章于 2024-09-03 17:18:42 发布

阅读量156

点赞数

CC 4.0 BY-SA版权

本文链接：https://blog.youkuaiyun.com/weixin_36898557/article/details/79862576

本文详细介绍了使用Java进行Excel导出的方法，包括如何构建Excel工作簿、设置单元格样式、循环填充数据，以及处理不同浏览器的文件下载兼容性。通过具体代码示例，展示了从Action到Service层的完整导出流程。

导出方法：

--Action中方法通用

--Action
public void exportManage(ActionMapping mapping,ActionForm form,HttpServletRequest request,HttpServletResponse response) {
		//获取参数
		JSONObject params = LocalWebUtils.convertRequestParameters2JSONObject(request);
		try {
			//导出后的文件名称
			String docName = "JAG报表";
			//获取session中的路径
			String path = request.getSession().getServletContext().getRealPath("/");
			//拼接路径
			path += "/excelTemplates/capitalExpendManage.xlsx";
			//
			response.reset();
			response.setContentType("application/application/msexcel");
			response.setCharacterEncoding("UTF-8");
			//
			if (request.getHeader("User-Agent").toLowerCase().indexOf("firefox") > 0) {
				docName = new String(docName.getBytes(), "ISO-8859-1");
				response.setHeader("Content-Disposition", "attachment; filename=" + docName + ".xlsx");
			} else {
				docName = java.net.URLEncoder.encode(docName, "UTF-8");
				response.setHeader("Content-Disposition", "attachment; filename=" + docName + ".xlsx");
			}
			//修改路径
			if (File.separatorChar == '/') {
				path = StringUtils.replace(path, "\\", "/");
				path = StringUtils.replace(path, "//", "/");
			} else {
				path = StringUtils.replace(path, "/", "\\");
				path = StringUtils.replace(path, "\\\\", "\\");
			}
			params.element("filepath", path);
			//调用 导出方法
			Workbook book = this.service.export(params);
			//调用流，写入excel中
			book.write(response.getOutputStream());
		} catch (Exception e) {
			log.error(e.getMessage(), e);
		}
    } 
		//获取参数
		JSONObject params = LocalWebUtils.convertRequestParameters2JSONObject(request);
		try {
			//导出后的文件名称
			String docName = "JAG报表";
			//获取session中的路径
			String path = request.getSession().getServletContext().getRealPath("/");
			//拼接路径
			path += "/excelTemplates/capitalExpendManage.xlsx";
			//
			response.reset();
			response.setContentType("application/application/msexcel");
			response.setCharacterEncoding("UTF-8");
			//
			if (request.getHeader("User-Agent").toLowerCase().indexOf("firefox") > 0) {
				docName = new String(docName.getBytes(), "ISO-8859-1");
				response.setHeader("Content-Disposition", "attachment; filename=" + docName + ".xlsx");
			} else {
				docName = java.net.URLEncoder.encode(docName, "UTF-8");
				response.setHeader("Content-Disposition", "attachment; filename=" + docName + ".xlsx");
			}
			//修改路径
			if (File.separatorChar == '/') {
				path = StringUtils.replace(path, "\\", "/");
				path = StringUtils.replace(path, "//", "/");
			} else {
				path = StringUtils.replace(path, "/", "\\");
				path = StringUtils.replace(path, "\\\\", "\\");
			}
			params.element("filepath", path);
			//调用 导出方法
			Workbook book = this.service.export(params);
			//调用流，写入excel中
			book.write(response.getOutputStream());
		} catch (Exception e) {
			log.error(e.getMessage(), e);
		}
    }

--service中

public Workbook export(JSONObject params);

--serviceImpl

(1)构建book

(2)设置样式

(3)循环数据

static String[] exportSummaryHeader = new String[]{ "row" , "name" , "orderMoney" , "recMoney" , "rate" };

public Workbook export(JSONObject params) {
		Project project = entityManager.get(Project.class, params.getLong("projectId"));
		//构建book
		XSSFWorkbook book;
		try {
			book = new XSSFWorkbook(new FileInputStream(params.getString("filepath")));
		} catch (Exception ex) {
			log.error(ex.getMessage(), ex);
			throw new BusinessException("导出报错：" + ex.getMessage());
		}
		
		//设置样式
		XSSFFont font = book.createFont();
		font.setFontHeightInPoints((short) 10);
		font.setFontName("Arial");
		XSSFCellStyle unassignedStyle = book.createCellStyle();
		unassignedStyle.setFont(font);
		unassignedStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直居中
		unassignedStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		unassignedStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		unassignedStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
		unassignedStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
		unassignedStyle.setWrapText(true);
		//循环数据
		List dataList = new ArrayList();
		if(params.getString("type").equals("supplier")){
			dataList = this.findPurchaseSupplierList(project);
		}else{
			dataList = this.findPurchaseOrgList(project);
		}
		
		//数据总数
		if (dataList != null && dataList.size() > 0) {
			//获取第一个sheet
			XSSFSheet sheet;
			sheet = book.getSheetAt(0);		
			//数据的写入从 第2行开始
			int begin = 1;
			//循环数据
			for(int i = 0 ; i < dataList.size() ; i++){
				//创建行
				XSSFRow row = sheet.createRow(begin+i);
				//定义单元格
				XSSFCell cell = null;
				//单元格所在的行
				int j = 0 ; 
				//循环表头,根据表头查数据
				for(String key : exportSummaryHeader ){
					//获取数据
					JSONObject itemData = (JSONObject) dataList.get(i);
					//添加单元格
					cell = row.createCell(j);
					//设置单元格格式
					cell.setCellStyle(unassignedStyle);
					if (key.equals("row")) {
						//生成序号
						cell.setCellValue(i + 1);
					} else {
						//添加值
						cell.setCellValue(itemData.containsKey(key) ? itemData.getString(key) : "");
					}
					j++;
				}				
			}
		}
		return book;
	}

end

导出 到 excel

导出到 excel