writeSheet

 writeSheet
   Writes a cell matrix to an Excel sheet into using the Java library Apache POI

   workbook        Workbook object representing the Excel file
   sheetName       name of the sheet
   sheetPosition   0-based position of the sheet
   captions        cell array of captions (optional)
   units           WRITE INFO
   raw             cell array with the data in the sheet
   isIntegers      true if numeric values should be integers (optional, default
                   true)

 Usage: wb=writeSheet(wb,sheetName,sheetPosition,captions,units,raw)

0001 function wb=writeSheet(wb,sheetName,sheetPosition,captions,units,raw,isIntegers)
0002 % writeSheet
0003 %   Writes a cell matrix to an Excel sheet into using the Java library Apache POI
0004 %
0005 %   workbook        Workbook object representing the Excel file
0006 %   sheetName       name of the sheet
0007 %   sheetPosition   0-based position of the sheet
0008 %   captions        cell array of captions (optional)
0009 %   units           WRITE INFO
0010 %   raw             cell array with the data in the sheet
0011 %   isIntegers      true if numeric values should be integers (optional, default
0012 %                   true)
0013 %
0014 % Usage: wb=writeSheet(wb,sheetName,sheetPosition,captions,units,raw)
0015 
0016 if nargin<7
0017     isIntegers=true;
0018 end
0019 
0020 %Adds the required classes to the static Java path if not already added
0021 addJavaPaths();
0022 
0023 %Import required classes from Apache POI.
0024 import org.apache.poi.ss.usermodel.*;
0025 import org.apache.poi.ss.util.*;
0026 import org.apache.poi.hssf.usermodel.*;
0027 import org.apache.poi.xssf.usermodel.*;
0028 
0029 %Set default style object and bold style object
0030 defaultStyle = wb.createCellStyle();
0031 if isIntegers
0032     idx = wb.getCreationHelper().createDataFormat().getFormat('0');
0033 else
0034     idx = wb.getCreationHelper().createDataFormat().getFormat('##0.00');
0035 end
0036 defaultStyle.setDataFormat(idx);
0037 
0038 boldFont=wb.createFont();
0039 boldFont.setBoldweight(boldFont.BOLDWEIGHT_BOLD);
0040 boldStyle=defaultStyle.clone();
0041 boldStyle.setFont(boldFont);
0042 
0043 s=wb.createSheet();
0044 wb.setSheetName(sheetPosition, sheetName);
0045 
0046 %Create the header row and units
0047 counter=0;
0048 if ~isempty(captions)
0049     r=s.createRow(counter);
0050     for i=0:numel(captions)-1
0051         c=r.createCell(i);
0052         c.setCellValue(captions{i+1});
0053         c.setCellStyle(boldStyle);
0054     end
0055     counter=counter+1;
0056 end
0057 if ~isempty(units)
0058     r=s.createRow(counter);
0059     for i=0:numel(units)-1
0060         c=r.createCell(i);
0061         content=units{i+1};
0062         if iscell(content) && numel(content)==2
0063             c.setCellValue(content{1});
0064             
0065             %This means that the cell should have a list of allowed values
0066             dvHelper=XSSFDataValidationHelper(s);
0067             
0068             cellRange=CellRangeAddressList(1,1,i,i); %Units are on the second line
0069             dvConstraint=dvHelper.createExplicitListConstraint(toValid(content{2}));
0070             validation=dvHelper.createValidation(dvConstraint, cellRange);
0071             validation.setShowErrorBox(true);
0072             s.addValidationData(validation);
0073         else
0074             c.setCellValue(content{1});
0075         end
0076         c.setCellStyle(boldStyle);
0077     end
0078     counter=counter+1;
0079 end
0080 
0081 %Loop through and fill in the values
0082 for i=0:size(raw,1)-1
0083     r=s.createRow(i+counter);
0084     
0085     for j=0:size(raw,2)-1
0086         if ~isempty(raw{i+1,j+1})
0087             c=r.createCell(j);
0088             content=raw{i+1,j+1};
0089             if iscell(content) && numel(content)==2
0090                 c.setCellValue(content{1});
0091                 
0092                 %This means that the cell should have a list of allowed
0093                 %values
0094                 dvHelper=XSSFDataValidationHelper(s);
0095                 cellRange=CellRangeAddressList(i+1,i+1,j,j);
0096                 dvConstraint=dvHelper.createExplicitListConstraint(toValid(content{2}));
0097                 validation=dvHelper.createValidation(dvConstraint, cellRange);
0098                 validation.setShowErrorBox(true);
0099                 s.addValidationData(validation);
0100             else
0101                 c.setCellValue(content);
0102             end
0103             c.setCellStyle(defaultStyle);
0104         end
0105     end
0106 end
0107 
0108 %Pre-determine column widths for each sheet
0109 if strcmp(sheetName,'RXNS')
0110     widths=[786;2358;7860;15719;3406;7860;3406;3406;2358;3406;7860;7860;3668;7860;7860;4192];
0111 elseif strcmp(sheetName,'METS')
0112     widths=[786;7860;7860;3668;7860;7860;7860;3406;3668;1834];
0113 elseif strcmp(sheetName,'COMPS')
0114     widths=[786;3144;7860;3144;7860];
0115 elseif strcmp(sheetName,'GENES')
0116     widths=[786;3144;7860;3144;3406];
0117 elseif strcmp(sheetName,'MODEL')
0118     widths=[786;3144;7860;3668;3668;5240;5240;7860;7860;2620;7860];
0119 end;
0120 
0121 %Resize columns
0122 for i=0:size(raw,2)-1
0123     s.setColumnWidth(i,widths(i+1));
0124 end
0125 
0126 %Add freeze panes
0127 if counter>0
0128     s.createFreezePane(0,counter,0,counter);
0129 end
0130 end
0131 
0132 function I=toValid(J)
0133 I=cell(numel(J),1);
0134 for i=1:numel(J)
0135     if ischar(J{i})
0136         I(i)=J(i);
0137     else
0138         if islogical(J{i})
0139             if J{i}==true
0140                 I{i}='TRUE';
0141             else
0142                 I{i}='FALSE';
0143             end
0144         else
0145             %Other, most likely numbers
0146             I{i}=num2str(J{i});
0147         end
0148     end
0149 end
0150 end