C#程序导出Excel时整数列存为文本问题求助（已尝试CAST）

Hey there! I’ve dealt with this exact frustrating issue before when exporting SQL results to Excel via C#—let’s walk through some fixes that should get your integer column displaying correctly.

1. Double-Check Your CAST in the Stored Procedure #

First, make sure your CAST is actually producing an integer type that C# can recognize. Sometimes even if you use CAST(col AS INT), edge cases like null values or implicit conversions might throw things off.

Verify the data type in your C# dataset: when you retrieve the stored procedure results into a DataTable, check yourDataTable.Columns["YourIntegerColumn"].DataType—it should be typeof(int). If it’s still string, your CAST might not be working as expected, or there’s a hidden issue with the source data (like trailing spaces in the original column).

Example of a solid CAST in your SQL:

CAST(COALESCE(your_source_column, 0) AS INT) AS IntegerColumn

Using COALESCE ensures nulls are converted to a valid integer, which helps avoid unexpected type shifts.

2. Force Excel Cell Formatting During Export #

Even if your data is correctly typed in C#, Excel might still auto-detect it as text. The most reliable fix is to explicitly set the column’s number format in your export code.

Example with EPPlus (a popular Excel library): #

using OfficeOpenXml;

// Assume you've already loaded your data into the worksheet
ExcelWorksheet worksheet = package.Workbook.Worksheets["YourReport"];

// Target the column (replace "B:B" with your actual column range)
var integerColumnRange = worksheet.Cells["B:B"];
// Set format to plain integer (no decimals)
integerColumnRange.Style.Numberformat.Format = "0";
// Alternatively, use "General" if you prefer Excel's default number handling
// integerColumnRange.Style.Numberformat.Format = "General";

Example with NPOI: #

using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;

// Get your sheet and create a cell style for integers
XSSFWorkbook workbook = new XSSFWorkbook();
ISheet sheet = workbook.CreateSheet("YourReport");
ICellStyle integerStyle = workbook.CreateCellStyle();
integerStyle.DataFormat = workbook.CreateDataFormat().GetFormat("0");

// Apply the style to every cell in your integer column
for (int rowIndex = 1; rowIndex <= sheet.LastRowNum; rowIndex++)
{
    IRow row = sheet.GetRow(rowIndex);
    if (row != null)
    {
        ICell cell = row.GetCell(1); // Replace 1 with your column index (0-based)
        if (cell != null)
        {
            cell.CellStyle = integerStyle;
        }
    }
}

3. Clean Up Data Before Export #

Sometimes hidden whitespace or invalid characters in your source data can trick Excel into treating integers as text. Add a quick cleanup step in C# to ensure the values are pure integers:

foreach (DataRow row in yourDataTable.Rows)
{
    object rawValue = row["YourIntegerColumn"];
    if (rawValue != DBNull.Value)
    {
        string cleanedValue = rawValue.ToString().Trim();
        if (int.TryParse(cleanedValue, out int intValue))
        {
            row["YourIntegerColumn"] = intValue;
        }
        else
        {
            // Handle invalid values (set to 0 or mark as error)
            row["YourIntegerColumn"] = 0;
        }
    }
}

4. Adjust OleDb Connection String (If Using OleDb Export) #

If you’re using OleDb to write directly to Excel, your connection string might be forcing mixed-type detection. Set IMEX=0 to prioritize writing data types instead of reading them:

string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\YourExportFile.xlsx;Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=0"";";

Note: IMEX=0 is for write operations—if you’re also reading from Excel, you might need to adjust this, but for exports, it helps enforce data types.

Start with steps 1 and 2 first—those usually resolve the issue. Let me know if you’re using a specific Excel library or have more details about your export code, and I can refine this further!

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