流浪天涯

import java.io.File;
import java.util.ArrayList;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.format.UnderlineStyle;
import jxl.write.DateFormat;
import jxl.write.DateTime;
import jxl.write.Label;
import jxl.write.NumberFormat;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
public class XlsManager
{
?
?/***************
? 導(dǎo)出數(shù)據(jù)到Excel文件中
? 下面的例子，設(shè)置了數(shù)字、日期的格式，還有字體，顏色等。
?**************/
?public static void writeXLS()throws Exception
?{
??
??File tempFile=new File("e:/work/output.xls");
??WritableWorkbook workbook = Workbook.createWorkbook(tempFile);
??WritableSheet sheet = workbook.createSheet("TestCreateExcel", 0);

//??一些臨時(shí)變量，用于寫到excel中
??Label l=null;
??jxl.write.Number n=null;
??jxl.write.DateTime d=null;

//??預(yù)定義的一些字體和格式，同一個(gè)Excel中最好不要有太多格式
??WritableFont headerFont = new WritableFont(WritableFont.ARIAL, 12, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLUE);
??WritableCellFormat headerFormat = new WritableCellFormat (headerFont);

??WritableFont titleFont = new WritableFont(WritableFont.ARIAL, 10, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.RED);
??WritableCellFormat titleFormat = new WritableCellFormat (titleFont);

??WritableFont detFont = new WritableFont(WritableFont.ARIAL, 10, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK);
??WritableCellFormat detFormat = new WritableCellFormat (detFont);

??NumberFormat nf=new NumberFormat("0.00000");? //用于Number的格式
??WritableCellFormat priceFormat = new WritableCellFormat (detFont, nf);

??DateFormat df=new DateFormat("yyyy-MM-dd");//用于日期的
??WritableCellFormat dateFormat = new WritableCellFormat (detFont, df);

//??剩下的事情，就是用上面的內(nèi)容和格式創(chuàng)建一些單元格，再加到sheet中
??l=new Label(0, 0, "用于測試的Excel文件", headerFormat);
??sheet.addCell(l);

//??add Title
??int column=0;
??l=new Label(column++, 2, "標(biāo)題", titleFormat);
??sheet.addCell(l);
??l=new Label(column++, 2, "日期", titleFormat);
??sheet.addCell(l);
??l=new Label(column++, 2, "貨幣", titleFormat);
??sheet.addCell(l);
??l=new Label(column++, 2, "價(jià)格", titleFormat);
??sheet.addCell(l);

//??add detail
??int i=0;
??column=0;
??l=new Label(column++, i+3, "標(biāo)題 "+i, detFormat);
??sheet.addCell(l);
??d=new DateTime(column++, i+3, new java.util.Date(), dateFormat);
??sheet.addCell(d);
??l=new Label(column++, i+3, "CNY", detFormat);
??sheet.addCell(l);
??n=new jxl.write.Number(column++, i+3, 5.678, priceFormat);
??sheet.addCell(n);

??i++;
??column=0;
??l=new Label(column++, i+3, "標(biāo)題 "+i, detFormat);
??sheet.addCell(l);
??d=new DateTime(column++, i+3, new java.util.Date(), dateFormat);
??sheet.addCell(d);
??l=new Label(column++, i+3, "SGD", detFormat);
??sheet.addCell(l);
??n=new jxl.write.Number(column++, i+3, 98832, priceFormat);
??sheet.addCell(n);

//??設(shè)置列的寬度
??column=0;
??sheet.setColumnView(column++, 20);
??sheet.setColumnView(column++, 20);
??sheet.setColumnView(column++, 10);
??sheet.setColumnView(column++, 20);

??workbook.write();
??workbook.close();
?}
?public static ArrayList readXLS() throws Exception
?{
??Workbook workbook = null;
??ArrayList list = new ArrayList();
??
??try {
???? workbook = Workbook.getWorkbook(new File("d:\\test.xls"));
??} catch (Exception e) {
???? throw new Exception("file to import not found!");
??}

??Sheet sheet = workbook.getSheet(0);
??Cell cell = null;

??int columnCount=8;
??int rowCount=sheet.getRows();
??for (int i = 1; i <rowCount; i++) {
???ArrayList sublist =new ArrayList();
???? for (int j = 0; j <columnCount; j++) {
???????? //注意，這里的兩個(gè)參數(shù)，第一個(gè)是表示列的，第二才表示行
???????? cell=sheet.getCell(j, i);
???????? //要根據(jù)單元格的類型分別做處理，否則格式化過的內(nèi)容可能會不正確
???????? /*
???????? if(cell.getType()==CellType.NUMBER){
???????????? System.out.print(((NumberCell)cell).getValue());
???????? }
???????? else if(cell.getType()==CellType.DATE){
???????????? System.out.print(((DateCell)cell).getDate());
???????? }
???????? else{
???????????? System.out.print(cell.getContents());
???????? }??
???????? ***/?????

???????? sublist.add(cell.getContents());
???? }
???? list.add(sublist);
??}
??//關(guān)閉它，否則會有內(nèi)存泄露
??workbook.close();
??return list;
?}
?