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

	}