Generating an xlsx file from a GridView that is fed by a Database

1
Good afternoon, I'm facing a problem when generating an Excel file, I was able to make the information appear in Excel and be shown on the screen, but I found myself with doubts when I try to generate only one file with the information, my doubts would be like do I generate this file or what do I have to implement in my code to 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();
            }
        }*/

    }
    
asked by anonymous 05.09.2018 / 20:20

1 answer

1

I was able to get results by reusing the code that generated an Excel Window with the data, I just had to add 3 lines that was the path of the file I passed with parameters to always create a new file and saved the data on 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();
    
05.09.2018 / 21:36