Execl 操作

//打开一个空的Excel

Microsoft.Office.Interop.Excel.Application app  =   new  Microsoft.Office.Interop.Excel.Application();
app.Workbooks.Add( true );
app.Visible  =   true ;

 

//打开一个已经存在的Excel文件

Microsoft.Office.Interop.Excel.Application app  =   new  Microsoft.Office.Interop.Excel.Application();
app.Workbooks.Add(路径和文件名);
app.Visible  =   true ;

//读取Excel2003 

conExcel.ConnectionString  =   " Provider=Microsoft.Jet.OLEDB.4.0;Data Source= "   +  filepath  +   " ;Extended Properties=\ " Excel  8.0 ;HDR = YES;IMEX = 1 \ "" ;

 

//读取Excel2007

conExcel.ConnectionString  =   " Provider=Microsoft.ACE.OLEDB.12.0;Data Source= "   + filepath  +   " ;Extended Properties=\ " Excel  12.0 ;HDR = YES\ "" ;

//设置禁止弹出保存和覆盖的询问提示框

            app.DisplayAlerts  =   false ;
            app.AlertBeforeOverwriting  =   false ;

//复制到指定的地方,默认路径为我的文档

wb.SaveCopyAs( @" D:\111.xlsx " );

//新建一个Excel并保存到指定的路径

app  =   new  Microsoft.Office.Interop.Excel.Application();
wb  =  app.Workbooks.Add( true );
app.Cells[ 1 ,  1 ]  =   " test " ;

app.DisplayAlerts  =   false ;
app.AlertBeforeOverwriting  =   false ;
wb.SaveCopyAs( @" C:\111.xlsx " );
Close();

//直接读取Excel文件

DataTable myT = ExcelToDataTable( " D:/文件/新武昌站点资料.xls " , " sheet1 " );
String mystr = myT.Rows[ 0 ][ 0 ].ToString();
this .textBox1.Text = mystr;

//版本检测

string  strPathResult  =   "" ;
             string  strVersionResult  =   "" ;
             string  strKeyName  =   " Path " ;
             object  objResult  =   null ;
            Microsoft.Win32.RegistryValueKind regValueKind;
            Microsoft.Win32.RegistryKey regKey  =   null ;
            Microsoft.Win32.RegistryKey regSubKey  =   null ;

              try
            {
                regKey  =  Microsoft.Win32.Registry.LocalMachine;

                 if  (regSubKey  ==   null )
                { // office97
                    regSubKey  =  regKey.OpenSubKey( @" SOFTWARE\Microsoft\Office\8.0\Common\InstallRoot " ,  false );
                    strVersionResult  =   " office97 " ;
                    strKeyName  =   " OfficeBin " ;
                }

                 if  (regSubKey  ==   null )
                { // Office2000
                    regSubKey  =  regKey.OpenSubKey( @" SOFTWARE\Microsoft\Office\9.0\Common\InstallRoot " ,  false );
                    strVersionResult  =   " office2000 " ;
                    strKeyName  =   " Path " ;
                }

                 if  (regSubKey  ==   null )
                { // officeXp
                    regSubKey  =  regKey.OpenSubKey( @" SOFTWARE\Microsoft\Office\10.0\Common\InstallRoot " ,  false );
                    strVersionResult  =   " officeXP " ;
                    strKeyName  =   " Path " ;
                }

                 if  (regSubKey  ==   null )
                { // Office2003
                    regSubKey  =  regKey.OpenSubKey( @" SOFTWARE\Microsoft\Office\11.0\Common\InstallRoot " ,  false );
                    strVersionResult  =   " office2003 " ;
                    strKeyName  =   " Path " ;
                }

                 if  (regSubKey  ==   null )
                { // office2007
                    regSubKey  =  regKey.OpenSubKey( @" SOFTWARE\Microsoft\Office\12.0\Common\InstallRoot " ,  false );
                    strVersionResult  =   " office2007 " ;
                    strKeyName  =   " Path " ;
                }

                objResult  =  regSubKey.GetValue(strKeyName);
                regValueKind  =  regSubKey.GetValueKind(strKeyName);
                 if  (regValueKind  ==  Microsoft.Win32.RegistryValueKind.String)
                {
                    strPathResult  =  objResult.ToString();
                }
            }
             catch  (System.Security.SecurityException ex)
            {
                 throw   new  System.Security.SecurityException( " 您没有读取注册表的权限 " , ex);
            }
             catch  (Exception ex)
            {
                 throw   new  Exception( " 读取注册表出错! " , ex);
            }
             finally
            {

                 if  (regKey  !=   null )
                {
                    regKey.Close();
                    regKey  =   null ;
                }

                 if  (regSubKey  !=   null )
                {
                    regSubKey.Close();
                    regSubKey  =   null ;
                }
            }

             MessageBox.Show(strVersionResult);

//获取行数

app  =   new  Microsoft.Office.Interop.Excel.Application();
wb  =  app.Workbooks.Open(path, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value
                , Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
ws  =  (Microsoft.Office.Interop.Excel.Worksheet)wb.Worksheets[ 1 ];
// 行数
string  count  =  ws.UsedRange.Rows.Count.ToString();

//读取某个单元格信息

Range r  =  (Range)ws.Cells[ 1 ,  1 ];
MessageBox.Show(r.Value2.ToString());

//删除一行数据(第一个单元格的位置)

         public   void  DelRow( int  row, string  fPath)
        {
            OpenApp(fPath);
            Range range  =  ws.UsedRange;
             int  columns  =  range.Columns.Count;
             for  ( int  i  =   0 ; i  <  columns; i ++ )
            {
                ((Range)ws.Cells[row, i + 1 ]).Delete(Missing.Value);
            }
            wb.Save();
            Close();
        }

 //删除一列

         public   void  DelCol( int  col,  string  fPath)
        {
            OpenApp(fPath);
            Range range  =  ws.UsedRange;
             int  rows  =  range.Rows.Count;
             for  ( int  i  =   0 ; i  <  rows; i ++ )
            {
                ((Range)ws.Cells[ 1 , col]).Delete(Missing.Value);
            }
            wb.Save();
            Close();
        }

//删除单元格

         public   void  DelCell( int  row, int  col, string  fPath)
        {
            OpenApp(fPath);
            Range range  =  (Range)ws.Cells[row, col];
            range.Delete(Missing.Value);
            wb.Save();
            Close();
        }

在这里需要注意一下,在Excel表中,删除和清除是不一样的,清除(Clear)只是把内容清空,而单元格还在,删除(Del)是把一个单元格删除,下面的数据就会 自动的对其,就会造成排版问题,这是需要注意的


//释放资源

因为引用的是COM组件,是非托管的代码,所以释放资源的代码就需要手动的编写,在网上查找了很多的代码都不好用,下面这个是比较好的,

         public   void  Close()
        {
             if  (app  ==   null )  return ;
             if  (wb  !=   null )
            {
                wb.Close( false , Missing.Value, Missing.Value);
                ReleaseCom(wb);
                wb  =   null ;
            }
             this .app.Quit();
            ReleaseCom(app);
            app  =   null ;
            GC.Collect();
        }
         static   private   void  ReleaseCom( object  o)
        {
             try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(o); // 强制释放一个对象   
            }
             catch  { }
             finally
            {
                o  =   null ;
            }
        }

//合并单元格

        {
            ws.get_Range(ws.Cells[x1, y1], ws.Cells[x2, y2]).Merge(Type.Missing);
        }

  public   void  UniteCells( string  ws,  int  x1,  int  y1,  int  x2,  int  y2)
// 合并单元格
        {
            GetSheet(ws).get_Range(GetSheet(ws).Cells[x1, y1], GetSheet(ws).Cells[x2, y2]).Merge(Type.Missing);

        }

//Range可以获取一个单元格,也可以是多个单元格

获取一个

Range ran = (Range)ws.Cells[Row,Col];

 

获取多行

Range ran = ws.Get_Range(左上角单元格,右下角单元格);

Range ran = ws.Get_Range(ws.Cells[row,col],ws.Cells[row,Col]);

//设置格式

Range ra  =  (Range)ws.Cells[row  +  row  +   4 , col];
ra.HorizontalAlignment  =  Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
ra.NumberFormatLocal  =   " #,##0.00 " ;

//拷贝格式将ran选中的区域复制到ra选中的区域中

Range ran  =  ws.get_Range(ws.Cells[ 3 , 1 ],ws.Cells[ 4 , 9 ]);
Range ra  =  ws.get_Range(ws.Cells[ 5 , 1 ],ws.Cells[ 6 , 9 ]);
ran.Copy(ra);