2020-12-23

java导出csv文件遇到的问题

1.导出的csv文件数据行数和实际的数据条数不一致

2.csv日期格式问题（丢零）

3.csv文件分隔符问题

目录

java导出csv文件遇到的问题

1.导出的csv文件数据行数和实际的数据条数不一致

由于在写代码的时候 打印了写入csv文件的条数和实际的条数，日志打印的两个条数是一致的，就没有仔细查看csv文件的具体条数，当想起来看csv文件中的数据时，猛然发现数据条数和打印的条数不一致，找了很多解决办法都没有解决，网上各种换行导致的错行，英文逗号双引号导致的错行解决方案，试了都不行。

然后看到逗号分隔符导致的错行，如果字段值里面有英文逗号会被当做分隔符，于是将英文逗号替换成中文逗号，问题解决。

2.csv日期格式问题（丢零）

由于客户要求csv文件里日期需要展示yyyy/mm/dd的格式，但是csv文件中，打开总是丢零，网上很多解决办法是在日期前面加  '\t'制表符，试了也不管用，最终发现，在日期每个数字前加制表符，问题就解决了

if(name.equals("campaignStartDate")||"campaignEndDate".equals(name)||
    "creativeStartDate".equals(name)||"creativeEndDate".equals(name)){
    String[] textvalueArr = textValue.substring(0,10).split("-");
    StringBuilder sb = new StringBuilder();
    sb.append("\t").append(textvalueArr[0]).append("/").append("\t").append(textvalueArr[1]).append("/")
            .append("\t").append(textvalueArr[2]).append(textValue.substring(10));
    textValue = sb.toString() +"\t";
}

3.csv文件分隔符问题

解决方法同 1；

4.csv文件utf-8打开乱码问题

utf-8编码导出的csv文件打开后中文乱码，如果需要打开后不乱码，则需要使用BOM utf-8的编码，解决方法如下：

参考链接：https://www.cnblogs.com/miaomiaokaixin/p/10885302.html

1.继承自CsvWriter类：

package cn.com.connext.cmp.content.management.util.csvutil;

import com.csvreader.CsvWriter;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.io.OutputStreamWriter;
import java.io.PrintWriter;
import java.io.Writer;
import java.nio.charset.Charset;

/**
 * User: xumengdi
 * Date: 2020/12/23
 */
public class CsvWriterExtend extends CsvWriter {
    private PrintWriter outputStream;
    private String fileName;
    private boolean firstColumn;
    private boolean useCustomRecordDelimiter;
    private Charset charset;
    private CsvWriterExtend.UserSettings userSettings;
    private boolean initialized;
    private boolean closed;
    public static final int ESCAPE_MODE_DOUBLED = 1;
    public static final int ESCAPE_MODE_BACKSLASH = 2;
    private String sheetFirstName;

    public CsvWriterExtend(String var1, char var2, Charset var3,String sheetFirstName) {
        super(var1,var2,var3);
        this.outputStream = null;
        this.fileName = null;
        this.firstColumn = true;
        this.useCustomRecordDelimiter = false;
        this.charset = null;
        this.userSettings = new CsvWriterExtend.UserSettings();
        this.initialized = false;
        this.closed = false;
        //1：这里加了一个csv表头的第一个名字字段，用来解决第一个问题：中文乱码问题
        this.sheetFirstName = sheetFirstName;
        if (var1 == null) {
            throw new IllegalArgumentException("Parameter fileName can not be null.");
        } else if (var3 == null) {
            throw new IllegalArgumentException("Parameter charset can not be null.");
        } else {
            this.fileName = var1;
            this.userSettings.Delimiter = var2;
            this.charset = var3;
        }
    }

    public CsvWriterExtend(Writer var1, char var2) {
        super(var1,var2);
        this.outputStream = null;
        this.fileName = null;
        this.firstColumn = true;
        this.useCustomRecordDelimiter = false;
        this.charset = null;
        this.userSettings = new CsvWriterExtend.UserSettings();
        this.initialized = false;
        this.closed = false;
        if (var1 == null) {
            throw new IllegalArgumentException("Parameter outputStream can not be null.");
        } else {
            this.outputStream = new PrintWriter(var1);
            this.userSettings.Delimiter = var2;
            this.initialized = true;
        }
    }

    public CsvWriterExtend(OutputStream var1, char var2, Charset var3) {
        this(new OutputStreamWriter(var1, var3), var2);
    }

    public char getDelimiter() {
        return this.userSettings.Delimiter;
    }

    public void setDelimiter(char var1) {
        this.userSettings.Delimiter = var1;
    }

    public char getRecordDelimiter() {
        return this.userSettings.RecordDelimiter;
    }

    public void setRecordDelimiter(char var1) {
        this.useCustomRecordDelimiter = true;
        this.userSettings.RecordDelimiter = var1;
    }

    public char getTextQualifier() {
        return this.userSettings.TextQualifier;
    }

    public void setTextQualifier(char var1) {
        this.userSettings.TextQualifier = var1;
    }

    public boolean getUseTextQualifier() {
        return this.userSettings.UseTextQualifier;
    }

    public void setUseTextQualifier(boolean var1) {
        this.userSettings.UseTextQualifier = var1;
    }

    public int getEscapeMode() {
        return this.userSettings.EscapeMode;
    }

    public void setEscapeMode(int var1) {
        this.userSettings.EscapeMode = var1;
    }

    public void setComment(char var1) {
        this.userSettings.Comment = var1;
    }

    public char getComment() {
        return this.userSettings.Comment;
    }

    public boolean getForceQualifier() {
        return this.userSettings.ForceQualifier;
    }

    public void setForceQualifier(boolean var1) {
        this.userSettings.ForceQualifier = var1;
    }

    public void write(String var1, boolean var2) throws IOException {
        this.checkClosed();
        this.checkInit();
        if (var1 == null) {
            var1 = "";
        }
        //2：这里加了一个判断条件，用来解决第一个问题：中文乱码问题
        //加的目的是：如果是写入bom则bom后面不追加逗号，即在bom后面和第一个表头前面，即两者之间不追加逗号,sheetFirstName为第一个表头的名字根据实际传入
        if (!this.firstColumn && !var1.contentEquals(sheetFirstName) ){
            this.outputStream.write(this.userSettings.Delimiter);
        }

        boolean var3 = this.userSettings.ForceQualifier;
        if (!var2 && var1.length() > 0) {
            var1 = var1.trim();
        }

        if (!var3 && this.userSettings.UseTextQualifier && (var1.indexOf(this.userSettings.TextQualifier) > -1 || var1.indexOf(this.userSettings.Delimiter) > -1 || !this.useCustomRecordDelimiter && (var1.indexOf(10) > -1 || var1.indexOf(13) > -1) || this.useCustomRecordDelimiter && var1.indexOf(this.userSettings.RecordDelimiter) > -1 || this.firstColumn && var1.length() > 0 && var1.charAt(0) == this.userSettings.Comment || this.firstColumn && var1.length() == 0)) {
            var3 = true;
        }

        if (this.userSettings.UseTextQualifier && !var3 && var1.length() > 0 && var2) {
            char var4 = var1.charAt(0);
            if (var4 == ' ' || var4 == '\t') {
                var3 = true;
            }

            if (!var3 && var1.length() > 1) {
                char var5 = var1.charAt(var1.length() - 1);
                if (var5 == ' ' || var5 == '\t') {
                    var3 = true;
                }
            }
        }
        //3：这里加了一个if语句，是为了解决第三个问题：csv""显示null的问题
        if(!this.firstColumn && var1.length()==0){
            var3=true;
        }
        if (var3) {
            this.outputStream.write(this.userSettings.TextQualifier);
            if (this.userSettings.EscapeMode == 2) {
                var1 = replace(var1, "\\", "\\\\");
                var1 = replace(var1, "" + this.userSettings.TextQualifier, "\\" + this.userSettings.TextQualifier);
            } else {
                var1 = replace(var1, "" + this.userSettings.TextQualifier, "" + this.userSettings.TextQualifier + this.userSettings.TextQualifier);
            }
        } else if (this.userSettings.EscapeMode == 2) {
            var1 = replace(var1, "\\", "\\\\");
            var1 = replace(var1, "" + this.userSettings.Delimiter, "\\" + this.userSettings.Delimiter);
            if (this.useCustomRecordDelimiter) {
                var1 = replace(var1, "" + this.userSettings.RecordDelimiter, "\\" + this.userSettings.RecordDelimiter);
            } else {
                var1 = replace(var1, "\r", "\\\r");
                var1 = replace(var1, "\n", "\\\n");
            }

            if (this.firstColumn && var1.length() > 0 && var1.charAt(0) == this.userSettings.Comment) {
                if (var1.length() > 1) {
                    var1 = "\\" + this.userSettings.Comment + var1.substring(1);
                } else {
                    var1 = "\\" + this.userSettings.Comment;
                }
            }
        }


        this.outputStream.write(var1);

        if (var3) {
            this.outputStream.write(this.userSettings.TextQualifier);
        }

        this.firstColumn = false;
    }

    public void write(String var1) throws IOException {
        this.write(var1, false);
    }

    public void writeComment(String var1) throws IOException {
        this.checkClosed();
        this.checkInit();
        this.outputStream.write(this.userSettings.Comment);
        this.outputStream.write(var1);
        if (this.useCustomRecordDelimiter) {
            this.outputStream.write(this.userSettings.RecordDelimiter);
        } else {
            this.outputStream.println();
        }

        this.firstColumn = true;
    }

    public void writeRecord(String[] var1, boolean var2) throws IOException {
        if (var1 != null && var1.length > 0) {
            for(int var3 = 0; var3 < var1.length; ++var3) {
                this.write(var1[var3], var2);
            }

            this.endRecord();
        }

    }

    public void writeRecord(String[] var1) throws IOException {
        this.writeRecord(var1, false);
    }

    public void writeLastRecord(String[] var1) throws IOException {
        this.writeLastRecord(var1, false);
    }

    //4：这里加了两个方法writeLastRecord和endLastRecord，用来解决第二个问题：某尾总是多一行空行的问题
    public void writeLastRecord(String[] var1, boolean var2) throws IOException {
        if (var1 != null && var1.length > 0) {
            for(int var3 = 0; var3 < var1.length; ++var3) {
                this.write(var1[var3], var2);
            }

            this.endLastRecord();
        }

    }
    public void endLastRecord() throws IOException {
        this.checkClosed();
        this.checkInit();
        if (this.useCustomRecordDelimiter) {
            this.outputStream.write(this.userSettings.RecordDelimiter);
        } else {//主要在下面这一行，当执行这个方法来结尾的时候是不追加换行符的
            this.outputStream.print("");
        }

        this.firstColumn = true;
    }

    public void endRecord() throws IOException {
        this.checkClosed();
        this.checkInit();
        if (this.useCustomRecordDelimiter) {
            this.outputStream.write(this.userSettings.RecordDelimiter);
        } else {
            this.outputStream.println();
        }

        this.firstColumn = true;
    }

    private void checkInit() throws IOException {
        if (!this.initialized) {
            if (this.fileName != null) {
                this.outputStream = new PrintWriter(new OutputStreamWriter(new FileOutputStream(this.fileName), this.charset));
            }

            this.initialized = true;
        }

    }

    public void flush() {
        this.outputStream.flush();
    }

    public void close() {
        if (!this.closed) {
            this.close(true);
            this.closed = true;
        }

    }

    private void close(boolean var1) {
        if (!this.closed) {
            if (var1) {
                this.charset = null;
            }

            try {
                if (this.initialized) {
                    this.outputStream.close();
                }
            } catch (Exception var3) {
                ;
            }

            this.outputStream = null;
            this.closed = true;
        }

    }

    private void checkClosed() throws IOException {
        if (this.closed) {
            throw new IOException("This instance of the CsvWriter class has already been closed.");
        }
    }

    protected void finalize() {
        this.close(false);
    }

    public static String replace(String var0, String var1, String var2) {
        int var3 = var1.length();
        int var4 = var0.indexOf(var1);
        if (var4 <= -1) {
            return var0;
        } else {
            StringBuffer var5 = new StringBuffer();

            int var6;
            for(var6 = 0; var4 != -1; var4 = var0.indexOf(var1, var6)) {
                var5.append(var0.substring(var6, var4));
                var5.append(var2);
                var6 = var4 + var3;
            }

            var5.append(var0.substring(var6));
            return var5.toString();
        }
    }

    private class UserSettings {
        public char TextQualifier = '"';
        public boolean UseTextQualifier = true;
        public char Delimiter = ',';
        public char RecordDelimiter = 0;
        public char Comment = '#';
        public int EscapeMode = 1;
        public boolean ForceQualifier = false;

        public UserSettings() {
        }
    }

    private class Letters {
        public static final char LF = '\n';
        public static final char CR = '\r';
        public static final char QUOTE = '"';
        public static final char COMMA = ',';
        public static final char SPACE = ' ';
        public static final char TAB = '\t';
        public static final char POUND = '#';
        public static final char BACKSLASH = '\\';
        public static final char NULL = '\u0000';

        private Letters() {
        }
    }
}

---

4.csv文件utf-8打开乱码问题