I used to write EXCEL Import and Export on B/s. In fact, the code is almost the same. I wrote my note for the first time, and it may be a bit fuzzy. # Region export EXCEL
Private void DataGridViewToExcel (DataTable dt, string filename)
{
Try
{
SaveFileDialog dlg = new SaveFileDialog ();
Dlg. Filter = "Execl files (*. xls) | *. xls ";
Dlg. FilterIndex = 0;
Dlg. RestoreDirectory = true;
Dlg. Title = "Save As an Excel file ";
Dlg. FileName = filename;
If (dlg. ShowDialog () = DialogResult. OK)
{
Stream myStream;
MyStream = dlg. OpenFile ();
StreamWriter sw = new StreamWriter (myStream, System. Text. Encoding. GetEncoding (-0 ));
String colHeaders = "", ls_item = "";
// Write the column title
ColHeaders + = "department" + "t ";
ColHeaders + = "Logon account" + "t ";
ColHeaders + = "name" + "t ";
ColHeaders + = "password" + "t ";
ColHeaders + = "phone" + "t ";
ColHeaders + = "Mobile Phone" + "t ";
ColHeaders + = "Email" + "t ";
ColHeaders + = "Birthday" + "t ";
ColHeaders + = "expiration time ";
Sw. WriteLine (colHeaders );
DataRow [] myRow = dt. Select ();
// Write column content
Foreach (DataRow row in myRow)
{
Ls_item + = row ["dept_name"]. ToString () + "t ";
Ls_item + = row ["user_loginName"]. ToString () + "t ";
Ls_item + = row ["user_realName"]. ToString () + "t ";
Ls_item + = "" + "t ";
Ls_item + = row ["user_telephone"]. ToString () + "t ";
Ls_item + = row ["user_mobile"]. ToString () + "t ";
Ls_item + = row ["user_email"]. ToString () + "t ";
Ls_item + = row ["user_birthday"]. ToString () + "t ";
Ls_item + = row ["user_expiredTime"]. ToString ();
Sw. WriteLine (ls_item );
Ls_item = "";
}
Sw. Close ();
MyStream. Close ();
MessageBox. Show ("exported [" + filename + "] succeeded", "prompt "); }
}
Catch (Exception e)
{
MessageBox. Show (e. Message );
}
}
# Endregion # Region Excel Import Private void tsinuser_Click (object sender, EventArgs e)
{
OpenFileDialog openFileDialog = new OpenFileDialog ();
OpenFileDialog. Filter = "Table file (*. xls) | *. xls ";
OpenFileDialog. RestoreDirectory = true;
OpenFileDialog. FilterIndex = 1;
If (openFileDialog. ShowDialog () = DialogResult. OK)
{
InExcelData (openFileDialog. FileName );
}
}
Private bool InExcelData (string filePath)
{
Try
{
HG_dms_folderEntity folderEnt = new HG_dms_folderEntity (); string folderid;
String strConn = "Provider = Microsoft. Jet. OLEDB.4.0;" + "Data Source =" + filePath + ";" + "Extended Properties = Excel 8.0 ;"; OleDbConnection con = new OleDbConnection (strConn );
Con. Open ();
String [] names = GetExcelSheetNames (con); // GetExcelSheetNames (filePath );
If (names! = Null)
{
If (names. Length> 0)
{
Foreach (string name in names)
{
OleDbCommand cmd = con. CreateCommand ();
Cmd. CommandText = string. Format ("select * from [{0}]", name); // [sheetName $] must be in this Format
OleDbDataReader odr = cmd. ExecuteReader (); While (odr. Read ())
{
If (odr [0]. ToString ()! = "")
{
If (odr [0]. ToString () = "Department") // filter the column header according to your actual Excel File
Continue;
If (userLogic. CheckUserLogin (odr [1]. ToString () = 0)
{
........................ Write to database }
}
}
Odr. Close ();
}
}
}
Con. Close ();
Return true;
}
Catch (Exception ex)
{
MessageBox. Show (ex. Message );
Return false;
}
}
/// <Summary>
/// Query the table name
/// </Summary>
/// <Param name = "con"> </param>
/// <Returns> </returns>
Public static string [] GetExcelSheetNames (OleDbConnection con)
{
Try
{
DataTable dt = con. GetOleDbSchemaTable (OleDbSchemaGuid. Tables, new [] {null, "Table"}); // retrieves Excel schema information
Var sheet = new string [dt. Rows. Count];
For (int I = 0, j = dt. Rows. Count; I <j; I ++)
{
Sheet [I] = dt. Rows [I] ["TABLE_NAME"]. ToString ();
}
Return sheet;
}
Catch
{
Return null;
}
} # Endregion |
