Copy/Paste from Excel to a Web Page

Copy/Paste from Excel to a web page

You don't lose the delimiters, the cells are separated by tabs (\t) and rows by newlines (\n) which might not be visible in the form. Try it yourself: copy content from Excel to Notepad, and you'll see your cells nicely lined up. It's easy then to split the fields by tabs and replace them with something else, this way you can build even a table from them. Here's a example using jQuery:

var data = $('input[name=excel_data]').val();
var rows = data.split("\n");

var table = $('<table />');

for(var y in rows) {
    var cells = rows[y].split("\t");
    var row = $('<tr />');
    for(var x in cells) {
        row.append('<td>'+cells[x]+'</td>');
    }
    table.append(row);
}

// Insert into DOM
$('#excel_table').html(table);

So in essence, this script creates an HTML table from pasted Excel data.

How to get actual value when copy from excel to web page

Clipboard formats

When you copy data from an application, it sends different pieces of data, on different formats, to the clipboard. As a result, you can do things as copying a file and then paste the actual file on a directory or just its path on a text document.

Likewise, an application is ready to understand a certain set of formats.

Some of these formats are standard. Others, are custom formats unique to an application. Maybe you want to read about clipboard formats on Windows and, mainly, the article provided by @Tim Williams.

You need to find a format that both Excel and your supported browsers can understand.

This other answer list some formats used by Excel. The Clipboard API and events W3C Working Draft enforces some mandatory data types.

In a slightly different scenario, I copied a range of cells from LibreOffice Calc to Firefox on my Linux machine. I can get a list of valid data formats for the current selection by using xclip:

xclip -selection clipboard -o -t TARGETS

As per @Tim Williams' link ~~you can use a tool called cbdump on Windows~~. It seems that it is not available any longer; the OP found a similar one called clipview.

It returns around 20 formats, half of them exclusive to OpenOffice (application/x-openoffice-...). Just two were supported by my browser (the rest return an empty string): text/plain and text/html.

Under text/plain format, the browser pastes the text inside the cells as you see it (1.26, not the real value 1.2562) and that makes sense. Under text/html it returns a very verbose output, including the cell value:

<!-- more html -->
<td height="34" align="right" sdval="1.2562">1.26</td>
<!-- more html -->

Set clipboard format in JavaScript

Once you know which is your target format, you can use ClipboardEvent.clipboardData:

to obtain the data to be pasted from the paste event handler, typically with a getData(format) call.

format is a DOMString representing the type of data to retrieve.

var textArea = document.querySelector('textarea');
textArea.addEventListener('paste', function(e) {  var format = document.querySelector('input[name="format"]:checked').value;  e.preventDefault();  var excel_data = (e.originalEvent || e).clipboardData.getData(format);  textArea.value = excel_data;});

<input id="plainText" type="radio" name="format" value="text/plain" checked><label for="plainText">plain text</label><input id="HTML" type="radio" name="format" value="text/html"><label for="html">HTML</label><p>Paste excel data here:</p><textarea></textarea>

Help interpret/implement "Copy/Paste from Excel to a web page" answer?

Given that you're referring to my original answer, perhaps I can give you some insight on how it should work.

I had to make few changes to my original answer (textarea instead of input), but the full HTML required for that code to work is as follows:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html>
<head>
    <script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.3.2/jquery.min.js"></script>
    <script type="text/javascript">
    $(function() {
        $('textarea[name=excel_data]').keyup(function() {
            var data = $(this).val();
            var rows = data.split("\n");
            var table = $('<table />');    
            for(var y in rows) {
                var cells = rows[y].split("\t");
                var row = $('<tr />');
                for(var x in cells) {
                    row.append('<td>'+cells[x]+'</td>');
                }
                table.append(row);
            }
            $('#excel_table').html(table);
        });
    });
    </script>
</head>
<body>
    <textarea name="excel_data" /></textarea>
    <div id="excel_table"></div>
</body>
</html>

That code will generate a HTML table of whatever excel data you paste into the textarea.

Note that this does not require any PHP to work as it is. If you want to save the data, you must create a form that submits the data to a PHP script which will do similar parsing on the data inside excel_data field.

Now, when you paste something in the excel_data field from excel, the resulting table should appear in the excel_table div.

Copy Data from Web page and paste into Excel

Try this:

It will enter the data into Range("C4:F4") for first dose and Range("G4:J4") for second dose.

Sub Newfunction()
    Const Url As String = "https://nims.nadra.gov.pk/nims/certificate"
    
    Dim LogData As Worksheet
    Set LogData = ThisWorkbook.Worksheets("Sheet1")
    
    Dim lastRow As Long
    lastRow = LogData.Range("A" & Rows.Count).End(xlUp).Row

Dim currentRow As Long

For currentRow = 3 to lastRow
    Dim IdNumber As String
    Dim openDate As Date
    IdNumber = LogData.Cells(currentRow, 1).Value
    openDate = LogData.Cells(currentRow, 2).Value
    
    Dim ie As Object
    Set ie = CreateObject("InternetExplorer.Application")

    With ie
        .navigate Url
        
        Do While .Busy Or .readyState <> 4
            DoEvents
        Loop
        
        .Visible = True

        Dim ieDoc As Object
        Set ieDoc = .document
    End With
        
    'Enter the CNIC
    Dim IDdata As Object
    Set IDdata = ieDoc.getElementById("checkEligibilityForm:cnic")
    If Not IDdata Is Nothing Then IDdata.Value = IdNumber
    Set IDdata = Nothing
    
    'Enter Date
    Dim puttdate As Object
    Set puttdate = ieDoc.getElementById("checkEligibilityForm:issueDate_input")
    If Not puttdate Is Nothing Then puttdate.Value = Format(openDate, "dd-mm-yyyy")
    Set puttdate = Nothing
    
    'Answering the captcha question
    'Split the innerText to string array to determine the equation
    
    Dim captchaQns As Object
    Set captchaQns = ieDoc.getElementsByClassName("submit__generated")(0)
    If Not captchaQns Is Nothing Then
        Dim mathEq() As String
        mathEq = Split(captchaQns.innerText, " ")
        Set captchaQns = Nothing
        
        'mathEq(0) = first number
        'mathEq(1) = math operator
        'mathEq(2) = second number
            
        If IsNumeric(mathEq(0)) Then
            Dim firstNum As Long
            firstNum = CLng(mathEq(0))
            
            If IsNumeric(mathEq(2)) Then
                Dim secondNum As Long
                secondNum = CLng(mathEq(2))
                
                'Select Case statement used here in case you encounter other form of math question (e.g. - X /), expand cases to cater for other scenario
                Dim mathAnswer As Long
                Select Case mathEq(1)
                    Case "+": mathAnswer = firstNum + secondNum
                End Select
            End If
        End If
        Erase mathEq
        
        If mathAnswer <> 0 Then
            'Enter the answer to the box
            Dim captchaAns As Object
            Set captchaAns = ieDoc.getElementsByClassName("submit__input")(0)
            If Not captchaAns Is Nothing Then captchaAns.Value = mathAnswer
            Set captchaAns = Nothing
            
            'Get the submit button element, remove "disabled" attribute to allow clicking
            Dim submitBtn As Object
            Set submitBtn = ieDoc.getElementsByName("checkEligibilityForm:j_idt79")(0)
            submitBtn.removeAttribute "disabled"
            submitBtn.Click
            Set submitBtn = Nothing
        End If
    End If
        
    With ie
        Do While .Busy Or .readyState <> 4
            DoEvents
        Loop
        
        Set ieDoc = .document
    End With
    
    Dim resultTbl As Object
    Set resultTbl = ieDoc.getElementsByTagName("table")
    If resultTbl.Length <> 0 Then
        Dim resultRows As Object
        Set resultRows = resultTbl(0).getElementsByTagName("tr")
        
        If resultRows.Length > 1 Then
            
            'Get the 2nd row (1st row is header so ignore)
            Dim firstDose As Object
            Set firstDose = resultRows(1).getElementsByTagName("td")
                                    
            LogData.Cells(currentRow, 3).Value = firstDose(0).innerText
            LogData.Cells(currentRow, 4).Value = firstDose(1).innerText
            LogData.Cells(currentRow, 5).Value = firstDose(2).innerText
            LogData.Cells(currentRow, 6).Value = firstDose(3).innerText
            
            Set firstDose = Nothing
            
            'If there are totals of 3 TR elements then there are 2nd dose
            If resultRows.Length = 3 Then
                Dim secondDose As Object
                Set secondDose = resultRows(2).getElementsByTagName("td")
                
                LogData.Cells(currentRow, 7).Value = secondDose(0).innerText
                LogData.Cells(currentRow, 8).Value = secondDose(1).innerText
                LogData.Cells(currentRow, 9).Value = secondDose(2).innerText
                LogData.Cells(currentRow, 10).Value = secondDose(3).innerText
                
                Set secondDose = Nothing
            End If
        'Else
            'Do something here if there is only a header row i.e. no dose (assumption)
        End If
        Set resultRows = Nothing
    End If
    
   
    Set resultTbl = Nothing
    Set ieDoc = Nothing
    
    ie.Quit     'Remove if you don't want to close IE
    Set ie = Nothing 'Remove if you don't want to close IE
Next currentRow

Set LogData = Nothing
End Sub

Copy/Paste from Excel to a Web Page