Exportando para Excel

Quem nunca teve a necessidade de exportar uma ou mais tabelas de um banco para o Excel? Existem algumas formas, como definir os cabeçalhos da página e gerar uma tabela html, por exemplo. Mas o problema dessa forma é que cada tabela deve ser exportada para um arquivo (.xls) diferente.

Como fazer então para que, num mesmo .xls tenhamos uma ou mais tabelas, cada uma exportada para uma planilha diferente? A resposta: XML.

Para exemplificar, vamos começar criando um novo website (ou acrescentar um webform a um site existente).

Na página criada, acrescente:

  • 1 CheckBoxList e renomeie-a para cklTabelas;
  • 1 Botão, renomeado para btBaixar;
  • 1 Label, com o ID lblErro, Visible = false e text= “Selecione uma ou mais tabelas p/ exportar.”

A idéia aqui é preenchermos a CheckBoxList com as tabelas existentes no banco. O usuário poderá selecionar quais tabelas baixar, e, ao clicar no botão, geramos o arquivo para o Excel.
Nota: o xml gerado não funciona em versões anteriores ao Office 2002.

Neste exemplo estou usando novamente o banco Northwind.

No código, referencie os namespaces System.Xml, System.IO (para usarmos streams), System.Text e System.Data.SqlClient:

using System.Xml;
using System.IO;
using System.Text;
using System.Data.SqlClient;

Declare também nosso objeto para conexão ao banco de dados:

SqlConnection cn;

No evento Load da página, acrescente o código:

protected void Page_Load(object sender, EventArgs e)
{

string cnString = @”Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Northwind;Data Source=LOCALHOST\SQLEXPRESS”;

cn = new SqlConnection(cnString);

if (!Page.IsPostBack)
{

cn.Open();

string[] restrictions = new string[4];
restrictions[3] = “BASE TABLE”;

DataTable schemaTable = cn.GetSchema(“TABLES”, restrictions);

cklTabelas.DataSource = schemaTable;
cklTabelas.DataTextField = “TABLE_NAME”;
cklTabelas.DataValueField = “TABLE_NAME”;
cklTabelas.DataBind();

}

}

Como você deve ter reparado, usamos o método GetSchema do objeto SqlConnection para obtermos a estrutura do banco de dados.

No nosso caso, queremos somente as tabelas, por isso, na linha

DataTable schemaTable = cn.GetSchema(“TABLES”, restrictions)

usamos o segundo parâmetro, onde especificamos, através de um array, que só queremos as tabelas.
Esse parâmetro é um array de 4 elementos, onde o 1º elemento é o nome do catálogo, o 2º, o owner, o 3º, o nome do objeto, e o 4º (que usamos), o tipo do objeto.

Repare que usamos “BASE TABLE” nessa restrição. Se usássemos somente “TABLE”, as views existentes também seriam retornadas.

Teste o projeto e veja que a CheckBoxList é preenchida com os nomes das tabelas:

Voltando ao código, acrescente o evento click do botão. Os comentários estão no código:

protected void btBaixar_Click(object sender, EventArgs e)
{

//Se não existem itens selecionados, mostramos uma mensagem
if
(cklTabelas.SelectedIndex == -1)
{

lblErro.Visible = true;
return;

}

MemoryStream stm = new MemoryStream();
StreamReader rdr = new StreamReader(stm);

XmlTextWriter writer = new XmlTextWriter(stm, Encoding.UTF8);

writer.Formatting = Formatting.Indented;
lblErro.Visible = false;

//Definimos os cabeçalhos do xml
writer.WriteStartDocument();
writer.WriteProcessingInstruction(“mso-application”, “progid=\”Excel.Sheet\””);

writer.WriteStartElement(“Workbook”);
writer.WriteAttributeString(“xmlns”,
null,null,“urn:schemas-microsoft-com:office:spreadsheet”);
writer.WriteAttributeString(“xmlns”,
“o”, null, “urn:schemas-microsoft-com:office:office”);
writer.WriteAttributeString(“xmlns”,
“x”, null, “urn:schemas-microsoft-com:office:excel”);
writer.WriteAttributeString(“xmlns”,
“ss”, null, “urn:schemas-microsoft-com:office:spreadsheet”);
writer.WriteAttributeString(“xmlns”,
“html”, null, “http://www.w3.org/TR/REC-html40”);

writer.WriteStartElement(“DocumentProperties”);
writer.WriteAttributeString(“xmlns”,
null, null, “urn:schemas-microsoft-com:office:office”);
writer.WriteEndElement();

writer.WriteStartElement(“ExcelWorkbook”);
writer.WriteAttributeString(“xmlns”,
null, null, “urn:schemas-microsoft-com:office:excel”);
writer.WriteEndElement();

//Formatação da planilha
writer.WriteStartElement(“Styles”);

writer.WriteStartElement(“Style”);
writer.WriteAttributeString(“ss”, “ID”, null, “Bold”);
writer.WriteStartElement(“Font”);
writer.WriteAttributeString(“x”, “Family”, null, “Swiss”);
writer.WriteAttributeString(“ss”, “Bold”, null, “1”);
writer.WriteAttributeString(“ss”, “Underline”, null, “Single”);
writer.WriteEndElement();

writer.WriteEndElement();
writer.WriteEndElement();
cn.Open();

for (int i = 0; i < cklTabelas.Items.Count; i++)
{

/* percorremos todos os itens da CheckBoxList. Caso o item atual
esteja selecionado, acrescentamos à lista de exportação
*/

if (cklTabelas.Items[i].Selected)
{

string tabela = cklTabelas.Items[i].Text;

// Definimos os cabeçalhos de cada planilha, incluindo nome, largura e coluna das células
writer.WriteStartElement(“Worksheet”);
writer.WriteAttributeString(“ss”, “Name”, null, tabela);

writer.WriteStartElement(“Table”);
writer.WriteAttributeString(“x”, “FullColumns”, null, “1”);
writer.WriteAttributeString(“x”, “FullRows”, null, “1”);
writer.WriteAttributeString(“ss”, “DefaultColumnWidth”, null, “100”); //largura da coluna
writer.WriteAttributeString(“ss”, “DefaultRowHeight”, null, “13”); //altura da linha

string sql = “select * from [“ + tabela + “]”;

SqlDataAdapter da = new SqlDataAdapter(sql, cn);
DataTable dt=new DataTable();

da.Fill(dt);

//Criamos as colunas da planilha de acordo com as colunas da tabela.

foreach (DataColumn dc in dt.Columns)
{

writer.WriteStartElement(“Column”);
writer.WriteAttributeString(“ss”, “Index”, null, (dc.Ordinal + 1).ToString());
writer.WriteEndElement();

}

//Nomes dos campos
writer.WriteStartElement(“Row”);
writer.WriteAttributeString(“ss”, “StyleID”, null, “Bold”);

foreach (DataColumn dc in dt.Columns)
{

writer.WriteStartElement(“Cell”);

writer.WriteStartElement(“Data”);
writer.WriteAttributeString(“ss”, “Type”, null, “String”);
writer.WriteValue(dc.ColumnName);
writer.WriteEndElement();

writer.WriteEndElement();

}

writer.WriteEndElement();

//preenchemos a planilha com os dados da tabela
foreach (DataRow dr in dt.Rows)
{

writer.WriteStartElement(“Row”);

for (int x=0; x < dt.Columns.Count;x++)
{

writer.WriteStartElement(“Cell”);

writer.WriteStartElement(“Data”);
writer.WriteAttributeString(“ss”, “Type”, null, “String”);
writer.WriteValue(dr[x].ToString().Replace(“<", “<“).Replace(“>”, “>”));
writer.WriteEndElement();

writer.WriteEndElement();

}

writer.WriteEndElement();

}

writer.WriteEndElement();

writer.WriteEndElement();

}

}

writer.WriteEndElement();
writer.WriteEndDocument();

cn.Close();

Response.ClearContent();
Response.ClearHeaders();
Page.Controls.Clear(); //para não mandar “lixo” junto

Response.ContentType = “application/vnd.ms-excel”;
Response.AppendHeader(“content-disposition”,
“attachment; filename=northwind.xls”);

stm.Position = 0;
Response.Write(rdr.ReadToEnd()); //o resultado é o xml pronto para download.

}

Execute novamente o projeto, selecione algumas tabelas (ou todas) e clique no botão. A janela de download aparece:

Mandando abrir o arquivo, veja que o Excel mostrará corretamente, separado por planilhas:

Wagner disse:
0

Olá.
Nos meus testes aqui não tive problemas com esses caracteres.

No seu caso, talvez resolva trocando a linha:
XmlTextWriter writer = new XmlTextWriter(stm, Encoding.UTF8);

por:
XmlTextWriter writer = new XmlTextWriter(stm, Encoding.Unicode);

Mas está se referindo somente a acentos ou caracteres de algum idioma particular?

Chan disse:
0

Por acaso vc sabe como fazer caracteres especiais aparecerem corretamente ?