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

1. 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>

2. 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>

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() {
  // 导出表格逻辑
}

3. 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');

(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' };
  }
}

(3) Save the Excel file:

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

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');
  });
}

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:

1. 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>

2. 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]]);
}

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

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

The complete HTML file sample code is as follows:

  
    
    
    
    
  
  
    

      

        
姓名
        
年龄
        
性别
      
      

        
张三
        
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>
  

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!