java中csv导出-追加-列转行

1、问题描述

业务数据量比较大,业务上查询条件写入数据库,java定时去读,然后导出csv,供用户下载,因为有模板要求,前一部分是统计信息,后一部分是明细信息;首先csv中写入统计信息,然后再追加明细信息,记录下;

2、问题说明

2.1 pom文件

        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-csv</artifactId>
            <version>1.6</version>
        </dependency>     

2.2 上代码

   public static void doExportCsv(List<String> titleList, List<LinkedHashMap<String, String>> contextList, String absFilePath) {
        FileOutputStream fileOutputStream = null;
        OutputStreamWriter outputStreamWriter = null;
        try {
            //获取文件流
            fileOutputStream = new FileOutputStream(absFilePath);
            //解决FileOutputStream中文乱码问题  解决MS office乱码问题
            //outputStreamWriter = new OutputStreamWriter(fileOutputStream, "UTF-8");
            outputStreamWriter = new OutputStreamWriter(fileOutputStream, "GBK");
            String[] header = new String[titleList.size()];
            for (int i = 0; i < titleList.size(); i++) {
                String title = titleList.get(i);
                header[i] = title;
            }
            //默认头字段
            CSVFormat csvFormat = CSVFormat.DEFAULT.withHeader(header);
            CSVPrinter csvPrinter = new CSVPrinter(outputStreamWriter, csvFormat);

            for (Map<String, String> map : contextList) {
                csvPrinter.printRecord(map.values());
            }
            csvPrinter.flush();
            csvPrinter.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (outputStreamWriter != null) {
                    outputStreamWriter.close();
                }
                if (fileOutputStream != null) {
                    fileOutputStream.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }

        }
    }

    public static void doAppendCsv(List<String> titleList, List<LinkedHashMap<String, String>> contextList, String absFilePath) {
        BufferedWriter writer = null;
        try {
            //解决乱码,gbk
            writer = new BufferedWriter (new OutputStreamWriter (new FileOutputStream (absFilePath,true),"GBK"));
            String[] header = new String[titleList.size()];
            for (int i = 0; i < titleList.size(); i++) {
                String title = titleList.get(i);
                header[i] = title;
            }
            //表头
            CSVFormat csvFormat = CSVFormat.DEFAULT.withHeader(header);
            //默认第一行是头字段
            //CSVFormat csvFormat = CSVFormat.DEFAULT.withFirstRecordAsHeader();
            CSVPrinter csvPrinter = new CSVPrinter(writer, csvFormat);
            for (Map<String, String> map : contextList) {
                csvPrinter.printRecord(map.values());
            }
            csvPrinter.flush();
            csvPrinter.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (writer != null) {
                    writer.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }

        }
    }

说明:

(1)两个方法,doExportCsv是第一次生成的,doAppendCsv是追加的,客户的服务器是windows的,所以代码里面采用的gbk;

(2)至于入参吗,其实就是三个,一个表头,一个内容,一个路径三个参数;

小王这里有点特别的点,list里面是LinkedHashMap<String, String>,老王项目中要行转列,简单说就是要行转换成列进行展示,如下:

mysql行转列sql,下面sql的功能:

一是增加个序列,方便用户查看;

二是按照时间戳,将多个节点信息,按照时间戳行展示;

select (@i:=@i+1) AS '序号', t2.* from (select t1.SJGL_SJC as '时间戳' , 
MAX(CASE t1.SJGL_DWMC WHEN'2127PQ'THEN t1.SJGL_DWZ ELSE 0 END )2127PQ,
MAX(CASE t1.SJGL_DWMC WHEN'2128PQ'THEN t1.SJGL_DWZ ELSE 0 END )2128PQ,
MAX(CASE t1.SJGL_DWMC WHEN'3268PQ'THEN t1.SJGL_DWZ ELSE 0 END )3268PQ from zy_sjgl t1 WHERE
 t1.SJGL_SJC >= '2022-10-01 00:00:00' and t1.SJGL_SJC <= '2022-11-10 10:11:00' and t1.SJGL_DWMC 
in ('2127PQ','2128PQ','3268PQ') group by t1.SJGL_SJC order by t1.SJGL_SJC asc) t2,(SELECT @i:=0) AS itable limit 500000;

2.3 调用入参

这里需要用LinkedHashMap,否则导出的数据会乱序;

2.3.1 mybatis配置

  <select id="selectStatisSjgls" parameterType="java.util.Map" resultType="java.util.LinkedHashMap">
      </select>

2.3.2 java代码

List<LinkedHashMap<String, String>> statisSjgls =sjglMapper.selectStatisSjgls(queryMap);

List<String> stitleList = new ArrayList<>();
stitleList.add("点名");
stitleList.add("描述");
stitleList.add("最大值");
stitleList.add("最小值");
stitleList.add("平均值");
statisSjgls.add(new LinkedHashMap<>());
statisSjgls.add(new LinkedHashMap<>()); //模板格式,空两行再显示明细信息
            

2.4.最终效果


更多信息请关注公众号:「软件老王」,关注不迷路,软件老王和他的IT朋友们,分享一些他们的技术见解和生活故事。

posted @ 2022-11-29 11:46  软件老王  阅读(444)  评论(0编辑  收藏  举报