栏目分类:
子分类:
返回
名师互学网用户登录
快速导航关闭
当前搜索
当前分类
子分类
实用工具
热门搜索
名师互学网 > IT > 软件开发 > 后端开发 > Java

SpringBoot将SQL查询出的数据导出CSV文件

Java 更新时间: 发布时间: IT归档 最新发布 模块sitemap 名妆网 法律咨询 聚返吧 英语巴士网 伯小乐 网商动力

SpringBoot将SQL查询出的数据导出CSV文件

导入依赖

 
     org.apache.commons
     commons-collections4
     4.4
 

工具类

import java.io.OutputStream;
 import java.io.UnsupportedEncodingException;
 import java.net.URLEncoder;
 import java.text.SimpleDateFormat;
 import java.util.Date;
 import java.util.List;
 import java.util.Map;
 ​
 import javax.servlet.http.HttpServletResponse;
 ​
 import org.apache.commons.collections4.CollectionUtils;
 ​
 public class CsvExportUtil {
 ​
     
     private static final String CSV_COLUMN_SEPARATOR = ",";
 ​
     
     private static final String CSV_ROW_SEPARATOR = "rn";
 ​
     
     public static void doExport(List> dataList, String titles, String keys, OutputStream os)
             throws Exception {
 ​
         // 保证线程安全
         StringBuffer buf = new StringBuffer();
 ​
         String[] titleArr = null;
         String[] keyArr = null;
 ​
         titleArr = titles.split(",");
         keyArr = keys.split(",");
 ​
         // 组装表头
         for (String title : titleArr) {
             buf.append(title).append(CSV_COLUMN_SEPARATOR);
         }
         buf.append(CSV_ROW_SEPARATOR);
 ​
         // 组装数据
         if (CollectionUtils.isNotEmpty(dataList)) {
             for (Map data : dataList) {
                 for (String key : keyArr) {
                     buf.append(data.get(key)).append(CSV_COLUMN_SEPARATOR);
                 }
                 buf.append(CSV_ROW_SEPARATOR);
             }
         }
 ​
         // 写出响应
         os.write(buf.toString().getBytes("GBK"));
         os.flush();
     }
 ​
     
     public static void responseSetProperties(String fileName, HttpServletResponse response)
             throws UnsupportedEncodingException {
         // 设置文件后缀
         SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
         String fn = fileName + sdf.format(new Date()) + ".csv";
         // 读取字符编码
         String utf = "UTF-8";
 ​
         // 设置响应
         response.setContentType("application/ms-txt.numberformat:@");
         response.setCharacterEncoding(utf);
         response.setHeader("Pragma", "public");
         response.setHeader("Cache-Control", "max-age=30");
         response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(fn, utf));
     }
 }

在Mybatis查询出List>,只需要返回值类型为Map即可

    
        select auth_name,
        book_create_time,
        book_name,
               book_id,
        book_price,
        publish_name,
        classify_name,
        book_count,
        book_sell
        from book_auth_publish
        where book_is_delete = 0;
    

测试

 import com.lixianhe.dao.BookMapper;
 import com.lixianhe.utils.CsvExportUtil;
 import lombok.extern.slf4j.Slf4j;
 import org.springframework.beans.factory.annotation.Autowired;
 import org.springframework.web.bind.annotation.GetMapping;
 import org.springframework.web.bind.annotation.RestController;
 ​
 import javax.servlet.http.HttpServletResponse;
 import java.io.OutputStream;
 import java.util.List;
 import java.util.Map;
 ​
 @RestController
 @Slf4j
 public class TestController {
 ​
     @Autowired
     private BookMapper bookMapper;
 ​
     @GetMapping("/c/virtualMachine/export")
     public void export(HttpServletResponse response) {
         // 返回的数据必须是List
         List> books = bookMapper.getBooks();
         
         String titles = "书编号,书名,出版社,价格,作者,分类,库存,创建时间,销量"; // 设置表头
         // 设置每列字段
         String keys = "book_id,book_name,publish_name,book_price,auth_name,classify_name,book_count,book_create,book_sell";
         // 设置导出文件前缀
         String fName = "bookstore_";
         // 文件导出
         try {
             OutputStream os = response.getOutputStream();
             CsvExportUtil.responseSetProperties(fName, response);
             CsvExportUtil.doExport(books, titles, keys, os);
             os.close();
 ​
         } catch (Exception e) {
             e.printStackTrace();
         }
     }
 }

转载请注明:文章转载自 www.mshxw.com
本文地址:https://www.mshxw.com/it/959607.html
我们一直用心在做
关于我们 文章归档 网站地图 联系我们

版权所有 (c)2021-2022 MSHXW.COM

ICP备案号:晋ICP备2021003244-6号