Advertisement
talkingtree

Get Table Array via JExcel API

Mar 19th, 2011
467
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
Java 2.17 KB | None | 0 0
  1. import java.io.File;
  2.  
  3. import jxl.Cell;
  4. import jxl.Sheet;
  5. import jxl.Workbook;
  6.  
  7. public class DataproviderMgr{
  8.    
  9.     private String xlFilePath;
  10.     private String sheetName;
  11.     private String tableName;
  12.    
  13.     public void setDataprovider(String xlFilePath, String sheetName, String tableName) {
  14.         try {
  15.             this.xlFilePath = xlFilePath;
  16.         } catch (Exception e)    {
  17.                 System.out.println("[ERROR] [DataProviderMgr] [setDataprovider]: " + e);
  18.             }
  19.         this.sheetName = sheetName;
  20.         this.tableName = tableName;
  21.     }
  22.    
  23.     // get all rows for table
  24.     public String[][] getData() throws Exception {
  25.             String[][] retObjArr = getTableArray(this.xlFilePath,this.sheetName,this.tableName);
  26.             return(retObjArr);
  27.     }  
  28.    
  29.     //Reads in the data values from an XLS spreadsheet. NOTE excel 2007 not supported.
  30.     private String[][] getTableArray(String xlFilePath, String sheetName, String tableName){
  31.         String[][] tabArray=null;
  32.         try{
  33.             Workbook workbook = Workbook.getWorkbook(new File(xlFilePath));
  34.             Sheet sheet = workbook.getSheet(sheetName);
  35.             int startRow,startCol, endRow, endCol,ci,cj;
  36.             Cell tableStart=sheet.findCell(tableName);
  37.             startRow=tableStart.getRow();
  38.             startCol=tableStart.getColumn();
  39.  
  40.             // startRow calculations are made to include the headers row
  41.             Cell tableEnd= sheet.findCell(tableName, startCol+1,startRow, 100, 64000,  false);                              
  42.  
  43.             endRow=tableEnd.getRow();
  44.             endCol=tableEnd.getColumn();
  45.             //System.out.println("startRow="+startRow+", endRow="+endRow+", " + "startCol="+startCol+", endCol="+endCol);
  46.             tabArray=new String[endRow-startRow][endCol-startCol-1];
  47.             ci=0;
  48.  
  49.             for (int i=startRow;i<endRow;i++,ci++){
  50.                 cj=0;
  51.                 for (int j=startCol+1;j<endCol;j++,cj++){
  52.                     tabArray[ci][cj]=sheet.getCell(j,i).getContents();
  53.                 }
  54.             }
  55.         }
  56.         catch (Exception e)    {
  57.             System.out.println("[ERROR] [DataProviderMgr] [getTableArray]: " + e);
  58.         }
  59.  
  60.         return(tabArray);
  61.     }
  62.    
  63.  
  64. }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement