A. Read
有两种方法:
a.OleDbConnection 示例代码如下:
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'";
其中参数解释:
IMEX=1 解决数字与字符混合时,识别不正常的情况
b.调用Com组件 需要添加Microsot.Excel 9.0 Object Library组件。
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的导出功能,此方法速度超级快
{
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格式,默认工作表名与文件名相同
{
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 此方法速度较以上两个显得慢了一些,数据量越大越明显
{
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个方法
{
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可以用二维数组
{
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>