C#中如何实现DataGridView数据的保存与加载？

错误原因分析 #

• xlrange类型错误：UsedRange返回的是Range对象，你错误地将其强制转换为_Worksheet，触发E_NOINTERFACE错误。
• 单元格索引错误：加载时从第2列开始读取（xlrow,2），但导出时是从第1列写入的，导致列索引越界，触发DISP_E_BADINDEX错误。
• 硬编码列数：代码里固定读取到第7列，若DataGridView列数变化会直接出错。

修复后的Interop加载代码 #

修正上述问题，同时匹配你的导出格式（第一行是标题，从第二行加载数据）：

using (OpenFileDialog ofd = new OpenFileDialog())
{
    ofd.Filter = "Excel Files Only | *.xlsx; *.xls";
    ofd.Title = "Choose the File";
    if (ofd.ShowDialog() == DialogResult.OK)
        FileName_LBL.Text = ofd.FileName;
}

Microsoft.Office.Interop.Excel.Application xlapp = null;
Microsoft.Office.Interop.Excel.Workbook xlworkbook = null;
Microsoft.Office.Interop.Excel.Worksheet xlworksheet = null;
Microsoft.Office.Interop.Excel.Range xlrange = null;

try
{
    xlapp = new Microsoft.Office.Interop.Excel.Application();
    xlapp.Visible = false; // 后台操作避免弹窗干扰
    xlworkbook = xlapp.Workbooks.Open(FileName_LBL.Text);
    
    // 优先按导出的工作表名称查找，找不到则用第一个工作表
    xlworksheet = xlworkbook.Worksheets["Exported from Journal Pro"] as Microsoft.Office.Interop.Excel.Worksheet;
    if (xlworksheet == null)
        xlworksheet = xlworkbook.Worksheets[1] as Microsoft.Office.Interop.Excel.Worksheet;

    xlrange = xlworksheet.UsedRange;
    int columnCount = xlrange.Columns.Count;
    int rowCount = xlrange.Rows.Count;

    // 清空DataGridView现有数据
    dataGridView1.Rows.Clear();
    dataGridView1.Columns.Clear();

    // 从Excel第一行读取并设置列标题
    for (int col = 1; col <= columnCount; col++)
    {
        string headerText = xlrange.Cells[1, col].Value?.ToString() ?? "";
        dataGridView1.Columns.Add($"Column{col}", headerText);
    }

    // 从Excel第二行开始加载数据
    for (int row = 2; row <= rowCount; row++)
    {
        string[] rowData = new string[columnCount];
        for (int col = 1; col <= columnCount; col++)
        {
            rowData[col - 1] = xlrange.Cells[row, col].Value?.ToString() ?? "";
        }
        dataGridView1.Rows.Add(rowData);
    }
}
catch (Exception ex)
{
    MessageBox.Show($"加载失败：{ex.Message}");
}
finally
{
    // 释放Excel COM对象，避免进程残留
    if (xlrange != null) System.Runtime.InteropServices.Marshal.ReleaseComObject(xlrange);
    if (xlworksheet != null) System.Runtime.InteropServices.Marshal.ReleaseComObject(xlworksheet);
    if (xlworkbook != null)
    {
        xlworkbook.Close(false);
        System.Runtime.InteropServices.Marshal.ReleaseComObject(xlworkbook);
    }
    if (xlapp != null)
    {
        xlapp.Quit();
        System.Runtime.InteropServices.Marshal.ReleaseComObject(xlapp);
    }
}

非Excel的替代方案（CSV格式） #

如果不想依赖Office Interop，CSV是更轻量、稳定的选择，以下是匹配原导出格式的保存和加载代码：

CSV保存代码 #

private void SaveToCsv()
{
    using (SaveFileDialog sfd = new SaveFileDialog())
    {
        sfd.Filter = "CSV Files (*.csv)|*.csv";
        sfd.Title = "Save as CSV";
        if (sfd.ShowDialog() != DialogResult.OK) return;

        using (StreamWriter writer = new StreamWriter(sfd.FileName))
        {
            // 写入列标题（处理含引号的特殊字符）
            string[] headers = dataGridView1.Columns.Cast<DataGridViewColumn>()
                .Select(col => $"\"{col.HeaderText.Replace("\"", "\"\"")}\"")
                .ToArray();
            writer.WriteLine(string.Join(",", headers));

            // 写入数据行，跳过新增行
            foreach (DataGridViewRow row in dataGridView1.Rows)
            {
                if (row.IsNewRow) continue;
                string[] rowData = row.Cells.Cast<DataGridViewCell>()
                    .Select(cell => 
                    {
                        string value = cell.Value?.ToString() ?? "";
                        return $"\"{value.Replace("\"", "\"\"")}\"";
                    })
                    .ToArray();
                writer.WriteLine(string.Join(",", rowData));
            }
        }
        MessageBox.Show("保存成功");
    }
}

CSV加载代码 #

private void LoadFromCsv()
{
    using (OpenFileDialog ofd = new OpenFileDialog())
    {
        ofd.Filter = "CSV Files (*.csv)|*.csv";
        ofd.Title = "Choose CSV File";
        if (ofd.ShowDialog() != DialogResult.OK) return;

        dataGridView1.Rows.Clear();
        dataGridView1.Columns.Clear();

        using (StreamReader reader = new StreamReader(ofd.FileName))
        {
            // 读取列标题
            string headerLine = reader.ReadLine();
            if (!string.IsNullOrEmpty(headerLine))
            {
                string[] headers = ParseCsvLine(headerLine);
                foreach (string header in headers)
                {
                    dataGridView1.Columns.Add($"Column{dataGridView1.Columns.Count + 1}", header);
                }
            }

            // 读取数据行
            string line;
            while ((line = reader.ReadLine()) != null)
            {
                string[] rowData = ParseCsvLine(line);
                dataGridView1.Rows.Add(rowData);
            }
        }
        MessageBox.Show("加载成功");
    }
}

// 解析带引号转义的CSV行
private string[] ParseCsvLine(string line)
{
    List<string> fields = new List<string>();
    StringBuilder currentField = new StringBuilder();
    bool inQuotes = false;

    foreach (char c in line)
    {
        if (c == '"')
        {
            inQuotes = !inQuotes;
            // 处理双引号转义（两个连续引号表示一个实际引号）
            if (inQuotes && currentField.Length > 0 && currentField[currentField.Length - 1] == '"')
            {
                currentField.Append('"');
                inQuotes = false;
            }
        }
        else if (c == ',' && !inQuotes)
        {
            fields.Add(currentField.ToString());
            currentField.Clear();
        }
        else
        {
            currentField.Append(c);
        }
    }
    fields.Add(currentField.ToString());
    return fields.ToArray();
}

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