protected void btnexport_Click(object sender, EventArgs e)
{
string yearmonth = ddlyear.Text.Trim() + "-" + ddlmonth.Text.Trim();
depID = Utils.IntSafeConvert(ddlDep.SelectedValue);
DataTable dt;
if (depID == 0)
{
dt = BGApplyReimburManager.GetApplyReimburAllExport(yearmonth);
}
else
{
dt = BGApplyReimburManager.GetApplyReimburSByDepID(depID, yearmonth);
}
if (dt != null && dt.Rows.Count > 0)
{
MemoryStream ms = ExcelRender.RenderToExcelSetHead(dt, "申请时间#报销单号#上报单位#经办人#金额#事由#申请单状态");
Response.ContentType = "application/xls";
Response.AddHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode("报销单历史查询.xls", System.Text.Encoding.UTF8));
Response.BinaryWrite(ms.ToArray());
Response.End();
}
else
{
this.Response.Write("<script>alert('无数据不允许导出')</script>");
}
}
protected void Button1_Click(object sender, EventArgs e)
{
string sqlstr = "select BQJE,SQJE,ZJJE,Depname,LSJL from RM_keshiyewufei where Remark1='" + DateTime.Now.Year + "' and Remark=0";
DataTable dt = DBUnity.AdapterToTab(sqlstr);
for (int i = 0; i < dt.Rows.Count; i++)
{
string aa = dt.Rows[i]["LSJL"].ToString();
decimal zjje = 0;
if (aa.Trim('@').Split('#').Length > 1)
{
for (int k = 0; k < aa.Trim('@').Split('@').Length; k++)
{
zjje += ParseUtil.ToDecimal(aa.Trim('@').Split('@')[k].Split('#')[0], 0);
}
}
dt.Rows[i]["ZJJE"] = 0;
}
dt.Columns.Remove("LSJL");
MemoryStream ms = ExcelRender.RenderToExcelSetHead(dt, "本期金额#上年余额#追加金额#部门名");
Response.ContentType = "application/xls";
Response.AddHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode("部门.xls", System.Text.Encoding.UTF8));
Response.BinaryWrite(ms.ToArray());
Response.End();
}
//private bool ExcelBasicDataValidate( DataTable excelDT , ref string errMsg )
//{
// if ( excelDT.Columns.Count!=9 )
// {
// errMsg="基础资料表列数为9列，请核对后重新导入！";
// }
// if ( excelDT.Columns[0].Caption!="Model code" )
// {
// errMsg="基础资料表第一列为Model code，请核对后重新导入！";
// }
// if ( excelDT.Columns[1].Caption!="法定计量单位" )
// {
// errMsg="基础资料表第二列为法定计量单位，请核对后重新导入！";
// }
// if ( excelDT.Columns[2].Caption!="HS CODE (IN CAT)" )
// {
// errMsg="基础资料表第三列为HS CODE (IN CAT)，请核对后重新导入！";
// }
// if ( excelDT.Columns[3].Caption!="中文品名" )
// {
// errMsg="基础资料表第四列为中文品名，请核对后重新导入！";
// }
// if ( excelDT.Columns[4].Caption!="英文品名" )
// {
// errMsg="基础资料表第五列为英文品名，请核对后重新导入！";
// }
// if ( excelDT.Columns[5].Caption!="申报要素" )
// {
// errMsg="基础资料表第六列为申报要素，请核对后重新导入！";
// }
// if ( excelDT.Columns[6].Caption!="监管条件" )
// {
// errMsg="基础资料表第七列为英文品名，请核对后重新导入！";
// }
// if ( excelDT.Columns[7].Caption!="Double or Set" )
// {
// errMsg="基础资料表第八列为Double or Set，请核对后重新导入！";
// }
// if ( excelDT.Columns[8].Caption!="检测报告号" )
// {
// errMsg="基础资料表第九列为检测报告号，请核对后重新导入！";
// }
// if ( errMsg!="" )
// return false;
// else
// return true;
//}
private void btnBasicDataInput_Click(object sender, EventArgs e)
{
this.operateType = OperateType.BasicDataAdd;
System.Windows.Forms.OpenFileDialog fd = new OpenFileDialog( );
if (fd.ShowDialog( ) == DialogResult.OK)
{
DataTable dt = null;
using (FileStream fs = new FileStream(fd.FileName, FileMode.Open, FileAccess.Read))
{
//把文件读取到字节数组
byte[] data = new byte[fs.Length];
fs.Read(data, 0, data.Length);
fs.Close( );
MemoryStream ms = new MemoryStream(data);
dt = ExcelRender.RenderFromExcel(ms, 0, 0);
}
string errMsg = "";
if (!ExcelDataValidate(dt, ref errMsg))
{
MessageBox.Show(errMsg);
return;
}
basicDataBLL.BulkBasicDataInsert(dt, 1000);
//if ( !ExcuteBasicDataInput( dt , fd.FileName , ref errMsg ) )
//{
// MessageBox.Show( errMsg );
// return;
//}
BasicDataTable = dt;
SetSubForm("FrmBasicData");
MessageBox.Show("基础资料成功导入！");
}
}
protected void Page_Load(object sender, EventArgs e)
{
var comid = Request["comid"];
var startime = Request["startime"].ConvertTo <string>("");
var endtime = Request["endtime"].ConvertTo <string>("");
//如果没有日期，只读取当天数据
if (startime == "")
{
startime = DateTime.Now.ToString("yyyy-MM-dd");
}
if (endtime == "")
{
endtime = DateTime.Now.ToString("yyyy-MM-dd");
}
string endtime_temp = endtime;
var condition = " a.comid=" + comid;
if (startime != "")
{
condition += " and a.usetime>='" + startime + "'";
}
if (endtime != "")
{
endtime = DateTime.Parse(endtime).AddDays(1).ToString();
condition += " and a.usetime<'" + endtime + "'";
}
ExcelRender.RenderToExcel(
ExcelSqlHelper.ExecuteDataTable(CommandType.Text, "select a.orderid [编号],b.Pro_name [产品名称],c.U_name [姓名],c.U_phone [手机],d.company [出票单位],a.wlorderid [WL订单号],c.Pay_price [结算价],a.usedQuantity [使用数量],b.Pro_end[有效期],a.usetime[使用时间] from wl_uselog as a left join b2b_com_pro as b on a.proid=b.id left join b2b_order as c on a.orderid=c.id left join agent_company as d on c.agentid=d.id where " + condition + " order by a.id desc"),
Context, "WLinterfaceuselog" + startime + "--" + endtime_temp + ".xls");
}
private void btnCLPOutput_Click(object sender, EventArgs e)
{
SaveFileDialog sfd = new SaveFileDialog( );
//设置文件类型
sfd.Filter = "Excel文件(*.xls)|*.xls|所有文件(*.*)|*.*";
sfd.Title = "保存文件";
//设置默认文件类型显示顺序
sfd.FilterIndex = 1;
//保存对话框是否记忆上次打开的目录
sfd.RestoreDirectory = true;
//点了保存按钮进入
if (sfd.ShowDialog( ) == DialogResult.OK)
{
string localFilePath = sfd.FileName.ToString( ); //获得文件路径
string fileNameExt = localFilePath.Substring(localFilePath.LastIndexOf("\\") + 1);
string sheetname = String.Empty;
foreach (DataTable dt in ParcelNumberDataSet.Tables)
{
sheetname += dt.TableName + ",";
}
sheetname = sheetname.Substring(0, sheetname.Length - 1);
ExcelRender.RenderToExcel(ParcelNumberDataSet, sheetname, localFilePath);
//ExcelRender.RenderToExcel( BasicDataTable , localFilePath );
MessageBox.Show("原始CLP成功导出！");
toolStripStatusLabel1.Text = "原始CLP成功导出！";
}
}
private void btnCLPInput_Click(object sender, EventArgs e)
{
this.operateType = OperateType.TotalCLPHandle;
System.Windows.Forms.OpenFileDialog fd = new OpenFileDialog( );
fd.Multiselect = true;
if (fd.ShowDialog( ) == DialogResult.OK)
{
bool flag = false;
foreach (string filename in fd.FileNames)
{
if (!ExcelRender.CheckFiles(filename))
{
flag = true;
break;
}
}
if (flag)
{
MessageBox.Show("当前需要导入的Excel文件正在被占用，请关闭该文件后重新导入！");
return;
}
DataSet ds = new DataSet( );
foreach (string filename in fd.FileNames)
{
using (FileStream fs = new FileStream(filename, FileMode.Open, FileAccess.Read))
{
//把文件读取到字节数组
byte[] data = new byte[fs.Length];
fs.Read(data, 0, data.Length);
fs.Close( );
MemoryStream ms = new MemoryStream(data);
_CLPTable = new DataTable( );
_CLPTable = ExcelRender.RenderFromExcel(ms, 0, 0);
_CLPTable.TableName = filename.Substring(filename.LastIndexOf('\\') + 1);
ds.Tables.Add(_CLPTable);
}
}
_ParcelNumberDataSet = ds;
//_fileName=fd.FileName.Substring( fd.FileName.LastIndexOf( '\\' )+1 );
//_OriginalCLPTable.TableName=_fileName;
//_OriginalCLPTable.AcceptChanges( );
//DataTable tempDT=new DataTable( );
//tempDT=_OriginalCLPTable.Copy( );
//tempDT.Columns.Add( "FileName" , typeof( String ) );
//tempDT.Columns.Add( "CreateTime" , typeof( DateTime ) );
//tempDT.Columns.Add( "Creator" , typeof( String ) );
//foreach ( DataRow row in tempDT.Rows )
//{
// row["FileName"]=FileName;
// row["CreateTime"]=DateTime.Now;
// row["Creator"]="admin";
//}
//bll.BulkOriginalCLPInsert( tempDT , 1000 );
SetSubForm("FrmTotalCLPHandle");
toolStripStatusLabel1.Text = "原始CLP成功导入！";
//MessageBox.Show( "原始CLP成功导入！" );
}
}
//导出Excel文件_NPOI
protected void btnExcelOut_Click(object sender, EventArgs e)
{
DateTime dt = System.DateTime.Now;
string str = dt.ToString("yyyyMMddhhmmss");
str = "gaokaoxinxi_" + str + ".xls";
GridView1.AllowPaging = false;
ExcelRender.RenderToExcel(showData().Tables[0], Context, str);
}
public void Test1()
{
IExcelRender render = new ExcelRender();
render.RenderToExcel(new List <IDictionary <string, object> > {
new Dictionary <string, object> {
{ "─с┤зм»", 1 }
}
});
}
//导出Excel文件_NPOI
protected void btnExcelOut_Click(object sender, EventArgs e)
{
DateTime dt = System.DateTime.Now;
string str = dt.ToString("yyyyMMddhhmmss");
str = "chengjichaxun_" + str + ".xls";
GridView1.AllowPaging = false;
ExcelRender.RenderToExcel(dsAboutExcel.Tables[0], Context, str);
}
/// <summary>
/// 导出
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnOut_Click(object sender, EventArgs e)
{
Hparams.Add("CpyNo", mUser.CpyNo.Trim());
Hparams.Add("LoginName", CommonManage.TrimSQL(txtUserAccount.Text.Trim()));
Hparams.Add("TimeStart", txtTimeBegin.Value);
Hparams.Add("TimeEnd", txtTimeEnd.Value + " 23:59:59");
dsSalesInfoCollect = base.baseDataManage.MulExecProc("pro_AirChangeStatistics", Hparams);
ExcelRender.RenderToExcel(dsSalesInfoCollect[0], Context, "航变统计报表" + DateTime.Now.ToString("yyyy_MM_dd_hh_mm_ss") + ".xls");
}
/// <summary>
/// 导出政策
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btndc_Click(object sender, EventArgs e)
{
//IHashObject parameter = new HashObject();
//GridView1.DataSource = new PbProject.Logic.SQLEXBLL.SQLEXBLL_Base().GetPolicyExcelByStrWhere("公司编号='" + mCompany.UninCode + "'");
//GridView1.DataBind();
//Response.Clear();
//DownloadExcelFlag = true;
//Export("政策表_" + DateTime.Now.ToString("yyyy_MM_dd_hh_mm_ss"));
DataTable dt = new PbProject.Logic.SQLEXBLL.SQLEXBLL_Base().GetPolicyExcelByStrWhere("公司编号='" + mCompany.UninCode + "'");
ExcelRender.RenderToExcel(dt, Context, "政策表_" + DateTime.Now.ToString("yyyy_MM_dd_hh_mm_ss") + ".xls");
}
//导入Excel文件，更新数据库
protected void btnOk_Click(object sender, EventArgs e)
{
Boolean fileOK = false;
//指定路径
String path = Server.MapPath("~/");
//文件上传控件中如果已经包含文件
if (fuExcel.HasFile)
{
//得到文件的后缀
String fileExtension = System.IO.Path.GetExtension(fuExcel.FileName).ToLower();
//允许的文件后缀
String allowedExtensions = ".xls";
// 看包含的文件是否是被允许的文件后缀
if (fileExtension == allowedExtensions)
{
fileOK = true;
}
}
//把文件中的数据导入数据库
if (fileOK)
{
try
{
byte[] fileBytes = fuExcel.FileBytes;
if (ExcelRender.HasData(new MemoryStream(fileBytes)))
{
str.Clear();
str.Append("insert into shulihuaxinxi(years,class,zhzx_num,ningbo_num,zhejiang_num,notes)");
int rowAffected = ExcelRender.RenderToDb(new MemoryStream(fileBytes), str.ToString(), showData2); //showData2函数作为参数传值
//GridView1.DataSource = showData(str);
//GridView1.DataBind();
BindGridView(showData(str));
lblMsg.Text = "<strong>成功导入数据，共：" + rowAffected.ToString() + "条</strong>";
}
else
{
lblMsg.Text = "没有数据可用于导入";
}
fileBytes = null;
}
catch (Exception ex)
{
lblMsg.Text = "数据格式不正确，请参照<u>模版文件</u>" + ex.ToString();
}
}
else
{
lblMsg.Text = "注意：只能上传 *.xls 文件!";
}
}
/// <summary>
/// 导入excel数据
/// </summary>
/// <param name="context"></param>
private void ImportExcel(HttpContext context)
{
try
{
//从Request中取参数，注意上传的文件在Requst.Files中
string name = context.Request["name"];
var data = context.Request.Files["data"];
string dir = context.Request.MapPath("~/FileTemp/");
if (!Directory.Exists(dir))
{
Directory.CreateDirectory(dir);
}
string file = dir + name;
data.SaveAs(file);
if (File.Exists(file))
{
//Stream stream = FGA_NUtility.FileOper.FileToStream(file);
DataTable dt = ExcelRender.RenderFromExcel(file);
if (dt == null)
{
context.Response.Write("No data");
}
string sql = string.Empty;
List <string> sqllist = new List <string>();
for (int i = 0; i < dt.Rows.Count; i++)
{
sql = @" insert into bomcost_rptbase (period_name,building_code,part_no,operation_no,container_status,onhand_qty,CREATEUSER,CREATEDATE)
values ('{0}','{1}','{2}','{3}','{4}',{5},'{6}',getdate()) ";
sql = string.Format(sql, dt.Rows[i]["period_name"], dt.Rows[i]["building_code"], dt.Rows[i]["part_no"], dt.Rows[i]["operation_no"],
dt.Rows[i]["constainer_status"], dt.Rows[i]["onhand_qty"], (HttpContext.Current.Session[SysConst.S_LOGIN_USER] as UsersModel).USERNAME);
sqllist.Add(sql);
}
if (sqllist.Count > 0)
{
if (FGA_DAL.Base.SQLServerHelper.ExecuteSqlTran(sqllist) > 0)
{
File.Delete(file);
context.Response.Write("success");
}
else
{
context.Response.Write("fail");
}
}
}
}
catch (Exception)
{
context.Response.Write("error");
}
}
protected void btnexport_Click(object sender, EventArgs e)
{
string filename = "bomcostdata" + DateTime.Now.ToString() + ".xls";
string sql = "select * from bomcost_rptbase";
DataSet ds = new DataSet();
ds = FGA_DAL.Base.SQLServerHelper.Query(sql);
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
HttpContext context = System.Web.HttpContext.Current;
ExcelRender.SetRenderToExcel(ds.Tables[0], context, filename);
}
}
/// <summary>
/// JobStatusUpt界面导入excel数据 add by i-wxl 20161201
/// </summary>
/// <param name="context"></param>
private void JobnoimpExcel(HttpContext context)
{
try
{
//从Request中取参数，注意上传的文件在Requst.Files中
string name = context.Request["name"];
var data = context.Request.Files["data"];
string dir = context.Request.MapPath("~/FileTemp/");
if (!Directory.Exists(dir))
{
Directory.CreateDirectory(dir);
}
string file = dir + name;
data.SaveAs(file);
if (File.Exists(file))
{
DataTable dt = ExcelRender.RenderFromExcel(file);
if (dt == null)
{
context.Response.Write("No data");
}
string sql = string.Empty;
List <string> sqllist = new List <string>();
for (int i = 0; i < dt.Rows.Count; i++)
{
sql = @" insert into jobnostatusupt (JOBNO,CREATER,CREATEDATE)
values ('{0}','{1}',getdate()) ";
//string aa = dt.Columns[0].ColumnName;
sql = string.Format(sql, dt.Rows[i][dt.Columns[0].ColumnName], (HttpContext.Current.Session[SysConst.S_LOGIN_USER] as UsersModel).USERNAME);
sqllist.Add(sql);
}
if (sqllist.Count > 0)
{
if (FGA_DAL.Base.SQLServerHelper.ExecuteSqlTran(sqllist) > 0)
{
File.Delete(file);
context.Response.Write("success");
}
else
{
context.Response.Write("fail");
}
}
}
}
catch (Exception)
{
context.Response.Write("error");
}
}
//导入Excel文件，更新数据库
protected void btnOk_Click(object sender, EventArgs e)
{
Boolean fileOK = false;
//指定路径
String path = Server.MapPath("~/");
//文件上传控件中如果已经包含文件
if (fuExcel.HasFile)
{
//得到文件的后缀
String fileExtension = System.IO.Path.GetExtension(fuExcel.FileName).ToLower();
//允许的文件后缀
String allowedExtensions = ".xls";
// 看包含的文件是否是被允许的文件后缀
if (fileExtension == allowedExtensions)
{
fileOK = true;
}
}
//把文件中的数据导入数据库
if (fileOK)
{
try
{
byte[] fileBytes = fuExcel.FileBytes;
if (ExcelRender.HasData(new MemoryStream(fileBytes)))
{
str.Clear();
str.Append("insert into exam_li01(class,stunum,stuname,xueji,yu,shu,ying,wu,hua,sheng,zixuan,total,MC)");
int rowAffected = ExcelRender.RenderToDb(new MemoryStream(fileBytes), str.ToString(), showData2); //showData2函数作为参数传值
BindGridView(showData(str));
lblMsg.Text = "<strong>成功导入数据，共：" + rowAffected.ToString() + "条</strong>";
}
else
{
lblMsg.Text = "没有数据可用于导入";
}
fileBytes = null;
}
catch (Exception ex)
{
lblMsg.Text = "数据格式必须为<u>常规</u>而不是<u>数值</u>，请<u>参照模版文件</u>" + ex.ToString();
}
}
else
{
lblMsg.Text = "注意：只能上传 *.xls 文件!";
}
}
protected void Page_Load(object sender, EventArgs e)
{
comid = Request["comid"].ConvertTo <int>(0);
md5info = Request["md5info"].ConvertTo <string>("");
string companyName = new B2bCompanyData().GetCompanyNameById(comid);
string Returnmd5 = EncryptionHelper.ToMD5(comid.ToString() + "lixh1210", "UTF-8");
if (Returnmd5 == md5info)//验证MD5
{
//ExcelRender.RenderToExcel(
// ExcelSqlHelper.ExecuteDataTable(CommandType.Text, "select idcard [会员卡号], name [姓名],phone [手机],email [邮件],imprest [预付款],Integral [积分],regidate [注册时间] from b2b_crm where com_id=" + comid),
// Context, companyName + "会员信息" + ".xls");
DataTable crmdt = ExcelSqlHelper.ExecuteDataTable(CommandType.Text, "select idcard [会员卡号], name [姓名],phone [手机],email [邮件],imprest [预付款],Integral [积分],regidate [注册时间] from b2b_crm where com_id=" + comid);
DataTable dt = new DataTable();
dt.Columns.Add("会员卡号");
dt.Columns.Add("姓名");
dt.Columns.Add("手机");
dt.Columns.Add("邮件");
dt.Columns.Add("预付款");
dt.Columns.Add("积分");
dt.Columns.Add("注册时间");
dt.Columns.Add("渠道单位");
dt.Columns.Add("推荐人");
DataRow dr = dt.NewRow();
foreach (DataRow rr in crmdt.Rows)
{
dr = dt.NewRow();
dr["会员卡号"] = rr["会员卡号"].ToString();
dr["姓名"] = rr["姓名"].ToString();
dr["手机"] = rr["手机"].ToString();
dr["邮件"] = rr["邮件"].ToString();
dr["预付款"] = rr["预付款"].ToString();
dr["积分"] = rr["积分"].ToString();
dr["注册时间"] = rr["注册时间"].ToString();
dr["渠道单位"] = new MemberChannelcompanyData().UpCompanyById(rr["会员卡号"].ToString());
dr["推荐人"] = MemberChannelData.SearchNamestring(rr["会员卡号"].ToString());
dt.Rows.Add(dr);
}
ExcelRender.RenderToExcel(dt, Context, companyName + "会员信息" + ".xls");
}
}
public void ProcessRequest(HttpContext context)
{
var db = ContextDB.Context();
var begindate = context.Request["begindate"];
var enddate = context.Request["enddate"];
var agid = context.Request["agid"];
var merchantid = context.Request["merchantid"];
var keyword = context.Request["keyword"];
var status = context.Request["status"];
var businesspasstype = context.Request["businesspasstype"];
var page = 1;
var pagesize = 10000000;
var query = db.Query("exec PROC_OrderList @0,@1,@2,@3,@4,@5,@6,@7,@8", begindate, enddate, agid, merchantid, keyword, status, page, pagesize, businesspasstype).ToList();
DataTable table = new DataTable();
if (query.Count > 0)
{
model.message = "查询成功";
model.status_code = 200;
model.data = query;
table.Columns.Add("订单号", typeof(string));
table.Columns.Add("订单金额", typeof(string));
table.Columns.Add("手续费", typeof(string));
table.Columns.Add("实到金额", typeof(string));
table.Columns.Add("订单状态", typeof(string));
table.Columns.Add("代理商", typeof(string));
table.Columns.Add("真实商户", typeof(string));
table.Columns.Add("付款类型", typeof(string));
table.Columns.Add("备注", typeof(string));
table.Columns.Add("订单时间", typeof(string));
foreach (var item in query)
{
var item_status = item.status == "1" ? "支付完成" : item.status == "0" ? "未支付" : "支付失败";
var item_paytype = item.status == "1" ? "微信" : item.status == "2" ? "支付宝" : item.status == "3" ? "QQ钱包":"京东";
table.Rows.Add(item.orderno, item.amount, item.servicecharge, item.realamount, item_status,
item.agname, item.shortname, item_paytype, item.remark, item.addtime);
}
}
else
{
model.message = "暂无数据";
model.status_code = 200;
context.Response.Write("查询不到导出数据");
}
db.Dispose();
ExcelRender.RenderToExcel(table, context, "订单列表" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls");
}
protected void btnexport_Click(object sender, EventArgs e)
{
string filename = "statsbyscanner" + DateTime.Now.ToString() + ".xls";
string month = this.DropDownList1.SelectedItem.Text;
string year = this.DropDownList2.SelectedItem.Text;
string sql = "select period_name,[SerialNO],[PartNO],[Location],[Quantity],[ActualQty],[ActualQty] - [Quantity] as difference,[creater] ,[createtime] from statsbyscanner";
//sql = string.Format(sql,month+"-"+year);
DataSet ds = new DataSet();
ds = FGA_DAL.Base.SQLServerHelper.Query(sql);
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
HttpContext context = System.Web.HttpContext.Current;
ExcelRender.SetRenderToExcel(ds.Tables[0], context, filename);
}
}
protected void Page_Load(object sender, EventArgs e)
{
comid = Request["comid"].ConvertTo <int>(0);
agentid = Request["agentid"].ConvertTo <int>(0);
orderid = Request["orderid"].ConvertTo <int>(0);
md5info = Request["md5info"].ConvertTo <string>("");
string Returnmd5 = EncryptionHelper.ToMD5(orderid.ToString() + comid.ToString() + agentid.ToString() + "lixh1210", "UTF-8");
if (Returnmd5 == md5info)//验证MD5
{
B2bOrderData orderdate = new B2bOrderData();
B2b_order ordermodel = orderdate.GetOrderById(orderid);
if (ordermodel != null)
{
B2bComProData prodata = new B2bComProData();
B2b_com_pro promodel = prodata.GetProById(ordermodel.Pro_id.ToString());
if (promodel != null)
{
if (ordermodel.Agentid == agentid && ordermodel.Comid == comid && ordermodel.Warrant_type == 2)
{
ExcelRender.RenderToExcel(
ExcelSqlHelper.ExecuteDataTable(CommandType.Text, "select e_proname [产品名称],pno [票号],pnum [数量] from b2b_eticket where oid=" + orderid),
Context, promodel.Pro_name + ordermodel.U_subdate.Date.ToString("yyyyMMdd") + "-" + ordermodel.Id.ToString() + ".xls");
}
else
{
err = "订单参数错误";
}
}
else
{
err = "产品错误";
}
}
else
{
err = "订单错误";
}
}
else
{
err = "验证错误";
}
}
/// <summary>
///
/// </summary>
/// <param name="context"></param>
private void ImportSelect_access(HttpContext context)
{
string postData = "";
string postUrl = "";
if (context.Session["fygx"] == null)
{
return;
}
postUrl = ConfigHelper.GetConfigValue("casesdataAccesslistExcel");
if (string.IsNullOrEmpty(postUrl))
{
context.Response.Write("error：未配置导出接口地址！");
return;
}
Dictionary <string, string> fygx = context.Session["fygx"] as Dictionary <string, string>;//取出集合
foreach (var item in fygx)
{
postData += item.Value + ",";
}
postData = postData.Substring(0, postData.Length - 1);
string res = HttpHelper.GetHttpResponse(postUrl + "?rowkey=" + postData);
JavaScriptSerializer js = new JavaScriptSerializer();
//FGA_MODEL.casemodel.casedatamodel search = js.Deserialize<FGA_MODEL.casemodel.casedatamodel>(res);
//DataTable dt = DataTableTemplate.CreateAcessDataTable();
//foreach (FGA_MODEL.casemodel.casedatamd model in search.lists)
//{
// DataRow rows = dt.NewRow();
// rows["攻击时间"] = FGA_NUtility.Common.StampToDateTime(model.capturetime + "") + "";
// rows["源IP"] = model.sourceip;
// rows["目的IP"] = model.destip;
// rows["等级"] = FGA_NUtility.Common.ConvertServersityLevelForText(model.attackhazardlevel);
// rows["服务类型"] = model.service;
// rows["分类"] = model.parentclassifytitle;
// dt.Rows.Add(rows);
//}
DataTable dt = new DataTable();
ExcelRender.SetRenderToExcel(dt, context, FGA_NUtility.Common.getcurrenttime() + ".xls");
}
public ResponseMessageResult GetOrderList(int status, int page, int pagesize, string keyword, string begindate = "", string enddate = "")
{
JwtModel jwtmodel = JwtHelper.getToken(HttpContext.Current.Request.Headers.GetValues("Authorization").First().ToString());
if (jwtmodel.isadmin)
{
var db = ContextDB.Context();
var query = db.Query("exec PROC_OrderList @0,@1,@2,@3,@4,@5,@6,@7", begindate, enddate, 0, 0, keyword, status, page, pagesize).ToList();
if (query.Count > 0)
{
model.message = "查询成功";
model.status_code = 200;
model.data = query;
DataTable table = new DataTable();
table.Columns.Add("aa", typeof(string));
table.Columns.Add("bb", typeof(string));
table.Columns.Add("cc", typeof(string));
for (int i = 0; i < 10; i++)
{
string a = DateTime.Now.Ticks.ToString();
Thread.Sleep(1);
string b = DateTime.Now.Ticks.ToString();
Thread.Sleep(1);
string c = DateTime.Now.Ticks.ToString();
Thread.Sleep(1);
table.Rows.Add(a, b, c);
}
ExcelRender.RenderToExcel(table, HttpContext.Current.Request.Context, "看看.xls");
}
else
{
model.message = "暂无数据";
model.status_code = 200;
}
db.Dispose();
}
else
{
model.message = "用户权限不足";
model.status_code = 401;
}
return(new ResponseMessageResult(Request.CreateResponse((HttpStatusCode)model.status_code, model)));
}
private void PrintST()
{
DataTable dt = BG_UserLogic.GetPrintdt();
if (dt != null && dt.Rows.Count > 0)
{
MemoryStream ms = ExcelRender.RenderToExcelSetHead(GetNew(dt), "姓名#登录名#特殊权限#权限#部门名称");
Response.ContentType = "application/xls";
Response.AddHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode("人员详细.xls", System.Text.Encoding.UTF8));
Response.BinaryWrite(ms.ToArray());
Response.End();
}
else
{
this.Response.Write("<script>alert('无数据不允许导出')</script>");
}
}
protected void btnexport_Click(object sender, EventArgs e)
{
string filename = "bomcost_rpt" + DateTime.Now.ToString() + ".xls";
string month = this.DropDownList1.SelectedItem.Text;
string year = this.DropDownList2.SelectedItem.Text;
string sql = "select * from bomcost_rpt where PERIOD_NAME='{0}'";
sql = string.Format(sql, month + "-" + year);
DataSet ds = new DataSet();
ds = FGA_DAL.Base.SQLServerHelper.Query(sql);
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
HttpContext context = System.Web.HttpContext.Current;
ExcelRender.SetRenderToExcel(ds.Tables[0], context, filename);
}
}
public static DataTable GetDTBySheetname(string sname, byte[] filename, int rowIndex, int cellNum)
{
DataTable dt = new DataTable();
Dictionary <string, int> dic = new Dictionary <string, int>();
if (ExcelRender.HasData(new MemoryStream(filename)))
{
IWorkbook wk = new HSSFWorkbook(new MemoryStream(filename));
for (int i = 0; i < wk.NumberOfSheets; i++)
{
ISheet sheet = wk.GetSheetAt(i);
string str = sheet.SheetName;
dic.Add(str, i);
}
}
dt = ExcelRender.RenderFromExcel1(new MemoryStream(filename), dic[sname], rowIndex, cellNum);
return(dt);
}
protected void btnPrint_Click(object sender, EventArgs e)
{
string cpyno = mCompany.UninCode.Length > 12 ? mCompany.UninCode.Substring(0, 12) : mCompany.UninCode;
DataBase.Data.HashObject Hparams = new DataBase.Data.HashObject();
Hparams.Add("sqlWhere", Con);
Hparams.Add("cpyno", cpyno);
string pro = "pro_PayMentRecord";
if (ViewState["paytype"].ToString() == "pos")
{
pro = "pro_PosPayMentRecord";
}
DataTable[] dtlist = base.baseDataManage.MulExecProc(pro, Hparams);
DataTable dt = dtlist[0];
ExcelRender.RenderToExcel(dt, Context, this.spantitle.InnerText + "报表_" + DateTime.Now.ToString("yyyy_MM_dd_hh_mm_ss") + ".xls");
}
/// <summary>
/// 导出报表
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnOut_Click(object sender, EventArgs e)
{
//Response.Clear();
//DownloadExcelFlag = true;
//Export(this.spantitle.InnerText+"报表_" + DateTime.Now.ToString("yyyy_MM_dd_hh_mm_ss"));
DataBase.Data.HashObject Hparams = new DataBase.Data.HashObject();
Hparams.Add("sqlwhere", Query());
//（账户，在线，销账）汇总
DataTable[] dsSalesInfoCollect = null;
if (ViewState["paytype"].ToString() == "qkxz")
{
dsSalesInfoCollect = base.baseDataManage.MulExecProc("pro_CpyXZAccountCollect", Hparams);
}
else
{
dsSalesInfoCollect = base.baseDataManage.MulExecProc("pro_CpyAccountCollect", Hparams);
}
ExcelRender.RenderToExcel(dsSalesInfoCollect[0], Context, this.spantitle.InnerText + "报表_" + DateTime.Now.ToString("yyyy_MM_dd_hh_mm_ss") + ".xls");
}
public IActionResult CreateExcelFile()
{
logger.Info("Начало создания Excel файла");
try
{
var persons = Repository.GetAllPersons();
var byteTable = ExcelRender.GetByteTable(persons);
logger.Info("Файл Excel успешно создан");
return(File(
fileContents: byteTable,
contentType: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
fileDownloadName: "Persons.xlsx"));
}
catch (Exception ex)
{
logger.Info($"Файл excel не создан, произошла ошибка: {ex}");
return(View("Error"));
}
}
protected void Page_Load(object sender, EventArgs e)
{
var comid = Request["comid"];
var startime = Request["startime"].ConvertTo <string>("");
var endtime = Request["endtime"].ConvertTo <string>("");
var projectid = Request["projectid"].ConvertTo <int>(0);
var proid = Request["proid"].ConvertTo <int>(0);
var key = Request["key"].ConvertTo <string>("");//电子码
object o1 = ExcelSqlHelper.ExecuteScalar("select com_name from b2b_company where id=" + comid);
var condition = " a.action=1 and a.com_id=" + comid + " and a.a_state=" + (int)ECodeOperStatus.OperSuc + " and a.pno in (select pno from b2b_eticket where 1=1 ";
if (projectid != 0)
{
condition += " and pro_id in (select id from b2b_com_pro where projectid=" + projectid + ")";
}
if (proid != 0)
{
condition += " and pro_id=" + proid;
}
condition += ")";
if (key != "")
{
condition += " and a.pno='" + key + "' ";
}
if (startime != "")
{
condition += " and a.actiondate>='" + startime + "'";
}
if (endtime != "")
{
endtime = DateTime.Parse(endtime).AddDays(1).ToString();
condition += " and a.actiondate<'" + endtime + "'";
}
ExcelRender.RenderToExcel(
ExcelSqlHelper.ExecuteDataTable(CommandType.Text, "select b.e_proname [产品名称],a.pno [电子码],b.pnum [订购数量],a.use_pnum [本次使用数量],a.actiondate [验证时间],a.posid [验证pos],b.e_face_price [门市价],b.e_sale_price [结算价],c.company[出票方] from b2b_etcket_log as a left join b2b_eticket as b on a.com_id=b.com_id and a.pno=b.pno and a.eticket_id=b.id left join agent_company as c on b.agent_id=c.id where " + condition + " order by a.id desc"),
Context, o1.ToString() + "验证电子票列表" + ".xls");
}
private void btnBasicDataOutput_Click(object sender, EventArgs e)
{
SaveFileDialog sfd = new SaveFileDialog( );
//设置文件类型
sfd.Filter = "Excel文件(*.xls)|*.xls|所有文件(*.*)|*.*";
sfd.Title = "保存文件";
//设置默认文件类型显示顺序
sfd.FilterIndex = 1;
//保存对话框是否记忆上次打开的目录
sfd.RestoreDirectory = true;
//点了保存按钮进入
if (sfd.ShowDialog( ) == DialogResult.OK)
{
string localFilePath = sfd.FileName.ToString( ); //获得文件路径
string fileNameExt = localFilePath.Substring(localFilePath.LastIndexOf("\\") + 1); //获取文件名，不带路径
ExcelRender.RenderToExcel(BasicDataTable, localFilePath);
MessageBox.Show("基础资料成功导出！");
toolStripStatusLabel1.Text = "基础资料成功导出！";
}
}
