C# Operate Excel

A. Read

 有两种方法：

  a.OleDbConnection 示例代码如下：

String connStr = " Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " + strFilePath + " ;Extended Properties=Excel 8.0; " ;
System.Data.OleDb.OleDbConnection con = new  System.Data.OleDb.OleDbConnection(connStr);
System.Data.OleDb.OleDbDataAdapter oda = new  System.Data.OleDb.OleDbDataAdapter( " select * from [Sheet1$] " ,con);
System.Data.DataSet ds = new  System.Data.DataSet();
oda.Fill(ds);
String[] SrcterminalId = new  String[ds.Tables[ 0 ].Rows.Count];
String[] DestterminalId = new  String[ds.Tables[ 0 ].Rows.Count];
String[] SendContent = new  String[ds.Tables[ 0 ].Rows.Count];
for (Int32 i = 0 ;i < ds.Tables[ 0 ].Rows.Count;i ++ )
{ // 从第0行，第0列开始
     if (ds.Tables[ 0 ].Rows[i][ 0 ].ToString() != "" )
    {
        SrcterminalId[i] = ds.Tables[ 0 ].Rows[i][ 0 ].ToString();
        Console.Write(SrcterminalId[i].ToString() + " " );
    }
     if (ds.Tables[ 0 ].Rows[i][ 1 ].ToString() != "" )
    {
        DestterminalId[i] = ds.Tables[ 0 ].Rows[i][ 1 ].ToString();
        Console.Write(DestterminalId[i].ToString() + " " );
    }
     if (ds.Tables[ 0 ].Rows[i][ 2 ].ToString() != "" )
    {
        SendContent[i] = ds.Tables[ 0 ].Rows[i][ 2 ].ToString();
        Console.Write(SendContent[i].ToString() + " " );
    }                    
}

大家会发现数据会丢失的现象, 将EXCEL数据导出的时候如果同一列数据中既有文字，又有数字！读取时一列中要么文字丢失只剩下数字，要么数字丢失，只剩下文字，这是由第一行的数据类型决定的。出现这种问题是由于数据类型不统一造成的。原来的连接字符串为 String connStr="Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+strFilePath+";Extended Properties=Excel 8.0;";查阅资料后问题解决：String connStr="Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+strFilePath+";Extended Properties='Excel 8.0;IMEX=1'";

其中参数解释： 

HDR=YES    有两个值:YES/NO,表示第一行是否字段名,默认是YES,第一行是字段名

IMEX=1   解决数字与字符混合时,识别不正常的情况

  b.调用Com组件 需要添加Microsot.Excel 9.0 Object Library组件。

// 创建Application对象
Excel.Application app = new  Excel.ApplicationClass();
// 是否显示Excel,默认为false
app.Visible = false ;
Object o = System.Reflection.Missing.Value;
Excel.WorkbookClass w = (Excel.WorkbookClass)app.Workbooks.Open(strFilePath,o,o,o,o,o,o,o,o,o,o,o,o);
Excel.Sheets sheets = w.Worksheets;
Excel.Worksheet datasheet = null ;
foreach (Excel.Worksheet sheet  in  sheets)
{
     if (sheet.Name == " Sheet1 " )
    {
        datasheet = sheet;
         break ;
    }
}
if (datasheet != null )
{  // ds还是采用上面的取行写法，如果不这样，不知道读几行
     for (Int32 i = 0 ;i < ds.Tables[ 0 ].Rows.Count;i ++ )
    {  // 从第二行，第一列开始
         // 读取，通过Range对象，但使用不同的接口得到Range
        Excel.Range range = (Excel.Range)datasheet.Cells[i + 2 , 1 ];
         if (range.Value2 != null   && range.Value2.ToString() != "" )
        {
            SrcterminalId[i] = range.Value2.ToString();
            Console.Write(SrcterminalId[i].ToString() + " " );
        }
        range = (Excel.Range)datasheet.Cells[i + 2 , 2 ];
         if (range.Value2 != null   &&  range.Value2.ToString() != "" )
        {
            DestterminalId[i] = range.Value2.ToString();
            Console.Write(DestterminalId[i].ToString() + " " );
        }
        range = (Excel.Range)datasheet.Cells[i + 2 , 3 ];
         if (range.Value2 != null   &&  range.Value2.ToString() != "" )
        {
            SendContent[i] = range.Value2.ToString();
            Console.Write(SendContent[i].ToString() + " " );
        }                    
    }
}
datasheet = null ;
sheets = null ;
app.Quit();
app = null ;

B.Write

 I.调用com组件(Access),导出access数据到Excel,就是直接调用access的导出功能,此方法速度超级快 

private   void  AccessExport(String strFilePath)
{
    Access.ApplicationClass oAccess = new  Access.ApplicationClass();
    oAccess.Visible = false ;
     try
    {
         // Access9
        oAccess.OpenCurrentDatabase(strFilePath, false );
         // 导出到Excel
        oAccess.DoCmd.TransferSpreadsheet(Access.AcDataTransferType.acExport,Access.AcSpreadSheetType.acSpreadsheetTypeExcel9, " test1 " , " D:/test1.xls " , true , null , null );
         // 导出到txt
        oAccess.DoCmd.TransferText(Access.AcTextTransferType.acExportDelim, "" , " test1 " , " D:/test1.txt " , true , "" , 0 );
        oAccess.CloseCurrentDatabase();
        oAccess.DoCmd.Quit(Access.AcQuitOption.acQuitSaveNone);
        System.Runtime.InteropServices.Marshal.ReleaseComObject(oAccess);
        oAccess = null ;
        MessageBox.Show( " 导入成功 " );
    }
     catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
     finally
    {
        GC.Collect();
    }
}

II. 此方法速度也是超级快，只不过导出的格式非标准的Excel格式，默认工作表名与文件名相同

private   void  DataTableToExcel(String strDBPath)
{
    String AccessConnectionString = " Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " + strDBPath;
    System.Data.OleDb.OleDbConnection con = new  System.Data.OleDb.OleDbConnection(AccessConnectionString);
    System.Data.OleDb.OleDbDataAdapter oda = new  System.Data.OleDb.OleDbDataAdapter( " select * from test1 " ,con);
    System.Data.DataTable dt = new  System.Data.DataTable();
    oda.Fill(dt);
    con.Close();

    String FilePath = " D:/test1.xls " ;
    System.IO.FileStream objFileStream;
    System.IO.StreamWriter objStreamWriter;
    objFileStream = new  System.IO.FileStream(FilePath,System.IO.FileMode.Create,System.IO.FileAccess.Write);
    objStreamWriter = new  System.IO.StreamWriter(objFileStream,System.Text.Encoding.Unicode);
    String strLine = "" ;
     for (Int32 i = 0 ;i < dt.Columns.Count;i ++ )
    { // Convert.ToChar(9) 
        strLine = strLine + dt.Columns[i].ColumnName.ToString() + Convert.ToChar( 9 );
    }
    objStreamWriter.WriteLine(strLine);
    strLine = "" ;
     for (Int32 i = 0 ;i < dt.Rows.Count;i ++ )
    {
        strLine = strLine + (i + 1 ) + Convert.ToChar( 9 );
         for (Int32 j = 1 ;j < dt.Columns.Count;j ++ )
        {
            strLine = strLine + dt.Rows[i][j].ToString() + Convert.ToChar( 9 );
        }
        objStreamWriter.WriteLine(strLine);
        strLine = "" ;
    }
    objStreamWriter.Close();
    objFileStream.Close();
}

 III.用Ado.net 此方法速度较以上两个显得慢了一些，数据量越大越明显

private   void  AdoExportExcel(String strDBPath)
{
    String AccessConnectionString = " Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " + strDBPath;
    System.Data.OleDb.OleDbConnection con = new  System.Data.OleDb.OleDbConnection(AccessConnectionString);
    System.Data.OleDb.OleDbDataAdapter oda = new  System.Data.OleDb.OleDbDataAdapter( " select * from test1 " ,con);
    System.Data.DataTable dt = new  System.Data.DataTable();
    oda.Fill(dt);
    con.Close();

    String strFilePath = " D:/test.xls " ;
    Int64 totalCount = dt.Rows.Count;
    Int64 rowRead = 0 ;
     float  percent = 0 ;
    System.Data.OleDb.OleDbParameter[] parm = new  System.Data.OleDb.OleDbParameter[dt.Columns.Count];
    String connString = " Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " + strFilePath + " ;Extended Properties='Excel 8.0;' " ;
    System.Data.OleDb.OleDbConnection objCon = new  System.Data.OleDb.OleDbConnection(connString);
    System.Data.OleDb.OleDbCommand objCom = new  System.Data.OleDb.OleDbCommand();
    objCom.Connection = objCon;
    objCon.Open();
     // 建立表结构
    objCom.CommandText = @" create table Sheet1(序号 Integer,名称 varchar) " ;
    objCom.ExecuteNonQuery();
     // 建立插入动作的Command,OleDbCommand是支持parameters的，但是使用的时候和SqlCommand有所区别
     // sqlCom.CommandText=@"insert into Sheet1 values(@Id,@Name)";
    objCom.CommandText = @" insert into Sheet1 values(?,?) " ;
    parm[ 0 ] = new  System.Data.OleDb.OleDbParameter( " @Id " ,System.Data.OleDb.OleDbType.Integer);
    objCom.Parameters.Add(parm[ 0 ]);
    parm[ 1 ] = new  System.Data.OleDb.OleDbParameter( " @Name " ,System.Data.OleDb.OleDbType.VarChar);
    objCom.Parameters.Add(parm[ 1 ]);
     // 便利DataTable将数据插入新建的Excel文件中
     for (Int32 i = 0 ;i < dt.Rows.Count;i ++ )
    {
        parm[ 0 ].Value = dt.Rows[i][ 0 ];
         for (Int32 j = 1 ;j < parm.Length;j ++ )
            parm[j].Value = dt.Rows[i][j];
        objCom.ExecuteNonQuery();
        rowRead ++ ;
        percent = (( float )( 100 * rowRead)) / totalCount;
        Console.WriteLine( " 正在导出数据，已导出[ " + percent.ToString( " 0.00 " ) + " %]... " );
         if (i == dt.Rows.Count - 1 )
                                                   Console.WriteLine( " 请稍后.... " );
        System.Windows.Forms.Application.DoEvents();
    }
    objCon.Close();
}

 IV. 此方法调用com组件(Excel)，速度都慢于以上3个方法

private   void  ComExportToExcel(String strDBPath)
{
    String AccessConnectionString = " Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " + strDBPath;
    System.Data.OleDb.OleDbConnection con = new  System.Data.OleDb.OleDbConnection(AccessConnectionString);
    System.Data.OleDb.OleDbDataAdapter oda = new  System.Data.OleDb.OleDbDataAdapter( " select * from test1 " ,con);
    System.Data.DataTable dt = new  System.Data.DataTable();
    oda.Fill(dt);
    con.Close();

    String strFilePath = " D:/test.xls " ;
    Int64 totalCount = dt.Rows.Count;
    Int64 rowRead = 0 ;
     float  percent = 0 ;
    Excel.Application xlApp = null ;
    xlApp = new  Excel.ApplicationClass();
    Excel.Workbooks workbooks = xlApp.Workbooks;
                     Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
    Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[ 1 ]; // 取得sheet1
    Excel.Range range;
     // 写入字段
     for (Int32 i = 0 ;i < dt.Columns.Count;i ++ )
    {
        worksheet.Cells[ 1 ,i + 1 ] = dt.Columns[i].ColumnName;
        range = (Excel.Range)worksheet.Cells[ 1 ,i + 1 ];
    }
     for (Int32 r = 0 ;r < dt.Rows.Count;r ++ )
    {
         for (Int32 i = 0 ;i < dt.Columns.Count;i ++ )
        {
            worksheet.Cells[r + 2 ,i + 1 ] = dt.Rows[r][i];
        }
        rowRead ++ ;
        percent = (( float )( 100 * rowRead)) / totalCount;
        Console.WriteLine( " 正在导出数据，已导出[ " + percent.ToString( " 0.00 " ) + " %]... " );
         if (r == dt.Rows.Count - 1 )
            Console.WriteLine( " 请稍后.... " );
        System.Windows.Forms.Application.DoEvents();
    }
    range = worksheet.get_Range(worksheet.Cells[ 2 , 1 ],worksheet.Cells[dt.Rows.Count + 1 ,dt.Columns.Count]);
    workbook.Saved = true ;
    workbook.SaveCopyAs(strFilePath);
}

 V. 利用剪贴板 ,有人说此方法很快,不使用Web,web可以用二维数组

private   void  ExportToExcel(String strDBPath)
{
    String AccessConnectionString = " Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " + strDBPath;
    System.Data.OleDb.OleDbConnection con = new  System.Data.OleDb.OleDbConnection(AccessConnectionString);
    System.Data.OleDb.OleDbDataAdapter oda = new  System.Data.OleDb.OleDbDataAdapter( " select * from test1 " ,con);
    System.Data.DataTable dt = new  System.Data.DataTable();
    oda.Fill(dt);
    con.Close();

    String strFilePath = " D:/test.xls " ;
    Object oMissing = System.Reflection.Missing.Value;
    Excel.ApplicationClass xlApp = new  Excel.ApplicationClass();
     try
    {
        xlApp.Visible = false ;
        xlApp.DisplayAlerts = false ;
        Excel.Workbooks oBooks = xlApp.Workbooks;
        Excel.Workbook xlWorkbook = null ;
        xlWorkbook = oBooks.Open(strFilePath,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing,oMissing);
        Excel.Worksheet xlWorksheet;
         // 添加一个新的Sheet页
        xlWorksheet = (Excel.Worksheet)xlWorkbook.Worksheets.Add(oMissing,oMissing, 1 ,oMissing);
         // 以TableName作为新加的Sheet页名
        xlWorksheet.Name = " 企业 " ;
         // 取出这个DataTable中的所有值，暂存于stringBuffer中
        String stringBuffer = "" ;
         for (Int32 j = 0 ;j < dt.Rows.Count;j ++ )
        {
             for (Int32 k = 0 ;k < dt.Columns.Count;k ++ )
            {
                stringBuffer += dt.Rows[j][k].ToString();
                 if (k < dt.Columns.Count - 1 )
                    stringBuffer += " " ;                        
            }
            stringBuffer += " " ;
        }
         // 利用系统剪贴板
        System.Windows.Forms.Clipboard.SetDataObject( "" );
         // 将stringBuffer放入剪贴板
        System.Windows.Forms.Clipboard.SetDataObject(stringBuffer);
         // 选中这个sheet页中的第一个单元格
        ((Excel.Range)xlWorksheet.Cells[ 1 , 1 ]).Select();
         // 粘贴
        xlWorksheet.Paste(oMissing,oMissing);
         // 清空系统剪贴板
        System.Windows.Forms.Clipboard.SetDataObject( "" );

         // 保存并关闭这个工作薄
        xlWorkbook.Close(Excel.XlSaveAction.xlSaveChanges,oMissing,oMissing);
        System.Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkbook);
        xlWorkbook = null ;
        xlApp.Quit();
        xlApp = null ;
    }
     catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
}

 

<script type="text/javascript"> </script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>