Python2.7生成Excel时ASCII编解码错误技术求助

Got it, let's break down how to fix this Unicode decode error you're facing when closing your Excel workbook. That 0x96 byte is an en dash (–), a non-ASCII character, and Python 2.7's default ASCII string handling is choking on it when writing to Excel. Here's what you can do:

1. Ensure Unicode strings from SQL Server #

First, fix the data at the source—make sure you're fetching Unicode strings from your SQL Server connection. If you're using pyodbc, add the unicode_results=True parameter to your connection string. This forces all string results to come back as Python 2's unicode type instead of ASCII-based str, avoiding encoding mismatches later:

import pyodbc
conn = pyodbc.connect(
    'DRIVER={SQL Server};SERVER=your_server;DATABASE=your_db;UID=your_user;PWD=your_pass',
    unicode_results=True
)

If you're using a different database driver, check its docs for similar Unicode-enabling settings.

2. Explicitly decode problematic strings #

If you can't adjust the database connection, manually convert any str type data to unicode using the correct encoding. The 0x96 byte maps to the en dash in Windows-1252 (a common encoding for SQL Server on Windows), so try decoding with that:

# For a single string
raw_str = "Some string with – character"
unicode_str = raw_str.decode('windows-1252')

# For a row from SQL Server
cursor.execute("SELECT * FROM your_table")
for row in cursor:
    processed_row = [col.decode('windows-1252') if isinstance(col, str) else col for col in row]
    # Write processed_row to Excel

If Windows-1252 doesn't work, try latin-1 (it safely maps every byte to a Unicode character, though it might not display the exact intended character if the original encoding is different).

3. Configure your Excel library for Unicode #

The error happens at workbook.close(), which means the library is trying to write non-ASCII characters without proper encoding settings. Adjust based on the library you're using:

• xlwt: When creating the workbook, specify encoding='utf-8' to support Unicode:

  import xlwt
  wb = xlwt.Workbook(encoding='utf-8')
  ws = wb.add_sheet('Sheet1')
  # Write unicode strings to the worksheet
  ws.write(0, 0, unicode_str)
  wb.close()  # No more error!
  

• openpyxl: In Python 2.7, ensure all strings written to the workbook are unicode type (not str). Openpyxl handles Unicode natively, but it will treat ASCII str as-is, so converting to unicode first avoids decoding issues.

4. (Last resort) Adjust Python's default encoding #

This is not recommended for most cases (it can break other libraries), but if you need a quick fix, override Python 2's default ASCII encoding to UTF-8:

import sys
reload(sys)
sys.setdefaultencoding('utf-8')

Use this only if the above methods don't work, as it changes global behavior.

Quick Troubleshooting Tip #

To find exactly which data is causing the error, add logging to print the type and content of each field before writing to Excel. This will help you confirm if the issue is with a specific string and its encoding.

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