在Excel 2007中打开时，CSV文件的任何列中的前导零都会丢失 [英] Leading zero in any column of a CSV file is lost when opened in Excel 2007

问题描述

我正在寻找一种在以Java导出为CSV文件时保留整数前导零的方法。以下是将前导零生成为CSV的示例。

  public class TestLargeDataset {
 
 int one_million = 1000000; 
 
 public void writeMillionRowsToCSVFile（String fqname）throws Exception {
 BufferedOutputStream bos = new BufferedOutputStream（new FileOutputStream（new File（fqname）））; 
 
 byte [] newLine =\\\
.getBytes（）; 
 
 for（int x = 0; x  bos.write（（0+ x +，）。getBytes（））; 
 bos.write（（0+（x + 1）+，）。getBytes（））; 
 bos.write（（0+（x + 2）+，）。getBytes（））; 
 bos.write（（0+（x + 3））。getBytes（））; 
 bos.write（newLine）; 
} 
 bos.close（）; 
} 
 
 / * 
 * Main。 
 * / 
 public static void main（String a []）throws Exception {
 TestLargeDataset tlr = new TestLargeDataset（）; 
 
 long startTime2 = System.nanoTime（）; 
 tlr.writeMillionRowsToCSVFile（C：\\output_+ System.currentTimeMillis（）+.csv）; 
 long diff2 =（System.nanoTime（） -  startTime2）/ 1000000000; 
 tlr.log（+ diff2 +秒（〜+（diff2 / 60）+分钟）中执行的文件写入操作。 
 
 tlr.getStatement（）。close（）; 
 tlr.getConnection（）。close（）; 
 
 tlr.log（Execution complete。）; 
} 
 
} 
  

当我打开输出文件Excel 2007，我失去前导零。有没有办法保留这些零，并在Excel中打开相同，而不转换成字符串？

思考？

附加一个双引号和标签，问题会纠正。

String yourString =002015;

使用逗号分隔逗号（，

解决方案：

\\t+ yourString +\;

I'm looking for a way to preserve leading zeros in integers when exporting to a CSV file in Java. The following is an example of generating leading zeros into a CSV.

public class TestLargeDataset {

    int one_million = 1000000;

    public void writeMillionRowsToCSVFile(String fqname) throws Exception {
        BufferedOutputStream bos = new BufferedOutputStream(new FileOutputStream(new File(fqname)));

        byte[] newLine = "\n".getBytes();

        for(int x = 0; x < one_million; x++) {
            bos.write(("0" + x + ",").getBytes());
            bos.write(("0" + (x+1) + ",").getBytes());
            bos.write(("0" + (x+2) + ",").getBytes());
            bos.write(("0" + (x+3)).getBytes());
            bos.write(newLine);
        }
        bos.close();
    }

    /*
     * Main.
     */
    public static void main(String a[]) throws Exception {
        TestLargeDataset tlr = new TestLargeDataset();      

        long startTime2 = System.nanoTime();
        tlr.writeMillionRowsToCSVFile("C:\\output_" + System.currentTimeMillis() + ".csv");
        long diff2 = (System.nanoTime() - startTime2)/1000000000;
        tlr.log("Executed file write operation in " + diff2 + " seconds (~" + (diff2/60) + " minutes)");

        tlr.getStatement().close();
        tlr.getConnection().close();

        tlr.log("Execution complete.");
    }

}

When I open the output file in Excel 2007, I lose the leading zeros. Is there a way to preserve these zeros and open the same in Excel WITHOUT converting then into Strings? The reason for not converting them into Strings is cos' I would need integer arithmetic applied to these values in the excel.

Thoughts?

解决方案

Found this solution on another forum and it worked for me.

Append a double quote and tab, problem will rectify. Tab prevent leading zeors from truncating and double quote separation of a cell when numbers has comma(,) with in it.

String yourString = "002015";

Solution:

"\"\t"+yourString + "\"";

这篇关于在Excel 2007中打开时，CSV文件的任何列中的前导零都会丢失的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！