- 浏览: 102715 次
- 性别:
- 来自: 天府之国
文章分类
最新评论
-
netkongjian:
不错CAD控件知识,感谢分享[deyi]
CAD速记手册 -
a545807638:
小老树
邓锋对话周鸿祎:中国为何没有Google? -
longxiaoyan:
javaeye表情汇总 -
bangyan2003:
...
javaeye表情汇总 -
xiaofu305:
/images/guest_book/2.gif
javaeye表情汇总
初学c#,由于项目需要操作Excel。网上搜索了一番,资料挺多。不过写的过于冗余,一上来就是一大断代码,看的头疼,
总结了下,不足的忘补充
考虑到兼容问题,针对office2003的操作
---必须的库 Microsoft.Office.Interop.Excel.dll (附件)
----代码中用到的类
using System; using System.Drawing; using System.Collections; using System.ComponentModel; using System.Data; using System.IO; using System.Text; using System.Windows; using System.Reflection; using System.Runtime.InteropServices; using Excel = Microsoft.Office.Interop.Excel;
创建一个新的Excel文件
(代码片段1)
Object missing = Missing.Value; Excel.Application m_objExcel = new Excel.Application(); Excel.Workbooks m_objWorkBooks = m_objExcel.Workbooks; Excel.Workbook m_objWorkBook = m_objWorkBooks.Add(true); Excel.Sheets m_objWorkSheets = m_objWorkBook.Sheets; ; Excel.Worksheet m_objWorkSheet = (Excel.Worksheet)m_objWorkSheets[1]; try { m_objExcel.Save("f:129.xls"); } catch (Exception e) { } finally { m_objWorkBooks.Close(); m_objExcel.Quit(); }
------ 操作单元格 单元格以横坐标和纵坐标进行导航
在代码一try{}catch(){}之前加入以下代码
(代码片段2)
m_objExcel.Cells[1, 1] = "建筑节能评估计算报告书";//第一行第一列文赋值
------合并单元格(在代码一try{}catch(){}之前加入以下代码)
m_objExcel.Cells[1, 1] = "建筑节能评估计算报告书"; Excel.Range range = m_objExcel.get_Range(m_objExcel.Cells[1, 1], m_objExcel.Cells[1, 10]); range.Merge(Type.Missing);//合并单元格
---操作字体大小,颜色,单元格背景色。边框颜色等(在代码一try{}catch(){}之前加入以下代码)
m_objExcel.Cells[1, 1] = "建筑节能评估计算报告书"; Excel.Range range = m_objExcel.get_Range(m_objExcel.Cells[1, 1], m_objExcel.Cells[1, 10]); range.Merge(Type.Missing);//合并单元格 range.Font.Size = 25;//字号 range.HorizontalAlignment = Excel.Constants.xlCenter; //居中对齐 range.Font.Bold = 17;//字体大小 // range.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;//边框 // range.Borders.Color = ColorTranslator.ToOle(Color.Red);//边框颜色 //range.Interior.ColorIndex = 34;背景色
- dll.rar (166.8 KB)
- 下载次数: 373
评论
1 楼
fscyr
2010-06-05
如果Excel格式固定,只是数据行数变动的话,可直接输出成xml的文本格式。
StreamWriter writer1 = new StreamWriter(fn, false);
writer1.WriteLine("<?xml version=\"1.0\"?>");
writer1.WriteLine("<?mso-application progid=\"Excel.Sheet\"?>");
writer1.WriteLine("<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"");
writer1.WriteLine("xmlns:o=\"urn:schemas-microsoft-com:office:office\"");
writer1.WriteLine("xmlns:x=\"urn:schemas-microsoft-com:office:excel\"");
writer1.WriteLine("xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\"");
writer1.WriteLine("xmlns:html=\"http://www.w3.org/TR/REC-html40\">");
writer1.WriteLine("<DocumentProperties xmlns=\"urn:schemas-microsoft-com:office:office\">");
writer1.WriteLine("<Author>Mark</Author>");
writer1.WriteLine("<LastAuthor>Mark</LastAuthor>");
writer1.WriteLine("<Created>2006-04-03T07:39:06Z</Created>");
writer1.WriteLine("<Company>SEALTECH</Company>");
writer1.WriteLine("<Version>11.6568</Version>");
writer1.WriteLine("</DocumentProperties>");
writer1.WriteLine("<ExcelWorkbook xmlns=\"urn:schemas-microsoft-com:office:excel\">");
writer1.WriteLine("<WindowHeight>10020</WindowHeight>");
writer1.WriteLine("<WindowWidth>16035</WindowWidth>");
writer1.WriteLine("<WindowTopX>0</WindowTopX>");
writer1.WriteLine("<WindowTopY>120</WindowTopY>");
writer1.WriteLine("<ProtectStructure>False</ProtectStructure>");
writer1.WriteLine("<ProtectWindows>False</ProtectWindows>");
writer1.WriteLine("</ExcelWorkbook>");
writer1.WriteLine("<Styles>");
writer1.WriteLine("<Style ss:ID=\"Default\" ss:Name=\"Normal\">");
writer1.WriteLine("<Alignment ss:Vertical=\"Center\"/>");
writer1.WriteLine("<Borders/>");
writer1.WriteLine("<Font ss:FontName=\"宋体\" x:CharSet=\"134\" ss:Size=\"12\"/>");
writer1.WriteLine("<Interior/>");
writer1.WriteLine("<NumberFormat/>");
writer1.WriteLine("<Protection/>");
writer1.WriteLine("</Style>");
writer1.WriteLine("<Style ss:ID=\"s21\">");
writer1.WriteLine("<NumberFormat ss:Format=\"@\"/>");
writer1.WriteLine("</Style>");
writer1.WriteLine("<Style ss:ID=\"s23\">");
writer1.WriteLine("<Alignment ss:Horizontal=\"Left\" ss:Vertical=\"Center\"/>");
writer1.WriteLine("</Style>");
writer1.WriteLine("<Style ss:ID=\"s24\">");
writer1.WriteLine("<Alignment ss:Horizontal=\"Left\" ss:Vertical=\"Center\"/>");
writer1.WriteLine("<NumberFormat ss:Format=\"Short Date\"/>");
writer1.WriteLine("</Style>");
writer1.WriteLine("</Styles>");
writer1.WriteLine("<Worksheet ss:Name=\"Sheet1\">");
writer1.WriteLine("<Table ss:ExpandedColumnCount=\"7\" ss:ExpandedRowCount=\""
+ (this.BindingContext[this.dataGrid1.DataSource, this.dataGrid1.DataMember].Count + 1).ToString()
+ "\" x:FullColumns=\"1\"");
writer1.WriteLine("x:FullRows=\"1\" ss:DefaultColumnWidth=\"54\" ss:DefaultRowHeight=\"14.25\">");
writer1.WriteLine("<Column ss:Width=\"21\"/>");
writer1.WriteLine("<Column ss:StyleID=\"s21\" ss:Width=\"96.75\"/>");
writer1.WriteLine("<Column ss:Width=\"39\"/>");
writer1.WriteLine("<Column ss:Width=\"270\"/>");
writer1.WriteLine("<Column ss:Width=\"57\"/>");
writer1.WriteLine("<Column ss:Width=\"45\"/>");
writer1.WriteLine("<Column ss:StyleID=\"s23\" ss:Width=\"83.25\"/>");
writer1.WriteLine("<Row>");
writer1.WriteLine("<Cell ss:Index=\"2\"><Data ss:Type=\"String\">貨品編號</Data></Cell>");
writer1.WriteLine("<Cell><Data ss:Type=\"String\">系列</Data></Cell>");
writer1.WriteLine("<Cell><Data ss:Type=\"String\">簡要説明</Data></Cell>");
writer1.WriteLine("<Cell><Data ss:Type=\"String\">存儲貨位</Data></Cell>");
writer1.WriteLine("<Cell><Data ss:Type=\"String\">庫存</Data></Cell>");
writer1.WriteLine("<Cell><Data ss:Type=\"String\">最後變動日期</Data></Cell>");
writer1.WriteLine("</Row>");
for (int i = 0; i < this.BindingContext[this.dataGrid1.DataSource, this.dataGrid1.DataMember].Count; i++)
{
writer1.WriteLine("<Row>");
writer1.WriteLine("<Cell><Data ss:Type=\"String\">" + this.dataGrid1[i, 0].ToString() + "</Data></Cell>");
writer1.WriteLine("<Cell><Data ss:Type=\"String\">" + this.dataGrid1[i, 1].ToString() + "</Data></Cell>");
writer1.WriteLine("<Cell><Data ss:Type=\"String\">" + this.dataGrid1[i, 2].ToString() + "</Data></Cell>");
writer1.WriteLine("<Cell><Data ss:Type=\"String\">" + this.dataGrid1[i, 3].ToString() + "</Data></Cell>");
writer1.WriteLine("<Cell><Data ss:Type=\"String\">" + this.dataGrid1[i, 4].ToString() + "</Data></Cell>");
writer1.WriteLine("<Cell><Data ss:Type=\"Number\">" + this.dataGrid1[i, 5].ToString() + "</Data></Cell>");
writer1.WriteLine("<Cell ss:StyleID=\"s24\"><Data ss:Type=\"DateTime\">" + String.Format("{0:s}", this.dataGrid1[i, 6]) + "</Data></Cell>");
writer1.WriteLine("</Row>");
}
writer1.WriteLine("</Table>");
writer1.WriteLine("<WorksheetOptions xmlns=\"urn:schemas-microsoft-com:office:excel\">");
writer1.WriteLine("<Selected/>");
writer1.WriteLine("<ProtectObjects>False</ProtectObjects>");
writer1.WriteLine("<ProtectScenarios>False</ProtectScenarios>");
writer1.WriteLine("</WorksheetOptions>");
writer1.WriteLine("</Worksheet>");
writer1.WriteLine("<Worksheet ss:Name=\"Sheet2\">");
writer1.WriteLine("<Table ss:ExpandedColumnCount=\"0\" ss:ExpandedRowCount=\"0\" x:FullColumns=\"1\"");
writer1.WriteLine("x:FullRows=\"1\" ss:DefaultColumnWidth=\"54\" ss:DefaultRowHeight=\"14.25\"/>");
writer1.WriteLine("<WorksheetOptions xmlns=\"urn:schemas-microsoft-com:office:excel\">");
writer1.WriteLine("<ProtectObjects>False</ProtectObjects>");
writer1.WriteLine("<ProtectScenarios>False</ProtectScenarios>");
writer1.WriteLine("</WorksheetOptions>");
writer1.WriteLine("</Worksheet>");
writer1.WriteLine("<Worksheet ss:Name=\"Sheet3\">");
writer1.WriteLine("<Table ss:ExpandedColumnCount=\"0\" ss:ExpandedRowCount=\"0\" x:FullColumns=\"1\"");
writer1.WriteLine("x:FullRows=\"1\" ss:DefaultColumnWidth=\"54\" ss:DefaultRowHeight=\"14.25\"/>");
writer1.WriteLine("<WorksheetOptions xmlns=\"urn:schemas-microsoft-com:office:excel\">");
writer1.WriteLine("<ProtectObjects>False</ProtectObjects>");
writer1.WriteLine("<ProtectScenarios>False</ProtectScenarios>");
writer1.WriteLine("</WorksheetOptions>");
writer1.WriteLine("</Worksheet>");
writer1.WriteLine("</Workbook>");
writer1.Close();
StreamWriter writer1 = new StreamWriter(fn, false);
writer1.WriteLine("<?xml version=\"1.0\"?>");
writer1.WriteLine("<?mso-application progid=\"Excel.Sheet\"?>");
writer1.WriteLine("<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"");
writer1.WriteLine("xmlns:o=\"urn:schemas-microsoft-com:office:office\"");
writer1.WriteLine("xmlns:x=\"urn:schemas-microsoft-com:office:excel\"");
writer1.WriteLine("xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\"");
writer1.WriteLine("xmlns:html=\"http://www.w3.org/TR/REC-html40\">");
writer1.WriteLine("<DocumentProperties xmlns=\"urn:schemas-microsoft-com:office:office\">");
writer1.WriteLine("<Author>Mark</Author>");
writer1.WriteLine("<LastAuthor>Mark</LastAuthor>");
writer1.WriteLine("<Created>2006-04-03T07:39:06Z</Created>");
writer1.WriteLine("<Company>SEALTECH</Company>");
writer1.WriteLine("<Version>11.6568</Version>");
writer1.WriteLine("</DocumentProperties>");
writer1.WriteLine("<ExcelWorkbook xmlns=\"urn:schemas-microsoft-com:office:excel\">");
writer1.WriteLine("<WindowHeight>10020</WindowHeight>");
writer1.WriteLine("<WindowWidth>16035</WindowWidth>");
writer1.WriteLine("<WindowTopX>0</WindowTopX>");
writer1.WriteLine("<WindowTopY>120</WindowTopY>");
writer1.WriteLine("<ProtectStructure>False</ProtectStructure>");
writer1.WriteLine("<ProtectWindows>False</ProtectWindows>");
writer1.WriteLine("</ExcelWorkbook>");
writer1.WriteLine("<Styles>");
writer1.WriteLine("<Style ss:ID=\"Default\" ss:Name=\"Normal\">");
writer1.WriteLine("<Alignment ss:Vertical=\"Center\"/>");
writer1.WriteLine("<Borders/>");
writer1.WriteLine("<Font ss:FontName=\"宋体\" x:CharSet=\"134\" ss:Size=\"12\"/>");
writer1.WriteLine("<Interior/>");
writer1.WriteLine("<NumberFormat/>");
writer1.WriteLine("<Protection/>");
writer1.WriteLine("</Style>");
writer1.WriteLine("<Style ss:ID=\"s21\">");
writer1.WriteLine("<NumberFormat ss:Format=\"@\"/>");
writer1.WriteLine("</Style>");
writer1.WriteLine("<Style ss:ID=\"s23\">");
writer1.WriteLine("<Alignment ss:Horizontal=\"Left\" ss:Vertical=\"Center\"/>");
writer1.WriteLine("</Style>");
writer1.WriteLine("<Style ss:ID=\"s24\">");
writer1.WriteLine("<Alignment ss:Horizontal=\"Left\" ss:Vertical=\"Center\"/>");
writer1.WriteLine("<NumberFormat ss:Format=\"Short Date\"/>");
writer1.WriteLine("</Style>");
writer1.WriteLine("</Styles>");
writer1.WriteLine("<Worksheet ss:Name=\"Sheet1\">");
writer1.WriteLine("<Table ss:ExpandedColumnCount=\"7\" ss:ExpandedRowCount=\""
+ (this.BindingContext[this.dataGrid1.DataSource, this.dataGrid1.DataMember].Count + 1).ToString()
+ "\" x:FullColumns=\"1\"");
writer1.WriteLine("x:FullRows=\"1\" ss:DefaultColumnWidth=\"54\" ss:DefaultRowHeight=\"14.25\">");
writer1.WriteLine("<Column ss:Width=\"21\"/>");
writer1.WriteLine("<Column ss:StyleID=\"s21\" ss:Width=\"96.75\"/>");
writer1.WriteLine("<Column ss:Width=\"39\"/>");
writer1.WriteLine("<Column ss:Width=\"270\"/>");
writer1.WriteLine("<Column ss:Width=\"57\"/>");
writer1.WriteLine("<Column ss:Width=\"45\"/>");
writer1.WriteLine("<Column ss:StyleID=\"s23\" ss:Width=\"83.25\"/>");
writer1.WriteLine("<Row>");
writer1.WriteLine("<Cell ss:Index=\"2\"><Data ss:Type=\"String\">貨品編號</Data></Cell>");
writer1.WriteLine("<Cell><Data ss:Type=\"String\">系列</Data></Cell>");
writer1.WriteLine("<Cell><Data ss:Type=\"String\">簡要説明</Data></Cell>");
writer1.WriteLine("<Cell><Data ss:Type=\"String\">存儲貨位</Data></Cell>");
writer1.WriteLine("<Cell><Data ss:Type=\"String\">庫存</Data></Cell>");
writer1.WriteLine("<Cell><Data ss:Type=\"String\">最後變動日期</Data></Cell>");
writer1.WriteLine("</Row>");
for (int i = 0; i < this.BindingContext[this.dataGrid1.DataSource, this.dataGrid1.DataMember].Count; i++)
{
writer1.WriteLine("<Row>");
writer1.WriteLine("<Cell><Data ss:Type=\"String\">" + this.dataGrid1[i, 0].ToString() + "</Data></Cell>");
writer1.WriteLine("<Cell><Data ss:Type=\"String\">" + this.dataGrid1[i, 1].ToString() + "</Data></Cell>");
writer1.WriteLine("<Cell><Data ss:Type=\"String\">" + this.dataGrid1[i, 2].ToString() + "</Data></Cell>");
writer1.WriteLine("<Cell><Data ss:Type=\"String\">" + this.dataGrid1[i, 3].ToString() + "</Data></Cell>");
writer1.WriteLine("<Cell><Data ss:Type=\"String\">" + this.dataGrid1[i, 4].ToString() + "</Data></Cell>");
writer1.WriteLine("<Cell><Data ss:Type=\"Number\">" + this.dataGrid1[i, 5].ToString() + "</Data></Cell>");
writer1.WriteLine("<Cell ss:StyleID=\"s24\"><Data ss:Type=\"DateTime\">" + String.Format("{0:s}", this.dataGrid1[i, 6]) + "</Data></Cell>");
writer1.WriteLine("</Row>");
}
writer1.WriteLine("</Table>");
writer1.WriteLine("<WorksheetOptions xmlns=\"urn:schemas-microsoft-com:office:excel\">");
writer1.WriteLine("<Selected/>");
writer1.WriteLine("<ProtectObjects>False</ProtectObjects>");
writer1.WriteLine("<ProtectScenarios>False</ProtectScenarios>");
writer1.WriteLine("</WorksheetOptions>");
writer1.WriteLine("</Worksheet>");
writer1.WriteLine("<Worksheet ss:Name=\"Sheet2\">");
writer1.WriteLine("<Table ss:ExpandedColumnCount=\"0\" ss:ExpandedRowCount=\"0\" x:FullColumns=\"1\"");
writer1.WriteLine("x:FullRows=\"1\" ss:DefaultColumnWidth=\"54\" ss:DefaultRowHeight=\"14.25\"/>");
writer1.WriteLine("<WorksheetOptions xmlns=\"urn:schemas-microsoft-com:office:excel\">");
writer1.WriteLine("<ProtectObjects>False</ProtectObjects>");
writer1.WriteLine("<ProtectScenarios>False</ProtectScenarios>");
writer1.WriteLine("</WorksheetOptions>");
writer1.WriteLine("</Worksheet>");
writer1.WriteLine("<Worksheet ss:Name=\"Sheet3\">");
writer1.WriteLine("<Table ss:ExpandedColumnCount=\"0\" ss:ExpandedRowCount=\"0\" x:FullColumns=\"1\"");
writer1.WriteLine("x:FullRows=\"1\" ss:DefaultColumnWidth=\"54\" ss:DefaultRowHeight=\"14.25\"/>");
writer1.WriteLine("<WorksheetOptions xmlns=\"urn:schemas-microsoft-com:office:excel\">");
writer1.WriteLine("<ProtectObjects>False</ProtectObjects>");
writer1.WriteLine("<ProtectScenarios>False</ProtectScenarios>");
writer1.WriteLine("</WorksheetOptions>");
writer1.WriteLine("</Worksheet>");
writer1.WriteLine("</Workbook>");
writer1.Close();
发表评论
-
C# (七) 类
2010-05-10 10:56 797格式 修饰符+类名{ //do somet ... -
c#学习笔记(六)调试和错误处理 基于visual studio IDE
2010-04-07 13:54 1143开发工具:visual studio 1.最简单的方法:打 ... -
c#学习笔记(五) 函数
2010-04-07 11:32 835函数 格式:基本上合java类同 修饰符 返 ... -
c#学习笔记(四) 复杂变量类型:杖举,数组,结构
2010-04-07 11:05 1380数组: 这个没啥好说的,与java类同 杖举 注 ... -
c#学习笔记(三)类型转换
2010-04-07 10:31 931c#,类型转换可分为隐式转换和显式转换 隐式转换: 可以由 ... -
c#学习笔记(二)布尔逻辑,流程控制
2010-04-07 09:43 1038布尔逻辑 流程控制 goto,for, switch, ... -
c#学习笔记(一)变量,表达式,数据类型
2010-04-06 16:44 850开发工具:visual studio 参考书籍:c# ...
相关推荐
C#导出excel文件
C#导出Excel,利用第三方的dll文件,可以改变导出数据的样式,有实例代码。
C# 导出Excel文件 打开Excel文件格式与扩展名指定格式不一致
C#导出Excel和图形.zip
导出EXCEL合并问题。EXCEL样式设置等
c#导出Excel文件 c#导出Excel文件
C#导出EXCEL如何设置单元格类型 - _NET技术 - C# 自己上网找的资料,没有经过测试,下载谨慎
c#导出excel支持多sheet导出,可自定义sheetName,如有疑问请留言,或qq1574697828.c#导出excel支持多sheet导出,可自定义sheetName,如有疑问请留言。
本人自己整理了C#导出Excel和图形方法。 导出Excel只有一个工作目录,导出图形会有多个工作目录。 希望大家能够喜欢 如有疑问,欢迎留言。
c# 导出到Excel (C#)c# 导出到Excel (C#)c# 导出到Excel (C#)c# 导出到Excel (C#)c# 导出到Excel (C#)c# 导出到Excel (C#)c# 导出到Excel (C#)c# 导出到Excel (C#)c# 导出到Excel (C#) winform
C# 导出EXCELC# 导出EXCELC# 导出EXCELC# 导出EXCELC# 导出EXCELC# 导出EXCELC# 导出EXCELC# 导出EXCEL
使用了一个高手的原有DEMO改造而成,优化了导出复杂表头增加样式过慢的问题。
c#导出Excel
C# 导出excel 实例代码C# 导出excel 实例代码C# 导出excel 实例代码
C#导出Excel并关闭进程解决方案,因为项目导出EXCEL偶尔会报错,现共享解决方案。
C# 导出excel代码C# 导出excel代码C# 导出excel代码C# 导出excel代码C# 导出excel代码C# 导出excel代码C# 导出excel代码C# 导出excel代码C# 导出excel代码C# 导出excel代码C# 导出excel代码C# 导出excel代码C# 导出...
c#导出Excel报表 日期,数字格式 用MyXls导出报表
目前C#导出Excel主要有八种方法(https://www.cnblogs.com/Brambling/p/6854731.html), 其中NPOI个人感觉是最好的, 好不容易找到完整Dll, 做了Demo, 给需要的朋友做参考.
使用C#实现了excel表格的导出功能,包含xls和xlsx两种格式。 程序有添加关于NOPI的引用,有疑问可以参考我的csdn博客http://blog.csdn.net/my_clear_mind
C#操作excel表,在代码中导出excel