loadSheet

 loadSheet
   Loads an Excel sheet into a cell matrix using the Java library Apache POI

   workbook    Workbook object representing the Excel file
   sheet       name of the sheet (optional, default first sheet)

   raw         cell array with the data in the sheet
   flag        0 if everything worked, -1 if it didn't

 Usage: [raw, flag]=loadSheet(workbook, sheet)

0001 % loadSheet
0002 %   Loads an Excel sheet into a cell matrix using the Java library Apache POI
0003 %
0004 %   workbook    Workbook object representing the Excel file
0005 %   sheet       name of the sheet (optional, default first sheet)
0006 %
0007 %   raw         cell array with the data in the sheet
0008 %   flag        0 if everything worked, -1 if it didn't
0009 %
0010 % Usage: [raw, flag]=loadSheet(workbook, sheet)
0011 
0012 function [raw, flag]=loadSheet(workbook, sheet)
0013 if nargin<2
0014     sheet=[];
0015 end
0016 flag=0;
0017 raw={};
0018 
0019 if any(sheet)
0020     sh=workbook.getSheet(sheet);
0021 else
0022     sh=workbook.getSheetAt(0);
0023 end
0024 if isempty(sh)
0025     flag=-1;
0026     return;
0027 end
0028 
0029 lastRow=sh.getLastRowNum();
0030 wasEmpty=false(lastRow+1,1);
0031 raw=cell(lastRow+1,0); %Allocate space for the cell array. The number of columns isn't know yet, as it's saved row-by-row
0032 for i=0:lastRow
0033     row=sh.getRow(i);
0034     %Sometimes the last rows only contain formatting (or some other weird
0035     %Excel thing). Ignore such empty rows. Note the +1 to deal with that
0036     %Matlab indexing starts at 1
0037     if isempty(row)
0038         wasEmpty(i+1)=true;
0039         continue;
0040     end
0041     lastCol=row.getLastCellNum();
0042     
0043     %Adjust the size of the cell array if needed
0044     if (lastCol+1)>size(raw,2)
0045         raw=[raw cell(lastRow+1,lastCol+1-size(raw,2))];
0046     end
0047     
0048     %Loop over the columns
0049     for j=0:lastCol
0050         c=row.getCell(j,row.RETURN_BLANK_AS_NULL);
0051         if ~isempty(c)
0052             %Then decide how to save it depending on the type. First check
0053             %if it's a formula. If so the cached value should be used
0054             if c.getCellType()==c.CELL_TYPE_FORMULA
0055                 type=c.getCachedFormulaResultType();
0056             else
0057                 type=c.getCellType();
0058             end
0059             
0060             switch type
0061                 case c.CELL_TYPE_STRING
0062                     raw{i+1,j+1}=char(c.getRichStringCellValue().getString());
0063                 case c.CELL_TYPE_NUMERIC
0064                     raw{i+1,j+1}=c.getNumericCellValue();
0065                 case c.CELL_TYPE_BOOLEAN
0066                     raw{i+1,j+1}=c.getBooleanCellValue();
0067             end
0068         end
0069     end
0070 end
0071 
0072 %Remove empty rows
0073 raw(wasEmpty,:)=[];
0074 end