Export to Excel and blank records instead of records with zeros

WilsonIonara

Hi guys,

I am using an excel library  to create a tool that saves the datagrid to an excel file. However if the records are blank, the excel shows zeros, instead of blank records. How would I change the code so the blank records show up as blank? Thank you

Here is the link to the library

http://code.google.com/p/as3xls/


and here is the code snippet

     
     
    {
     
     CursorManager.setBusyCursor();
     
     sheet = new Sheet();
     var dataProviderCollection:ArrayCollection = excelgrid.dataProvider as ArrayCollection;
     var rowCount:int = dataProviderCollection.length;
     sheet.resize(rowCount + 1,excelgrid.columnCount);
     var columns:Array = excelgrid.columns;
     columns.splice(0, 1) // to delete the first column which is the number of each record , because this field has no datafield, the excel
      //function does not work if we include it in the table like it is included now
     var i:int = 0;
     
     for each (var field:DataGridColumn in columns)
     
     
     {  
            
      fields.push(field.dataField.toString());
     
      sheet.setCell(0,i,field.dataField.toString());
      i++;
      
     }
     
     for(var r:int=0; r < rowCount; r++)
     {
      var record:Object = dataProviderCollection.getItemAt(r);
       
      /*insert record starting from row no 2 else
      headers will be overwritten*/
      insertRecordInSheet(r+1,sheet,record);
      
     }
     
     
     var xls:ExcelFile = new ExcelFile();
     xls.sheets.addItem(sheet);
     
     CursorManager.removeBusyCursor();
     
     var bytes: ByteArray = xls.saveToByteArray();
     var fr:FileReference = new FileReference();
     fr.save(bytes,"DirectoryofForestProductsIndustries.xls");
    }
     
    else 
    {
     Alert.show("Make sure your search produces any results before using this tool");
     CursorManager.removeBusyCursor();
    }
    
   }

RhettZufelt

It is possible that before populating the field, you could use the as3xls functions to format that field as text.
Excel might not interpret the blanks as zeros.

Otherwise, I would put a trace statement in there and find out exactly what is being sent as zeros.
If blanks are zeros, but spaces or nulls are not, you might try something like the coding in red below.

{
     
     CursorManager.setBusyCursor();
     
     sheet = new Sheet();
     var dataProviderCollection:ArrayCollection = excelgrid.dataProvider as ArrayCollection;
     var rowCount:int = dataProviderCollection.length;
     sheet.resize(rowCount + 1,excelgrid.columnCount);
     var columns:Array = excelgrid.columns;
     columns.splice(0, 1) // to delete the first column which is the number of each record , because this field has no datafield, the excel
      //function does not work if we include it in the table like it is included now
     var i:int = 0;
     
     for each (var field:DataGridColumn in columns)
     
     
     {  
            
      fields.push(field.dataField.toString());
     
      sheet.setCell(0,i,field.dataField.toString());
      i++;
      
     }
     
     for(var r:int=0; r < rowCount; r++)
     {
      var record:Object = dataProviderCollection.getItemAt(r);
       
      /*insert record starting from row no 2 else
      headers will be overwritten*/
           trace("record = ",record);
                                           if (record == ""){
                                                    record = " ";    // or set record = null;   to see if something works
                                                   }
                                                insertRecordInSheet(r+1,sheet,record);
      
     }
     
     
     var xls:ExcelFile = new ExcelFile();
     xls.sheets.addItem(sheet);
     
     CursorManager.removeBusyCursor();
     
     var bytes: ByteArray = xls.saveToByteArray();
     var fr:FileReference = new FileReference();
     fr.save(bytes,"DirectoryofForestProductsIndustries.xls");
    }
     
    else 
    {
     Alert.show("Make sure your search produces any results before using this tool");
     CursorManager.removeBusyCursor();
    }
    
   }