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);
}
