C#操作excel(多種方法比較)

昵稱12577723 2013-07-30

展開全文

我們?cè)谧鰁xcel資料的時(shí)候，通常有以下方法。

一.導(dǎo)入導(dǎo)出excel常用方法：

1.用查詢表的方式查詢并show在數(shù)據(jù)集控件上。

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, EventArgs 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;
        datagrid1.DataBind();


        //Excel.Application excel = new Excel.Application();
        //excel.Application.Workbooks.Add(true);
        //excel.Visible = true;
    }

2.一個(gè)一個(gè)單元格的進(jìn)行插入

 1         string str = @"Data Source=IT-428E4EA4B0C7\SQLEXPRESS;Initial Catalog=TestBase;Integrated Security=True";
 2         SqlConnection conn = new SqlConnection(str);
 3         conn.Open();
 4         int n = 0;
 5         for (int i = 1; i < 20; i++)
 6         {
 7             if (n > 3)
 8                 break;
 9             else
10                 if (msheet.Cells.get_Range("A" + i, Type.Missing).Text.ToString() == "" && n <= 3)
11                 { n++; }
12                 else
13                 {
14  //循環(huán)獲取excel單元格的值一次一次的插入，excuteSql為執(zhí)行的存儲(chǔ)過程
15                      excuteSql(msheet.Cells.get_Range("B" + i, Type.Missing).Text.ToString(),
16                        msheet.Cells.get_Range("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();

二快速導(dǎo)入導(dǎo)出

1.我們都知道當(dāng)向db里批量插入數(shù)據(jù)的時(shí)候我們會(huì)選擇SqlBulkCopy

if (dataTable!=null && dataTable.Rows.Count!=0)
            {
                sqlBulkCopy.WriteToServer(dataTable);
            }

這個(gè)可以看深山老林新發(fā)的一篇

 1 // block copy to DB from Excel
 2         //By xijun, 
 3         //step 1 create an excel file  C:\Inetpub\wwwroot\test.xls , fill 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("<br>start time:" + t1.ToString());
 8         string ExcelFile = @"C:\\20090916_Hub_Report.xls";
 9         string excelConnectionString = @"Provider=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 data
16             //非空讀入數(shù)據(jù)
17             OleDbCommand command = new OleDbCommand("Select [Region],[CustomerPN],[RMA],[Date],[QTY],[Return/Pull] FROM [20090916_Hub_Report$]  ", excelConnection);
18             // Initialize SqlBulkCopy object
19 
20             using (OleDbDataReader dr = command.ExecuteReader())
21             {
22                 // Copy data to destination
23                 string sqlConnectionString = @"Data Source=MININT-G87PHNA\SQLEXPRESS;Initial Catalog=GDS_Service;Integrated Security=True";
24                 using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString))
25                 {
26                     bulkCopy.DestinationTableName = "GDS_Hub_data";
27                     //加入只加入一個(gè)列的話,那么就會(huì)其他數(shù)據(jù)庫列都默認(rèn)為空。
28                     bulkCopy.ColumnMappings.Add("Region", "region");
29                     bulkCopy.ColumnMappings.Add("CustomerPN", "customer_item_number");
30                     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;//每次傳輸?shù)男袛?shù)
35                     //bcp.NotifyAfter = 100;//進(jìn)度提示的行數(shù)
36                     bulkCopy.BatchSize = 100;
37                     bulkCopy.NotifyAfter = 100;
38                     bulkCopy.WriteToServer((IDataReader)dr);
39                     
40 
41                 }
42             }
43             //Closing connection
44             excelConnection.Close();
45         }
46 
47         DateTime t2 = DateTime.Now;
48         Response.Write("<br>End time:" + t2.ToString());
49         Response.Write("<br>use time:" + ((TimeSpan)(t2 - t1)).Milliseconds.ToString() + " Milliseconds");
50         Response.Write("<br>inser record count :3307");

2.快速導(dǎo)出db的數(shù)據(jù)到excel

這種方法就是利用

Excel.QueryTables
Excel.QueryTable

Querytable把數(shù)據(jù)快速導(dǎo)入excel里。我們?cè)谧鰪?fù)雜報(bào)表的時(shí)候，這個(gè)用的是比較多了，但是單單會(huì)這個(gè)沒有用，它只是快速的把db里的數(shù)據(jù)放放到excel里，

在做大量數(shù)據(jù)而且需要設(shè)定excel樣式的時(shí)候我們會(huì)選擇先用這種方法把數(shù)據(jù)導(dǎo)入excel一個(gè)臨時(shí)sheet，再利sheet復(fù)制，sheet移動(dòng)，和一些excel樣式設(shè)定，以及

excel一個(gè)強(qiáng)大的自動(dòng)填充的功能，那么這些就可以讓我們快速的做出花樣多試的excel報(bào)表，當(dāng)然這個(gè)要求我們比較熟練office的操作，包括宏的操作。

 1   public string query_table_getdata(string sourpath)
 2         {
 3             string str_path = sourpath.Substring(0, sourpath.Length - 22);
 4             str_path = str_path + "basic.xls";
 5 
 6             Excel.QueryTables m_objQryTables = null;
 7             Excel.QueryTable m_objQryTable = null;
 8             Excel.Application m_objExcel = null;
 9             Excel.Workbooks m_objBooks = null;
10             Excel.Workbook m_objBook = null;
11             Excel.Sheets m_objSheets = null;
12             Excel.Worksheet m_objSheet = null;
13             Excel.Range m_objRange = null;
14             m_objExcel = new Excel.Application();
15 
16             //try
17             //{
18                 m_objBooks = m_objExcel.Workbooks;
19                 m_objBooks.Open(sourpath, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
20                Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
21 
22                 m_objBook = (Excel.Workbook)m_objBooks.get_Item(1);
23 
24 
25                 m_objSheets = (Excel.Sheets)m_objBook.Worksheets;
26                 m_objSheet = (Excel.Worksheet)m_objSheets.get_Item(1);
27                 m_objRange = m_objSheet.get_Range("A2", Type.Missing);
28                 m_objQryTables = m_objSheet.QueryTables;
29                 string sqlstr = "SELECT [day01],[day02],[day03],[day04],[day05],[day06],[day07],[day08],[day09],[day10],[day11],[day12],[day13],[day14]";
30                 sqlstr += ",[week01] ,[week02],[week03],[week04],[week05],[week06],[week07],[week08],[week09],[week10],[week11],[week12],[week13],[week14]";
31                 sqlstr += ",[week15],[week16],[week17],[week18],[week19],[week20],[week21],[week22],[week23],[week24]";
32                 sqlstr += " FROM [GDS_Service].[dbo].[GDS_Service_Report_Base] order by groupID ,id";
33                              //可以放在配置文件里
34                 string conn = @"Provider=SQLOLEDB.1;Data Source=MININT-G87PHNA\SQLEXPRESS;uid=xijun_ke;Password=12345678;
                   Initial Catalog=GDS_Service;Persist Security Info=False;";
35 
36                 m_objQryTable = (Excel.QueryTable)m_objQryTables.Add("OLEDB;" + conn, m_objRange, sqlstr);
37 
38                 m_objQryTable.RefreshStyle = Excel.XlCellInsertionMode.xlInsertEntireRows;
39 
40                 m_objQryTable.Refresh(false);
41                 m_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);
42                 m_objBook.Close(false, Type.Missing, Type.Missing);
43             //}
44             //catch (Exception ee)
45             //{
46             //    mp.WriteLog(ee.ToString());
47             //}
48             //finally
49             //{
50                 m_objExcel.Quit();
51                 GC.Collect();
52             //}
53             return str_path;
54 
55         }

com操作excel的一些特性操作：

range.NumberFormatLocal = "@";     //設(shè)置單元格格式為文本 
range = (Range)worksheet.get_Range("A1", "E1");     //獲取Excel多個(gè)單元格區(qū)域：本例做為Excel表頭
range.Merge(0);     //單元格合并動(dòng)作
worksheet.Cells[1, 1] = "Excel單元格賦值";     //Excel單元格賦值
range.Font.Size = 15;     //設(shè)置字體大小
range.Font.Underline=true;     //設(shè)置字體是否有下劃線
range.Font.Name="黑體";     設(shè)置字體的種類
range.HorizontalAlignment=XlHAlign.xlHAlignCenter;     //設(shè)置字體在單元格內(nèi)的對(duì)其方式
range.ColumnWidth=15;     //設(shè)置單元格的寬度
range.Cells.Interior.Color=System.Drawing.Color.FromArgb(255,204,153).ToArgb();     //設(shè)置單元格的背景色
range.Borders.LineStyle=1;     //設(shè)置單元格邊框的粗細(xì)
range.BorderAround(XlLineStyle.xlContinuous,XlBorderWeight.xlThick,XlColorIndex.xlColorIndexAutomatic,System.Drawing.Color.Black.ToArgb());     //給單元格加邊框
range.EntireColumn.AutoFit();     //自動(dòng)調(diào)整列寬
Range.HorizontalAlignment= xlCenter;     // 文本水平居中方式
Range.VerticalAlignment= xlCenter     //文本垂直居中方式
Range.WrapText=true;     //文本自動(dòng)換行
Range.Interior.ColorIndex=39;     //填充顏色為淡紫色
Range.Font.Color=clBlue;     //字體顏色
xlsApp.DisplayAlerts=false;     //保存Excel的時(shí)候，不彈出是否保存的窗口直接進(jìn)行保存
   workbook.SaveCopyAs(temp);/**////填入完信息之后另存到路徑及文件名字

excel宏操作，sheet和單元格操作：

 1     /// <summary>
 2     /// 讀取excel數(shù)據(jù)和插入公式
 3     /// </summary>
 4     /// <param name="sender"></param>
 5     /// <param name="e"></param>
 6     protected void Button2_Click(object sender, EventArgs e)
 7     {
 8         DateTime t1 = DateTime.Now;
 9         Response.Write("<br>start time:" + t1.ToString());
10         Excel.Application excelkk = new Excel.Application();
11         
12         excelkk.Workbooks.Add(true);
13         int row = 2;
14         DataTable myTable = ds.Tables["[Sheet1$]"];
15         for (int i = 0; i < myTable.Columns.Count; i++)
16         {
17             excelkk.Cells[1, 1 + i] = myTable.Columns[i].ColumnName.ToString();
18         }
19         for (int i = 0; i < myTable.Rows.Count; i++)
20         {
21             for (int j = 0; j < myTable.Columns.Count; j++)
22             {
23                 excelkk.Cells[row, j + 1] = myTable.Rows[i][j].ToString();
24             }
25             
26             row++;
27         }
28 
29       
30        //取得特定單元格的值
31         excelkk.Visible = true;
32         this.TextBoxChange.Text = excelkk.get_Range("A2", Type.Missing).Text.ToString();
33         //表的單元格合并
34         Excel.Range range1 = excelkk.get_Range("A2", "D4");
35         range1.Merge(Type.Missing);
36         
37         
38         
39         
40         //想表格中插入求和的值
41         Excel.Range range2 = excelkk.get_Range("B25", Type.Missing);
42         range2.Formula = "=SUM(B2:B24)";
43         range2.Calculate();
44 
45         //進(jìn)行宏的循環(huán)應(yīng)用與單元格的刪除和添加，多個(gè)單元格默認(rèn)宏自動(dòng)操作。
46         Excel.Range range3 = excelkk.get_Range("B25","E25");
47         range2.AutoFill(range3,Excel.XlAutoFillType.xlFillDefault);
48         //刪除表的指定行數(shù)操作
49         Excel.Range range7 = null;
50         range7 = excelkk.get_Range(excelkk.Cells[2, 2], excelkk.Cells[4, 4]);
51         range7.Select();
52         range7.EntireRow.Delete(Excel.XlDirection.xlUp);
53 
54         //獲取最大用過的行數(shù)
55         Excel.Worksheet wsheet1 = (Excel.Worksheet)excelkk.Worksheets.get_Item(1);
56         int n =wsheet1.UsedRange.Cells.Columns.Count;
57         Response.Write(n.ToString() + "<br>");
58         //MessageBox.Show(n.ToString());
59         n = wsheet1.UsedRange.Cells.Rows.Count;
60         Response.Write(n.ToString() + "<br>");
61         //MessageBox.Show(n.ToString());
62         //數(shù)據(jù)的複製
63         Excel.Range range4 = excelkk.get_Range("A2", "B25");
64         Excel.Range range5 = excelkk.get_Range("E3", "F25");
65         //range4.get_Offset(1,4).Select();
66         range4.Copy(range5);
67         //停用警告信息
68         excelkk.DisplayAlerts = false;
69         GC.Collect();     
70 
71     }
72 
73 
74 單個(gè)sheet里求和：
75 Excel.Range range2 = excelkk.get_Range("B25", Type.Missing);
76         range2.Formula = "=SUM(B2:B24)";
77         range2.Calculate();
78 
79 跨sheet求和：
80  Excel.Worksheet wsheet1 = (Excel.Worksheet)excelSql.Worksheets.get_Item(1);
81         Excel.Range range3 =wsheet1.get_Range("A23", Type.Missing);
82         range3.Formula = "=Sheet3!B8+Sheet3!B12";
83         range3.Calculate();
84

雖然我們擁有強(qiáng)大的NPOI，不過我還是寫出來，希望對(duì)大家理解office有一定的用處。

本站是提供個(gè)人知識(shí)管理的網(wǎng)絡(luò)存儲(chǔ)空間，所有內(nèi)容均由用戶發(fā)布，不代表本站觀點(diǎn)。請(qǐng)注意甄別內(nèi)容中的聯(lián)系方式、誘導(dǎo)購買等信息，謹(jǐn)防詐騙。如發(fā)現(xiàn)有害或侵權(quán)內(nèi)容，請(qǐng)點(diǎn)擊一鍵舉報(bào)。

轉(zhuǎn)藏 分享

QQ空間 QQ好友新浪微博微信

獻(xiàn)花（0） +1

來自：昵稱12577723 > 《c# excel讀寫》

舉報(bào)/認(rèn)領(lǐng)