java后台导出excel表格的两种方法

最新推荐文章于 2024-05-10 11:37:26 发布

黑皮爱学习

最新推荐文章于 2024-05-10 11:37:26 发布

阅读量5.6k

点赞数

分类专栏： java

本文链接：https://blog.csdn.net/qq_22701869/article/details/85108668

版权

java 专栏收录该内容

42 篇文章 6 订阅

订阅专栏

引入的jar包

<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi-scratchpad</artifactId>
	<version>3.11-beta2</version>
</dependency>
<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi-ooxml</artifactId>
	<version>3.11-beta2</version>
</dependency>
<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi-ooxml-schemas</artifactId>
	<version>3.11-beta2</version>
</dependency>
<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi-excelant</artifactId>
	<version>3.11-beta2</version>
</dependency>

第一种方法如下，这种方法很清晰，明了，使用简单，推荐使用第一种

@RequestMapping(value = "/exportOrderCar", method = { RequestMethod.GET, RequestMethod.POST })
	@ResponseBody
	public BaseRes<Map<String, Object>> exportOrderCar(HttpServletRequest request, HttpServletResponse response,
			OrderParam orderParam) {
		SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		DecimalFormat dataf = new DecimalFormat("#0.0000"); // 计算结果取四位小数
		try {
            //获取列表查询的对象list，这就是我们要导出的数据
			Page<OrderVo> page = orderService.findOrderPage(orderParam);
			List<OrderVo> orderList = page.getRecords();

			if (orderList != null && orderList.size() > 0) {

				// 2.4 设置表格
				String sheetName = "汽车发货通知单表";// 设置sheet名称
                //设置列标题，列标题要和相应对象的字段位置对应
				String[] excelHeader = { "订单号", "合同编号", "客户名称", "货物类型", "重量（吨）", "运费（元）", "收货地址", "承运商", "车牌号", "司机名称",
						"司机电话", "提货日期", "创建日期", "备注" };// 填充表头

				HSSFCellStyle style = Export2ExcelUtil.createStyle(workbook, 0);
				HSSFCellStyle leftStyle = Export2ExcelUtil.createStyle(workbook, 2);
				HSSFCellStyle[] styleList = new HSSFCellStyle[] { style, style, style, style, style, style, style,
						style, style, leftStyle, style, leftStyle, style, style };// 设置表格样式
                //这里设置导出的每个列的显示长度，和上面的类标题要一一对应
				int[] widthList = new int[] { 2000, 5000, 2800, 2800, 2800, 3760, 5000, 3760, 3760, 5000, 2800, 10000,
						2800, 2800 };// 设置表格列宽
				Map<Integer, Object[]> dataMap = new HashMap<Integer, Object[]>();
				// 这里是用来计算某一列需要计算的总数，如果不需要，可以删除
				double totalWayFee = 0d;
				// 计算总共有多少列数据
				int totalNum = 0;
				// 2.5 填充数据
				if (orderList != null && !orderList.isEmpty()) {
					totalNum = orderList.size();
					for (int i = 0; i < orderList.size(); i++) {
						Object[] obj = new Object[excelHeader.length];
						OrderVo vo = orderList.get(i);
						int j = 0;
						obj[j++] = vo.getOrderNo() != null ? vo.getOrderNo() : "";
						obj[j++] = vo.getContractNo() != null ? vo.getContractNo() : "";
						obj[j++] = vo.getCustomerName() != null ? vo.getCustomerName() : "";
						obj[j++] = vo.getProductType() != null ? vo.getProductType() : "";
						obj[j++] = dataf.format(vo.getWeightAll());
						obj[j++] = dataf.format(vo.getWayFeeAll());
						obj[j++] = vo.getDeliveryAddress() != null ? vo.getDeliveryAddress() : "";
						obj[j++] = vo.getCarrierName() != null ? vo.getCarrierName() : "";
						obj[j++] = vo.getCarNo() != null ? vo.getCarNo() : "";
						obj[j++] = vo.getDriveName() != null ? vo.getDriveName() : "";
						obj[j++] = vo.getDriveMobile() != null ? vo.getDriveMobile() : "";
						obj[j++] = vo.getPickUpTime() != null ? df.format(vo.getPickUpTime()) : "";
						obj[j++] = vo.getCreateTime() != null ? df.format(vo.getCreateTime()) : "";
						obj[j++] = vo.getRemark() != null ? vo.getRemark() : "";
						totalWayFee = totalWayFee + Double.valueOf(dataf.format(vo.getWayFeeAll()));
						dataMap.put(i, obj);
						// 计算合计数量
						// totalWeight = totalWeight + groWeiVal;
						// totalWeight = new Double(dataf.format(totalWeight));
						// totalVolume = totalVolume + groVolVal;
						// totalVolume = new Double(dataf.format(totalVolume));
						// totalPcsQuantity = totalPcsQuantity + quaPcsVal;
						// totalScanQuantity = totalScanQuantity + scanQuantity;
					}
				}
                //最后一行添加的参数，一般是合计相关的信息
				Object[] totalDataList = new Object[] { "合计", totalNum, null, null, null, totalWayFee, null, null, null,
						null, null, null, null, null };
				// 2.6 生成EXCEL
				workbook = Export2ExcelUtil.createWorkbook(workbook, sheetName, excelHeader, dataMap, totalDataList,
						styleList, widthList);
				// 2.7 输出EXCEL
				SimpleDateFormat ddf = new SimpleDateFormat("yyyy-MM-dd");
				String today = ddf.format(new Date());
				String fileName = "OrderCar" + today + ".xls";
				response.setContentType("application/vnd.ms-excel;charset=utf-8");
				response.setHeader("Content-disposition",
						"attachment;filename=" + new String(fileName.getBytes("UTF-8"), "iso8859-1"));
				outputStream = response.getOutputStream();
				workbook.write(outputStream);
				outputStream.flush();
				return new BaseRes<Map<String, Object>>(BaseRes.CODE_SUCCESS, "导出成功", null);
			} else {
				return new BaseRes<Map<String, Object>>(BaseRes.CODE_PARAM_ERROR, "导出失败", null);
			}
		} catch (Exception e) {
			logger.error("WEB导出汽车发货通知单-->导出失败！", e);
			return new BaseRes<Map<String, Object>>(BaseRes.CODE_PARAM_ERROR, "导出失败！", null);
		} finally {
			if (outputStream != null) {
				try {
					workbook.close();
					outputStream.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}

	}

第二种如下，个人感觉不是很清晰

@RequestMapping("/exportExcel")
	public void exportExcel(HttpServletRequest request, HttpServletResponse response) throws IOException {
		List<SysUserVo> list = null;
		//获取查询参数
		String ids = request.getParameter("ids");
		// String idFlag = request.getParameter("idFlag");
		String account = request.getParameter("account");
		String name = request.getParameter("name");
		String status = request.getParameter("status");
		String orgId = request.getParameter("orgId");
		String showAllFlag = request.getParameter("showAllFlag");
		
		
		
		if (StringUtils.isBlank(ids)) {
			// 导出符合条件的所有
			// 查询 数据不分页
			list = sysUserService.geUserVoList(account, name, status, orgId, showAllFlag);

		} else {
			// 导出勾选的
			// 根据id 查询
			list = sysUserService.getUserVoListByIds(ids);
		}
		response.setContentType("application/vnd.ms-excel");
		String exportFileName = null;
		OutputStream fOut = null;
		try {
			// 进行转码，使其支持中文文件名
			exportFileName = RequestUtils.getDownFileNameByBrower(request, "用户信息表");
			response.setHeader("content-disposition", "attachment;filename=" + exportFileName + ".xls");
			// 产生工作簿对象
			HSSFWorkbook workbook = new HSSFWorkbook();
			// 产生工作表对象
			HSSFSheet sheet = workbook.createSheet();
			sheet.setDefaultColumnWidth(15);

			HSSFCellStyle headerStyle = (HSSFCellStyle) workbook.createCellStyle();// 创建标题样式
			// headerStyle.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index);// 设置背景色
			// headerStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);// 设置前景色
			// headerStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
			// headerStyle.setDataFormat(HSSFDataFormat.);
			headerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 设置垂直居中
			HSSFFont headerFont = (HSSFFont) workbook.createFont(); // 创建字体样式
			headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 字体加粗
			headerFont.setFontName("宋体"); // 设置字体类型
			headerFont.setFontHeightInPoints((short) 10); // 设置字体大小
			headerStyle.setFont(headerFont); // 为标题样式设置字体样式
			headerStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
			headerStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
			// cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
			headerStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框

			// 定义居左对齐方式
			CellStyle cellStyle = workbook.createCellStyle();
			cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_BOTTOM);
			HSSFFont headerFont2 = (HSSFFont) workbook.createFont(); // 创建字体样式
			headerFont2.setFontName("宋体"); // 设置字体类型
			headerFont2.setFontHeightInPoints((short) 12); // 设置字体大小
			cellStyle.setFont(headerFont2); // 为标题样式设置字体样式
			cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
			cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
			cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
			cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框

			// 生成excel表头
			HSSFRow headerRow = sheet.createRow(0);// 创建首行
			headerRow.setHeightInPoints(48);
			createCell(headerRow, 0, headerStyle, "登录账号");
			createCell(headerRow, 1, headerStyle, "员工姓名");
			createCell(headerRow, 2, headerStyle, "性别(填男或女)");
			createCell(headerRow, 3, headerStyle, "手机号码");
			createCell(headerRow, 4, headerStyle, "固定电话");
			createCell(headerRow, 5, headerStyle, "邮箱地址");
			createCell(headerRow, 6, headerStyle, "所属机构");

			int rowNum = 0;
			for (SysUserVo sysUserVo : list) {
				rowNum++;
				HSSFRow row = sheet.createRow(rowNum);// 创建一行
				createCell(row, 0, cellStyle, sysUserVo.getAccount());
				createCell(row, 1, cellStyle, sysUserVo.getName());
				createCell(row, 2, cellStyle, sysUserVo.getSex());
				createCell(row, 3, cellStyle, sysUserVo.getMobile());
				createCell(row, 4, cellStyle, sysUserVo.getPhone());
				createCell(row, 5, cellStyle, sysUserVo.getEmail());
				createCell(row, 6, cellStyle, sysUserVo.getOrgName());
			}
			fOut = response.getOutputStream();
			workbook.write(fOut);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			fOut.flush();
			fOut.close();
		}
		System.out.println("文件生成...");
	}
	public void createCell(HSSFRow row, int cellNum, CellStyle cellStyle, String cellValue) {
		HSSFCell idCell = row.createCell(cellNum);
		idCell.setCellStyle(cellStyle);
		if (StringUtils.isNotBlank(cellValue))
			idCell.setCellValue(cellValue);
	}

黑皮爱学习

关注

0
点赞
踩
9

收藏

觉得还不错? 一键收藏
1
评论
java后台导出excel表格的两种方法

引入的jar包<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-scratchpad</artifactId> <version>3.11-beta2</version></dependency><depe...
复制链接

扫一扫