Node.js如何识别Excel中的上标与下标并转为HTML标签？

Hey there! I’ve wrestled with this exact problem before—grabbing superscript and subscript formatting from Excel can be frustrating because many popular libraries don’t expose that character-level detail by default. Since you’ve already tried xlsx, excel parser, and SheetJS, here are a few alternative approaches that should work:

1. Use Apache POI (Java/Scala) #

Apache POI is hands down one of the most powerful libraries for deep Excel manipulation. It lets you access character-level formatting, including superscript/subscript, by inspecting individual font properties.

Here’s a quick snippet to get you started:

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;

// Assuming you have a Cell object named 'cell'
RichTextString richText = cell.getRichStringCellValue();
StringBuilder result = new StringBuilder();

for (int i = 0; i < richText.numFormattingRuns(); i++) {
    int startIdx = richText.getIndexOfFormattingRun(i);
    int endIdx = richText.getIndexOfFormattingRun(i+1);
    String textSegment = richText.getString().substring(startIdx, endIdx);
    Font font = richText.getFontOfFormattingRun(i);

    if (font.getTypeOffset() == Font.SUPER_SCRIPT) {
        result.append("<sup>").append(textSegment).append("</sup>");
    } else if (font.getTypeOffset() == Font.SUB_SCRIPT) {
        result.append("<sub>").append(textSegment).append("</sub>");
    } else {
        result.append(textSegment);
    }
}

// result now contains your text with <sup>/<sub> tags

2. Leverage openpyxl (Python) #

If you’re working in Python, openpyxl has solid support for rich text cells. You can iterate through each text "run" in a cell and check the font’s superscript/subscript flags.

Example code:

from openpyxl import load_workbook

wb = load_workbook("your_file.xlsx")
ws = wb.active

for row in ws.iter_rows():
    for cell in row:
        if cell.value and hasattr(cell.value, 'runs'):
            processed_text = []
            for run in cell.value.runs:
                text = run.text
                font = run.font
                if font.superscript:
                    processed_text.append(f"<sup>{text}</sup>")
                elif font.subscript:
                    processed_text.append(f"<sub>{text}</sub>")
                else:
                    processed_text.append(text)
            cell_processed = ''.join(processed_text)
            print(cell_processed)

3. Parse Excel’s Underlying XML Directly #

XLSX files are just ZIP archives with XML files inside. You can manually extract and parse the relevant XML (like xl/sharedStrings.xml or worksheet XML files) to find superscript/subscript markers.

The key tags to look for are <vertAlign val="superscript"/> and <vertAlign val="subscript"/> under the <rPr> (run properties) elements. Here’s a rough Python example using zipfile and xml.etree:

import zipfile
import xml.etree.ElementTree as ET

with zipfile.ZipFile("your_file.xlsx", 'r') as zf:
    with zf.open('xl/sharedStrings.xml') as f:
        tree = ET.parse(f)
        root = tree.getroot()
        ns = {'ss': 'http://schemas.openxmlformats.org/spreadsheetml/2006/main'}

        for si in root.findall('ss:si', ns):
            processed = []
            for r in si.findall('ss:r', ns):
                text = r.find('ss:t', ns).text
                rPr = r.find('ss:rPr', ns)
                if rPr is not None:
                    vertAlign = rPr.find('ss:vertAlign', ns)
                    if vertAlign is not None:
                        val = vertAlign.get('val')
                        if val == 'superscript':
                            processed.append(f"<sup>{text}</sup>")
                        elif val == 'subscript':
                            processed.append(f"<sub>{text}</sub>")
                        else:
                            processed.append(text)
                    else:
                        processed.append(text)
                else:
                    processed.append(text)
            print(''.join(processed))

4. Preprocess with Excel VBA #

If you can manually open the Excel file first, a quick VBA macro can convert all superscript/subscript text to your desired tag format directly in the spreadsheet, which you can then export as CSV/Text for easy parsing later.

Here’s a sample macro:

Sub ConvertSubSuperscriptToTags()
    Dim cell As Range
    Dim i As Integer
    Dim text As String
    Dim newText As String
    
    For Each cell In Selection
        newText = ""
        text = cell.Value
        For i = 1 To Len(text)
            With cell.Characters(i, 1)
                If .Font.Superscript Then
                    newText = newText & "<sup>" & .Text & "</sup>"
                ElseIf .Font.Subscript Then
                    newText = newText & "<sub>" & .Text & "</sub>"
                Else
                    newText = newText & .Text
                End If
            End With
        Next i
        cell.Offset(0, 1).Value = newText ' Write result to adjacent cell
    Next cell
End Sub

Quick Notes #

• .xls (binary) files might need different handling than .xlsx (XML-based) files—most libraries handle both, but double-check documentation.
• For large files, character-level parsing can be slow—prioritize libraries optimized for performance (like Apache POI for Java).
• Some cells might use "fake" superscript/subscript (e.g., manually raised text instead of true formatting)—these are harder to detect, but the methods above will catch true formatted text.

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