How to pass data from a query to the columns and rows of a worksheet 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 do you make the form instead of filling only the datagrid in the form also fill a spreadsheet?
I got what I needed with this code I found in a forum. But I'm having trouble sending the query to a specific column of the spreadsheet and would like to know if it's possible to use that spreadsheet 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.");
}
}