Reading some excel data without loading the whole excel file

Reading some excel data without loading the whole excel file

Posted by: arkhyuki on 10 December 2018, 4:59 am EST

• 

  arkhyuki

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  Posted 10 December 2018, 4:59 am EST

  Hello,
  
  I am using the library to display the name of the sheets of an excel, and also to display the N first rows of a determined sheet.

  That is working correctly, but when I load a very big excel it takes a long time. That´s to be expected, since apparently is processing the WHOLE excel. But given that actually I do not need to read ALL the data in the Excel, but only a subset (first hundred of rows, I can do without the 500.000 that follow) I was wondering if there is a more efficient way of loading the file.

  My current code:

  
  public onFileChange(evt: any) {
    
      const target: DataTransfer = <DataTransfer>(evt.target);
      if (target.files.length !== 1) throw new Error('Cannot use multiple files');
    
      console.log(target.files.item(0).name);
      this.readMyExcel(target.files.item(0));
    }
    
    private readMyExcel(myFile)
    {
      this._excelIO.open(myFile, (json) => {
          console.log(json);
          console.log(json.sheetCount);
          
          var mySheets = json.sheets;
          this.myExcelSheets = [];
          var countersheet = 0;
          for (var x in mySheets) {
            this.myExcelSheets.push({name:x,position:countersheet});
            countersheet = countersheet + 1;
          }
  
          console.log(this.myExcelSheets);
          this.cdr.detectChanges();
  
      });
    }
  
  

  1. How could I modify the code above so that when reading a big file, I can still list the sheets names, without actually having to load all the data? Notice tha in the sample above I do not ven use the actual data of any sheet, I am only interested in the sheet name.

  2. In the above example I am using only ‘@grapecity/spread-excelio’. since I am only trying to parse some information from h excel, but I am not actually to display it I am not using the graphical component in “@grapecity/spread-sheets”. Is this a good practice? Should I be accesing the ‘json’ object the way I do, or is that a frail hack, and I must do this:

  
  this._spread.fromJSON(json, {});
  
  

  And then obtain the data from this._spread (GC.Spread.Sheets.Workbook)

  Thanks for your help

• 

  grapecity

  • Post Options:
  • Link
    Copy

  Posted 11 December 2018, 10:17 am EST

  Hello,

  I could not find a direct way to intercept the rows or sheetname from JSON without loading it completely.
  
  I have escalated this issue to our development team for further investigation. I will let you know as soon as I get an update on it. The tracking id for this is 268262

  Thanks,
  
  Deepak Sharma

• 

  arkhyuki

  • Post Options:
  • Link
    Copy

  Posted 12 December 2018, 7:42 am EST

  Hello Deepak,
  
  thanks.

  What about the point 2?

  Is the library designed with the idea in mind of accesing directly the json object, without loading the spread-sheets library?

  
   this._excelIO.open(myFile, (json) => {
          console.log(json);
          console.log(json.sheetCount);
  }
  
  

  in this example I use onl this import:

  
  import * as Excel from '@grapecity/spread-excelio';
  
  

  Thanks.

• 

  grapecity

  • Post Options:
  • Link
    Copy

  Posted 13 December 2018, 12:50 am EST

  Hello,

  You can import the JSON without actually using the Spread.Sheets for UI.
  
  Excel IO gives you the JSON which you load into Spread.Sheets using fromJSON method. But if you do not want to show the Excel file in UI, you can access the JSON in code too. So this approach should work fine:

  this._excelIO.open(myFile, (json) => {
  
  console.log(json);
  
  console.log(json.sheetCount);
  
  }

  However, if you do not include the Spread.Sheets it gives ‘GC’ undefined error at:

  var excelIO = new GC.Spread.Excel.IO();

  but loads the JSON.

  Thanks,
  
  Deepak Sharma