其一: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#glen@abcd.com".split("#");
- String[] line3 = "John#5678#john@efgh.com".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();
- }
- }