C# Operate Excel

Excel数据读写方法
本文介绍了多种Excel数据读写的方法,包括使用OleDbConnection、调用Com组件、Ado.net等技术实现数据的高效处理。特别针对混合数据类型的问题提出了解决方案。

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" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值