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, 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.一个一个单元格的进行插入

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 //循环获取excel单元格的值一次一次的插入，excuteSql为执行的存储过程
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();

二快速导入导出

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 // 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(" 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 //非空讀入數據
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 //加入只加入一個列的話,那么就會其他數據庫列都默認為空。
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;//每次传输的行数

35 //bcp.NotifyAfter = 100;//进度提示的行数

36 bulkCopy.BatchSize = 100;

37 bulkCopy.NotifyAfter = 100;

38 bulkCopy.WriteToServer((IDataReader)dr);

41}

42 }

43 //Closing connection

44 excelConnection.Close();

45 }

47 DateTime t2 = DateTime.Now;

48 Response.Write(" End time:" + t2.ToString());

49 Response.Write(" use time:" + ((TimeSpan)(t2 - t1)).Milliseconds.ToString() + " Milliseconds");

50 Response.Write(" inser record count :3307");

2.快速导出db的数据到excel

这种方法就是利用

Excel.QueryTables
Excel.QueryTable

Querytable把数据快速导入excel里。我们在做复杂报表的时候，这个用的是比较多了，但是单单会这个没有用，它只是快速的把db里的数据放放到excel里，

在做大量数据而且需要设定excel样式的时候我们会选择先用这种方法把数据导入excel一个临时sheet，再利sheet复制，sheet移动，和一些excel样式设定，以及

excel一个强大的自动填充的功能，那么这些就可以让我们快速的做出花样多试的excel报表，当然这个要求我们比较熟练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 = "@";     //设置单元格格式为文本
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 /// <summary>
2 /// 讀取excel數據和插入公式
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(" 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 //進行宏的循環應用與單元格的刪除和添加，多個單元格默認宏自動操作。46 Excel.Range range3 = excelkk.get_Range("B25","E25");47 range2.AutoFill(range3,Excel.XlAutoFillType.xlFillDefault);48 //刪除表的指定行數操作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 //獲取最大用過的行數55 Excel.Worksheet wsheet1 = (Excel.Worksheet)excelkk.Worksheets.get_Item(1);56 int n =wsheet1.UsedRange.Cells.Columns.Count;57 Response.Write(n.ToString() + " ");58 //MessageBox.Show(n.ToString());59 n = wsheet1.UsedRange.Cells.Rows.Count;60 Response.Write(n.ToString() + " ");61 //MessageBox.Show(n.ToString());62 //數據的複製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 單個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

虽然我们拥有强大的NPOI，不过我还是写出来，希望对大家理解office有一定的用处。

有错误的地方欢迎大家拍砖,希望交流和共享。


--------------------------------------------------------
Range 对象是 Excel 应用程序中最经常使用的对象；在操作 Excel 内的任何区域之前，都需要将其表示为一个 Range 对象，然后使用该 Range 对象的方法和属性。一个 Range 对象代表一个单元格、一行、一列、包含一个或者更多单元块（可以是连续的单元格，也可以式不连续的单元格）的选定单元格，甚至是多个工作表上的一组单元格。
在代码中引用范围。
Address 属性：这个属性返回范围坐标的字符串，坐标以下面几种格式之一表示，包括：“$A$1”（单元格在位置 A1）、“$1”（在工作表的第一行）和“$A$1:$C$5”（范围包括介于 A1 和 C5 之间矩形内的所有单元格）。“$”表示绝对坐标（而非相对坐标）。
Excel.Worksheet ws = (Excel.Worksheet)ThisWorkbook.Worksheets[1];
Excel.Range rng;
rng = ThisApplication.ActiveCell;
rng = ws.get_Range("A1", Type.Missing);//使用对象的 Range 属性指定一个区域
rng = ws.get_Range("A1:B12", Type.Missing);//使用对象的 Range 属性指定一个区域
rng = (Excel.Range)ws.Cells[1, 1];//使用工作表的 Cells 属性，指定单个行和列值
rng = ThisApplication.Range("SomeRangeName", Type.Missing);//引用一个命名范围

rng = ws.Range("A1", "C5")//直接引用范围的 Cells、Rows 或 Columns 属性，作用相同
rng = ws.Range("A1", "C5").Cells
rng = ws.Range("A1", "C5").Rows
rng = ws.Range("A1", "C5").Columns
rng = (Excel.Range)ws.Rows[1, Type.Missing];//引用特定行或特定列或行和列的范围
rng = (Excel.Range)ws.Rows["1:3", Type.Missing];
rng = (Excel.Range)ws.Columns[3, Type.Missing];
//Application 对象的 Selection 属性返回与选定单元格对应的范围
rng = (Excel.Range)ThisApplication.Selection;

//创建一个包含其他两个合并范围的范围（在引号内指定两个范围，并用逗号隔开）
rng = ThisApplication.get_Range("A1:D4, F2:G5", Type.Missing);//与后三行效果相同
rng1 = ThisApplication.get_Range("A1", "D4");
rng2 = ThisApplication.get_Range("F2", "G5");
rng = ThisApplication.Union(rng1, rng2, Type.Missing, Type.Missing, ……);
//创建一个引用其他两个范围重叠部分的范围（在引号内指定两个范围，并不使用分隔符）
rng = ThisApplication.get_Range("A1:D16 B2:F14", Type.Missing);//与后三行效果相同
rng1 = ThisApplication.get_Range("A1", "D16");
rng2 = ThisApplication.get_Range("B2", "F14");
rng = ThisApplication.Intersect(rng1, rng2,Type.Missing, Type.Missing, Type.Missing, Type.Missing,……);
//使用范围的 Offset 属性取得相对于初始范围的一个偏移
rng = (Excel.Range) ws.Cells[1, 1];
for (int i = 1; i <= 5; i++)
{
    rng.get_Offset(i, 0).Value2 = i.ToString();
}
//使用范围的 CurrentRegion 属性取得一个代表当前区域的范围，这个当前区域由最近的空行和列限定,即连接在一起的单元格
 ThisApplication.get_Range("C3", Type.Missing).CurrentRegion.Font.Bold = true;
//使用范围的 Areas 属性取得范围集合，其中每个范围对应于范围内容的一个区域
rng = ThisApplication.get_Range("Test", Type.Missing);
for (int i = 1; i <= rng.Areas.Count; i++)
{
     MessageBox.Show(
        rng.Areas[i].get_Address(Type.Missing, Type.Missing,
        Excel.XlReferenceStyle.xlA1, Type.Missing, Type.Missing));
}
//使用 End 属性，以及一个 XlDirection 枚举值（xlUp、xlToRight、xlToLeft、xlDown）来取得一个代表区域末端单元格的范围(即四个方向上离该选中范围最远的单元格)
Excel.Range rngLeft, rngRight, rngUp, rngDown;
rng = (Excel.Range) ThisApplication.Selection;
rngRight = rng.get_End(Excel.XlDirection.xlToRight);
rngLeft = rng.get_End(Excel.XlDirection.xlToLeft);
rngUp = rng.get_End(Excel.XlDirection.xlUp);
rngDown = rng.get_End(Excel.XlDirection.xlDown);
//使用 EntireRow 或 EntireColumn 属性引用包含特定范围的行或列
rng = ThisApplication.get_Range("Test", Type.Missing);
rng.Areas[2].EntireRow.Font.Bold = true;
在代码中操作范围。
自动填充：AutoFill
查找：Find
排序：Sort
Cells 获取一个 Range 对象，该对象表示工作表上的所有单元格（不仅仅是当前正在使用的单元格）。
Cells[行,列]
Columns 获取一个表示工作表上的所有列的 Range 对象
Rows 获取一个表示工作表上的所有行的 Range 对象

原文：//http://www.cnblogs.com/MR_ke/

posted @ 2016-08-12 20:46 小-波阅读(11026) 评论(0) 编辑收藏举报

刷新页面返回顶部

小波

C#操作excel(多种方法比较)

公告