[转载]一个Office 经典操作类
1
using System;
2
using System.Collections.Generic;
3
using System.Text;
4
using System.Data.OleDb;
5
using System.Data;
6
using Excel;
7
using System.Reflection;
8![](/Images/OutliningIndicators/None.gif)
9
namespace OtherTools
10
{
11
public class OfficeUse
12
{
13
public OfficeUse()
14
{ }
15
/// <summary>
16
/// 读取Excel文档返回DataSet["table1"]
17
/// </summary>
18
/// <param name="Path">文件名称</param>
19
/// <returns>返回一个数据集</returns>
20
public DataSet ReadExcelToDS(string Path)
21
{
22
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 8.0;";
23
OleDbConnection conn = new OleDbConnection(strConn);
24
conn.Open();
25
string strExcel = "";
26
OleDbDataAdapter myCommand = null;
27
DataSet ds = null;
28
strExcel = "select * from [sheet1$]";
29
myCommand = new OleDbDataAdapter(strExcel, strConn);
30
ds = new DataSet();
31
myCommand.Fill(ds, "table1");
32
return ds;
33
}
34
/// <summary>
35
/// 根据数据表创建Excel
36
/// </summary>
37
/// <param name="dt">要创建的数据表DataTable</param>
38
public void CreateExcelWorkbook(System.Data.DataTable dt)
39
{
40![](/Images/OutliningIndicators/InBlock.gif)
41![](/Images/OutliningIndicators/InBlock.gif)
42
//RemoveFiles(strCurrentDir); // utility method to clean up old files
43![](/Images/OutliningIndicators/InBlock.gif)
44
Excel.Application oXL;
45
Excel._Workbook oWB;
46
Excel._Worksheet oSheet;
47
Excel.Range oRng;
48
try
49
{
50
GC.Collect();
51
oXL = new Excel.Application();
52
oXL.Visible = true;
53
//Get a new workbook.
54
oWB = (Excel._Workbook)(oXL.Workbooks.Add(Missing.Value));
55
oSheet = (Excel._Worksheet)oWB.ActiveSheet;
56
// Create Header and sheet
57
for (int j = 0; j < dt.Columns.Count; j++)
58
{
59
oSheet.Cells[1, j + 1] = dt.Columns[j].Caption.ToString();
60
}
61
int ri = 1;
62
int di = 0;
63
foreach (DataRow dr in dt.Rows)
64
{
65
ri++;
66
di = 0;
67
foreach (DataColumn dc in dt.Columns)
68
{
69
di++;
70
oSheet.Cells[ri, di] = dr[dc.ColumnName].ToString();
71
}
72
}
73
// build the sheet contents
74![](/Images/OutliningIndicators/InBlock.gif)
75
//Format A1:Z1 as bold, vertical alignment = center.
76
oSheet.get_Range("A1", "Z1").Font.Bold = true;
77
oSheet.get_Range("A1", "Z1").VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
78
//AutoFit columns A:Z.
79
oRng = oSheet.get_Range("A1", "Z1");
80
oRng.EntireColumn.AutoFit();
81
//oXL.Visible = false;
82
//oXL.UserControl = false;
83
//string strFile = "report" + System.DateTime.Now.Ticks.ToString() + ".xls";
84
//oWB.SaveAs(strCurrentDir + strFile, Excel.XlFileFormat.xlWorkbookNormal,
85
//null, null, false, false, Excel.XlSaveAsAccessMode.xlShared, false, false, null, null, null);
86
//// Need all following code to clean up and extingush all references!!!
87
//oWB.Close(null, null, null);
88
//oXL.Workbooks.Close();
89
//oXL.Quit();
90
//System.Runtime.InteropServices.Marshal.ReleaseComObject(oRng);
91
//System.Runtime.InteropServices.Marshal.ReleaseComObject(oXL);
92
//System.Runtime.InteropServices.Marshal.ReleaseComObject(oSheet);
93
//System.Runtime.InteropServices.Marshal.ReleaseComObject(oWB);
94
//oSheet = null;
95
//oWB = null;
96
//oXL = null;
97
//GC.Collect(); // force final cleanup!
98
}
99![](/Images/OutliningIndicators/InBlock.gif)
100
catch (Exception theException)
101
{
102![](/Images/OutliningIndicators/InBlock.gif)
103
String errorMessage;
104![](/Images/OutliningIndicators/InBlock.gif)
105
errorMessage = "Error: ";
106![](/Images/OutliningIndicators/InBlock.gif)
107
errorMessage = String.Concat(errorMessage, theException.Message);
108![](/Images/OutliningIndicators/InBlock.gif)
109
errorMessage = String.Concat(errorMessage, " Line: ");
110![](/Images/OutliningIndicators/InBlock.gif)
111
errorMessage = String.Concat(errorMessage, theException.Source);
112
System.Windows.Forms.MessageBox.Show("导出未能完成:" + errorMessage);
113
KillProcess("Excel");
114
}
115
finally
116
{
117
118
}
119![](/Images/OutliningIndicators/InBlock.gif)
120
}
121
/// <summary>
122
/// 杀死运行中的进程
123
/// </summary>
124
/// <param name="processName">进程名</param>
125
public void KillProcess(string processName)
126
{
127
System.Diagnostics.Process[] procs = System.Diagnostics.Process.GetProcessesByName(processName);
128![](/Images/OutliningIndicators/InBlock.gif)
129
foreach (System.Diagnostics.Process procCur in procs)
130
{
131
procCur.Kill();
132
procCur.Close();
133
}
134
}
135![](/Images/OutliningIndicators/InBlock.gif)
136![](/Images/OutliningIndicators/InBlock.gif)
137![](/Images/OutliningIndicators/InBlock.gif)
138![](/Images/OutliningIndicators/InBlock.gif)
139![](/Images/OutliningIndicators/InBlock.gif)
140
/// <summary>
141
/// 写入Excel文档
142
/// </summary>
143
/// <param name="Path">文件名称</param>
144
//public bool SaveFP2toExcel(string Path)
145
//{
146
// try
147
// {
148
// string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 8.0;";
149
// OleDbConnection conn = new OleDbConnection(strConn);
150
// conn.Open();
151
// System.Data.OleDb.OleDbCommand cmd = new OleDbCommand();
152
// cmd.Connection = conn;
153
// for (int i = 0; i < fp2.Sheets[0].RowCount - 1; i++)
154
// {
155
// if (fp2.Sheets[0].Cells[i, 0].Text != "")
156
// {
157
// cmd.CommandText = "INSERT INTO [sheet1$] (工号,姓名,部门,职务,日期,时间) VALUES('" + fp2.Sheets[0]. Cells[i, 0].Text + "','" +
158
// fp2.Sheets[0].Cells[i, 1].Text + "','" + fp2.Sheets[0].Cells[i, 2].Text + "','" + fp2.Sheets[0].Cells[i, 3].Text +
159
// "','" + fp2.Sheets[0].Cells[i, 4].Text + "','" + fp2.Sheets[0].Cells[i, 5].Text + "')";
160
// cmd.ExecuteNonQuery();
161
// }
162
// }
163
// conn.Close();
164
// return true;
165
// }
166
// catch (System.Data.OleDb.OleDbException ex)
167
// {
168
// System.Diagnostics.Debug.WriteLine("写入Excel发生错误:" + ex.Message);
169
// }
170
// return false;
171
//}
172![](/Images/OutliningIndicators/InBlock.gif)
173
}
174
}
![](/Images/OutliningIndicators/None.gif)
2
![](/Images/OutliningIndicators/None.gif)
3
![](/Images/OutliningIndicators/None.gif)
4
![](/Images/OutliningIndicators/None.gif)
5
![](/Images/OutliningIndicators/None.gif)
6
![](/Images/OutliningIndicators/None.gif)
7
![](/Images/OutliningIndicators/None.gif)
8
![](/Images/OutliningIndicators/None.gif)
9
![](/Images/OutliningIndicators/None.gif)
10
![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
11
![](/Images/OutliningIndicators/InBlock.gif)
12
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
13
![](/Images/OutliningIndicators/InBlock.gif)
14
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
15
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
16
![](/Images/OutliningIndicators/InBlock.gif)
17
![](/Images/OutliningIndicators/InBlock.gif)
18
![](/Images/OutliningIndicators/InBlock.gif)
19
![](/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
20
![](/Images/OutliningIndicators/InBlock.gif)
21
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
22
![](/Images/OutliningIndicators/InBlock.gif)
23
![](/Images/OutliningIndicators/InBlock.gif)
24
![](/Images/OutliningIndicators/InBlock.gif)
25
![](/Images/OutliningIndicators/InBlock.gif)
26
![](/Images/OutliningIndicators/InBlock.gif)
27
![](/Images/OutliningIndicators/InBlock.gif)
28
![](/Images/OutliningIndicators/InBlock.gif)
29
![](/Images/OutliningIndicators/InBlock.gif)
30
![](/Images/OutliningIndicators/InBlock.gif)
31
![](/Images/OutliningIndicators/InBlock.gif)
32
![](/Images/OutliningIndicators/InBlock.gif)
33
![](/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
34
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
35
![](/Images/OutliningIndicators/InBlock.gif)
36
![](/Images/OutliningIndicators/InBlock.gif)
37
![](/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
38
![](/Images/OutliningIndicators/InBlock.gif)
39
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
40
![](/Images/OutliningIndicators/InBlock.gif)
41
![](/Images/OutliningIndicators/InBlock.gif)
42
![](/Images/OutliningIndicators/InBlock.gif)
43
![](/Images/OutliningIndicators/InBlock.gif)
44
![](/Images/OutliningIndicators/InBlock.gif)
45
![](/Images/OutliningIndicators/InBlock.gif)
46
![](/Images/OutliningIndicators/InBlock.gif)
47
![](/Images/OutliningIndicators/InBlock.gif)
48
![](/Images/OutliningIndicators/InBlock.gif)
49
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
50
![](/Images/OutliningIndicators/InBlock.gif)
51
![](/Images/OutliningIndicators/InBlock.gif)
52
![](/Images/OutliningIndicators/InBlock.gif)
53
![](/Images/OutliningIndicators/InBlock.gif)
54
![](/Images/OutliningIndicators/InBlock.gif)
55
![](/Images/OutliningIndicators/InBlock.gif)
56
![](/Images/OutliningIndicators/InBlock.gif)
![](/Images/dot.gif)
57
![](/Images/OutliningIndicators/InBlock.gif)
58
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
59
![](/Images/OutliningIndicators/InBlock.gif)
60
![](/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
61
![](/Images/OutliningIndicators/InBlock.gif)
62
![](/Images/OutliningIndicators/InBlock.gif)
63
![](/Images/OutliningIndicators/InBlock.gif)
64
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
65
![](/Images/OutliningIndicators/InBlock.gif)
66
![](/Images/OutliningIndicators/InBlock.gif)
67
![](/Images/OutliningIndicators/InBlock.gif)
68
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
69
![](/Images/OutliningIndicators/InBlock.gif)
70
![](/Images/OutliningIndicators/InBlock.gif)
71
![](/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
72
![](/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
73
![](/Images/OutliningIndicators/InBlock.gif)
74
![](/Images/OutliningIndicators/InBlock.gif)
75
![](/Images/OutliningIndicators/InBlock.gif)
76
![](/Images/OutliningIndicators/InBlock.gif)
77
![](/Images/OutliningIndicators/InBlock.gif)
78
![](/Images/OutliningIndicators/InBlock.gif)
79
![](/Images/OutliningIndicators/InBlock.gif)
80
![](/Images/OutliningIndicators/InBlock.gif)
81
![](/Images/OutliningIndicators/InBlock.gif)
82
![](/Images/OutliningIndicators/InBlock.gif)
83
![](/Images/OutliningIndicators/InBlock.gif)
84
![](/Images/OutliningIndicators/InBlock.gif)
85
![](/Images/OutliningIndicators/InBlock.gif)
86
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
87
![](/Images/OutliningIndicators/InBlock.gif)
88
![](/Images/OutliningIndicators/InBlock.gif)
89
![](/Images/OutliningIndicators/InBlock.gif)
90
![](/Images/OutliningIndicators/InBlock.gif)
91
![](/Images/OutliningIndicators/InBlock.gif)
92
![](/Images/OutliningIndicators/InBlock.gif)
93
![](/Images/OutliningIndicators/InBlock.gif)
94
![](/Images/OutliningIndicators/InBlock.gif)
95
![](/Images/OutliningIndicators/InBlock.gif)
96
![](/Images/OutliningIndicators/InBlock.gif)
97
![](/Images/OutliningIndicators/InBlock.gif)
98
![](/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
99
![](/Images/OutliningIndicators/InBlock.gif)
100
![](/Images/OutliningIndicators/InBlock.gif)
101
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
102
![](/Images/OutliningIndicators/InBlock.gif)
103
![](/Images/OutliningIndicators/InBlock.gif)
104
![](/Images/OutliningIndicators/InBlock.gif)
105
![](/Images/OutliningIndicators/InBlock.gif)
106
![](/Images/OutliningIndicators/InBlock.gif)
107
![](/Images/OutliningIndicators/InBlock.gif)
108
![](/Images/OutliningIndicators/InBlock.gif)
109
![](/Images/OutliningIndicators/InBlock.gif)
110
![](/Images/OutliningIndicators/InBlock.gif)
111
![](/Images/OutliningIndicators/InBlock.gif)
112
![](/Images/OutliningIndicators/InBlock.gif)
113
![](/Images/OutliningIndicators/InBlock.gif)
114
![](/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
115
![](/Images/OutliningIndicators/InBlock.gif)
116
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
117
![](/Images/OutliningIndicators/InBlock.gif)
118
![](/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
119
![](/Images/OutliningIndicators/InBlock.gif)
120
![](/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
121
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
122
![](/Images/OutliningIndicators/InBlock.gif)
123
![](/Images/OutliningIndicators/InBlock.gif)
124
![](/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
125
![](/Images/OutliningIndicators/InBlock.gif)
126
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
127
![](/Images/OutliningIndicators/InBlock.gif)
128
![](/Images/OutliningIndicators/InBlock.gif)
129
![](/Images/OutliningIndicators/InBlock.gif)
130
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
131
![](/Images/OutliningIndicators/InBlock.gif)
132
![](/Images/OutliningIndicators/InBlock.gif)
133
![](/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
134
![](/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
135
![](/Images/OutliningIndicators/InBlock.gif)
136
![](/Images/OutliningIndicators/InBlock.gif)
137
![](/Images/OutliningIndicators/InBlock.gif)
138
![](/Images/OutliningIndicators/InBlock.gif)
139
![](/Images/OutliningIndicators/InBlock.gif)
140
![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
141
![](/Images/OutliningIndicators/InBlock.gif)
142
![](/Images/OutliningIndicators/InBlock.gif)
143
![](/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
144
![](/Images/OutliningIndicators/InBlock.gif)
145
![](/Images/OutliningIndicators/InBlock.gif)
146
![](/Images/OutliningIndicators/InBlock.gif)
147
![](/Images/OutliningIndicators/InBlock.gif)
148
![](/Images/OutliningIndicators/InBlock.gif)
149
![](/Images/OutliningIndicators/InBlock.gif)
150
![](/Images/OutliningIndicators/InBlock.gif)
151
![](/Images/OutliningIndicators/InBlock.gif)
152
![](/Images/OutliningIndicators/InBlock.gif)
153
![](/Images/OutliningIndicators/InBlock.gif)
154
![](/Images/OutliningIndicators/InBlock.gif)
155
![](/Images/OutliningIndicators/InBlock.gif)
156
![](/Images/OutliningIndicators/InBlock.gif)
157
![](/Images/OutliningIndicators/InBlock.gif)
158
![](/Images/OutliningIndicators/InBlock.gif)
159
![](/Images/OutliningIndicators/InBlock.gif)
160
![](/Images/OutliningIndicators/InBlock.gif)
161
![](/Images/OutliningIndicators/InBlock.gif)
162
![](/Images/OutliningIndicators/InBlock.gif)
163
![](/Images/OutliningIndicators/InBlock.gif)
164
![](/Images/OutliningIndicators/InBlock.gif)
165
![](/Images/OutliningIndicators/InBlock.gif)
166
![](/Images/OutliningIndicators/InBlock.gif)
167
![](/Images/OutliningIndicators/InBlock.gif)
168
![](/Images/OutliningIndicators/InBlock.gif)
169
![](/Images/OutliningIndicators/InBlock.gif)
170
![](/Images/OutliningIndicators/InBlock.gif)
171
![](/Images/OutliningIndicators/InBlock.gif)
172
![](/Images/OutliningIndicators/InBlock.gif)
173
![](/Images/OutliningIndicators/ExpandedSubBlockEnd.gif)
174
![](/Images/OutliningIndicators/ExpandedBlockEnd.gif)