Datagridview到Excel C＃ [英] Datagridview to Excel C#

问题描述

您好，



我有一个问题，希望有人可以帮我。

这很简单：我想要将我的datagridview导出到Excel工作表。它很顺利。

使用这段代码：

Hello,

I have a question and hope someone could help me with it.
It`s quite simple: I want to export my datagridview to excel sheet. It goes well.
Using this piece of code:

try
{

    Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();

    Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);

    Microsoft.Office.Interop.Excel._Worksheet worksheet = null;

    app.Visible = true;

    worksheet = workbook.Sheets["Sheet1"];

    worksheet = workbook.ActiveSheet;

    worksheet.Name = "Export from datagrid";

    for (int i = 3; i < gv_gsk.Columns.Count + 1; i++)
    {

        worksheet.Cells[1, i] = gv_gsk.Columns[i - 1].HeaderText;

    }

    for (int i = 0; i < gv_gsk.Rows.Count; i++)
    {

        for (int j = 2; j < gv_gsk.Columns.Count; j++)
        {

            worksheet.Cells[i + 2, j + 1] = gv_gsk.Rows[i].Cells[j].Value.ToString();

        }

    }





    app.Quit();
}
catch (Exception)
{ }

问题是，我的datagridview包含很少的行是空的（我只有产品的名称，但不是所有其他细节），当我导出到excel-in excel工作簿时，并不是所有的产品，而是之前的第一个产品一个空行。

有什么诀窍可以阻止它吗？我希望所有行都在excel中。



非常感谢。

The problem is, my datagridview contains few rows which are empty (I have just name of product, but not all the other details), and when I export to excel- in excel workbook are shown not all products but the first ones before an empty row.
Is there any trick to prevent that? I want all rows to be in excel.

Thank you very much.

推荐答案

我不知道你的requriemetns要显示哪一列，但你的for循环似乎有一些缺陷。



On第一个for循环

Hi,
I don''t know your requriemetns on which column to to be displayed, but there seems some flaws in your for loops.

On the first for loop

for (int i = 3; i < gv_gsk.Columns.Count + 1; i++)
{
    worksheet.Cells[1, i] = gv_gsk.Columns[i - 1].HeaderText;
}

你从第3列开始并神奇地在计数中添加一个额外的ghost列，然后你将列递减1，所以实际上你正在开始从第2列开始。我相信您要将第2列添加到Excel中的第3列。在这种情况下，您可以按如下方式重写代码：

you are starting with column 3 and magically adding an extra ghost column to the count and then you are decrementing the column by 1 so actually you are starting from column 2. I believe you want to add the column 2 to the column 3 in your excel. In that case you could rewrite the code as follow:

for (int i = 2; i < gv_gsk.Columns.Count; i++)
{
    Microsoft.Office.Interop.Excel.Range range = worksheet.Cells[1, (i + 1)] as Range;
    range.Value2 = gv_gsk.Columns[i].HeaderText;
}

所以这里你从第二列开始，你要导出到Excel中的第三列。



和你的第三个for循环：

So here you are starting from the second column that you wanted to exported to the third column in Excel.

And on your third for loop:

for (int i = 0; i < gv_gsk.Rows.Count; i++)
{
    for (int j = 2; j < gv_gsk.Columns.Count; j++)
    {
        worksheet.Cells[i + 2, j + 1] = gv_gsk.Rows[i].Cells[j].Value.ToString();
    }
}

对于每一行，现在您从2开始列，这是正确的。然后在excel上你插入错误的地方。你想在i + 1而不是2的标题下添加数据行。

For each row, now you are starting the column at 2, which is correct. Then on the excel you are inserting then in the wrong place. you want add the data row under the heading that is i + 1 not 2.

for (int i = 0; i < gv_gsk.Rows.Count; i++)
{
    for (int j = 2; j < gv_gsk.Columns.Count; j++)
    {
        Microsoft.Office.Interop.Excel.Range range = worksheet.Cells[(i + 1), (j + 1)] as Range;
        range.Value2 = gv_gsk.Rows[i].Cells[j].Value.ToString();
    }
}

现在你的列标题和值应该在你的excel中匹配，以及你是不在标题和数据之间添加额外的行。





我希望这会有所帮助。



问候

Jegan

Now your column header and the values should match in your excel as well as you are not adding an extra row between the header and the data.


I hope this helps.

Regards
Jegan

要导出所有列，请按如下方式更改代码：

< b>第一次循环

替换：

To export all columns, change code as follows:
First loop
Replace:

for (int i = 3; i < gv_gsk.Columns.Count; i++)

with：

with:

for (int i = 0; i < gv_gsk.Columns.Count; i++)

第三个循环（第二个循环内部）

Relace：

Third loop (inside second loop)
Relace:

for (int j = 2; j < gv_gsk.Columns.Count; j++)

with

with

for (int j = 0; j < gv_gsk.Columns.Count; j++)

for (int i = 0; i < gv_alkaloid.Rows.Count; i++)
              {
                  for (int j = 2; j < gv_alkaloid.Columns.Count; j++)
                  {
                      worksheet.Cells[i + 2, j + 1] = gv_alkaloid.Rows[i].Cells[j].Value == null ? string.Empty : gv_alkaloid.Rows[i].Cells[j].Value.ToString();
                  }
              }

这篇关于Datagridview到Excel C＃的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！