Pulling and displaying data from excel spreadsheet

Dev Articles Community Forums Sponsor:

February 27th, 2006, 03:12 PM

Thorsun

Registered User

Join Date: Feb 2006

Posts: 1

Time spent in forums: 35 m 19 sec
Reputation Power: 0

Pulling and displaying data from excel spreadsheet

I am trying to develop a javascript application that opens an excel spreadsheet and pulls data from one of the worksheets for display. I've seen the codes that can display data the same as excel. but I don't want to display the data in a spreadsheet format. I want to be able to take the data from the spreadsheet and display it any way I want to.

Could anyone walk me through what I need to do? Thanks.

June 30th, 2006, 04:39 PM

colton22

\ ^_^ / - *Local Friend*

Join Date: Jun 2006

Location: near chicago, Illinois

Posts: 469

Time spent in forums: 2 Days 1 h 36 m 37 sec
Reputation Power: 12

i really dont think that is possible because files like .doc load seperatly through word, the only way i have seen this happen is between .doc files and .xls files

dont take my answer literally, im not a complete expert

colton22

September 7th, 2006, 06:46 AM

nickf

Registered User

Join Date: Sep 2006

Posts: 1

Time spent in forums: 14 m 35 sec
Reputation Power: 0

How to read/write to Excel using JavaScript

Reply (if still looking now?!):

U can read from Excel and write to Excel using the following code (for a webpage):

Code:

<html>
<script>
function mytest1() {
	var Excel, Book;	// Declare the variables
	Excel = new ActiveXObject("Excel.Application");	// Create the Excel application object.
	Excel.Visible = false;	// Make Excel invisible.
	Book = Excel.Workbooks.Add()	// Create a new work book.
	Book.ActiveSheet.Cells(1,1).Value = document.all.my_textarea1.value;
	Book.SaveAs("C:/Documents and Settings/ProGamer/Desktop/word to text/TEST.xls");		
	Excel.Quit();	// Close Excel with the Quit method on the Application object.
}

function mytest2() {
	var Excel;
	Excel = new ActiveXObject("Excel.Application");	
	Excel.Visible = false;
	form1.my_textarea2.value = Excel.Workbooks.Open("C:/Documents and Settings/ProGamer/Desktop/word to text/TEST.xls").ActiveSheet.Cells(1,1).Value;
	Excel.Quit();
}

</script>

<body>
<form name="form1">
	<input type=button onClick="mytest1();" value="Send Excel Data"><input type=text name="my_textarea1" size=70 value="enter ur data here">
	<br><br>
	<input type=button onClick="mytest2();" value="Get Excel Data"><input type=text name="my_textarea2" size=70  value="no data collected yet">

</form>
</body>
</html>

But u will have to go into your browser's internet options and enable ActiveX controls.
Have fun,
Nick

April 9th, 2009, 09:46 AM

StanZelda

Registered User

Join Date: Apr 2009

Posts: 1

Time spent in forums: 7 m 40 sec
Reputation Power: 0

Append Data to Excel Spreadsheet

Hi Guys, Hope someone can help me, I fairly new scripting and I found the code below that is almost exactly what I want, except I need to append to the the spreadsheet on the next available line, instead of creating a new document each time - hope someone can help.

Cheers Jason.

[code]
<html>
<script>
function mytest1() {
var Excel, Book; // Declare the variables
Excel = new ActiveXObject("Excel.Application"); // Create the Excel application object.
Excel.Visible = false; // Make Excel invisible.
Book = Excel.Workbooks.Add() // Create a new work book.
Book.ActiveSheet.Cells(2,2).Value = document.all.my_textarea1.value;
Book.SaveAs("C:/temp/TEST.xls");
Excel.Quit(); // Close Excel with the Quit method on the Application object.
}

function mytest2() {
var Excel;
Excel = new ActiveXObject("Excel.Application");
Excel.Visible = false;
form1.my_textarea2.value = Excel.Workbooks.Open("C:/temp/TEST.xls").ActiveSheet.Cells(1,1).Value;
Excel.Quit();
}

</script>

<body>
<form name="form1">
<input type=button onClick="mytest1();" value="Send Excel Data"><input type=text name="my_textarea1" size=70 value="enter ur data here">
<br><br>
<input type=button onClick="mytest2();" value="Get Excel Data"><input type=text name="my_textarea2" size=70 value="no data collected yet">

</form>
</body>
</html>
[code]

July 28th, 2009, 05:35 AM

urparagjyoti

Registered User

Join Date: Jul 2009

Posts: 1

Time spent in forums: 8 m 25 sec
Reputation Power: 0

Could not run in IE7.0

Quote:

Originally Posted by nickf

Reply (if still looking now?!):
Thank you so much for your code but I could not run in firefox or safari.How can I run it,please help me!!!!!!!
U can read from Excel and write to Excel using the following code (for a webpage):

Code:

<html>
<script>
function mytest1() {
	var Excel, Book;	// Declare the variables
	Excel = new ActiveXObject("Excel.Application");	// Create the Excel application object.
	Excel.Visible = false;	// Make Excel invisible.
	Book = Excel.Workbooks.Add()	// Create a new work book.
	Book.ActiveSheet.Cells(1,1).Value = document.all.my_textarea1.value;
	Book.SaveAs("C:/Documents and Settings/ProGamer/Desktop/word to text/TEST.xls");		
	Excel.Quit();	// Close Excel with the Quit method on the Application object.
}

function mytest2() {
	var Excel;
	Excel = new ActiveXObject("Excel.Application");	
	Excel.Visible = false;
	form1.my_textarea2.value = Excel.Workbooks.Open("C:/Documents and Settings/ProGamer/Desktop/word to text/TEST.xls").ActiveSheet.Cells(1,1).Value;
	Excel.Quit();
}

</script>

<body>
<form name="form1">
	<input type=button onClick="mytest1();" value="Send Excel Data"><input type=text name="my_textarea1" size=70 value="enter ur data here">
	<br><br>
	<input type=button onClick="mytest2();" value="Get Excel Data"><input type=text name="my_textarea2" size=70  value="no data collected yet">

</form>
</body>
</html>

But u will have to go into your browser's internet options and enable ActiveX controls.
Have fun,
Nick

January 30th, 2011, 08:04 AM

Nontuthuko

Registered User

Join Date: Jan 2011

Location: Durban

Posts: 1

Time spent in forums: 34 m 40 sec
Reputation Power: 0

Send a message via Google Talk to Nontuthuko

Writting and Reading from files using javascripts (excel and text files)

<html>
<head>
<title>Read from Excel or Access</title>
<script language="JavaScript">
function getCount()
{

var fso = new ActiveXObject("Scripting.FileSystemObject");
var s = fso.CreateTextFile("C:\\ASPConnection\\test.txt", true);
var t = fso.CreateTextFile("C:\\ASPConnection\\test1.txt", true);
var r = fso.CreateTextFile("C:\\ASPConnection\\test2.txt", true);
var cn = new ActiveXObject("ADODB.Connection");
var strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = C:\\ASPConnection\\Barcodes_VOCS_2011.xls;Persist Security Info=False;Extended Properties=Excel 8.0;";
cn.Open(strConn);
var rs = new ActiveXObject("ADODB.Recordset");
var SQL = "select * from [Sheet7$]";
rs.Open(SQL, cn);
if(rs.bof)
{
document.write('No records available for this query');
}
if(!rs.bof)
{
rs.MoveFirst()
while(!rs.eof)
{
var store = rs.fields(2).value;
for(var i=0; i!= rs.fields.count; ++i)
{
document.write(rs.fields(i).value + ",");
t.Write(rs.fields(i).value + ","); }
s.WriteLine(rs.fields(2).value + ",");
t.WriteLine(",");
document.write("<br />");
rs.MoveNext() }
}
rs.Close();
cn.Close();
s.Close();
t.Close();
r.Close();
}

var ForReading = 1, ForWriting = 2, ForAppending = 8;
var TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0;
var fso = new ActiveXObject("Scripting.FileSystemObject");

// Open the files for input and output
//CHANGE THESE LOCATIONS TO ENSURE THAT :
// a) testfile1.txt exists and
// b) testfile2.txt can be created and is writable
var filename1 = "C:\\ASPConnection\\test.txt";
var filename2 = "C:\\ASPConnection\\test2.txt";

var f1 = fso.OpenTextFile(filename1, ForReading, true);
// Open the file for input.
f2 = fso.OpenTextFile(filename2, ForWriting);

var r = f1.ReadLine(); //This line will read the data to the input file
var g = r;
// Read from the file and display the results.
var r = f1.ReadLine(); //This line will read the data to the input file
f2.WriteLine(r); //This line will write the data to the output file
document.write(r + "<br />");
// Read from the file and display the results.
while (!f1.AtEndOfStream)
{
if(r!=g)
{var r = f1.ReadLine(); //This line will read the data to the input file
document.write (r + "<br />"); //This line will display the data on the screen
f2.WriteLine(r); //This line will write the data to the output file
var g = r;
}
else
{var r = f1.ReadLine(); //This line will read the data to the input file
}
/*var r = f1.ReadLine(); //This line will read the data to the input file
document.write (r + "<br />"); //This line will display the data on the screen
f2.WriteLine(r); //This line will write the data to the output file*/
}
f1.Close();
f2.Close();
</script>
</head>
<body>
<input type="button" value="Get count" onClick="getCount()">
</body>
</html>

April 7th, 2014, 09:28 AM

Oladimeji

Registered User

Join Date: Apr 2014

Posts: 2

Time spent in forums: 1 m 29 sec
Reputation Power: 0

Hello, how are you doing. Thanks for this eyeopener. pls i need something like this that can enable me to a search form that will fetch a rows data from excel using a column as search string, then use the searched line to retrieve d main form for update and save same.

April 7th, 2014, 09:29 AM

Oladimeji

Registered User

Join Date: Apr 2014

Posts: 2

Time spent in forums: 1 m 29 sec
Reputation Power: 0

Thank so much

Quote:

Originally Posted by urparagjyoti

Pulling and displaying data from excel spreadsheet

Developer Shed Advertisers and Affiliates