By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,967 Members | 843 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,967 IT Pros & Developers. It's quick & easy.

open an excel file and paste values

P: n/a
Hi all,

I got a pre-formatted spreadsheet. would it be possible using js to copy the
data from a table on the current webpage, open the spreadsheet and paste the
content ?
if so, anyone got any links or pointers?

i've already tried google - but all i get is ActiveX methods which work in a
very few cases.

Thanks

T
Mar 26 '07 #1
Share this Question
Share on Google+
3 Replies


P: n/a
ASM
toffee a écrit :
Hi all,

I got a pre-formatted spreadsheet. would it be possible using js to copy the
data from a table on the current webpage, open the spreadsheet and paste the
content ?
I would be very surprised to see Javascript in a browser writing
something in another application (Excel).
all i get is ActiveX methods which work in a
very few cases.
Without a feature of the system of user's computer I imagine you can't
do what you want.

Perhaps an applet in Java ?

You can try to put (copy via JS) your datas in a html table, then to
save this new page and to ask Excel to open it ?

--
Stephane Moriaux et son (moins) vieux Mac déjà dépassé
Stephane Moriaux and his (less) old Mac already out of date
Mar 26 '07 #2

P: n/a
On Mar 27, 12:08 am, ASM <stephanemoriaux.NoAd...@wanadoo.fr.invalid>
wrote:
toffee a écrit :
Hi all,
I got a pre-formatted spreadsheet. would it be possible using js to copy the
data from a table on the current webpage, open the spreadsheet and paste the
content ?

I would be very surprised to see Javascript in a browser writing
something in another application (Excel).
all i get is ActiveX methods which work in a
very few cases.

Without a feature of the system of user's computer I imagine you can't
do what you want.

Perhaps an applet in Java ?

You can try to put (copy via JS) your datas in a html table, then to
save this new page and to ask Excel to open it ?

--
Stephane Moriaux et son (moins) vieux Mac déjà dépassé
Stephane Moriaux and his (less) old Mac already out of date

yeah you can't do this because the access methods only come with the
user having office on their machine of course, so if they don't have
office you are stuck. Also of course this would be heavy security
restrictions surrounding anything like this.

You can easily interact with a webpage <--MS Office using COM inside
IE, but that's it, plus of course you can have a macro inside the
office document, which if allowed to run would do a similar thing.

Here's an example of a webpage generating data, opening excel and
drawing a graph. I think I got it from the MS website here?
http://support.microsoft.com/kb/234774 once:
just save as html, open from the local file system (or you will have
to allow a few security warnings)

This stuff is great if you are generating data from a database for an
end user on a intranet where you can control the end usrs machine via
group policy, obviously the webpage could make use of data input by
the user, or data from the server / database / parsed from an upload
from the user / web service / feed etc...

<HTML>
<BODY>
Press the button to start Excel and display quarterly data.
<SCRIPT LANGUAGE="VBScript">
Function CreateNamesArray()
' Create an array to set multiple values at once.
Dim saNames(5, 2)
saNames(0, 0) = "John"

saNames(0, 1) = "Smith"
saNames(1, 0) = "Tom"
saNames(1, 1) = "Brown"
saNames(2, 0) = "Sue"
saNames(2, 1) = "Thomas"
saNames(3, 0) = "Jane"
saNames(3, 1) = "Jones"
saNames(4, 0) = "Adam"
saNames(4, 1) = "Johnson"
CreateNamesArray = saNames
End Function
</SCRIPT>

<SCRIPT LANGUAGE="JScript">
function AutomateExcel()
{

// Start Excel and get Application object.
var oXL = new ActiveXObject("Excel.Application");

oXL.Visible = true;

// Get a new workbook.
var oWB = oXL.Workbooks.Add();
var oSheet = oWB.ActiveSheet;

// Add table headers going cell by cell.
oSheet.Cells(1, 1).Value = "First Name";
oSheet.Cells(1, 2).Value = "Last Name";
oSheet.Cells(1, 3).Value = "Full Name";
oSheet.Cells(1, 4).Value = "Salary";

// Format A1:D1 as bold, vertical alignment = center.
oSheet.Range("A1", "D1").Font.Bold = true;
oSheet.Range("A1", "D1").VerticalAlignment = -4108; //
xlVAlignCenter

// Create an array to set multiple values at once.

// Fill A2:B6 with an array of values (from VBScript).
oSheet.Range("A2", "B6").Value = CreateNamesArray();

// Fill C2:C6 with a relative formula (=A2 & " " & B2).
var oRng = oSheet.Range("C2", "C6");
oRng.Formula = "=A2 & \" \" & B2";

// Fill D2:D6 with a formula(=RAND()*100000) and apply format.
oRng = oSheet.Range("D2", "D6");
oRng.Formula = "=RAND()*100000";
oRng.NumberFormat = "$0.00";

// AutoFit columns A:D.
oRng = oSheet.Range("A1", "D1");
oRng.EntireColumn.AutoFit();

// Manipulate a variable number of columns for Quarterly Sales
Data.
DispalyQuarterlySales(oSheet);

// Make sure Excel is visible and give the user control
// of Excel's lifetime.
oXL.Visible = true;
oXL.UserControl = true;
}

function DispalyQuarterlySales(oWS)
{
var iNumQtrs, sMsg, iRet;

// Number of quarters to display data for.
iNumQtrs = 4;

// Starting at E1, fill headers for the number of columns
selected.
var oResizeRange = oWS.Range("E1", "E1").Resize(1,iNumQtrs);
oResizeRange.Formula = "=\"Q\" & COLUMN()-4 & CHAR(10) & \"Sales
\"";

// Change the Orientation and WrapText properties for the headers.
oResizeRange.Orientation = 38;
oResizeRange.WrapText = true;

// Fill the interior color of the headers.
oResizeRange.Interior.ColorIndex = 36;

// Fill the columns with a formula and apply a number format.
oResizeRange = oWS.Range("E2", "E6").Resize(5,iNumQtrs);
oResizeRange.Formula = "=RAND()*100";
oResizeRange.NumberFormat = "$0.00";

// Apply borders to the Sales data and headers.
oResizeRange = oWS.Range("E1", "E6").Resize(6,iNumQtrs);
oResizeRange.Borders.Weight = 2; // xlThin

// Add a Totals formula for the sales data and apply a border.
oResizeRange = oWS.Range("E8", "E8").Resize(1,iNumQtrs);
oResizeRange.Formula = "=SUM(E2:E6)";
// 9 = xlEdgeBottom
oResizeRange.Borders(9).LineStyle = -4119; //xlDouble
oResizeRange.Borders(9).Weight = 4; //xlThick

// Add a Chart for the selected data.

oResizeRange = oWS.Range("E2:E6").Resize(5,iNumQtrs);
var oChart = oWS.Parent.Charts.Add();
oChart.ChartWizard(oResizeRange, -4100, null, 2); // -4100 =
xl3dColumn
oChart.SeriesCollection(1).XValues = oWS.Range("A2", "A6");
for (iRet = 1; iRet <= iNumQtrs; iRet++) {
oChart.SeriesCollection(iRet).Name = "=\"Q" + iRet + "\"";
}
oChart.Location(2, oWS.Name); // 2 = xlLocationAsObject

// Move the chart so as not to cover your data.
oWS.Shapes("Chart 1").Top = oWS.Rows(10).Top;
oWS.Shapes("Chart 1").Left = oWS.Columns(2).Left;
}
</SCRIPT>
<P><INPUT id=button1 type=button value="Start Excel"
onclick="AutomateExcel"></P>
</BODY>
</HTML>


Mar 27 '07 #3

P: n/a
ASM wrote:
toffee a écrit :
>I got a pre-formatted spreadsheet. would it be possible
using js to copy the data from a table on the current
webpage, open the spreadsheet and paste the content ?

I would be very surprised to see Javascript in a browser
writing something in another application (Excel).
It's possible in j(ava)script using OWC:

<object id="O" classid="CLSID:0002E510-0000-0000-C000-000000000046">
</object>
<div id="D">
<table border="1">
<tr><td>123</td><td>456</td></tr>
<tr><td><i><u><b>abc</b></u></i></td></tr>
</table>
</div>
<a onClick="O.HTMLData = document.getElementById('D').innerHTML;
return false;"
href="#">put HTML table in Excel</a>

See http://en.wikipedia.org/wiki/Office_Web_Components
There are Microsoft OWC tutorials on http://msdn.microsoft.com

Use the "Export to Excel" button at the top (green cross) to get the
actual xls-file. Also, the pre-formatting should be no problem by
setting the available <param>'s of the object and by initializing the
HTMLdata-parameter of the component.

But. The frustration about this technology is - as often with
Microsoft products - that it lacks compatibility, robustness and
continuity. OWC only works in IE5+ with Office 2000+ installed, and
Microsoft will stop supporting it as of Office 2007.

I would therefore counsel a more solid strategy that puts a part of
the work at the server side. An HTML form could go like this:

<form method="post" action="getexcel.xls">
row 0 col 0 <input type="text" name="R0C0" value="123"><br>
row 4 col 3 <input type="text" name="R4C3" value="456"><br>
etc...<br>
<input type="submit" value="View in Excel">
</form>

It should be possible to tie a HTML table to R1C1-references using DOM
in javascript, and then pass them as hidden form fields. The CGI then
goes like this:

#!/usr/bin/perl
use strict;
use CGI qw(:standard);
use Spreadsheet::WriteExcel;
print "Content-type: application/vnd.ms-excel\n";
print "Content-Disposition: attachment; filename=getexcel.xls\n\n";
my $workbook = Spreadsheet::WriteExcel->new("-");
my $worksheet = $workbook->addworksheet();
foreach (param()) {
my ($row, $col) = split /C/, $_;
$row=~s/^R//;
$worksheet->write($row, $col, param($_));
}
$workbook->close();

I made an example of this functionality at:
http://www.dotinternet.be/temp/test.htm

Info Spreadsheet-WriteExcel:
http://search.cpan.org/dist/Spreadsheet-WriteExcel/
Unfortunately, this is quite inaccessible for those unfamiliar with
Perl, but I mention it anyway so that the original poster might get a
clue how to pre-format his file.

If you're not a Perl guy, use your favourite language's Excel
routines.

--
Bart

Mar 27 '07 #4

This discussion thread is closed

Replies have been disabled for this discussion.