C#操作Excel!

转载 2004年08月19日 09:12:00

 public class ImportExportToExcel
ExpandedBlockStart.gif    
{
InBlock.gif        
private string strConn ;
InBlock.gif        
InBlock.gif        
private System.Windows.Forms.OpenFileDialog openFileDlg=new System.Windows.Forms.OpenFileDialog();
InBlock.gif        
private System.Windows.Forms.SaveFileDialog saveFileDlg=new System.Windows.Forms.SaveFileDialog();      
InBlock.gif        
InBlock.gif        
public ImportExportToExcel()
ExpandedSubBlockStart.gif        
{
InBlock.gif            
//
InBlock.gif            
// TODO: 在此处添加构造函数逻辑
InBlock.gif            
//
InBlock.gif
            this.openFileDlg.DefaultExt = "xls";
InBlock.gif            
this.openFileDlg.Filter = "Excel文件 (*.xls)|*.xls";
InBlock.gif
InBlock.gif            
this.saveFileDlg.DefaultExt="xls";
InBlock.gif            
this.saveFileDlg.Filter= "Excel文件 (*.xls)|*.xls";
InBlock.gif
ExpandedSubBlockEnd.gif        }

InBlock.gif
ExpandedSubBlockStart.gif        
#region 从Excel文件导入到DataSet
InBlock.gif        
//        /// <summary>
InBlock.gif        
//        /// 从Excel导入文件
InBlock.gif        
//        /// </summary>
InBlock.gif        
//        /// <param name="strExcelFileName">Excel文件名</param>
InBlock.gif        
//        /// <returns>返回DataSet</returns>
InBlock.gif        
//        public DataSet ImportFromExcel(string strExcelFileName)
InBlock.gif        
//        {
InBlock.gif        
//            return doImport(strExcelFileName);
InBlock.gif        
//        }
ExpandedSubBlockStart.gif
        /// <summary>
InBlock.gif        
/// 从选择的Excel文件导入
InBlock.gif        
/// </summary>
ExpandedSubBlockEnd.gif        
/// <returns>DataSet</returns>

InBlock.gif        public DataSet ImportFromExcel()
ExpandedSubBlockStart.gif        
{
InBlock.gif            DataSet ds
=new DataSet();
InBlock.gif            
if (openFileDlg.ShowDialog() == System.Windows.Forms.DialogResult.OK) 
InBlock.gif                ds
=doImport(openFileDlg.FileName);
InBlock.gif            
return ds;
ExpandedSubBlockEnd.gif        }

ExpandedSubBlockStart.gif        
/// <summary>
InBlock.gif        
/// 从指定的Excel文件导入
InBlock.gif        
/// </summary>
InBlock.gif        
/// <param name="strFileName">Excel文件名</param>
ExpandedSubBlockEnd.gif        
/// <returns></returns>

InBlock.gif        public DataSet ImportFromExcel(string strFileName)
ExpandedSubBlockStart.gif        
{
InBlock.gif            DataSet ds
=new DataSet();
InBlock.gif            ds
=doImport(strFileName);
InBlock.gif            
return ds;
ExpandedSubBlockEnd.gif        }

ExpandedSubBlockStart.gif        
/// <summary>
InBlock.gif        
/// 执行导入
InBlock.gif        
/// </summary>
InBlock.gif        
/// <param name="strFileName">文件名</param>
ExpandedSubBlockEnd.gif        
/// <returns>DataSet</returns>

InBlock.gif        private DataSet doImport(string strFileName)
ExpandedSubBlockStart.gif        
{
InBlock.gif            
if (strFileName==""return null;
InBlock.gif              
InBlock.gif            strConn 
= "Provider=Microsoft.Jet.OLEDB.4.0;" +
InBlock.gif                
"Data Source=" +  strFileName + ";" +
InBlock.gif                
"Extended Properties=Excel 8.0;";
InBlock.gif            OleDbDataAdapter ExcelDA 
= new OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn);
InBlock.gif
InBlock.gif            DataSet ExcelDs 
= new DataSet();
InBlock.gif            
try
ExpandedSubBlockStart.gif            
{
InBlock.gif                ExcelDA.Fill(ExcelDs, 
"ExcelInfo");
InBlock.gif                
ExpandedSubBlockEnd.gif            }

InBlock.gif            
catch(Exception err)
ExpandedSubBlockStart.gif            
{
InBlock.gif                System.Console.WriteLine( err.ToString() );
ExpandedSubBlockEnd.gif            }

InBlock.gif            
return ExcelDs;
InBlock.gif            
InBlock.gif            
InBlock.gif        
ExpandedSubBlockEnd.gif        }

ExpandedSubBlockEnd.gif        
#endregion

InBlock.gif
ExpandedSubBlockStart.gif        
#region 从DataSet到出到Excel
ExpandedSubBlockStart.gif        
/// <summary>
InBlock.gif        
/// 导出指定的Excel文件
InBlock.gif        
/// </summary>
InBlock.gif        
/// <param name="ds">要导出的DataSet</param>
ExpandedSubBlockEnd.gif        
/// <param name="strExcelFileName">要导出的Excel文件名</param>

InBlock.gif        public void ExportToExcel(DataSet ds,string strExcelFileName)
ExpandedSubBlockStart.gif        
{
InBlock.gif            
if (ds.Tables.Count==0 || strExcelFileName==""return;
InBlock.gif            doExport(ds,strExcelFileName);
InBlock.gif    
InBlock.gif
ExpandedSubBlockEnd.gif        }

ExpandedSubBlockStart.gif        
/// <summary>
InBlock.gif        
/// 导出用户选择的Excel文件
InBlock.gif        
/// </summary>
ExpandedSubBlockEnd.gif        
/// <param name="ds">DataSet</param>

InBlock.gif        public void ExportToExcel(DataSet ds)
ExpandedSubBlockStart.gif        
{
InBlock.gif            
if (saveFileDlg.ShowDialog() == System.Windows.Forms.DialogResult.OK) 
InBlock.gif                doExport(ds,saveFileDlg.FileName);
InBlock.gif            
ExpandedSubBlockEnd.gif        }

ExpandedSubBlockStart.gif        
/// <summary>
InBlock.gif        
/// 执行导出
InBlock.gif        
/// </summary>
InBlock.gif        
/// <param name="ds">要导出的DataSet</param>
ExpandedSubBlockEnd.gif        
/// <param name="strExcelFileName">要导出的文件名</param>

InBlock.gif        private void doExport(DataSet ds,string strExcelFileName)
ExpandedSubBlockStart.gif        
{
InBlock.gif            
InBlock.gif            Excel.Application excel
= new Excel.Application();
InBlock.gif            
InBlock.gif            
//            Excel.Workbook obj=new Excel.WorkbookClass();
InBlock.gif            
//            obj.SaveAs("c:/zn.xls",Excel.XlFileFormat.xlExcel9795,null,null,false,false,Excel.XlSaveAsAccessMode.xlNoChange,null,null,null,null);
InBlock.gif

InBlock.gif            
int rowIndex=1;
InBlock.gif            
int colIndex=0;
InBlock.gif
InBlock.gif            excel.Application.Workbooks.Add(
true);
InBlock.gif            
InBlock.gif    
InBlock.gif            System.Data.DataTable table
=ds.Tables[0] ;
InBlock.gif            
foreach(DataColumn col in table.Columns)
ExpandedSubBlockStart.gif            
{
InBlock.gif                colIndex
++;    
InBlock.gif                excel.Cells[
1,colIndex]=col.ColumnName;                
ExpandedSubBlockEnd.gif            }

InBlock.gif
InBlock.gif            
foreach(DataRow row in table.Rows)
ExpandedSubBlockStart.gif            
{
InBlock.gif                rowIndex
++;
InBlock.gif                colIndex
=0;
InBlock.gif                
foreach(DataColumn col in table.Columns)
ExpandedSubBlockStart.gif                
{
InBlock.gif                    colIndex
++;
InBlock.gif                    excel.Cells[rowIndex,colIndex]
=row[col.ColumnName].ToString();
ExpandedSubBlockEnd.gif                }

ExpandedSubBlockEnd.gif            }

InBlock.gif            excel.Visible
=false;    
InBlock.gif            excel.Sheets[
0= "sss";
InBlock.gif            excel.ActiveWorkbook.SaveAs(strExcelFileName
+".XLS",Excel.XlFileFormat.xlExcel9795,null,null,false,false,Excel.XlSaveAsAccessMode.xlNoChange,null,null,null,null);
InBlock.gif            
InBlock.gif            
InBlock.gif            
//wkbNew.SaveAs strBookName
InBlock.gif
InBlock.gif
InBlock.gif            
//excel.Save(strExcelFileName);
InBlock.gif
            excel.Quit();
InBlock.gif            excel
=null;
InBlock.gif            
InBlock.gif            GC.Collect();
//垃圾回收
ExpandedSubBlockEnd.gif
        }

ExpandedSubBlockEnd.gif        
#endregion

InBlock.gif
ExpandedSubBlockStart.gif        
#region 从XML导入到Dataset
InBlock.gif
ExpandedSubBlockStart.gif        
/// <summary>
InBlock.gif        
/// 从选择的XML文件导入
InBlock.gif        
/// </summary>
ExpandedSubBlockEnd.gif        
/// <returns>DataSet</returns>

InBlock.gif        public DataSet ImportFromXML()
ExpandedSubBlockStart.gif        
{
InBlock.gif            DataSet ds
=new DataSet();
InBlock.gif            System.Windows.Forms.OpenFileDialog openFileDlg
=new System.Windows.Forms.OpenFileDialog();
InBlock.gif            openFileDlg.DefaultExt
="xml";
InBlock.gif            openFileDlg.Filter
= "xml文件 (*.xml)|*.xml";
InBlock.gif            
if (openFileDlg.ShowDialog() == System.Windows.Forms.DialogResult.OK) 
ExpandedSubBlockStart.gif                
try{ds.ReadXml(openFileDlg.FileName,System.Data.XmlReadMode.ReadSchema);}
ExpandedSubBlockStart.gif                
catch{}
InBlock.gif            
return ds;
ExpandedSubBlockEnd.gif        }

ExpandedSubBlockStart.gif        
/// <summary>
InBlock.gif        
/// 从指定的XML文件导入
InBlock.gif        
/// </summary>
InBlock.gif        
/// <param name="strFileName">XML文件名</param>
ExpandedSubBlockEnd.gif        
/// <returns></returns>

InBlock.gif        public DataSet ImportFromXML(string strFileName)
ExpandedSubBlockStart.gif        
{
InBlock.gif            
if (strFileName=="")
InBlock.gif                
return null;
InBlock.gif            DataSet ds
=new DataSet();
ExpandedSubBlockStart.gif            
try{ds.ReadXml(strFileName,System.Data.XmlReadMode.ReadSchema);}
ExpandedSubBlockStart.gif            
catch{}
InBlock.gif            
return ds;
ExpandedSubBlockEnd.gif        }

InBlock.gif        
ExpandedSubBlockEnd.gif        
#endregion

InBlock.gif
ExpandedSubBlockStart.gif        
#region 从DataSet导出到XML
ExpandedSubBlockStart.gif        
/// <summary>
InBlock.gif        
/// 导出指定的XML文件
InBlock.gif        
/// </summary>
InBlock.gif        
/// <param name="ds">要导出的DataSet</param>
ExpandedSubBlockEnd.gif        
/// <param name="strXMLFileName">要导出的XML文件名</param>

InBlock.gif        public void ExportToXML(DataSet ds,string strXMLFileName)
ExpandedSubBlockStart.gif        
{
InBlock.gif            
if (ds.Tables.Count==0 || strXMLFileName==""return;
InBlock.gif            doExportXML(ds,strXMLFileName);
ExpandedSubBlockEnd.gif        }

ExpandedSubBlockStart.gif        
/// <summary>
InBlock.gif        
/// 导出用户选择的XML文件
InBlock.gif        
/// </summary>
ExpandedSubBlockEnd.gif        
/// <param name="ds">DataSet</param>

InBlock.gif        public void ExportToXML(DataSet ds)
ExpandedSubBlockStart.gif        
{
InBlock.gif            System.Windows.Forms.SaveFileDialog saveFileDlg
=new System.Windows.Forms.SaveFileDialog(); 
InBlock.gif            saveFileDlg.DefaultExt
="xml";
InBlock.gif            saveFileDlg.Filter
= "xml文件 (*.xml)|*.xml";
InBlock.gif            
if (saveFileDlg.ShowDialog() == System.Windows.Forms.DialogResult.OK) 
InBlock.gif                doExportXML(ds,saveFileDlg.FileName);
ExpandedSubBlockEnd.gif        }

InBlock.gif
ExpandedSubBlockStart.gif        
/// <summary>
InBlock.gif        
/// 执行导出
InBlock.gif        
/// </summary>
InBlock.gif        
/// <param name="ds">要导出的DataSet</param>
ExpandedSubBlockEnd.gif        
/// <param name="strExcelFileName">要导出的XML文件名</param>

InBlock.gif        private void doExportXML(DataSet ds,string strXMLFileName)
ExpandedSubBlockStart.gif        
{
InBlock.gif            
try
ExpandedSubBlockStart.gif            
{ds.WriteXml(strXMLFileName,System.Data.XmlWriteMode.WriteSchema );}
InBlock.gif            
catch(Exception ex)
ExpandedSubBlockStart.gif            
{System.Windows.Forms.MessageBox.Show(ex.Message,"Errol") ;}    
ExpandedSubBlockEnd.gif        }

InBlock.gif
ExpandedSubBlockEnd.gif        
#endregion

InBlock.gif
InBlock.gif    
InBlock.gif    
ExpandedBlockEnd.gif    }

C#对Excel的基本操作

Office2007尚未普及,Office2000~2003仍然是主流。本文介绍一些C#处理Excel得方法,都是自己封装的~1. 添加COM组件的Reference2. 引入名字空间using Mi...
  • fallriver
  • fallriver
  • 2008-04-24 14:53:00
  • 1866

自己用的c#操作excel类。

下面的程序,是自己经常用到的excel操作类,本程序与别的对excel操作唯一改进的地方是对excel数据的填充方式上。网上经常看到的填充方式有:1:按单元格2:按区域(range)本程序,也还是按区...
  • yangang0201
  • yangang0201
  • 2007-07-13 09:56:00
  • 4470

C# 操作Excel

  • 2013年02月19日 16:08
  • 703KB
  • 下载

C#操作Excel类,功能比较全

原文出处: http://www.cnblogs.com/wangchuang/p/5546345.html /// 常用工具类——Excel操作类 ///  --------...
  • wuyuander
  • wuyuander
  • 2017-06-06 11:13:52
  • 659

C#学习之操作excel表格

-------------------自己的实践方案          今天开始学习C#使用Excel。          首先,要先添加引用:   若发现没有这个项,可以重新安装office,我...
  • HK_5788
  • HK_5788
  • 2015-10-18 01:21:04
  • 3591

c#对Excel操作

本代码可以对Excel生成自定义的交叉表头,可以制作自定义的交叉报表。代码使用的是Owc11库,要先安装这个库,再将其引用到项目中。 对excel主要的操作有:1.新建excel进程2.获得操作范围3...
  • troublelee
  • troublelee
  • 2007-10-09 12:48:00
  • 918

WinForm读取Excel(附带一个C#操作Excel类,功能比较全)

  • 2010年03月19日 19:16
  • 12KB
  • 下载

C#操作Excel(1)Excel对象模型

Excel对象模型  (.Net Perspective) 本文主要针对在Visual Studio中使用C# 开发关于Excel的应用程序 本文的PDF下载地址:C#操作Excel200...
  • ruby97
  • ruby97
  • 2012-03-30 16:58:47
  • 9095

C#(源码)操作Excel大全1

C#对excel的操作
  • u011981242
  • u011981242
  • 2015-09-24 10:35:59
  • 438

C#对Excel的读写操作

读取Excel,首先要添加Microsoft.Office.Interop.Excel 12.0 动态库  读取工作薄中的sheetspublic ArrayList loadExcelSheets(...
  • dyyaries
  • dyyaries
  • 2011-06-29 18:05:00
  • 1986
收藏助手
不良信息举报
您举报文章:C#操作Excel!
举报原因:
原因补充:

(最多只允许输入30个字)