|
Posted on
2005-12-09 09:36
浪地
阅读( 1121)
评论()
编辑
收藏
举报
最近一直在做C#操作office方面的工作!总结一下!(一)Excel C#操作Excel!
public class ImportExportToExcel
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif) {
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()
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif) {
//
// TODO: 在此处添加构造函数逻辑
//
this.openFileDlg.DefaultExt = "xls";
this.openFileDlg.Filter = "Excel文件 (*.xls)|*.xls";
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
this.saveFileDlg.DefaultExt="xls";
this.saveFileDlg.Filter= "Excel文件 (*.xls)|*.xls";
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif) 从Excel文件导入到DataSet#region 从Excel文件导入到DataSet
// /// <summary>
// /// 从Excel导入文件
// /// </summary>
// /// <param name="strExcelFileName">Excel文件名</param>
// /// <returns>返回DataSet</returns>
// public DataSet ImportFromExcel(string strExcelFileName)
// {
// return doImport(strExcelFileName);
// }
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif) /**//// <summary>
/// 从选择的Excel文件导入
/// </summary>
/// <returns>DataSet</returns>
public DataSet ImportFromExcel()
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif) {
DataSet ds=new DataSet();
if (openFileDlg.ShowDialog() == System.Windows.Forms.DialogResult.OK)
ds=doImport(openFileDlg.FileName);
return ds;
}
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif) /**//// <summary>
/// 从指定的Excel文件导入
/// </summary>
/// <param name="strFileName">Excel文件名</param>
/// <returns></returns>
public DataSet ImportFromExcel(string strFileName)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif) {
DataSet ds=new DataSet();
ds=doImport(strFileName);
return ds;
}
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif) /**//// <summary>
/// 执行导入
/// </summary>
/// <param name="strFileName">文件名</param>
/// <returns>DataSet</returns>
private DataSet doImport(string strFileName)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif) {
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);
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
DataSet ExcelDs = new DataSet();
try
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif) {
ExcelDA.Fill(ExcelDs, "ExcelInfo");
}
catch(Exception err)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif) {
System.Console.WriteLine( err.ToString() );
}
return ExcelDs;
}
#endregion
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif) 从DataSet到出到Excel#region 从DataSet到出到Excel
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif) /**//// <summary>
/// 导出指定的Excel文件
/// </summary>
/// <param name="ds">要导出的DataSet</param>
/// <param name="strExcelFileName">要导出的Excel文件名</param>
public void ExportToExcel(DataSet ds,string strExcelFileName)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif) {
if (ds.Tables.Count==0 || strExcelFileName=="") return;
doExport(ds,strExcelFileName);
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
}
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif) /**//// <summary>
/// 导出用户选择的Excel文件
/// </summary>
/// <param name="ds">DataSet</param>
public void ExportToExcel(DataSet ds)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif) {
if (saveFileDlg.ShowDialog() == System.Windows.Forms.DialogResult.OK)
doExport(ds,saveFileDlg.FileName);
}
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif) /**//// <summary>
/// 执行导出
/// </summary>
/// <param name="ds">要导出的DataSet</param>
/// <param name="strExcelFileName">要导出的文件名</param>
private void doExport(DataSet ds,string strExcelFileName)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif) {
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);
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
int rowIndex=1;
int colIndex=0;
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
excel.Application.Workbooks.Add(true);
System.Data.DataTable table=ds.Tables[0] ;
foreach(DataColumn col in table.Columns)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif) {
colIndex++;
excel.Cells[1,colIndex]=col.ColumnName;
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
foreach(DataRow row in table.Rows)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif) {
rowIndex++;
colIndex=0;
foreach(DataColumn col in table.Columns)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif) {
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
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
//excel.Save(strExcelFileName);
excel.Quit();
excel=null;
GC.Collect();//垃圾回收
}
#endregion
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif) 从XML导入到Dataset#region 从XML导入到Dataset
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif) /**//// <summary>
/// 从选择的XML文件导入
/// </summary>
/// <returns>DataSet</returns>
public DataSet ImportFromXML()
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif) {
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)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif) try {ds.ReadXml(openFileDlg.FileName,System.Data.XmlReadMode.ReadSchema);}
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif) catch {}
return ds;
}
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif) /**//// <summary>
/// 从指定的XML文件导入
/// </summary>
/// <param name="strFileName">XML文件名</param>
/// <returns></returns>
public DataSet ImportFromXML(string strFileName)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif) {
if (strFileName=="")
return null;
DataSet ds=new DataSet();
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif) try {ds.ReadXml(strFileName,System.Data.XmlReadMode.ReadSchema);}
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif) catch {}
return ds;
}
#endregion
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif) 从DataSet导出到XML#region 从DataSet导出到XML
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif) /**//// <summary>
/// 导出指定的XML文件
/// </summary>
/// <param name="ds">要导出的DataSet</param>
/// <param name="strXMLFileName">要导出的XML文件名</param>
public void ExportToXML(DataSet ds,string strXMLFileName)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif) {
if (ds.Tables.Count==0 || strXMLFileName=="") return;
doExportXML(ds,strXMLFileName);
}
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif) /**//// <summary>
/// 导出用户选择的XML文件
/// </summary>
/// <param name="ds">DataSet</param>
public void ExportToXML(DataSet ds)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif) {
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);
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif) /**//// <summary>
/// 执行导出
/// </summary>
/// <param name="ds">要导出的DataSet</param>
/// <param name="strExcelFileName">要导出的XML文件名</param>
private void doExportXML(DataSet ds,string strXMLFileName)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif) {
try
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif) {ds.WriteXml(strXMLFileName,System.Data.XmlWriteMode.WriteSchema );}
catch(Exception ex)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif) {System.Windows.Forms.MessageBox.Show(ex.Message,"Errol") ;}
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
#endregion
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
}
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
|