C#高效率匯出Excel

標籤：

首先，需要引用excel的庫：

 

Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();

Workbooks books = excel.Workbooks;
Workbook xlbook = books.Add(true);
Worksheet xlsheet = (Worksheet)xlbook.ActiveSheet;

xlsheet.Name = "DataEdit";
var fileName = "test.xlsx";

var rownum = 10; //excel行數
var colnum =10; //excel列數
Array arr = Array.CreateInstance(typeof(string), rownum, colnum);//先將屬性資料緩衝到Array數組中

int i = 0;
foreach (var proInfo in listProperties)
{
　　arr.SetValue(proInfo, 0, i);
　　for (var index = 0; index < listProperties.Count; index++)
　　{
　　　　string xsheetValue = "";
　　　　if (listProperties[index].ContainsKey(proInfo))
　　　　{
　　　　　　xsheetValue = listProperties[index][proInfo];
　　　　}
　　　　arr.SetValue(xsheetValue, index + 1, i);
　　}
　　i++;
}

//一次性寫入到excel的sheet中，減少對excel檔案的I/O操作次數，提高效率
Range range = xlsheet.Range[xlsheet.Cells[1, 1], xlsheet.Cells[rownum, colnum]];
range.Value2 = arr;

//excel表頭添加背景色
Range rangeHeader = xlsheet.Range[xlsheet.Cells[1, 1], xlsheet.Cells[1, colnum]];
rangeHeader.Interior.Color = System.Drawing.Color.GreenYellow;

excel.Columns.AutoFit();
xlbook.Saved = true;
xlbook.SaveCopyAs("D:\\" + fileName);

excel.Quit();

 

C#高效率匯出Excel