艾斯的軟體學習誌

JXL API觀念

Workbook	只能用於讀取，沒有寫入權限的Excel檔案
WritableSheet	有寫入權限的Excel檔案
Sheet	只能用於讀取，沒有寫入權限的活頁簿
WritableSheet	有寫入權限的活頁簿
Cell	一個儲存格
Column	行方向，縱向方向
Row	列方向，橫向方向
Label	包含文字內容的儲存格
Number	包含數字內容的儲存格

 

JXL API實作練習

下方為使用JXL API的實作練習(JExcelTest.java)

分別就讀取現有Excel檔案(DataRead.java)，和創建新的Excel檔案(DataWrite.java)寫成function做練習

 

JExcelTest.java

import java.util.ArrayList;
import jxl.write.WriteException;

public class JExcelTest {

    public static void main(String[] args) {
        // Read existed excel file
        DataRead dr = new DataRead();
        dr.readExcel("/Users/eyes/Documents/Preparatory/ExcelOperate/file/score.xls");
        System.out.println(dr.getValueFromCell(1, 1));
        System.out.println(dr.getValueFromCell(2, 1));
        System.out.println(dr.getValueFromCell(1, 2));
        System.out.println(dr.getValueFromCell(2, 2));

        // Get designated column with all row data
        ArrayList columnData = dr.getColumnData(1);
        for (Object o : columnData) {
            System.out.println(o);
        }

        // Get total column number
        System.out.println(dr.getColumnNumber());

        // Get designated row with all column data
        ArrayList rowData = dr.getRowNumber(1);
        for (Object o : rowData) {
            System.out.println(o);
        }

        dr.closeFile();

        // Create a new excel file
        try {
            DataWrite dw = new DataWrite();
            dw.createExcel("/Users/eyes/Documents/Preparatory/ExcelOperate/file/result.xls");
            dw.createSheet("t0", 0);
            dw.setValueIntoCell(0, 0, 8);
            dw.setValueIntoCell(0, 1, "YT");
            dw.closeFile();
        } catch (WriteException ex) {
            System.out.println(ex);
        }

    }

}

 

DataRead.java

import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import javax.swing.JOptionPane;
import jxl.Cell;
import jxl.CellType;
import jxl.LabelCell;
import jxl.NumberCell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

public class DataRead {

    private Workbook wbook;
    private Sheet sheet;
    private String strSheetName;

    public void readExcel(String path) {
        try {
            wbook = Workbook.getWorkbook(new File(path));
            int numberOfSheet = wbook.getNumberOfSheets();
            if (numberOfSheet != 1) {
                strSheetName = JOptionPane.showInputDialog("Whih sheet do you want to read?");
                sheet = wbook.getSheet(strSheetName);

            } else {
                sheet = wbook.getSheet(0);
                strSheetName = sheet.getName();
            }
        } catch (IOException | BiffException ex) {
            System.out.println(ex);
        }
    }

    public String getValueFromCell(int iColumnNumber, int iRowNumber) {
        String result
                = sheet.getCell(iColumnNumber, iRowNumber).getContents();
        return result;
    }

    public ArrayList getColumnData(int columnNumber) {
        ArrayList cellResult = new ArrayList();
        Cell[] cellList = sheet.getColumn(columnNumber);

        for (Cell cell : cellList) {
            if (cell.getType() == CellType.LABEL) {
                LabelCell lbc = (LabelCell) cell;
                cellResult.add(lbc.getString());
            } else if (cell.getType() == CellType.NUMBER) {
                NumberCell nc = (NumberCell) cell;
                cellResult.add(nc.getValue());
            }

        }

        return cellResult;
    }

    public int getColumnNumber() {
        return sheet.getColumns();
    }

    public ArrayList getRowNumber(int rowNumber) {
        ArrayList cellResult = new ArrayList();
        Cell[] cellList = sheet.getRow(rowNumber);

        for (Cell cell : cellList) {
            if (cell.getType() == CellType.LABEL) {
                LabelCell lbc = (LabelCell) cell;
                cellResult.add(lbc.getString());
            } else if (cell.getType() == CellType.NUMBER) {
                NumberCell nc = (NumberCell) cell;
                cellResult.add(nc.getValue());
            }

        }

        return cellResult;
    }

    public void closeFile() {
        wbook.close();
    }

}

 

DataWrite.java

import java.io.File;
import java.io.IOException;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;

public class DataWrite {

    private WritableWorkbook wwbCopy;
    private WritableSheet shSheet;
    private String strSheetName;

    public void createExcel(String path) {
        try {
            wwbCopy = Workbook.createWorkbook(new File(path));
        } catch (IOException ex) {
            System.out.println(ex);
        }
    }

    public void createSheet(String strSheetName, int number) {
        this.strSheetName = strSheetName;
        shSheet = wwbCopy.createSheet(strSheetName, number);
    }

    public void setValueIntoCell(int iColumnNumber, int iRowNumber, Object data) throws WriteException {
        WritableSheet wshTemp = wwbCopy.getSheet(strSheetName);

        if (data instanceof String) {
            Label labTemp = new Label(iColumnNumber, iRowNumber, (String) data);
            wshTemp.addCell(labTemp);
        } else if (data instanceof Double) {
            jxl.write.Number number
                    = new jxl.write.Number(iColumnNumber, iRowNumber, (double) data);
            wshTemp.addCell(number);
        } else if (data instanceof Integer) {
            jxl.write.Number number
                    = new jxl.write.Number(iColumnNumber, iRowNumber, Integer.parseInt(data.toString()));
            wshTemp.addCell(number);
        }

    }

    public void closeFile() {

        try {
            wwbCopy.write();
            wwbCopy.close();
        } catch (IOException | WriteException ex) {
            System.out.println(ex);
        }

    }

}