性格决定命运气度左右格局拼搏方能取胜谦虚才会进步

C# 操作Excel [不确定sheet name]

上一篇 / 下一篇 2008-05-09 15:45:17 / 个人分类：做着

C#操作Excel我掌握两种方法ITPUB个人空间|Hep$XM
1.Microsoft.Jet.OLEDB.4.0ITPUB个人空间+q*A w|m
操作简单，但不知道某种原因，部分Cell不能正确读取到．

2.Excel.ApplicationClassITPUB个人空间*t:?M"u`(c h(rg
操作相对复杂，功能多一些．

遇到问题：由于方法1不能正确读取Excel数据，所以选择方法2．但是sheet name不知，方法二不能操作Excel，不能确定sheetName.ITPUB个人空间N/]T/a;QS8Y
解决：使用方法1获取到Excel中所有SheetName，在方法2中使用这个结果．

Source:

using System;ITPUB个人空间/v3[:k D4n)e4v3K(Bw
using System.Data;
$uom t-Rt|T0using System.Data.OleDb;ITPUB个人空间ht5J!r;j!k
using System.IO;ITPUB个人空间7{P[jq](e
using System.Collections;
'~8Q VAHB%`&@c&w|0using Excel;

namespace ImportIDPSCDITPUB个人空间[:U;J*o1dG6x
{
v!`T%l.Au+h:C^0 /// <summary>ITPUB个人空间)ILq Y.`vw
/// Input of IDP and SCD Spending per day .ITPUB个人空间}c7z @W U\y'@
/// </summary>
+b"?-EH-p'xx z&M?0 class ImportIDPSCD
)AtqToEMii\$@0 {
Y9a:?TXC0  // IDP and SCD Spendin File Path
4a f h{UC.xN0  private static string filePath = string.Empty;
1l'cB8F$[3C&W{0  // DB Connect StringITPUB个人空间"|_yjp;_Y:Z
  private static string strDBManager = string.Empty;

  /// <summary>ITPUB个人空间O|:`/Bvmx4?
  /// Input of IDP and SCD Spending per day .
/{Du,}/p^)I8M0  /// </summary>ITPUB个人空间#bw2Sy)v,n5l;f(b
  [STAThread]ITPUB个人空间#V9J.Zgb5M4o8gY]
  static void Main(string[] args)
2D+h+x;wM:|Aq3~0  {
cRI5aC~0he0   // Get File PathITPUB个人空间a{*u{@,i[
   filePath = System.Configuration.ConfigurationSettings.AppSettings["XXX"];
f&PTx5{8I0   // Get DB ConnecctITPUB个人空间*?9]gHV0Vt
   strDBManager = System.Configuration.ConfigurationSettings.AppSettings["YYY"];ITPUB个人空间"u0]TG2@{,f
   // Get FileITPUB个人空间TUzM`*ZN X
   string[] fileEntries = Directory.GetFiles(filePath);ITPUB个人空间7\#c;p_I.F&`:[+S
   foreach(string fileName in fileEntries)ITPUB个人空间;g8X'[_3gQ6EFx
   {ITPUB个人空间p0D$I"L f!Wm J*C\
    // Get Excel File
"hUQ(Q+_+dg,Z0    GetFileFull(fileName);
!])R8zfoH z%Re0   }
2c PX!bc0  }

  /// <summary>
(ofG2\2|6vaVl*w5K/y0  /// Get Excel Data.
7|0Z9X%rp L0  /// </summary>
9mpz.\/{s.aaG0  /// <param name="fileName">file Name</param>
tp;k2I^3Sl%Q8Z0  /// <returns>int</returns>
d Jw||IbaBF0  private static int GetFile(string fileName)ITPUB个人空间!Kmzlh.?9j
  {
0c0w3g L6}a"@ w-j0   // Read Excel File ConnectITPUB个人空间y3MH;ms0]K$H
   string excelConnect = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source =" + fileName + ";Extended Properties=Excel 8.0";
'JVfGE/\b0   string excelCom = string.Empty;
!J)aX+s5E)`B8]&s0   OleDbConnection excelConn = new OleDbConnection(excelConnect);ITPUB个人空间,X.G8l\ Z#W9Be

n]iF/Oo3t0   try
Qy5?@[G0   {
O:q.b,w!WIhF0    // Open Excel
5]fK*{^m [0    excelConn.Open();
)?#{t7_B1x0    System.Data.DataTable excelDT = null;
z!tHc K0    excelDT = excelConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
_dxs5|%l+S0    int i = 0;ITPUB个人空间xVc}B-O:[2d
    // Add the sheet name to the string array.ITPUB个人空间 r*AHa0j"j%mowo
    foreach(DataRow row in excelDT.Rows)ITPUB个人空间]e*G0_|1F.S
    {
hF*TN3X:I$^$Uu0     DataSet excelDataSet = new DataSet();ITPUB个人空间cg m4BC)k
     // Get sheet Name.ITPUB个人空间$KR5eAdwP
     string sheetName = row["TABLE_NAME"].ToString();ITPUB个人空间qd&i3K^
     i++;ITPUB个人空间J3y(y8LS
     excelCom = " SELECT * FROM [" + sheetName + "]";
r6G8~G7L(X5tP0     OleDbDataAdapter excelAdap = new OleDbDataAdapter(excelCom , excelConn);
     // Get Excel DataITPUB个人空间e\/F[ Nd:Qx
     excelAdap.Fill(excelDataSet, "sheet1");
"a ];}K*B6H~w[5A0     // Import Excel DataITPUB个人空间M@r'oy$J4op8@}
     ImportFile(excelDataSet);
g,N5a~ZN thC:g0    }ITPUB个人空间5X+zD3vE1m)XFy
   }ITPUB个人空间`+?w8G} S3t
   catch(Exception ex)ITPUB个人空间\h8A}(x"E{Ju
   {
{ RM.| s0    Console.WriteLine(ex.Message);
L"B$g#@j&R5]]Y0    return -1;    ITPUB个人空间?:m[1rT
   }
1J8o;KRT~m:y$c,a0   finallyITPUB个人空间:d#b&]V c*|O
   {
,P"bsTj`6nQ.|0    if(excelConn != null)ITPUB个人空间]AAP|T` _
    {ITPUB个人空间4lr3Dp8X rJ&EKd(n
     excelConn.Close();
5dh%C.?.xCRO!G0     excelConn = null;
pq*~2q~GOc/Q0    }ITPUB个人空间G#d}~2my
   }

return 0;ITPUB个人空间.R/@!W F|;_h2WE)y
}

  /// <summary>
qe:Lg/Z&\0  /// Get ExcelFileITPUB个人空间;H3J;G1D/[f5a
  /// </summary>
}t{rRx0  /// <param name="fileName">File Name</param>ITPUB个人空间F r]8fW&L\p
  /// <returns>int</returns>ITPUB个人空间&Y'A#QC;l%bq:G:AY
  private static int GetFileFull(string fileName)ITPUB个人空间A'z+Brqt]grS
  {
!z'X'TAm+a6[0   // Excel Data
XL&q6Kg#Dy0   DataSet allDS = new DataSet();ITPUB个人空间-c G8la,^.ph
   // Sheets Name
8k$S!}bj0e0   String[] excelSheetsName = null;ITPUB个人空间$JoWA4[2l
   // All Cell Value of a Sheet
/k^1|&y/g`c2X0   ArrayList allCellValue = new ArrayList();

   // Read Excel File Connect
"xT2g-Ccb&h eMS2q0   string excelConnect = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source =" + fileName + ";Extended Properties=Excel 8.0";ITPUB个人空间_iJ#UH6bg
   string excelCom = string.Empty;ITPUB个人空间2{7NHK Pk+AY
   OleDbConnection excelConn = new OleDbConnection(excelConnect);

   // Creates a new Excel ApplicationITPUB个人空间1Iv0GW0g!A
   Excel.Application excelApp = new Excel.ApplicationClass();ITPUB个人空间1dwxTAW9^O
   // Makes Excel visible to the user.ITPUB个人空间*|9D:s|T|\a
   excelApp.Visible = false;
?.z;\kWI"{0
!B&C1lQOr0   tryITPUB个人空间 Z WIf Dx^5j
   {
ad:ommtwo0    // Open Excel
R&h#v5@s |&C6h3]0    excelConn.Open();
1e6~9X_'Iy q0    System.Data.DataTable excelDT = null;
*_D$uV@z b0    excelDT = excelConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);ITPUB个人空间%cm3n:p,]`M4L
    excelSheetsName = new String[excelDT.Rows.Count];
    int i = 0;ITPUB个人空间i2K"d%m J(b5F3k
    // Add the sheet name to the string array.
8o|`EB[Ki0    foreach(DataRow row in excelDT.Rows)
{L3V'K_cP]!G0    {
cF)Wi1z/\6{Yu0     // Get sheet Name.ITPUB个人空间 b Wcj&@
     excelSheetsName[i] = row["TABLE_NAME"].ToString();ITPUB个人空间1b ~h0p+^ ]q3s
     i++;
:?8s nDc9t0    }

    // Get Excel Data Command
~eia_(~;{mj0    excelCom = " SELECT * FROM [" + excelSheetsName[0] + "]";
X;\@` G.b%S(\D9]0    OleDbDataAdapter excelAdap = new OleDbDataAdapter(excelCom , excelConn);    ITPUB个人空间N EF C2Kgi6}0hs
    // Get Excel DataITPUB个人空间A]3f;{-sA#r X
    excelAdap.Fill(allDS, "AllDS");

    // Close Open Object
os`` `bH6^/k0    if(excelConn != null)ITPUB个人空间b;zPb;~r8y
    {
t.w-R#h+X G0{ d E0     excelConn.Close();
2t2~"^f4|f\0Og(l,l"l0     excelConn = null;
2eX1paPM,d0    }ITPUB个人空间c&`4m7Ah)j!r5D-{
    #region Execute Excel

    // Opens existing workbookITPUB个人空间[ `\ {~PU(moM
    string workbookPath = fileName;ITPUB个人空间o1hN-UN
    Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(workbookPath, 0,
.A8OVjH0     false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true,ITPUB个人空间FJ w(]vj$V
     false, 0, true, false, false);
xGg3Z!w)Y4g OU]0
I?wsP p#DwJ_]0    // Gets the Worksheets collectionITPUB个人空间 {3^ r1V&A4z
    Excel.Sheets excelSheets = excelWorkbook.Worksheets;

    // Gets Sheet Name for WorksheetsITPUB个人空间u9k$Wf4Vl0J
    string currentSheet = excelSheetsName[0].Replace("$", "").Replace("'", "");
it+ut4II-fW2F(A0    Excel.Worksheet excelWorksheet = (Excel.Worksheet)excelSheets.get_Item(currentSheet);

    // For each the sheet Data
AXNjVDgt0    for(int index = 4; index < allDS.Tables[0].Rows.Count - 1; index++)ITPUB个人空间7cGN(B)\Du a
    {
     if(allDS.Tables[0].Rows[index][0] != null && !allDS.Tables[0].Rows[index][0].ToString().Equals(""))ITPUB个人空间s1p_rUl
     {
h*OS8X&u|E R;@0      // Gets cellITPUB个人空间6Ot%SgGw#W#Ox]
      int lineNumber = index + 2;ITPUB个人空间?h r0|R3V9D)KJ
      Excel.Range excelCell = (Excel.Range)excelWorksheet.get_Range("A" + lineNumber, "G" + lineNumber);

      // Keep Data
,kGkzd2HU0      object [,] cellValue = (object[,])excelCell.Value2;
&K|"\]5gHv0      allCellValue.Add(cellValue);
/D(W9{gC)w!h'K Yp:F6_0     }
;V&Fd.y'yK0    }

    // Import Data to DBITPUB个人空间?q\:M5Y
    ImportFile(allCellValue);ITPUB个人空间&{p {ou5gjaY
                #endregion
B%{8~W"D2]%s0g,W7D0   }
P:q,S;xr0   catch(Exception ex)ITPUB个人空间"NKiB6xZ1uA
   {
scd_&{p&k0    Console.WriteLine(ex.Message);
_;M|8QW0   }ITPUB个人空间n`E'A8k8M St;K|
   finallyITPUB个人空间-XTa!MR OF-c0c8_
   {ITPUB个人空间T,tg K!id0V2d
    if(excelConn != null)ITPUB个人空间j7r5E7`5i"f,u%G
    {ITPUB个人空间DdcT ?r4H8R2]C
     excelConn.Close();
}KR!|6Z&f'k0     excelConn = null;
5kssN.]so0    }
\;KdYn0    if(excelApp != null)
f`Ipi%[1x0    {
&d4I-M^p0     excelApp.Workbooks.Close();ITPUB个人空间5QZ!kU){
     excelApp = null;
1b};y$J1vWB{0    }ITPUB个人空间$raOJSujo!]
   }

return 0;ITPUB个人空间1{#q%U3l#m8qxF Q
}

  /// <summary>ITPUB个人空间S;HcGK&o8[v
  /// Import File Data into DBITPUB个人空间5_&bKie\&Aa
  /// </summary>
n.q-S1s L0  /// <param name="allAellValue">All Cell Data</param>
z0qu.T5F'j'~C0  private static void ImportFile(ArrayList allAellValue)ITPUB个人空间T(`-FALt)SJE
  {ITPUB个人空间O"p~ NXUt%P
   // Declare variables
j@d7U/E0   OleDbConnection cn = null;
k9AS`-_D0   OleDbCommand cmd    = null;
h_0n(eR j ]v G0   OleDbTransaction Trans = null;

   try
z0sQ+Ep y0   {
q'F8\l|/]'C/x XxU0    cn = new OleDbConnection(strDBManager);ITPUB个人空间y#J L`2WB
    cn.Open();ITPUB个人空间N4UsTJ'i)Zn@K
    cmd = cn.CreateCommand();
6@bg s0W;|A0    cmd.Connection = cn;ITPUB个人空间6g.u{:T1rVl
    Trans = cn.BeginTransaction();ITPUB个人空间5BIOI)y
    cmd.Transaction = Trans;

cmd.CommandText = "eFund_ImportIDPSCD";
6LP5@$x M0 cmd.CommandType = CommandType.StoredProcedure;

    for(int index = 0; index < allAellValue.Count; index++)ITPUB个人空间!OZ:e_$B0O7g
    {ITPUB个人空间'IFhA2{5c'{_v
     cmd.Parameters.Clear();
.cg{DM ^Ff0     object [,] cellValue = (object [,])allAellValue[index];
-Nu6aT|:Sy(K*W'y0     OleDbParameter p1 = cmd.Parameters.Add("@SettAmoIDP",OleDbType.Double);
]\o6|R3r4G0     OleDbParameter p2 = cmd.Parameters.Add("@SettAmoSCD",OleDbType.Double);ITPUB个人空间a#T)o!kCVm"w
     OleDbParameter p3 = cmd.Parameters.Add("@AdjuAmoIDP",OleDbType.Double);
~&ZOt3i"^M X3g&x0     OleDbParameter p4 = cmd.Parameters.Add("@AdjuAmoSCD",OleDbType.Double);
V F;SmXZ,lk0     OleDbParameter p5 = cmd.Parameters.Add("@DailyAmo",OleDbType.Double);
U+Vn'iGt`4V6g0     OleDbParameter p6 = cmd.Parameters.Add("@ServiceAmo",OleDbType.Double);
*^qe"@"h|)@0     OleDbParameter p7 = cmd.Parameters.Add("@SettDate",OleDbType.DBTimeStamp);
(z cE"m!eG%v&q0     // Set Value
Qu1@;eDk7Q.Q0     p1.Value = Convert.ToDouble(cellValue[1,4]);   // SettlementAmountIDPITPUB个人空间c5I)F~i n
     p2.Value = Convert.ToDouble(cellValue[1,3]);   // SettlementAmountSCDITPUB个人空间2WO4d*F'D-W3gDK_
     p3.Value = Convert.ToDouble(cellValue[1,6]);   // AdjustmentAmountIDP
Kb$Y(x+X l\n0     p4.Value = Convert.ToDouble(cellValue[1,5]);   // AdjustmentAmountSCD
/\GFN9zNV0     p5.Value = Convert.ToDouble(cellValue[1,2]);   // DailyDepositAmount
}u[G)A h^%V0     p6.Value = Convert.ToDouble(cellValue[1,7]);   // ServiceAmount
aB e9d0RI*ig-D8Z}0     p7.Value = Convert.ToDateTime(cellValue[1,1]);   // SettlementDateITPUB个人空间5Q;X1KnF~\
    ITPUB个人空间!chOh ~;e
     cmd.ExecuteNonQuery();
1k,g't brB3o0    }ITPUB个人空间}wf*y:\Hk
    Trans.Commit();ITPUB个人空间(DkBAs*D
   }
$C_"Q1h$c^*rs S0   catch(Exception ex)
;C_$H'?0SU0   {ITPUB个人空间V+zQ5h!_!bpO
    Trans.Rollback();
c U,_a*R:As]*B0    Console.WriteLine(ex.Message);ITPUB个人空间BU h2vKm X+K(j
    throw ex;ITPUB个人空间6ZS:owO
   }
[Hep3GW1r2[[4V)O0   finallyITPUB个人空间Y-ucE$Y
   {ITPUB个人空间/J Z#S,ST
    if(cn != null)ITPUB个人空间 w$W d;F@!a6ob/oYL
    {
P"V.~ N A-O0     cn.Close();ITPUB个人空间E`GZlIP,o
     cn = null;
!eJ:XFng4hF0    }
)Z L.gJ3j#]%vbo0   }
[6C4l])H_ G0  }

  /// <summary>ITPUB个人空间IxE])oBe A
  /// Import File Data into DBITPUB个人空间*w ^7VhN*kG4Z,Bz
  /// </summary>ITPUB个人空间&oT {;B6K D5So
  /// <param name="excelDataSet">Data</param>ITPUB个人空间9U0C3wI|h-FS
  private static void ImportFile(DataSet excelDataSet)ITPUB个人空间 {u5`-G.G,v?5Z
  {
u3{:_$i/wP$R0   // Declare variables
{:w1t H;P(e~E0   OleDbConnection cn = null;
(^[@$I-c0   OleDbCommand cmd    = null;ITPUB个人空间@&d\~$x
   OleDbTransaction Trans = null;

   try
7m4]tRRt]0   {ITPUB个人空间+Y"Mt[&V
    cn = new OleDbConnection(strDBManager);
_Q(H%k_,XG*iy0    cn.Open();
5M+kC"~a3o0    cmd = cn.CreateCommand();ITPUB个人空间:|3P2H+^@ X
    cmd.Connection = cn;
nt5c%d/s&|bab!C3_%G0    Trans = cn.BeginTransaction();ITPUB个人空间&F!b;z-nk6u X
    cmd.Transaction = Trans;

cmd.CommandText = "XXX";ITPUB个人空间!} zrz,xx
cmd.CommandType = CommandType.StoredProcedure;

    for(int index = 4; index < excelDataSet.Tables[0].Rows.Count - 1; index++)ITPUB个人空间0C8D3@pO
    {
     cmd.Parameters.Clear();
't~v+EvQ0     if(excelDataSet.Tables[0].Rows[index][0] != null && !excelDataSet.Tables[0].Rows[index][0].ToString().Equals(""))ITPUB个人空间CI7@e$@ti
     {
9R c ~8H+u @*?Dl0      OleDbParameter p1 = cmd.Parameters.Add("@SettAmoIDP",OleDbType.Double);ITPUB个人空间U)|*M `"sD {AV+s
      OleDbParameter p2 = cmd.Parameters.Add("@SettAmoSCD",OleDbType.Double);ITPUB个人空间"RO Q/d+ee
      OleDbParameter p3 = cmd.Parameters.Add("@AdjuAmoIDP",OleDbType.Double);
0QOD'P4c$O9j0      OleDbParameter p4 = cmd.Parameters.Add("@AdjuAmoSCD",OleDbType.Double);
(`!u9i,dBC0      OleDbParameter p5 = cmd.Parameters.Add("@DailyAmo",OleDbType.Double);ITPUB个人空间YiJN,L+T)c1hf'eI
      OleDbParameter p6 = cmd.Parameters.Add("@ServiceAmo",OleDbType.Double);ITPUB个人空间)D/TJM ]r;f
      OleDbParameter p7 = cmd.Parameters.Add("@SettDate",OleDbType.DBTimeStamp);
s/t lRA'g2E$zj0a0      p1.Value = excelDataSet.Tables[0].Rows[index][3];ITPUB个人空间W2u0|NV+c&i
      p2.Value = excelDataSet.Tables[0].Rows[index][2];ITPUB个人空间d@ x,?Na
      p3.Value = excelDataSet.Tables[0].Rows[index][5];ITPUB个人空间gQ(\2Be:^.e
      p4.Value = excelDataSet.Tables[0].Rows[index][4];ITPUB个人空间;~b^BX{`
      p5.Value = excelDataSet.Tables[0].Rows[index][1];
Vb5nz2t0      p6.Value = excelDataSet.Tables[0].Rows[index][6];
5G'e^ O[ pt0      p7.Value = excelDataSet.Tables[0].Rows[index][0];
i G%[.u6Ha0    ITPUB个人空间1Z/^ u(N1a WH2e
      cmd.ExecuteNonQuery();
b8}0A7H1h0     }     ITPUB个人空间*k&H%r_N
    }ITPUB个人空间hkj!A M5sF
    Trans.Commit();
Y8j'~"q*nEo+vj`?0   }ITPUB个人空间^"~A-l[;M/r~
   catch(Exception ex)
RpZ2q\k0   {
kg2t7K$~(y#o0    Trans.Rollback();
V7ZX }n@z0    Console.WriteLine(ex.Message);ITPUB个人空间+WE yU~m"^F
   }ITPUB个人空间v ~ ^;O`2F K:S
   finally
5t"i9pii0o#y{0   {ITPUB个人空间/}YP*p$m
    if(cn != null)ITPUB个人空间.@6V/L~mm
    {ITPUB个人空间Xd-kulS
     cn.Close();ITPUB个人空间KukL+Mu?0V
     cn = null;ITPUB个人空间;gheU6|e~b;HY
    }
'u)n6O$m(Dc(f0   }ITPUB个人空间2yvrK%Z2Om
  }ITPUB个人空间%Daa(?Ha_
}ITPUB个人空间Ao6d2?+xBjWcg
}ITPUB个人空间[y2JASR