导出Date、Title、Description至Excel时特殊字符转HTML编码问题

Hey there! I’ve dealt with this exact headache before—those HTML entities like ‘ and & turning your nice Description text into gibberish in Excel. Let’s break down the best fixes depending on how you’re handling the export:

1. Decode before exporting (code-based solutions) #

If you’re using a programming language to handle the export (C#, Python, etc.), leverage built-in functions to turn those entities back into regular characters before writing to Excel:

• C#: Use System.Net.WebUtility.HtmlDecode() (no extra dependencies needed) or System.Web.HttpUtility.HtmlDecode():

  // Assuming originalHtmlDesc is your stored HTML text
  string decodedDescription = System.Net.WebUtility.HtmlDecode(originalHtmlDesc);
  // Now write decodedDescription to your Excel file
  

• Python: Use the standard html module’s unescape() method:

  import html
  decoded_desc = html.unescape(original_html_desc)
  # Export decoded_desc to Excel using your library of choice (pandas, openpyxl, etc.)
  

This method is clean and scalable, especially if you’re automating exports.

2. Decode directly in your SQL Server query #

If you’re exporting straight from SSMS or using SQL to generate the export data, create a simple user-defined function (UDF) to decode common entities:

CREATE FUNCTION dbo.udf_HtmlDecode(@HtmlText NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
AS
BEGIN
    -- Replace the most common entities first
    SET @HtmlText = REPLACE(@HtmlText, '‘', '‘')
    SET @HtmlText = REPLACE(@HtmlText, '’', '’')
    SET @HtmlText = REPLACE(@HtmlText, '&', '&')
    SET @HtmlText = REPLACE(@HtmlText, '"', '"')
    SET @HtmlText = REPLACE(@HtmlText, '&lt;', '<')
    SET @HtmlText = REPLACE(@HtmlText, '&gt;', '>')
    -- Add more REPLACE lines for other entities you encounter
    RETURN @HtmlText
END

Then use it in your export query:

SELECT 
    Date,
    Title,
    dbo.udf_HtmlDecode(Description) AS Description
FROM YourTableName

This way, the data coming out of SQL is already decoded, so Excel gets the correct text.

3. Fix after export in Excel #

If you already have the exported file and need a quick fix, use nested SUBSTITUTE functions in a helper column:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, "‘", "‘"), "’", "’"), "&", "&"), """, """")

Replace A2 with the cell containing your encoded Description text. You can add more SUBSTITUTE layers for any other entities you need to resolve.

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