C Sharp 导出导入数据从EXCEL到DATASET.doc

C# DataSet和Excel之间导入导出Posted on 2006-04-20 01:54西瓜K菠萝阅读(7598)评论(5)编辑收藏1publicclassImportExportToExcel23privatestringstrConn;45privateSystem.Windows.Forms.OpenFileDialogopenFileDlg=newSystem.Windows.Forms.OpenFileDialog();6privateSystem.Windows.Forms.SaveFileDialogsaveFileDlg=newSystem.Windows.Forms.SaveFileDialog();78publicImportExportToExcel()910/11/TODO:在此处添加构造函数逻辑12/13this.openFileDlg.DefaultExt=xls;14this.openFileDlg.Filter=Excel文件(*.xls)|*.xls;1516this.saveFileDlg.DefaultExt=xls;17this.saveFileDlg.Filter=Excel文件(*.xls)|*.xls;18192021从Excel文件导入到DataSet#region从Excel文件导入到DataSet22/23/从Excel导入文件24/25/Excel文件名26/返回DataSet27/publicDataSetImportFromExcel(stringstrExcelFileName)28/29/returndoImport(strExcelFileName);30/31/*/32/从选择的Excel文件导入33/34/DataSet35publicDataSetImportFromExcel()3637DataSetds=newDataSet();38if(openFileDlg.ShowDialog()=System.Windows.Forms.DialogResult.OK)39ds=doImport(openFileDlg.FileName);40returnds;4142/*/43/从指定的Excel文件导入44/45/Excel文件名46/47publicDataSetImportFromExcel(stringstrFileName)4849DataSetds=newDataSet();50ds=doImport(strFileName);51returnds;5253/*/54/执行导入55/56/文件名57/DataSet58privateDataSetdoImport(stringstrFileName)5960if(strFileName=)returnnull;6162strConn=Provider=Microsoft.Jet.OLEDB.4.0;+63DataSource=+strFileName+;+64ExtendedProperties=Excel8.0;65OleDbDataAdapterExcelDA=newOleDbDataAdapter(SELECT*FROMSheet1$,strConn);6667DataSetExcelDs=newDataSet();68try6970ExcelDA.Fill(ExcelDs,ExcelInfo);717273catch(Exceptionerr)7475System.Console.WriteLine(err.ToString();7677returnExcelDs;7879808182#endregion8384从DataSet到出到Excel#region从DataSet到出到Excel85/*/86/导出指定的Excel文件87/88/要导出的DataSet89/要导出的Excel文件名90publicvoidExportToExcel(DataSetds,stringstrExcelFileName)9192if(ds.Tables.Count=0|strExcelFileName=)return;93doExport(ds,strExcelFileName);94959697/*/98/导出用户选择的Excel文件99/100/DataSet101publicvoidExportToExcel(DataSetds)102103if(saveFileDlg.ShowDialog()=System.Windows.Forms.DialogResult.OK)104doExport(ds,saveFileDlg.FileName);105106107/*/108/执行导出109/110/要导出的DataSet111/要导出的文件名112privatevoiddoExport(DataSetds,stringstrExcelFileName)113114115Excel.Applicationexcel=newExcel.Application();116117/Excel.Workbookobj=newExcel.WorkbookClass();118/obj.SaveAs(c:zn.xls,Excel.XlFileFormat.xlExcel9795,null,null,false,false,Excel.XlSaveAsAccessMode.xlNoChange,null,null,null,null);119120introwIndex=1;121intcolIndex=0;122123excel.Application.Workbooks.Add(true);124125126System.Data.DataTabletable=ds.Tables0;127foreach(DataColumncolintable.Columns)128129colIndex+;130excel.Cells1,colIndex=col.ColumnName;131132133foreach(DataRowrowintable.Rows)134135rowIndex+;136colIndex=0;137foreach(DataColumncolintable.Columns)138139colIndex+;140excel.CellsrowIndex,colIndex=rowcol.ColumnName.ToString();141142143excel.Visible=false;144excel.Sheets0=sss;145excel.ActiveWorkbook.SaveAs(strExcelFileName+.XLS,Excel.XlFileFormat.xlExcel9795,null,null,false,false,Excel.XlSaveAsAccessMode.xlNoChange,null,null,null,null);146147148/wkbNew.SaveAsstrBookName149150151/excel.Save(strExcelFileName);152excel.Quit();153excel=null;154155GC.Collect();/垃圾回收156157#endregion158159从XML导入到Dataset#region从XML导入到Dataset160161/*/162/从选择的XML文件导入163/164/DataSet165publicDataSetImportFromXML()166167DataSetds=newDataSet();168System.Windows.Forms.OpenFileDialogopenFileDlg=newSystem.Windows.Forms.OpenFileDialog();169openFileDlg.DefaultExt=xml;170openFileDlg.Filter=xml文件(*.xml)|*.xml;171if(openFileDlg.ShowDialog()=System.Windows.Forms.DialogResult.OK)172tryds.ReadXml(openFileDlg.FileName,System.Data.XmlReadMode.ReadSchema);173catch174returnds;175176/*/177/从指定的XML文件导入178/179/XML文件名180/181publicDataSetImportFromXML(stringstrFileName)182183if(strFileName=)184returnnull;185DataSetds=newDataSet();186tryds.ReadXml(strFileName,System.Data.XmlReadMode.ReadSchema);187catch188returnds;189190191#endregion192193从DataSet导出到XML#region从DataSet导出到XML194/*/195/导出指定的XML文件196/197/要导出的DataSet198/要导出的XML文件名199publicvoidExportToXML(DataSetds,stringstrXMLFileName)200201if(ds.Tables.Count=0|strXMLFileName=)return;202doExportXML(ds,strXMLFileName);203204/*/205/导出用户选择的XML文件206/207/DataSet208publicvoidExportToXML(DataSetds)209210System.Windows.Forms.SaveFileDialogsaveFileDlg=newSystem.Windows.Forms.SaveFileDialog();211saveFileDlg.DefaultExt=xml;212saveFileDlg.Filter=xml文件(*.xml)|*.xml;213if(saveFileDlg.ShowDialog()=System.Windows.Forms.DialogResult.OK)214doExportXML(ds,saveFileDlg.FileName);215216217/*/218/执行导出219/220/要导出的DataSet221/要导出的XML文件名222privatevoiddoExportXML(DataSetds,stringstrXMLFileName)223224try225ds.WriteXml(strXMLFileName,System.Data.XmlWriteMode.WriteSchema);226catch(Exceptionex)227System.Windows.Forms.MessageBox.Show(ex.Message,Errol);228229230#endregion231232233234235可能用进程的强制删除方法:kill()1#region强制结束Word进程(垃圾回收)2/3/强制结束Word进程(垃圾回收)4/5publicvoidKillWordProcess()67intProceedingCount=0;8try910System.Diagnostics.ProcessProceddingCon=System.Diagnostics.Process.GetProcesses();11foreach(System.Diagnostics.ProcessIsProceddinginProceddingCon)1213if(IsProcedding.ProcessName.ToUpper()=WINWORD)1415ProceedingCount+=1;16IsProcedding.Kill();17181920catch(System.Exceptionerr)2122MessageBox.Show(err.Message+r+(+err.Source+)+r+err.StackTrace);232425#endregionc# Excel转入到DataSet收藏#region 从Excel读入数据到DataSet public static DataSet ExcelToDataSet(string strFilePath,string type) string strConn=; if (type = excel2003) strConn = Provider=Microsoft.Jet.OLEDB.4.0;Data Source= + strFilePath + ;Extended Properties=Excel 8.0;HDR=YES;IMEX=1; else if (type = excel2007) strConn = Provider=Microsoft.Ace.OLEDB.12.0;Data Source= + strFilePath