如何将含多数据的HTML转换为Excel并调整数据为横向排列？

It sounds like your current export is sticking to the original HTML table's row/column structure, leading to vertical data arrangement—but you need to flip that to a horizontal layout. Here are two practical, reliable solutions to fix this:

Method 1: Vanilla JavaScript (No External Libraries) #

This approach extracts your table data, transposes it (swaps rows and columns), then generates a temporary table to export. It’s lightweight and doesn’t require any extra tools.

Step-by-Step Code #

First, add an ID to your original table (adjust to match your actual table if needed):

<table id="data-table" border=0 cellspacing=0 cellpadding=0>
  <!-- Your existing table rows here -->
</table>
<button onclick="exportToExcel()">Export to Excel</button>

Then add this JavaScript function:

function exportToExcel() {
  // Grab the original table data
  const originalTable = document.getElementById('data-table');
  const rows = originalTable.querySelectorAll('tr');
  
  // Convert table rows into a 2D array
  const data = [];
  rows.forEach(row => {
    const rowData = [];
    row.querySelectorAll('td').forEach(cell => {
      rowData.push(cell.textContent.trim());
    });
    if (rowData.length > 0) data.push(rowData);
  });
  
  // Transpose the array (swap rows ↔ columns to get horizontal layout)
  const transposedData = data[0].map((_, colIndex) => data.map(row => row[colIndex]));
  
  // Create a temporary table with the rearranged data
  const tempTable = document.createElement('table');
  transposedData.forEach(row => {
    const tr = document.createElement('tr');
    row.forEach(cellText => {
      const td = document.createElement('td');
      td.textContent = cellText;
      tr.appendChild(td);
    });
    tempTable.appendChild(tr);
  });
  
  // Generate and trigger the Excel download
  const blob = new Blob([`<!DOCTYPE html><html><body>${tempTable.outerHTML}</body></html>`], {
    type: 'application/vnd.ms-excel'
  });
  const url = URL.createObjectURL(blob);
  const downloadLink = document.createElement('a');
  downloadLink.href = url;
  downloadLink.download = 'horizontal_data.xls';
  downloadLink.click();
  URL.revokeObjectURL(url);
}

Method 2: Using SheetJS (For Robust .xlsx Exports) #

If you need proper .xlsx formatting (instead of the basic .xls from the vanilla method), the SheetJS library gives you more control over the export.

Step-by-Step Code #

First, include the SheetJS library (you can download it locally or add it via a script tag in your page). Then use this function:

function exportWithSheetJS() {
  const originalTable = document.getElementById('data-table');
  const rows = originalTable.querySelectorAll('tr');
  
  // Extract table data into a 2D array
  const data = [];
  rows.forEach(row => {
    const rowData = [];
    row.querySelectorAll('td').forEach(cell => {
      rowData.push(cell.textContent.trim());
    });
    if (rowData.length > 0) data.push(rowData);
  });
  
  // Transpose the data to switch to horizontal layout
  const transposedData = data[0].map((_, colIndex) => data.map(row => row[colIndex]));
  
  // Create a workbook and worksheet with the rearranged data
  const workbook = XLSX.utils.book_new();
  const worksheet = XLSX.utils.aoa_to_sheet(transposedData);
  
  // Add the worksheet to the workbook and trigger download
  XLSX.utils.book_append_sheet(workbook, worksheet, 'Horizontal Data');
  XLSX.writeFile(workbook, 'horizontal_data.xlsx');
}

Key Notes #

• This method handles complex data types and produces standard .xlsx files that work seamlessly with Excel.
• Make sure the SheetJS library is loaded before calling this function.

Why This Works #

Excel exports from HTML tables mirror the table’s structure exactly. By transposing the data (swapping rows and columns) before export, we force Excel to display your data in the horizontal arrangement you need.

内容的提问来源于stack exchange，提问作者Kalai Shruthi