private void ExportToExcel(string mdbFile, string excelFileName) { if (File.Exists(excelFileName)) { FileInfo FinfoReport = new FileInfo(excelFileName); FinfoReport.Delete(); } string exportExcel = ""; try { if (_Is_CX) { exportExcel = " SELECT *"; exportExcel += " into [Excel 8.0;database=" + excelFileName + "].[VTable] FROM [VTable] WHERE (0=0) "; } else { exportExcel = @" SELECT SEQUENCE_NUM,ORDER_NO,OUT_GOOD,OUT_GOODTIME, RLENS_SIZE,RLENS_TYPE,RSPH,RCYL,RAXIS,RADDS,RQTY, LLENS_SIZE,LLENS_TYPE,LSPH,LCYL,LAXIS,LADDS,LQTY, TINT_CODE,TINT_CONTENT,UV,HARD,COATING,BR,MIR,RCP,LCP,RBASE,LBASE,RKNIFE_SIDER,LKNIFE_SIDER,RDECENTER,LDECENTER, RPRISM,LPRISM,INPUT_DATE,OUTPUT_DATE,PRINTED,PRINT_BAG,REMARK,LENS_MATERIAL,BarCode,SPECIAL_CODE,AURO" ; exportExcel += " into [Excel 8.0;database=" + excelFileName + "].[VTable] FROM [VTable] WHERE (0=0) "; } string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + mdbFile; DbHelperOledb.ExecuteSql(exportExcel, connectionString); } catch { throw; } }
private void CreateMdb(string mdb) { string sSql; Catalog catalog = new Catalog(); try { string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + mdb; catalog.Create(connectionString); if (null != catalog.ActiveConnection) { System.Runtime.InteropServices.Marshal.FinalReleaseComObject(catalog.ActiveConnection); } System.Runtime.InteropServices.Marshal.FinalReleaseComObject(catalog); sSql = @"CREATE TABLE [VTable]"; sSql += @"([SEQUENCE_NUM] char(255) , [ORDER_NO] char(255) NOT NULL,"; sSql += @"[OUT_GOOD] char(50),[OUT_GOODTIME] char(50), [RLENS_SIZE] int, [RLENS_TYPE] char(30),"; sSql += @"[RSPH] int, [RCYL] int, [RAXIS] char(50) , [RADDS] int,"; sSql += @"[RQTY] decimal(18, 6) NULL, [LLENS_SIZE] int NULL, [LLENS_TYPE] varchar(30) NULL,"; sSql += @"[LSPH] int NULL, [LCYL] int NULL, [LAXIS] nvarchar(50) NULL, [LADDS] int NULL,"; sSql += @"[LQTY] decimal(18, 6) NULL, [TINT_CODE] char(10) NULL, [TINT_CONTENT] char(10) NULL,"; sSql += @"[UV] bit NULL, [HARD] bit NULL, [COATING] nvarchar(50) NULL, [BR] nchar(10) NULL,"; sSql += @"[MIR] char(10) NULL, [RCP] nvarchar(50) NULL, [LCP] nvarchar(50) NULL, [RBASE] nvarchar(50) NULL,"; sSql += @"[LBASE] nvarchar(50) NULL, [RKNIFE_SIDER] bit NULL, [LKNIFE_SIDER] bit NULL,"; sSql += @"[RDECENTER] nvarchar(50) NULL, [LDECENTER] nvarchar(50) NULL, [RPRISM] nvarchar(50) NULL,"; sSql += @"[LPRISM] nvarchar(50) NULL, [INPUT_DATE] datetime NOT NULL, [OUTPUT_DATE] datetime NULL,"; sSql += @"[PRINTED] bit NULL,[PRINT_BAG] bit NULL, [REMARK] varchar(255) NULL,"; sSql += @"[LENS_MATERIAL] char(10) NULL, [BarCode] char(30) NULL,"; sSql += @"[PD] char(30) NULL, [POLISH] char(30) NULL,"; sSql += @"[AURO] char(30) NULL, [EDGE] char(30) NULL,"; sSql += @"[SUPER] char(30) NULL, [SPECIAL] char(30) NULL, [SPECIAL_CODE] char(30) NULL"; sSql += @") "; DbHelperOledb.ExecuteSql(sSql, connectionString); } catch { throw; } }
private void PrepareExportToExcel(string dbCode, int lgIndex, string mdbFile, string excelFileName, string totleBillcode) { string sSql = "delete from [VTable]"; string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + mdbFile + ";Persist Security Info=True"; DbHelperOledb.ExecuteSql(sSql, connectionString); sSql = @" select S1.SN as SEQUENCE_NUM,S1.BCode as ORDER_NO,DAY(S1.BDate) as OUT_GOOD,'' as OUT_GOODTIME, S1.DiaR as RLENS_SIZE,S1.LensCodeRR as RLENS_TYPE,SPHR as RSPH,CYLR as RCYL,S1.AxisR as RAXIS,X_ADDR as RADDS, QtyR as RQTY, S1.DiaL as LLENS_SIZE,S1.LensCodeRL as LLENS_TYPE,SPHL as LSPH,CYLL as LCYL,S1.AxisL as LAXIS,X_ADDL as LADDS,QtyL as LQTY, S1.RSName as TINT_CODE,S1.RS as TINT_CONTENT,S1.UV,S1.JY as HARD,S1.JS as COATING,S1.CS as BR,S1.SY as MIR, S1.CTR as RCP,S1.CTL as LCP,S1.BASER as RBASE,S1.BASEL as LBASE,S1.DBR as RKNIFE_SIDER,S1.DBL as LKNIFE_SIDER, rtrim(ltrim(S1.D1R))+rtrim(ltrim(S1.D2R))+rtrim(ltrim(S1.D3R))+rtrim(ltrim(S1.D4R)) as RDECENTER, rtrim(ltrim(S1.D1L))+rtrim(ltrim(S1.D2L))+rtrim(ltrim(S1.D3L))+rtrim(ltrim(S1.D4L)) as LDECENTER, rtrim(ltrim(S1.P1R))+rtrim(ltrim(S1.P2R))+rtrim(ltrim(S1.P3R))+rtrim(ltrim(S1.P4R)) as RPRISM, rtrim(ltrim(S1.P1L))+rtrim(ltrim(S1.P2L))+rtrim(ltrim(S1.P3L))+rtrim(ltrim(S1.P4L)) as LPRISM, CONVERT(varchar(100), BDate , 23) as INPUT_DATE,CONVERT(varchar(100), GETDATE() , 23) OUTPUT_DATE,0 PRINTED,0 PRINT_BAG, '['+rtrim(ltrim(S1.BCode))+']'+';'+'[PD:'+PD+'];'+rtrim(ltrim(Notes)) as TBZM, isnull((select top 1 B1.Materials from B_Material_Lens B1 with (nolock) where B1.LensCode=S1.LensCodeRR),'') LENS_MATERIAL, isnull((select top 1 BCode+right(rtrim(ltrim(S1.OBCode)),5) from B_Material_Lens B1 with(nolock) where substring(BCode,4,12)=substring(S1.OBCode,1,4)),'') as Barcode, PD,S1.PG as POLISH,S1.CB as AURO,S1.ChB as EDGE,S1.KK as SUPER,S1.PH as SPECIAL,'' as SPECIAL_CODE from V_Sale_Order_SD S1 where S1.ID in (" + totleBillcode + @") order by S1.BCode"; DbHelperSQL db = new DbHelperSQL(dbCode); DataSet ds = db.Query(sSql); DataTable dt = ds.Tables[0]; foreach (DataRow dr in dt.Rows) { if (dr["RAXIS"].ToString() == "") { dr["RAXIS"] = "0"; } if (dr["LAXIS"].ToString() == "") { dr["LAXIS"] = "0"; } if (Convert.ToInt32(dr["RQTY"]) <= 0) { dr["RSPH"] = "0"; dr["RCYL"] = "0"; dr["RAXIS"] = "0"; } if (Convert.ToInt32(dr["LQTY"]) <= 0) { dr["LSPH"] = "0"; dr["LCYL"] = "0"; dr["LAXIS"] = "0"; } sSql = "Insert into [VTable] values ('" + dr["ORDER_NO"].ToString().Trim() + "','" + dr["SEQUENCE_NUM"].ToString().Trim() + _add + "','"; sSql += dr["OUT_GOOD"].ToString().Trim() + "','" + dr["OUT_GOODTIME"].ToString().Trim() + "'," + (dr["RLENS_SIZE"].ToString().Trim() == "" ? "0" : dr["RLENS_SIZE"].ToString().Trim()).Trim() + ",'"; sSql += dr["RLENS_TYPE"].ToString().Trim() + "'," + dr["RSPH"].ToString().Trim() + "," + dr["RCYL"].ToString().Trim() + ",'" + dr["RAXIS"].ToString().Trim() + "',"; sSql += dr["RADDS"].ToString().Trim() + "," + Convert.ToInt32(dr["RQTY"]) + "," + (dr["LLENS_SIZE"].ToString().Trim() == "" ? "0" : dr["LLENS_SIZE"].ToString().Trim()).Trim() + ",'" + dr["LLENS_TYPE"].ToString().Trim() + "',"; sSql += dr["LSPH"].ToString().Trim() + "," + dr["LCYL"].ToString().Trim() + ",'" + dr["LAXIS"].ToString().Trim() + "'," + dr["LADDS"].ToString().Trim() + ","; sSql += Convert.ToInt32(dr["LQTY"]) + ",'" + dr["TINT_CODE"].ToString().Trim() + "','" + dr["TINT_CONTENT"].ToString().Trim() + "'," + Convert.ToByte(dr["UV"]) + ","; sSql += Convert.ToByte(dr["HARD"]) + ",'" + dr["COATING"].ToString().Trim() + "','" + dr["BR"].ToString().Trim() + "','" + dr["MIR"].ToString().Trim() + "','"; sSql += dr["RCP"].ToString().Trim() + "','" + dr["LCP"].ToString().Trim() + "','" + dr["RBASE"].ToString().Trim() + "','" + dr["LBASE"].ToString().Trim() + "',"; sSql += Convert.ToByte(dr["RKNIFE_SIDER"]) + "," + Convert.ToByte(dr["LKNIFE_SIDER"]) + ",'" + dr["RDECENTER"].ToString().Trim() + "','" + dr["LDECENTER"].ToString().Trim() + "','"; sSql += dr["RPRISM"].ToString().Trim() + "','" + dr["LPRISM"].ToString().Trim() + "','" + dr["INPUT_DATE"].ToString().Trim() + "','" + dr["OUTPUT_DATE"].ToString().Trim() + "',"; sSql += Convert.ToByte(dr["PRINTED"]) + "," + Convert.ToByte(dr["PRINT_BAG"]) + ",'" + dr["TBZM"].ToString().Trim() + "','" + dr["LENS_MATERIAL"].ToString().Trim() + "','" + dr["Barcode"].ToString().Trim() + "','"; sSql += dr["PD"].ToString().Trim() + "','" + dr["POLISH"].ToString().Trim() + "','" + dr["AURO"].ToString().Trim() + "','" + dr["EDGE"].ToString().Trim() + "','" + dr["SUPER"].ToString().Trim() + "','"; sSql += dr["SPECIAL"].ToString().Trim() + "','')"; DbHelperOledb.ExecuteSql(sSql, connectionString); } this.ExportToExcel(mdbFile, excelFileName); }