데이터 테이블을 Excel 워크시트에 덤프하는 속도를 높이는 방법은 무엇입니까?
데이터 테이블을 Excel 워크시트에 덤프하는 다음 루틴이 있습니다.
private void RenderDataTableOnXlSheet(DataTable dt, Excel.Worksheet xlWk,
string [] columnNames, string [] fieldNames)
{
// render the column names (e.g. headers)
for (int i = 0; i < columnNames.Length; i++)
xlWk.Cells[1, i + 1] = columnNames[i];
// render the data
for (int i = 0; i < fieldNames.Length; i++)
{
for (int j = 0; j < dt.Rows.Count; j++)
{
xlWk.Cells[j + 2, i + 1] = dt.Rows[j][fieldNames[i]].ToString();
}
}
}
이유가 무엇이든 간에, 비교적 최신 PC에서 25개의 열과 400개의 행으로 구성된 DataTable을 덤프하는 데는 약 10-15초가 걸립니다.훨씬 더 긴 테스터 기계가 필요합니다.
이 코드를 빠르게 하기 위해 제가 할 수 있는 일이 있나요?아니면 인터럽트는 본질적으로 느릴 뿐입니까?
해결책: 헬렌 투믹의 제안에 따라, 저는 방법을 수정했고 이제 몇 가지 일반적인 데이터 유형(int32, double, datetime, string)에 대해 작동할 것입니다.얼마든지 연장해 주세요.데이터 세트 처리 속도가 15초에서 1초 미만으로 떨어졌습니다.
private void RenderDataTableOnXlSheet(DataTable dt, Excel.Worksheet xlWk, string [] columnNames, string [] fieldNames)
{
Excel.Range rngExcel = null;
Excel.Range headerRange = null;
try
{
// render the column names (e.g. headers)
for (int i = 0; i < columnNames.Length; i++)
xlWk.Cells[1, i + 1] = columnNames[i];
// for each column, create an array and set the array
// to the excel range for that column.
for (int i = 0; i < fieldNames.Length; i++)
{
string[,] clnDataString = new string[dt.Rows.Count, 1];
int[,] clnDataInt = new int[dt.Rows.Count, 1];
double[,] clnDataDouble = new double[dt.Rows.Count, 1];
string columnLetter = char.ConvertFromUtf32("A".ToCharArray()[0] + i);
rngExcel = xlWk.get_Range(columnLetter + "2", Missing.Value);
rngExcel = rngExcel.get_Resize(dt.Rows.Count, 1);
string dataTypeName = dt.Columns[fieldNames[i]].DataType.Name;
for (int j = 0; j < dt.Rows.Count; j++)
{
if (fieldNames[i].Length > 0)
{
switch (dataTypeName)
{
case "Int32":
clnDataInt[j, 0] = Convert.ToInt32(dt.Rows[j][fieldNames[i]]);
break;
case "Double":
clnDataDouble[j, 0] = Convert.ToDouble(dt.Rows[j][fieldNames[i]]);
break;
case "DateTime":
if (fieldNames[i].ToLower().Contains("time"))
clnDataString[j, 0] = Convert.ToDateTime(dt.Rows[j][fieldNames[i]]).ToShortTimeString();
else if (fieldNames[i].ToLower().Contains("date"))
clnDataString[j, 0] = Convert.ToDateTime(dt.Rows[j][fieldNames[i]]).ToShortDateString();
else
clnDataString[j, 0] = Convert.ToDateTime(dt.Rows[j][fieldNames[i]]).ToString();
break;
default:
clnDataString[j, 0] = dt.Rows[j][fieldNames[i]].ToString();
break;
}
}
else
clnDataString[j, 0] = string.Empty;
}
// set values in the sheet wholesale.
if (dataTypeName == "Int32")
rngExcel.set_Value(Missing.Value, clnDataInt);
else if (dataTypeName == "Double")
rngExcel.set_Value(Missing.Value, clnDataDouble);
else
rngExcel.set_Value(Missing.Value, clnDataString);
}
// figure out the letter of the last column (supports 1 letter column names)
string lastColumn = char.ConvertFromUtf32("A".ToCharArray()[0] + columnNames.Length - 1);
// make the header range bold
headerRange = xlWk.get_Range("A1", lastColumn + "1");
headerRange.Font.Bold = true;
// autofit for better view
xlWk.Columns.AutoFit();
}
finally
{
ReleaseObject(headerRange);
ReleaseObject(rngExcel);
}
}
private void ReleaseObject(object obj)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
obj = null;
}
catch
{
obj = null;
}
finally
{
GC.Collect();
}
}
셀 값을 하나씩 설정하는 대신 일괄적으로 설정합니다.
1단계. 데이터 테이블의 데이터를 동일한 차원의 배열로 전송합니다.
2단계. 적절한 범위에 걸쳐 있는 Excel Range 개체를 정의합니다.
3단계. 범위를 설정합니다.배열의 값입니다.
셀당 두 개(셀 가져오기, 값 설정)가 아닌 Interop 경계를 통해 총 두 개의 호출(하나는 Range 개체를 가져오기 위해, 하나는 값을 설정하기 위해)이 발생하기 때문에 훨씬 더 빨라집니다.
MSDN KB 문서 302096에 샘플 코드가 있습니다.
인터럽트는 본질적으로 매우 느립니다.각 통화와 관련된 오버헤드가 큽니다.속도를 높이려면 데이터의 개체 배열을 하나의 할당 문에 셀 범위에 다시 기록해 보십시오.
또는 이것이 심각한 문제라면 XLL 인터페이스를 통해 관리 코드를 사용하여 데이터를 읽고 쓸 수 있는 관리 코드 엑셀 확장 중 하나를 사용해 보십시오. (Addin Express, Managed XLL 등)
레코드 집합이 있는 경우 Excel에 쓰는 가장 빠른 방법은 CopyFromRecordset입니다.
COM 자동화 경로를 가기 위한 특별한 요구 사항이 있습니까?그렇지 않으면 몇 가지 다른 옵션이 있습니다.
OLEDB 공급자를 사용하여 Excel 파일 생성/쓰기
http://support.microsoft.com/kb/316934타사 라이브러리를 사용하여 Excel에 씁니다.라이센스 요구 사항에 따라 몇 가지 옵션이 있습니다.업데이트:좋은 무료 라이브러리는 NPOI http://npoi.codeplex.com/ 입니다.
데이터를 csv 파일에 쓰고 Excel에 로드합니다.
Excel에 로드할 수 있는 XML로 데이터를 작성합니다.
Interop에는 CopyFromRecordset이라는 가장 빠른 메서드가 있지만 ADODB 라이브러리를 사용해야 합니다.
확실히 가장 빠른 방법이며 몇 가지 시도해 보았습니다.사용하기는 쉽지 않지만 속도는 놀라울 정도입니다.
https://learn.microsoft.com/en-us/office/vba/api/excel.range.copyfromrecordset
짧은 샘플:
using ADODB;
using Microsoft.Office.Interop;
//--- datatable --- already exists
DataTable dt_data = new DataTable();
//--- or your dt code is here ..........
//--- mine has 3 columns ------
//--- code to populate ADO rs with DataTable data --- nothing special
//--- create empty rs .....
ADODB.Recordset rs = new ADODB.Recordset();
rs.CursorType = CursorTypeEnum.adOpenKeyset;
rs.CursorLocation = CursorLocationEnum.adUseClient;
rs.LockType = LockTypeEnum.adLockOptimistic;
rs.Fields.Append("employee_id",DataTypeEnum.adBSTR,255,FieldAttributeEnum.adFldIsNullable);
rs.Fields.Append("full_name", DataTypeEnum.adBSTR, 255, FieldAttributeEnum.adFldIsNullable);
rs.Fields.Append("start_date", DataTypeEnum.adBSTR, 10, FieldAttributeEnum.adFldIsNullable);
rs.Open();
//--- populate ADO rs with DataTable data ----
for (int i = 0; i < dt_data.Rows.Count; i++)
{
rs.AddNew();
rs.Fields["employee_id"].Value = dt_data.Rows[i]["employee_id"].ToString();
rs.Fields["full_name"].Value = dt_data.Rows[i]["full_name"].ToString();
//--- if date is empty......
if (dt_data.Rows[i]["start_date"].ToString().Length > 0)
{
rs.Fields["start_date"].Value = dt_data.Rows[i]["start_date"].ToString();
}
rs.Update();
}
Microsoft.Office.Interop.Excel.Application xlexcel;
Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlexcel = new Microsoft.Office.Interop.Excel.Application();
xlexcel.Visible = true;
xlWorkBook = xlexcel.Workbooks.Add(misValue);
xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
//--- populate columns from rs --
for (int i = 0; i < rs.Fields.Count; i++)
{
xlWorkSheet.Cells[1, i + 1] = rs.Fields[i].Name.ToString();
};
//----- .CopyFromRecordset method -- (rs object, MaxRows, MaxColumns) --- in this case 3 columns but it can 1,2,3 etc ------
xlWorkSheet.Cells[2, 1].CopyFromRecordset(CloneFilteredRecordset(rs), rs.RecordCount, 3);
당신은 당신의 모든 db 헤비 리프팅을 하기 위해 VBA 코드와 함께 Excel 추가 기능을 만들 수 있습니다.부터.NET에서는 Excel을 인스턴스화하고 추가 기능을 추가한 다음 Excel VBA 루틴을 호출하여 SQL 문을 실행하는 데 필요한 매개 변수를 전달하기만 하면 됩니다.
찰스의 말에 동의합니다.인터럽트는 정말 느립니다.하지만 사용해 보십시오.
private void RenderDataTableOnXlSheet(DataTable dt, Excel.Worksheet xlWk,
string [] columnNames, string [] fieldNames)
{
// render the column names (e.g. headers)
int columnLength = columnNames.Length;
for (int i = 0; i < columnLength; i++)
xlWk.Cells[1, i + 1] = columnNames[i];
// render the data
int fieldLength = fieldNames.Length;
int rowCount = dt.Rows.Count;
for (int j = 0; j < rowCount; j++)
{
for (int i = 0; i < fieldLength; i++)
{
xlWk.Cells[j + 2, i + 1] = dt.Rows[j][fieldNames[i]].ToString();
}
}
}
HTH
언급URL : https://stackoverflow.com/questions/2692979/how-to-speed-up-dumping-a-datatable-into-an-excel-worksheet
'programing' 카테고리의 다른 글
| Visual Studio Code의 나란히 있는 파일에서 'git diff'를 보려면 어떻게 해야 합니까? (0) | 2023.08.15 |
|---|---|
| 안드로이드에서 텍스트 보기에 새 줄을 추가하려면 어떻게 해야 합니까? (0) | 2023.08.15 |
| mysql 테이블에서 열 크기를 가져오는 방법 (0) | 2023.08.15 |
| '@angular/common/http' 모듈을 찾을 수 없습니다. (0) | 2023.08.15 |
| jQuery를 사용하지 않고 rails-ujs Rails.ajax POST 호출로 JSON 데이터를 어떻게 전송합니까? (0) | 2023.07.26 |