最新消息:20210816 当前crifan.com域名已被污染,为防止失联,请关注(页面右下角的)公众号

【已解决】将C#中的DataGridView中的数据,导出为Excel

C# crifan 8744浏览 0评论

【问题】

C#的DataGridView中已经有了数据了:

has got datagridview data

接下来,想要将这些数据,导出为excel。

其中要注意的是,最后一列为DataGridViewButtonColumn,每个cell都是DataGridViewButtonCell。

【解决过程】

1.参考:

玩转DataGridView之将数据导出成Excel和Word格式

How to export DataGridView to excel file

去试试。

2.先去导入dll库:Microsoft.Office.Interop.Excel.dl

add reference

找了下,才在.NET下找到这个:

dotnet microsoft.office.interop.excel 12

 

added excel dll

3.然后再去写代码:

using Excel = Microsoft.Office.Interop.Excel; 


private void btnSaveAll_Click(object sender, EventArgs e)
{
    Excel.Application xlApp = new Excel.Application();
    Excel.Workbook xlWorkBook;
    Excel.Worksheet xlWorkSheet;

    object misValue = System.Reflection.Missing.Value;
    xlApp = new Excel.ApplicationClass();
    xlWorkBook = xlApp.Workbooks.Add(misValue);
    xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
    int i = 0;
    int j = 0;


    for (i = 0; i <= dgvSearchResult.RowCount - 1; i++)
    {
        for (j = 0; j <= dgvSearchResult.ColumnCount - 1; j++)
        {
            DataGridViewCell cell = dgvSearchResult[j, i];
            if (j == girUrlColumnIdx)
            {
                xlWorkSheet.Cells[i + 1, j + 1] = cell.Tag.ToString();
            }
            else
            {
                xlWorkSheet.Cells[i + 1, j + 1] = cell.Value;
            }
        }
    }

    //xlWorkBook.SaveAs("csharp.net-informations.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
    xlWorkBook.SaveAs("fiverrComScrapedResult.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
    xlWorkBook.Close(true, misValue, misValue);
    xlApp.Quit();

    releaseObject(xlWorkSheet);
    releaseObject(xlWorkBook);
    releaseObject(xlApp);

    MessageBox.Show("Excel file created , you can find the file c:\\csharp.net-informations.xls");
}

private void releaseObject(object obj)
{
    try
    {
        System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
        obj = null;
    }
    catch (Exception ex)
    {
        obj = null;
        MessageBox.Show("Exception Occured while releasing object " + ex.ToString());
    }
    finally
    {
        GC.Collect();
    }
}

结果是,代码运行正常,但是却在当前的程序目录下,找不到所保存的excel文件。

4.参考:

_Workbook.SaveAs Method

去试试文件的绝对路径:

xlWorkBook.SaveAs("c;\\fiverrComScrapedResult.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);

结果竟然出错了:

excel cannot access the file

很明显,之前一直是把excel文件保存到

C:\Users\CLi\Documents

中的。

所以,就去看看,果然有之前就保存好的excel文件:fiverrComScrapedResult.xls

打开后,效果如下:

saved excel file ok but not header

5.很明显,没有header。

再去加代码。

折腾过程参见:

【已解决】C#中保存数据到Worksheet结果出错:An unhandled exception of type ‘System.Runtime.InteropServices.COMException’ occurred in mscorlib.dll。Additional information: Exception from HRESULT: 0x800A03EC

6.后来改为:

            //save header
            for (i = 0; i <= dgvSearchResult.ColumnCount - 1; i++)
            {
                xlWorkSheet.Cells[0+1, i+1] = dgvSearchResult.Columns[i].HeaderText;
            }

就正常了,因为把i和j搞错了。。。

结果保存出来的数据,和原先的数据,第一行丢失了。

7.最终,使用如下代码:

using Excel = Microsoft.Office.Interop.Excel; 

private void btnSaveAll_Click(object sender, EventArgs e)
{
    Excel.Application xlApp = new Excel.Application();
    Excel.Workbook xlWorkBook;
    Excel.Worksheet xlWorkSheet;

    object misValue = System.Reflection.Missing.Value;
    xlApp = new Excel.ApplicationClass();
    xlWorkBook = xlApp.Workbooks.Add(misValue);
    xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
    int i = 0;
    int j = 0;

    //save header
    for (i = 0; i <= dgvSearchResult.ColumnCount - 1; i++)
    {
        xlWorkSheet.Cells[0+1, i+1] = dgvSearchResult.Columns[i].HeaderText;
    }

    //save cells
    for (i = 0; i <= dgvSearchResult.RowCount - 1; i++)
    {
        for (j = 0; j <= dgvSearchResult.ColumnCount - 1; j++)
        {
            DataGridViewCell cell = dgvSearchResult[j, i];
            if (j == girUrlColumnIdx)
            {
                xlWorkSheet.Cells[i + 2, j + 1] = cell.Tag.ToString();
            }
            else
            {
                xlWorkSheet.Cells[i + 2, j + 1] = cell.Value;
            }
        }
    }

    string currentPath = System.Environment.CurrentDirectory;
    string outputFilename = "fiverrComScrapedResult.xls";
    string fullFilename = Path.Combine(currentPath, outputFilename);
    //xlWorkBook.SaveAs(fullFilename, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
    xlWorkBook.SaveAs(fullFilename, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
    xlWorkBook.Close(true, misValue, misValue);
    xlApp.Quit();

    releaseObject(xlWorkSheet);
    releaseObject(xlWorkBook);
    releaseObject(xlApp);
}

private void releaseObject(object obj)
{
    try
    {
        System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
        obj = null;
    }
    catch (Exception ex)
    {
        obj = null;
        MessageBox.Show("Exception Occured while releasing object " + ex.ToString());
    }
    finally
    {
        GC.Collect();
    }
}

 

解决了标题的问题,最终可以获得了正常的输出,带标题的:

excel has title

 

【总结】

效果还不错。


关于继续折腾格式化的问题,可参见:

【已解决】C#中格式化Excel文件(的标题变为加粗)

转载请注明:在路上 » 【已解决】将C#中的DataGridView中的数据,导出为Excel

发表我的评论
取消评论

表情

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址

网友最新评论 (1)

  1. girUrlColumnIdx 这个值是什么 ?????????????
    moyan10年前 (2014-07-31)回复
85 queries in 0.178 seconds, using 22.12MB memory