Generating an xlsx file from a Gridview that is powered by a Database

Asked

Viewed 98 times

1

Good afternoon, I am facing a problem when it comes to generating an Excel file, I was able to make the information appear in Excel and be shown on the screen, but found me with doubt when I try to generate only a file with the information, my doubt would be how do I generate this file or what I have to implement in my code to make it work 

    private void btnExcel_Click(object sender, EventArgs e)
    {
        //Esse codigo gera um arquivo Excel tirando as informações do Grid.

        Microsoft.Office.Interop.Excel.Application Excel = new Microsoft.Office.Interop.Excel.Application();
        Excel.Application.Workbooks.Add(Type.Missing);

        for(int i=0; i < grdRelatorio.Rows.Count; i++)
        {
            DataGridViewRow row = grdRelatorio.Rows[i];
            for(int j=0; j < row.Cells.Count; j++)
            {
                Excel.Cells[i + 1, j + 1] = row.Cells[j].ToString();
            }
        }

        Excel.ActiveWorkbook.SaveCopyAs(@"C:\Arquivos Excel");
        Excel.ActiveWorkbook.Saved = true;

        Excel.Quit();

        //Esse codigo passa as informações do Grid para um Excel e abre na Tela.

        /*Microsoft.Office.Interop.Excel.Application Excel = new Microsoft.Office.Interop.Excel.Application();
        Workbook wb = Excel.Workbooks.Add(XlSheetType.xlWorksheet);
        Worksheet ws = (Worksheet)Excel.ActiveSheet;
        Excel.Visible = true;

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

        for(int i=0; i< grdRelatorio.Columns.Count - 1; i++)
        {
            for(int j = 0; j < grdRelatorio.Columns.Count; j++)
            {
                ws.Cells[i + 2, j + 1] = grdRelatorio.Rows[i].Cells[j].Value.ToString();
            }
        }*/

    }
c# excel visual-studio-2017

  • What is your doubt and error?

  • Sorry I didn’t leave this explicitly kk, my doubt and how do I generate an Excel file

  • Have you tried the method SaveAs(), your SaveCopyAs(path) shouldn’t have the full path as in the file me? Your code returns some error?

  • Well with Saveas and Savecopyas the result is the same, the program hangs when I click the button and the file is not generated

  • How so hangs... you debugged to see what happens?

  • I did this with a "teacher" he told me that it was not to lock because I’m just reading the data from Grid and that there may be a logic error in my for. I’m not sure why I’m beginner yet, I don’t know the best way to generate this file

Show 1 more comment

1 answer

1


I managed to get result by reusing the code that generated an Excel Window with the data, I only had to add 3 lines that was the file path I passed with parameters to always create a new file and saved the data in the next line. (:

        Microsoft.Office.Interop.Excel.Application Excel = new 
        Microsoft.Office.Interop.Excel.Application();
        Workbook wb = Excel.Workbooks.Add(XlSheetType.xlWorksheet);
        Worksheet ws = (Worksheet)Excel.ActiveSheet;
        Excel.Visible = false;

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

        for(int i=0; i< grdRelatorio.Columns.Count - 1; i++)
        {
            for(int j = 0; j < grdRelatorio.Columns.Count; j++)
            {
                ws.Cells[i + 2, j + 1] = grdRelatorio.Rows[i].Cells[j].Value.ToString();
            }
        }
        Excel.ActiveWorkbook.SaveAs(string.Format(@"C:\Arquivos Excel\{0}_{1}.xlsx", lblConexao.Text, DateTime.Now.ToString("yyyyMMddHHmmss")));
        Excel.ActiveWorkbook.Saved = true;
        Excel.Quit();

Browser other questions tagged c# excel visual-studio-2017

You are not signed in. Login or sign up in order to post.