Read and Write Excel files and convert Excel Data to access [Code]

@@@@@@@@@

// The following Code reads data from an Excel file and writes it to access.
// Introduce EXCEL to create an Excel source data file and an access target data file on disk D

Public static datatable exceltodatatable (string strexcelfilename, string strsheetname)
{
String strconn = "provider = Microsoft. Jet. oledb.4.0;" + "Data Source =" + strexcelfilename + ";" + "extended properties = Excel 5.0 ;";
String strexcel = string. Format ("select * from [{0} $]", strsheetname );
Dataset DS = new dataset ();

Using (oledbconnection conn = new oledbconnection (strconn ))
{
Conn. open ();
Oledbdataadapter adapter = new oledbdataadapter (strexcel, strconn );
Adapter. Fill (DS, strsheetname );
Conn. Close ();
}

Return Ds. Tables [strsheetname];
}

Public static void insertdatatoaccess (oledbconnection oledbconn, string v0, string V1, string V2, string V3, string V4, string V5)
{
String strinsertstring = "insert into T1 (ID, [order], name, age, sex, demo) values (@ ID, @ order, @ name, @ age, @ sex, @ demo )";

Oledbcommand ocomm = new oledbcommand (strinsertstring, oledbconn );

Ocomm. Parameters. Add ("@ ID", oledbtype. Char, 50 );
Ocomm. Parameters ["@ ID"]. value = V0;

Ocomm. Parameters. Add ("@ order", oledbtype. Char, 50 );
Ocomm. Parameters ["@ order"]. value = V1;

Ocomm. Parameters. Add ("@ name", oledbtype. Char, 50 );
Ocomm. Parameters ["@ name"]. value = V2;

Ocomm. Parameters. Add ("@ age", oledbtype. Char, 50 );
Ocomm. Parameters ["@ age"]. value = V3;

Ocomm. Parameters. Add ("@ sex", oledbtype. Char, 50 );
Ocomm. Parameters ["@ sex"]. value = V4;

Ocomm. Parameters. Add ("@ Demo", oledbtype. Char, 50 );
Ocomm. Parameters ["@ Demo"]. value = V5;

Ocomm. executenonquery ();

}

Private void form1_load (Object sender, eventargs E)
{
Datatable dtexcel = exceltodatatable ("D: // B .xls", "sheet1 ");

Oledbconnection oledbconn = new oledbconnection ();
Oledbconn. connectionstring = @ "provider = Microsoft. Jet. oledb.4.0; Data Source = D:/a. mdb; user id = admin; Password = ;";
Oledbconn. open ();

For (INT I = 0; I <dtexcel. Rows. Count-1; I ++)
{
String nowtime = system. datetime. Now. tostring ("yyyy/mm/dd hh: mm: SS ");

Insertdatatoaccess (oledbconn,
Dtexcel. Rows [I] [0]. tostring (),
Dtexcel. Rows [I] [1]. tostring (),
Dtexcel. Rows [I] [2]. tostring (),
Dtexcel. Rows [I] [3]. tostring (),
Dtexcel. Rows [I] [4]. tostring (),
Nowtime
);
}
Oledbconn. Close ();

@@@@@@@@@
// The following Code reads and writes an Excel file.
// Basic conditions for code usage
// * Using system. reflection; // reference this to use the missing Field
// * Use Excel for external reference
// Missing is used to generate the default parameter of a function through reflection.
// During the operation of writing External Object components, the null error is sometimes called for a parameter not used in the called method. Fill in this parameter.


// Function starts

Excel. Application xapp = new excel. applicationclass ();
Xapp. Visible = true;


// Obtain the workbook object. You can use either of the following methods:

Excel. Workbook xbook = xapp. workbooks. _ open (@ "D:/B .xls ",
Missing. Value, missing. Value
, Missing. Value
, Missing. Value, missing. value );

// Method 2 create an xls file
// Excel. Workbook xbook = xapp. workbooks. Add (missing. value );

// Obtain the sheet of the current xls file. The first sentence is the first and the second sentence is the active one.
Excel. worksheet xsheet = (Excel. worksheet) xbook. Sheets [1];
// Excel. worksheet xsheet = (Excel. worksheet) xapp. activesheet;

// Read data through the range object

Excel. Range rng1 = xsheet. get_range ("A1", type. Missing );

// Read. The range object is used, but different interfaces are used to obtain the range.
Excel. Range rng2 = (Excel. Range) xsheet. cells [3, 1];
Console. writeline (rng2.value2 );

// Write data
// Regardless of the range attribute, cells, rows, and columns attributes, the data type is Excel. range, and the value2 attribute is available.
// Obtain the value of value. value2 is for numbers.

Excel. Range rng3 = xsheet. get_range ("C6", missing. value );
Rng3.value2 = 4;
Rng3.interior. colorindex = 4; // set the background color (green) of the range)

/// Save Method 1: Save the workbook
// Xbook. saveas (@ "D:/cdata.xls ",
// Missing. Value, missing. Value, missing. value,
// Excel. xlsaveasaccessmode. xlnochange, missing. Value, missing. Value, missing. value,
// Missing. Value, missing. value );

/// Method 2: Save the worksheet
// Xsheet. saveas (@ "D:/cdata2.xls ",
// Missing. Value, missing. value,
// Missing. Value, missing. Value, missing. value );

// Method 3
Xbook. Save ();

Xsheet = NULL;
Xbook = NULL;
Xapp. Quit (); // This sentence is very important, otherwise the EXCEL object cannot exit from the memory
Xapp = NULL;

@@@@@@@@@
/**
* This is a C #-based program for adding, deleting, modifying, and querying access databases. It can run through.
*
* However, I think the program structure is not optimized yet, for example:
*
* 1. oledbcommand odcommand = odcconnection. createcommand () and odcommand. commandtext = strsql; can you submit it separately?
*
* 2. to implement a comprehensive function, you only need two parameters (Conn, SQL) to execute all SQL files. When an update, insert, or delete operation occurs, the number of items can be reported.
*
* 3. If the insert and update statements have a large number of fields, can they be used to simplify SQL statements, for example? As in Java. How to implement it.
*
* 4. How can I enable the database to connect to this part of the function independently (made into a base, including access, SQL, and Oracle) and then run the SQL statement by adding parameters.
*
**/

// Establish a connection, initial
Oledbconnection conn = initconn ();

// Query
String strsql = "select * from T1 ";
Selectsta (Conn, strsql );

// Update
String strupdate = "Update T1 set age = 12 ";
Updatestatus (Conn, strupdate );

// Insert
String strrecorder = ", 'Tom ', 'ddd '";
String strinsert = "insert into T1 values (" + strrecorder + ")"; // can I modify it? This is too awkward.
Insertstatus (Conn, strinsert );

// Delete
String strdel = "delete from T1 where age <> '12 '";
Deletestatus (Conn, strdel );
Conn. Close ();
}

// Query processing
Private void selectsta (oledbconnection odcconnection, string strsql)
{
Oledbcommand odcommand = odcconnection. createcommand ();
Odcommand. commandtext = strsql;
Oledbdatareader odrreader = odcommand. executereader ();
Arraylist arr = new arraylist ();
While (odrreader. Read ())
{
Arr. Add (odrreader ["name"]. tostring ());
Listbox1.items. Add (odrreader ["name"]. tostring ());
}
}

// Update Processing
Private void updatestatus (oledbconnection Conn, string strupdate)
{
Oledbcommand odcommand = conn. createcommand ();
Odcommand. commandtext = strupdate;
Int intcnt = odcommand. executenonquery ();
MessageBox. Show ("A total of [" + intcnt + "] data is updated! ");
}

// Delete
Private void deletestatus (oledbconnection Conn, string strdel)
{
Oledbcommand odcommand = conn. createcommand ();
Odcommand. commandtext = strdel;
Int intcnt = odcommand. executenonquery ();
MessageBox. Show ("A total of [" + intcnt + "] pieces of data are deleted! ");
}

// Insert Processing
Private void insertstatus (oledbconnection Conn, string strinsert)
{
Oledbcommand odcommand = conn. createcommand ();
Odcommand. commandtext = strinsert;
Int intcnt = odcommand. executenonquery ();
MessageBox. Show ("A total of [" + intcnt + "] data is inserted! ");
}

// Initial connection
Private oledbconnection initconn ()
{
String strconn = @ "provider = Microsoft. Jet. oledb.4.0; Data Source = D:/a. mdb ";
Oledbconnection odcconnection = new oledbconnection (strconn );
Odcconnection. open ();
Return odcconnection;
}