C#操作Excel知识点

最新推荐文章于 2021-02-19 13:35:11 发布

ibmfashion

最新推荐文章于 2021-02-19 13:35:11 发布

阅读量1.8k

点赞数 2

分类专栏： c#开发excel报表文章标签： excel c# dataset null application 工作

c#开发excel报表专栏收录该内容

9 篇文章 0 订阅

订阅专栏

近期在使用C#操作excel，主要是读取excel模板，复制其中的模板sheet页，生成多个sheet页填充相应数据后另存到excel文件，所用到的知识点如下。

　　一、添加引用和命名空间

　　添加Microsoft.Office.Interop.Excel引用，它的默认路径是C:\Program Files\Microsoft Visual Studio 9.0\Visual Studio Tools for Office\PIA\Office12\Microsoft.Office.Interop.Excel.dll

　　代码中添加引用using Microsoft.Office.Interop.Excel;

　　二、Excel类的简单介绍

　　此命名空间下关于Excel类的结构分别为：

　　ApplicationClass - 就是我们的excel应用程序。

　　Workbook - 就是我们平常见的一个个excel文件，经常是使用Workbooks类对其进行操作。

　　Worksheet - 就是excel文件中的一个个sheet页。

　　Worksheet.Cells[row, column] - 就是某行某列的单元格，注意这里的下标row和column都是从1开始的，跟我平常用的数组或集合的下标有所不同。

　　知道了上述基本知识后，利用此类来操作excel就清晰了很多。

　　三、Excel的操作

　　任何操作Excel的动作首先肯定是用excel应用程序，首先要new一个ApplicationClass 实例，并在最后将此实例释放。

　　ApplicationClass xlsApp = new ApplicationClass(); // 1. 创建Excel应用程序对象的一个实例，相当于我们从开始菜单打开Excel应用程序。

　　if (xlsApp == null)

　　{

　　//对此实例进行验证，如果为null则表示运行此代码的机器可能未安装Excel

　　}

　　1. 打开现有的Excel文件

　　Workbook workbook = xlsApp.Workbooks.Open(excelFilePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

　　Worksheet mySheet = workbook.Sheets[1] as Worksheet; //第一个sheet页

　　mySheet.Name = "testsheet"; //这里修改sheet名称

　　2.复制sheet页

　　mySheet.Copy(Type.Missing, workbook.Sheets[1]); //复制mySheet成一个新的sheet页，复制完后的名称是mySheet页名称后加一个(2)，这里就是testsheet(2)，复制完后，Worksheet的数量增加一个

　　注意这里Copy方法的两个参数，指是的复制出来新的sheet页是在指定sheet页的前面还是后面，上面的例子就是指复制的sheet页在第一个sheet页的后面。

　　3.删除sheet页

　　xlsApp.DisplayAlerts = false; //如果想删除某个sheet页，首先要将此项设为fasle。

　　(xlsApp.ActiveWorkbook.Sheets[1] as Worksheet).Delete();

　　4.选中sheet页

　　(xlsApp.ActiveWorkbook.Sheets[1] as Worksheet).Select(Type.Missing); //选中某个sheet页

　　5.另存excel文件

　　workbook.Saved = true;

　　workbook.SaveCopyAs(filepath);

　　6.释放excel资源

　　workbook.Close(true, Type.Missing, Type.Missing);

　　workbook = null;

　　xlsApp.Quit();

　　xlsApp = null;

添加的文件头:

using System.Reflection; // 引用这个才能使用Missing字段

using Excel;

Excel.ApplicationClass excel = new Excel.ApplicationClass();

excel.Visible = true; //激活Excel

Workbook wBook = excel.Workbooks.Add(true);

// Worksheet wSheet = (Excel._Worksheet)wBook.ActiveSheet;

Worksheet wSheet = (Excel.Worksheet)wBook.ActiveSheet;

excel.Cells[3, 5] = "本公司电话: " + Phone;

excel.Cells[4, 5] = "本公司传真: " + Zhen;

excel.Cells[5, 5] = "联系人: " + ComName;

excel.Cells[4, 1] = "客户: " + CustomerName;

excel.Cells[5, 1] = "联系人: " + Associate;

excel.Cells[3, 8] = "户名:";

excel.Cells[3, 9] = AccountName;

excel.Cells[4, 8] = "开户行:";

excel.Cells[4, 9] = BranchName;

excel.Cells[5, 8] = "帐号:";

excel.Cells[5, 9] = "'" + AccountID;

//设置禁止弹出保存和覆盖的询问提示框

excel.DisplayAlerts = false;

excel.AlertBeforeOverwriting = false;

//保存工作薄

// wBook.Save();

//每次保存激活的表，这样才能多次操作保存不同的Excel表，默认保存位置是在”我的文档"

excel.Cells.Font.Size = 12;

excel.Cells.Font.Bold = false;

// Excel.Range m_objRange = m_objRange.get_Range(1, 3);

wSheet.get_Range(excel.Cells[1, 3], excel.Cells[1, 3]).Font.Size = 24;

wSheet.get_Range(excel.Cells[1, 3], excel.Cells[1, 3]).Font.Bold = true;

wSheet.get_Range(excel.Cells[3, 1], excel.Cells[3, 1]).Font.ColorIndex = 3;//此处设为红色，不能用Font.Color来设置颜色

// m_objRange.Cells.Font.Size = 24;

// m_objRange.Cells.Font.Bold = true;

excel.ActiveWorkbook.SaveCopyAs(filename);

excel.Quit();

代码注释部分只是简单描述各语句的原由，个别的还是值得推敲的。

语句一 Workbook wBook = excel.Workbooks.Add(true);

Workbooks.Add的参数是个object类型，通常使用true或null,表明工作簿在默认文档下创建，或者使用枚举值

XlWBATemplate.xlWBATWorksheet,但如果传入一个excel完整文件名，却相当于打开已有工作簿。

语句二 Worksheet wSheet = (Excel.Worksheet)wBook.ActiveSheet; 这样可以操作多个工作表的话，实例化之后加入到wBook.Worksheets中去。如果是打开已存在的工作簿，这条语句也可能会报错，最好是调用wBook.ActiveSheet来获取或者再加些判断。

语句三

excel.ActiveWorkbook.SaveCopyAs(filename);这两句代码至关重要，而且必不可少，否则，保存时会弹出“是否保存sheet1.xls”的对话框。判断当前激活的表，并保存这个表。

语句四 excel.Quit();

这个关闭一直有疑点，因为C#操作com非托管对象时，凭借Quit()还没有释放掉对象，excel进程不一定会终止,于是,有人使用KillProcess()来处理,我个人认为这不是一个好主意,可能会破坏其它正在执行的excel进程。目前我使用app

null;权作安慰吧。不过有一点是一定要做到，就是在Quit()前不能再有任何更改，不然还是会弹出保存的对话框。所以退出前确保一定是执行过WorkBook或是Application的Save()方法的。

VS2005[C#] 操作 Excel 全攻略

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using Microsoft.Office.Interop.Excel;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Reflection;
namespace ExcelPrj
{
/// <summary>
/// Excel 系统中的主文件Excel.exe 本身就是 COM 组件,通过在.NET 项目中引用Exel.exe 文件可以实现对Excel 的功能控制
/// 与COM 组件相互操作是通过使用"包装类"(Wrapper Class) 和"代理"(Proxy) 的机制实现的.包装类使.NET 程序可以识别COM 组件提供的接口,而代理类则是提供对 COM 接口的访问
/// </summary>
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void button3_Click(object sender, EventArgs e)
{
ExportTasks(Bind(), dataGridView1);
}
//如果 Excel 安装在计算机上,侧导出表格内容到 Excel
public void ExportTasks(DataSet TasksData, DataGridView TasksGridView)
{
// 定义要使用的Excel 组件接口
// 定义Application 对象,此对象表示整个Excel 程序
Microsoft.Office.Interop.Excel.Application excelApp = null ;
// 定义Workbook对象,此对象代表工作薄
Microsoft.Office.Interop.Excel.Workbook workBook;
// 定义Worksheet 对象,此对象表示Execel 中的一张工作表
Microsoft.Office.Interop.Excel.Worksheet ws=null;
//定义Range对象,此对象代表单元格区域
Microsoft.Office.Interop.Excel.Range r;
int row = 1; int cell = 1;
try
{
//初始化 Application 对象 excelApp
excelApp = new Microsoft.Office.Interop.Excel.Application();
//在工作薄的第一个工作表上创建任务列表
workBook = excelApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
ws =(Worksheet)workBook.Worksheets[1];
// 命名工作表的名称为 "Task Management"
ws.Name = "Task Management";
#region 创建表格的列头
// 遍历数据表中的所有列
foreach (DataGridViewColumn cs in TasksGridView.Columns)
{
// 假如并不想把主键也显示出来
if (cs.HeaderText != "编号")
{
ws.Cells[row, cell] = cs.HeaderText;
r = (Range)ws.Cells[row, cell];
ws.get_Range(r, r).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;

//此处用来设置列的样式
cell++;
}
}

// 创建行,把数据视图记录输出到对应的Excel 单元格
for (int i = 2; i < TasksData.Tables[0].Rows.Count; i++)
{
for (int j = 1; j < TasksData.Tables[0].Columns.Count; j++)
{

ws.Cells[i, j] = TasksData.Tables[0].Rows[i][j].ToString();
// r = (Range)ws.Cells[i,j];
Range rg = (Range)ws.get_Range(ws.Cells[i, j], ws.Cells[i, j]);
rg.EntireColumn.ColumnWidth = 20;
// rg.Columns.AutoFit();
rg.NumberFormatLocal = "@";
}
}
#endregion
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}

//显示 Excel
excelApp.Visible = true;

}
private void button5_Click(object sender, EventArgs e)
{
DataSet ds = Bind();
dataGridView1.DataSource = ds.Tables[0];
}
private DataSet Bind()
{
SqlConnection conn = new SqlConnection("Server=.;Database=testManage;Integrated Security=SSPI");
SqlDataAdapter da = new SqlDataAdapter("select FNumber,FExamNum,FName,FSex,FJobAdd,FCardID,FBirDate from stuInfo", conn);
DataSet ds = new DataSet();
da.Fill(ds);
return ds;
}
private void button2_Click(object sender, EventArgs e)
{
SaveFileDialog sfd = new SaveFileDialog();
sfd.Title = "请选择将导出的EXCEL文件存放路径";
sfd.FileName = System.DateTime.Now.ToShortDateString() + "-学生信息";
sfd.Filter = "Excel文档(*.xls)|*.xls";
sfd.ShowDialog();

if (sfd.FileName != "")
{

Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
if (excelApp == null)
{
MessageBox.Show("无法创建Excel对象，可能您的机器未安装Excel");
}
else
{
Microsoft.Office.Interop.Excel.Workbooks workbooks = excelApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet =(Worksheet) workbook.Worksheets[1];
DataSet ds=Bind();
for (int i = 1; i < ds.Tables[0].Rows.Count; i++)
{
for (int j = 1; j < ds.Tables[0].Columns.Count;j++ )
{
if (i == 1)
{
worksheet.Cells[i, j] = dataGridView1.Columns[j].HeaderText;

}
worksheet.Cells[i+1, j] = ds.Tables[0].Rows[i][j].ToString();
}
}
//保存方式一：保存WorkBook
//workbook.SaveAs(@"F:\CData.xls",
// Missing.Value,Missing.Value,Missing.Value,Missing.Value,Missing.Value,
// Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,Missing.Value,Missing.Value,Missing.Value,
// Missing.Value,Missing.Value);
//保存方式二：保存WorkSheet
// worksheet.SaveAs(@"F:\CData2.xls",
// Missing.Value, Missing.Value, Missing.Value, Missing.Value,
// Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
保存方式三
//workbook.Saved = true;
//workbook.SaveCopyAs(sfd.FileName);

System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet);
worksheet = null;
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
workbook = null;
workbooks.Close();
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbooks);
workbooks = null;
excelApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
excelApp = null;
MessageBox.Show("导出Excel完成!");
}
}

}
private void button4_Click(object sender, EventArgs e)
{
string strExcelFileName = @"F:\\2007-07-16-学生信息.xls";
string strSheetName = "sheet1";
#region Aspnet 操作Excel 正确
源的定义
//string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = " + strExcelFileName + ";Extended Properties ='Excel 8.0;HDR=NO;IMEX=1'";
Sql语句
//string strExcel = "select * from [" + strSheetName + "$]";
定义存放的数据表
//DataSet ds = new DataSet();
连接数据源
//OleDbConnection conn = new OleDbConnection(strConn);
//conn.Open();
适配到数据源
//OleDbDataAdapter adapter = new OleDbDataAdapter(strExcel, conn);
//adapter.Fill(ds,"res");
//conn.Close();
一般的情况下. Excel 表格的第一行是列名
//dataGridView2.DataSource = ds.Tables["res"];
#endregion
#region COM 组件读取复杂Excel
Microsoft.Office.Interop.Excel.Application excelApp = null;
Microsoft.Office.Interop.Excel.Workbook workBook;
Microsoft.Office.Interop.Excel.Worksheet ws = null;
try
{
excelApp = new Microsoft.Office.Interop.Excel.Application();
workBook = excelApp.Workbooks.Open(@"F:\\Book1.xls", Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
ws = (Worksheet)workBook.Worksheets[1];

//Excel 默认为 256 列..
MessageBox.Show(ws.Cells.Columns.Count.ToString());
excelApp.Quit();
}
catch (Exception ex)
{
throw ex;
}
#endregion
}
}
}

C# 操作Excel多个sheet的具体的操作

1. private void DataViewExcelBySheetMultipleDt(

2. DataSet ds, string fileName)

3. {

4. try

5. {

6. int sheetCount = ds.Tables.Count;

7. GC.Collect();

8. Application excel;

9. _Workbook xBk;

10. _Worksheet xSt = null;

11. excel = new ApplicationClass();

12. xBk = excel.Workbooks.Add(true);

13.

14. int rowIndex = 0;

15. int colIndex = 0;

16. for (int sheetIndex = 0;

17. sheetIndex < sheetCount; sheetIndex++)

18. {

19. rowIndex = 1;

20. colIndex = 1;

21. xSt = (_Worksheet)xBk.Worksheets.Add(

22. Type.Missing, Type.Missing, 1, Type.Missing);

23. switch (sheetIndex)

24. {

25. case 0:

26. xSt.Name = "test1";

27. break;

28. case 1:

29. xSt.Name = "test2";

30. break;

31. case 2:

32. xSt.Name = "test3";

33. break;

34. case 3:

35. xSt.Name = "test4";

36. break;

37. } //C# 操作Excel多个sheet的具体的操作

38. foreach (DataColumn col in ds.Tables[sheetIndex].Columns)

39. {

40. xSt.get_Range(excel.Cells[rowIndex,

41. colIndex], excel.Cells[rowIndex,

42. colIndex]).HorizontalAlignment = XlVAlign.xlVAlignCenter;

43. xSt.get_Range(excel.Cells[rowIndex,

44. colIndex], excel.Cells[rowIndex,

45. colIndex]).Font.Bold = true;

46. excel.Cells[rowIndex, colIndex++] = col.ColumnName;

47. }

48. foreach (DataRow row in ds.

49. Tables[sheetIndex].Rows)

50. {

51. rowIndex++;

52. colIndex = 1;

53. foreach (DataColumn col in ds.

54. Tables[sheetIndex].Columns)

55. {

56. if (col.DataType == System.Type.GetType(

57. "System.DateTime"))

58. {

59. if (!"".Equals(row[col.ColumnName].ToString()))

60. excel.Cells[rowIndex, colIndex] =

61. (Convert.ToDateTime(row[col.ColumnName].

62. ToString())).ToString("MM/dd/yyyy");

63. else

64. excel.Cells[rowIndex, colIndex] = "";

65. }

66. else if (col.DataType == S

67. ystem.Type.GetType("System.String"))

68. {

69. excel.Cells[rowIndex, colIndex] = "'" +

70. row[col.ColumnName].ToString();

71. }

72. else

73. {

74. excel.Cells[rowIndex, colIndex] =

75. row[col.ColumnName].ToString();

76. }

77. colIndex++;

78. } //C# 操作Excel多个sheet的具体的操作

79. }

80. Range allDataWithTitleRange = xSt.get_Range(

81. excel.Cells[1, 1], excel.Cells[rowIndex, colIndex - 1]);

82. allDataWithTitleRange.Select();

83. allDataWithTitleRange.Columns.AutoFit();

84. allDataWithTitleRange.Borders.LineStyle = 1;

85. }

86. string exportDir = "~/Attachment/";

87. string absFileName = HttpContext.

88. Current.Server.MapPath(

89. System.IO.Path.Combine(exportDir, fileName));

90. xBk.SaveCopyAs(absFileName);

91. xBk.Close(false, null, null);

92. excel.Quit();

93.

94. System.Runtime.InteropServices.

95. Marshal.ReleaseComObject(xBk);

96. System.Runtime.InteropServices.

97. Marshal.ReleaseComObject(excel);

98. System.Runtime.InteropServices.

99. Marshal.ReleaseComObject(xSt);

100. //C# 操作Excel多个sheet的具体的操作

101. xBk = null;

102. excel = null;

103. xSt = null;

104. GC.Collect();

105. }

106. catch (Exception ex)

107. {

108.

109. }

110. }

111. private void DataViewExcelBySheetMultipleDt(

112. DataSet ds, string fileName)

113. {

114. try

115. {

116. int sheetCount = ds.Tables.Count;

117. GC.Collect();

118. Application excel;

119. _Workbook xBk;

120. _Worksheet xSt = null;

121. excel = new ApplicationClass();

122. xBk = excel.Workbooks.Add(true);

123. //C# 操作Excel多个sheet的具体的操作

124. int rowIndex = 0;

125. int colIndex = 0;

126. for (int sheetIndex = 0;

127. sheetIndex < sheetCount; sheetIndex++)

128. {

129. rowIndex = 1;

130. colIndex = 1;

131. xSt = (_Worksheet)xBk.Worksheets.Add(

132. Type.Missing, Type.Missing, 1, Type.Missing);

133. switch (sheetIndex)

134. {

135. case 0:

136. xSt.Name = "test1";

137. break;

138. case 1:

139. xSt.Name = "test2";

140. break;

141. case 2:

142. xSt.Name = "test3";

143. break;

144. case 3:

145. xSt.Name = "test4";

146. break;

147. }

148. foreach (DataColumn col in ds.Tables[sheetIndex].Columns)

149. { //C# 操作Excel多个sheet的具体的操作

150. xSt.get_Range(excel.Cells[rowIndex,

151. colIndex], excel.Cells[rowIndex,

152. colIndex]).HorizontalAlignment = XlVAlign.xlVAlignCenter;

153. xSt.get_Range(excel.Cells[rowIndex,

154. colIndex], excel.Cells[rowIndex, colIndex]).Font.Bold = true;

155. excel.Cells[rowIndex, colIndex++] = col.ColumnName;

156. }

157. foreach (DataRow row in ds.Tables[sheetIndex].Rows)

158. {

159. rowIndex++;

160. colIndex = 1;

161. foreach (DataColumn col in ds.Tables[

162. sheetIndex].Columns)

163. {

164. if (col.DataType == System.Type.GetType(

165. "System.DateTime"))

166. {

167. if (!"".Equals(row[col.ColumnName].ToString()))

168. excel.Cells[rowIndex, colIndex] = (

169. Convert.ToDateTime(row[col.ColumnName].

170. ToString())).ToString("MM/dd/yyyy");

171. else

172. excel.Cells[rowIndex, colIndex] = "";

173. }

174. else if (col.DataType ==

175. System.Type.GetType("System.String"))

176. {

177. excel.Cells[rowIndex,

178. colIndex] = "'" + row[col.ColumnName].ToString();

179. }

180. else

181. {

182. excel.Cells[rowIndex,

183. colIndex] = row[col.ColumnName].ToString();

184. }

185. colIndex++;

186. }

187. } //C# 操作Excel多个sheet的具体的操作

188. Range allDataWithTitleRange = xSt.get_Range(

189. excel.Cells[1, 1],

190. excel.Cells[rowIndex, colIndex - 1]);

191. allDataWithTitleRange.Select();

192. allDataWithTitleRange.Columns.AutoFit();

193. allDataWithTitleRange.Borders.LineStyle = 1;

194. }

195. string exportDir = "~/Attachment/";

196. string absFileName = HttpContext.Current.Server.

197. MapPath(System.IO.Path.Combine(exportDir, fileName));

198. xBk.SaveCopyAs(absFileName);

199. xBk.Close(false, null, null);

200. excel.Quit();

201.

202. System.Runtime.InteropServices.

203. Marshal.ReleaseComObject(xBk);

204. System.Runtime.InteropServices.

205. Marshal.ReleaseComObject(excel);

206. System.Runtime.InteropServices.

207. Marshal.ReleaseComObject(xSt);

208.

209. xBk = null;

210. excel = null;

211. xSt = null;

212. GC.Collect();

213. }

214. catch (Exception ex)

215. {

216.

217. }

218. }

上面方法,首先形成一个多个DataTable的DataSet,

C# 操作Excel重点还是

1. 生成一个新的xls时,打开方式,总是会提示进程占用

2. 用不同的sheet时一定要命名

3. 使用传入一个datatable时,总是会重写第一个sheet

ibmfashion

关注

2
点赞
踩
6

收藏

觉得还不错? 一键收藏
0
评论
C#操作Excel知识点

近期在使用C#操作excel，主要是读取excel模板，复制其中的模板sheet页，生成多个sheet页填充相应数据后另存到excel文件，所用到的知识点如下。　　一、添加引用和命名空间　　添加Microsoft.Office.Interop.Excel引用，它的默认路径是C:\Program Files\Microsoft Visual Studio 9.0\Visual Studio
复制链接

扫一扫

专栏目录