java功能实现 -- Excel导入导出(简版)

需求

项目中有数据导出为表格的需求，且字段较少，业务处理简单，可使用下面方式进行导出。若需要适应性更广可参考通用导入导出。

思路

1.请求数据库，获取需要导出的所有数据；
2.创建表格写入类，进行每行的写入；
3.设置每列单元格宽度后响应即可。

步骤

API接口代码

    @ApiOperation(value = "导出excel表", notes = "导出excel表")
	@RequestMapping(value = "/excelTest", method = RequestMethod.GET)
	@JsonView(ModelDto.ListView.class)
	public Object excelTest(@ModelAttribute @Validated({ ModelDto.ListValidated.class }) ModelDto modelDto) {
		Response response = modelService.excel(modelDto);
		if (200 == response.getStatus()) {
			List<ModelEntity> modelDtos = (List<ModelEntity>) response.getData();
			ArrayList<Map<String, Object>> rows = new ArrayList<Map<String, Object>>();
			for (int i = 0; i < modelDtos.size(); i++) {
				Map<String, Object> row1 = new LinkedHashMap<>();
				ModelEntity ModelEntity = (ModelEntity) JSONObject.toBean(
						JSONObject.fromObject(modelDtos.get(i)), ModelEntity.class);
				row1.put("序号", i + 1);
				row1.put("编码", modelEntity.getCode());
				row1.put("名称", modelEntity.getName());
				if (("0").equals(modelEntity.getIs_implement_estate_management().toString())) {
					row1.put("是否实行物业管理", "是");
				} else {
					row1.put("是否实行物业管理", "否");
				}
				rows.add(row1);
			}
			String date = DateUtil.format(new Date(), "yyyy-MM-dd");
			String path = filePath + System.getProperty("file.separator") + date;
			File fileDirs = new File(path);
			if (!fileDirs.exists()) {
				fileDirs.mkdirs();
			}
			String fileName = Uuid.getOrderNo() + ".xlsx";
			path += System.getProperty("file.separator") + fileName;
			// 通过工具类创建writer
			ExcelWriter writer = ExcelUtil.getWriter(path);
			// 合并单元格后的标题行，使用默认标题样式
			if (rows.size() > 0) {
				writer.merge(rows.get(0).size() - 1, user.getName() + year + "数据列表");
			}
			// 一次性写出内容，使用默认样式
			writer.write(rows);
			Workbook workbook = writer.getWorkbook();
			Sheet sheet = workbook.getSheetAt(0);
			for (int i = 0; i < rows.size() + 2; i++) {
				sheet.autoSizeColumn(i);
			}
			// 控制生成的单元格宽度
			sheet.setColumnWidth(0, 1500);
			sheet.setColumnWidth(1, 4000);
			sheet.setColumnWidth(2, 4000);
			sheet.setColumnWidth(3, 4000);
			// 关闭writer，释放内存
			writer.close();
			String url = fileUrl + System.getProperty("file.separator") + date + System.getProperty("file.separator") + fileName;
			System.out.println(url);
			response.setData(url);
			return response;
		} else {
			response = new Response(1, "数据查询出错！");
		}
		return response;
	}