C#导出DataGridView单行至Excel模板时遇GemBox免费版限制错误

我要实现C#中点击DataGridView对应列按钮，将当前选中单行数据导出到Excel模板的功能，但运行时触发如下错误：

GemBox.Spreadsheet.FreeLimitReachedException: 'Free version limitation has been exceeded (Free version is limited to 150 rows and 5 sheets). To control what happens when your application reaches free version limit use SpreadsheetInfo.FreeLimitReached event. For more information about evaluation

以下是我的实现代码：

private void dataGridView1_CellClick(object sender, DataGridViewCellEventArgs e)
{
    string id = dataGridView1.CurrentRow.Cells[2].Value.ToString();

    if (e.ColumnIndex == dataGridView1.Columns["Column1excel"].Index)
    {
        if (MessageBox.Show("Are you sure want to convert the recorde into Excel File ?", "message", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
        {
           if (e.ColumnIndex == dataGridView1.Columns["Column1excel"].Index && e.RowIndex >= 0)
           {
               InsertIntoExcelTemplate("switchyard.XLSX");

               MessageBox.Show("Data inserted into Excel template successfully.");
           }
       }
   }

   if (e.ColumnIndex == dataGridView1.Columns["Column1delet"].Index)
   {
       if (MessageBox.Show("Are you sure want to delete the record?", "message", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
       {
           if (e.ColumnIndex == dataGridView1.Columns["Column1delet"].Index && e.RowIndex >= 0)
           {
               // Retrieve the data associated with the clicked row
               con.Open();

               SqlCommand cmd = new SqlCommand("delete from TB_report_trip where ID_trip=@ID_trip", con);

               cmd.Parameters.AddWithValue("@ID_trip", id);

               cmd.ExecuteNonQuery();

               MessageBox.Show("are you sure want to delete it?", MessageBoxButtons.YesNoCancel.ToString());

               con.Close();
           }
       }
   }
}

private void InsertIntoExcelTemplate(string templateFilePath)
{
     SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");

     ExcelFile workbook = ExcelFile.Load("switchyard.XLSX");

     GemBox.Spreadsheet.ExcelWorksheet worksheet = workbook.Worksheets[0];

     int rowIndex = 1; // Start from the second row since the first row might contain headers

     foreach (DataGridViewRow dataGridViewRow in dataGridView1.Rows)
     {
         int columnIndex = 0;

         foreach (DataGridViewCell dataGridViewCell in dataGridViewRow.Cells)
         {
             object cellValue = dataGridViewCell.Value;

             // GemBox.Spreadsheet uses 0-based indexes for columns
             worksheet.Cells[rowIndex, columnIndex].Value = cellValue;
             columnIndex++;
         }

         rowIndex++;
     }

     workbook.Save("Output.xlsx");
}

private void SpreadsheetInfo_FreeLimitReached(object sender, FreeLimitReachedEventArgs e)
{
    MessageBox.Show("GemBox.Spreadsheet free version limitation reached. Please purchase a license to remove this limitation.", "Limit Exceeded", MessageBoxButtons.OK, MessageBoxIcon.Warning);

    // You may handle this event in other ways, such as limiting the number of rows or sheets used in your application.
}

问题根源 #

1. 导出逻辑偏离需求：需求是导出当前选中单行，但InsertIntoExcelTemplate遍历了DataGridView的所有行，若表格数据超过150行，直接触发GemBox免费版的行数量限制。
2. 限制事件未绑定：定义了SpreadsheetInfo_FreeLimitReached处理方法，但未绑定到SpreadsheetInfo.FreeLimitReached事件，错误触发时无法进入自定义处理流程。

修复方案 #

1. 修改导出逻辑，仅导出选中单行

private void InsertIntoExcelTemplate(string templateFilePath)
{
     SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");
     // 绑定免费版限制事件
     SpreadsheetInfo.FreeLimitReached += SpreadsheetInfo_FreeLimitReached;

     ExcelFile workbook = ExcelFile.Load("switchyard.XLSX");
     GemBox.Spreadsheet.ExcelWorksheet worksheet = workbook.Worksheets[0];

     int rowIndex = 1; // 从第二行写入（假设第一行为表头）
     // 仅处理当前选中行
     DataGridViewRow selectedRow = dataGridView1.CurrentRow;
     if (selectedRow != null)
     {
         int columnIndex = 0;
         foreach (DataGridViewCell cell in selectedRow.Cells)
         {
             worksheet.Cells[rowIndex, columnIndex].Value = cell.Value;
             columnIndex++;
         }
     }

     workbook.Save("Output.xlsx");
     // 解绑事件，避免重复绑定
     SpreadsheetInfo.FreeLimitReached -= SpreadsheetInfo_FreeLimitReached;
}

2. 简化CellClick事件的冗余判断

private void dataGridView1_CellClick(object sender, DataGridViewCellEventArgs e)
{
     // 排除表头行点击
     if (e.RowIndex < 0) return;

     string id = dataGridView1.Rows[e.RowIndex].Cells[2].Value.ToString();

     if (e.ColumnIndex == dataGridView1.Columns["Column1excel"].Index)
     {
         if (MessageBox.Show("确定要将该记录导出为Excel文件吗？", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
         {
             InsertIntoExcelTemplate("switchyard.XLSX");
             MessageBox.Show("数据已成功插入Excel模板。");
         }
     }
     else if (e.ColumnIndex == dataGridView1.Columns["Column1delet"].Index)
     {
         if (MessageBox.Show("确定要删除该记录吗？", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
         {
             // 使用using自动释放数据库连接，避免泄漏
             using (SqlConnection con = new SqlConnection("你的数据库连接字符串"))
             {
                 con.Open();
                 SqlCommand cmd = new SqlCommand("delete from TB_report_trip where ID_trip=@ID_trip", con);
                 cmd.Parameters.AddWithValue("@ID_trip", id);
                 cmd.ExecuteNonQuery();
             }
             MessageBox.Show("记录已删除。");
         }
     }
}

内容的提问来源于stack exchange，提问作者en.nawroz