Export HTML table to Excel with formatting

Question

I am creating a web page where the data is uploaded to an HTML table. After some calculation, an output HTML table gets generated. The output HTML table must be dumped in Excel (.xlsx) format. I'm using the SheetJS library for uploading and downloading purpose.

I have tried the following code which exports the data to excel without any formatting.

HTML table markup

Output HTML after calculation:

<table border="1" id="tempTable">
  <thead>
    <tr>
      <th style="border:1px solid #000000;border-collapse: collapse">Column1</th>
      <th style="border:1px solid #000000;border-collapse: collapse">Column2</th>
      <th style="border:1px solid #000000;border-collapse: collapse">Column3</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td style="border:1px solid #000000;border-collapse: collapse">R1C1</td>
      <td style="border:1px solid #000000;border-collapse: collapse">R1C2</td>
      <td style="border:1px solid #000000;border-collapse: collapse">R1C3</td>
    </tr>
    <tr>
      <td style="border:1px solid #000000;border-collapse: collapse">R2C1</td>
      <td style="border:1px solid #000000;border-collapse: collapse">R2C2</td>
      <td style="border:1px solid #000000;border-collapse: collapse">R2C3</td>
    </tr>
  </tbody>
</table>

JavaScript code

Function that helps me to export data:

function ExportAllData_HTML() {
  var wb = { SheetNames: [], Sheets: {} };
  var ws9 = XLSX.utils.table_to_sheet(document.getElementById('tempTable'), { raw: true });
  wb.SheetNames.push('Temp Table');
  wb.Sheets["Temp Table"] = ws9;
  XLSX.writeFile(wb, "myTemp.xlsx", { cellStyles: true });
}

I've searched and found out that only inline CSS works while exporting the data, but that too doesn't seem to be working.

I'm not able to export data to Excel with CSS that has been applied.

Is there any other way that I can export data with CSS that has been applied?

probably you should try this stackoverflow.com/a/41608958/7499069 — tuhin47
– tuhin47, Commented Jul 28, 2019 at 11:30

Code.king · Accepted Answer · 2022-01-29 12:41:39Z

0

if you just need to export the html table you see , I think the following link do what you want : Javascript to export html table to Excel

answered Jan 29, 2022 at 12:41

Code.king

471 silver badge4 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

M Dileepkumar · Accepted Answer · 2024-07-16 16:25:45Z

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Export HTML Table to Excel</title>
    <style>
        table {
            border-collapse: collapse;
            width: 100%;
        }
        th, td {
            border: 1px solid black;
            padding: 8px;
            text-align: left;
        }
        th {
            background-color: #f2f2f2; /* Light gray */
        }
        tr:nth-child(even) {
            background-color: #f9f9f9; /* Lighter gray */
        }
    </style>
</head>
<body>
    <table id="myTable">
        <thead>
            <tr>
                <th>Header 1</th>
                <th>Header 2</th>
                <th>Header 3</th>
            </tr>
        </thead>
        <tbody>
            <tr>
                <td>Row 1, Cell 1</td>
                <td>Row 1, Cell 2</td>
                <td>Row 1, Cell 3</td>
            </tr>
            <tr>
                <td>Row 2, Cell 1</td>
                <td>Row 2, Cell 2</td>
                <td>Row 2, Cell 3</td>
            </tr>
        </tbody>
    </table>
    <button onclick="exportTableToExcel('myTable', 'tableData')">Export Table to Excel</button>

    <script src="https://cdnjs.cloudflare.com/ajax/libs/exceljs/4.3.0/exceljs.min.js"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/FileSaver.js/2.0.5/FileSaver.min.js"></script>
    <script>
        async function exportTableToExcel(tableID, filename = '') {
            const table = document.getElementById(tableID);
            const workbook = new ExcelJS.Workbook();
            const worksheet = workbook.addWorksheet('Sheet1');
            
            // Apply table styles
            const styles = {
                header: { fill: { type: 'pattern', pattern: 'solid', fgColor: { argb: 'FFF2F2F2' } } },
                even: { fill: { type: 'pattern', pattern: 'solid', fgColor: { argb: 'FFF9F9F9' } } },
                border: {
                    top: { style: 'thin' },
                    left: { style: 'thin' },
                    bottom: { style: 'thin' },
                    right: { style: 'thin' }
                }
            };

            // Add table rows and cells
            for (let i = 0; i < table.rows.length; i++) {
                const row = table.rows[i];
                const excelRow = worksheet.addRow(Array.from(row.cells).map(cell => cell.textContent));

                // Apply styles
                if (i === 0) {
                    excelRow.eachCell(cell => {
                        cell.style = { ...styles.header, border: styles.border };
                    });
                } else if (i % 2 === 0) {
                    excelRow.eachCell(cell => {
                        cell.style = { ...styles.even, border: styles.border };
                    });
                } else {
                    excelRow.eachCell(cell => {
                        cell.style = { border: styles.border };
                    });
                }
            }

            // Specify file name
            filename = filename ? filename + '.xlsx' : 'excel_data.xlsx';

            // Write to buffer and save file
            const buffer = await workbook.xlsx.writeBuffer();
            saveAs(new Blob([buffer], { type: 'application/octet-stream' }), filename);
        }
    </script>
</body>
</html>

I hope this is useful..

Collectives™ on Stack Overflow

Export HTML table to Excel with formatting

HTML table markup

JavaScript code

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

HTML table markup

JavaScript code

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related