数据库数据通过excel导出案例

package com.datanew.service.impl; import com.datanew.dao.BaseDao; import com.datanew.dto.unalterable.$Pages; import com.datanew.exception.BusinessException; import com.datanew.model.XtUser; import com.datanew.service.FyxxCxService; import com.datanew.service.ZgxxgxServer; import com.datanew.util.FySql; import com.datanew.util.StringUtil; import org.springframework.stereotype.Service; import javax.annotation.Resource; import java.time.LocalDate; import java.time.format.DateTimeFormatter; import java.util.*; import java.util.stream.Collectors; @Service public class FyxxCxServiceImpl implements FyxxCxService { @Resource private ZgxxgxServer zgxxgxServer; @Resource private BaseDao baseDao; //费源查询条件字段 private final Map<String, List<String>> FY_QUERY_PARAMS = new HashMap<String, List<String>>(){{ put("001", Arrays.asList("szsbm", "guid", "sfxmlx", "htbh", "status", "makedatestart", "makedateend", "appid")); put("002", Arrays.asList("admindivcode", "guid", "sfxmlx", "htbh", "status", "makedatestart", "makedateend", "appid")); put("003", Arrays.asList("szsbm", "guid", "sfxmlx", "htbh", "status", "makedatestart", "makedateend", "appid")); put("004", Arrays.asList("admindivcode", "guid", "sfxmlx", "appid", "itemname", "htbh", "makedatestart", "makedateend", "status")); put("005", Arrays.asList("admindivcode", "guid", "appid", "itemname", "htbh", "makedatestart", "makedateend", "status")); put("006", Arrays.asList("admindivcode", "guid", "appid", "itemname", "htbh", "makedatestart", "makedateend", "status")); put("007", Arrays.asList("admindivcode", "guid", "appid", "tddkbh", "makedatestart", "makedateend", "status")); put("008", Arrays.asList("admindivcode", "guid", "appid", "xmmc", "makedatestart", "makedateend", "status")); put("009", Arrays.asList("admindivcode", "guid", "appid", "htbh", "htmc", "spwh", "makedatestart", "makedateend", "status")); }}; private final int DEFAULT_LIMIT = 30; @Override public Map getJkjg(String sfxmlx, String fyxx_uuid) { return zgxxgxServer.syncJkjg(sfxmlx, fyxx_uuid); } @Override public $Pages<Map> getRf(Map<String, String> pageMap, XtUser user) { //根据条件查询yw_001_info的fyxx_uuid 和 总记录数 String sql = "select fyxx_uuid from yw_001_rf_info where 1 = 1 " + generateConditions(pageMap, FY_QUERY_PARAMS.get("001"), user) + " order by makedate desc"; //分页参数 int offset = pageMap.get("offset") == null ? 0 : Integer.parseInt(String.valueOf(pageMap.get("offset"))); int limit = pageMap.get("limit") == null ? DEFAULT_LIMIT : Integer.parseInt(String.valueOf(pageMap.get("limit"))); //查询转换 List<Map<String, String>> fyxxUuidList = baseDao.selectMapsBySQL(sql, null, offset, limit); String fyxx_uuids = fyxxUuidList.stream().map(f -> f.get("fyxx_uuid")).collect(Collectors.joining("','")); String conditions = " and t.fyxx_uuid in ('" + fyxx_uuids + "')"; List tableData = baseDao.selectMapsBySQL(FySql.getRf(conditions)); //排序 DateTimeFormatter yyyy1MM1dd = DateTimeFormatter.ISO_DATE; tableData.sort((m1, m2) -> { LocalDate d1 = LocalDate.parse(((Map) m1).get("makedate").toString(), yyyy1MM1dd); LocalDate d2 = LocalDate.parse(((Map) m2).get("makedate").toString(), yyyy1MM1dd); return -1 * d1.compareTo(d2); }); Long count = baseDao.getCountBySQL("select count(*) from (" + sql + ") m ", null); $Pages<Map> page = new $Pages(limit, offset); page.setTotal(count.intValue()); page.setRows(tableData); return page; } @Override public $Pages<Map> getGytd(Map<String, String> pageMap, XtUser user) { //根据条件查询yw_002_info的fyxx_uuid 和 总记录数 String sql = "select fyxx_uuid from yw_002_gytdcrsr_info where 1 = 1 " + generateConditions(pageMap, FY_QUERY_PARAMS.get("002"), user) + " order by makedate desc"; //分页参数 int offset = pageMap.get("offset") == null ? 0 : Integer.parseInt(String.valueOf(pageMap.get("offset"))); int limit = pageMap.get("limit") == null ? DEFAULT_LIMIT : Integer.parseInt(String.valueOf(pageMap.get("limit"))); //查询转换 List<Map<String, String>> fyxxUuidList = baseDao.selectMapsBySQL(sql, null, offset, limit); String fyxx_uuids = fyxxUuidList.stream().map(f -> f.get("fyxx_uuid")).collect(Collectors.joining("','")); String conditions = " and t.fyxx_uuid in ('" + fyxx_uuids + "')"; List tableData = baseDao.selectMapsBySQL(FySql.getGytd(conditions)); //排序 DateTimeFormatter yyyy1MM1dd = DateTimeFormatter.ISO_DATE; tableData.sort((m1, m2) -> { LocalDate d1 = LocalDate.parse(((Map) m1).get("makedate").toString(), yyyy1MM1dd); LocalDate d2 = LocalDate.parse(((Map) m2).get("makedate").toString(), yyyy1MM1dd); return -1 * d1.compareTo(d2); }); Long count = baseDao.getCountBySQL("select count(*) from (" + sql + ") m ", null); $Pages<Map> page = new $Pages(limit, offset); page.setTotal(count.intValue()); page.setRows(tableData); return page; } @Override public $Pages<Map> getStbcf(Map<String, String> pageMap, XtUser user) { //根据条件查询yw_003_info的fyxx_uuid 和 总记录数 String sql = "select fyxx_uuid from yw_003_stbcf_info where 1 = 1 " + generateConditions(pageMap, FY_QUERY_PARAMS.get("003"), user) + " order by makedate desc"; //分页参数 int offset = pageMap.get("offset") == null ? 0 : Integer.parseInt(String.valueOf(pageMap.get("offset"))); int limit = pageMap.get("limit") == null ? DEFAULT_LIMIT : Integer.parseInt(String.valueOf(pageMap.get("limit"))); //查询转换 List<Map<String, String>> fyxxUuidList = baseDao.selectMapsBySQL(sql, null, offset, limit); String fyxx_uuids = fyxxUuidList.stream().map(f -> f.get("fyxx_uuid")).collect(Collectors.joining("','")); String conditions = " and t.fyxx_uuid in ('" + fyxx_uuids + "')"; List tableData = baseDao.selectMapsBySQL(FySql.getStbc(conditions)); //排序 DateTimeFormatter yyyy1MM1dd = DateTimeFormatter.ISO_DATE; tableData.sort((m1, m2) -> { LocalDate d1 = LocalDate.parse(((Map) m1).get("makedate").toString(), yyyy1MM1dd); LocalDate d2 = LocalDate.parse(((Map) m2).get("makedate").toString(), yyyy1MM1dd); return -1 * d1.compareTo(d2); }); Long count = baseDao.getCountBySQL("select count(*) from (" + sql + ") m ", null); $Pages<Map> page = new $Pages(limit, offset); page.setTotal(count.intValue()); page.setRows(tableData); return page; } @Override public $Pages<Map> getKczy(Map<String, String> pageMap, XtUser user) { String sql = "select fyxx_uuid from yw_004_kczyzxsr_info where 1 = 1 " + generateConditions(pageMap, FY_QUERY_PARAMS.get("004"), user) + " order by makedate desc"; //分页参数 int offset = pageMap.get("offset") == null ? 0 : Integer.parseInt(String.valueOf(pageMap.get("offset"))); int limit = pageMap.get("limit") == null ? DEFAULT_LIMIT : Integer.parseInt(String.valueOf(pageMap.get("limit"))); //查询转换 List<Map<String, String>> fyxxUuidList = baseDao.selectMapsBySQL(sql, null, offset, limit); String fyxx_uuids = fyxxUuidList.stream().map(f -> f.get("fyxx_uuid")).collect(Collectors.joining("','")); String conditions = " and t.fyxx_uuid in ('" + fyxx_uuids + "')"; List tableData = baseDao.selectMapsBySQL(FySql.getKczy(condition