读取多语言多Sheet Excel并生成指定哈希结构的技术问题

Let's fix this step by step. Your goal is to turn each row of your Excel data into that specific nested object structure, and I can see where your current code is missing the connection between headers, row values, and your target format.

First, Let's Spot the Key Issues in Your Current Code #

• You’re re-declaring variables like headers and data inside the sheet loop, which stops you from accumulating data across rows or sheets.
• The logic to link row values to their corresponding header fields isn’t structured to build each entry in your target array.
• You aren’t tracking which column letter maps to each header (critical for generating the cellId property).

Fixed Code with Detailed Explanations #

Here’s the adjusted code that will generate exactly the array structure you want:

$("#btn").on("change", function(e) {
    e.preventDefault();
    var files = e.target.files;
    
    for (var i = 0, f = files[i]; i != files.length; ++i) {
        var reader = new FileReader();
        reader.onload = function(e) {
            var data = e.target.result;
            var workbook = XLSX.read(data, { 
                type: "buffer", 
                blankRows: true, 
                defval: ' ' 
            });
            var sheet_name_list = workbook.SheetNames;
            var theExcelDataArray = []; // Holds all entries across every sheet

            sheet_name_list.forEach(function(sheetName) {
                var worksheet = workbook.Sheets[sheetName];
                var headerMap = {}; // Maps header names to column letters (e.g., "Name" => "A")
                var maxRow = 0;

                // First pass: Build header map and find the last row with data
                for (var z in worksheet) {
                    if (z[0] === '!') continue; // Skip worksheet metadata (like !ref)
                    var col = z.substring(0, 1);
                    var row = parseInt(z.substring(1));
                    var value = worksheet[z].v;

                    if (row === 1) {
                        // Store each header and its corresponding column
                        headerMap[value] = col;
                    }
                    if (row > maxRow) maxRow = row; // Track the last row with content
                }

                // Second pass: Process each data row (starts at row 2, since row 1 is headers)
                for (var row = 2; row <= maxRow; row++) {
                    var entry = {};
                    // Loop through each header to build the entry object
                    Object.keys(headerMap).forEach(function(header) {
                        var col = headerMap[header];
                        var cellAddress = col + row;
                        var cellValue = worksheet[cellAddress] ? worksheet[cellAddress].v : '';

                        // Convert header to lowercase to match your target keys (e.g., "Name" => "name")
                        var key = header.toLowerCase();
                        entry[key] = {
                            default: cellValue,
                            cellId: cellAddress // Uses actual Excel cell address (e.g., "A2")
                        };
                    });
                    theExcelDataArray.push(entry);
                }
            });

            // Your target array is ready!
            console.log("Final Result:", theExcelDataArray);
        };
        reader.readAsArrayBuffer(f);
    }
});

Key Improvements Breakdown #

1. Header Mapping: We first scan the worksheet to create a headerMap that links each column name (like "Name") to its column letter (like "A"). This makes it easy to find the right cell for each field in every row.
2. Row-by-Row Processing: After building the header map, we loop through each data row (starting at row 2). For each row, we create a new entry object, populate each field with the cell’s value and address, then add it to the final array.
3. Variable Scoping: We moved theExcelDataArray outside the sheet loop so it accumulates entries from all sheets. We also stopped re-declaring variables inside loops, which was causing data loss.
4. Customizable CellId: The cellId uses the actual Excel cell address (e.g., "A2" for the first data row’s Name field). If you want to match your sample’s numbering (like "B1" for the first entry), adjust the cellId line to:

   cellId: col + (row - 1)
   

   This shifts the row number down by 1, so row 2 becomes 1, row 3 becomes 2, etc.

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