`
JavaCrazyer
  • 浏览: 3011762 次
  • 性别: Icon_minigender_1
  • 来自: 河南
社区版块
存档分类

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

	}

 

  • jxl.jar (708.7 KB)
  • 下载次数: 43
2
0
分享到:
评论

相关推荐

    C#读取excel、csv文件到折线图

    以上就是使用C#读取Excel和CSV文件,将数据加载到Chart控件并绘制折线图的基本过程。根据实际需求,你可能需要对数据进行预处理,例如过滤、排序或计算。此外,还可以通过调整Chart的样式和设置来优化视觉效果,如...

    java读写csv文件,中文乱码问题

    下面我们将深入探讨如何在Java中正确地读取和写入CSV文件,以及解决中文乱码的问题。 1. **字符编码的理解**: - 在处理中文字符时,必须确保使用正确的字符编码,例如UTF-8。UTF-8是一种广泛支持的编码格式,可以...

    使用pandas模块读取csv文件和excel表格,并用matplotlib画图的方法

    在处理数据分析和可视化的场景中,pandas库和matplotlib库是Python编程语言中最为常用的两个库,它们各自有着丰富的...希望本文对pandas模块读取csv文件和excel表格以及使用matplotlib画图的介绍能够对大家有所帮助。

    NPOI读写Excel和第三方dll读写CSV文件

    在IT行业中,处理数据时,Excel和CSV文件是非常常见的格式,尤其在数据分析、报表生成以及数据交换等场景中。本话题主要围绕“NPOI读写Excel”和“第三方DLL读写CSV文件”这两个核心知识点展开,同时提供了一系列...

    VBA批量处理csv或其他excel文件数据

    在Excel中,VBA可以编写宏,这些宏是预定义的指令序列,可以执行一系列操作,如打开文件、编辑数据或生成报告。在“VBA批量处理csv或其他excel文件数据”的场景下,我们可以通过编写VBA宏实现以下功能: 1. **遍历...

    C#读取CSV EXCEL文件

    C#读取CSV EXCEL文件的知识点解析 C#读取CSV EXCEL文件是指使用C#语言读取CSV(Comma Separated Values,逗号分隔值)文件和EXCEL文件的方法。CSV文件是一种通用的文本文件格式,用于存储表格数据,而EXCEL文件是一...

    STM32F103ZE 生成并写入CSV文件 FATFS实验.rar

    在本实验中,我们将探讨如何在STM32F103ZE上实现FATFS文件系统,以及如何生成并写入CSV文件。 FATFS是DOS/Windows兼容的FAT文件系统的轻量级实现,适用于资源有限的嵌入式系统。它提供了一个通用的文件操作接口,...

    EXCEL用VBA程序用ADO读取CSV

    在Excel VBA编程中,有时我们需要处理大量的数据,这时候可以借助外部数据访问对象(ADO)来读取和操作数据,比如CSV(逗号分隔值)文件。CSV格式因其通用性和简洁性,常用于数据交换。下面我们将详细介绍如何使用...

    python读取csv文件指定行的2种方法详解

    假设上述csv文件保存为”A.csv”,如何用Python像操作Excel一样提取其中的一行,也就是一条记录,利用Python自带的csv模块,有2种方法可以实现: 方法一:reader 第一种方法使用reader函数,接收一个可迭代的对象...

    CSV文件的生成与分析(转)

    CSV文件,全称为“逗号分隔值”(Comma ...总的来说,CSV文件的生成与分析是数据处理中的基础技能,无论是在日常办公还是在开发环境中都有着广泛的应用。了解并掌握这些技能,能够帮助我们更有效地管理和利用数据。

    C#读取csv文件并导入Excel中

    在IT行业中,尤其是在数据分析、报表生成以及数据迁移等任务中,C#编程语言与CSV(逗号分隔值)文件和Excel的交互是常见的应用场景。本篇将详细讲解如何使用C#来读取CSV文件并将数据导入到Excel中。 首先,我们需要...

    Codesys读取和写入CSV数据源程序

    codesys通过文件写入...d、通过SysFileClose关闭CSV文件,若文件不关闭,则下次无法读取和写入。 本文所采用的codesys版本为V3.5.14.10,参考博客地址:https://blog.csdn.net/qq_19979629/article/details/124355867

    Qt解析CSV文件

    总结来说,这个主题涉及到的关键技术包括Qt的文本I/O操作,自定义的CSV解析策略,以及使用第三方库SimpleXlsxWriter生成Excel文件。这些技能对于构建能处理复杂数据任务的桌面应用程序至关重要。在实际项目中,...

    thinkphp下 导入导出csv文件

    6. 测试环境准备:描述中提到“测试需要手动修改TP数据库配置”,意味着在测试环境中,可能需要创建一个新的数据库或修改现有数据库的连接参数,以便与CSV文件数据的导入导出操作匹配。 7. 压缩包文件内容: - `....

    OLEDB读取Excel、csv出现字符串截断、丢失原因及其解决方案

    某汽车整车生产企业需要将其车辆发车信息发布到汽车产业链平台上去,其数据为内部ERP系统生成的Excel数据表,用户首先将该数据表上传至汽车产业链平台,平台将此Excel数据读取导入到平台内部的SQL Sever数据库中,以...

    jmeter用beanshell后置处理程序生成全局变量 csv文件

    文档文件

    labview创建并保存CSV文件

    创建的CSV文件可被Excel、Python、R等工具读取,方便进行进一步的数据分析和处理。 8. **学习资源**: - 官方文档:LabVIEW的帮助文档提供了详细的编程指南和例子。 - 在线社区:如NI的LAVA论坛,你可以找到许多...

    Qt qtablewidget与CSV文件的关系

    本文将深入探讨Qt中的QTableWidget与CSV文件之间的关系,以及如何在两者之间进行数据交互。 首先,理解QTableWidget的基本操作至关重要。QTableWidget可以创建带有行和列的表格,每个单元格都可以存储不同类型的...

    XlS批量生成CSV 多个CSV合并为1个CSV

    在标题“XlS批量生成CSV 多个CSV合并为1个CSV”中,涉及到的主要知识点是Excel文件(XLS)的批处理转换以及多个CSV文件的合并。这些操作在数据分析、报表整理、数据库导入等场景中非常实用。 首先,我们需要理解XLS...

    VB操作EXCEL的csv文件

    6. **错误处理**:在处理CSV文件时,需要考虑可能的异常,如文件不存在、读写权限问题等。使用`Try...Catch`结构可以捕获并处理这些异常。 7. **性能优化**:如果文件非常大,一次性加载到内存可能会消耗大量资源。...

Global site tag (gtag.js) - Google Analytics