请问哪里有C# Excel COM组件的使用的详细资料

ligongruangao 2009-11-30 11:53:00

今天在一个代码中发现这块不好理解,网上找了很长时间,都没讲清楚
Excel.Application m_objExcel = null;
Excel.Workbooks m_objBooks = null;
Excel._Workbook m_objBook = null;
Excel.Sheets m_objSheets = null;
Excel._Worksheet m_objSheet = null;
Excel.Range m_objRange = null;
Excel.QueryTables m_objQryTables = null;
Excel._QueryTable m_objQryTable = null;

try
{
int SheetSize = 65535;
int sheetNum=1;
// Frequenty-used variable for optional arguments.
object m_objOpt = System.Reflection.Missing.Value;

// Start a new workbook in Excel.
m_objExcel = new Excel.Application();
m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks;
m_objBook = (Excel._Workbook)(m_objBooks.Add(m_objOpt));

// Create a QueryTable that starts at cell A1.
m_objSheets = (Excel.Sheets)m_objBook.Worksheets;

string Conn = "ODBC;DRIVER=SQL Server;SERVER=192.168.12.96;UID=it;PWD=It37#&;DATABASE=NL_ITdevelop;Connect Timeout=0";

string strQuery = "Select * " + " FROM " + GlobalTempTable + " order by px,id";

m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(1));
m_objSheet.Name = "ItemRouteReverse";
m_objRange = m_objSheet.get_Range("A1", m_objOpt);

m_objQryTables = m_objSheet.QueryTables;

m_objQryTable = (Excel._QueryTable)m_objQryTables.Add(Conn, m_objRange, strQuery);

m_objQryTable.RefreshStyle = Excel.XlCellInsertionMode.xlInsertEntireRows;

m_objQryTable.Refresh(false);

if (checkBox.Checked == false)
{
strQuery = "Select * " + " FROM " + GlobalTempTable2;
m_objSheet = (Excel._Worksheet)(m_objSheets.get_Item(2));
m_objSheet.Name = "ItemUsedInJob";
m_objRange = m_objSheet.get_Range("A1", m_objOpt);

m_objQryTables = m_objSheet.QueryTables;

m_objQryTable = (Excel._QueryTable)m_objQryTables.Add(Conn, m_objRange, strQuery);

m_objQryTable.RefreshStyle = Excel.XlCellInsertionMode.xlInsertEntireRows;
m_objQryTable.Refresh(false);
}

m_objExcel.Visible = true;
}
catch (System.Exception e)
{
throw e;
}
finally
{
m_objExcel.Quit();
m_objExcel = null;
m_objRange = null;
m_objSheet = null;
m_objSheets = null;
m_objBooks = null;
m_objBook = null;
m_objExcel = null;
GC.Collect();//垃圾回收
}

Class_DataAccess clsDA = new Class_DataAccess();
SqlConnection scn = clsDA.SqlConnectionForNL_ITDevelop();

// 创建一个SqlCommand对象，并指明其命令类型为存储过程
SqlCommand scmd = new SqlCommand();
scmd.Connection = scn;
scmd.CommandType = CommandType.Text;
scmd.CommandText = "DROP TABLE " + GlobalTempTable; //IF(EXISTS(SELECT name FROM sysobjects WHERE name="+ @GlobalTempTable +" AND type='U'))
scmd.CommandTimeout = 0;
scmd.ExecuteNonQuery(); //
if (checkBox.Checked == false)
{
scmd.CommandText = "DROP TABLE " + GlobalTempTable2; //IF(EXISTS(SELECT name FROM sysobjects WHERE name="+ @GlobalTempTable +" AND type='U'))
scmd.CommandTimeout = 0;
scmd.ExecuteNonQuery(); //
}
scmd.Dispose();
scn.Close();
scn.Dispose();

...全文