Java 解析数据导出excel文件，SXSSFWorkbook应用实践

记录一个常见的excel导出案例

一、pom.xml 引入依赖

		<dependency>
		    <groupId>org.apache.poi</groupId>
		    <artifactId>poi-scratchpad</artifactId>
		    <version>3.12</version>
		</dependency>

		<dependency>
			<groupId>net.sf.json-lib</groupId>
			<artifactId>json-lib</artifactId>
			<version>2.4</version>
			<classifier>jdk15</classifier>
		</dependency>

二、点击导出按钮，js请求下载文件

// get请求拼接参数
window.location.href='http://127.0.0.1:8080/demo/dc?value1=xxx&value2=xxx';

三、controller查询数据导出excel文件

	@RequestMapping(value = "dc", method = RequestMethod.GET)
	@ResponseBody
	public void pksbxxDc(Bean bean, HttpSession session, HttpServletResponse response) {

		String filename = "导出文件名.xlsx";// 文件名

		synchronized(this) {
			// 获取 导出查询的数据
			List<Map<String, Object>> list = null;
			try {
			    // 查询后台数据json，此处省略了
				......
				
				list = Utils.jsonToMaps(json);
			} catch (Throwable e) {
				System.out.println("导出查询失败：" + e.getMessage());
				filename = "导出查询导出失败.xlsx";
			}

			// 字段设置
			String[] names = new String[] {"序号", "姓名", "证件号", "时间"};
			String[] columns = new String[] {"num","name", "IDcard", "time"};
			int[] widths = new int[] {80, 180, 250, 150}; // excel 每列的宽度
			// 根据 查询数据生成 excel文件，获取该文件url
			String excelUrl = Utils.createExcelFile(list, names, columns, widths, 10000);

			File file = new File(excelUrl);
			// 导出文件流
			try (BufferedInputStream bis = new BufferedInputStream(new FileInputStream(file));
				 OutputStream os = response.getOutputStream()) {
				byte[] buffer = new byte[bis.available()];
				bis.read(buffer);
				response.reset();
				response.setHeader("Content-Disposition", "attachment;filename=" +
						new String(filename.replaceAll(" ", "").getBytes("gbk"), "iso8859-1"));
				response.setHeader("Content-Length", "" + file.length());
				os.write(buffer);
			} catch (FileNotFoundException e) {
				e.printStackTrace();
			} catch (IOException e) {
				e.printStackTrace();
			}

		}
	}

四、工具类Utils中两个方法， json字符转List<Map>和解析数据生成excel文件

	/**
	 * json字符转 List<Map>集合
	 * @param json
	 * @return
	 */
	public static List<Map<String, Object>> jsonToMaps(String json) {
		List<Map<String, Object>> list = new ArrayList<>();
		JSONArray arr = JSONArray.fromObject(json);
		if (json != null && !"".equals(json.trim())) {
			for (Object o : arr) {
				Map<String, Object> map = JSONObject.fromObject((JSONObject) o);
				list.add(map);
			}
		}
		return list;
	}


	/**
	 * 根据 查询数据生成 excel文件，获取该文件url
	 * @param list  List<Map<String, Object>>
	 * @param names  excel 列头名称数组
	 * @param columns  map的key数组，其对应value就是 excel每行各列的数据
	 * @param widths  excel每列的宽度
	 * @param pagesSize  excel每页sheet显示的数据条数
	 * @return
	 */
	public static String createExcelFile(List<Map<String, Object>> list, String[] names, String[] columns, int[] widths, int pagesSize) {
		// 生成文件地址设置
		String  dateStr = new SimpleDateFormat("yyyyMMddhhmmssSSS").format(new Date());
		String fileName = "D:/" + dateStr + ".xlsx";
		String osName = System.getProperty("os.name").toLowerCase();
		if (osName.startsWith("linux") || osName.startsWith("Linux")) {
			fileName = "/home/Tomcat/temp/";
			File file = new File(fileName);
			if (file.exists()) {
				file.mkdirs();
			}
			fileName = fileName + dateStr + ".xlsx";
		}
		System.out.println("===============osName===============" + osName);
		System.out.println("===============fileName===============" + fileName);

		// 配置excel 生成文件
		try (FileOutputStream fos = new FileOutputStream(fileName)) {
			// 配置excel表格
			int rowAccess = 3000; //内存中缓存记录数
			int total = list == null ? 0 : list.size();
			int mus = pagesSize; // 每页sheet 条数
			int avg = mus == 0 ? 0 : total / mus; // sheet 总页数
			if (total % mus > 0 || total == 0) {
				avg += 1;
			}
			SXSSFWorkbook xWorkbook = new SXSSFWorkbook(rowAccess);
			for (int a = 0; a < avg; a++) { // 遍历每页
				// 设置表格Sheet页名称
				Sheet sh = xWorkbook.createSheet("sheet" + (a + 1));
				// 设置Sheet页表格头及样式
				CellStyle cs = xWorkbook.createCellStyle();
				cs.setAlignment(CellStyle.ALIGN_CENTER);
				cs.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
				Font font = xWorkbook.createFont();
				font.setFontHeightInPoints((short) 12);
				font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
				font.setFontName("宋体");
				cs.setFont(font);
				cs.setWrapText(true); // 自动换行
				// 设置sheet页 首行
				Row row0 = sh.createRow(0);
				for (int i = 0; i < names.length; i++) { // 设置首行每列
					Cell cell = row0.createCell(i);
					cell.setCellStyle(cs);
					cell.setCellValue(names[i]);
				}
				// 设置sheet页 内容 （各行）
				CellStyle cs2 = xWorkbook.createCellStyle();
				cs2.setWrapText(true);
				if (list != null && mus != 0) {
					int num = a * mus;
					for (int j = num; j < list.size(); j++) {
						Row xRow = sh.createRow(j + 1 - num);
						Map emp = list.get(j);
						// 生成对应列
						if (names.length == columns.length) {
							for (int x = 0; x < names.length; x++) { // 设置该行每列
								Cell cell = xRow.createCell(x);
								cell.setCellStyle(cs2);
								if (x == 0) {
									cell.setCellValue(j + 1);
									continue;
								}
								Object col = emp.get(columns[x]);
								if (col instanceof String) {
									cell.setCellValue((String) col);
								} else if (col instanceof Integer) {
									cell.setCellValue((Integer) col);
								} else if (col instanceof Double) {
									cell.setCellValue((Double) col);
								} else if (col instanceof Float) {
									cell.setCellValue((Float) col);
								} else {
									cell.setCellValue(String.valueOf(col));
								}
							}
						}
						// 每当行数达到设置的值 就刷新数据到硬盘，以清理内存
						if (j % rowAccess == 0) {
							((SXSSFSheet) sh).flushRows();
						}
					}
				}
				// 列宽设置
				if (names.length == widths.length) {
					for (int y = 0; y < names.length; y++) {
						sh.setColumnWidth(y, 20 * widths[y]);
					}
				}
			}
			// 生成文件
			xWorkbook.write(fos);
		}catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		return fileName;
	}