//csv异步方式导入
//public async Task TaskImportCsv(IList<string> List, string path, string tname)
//{
// await Task.Run(() =>
// {
// Stopwatch watch = Stopwatch.StartNew();
// watch.Start();
// DataTable[] dt = UiServices.GetDataTableFromCSV(path,false);
// for (int i = 0; i < dt.Count(); i++)
// {
// try
// {
// db.DBProvider.SqlBulkCopyImport(List, tname, dt[i]);
// }
// catch (Exception ee)
// {
// this.BeginInvoke(new System.EventHandler(ShowErr), ee);
// return;
// }
// }
// watch.Stop();
// string s = "导入成功！";
// this.BeginInvoke(new System.EventHandler(ShowMessage), s);
// MessageBox.Show("导入成功");
// GC.Collect();
// });
//}
public async Task TaskImportCsv(IList <string> List, string path, string tname)
{
await Task.Run(() =>
{
Stopwatch watch = Stopwatch.StartNew();
watch.Start();
List <FileInfo> filelist = UiServices.GetFilelist(path, false);
for (int i = 0; i < filelist.Count(); i++)
{
try
{
string m = "正在导入第 " + (i + 1) + " 个文件：" + filelist[i].ToString();
this.BeginInvoke(new System.EventHandler(ShowMessage), m);
DataTable dt = UiServices.GetDataTableFromfile(filelist[i]);
db.DBProvider.SqlBulkCopyImport(List, tname, dt);
}
catch (Exception ee)
{
this.BeginInvoke(new System.EventHandler(ShowErr), ee);
return;
}
}
watch.Stop();
string s = "导入成功！ 使用时间：" + watch.ElapsedMilliseconds / 1000 + "秒";
this.BeginInvoke(new System.EventHandler(ShowMessage), s);
MessageBox.Show("导入成功");
GC.Collect();
});
}
private void button9_Click(object sender, EventArgs e)
{
string tbname = comboBox2.Text.ToString();
string colname = comboBox5.Text.ToString();
string csql = " select distinct " + "[" + colname + "]" + " from " + "[" + tbname + "]";
string sql = " select * from " + "[" + tbname + "]" + " where " + "[" + colname + "]" + " = ";
IList <string> clums = new List <string>();
DataTable dt = UiServices.GetDataTableFromSQL(csql);
int num = dt.Rows.Count;
if (dt.Rows.Count > 0)
{
foreach (DataRow _DataRowItem in dt.Rows)
{
clums.Add(_DataRowItem[colname].ToString());
}
}
string filename = UiServices.ShowFileDialog(tbname);
Stopwatch watch = Stopwatch.StartNew();
watch.Start();
foreach (var a in clums)
{
string savepath = filename.Remove(filename.LastIndexOf('.')) + '_' + a + ".xlsx";
DataTable dt1 = UiServices.GetDataTableFromSQL(sql + "'" + a + "'");
UiServices.SaveExcel(savepath, dt1, "sheet1");
}
watch.Stop();
toolStripStatusLabel1.Text = string.Format("导出的时间为:{0}秒", watch.ElapsedMilliseconds / 1000);
toolStripStatusLabel1.ForeColor = Color.Red;
MessageBox.Show("导出成功");
GC.Collect();
}
public async Task WriteCsvFromsql(string sql, string filename)
{
await Task.Run(() =>
{
int time = UiServices.WriteCsvFromsql(sql, filename);
string s = string.Format("单个csv方式导出的时间为:{0}秒", time);
this.BeginInvoke(new System.EventHandler(ShowMessage), s);
MessageBox.Show("导数已完成！");
GC.Collect();
});
}
public async Task TaskExport(string TableName, string sql, string filename)
{
await Task.Run(() =>
{
int time = UiServices.ExportExcel("Sheet1", sql, filename);
string s = string.Format("导出的时间为:{0}秒", time);
this.BeginInvoke(new System.EventHandler(ShowMessage), s);
//MessageBox.Show("导数已完成！");
GC.Collect();
});
}
//异步分页导出csv
public async Task TaskExportCsv(string TableName, string filename, int pagesize)
{
await Task.Run(() =>
{
int time = UiServices.WriteDataTableToCsv(TableName, pagesize, filename);
string s = string.Format("分页csv方式导出的时间为:{0}秒", time);
this.BeginInvoke(new System.EventHandler(ShowMessage), s);
MessageBox.Show("导数已完成！");
GC.Collect();
});
}
/// <summary>
/// 单excel，openXML
/// </summary>
private void button6_Click(object sender, EventArgs e)
{
string TableName = comboBox4.Text.ToString();
string filename = UiServices.ShowFileDialog(TableName);
toolStripStatusLabel1.Text = "导数中…";
toolStripStatusLabel1.ForeColor = Color.Red;
if (filename != null)
{
Task t = TaskExport(TableName, filename);
}
}
private void toolStripButton1_Click(object sender, EventArgs e)
{
try
{
string filename = UiServices.ShowFileDialog("Sheet1");
toolStripStatusLabel1.Text = "导数中…";
toolStripStatusLabel1.ForeColor = Color.Red;
Task t = TaskExport("Sheet1", sqlText.Text.ToString(), filename);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
private void button10_Click(object sender, EventArgs e)
{
string tbname = comboBox2.Text.ToString();
string colname = comboBox5.Text.ToString();
string csql = " select distinct " + "[" + colname + "]" + " from " + "[" + tbname + "]";
string sql = " select * from " + "[" + tbname + "]" + " where " + "[" + colname + "]" + " = ";
IList <string> clums = new List <string>();
DataTable dt = UiServices.GetDataTableFromSQL(csql);
int num = dt.Rows.Count;
if (dt.Rows.Count > 0)
{
foreach (DataRow _DataRowItem in dt.Rows)
{
clums.Add(_DataRowItem[colname].ToString());
}
}
string filename = "";
System.Windows.Forms.SaveFileDialog saveFileDialog1 = new System.Windows.Forms.SaveFileDialog();
saveFileDialog1.Filter = "CSV|*.csv";
saveFileDialog1.FileName = tbname;
saveFileDialog1.DefaultExt = ".csv";
if (saveFileDialog1.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
filename = saveFileDialog1.FileName.ToString();
}
else
{
return;
}
//string filename = UiServices.ShowFileDialog(tbname);
Stopwatch watch = Stopwatch.StartNew();
watch.Start();
foreach (var a in clums)
{
string savepath = filename.Remove(filename.LastIndexOf('.')) + '_' + a + ".csv";
DataTable dt1 = UiServices.GetDataTableFromSQL(sql + "'" + a + "'");
UiServices.SaveCsv(dt1, savepath);
}
watch.Stop();
toolStripStatusLabel1.Text = string.Format("导出的时间为:{0}秒", watch.ElapsedMilliseconds / 1000);
toolStripStatusLabel1.ForeColor = Color.Red;
MessageBox.Show("导出成功");
GC.Collect();
}
private void button3_Click(object sender, EventArgs e)
{
if (textBox2.Text.ToString() == "")
{
MessageBox.Show("请选择需要导入的文件夹！");
}
DataTable[] ds = UiServices.GetDataTableFromCSV(this.textBox2.Text.ToString(), true);
string filename = UiServices.ShowFileDialog("Book1");
int time = UiServices.ExportExcel(ds, filename);
GC.Collect();
toolStripStatusLabel1.Text = string.Format("执行操作时间为:{0}秒", time);
toolStripStatusLabel1.ForeColor = Color.Red;
}
public static int WriteCsvFromsql(string sql, string filename)
{
try
{
DataTable dt = UiServices.GetDataTableFromSQL(sql);
int i = SaveCsv(dt, filename);
return(i);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
return(-1);
}
//导出EXCEL模板文件
private void btnTP_Click(object sender, EventArgs e)
{
if (comboBox1.Text.ToString() == "")
{
MessageBox.Show("请选择需要导出模板的表名！");
}
else
{
string TableName = comboBox1.Text.ToString();
string filename = UiServices.ShowFileDialog(TableName);
int time = UiServices.ExportTemplate(TableName, filename);
toolStripStatusLabel1.Text = string.Format("导出的时间为:{0}秒", time);
toolStripStatusLabel1.ForeColor = Color.Red;
MessageBox.Show("导出成功！");
}
}
//异步导出多csv
public async Task TaskExportMuticsv(IList <string> SheetNames, string filename)
{
await Task.Run(() =>
{
try
{
int time = UiServices.ExportMuticsv(SheetNames, filename);
string s = string.Format("导出的时间为:{0}秒", time);
this.BeginInvoke(new System.EventHandler(ShowMessage), s);
MessageBox.Show("导数已完成！");
GC.Collect();
}
catch (Exception ee)
{
this.BeginInvoke(new System.EventHandler(ShowErr), ee);
return;
}
});
}
public static int WriteDataTableToCsv(string TabelName, string FileName)
{
if (FileName != null)
{
try
{
Stopwatch watch = Stopwatch.StartNew();
watch.Start();
DataTable dt = UiServices.GetDataTableFromName(TabelName);
SaveCsv(dt, FileName);
watch.Stop();
return(Convert.ToInt32(watch.ElapsedMilliseconds / 1000));
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
return(-1);
}
public static int WriteDataTableToCsv(string TabelName)
{
System.Windows.Forms.SaveFileDialog saveFileDialog1 = new System.Windows.Forms.SaveFileDialog();
saveFileDialog1.Filter = "csv文件|*.csv";
saveFileDialog1.FileName = TabelName;
saveFileDialog1.DefaultExt = ".csv";
if (saveFileDialog1.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
string filename = saveFileDialog1.FileName.ToString();
try
{
DataTable dt = UiServices.GetDataTableFromName(TabelName);
int i = SaveCsv(dt, filename);
return(i);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
return(-1);
}
//导出数据
private void btnDtout_Click(object sender, EventArgs e)
{
if (listBox1.Items.Count < 1)
{
MessageBox.Show("请选择需要导入的表名！");
}
else
{
IList <string> SheetNames = new List <string>();
foreach (var item in listBox1.Items)
{
SheetNames.Add(item.ToString());
}
string filename = UiServices.ShowFileDialog(SheetNames[0]);
toolStripStatusLabel1.Text = "导数中…";
toolStripStatusLabel1.ForeColor = Color.Red;
if (filename != null)
{
Task t = TaskExport(SheetNames, filename);
}
}
}
//excel异步方式导入
public async Task TaskImport(IList <string> List, string filename, string tname)
{
await Task.Run(() =>
{
Stopwatch watch = Stopwatch.StartNew();
watch.Start();
try
{
DataTable dt = UiServices.GetExcelDataTable(filename, tname);
db.DBProvider.SqlBulkCopyImport(List, tname, dt);
}
catch (Exception ee)
{
this.BeginInvoke(new System.EventHandler(ShowErr), ee);
return;
}
watch.Stop();
string s = "导入成功！ 使用时间：" + watch.ElapsedMilliseconds / 1000 + "秒";
this.BeginInvoke(new System.EventHandler(ShowMessage), s);
MessageBox.Show("导入成功！");
GC.Collect();
});
}
