Export any form of Excel (sharing experiences)

///

/// Generate an Excel file
/// _ Til is the table title, _ row is the number of rows in the table, _ col is the number of columns in the table, _ dt is the data table, and _ filename is the name of the saved Excel table.
///
Public Void Savetoexcel ( String _ Til, Int _ Row, Int _ Col, datatable _ DT, String _ Filename)
{
Microsoft. Office. InterOP. owc11.spreadsheetclass xlsheet = New Microsoft. Office. InterOP. owc11.spreadsheetclass ();
// Merge Cells
Xlsheet. get_range (xlsheet. cells [ 1 , 1 ], Xlsheet. cells [ 1 , _ Col]). set_mergecells ( True );
// Xlsheet. get_range (xlsheet. cells [2, 1], xlsheet. cells [3, 2]). set_mergecells (true );
Xlsheet. activesheet. cells [ 1 , 1 ] = _ Til;
// Bold font
Xlsheet. get_range (xlsheet. cells [ 1 , 1 ], Xlsheet. cells [ 1 , _ Col]). Font. set_bold ( True );
// Cell text align horizontally
Xlsheet. get_range (xlsheet. cells [ 1 , 1 ], Xlsheet. cells [_ row + 2 , _ Col]). set_horizontalalignment (Microsoft. Office. InterOP. owc11.xlhalign. xlhaligncenter );
// Set Font Size
Xlsheet. get_range (xlsheet. cells [ 1 , 1 ], Xlsheet. cells [ 1 , _ Col]). Font. set_size ( 14 );
// Set column width
// Xlsheet. get_range (xlsheet. cells [1, 3], xlsheet. cells [1, 3]). set_columnwidth (50 );

//Draw borders
Xlsheet. get_range (xlsheet. cells [1,1], Xlsheet. cells [_ row+ 2, _ Col]). Borders. set_linestyle (Microsoft. Office. InterOP. owc11.xllinestyle. xlcontinuous );

// Defines a 2-dimensional array to store data in a able.
Object [,] Dataarray = New Object [_ Row, _ Col];
// Add column name
For ( Int K = 0 ; K < _ DT. Columns. Count; k ++ )
{
Xlsheet. activesheet. cells [ 2 , K + 1 ] = _ DT. Columns [K]. Caption. tostring ();
}
// Export the data in the able to a 2-dimensional array
For ( Int I = 0 ; I < _ Row; I ++ )
{
For ( Int J = 0 ; J < _ Col; j ++ )
{
Dataarray [I, j] = _ DT. Rows [I] [J];
}
}
// Import data from a 2-dimensional array to excel
Xlsheet. get_range ( " A3 " , Xlsheet. cells [_ row + 2 , _ Col]). value2 = Dataarray;
Xlsheet. Export (_ filename, Microsoft. Office. InterOP. owc11.sheetexportactionenum. ssexportactionnone, Microsoft. Office. InterOP. owc11.sheetexportformat. ssexportxmlspreadsheet );
}
///

/// Import an Excel table
///
///
///
Public Dataset createdatasource ( String Strfilename, String Sqlstr)
{
String Strcon = " Provider = Microsoft. Jet. oledb.4.0; Data Source = " + Strfilename + " ; Extended properties = Excel 8.0; " ;
Oledbconnection olecon = New Oledbconnection (strcon );
Oledbdataadapter myda = New Oledbdataadapter (sqlstr, olecon );
Dataset myds = New Dataset ();
Myda. Fill (myds );
Return Myds;
}