C# 导出Excel

using System;ITPUB个人空间@b
q{f
using System.IO;
;nvJ|(z"s0using Excel;

t4}Lt"Mf"F(E0namespace Kits.ExcelITPUB个人空间"s+aL$}cy?+K
{ITPUB个人空间7N0y F.M9yoP.xD/}1g
    public class ExcelToolsITPUB个人空间;QW2L!`%IKvD`
    {ITPUB个人空间Z3v.Q-f$fqDPL#G
        /// <summary>
+SH3v K$l-e9W0  /// 导出到EXCEL表ITPUB个人空间?Ht/bJ9Y5S
  /// </summary>ITPUB个人空间yn)_0/Ix2/'e
        private static void toExcel(string FileName, string SheetName, System.Data.DataTable dt)ITPUB个人空间-x%N[R8f
  {ITPUB个人空间H!V]6d?0^
   ApplicationClass myExcel = new ApplicationClass();
K&}&[6~8NA9S$/0   Worksheet worksheet = null;

   string curPath = Directory.GetParent(FileName).FullName;
d_"K1sB;e)}0   if(!Directory.Exists(curPath))ITPUB个人空间,r1W_i*[j
   {ITPUB个人空间1sM:DlQ d#z$~:AG
    Directory.CreateDirectory(curPath);ITPUB个人空间b'E;B6{Y}bF
   }

   if (File.Exists(FileName))
BB$/-oew�`b0   {
qy.N0ir]!f)q0    try
3Uj{c:y0    {ITPUB个人空间0?0]1D3^*C5ulDjz9H
     File.Delete(FileName);ITPUB个人空间~ws6OD�b
    }
,s)c4~_;w4|0    catchITPUB个人空间;} d7s%o!l@i
    {
9R,C'g~'^0B'f"XXA0     throw new Exception("文件操作出错，可能正在运行当前的EXCEL文件！");ITPUB个人空间@h;e}�s!n5nM+F%K~
    }ITPUB个人空间Dqc3fK_oH;Qw
   }

   tryITPUB个人空间a4t|'W)@Eiia
   {ITPUB个人空间!vIx$DO
    object missing = System.Reflection.Missing.Value;

    myExcel.Workbooks.Add(missing);ITPUB个人空间p8b!M3@bZ:N(P[P
    worksheet = (Worksheet)myExcel.ActiveSheet;ITPUB个人空间pyHJ&Bt1C
    worksheet.Name = SheetName;ITPUB个人空间Sol+v:w
    myExcel.Visible = false;ITPUB个人空间Mi2C-]F+|

    int iRows = dt.Rows.Count;ITPUB个人空间YK.j8f"MuqC
    int iCol = dt.Columns.Count;

    worksheet.get_Range( worksheet.Cells[1, 1], worksheet.Cells[ iRows+1, iCol] ).NumberFormatLocal = "@";

    for( int i=0; i<iCol; i++)ITPUB个人空间;_
p3g.RcU/u`z
    {ITPUB个人空间.]*yI;~4okY*Z
N
     worksheet.Cells[1,i+1] = dt.Columns[i].ToString();ITPUB个人空间KVpCv b{
    }

    int size = iRows / 1000;

zSP X9s:td0K0    int page = 0;
ga)qE9l2}z0    string[,] rows = null;
-T+QM+Py/X0    for(int i=0; i<iRows; )ITPUB个人空间i-eF,X|?c
    {    
y:iZ*`0Fc"hj']p-g0     if(page<size)
j"id"dU4b0     {ITPUB个人空间tfPgo/c&e4C]-~m
      rows = new string[1000,iCol];ITPUB个人空间.IZ7/1G8i�Cc
      for(int count=0;count<1000;count++)
*tGKu3K!C:/+u/]0      {
8?&r^/EK t*{0       for(int j=0; j<iCol; j++)ITPUB个人空间QLD*Z4Fp
       {ITPUB个人空间"S
[4W&m!A
u9T
        rows[count,j] = dt.DefaultView[i][j].ToString();ITPUB个人空间C M3@ P4PwN
       }ITPUB个人空间+F9J:N/Cd4c�N
       i++;
7b0I�O3//c0      }ITPUB个人空间@o4C.Xxg2i
      worksheet.get_Range(worksheet.Cells[page * 1000 + 2, 1], worksheet.Cells[ page * 1000 + 1001, iCol]).Value2 = rows;
OR"u!O3F0      page++;
lb!P1Y!LP~Yi0     }ITPUB个人空间+TY^3Y
Vq
     else
rB%nIC%au0     {ITPUB个人空间OGiP]J
      int left = iRows % 1000;
Qd!r!BQ8[n(gp5SU0      rows = new string[left,iCol];ITPUB个人空间fz(I#m.XE
      for(int count=0;count<left;count++)
"yQsQX+Z0      {
/tcof!i0       for(int j=0; j<iCol; j++)
0x5L)JA*zb1Z'aB0       {ITPUB个人空间�|1q!Y5bZ
        rows[count,j] = dt.DefaultView[i][j].ToString();ITPUB个人空间zT%~Hr;jqfh
       }ITPUB个人空间}*]-]7Gsew%_
       i++;ITPUB个人空间#u7Z4Ur
fv
      }
^4md%a @2V0      worksheet.get_Range(worksheet.Cells[iRows+2-left, 1], worksheet.Cells[ iRows+1, iCol]).Value2 = rows;
J
}4S;OQ j-t7R!f0     }
-h'c8WHPqoI0    }ITPUB个人空间HH!z2O,[o0[V
    
el3o8m#mjq7[/T)Bj7KY0    worksheet.SaveAs( FileName, missing, missing, missing, missing, missing, missing, missing, missing, missing);

   }
m1vv8E'[L^T0   catch(Exception ex)ITPUB个人空间6p|]Z7E5d
   {ITPUB个人空间7HqHAG|S,Z�e
    throw new Exception(ex.Message);
z2y!D2[Er0   }
E9Nk%}%Y0   finallyITPUB个人空间Dw&oM^7D9k,R
   {ITPUB个人空间bkD#r8?B
~%x
    myExcel.Workbooks.Close();ITPUB个人空间]0L}!k]A&x+^
    myExcel.Quit();

    System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet);ITPUB个人空间3S@*C2^jt'@MLLy%S
    System.Runtime.InteropServices.Marshal.ReleaseComObject(myExcel);
q+a7Id%b8ty/8Q2/0    worksheet = null;
3d0l1h%GB)]0    myExcel = null;ITPUB个人空间-{ra/D:~"_c6l
    GC.Collect();
ZKzn(Q.fh7Tcc0   }
Ykt3O$E0   
Yq(T7t/_6A0  }

  /// <summary>
bgLfAyL;f*_n0  /// 因为调用EXCEL后，结束时EXCEL进程并没有立即KILL，用这个方法调用可以KILL掉EXCEL
'F7i,Tv7e;f0  /// </summary>ITPUB个人空间P)Ju)rpO4m:/D
Ls
        public static void callToExcel(string FileName, string SheetName, System.Data.DataTable dt)
O*n0^p5`d@a0  {ITPUB个人空间hd$A!} sVsb1r$w
   toExcel(FileName, SheetName, dt);ITPUB个人空间P`t#@
?3b
   GC.Collect();
2u As2K4T$I0  }ITPUB个人空间[2HL"j@7Ev'y
 }
fI@L)L0}