void OutputExcel(CSaleNote SaleNote)
{
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("SaleNoteOutput.aspx");
sDir = sDir.Substring(0, sDir.Length - "SaleNoteOutput.aspx".Length);
string sDir1 = sDir + "output/";
if (!Directory.Exists(sDir1))
{
Directory.CreateDirectory(sDir1);
}
string sFileSrc = sDir + "templet/SaleNote.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("编号", SaleNote.Code));
cmd.Parameters.Add(new OleDbParameter("销售日期", SaleNote.SaleDate));
cmd.Parameters.Add(new OleDbParameter("客户名称", SaleNote.Customer));
cmd.Parameters.Add(new OleDbParameter("联系人", SaleNote.Contacts));
cmd.Parameters.Add(new OleDbParameter("联系电话", SaleNote.Tel));
cmd.Parameters.Add(new OleDbParameter("送货地址", SaleNote.Addr));
cmd.Parameters.Add(new OleDbParameter("其他费用", SaleNote.OtherCharge));
cmd.Parameters.Add(new OleDbParameter("运费", SaleNote.ShipCharge));
cmd.Parameters.Add(new OleDbParameter("优惠", SaleNote.Discount));
cmd.Parameters.Add(new OleDbParameter("公司", Company.Name));
cmd.Parameters.Add(new OleDbParameter("经办人", SaleNote.Attn));
cmd.Parameters.Add(new OleDbParameter("备注", SaleNote.Remarks));
try { cmd.ExecuteNonQuery(); }
catch
{
conn.Close();
Response.Write("导出失败！");
return;
}
//从表
List <CBaseObject> lstObj = SaleNote.SaleNoteDetailMgr.GetList();
foreach (CBaseObject obj in lstObj)
{
CSaleNoteDetail SaleNoteDetail = (CSaleNoteDetail)obj;
string sIns2 = string.Format("insert into [Sheet2$] ([商品编号],[规格型号],[数量],[单价],[折扣],[赠送商品],[换货]) values (?,?,?,?,?,?,?)");
CProduct Product = (CProduct)Global.GetStore().ProductMgr.Find(SaleNoteDetail.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("规格型号", SaleNoteDetail.Specification));
cmd2.Parameters.Add(new OleDbParameter("数量", SaleNoteDetail.Num));
cmd2.Parameters.Add(new OleDbParameter("单价", SaleNoteDetail.Price));
cmd2.Parameters.Add(new OleDbParameter("折扣", SaleNoteDetail.Discount));
cmd2.Parameters.Add(new OleDbParameter("赠送商品", SaleNoteDetail.IsGive?"是":"否"));
cmd2.Parameters.Add(new OleDbParameter("换货", SaleNoteDetail.IsExchange ? "是" : "否"));
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();
string sFieldVal12 = r["备注"].ToString().Trim();
DateTime dtimeSaleDate = DateTime.Now;
if (sFieldVal2 != "")
{
try { dtimeSaleDate = Convert.ToDateTime(sFieldVal2); }
catch {}
}
Guid guidCompanyId = m_User.B_Company_id;
if (sFieldVal10 != "")
{
CCompany Company = Global.GetCtx(Session["TopCompany"].ToString()).CompanyMgr.FindByName(sFieldVal10);
if (Company != null)
{
guidCompanyId = Company.Id;
}
}
CSaleNote obj = new CSaleNote();
obj.Ctx = Global.GetCtx(Session["TopCompany"].ToString());
obj.Code = sFieldVal1;
obj.SaleDate = dtimeSaleDate;
obj.Customer = sFieldVal3;
obj.Contacts = sFieldVal4;
obj.Tel = sFieldVal5;
obj.Addr = sFieldVal6;
obj.OtherCharge = Convert.ToDouble(sFieldVal7);
obj.ShipCharge = Convert.ToDouble(sFieldVal8);
obj.Discount = Convert.ToDouble(sFieldVal9);
obj.B_Company_id = guidCompanyId;
obj.Attn = sFieldVal11;
obj.Remarks = sFieldVal12;
Global.GetInvoicing().SaleNoteMgr.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();
string sFieldVal25 = r2["折扣"].ToString().Trim();
string sFieldVal26 = r2["赠送商品"].ToString().Trim();
string sFieldVal27 = r2["换货"].ToString().Trim();
CProduct Product = Global.GetStore().ProductMgr.FindByCode(sFieldVal21);
if (Product == null)
{
continue;
}
CSaleNoteDetail objDetail = new CSaleNoteDetail();
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);
if (sFieldVal25 != "")
{
objDetail.Discount = Convert.ToDouble(sFieldVal25);
}
objDetail.IsGive = (sFieldVal26 != "是") ? true : false;
objDetail.IsExchange = (sFieldVal27 != "是") ? true : false;
objDetail.XS_SaleNote_id = obj.Id;
obj.SaleNoteDetailMgr.AddNew(objDetail);
}
if (!Global.GetInvoicing().SaleNoteMgr.Save(true))
{
return(false);
}
return(true);
}
