本文介绍使用Excel进行数据导入导出的多种方法，包括通过查询表显示数据、逐个单元格插入数据、使用SqlBulkCopy批量导入等。同时分享了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(
"
<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
//
非空讀入數據
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);
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.快速导出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(
"
<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
//
進行宏的循環應用與單元格的刪除和添加，多個單元格默認宏自動操作。
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()
+
"
<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
//
數據的複製
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有一定的用处。
有错误的地方欢迎大家拍砖,希望交流和共享。
扫一扫
574
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
