你好,从数据库检索出来的数据要输出excel表格,但是需要把列变成横,实绩远行时,效率比较底下,有没有什么提高效率的方法呢?
数据格式如图:
需要的格式:itm_cd 21180310 21180311 21180312 21180313
0B007X 12 10 10 10现有的处理代码:
private EUIDataGridResult formatTableData(List<Map<String, Object>> list, List<Map<String, Object>> dt_list, int total) {
Map<String,Object> map = new HashMap<String,Object>();
Map<String,Object> map_dts = new HashMap<String,Object>();
List<Map<String,Object>> result = new ArrayList<Map<String,Object>>();
for(int i=0; i<dt_list.size(); i++) {
map_dts.put(dt_list.get(i).get("DT").toString(), 0);
}
Iterator<Map<String, Object>> iterator = list.iterator();
Iterator<Map<String, Object>> iterator2 = null;
while(iterator.hasNext()) {
Map<String, Object> map2 = iterator.next();
String itmCd = (String) map2.get("itm_cd");
map = new HashMap<String,Object>();
map.putAll(map_dts);
map.put("itm_cd", itmCd);
map.put(map2.get("dt").toString(), map2.get("col"));
iterator.remove();
iterator2 = list.iterator();
while(iterator2.hasNext()) {
map2 = iterator2.next();
String itmCdNext = (String) map2.get("itm_cd");
if(itmCd.equals(itmCdNext)) {
map.put(map2.get("dt").toString(), map2.get("col"));
iterator2.remove();
}
}
result.add(map);
iterator = list.iterator();
}
// System.out.println(result);
EUIDataGridResult resultData = new EUIDataGridResult();
resultData.setRows(result);
resultData.setTotal(total);
return resultData;
}
数据格式如图:
需要的格式:itm_cd 21180310 21180311 21180312 21180313
0B007X 12 10 10 10现有的处理代码:
private EUIDataGridResult formatTableData(List<Map<String, Object>> list, List<Map<String, Object>> dt_list, int total) {
Map<String,Object> map = new HashMap<String,Object>();
Map<String,Object> map_dts = new HashMap<String,Object>();
List<Map<String,Object>> result = new ArrayList<Map<String,Object>>();
for(int i=0; i<dt_list.size(); i++) {
map_dts.put(dt_list.get(i).get("DT").toString(), 0);
}
Iterator<Map<String, Object>> iterator = list.iterator();
Iterator<Map<String, Object>> iterator2 = null;
while(iterator.hasNext()) {
Map<String, Object> map2 = iterator.next();
String itmCd = (String) map2.get("itm_cd");
map = new HashMap<String,Object>();
map.putAll(map_dts);
map.put("itm_cd", itmCd);
map.put(map2.get("dt").toString(), map2.get("col"));
iterator.remove();
iterator2 = list.iterator();
while(iterator2.hasNext()) {
map2 = iterator2.next();
String itmCdNext = (String) map2.get("itm_cd");
if(itmCd.equals(itmCdNext)) {
map.put(map2.get("dt").toString(), map2.get("col"));
iterator2.remove();
}
}
result.add(map);
iterator = list.iterator();
}
// System.out.println(result);
EUIDataGridResult resultData = new EUIDataGridResult();
resultData.setRows(result);
resultData.setTotal(total);
return resultData;
}
解决方案 »
- 话说像人人那样的sns的新鲜事功能一般是咋实现的?
- 怎么在unix下部署tomcat?急用,在线等
- 一个HIBERNATE 的复杂问题。
- jstl输出list问题报错,各位前辈指教
- 8个8求算法
- 开发EJB3实体Bean时,如何让该Bean对应的数据库表的内容打印成XML文件?
- OneToMany级联新增失败求解
- 菜鸟问题:win2003下安装JBuilder9为什么编译总是出:Cannot build JSPs: Tomcat 4.0 server is not configured!怎么设啊?高人指点一下
- JAVA上的探索。。。。。。
- 大家好!请问哪儿能下到jubilder X自带的帮助文档!
- 有大神使用jetty启动项badMessage: 400 Illegal character 0x0in state=START in '\x00<<<\x00
- 请教JSP大神一个“JSP页面乱码”的问题的产生原因与解决方案的问题答案。
是10g的oracle,所以只能在程序里做。数据库已经是排序了的,但是每个itm_cd不一定每个日期都有,比如第一个itm_cd有10个日期,第二个itm_cd只有1个日期,如何降低复杂度呢?itm_cd 20180310 20180311 20180312 20180313 ........ 20180325
0B007X 12 10 10 10 0
0B031X 0 0 0 0 1954268.62我的数据是spring的jdbcTemplanet.queryForList返回的结果集
List<Map<String, Object>> list = jdbcTemplate.queryForList(sql, new Object[]{sdt, edt});
是10g的oracle,所以只能在程序里做。数据库已经是排序了的,但是每个itm_cd不一定每个日期都有,比如第一个itm_cd有10个日期,第二个itm_cd只有1个日期,如何降低复杂度呢?itm_cd 20180310 20180311 20180312 20180313 ........ 20180325
0B007X 12 10 10 10 0
0B031X 0 0 0 0 1954268.62我的数据是spring的jdbcTemplanet.queryForList返回的结果集
List<Map<String, Object>> list = jdbcTemplate.queryForList(sql, new Object[]{sdt, edt});一样的。不管有没有空值,都可以一次遍历出结果。具体写法需要知道“20180310 20180311 20180312 20180313 ........ 20180325”这个时间列的生成逻辑
//构造数据
List<Map<String, String>> list = Lists.newArrayListWithExpectedSize(256); for (int i = 1; i <= 1000000; i++) {
Map<String, String> map = Maps.newHashMapWithExpectedSize(3);
map.put("ITM_CD", "0B007X");
map.put("DT", String.valueOf(20080309 + i));
map.put("COL", "10");
list.add(map);
}
for (int i = 1; i <= 1000000; i++) {
Map<String, String> map = Maps.newHashMapWithExpectedSize(3);
map.put("ITM_CD", "0B036XN");
map.put("DT", String.valueOf(20080300 + i));
map.put("COL", "12");
list.add(map);
} //数据处理
List<String> itmCds = list.parallelStream().map(m -> m.get("ITM_CD")).distinct().collect(Collectors.toList());
List<Map<String, String>> result = Lists.newArrayListWithExpectedSize(itmCds.size());
itmCds.parallelStream().forEach(key -> {
Map<String, String> data = Maps.newHashMapWithExpectedSize(256);
data.put("ITM_CD", key);
list.stream().filter(m -> key.equals(m.get("ITM_CD"))).forEach(m -> data.put(m.get("DT"), m.getOrDefault("COL", "0")));
result.add(data);
}); //输出
result.stream().peek(System.out::println).collect(Collectors.toList());
}
class DT{
public String itm_cd;
public String dt;
public String col;
} @Test
public String[][] test(List<DT> rows) {
HashSet rowNames = new HashSet(); // 行名
HashSet colNames = new HashSet(); // 列名
Map<String,String> dataMap = new HashMap<>(); // 列名$行名 : 值
for(DT row : rows){
rowNames.add(row.itm_cd);
colNames.add(row.dt);
dataMap.put(row.itm_cd + "&" + row.dt, row.col); // 数据查询表
}
// 输出矩阵
int i=0,j=0;
String[][] rslt = new String[rowNames.size()+1][colNames.size()+1];
Iterator it1 = rowNames.iterator();
while(it1.hasNext()){
String rName = (String) it1.next();
rslt[i][0] = rName; // 行名
Iterator it2 = colNames.iterator();
while(it2.hasNext()){
String cName = (String) it2.next();
String val;
if(i==0){
val = cName;
}else{
val = dataMap.get(rName +"&" + cName);
}
rslt[i][j] = val;
j++;
}
i++;
} }
是10g的oracle,所以只能在程序里做。数据库已经是排序了的,但是每个itm_cd不一定每个日期都有,比如第一个itm_cd有10个日期,第二个itm_cd只有1个日期,如何降低复杂度呢?itm_cd 20180310 20180311 20180312 20180313 ........ 20180325
0B007X 12 10 10 10 0
0B031X 0 0 0 0 1954268.62我的数据是spring的jdbcTemplanet.queryForList返回的结果集
List<Map<String, Object>> list = jdbcTemplate.queryForList(sql, new Object[]{sdt, edt});一样的。不管有没有空值,都可以一次遍历出结果。具体写法需要知道“20180310 20180311 20180312 20180313 ........ 20180325”这个时间列的生成逻辑这个时间列就是DT列,一个时间区间去重排序得到的时间列(select distinct dt from tbl where dt >='' and dt <= '' order by dt)这段sql文查出来的时间就是我要生成表格的所有列名
并且加上这两列:row_number() over(partition by itm_cd order by dt) "aa",
decode(count(1) over(partition by itm_cd), row_number() over(partition by itm_cd order by dt), 'end', 'else') "bb"暂定dt_list是这么查的:select distinct dt from data_source order by dt;参数list为全量数据 /**
order by itm_cd, dt
select distinct dt from data_source order by dt;row_number() over(partition by itm_cd order by dt) "aa",
decode(count(1) over(partition by itm_cd), row_number() over(partition by itm_cd order by dt), 'end', 'else') "bb"
* @param list
* @param dt_list
* @param total
* @return
*/
private List formatTableData(List<Map<String, Object>> list, List<String> dt_list, int total) {
List<Map<String, Object>> result = new ArrayList(list.size());
Map<String, Object> map = new LinkedHashMap(dt_list.size());
for(Map<String, Object> tempMap : list){
if("1".equals(tempMap.get("aa")))
map = new LinkedHashMap(dt_list.size());
for(String tempDt : dt_list)
if(tempDt.equals(tempMap.get("DT"))){
map.put(tempDt, tempMap.get("DT"));
break;
}
if("end".equals(tempMap.get("bb"))){
for(String tempDt : dt_list){
if(!map.containsKey(tempDt))
map.put(tempDt, "0");
}
result.add(map);
}
}
return result;
}循环次数为2*list*dt_list