How to transfer Html to exel-HTML Tutorial-php.cn

How to transfer Html to exel

PHPz

Release： 2024-02-21 22:48:04

Original

636 people have browsed it

How to transfer Html to exel

How to transfer HTML to Excel requires specific code examples

Introduction:
In actual work, sometimes we need to export the data in the web page as Excel format. HTML is a common web markup language, while Excel is a commonly used spreadsheet software. The formats of the two are different, so conversion is required. This article will introduce how to use JavaScript and third-party libraries to realize the function of transferring HTML to Excel files, and give specific code examples.

1. Use JavaScript to transfer HTML to Excel

Create an HTML table
First, we need to create a table in HTML and put the data that needs to be exported into in the table. For example, the following is a table with three rows and three columns:

<table id="myTable">
  <tr>
    <th>姓名</th>
    <th>年龄</th>
    <th>性别</th>
  </tr>
  <tr>
    <td>张三</td>
    <td>25</td>
    <td>男</td>
  </tr>
  <tr>
    <td>李四</td>
    <td>30</td>
    <td>女</td>
  </tr>
</table>

Copy after login

Add export button and event listener
Add a button in HTML that triggers the export event when the button is clicked . The button can be added as follows:

<button onclick="exportToExcel()">导出Excel</button>

Copy after login

Next, we need to add an event listener for the button to trigger the export function. Use JavaScript to write the following code:

function exportToExcel() {
  // 导出表格逻辑
}

Copy after login

Implement the export logic and generate the Excel file
To implement the export logic, we can use JavaScript to convert the content in the HTML table into an Excel file. In the exported function, we need to perform the following steps:

(1) Create a new Excel workbook:

var wb = new ExcelJS.Workbook();
var ws = wb.addWorksheet('Sheet 1');

Copy after login

(2) Traverse all rows in the HTML table and column and fill the data into the Excel worksheet:

var rows = document.getElementById("myTable").rows;
for (var i = 0; i < rows.length; i++) {
  var cells = rows[i].cells;
  for (var j = 0; j < cells.length; j++) {
    ws.getCell(i + 1, j + 1).value = cells[j].innerText;
    // 根据需要设置单元格样式
    ws.getCell(i + 1, j + 1).alignment = { horizontal: 'left' };
  }
}

Copy after login

(3) Save the Excel file:

wb.xlsx.writeBuffer().then(function(buffer) {
  saveAs(new Blob([buffer], { type: 'application/octet-stream' }), 'data.xlsx');
});

Copy after login

Add these codes to the "exportToExcel" function, the complete code is as follows:

function exportToExcel() {
  var wb = new ExcelJS.Workbook();
  var ws = wb.addWorksheet('Sheet 1');

  var rows = document.getElementById("myTable").rows;
  for (var i = 0; i < rows.length; i++) {
    var cells = rows[i].cells;
    for (var j = 0; j < cells.length; j++) {
      ws.getCell(i + 1, j + 1).value = cells[j].innerText;
      ws.getCell(i + 1, j + 1).alignment = { horizontal: 'left' };
    }
  }

  wb.xlsx.writeBuffer().then(function(buffer) {
    saveAs(new Blob([buffer], { type: 'application/octet-stream' }), 'data.xlsx');
  });
}

Copy after login

2. Use a third-party library to transfer HTML to Excel
In addition to JavaScript native operations, we can also use third-party libraries to realize the function of transferring HTML to Excel, such as the "AlaSQL" library. The following is the specific code for using the AlaSQL library:

Introducing the AlaSQL library and ExcelJS library
First, we need to introduce the AlaSQL library and ExcelJS library scripts in HTML, which can be introduced in the following ways:

<script src="https://cdn.jsdelivr.net/npm/alasql"></script>
<script src="https://cdn.jsdelivr.net/npm/exceljs"></script>

Copy after login

Modify the export function
In the export function, we use the "alasql" method in the AlaSQL library to convert the HTML table to an Excel file. Modify the code of the exported function as follows:

function exportToExcel() {
  var tableData = [];
  var rows = document.getElementById("myTable").rows;
  for (var i = 0; i < rows.length; i++) {
    var rowData = [];
    var cells = rows[i].cells;
    for (var j = 0; j < cells.length; j++) {
      rowData.push(cells[j].innerText);
    }
    tableData.push(rowData);
  }

  var opts = {
    sheetid: 'Sheet 1',
    headers: true,
    skipHeader: true
  };

  var res = alasql('SELECT * INTO XLSX("data.xlsx",?) FROM ?', [opts, [tableData]]);
}

Copy after login

Add button and event listener
Similarly, add button and event listener in HTML:

<button onclick="exportToExcel()">导出Excel</button>

Copy after login

The complete HTML file sample code is as follows:



  
    
    HTML转存为Excel
    
    
  

  
    
      
        姓名
        年龄
        性别
      
      
        张三
        25
        男
      
      
        李四
        30
        女
      
    
  
    <button onclick="exportToExcel()">导出Excel</button>
    
    <script>
      function exportToExcel() {
        var tableData = [];
        var rows = document.getElementById("myTable").rows;
        for (var i = 0; i < rows.length; i++) {
          var rowData = [];
          var cells = rows[i].cells;
          for (var j = 0; j < cells.length; j++) {
            rowData.push(cells[j].innerText);
          }
          tableData.push(rowData);
        }

        var opts = {
          sheetid: 'Sheet 1',
          headers: true,
          skipHeader: true
        };

        var res = alasql('SELECT * INTO XLSX("data.xlsx",?) FROM ?', [opts, [tableData]]);
      }
    </script>

姓名	年龄	性别
张三	25	男
李四	30	女

Copy after login

Summary:
This article introduces how to use JavaScript and third-party libraries to transfer HTML to Excel files, and gives the specific code Example. Through these codes, we can easily transfer the data on the web page to Excel format to meet the export needs in actual work. Hope this article is helpful to everyone.

The above is the detailed content of How to transfer Html to exel. For more information, please follow other related articles on the PHP Chinese website!