《C#复制EXCEL并导入数据库.docx》由会员分享,可在线阅读,更多相关《C#复制EXCEL并导入数据库.docx(5页珍藏版)》请在金锄头文库上搜索。
1、using Microsoft.Office.Interop.Excel;using System.Reflection;using Excel;private void UpdateCE() /创建Application对象 Excel.Application xApp = new Excel.Application(); xApp.Visible = true; /得到WorkBook对象, 可以用两种方式之一: 下面的是打开已有的文件 Excel.Workbook xBook = xApp.Workbooks._Open(F:new_itemPartInfo-SendToCE+date0
2、+.xls, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); /xBook=xApp.Workbooks.Add(Missing.Value); /新建文件的代码 /指定要操作的Sheet,两种方式: Excel.Worksheet xSheet = (Excel.Worksheet)
3、xBook.Sheets1; /Excel.Worksheet xSheet=(Excel.Worksheet)xApp.ActiveSheet; /读取数据,通过Range对象 Excel.Range rng1 = xSheet.get_Range(A1, Type.Missing); Console.WriteLine(rng1.Value2); /读取,通过Range对象,但使用不同的接口得到Range Excel.Range rng2 = (Excel.Range)xSheet.Cells3, 1; Console.WriteLine(rng2.Value2); /写入数据 /Exce
4、l.Range rng3 = xSheet.get_Range(C6, Missing.Value); /rng3.Value2 = Hello; /rng3.Interior.ColorIndex = 6; /设置Range的背景色 /保存方式一:保存WorkBook xBook.SaveAs(F:new_itemPartInfo + date0 + .xls, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlNoChange, Miss
5、ing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); /保存方式二:保存WorkSheet /xSheet.SaveAs(F:new_itemPartInfo+date0+.xls, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); /保存方式三 /xBook.Save(); Syste
6、m.Runtime.InteropServices.Marshal.ReleaseComObject(xSheet); xSheet = null; xBook.Close(false, Missing.Value, Missing.Value); System.Runtime.InteropServices.Marshal.ReleaseComObject(xBook); xBook = null; xApp.Quit(); /这一句是非常重要的,否则Excel对象不能从内存中退出 System.Runtime.InteropServices.Marshal.ReleaseComObject
7、(xApp); xApp = null; /DataSet ds1; try DataSet ds = ExcelOperate.ExceleDs(F:/new_item/PartInfo + date0 + .xls); /DataSet ds = ExcelOperate.ExceleDs(F:/new_item/test.xls); DataRow dr = ds.Tables0.Select(); int rowsum = ds.Tables0.Rows.Count; if (rowsum = 0) /Response.Write(alert(空表無數據!); else for (in
8、t i = 0; i dr.Length; i+) string lh12 = dri0.ToString(); string lhdes = dri2.ToString(); if (lhdes.Contains() lhdes = lhdes.Replace(,); string jdzk = dri4.ToString(); string GAstate = dri5.ToString(); string TXstate = dri6.ToString(); string Isstop = dri7.ToString(); string qdate = dri8.ToString();
9、string zt = dri9.ToString(); string q1 = dri10.ToString(); if (q1 = 重大品異 | q1 = 過期零件) zt = Q; string sql = null; if (qdate = ) sql = update ljcr set des= + lhdes + ,lhdes= + lhdes + ,jdzk= + jdzk + ,GAstate= + GAstate + ,TXstate= + TXstate + ,Isstop= + Isstop + ,zt= + zt + where 12lh= + lh12 + ; els
10、e string qdate2 = Convert.ToDateTime(qdate).ToShortDateString(); sql = update ljcr set des= + lhdes + ,lhdes= + lhdes + ,jdzk= + jdzk + ,GAstate= + GAstate + ,TXstate= + TXstate + ,Isstop= + Isstop + ,qdate= + qdate2 + ,zt= + zt + where 12lh= + lh12 + ; MySqlHelper.ExecuteNonQuery(constr, sql); /Response.Write(alert(成功導入 + n + 筆!); WriteLog(導入成功!); /ds.Dispose(); catch (Exception ex) WriteLog(ex.Message); using System;using System.Web;using