-
[转载]一个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![](/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](/Images/OutliningIndicators/ContractedBlock.gif)
{
11
public class OfficeUse
12![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
13
public OfficeUse()
14![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{ }
15![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
16
/// 读取Excel文档返回DataSet["table1"]
17
/// </summary>
18
/// <param name="Path">文件名称</param>
19
/// <returns>返回一个数据集</returns>
20
public DataSet ReadExcelToDS(string Path)
21![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
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![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
35
/// 根据数据表创建Excel
36
/// </summary>
37
/// <param name="dt">要创建的数据表DataTable</param>
38
public void CreateExcelWorkbook(System.Data.DataTable dt)
39![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
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![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
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![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
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![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
65
ri++;
66
di = 0;
67
foreach (DataColumn dc in dt.Columns)
68![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
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![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**///// 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![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
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![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
117
118
}
119![](/Images/OutliningIndicators/InBlock.gif)
120
}
121![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
122
/// 杀死运行中的进程
123
/// </summary>
124
/// <param name="processName">进程名</param>
125
public void KillProcess(string processName)
126![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
127
System.Diagnostics.Process[] procs = System.Diagnostics.Process.GetProcessesByName(processName);
128![](/Images/OutliningIndicators/InBlock.gif)
129
foreach (System.Diagnostics.Process procCur in procs)
130![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
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![](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <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
}
-
相关阅读:
SAP B1的几点不足
对公司内审员培训的总结
我们为了什么而活
ERP实施一周总结
SAP B1中物料主数据的术语解释
好像回到了以前
ERP总结
WinHex
Delphi和Windows主题相关的报错
事件
-
原文地址:https://www.cnblogs.com/RobotTech/p/1217584.html
Copyright © 2020-2023
润新知