C # Excel operations

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: add the constructor logic here // This. openfiledlg. defaultext = "xls"; this. openfiledlg. filter = "Excel file (*. XLS) | *. xls "; this. savefiledlg. defaultext = "xls"; this. savefiledlg. filter = "Excel file (*. XLS) | *. xls ";} import from an Excel file to dataset # region import from an Excel file to dataset // <summary> //// import from an Excel file //// </Summary> ///// <Param name = "strexcelfilename"> Excel file name </param> //// <returns> return dataset </returns> // public dataset importfromexcel (string strexcelfilename) // {// return doimport (strexcelfilename ); ///}/** // <summary> // import data from the selected Excel file /// </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> /// import from the specified Excel file /// </Summary> /// <Param name = "strfilename"> Excel file name </param> // <returns> </returns> Public dataset importfromexcel (string strfilename) {dataset DS = new dataset (); DS = doimport (strfilename); Return Ds ;} /** // <summary> // execute the import /// </Summary> // <Param name = "strfilename"> file name </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 from dataset to excel # region from dataset to excel/** // <summary> /// export the specified Excel file /// </Summary>/ // <Param name = "ds"> dataset to be exported </param> // <Param name = "strexcelfilename"> name of the Excel file to be exported </param> Public void exporttoexcel (Dataset ds, string strexcelfilename) {If (Ds. tables. count = 0 | strexcelfilename = "") return; doexport (DS, strexcelfilename );} /** // <summary> // export the selected Excel file /// </Summary> // <Param name = "ds"> dataset </Param> Public void exporttoexcel (Dataset DS) {If (savefiledlg. showdialog () = system. windows. forms. dialogresult. OK) doexport (DS, savefiledlg. filename );} /** // <summary> // execute export /// </Summary> // <Param name = "ds"> dataset to be exported </Param> /// <Param name = "strexcelfilename"> name of the file to be exported </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); 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. sheet [0] = "Sss"; excel. activeworkbook. saveas (strexcelfilename + ". xls ", Excel. xlfileformat. xlexcel9795, null, null, false, false, Excel. xlsaveasaccessmode. xlnochange, null); // wkbnew. saveas strbookname // excel. save (strexcelfilename); excel. quit (); Excel = NULL; GC. collect (); // garbage collection} # import endregion from XML to dataset # import region from XML to dataset/*** /// <summary> /// import from the selected XML file // /</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 file (*. XML) | *. XML "; if (openfiledlg. showdialog () = system. windows. forms. dialogresult. OK) Try {Ds. readxml (openfiledlg. filename, system. data. xmlreadmode. readschema);} catch {} return Ds ;} /** //// <summary> /// import from the specified XML file /// </Summary> /// <Param name = "strfilename"> XML file name </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 export from dataset to XML # region export from dataset to XML/*** //// <summary> /// export the specified XML file /// </Summary>/ // <Param name = "ds"> dataset to be exported </param> // <Param name = "strxmlfilename"> XML file name to be exported </param> Public void exporttoxml (Dataset ds, string strxmlfilename) {If (Ds. tables. count = 0 | strxmlfilename = "") return; doexportxml (DS, strxmlfilename );} /** // <summary> // export the selected XML file // </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 file (*. XML) | *. XML "; if (savefiledlg. showdialog () = system. windows. forms. dialogresult. OK) doexportxml (DS, savefiledlg. filename );} /** // <summary> // execute export /// </Summary> // <Param name = "ds"> dataset to be exported </Param> /// <Param name = "strexcelfilename"> name of the XML file to be exported </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}