none
How to read excel file using javascript

    Question

  • Hi all;

     

    Currently I wan to create a rss gadget and I want to let user to choose what rss feed they would like to present in the gadget, but the only way i can do it is hard code the line and url inside the main javascript of my gadget and add another name for the url and put in to my settings.js in my gadget file and then the gadget option will show out this new option.

    Now my question is, basically not all user can read and modifies javsascript, hence I was thinking on make a excel file and let the user copy and paste the link and name in the excel file, and when the user excuse the gadget the script will read all the data from excel file and let the user to choose which rss feed then wan to show in the gadget. (Note: all URl is copy and paste into the excel file by the user).

     

    Can anyone please teach me how to do it or give me a link or tutorial about this. I already try searching google but all information  I get is javascript access excel in database. But for a gadget apps, I cant see any database on it.

     

    Thank for reading...hope anyone can help me about this =D

     

    • Edited by ShenWei Thursday, June 03, 2010 12:36 PM
    Sunday, May 30, 2010 7:13 AM

All replies

  • var excel=new ActiveXObject("Excel.Application");
    excel.Workbooks.Open("my.xls");

    But why not using txt file? (Maybe user doesn't have Excel on his computer)

    or why not using the gadget settings page for getting URIs from user? (which, of course, is the best way)

    Sunday, May 30, 2010 9:11 AM
  • Megapup thank alot of your answer, but can you please help me check this

     

     

    function readData(x,y)
    {
          var excel=new ActiveXObject("Excel.Application"); excel.Workbooks.Open("my.xls");
        var excel.workbooks.open("my.xls");
    var data = excel_sheet.cells(x,y).value; //x,y consider the coordinate of row and column or the data    return data; }    
    	var value = readData(1,1);  //this should read cell A1
         
          document.write("Value from Excel file is " + value);

     

    but why when i put both of this code into my html file as show below, it wont give me a result? I mean when I double click it, how come it won't show me the information which i call and show it my browser? 

     

     

    <html>
    <body>
    <script type='text/javascript'>
    
    <-all the above code inside here->
    
    </script>
    </body>
    </html>

     

    and here i reply your question why i not settigs page for the user, this is because the gadget i was designing is basically update the information from school database, such as moodle database. But you must remember this as not all user is taking the same course, and if you wan to insert all course inside the gadget settings, then it will be a very long list as one school maybe have offer more that 1k of unit. Hence this is why I let the user choose what they want to insert into their gadget, but some how I am still very new in javascript, I try google to find how to read excel file by js, ya got many answer, but all is playing around with server side. And this is what i can think to make my gadget more useful is to let user add what rss url they want to read to the excel file and my gadget always read the excel file rather that need all the user to modifies their need it the main.js. Hope you understand what i try to say. Sorry for my bad english.

     

    You answer is very useful for me..Thank you.

     

     

     

    Sunday, May 30, 2010 12:34 PM
  • Something like this

    var excel=new ActiveXObject("Excel.Application");
    var book=excel.Workbooks.Open(System.Gadget.path+"
    \\my.xls");
    var sheet=book.Sheets.Item(1);
    var value=sheet.Range("A1");

    Sunday, May 30, 2010 3:34 PM
  •     var excel=new ActiveXObject("Excel.Application");
    	var book=excel.Workbooks.Open("c:/..../my.xls");
    	var sheet=book.Sheets.Item(1);
    	var data=sheet.Range("A1");
    	var feedArray = new Array(data);
    	var feedNames = new Array(data);

    Thank again for replying =D

    I try using your code putting into my setting.js, but it it does seem like opening and read the information inside the excel file...

     

    Monday, May 31, 2010 8:27 AM
  • var feedArray = new Array(5);
    	var feedNames = new Array(5);
    	var u = 0;
    	//var v = 1;
    	var trying = new Array('A1','A2','A3','A4','A5');
    	var excel=new ActiveXObject("Excel.Application");
    	var book=excel.Workbooks.Open("C:\my1.xls");
    	var sheet=book.Sheets.Item(1);
    	do{
    	
    	var value=sheet.Range(trying[u]);
    	
    	feedNames[u] = value;
    	feedArray[u] = value;
    	
    	u++;
    	}while(u < feedArray.length)

    Me again =p, after i modifies the code, itwork. BUT i found a new problem. The new problem is when I press setting from the gadget my processes will need to run a new file "excel.exe" and after my setting done, the excel.exe file won't terminate it self from my processes. Which mean that if the user keep changing the rss he/she want to read, one time the user press the setting it will come out a new excel.exe in my processes list which using 4k memory...for example if the user click setting 10 time the processes will have 10 excel.exe file running...so my question now is, did a gadget have a function to terminate program? i mean after reading the excel file then close it. I try to add a line in "event.closeAction == event.Action.commit" but it not useful. Any idea?

    p/s: Did gadget have any function like system.gadget.settings.close("filename")?  =p

    (cannot using object.close, because if we close the excel before the system commit, the excel file won't be readed)

    Hope you understand what I mean...and thank again for reading =D

    • Edited by ShenWei Monday, May 31, 2010 10:33 AM make clearly
    Monday, May 31, 2010 9:29 AM
  • Yes, I think I understand what you've meant (your english is better than mine :)

    You can use this function
    book.Close();
    and the created process will be closed.

    For additional parameters see help:
    http://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.workbook.close(v=VS.80).aspx

    Monday, May 31, 2010 11:59 AM
  • Thank again =D

     

    did what you mean is same as this link?

    http://msdn.microsoft.com/en-us/library/yb3tbdkw(VS.85).aspx

    if it is the same then I have already try it. I can't put any book.close() command after the commit function, it will make the whole commit function stop working. I mean when i press OK the system just will hang there. below are the code I trying. If i put the book.close() commant before the commit function(before user press ok), then the system will just close the excel without reading it.

     

    function settingsClosing(event) {

        // User hits OK on the settings page.

        if (event.closeAction == event.Action.commit)

        {

         feedName = feedType.value;

    feedCount = 10;

           System.Gadget.Settings.write("isSettings", true);

           System.Gadget.Settings.writeString("feedName", feedName);

            System.Gadget.Settings.write("feedCount", feedCount);

    book.close();

    }

     

    }


    Monday, May 31, 2010 12:42 PM
  • Try to play with book.close() parametres, maybe book.close(false) will work.

    or maybe the variable "book" isn't exist when you're trying to do "close" and script crashes

    In this example variable "a" is not exist inside function 'func2'
    function func1()
    {
         var a=1;
    }
    function func2()
    {
         var b=a;
         // b==undefined
    }

    Monday, May 31, 2010 1:05 PM
  • Thank again, I will try this tomorrow, now is time for me to go have a rest..=D

    You are seriously a very helpful guy. Thank very much.

    Monday, May 31, 2010 1:13 PM
  • Hi;

    The close function have already success working, thank alot. 

    Now I have a new question, if I wan read information from the excel more that 2 column then what can I do now? I mean for example A1 from the excel file is the rss name and B1 from the excel file is the URL link. So now my question is, how to I read a URL link in my excel file? I try using the same way, but it seem fail to get the url link from there. Thank for reading.

     

     

    var trying = new Array('A1','A2','A3','A4','A5');

    var excel=new ActiveXObject("Excel.Application");

    var book=excel.Workbooks.Open("C:\my1.xls");

    var sheet=book.Sheets.Item(1);

    //var value=sheet.Range(trying[u]);

     

    var trying2 = new Array('B1','B2','B3','B4','B5');

    var excel=new ActiveXObject("Excel.Application");

    var book=excel.Workbooks.Open("C:\my1.xls");

    var sheet=book.Sheets.Item(1);

     

     

    if (typeof System != "undefined" && System.Gadget.Settings.readString("Settings")) {

     

    feedName = System.Gadget.Settings.readString("feedName");

    if (feedName == trying[1]) {

    feedUrl= trying2[1];

    }

    else if (feedName == trying[2]) {

    .....

    .....

     

     

     

    Beside this I also get a problem from this "" var book=excel.Workbooks.Open("C:\my1.xls");"" function.

    If I save my excel file in another place for example here C:/trying/here/my1.xls, for theoretical I should also change the path name inside the

    blanket to var book = excel.Workbooks.Open(" C:\trying\here\my1.xls") but the weird things is if i change to this the file won't read by my

    script


    • Edited by ShenWei Tuesday, June 01, 2010 12:04 PM adding question
    Tuesday, June 01, 2010 11:49 AM
  • add new question, if I not changing the settings into read from excel, then this should be work.

     

    if (typeof System != "undefined" && System.Gadget.Settings.readString("Settings")) {

     

     feedName = System.Gadget.Settings.readString("feedName");

    if (feedName == helloworld) {

    feedUrl=' http://127.0.0.1/rss/file.php/2/2/forum/1/rss.xml';

    }

     

     

    if (feedName == helloworld)

    header.innerHTML =  "Hello world";     //this is the name show to user when they choosing helloworld rss. example the user

    // should see Hello world as the topic of the rss feed.

     

     

    But now since I changing my system to read from excel file, hence i should write like this.

     

     

    if (feedName == trying[1])

    header.innerHTML =  trying[1];

     

     

    But some how it didn't work..Thank

    Tuesday, June 01, 2010 12:28 PM
  • var obj=sheet.Range("A1") returns object, and you can get value by obj.Value or obj.Text (or through cells, see below)
    For strings with file names use double slashes: "C:\\folder1\\folder2\\file.txt"

    my.xls

       A    B 
    1 one	http://site1
    2 two	http://site2
    3 three	http://site3
    4 four	http://site4
    5 five	http://site5
    
    var book=(new ActiveXObject("Excel.Application")).Workbooks.Open("C:\\my.xls");
    var cells=book.Sheets.Item(1).cells;	// all cells
    	
    var feedName="three";	//System.Gadget.Settings.readString("feedName");
    var feedUrl="";
    	
    for(var i=1;i<5;i++)	// i - row
    	if(feedName==cells.item(i,1))	// 1 column (A) - name
    		feedUrl=cells.item(i,2);	// 2 column (B) - link
    			
    if(feedUrl!="")
    	(new ActiveXObject("WScript.Shell")).popup(feedUrl);	//http://site3		// header.innerHTML=feedUrl;	
    book.Close();
    Tuesday, June 01, 2010 3:52 PM
  • by the way, you can see variables, object through Visual Studio, it is very useful

     

    Tuesday, June 01, 2010 4:04 PM
  • Another question, My gadget now can show the rss feed title which I select. But how do I let my gadget show also the content of the feed? I not going to make a flyout for showing the content. I just want it to be like when the gadget is in undocked mode it show out the content and the title of the feed. And when the gadget is on docked mode it just showing the title of the gadget.

    Some how i know the problem is from here. But I can't find out the bug.

     

     

    function handleXML() {

    try {

       // do something with the response from the server

       var response = xmlHttp.responseText;

     

    // server error?

    if (response.indexOf("ERRNO") >= 0 || response.indexOf("error") >= 0 || response.length === 0) {

    throw(response.length === 0 ? "Server error." : response);

    }

       

    // XML --> RSS

    this.aXML = [];

    var aItem = xmlHttp.responseXML.getElementsByTagName('item');

     

    for (var iI=0;iI<aItem.length;iI++)

    {

    var aTemp = [];

    for (var iA=0;iA<aItem[iI].childNodes.length;iA++)

    {

    if (aItem[iI].childNodes[iA].nodeType != 3)

    {

    aTemp[aItem[iI].childNodes[iA].nodeName] = aItem[iI].childNodes[iA].childNodes[0].data;

    }

     

    }

    this.aXML.push(aTemp);

     

     

    }

     

    div = document.getElementById("bodyContent");

    div.innerHTML = '';

     

    for (var a=0;a<this.aXML.length;a++)

    {

     

    div.innerHTML += '<a href="' + this.aXML[a]['link'] +'">' + this.aXML[a]['title'] + '</a><br />';

    descdiv = document.createElement('div');

    descdiv.setAttribute('id', 'description_' + a);

     

    if (!description) descdiv.style.display = 'none';

     

    descdiv.innerHTML =  this.aXML[a]['description']

     

    div.appendChild(descdiv);

    div.innerHTML += '<br />';

    }  

    }

    catch(e) {

    // display error message

           alert(e.toString());

    }

    }

     

     

     

    Thank alot.  Can I have you email address, so that I can send my code for you to take a check...Thank again

    Wednesday, June 02, 2010 10:06 AM
  • Unfortunately I have no time to answer you fully now, and I will have been away for two days.
    I think you should check your code carefully to make sure that you always close the book which has been opened.
    RSS-feed usually represent structure called XML and you should analyse XML with some library or manually in order to get data fields from inside.
    Yes, you can of course send me code, maybe I could help, supermegapup@gmail.com

    Wednesday, June 02, 2010 12:00 PM
  • Haha, my code really have many insect(bug) inside. After reload all the file, my gadget work fine again. I think my question can be semi end here (hopefully can be THE END, if I can kill all the bug =s ). And Before I close this page, I would like to say a million of thank to you. You seriously is a very helpful guy I meet. I very appreciate on your help.

     

    Thank you very much megapup.

    P/S: If you are away for a trip, then at here I wish you  Have a splendid voyage. =D 

     

    Thank you;


    Wednesday, June 02, 2010 12:59 PM
  • Try to play with book.close() parametres, maybe book.close(false) will work.

    or maybe the variable "book" isn't exist when you're trying to do "close" and script crashes

    In this example variable "a" is not exist inside function 'func2'
    function func1()
    {
         var a=1;
    }
    function func2()
    {
         var b=a;
         // b==undefined
    }

    Sorry, disturb you again =p, last time u tell me if var a define at the first function then the second function will not get the value of a.

    Here my question see code below.

    function func1()
    {
       
    var book=(newActiveXObject("Excel.Application")).Workbooks.Open("C:\\my.xls");
    var cells=book.Sheets.Item(1).cells;	
    }
    
    function func2()
    {
      //so how do i going to close the excel file in this function? as like above what you say if book define at the first function, then it will be unknow valuable in this function. I want to close the excel file here is because I want the book close until the settings commit. Hence I need to close the book inside the commit function.
    
    
    }
    I mean I want to close the book until the settings commit. Hence I need to close the book inside the commit function there. So how going to close the book at the function?

     

     

    Wednesday, June 02, 2010 2:57 PM
  •  

     

    ShenWei... what's the Final Code!? 

    I'm triying 

     

    function readData()
    {
    	 var excel;
    	 var data="String"
    	 excel=new ActiveXObject("Excel.Application"); /*excel.workbooks.open("http://142.116.39.71/Produ.xls");*/
    	 var book=excel.Workbooks.open("http://142.116.39.71/Produ.xls");
    	 var sheet=book.Worksheets.Item(1); // var excel_sheet=excel_file.Worksheets("libro1");
    	 /*var data=sheet.Cells(x,y).Value;
    	var data=book.ActiveSheet.Cells("");
    	return data();*/
    
     excel.quit();
     excel.application.quit();
     excel = null;
     book = null;
     sheet = null;
     CollectGarbage();
    }
    	
    	value=readData("E,18"); // this should read cell A1
    	//var value=getRange("A1", "A1"); 
       
       document.write("Value from Excel file is "+value);

     

    BUT..... I have this: Value from Excel file is undefined

     

     

    please!!!  I just need to show Specfic Cells!

     

    Tuesday, March 22, 2011 7:34 PM