C#高效率导出Excel

时间:2021-01-08 20:31:52

首先,需要引用excel的库:

C#高效率导出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();