protected void btOk_Click(object sender, EventArgs e) { string sOrderId = hidOrderId.Value; string sNoteCode = txtNoteCode.Value; if (Global.GetInvoicing().PurchaseNoteMgr.FindByCode(sNoteCode) != null) { Response.Write(string.Format("<script>parent.$.ligerDialog.warn('{0} 编号重复!');</script>", sNoteCode)); return; } CPurchaseOrder PurchaseOrder = (CPurchaseOrder)Global.GetInvoicing().PurchaseOrderMgr.Find(new Guid(sOrderId)); CPurchaseNote PurchaseNote = new CPurchaseNote(); PurchaseNote.Ctx = Global.GetCtx(Session["TopCompany"].ToString()); PurchaseNote.Code = sNoteCode; PurchaseNote.PurchaseDate = PurchaseOrder.PurchaseDate; PurchaseNote.Supplier = PurchaseOrder.Supplier; PurchaseNote.Contacts = PurchaseOrder.Contacts; PurchaseNote.Tel = PurchaseOrder.Tel; PurchaseNote.Addr = PurchaseOrder.Addr; PurchaseNote.OtherCharge = PurchaseOrder.OtherCharge; PurchaseNote.Discount = PurchaseOrder.Discount; PurchaseNote.B_Company_id = PurchaseOrder.B_Company_id; PurchaseNote.Attn = PurchaseOrder.Attn; PurchaseNote.Remarks = PurchaseOrder.Remarks; Global.GetInvoicing().PurchaseNoteMgr.AddNew(PurchaseNote); if (!Global.GetInvoicing().PurchaseNoteMgr.Save(true)) { Response.Write(string.Format("<script>parent.$.ligerDialog.warn('生成失败!');</script>")); return; } Response.Write(string.Format("<script>parent.$.ligerDialog.success('生成成功!', function(type) {{ parent.$.ligerDialog.close(); }});</script>")); }
void PostData() { string id = Request["id"]; if (string.IsNullOrEmpty(id)) { Response.Write("请选择一项!"); return; } CPurchaseNote PurchaseNote = (CPurchaseNote)Global.GetInvoicing().PurchaseNoteMgr.Find(new Guid(id)); if (PurchaseNote == null) { Response.Write("请选择一项!"); return; } OutputExcel(PurchaseNote); }
void OutputExcel(CPurchaseNote PurchaseNote) { 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("PurchaseNoteOutput.aspx"); sDir = sDir.Substring(0, sDir.Length - "PurchaseNoteOutput.aspx".Length); string sDir1 = sDir + "output/"; if (!Directory.Exists(sDir1)) { Directory.CreateDirectory(sDir1); } string sFileSrc = sDir + "templet/PurchaseNote.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("编号", PurchaseNote.Code)); cmd.Parameters.Add(new OleDbParameter("采购日期", PurchaseNote.PurchaseDate)); cmd.Parameters.Add(new OleDbParameter("供应商", PurchaseNote.Supplier)); cmd.Parameters.Add(new OleDbParameter("联系人", PurchaseNote.Contacts)); cmd.Parameters.Add(new OleDbParameter("联系电话", PurchaseNote.Tel)); cmd.Parameters.Add(new OleDbParameter("进货地址", PurchaseNote.Addr)); cmd.Parameters.Add(new OleDbParameter("其他费用", PurchaseNote.OtherCharge)); cmd.Parameters.Add(new OleDbParameter("优惠", PurchaseNote.Discount)); cmd.Parameters.Add(new OleDbParameter("公司", Company.Name)); cmd.Parameters.Add(new OleDbParameter("经办人", PurchaseNote.Attn)); cmd.Parameters.Add(new OleDbParameter("备注", PurchaseNote.Remarks)); try { cmd.ExecuteNonQuery(); } catch { conn.Close(); Response.Write("导出失败!"); return; } //从表 List <CBaseObject> lstObj = PurchaseNote.PurchaseNoteDetailMgr.GetList(); foreach (CBaseObject obj in lstObj) { CPurchaseNoteDetail PurchaseNoteDetail = (CPurchaseNoteDetail)obj; string sIns2 = string.Format("insert into [Sheet2$] ([商品编号],[规格型号],[数量],[单价]) values (?,?,?,?)"); CProduct Product = (CProduct)Global.GetStore().ProductMgr.Find(PurchaseNoteDetail.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("规格型号", PurchaseNoteDetail.Specification)); cmd2.Parameters.Add(new OleDbParameter("数量", PurchaseNoteDetail.Num)); cmd2.Parameters.Add(new OleDbParameter("单价", PurchaseNoteDetail.Price)); 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(); string sFieldVal6 = r["进货地址"].ToString().Trim(); string sFieldVal7 = r["其他费用"].ToString().Trim(); string sFieldVal8 = r["优惠"].ToString().Trim(); string sFieldVal9 = r["公司"].ToString().Trim(); string sFieldVal10 = r["经办人"].ToString().Trim(); string sFieldVal11 = r["备注"].ToString().Trim(); DateTime dtimePurchaseDate = DateTime.Now; if (sFieldVal2 != "") { try { dtimePurchaseDate = Convert.ToDateTime(sFieldVal2); } catch {} } Guid guidCompanyId = m_User.B_Company_id; if (sFieldVal9 != "") { CCompany Company = Global.GetCtx(Session["TopCompany"].ToString()).CompanyMgr.FindByName(sFieldVal9); if (Company != null) { guidCompanyId = Company.Id; } } CPurchaseNote obj = new CPurchaseNote(); obj.Ctx = Global.GetCtx(Session["TopCompany"].ToString()); obj.Code = sFieldVal1; obj.PurchaseDate = dtimePurchaseDate; obj.Supplier = sFieldVal3; obj.Contacts = sFieldVal4; obj.Tel = sFieldVal5; obj.Addr = sFieldVal6; obj.OtherCharge = Convert.ToDouble(sFieldVal7); obj.Discount = Convert.ToDouble(sFieldVal8); obj.B_Company_id = guidCompanyId; obj.Attn = sFieldVal10; obj.Remarks = sFieldVal11; Global.GetInvoicing().PurchaseNoteMgr.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(); string sFieldVal24 = r2["单价"].ToString().Trim(); CProduct Product = Global.GetStore().ProductMgr.FindByCode(sFieldVal21); if (Product == null) { continue; } CPurchaseNoteDetail objDetail = new CPurchaseNoteDetail(); objDetail.Ctx = Global.GetCtx(Session["TopCompany"].ToString()); objDetail.SP_Product_id = Product.Id; objDetail.Specification = sFieldVal22; objDetail.Num = Convert.ToDouble(sFieldVal23); objDetail.Price = Convert.ToDouble(sFieldVal24); objDetail.CG_PurchaseNote_id = obj.Id; obj.PurchaseNoteDetailMgr.AddNew(objDetail); } if (!Global.GetInvoicing().PurchaseNoteMgr.Save(true)) { return(false); } return(true); }