C#操作Excel (转)

C#操作Excel (转)

首先的一步就是使用Tlbimp这个工具将Excel9.0的对象库文件Excel8.olb转换成为dll，这样才能做为.Net平台Assembly来使用：）操作如下：

TlbImp Excel9.olb Excel.dll 

只要有了这个Excel.dll，现在我们就能使用Excel的各种操作函数了。 
下面就让我们具体看看C#是如何使用这些东东吧。 

1 . 创建一个新Excel的Application: 

Application exc  =   new  Application();
if  (exc  ==   null ) {
Console.WriteLine( " ERROR: EXCEL couldn " t be started " );
return   0 ;
}

2 . 让这个工程可见: 

exc.set_Visible( 0 ,  true ); 

3 . 获取WorkBooks集合: 

Workbooks workbooks  =  exc.Workbooks; 

4 . 加入新的WorkBook: 

_Workbook workbook  =  workbooks.Add(XlWBATemplate.xlWBATWorksheet,  0 ); 

5 . 获取WorkSheets集合: 

_Worksheet worksheet  =  (_Worksheet) sheets.get_Item( 1 );
if  (worksheet  ==   null ) {
Console.WriteLine ( " ERROR in worksheet == null " );
}

6 . 给单元格设置变量： 

Range range1  =  worksheet.get_Range( " C1 " , Missing.Value);
if  (range1  ==   null ) {
Console.WriteLine ( " ERROR: range == null " );
}
const   int  nCells  =   1 ;
Object[] args1  =   new  Object[ 1 ];
args1[ 0 ]  =  nCells;
range1.GetType().InvokeMember( " Value " , BindingFlags.SetProperty,  null , range1, args1);

例程: 

using  System;
using  System.Reflection; 
using  System.Runtime.InteropServices; 
using  Excel;

class  Excel {
public   static   int  Main() {
Application exc  =   new  Application();
if  (exc  ==   null ) {
Console.WriteLine( " ERROR: EXCEL couldn " t be started ! " );
return   0 ;
}

exc.set_Visible( 0 ,  true ); 
Workbooks workbooks  =  exc.Workbooks;
_Workbook workbook  =  workbooks.Add(XlWBATemplate.xlWBATWorksheet,  0 ); 
Sheets sheets  =  workbook.Worksheets;

_Worksheet worksheet  =  (_Worksheet) sheets.get_Item( 1 );
if  (worksheet  ==   null ) {
Console.WriteLine ( " ERROR: worksheet == null " );
}

Range range1  =  worksheet.get_Range( " C1 " , Missing.Value);
if  (range1  ==   null ) {
Console.WriteLine ( " ERROR: range == null " );
}
const   int  nCells  =   1 ;
Object[] args1  =   new  Object[ 1 ];
args1[ 0 ]  =  nCells;
range1.GetType().InvokeMember( " Value " , BindingFlags.SetProperty,  null ,range1, args1);
return   100 ;
}
}

现在我们来看看如何使用数组，他有些类似于设置单元格。仅仅需要的改变只是args2[ 0 ]  =  array2; 
const   int  nCell  =   5 ;
Range range2  =  worksheet.get_Range( " A1 " ,  " E1 " );
int [] array2  =   new   int  [nCell];
for  ( int  i = 0 ; i  <  array2.GetLength( 0 ); i ++ ) {
array2[i]  =  i + 1 ;
}
Object[] args2  =   new  Object[ 1 ];
args2[ 0 ]  =  array2;
range2.GetType().InvokeMember( " Value " , BindingFlags.SetProperty,  null , range2, args2);

　　大家需要了解Tlbimp这个工具的使用啊：）这个东东很有用，可以将普通Win32程序移植到.Net下面来：） 



Excel表格文档由于其简单易用，普遍应用于不少单位或部门，因而在编写应用程序过程中，有时会需要对Excel文档进行操作，最简单的情况通常有两种：（ 1 ）需要获取文档中一些单元格的值；（ 2 ）将文档导入至数据库。

 

1 ).在操作Excel文档之前，需要添加对Excel对象库的引用：

引用—COM—Microsoft Excel  11.0  Object Library（版本号可能不同）

 

2 ).以下代码示意打开一个已有的Excel文档的第一个sheet页，获取单元格“B1”的内容，并判断其值是否为“my”：


         private void Operate(string  pFileName)
        {
            Excel.Application app = new Excel.Application();//打开一个Excel应用
            if (app == null )
            {
                return ;
            }

            Workbooks wbs =  app.Workbooks;
            _Workbook wb = wbs.Add(pFileName);//打开一个现有的工作薄
            Sheets shs =  wb.Sheets;
            _Worksheet sh = (_Worksheet)shs.get_Item(1);//选择第一个Sheet页
            if (sh == null )
            {
                return ;
            }

            Range r = sh.get_Range("B1" , Missing.Value);
            if (System.Convert.ToString(r.Value2).Trim().Equals("my" ))
            {
                //Do Something.
            }
        }

 

3 ).不少人在打开Excel应用后，对如何在使用完成后释放它大感头痛，在网上我找到一种方法，经过实验证明是OK的：

         private   void  ReleaseCOM( object  pObj)
        {
             try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(pObj);
            }
             catch
            {
                 throw   new  Exception( " 释放资源时发生错误！ " );
            }
             finally
            {
                pObj  =   null ;
            }
        }

有了该方法，则只要在步骤2)代码之后添加以下代码，就可以完成对资源的释放：


         private   void  Operate( string  pFileName)
        { 
……
// 释放资源
            ReleaseCOM(sh);
            ReleaseCOM(shs);
            ReleaseCOM(wb);
            ReleaseCOM(wbs);
            app.Quit();
            ReleaseCOM(app);
        }


4 ).基于Excel中的函数可以编辑出相当复杂的公式，有时候在处理Excel文档时，希望使用Excel中的公式，以下结合FREQUENCY，MATCH函数对B列计算distinct count，将其写入B100单元格（假定有数据的行不超过100行）：


         string   count  =  sh.UsedRange.Rows.Count.ToString();
        Range r  =  sh.get_Range( " B100 " ,Missing.Value);
        r.Formula  =   " =SUM(IF(FREQUENCY(MATCH(B1:B "   +  count  +   " ,B1:B "   +  count  +   " ,0),MATCH(B1:B "   +  count  +   " ,B1:B "   +  count  +   " ,0))>0,1)) " ;
        Console.WriteLine(System.Convert.ToString(r.Value2));


5 ).从Excel导入数据，可以先使用ODBC，以select查询时使用“[sheet页名称$]”作为“表名”，将数据以datatable形式载入到内存中，之后的事就比较简单了，可以根据datatable逐行构造insert语句，然后插入到目标数据库中：


         private  DataTable LoadExcel( string  pPath)
        {
             string  connString  =   " Driver={Driver do Microsoft Excel(*.xls)};DriverId=790;SafeTransactions=0;ReadOnly=1;MaxScanRows=16;Threads=3;MaxBufferSize=2048;UserCommitSync=Yes;FIL=excel 8.0;PageTimeout=5; " ;
            connString  +=   " DBQ= "   +  pPath;
            OdbcConnection conn  =   new  OdbcConnection(connString);
            OdbcCommand cmd  =   new  OdbcCommand();
            cmd.Connection  =  conn;
             // 获取Excel中第一个Sheet名称，作为查询时的表名
             string  sheetName  =   this .GetExcelSheetName(pPath);
             string  sql  =   " select * from [ "   +  sheetName.Replace( ' . ' ,  ' # ' )  +   " $] " ;
            cmd.CommandText  =  sql;
            OdbcDataAdapter da  =   new  OdbcDataAdapter(cmd);
            DataSet ds  =   new  DataSet();
             try
            {                
                da.Fill(ds);
                 return  ds.Tables[ 0 ];
            }
             catch  (Exception x)
            {
                ds  =   null ;
                 throw   new  Exception( " 从Excel文件中获取数据时发生错误！ " );
            }
             finally
            {
                cmd.Dispose();
                cmd  =   null ;
                da.Dispose();
                da  =   null ;
                 if  (conn.State  ==  ConnectionState.Open)
                {
                    conn.Close();
                }
                conn  =   null ;
            }
        }

         private   string  GetExcelSheetName( string  pPath)
        {
             // 打开一个Excel应用
            Excel.Application app  =   new  Excel.Application();
             if  (app  ==   null )
            {
                 throw   new  Exception( " 打开Excel应用时发生错误！ " );
            }
            Excel.Workbooks wbs  =  app.Workbooks;
             // 打开一个现有的工作薄
            Excel._Workbook wb  =  wbs.Add(pPath);
            Excel.Sheets shs  =  wb.Sheets;
             // 选择第一个Sheet页
            Excel._Worksheet sh  =  (Excel._Worksheet)shs.get_Item( 1 );
             string  sheetName  =  sh.Name;

            ReleaseCOM(sh);
            ReleaseCOM(shs);
            ReleaseCOM(wb);
            ReleaseCOM(wbs);
            app.Quit();
            ReleaseCOM(app);
             return  sheetName;
        }

(“ string  sql  =   " select * from [ "   +  sheetName.Replace( ' . ' ,  ' # ' )  +   " $] " ;”如果sheet页名称中含有“.”，则要将其替换为“#”，具体原因，没研究出来 - _ - 。另外贴一张界面截图：)




6 ).关于从Excel导入数据，网上应该有很多例子，这里补充一个关于数据导入时的效率问题。最初在从Excel导入数据到MySql中时，当文件达到50000行时，逐行导入花了10余分钟的时间。大量执行插入操作，逐条执行很慢原因可能在于：

( 1 )每执行一条插入语句，客户端需要与服务器交互一次，这要有代价；

( 2 )一些数据库对每一条插入操作都执行事务，这也要有代价

所以在大量执行插入操作时，应该尽量先多个insert语句拼接好，例如每次拼接好200个insert语句，然后再一次执行它，这样可以大幅提高效率。

最近因为需要学习了一下使用C#操作Excel表格，现在把我使用C#如何定制表格的过程提供给需要的兄弟： 
/* 从数据库提取数据 */  
string  strconn = " packet size=4096;user id=sa;data source=localhost;persist security info=True;initial catalog=Database;password=sa " ; 
SqlConnection sqlconn = new  SqlConnection(strconn); 
sqlconn.Open(); 
SqlDataAdapter sqldataAdapter = new  SqlDataAdapter( " Select * from [数据表] " ,sqlconn); 
DataSet myds = new  DataSet(); 
sqldataAdapter.Fill(myds); 
/* 在Execl中建立“成果表”的格式表格 */  
Excel.ApplicationClass excel = new  Excel.ApplicationClass(); 
excel.Application.Workbooks.Add( true ); 
excel.Cells[ 1 , 1 ]  =   " 单元名称 "  ; 
excel.Cells[ 1 , 2 ] = " 指标及其描述、特征值和权重 " ; 
excel.Cells[ 1 , 23 ] = " 属性 " ; 
excel.Cells[ 2 , 2 ] = " D " ; 
excel.Cells[ 2 , 5 ] = " R " ; 
excel.Cells[ 2 , 8 ] = " A " ; 
excel.Cells[ 2 , 11 ] = " S " ; 
excel.Cells[ 2 , 14 ] = " T " ; 
excel.Cells[ 2 , 17 ] = " I " ; 
excel.Cells[ 2 , 20 ] = " C " ; 
excel.Cells[ 2 , 23 ] = " 结果 " ; 
excel.Cells[ 2 , 24 ] = " 等级 " ; 
excel.Cells[ 3 , 2 ] = " 描述 " ; 
excel.Cells[ 3 , 3 ] = " 特征值 " ; 
excel.Cells[ 3 , 4 ] = " 权重 " ; 
excel.Cells[ 3 , 5 ] = " 描述 " ; 
excel.Cells[ 3 , 6 ] = " 特征值 " ; 
excel.Cells[ 3 , 7 ] = " 权重 " ; 
excel.Cells[ 3 , 8 ] = " 描述 " ; 
excel.Cells[ 3 , 9 ] = " 特征值 " ; 
excel.Cells[ 3 , 10 ] = " 权重 " ; 
excel.Cells[ 3 , 11 ] = " 描述 " ; 
excel.Cells[ 3 , 12 ] = " 特征值 " ; 
excel.Cells[ 3 , 13 ] = " 权重 " ; 
excel.Cells[ 3 , 14 ] = " 描述 " ; 
excel.Cells[ 3 , 15 ] = " 特征值 " ; 
excel.Cells[ 3 , 16 ] = " 权重 " ; 
excel.Cells[ 3 , 17 ] = " 描述 " ; 
excel.Cells[ 3 , 18 ] = " 特征值 " ; 
excel.Cells[ 3 , 19 ] = " 权重 " ; 
excel.Cells[ 3 , 20 ] = " 描述 " ; 
excel.Cells[ 3 , 21 ] = " 特征值 " ; 
excel.Cells[ 3 , 22 ] = " 权重 " ; 

excel.get_Range(excel.Cells[  1  ,  1  ],excel.Cells[  3  , 1  ]).MergeCells = true ; 
excel.get_Range(excel.Cells[  1  ,  2  ],excel.Cells[  1  , 22  ]).MergeCells = true ; 
excel.get_Range(excel.Cells[  1  ,  23  ],excel.Cells[  1  , 24  ]).MergeCells = true ; 
excel.get_Range(excel.Cells[  2  ,  2  ],excel.Cells[  2  , 4  ]).MergeCells = true ; 
excel.get_Range(excel.Cells[  2  ,  5  ],excel.Cells[  2  , 7  ]).MergeCells = true ; 
excel.get_Range(excel.Cells[  2  ,  8 ],excel.Cells[  2  , 10  ]).MergeCells = true ; 
excel.get_Range(excel.Cells[  2  , 11  ],excel.Cells[  2  , 13  ]).MergeCells = true ; 
excel.get_Range(excel.Cells[  2  ,  14  ],excel.Cells[  2  , 16  ]).MergeCells = true ; 
excel.get_Range(excel.Cells[  2  ,  17  ],excel.Cells[  2  , 19  ]).MergeCells = true ; 
excel.get_Range(excel.Cells[  2  ,  20  ],excel.Cells[  2  , 22  ]).MergeCells = true ; 
excel.get_Range(excel.Cells[  2  ,  23  ],excel.Cells[  3  , 23  ]).MergeCells = true ; 
excel.get_Range(excel.Cells[  2  ,  24  ],excel.Cells[  3  , 24  ]).MergeCells = true ; 

/* 往Excel格式表格中写入数据 */  
int  waterdataRows = myds.Tables[ 0 ].Rows.Count; 
int  waterdataCols = myds.Tables[ 0 ].Columns.Count; 
for ( int  i = 1 ;i <= waterdataRows;i ++ ) 
{ 
excel.Cells[i + 3 , 1 ] = myds.Tables[ 0 ].Rows[i - 1 ].ItemArray.GetValue( 0 ); 
for ( int  j = 1 ;j <= 7 ;j ++ ) 
{ 
excel.Cells[i + 3 , 3 * j] = myds.Tables[ 0 ].Rows[i - 1 ].ItemArray.GetValue(j); 
excel.Cells[i + 3 , 3 * j + 1 ] = WW[j - 1 ]; 
} 
excel.Cells[i + 3 , 23 ] = ds.Tables[ 0 ].Rows[i - 1 ].ItemArray.GetValue( 1 ); 
excel.Cells[i + 3 , 24 ] = ds.Tables[ 0 ].Rows[i - 1 ].ItemArray.GetValue( 3 ); 
} 
excel.Visible = false ; 
excel.DisplayAlerts = false ; 
// Excel.WorksheetClass worksheet=(Excel.WorksheetClass)excel.ActiveSheet; 
excel.Save(); 
excel.Application.Workbooks.Close(); 
excel.Application.Quit(); 
excel.Quit(); 
System.Runtime.InteropServices.Marshal.ReleaseComObject (excel); 
KillProcess( " EXCEL.EXE " ); 
GC.Collect();