C # excel and xml operations

You need to add the COM component Interop. Excel. dll, Interop. Microsoft. Office. Core. dll

Using System;
Using System. Collections. Generic;
Using System. ComponentModel;
Using System. Data;
Using System. Data. OleDb;
Using System. Text;
Using System. Drawing;
Using System. IO;
Using System. Runtime. InteropServices;

Using Excel = Microsoft. Office. Interop. Excel;
Using Microsoft. Office. Interop. 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: 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
/////

//// Import a file from Excel
/////
///// Excel file name
///// Return DataSet
// Public DataSet ImportFromExcel (string strExcelFileName)
//{
// Return doImport (strExcelFileName );
//}
///
/// Import from the selected Excel File
///
/// DataSet
Public DataSet ImportFromExcel ()
{
DataSet ds = new DataSet ();
If (openFileDlg. ShowDialog () = System. Windows. Forms. DialogResult. OK)
Ds = doImport (openFileDlg. FileName );
Return ds;
}
/**/
///
/// Import from the specified Excel File
///
/// Excel file name
///
Public DataSet ImportFromExcel (string strFileName)
{
DataSet ds = new DataSet ();
Ds = doImport (strFileName );
Return ds;
}
///
/// Execute Import
///
/// File Name
/// DataSet
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;



}

// From DataSet to Excel # region from DataSet to Excel
///
/// Export the specified Excel File
///
/// DataSet to Be Exported
/// Excel file name to export
Public void ExportToExcel (DataSet ds, string strExcelFileName)
{
If (ds. Tables. Count = 0 | strExcelFileName = "") return;
DoExport (ds, strExcelFileName );


}
///
/// Export the selected Excel File
///
/// DataSet
Public void ExportToExcel (DataSet ds)
{
If (saveFileDlg. ShowDialog () = System. Windows. Forms. DialogResult. OK)
DoExport (ds, saveFileDlg. FileName );

}
///
/// Execute Export
///
/// DataSet to Be Exported
/// File name to export
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 );
// Handle the damn prompt
Excel. AlertBeforeOverwriting = false; // be sure to enter the prompt that the modification is not displayed.
If (File. Exists (strExcelFileName + ". XLS") {File. Delete (strExcelFileName + ". XLS ");}

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, true );
// Excel. ActiveWorkbook. SaveAs (

// WkbNew. SaveAs strBookName


// Excel. Save (strExcelFileName );
Excel. Quit ();
Excel = null;

GC. Collect (); // garbage collection
}

// Import from XML to Dataset # region import from XML to Dataset

///
/// Import from the selected XML file
///
/// DataSet
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;
}
///
/// Import from the specified XML file
///
/// XML file name
///
Public DataSet ImportFromXML (string strFileName)
{
If (strFileName = "")
Return null;
DataSet ds = new DataSet ();
Try {ds. ReadXml (strFileName, System. Data. XmlReadMode. ReadSchema );}
Catch {}
Return ds;
}


// Export data from DataSet to XML # region export data from DataSet to XML
///
/// Export the specified XML file
///
/// DataSet to Be Exported
/// XML file name to be exported
Public void ExportToXML (DataSet ds, string strXMLFileName)
{
If (ds. Tables. Count = 0 | strXMLFileName = "") return;
DoExportXML (ds, strXMLFileName );
}
///
/// Export the selected XML file
///
/// DataSet
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 );
}

///
/// Execute Export
///
/// DataSet to Be Exported
/// XML file name to be exported
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 ");}
}
}