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 newlinesb.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,gif (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);}/** * 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. */@Beforepublic void init() throws Exception {StringBuffer sb = new StringBuffer();sb.append("a,b,c").append("\n"); // standard casesb.append("a,\"b,b,b\",c").append("\n"); // quoted elementssb.append(",,").append("\n"); // empty elementssb.append("a,\"PO Box 123,\nKippax,ACT. 2615.\nAustralia\",d.\n");//Glen \"The Man\" Smithsb.append("\"Glen \"\"The Man\"\" Smith\",Athlete,Developer\n"); // Test// quoted// quote// charssb.append("\"\"\"\"\"\",\"test\"\n"); // """""","test" representing: "",// testsb.append("\"a\nb\",b,\"\nd\",e\n");csvr = new CSVReader(new FileReader("d:/myfile.csv"));//这种方式就是读取文件了//csvr = new CSVReader(new StringReader(sb.toString()));//这种方式就是读取字符串了}//测试读取文件@Testpublic void test1() throws IOException{CSVReader c = new CSVReader(new FileReader("d:/myfile.csv"), ',','\"', 1);String[] nextline=c.readNext();System.out.println(nextline);assertEquals("CRM4005", nextline);}/** * 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); //第一行第一个元素assertEquals("b", nextLine); //第一行第二个元素assertEquals("c", nextLine); //第一行第三个元素// test quoted commas 测试引用起来的逗号 nextLine = csvr.readNext();assertEquals("a", nextLine);assertEquals("b,b,b", nextLine);assertEquals("c", nextLine);// 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);nextLine = csvr.readNext();assertTrue(nextLine.equals("\"\"")); // check the tricky situation //检查复杂的位置assertTrue(nextLine.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 casesb.append("a\t'b\tb\tb'\tc").append("\n"); // single quoted elementsCSVReader 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 thissb.append("And this line too").append("\n"); // and thissb.append("a\t'b\tb\tb'\tc").append("\n"); // single quoted elementsCSVReader c = new CSVReader(new StringReader(sb.toString()), '\t','\'', 2);//跳过两行来读取文本，那么读取的当然是第三行了String[] nextLine = c.readNext();assertEquals(3, nextLine.length);assertEquals("a", nextLine);}/** * 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",cCSVReader c = new CSVReader(new StringReader(sb.toString()));String[] nextLine = c.readNext();assertEquals(3, nextLine.length);System.out.println(nextLine);assertEquals("123\"4\"567", nextLine);}/** * 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;for (int i = 0; i < columnCount; i++) {nextLine = 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;for (int i = 0; i < columnCount; i++) {nextLine = 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;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;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 caseString[] normal = { "a", "b", "c" };String output = invokeWriter(normal);assertEquals("'a','b','c'\n", output);// test quoted commasString[] quoted = { "a", "b,b,b", "c" };output = invokeWriter(quoted);assertEquals("'a','b,b,b','c'\n", output);// test empty elementsString[] empty = {,};output = invokeWriter(empty);assertEquals("\n", output);// test multiline quotedString[] 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()));}//@Testpublic 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);// copyWritableSheet 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");// 输出ExcelFile 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, detFormat);sheet.addCell(l);if (j == 0) {tp.setTarget_id(nextline);continue;} else if (j == 1) {tp.setRemark(nextline);continue;} else if (j == 2) {tp.setTarget_value(nextline);continue;} else if (j == 3) {tp.setTarget_data_max(nextline);continue;} else if (j == 4) {tp.setTarget_data_min(nextline);continue;} else if (j == 5) {tp.setTarget_date(nextline);// 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;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 blocke.printStackTrace();} catch (RowsExceededException e) {// TODO Auto-generated catch blocke.printStackTrace();} catch (WriteException e) {// TODO Auto-generated catch blocke.printStackTrace();}}
页:
[1]