-
c#操作office 技术总结（1）
C#操作Excel!
public class ImportExportToExcel
{
private string strConn ;
private System.Windows.Forms.OpenFileDialog openFileDlg=new System.Windows.Forms.OpenFileDialog();
private System.Windows.Forms.SaveFileDialog saveFileDlg=new System.Windows.Forms.SaveFileDialog();
public ImportExportToExcel()
{
//
// TODO: 在此处添加构造函数逻辑
//
this.openFileDlg.DefaultExt = "xls";
this.openFileDlg.Filter = "Excel文件 (*.xls)|*.xls";
this.saveFileDlg.DefaultExt="xls";
this.saveFileDlg.Filter= "Excel文件 (*.xls)|*.xls";
}
从Excel文件导入到DataSet#region 从Excel文件导入到DataSet // /// <summary>
// /// 从Excel导入文件
// /// </summary>
// /// <param name="strExcelFileName">Excel文件名</param>
// /// <returns>返回DataSet</returns>
// public DataSet ImportFromExcel(string strExcelFileName)
// {
// return doImport(strExcelFileName);
// }
/**//// <summary>
/// 从选择的Excel文件导入
/// </summary>
/// <returns>DataSet</returns>
public DataSet ImportFromExcel()
{
DataSet ds=new DataSet();
if (openFileDlg.ShowDialog() == System.Windows.Forms.DialogResult.OK)
ds=doImport(openFileDlg.FileName);
return ds;
}
/**//// <summary>
/// 从指定的Excel文件导入
/// </summary>
/// <param name="strFileName">Excel文件名</param>
/// <returns></returns>
public DataSet ImportFromExcel(string strFileName)
{
DataSet ds=new DataSet();
ds=doImport(strFileName);
return ds;
}
/**//// <summary>
/// 执行导入
/// </summary>
/// <param name="strFileName">文件名</param>
/// <returns>DataSet</returns>
private DataSet doImport(string strFileName)
{
if (strFileName=="") return null;
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + strFileName + ";" +
"Extended Properties=Excel 8.0;";
OleDbDataAdapter ExcelDA = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn);
DataSet ExcelDs = new DataSet();
try
{
ExcelDA.Fill(ExcelDs, "ExcelInfo");
}
catch(Exception err)
{
System.Console.WriteLine( err.ToString() );
}
return ExcelDs;
}
#endregion
从DataSet到出到Excel#region 从DataSet到出到Excel /**//// <summary>
/// 导出指定的Excel文件
/// </summary>
/// <param name="ds">要导出的DataSet</param>
/// <param name="strExcelFileName">要导出的Excel文件名</param>
public void ExportToExcel(DataSet ds,string strExcelFileName)
{
if (ds.Tables.Count==0 || strExcelFileName=="") return;
doExport(ds,strExcelFileName);
}
/**//// <summary>
/// 导出用户选择的Excel文件
/// </summary>
/// <param name="ds">DataSet</param>
public void ExportToExcel(DataSet ds)
{
if (saveFileDlg.ShowDialog() == System.Windows.Forms.DialogResult.OK)
doExport(ds,saveFileDlg.FileName);
}
/**//// <summary>
/// 执行导出
/// </summary>
/// <param name="ds">要导出的DataSet</param>
/// <param name="strExcelFileName">要导出的文件名</param>
private void doExport(DataSet ds,string strExcelFileName)
{
Excel.Application excel= new Excel.Application();
// Excel.Workbook obj=new Excel.WorkbookClass();
// obj.SaveAs("c:zn.xls",Excel.XlFileFormat.xlExcel9795,null,null,false,false,Excel.XlSaveAsAccessMode.xlNoChange,null,null,null,null);
int rowIndex=1;
int colIndex=0;
excel.Application.Workbooks.Add(true);
System.Data.DataTable table=ds.Tables[0] ;
foreach(DataColumn col in table.Columns)
{
colIndex++;
excel.Cells[1,colIndex]=col.ColumnName;
}
foreach(DataRow row in table.Rows)
{
rowIndex++;
colIndex=0;
foreach(DataColumn col in table.Columns)
{
colIndex++;
excel.Cells[rowIndex,colIndex]=row[col.ColumnName].ToString();
}
}
excel.Visible=false;
excel.Sheets[0] = "sss";
excel.ActiveWorkbook.SaveAs(strExcelFileName+".XLS",Excel.XlFileFormat.xlExcel9795,null,null,false,false,Excel.XlSaveAsAccessMode.xlNoChange,null,null,null,null);
//wkbNew.SaveAs strBookName
//excel.Save(strExcelFileName);
excel.Quit();
excel=null;
GC.Collect();//垃圾回收
}
#endregion
从XML导入到Dataset#region 从XML导入到Dataset
/**//// <summary>
/// 从选择的XML文件导入
/// </summary>
/// <returns>DataSet</returns>
public DataSet ImportFromXML()
{
DataSet ds=new DataSet();
System.Windows.Forms.OpenFileDialog openFileDlg=new System.Windows.Forms.OpenFileDialog();
openFileDlg.DefaultExt="xml";
openFileDlg.Filter= "xml文件 (*.xml)|*.xml";
if (openFileDlg.ShowDialog() == System.Windows.Forms.DialogResult.OK)
try{ds.ReadXml(openFileDlg.FileName,System.Data.XmlReadMode.ReadSchema);}
catch{}
return ds;
}
/**//// <summary>
/// 从指定的XML文件导入
/// </summary>
/// <param name="strFileName">XML文件名</param>
/// <returns></returns>
public DataSet ImportFromXML(string strFileName)
{
if (strFileName=="")
return null;
DataSet ds=new DataSet();
try{ds.ReadXml(strFileName,System.Data.XmlReadMode.ReadSchema);}
catch{}
return ds;
}
#endregion
从DataSet导出到XML#region 从DataSet导出到XML /**//// <summary>
/// 导出指定的XML文件
/// </summary>
/// <param name="ds">要导出的DataSet</param>
/// <param name="strXMLFileName">要导出的XML文件名</param>
public void ExportToXML(DataSet ds,string strXMLFileName)
{
if (ds.Tables.Count==0 || strXMLFileName=="") return;
doExportXML(ds,strXMLFileName);
}
/**//// <summary>
/// 导出用户选择的XML文件
/// </summary>
/// <param name="ds">DataSet</param>
public void ExportToXML(DataSet ds)
{
System.Windows.Forms.SaveFileDialog saveFileDlg=new System.Windows.Forms.SaveFileDialog();
saveFileDlg.DefaultExt="xml";
saveFileDlg.Filter= "xml文件 (*.xml)|*.xml";
if (saveFileDlg.ShowDialog() == System.Windows.Forms.DialogResult.OK)
doExportXML(ds,saveFileDlg.FileName);
}
/**//// <summary>
/// 执行导出
/// </summary>
/// <param name="ds">要导出的DataSet</param>
/// <param name="strExcelFileName">要导出的XML文件名</param>
private void doExportXML(DataSet ds,string strXMLFileName)
{
try
{ds.WriteXml(strXMLFileName,System.Data.XmlWriteMode.WriteSchema );}
catch(Exception ex)
{System.Windows.Forms.MessageBox.Show(ex.Message,"Errol") ;}
}
#endregion
}
C#操作Word!
namespace ImportExportToOffice
{
using System;
using System.ComponentModel;
using System.Data;
using System.Windows.Forms;
public class ImportExportToWord
{
private Word.ApplicationClass oWordApplic;
private Word.Document oDoc;
private const string strFileName = @"F:";
private const string PostfixForWord = @".doc";
private const string PostfixForHtml = @".Html";
构造函数#region 构造函数 public ImportExportToWord()
{
oWordApplic = new Word.ApplicationClass();
}
#endregion
public Word.Document Document
{
get
{
return this.oDoc;
}
}
public Word.ApplicationClass Application
{
get
{
return this.oWordApplic;
}
}
私有方法#region 私有方法
/**//// <summary>
/// 设置Word文档是否可视
/// </summary>
/// <param name="InEnabled">boolean</param>
private void SetVisible( Boolean InEnabled )
{
oWordApplic.Visible = InEnabled;
}
/**//// <summary>
/// 在垃圾回收时，在任务管理器中还存在当前操作的WORD的进程
/// 查阅资料，必须在另一个方法中在调用GC才可以真正的清楚掉，当前的进程
/// </summary>
private void GCForQuit()
{
object missing = System.Reflection.Missing.Value;
oWordApplic.Application.Quit( ref missing, ref missing, ref missing );
if ( oDoc != null )
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(oDoc);
oDoc = null;
}
if ( oWordApplic != null )
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(oWordApplic);
oWordApplic = null;
}
GC.Collect();
}
/**//// <summary>
/// 返回保存文件的FileName
/// </summary>
/// <param name="strDefaultExt">要保存文件的类型</param>
/// <param name="strFilter">文件名筛选器字符串</param>
/// <returns>保存的路径</returns>
private string SaveFileName( string strDefaultExt, string strFilter )
{
string fileName = "";
System.Windows.Forms.SaveFileDialog saveFileDlg = new System.Windows.Forms.SaveFileDialog();
saveFileDlg.DefaultExt = strDefaultExt;
saveFileDlg.Filter = strFilter;
if ( saveFileDlg.ShowDialog() == System.Windows.Forms.DialogResult.OK )
fileName = saveFileDlg.FileName;
return fileName;
}
/**//// <summary>
/// 将被 SaveFileName 取代
/// </summary>
/// <returns></returns>
private string SaveFileToHtmlForName()
{
string fileName = "";
System.Windows.Forms.SaveFileDialog saveFileDlg = new System.Windows.Forms.SaveFileDialog();
saveFileDlg.DefaultExt = "Html";
saveFileDlg.Filter = "html文件 (*.html)|*.htm";
if ( saveFileDlg.ShowDialog() == System.Windows.Forms.DialogResult.OK )
fileName = saveFileDlg.FileName;
return fileName;
}
/**//// <summary>
/// 保存
/// </summary>
private void Save( )
{
oDoc.Save();
}
#endregion
公有方法#region 公有方法
/**//// <summary>
/// 打开一个空的Word模板
/// </summary>
public bool Open( )
{
bool result = false;
try
{
object missing = System.Reflection.Missing.Value;
oDoc = oWordApplic.Documents.Add( ref missing, ref missing, ref missing, ref missing );
oDoc.Activate();
result = true;
}
catch
{
this.Quit();
//throw ( new Exception() );
}
return result;
}
/**//// <summary>
/// 退出
/// </summary>
public void Quit( )
{
GCForQuit();
GC.Collect();
foreach(System.Diagnostics.Process p in System.Diagnostics.Process.GetProcesses())
{
if(p.ProcessName.ToUpper() == "WINWORD")
{
p.Kill();
}
}
}
/**//// <summary>
/// 打开指定的Word文档
/// </summary>
/// <param name="strFileName">指定的Word文档</param>
public bool Open( string strFileName )
{
return this.Open( strFileName, true );
}
/**//// <summary>
/// 打开指定的Word文档并判断是否显示
/// </summary>
/// <param name="strFileName">指定的Word文档</param>
/// <param name="isEnabled">显示与否</param>
public bool Open( string strFileName, bool isEnabled )
{
bool result = false;
if ( strFileName == null || strFileName == "" ) return result;
try
{
object fileName = strFileName;
object readOnly = false;
object isVisible = true;
object missing = System.Reflection.Missing.Value;
#if OFFICEXP
oDoc = oWordApplic.Documents.Open(ref fileName, ref missing,ref readOnly,
ref missing, ref missing, ref missing, ref missing, ref missing, ref missing,
ref missing, ref missing, ref isVisible,ref missing,ref missing,ref missing);
#else
oDoc = oWordApplic.Documents.Open(ref fileName, ref missing,ref readOnly,
ref missing, ref missing, ref missing, ref missing, ref missing, ref missing,
ref missing, ref missing, ref isVisible);
#endif
oDoc.Activate();
oWordApplic.Visible = isEnabled;
result = true;
}
catch
{
this.Quit();
//throw ( new Exception() );
}
return result;
}
/**//// <summary>
/// 另存
/// </summary>
public bool SaveAs( )
{
object missing = System.Reflection.Missing.Value;
object fileName = SaveFileName( "doc", "doc文件 (*.doc)|*.doc" );
return this.SaveAs( Convert.ToString( fileName ) );
}
/**//// <summary>
/// 另存
/// </summary>
/// <param name="strFileName"></param>
public bool SaveAs( string strFileName )
{
bool result = false;
if ( strFileName == null || strFileName == "" ) return result;
try
{
object missing = System.Reflection.Missing.Value;
object fileName = strFileName ;
#if OFFICEXP
oDoc.SaveAs( ref fileName, ref missing,ref missing, ref missing,ref missing,ref missing,ref missing,
ref missing,ref missing,ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing );
#else
oDoc.SaveAs( ref fileName, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing, ref missing );
#endif
result = true;
}
catch
{
//throw( new Exception() );
}
finally
{
this.Quit();
}
return result;
}
从DataSet到出到Excel#region 从DataSet到出到Excel /**//// <summary>
/// 导出指定的Excel文件
/// </summary>
/// <param name="ds">要导出的DataSet</param>
/// <param name="strExcelFileName">要导出的Excel文件名</param>
public void ExportToExcel(DataSet ds,string strExcelFileName)
{
if (ds.Tables.Count==0 || strExcelFileName=="") return;
doExport(ds,strExcelFileName);
}
/**//// <summary>
/// 导出用户选择的Excel文件
/// </summary>
/// <param name="ds">DataSet</param>
public void ExportToExcel(DataSet ds)
{
if (saveFileDlg.ShowDialog() == System.Windows.Forms.DialogResult.OK)
doExport(ds,saveFileDlg.FileName);
}
/**//// <summary>
/// 执行导出
/// </summary>
/// <param name="ds">要导出的DataSet</param>
/// <param name="strExcelFileName">要导出的文件名</param>
private void doExport(DataSet ds,string strExcelFileName)
{
Excel.Application excel= new Excel.Application();
// Excel.Workbook obj=new Excel.WorkbookClass();
// obj.SaveAs("c:zn.xls",Excel.XlFileFormat.xlExcel9795,null,null,false,false,Excel.XlSaveAsAccessMode.xlNoChange,null,null,null,null);
int rowIndex=1;
int colIndex=0;
excel.Application.Workbooks.Add(true);
System.Data.DataTable table=ds.Tables[0] ;
foreach(DataColumn col in table.Columns)
{
colIndex++;
excel.Cells[1,colIndex]=col.ColumnName;
}
foreach(DataRow row in table.Rows)
{
rowIndex++;
colIndex=0;
foreach(DataColumn col in table.Columns)
{
colIndex++;
excel.Cells[rowIndex,colIndex]=row[col.ColumnName].ToString();
}
}
excel.Visible=false;
excel.Sheets[0] = "sss";
excel.ActiveWorkbook.SaveAs(strExcelFileName+".XLS",Excel.XlFileFormat.xlExcel9795,null,null,false,false,Excel.XlSaveAsAccessMode.xlNoChange,null,null,null,null);
//wkbNew.SaveAs strBookName
//excel.Save(strExcelFileName);
excel.Quit();
excel=null;
GC.Collect();//垃圾回收
}
#endregion
