《C操作excel多种方法比较》由会员分享，可在线阅读，更多相关《C操作excel多种方法比较（8页珍藏版）》请在金锄头文库上搜索。
1、文档供参考，可复制、编制，期待您的好评与关注！ C#操作excel(多种方法比较) 我们在做excel资料的时候，通常有以下方法。一.导入导出excel常用方法：1.用查询表的方式查询并show在数据集控件上。代码 public static string strCon = Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source =C:08.xls;Extended Properties=Excel 8.0; public static DataSet ds; protected void Page_Load(object sender, EventA
2、rgs e) OleDbConnection conn = new OleDbConnection(strCon); string sql = select * from Sheet1$; conn.Open(); OleDbDataAdapter myCommand = new OleDbDataAdapter(sql, strCon); ds = new DataSet(); myCommand.Fill(ds, Sheet1$); conn.Close(); datagrid1.DataMember = Sheet1$; datagrid1.DataSource = ds; datagr
3、id1.DataBind(); /Excel.Application excel = new Excel.Application(); /excel.Application.Workbooks.Add(true); /excel.Visible = true; 2.一个一个单元格的进行插入代码 1 string str = Data Source=IT-428E4EA4B0C7SQLEXPRESS;Initial Catalog=TestBase;Integrated Security=True; 2 SqlConnection conn = new SqlConnection(str); 3
4、 conn.Open(); 4 int n = 0; 5 for (int i = 1; i 3) 8 break; 9 else10 if (msheet.Cells.get_Range(A + i, Type.Missing).Text.ToString() = & n = 3)11 n+; 12 else13 14 /循环获取excel单元格的值一次一次的插入，excuteSql为执行的存储过程15 excuteSql(msheet.Cells.get_Range(B + i, Type.Missing).Text.ToString(),16 msheet.Cells.get_Range
5、(B + (i + 1), Type.Missing).Text.ToString(),17 msheet.Cells.get_Range(B + (i + 2), Type.Missing).Text.ToString(),18 conn);19 i = i + 3;20 21 22 23 24 conn.Close();二快速导入导出1.我们都知道当向db里批量插入数据的时候我们会选择SqlBulkCopyif(dataTable!=null&dataTable.Rows.Count!=0)sqlBulkCopy.WriteToServer(dataTable);这个可以看深山老林新发的一
6、篇SQLServer中批量插入数据方式的性能对比下面是SqlBulkCopy的方法，这个方法有一个弊端就是当excel某一列即有文字，还有日期的时候，会出现null值，我在网上查了一些资料说连接字串加上;HDR=YES;IMEX=1的时候会都当做字符处理，但是还是会出现一些bug，所以建议最好先把excel数据分析到datatable里然后再用SqlBulkCopy倒入数据库代码 1 / block copy to DB from Excel 2 /By xijun, 3 /step 1 create an excel file C:Inetpubwwwroottest.xls , fill
7、cell(1,1) with Data,cell(1,2) with name 4 /step 2 create table named Data with 2 column (data,name) in your DB 5 /there the code below: 6 DateTime t1 = DateTime.Now; 7 Response.Write(start time: + t1.ToString(); 8 string ExcelFile = C:20090916_Hub_Report.xls; 9 string excelConnectionString = Provide
8、r=Microsoft.Jet.OLEDB.4.0;Data Source= + ExcelFile + ;Extended Properties=Excel 8.0;HDR=YES;IMEX=1;10 11 using (OleDbConnection excelConnection = new OleDbConnection(excelConnectionString)12 13 14 excelConnection.Open();15 /Getting source data16 /非空讀入數據17 OleDbCommand command = new OleDbCommand(Sele
9、ct Region,CustomerPN,RMA,Date,QTY,Return/Pull FROM 20090916_Hub_Report$ , excelConnection);18 / Initialize SqlBulkCopy object19 20 using (OleDbDataReader dr = command.ExecuteReader()21 22 / Copy data to destination23 string sqlConnectionString = Data Source=MININT-G87PHNASQLEXPRESS;Initial Catalog=G
10、DS_Service;Integrated Security=True;24 using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString)25 26 bulkCopy.DestinationTableName = GDS_Hub_data;27 /加入只加入一個列的話,那么就會其他數據庫列都默認為空。28 bulkCopy.ColumnMappings.Add(Region, region);29 bulkCopy.ColumnMappings.Add(CustomerPN, customer_item_number);3
11、0 bulkCopy.ColumnMappings.Add(RMA, Rma);31 bulkCopy.ColumnMappings.Add(Date, date);32 bulkCopy.ColumnMappings.Add(QTY, Qty_1);33 bulkCopy.ColumnMappings.Add(Return/Pull, return_pull);34 /bcp.BatchSize = 100;/每次传输的行数35 /bcp.NotifyAfter = 100;/进度提示的行数36 bulkCopy.BatchSize = 100;37 bulkCopy.NotifyAfter = 100;38 bulkCopy.WriteToServer(IDataReader)dr);39 40 41 42 43 /Closing connection44 excelConnection.Close();45 46 47 DateTime t2 = DateTime.Now;48 Response.Write(End time: + t2.ToString();49 Response.Write(use time:
