private void button3_Click(object sender, RoutedEventArgs e)
{
MyExcel.Application app = new MyExcel.Application();
try
{
MyExcel.Workbook obook = app.Workbooks.Open("d:/test.xlsx"); // 添加一个工作簿
MyExcel.Worksheet osheet = (MyExcel.Worksheet)obook.Worksheets[1]; // 获取当前工作表
int r, c;
int rCount = osheet.UsedRange.Rows.Count;
int cCount = osheet.UsedRange.Columns.Count;
for (r = 2; r < rCount; r++)
//for (r = 12; r < 20; r++)
{
string sellerid = string2length(osheet.Cells[r, 1].Value.ToString(), 8);
string sellinfoid = string2length(osheet.Cells[r, 2].Value.ToString(), 11);
string bookid = "1";
string buyer = "admin";
string buytime = "2014/8/20 00:00:00";
string price = osheet.Cells[r, 4].Value.ToString();
string issold = "";
string seller = "";
string soldtime = "";
if (osheet.Cells[r, 5].Value.ToString() == "False")
{
issold = "0";
seller = null;
soldtime = null;
}
else
{
issold = "1";
seller = "admin";
soldtime = "2014/8/21 00:00:00";
}
string sql = string.Format("INSERT INTO tt_sellinfo (sellinfoid,sellerid,bookid,price,seller,buyer,buytime,issold,soldtime) VALUES ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}')",
sellinfoid, sellerid, bookid, price, seller, buyer, buytime, issold, soldtime);
dbo.AddDelUpdate(sql);
}
MessageBox.Show("数据导入完毕");
}
catch (Exception ex)
{
MyOperation.DebugPrint(ex.Message);
MyOperation.MessageShow(ex.Message);
}
finally
{
//留给用户选择是否关闭
//app.Quit();
app = null;
}
}
private void tb_password_KeyDown(object sender, KeyEventArgs e)
{
try
{
if (e.Key == Key.Enter)
{
logintest();
e.Handled = true;
}
}
catch (Exception e1)
{
MyOperation.MessageShow(e1.Message);
}
}
public static DataTable CsvToDataTable(string filepath)
{
string pCsvPath = filepath;//文件路径
DataTable table = new DataTable();
try
{
String line;
String[] split = null;
DataRow row = null;
StreamReader sr = new StreamReader(pCsvPath, System.Text.Encoding.Default);
//创建与数据源对应的数据列
line = sr.ReadLine();
split = line.Split(',');
foreach (String colname in split)
{
table.Columns.Add(colname, System.Type.GetType("System.String"));
}
//将数据填入数据表
int j = 0;
while ((line = sr.ReadLine()) != null)
{
j = 0;
row = table.NewRow();
split = line.Split(',');
foreach (String colname in split)
{
row[j] = colname;
j++;
}
table.Rows.Add(row);
}
sr.Close();
}
catch (Exception ex)
{
MyOperation.DebugPrint(ex.Message);
MyOperation.MessageShow(ex.Message);
}
finally
{
GC.Collect();
}
return(table);
}
private void button2_Click(object sender, RoutedEventArgs e)
{
if (string.IsNullOrEmpty(this.textBox1.Text))
{
MyOperation.MessageShow("请输入查询的书主信息");
this.textBox1.Focus();
return;
}
DataTable newdt = new DataTable();
newdt = dt.Clone(); // 克隆dt 的结构，包括所有 dt 架构和约束,并无数据；
DataRow[] rows = dt.Select(string.Format("phone LIKE '%{0}%'", textBox1.Text)); // 从dt 中查询符合条件的记录；
foreach (DataRow row in rows) // 将查询的结果添加到newdt中；
{
newdt.Rows.Add(row.ItemArray);
}
dataGrid.ItemsSource = newdt.DefaultView;
}
/// <summary>
/// 增删改操作
/// </summary>
/// <param name="sql">sql语句</param>
/// <returns>执行后的条数</returns>
public int AddDelUpdate(string sql)
{
MySqlConnection conn = null;
MySqlCommand cmd = null;
try
{
conn = this.GetConn();
conn.Open();
cmd = new MySqlCommand(sql, conn);
int i = cmd.ExecuteNonQuery();
conn.Close();
return(i);
}
catch (Exception ex)
{
MyOperation.DebugPrint(ex.Message);
throw;
}
}
public static void SendToExcel(DataTable Table, String SheetName = "undefine", bool Visible = true, bool close = false)
{
MyExcel.Application app = new MyExcel.Application();
try
{
MyExcel.Workbook obook = app.Workbooks.Add(Missing.Value); // 添加一个工作簿
MyExcel.Worksheet osheet = (MyExcel.Worksheet)app.ActiveSheet; // 获取当前工作表
osheet.Name = SheetName; // 修改工作表的名字
int r, c;
int rCount = Table.Rows.Count;
int cCount = Table.Columns.Count;
osheet.Range["A1:Z1"].Font.Bold = true;//设置列标题加粗
for (c = 1; c <= cCount; c++)
{
osheet.Cells[1, c] = Table.Columns[c - 1].Caption;//设置列标题
}
for (r = 1; r <= rCount; r++)
{
for (c = 1; c <= cCount; c++)
{
osheet.Cells[r + 1, c] = Convert.ToString(Table.Rows[r - 1][c - 1].ToString());
}
}
if (close)
{
obook.Save();
obook.Close(false, null, null);
app.Quit();
}
}
catch (Exception ex)
{
MyOperation.DebugPrint(ex.Message);
MyOperation.MessageShow(ex.Message);
}
finally
{
app.Visible = Visible;
app = null;
}
}
public static void SendFinanceInfo(DataTable Table, string filename = "")
{
MyExcel.Application app = new MyExcel.Application();
try
{
MyExcel.Workbook obook = app.Workbooks.Open(filename); //打开标准模板
MyExcel.Worksheet osheet = (MyExcel.Worksheet)app.ActiveSheet; // 获取当前工作表
app.Visible = true;
}
catch (Exception ex)
{
MyOperation.MessageShow(ex.Message);
}
finally
{
//留给用户选择是否关闭
//app.Quit();
app = null;
}
}
/// <summary>
/// 查询操作
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public DataTable Selectinfo(string sql)
{
MySqlConnection mysqlconn = null;
MySqlDataAdapter sda = null;
DataTable dt = null;
try
{
mysqlconn = this.GetConn();
mysqlconn.Open();
sda = new MySqlDataAdapter(sql, mysqlconn);
dt = new DataTable();
sda.Fill(dt);
mysqlconn.Close();
return(dt);
}
catch (Exception ex)
{
MyOperation.DebugPrint(ex.Message);
throw;
}
}
private void logintest()
{
if (string.IsNullOrEmpty(this.tb_user.Text) || string.IsNullOrEmpty(this.tb_password.Password))
{
return;
}
string password = MyOperation.MD5(tb_password.Password);
string sql = string.Format("SELECT * FROM `tt_staffinfo` WHERE `staffid` = '{0}' AND `password` = '{1}'",
tb_user.Text, password);
DataTable dt = dbo.Selectinfo(sql);
if (dt.Rows.Count == 1)
{
MyOperation.DebugPrint(dt.Rows[0]["name"].ToString() + " Login.");
if (this.checkBox1.IsChecked == true)
{
Properties.Settings.Default.username = tb_user.Text;
Properties.Settings.Default.Save();
}
else
{
Properties.Settings.Default.username = DefaultUserName;
Properties.Settings.Default.Save();
}
App.login_staffid = dt.Rows[0]["staffid"].ToString();
App.login_username = dt.Rows[0]["name"].ToString();
App.login_group = dt.Rows[0]["role"].ToString();
MainWindow mw = new MainWindow();
mw.Show();
this.Close();
}
else
{
ModernDialog.ShowMessage("用户名或者密码错误，请重新输入", "登陆异常", MessageBoxButton.OK);
}
}
private void button2_Click(object sender, RoutedEventArgs e)
{
MyOperation.MessageShow("本系统不提供自助注册功能，请联系管理员添加用户");
}
