SharePoint Foundation 2013：能否将Excel文件置于页面后台并实现前端搜索展示？

Absolutely, this requirement is totally doable! I’ve helped folks implement similar features before, so let’s break down a couple of practical approaches based on your setup:

This is the most scalable option, especially if your Excel file is large or you need to restrict access to the data. Here’s how to go about it:

• Step 1: Migrate Excel data to a database
  First, you’ll want to get the Excel data into a relational database (like MySQL, PostgreSQL, or even SQLite for smaller projects). You can use a script to automate this—here’s a quick Python example using pandas:

  import pandas as pd
  from sqlalchemy import create_engine
  
  # Create a database connection (replace with your DB credentials)
  engine = create_engine('mysql+pymysql://user:password@localhost/db_name')
  
  # Read Excel file and write to database
  df = pd.read_excel('your_backend_excel_file.xlsx')
  df.to_sql('excel_data_table', engine, if_exists='replace', index=False)
  

  You can even set up a cron job or scheduled task to refresh the database if the Excel file gets updated regularly.

• Step 2: Build a search API endpoint
  Next, create a simple API on your backend that accepts search parameters and returns matching results. For example, using Node.js/Express:

  const express = require('express');
  const mysql = require('mysql2');
  const app = express();
  
  // Database connection (update with your details)
  const db = mysql.createConnection({
    host: 'localhost',
    user: 'your_user',
    password: 'your_password',
    database: 'db_name'
  });
  
  // Search endpoint
  app.get('/api/search', (req, res) => {
    const searchTerm = req.query.term;
    // Adjust the query to match your table columns and search logic
    const query = 'SELECT * FROM excel_data_table WHERE your_column LIKE ?';
    db.query(query, [`%${searchTerm}%`], (err, results) => {
      if (err) {
        console.error(err);
        return res.status(500).json({ error: 'Failed to fetch data' });
      }
      res.json(results);
    });
  });
  
  app.listen(3000, () => console.log('Server running on port 3000'));
  

• Step 3: Frontend search interface
  Build a simple search form in your UI (an input field + submit button), then use fetch or axios to call your API when the user searches. Take the returned JSON data and render it on the page (with HTML tables, cards, etc.).

If your Excel file is small (a few thousand rows max) and you don’t want to mess with backend code, you can parse the file directly in the browser using a library like SheetJS (xlsx):

• Step 1: Host the Excel file
  Place your Excel file in your frontend’s static assets folder (so it’s accessible via a URL like /assets/your_excel_file.xlsx).

• Step 2: Parse the Excel file with SheetJS
  Install the library first (npm install xlsx), then add code to read and parse the file on page load:

  import XLSX from 'xlsx';
  
  let excelData = [];
  
  // Fetch and parse the Excel file when the page loads
  window.addEventListener('load', async () => {
    const response = await fetch('/assets/your_excel_file.xlsx');
    const arrayBuffer = await response.arrayBuffer();
    const workbook = XLSX.read(arrayBuffer, { type: 'array' });
    const firstSheet = workbook.Sheets[workbook.SheetNames[0]];
    // Convert sheet data to JSON
    excelData = XLSX.utils.sheet_to_json(firstSheet);
  });
  
  // Handle search input
  function handleSearch(event) {
    event.preventDefault();
    const searchTerm = document.getElementById('search-input').value.toLowerCase();
    // Filter data based on your search criteria
    const filteredResults = excelData.filter(item => {
      // Adjust this to match the columns you want to search
      return item['Your Column Name'].toLowerCase().includes(searchTerm);
    });
    // Render results to the page
    renderResults(filteredResults);
  }
  
  // Helper function to render results
  function renderResults(results) {
    const resultsContainer = document.getElementById('results-container');
    resultsContainer.innerHTML = '';
    if (results.length === 0) {
      resultsContainer.innerHTML = '<p>No matches found.</p>';
      return;
    }
    // Create a table to display results
    const table = document.createElement('table');
    // Add header row
    const headers = Object.keys(results[0]);
    const headerRow = document.createElement('tr');
    headers.forEach(header => {
      const th = document.createElement('th');
      th.textContent = header;
      headerRow.appendChild(th);
    });
    table.appendChild(headerRow);
    // Add data rows
    results.forEach(row => {
      const dataRow = document.createElement('tr');
      headers.forEach(header => {
        const td = document.createElement('td');
        td.textContent = row[header];
        dataRow.appendChild(td);
      });
      table.appendChild(dataRow);
    });
    resultsContainer.appendChild(table);
  }
  

• Step 3: Add the search UI
  Add an HTML form to your page that triggers the handleSearch function:

  <form onsubmit="handleSearch(event)">
    <input type="text" id="search-input" placeholder="Search...">
    <button type="submit">Search</button>
  </form>
  <div id="results-container"></div>
  

A few quick tips #

• If your Excel file is large (10k+ rows), stick with the backend approach—frontend parsing can slow down the browser.
• For date or numeric fields, make sure to handle data type conversions properly (both when importing to DB or parsing in frontend).
• If you need to update the Excel data regularly, the backend approach makes it easier to automate refreshes.

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