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

导入依赖

 <dependency>
     <groupId>org.apache.commons</groupId>
     <artifactId>commons-collections4</artifactId>
     <version>4.4</version>
 </dependency>

工具类

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 {
 ​
     /**
      * CSV文件列分隔符
      */
     private static final String CSV_COLUMN_SEPARATOR = ",";
 ​
     /**
      * CSV文件行分隔符
      */
     private static final String CSV_ROW_SEPARATOR = "\r\n";
 ​
     /**
      * @param dataList
      *            集合数据
      * @param titles
      *            表头部数据
      * @param keys
      *            表内容的键值
      * @param os
      *            输出流
      */
     public static void doExport(List<Map<String, Object>> 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<String, Object> 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();
     }
 ​
     /**
      * 设置Header
      *
      * @param fileName
      * @param response
      * @throws UnsupportedEncodingException
      */
     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<String,Object>>，只需要返回值类型为Map即可

    <select id="getBooks" resultType="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;
    </select>

测试

 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<Map<String,Object>
         List<Map<String, Object>> 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();
         }
     }
 }