C#使用Office Interop读取Excel单元格数据丢失的解决方法

你的问题出在使用cell.Value?.ToString()读取单元格内容上——Value属性获取的是Excel单元格的内部存储值，如果单元格格式被Excel自动识别（比如误判内容格式），会导致文本被截断。要获取单元格显示的完整原始文本，应该改用Range.Text属性。

修改后的关键代码 #

将读取单元格内容的行：

string value = cell.Value?.ToString();

替换为：

string value = cell.Text;

完整修改后的代码 #

using System;
using System.Collections.Generic;
using Microsoft.Office.Interop.Excel;

namespace PracFileCreater
{
    class Program
    {
        static void Main(string[] args)
        {
            string filePath = "test.xlsx";

            int sheetNum = 1;
            string columnName = "E";
            int startRow = 6;
            int endRow = 39;

            Application app = new Application();
            List<string> pracNameList = ReadWorksheetColumn(app, filePath, sheetNum, columnName, startRow, endRow);
            foreach (string name in pracNameList)
            {
                Console.WriteLine(name);
            }
            app.Quit();
        }

        static List<string> ReadWorksheetColumn(Application app, string path, int sheetNum, string columnName, int startRow, int endRow)
        {
            List<string> pracNameList = new List<string>();

            Workbook workbook = app.Workbooks.Open(path);
            Worksheet worksheet = workbook.Sheets[sheetNum];
            Range pracNameRange = worksheet.Range[columnName + startRow.ToString() + ":" + columnName + endRow.ToString()];
            
            foreach (Range cell in pracNameRange.Cells)
            {
                string value = cell.Text;
                if (!string.IsNullOrEmpty(value))
                {
                    pracNameList.Add(value);
                }
            }

            workbook.Close(false);

            return pracNameList;
        }
    }
}

修改后执行代码，就能正确读取到"演習4-3f"和"演習4-3w"，不会丢失末尾字符。

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