Excel to Html Table
Published: 2022-09-15 13:01:05

We print tickets for fabrics to be cut in our workplace. Such as customer name, fabric code, meters, company to be delivered columns that exists in the ticket. We have an excel work file and we copy these pieces of information and we paste it to an excel page that is simply designed and has titles and borders then we print it. I coded a web page to take the output of the ticket.

There are small things too except those like bots that I wrote with use my software knowledge.

When I think about how possible to transfer cells from excel to HTML table I pasted rows that I've copied from excel to the notepad then I saw that the cells separating from each other by \t character. So If I firstly fill an array with the text that I pasted to the textarea element according to \n characters in javascript then I would have got all rows and if I put column array into row array I would have uploaded the excel data to a two-dimensional array so I do whatever I want from now on. Holey!

Here is the piece of code that does it.

```
function excelToArray(text) {
    let data = [];
    const tab = '\\t';
    const yenisatir = '\\n';

    text.split(yenisatir).forEach(s => data.push(s.split(tab)));

    return data;
}
```
Thanks to js split function we easily partition the string.

Now I'm leaving the codes of the page here. You can try copying cells from an excel page.

```
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Excel'den HTML Tablosuna</title>
    <style>
        @import url('<https://fonts.googleapis.com/css2?family=Roboto&display=swap>');

        * {
            box-sizing: border-box;
            padding: 0;
            margin: 0;
        }

        body {
            font-family: 'Roboto', sans-serif;
        }

        #pasteBtn {
            outline: none;
            background: royalblue;
            padding: 1rem;
            font-weight: bold;
            font-size: 2rem;
            color: #fff;
            cursor: pointer;
            display: block;
            width: 100vw;
            height: 100vh;
            border: 0;
        }

        table {
            border-collapse: collapse;
            margin: 1rem;
        }

        td {
            padding: .5rem;
            border: 1px solid #fff;
        }

        tr:nth-child(odd) td {
            background: lightblue;
        }

        tr:hover td {
            background: lightcoral;
        }
    </style>
</head>
<body>
    <textarea></textarea>    
    <table></table>
    <button id="pasteBtn">YAPIŞTIRMAK İÇİN TIKLA</button>

    <script>
        const tableEL = document.querySelector('table');
        const textareaEL = document.querySelector('textarea');
        const pasteBtn = document.querySelector('#pasteBtn');

        initElements();

        pasteBtn.addEventListener('click', async () => {
            pasteBtn.style.display = 'none';
            textareaEL.value = await navigator.clipboard.readText();
            tableEL.innerHTML = excelToTable(textareaEL.value);
            tableEL.style.display = 'block';
        });

        function initElements() {
            tableEL.style.display = 'none';
            textareaEL.style.display = 'none';
        }

        function excelToArray(text) {
            let data = [];
            const tab = '\\t';
            const yenisatir = '\\n';

            text.split(yenisatir).forEach(s => data.push(s.split(tab)));

            return data;
        }

        function excelToTable(text) {
            const data = excelToArray(text);

            let tableHTML = '<tbody>';

            data.forEach(d => {
                tableHTML += '<tr>';
                d.forEach(c => tableHTML += `<td>${c}</td>`);
                tableHTML += '</tr>';
            });

            tableHTML += '</tbody>';

            return tableHTML;
        }
    </script>
</body>
</html>
```