实现HTML表格按日期分组导出至Excel多工作表功能

Alright, let's build this export button step by step. We'll use SheetJS (the xlsx library) for client-side Excel generation—it's perfect for this task since we don't need any server-side code. Here's how to make it work:

Step 1: HTML Structure #

First, set up your table and export button:

<table id="dataTable">
  <thead>
    <tr>
      <th>姓名</th>
      <th>日期</th>
    </tr>
  </thead>
  <tbody>
    <tr><td>Hamza</td><td>2018/04/25</td></tr>
    <tr><td>Alice</td><td>2018/04/26</td></tr>
    <tr><td>Bob</td><td>2018/04/27</td></tr>
    <tr><td>Charlie</td><td>2018/04/28</td></tr>
    <tr><td>Diana</td><td>2018/04/29</td></tr>
    <tr><td>Eve</td><td>2018/04/30</td></tr>
    <tr><td>John</td><td>2018/06/05</td></tr>
    <!-- Add more rows as needed -->
  </tbody>
</table>

<button id="exportBtn">导出到Excel</button>

Step 2: Include the SheetJS Library #

Add this script tag to your HTML (it loads the library directly, no external links to click):

<script src="https://cdn.jsdelivr.net/npm/xlsx@0.18.5/dist/xlsx.full.min.js"></script>

Step 3: JavaScript Export Logic #

Here's the code that handles grouping data by date range and generating the Excel file:

document.getElementById('exportBtn').addEventListener('click', function() {
  // Define your target date range for the first week
  const firstWeekStart = new Date('2018-04-25');
  const firstWeekEnd = new Date('2018-04-30');

  // Grab table data
  const table = document.getElementById('dataTable');
  const rows = table.querySelectorAll('tbody tr');
  
  // Initialize two data groups (include headers for each worksheet)
  const firstWeekData = [['姓名', '日期']];
  const otherWeeksData = [['姓名', '日期']];

  // Helper function to check if a date falls in the target range
  function isDateInRange(dateStr, start, end) {
    const date = new Date(dateStr.replace(/\//g, '-'));
    // Normalize time to 00:00:00 to avoid time-based comparison errors
    date.setHours(0, 0, 0, 0);
    start.setHours(0, 0, 0, 0);
    end.setHours(0, 0, 0, 0);
    return date >= start && date <= end;
  }

  // Loop through rows and group data
  rows.forEach(row => {
    const name = row.cells[0].textContent;
    const date = row.cells[1].textContent;
    
    if (isDateInRange(date, firstWeekStart, firstWeekEnd)) {
      firstWeekData.push([name, date]);
    } else {
      otherWeeksData.push([name, date]);
    }
  });

  // Create a new Excel workbook
  const wb = XLSX.utils.book_new();

  // Add worksheets for each data group
  const firstWeekSheet = XLSX.utils.aoa_to_sheet(firstWeekData);
  XLSX.utils.book_append_sheet(wb, firstWeekSheet, '第一周数据');

  const otherWeeksSheet = XLSX.utils.aoa_to_sheet(otherWeeksData);
  XLSX.utils.book_append_sheet(wb, otherWeeksSheet, '其他周数据');

  // Trigger the download
  XLSX.writeFile(wb, '分组数据导出.xlsx');
});

Quick Breakdown of Key Parts #

• Date Check: The isDateInRange function fixes date string formatting and normalizes time to make sure we're only comparing dates, not times.
• Data Grouping: We loop through every table row, check which group it belongs to, and add it to the correct array (each array includes its own header for the worksheet).
• Excel Generation: SheetJS converts our array data into worksheets, adds them to a workbook, and generates a downloadable Excel file.

Click the button, and you'll get an Excel file with two worksheets—one for the 2018/04/25-2018/04/30 range, and another for all other dates.

内容的提问来源于stack exchange，提问作者Hza Developer