How to generate a report in excel with tables from a database using C#?

Question

How to pass the data of a query to the columns and rows of a sheet in excel?

Example:

OleDbDataAdapter oda = new OleDbDataAdapter("select * from usuario", conexao.cn);
            DataTable dt = new DataTable();
            oda.Fill(dt);
            dgvconsulta.DataSource = dt;

1   ti  
2   adm 
3   gerente

How to make instead of filling only the datagrid in the form also fill a spreadsheet?

Answer 1

I got what I needed from that code I found on a forum. However I am having difficulties to send the query to a specific column of the spreadsheet and I would like to know if it is possible to use this code with a spreadsheet ready.

        try
        {
            string filename = null;
            SaveFileDialog SaveXml = new SaveFileDialog();
            SaveXml.Filter = "EXCEL files|*.xls";
            SaveXml.FileName = "testes.xls";
            if (SaveXml.ShowDialog() == DialogResult.OK)
            {
                filename = SaveXml.FileName;
            }

            //Criar um arquivo para escrever
            using (StreamWriter sw = new StreamWriter(filename))
            {
                Conexao conexao = new Conexao();

                //Define a instrução SQL para executar contra o banco de dados
                string query = " SELECT * FROM usuario";
                OleDbCommand cmd = new OleDbCommand(query, conexao.cn);
                try
                {
                    //Abre a conexão e gera o datareader
                    conexao.conectar();
                    OleDbDataReader dr = cmd.ExecuteReader();
                    //Percorre o datareader e escreve os dados no arquivo .xls definido
                    while (dr.Read())
                    {
                        sw.WriteLine(dr["cod"].ToString() + "\t" + dr["usuario"].ToString() + "\t" + dr["nivel"].ToString() + "\t" + dr["senha"].ToString());
                    }
                    //Exibe mensagem ao usuario se funcionar
                    MessageBox.Show("Arquivo " + filename + " gerado com sucesso.");
                }
                catch (Exception excpt)
                {
                    MessageBox.Show(excpt.Message);
                }
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show("Erro! " + ex.Message, "Não foi possível exportar.");
        }
    }