导出CSV文件

​

 从数据库导出csv文件

从HeidiSQL 导数据出来成.csv文件

SELECT * FROM csv INTO OUTFILE 'C:\\feiniu\\note\\csv\\demo.csv' fields terminated by ','

CSV是什么

跟Excel表差不多

csv与excel对比：

• csv只能用于存储纯文本内容，excel不仅支持纯文本内容还支持二进制数据
• csv可以看做是excel的轻量级简单版实现，excel比csv更加强大
• csv文件可以被excel软件直接打开，csv文件一般用于表格数据的传输

 

纯文本形式存储表格数据，分隔字符有：逗号、分号 空格等

每行一条记录

 

关于操作csv文件

1. 读取CSV 文件转Java对象
2. CSV转Excel
3. CSV转对象后存数据库
4. 下载（导出）CSV文件

依赖：opencsv、easyexcel

 

导出Java对象转CSV导出文件

maven: OpenCSV

   <dependency>
            <groupId>com.opencsv</groupId>
            <artifactId>opencsv</artifactId>
            <version>5.6</version>
   </dependency>

controller

@RestController
@Slf4j
@RequestMapping("/file")
public class FileController extends BaseController{
<span class="hljs-meta">@Autowired
<span class="hljs-keyword">private FileService fileService;

<span class="hljs-meta">@PostMapping("/downloadUserListCsv")
<span class="hljs-keyword">public <span class="hljs-keyword">void <span class="hljs-title function_">downloadUserListCsv<span class="hljs-params">(HttpServletResponse response) {
    fileService.downUserListCsv(DownloadPublicUtil.UserListCsvHeader, response);
}

}

service

@Service
public class FileServiceImpl implements FileService {
    @Override
    public void downUserListCsv(String[] head, HttpServletResponse response) {
    List<User> userList = List.of(
            User.builder().userId(<span class="hljs-string">"1").userName(<span class="hljs-string">"张三").sex(<span class="hljs-string">"男").build(),
            User.builder().userId(<span class="hljs-string">"2").userName(<span class="hljs-string">"李四").sex(<span class="hljs-string">"男").build(),
            User.builder().userId(<span class="hljs-string">"3").userName(<span class="hljs-string">"王五").sex(<span class="hljs-string">"男").build()
    );

    ExportCsvUtils.exportCsv(head,userList,User.class,<span class="hljs-string">"user-list-1",response);
}

}

utils

ExportCsvUtils

public class ExportCsvUtils {
<span class="hljs-keyword">private <span class="hljs-title function_">ExportCsvUtils<span class="hljs-params">(){

}

<span class="hljs-keyword">public <span class="hljs-keyword">static &lt;T&gt; <span class="hljs-keyword">void <span class="hljs-title function_">exportCsv<span class="hljs-params">(String[] headers, List&lt;T&gt; list, Class c, String fileName, HttpServletResponse response){
    <span class="hljs-type">OutputStream <span class="hljs-variable">outputStream <span class="hljs-operator">= <span class="hljs-literal">null;
    <span class="hljs-type">CSVWriter <span class="hljs-variable">csvWriter <span class="hljs-operator">= <span class="hljs-literal">null;

    <span class="hljs-keyword">try {
        DownloadPublicUtil.setResponse(response, FileTypeEnum.CSV,
                fileName, DownloadCharsetEnum.WINDOWS_1252);
        outputStream = response.getOutputStream();

//          prevent from turning Chinese into garbled code

outputStream.write(0xEF);

outputStream.write(0xBB);

outputStream.write(0xBF);

//     outputStream = response.getOutputStream();
        csvWriter = <span class="hljs-keyword">new <span class="hljs-title class_">CSVWriter(<span class="hljs-keyword">new <span class="hljs-title class_">OutputStreamWriter(outputStream, StandardCharsets.UTF_8));
        <span class="hljs-comment">//写入标题行
        csvWriter.writeNext(headers);

        Map&lt;String, String&gt; map = <span class="hljs-keyword">new <span class="hljs-title class_">HashMap&lt;&gt;();
        Field[] declaredFields = c.getDeclaredFields();

        <span class="hljs-comment">// each row
        <span class="hljs-keyword">for (T t : list) {
            <span class="hljs-keyword">for (Field declaredField : declaredFields){
                declaredField.setAccessible(<span class="hljs-literal">true);
                <span class="hljs-type">Object <span class="hljs-variable">o <span class="hljs-operator">= <span class="hljs-literal">null;

                <span class="hljs-keyword">try {
                    o = declaredField.get(t);
                    <span class="hljs-keyword">if (o <span class="hljs-keyword">instanceof String){
                        <span class="hljs-comment">// userId -&gt; UserId
                        <span class="hljs-type">String <span class="hljs-variable">fieldName <span class="hljs-operator">= declaredField.getName();
                        <span class="hljs-type">String <span class="hljs-variable">firstChar <span class="hljs-operator">= fieldName.substring(<span class="hljs-number">0, <span class="hljs-number">1).toUpperCase();
                        fieldName = firstChar + fieldName.substring(<span class="hljs-number">1);
                        map.put(fieldName,(String) o);
                    }
                } <span class="hljs-keyword">catch (IllegalAccessException e) {
                    <span class="hljs-keyword">throw <span class="hljs-keyword">new <span class="hljs-title class_">RuntimeException(e);
                }
            }
            ArrayList&lt;String&gt; values = <span class="hljs-keyword">new <span class="hljs-title class_">ArrayList&lt;&gt;();

            <span class="hljs-keyword">for(String header : headers){
                <span class="hljs-comment">//User Id -&gt; UserId
                header = header.replace(<span class="hljs-string">" ",<span class="hljs-string">"");
                <span class="hljs-type">String <span class="hljs-variable">value <span class="hljs-operator">= map.get(header);
                values.add(value);
            }
            <span class="hljs-comment">//  写入数据行
            csvWriter.writeNext(values.toArray(<span class="hljs-keyword">new <span class="hljs-title class_">String[<span class="hljs-number">0]));
            csvWriter.flush();
        }

    } <span class="hljs-keyword">catch (IOException e) {
        <span class="hljs-keyword">throw <span class="hljs-keyword">new <span class="hljs-title class_">RuntimeException(e);
    }<span class="hljs-keyword">finally {
            <span class="hljs-keyword">try {
                <span class="hljs-keyword">if (csvWriter != <span class="hljs-literal">null) {
                    csvWriter.close();
                }
                <span class="hljs-keyword">if (outputStream != <span class="hljs-literal">null) {
                    outputStream.close();
                }
            } <span class="hljs-keyword">catch (IOException e) {
                <span class="hljs-keyword">throw <span class="hljs-keyword">new <span class="hljs-title class_">RuntimeException(e);
            }
        }
    }
}

DownloadPublicUtil

public class DownloadPublicUtil {
<span class="hljs-keyword">public <span class="hljs-keyword">static String[] UserListCsvHeader =  {<span class="hljs-string">"User Id",<span class="hljs-string">"User Name",<span class="hljs-string">"Sex"};

<span class="hljs-keyword">private <span class="hljs-title function_">DownloadPublicUtil<span class="hljs-params">(){

}

<span class="hljs-keyword">public <span class="hljs-keyword">static <span class="hljs-keyword">void <span class="hljs-title function_">setResponse<span class="hljs-params">(HttpServletResponse response, FileTypeEnum fileTypeEnum, String fileName, DownloadCharsetEnum charsets) {
    fileName = URLEncoder.encode(fileName, StandardCharsets.UTF_8).replaceAll(<span class="hljs-string">"\\+", <span class="hljs-string">"%20");
    response.reset();
    response.setCharacterEncoding(charsets.getVal());
    <span class="hljs-keyword">if (fileTypeEnum.equals(FileTypeEnum.CSV)) {
        response.setContentType(<span class="hljs-string">"text/" + fileTypeEnum + <span class="hljs-string">";charset=" + charsets.getVal());
    } <span class="hljs-keyword">else {
        response.setContentType(<span class="hljs-string">"application/" + fileTypeEnum + <span class="hljs-string">";charset=" + charsets.getVal());
    }
    response.setHeader(<span class="hljs-string">"Content-Disposition", <span class="hljs-string">"attachment; filename=" + fileName + fileTypeEnum.getSuffix());
}

}

enum

@Getter
@AllArgsConstructor
public enum DownloadCharsetEnum {
UTF_8(<span class="hljs-string">"utf-8"),
WINDOWS_1252(<span class="hljs-string">"windows-1252");

<span class="hljs-keyword">private <span class="hljs-keyword">final String val;

}

 

@Getter
public enum FileTypeEnum {
    EXCEL(".xlsx"),
    PDF(".pdf"),
    CSV(".csv");
<span class="hljs-keyword">private <span class="hljs-keyword">final String suffix;

FileTypeEnum(String suffix) {
    <span class="hljs-built_in">this.suffix = suffix;
}

}