C# DevExpress 将 GridView 导出到 Excel

DevExpress export GridView to Excel

提问by Nejthe

I really need help with this.. I can't find any example on the internet I am using DevExpress GridView I need to send it to excel and I'm getting problems to loop to every cell and column because DevExpress contains different methods then the DataGridView

我真的需要这方面的帮助..我在互联网上找不到任何示例我正在使用 DevExpress GridView 我需要将它发送到 excel 并且我在循环到每个单元格和列时遇到问题，因为 DevExpress 包含不同的方法然后数据网格视图

that's the code that i'm trying to write.. I really Appreciate your help

这就是我正在尝试编写的代码..我真的很感谢你的帮助

    public class Form1 : System.Windows.Forms.Form
    {
    Microsoft.Office.Interop.Excel.ApplicationClass ExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();

    string FirstName = "First Name";
    string FatherName = "Father Name";
    string LastName = "Last Name";
    }
    public Form1()
    {
        ExcelApp.Application.Workbooks.Add(Type.Missing);
        ExcelApp.Columns.ColumnWidth = 20;
        //
        // Required for Windows Form Designer support
        //
        InitializeComponent();

        //
        // TODO: Add any constructor code after InitializeComponent call
        //
    }
    private void simpleButton1_Click(object sender, System.EventArgs e)
    {
        try
        {
            OleDbConnection con = new OleDbConnection();
            con.ConnectionString = "Provider=Microsoft.Ace.OLEDB.12.0;Data Source=C:\Users\pc\Documents\Emp.xlsx;Extended Properties=\"Excel 12.0;HDR=Yes\"";

            con.Open();
            DataTable dtSchema;
            dtSchema = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
            OleDbCommand Command = new OleDbCommand ("select * FROM [" + dtSchema.Rows[0]["TABLE_NAME"].ToString() + "]", con);
            OleDbDataAdapter da = new OleDbDataAdapter(Command);
            DataSet ds = new DataSet ();
            da.Fill(ds);
            dataGrid1.DataSource = ds.Tables[0];
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }

    private void ExportBtn_Click(object sender, System.EventArgs e)
    {
        for (int i = 1; i < gridView3.Columns.Count + 1; i++)
        {
            //ExcelApp.Cells[1, i] = gridView3.Columns[i].HeaderStyleName;

        }
        for (int i = 0; i< gridView3.RowCount - 1; i++)
        {
            for (int j = 0; j < gridView3.Columns.Count; j++)
            {
                ExcelApp.Cells[i + 2, j + 1] = gridView3.Columns[j].ToString();
            }
        }
        ExcelApp.ActiveWorkbook.SaveCopyAs("C:\Users\pc\Emp.xlsx");
        ExcelApp.ActiveWorkbook.Saved = true;
        ExcelApp.Quit();
    }

My problem is in the Export button the click event.. There's no such thing as Row()

我的问题是在导出按钮点击事件.. 没有像 Row() 这样的东西

采纳答案by Niranjan Singh

To know various export methods of XtraGrid, go through Export Methods and Settings

要了解 XtraGrid 的各种导出方法，请查看导出方法和设置

Use GridControl.ExportToXls(String)Method

使用GridControl.ExportToXls(String)方法

Example code snippet:

示例代码片段：

private void mnuExportTable_ItemClick_1(object sender, DevExpress.XtraBars.ItemClickEventArgs e)
{
    using (SaveFileDialog saveDialog = new SaveFileDialog())
    {
        saveDialog.Filter = "Excel (2003)(.xls)|*.xls|Excel (2010) (.xlsx)|*.xlsx |RichText File (.rtf)|*.rtf |Pdf File (.pdf)|*.pdf |Html File (.html)|*.html";
        if (saveDialog.ShowDialog() != DialogResult.Cancel)
        {
            string exportFilePath = saveDialog.FileName;
            string fileExtenstion = new FileInfo(exportFilePath).Extension;

            switch (fileExtenstion)
            {
                case ".xls":
                    gridControl.ExportToXls(exportFilePath);
                    break;
                case ".xlsx":
                    gridControl.ExportToXlsx(exportFilePath);
                    break;
                case ".rtf":
                    gridControl.ExportToRtf(exportFilePath);
                    break;
                case ".pdf":
                    gridControl.ExportToPdf(exportFilePath);
                    break;
                case ".html":
                    gridControl.ExportToHtml(exportFilePath);
                    break;
                case ".mht":
                    gridControl.ExportToMht(exportFilePath);
                    break;
                default:
                    break;
            }

            if (File.Exists(exportFilePath))
            {
               try
               {
                   //Try to open the file and let windows decide how to open it.
                   System.Diagnostics.Process.Start(exportFilePath);
                }
                catch
                {
                    String msg = "The file could not be opened." + Environment.NewLine + Environment.NewLine + "Path: " + exportFilePath;
                    MessageBox.Show(msg, "Error!", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
             }
             else
             {
                 String msg = "The file could not be saved." + Environment.NewLine + Environment.NewLine + "Path: " + exportFilePath;
                 MessageBox.Show(msg, "Error!", MessageBoxButtons.OK, MessageBoxIcon.Error);
             }
        }
    }
}

Reference:
Exporting Several XtraGrid Controls to a Single Excel File

参考：将
多个 XtraGrid 控件导出到单个 Excel 文件

回答by Nilesh Sarkale

try below code. SaveFileDialog1 is tool also add required references --

试试下面的代码。SaveFileDialog1 是工具还添加所需的引用-

 Try

        Dim sv As New SaveFileDialog1
        SaveFileDialog1.Filter = "Excel Workbook|*.xlsx"
        If SaveFileDialog1.ShowDialog() = DialogResult.OK And SaveFileDialog1.FileName <> Nothing Then
            If SaveFileDialog1.FileName.EndsWith(".xlsx") Then
                Dim path = SaveFileDialog1.FileName.ToString()
                GridControlAFP.ExportToXlsx(path)
                Dim xlApp As New Excel.Application
                Dim xlWorkBook As Excel.Workbook
                Dim xlWorkSheet As Excel.Worksheet
                xlWorkBook = xlApp.Workbooks.Open(path)
                xlWorkSheet = xlWorkBook.Sheets("Sheet")
                xlWorkSheet.Range("A1:XFD1").EntireColumn.AutoFit()

                xlWorkBook.Save()
                xlWorkBook.Close()
                xlApp.Quit()
            End If


            MessageBox.Show("Data Exported to :" + vbCrLf + SaveFileDialog1.FileName, "Business Intelligence Portal", MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1)
            SaveFileDialog1.FileName = Nothing

        End If
    Catch ex As Exception
    End Try

回答by Adithya Vijay

The selectgridvaluesfunction returns the values in the Database in Datatable format. Calling this function in the show button sets these values in the gridcontrol. Export button exports the values into an Excel sheet in .xlsx format

该selectgridvalues函数以数据表格式返回数据库中的值。在显示按钮中调用此函数可在网格控件中设置这些值。导出按钮将值以 .xlsx 格式导出到 Excel 工作表中

    //function to get values from DataTable to gridControl Devexpress
    public DataTable selectgridvalues()
    {
      SqlConnection con;
      con = new SqlConnection();
      con.ConnectionString = "server='SERVER';uid='sa';pwd='1234';database='DBName'";
      con.Open();

      SqlDataAdapter adp = new SqlDataAdapter("select * from Tablename order by id ", con);
      DataTable dt = new DataTable();
      adp.Fill(dt);
      gridControl1.DataSource =dt;
    }


    //code on showdatagridview values button
    public void buttonShow_Click(object sender, EventArgs e)
    {         
        gridControl1.DataSource = selectgridvalues();
    }


     //code on export to excel button
     private void buttonExportExcel_Click(object sender, EventArgs e)
    {
        SaveFileDialog saveFileDialogExcel = new SaveFileDialog();
        saveFileDialogExcel.Filter = "Excel files (*.xlsx)|*.xlsx";
        if (saveFileDialogExcel.ShowDialog() == DialogResult.OK)
        {
            string exportFilePath = saveFileDialogExcel.FileName;
            gridControl1.DataSource = selectgridvalues();
            gridControl1.ExportToXlsx(exportFilePath);
        }

    }