Exportar os dados da pesquisa SQL para o Excel
Tenho uma pergunta que devolve um conjunto de dados muito grande. Não posso copiá-lo e colá-lo no Excel, o que costumo fazer. Tenho feito algumas pesquisas sobre como exportar diretamente para uma folha do Excel. Estou executando o SQL SERVER 2008 em um servidor executando o Microsoft Server 2003. Estou a tentar usar a Microsoft.Jacto.OLEDB.4.0 data provider e Excel 2007. Juntei um pequeno pedaço de código que se parece com isto pelo que vi em exemplos.
INSERT INTO OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C:\Working\Book1.xlsx;Extended Properties=EXCEL 12.0;HDR=YES')
SELECT productid, price FROM dbo.product
No entanto isto é não está funcionando, estou recebendo uma mensagem de erro dizendo
Alguém tem alguma ideia de como fazer isto ou, possivelmente, uma abordagem melhor?"sintaxe incorrecta perto da palavra-chave 'SELECT'".
5 answers
Não sei se é isto que procuras, mas podes exportar os resultados para o Excel assim:
Na área de resultados, carregue na célula superior-esquerda para realçar todos os registos e, em seguida, carregue com o botão direito na célula superior-esquerda e carregue em "gravar os resultados como". Uma das opções de exportação é a CSV.
Podes tentar também.INSERT INTO OPENROWSET
('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=c:\Test.xls;','SELECT productid, price FROM dbo.product')
Por último, pode procurar usar o SSIS (DTS substituído) para a exportação de dados. Aqui está um link para um tutorial:
Http://www.accelebrate.com/sql_training/ssis_2008_tutorial.htm
== actualizar #1 ==
Para gravar o resultado como ficheiro CSV com cabeçalhos de colunas, pode-se seguir os passos abaixo:
- ir para Ferramentas - >Opções
- resultados da pesquisa - >servidor SQL - >resultados da grelha
- verificar "incluir os cabeçalhos das colunas ao copiar ou gravar os resultados"
- Clique em OK.
- Note que a nova configuração não irá afectar nenhuma pesquisa existente tabs-terá de abrir novos e/ou reiniciar o SSMS.
Se só precisa de exportar para o excel, pode usar o Assistente de exportação de dados. Carregue com o botão direito na base de dados, Tarefas->exportar os dados.
Eu tive um problema semelhante, mas com uma reviravolta - as soluções listadas acima funcionaram quando o resultado foi de uma consulta, mas na minha situação, eu tive várias consultas individuais selecionadas para as quais eu precisava de resultados para serem exportados para o Excel. Abaixo está apenas um exemplo para ilustrar, embora eu pudesse fazer uma cláusula name in
...
select a,b from Table_A where name = 'x'
select a,b from Table_A where name = 'y'
select a,b from Table_A where name = 'z'
O assistente estava a deixar-me exportar o resultado de uma consulta para o excel, mas nem todos os resultados de diferentes consultas neste caso.
Quando pesquisei, eu ... descobrimos que podíamos desactivar os resultados para a grelha e activar os resultados para o texto. Então, carregue em Ctrl + T e execute todas as declarações. Isto deve mostrar os resultados como um arquivo de texto na janela de saída. Você pode manipular o texto para um formato delimitado por uma página para importar no Excel.Você também poderia carregar em Ctrl + Shift + F para exportar os resultados para um ficheiro - ele exporta como A.arquivo rpt que pode ser aberto usando um editor de texto e manipulado para a importação do excel.
Espero que isto ajude qualquer um. outros têm um problema semelhante.Para quem vem aqui à procura de Como fazer isto em C#{12]}, eu tentei o seguinte método e tive sucesso em {[[4]} e entity framework core 2.0.3
public class User
{
public string Name { get; set; }
public int Address { get; set; }
public int ZIP { get; set; }
public string Gender { get; set; }
}
Depois instalar O pacote EPPlus Nuget . (I used version 4.0.5, probably will work for other versions as well.)
Install-Package EPPlus -Version 4.0.5
A classe create ExcelExportHelper
, que conterá a lógica para converter o conjunto de dados em linhas do Excel. Esta classe não tem dependências com a sua classe ou conjunto de dados modelo.
public class ExcelExportHelper
{
public static string ExcelContentType
{
get
{ return "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; }
}
public static DataTable ListToDataTable<T>(List<T> data)
{
PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(typeof(T));
DataTable dataTable = new DataTable();
for (int i = 0; i < properties.Count; i++)
{
PropertyDescriptor property = properties[i];
dataTable.Columns.Add(property.Name, Nullable.GetUnderlyingType(property.PropertyType) ?? property.PropertyType);
}
object[] values = new object[properties.Count];
foreach (T item in data)
{
for (int i = 0; i < values.Length; i++)
{
values[i] = properties[i].GetValue(item);
}
dataTable.Rows.Add(values);
}
return dataTable;
}
public static byte[] ExportExcel(DataTable dataTable, string heading = "", bool showSrNo = false, params string[] columnsToTake)
{
byte[] result = null;
using (ExcelPackage package = new ExcelPackage())
{
ExcelWorksheet workSheet = package.Workbook.Worksheets.Add(String.Format("{0} Data", heading));
int startRowFrom = String.IsNullOrEmpty(heading) ? 1 : 3;
if (showSrNo)
{
DataColumn dataColumn = dataTable.Columns.Add("#", typeof(int));
dataColumn.SetOrdinal(0);
int index = 1;
foreach (DataRow item in dataTable.Rows)
{
item[0] = index;
index++;
}
}
// add the content into the Excel file
workSheet.Cells["A" + startRowFrom].LoadFromDataTable(dataTable, true);
// autofit width of cells with small content
int columnIndex = 1;
foreach (DataColumn column in dataTable.Columns)
{
int maxLength;
ExcelRange columnCells = workSheet.Cells[workSheet.Dimension.Start.Row, columnIndex, workSheet.Dimension.End.Row, columnIndex];
try
{
maxLength = columnCells.Max(cell => cell.Value.ToString().Count());
}
catch (Exception) //nishanc
{
maxLength = columnCells.Max(cell => (cell.Value +"").ToString().Length);
}
//workSheet.Column(columnIndex).AutoFit();
if (maxLength < 150)
{
//workSheet.Column(columnIndex).AutoFit();
}
columnIndex++;
}
// format header - bold, yellow on black
using (ExcelRange r = workSheet.Cells[startRowFrom, 1, startRowFrom, dataTable.Columns.Count])
{
r.Style.Font.Color.SetColor(System.Drawing.Color.White);
r.Style.Font.Bold = true;
r.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
r.Style.Fill.BackgroundColor.SetColor(Color.Brown);
}
// format cells - add borders
using (ExcelRange r = workSheet.Cells[startRowFrom + 1, 1, startRowFrom + dataTable.Rows.Count, dataTable.Columns.Count])
{
r.Style.Border.Top.Style = ExcelBorderStyle.Thin;
r.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
r.Style.Border.Left.Style = ExcelBorderStyle.Thin;
r.Style.Border.Right.Style = ExcelBorderStyle.Thin;
r.Style.Border.Top.Color.SetColor(System.Drawing.Color.Black);
r.Style.Border.Bottom.Color.SetColor(System.Drawing.Color.Black);
r.Style.Border.Left.Color.SetColor(System.Drawing.Color.Black);
r.Style.Border.Right.Color.SetColor(System.Drawing.Color.Black);
}
// removed ignored columns
for (int i = dataTable.Columns.Count - 1; i >= 0; i--)
{
if (i == 0 && showSrNo)
{
continue;
}
if (!columnsToTake.Contains(dataTable.Columns[i].ColumnName))
{
workSheet.DeleteColumn(i + 1);
}
}
if (!String.IsNullOrEmpty(heading))
{
workSheet.Cells["A1"].Value = heading;
// workSheet.Cells["A1"].Style.Font.Size = 20;
workSheet.InsertColumn(1, 1);
workSheet.InsertRow(1, 1);
workSheet.Column(1).Width = 10;
}
result = package.GetAsByteArray();
}
return result;
}
public static byte[] ExportExcel<T>(List<T> data, string Heading = "", bool showSlno = false, params string[] ColumnsToTake)
{
return ExportExcel(ListToDataTable<T>(data), Heading, showSlno, ColumnsToTake);
}
}
Agora adicione este método onde você deseja gerar o arquivo excel, provavelmente para um método no controlador. Você pode passar parâmetros para o seu procedimento armazenado também. Note que o tipo de retorno do método é FileContentResult
. Seja qual for a consulta que execute, o importante é você deve ter os resultados em um List
.
[HttpPost]
public async Task<FileContentResult> Create([Bind("Id,StartDate,EndDate")] GetReport getReport)
{
DateTime startDate = getReport.StartDate;
DateTime endDate = getReport.EndDate;
// call the stored procedure and store dataset in a List.
List<User> users = _context.Reports.FromSql("exec dbo.SP_GetEmpReport @start={0}, @end={1}", startDate, endDate).ToList();
//set custome column names
string[] columns = { "Name", "Address", "ZIP", "Gender"};
byte[] filecontent = ExcelExportHelper.ExportExcel(users, "Users", true, columns);
// set file name.
return File(filecontent, ExcelExportHelper.ExcelContentType, "Report.xlsx");
}
Mais detalhes podem ser encontrados aqui
Vejo que está a tentar exportar dados SQL para o Excel para evitar copiar o seu conjunto de dados muito grande para o Excel.
Você pode estar interessado em aprender como exportar dados SQL para o Excel e atualizar a exportação automaticamente (com qualquer banco de dados SQL: MySQL, Microsoft SQL Server, PostgreSQL).
Para exportar dados de SQL para o Excel, você precisa seguir 2 passos:
- Passo 1: Ligar o Excel à sua base de dados SQL (Microsoft SQL Server, MySQL, PostgreSQL...)
- Passo 2: Importar os seus dados SQL para o Excel
O resultado será a lista de tabelas que deseja consultar os dados da sua base de dados SQL no Excel:

Passo 1: Ligar o Excel a uma fonte de dados externa: a sua base de dados SQL
- instalar um ODBC
- Instale Um Controlador
- Evite Um Erro Comum
- criar um DSN
Passo 2: importar os seus dados SQL para o Excel
- Clique Onde Quer A Sua Tabela De Pivô
- Clique Inserir
- Clique Na Tabela Pivot
- Carregue em usar uma fonte de dados externa, e depois escolha a ligação
- carregue na página DSN do sistema
- Seleccione o DSN criado no Gestor ODBC
- preencha o utilizador e a senha solicitados
- evite um erro comum
- Acesse A Janela De Consulta Da Microsoft
- carregue na seta para ver a lista de tabelas na sua base de dados
- seleccione a tabela que deseja consultar os dados da sua base de dados SQL no Excel
- clique em Devolve os dados quando terminares a tua selecção
Para actualizar automaticamente a exportação, existem 2 passos adicionais:
- criar uma tabela Pivot com uma fonte externa de dados SQL
- automatize a sua actualização de Dados SQL no Excel com a função GETPIVOTDATA