论坛首页 → 编程语言技术论坛 →

c# 导出Excel

浏览 8548 次
锁定老帖子 主题：c# 导出Excel
精华帖 (0) :: 良好帖 (0) :: 新手帖 (16) :: 隐藏帖 (0)
作者 正文
  • select*from爱
  • 等级: 五星会员
  • 性别:
  • 文章: 471
  • 积分: 730
  • 来自: 天府之国
   发表时间：2010-06-04  

初学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
返回顶楼
         
  • fscyr
  • 等级: 初级会员
  • 性别:
  • 文章: 5
  • 积分: 30
  • 来自: 佛山
   发表时间：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();
返回顶楼
          回帖地址
0 请登录后投票
论坛首页 → 编程语言技术版

跳转论坛:
Global site tag (gtag.js) - Google Analytics