public void readexcel1(DataTable dt)
{
string path = System.Windows.Forms.Application.StartupPath + "\\export.xlsx";
filePath = System.Windows.Forms.Application.StartupPath + "\\exportcopy" + DateTime.Now.ToString().Replace('/', '-').Replace("-", "").Replace(":", "").Replace(" ", "") + ".xlsx";
if (!File.Exists(path))
{
MessageBox.Show("模板文件不存在！");
return;
}
File.Copy(path, filePath, true);
object missing = Type.Missing;
Microsoft.Office.Interop.Excel.Application ExcelRS;
Microsoft.Office.Interop.Excel.Workbook RSbook;
Microsoft.Office.Interop.Excel.Worksheet RSsheet;
ExcelRS = new Microsoft.Office.Interop.Excel.Application();
//打开目标文件filePath
RSbook = ExcelRS.Workbooks.Open(filePath, missing, missing, missing, missing, missing,
missing, missing, missing, missing, missing, missing, missing, missing, missing);
RSsheet = (Microsoft.Office.Interop.Excel.Worksheet)RSbook.Sheets.get_Item(1);
try
{
// ExcelRS = null;
//实例化ExcelRS对象
// MessageBox.Show("开始保存");
RSsheet.Activate();
//设置第一个工作溥
// int row = 2;
for (int r = 0; r < 10; r++)
{
for (int c = 0; c < dt.Columns.Count; c++)
{
RSsheet.Cells[r + 2, c + 1] = dt.Rows[r][c].ToString();
}
// row++;
this.Invoke(new Action(() =>
{
richTextBox4.AppendText(DateTime.Now.ToString() + "--保存第" + (r + 1).ToString() + "记录中... ");
}));
}
richTextBox4.AppendText(DateTime.Now.ToString() + "--保存记录完成... ");
}
catch (Exception er)
{
MessageBox.Show(er.ToString());
}
finally
{
ExcelRS.DisplayAlerts = false;
RSbook.Save();
ExcelRS.Visible = false;
}
}
public void readexcel()
{
if (openFileDialog1.FileName != "")
{
this.Invoke(new Action(() =>
{
richTextBox1.AppendText("开始导入数据..." + "\r\n");
}));
int col = 0;
int totalnum = 0;
DataTable dt = ora.GetDt("select '' as NOROW, GOODSID,GOODSNAME,QUANTITY,PLACECODE from temp_zhy_excption_2017th");
DataRow dr;
// Sqlexec sqlex = new Sqlexec(getelement("connstr"));
object missing = Type.Missing;
Microsoft.Office.Interop.Excel.Application ExcelRS;
Microsoft.Office.Interop.Excel.Workbook RSbook;
Microsoft.Office.Interop.Excel.Worksheet RSsheet;
// ExcelRS = null;
//实例化ExcelRS对象
ExcelRS = new Microsoft.Office.Interop.Excel.Application();
//打开目标文件filePath
RSbook = ExcelRS.Workbooks.Open(openFileDialog1.FileName, missing, missing, missing, missing, missing,
missing, missing, missing, missing, missing, missing, missing, missing, missing);
//设置第一个工作溥
RSsheet = (Microsoft.Office.Interop.Excel.Worksheet)RSbook.Sheets.get_Item(1);
//激活当前工作溥
RSsheet.Activate();
StringBuilder errmsg = new StringBuilder();
// object missing = Type.Missing;
errmsg.Append("错误信息:");
try
{
string GOODSID, GOODSNAME, QUANTITY, PLACECODE;
int Qty;
int i = 0;
int result = 0;
string insql;
for (int row = int.Parse(textBox1.Text); row < int.Parse(textBox2.Text) + 1; row++)
{
this.Invoke(new Action(() =>
{
richTextBox1.AppendText("读取第" + row.ToString() + "行记录中..." + "\r\n");
this.button5.Enabled = false;
}));
GOODSID = ((Microsoft.Office.Interop.Excel.Range)RSsheet.Cells[row, 1]).Text;
QUANTITY = ((Microsoft.Office.Interop.Excel.Range)RSsheet.Cells[row, 2]).Text;
PLACECODE = ((Microsoft.Office.Interop.Excel.Range)RSsheet.Cells[row, 3]).Text;
if (int.TryParse(QUANTITY, out Qty))
{
}
else
{
this.Invoke(new Action(() =>
{
richTextBox1.AppendText("第" + row.ToString() + "行记录数量不是正确格式" + "\r\n");
}));
Qty = 0;
}
totalnum += Qty;
dr = dt.NewRow();
dr[0] = row;
dr[1] = GOODSID;
dr[2] = GetGoodsName(GOODSID);
dr[3] = Qty;
dr[4] = PLACECODE;
dt.Rows.Add(dr);
insql = $"insert into temp_zhy_excption_2017th(GOODSID,GOODSNAME,QUANTITY,PLACECODE) values('{GOODSID}','{dr[1].ToString()}',{Qty},'{PLACECODE}')";
result = ora.ExecuteNonQuery(insql);
if (result > 0)
{
this.Invoke(new Action(() =>
{
richTextBox1.AppendText("第" + row.ToString() + "行记录保存至数据库成功" + "\r\n");
}));
}
else
{
this.Invoke(new Action(() =>
{
richTextBox1.AppendText("第" + row.ToString() + "行记录保存至数据库失败" + "\r\n");
}));
}
i++;
}
dr = dt.NewRow();
dr[0] = "";
dr[1] = "";
dr[2] = "合计";
dr[3] = totalnum;
dr[4] = "";
dt.Rows.Add(dr);
ExcelRS.Visible = false;
ExcelRS.DisplayAlerts = false;
RSbook.Save();
this.Invoke(new Action(() =>
{
dataGridView2.DataSource = dt;
}));
MessageBox.Show("成功导入记录:" + i.ToString() + "条!");
}
catch (Exception er)
{
MessageBox.Show(er.ToString());
}
finally
{
ExcelRS.Quit();
IntPtr t = new IntPtr(ExcelRS.Hwnd);
int k = 0;
GetWindowThreadProcessId(t, out k);
System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k);
p.Kill();
this.Invoke(new Action(() =>
{
this.button5.Enabled = true;
}));
}
}
}
private void saveFileDialog4_FileOk(object sender, CancelEventArgs e)
{
if (saveFileDialog4.FileName != "")
{
string path = System.Windows.Forms.Application.StartupPath + "\\temp.xls";
if (!File.Exists(path))
{
MessageBox.Show("模板文件不存在！");
return;
}
string filePath = saveFileDialog4.FileName;
File.Copy(path, filePath, true);
if (listView1.Items.Count == 0)
{
MessageBox.Show("数据不存在！");
return;
}
try
{
object missing = Type.Missing;
Microsoft.Office.Interop.Excel.Application ExcelRS;
Microsoft.Office.Interop.Excel.Workbook RSbook;
Microsoft.Office.Interop.Excel.Worksheet RSsheet;
ExcelRS = null;
//实例化ExcelRS对象
ExcelRS = new Microsoft.Office.Interop.Excel.Application();
//打开目标文件filePath
RSbook = ExcelRS.Workbooks.Open(filePath, missing, missing, missing, missing, missing,
missing, missing, missing, missing, missing, missing, missing, missing, missing);
//设置第一个工作溥
RSsheet = (Microsoft.Office.Interop.Excel.Worksheet)RSbook.Sheets.get_Item(1);
//激活当前工作溥
RSsheet.Activate();
List <TakeCode> list = takeBll.GetList().ToList();
for (int i = 0; i < list.Count; i++)
{
TakeCode takeinfo = (TakeCode)list[i];
RSsheet.Cells[i + 4, 1] = (i + 1).ToString();
RSsheet.Cells[i + 4, 3] = takeinfo.ItemName;
RSsheet.Cells[i + 4, 2] = takeinfo.BrandName;
RSsheet.Cells[i + 4, 4] = takeinfo.CodeBard;
RSsheet.Cells[i + 4, 5] = takeinfo.TV1;
RSsheet.Cells[i + 4, 6] = takeinfo.TV2;
RSsheet.Cells[i + 4, 7] = takeinfo.TV3;
}
//保存目标文件
RSbook.Save();
//设置DisplayAlerts
ExcelRS.DisplayAlerts = false;
ExcelRS.Visible = true;
//ExcelRS.DisplayAlerts = true;
//释放对象
RSsheet = null;
RSbook = null;
ExcelRS = null;
}
catch (Exception er)
{
MessageBox.Show(er.ToString());
}
}
}
public void readexcel()
{
if (openFileDialog1.FileName != "")
{
this.Invoke(new Action(() =>
{
this.lblprocessmsg.Text = "开始导入数据...";
this.button1.Enabled = false;
}));
object missing = Type.Missing;
Microsoft.Office.Interop.Excel.Application ExcelRS;
Microsoft.Office.Interop.Excel.Workbook RSbook;
Microsoft.Office.Interop.Excel.Worksheet RSsheet;
// ExcelRS = null;
//实例化ExcelRS对象
ExcelRS = new Microsoft.Office.Interop.Excel.Application();
//打开目标文件filePath
RSbook = ExcelRS.Workbooks.Open(openFileDialog1.FileName, missing, missing, missing, missing, missing,
missing, missing, missing, missing, missing, missing, missing, missing, missing);
//设置第一个工作溥
RSsheet = (Microsoft.Office.Interop.Excel.Worksheet)RSbook.Sheets.get_Item(1);
//激活当前工作溥
RSsheet.Activate();
StringBuilder errmsg = new StringBuilder();
// object missing = Type.Missing;
errmsg.Append("错误信息:");
try
{
int col = 0;
string CodeBard, ItemName, ItemCode;
#region 读取工作薄内容
for (int row = int.Parse(textBox1.Text); row < int.Parse(textBox2.Text) + 1; row++)
{
this.Invoke(new Action(() =>
{
this.lblprocessmsg.Text = "读取第" + row.ToString() + "条记录中...";
this.button1.Enabled = false;
this.button3.Enabled = false;
}));
CodeBard = ((Microsoft.Office.Interop.Excel.Range)RSsheet.Cells[row, int.Parse(textBox3.Text)]).Text;
CodeBard = CodeBard.Replace('.', ' ').Trim();
ItemName = ((Microsoft.Office.Interop.Excel.Range)RSsheet.Cells[row, int.Parse(textBox4.Text)]).Text;
if (checkbar(CodeBard))
{
// if (checkbar_forproduct(CodeBard,ItemName)<3)
// { //我司存在的条码标为红色
((Microsoft.Office.Interop.Excel.Range)RSsheet.Cells[row, int.Parse(textBox3.Text)]).Font.Color = ColorTranslator.ToOle(Color.Red);
}
else
{
List <NoexitsBar> nblist = nbBll.GetList().Where(n => n.CodeBard.Equals(CodeBard)).ToList();
if (nblist.Count > 0)
{
//我司不存在但已经登记的条码标为蓝色
((Microsoft.Office.Interop.Excel.Range)RSsheet.Cells[row, int.Parse(textBox3.Text)]).Font.Color = ColorTranslator.ToOle(Color.Blue);
}
else
{
NoexitsBar nb = new NoexitsBar();
nb.ItemName = ItemName;
nb.CodeBard = CodeBard;
if (nbBll.Add(nb))
{
this.Invoke(new Action(() =>
{
this.lblprocessmsg.Text = "插入" + CodeBard + "记录成功";
this.button1.Enabled = false;
this.button3.Enabled = false;
}));
col++;
}
}
// ((Microsoft.Office.Interop.Excel.Range)RSsheet.Cells[row, 2]).Font.Color = ColorTranslator.ToOle(Color.Red);
}
}
// DataTable dt= oleGetDataSet("select BARCODE,SPDM,GG1DM,GG2DM,N_SL,SL,MARK FROM WPHYCD");
ExcelRS.Visible = false;
ExcelRS.DisplayAlerts = false;
RSbook.Save();
MessageBox.Show("成功导入记录:" + col.ToString() + "条,未成功导入的记录将以红色标记，请打开原文件查看!");
}
catch (Exception er)
{
MessageBox.Show(er.ToString());
}
finally
{
ExcelRS.Quit();
IntPtr t = new IntPtr(ExcelRS.Hwnd);
int k = 0;
GetWindowThreadProcessId(t, out k);
System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k);
p.Kill();
this.Invoke(new Action(() =>
{
this.lblprocessmsg.Text = "导入完成";
this.button1.Enabled = true;
this.button3.Enabled = true;
}));
}
}
}
public void readexcel()
{
if (openFileDialog1.FileName != "")
{
this.Invoke(new Action(() =>
{
this.lblprocessmsg.Text = "开始导入数据...";
this.button3.Enabled = false;
}));
Sqlexec sqlex = new Sqlexec(getelement("connstr"));
object missing = Type.Missing;
Microsoft.Office.Interop.Excel.Application ExcelRS;
Microsoft.Office.Interop.Excel.Workbook RSbook;
Microsoft.Office.Interop.Excel.Worksheet RSsheet;
// ExcelRS = null;
//实例化ExcelRS对象
ExcelRS = new Microsoft.Office.Interop.Excel.Application();
//打开目标文件filePath
RSbook = ExcelRS.Workbooks.Open(openFileDialog1.FileName, missing, missing, missing, missing, missing,
missing, missing, missing, missing, missing, missing, missing, missing, missing);
//设置第一个工作溥
RSsheet = (Microsoft.Office.Interop.Excel.Worksheet)RSbook.Sheets.get_Item(1);
//激活当前工作溥
RSsheet.Activate();
StringBuilder errmsg = new StringBuilder();
// object missing = Type.Missing;
errmsg.Append("错误信息:");
try
{
DataTable dt = new DataTable();
dt.Columns.Add(new DataColumn("U_clientid", typeof(System.String)));
dt.Columns.Add(new DataColumn("U_clientname", typeof(System.String)));
dt.Columns.Add(new DataColumn("U_userid", typeof(System.String)));
dt.Columns.Add(new DataColumn("U_name", typeof(System.String)));
int col = 0;
string U_clientid, U_clientName, U_userid;
#region 读取工作薄内容
for (int row = int.Parse(textBox1.Text); row < int.Parse(textBox2.Text) + 1; row++)
{
this.Invoke(new Action(() =>
{
this.lblprocessmsg.Text = "读取第" + row.ToString() + "行记录中...";
this.button3.Enabled = false;
}));
U_clientName = ((Microsoft.Office.Interop.Excel.Range)RSsheet.Cells[row, int.Parse(textBox3.Text)]).Text;
U_userid = ((Microsoft.Office.Interop.Excel.Range)RSsheet.Cells[row, int.Parse(textBox4.Text)]).Text;
DataRow dr = dt.NewRow();
dr[0] = getClientid(U_clientName);
dr[1] = U_clientName;
dr[2] = U_userid;
dr[3] = getUserName(U_userid);
dt.Rows.Add(dr);
this.Invoke(new Action(() =>
{
this.lblprocessmsg.Text = "读取" + U_clientName + "记录成功";
this.button3.Enabled = false;
}));
col++;
// ((Microsoft.Office.Interop.Excel.Range)RSsheet.Cells[row, 2]).Font.Color = ColorTranslator.ToOle(Color.Red);
}
// DataTable dt= oleGetDataSet("select BARCODE,SPDM,GG1DM,GG2DM,N_SL,SL,MARK FROM WPHYCD");
ExcelRS.Visible = false;
ExcelRS.DisplayAlerts = false;
RSbook.Save();
this.Invoke(new Action(() =>
{
dataGridView6.DataSource = dt;
}));
MessageBox.Show("成功导入记录:" + col.ToString() + "条!");
}
catch (Exception er)
{
MessageBox.Show(er.ToString());
}
finally
{
ExcelRS.Quit();
IntPtr t = new IntPtr(ExcelRS.Hwnd);
int k = 0;
GetWindowThreadProcessId(t, out k);
System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k);
p.Kill();
this.Invoke(new Action(() =>
{
this.lblprocessmsg.Text = "导入完成";
this.button3.Enabled = true;
}));
}
}
}