| Before on B/S, I have written Excel import and export, in fact, the code is almost the first time I write notes, said may be a little 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 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 column headers
Colheaders + = "department" + "T";
Colheaders + + "login account" + "T";
Colheaders + = "name" + "T";
Colheaders + + "password" + "T";
Colheaders + + "Telephone" + "T";
Colheaders + + "mobile" + "T";
Colheaders + + "Email" + "T";
Colheaders = "Birthday" + "T";
Colheaders + = "Expired Time";
Sw. WriteLine (colheaders);
datarow[] myrow = dt. Select ();
Writing column Contents
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 ("Export" + filename + "] success", "hint"); }
}
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 = "Tabular 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$]
OleDbDataReader ODR = cmd. ExecuteReader (); while (ODR. Read ())
{
if (odr[0). ToString ()!= "")
{
if (odr[0). ToString () = = "department")/Filter column header Press your actual Excel file
Continue
if (Userlogic.checkuserlogin (odr[1). ToString ()) = = 0)
{
........................ Writing to the database }
}
}
Odr. Close ();
}
}
}
Con. Close ();
return true;
}
catch (Exception ex)
{
MessageBox.Show (ex. message);
return false;
}
}
<summary>
Query table name
</summary>
<param name= "Con" ></param>
<returns></returns>
public static string[] Getexcelsheetnames (OleDbConnection con)
{
Try
{
DataTable dt = con. GetOleDbSchemaTable (OleDbSchemaGuid.Tables, new[] {null, NULL, NULL, "Table"});//Retrieve schema information for Excel
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 |
