Interaction between WPF and EXCEL

 

In our daily work, we often need to deal with the office, and our program may also need to export the data as excel for further processing. In WPF, I find it easier to interact with excel in two ways:

 

Write to excel:

Code

 System. Windows. Forms. SaveFileDialog sd = new System. Windows. Forms. SaveFileDialog ();
Sd. Filter = "Excel 2003 document | *. xls ";
If (sd. ShowDialog () = System. Windows. Forms. DialogResult. OK)
{
FileStream fs = new FileStream (sd. FileName, FileMode. Create, FileAccess. Write );
StreamWriter sw = new StreamWriter (fs, Encoding. Default );
String line = "";
// Write the column title
For (int I = 0; I <datamain. Columns. Count; I ++)
{
Line + = datamain. Columns [I]. Header. ToString () + "\ t ";
}
Sw. WriteLine (line );
For (int j = 0; j <datamain. Items. Count; j ++)
{

Statresult dt = datamain. Items [j] as Statresult;
Line = dt. client + "\ t" + dt. total + "\ t" + dt. receive + "\ t" + dt. rates + "\ t" + dt. inspection + "\ t" + dt. bugnum + "\ t" + dt. qualified + "\ t ";
Sw. WriteLine (line );

}
// Close the stream
Sw. Close ();
Fs. Close ();
MessageBox. Show ("data has been exported successfully! "," NOTE ", MessageBoxButton. OK, MessageBoxImage. Information );
}

Read excel:

Code

 // Connect the excel file in oledb mode and read the mixed type as text type
StrConn = "Provider = Microsoft. Jet. OLEDB.4.0; Data Source = C: \ Erp1912.xls; Extended Properties = 'excel8. 0; HDR = Yes; IMEX = 1 '";
OleDbConnection conn = new OleDbConnection (strCon );
// Use a worksheet as a data table
String SQL = "SELECT * FROM [Sheet1 $]";
OleDbDataAdapter adp = new OleDbDataAdapter (SQL, conn );
DataSet myDataSet = new DataSet ();
Adp. Fill (myDataSet, "[Sheet1 $]");