JXL方式读取Excel
?
?
/* * Classname: * Version information: * Creator: chenjian * Create Date: 2007-3-21下午06:00:23 * Copyright notice: */package nc.ui.fch.bg.t80303;import java.io.File;import java.io.IOException;import java.util.ArrayList;import jxl.Cell;import jxl.CellType;import jxl.Workbook;import jxl.format.CellFormat;import jxl.read.biff.BiffException;import jxl.write.WritableCell;import jxl.write.WritableCellFormat;import jxl.write.WritableSheet;import jxl.write.WritableWorkbook;import jxl.write.WriteException;import jxl.write.biff.RowsExceededException;/** * 功能说明: * * @author chenjian 2007-3-21 下午06:00:22 */public class WriteDataToExcel { /** * */ public WriteDataToExcel() { // TODO Auto-generated constructor stub } /** * 功能: 向Excel里写数据 * * @param sourceFile * 从服务器下载下来的源文件 * @param newFile * copy源文件得到的新文件,用于写入数据 * @throws Exception * @author chenjian 2007-3-22 上午11:20:12 */ public static void writeData(String sourceFile, String newFile, ArrayList arr) { Workbook wb = null; WritableWorkbook wwb = null; WritableSheet wsB = null; try { // 创建只读的Excel工作薄的对象 wb = Workbook.getWorkbook(new File(sourceFile)); // copy上面的Excel工作薄,创建新的可写入的Excel工作薄对象 wwb = Workbook.createWorkbook(new File(newFile), wb); // 读取工作表--(注:工作表索引从0开始) wsB = wwb.getSheet(0); // 循环插入数据 for (int i = 0; i < arr.size(); i++) { ArrayList rowArr = (ArrayList) arr.get(i); for (int j = 0; j < rowArr.size(); j++) { WritableCell wc = null; // 以第一行所有的列为模板， wc = wsB.getWritableCell(j, 1); WritableCellFormat wcFormat = null; if (wc.getCellFormat() != null) { // 获得源单元格格式 wcFormat = new WritableCellFormat(wc.getCellFormat()); } else { wcFormat = new WritableCellFormat(); } wc = cloneCellWithValue(j, i + 1, rowArr.get(j),wcFormat ); wsB.addCell(wc); } } // 写入Excel对象 wwb.write(); } catch (BiffException e) { // TODO Auto-generated catch block 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(); } finally { // 关闭可写入的Excel对象 try { if (null != wwb) wwb.close(); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } // 关闭只读的Excel对象 if (null != wb) wb.close(); } } /** *TODO功能 复制模板单元格的类型，并判断插入数据的类型，进行写入数据 Administrator maoyulong 2007-4-9 * 下午03:52:04 * * @param col * @param row * @param value * @param wcFormat * @return */ public static WritableCell cloneCellWithValue(int col, int row, Object value, WritableCellFormat wcFormat) { WritableCell wc = null; // 判断数据是否为STRING类型，是用LABLE形式插入，否则用NUMBER形式插入 if(value == null){ wc = new jxl.write.Blank(col, row, wcFormat); } else if (value instanceof String) { jxl.write.Label label = new jxl.write.Label(col, row, value .toString(), wcFormat); wc = label; } else { wc = new jxl.write.Number(col, row, new Double(value.toString()) .doubleValue(), wcFormat); } return wc; } /** * 功能: * * @param args * @author chenjian 2007-3-21 下午06:00:23 */ public static void main(String[] args) { // TODO Auto-generated method stub try { ArrayList q = new ArrayList(); ArrayList w = new ArrayList(); w.add("==="); w.add(new Double(10.00)); q.add(w); new WriteDataToExcel().writeData("C:\\bgttemplet\\wbs.xls", "C:\\bgtreport\\wbs.xls", q); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } }}??
一 JXL读日期 会把 “2009-09-09” 读为 “09-09-09”使用以下方法解决 private String dateFromExcel(Cell cell) { String data = cell.getContents(); if (data.toString().trim().equals("")) { return null; } SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); if (cell.getType() == CellType.DATE) { DateCell dc = (DateCell) cell; data = sdf.format(dc.getDate()); } else { data = data.toString().trim(); } return data; }??
?
?
package com.jxl.study;import java.io.File;import java.io.FileOutputStream;import java.io.IOException;import java.io.OutputStream;import jxl.Cell;import jxl.Sheet;import jxl.Workbook;import jxl.read.biff.BiffException;import jxl.write.Label;import jxl.write.WritableSheet;import jxl.write.WritableWorkbook;import jxl.write.WriteException;import jxl.write.biff.RowsExceededException;public class JXLUtil { private static Workbook workbook; private static WritableWorkbook writableWorkbook; /** * 功能： * <p> * 打印出Excel表里的全部内容 * </p> * * @param Excel * 所在的绝对路径 */ public static void readXLS(String filePatch) { if (!filePatch.endsWith(".xls")) { System.out.println("=======不是正确的xls格式，请核查=========="); return; }// end if File file = new File(filePatch); // 文件只读 file.setReadOnly(); try { // 得到Excel文件 workbook = Workbook.getWorkbook(file); // Excel中的工作表 下表从0开始 Sheet sheet = workbook.getSheet(0); int row = sheet.getRows(); // 工作表共有的行 for (int i = 0; i < row; i++) { Cell[] cells = sheet.getRow(i); // 拿一行的内容 for (int j = 0; j < cells.length; j++) { System.out.print(cells[j].getContents() + " "); } System.out.println(); } } catch (BiffException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } finally { if (workbook != null) { workbook.close(); } } // 还原文件状态 file.canWrite(); }// end function /** * 功能： * <p> * 项Excel文件里写入内容 * </p> * * @param Excel文件的绝对路径 */ public static void writeJxl(String filePatch) { if (!filePatch.endsWith(".xls")) { System.out.println("=======不是正确的xls格式，请核查=========="); return; }// end if try { OutputStream os = new FileOutputStream(filePatch); // 创建可写簿 writableWorkbook = Workbook.createWorkbook(os); // 创建工作表 WritableSheet ws = writableWorkbook.createSheet("sheet", 0); // 创建一个内容 第一个整数为 列，第二个整数位 行 Label label; for (int i = 0; i < 10; i++) { for (int j = 0; j < 10; j++) { if (i == 0 && j == 0) { label = new Label(i, j, ""); } else if (j == 0) { label = new Label(i, j, "第" + (i + 1) + "列"); } else if (i == 0) { label = new Label(i, j, "第" + (j + 1) + "行"); } else { label = new Label(i, j, "内容：" + i + "," + j); } ws.addCell(label); } } writableWorkbook.write(); writableWorkbook.close(); os.close(); } 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(); } finally { } } public static void main(String[] args) { writeJxl("c:\\2.xls"); }}