C # excel and xml operations

Source: Internet
Author: User

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 ");}
}
}

Related Article

E-Commerce Solutions

Leverage the same tools powering the Alibaba Ecosystem

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.