topic Re: Export to Excel and blank records instead of records with zeros in ArcGIS API for Flex Questions

Export to Excel and blank records instead of records with zeros

ionarawilson1 — Sat, 11 Dec 2021 10:56:07 GMT

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();
    }
    
   }

Re: Export to Excel and blank records instead of records with zeros

RhettZufelt — Sun, 12 Dec 2021 16:12:30 GMT

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();
    }
    
   }

Re: Export to Excel and blank records instead of records with zeros

ionarawilson1 — Fri, 30 Nov 2012 20:24:33 GMT

Hi Rhett,

Thank you so much for helping me. This is I what I see when I use your code:

record = [object Object]
record = [object Object]
record = [object Object]
record = [object Object]
record = [object Object]
record = [object Object]
record = [object Object]
record = [object Object]
record = [object Object]
record = [object Object]
record = [object Object]
record = [object Object]
record = [object Object]
record = [object Object]
record = [object Object]
record = [object Object]
record = [object Object]
record = [object Object]
record = [object Object]
record = [object Object]
record = [object Object]
record = [object Object]
record = [object Object]
record = [object Object]
record = [object Object]
record = [object Object]
record = [object Object]
record = [object Object]
record = [object Object]
record = [object Object]
record = [object Object]

What should I do? Thanks

Re: Export to Excel and blank records instead of records with zeros

ionarawilson1 — Fri, 30 Nov 2012 20:26:46 GMT

By the way, I got 346 lines of that, all the records on my database

Re: Export to Excel and blank records instead of records with zeros

ionarawilson1 — Sat, 11 Dec 2021 10:56:09 GMT

If I set it to null, I get no records traced

 trace("record = ",record);
 if (record == null )
 {
 record = " ";  }

Re: Export to Excel and blank records instead of records with zeros

RhettZufelt — Sat, 01 Dec 2012 19:58:27 GMT

Now that I slow down and look at it, of course that is what you would get trying to just put a trace on an object.

I don't have a way to test or dive into this, but my thinking was to see if you could go to the part of the code that is building/populating the record object and trace the values being loaded into it. If it is actually passing the zeros, you could if/then change them there.

If it is being populated with blanks here, then you would know that it is getting changed elsewhere (I.e, somewhere in the Excel formatting or such) and could go from there.

R_

Re: Export to Excel and blank records instead of records with zeros

ionarawilson1 — Sat, 11 Dec 2021 10:56:12 GMT

Thank you Rhett. I believe the problem is when the excel is created because in another function when a data-grid is created the values are blank as in the original database. But I don't see anywhere on the function to create the excel something that would change the nulls to zero.

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();
    }
    
   }

Re: Export to Excel and blank records instead of records with zeros

RhettZufelt — Wed, 05 Dec 2012 19:13:22 GMT

So, what is the data type of the fields that are changing blanks to zeros?

Does this just happen when the input data is type string, smallInteger, double, etc, or does it just happen with one data type?

R_

Re: Export to Excel and blank records instead of records with zeros

DougBrowning — Wed, 05 Dec 2012 20:04:46 GMT

I am not sure if this will help but when I did a Python Excel script there was a way to check the Excel "cell_type" which is equal to 0 when the cell is blank.