SQL 쿼리 데이터를 Excel로 내보내기
매우 큰 데이터 세트를 반환하는 쿼리가 있습니다.평소처럼 복사해서 엑셀에 붙여넣을 수 없습니다.엑셀 시트로 직접 수출하는 방법에 대해 조사를 하고 있습니다.Microsoft Server 2003을 실행하는 서버에서 SQL SERVER 2008을 실행하고 있습니다.Microsoft 를 사용하려고 합니다.Jet.OLEDB.4.0 데이터 프로바이더 및 Excel 2007.예를 들어 본 코드의 작은 조각을 조합했습니다.
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
그러나 이것은 동작하지 않고, 다음과 같은 에러 메세지가 표시됩니다.
키워드 'SELECT' 근처에 잘못된 구문이 있습니다.
이 방법이나 더 나은 접근법에 대해 알고 있는 사람이 있습니까?
이것이 당신이 원하는 것인지 모르겠지만, 다음과 같이 결과를 Excel로 내보낼 수 있습니다.
결과 영역에서 왼쪽 위 셀을 눌러 모든 레코드를 강조 표시한 후 왼쪽 위 셀을 마우스 오른쪽 버튼으로 누르고 "다른 이름으로 결과 저장"을 누르십시오.내보내기 옵션 중 하나는 CSV입니다.
이것도 시도해 보세요.
INSERT INTO OPENROWSET
('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=c:\Test.xls;','SELECT productid, price FROM dbo.product')
마지막으로 데이터 내보내기에 SSIS(대체된 DTS)를 사용하는 방법을 검토할 수 있습니다.다음은 튜토리얼 링크입니다.
http://www.accelebrate.com/sql_training/ssis_2008_tutorial.htm
== 업데이트 #1 ==
결과를 컬럼 헤더가 있는 CSV 파일로 저장하려면 다음 절차를 수행합니다.
- [툴(Tools)]-> [옵션(Options)]으로 이동합니다.
- 쿼리 결과 -> SQL Server -> 그리드에 대한 결과
- "결과 복사 또는 저장 시 열 머리글 포함" 선택
- [확인] 을 클릭합니다.
- 새 설정은 기존 [Query]탭에는 영향을 주지 않습니다.새 설정을 열거나 SSMS를 재시작해야 합니다.
Excel로 내보내야 하는 경우 데이터 내보내기 마법사를 사용할 수 있습니다.데이터베이스 오른쪽 버튼 [태스크]-> [데이터 내보내기]을 클릭합니다.
비슷한 문제가 있었습니다만, 반전이 있었습니다.상기의 솔루션은, 결과 세트가 1개의 쿼리에서 나왔을 때에 동작했습니다만, 제 상황에서는, 결과를 Excel로 export 할 필요가 있는 개별 선택 쿼리가 복수 있었습니다.를 들어 하겠습니다.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'
마법사에서 하나의 쿼리에서 결과를 Excel로 내보낼 수 있었지만 이 경우 다른 쿼리에서 모든 결과를 내보내는 것은 아니었습니다.
조사해보니 결과를 그리드로 비활성화하고 결과를 텍스트로 활성화할 수 있더군요.따라서 Ctrl + T를 누른 다음 모든 문을 실행합니다.출력창에 결과가 텍스트파일로 표시됩니다.텍스트를 탭으로 구분된 형식으로 조작하여 Excel로 가져올 수 있습니다.
Ctrl + Shift + F를 눌러 결과를 파일로 내보낼 수도 있습니다. 텍스트 편집기를 사용하여 열고 Excel 가져오기를 위해 조작할 수 있는 .rpt 파일로 내보냅니다.
이것이 비슷한 문제를 안고 있는 다른 사람들에게 도움이 되기를 바랍니다.
C#에서 이 작업을 수행하는 방법을 찾고 계신 분들을 위해 다음과 같은 방법을 시도하여 성공하였습니다.dotnet core 2.0.3 ★★★★★★★★★★★★★★★★★」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; }
}
그런 다음 EPplus Nuget 패키지를 설치합니다.(버전 4.0.5를 사용했는데 다른 버전에서도 사용할 수 있을 것 같습니다.)
Install-Package EPPlus -Version 4.0.5
.ExcelExportHelper클래스 - 데이터 집합을 Excel 행으로 변환하는 논리를 포함합니다.이 클래스는 모델 클래스 또는 데이터 집합과 종속성이 없습니다.
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);
}
}
이 메서드를 Excel 파일을 생성하는 장소에 추가합니다(컨트롤러 내의 메서드일 가능성이 있습니다).저장 프로시저의 파라미터도 전달할 수 있습니다.메서드의 반환 유형은 입니다.어떤 쿼리를 실행하든 중요한 것은 에서 결과를 얻을 수 있어야 한다는 것입니다.
[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");
}
자세한 내용은 여기를 참조하십시오.
매우 큰 데이터 세트를 Excel에 복사 붙여넣기를 피하기 위해 SQL 데이터를 Excel로 내보내려는 것입니다.
SQL 데이터를 Excel로 내보내고 SQL 데이터베이스를 사용하여 내보내기를 자동으로 업데이트하는 방법에 관심이 있을 수 있습니다.MySQL, Microsoft SQL Server, PostgreSQL).
SQL에서 Excel로 데이터를 내보내려면 다음 두 단계를 수행해야 합니다.
- 순서 1: SQL 데이터베이스(Microsoft SQL Server, MySQL, PostgreSQL...)에 Excel을 연결합니다.
- 순서 2: SQL 데이터를 Excel로 Import합니다.
그 결과 SQL 데이터베이스에서 Excel로 데이터를 쿼리하는 테이블 목록이 나타납니다.

순서 1: Excel을 외부 데이터 소스인 SQL 데이터베이스에 연결합니다.
- ODBC 설치
- 드라이버의 인스톨
- 일반적인 에러 회피
- DSN 작성
순서 2: SQL 데이터를 Excel로 Import합니다.
- 피벗 테이블이 필요한 곳을 클릭합니다.
- [삽입]을 클릭합니다.
- [피벗 테이블]
- 외부 데이터 원본 사용을 클릭한 다음 연결 선택을 클릭합니다.
- 시스템 DSN 탭을 클릭합니다.
- ODBC Manager에서 생성된 DSN 선택
- 요청된 사용자 이름과 비밀번호를 입력합니다.
- 일반적인 에러 회피
- [ Access the Microsoft Query ]대화 상자
- 화살표를 클릭하여 데이터베이스 내의 테이블 목록을 표시합니다.
- SQL 데이터베이스의 데이터를 Excel로 쿼리할 테이블을 선택합니다.
- 선택을 마치면 Return Data(데이터 반환)를 클릭합니다.
내보내기를 자동으로 업데이트하려면 다음 두 가지 추가 단계가 있습니다.
- 외부 SQL 데이터 원본이 있는 피벗 테이블 생성
- GETPIVOTDATA 기능으로 Excel에서 SQL 데이터 업데이트 자동화
Excel을 SQL에 연결하는 것부터 자동으로 업데이트되는 것까지 모든 프로세스에 대한 단계별 튜토리얼을 만들었습니다.자세한 설명과 스크린샷이 도움이 될 수 있습니다.
언급URL : https://stackoverflow.com/questions/7389637/export-sql-query-data-to-excel
'programing' 카테고리의 다른 글
| 긴 형식의 printf 변환 지정자는 무엇입니까? (0) | 2023.04.15 |
|---|---|
| Bash에서 변수에 유전 값을 할당하려면 어떻게 해야 합니까? (0) | 2023.04.15 |
| 탐색 모음의 UIBarButtonItem을 프로그래밍 방식으로 표시하시겠습니까? (0) | 2023.04.15 |
| Index Range Swift의 새로운 어레이 (0) | 2023.04.15 |
| 열의 Excel VBA 루프 (0) | 2023.04.15 |