homepage Welcome to WebmasterWorld Guest from 54.145.183.190
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Code, Content, and Presentation / JavaScript and AJAX
Forum Library, Charter, Moderator: open

JavaScript and AJAX Forum

    
Excel and Javascript
learning javascript/arrays -
Rominall



 
Msg#: 4576567 posted 3:51 pm on May 22, 2013 (gmt 0)

I'm new to javascript so bear with me. I have cobbled together some code to read an excel file. it currently does two things, retrieves the value in cell A1 variable name repCount and loads the next X cells in col a to a table.

What I'd really like to do is have this list populate an array that I can manipulate for several purposes. I haven't tried this yet because I'm still working on how to view if I've created the array correctly.

I'm pretty good at VB but syntax is where I have an issue. Along with lifetimes of variables.

I will need the repCount variable to be global. No clue how to accomplish that.

I need to replace the row count in the table function to be repCount.

<script language="javascript" >
// Open the spreadsheet and get the count of reps
function GetData(cell,row)
{
var excel = new ActiveXObject("Excel.Application");
var excel_file = excel.Workbooks.Open("somedoc.xlsx");
var excel_sheet = excel.Worksheets("Sheet1");
var repCount = excel_sheet.Cells(cell,row).Value;
document.getElementById('div1').innerText = repCount;


}

// open spreadsheet and populate table with representatives
var excelApp=null, excelFile=null, excelSheet=null;
var filename = "somedoc.xlsx";

function initExcel(filename)
{
excelApp = new ActiveXObject("Excel.Application");
excelFile = excelApp.Workbooks.Open(filename);
excelSheet = excelApp.Worksheets('Sheet1');
}

function myShutdownExcel()
{
excelApp.Quit();
excelApp=null;
excelFile=null;
excelSheet=null;
}

function myGetData(column, row)
{
return excelSheet.Cells(column, row).Value;
}

function byId(e) {return document.getElementById(e);}

function myOnLoad2()
{
var numRows = 33, numCols = 1;
var tBody = byId('dataTableBody');
var rowIndex, colIndex, curVal;
var curRow, curCell, curCellText;
initExcel(filename);

for (rowIndex=2; rowIndex<=numRows; rowIndex++)
{
curRow = document.createElement('tr');
for (colIndex=1; colIndex<=numCols; colIndex++)
{
curVal = myGetData(rowIndex, colIndex);
curCell = document.createElement('td');
curCell.setAttribute('title', 'The value of cell [' + rowIndex + ',' + colIndex +']\nis: ' + curVal);
curCellText = document.createTextNode(curVal);
curCell.appendChild(curCellText);
curRow.appendChild(curCell);
}
tBody.appendChild(curRow);
}
myShutdownExcel();
}

</script>


</head>
<body>
<p>&nbsp;</p>
<div style="background: #009955; width:'100%';" align="center">
<font color="#000080" size="12pt">
<b>Get data from excel sheets</b>
</font>
</div>
<center>
<p>&nbsp;</p>
<div id="div1" style="background: #DFDFFF; width:'100%';" align="center">
To start the statistics page - Click the button below
</div>

<input type="button" value="Start" onClick="GetData(1,1);" />
<input type="button" value="Next" onClick="myOnLoad2()" />
</center>
<b>Get data from excel sheets</b>
<table id='dataTable'>
<tbody id='dataTableBody'>
</tbody>
</table>
</body>
</html>


Am I going at this correctly or do I need to go another direction? Any help would be appreciated or point me to some really good documentation. I want to learn this and don't mind reading.

 

daveVk

WebmasterWorld Senior Member 5+ Year Member



 
Msg#: 4576567 posted 2:02 pm on May 23, 2013 (gmt 0)

To make repCount global

var repCount;
function GetData(cell,row)
{
var excel = new ActiveXObject("Excel.Application");
var excel_file = excel.Workbooks.Open("somedoc.xlsx");
var excel_sheet = excel.Worksheets("Sheet1");
repCount = excel_sheet.Cells(cell,row).Value;
document.getElementById('div1').innerText = repCount;
}

But you already have to many globals, ie excelApp, excelFile, excelSheet, filename and all the functions.

What you are making is an Excel Tool ? so start with

function ExcelTool(myFileName) {
var et = this;
et.repCount = 0;
et.filename = myFileName;
et.excelApp = new ActiveXObject("Excel.Application");
et.excelFile = et.excelApp.Workbooks.Open(myFileName);
et.excelSheet = excelApp.Worksheets('Sheet1');
}

then rewrite other functions as methods of ExcelTool

Its an interesting languages that allows many coding styles take your time, treating it like VB is not a good idea.

The "Crockford on Javascript" videos are well worth a look.

Welcome to forum

Rominall



 
Msg#: 4576567 posted 6:15 pm on Jun 5, 2013 (gmt 0)

Thank you for the videos. My problem - not all of my target audience can't run ActiveX. :(

daveVk

WebmasterWorld Senior Member 5+ Year Member



 
Msg#: 4576567 posted 6:31 am on Jun 6, 2013 (gmt 0)

Yes ActiveX is IE only, and may be disabled for security reasons.

Perhaps doing it server side is an option ?

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / JavaScript and AJAX
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved