none
datagridview中的困惑, RRS feed

  • 问题

  • 将EXL导入后随机抽取的数据赋给三个  datagridview窗体后导出,按下BUTTON,执行导出:代码如下

     

     private void btnExportRaw_Click(object sender, EventArgs e)
            {
                SaveDataTableToExcel(new System.Data.DataTable[] { this.departResDT, this.expertResDT, this.humanManResDT }, System.Windows.Forms.Application.StartupPath + @"\groupraw.xls");
            }

            public static bool SaveDataTableToExcel(System.Data.DataTable[] excelTables, string filePath)
            {

                Microsoft.Office.Interop.Excel.Application app =
                    new Microsoft.Office.Interop.Excel.ApplicationClass();
                try
                {
                    app.Visible = false;
                    object objOpt = System.Reflection.Missing.Value;
                    Workbook wBook = app.Workbooks.Add(objOpt);


                    //wBook.Worksheets.Add(true);
                    for (int di = 0; di < excelTables.Length; di++)
                    {
                        System.Data.DataTable excelTable = excelTables[di];
                        Worksheet wSheet = wBook.Worksheets["sheet" + (di + 1)] as Worksheet;
                        if (excelTable.Rows.Count > 0)
                        {
                            int row = 0;
                            row = excelTable.Rows.Count;
                            int col = excelTable.Columns.Count;
                            for (int i = 0; i < row; i++)
                            {
                                for (int j = 0; j < col; j++)
                                {
                                    string str = excelTable.Rows[i][j].ToString();
                                    wSheet.Cells[i + 2, j + 1] = str;
                                }
                            }
                        }
                        int size = excelTable.Columns.Count;
                        for (int i = 0; i < size; i++)
                        {
                            wSheet.Cells[1, 1 + i] = excelTable.Columns[i].ColumnName;
                        }
                    }
                    //设置禁止弹出保存和覆盖的询问提示框             
                    app.DisplayAlerts = false;
                    app.AlertBeforeOverwriting = false;
                    //保存工作簿
                    wBook.Save();
                    //保存excel文件    
                    app.Save(filePath);
                    app.SaveWorkspace(filePath);
                    //
                    app.Quit();
                    MessageBox.Show("数据已经导出到目录:" + filePath);
                    app = null;
                    return true;
                }
                catch (Exception err)
                {
                    MessageBox.Show("导出Excel出错!错误原因:" + err.Message, "提示信息",
                         MessageBoxButtons.OK, MessageBoxIcon.Information);
                    return false;
                }
                finally
                {
                }
            }
        }
    }

     

     

    很遗憾,没有结果啊!请高手赐教!C#

    2011年6月2日 2:47

答案

  • 我摸索了一天,终于解决了这个导出问题了,程序没有错,是组件有问题。〔解决VS2010+OFFICE2010环境下开发的软件在OFFICE2003环境下的应用问题哦!〕

    重新安装完整版的OFFICE 2003以后,

     

    重新添加引用COM:EXCEL和microsoft.office.core


    出现如下情况:

     

    问题1.
    类型“Microsoft.Office.Interop.Excel.ApplicationClass”未定义构造函数   无法嵌入互操作类型“Microsoft.Office.Interop.Excel.ApplicationClass”。请改用适用的接口。

    解决办法:是将引用的DLL:Microsoft.Office.Interop.Excel;的嵌入互操作类型改为false,就可以了。

    问题2.
    “Microsoft.Office.Interop.Excel.ApplicationClass”是一个类型,而不是命名空间

    解决办法:添加定义using Excel=Microsoft.Office.Interop.Excel.ApplicationClass;        看清楚了哦“Excel=”就可以了啊!哈哈!

    \Release\目录下已经自动生成了EXE啦!我这一整天算是没有白干吧!嘿嘿!

    2011年6月2日 10:17

全部回复

  • 你好
    可不可以把你的DATATABLE ARRAY 轉成DataSet

    如果可以的話..可以參考以下的CODE

    把DataSet 轉成XLSX 的

    Public Shared Function ExportDataSetToXLSX(ByVal ds As System.Data.DataSet, ByVal ToIncludeHeader As Boolean, ByVal IncludeBorder As Boolean,
                     ByVal FileType As ExportFileType, ByVal FilePath As String, ByVal FileName As String, Optional ByVal ExcelTemplateFile As String = "", Optional ByVal NoOfHeaderColumnsToSkip As Integer = 0,
                     Optional ByVal UseTableNameAsSheetName As Boolean = False) As Boolean
        Dim result As Boolean
    
    
        Dim ExcelApp As Microsoft.Office.Interop.Excel.Application
        Dim ExcelWorkBook As Microsoft.Office.Interop.Excel.Workbook
        Dim ExcelWorkSheet As Microsoft.Office.Interop.Excel.Worksheet
        Dim MissingValue As Object = System.Reflection.Missing.Value
    
        ExcelApp = New Microsoft.Office.Interop.Excel.Application
    
        If File.Exists(ExcelTemplateFile) Then
          ExcelWorkBook = ExcelApp.Workbooks.Open(ExcelTemplateFile)
        Else
          ExcelWorkBook = ExcelApp.Workbooks.Add(MissingValue)
        End If
    
    
    
    
        Dim DestinationFileName As String = GenerateDestinationFileName(FilePath, FileName, FileType)
        Dim CellRange As Microsoft.Office.Interop.Excel.Range
        Dim NoOfTable As Int32 = 1
        For Each tbl As Data.DataTable In ds.Tables
    
    
    
    
          ExcelWorkSheet = ExcelWorkBook.Sheets(NoOfTable)
    
    
          If UseTableNameAsSheetName Then
            ExcelWorkSheet.Name = tbl.TableName
          End If
    
          Dim NoOfColumns As Integer = tbl.Columns.Count
          Dim NoOfRows As Integer = tbl.Rows.Count
    
          If ToIncludeHeader Then
            NoOfRows = NoOfRows + 1
            For c = 0 To tbl.Columns.Count - 1
              ExcelWorkSheet.Cells(1, c + 1) = _
                tbl.Columns(c).ColumnName
            Next
    
            For r = 0 To tbl.Rows.Count - 1
              For c = 0 To tbl.Columns.Count - 1
                If Not tbl.Rows(r)(c) Is DBNull.Value Then
                  ExcelWorkSheet.Cells(r + 2, c + 1) = _
                    tbl.Rows(r)(c)
                End If
    
              Next
            Next
          Else
            For r = 0 To tbl.Rows.Count - 1
              For c = 0 To tbl.Columns.Count - 1
                If Not tbl.Rows(r)(c) Is DBNull.Value Then
                  ExcelWorkSheet.Cells(r + 1 + NoOfHeaderColumnsToSkip, c + 1) = _
                    tbl.Rows(r)(c)
                End If
    
              Next
            Next
          End If
    
    
          CellRange = ExcelWorkSheet.Range("A1", ExcelWorkSheet.Cells(NoOfRows + 1, NoOfColumns))
          CellRange.EntireColumn.AutoFit()
          'Apply border to selected cells
          If IncludeBorder Then
            ApplyCellBorder(CellRange)
          End If
    
          NoOfTable = NoOfTable + 1
    
    
          ReleaseExcelObject(CellRange)
          ReleaseExcelObject(ExcelWorkSheet)
    
        Next
    
        'Save work book
        SaveWorkBook(ExcelWorkBook, FileType, FilePath, FileName)
    
    
    
        ExcelWorkBook.Close(False)
        ExcelApp.Quit()
        ExcelApp.Workbooks.Close()
    
        ReleaseExcelObject(ExcelApp)
        ReleaseExcelObject(ExcelWorkBook)
    
        Return result 

    End Function

    full source code in http://sharechiwailib.codeplex.com/SourceControl/changeset/view/6523#147619

    或者可以使用 http://sharechiwailib.codeplex.com

     

     Please correct me if my concept is wrong 


    Chi
    2011年6月2日 5:14
    版主
  • 我摸索了一天,终于解决了这个导出问题了,程序没有错,是组件有问题。〔解决VS2010+OFFICE2010环境下开发的软件在OFFICE2003环境下的应用问题哦!〕

    重新安装完整版的OFFICE 2003以后,

     

    重新添加引用COM:EXCEL和microsoft.office.core


    出现如下情况:

     

    问题1.
    类型“Microsoft.Office.Interop.Excel.ApplicationClass”未定义构造函数   无法嵌入互操作类型“Microsoft.Office.Interop.Excel.ApplicationClass”。请改用适用的接口。

    解决办法:是将引用的DLL:Microsoft.Office.Interop.Excel;的嵌入互操作类型改为false,就可以了。

    问题2.
    “Microsoft.Office.Interop.Excel.ApplicationClass”是一个类型,而不是命名空间

    解决办法:添加定义using Excel=Microsoft.Office.Interop.Excel.ApplicationClass;        看清楚了哦“Excel=”就可以了啊!哈哈!

    \Release\目录下已经自动生成了EXE啦!我这一整天算是没有白干吧!嘿嘿!

    2011年6月2日 10:17