使用Interop方式快速写Excel

专门针对Excel2007的读写操作，首先想到的是OpenXml，可以搜了很久，只有一个ExcelPackage，用了之后发现缺胳膊少腿的，还有性能问题，所以还是只能换成老老实实的Interop的方式。但数据量比较大，怎么解决呢？

搜索了一下，使用Range的方式，先把数据保存在Object数组中，之后根据设定的大小一次性写入，速度的确提升很多

private static void RangeWriteToSheet(FileInfo excelFile,string sheetName, System.Data.DataTable dtResult)
        {
            object missing = System.Reflection.Missing.Value;
            Microsoft.Office.Interop.Excel.ApplicationClass appc = new Microsoft.Office.Interop.Excel.ApplicationClass();//lauch excel application
            Microsoft.Office.Interop.Excel.Range rangedata;
            Microsoft.Office.Interop.Excel.Workbook workbookData;
            Microsoft.Office.Interop.Excel.Worksheet worksheetData;

            try
            {
                workbookData = appc.Application.Workbooks.Open(excelFile.FullName, missing, false, missing, missing, missing, missing, missing, missing, true, missing, missing, missing, missing, missing);
                int sheetId = 0;
                for (int k = 1; k <= workbookData.Worksheets.Count; k++)
                {
                    worksheetData = (Microsoft.Office.Interop.Excel.Worksheet)workbookData.Worksheets.get_Item(k);
                    if (worksheetData.Name.Equals(sheetName))
                    {
                        sheetId = k;
                    }
                }

                worksheetData = (Microsoft.Office.Interop.Excel.Worksheet)workbookData.Worksheets.get_Item(sheetId);
                worksheetData.get_Range("A1", "H1").AutoFilter(1, missing, Excel.XlAutoFilterOperator.xlAnd, missing, true);
              
                
                rangedata = worksheetData.get_Range("A2", missing);
                Microsoft.Office.Interop.Excel.Range xlRang = null;
                int iEachSize = 2000;
                int iParstedRow = 0, iCurrSize = 0;
                int iColumnAccount = dtResult.Columns.Count;
                int iRowCount = dtResult.Rows.Count;
                object[,] objVal = new object[iEachSize, iColumnAccount];
                iCurrSize = iEachSize;
                while (iParstedRow < iRowCount)
                {
                    if ((iRowCount - iParstedRow) < iEachSize)
                        iCurrSize = iRowCount - iParstedRow;
                    for (int i = 0; i < iCurrSize; i++)
                    {
                        for (int j = 0; j < iColumnAccount; j++)
                            objVal[i, j] = dtResult.Rows[i + iParstedRow][j].ToString();
                    }
                    xlRang = worksheetData.get_Range("A" + ((int)(iParstedRow + 2)).ToString(), ((char)('A' + iColumnAccount - 1)).ToString() + ((int)(iParstedRow + iCurrSize + 1)).ToString());
                    xlRang.Value2 = objVal;
                    iParstedRow = iParstedRow + iCurrSize;
                }
                System.Runtime.InteropServices.Marshal.ReleaseComObject(xlRang);
                workbookData.Save();
                workbookData.Close(Type.Missing, Type.Missing, Type.Missing);
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                appc.Quit();
                appc = null;

            }
        }