open an excel file and paste values

Discussion in 'Javascript' started by toffee, Mar 26, 2007.

  1. toffee

    toffee Guest

    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
    toffee, Mar 26, 2007
    #1
    1. Advertising

  2. toffee

    ASM Guest

    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
    ASM, Mar 27, 2007
    #2
    1. Advertising

  3. toffee

    shimmyshack Guest

    On Mar 27, 12:08 am, ASM <>
    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>
    shimmyshack, Mar 27, 2007
    #3
  4. 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
    Bart Van der Donck, Mar 27, 2007
    #4
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Replies:
    1
    Views:
    731
  2. Humvee
    Replies:
    1
    Views:
    384
    Mr. Clean
    Jul 22, 2003
  3. zxo102
    Replies:
    3
    Views:
    3,246
    Stuart Corrie
    Aug 9, 2005
  4. viza
    Replies:
    0
    Views:
    655
  5. Luigi
    Replies:
    5
    Views:
    2,791
    Registered User
    May 27, 2010
Loading...

Share This Page