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 {        /**       * 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> 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();      }        /**       * 设置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即可

             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();          }      }  }