sql导出Excel时，用数组的方式更快

 
#region sql数据库导出Excel
        public void Export(string tableName)
        {

            DataSet ds1 = sqlhelper.getds("select F_Num as '学 号',F_Name as '姓 名',F_Sex as '性 别',F_Dept as '所有系院' from " + tableName, tableName);
            if (ds1 == null)
            {
                MessageBox.Show("系统内部错误！请联系系统开发者！", "出错了：", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            string saveFileName = "";
            SaveFileDialog saveDialog = new SaveFileDialog();
            saveDialog.DefaultExt = "xlsx";
            saveDialog.Filter = "Excel文件|*.xlsx";
            saveDialog.FileName = "";
            saveDialog.ShowDialog();
            saveFileName = saveDialog.FileName;
            if (saveFileName.IndexOf(":") < 0)
            {
                MessageBox.Show("您取消了导出操作！", "提示信息：", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
                return;
            }

            Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
            object miss = System.Reflection.Missing.Value;

            if (xlApp == null)
            {
                MessageBox.Show("无法创建Excel对象，可能您的机子未安装Excel！", "出错了：", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }


            Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
            Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
            Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
            Microsoft.Office.Interop.Excel.Range range;


            //总行数和总列数
            long totalRowCount = ds1.Tables[0].Rows.Count;
            long totalColCount = ds1.Tables[0].Columns.Count;

            //写入第一行表头    
            for (int i = 0; i < totalColCount; i++)
            {
                worksheet.Cells[1, i + 1] = ds1.Tables[0].Columns[i].ColumnName;
                range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, i + 1];
                range.Interior.ColorIndex = 15;
                range.Font.Size = 15;//设定标题的大小
                range.Columns.AutoFit();//自动调整报表表格宽度为最适合宽度
                range.Font.Bold = true;
            }

            //创建对象数组存储DataTable的数据，这样的效率比直接将Datateble的数据填充worksheet.Cells[row,col]高
            //object[,] objDataHead = new object[1, totalColCount];//表头数据
            //object[,] objData = new object[dsData.Tables[t].Rows.Count, columnCount];//数据数据
            object[,] objData = new object[totalRowCount, totalColCount];//数据数据
            //写入数值  
            //把数组写入excel文件中


            for (int r = 0; r < totalRowCount; r++)
            {
                for (int i = 0; i < totalColCount; i++)
                {
                    objData[r, i] = ds1.Tables[0].Rows[r][i].ToString();
                    //如果把下面的两行代码放在这里，就会变得很慢，跟原来一个一个读的速率一样
                    //range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[r + 2, 1];//设置第一列为自动调整宽度
                    //range.Columns.AutoFit();
                }

            }
            //把数组写入excel文件中
            range = worksheet.get_Range(worksheet.Cells[2, 1], worksheet.Cells[ds1.Tables[0].Rows.Count + 1, ds1.Tables[0].Columns.Count]);
            range.Value2 = objData;


            //range = worksheet.get_Range(worksheet.Cells[rowCount + startRow + 1, 1], worksheet.Cells[rowCount + startRow + dsData.Tables[t].Rows.Count, columnCount]);
            //设置数据区域的边框及列宽自适应
            worksheet.get_Range(worksheet.Cells[2, 1], worksheet.Cells[ds1.Tables[0].Rows.Count + 1, ds1.Tables[0].Columns.Count]).Cells.Borders.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;//设置边框
            worksheet.get_Range(worksheet.Cells[2, 1], worksheet.Cells[ds1.Tables[0].Rows.Count + 1, 1]).Columns.AutoFit();//设置单元格宽度为自适应

            if (totalRowCount > 0)
            {
                range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].ColorIndex = Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic;
                range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
                range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;

            }

            if (totalColCount > 1)
            {
                range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].ColorIndex = Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic;
                range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
                range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;

            }

            if (range != null)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(range);
                range = null;
            }

            if (worksheet != null)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet);
                worksheet = null;
            }
            if (saveFileName != "")
            {
                try
                {
                    workbook.Saved = true;
                    System.Reflection.Missing missing = System.Reflection.Missing.Value;
                    //workbook.SaveAs(saveFileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, miss, miss, miss, miss,
                    //Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, miss, miss, miss, miss, miss);//Eecel2003格式
                    //workbook.SaveAs(saveFileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlAddIn,
                    //    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                    workbook.SaveCopyAs(saveFileName);//Excel2007以复制的形式保存在已有的文档里
                    MessageBox.Show("导出成功！", "提示信息：", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
                }
                catch (Exception ex)
                {
                    MessageBox.Show("导出文件错误！请重试！", "出错了：", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }

            }

            if (workbook != null)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
                workbook = null;

            }

            if (workbooks != null)
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(workbooks);
                workbooks = null;

            }

            xlApp.Application.Workbooks.Close();

            xlApp.Quit();
            GC.Collect();
        }