CSV文件读取与生成以及Excel文件的读取生成

 

其一：CSV文件读取与生成

 ①CSV文件读取

     读取操作类：

public class CSVReader {
    private BufferedReader br;

    private boolean hasNext = true;

    private char separator;//分隔符

    private char quotechar;//引号符

    private int skipLines;//

    private boolean linesSkiped; //转移线

    /** The default separator to use if none is supplied to the constructor. */
    public static final char DEFAULT_SEPARATOR = ',';

    /**
     * The default quote character to use if none is supplied to the
     * constructor.
     */
    public static final char DEFAULT_QUOTE_CHARACTER = '"';

    /**
     * The default line to start reading.
     */
    public static final int DEFAULT_SKIP_LINES = 0;

    /**
     * Constructs CSVReader using a comma for the separator.
     *
     * @param reader
     *            the reader to an underlying CSV source.
     */
    public CSVReader(Reader reader) {
        this(reader, DEFAULT_SEPARATOR);
    }

    /**
     * Constructs CSVReader with supplied separator.
     *
     * @param reader
     *            the reader to an underlying CSV source.
     * @param separator
     *            the delimiter to use for separating entries.
     */
    public CSVReader(Reader reader, char separator) {
        this(reader, separator, DEFAULT_QUOTE_CHARACTER);
    }

    /**
     * Constructs CSVReader with supplied separator and quote char.
     *
     * @param reader
     *            the reader to an underlying CSV source.
     * @param separator
     *            the delimiter to use for separating entries
     * @param quotechar
     *            the character to use for quoted elements
     */
    public CSVReader(Reader reader, char separator, char quotechar) {
        this(reader, separator, quotechar, DEFAULT_SKIP_LINES);
    }

    /**
     * Constructs CSVReader with supplied separator and quote char.
     *
     * @param reader
     *            the reader to an underlying CSV source.
     * @param separator
     *            the delimiter to use for separating entries
     * @param quotechar
     *            the character to use for quoted elements
     * @param line
     *            the line number to skip for start reading
     */
    public CSVReader(Reader reader, char separator, char quotechar, int line) {
        this.br = new BufferedReader(reader);
        this.separator = separator;
        this.quotechar = quotechar;
        this.skipLines = line;
    }

    /**
     * Reads the entire file into a List with each element being a String[] of
     * tokens.
     *
     * @return a List of String[], with each String[] representing a line of the
     *         file.
     *
     * @throws IOException
     *             if bad things happen during the read
     */
    public List readAll() throws IOException {

        List allElements = new ArrayList();
        while (hasNext) {
            String[] nextLineAsTokens = readNext();
            if (nextLineAsTokens != null)
                allElements.add(nextLineAsTokens);
        }
        return allElements;

    }

    /**
     * Reads the next line from the buffer and converts to a string array.
     *
     * @return a string array with each comma-separated element as a separate
     *         entry.
     *
     * @throws IOException
     *             if bad things happen during the read
     */
    public String[] readNext() throws IOException {

        String nextLine = getNextLine();
        return hasNext ? parseLine(nextLine) : null;
    }

    /**
     * Reads the next line from the file.
     *
     * @return the next line from the file without trailing newline
     * @throws IOException
     *             if bad things happen during the read
     */
    private String getNextLine() throws IOException {
        if (!this.linesSkiped) {
            for (int i = 0; i < skipLines; i++) {
                br.readLine();
            }
            this.linesSkiped = true;
        }
        String nextLine = br.readLine();
        if (nextLine == null) {
            hasNext = false;
        }
        return hasNext ? nextLine : null;
    }

    /**
     * Parses an incoming String and returns an array of elements.
     *
     * @param nextLine
     *            the string to parse
     * @return the comma-tokenized list of elements, or null if nextLine is null
     * @throws IOException
     *             if bad things happen during the read
     */
    private String[] parseLine(String nextLine) throws IOException {

        if (nextLine == null) {
            return null;
        }

        List tokensOnThisLine = new ArrayList();
        StringBuffer sb = new StringBuffer();
        boolean inQuotes = false;
        do {
            if (inQuotes) {
                // continuing a quoted section, reappend newline
                sb.append("\n");
                nextLine = getNextLine();
                if (nextLine == null)
                    break;
            }
            for (int i = 0; i < nextLine.length(); i++) {

                char c = nextLine.charAt(i);
                if (c == quotechar) {
                    // this gets complex... the quote may end a quoted block, or
                    // escape another quote.
                    // do a 1-char lookahead:
                    if (inQuotes // we are in quotes, therefore there can be
                            // escaped quotes in here.
                            && nextLine.length() > (i + 1) // there is indeed
                            // another character
                            // to check.
                            && nextLine.charAt(i + 1) == quotechar) { // ..and
                        // that
                        // char.
                        // is a
                        // quote
                        // also.
                        // we have two quote chars in a row == one quote char,
                        // so consume them both and
                        // put one on the token. we do *not* exit the quoted
                        // text.
                        sb.append(nextLine.charAt(i + 1));
                        i++;
                    } else {
                        inQuotes = !inQuotes;
                        // the tricky case of an embedded quote in the middle:
                        // a,bc"d"ef,g
                        if (i > 2 // not on the begining of the line
                                && nextLine.charAt(i - 1) != this.separator // not
                                // at
                                // the
                                // begining
                                // of
                                // an
                                // escape
                                // sequence
                                && nextLine.length() > (i + 1)
                                && nextLine.charAt(i + 1) != this.separator // not
                        // at
                        // the
                        // end
                        // of
                        // an
                        // escape
                        // sequence
                        ) {
                            sb.append(c);
                        }
                    }
                } else if (c == separator && !inQuotes) {
                    tokensOnThisLine.add(sb.toString());
                    sb = new StringBuffer(); // start work on next token
                } else {
                    sb.append(c);
                }
            }
        } while (inQuotes);
        tokensOnThisLine.add(sb.toString());
        return (String[]) tokensOnThisLine.toArray(new String[0]);

    }

    /**
     * Closes the underlying reader.
     *
     * @throws IOException
     *             if the close fails
     */
    public void close() throws IOException {
        br.close();
    }

}

    读取测试类

  

public class CSVReaderTest {
    CSVReader csvr;

    /**
     * Setup the test.
     */
    @Before
    public void init() throws Exception {
        StringBuffer sb = new StringBuffer();
        sb.append("a,b,c").append("\n"); // standard case
        sb.append("a,\"b,b,b\",c").append("\n"); // quoted elements
        sb.append(",,").append("\n"); // empty elements
        sb.append("a,\"PO Box 123,\nKippax,ACT. 2615.\nAustralia\",d.\n");//Glen \"The Man\" Smith
        sb.append("\"Glen \"\"The Man\"\" Smith\",Athlete,Developer\n"); // Test
        // quoted
        // quote
        // chars
        sb.append("\"\"\"\"\"\",\"test\"\n"); // """""","test" representing: "",
        // test
        sb.append("\"a\nb\",b,\"\nd\",e\n");
        csvr = new CSVReader(new FileReader("d:/myfile.csv"));//这种方式就是读取文件了

        //csvr = new CSVReader(new StringReader(sb.toString()));//这种方式就是读取字符串了
    }
    //测试读取文件
    @Test
    public void test1() throws IOException{

        CSVReader c = new CSVReader(new FileReader("d:/myfile.csv"), ',',
                '\"', 1);
        String[] nextline=c.readNext();
        System.out.println(nextline[0]);
        assertEquals("CRM4005", nextline[0]);
    }

    /**
     * Tests iterating over a reader.
     *
     * @throws IOException
     *             if the reader fails.
     */

    public void ParseLine() throws IOException {

        // test normal case //测试普通示例
        String[] nextLine = csvr.readNext(); //第一行
        assertEquals("a", nextLine[0]); //第一行第一个元素
        assertEquals("b", nextLine[1]); //第一行第二个元素
        assertEquals("c", nextLine[2]); //第一行第三个元素

        // test quoted commas 测试引用起来的逗号
        nextLine = csvr.readNext();
        assertEquals("a", nextLine[0]);
        assertEquals("b,b,b", nextLine[1]);
        assertEquals("c", nextLine[2]);

        // test empty elements 测试空元素
        nextLine = csvr.readNext();
        assertEquals(3, nextLine.length);

        // test multiline quoted //测试多行引用的
        nextLine = csvr.readNext();
        assertEquals(3, nextLine.length);

        // test quoted quote chars //测试引用起来的引号字符
        nextLine = csvr.readNext();
        assertEquals("Glen \"The Man\" Smith", nextLine[0]);

        nextLine = csvr.readNext();
        assertTrue(nextLine[0].equals("\"\"")); // check the tricky situation //检查复杂的位置
        assertTrue(nextLine[1].equals("test")); // make sure we didn't ruin the
        // next field.. 确保不破坏下一个域

        nextLine = csvr.readNext();
        assertEquals(4, nextLine.length);

        // test end of stream 测试流的结尾
        assertEquals(null, csvr.readNext());

    }

    /**
     * Test parsing to a list.
     *
     * @throws IOException
     *             if the reader fails.
     */
    @SuppressWarnings("unchecked")

    public void testParseAll() throws IOException {

        List allElements = csvr.readAll();
        assertEquals(7, allElements.size());//应该指的是总共有多少行

    }

    /**
     * Tests constructors with optional delimiters and optional quote char.
     *
     * @throws IOException
     *             if the reader fails.
     */

    public void testOptionalConstructors() throws IOException {

        StringBuffer sb = new StringBuffer();
        sb.append("a\tb\tc").append("\n"); // tab separated case
        sb.append("a\t'b\tb\tb'\tc").append("\n"); // single quoted elements
        CSVReader c = new CSVReader(new StringReader(sb.toString()), '\t', '\'');
        //上面的制定了分隔符为\t，指定了引号为单引号
        String[] nextLine = c.readNext();
        assertEquals(3, nextLine.length);

        nextLine = c.readNext();
        assertEquals(3, nextLine.length);

    }

    /**
     * Tests option to skip the first few lines of a file.
     *
     * @throws IOException
     *             if bad things happen
     */

    public void testSkippingLines() throws IOException {

        StringBuffer sb = new StringBuffer();
        sb.append("Skip this line\t with tab").append("\n"); // should skip this
        sb.append("And this line too").append("\n"); // and this
        sb.append("a\t'b\tb\tb'\tc").append("\n"); // single quoted elements
        CSVReader c = new CSVReader(new StringReader(sb.toString()), '\t',
                '\'', 2);//跳过两行来读取文本，那么读取的当然是第三行了

        String[] nextLine = c.readNext();
        assertEquals(3, nextLine.length);

        assertEquals("a", nextLine[0]);
    }

    /**
     * Tests quotes in the middle of an element.
     *
     * @throws IOException
     *             if bad things happen
     */

    public void testParsedLineWithInternalQuota() throws IOException {

        StringBuffer sb = new StringBuffer();

        sb.append("a,123\"4\"567,c").append("\n");// a,123"4",c

        CSVReader c = new CSVReader(new StringReader(sb.toString()));

        String[] nextLine = c.readNext();
        assertEquals(3, nextLine.length);

        System.out.println(nextLine[1]);
        assertEquals("123\"4\"567", nextLine[1]);

    }

    /**
     * The Test Runner for commandline use.
     *
     * @param args
     *            no args required
     */
    public static void main(String args[]) {
        junit.textui.TestRunner.run(CSVReaderTest.class);//这个主要是用来测试继承自TestCase类的所有方法，并且方法名称那个以test开头，在此我没有继承所以这里报错正常
    }

}

 

 ②CSV文件写入

    文件写入操作类

    

public class CSVWriter {
    private Writer rawWriter;

    private PrintWriter pw;

    private char separator;

    private char quotechar;

    private char escapechar;

    private String lineEnd;

    /** The character used for escaping quotes. */
    public static final char DEFAULT_ESCAPE_CHARACTER = '"';

    /** The default separator to use if none is supplied to the constructor. */
    public static final char DEFAULT_SEPARATOR = ',';

    /**
     * The default quote character to use if none is supplied to the
     * constructor.
     */
    public static final char DEFAULT_QUOTE_CHARACTER = '"';

    /** The quote constant to use when you wish to suppress all quoting. */
    public static final char NO_QUOTE_CHARACTER = '\u0000';

    /** The escape constant to use when you wish to suppress all escaping. */
    public static final char NO_ESCAPE_CHARACTER = '\u0000';

    /** Default line terminator uses platform encoding. */
    public static final String DEFAULT_LINE_END = "\n";

    private static final SimpleDateFormat TIMESTAMP_FORMATTER = new SimpleDateFormat(
            "dd-MMM-yyyy HH:mm:ss");

    private static final SimpleDateFormat DATE_FORMATTER = new SimpleDateFormat(
            "dd-MMM-yyyy");

    /**
     * Constructs CSVWriter using a comma for the separator.
     *
     * @param writer
     *            the writer to an underlying CSV source.
     */
    public CSVWriter(Writer writer) {
        this(writer, DEFAULT_SEPARATOR);
    }

    /**
     * Constructs CSVWriter with supplied separator.
     *
     * @param writer
     *            the writer to an underlying CSV source.
     * @param separator
     *            the delimiter to use for separating entries.
     */
    public CSVWriter(Writer writer, char separator) {
        this(writer, separator, DEFAULT_QUOTE_CHARACTER);
    }

    /**
     * Constructs CSVWriter with supplied separator and quote char.
     *
     * @param writer
     *            the writer to an underlying CSV source.
     * @param separator
     *            the delimiter to use for separating entries
     * @param quotechar
     *            the character to use for quoted elements
     */
    public CSVWriter(Writer writer, char separator, char quotechar) {
        this(writer, separator, quotechar, DEFAULT_ESCAPE_CHARACTER);
    }

    /**
     * Constructs CSVWriter with supplied separator and quote char.
     *
     * @param writer
     *            the writer to an underlying CSV source.
     * @param separator
     *            the delimiter to use for separating entries
     * @param quotechar
     *            the character to use for quoted elements
     * @param escapechar
     *            the character to use for escaping quotechars or escapechars
     */
    public CSVWriter(Writer writer, char separator, char quotechar,
            char escapechar) {
        this(writer, separator, quotechar, escapechar, DEFAULT_LINE_END);
    }

    /**
     * Constructs CSVWriter with supplied separator and quote char.
     *
     * @param writer
     *            the writer to an underlying CSV source.
     * @param separator
     *            the delimiter to use for separating entries
     * @param quotechar
     *            the character to use for quoted elements
     * @param lineEnd
     *            the line feed terminator to use
     */
    public CSVWriter(Writer writer, char separator, char quotechar,
            String lineEnd) {
        this(writer, separator, quotechar, DEFAULT_ESCAPE_CHARACTER, lineEnd);
    }

    /**
     * Constructs CSVWriter with supplied separator, quote char, escape char and
     * line ending.
     *
     * @param writer
     *            the writer to an underlying CSV source.
     * @param separator
     *            the delimiter to use for separating entries
     * @param quotechar
     *            the character to use for quoted elements
     * @param escapechar
     *            the character to use for escaping quotechars or escapechars
     * @param lineEnd
     *            the line feed terminator to use
     */
    public CSVWriter(Writer writer, char separator, char quotechar,
            char escapechar, String lineEnd) {
        this.rawWriter = writer;
        this.pw = new PrintWriter(writer);
        this.separator = separator;
        this.quotechar = quotechar;
        this.escapechar = escapechar;
        this.lineEnd = lineEnd;
    }

    /**
     * Writes the entire list to a CSV file. The list is assumed to be a
     * String[]
     *
     * @param allLines
     *            a List of String[], with each String[] representing a line of
     *            the file.
     */
    @SuppressWarnings("unchecked")
    public void writeAll(List allLines) {

        for (Iterator iter = allLines.iterator(); iter.hasNext();) {
            String[] nextLine = (String[]) iter.next();
            writeNext(nextLine);
        }

    }

    protected void writeColumnNames(ResultSetMetaData metadata)
            throws SQLException {

        int columnCount = metadata.getColumnCount();

        String[] nextLine = new String[columnCount];
        for (int i = 0; i < columnCount; i++) {
            nextLine[i] = metadata.getColumnName(i + 1);
        }
        writeNext(nextLine);
    }

    /**
     * Writes the entire ResultSet to a CSV file.
     *
     * The caller is responsible for closing the ResultSet.
     *
     * @param rs
     *            the recordset to write
     * @param includeColumnNames
     *            true if you want column names in the output, false otherwise
     *
     */
    public void writeAll(java.sql.ResultSet rs, boolean includeColumnNames)
            throws SQLException, IOException {

        ResultSetMetaData metadata = rs.getMetaData();

        if (includeColumnNames) {
            writeColumnNames(metadata);
        }

        int columnCount = metadata.getColumnCount();

        while (rs.next()) {
            String[] nextLine = new String[columnCount];

            for (int i = 0; i < columnCount; i++) {
                nextLine[i] = getColumnValue(rs, metadata.getColumnType(i + 1),
                        i + 1);
            }

            writeNext(nextLine);
        }
    }

    private static String getColumnValue(ResultSet rs, int colType, int colIndex)
            throws SQLException, IOException {

        String value = "";

        switch (colType) {
        case Types.BIT:
            Object bit = rs.getObject(colIndex);
            if (bit != null) {
                value = String.valueOf(bit);
            }
            break;
        case Types.BOOLEAN:
            boolean b = rs.getBoolean(colIndex);
            if (!rs.wasNull()) {
                value = Boolean.valueOf(b).toString();
            }
            break;
        case Types.CLOB:
            Clob c = rs.getClob(colIndex);
            if (c != null) {
                value = read(c);
            }
            break;
        case Types.BIGINT:
        case Types.DECIMAL:
        case Types.DOUBLE:
        case Types.FLOAT:
        case Types.REAL:
        case Types.NUMERIC:
            BigDecimal bd = rs.getBigDecimal(colIndex);
            if (bd != null) {
                value = "" + bd.doubleValue();
            }
            break;
        case Types.INTEGER:
        case Types.TINYINT:
        case Types.SMALLINT:
            int intValue = rs.getInt(colIndex);
            if (!rs.wasNull()) {
                value = "" + intValue;
            }
            break;
        case Types.JAVA_OBJECT:
            Object obj = rs.getObject(colIndex);
            if (obj != null) {
                value = String.valueOf(obj);
            }
            break;
        case Types.DATE:
            java.sql.Date date = rs.getDate(colIndex);
            if (date != null) {
                value = DATE_FORMATTER.format(date);
                ;
            }
            break;
        case Types.TIME:
            Time t = rs.getTime(colIndex);
            if (t != null) {
                value = t.toString();
            }
            break;
        case Types.TIMESTAMP:
            Timestamp tstamp = rs.getTimestamp(colIndex);
            if (tstamp != null) {
                value = TIMESTAMP_FORMATTER.format(tstamp);
            }
            break;
        case Types.LONGVARCHAR:
        case Types.VARCHAR:
        case Types.CHAR:
            value = rs.getString(colIndex);
            break;
        default:
            value = "";
        }

        if (value == null) {
            value = "";
        }

        return value;

    }

    private static String read(Clob c) throws SQLException, IOException {
        StringBuffer sb = new StringBuffer((int) c.length());
        Reader r = c.getCharacterStream();
        char[] cbuf = new char[2048];
        int n = 0;
        while ((n = r.read(cbuf, 0, cbuf.length)) != -1) {
            if (n > 0) {
                sb.append(cbuf, 0, n);
            }
        }
        return sb.toString();
    }

    /**
     * Writes the next line to the file.
     *
     * @param nextLine
     *            a string array with each comma-separated element as a separate
     *            entry.
     */
    public void writeNext(String[] nextLine) {

        if (nextLine == null)
            return;

        StringBuffer sb = new StringBuffer();
        for (int i = 0; i < nextLine.length; i++) {

            if (i != 0) {
                sb.append(separator);
            }

            String nextElement = nextLine[i];
            if (nextElement == null)
                continue;
            if (quotechar != NO_QUOTE_CHARACTER)
                sb.append(quotechar);
            for (int j = 0; j < nextElement.length(); j++) {
                char nextChar = nextElement.charAt(j);
                if (escapechar != NO_ESCAPE_CHARACTER && nextChar == quotechar) {
                    sb.append(escapechar).append(nextChar);
                } else if (escapechar != NO_ESCAPE_CHARACTER
                        && nextChar == escapechar) {
                    sb.append(escapechar).append(nextChar);
                } else {
                    sb.append(nextChar);
                }
            }
            if (quotechar != NO_QUOTE_CHARACTER)
                sb.append(quotechar);
        }

        sb.append(lineEnd);
        pw.write(sb.toString());

    }

    /**
     * Flush underlying stream to writer.
     *
     * @throws IOException
     *             if bad things happen
     */
    public void flush() throws IOException {

        pw.flush();

    }

    /**
     * Close the underlying stream writer flushing any buffered content.
     *
     * @throws IOException
     *             if bad things happen
     *
     */
    public void close() throws IOException {
        pw.flush();
        pw.close();
        rawWriter.close();
    }

}

    生成文件测试类

  

public class CSVWriterTest {

    /**
     * Test routine for converting output to a string.
     *
     * @param args
     *            the elements of a line of the cvs file
     * @return a String version
     * @throws IOException
     *             if there are problems writing
     */
    private String invokeWriter(String[] args) throws IOException {
        StringWriter sw = new StringWriter();
        CSVWriter csvw = new CSVWriter(sw, ',', '\'');
        csvw.writeNext(args);
        return sw.toString();
    }

    /**
     * Tests parsing individual lines.
     *
     * @throws IOException
     *             if the reader fails.
     */

    public void testParseLine() throws IOException {

        // test normal case
        String[] normal = { "a", "b", "c" };
        String output = invokeWriter(normal);
        assertEquals("'a','b','c'\n", output);

        // test quoted commas
        String[] quoted = { "a", "b,b,b", "c" };
        output = invokeWriter(quoted);
        assertEquals("'a','b,b,b','c'\n", output);

        // test empty elements
        String[] empty = {,};
        output = invokeWriter(empty);
        assertEquals("\n", output);

        // test multiline quoted
        String[] multiline = { "This is a \n multiline entry", "so is \n this" };
        output = invokeWriter(multiline);
        assertEquals("'This is a \n multiline entry','so is \n this'\n", output);

    }

    /**
     * Test parsing from to a list.
     *
     * @throws IOException
     *             if the reader fails.
     */
    @SuppressWarnings("unchecked")

    public void testParseAll() throws IOException {

        List allElements = new ArrayList();
        String[] line1 = "Name#Phone#Email".split("#");
        String[] line2 = "Glen#1234#[e-mail]".split("#");
        String[] line3 = "John#5678#[e-mail]".split("#");
        allElements.add(line1);
        allElements.add(line2);
        allElements.add(line3);

        StringWriter sw = new StringWriter();
        CSVWriter csvw = new CSVWriter(new FileWriter("d:/test.csv"), ',', '\'');
        csvw.writeAll(allElements);

        //String result = sw.toString();
        //String[] lines = result.split("\n");

        //assertEquals(3, lines.length);

    }

    /**
     * Tests the option of having omitting quotes in the output stream.
     *
     * @throws IOException
     *             if bad things happen
     */
    public void testNoQuoteChars() throws IOException {

        String[] line = { "Foo", "Bar", "Baz" };
        StringWriter sw = new StringWriter();
        CSVWriter csvw = new CSVWriter(sw, CSVWriter.DEFAULT_SEPARATOR,
                CSVWriter.NO_QUOTE_CHARACTER);
        csvw.writeNext(line);
        String result = sw.toString();

        assertEquals("Foo,Bar,Baz\n", result);
    }

    /**
     * Test null values.
     *
     * @throws IOException
     *             if bad things happen
     */

    public void testNullValues() throws IOException {

        String[] line = { "Foo", null, "Bar", "baz" };
        StringWriter sw = new StringWriter();
        CSVWriter csvw = new CSVWriter(sw);
        csvw.writeNext(line);
        String result = sw.toString();

        assertEquals("\"Foo\",,\"Bar\",\"baz\"\n", result);

    }
    //@Test
    //生成CSV文件
    public void testStreamFlushing() throws IOException {

        String WRITE_FILE = "d:/myfile.csv";

        String[] nextLine = new String[] { "aaaa", "bbbb", "cccc", "dddd" };

        FileWriter fileWriter = new FileWriter(WRITE_FILE);
        CSVWriter writer = new CSVWriter(fileWriter);

        writer.writeNext(nextLine);

        // If this line is not executed, it is not written in the file.
        writer.close();

    }

    public void testAlternateEscapeChar() {
        String[] line = { "Foo", "bar's" };
        StringWriter sw = new StringWriter();
        CSVWriter csvw = new CSVWriter(sw, CSVWriter.DEFAULT_SEPARATOR,
                CSVWriter.DEFAULT_QUOTE_CHARACTER, '\'');
        csvw.writeNext(line);
        assertEquals("\"Foo\",\"bar''s\"\n", sw.toString());
    }

    public void testNoQuotingNoEscaping() {
        String[] line = { "\"Foo\",\"Bar\"" };
        StringWriter sw = new StringWriter();
        CSVWriter csvw = new CSVWriter(sw, CSVWriter.DEFAULT_SEPARATOR,
                CSVWriter.NO_QUOTE_CHARACTER, CSVWriter.NO_ESCAPE_CHARACTER);
        csvw.writeNext(line);
        assertEquals("\"Foo\",\"Bar\"\n", sw.toString());
    }
    @Test
    //测试嵌套的引号
    public void testNestedQuotes() {
        String[] data = new String[] { "\"\"", "test" };
        String oracle = new String("\"\"\"\"\"\",\"test\"\n");

        CSVWriter writer = null;
        File tempFile = null;
        FileWriter fwriter = null;

        try {
            tempFile = File.createTempFile("csvWriterTest", ".csv");
            tempFile.deleteOnExit();
            fwriter = new FileWriter(tempFile);
            writer = new CSVWriter(fwriter);
        } catch (IOException e) {
            fail();
        }

        // write the test data:
        writer.writeNext(data);

        try {
            writer.close();
        } catch (IOException e) {
            fail();
        }

        try {
            // assert that the writer was also closed.
            fwriter.flush();
            fail();
        } catch (IOException e) {
            // we should go through here..
        }

        // read the data and compare.
        FileReader in = null;
        try {
            in = new FileReader(tempFile);
        } catch (FileNotFoundException e) {
            fail();
        }

        StringBuffer fileContents = new StringBuffer();
        try {
            int ch;
            while ((ch = in.read()) != -1) {
                fileContents.append((char) ch);
            }
            in.close();
        } catch (IOException e) {
            fail();
        }

        assertTrue(oracle.equals(fileContents.toString()));
    }
    //@Test
    public void testAlternateLineFeeds() {
        String[] line = { "Foo", "Bar", "baz" };
        StringWriter sw = new StringWriter();
        CSVWriter csvw = new CSVWriter(sw, CSVWriter.DEFAULT_SEPARATOR,
                CSVWriter.DEFAULT_QUOTE_CHARACTER, "\r");
        csvw.writeNext(line);
        String result = sw.toString();

        assertTrue(result.endsWith("\r"));

    }

    /**
     * The Test Runner for commandline use.
     *
     * @param args
     *            no args required
     */
    public static void main(String args[]) {
        junit.textui.TestRunner.run(CSVWriterTest.class);
    }

}

其二：EXCEL文件的读取与生成（要用到jxl.jar, 上网可以搜到，我下面附上）

 

public class ExcelHandle {
    public ExcelHandle() {
    }

    /**
     * 读取Excel
     *
     * @param filePath
     */
    public static void readExcel(String filePath) {
        try {
            InputStream is = new FileInputStream(filePath);
            Workbook rwb = Workbook.getWorkbook(is);
            // Sheet st = rwb.getSheet("0")这里有两种方法获取sheet表,1为名字，而为下标，从0开始
            Sheet st = rwb.getSheet("original");
            Cell c00 = st.getCell(0, 0);
            // 通用的获取cell值的方式,返回字符串
            String strc00 = c00.getContents();
            // 获得cell具体类型值的方式
            if (c00.getType() == CellType.LABEL) {
                LabelCell labelc00 = (LabelCell) c00;
                strc00 = labelc00.getString();
            }
            // 输出
            System.out.println(strc00);
            // 关闭
            rwb.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 输出Excel
     *
     * @param os
     */
    public static void writeExcel(OutputStream os) {
        try {
            /**
             * 只能通过API提供的工厂方法来创建Workbook，而不能使用WritableWorkbook的构造函数，
             * 因为类WritableWorkbook的构造函数为protected类型
             * method(1)直接从目标文件中读取WritableWorkbook wwb =
             * Workbook.createWorkbook(new File(targetfile)); method(2)如下实例所示
             * 将WritableWorkbook直接写入到输出流
             */
            WritableWorkbook wwb = Workbook.createWorkbook(os);
            // 创建Excel工作表 指定名称和位置
            WritableSheet ws = wwb.createSheet("Test Sheet 1", 0);

            // **************往工作表中添加数据*****************

            // 1.添加Label对象
            Label label = new Label(0, 0, "this is a label test");
            ws.addCell(label);

            // 添加带有字型Formatting对象
            WritableFont wf = new WritableFont(WritableFont.TIMES, 18,
                    WritableFont.BOLD, true);
            WritableCellFormat wcf = new WritableCellFormat(wf);
            Label labelcf = new Label(1, 0, "this is a label test", wcf);
            ws.addCell(labelcf);

            // 添加带有字体颜色的Formatting对象
            WritableFont wfc = new WritableFont(WritableFont.ARIAL, 10,
                    WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE,
                    jxl.format.Colour.RED);
            WritableCellFormat wcfFC = new WritableCellFormat(wfc);
            Label labelCF = new Label(1, 0, "This is a Label Cell", wcfFC);
            ws.addCell(labelCF);

            // 2.添加Number对象
            Number labelN = new Number(0, 1, 3.1415926);
            ws.addCell(labelN);

            // 添加带有formatting的Number对象
            NumberFormat nf = new NumberFormat("#.##");
            WritableCellFormat wcfN = new WritableCellFormat(nf);
            Number labelNF = new jxl.write.Number(1, 1, 3.1415926, wcfN);
            ws.addCell(labelNF);

            // 3.添加Boolean对象
            Boolean labelB = new jxl.write.Boolean(0, 2, false);
            ws.addCell(labelB);

            // 4.添加DateTime对象
            jxl.write.DateTime labelDT = new jxl.write.DateTime(0, 3,
                    new java.util.Date());
            ws.addCell(labelDT);

            // 添加带有formatting的DateFormat对象
            DateFormat df = new DateFormat("dd MM yyyy hh:mm:ss");
            WritableCellFormat wcfDF = new WritableCellFormat(df);
            DateTime labelDTF = new DateTime(1, 3, new java.util.Date(), wcfDF);
            ws.addCell(labelDTF);

            // 添加图片对象,jxl只支持png格式图片
            File image = new File("E:\\2.png");
            WritableImage wimage = new WritableImage(0, 1, 2, 2, image);
            ws.addImage(wimage);
            // 写入工作表
            wwb.write();
            wwb.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 拷贝后,进行修改,其中file1为被copy对象，file2为修改后创建的对象
     * 尽单元格原有的格式化修饰是不能去掉的，我们还是可以将新的单元格修饰加上去， 以使单元格的内容以不同的形式表现
     *
     * @param file1
     * @param file2
     */
    public static void modifyExcel(File file1, File file2) {
        try {
            Workbook rwb = Workbook.getWorkbook(file1);
            WritableWorkbook wwb = Workbook.createWorkbook(file2, rwb);// copy
            WritableSheet ws = wwb.getSheet(0);
            WritableCell wc = ws.getWritableCell(0, 0);
            // 判断单元格的类型,做出相应的转换
            if (wc.getType() == CellType.LABEL) {
                Label label = (Label) wc;
                label.setString("The value has been modified");
            }
            wwb.write();
            wwb.close();
            rwb.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    // 测试
    public static void main(String[] args) {
        try {
            // 读Excel
            //ExcelHandle.readExcel("E:/testRead.xls");
            // 输出Excel
            File fileWrite = new File("E:/testWrite.xls");
            fileWrite.createNewFile();
            OutputStream os = new FileOutputStream(fileWrite);
            ExcelHandle.writeExcel(os);
            // 修改Excel
            //ExcelHandle.modifyExcel(new File(""), new File(""));
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

 

   其三：读取CSV文件内容写入到Excel中(此代码含有同步数据库的操作)

public static void readCsv(String fileDir, Set targetSet) {
        System.out.println("执行解析文件............");
        try {
            CSVReader csvr = new CSVReader(new FileReader(fileDir), ',', '\"',
                    1);
            List allElements = csvr.readAll();
            int size = allElements == null ? 0 : allElements.size();
            System.out.println("总共有几行：" + size);
            csvr = new CSVReader(new FileReader(fileDir), ',', '\"', 1);
            String[] nextline = null;

            File tempFile = new File("d:/output2.xls");//
            WritableWorkbook workbook;
            workbook = Workbook.createWorkbook(tempFile);
            WritableSheet sheet = workbook.createSheet("kk", 1);
            Label l = null;
            WritableFont detFont = new WritableFont(WritableFont.ARIAL, 10,
                    WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE,
                    jxl.format.Colour.BLACK);
            WritableCellFormat detFormat = new WritableCellFormat(detFont);

            int column = 0;
            l = new Label(column++, 0, "KPI_CODE", detFormat);
            sheet.addCell(l);

            l = new Label(column++, 0, "KPI_DESC", detFormat);
            sheet.addCell(l);

            l = new Label(column++, 0, "KPI_VALUE", detFormat);
            sheet.addCell(l);

            l = new Label(column++, 0, "KPI_MAX", detFormat);
            sheet.addCell(l);

            l = new Label(column++, 0, "KPI_MIN", detFormat);
            sheet.addCell(l);

            l = new Label(column++, 0, "MONTH_ID", detFormat);
            sheet.addCell(l);

            for (int i = 0; i < size; i++) {
                TargetRecordPojo tp = new TargetRecordPojo();
                nextline = csvr.readNext();
                int len = nextline.length;

                for (int j = 0; j < len; j++) {

                    l = new Label(j, i + 1, nextline[j], detFormat);
                    sheet.addCell(l);

                    if (j == 0) {
                        tp.setTarget_id(nextline[0]);

                        continue;
                    } else if (j == 1) {
                        tp.setRemark(nextline[1]);

                        continue;

                    } else if (j == 2) {
                        tp.setTarget_value(nextline[2]);

                        continue;

                    } else if (j == 3) {
                        tp.setTarget_data_max(nextline[3]);

                        continue;

                    } else if (j == 4) {
                        tp.setTarget_data_min(nextline[4]);

                        continue;

                    } else if (j == 5) {

                        tp.setTarget_date(nextline[5]);

                        // tp.setTarget_data_date(DateUtils.dateFormat(date));
                        continue;
                    }

                }

                targetSet.add(tp);

            }
            column = 0;
            sheet.setColumnView(column++, 20);
            sheet.setColumnView(column++, 20);
            sheet.setColumnView(column++, 20);
            sheet.setColumnView(column++, 20);
            sheet.setColumnView(column++, 20);
            sheet.setColumnView(column++, 20);
            workbook.write();
            workbook.close();
            FileInputStream fis = new FileInputStream(tempFile);
            FileOutputStream fos = new FileOutputStream(new File(
                    "d:/backupfile/backup_" + System.currentTimeMillis()
                            + ".xls"));
            byte[] buff = new byte[1024];
            int len = -1;
            while ((len = fis.read(buff)) != -1) {
                fos.write(buff, 0, len);

            }
            fis.close();
            fos.flush();
            fos.close();
            tempFile.delete();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (RowsExceededException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (WriteException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

    }