C#操作excel多种方法比较.docx
《C#操作excel多种方法比较.docx》由会员分享，可在线阅读，更多相关《C#操作excel多种方法比较.docx（8页珍藏版）》请在冰豆网上搜索。
C#操作excel多种方法比较
C#操作excel（多种方法比较）
我们在做excel资料的时候，通常有以下方法。
一.导入导出excel常用方法：
1.用查询表的方式查询并show在数据集控件上。
代码
publicstaticstringstrCon="Provider=Microsoft.Jet.OLEDB.4.0;DataSource=C:
\\08.xls;ExtendedProperties=Excel8.0";
publicstaticDataSetds;
protectedvoidPage_Load（objectsender,EventArgse）
{
OleDbConnectionconn=newOleDbConnection（strCon）;
stringsql="select*from[Sheet1$]";
conn.Open（）;
OleDbDataAdaptermyCommand=newOleDbDataAdapter（sql,strCon）;
ds=newDataSet（）;
myCommand.Fill（ds,"[Sheet1$]"）;
conn.Close（）;
datagrid1.DataMember="[Sheet1$]";
datagrid1.DataSource=ds;
datagrid1.DataBind（）;
//Excel.Applicationexcel=newExcel.Application（）;
//excel.Application.Workbooks.Add（true）;
//excel.Visible=true;
}
2.一个一个单元格的进行插入
代码
1stringstr=@"DataSource=IT-428E4EA4B0C7\SQLEXPRESS;InitialCatalog=TestBase;IntegratedSecurity=True";
2SqlConnectionconn=newSqlConnection（str）;
3conn.Open（）;
4intn=0;
5for（inti=1;i<20;i++）
6{
7if（n>3）
8break;
9else
10if（msheet.Cells.get_Range（"A"+i,Type.Missing）.Text.ToString（）==""&&n<=3）
11{n++;}
12else
13{
14 //循环获取excel单元格的值一次一次的插入，excuteSql为执行的存储过程
15 excuteSql（msheet.Cells.get_Range（"B"+i,Type.Missing）.Text.ToString（）,
16msheet.Cells.get_Range（"B"+（i+1）,Type.Missing）.Text.ToString（）,
17msheet.Cells.get_Range（"B"+（i+2）,Type.Missing）.Text.ToString（）,
18conn）;
19i=i+3;
20
21}
22}
23
24conn.Close（）;
二快速导入导出
1.我们都知道当向db里批量插入数据的时候我们会选择SqlBulkCopy
if （dataTable!
=null && dataTable.Rows.Count!
=0）
{
sqlBulkCopy.WriteToServer（dataTable）;
}
这个可以看 深山老林新发的一篇SQLServer中批量插入数据方式的性能对比下面是SqlBulkCopy的方法，这个方法有一个弊端就是当excel某一列即有文字，还有日期的时候，会出现null值，我在网上查了一些资料说连接字串加上;HDR=YES;IMEX=1'的时候会都当做字符处理，但是还是会出现一些bug，所以建议最好先把excel数据分析到datatable里然后再用SqlBulkCopy倒入数据库
代码
1//blockcopytoDBfromExcel
2//Byxijun,
3//step1createanexcelfileC:
\Inetpub\wwwroot\test.xls,fillcell（1,1）with"Data",cell（1,2）with"name"
4//step2createtablenamed"Data"with2column（"data","name"）inyourDB
5//therethecodebelow:
6DateTimet1=DateTime.Now;
7Response.Write（"
starttime:
"+t1.ToString（））;
8stringExcelFile=@"C:
\\20090916_Hub_Report.xls";
9stringexcelConnectionString=@"Provider=Microsoft.Jet.OLEDB.4.0;DataSource="+ExcelFile+";ExtendedProperties='Excel8.0;HDR=YES;IMEX=1'";
10
11using（OleDbConnectionexcelConnection=newOleDbConnection（excelConnectionString））
12{
13
14excelConnection.Open（）;
15//Gettingsourcedata
16//非空讀入數據
17OleDbCommandcommand=newOleDbCommand（"Select[Region],[CustomerPN],[RMA],[Date],[QTY],[Return/Pull]FROM[20090916_Hub_Report$]",excelConnection）;
18//InitializeSqlBulkCopyobject
19
20using（OleDbDataReaderdr=command.ExecuteReader（））
21{
22//Copydatatodestination
23stringsqlConnectionString=@"DataSource=MININT-G87PHNA\SQLEXPRESS;InitialCatalog=GDS_Service;IntegratedSecurity=True";
24using（SqlBulkCopybulkCopy=newSqlBulkCopy（sqlConnectionString））
25{
26bulkCopy.DestinationTableName="GDS_Hub_data";
27//加入只加入一個列的話,那么就會其他數據庫列都默認為空。
28bulkCopy.ColumnMappings.Add（"Region","region"）;
29bulkCopy.ColumnMappings.Add（"CustomerPN","customer_item_number"）;
30bulkCopy.ColumnMappings.Add（"RMA","Rma"）;
31bulkCopy.ColumnMappings.Add（"Date","date"）;
32bulkCopy.ColumnMappings.Add（"QTY","Qty_1"）;
33bulkCopy.ColumnMappings.Add（"Return/Pull","return_pull"）;
34//bcp.BatchSize=100;//每次传输的行数
35//bcp.NotifyAfter=100;//进度提示的行数
36bulkCopy.BatchSize=100;
37bulkCopy.NotifyAfter=100;
38bulkCopy.WriteToServer（（IDataReader）dr）;
39
40
41}
42}
43//Closingconnection
44excelConnection.Close（）;
45}
46
47DateTimet2=DateTime.Now;
48Response.Write（"
Endtime:
"+t2.ToString（））;
49Response.Write（"
usetime:
"+（（TimeSpan）（t2-t1））.Milliseconds.ToString（）+"Milliseconds"）;
50Response.Write（"
inserrecordcount:
3307"）;
2.快速导出db的数据到excel
这种方法就是利用
Excel.QueryTables
Excel.QueryTable
Querytable把数据快速导入excel里。
我们在做复杂报表的时候，这个用的是比较多了，但是单单会这个没有用，它只是快速的把db里的数据放放到excel里，
在做大量数据而且需要设定excel样式的时候我们会选择先用这种方法把数据导入excel一个临时sheet，再利sheet复制，sheet移动，和一些excel样式设定，以及
excel一个强大的自动填充的功能，那么这些就可以让我们快速的做出花样多试的excel报表，当然这个要求我们比较熟练office的操作，包括宏的操作。
代码
1publicstringquery_table_getdata（stringsourpath）
2{
3stringstr_path=sourpath.Substring（0,sourpath.Length-22）;
4str_path=str_path+"basic.xls";
5
6Excel.QueryTablesm_objQryTables=null;
7Excel.QueryTablem_objQryTable=null;
8Excel.Applicationm_objExcel=null;
9Excel.Workbooksm_objBooks=null;
10Excel.Workbookm_objBook=null;
11Excel.Sheetsm_objSheets=null;
12Excel.Worksheetm_objSheet=null;
13Excel.Rangem_objRange=null;
14m_objExcel=newExcel.Application（）;
15
16//try
17//{
18m_objBooks=m_objExcel.Workbooks;
19m_objBooks.Open（sourpath,Type.Missing,Type.Missing,Type.Missing,Type.Missing,
20Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing）;
21
22m_objBook=（Excel.Workbook）m_objBooks.get_Item
（1）;
23
24
25m_objSheets=（Excel.Sheets）m_objBook.Worksheets;
26m_objSheet=（Excel.Worksheet）m_objSheets.get_Item
（1）;
27m_objRange=m_objSheet.get_Range（"A2",Type.Missing）;
28m_objQryTables=m_objSheet.QueryTables;
29stringsqlstr="SELECT[day01],[day02],[day03],[day04],[day05],[day06],[day07],[day08],[day09],[day10],[day11],[day12],[day13],[day14]";
30sqlstr+=",[week01],[week02],[week03],[week04],[week05],[week06],[week07],[week08],[week09],[week10],[week11],[week12],[week13],[week14]";
31sqlstr+=",[week15],[week16],[week17],[week18],[week19],[week20],[week21],[week22],[week23],[week24]";
32sqlstr+="FROM[GDS_Service].[dbo].[GDS_Service_Report_Base]orderbygroupID,id";
33//可以放在配置文件里
34stringconn=@"Provider=SQLOLEDB.1;DataSource=MININT-G87PHNA\SQLEXPRESS;uid=xijun_ke;Password=12345678;
InitialCatalog=GDS_Service;PersistSecurityInfo=False;";
35
36m_objQryTable=（Excel.QueryTable）m_objQryTables.Add（"OLEDB;"+conn,m_objRange,sqlstr）;
37
38m_objQryTable.RefreshStyle=Excel.XlCellInsertionMode.xlInsertEntireRows;
39
40m_objQryTable.Refresh（false）;
41m_objBook.SaveAs（str_path,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Excel.XlSaveAsAccessMode.xlExclusive,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing）;
42m_objBook.Close（false,Type.Missing,Type.Missing）;
43//}
44//catch（Exceptionee）
45//{
46//mp.WriteLog（ee.ToString（））;
47//}
48//finally
49//{
50m_objExcel.Quit（）;
51GC.Collect（）;
52//}
53returnstr_path;
54
55}
com操作excel的一些特性操作：
range.NumberFormatLocal = "@"; //设置单元格格式为文本
range = （Range）worksheet.get_Range（"A1", "E1"）; //获取Excel多个单元格区域：
本例做为Excel表头
range.Merge（0）; //单元格合并动作
worksheet.Cells[1, 1] = "Excel单元格赋值"; //Excel单元格赋值
range.Font.Size = 15; //设置字体大小
range.Font.Underline=true; //设置字体是否有下划线
range.Font.Name="黑体"; 设置字体的种类
range.HorizontalAlignment=XlHAlign.xlHAlignCenter; //设置字体在单元格内的对其方式
range.ColumnWidth=15; //设置单元格的宽度
range.Cells.Interior.Color=System.Drawing.Color.FromArgb（255,204,153）.ToArgb（）; //设置单元格的背景色
range.Borders.LineStyle=1; //设置单元格边框的粗细
range.BorderAround（XlLineStyle.xlContinuous,XlBorderWeight.xlThick,XlColorIndex.xlColorIndexAutomatic,System.Drawing.Color.Black.ToArgb（））; //给单元格加边框
range.EntireColumn.AutoFit（）; //自动调整列宽
Range.HorizontalAlignment= xlCenter; // 文本水平居中方式
Range.VerticalAlignment= xlCenter //文本垂直居中方式
Range.WrapText=true; //文本自动换行
Range.Interior.ColorIndex=39; //填充颜色为淡紫色
Range.Font.Color=clBlue; //字体颜色
xlsApp.DisplayAlerts=false; //保存Excel的时候，不弹出是否保存的窗口直接进行保存
workbook.SaveCopyAs（temp）;/**////填入完信息之后另存到路径及文件名字
excel宏操作，sheet和单元格操作：
代码
1///
2///讀取excel數據和插入公式
3///
4///
5///
6protectedvoidButton2_Click（objectsender,EventArgse）
7{
8DateTimet1=DateTime.Now;
9Response.Write（"
starttime:
"+t1.ToString（））;
10Excel.Applicationexcelkk=newExcel.Application（）;
11
12excelkk.Workbooks.Add（true）;
13introw=2;
14DataTablemyTable=ds.Tables["[Sheet1$]"];
15for（inti=0;i16{
17excelkk.Cells[1,1+i]=myTable.Columns[i].ColumnName.ToString（）;
18}
19for（inti=0;i20{
21for（intj=0;j22{
23excelkk.Cells[row,j+1]=myTable.Rows[i][j].ToString（）;
24}
25
26row++;
27}
28
29
30//取得特定單元格的值
31excelkk.Visible=true;
32this.TextBoxChange.Text=excelkk.get_Range（"A2",Type.Missing）.Text.ToString（）;
33//表的單元格合并
34Excel.Rangerange1=excelkk.get_Range（"A2","D4"）;
35range1.Merge（Type.Missing）;
36
37
38
39
40//想表格中插入求和的值
41Excel.Rangerange2=excelkk.get_Range（"B25",Type.Missing）;
42range2.Formula="=SUM（B2:
B24）";
43range2.Calculate（）;
44
45//進行宏的循環應用與單元格的刪除和添加，多個單元格默認宏自動操作。
46Excel.Rangerange3=excelkk.get_Range（"B25","E25"）;
47range2.AutoFill（range3,Excel.XlAutoFillType.xlFillDefault）;
48//刪除表的指定行數操作
49Excel.Rangerange7=null;
50range7=excelkk.get_Range（excelkk.Cells[2,2],excelkk.Cells[4,4]）;
51range7.Select（）;
52range7.EntireRow.Delete（Excel.XlDirection.xlUp）;
53
54//獲取最大用過的行數
55Excel.Worksheetwsheet1=（Excel.Worksheet）excelkk.Worksheets.get_Item
（1）;
56intn=wsheet1.UsedRange.Cells.Columns.Count;
57Response.Write（n.ToString（）+"
"）;
升级会员 