void OutputExcel(CStorageNote StorageNote) { DateTime dtNow = DateTime.Now; string sNewFile = string.Format("{0}{1}{2}{3}{4}{5}.xls", dtNow.Year, dtNow.Month, dtNow.Day, dtNow.Hour, dtNow.Minute, dtNow.Second); string sDir = Server.MapPath("StorageNoteOutput.aspx"); sDir = sDir.Substring(0, sDir.Length - "StorageNoteOutput.aspx".Length); string sDir1 = sDir + "output/"; if (!Directory.Exists(sDir1)) { Directory.CreateDirectory(sDir1); } string sFileSrc = sDir + "templet/StorageNote.xls"; string sFileDst = sDir1 + sNewFile; if (File.Exists(sFileDst)) { File.Delete(sFileDst); } File.Copy(sFileSrc, sFileDst); string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Extended Properties='Excel 8.0;HDR=YES;IMEX=0';" + "data source=" + sFileDst; OleDbConnection conn = new OleDbConnection(connStr); try { conn.Open(); } catch { Response.Write("导出失败!"); return; } //主表 string sIns = string.Format("insert into [Sheet1$] ([编号],[入库日期],[公司],[经办人],[备注]) values (?,?,?,?,?)"); CCompany Company = (CCompany)Global.GetCtx(Session["TopCompany"].ToString()).CompanyMgr.Find(m_User.B_Company_id); OleDbCommand cmd = new OleDbCommand(sIns, conn); cmd.Parameters.Add(new OleDbParameter("编号", StorageNote.Code)); cmd.Parameters.Add(new OleDbParameter("入库日期", StorageNote.StoreDate)); cmd.Parameters.Add(new OleDbParameter("公司", Company.Name)); cmd.Parameters.Add(new OleDbParameter("经办人", StorageNote.Attn)); cmd.Parameters.Add(new OleDbParameter("备注", StorageNote.Remarks)); try { cmd.ExecuteNonQuery(); } catch { conn.Close(); Response.Write("导出失败!"); return; } //从表 List <CBaseObject> lstObj = StorageNote.StorageNoteDetailMgr.GetList(); foreach (CBaseObject obj in lstObj) { CStorageNoteDetail StorageNoteDetail = (CStorageNoteDetail)obj; string sIns2 = string.Format("insert into [Sheet2$] ([商品编号],[规格型号],[数量]) values (?,?,?)"); CProduct Product = (CProduct)Global.GetStore().ProductMgr.Find(StorageNoteDetail.SP_Product_id); if (Product == null) { continue; } OleDbCommand cmd2 = new OleDbCommand(sIns2, conn); cmd2.Parameters.Add(new OleDbParameter("商品编号", Product.Code)); cmd2.Parameters.Add(new OleDbParameter("规格型号", StorageNoteDetail.Specification)); cmd2.Parameters.Add(new OleDbParameter("数量", StorageNoteDetail.Num)); try { cmd2.ExecuteNonQuery(); } catch { conn.Close(); Response.Write("导出失败!"); return; } } conn.Close(); Response.Write("OutXls.aspx?file=" + sNewFile); }
bool ImportExcelRecord(string connStr) { // 主表 string sql = "SELECT * FROM [Sheet1$]"; DataSet ds = new DataSet(); OleDbDataAdapter da = new OleDbDataAdapter(sql, connStr); da.Fill(ds); // 填充DataSet DataTable dt = ds.Tables[0]; DataRow r = dt.Rows[0]; string sFieldVal1 = r["编号"].ToString().Trim(); string sFieldVal2 = r["入库日期"].ToString().Trim(); string sFieldVal3 = r["公司"].ToString().Trim(); string sFieldVal4 = r["经办人"].ToString().Trim(); string sFieldVal5 = r["备注"].ToString().Trim(); DateTime dtimeStoreDate = DateTime.Now; if (sFieldVal2 != "") { try{ dtimeStoreDate = Convert.ToDateTime(sFieldVal2); } catch {} } Guid guidCompanyId = m_User.B_Company_id; if (sFieldVal3 != "") { CCompany Company = Global.GetCtx(Session["TopCompany"].ToString()).CompanyMgr.FindByName(sFieldVal3); if (Company != null) { guidCompanyId = Company.Id; } } CStorageNote obj = new CStorageNote(); obj.Ctx = Global.GetCtx(Session["TopCompany"].ToString()); obj.Code = sFieldVal1; obj.StoreDate = dtimeStoreDate; obj.B_Company_id = guidCompanyId; obj.Attn = sFieldVal4; obj.Remarks = sFieldVal5; Global.GetInvoicing().StorageNoteMgr.AddNew(obj); //明细 string sql2 = "SELECT * FROM [Sheet2$]"; DataSet ds2 = new DataSet(); OleDbDataAdapter da2 = new OleDbDataAdapter(sql2, connStr); da2.Fill(ds2); // 填充DataSet DataTable dt2 = ds2.Tables[0]; foreach (DataRow r2 in dt2.Rows) { string sFieldVal21 = r2["商品编号"].ToString().Trim(); string sFieldVal22 = r2["规格型号"].ToString().Trim(); string sFieldVal23 = r2["数量"].ToString().Trim(); CProduct Product = Global.GetStore().ProductMgr.FindByCode(sFieldVal21); if (Product == null) { continue; } CStorageNoteDetail objDetail = new CStorageNoteDetail(); objDetail.Ctx = Global.GetCtx(Session["TopCompany"].ToString()); objDetail.SP_Product_id = Product.Id; objDetail.Specification = sFieldVal22; objDetail.Num = Convert.ToDouble(sFieldVal23); objDetail.KC_StorageNote_id = obj.Id; obj.StorageNoteDetailMgr.AddNew(objDetail); } if (!Global.GetInvoicing().StorageNoteMgr.Save(true)) { return(false); } return(true); }