How to import and export Winform, and how to import and export winform

How to import and export Winform, and how to import and export winform

Export: reference using Excel = Microsoft. Office. Interop. Excel;

 

 

 

 

 

# Region reading excel
// Open method
Public DataTable ExceltoDataSet (string path)
{
String strConn = "Provider = Microsoft. ACE. OLEDB.12.0; Data Source =" + path + "; Extended Properties = 'excel 12.0; HDR = Yes; IMEX = 1 ';";
OleDbConnection conn = new OleDbConnection (strConn );
Conn. Open ();
System. Data. DataTable schemaTable = conn. GetOleDbSchemaTable (System. Data. OleDb. OleDbSchemaGuid. Tables, null );
String tableName = schemaTable. Rows [0] [2]. ToString (). Trim ();

String strExcel = "";
OleDbDataAdapter myCommand = null;
DataSet ds = null;
StrExcel = "Select * From [" + tableName + "]";
MyCommand = new OleDbDataAdapter (strExcel, strConn );

Ds = new DataSet ();

MyCommand. Fill (ds, tableName );
System. Data. DataTable dt = ds. Tables [0];

Return dt;

}

# Endregion

# Region export excel
/// <Summary>
/// Save the data table to an Excel table
/// </Summary>
/// <Param name = "addr"> address of the Excel table (part after the program running directory) </param>
/// <Param name = "dt"> DataTable to be output </param>
Public void SaveToExcel (string addr, System. Data. DataTable dt, string sheetName)
{
// 0. Note:
// * In Excel, the expression is like Cells [x] [y]. The number above is a column and the number behind it is a row!
// * Rows and columns in Excel start from 1 instead of 0.
// 1. Create a New Excel document instance
Excel: Application xlsApp = new Excel: Application ();
XlsApp. Workbooks. Add (true );
/* Example input: note that the array in Excel starts with 1 (instead of 0)
* For (int I = 1; I <10; I ++)
*{
* For (int j = 1; j <10; j ++)
*{
* XlsApp. Cells [I] [j] = "-";
*}
*}
*/
// 2. Set the title of the Excel Tab
XlsApp. ActiveSheet. Name = sheetName;
// 3. Merge the cells in the first row
String temp = "";
If (dt. Columns. Count <26)
{
Temp = (char) ('A' + dt. Columns. Count). ToString ();
}
Else if (dt. Columns. Count <= 26 + 26*26)
{
Temp = (char) ('A' + (dt. Columns. Count-26)/26). ToString ()
+ (Char) ('A' + (dt. Columns. Count-26) % 26). ToString ();
}
Else throw new Exception ("too many columns ");
Excel: Range range = xlsApp. get_Range ("A1", temp + "1 ");
// Range. ClearContents (); // clear the region to be merged
// Range. MergeCells = true; // merge Cells
/// 4. Fill in the first row: Table Name, corresponding to the TableName of the DataTable
// XlsApp. Cells [1] [1] = dt. TableName;
// XlsApp. Cells [1] [1]. Font. Name = "";
// XlsApp. Cells [1] [1]. Font. Size = 25;
// XlsApp. Cells [1] [1]. Font. Bold = true;
// XlsApp. Cells [1] [1]. HorizontalAlignment = Excel. XlVAlign. xlVAlignCenter; // center
// XlsApp. Rows [1]. RowHeight = 60; // The height of the first row is 60 (lbs)
/// 5. merge the second row of cells to write the table generation date.
// Range = xlsApp. get_Range ("A2", temp + "2 ");
// Range. ClearContents (); // clear the region to be merged
// Range. MergeCells = true; // merge Cells
/// 6. Enter the second line: generation time
// XlsApp. Cells [1] [2] = "The report is generated on:" + DateTime. Now. ToString ();
// XlsApp. Cells [1] [2]. Font. Name = "";
// XlsApp. Cells [1] [2]. Font. Size = 15;
/// XlsApp. Cells [1] [2]. HorizontalAlignment = 4; // right alignment
// XlsApp. Cells [1] [2]. HorizontalAlignment = Excel. XlVAlign. xlVAlignCenter; // center
// XlsApp. Rows [2]. RowHeight = 30; // The height of the first row is 60 (unit: lbs)
// 7. Enter the header row of each column
XlsApp. Cells [1] [1] = "ID ";
For (int I = 0; I <dt. Columns. Count; I ++)
{
XlsApp. Cells [I + 2] [1] = dt. Columns [I]. ColumnName;
}
XlsApp. Rows [1]. Font. Name = "";
XlsApp. Rows [1]. Font. Size = 15;
XlsApp. Rows [1]. Font. Bold = true;
// XlsApp. Rows [1]. HorizontalAlignment = Excel. XlVAlign. xlVAlignCenter; // center
//// Set the color
// Range = xlsApp. get_Range ("A3", temp + "3 ");
// Range. Interior. ColorIndex = 33;
// 8. Fill in the data in the DataTable
For (int I = 0; I <dt. Rows. Count; I ++)
{
XlsApp. Cells [1] [I + 2] = (I + 1). ToString ();
For (int j = 0; j <dt. Columns. Count; j ++)
{
XlsApp. Cells [j + 2] [I + 2] = dt. Rows [I] [j];
}
}
Range = xlsApp. get_Range ("A4", temp + (dt. Rows. Count + 1). ToString ());
// Range. Interior. ColorIndex = 37;
// Range. HorizontalAlignment = Excel. XlVAlign. xlVAlignCenter;
/// 9. Draw the border
// Range = xlsApp. get_Range ("A1", temp + (dt. Rows. Count + 1). ToString ());
// Range. Borders. LineStyle = 1;
// Range. Borders. Weight = 3;
// 10. Open the created table
// XlsApp. Visible = true;
// 11. Save the table to the file with the specified name in the root directory.
XlsApp. ActiveWorkbook. SaveAs (addr );
XlsApp. Quit ();
XlsApp = null;
GC. Collect ();
}
# Endregion