public override void ExecuteResult(ControllerContext context)
{
var r = context.HttpContext.Response;
r.Clear();
r.ContentType = "application/vnd.ms-excel";
if (!string.IsNullOrEmpty(FileName))
r.AddHeader("content-disposition",
"attachment;filename=" + FileName);
const string header =
@"<html xmlns:x=""urn:schemas-microsoft-com:office:excel"">
<head>
<meta http-equiv=Content-Type content=""text/html; charset=utf-8"">
<style>
<!--table
br {mso-data-placement:same-cell;}
tr {vertical-align:top;}
-->
</style>
</head>
<body>";
r.Write(header);
r.Charset = "";
var dg = new DataGrid();
dg.EnableViewState = false;
dg.DataSource = Data;
dg.DataBind();
dg.RenderControl(new HtmlTextWriter(r.Output));
r.Write("</body></HTML>");
}
private void UploadDataTableToExcel(DataTable feedBacks)
{
HttpResponse response = HttpContext.Current.Response;
try
{
response.Clear();
response.Charset = string.Empty;
response.ContentType = "application/vnd.ms-excel";
response.AddHeader("Content-Disposition", "attachment;filename=\"FeedBack.xls\"");
using (StringWriter stringWriter = new StringWriter())
{
using (HtmlTextWriter htmlTextWriter = new HtmlTextWriter(stringWriter))
{
DataGrid dataGrid = new DataGrid();
dataGrid.DataSource = feedBacks;
dataGrid.DataBind();
dataGrid.RenderControl(htmlTextWriter);
response.Write(stringWriter.ToString());
}
}
response.End();
}
catch (ThreadAbortException ex)
{
Elmah.ErrorSignal.FromCurrentContext().Raise(ex);
}
}
//protected override void OnPreInit(EventArgs e)
//{
// //if (Request.Url.AbsoluteUri.IndexOf("Login") == -1)
// //{
// // if (CurrentUserInfo == null)
// // {
// // Response.Redirect(ResolveUrl("~/Login.aspx"));
// // }
// //}
// //base.OnPreInit(e);
//}
/// <summary>
/// 导出excel
/// </summary>
/// <param name="ds"></param>
/// <param name="FileName"></param>
public void CreateExcel(DataSet ds, string FileName)
{
System.Web.UI.WebControls.DataGrid dgExport = null;
System.Web.HttpContext curContext = System.Web.HttpContext.Current;
System.IO.StringWriter strWriter = null;
System.Web.UI.HtmlTextWriter htmlWriter = null;
if (ds != null)
{
curContext.Response.ContentType = "application/vnd.ms-excel";
curContext.Response.ContentEncoding = System.Text.Encoding.UTF8;
curContext.Response.Charset = "UTF-8";
curContext.Response.AppendHeader("Content-Disposition", "attachment;filename=" + System.Web.HttpUtility.UrlEncode(System.Text.Encoding.UTF8.GetBytes(FileName)) + ".xls");
strWriter = new System.IO.StringWriter();
htmlWriter = new System.Web.UI.HtmlTextWriter(strWriter);
dgExport = new System.Web.UI.WebControls.DataGrid();
dgExport.DataSource = ds.Tables[0].DefaultView;
dgExport.AllowPaging = false;
dgExport.DataBind();
dgExport.RenderControl(htmlWriter);
curContext.Response.Write(strWriter.ToString());
curContext.Response.End();
}
}
public static void Convert(System.Data.DataSet ds, string TableName, System.Web.HttpResponse response, string ExcelName)
{
//let's make sure the table name exists
//if it does not then call the default method
if (ds.Tables[TableName] == null)
{
Convert(ds, response, ExcelName);
}
//we've got a good table so
//let's clean up the response.object
response.Clear();
response.Charset = "";
//set the response mime type for excel
response.ContentType = "application/vnd.ms-excel";
//create a string writer
System.IO.StringWriter stringWrite = new System.IO.StringWriter();
//create an htmltextwriter which uses the stringwriter
System.Web.UI.HtmlTextWriter htmlWrite = new System.Web.UI.HtmlTextWriter(stringWrite);
//instantiate a datagrid
System.Web.UI.WebControls.DataGrid dg = new System.Web.UI.WebControls.DataGrid();
//set the datagrid datasource to the dataset passed in
dg.DataSource = ds.Tables[TableName];
//bind the datagrid
dg.DataBind();
//tell the datagrid to render itself to our htmltextwriter
dg.RenderControl(htmlWrite);
//all that's left is to output the html
response.Write(stringWrite.ToString());
response.End();
}
public static void DataTable2Excel(System.Data.DataTable dtData)
{
System.Web.UI.WebControls.DataGrid dgExport = null;
// 当前对话
System.Web.HttpContext curContext = System.Web.HttpContext.Current;
// IO用于导出并返回excel文件
System.IO.StringWriter strWriter = null;
System.Web.UI.HtmlTextWriter htmlWriter = null;
if (dtData != null)
{
// 设置编码和附件格式
curContext.Response.ContentType = "application/vnd.ms-excel";
curContext.Response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312");
curContext.Response.Charset = "gb2312";
curContext.Response.AppendHeader("Content-Disposition", "attachment;filename=111111111111111.xls");
// 导出excel文件
strWriter = new System.IO.StringWriter();
htmlWriter = new System.Web.UI.HtmlTextWriter(strWriter);
// 为了解决dgData中可能进行了分页的情况，需要重新定义一个无分页的DataGrid
dgExport = new System.Web.UI.WebControls.DataGrid();
dgExport.DataSource = dtData.DefaultView;
dgExport.AllowPaging = false;
dgExport.DataBind();
// 返回客户端
dgExport.RenderControl(htmlWriter);
curContext.Response.Write("<meta http-equiv=\"content-type\" content=\"application/ms-excel; charset=gb2312\"/>" + strWriter.ToString());
curContext.Response.End();
}
}
private void save(DataTable dt,string filename)
{
DataGrid excel = new DataGrid();
System.Web.UI.WebControls.TableItemStyle AlternatingStyle = new TableItemStyle();
System.Web.UI.WebControls.TableItemStyle headerStyle = new TableItemStyle();
System.Web.UI.WebControls.TableItemStyle itemStyle = new TableItemStyle();
AlternatingStyle.BackColor = System.Drawing.Color.LightGray;
headerStyle.BackColor = System.Drawing.Color.LightGray;
headerStyle.Font.Bold = true;
headerStyle.HorizontalAlign = System.Web.UI.WebControls.HorizontalAlign.Center;
itemStyle.HorizontalAlign = System.Web.UI.WebControls.HorizontalAlign.Center; ;
excel.AlternatingItemStyle.MergeWith(AlternatingStyle);
excel.HeaderStyle.MergeWith(headerStyle);
excel.ItemStyle.MergeWith(itemStyle);
excel.GridLines = GridLines.Both;
excel.HeaderStyle.Font.Bold = true;
DataSet ds = new DataSet();
ds.Tables.Add(dt);
excel.DataSource = ds; //输出DataTable的内容
excel.DataBind();
System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
excel.RenderControl(oHtmlTextWriter);
Response.AddHeader("Content-Disposition", "attachment; filename=" + System.Web.HttpUtility.UrlEncode(filename, System.Text.Encoding.UTF8) + ".xls");
Response.ContentType = "application/ms-excel";
Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
Response.Write(oHtmlTextWriter.InnerWriter.ToString());
Response.End();
}
public static void Convert(System.Data.DataSet ds, int TableIndex, System.Web.HttpResponse response, string ExcelName)
{
//lets make sure a table actually exists at the passed in value
//if it is not call the base method
if (TableIndex > ds.Tables.Count - 1)
{
Convert(ds, response, ExcelName);
}
//we've got a good table so
//let's clean up the response.object
response.Clear();
response.Charset = "";
response.AddHeader("Content-Disposition", "attachment;filename=Shilpa.xls");
//set the response mime type for excel
response.ContentType = "application/vnd.ms-excel";
//create a string writer
System.IO.StringWriter stringWrite = new System.IO.StringWriter();
//create an htmltextwriter which uses the stringwriter
System.Web.UI.HtmlTextWriter htmlWrite = new System.Web.UI.HtmlTextWriter(stringWrite);
//instantiate a datagrid
System.Web.UI.WebControls.DataGrid dg = new System.Web.UI.WebControls.DataGrid();
//set the datagrid datasource to the dataset passed in
dg.DataSource = ds.Tables[TableIndex];
//bind the datagrid
dg.DataBind();
//tell the datagrid to render itself to our htmltextwriter
dg.RenderControl(htmlWrite);
//all that's left is to output the html
response.Write(stringWrite.ToString());
response.End();
}
public void ExportToExcel(DataTable dt)
{
if (dt.Rows.Count > 0)
{
string filename = "Test1234.xls";
string excelHeader = "Quiz Report";
System.IO.StringWriter tw = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw);
DataGrid dgGrid = new DataGrid();
dgGrid.DataSource = dt;
dgGrid.DataBind();
// Report Header
hw.WriteLine("<b><u><font size=’3′> " + excelHeader + " </font></u></b>");
//Get the HTML for the control.
dgGrid.RenderControl(hw);
//Write the HTML back to the browser.
Response.ContentType = "application/vnd.ms-excel";
Response.AppendHeader("Content-Disposition", "attachment; filename=" + filename + "");
this.EnableViewState = false;
Response.Write(tw.ToString());
Response.End();
}
}
public static void Convert(System.Data.DataSet ds, int TableIndex, System.Web.HttpResponse response, string ExcelName)
{
//lets make sure a table actually exists at the passed in value
//if it is not call the base method
if (TableIndex > ds.Tables.Count - 1)
{
Convert(ds, response, ExcelName);
}
//we've got a good table so
//let's clean up the response.object
response.Clear();
response.Charset = "";
response.AddHeader("Content-Disposition", "attachment;filename=Shilpa.xls");
//set the response mime type for excel
response.ContentType = "application/vnd.ms-excel";
//create a string writer
System.IO.StringWriter stringWrite = new System.IO.StringWriter();
//create an htmltextwriter which uses the stringwriter
System.Web.UI.HtmlTextWriter htmlWrite = new System.Web.UI.HtmlTextWriter(stringWrite);
//instantiate a datagrid
System.Web.UI.WebControls.DataGrid dg = new System.Web.UI.WebControls.DataGrid();
//set the datagrid datasource to the dataset passed in
dg.DataSource = ds.Tables[TableIndex];
//bind the datagrid
dg.DataBind();
//tell the datagrid to render itself to our htmltextwriter
dg.RenderControl(htmlWrite);
//all that's left is to output the html
response.Write(stringWrite.ToString());
response.End();
}
public void ToExcel(object sender, System.EventArgs e)
{
try
{
DataTable dtData = GetDTList();
System.Web.UI.WebControls.DataGrid dgExport = null;
System.Web.HttpContext curContext = System.Web.HttpContext.Current;
System.IO.StringWriter strWriter = null;
System.Web.UI.HtmlTextWriter htmlWriter = null;
if (dtData != null && dtData.Rows.Count > 0)
{
curContext.Response.ContentType = "application/vnd.ms-excel";
curContext.Response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312");
curContext.Response.Charset = "UTF-8";
curContext.Response.Write("<meta http-equiv=Content-Type content=text/html;charset=gb2312 >");
strWriter = new System.IO.StringWriter();
htmlWriter = new System.Web.UI.HtmlTextWriter(strWriter);
dgExport = new System.Web.UI.WebControls.DataGrid();
dgExport.DataSource = dtData.DefaultView;
dgExport.AllowPaging = false;
dgExport.DataBind();
for (int i = 0; i < dgExport.Items.Count; i++)
{
for (int j = 0; j < 3; j++)
{
dgExport.Items[i].Cells[j].Attributes.Add("style", "vnd.ms-excel.numberformat:@");
}
}
dgExport.RenderControl(htmlWriter);
curContext.Response.Write(strWriter.ToString());
curContext.Response.End();
WriteAlertInfo(AlertSuccess);
}
else
{
WriteAlertInfo(AlertNoData);
}
}
catch (FisException fe)
{
this.GridViewExt1.DataSource = GetTable();
this.GridViewExt1.DataBind();
writeToAlertMessage(fe.mErrmsg);
}
catch (Exception ex)
{
this.GridViewExt1.DataSource = GetTable();
this.GridViewExt1.DataBind();
writeToAlertMessage(ex.Message);
}
finally
{
endWaitingCoverDiv();
}
}
public override void ExecuteResult(ControllerContext context)
{
var Response = context.HttpContext.Response;
Response.Buffer = true;
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("Content-Disposition", "attachment;filename=CMSPeople.xls");
Response.Charset = "";
var name = "ExtraExcelResult " + DateTime.Now;
var tag = DbUtil.Db.PopulateSpecialTag(qid, DbUtil.TagTypeId_ExtraValues);
var roles = CMSRoleProvider.provider.GetRolesForUser(Util.UserName);
var xml = XDocument.Parse(DbUtil.Db.Content("StandardExtraValues.xml", "<Fields/>"));
var fields = (from ff in xml.Root.Elements("Field")
let vroles = ff.Attribute("VisibilityRoles")
where vroles != null && (vroles.Value.Split(',').All(rr => !roles.Contains(rr)))
select ff.Attribute("name").Value);
var nodisplaycols = string.Join("|", fields);
var cmd = new SqlCommand("dbo.ExtraValues @p1, @p2, @p3");
cmd.Parameters.AddWithValue("@p1", tag.Id);
cmd.Parameters.AddWithValue("@p2", "");
cmd.Parameters.AddWithValue("@p3", nodisplaycols);
cmd.Connection = new SqlConnection(Util.ConnectionString);
cmd.Connection.Open();
var dg = new DataGrid();
dg.DataSource = cmd.ExecuteReader();
dg.DataBind();
dg.RenderControl(new HtmlTextWriter(Response.Output));
}
public static void Convert(System.Data.DataSet ds, System.Web.HttpResponse response, string ExcelName)
{
try
{
//first let's clean up the response.object
response.Clear();
response.Charset = "";
//set the response mime type for excel
response.ContentType = "application/vnd.ms-excel";
//create a string writer
//response.AddHeader("Content-Disposition", "attachment;filename=Shilpa.xls");
response.AddHeader("Content-Disposition", "attachment;filename=" + ExcelName + ".xls");
System.IO.StringWriter stringWrite = new System.IO.StringWriter();
//create an htmltextwriter which uses the stringwriter
System.Web.UI.HtmlTextWriter htmlWrite = new System.Web.UI.HtmlTextWriter(stringWrite);
//instantiate a datagrid
System.Web.UI.WebControls.DataGrid dg = new System.Web.UI.WebControls.DataGrid();
//set the datagrid datasource to the dataset passed in
dg.DataSource = ds.Tables[0];
//bind the datagrid
dg.DataBind();
//tell the datagrid to render itself to our htmltextwriter
dg.RenderControl(htmlWrite);
//all that's left is to output the html
response.Write(stringWrite.ToString());
response.End();
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// Exporta la información a Excel.
/// </summary>
/// <param name="response">HttpResponse actual.</param>
/// <param name="data">Datos a exportar.</param>
/// <param name="nombreArchivo">Nombre del archivo Excel</param>
public static void ExportToExcelFile(HttpResponse response, DataView data, string nombreArchivo)
{
var dg = new DataGrid { DataSource = data };
dg.DataBind();
response.Clear();
response.Buffer = true;
//application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
response.AddHeader("Content-Disposition", "filename=" + nombreArchivo);
response.ContentType = "application/vnd.ms-excel";
response.Charset = "UTF-8";
response.ContentEncoding = System.Text.Encoding.Default;
var stringWriter = new StringWriter();
var htmlWriter = new HtmlTextWriter(stringWriter);
dg.RenderControl(htmlWriter);
response.Write(stringWriter.ToString());
//resp.Flush();
try
{
response.End();
}
catch (Exception ex)
{
ISException.RegisterExcepcion(ex);
throw ex;
}
}
/// <summary>
/// 导出Excel文件，转换为可读模式
/// </summary>
public static void DataTable2Excel(System.Data.DataTable dtData)
{
DataGrid dgExport = null;
HttpContext curContext = HttpContext.Current;
StringWriter strWriter = null;
HtmlTextWriter htmlWriter = null;
if (dtData != null)
{
curContext.Response.ContentType = "application/vnd.ms-excel";
curContext.Response.ContentEncoding = System.Text.Encoding.UTF8;
curContext.Response.Charset = "";
strWriter = new StringWriter();
htmlWriter = new HtmlTextWriter(strWriter);
dgExport = new DataGrid();
dgExport.DataSource = dtData.DefaultView;
dgExport.AllowPaging = false;
dgExport.DataBind();
try
{
dgExport.RenderControl(htmlWriter);
}
catch (Exception e)
{
Log4Net.LogWrite("err", e.Message);
}
curContext.Response.Write(strWriter.ToString());
curContext.Response.End();
}
}
/// <summary>
/// 导出Excel文件，转换为可读模式
/// </summary>
public static void DataTable2Excel(DataTable dtData)
{
DataGrid dgExport = null;
var curContext = HttpContext.Current;
StringWriter strWriter = null;
HtmlTextWriter htmlWriter = null;
if (dtData != null)
{
curContext.Response.ContentType = "application/vnd.ms-excel";
curContext.Response.ContentEncoding = Encoding.UTF8;
curContext.Response.Charset = "";
strWriter = new StringWriter();
htmlWriter = new HtmlTextWriter(strWriter);
dgExport = new DataGrid
{
DataSource = dtData.DefaultView,
AllowPaging = false
};
dgExport.DataBind();
dgExport.RenderControl(htmlWriter);
curContext.Response.Write(strWriter.ToString());
curContext.Response.End();
}
}
protected void Page_Load(object sender, EventArgs e)
{
//if (Session["Type"].ToString() == "Excel")
//{
DataSet ds = Session["ExcelDs"] as DataSet;
Response.Clear();
Response.Charset = "";
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("Content-Disposition", "attachment;filename=ExcelName.xls");
System.IO.StringWriter stringWrite = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter htmlWrite = new System.Web.UI.HtmlTextWriter(stringWrite);
System.Web.UI.WebControls.DataGrid dg = new System.Web.UI.WebControls.DataGrid();
dg.DataSource = ds.Tables[0];
dg.DataBind();
dg.RenderControl(htmlWrite);
Response.Write(stringWrite.ToString());
Response.End();
//}
//else
//{
// Response.ContentType = "application/pdf";
// string path = Server.MapPath("~/PDFS/TestM10.pdf");
// byte[] bts = System.IO.File.ReadAllBytes(path);
// Response.Clear();
// Response.ClearHeaders();
// Response.AddHeader("Content-Type", "Application/octet-stream");//octet-stream
// Response.AddHeader("Content-Length", bts.Length.ToString());
// Response.AddHeader("Content-Disposition", "attachment; filename=1.pdf");
// Response.BinaryWrite(bts);
// Response.Flush();
// Response.TransmitFile(path);
// Response.End();
//}
}
private static System.IO.StringWriter GetHtmlTagsfromDatagrid(DataTable datatable)
{
System.IO.StringWriter stringWriter = new System.IO.StringWriter();
HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWriter);
DataGrid dg = new DataGrid();
dg.DataSource = datatable;
dg.DataBind();
dg.RenderControl(htmlWrite);
return stringWriter;
}
public void ExportarExcel(DataGrid grid)
{
this.Response.Clear();
this.Response.AddHeader("content-disposition", "attachment;filename=Arquivo.xls");
this.Response.Charset = "";
this.Response.Cache.SetCacheability(HttpCacheability.NoCache);
this.Response.ContentType = "application/vnd.xls";
StringWriter sWr = new StringWriter();
HtmlTextWriter hWr = new HtmlTextWriter(sWr);
grid.RenderControl(hWr);
this.Response.Write(sWr.ToString());
this.Response.End();
}
public override void ExecuteResult(ControllerContext context)
{
var Response = context.HttpContext.Response;
Response.Buffer = true;
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("Content-Disposition", "attachment;filename=CMSOrganizations.xls");
Response.Charset = "";
var d = m.OrganizationExcelList();
var dg = new DataGrid();
dg.DataSource = d;
dg.DataBind();
dg.RenderControl(new HtmlTextWriter(Response.Output));
}
public override void ExecuteResult(ControllerContext context)
{
var Response = context.HttpContext.Response;
Response.Buffer = true;
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("Content-Disposition", "attachment;filename=CMSStatusFlags.xls");
Response.Charset = "";
var collist = from ss in DbUtil.Db.ViewStatusFlagNamesRoles.ToList()
where ss.Role == null || HttpContext.Current.User.IsInRole(ss.Role)
select ss;
IEnumerable<string> cq = null;
if (flags.HasValue())
cq = from f in flags.Split(',')
join c in collist on f equals c.Flag
select "\tss.{0} as [{0}_{1}]".Fmt(c.Flag, c.Name);
else
cq = from c in collist
where c.Role == null || HttpContext.Current.User.IsInRole(c.Role)
select "\tss.{0} as [{1}]".Fmt(c.Flag, c.Name);
var tag = DbUtil.Db.PopulateSpecialTag(qid, DbUtil.TagTypeId_StatusFlags);
var cols = string.Join(",\n", cq);
var cn = new SqlConnection(Util.ConnectionString);
cn.Open();
var cmd = new SqlCommand(@"
SELECT
md.PeopleId,
md.[First],
md.[Last],
md.Age,
md.Marital,
md.Decision,
md.DecisionDt,
md.JoinDt,
md.Baptism,
" + cols + @"
FROM StatusFlagColumns ss
JOIN MemberData md ON md.PeopleId = ss.PeopleId
JOIN dbo.TagPerson tp ON tp.PeopleId = md.PeopleId
WHERE tp.Id = @p1", cn);
cmd.Parameters.AddWithValue("@p1", tag.Id);
var rd = cmd.ExecuteReader();
var dg = new DataGrid();
dg.DataSource = rd;
dg.DataBind();
dg.RenderControl(new HtmlTextWriter(Response.Output));
}
public static string Export(System.Data.DataTable dt, string fileName)
{
System.Web.UI.Page page = System.Web.HttpContext.Current.Handler as System.Web.UI.Page;
StringWriter stringWriter = new StringWriter();
HtmlTextWriter htmlWriter = new HtmlTextWriter(stringWriter);
DataGrid dGrid = new DataGrid();
TableItemStyle alternatingStyle = new TableItemStyle();
TableItemStyle headerStyle = new TableItemStyle();
TableItemStyle itemStyle = new TableItemStyle();
alternatingStyle.BackColor = Color.LightGray;
headerStyle.BackColor = Color.LightGray;
headerStyle.Font.Bold = true;
headerStyle.HorizontalAlign = HorizontalAlign.Center;
itemStyle.HorizontalAlign = HorizontalAlign.Center;
dGrid.GridLines = GridLines.Both;
dGrid.HeaderStyle.MergeWith(headerStyle);
dGrid.HeaderStyle.Font.Bold = true;
dGrid.AlternatingItemStyle.MergeWith(alternatingStyle);
dGrid.ItemStyle.MergeWith(itemStyle);
dGrid.DataSource = dt.DefaultView;
dGrid.DataBind();
dGrid.RenderControl(htmlWriter);
string filePath = Path.Combine(excelFullFolder, fileName + ext);
if (System.IO.File.Exists(filePath))
{
System.IO.File.Delete(filePath);
}
StreamWriter sw = new StreamWriter(filePath, false, System.Text.Encoding.UTF8);
sw.Write(stringWriter.ToString());
sw.Close();
int pos = page.Request.Url.ToString().LastIndexOf(page.Request.Path);
string fileUrl = page.Request.Url.ToString().Substring(0, pos);
fileUrl += page.Request.ApplicationPath + excelFolder.Replace("\\", "/") + fileName + ext;
HttpContext.Current.Response.Redirect(fileUrl);
return fileUrl;
}
protected void btnReport_Click(object sender, EventArgs e)
{
LogicaNegocio.AconpanaClase aconpanaClase = new LogicaNegocio.AconpanaClase();
// List<ModeloNegocio.Asesor> listUsuario = new List<ModeloNegocio.Asesor>();
// NOW ASSIGN DATA TO A DATAGRID.
DataGrid dg = new DataGrid();
dg.DataSource = aconpanaClase.getReportAllAconpanaClase();
dg.DataBind();
// THE EXCEL FILE.
string sFileName = "Segui-Clase-" + System.DateTime.Now.Date + "-.xls";
sFileName = sFileName.Replace("/", "");
// SEND OUTPUT TO THE CLIENT MACHINE USING "RESPONSE OBJECT".
Encoding encoding = Encoding.UTF8;
Response.ClearContent();
Response.Buffer = true;
//Response.Charset = encoding.EncodingName;
//Response.ContentEncoding = System.Text.Encoding.UTF8;
Response.Charset = encoding.EncodingName ;
Response.ContentEncoding = System.Text.Encoding.GetEncoding("Windows-1252");
Response.AddHeader("content-disposition", "attachment; filename=" + sFileName);
Response.ContentType = "application/vnd.ms-excel";
//Response.ContentType = "application/application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
//Response.AppendHeader("content-disposition", "attachment; filename=myfile.xlsx");
EnableViewState = false;
System.IO.StringWriter objSW = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter objHTW = new System.Web.UI.HtmlTextWriter(objSW);
dg.HeaderStyle.Font.Bold = true; // SET EXCEL HEADERS AS BOLD.
dg.HeaderStyle.ForeColor = Color.White;
dg.HeaderStyle.BackColor = Color.Blue;
dg.RenderControl(objHTW);
// STYLE THE SHEET AND WRITE DATA TO IT.
Response.Write("<style> TABLE { border:solid 1px #999; } " +
"TD { border:solid 1px #000000; text-align:center } </style>");
Response.Write(objSW.ToString());
// ADD A ROW AT THE END OF THE SHEET SHOWING A RUNNING TOTAL OF PRICE.
//Response.Write("<table><tr><td><b>Total: </b></td><td></td><td><b>" +
//dTotalPrice.ToString("N2") + "</b></td></tr></table>");
Response.End();
}
public static void DataGridToExcel(HttpResponse myResponse, DataGrid dg, string fileName, int numRowLock)
{
myResponse.Clear();
myResponse.Buffer = true;
myResponse.ContentType = "application/vnd.ms-excel";
myResponse.Charset = "";
myResponse.AddHeader("content-disposition", "attachment;filename=" + fileName + ".xls");
StringWriter writer = new StringWriter();
HtmlTextWriter writer2 = new HtmlTextWriter(writer);
dg.HeaderStyle.Height = 30;
dg.HeaderStyle.BackColor = Color.LightSlateGray;
MassageData(dg);
dg.RenderControl(writer2);
myResponse.End();
}
public static void subExportToExcel(System.Web.UI.Page objPage, DataSet dsData, string strFileName)
{
objPage.EnableViewState = false;
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.Charset = "";
HttpContext.Current.Response.Buffer = true;
HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
HttpContext.Current.Response.AddHeader("content-disposition", "attachment;filename=" + strFileName + ".xls");
System.IO.StringWriter swWriter = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter hwHtmlWriter = new System.Web.UI.HtmlTextWriter(swWriter);
System.Web.UI.WebControls.DataGrid dgData = new System.Web.UI.WebControls.DataGrid();
dgData.DataSource = dsData.Tables[0];
dgData.DataBind();
dgData.RenderControl(hwHtmlWriter);
HttpContext.Current.Response.Write(swWriter.ToString());
System.Web.HttpContext.Current.Response.End();
}
public void DataTableToExcel(System.Data.DataTable dtData)
{
string filename = DateTime.Now.ToString("yyyyMMddhhmmss") + ".xls";
System.Web.UI.WebControls.DataGrid dgExport = null;
// ��ǰ�Ի�
System.Web.HttpContext curContext = System.Web.HttpContext.Current;
// IO���ڵ���������excel�ļ�
System.IO.StringWriter strWriter = null;
System.Web.UI.HtmlTextWriter htmlWriter = null;
if (dtData != null)
{
// ���ñ��������ʽ
//Response.ContentTypeָ���ļ����� ����Ϊapplication/ms-excel��application/ms-word��application/ms-txt��application/ms-html
curContext.Response.ContentType = "application/vnd.ms-excel";
curContext.Response.ContentEncoding = System.Text.Encoding.UTF8;
System.Web.HttpContext.Current.Response.Charset = "GB2312";
//�������к���Ҫ�� attachment ������ʾ��Ϊ�������أ������Ըij� online���ߴ�
//filename=FileFlow.xls ָ������ļ������ƣ�ע������չ����ָ���ļ��������������Ϊ��.doc .xls .txt��.htm����
System.Web.HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + System.Web.HttpUtility.UrlEncode(filename, Encoding.GetEncoding("utf-8")));
// curContext.Response.Charset = "";
// ����excel�ļ�
strWriter = new System.IO.StringWriter();
htmlWriter = new System.Web.UI.HtmlTextWriter(strWriter);
// Ϊ�˽��dgData�п��ܽ����˷�ҳ���������Ҫ���¶���һ����ҳ��DataGrid
dgExport = new System.Web.UI.WebControls.DataGrid();
dgExport.DataSource = dtData.DefaultView;
dgExport.AllowPaging = false;
//���֤�ŵĴ���Ĺؼ��ڴ��¼�
dgExport.ItemDataBound += new System.Web.UI.WebControls.DataGridItemEventHandler(gridHrInfo_ItemDataBound1);
dgExport.DataBind();
// ���ؿͻ���
dgExport.RenderControl(htmlWriter);
curContext.Response.Write(strWriter.ToString());
//curContext.flush();
curContext.Response.End();
}
}
//Exporting to Excel
public void ExportToExcel(DataTable dt)
{
if (dt.Rows.Count > 0)
{
string filename = "ClientJobs.xls";
string excelHeader = "Report generated by :" + strArrUser[0].ToString() + " ";
System.IO.StringWriter tw = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw);
DataGrid dgGrid = new DataGrid();
dgGrid.DataSource = dt;
dgGrid.DataBind();
dgGrid.HeaderStyle.BackColor = System.Drawing.Color.LightBlue;
dgGrid.HeaderStyle.Font.Bold = true;
dgGrid.GridLines = GridLines.Both;
// Report Header
/*arivu*/
hw.WriteLine("<table>");
hw.WriteLine("<tr><td><b><u><font size=’3′> " + excelHeader + " </font></u></b></td></tr>");
hw.WriteLine("<tr><td><b><u><font size=’3′> " + "Client Type:" + lbClientType.Text.ToUpper() + " </font></u></b></td><td><b><u><font size=’3′> " + "Client Name:" + lbClientName.Text.ToUpper() + " </font></u></b></td>");
hw.WriteLine("</table>");
/**/
//Get the HTML for the control.
dgGrid.RenderControl(hw);
Response.ContentType = "application/vnd.ms-excel";
Response.AppendHeader("Content-Disposition", "attachment; filename=" + filename + "");
//this.EnableViewState = false;
Response.Write(tw.ToString());
Response.End();
}
}
protected override void RenderContents(HtmlTextWriter writer)
{
base.RenderContents(writer);
String sql = "select * from Vendors";
SqlConnection conn = new SqlConnection("Data Source=ABCUNIVERSITY\\SHAREPOINT;Initial Catalog=ABCPurchasing;User Id=dbu;Password=test");
DataSet ds = new DataSet("root");
SqlDataAdapter sda = new SqlDataAdapter(sql, conn);
sda.Fill(ds, "Vendors");
grid = new DataGrid();
grid.HeaderStyle.CssClass = "msh-vh2";
grid.CellPadding = 2;
grid.DataSource = ds;
grid.DataMember = "Vendors";
grid.DataBind();
Controls.Add(grid);
grid.RenderControl(writer);
}
public void Test_DataGrid()
{
PrecisionModel pm = new PrecisionModel(100000,0,0);
GeometryFactory geometryFactory = new GeometryFactory(pm,-1);
string filename= Global.GetUnitTestRootDirectory()+@"\IO\Shapefile\Testfiles\statepop";
ShapefileDataReader shpDataReader = Geotools.IO.Shapefile.CreateDataReader(filename, geometryFactory);
// make sure the datagrid gets the column headings.
DataGrid grid = new DataGrid();
grid.DataSource = shpDataReader;
grid.DataBind();
TextWriter tempWriter = new StringWriter();
grid.RenderControl(new HtmlTextWriter(tempWriter));
string html = tempWriter.ToString();
bool same = Compare.CompareAgainstString(Global.GetUnitTestRootDirectory()+@"\IO\Shapefile\Testfiles\ExpectedDataGridDataReader.txt",html);
Assertion.AssertEquals("Datagrid properties",true,same);
}
public override void ExecuteResult(ControllerContext context)
{
var Response = context.HttpContext.Response;
Response.Buffer = true;
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("Content-Disposition", "attachment;filename=CMSPeople.xls");
Response.Charset = "";
var q = from p in DbUtil.Db.PeopleQuery(qid)
let p2 = p.Family.People.FirstOrDefault(pp => pp.PositionInFamilyId == 10 && pp.PeopleId != p.PeopleId)
let name = DbUtil.Db.PeopleExtras.SingleOrDefault(ee => ee.Field == "name" && ee.PeopleId == p.PeopleId).Data
let web = DbUtil.Db.PeopleExtras.SingleOrDefault(ee => ee.Field == "web" && ee.PeopleId == p.PeopleId).Data
select new
{
Organization = name != "name" ? name : p.LastName,
FirstName = p2.PreferredName,
p2.LastName,
Email = p2.EmailAddress,
Street = p.PrimaryAddress,
Street2 = p.PrimaryAddress2,
City = p.PrimaryCity,
Province = p.PrimaryState,
Country = "USA",
PostalCode = p.PrimaryZip,
BusPhone = p.WorkPhone.FmtFone(),
MobPhone = p2.CellPhone.FmtFone(),
Fax = "",
SecStreet = p.AddressLineOne,
SecStreet2 = p.AddressLineTwo,
SecCity = p.CityName,
SecProvince = p.StateCode,
SecCountry = "USA",
SecPostalCode = p.ZipCode,
Notes = "DbName: " + p.LastName + "\nWeb:" + web
};
var dg = new DataGrid();
dg.DataSource = q;
dg.DataBind();
dg.RenderControl(new HtmlTextWriter(Response.Output));
}
public static void OutExcel(Page myPage, DataGrid dg, string name, string codetype)
{
if (codetype == "")
{
codetype = "GB2312";
}
HttpResponse response = myPage.Response;
string str = "attachment;filename=" + HttpContext.Current.Server.UrlEncode(name) + ".xls";
dg.Visible = true;
response.Clear();
response.Buffer = true;
response.Charset = codetype;
response.AppendHeader("Content-Disposition", str);
response.ContentEncoding = Encoding.GetEncoding(codetype);
response.ContentType = "application/ms-excel";
dg.EnableViewState = false;
StringWriter writer = new StringWriter();
HtmlTextWriter writer2 = new HtmlTextWriter(writer);
dg.RenderControl(writer2);
response.Write(writer.ToString());
response.End();
}
public static void Convert(System.Data.DataSet ds, string ExcelName)
{
//try
//{
//first let's clean up the response.object
HttpResponse response = HttpContext.Current.Response;
response.Clear();
response.Charset = "";
//set the response mime type for excel
response.ContentType = "application/vnd.ms-excel";
//create a string writer
//response.AddHeader("Content-Disposition", "attachment;filename=Shilpa.xls");
response.AddHeader("Content-Disposition", "attachment;filename=" + ExcelName + ".xls");
System.IO.StringWriter stringWrite = new System.IO.StringWriter();
//create an htmltextwriter which uses the stringwriter
System.Web.UI.HtmlTextWriter htmlWrite = new System.Web.UI.HtmlTextWriter(stringWrite);
//instantiate a datagrid
System.Web.UI.WebControls.DataGrid dg = new System.Web.UI.WebControls.DataGrid();
//set the datagrid datasource to the dataset passed in
dg.DataSource = ds.Tables[0];
//bind the datagrid
dg.DataBind();
//tell the datagrid to render itself to our htmltextwriter
dg.RenderControl(htmlWrite);
//all that's left is to output the html
response.Write(stringWrite.ToString());
response.Flush();
response.End();
//}
//catch (Exception ex)
//{
// throw ex;
//}
}
protected void lnkExportToExcel_Click(object sender, EventArgs e)
{
try
{
DataTable dtb = new DataTable();
dtb = segment07ListBAL.GetSegment07ListForExportToExcel();
string exportedFileName = "Segment07_" + System.DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
Response.Clear();
Response.Charset = "";
Response.ContentType = "application/ms-excel";
Response.Buffer = true;
Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", exportedFileName));
System.IO.StringWriter stringWrite = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter htmlWrite = new System.Web.UI.HtmlTextWriter(stringWrite);
System.Web.UI.WebControls.DataGrid dg = new System.Web.UI.WebControls.DataGrid();
dg.AllowPaging = false;
dg.HeaderStyle.BackColor = System.Drawing.Color.RoyalBlue;
dg.HeaderStyle.ForeColor = System.Drawing.Color.White;
dg.DataSource = dtb;
dg.DataBind();
dg.RenderControl(htmlWrite);
Response.Write(stringWrite.ToString());
Response.End();
}
catch (Exception exp)
{
logExcpUIobj.MethodName = "lnkExportToExcel_Click()";
logExcpUIobj.ResourceName = "System_Settings_Segment07List.CS";
logExcpUIobj.RecordId = "Export To excel";
logExcpUIobj.ExceptionDetails = "Error Occured. System Generated Error is: " + exp.ToString();
logExcpDALobj.SaveExceptionToDB(logExcpUIobj);
log.Error("[System_Settings_Segment07List : lnkExportToExcel_Click] An error occured in the processing of Records. Details : [" + exp.ToString() + "]");
}
}
/// <summary>
/// 导出EXCEL（HTML格式的）
/// </summary>
/// <param name="dt">要导出的DataTable</param>
/// <param name="fileName"></param>
public static void CreateEXCEL(DataTable dt, string fileName, string RequestBrowser)
{
System.Web.UI.WebControls.DataGrid dg = new DataGrid();
dg.DataSource = dt;
dg.DataBind();
for (int i = 0; i < dg.Items.Count; i++)
{
for (int j = 0; j < dg.Items[i].Cells.Count; j++)
{
dg.Items[i].Cells[j].Attributes.Add("style", "vnd.ms-excel.numberformat:@");
}
}
System.Web.HttpResponse Response = System.Web.HttpContext.Current.Response;
Response.Clear();
Response.Buffer = true;
Response.Charset = "UTF-8";
if (RequestBrowser == "IE")
{
Response.AppendHeader("Content-Disposition", "attachment;filename=\"" + HttpUtility.UrlEncode(fileName + ".xls", System.Text.Encoding.UTF8) + "\"");
}
else
{
Response.AppendHeader("Content-Disposition", "attachment;filename=\"" + fileName + ".xls\"");
}
Response.ContentEncoding = System.Text.Encoding.GetEncoding("UTF-8");
Response.ContentType = "application/ms-excel";//设置输出文件类型为excel文件。
System.Globalization.CultureInfo myCItrad = new System.Globalization.CultureInfo("ZH-CN", true);
System.IO.StringWriter oStringWriter = new System.IO.StringWriter(myCItrad);
System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
System.Web.UI.WebControls.DataGrid DG = dg;
DG.RenderControl(oHtmlTextWriter);
Response.Write(oStringWriter.ToString());
Response.End();
}
public override void ExecuteResult(ControllerContext context)
{
var Response = context.HttpContext.Response;
Response.Buffer = true;
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("Content-Disposition", "attachment;filename=CMSStatusFlags.xls");
Response.Charset = "";
var tag = DbUtil.Db.PopulateSpecialTag(qid, DbUtil.TagTypeId_StatusFlags);
var roles = CMSRoleProvider.provider.GetRolesForUser(Util.UserName);
var cmd = new SqlCommand("dbo.StatusGrid @p1");
cmd.Parameters.AddWithValue("@p1", tag.Id);
cmd.Connection = new SqlConnection(Util.ConnectionString);
cmd.Connection.Open();
var dg = new DataGrid();
dg.DataSource = cmd.ExecuteReader();
dg.DataBind();
dg.RenderControl(new HtmlTextWriter(Response.Output));
}
public override void ExecuteResult(ControllerContext context)
{
var Response = context.HttpContext.Response;
Response.Buffer = true;
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("Content-Disposition", "attachment;filename=CMSTasks.xls");
Response.Charset = "";
var q = DbUtil.Db.PeopleQuery(qid);
var q2 = from p in q
let t = p.TasksAboutPerson.OrderByDescending(t => t.CreatedOn).FirstOrDefault(t => t.Notes != null)
where t != null
select new
{
p.Name,
t.Notes,
t.CreatedOn
};
var dg = new DataGrid();
dg.DataSource = q2;
dg.DataBind();
dg.RenderControl(new HtmlTextWriter(Response.Output));
}
public static void TOExcel(HttpResponse Response, IList DataSource, string FileName)
{
Response.Clear();
Response.AddHeader("content-disposition", string.Format("attachment;filename={0}", FileName));
Response.ContentEncoding = Encoding.UTF8;
Response.Charset = "";
// If you want the option to open the Excel file without saving than
// comment out the line below
Response.Cache.SetCacheability(HttpCacheability.NoCache);
Response.ContentType = "excel/ms-excel";
StringWriter stringWrite = new StringWriter();
HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
DataGrid grid = new DataGrid();
grid.HeaderStyle.Font.Bold = true;
grid.AllowPaging = false;
grid.DataSource = DataSource;
grid.DataBind();
grid.RenderControl(htmlWrite);
Response.Write(stringWrite.ToString());
Response.End();
}
public static void DataTable2Excel(System.Data.DataTable dtData)
{
System.Web.UI.WebControls.DataGrid dgExport = null;
// 当前对话
System.Web.HttpContext curContext = System.Web.HttpContext.Current;
// IO用于导出并返回excel文件
System.IO.StringWriter strWriter = null;
System.Web.UI.HtmlTextWriter htmlWriter = null;
//设置导出格式
string style = @"<style> .text {mso-number-format:\@} </script>";
if (dtData != null)
{
// 设置编码和附件格式
curContext.Response.ContentType = "application/vnd.ms-excel";
curContext.Response.AddHeader("content-disposition", "attachment;");
curContext.Response.ContentEncoding = System.Text.Encoding.UTF8;
curContext.Response.Charset = "";
// 导出excel文件
strWriter = new System.IO.StringWriter();
htmlWriter = new System.Web.UI.HtmlTextWriter(strWriter);
// 为了解决dgData中可能进行了分页的情况，需要重新定义一个无分页的DataGrid
dgExport = new System.Web.UI.WebControls.DataGrid();
dgExport.DataSource = dtData.DefaultView;
dgExport.AllowPaging = false;
dgExport.DataBind();
// 返回客户端
curContext.Response.Write(style);
dgExport.RenderControl(htmlWriter);
curContext.Response.Write(strWriter.ToString());
curContext.Response.End();
}
}
protected void generar(Object Sender, EventArgs e)
{
string [] pr = new string[2];
pr[0] = pr[1] = "";
Press frontEnd = new Press(new DataSet(), reportTitle);
frontEnd.PreHeader(tabPreHeader, Grid.Width, pr);
frontEnd.Firmas(tabFirmas, Grid.Width);
lb.Text = "";
//////////////FECHA////////////
int Año1 = Convert.ToInt32(añoI.SelectedValue.ToString());
int Año2 = Convert.ToInt32(añoF.SelectedValue.ToString());
int Mes1 = Convert.ToInt32(DBFunctions.SingleData("select PMES_MES from DBXSCHEMA.PMES WHERE PMES_NOMBRE='" + mesI.SelectedValue.ToString() + "'"));
int Mes2 = Convert.ToInt32(DBFunctions.SingleData("select PMES_MES from DBXSCHEMA.PMES WHERE PMES_NOMBRE='" + mesF.SelectedValue.ToString() + "'"));
int dia1 = 1;
int dia2 = 1;
string dia1S = diaI.Text;
string dia2S = diaF.Text;
if (dia1S.ToString().Equals(null) || dia1S.ToString().Equals("0"))
{
dia1 = 1;
}
else
{
dia1 = Convert.ToInt32(diaI.Text.ToString());
}
if (dia2S.ToString().Equals(null) || dia2S.ToString().Equals("0"))
{
dia2 = Convert.ToInt32(DateTime.Now.Day);
}
else
{
dia2 = Convert.ToInt32(diaF.Text.ToString());
}
string fecha = Año1 + "-" + Mes1 + "-" + dia1;
string fecha2 = Año2 + "-" + Mes2 + "-" + dia2;
///////////////////////////////
/////opciones de estado comparendo
if (pago.Checked.Equals(true))
{
queryEstado = "AND MCOM_ESTADO =1";
}
if (nopago.Checked.Equals(true))
{
queryEstado = "AND MCOM_ESTADO =3";
}
if (proceso.Checked.Equals(true))
{
queryEstado = "AND MCOM_ESTADO =2";
}
if (todos.Checked.Equals(true))
{
queryEstado = " ";
}
//////////////////////////////////////////////////
////seleccion conductor
conductoresS = conductor.SelectedValue.ToString();
if (conductoresS.Equals("0"))
{
queryF = "select MCOM_FECHACOM,MCOM_INFRACCION,MCOM_CONDUCTOR,MCOM_ESTADO,MCOM_NUMCOM FROM DBXSCHEMA.MCOMPARENDOS WHERE MCOM_FECHACOM BETWEEN '" + fecha + "' AND '" + fecha2 + "' " + queryEstado + " ";
}
else
{
string nit = DBFunctions.SingleData("select MNIT.MNIT_NIT FROM DBXSCHEMA.MNIT MNIT WHERE MNIT.MNIT_NOMBRES CONCAT ' ' CONCAT coalesce(MNIT.MNIT_NOMBRE2,'') CONCAT ' ' CONCAT MNIT.MNIT_APELLIDOS CONCAT ' 'CONCAT coalesce(MNIT.MNIT_APELLIDO2,'') ='" + conductoresS + "' ");
queryF = "select MCOM_FECHACOM,MCOM_INFRACCION,MCOM_CONDUCTOR,MCOM_ESTADO,MCOM_NUMCOM FROM DBXSCHEMA.MCOMPARENDOS WHERE MCOM_FECHACOM BETWEEN '" + fecha + "' AND '" + fecha2 + "' " + queryEstado + " AND MCOM_CONDUCTOR='" + nit + "' ";
}
///////////////////////////////////////////////////////
this.PrepararTabla();
lineas = new DataSet();
DBFunctions.Request(lineas, IncludeSchema.NO, " " + queryF + " ");
for (int i = 0; i < lineas.Tables[0].Rows.Count; i++)
{
string infraccion = null;
int codigoinfraccion = 0;
double valorcomparendo = 0;
codigoinfraccion = Convert.ToInt32(lineas.Tables[0].Rows[i].ItemArray[1].ToString());
infraccion = DBFunctions.SingleData("Select DESC_COMPARENDO from DBXSCHEMA.TCOMPARENDO WHERE COD_COMPARENDO=" + codigoinfraccion + " ");
valorcomparendo = Convert.ToDouble(DBFunctions.SingleData("Select VALO_COMPARENDO from DBXSCHEMA.TCOMPARENDO WHERE COD_COMPARENDO=" + codigoinfraccion + " "));
string estadoIn = null;
int estadoInfraccion = Convert.ToInt32(lineas.Tables[0].Rows[i].ItemArray[3].ToString());
estadoIn = DBFunctions.SingleData("Select DESC_ESTACOMPA from DBXSCHEMA.TESTADO_COMPARENDO WHERE COD_ESTACOMPA=" + estadoInfraccion + " ");
string nombreco = DBFunctions.SingleData("select MNIT.MNIT_NOMBRES CONCAT ' ' CONCAT coalesce(MNIT.MNIT_NOMBRE2,'') CONCAT ' ' CONCAT MNIT.MNIT_APELLIDOS CONCAT ' 'CONCAT coalesce(MNIT.MNIT_APELLIDO2,'') AS NOMBRE FROM DBXSCHEMA.MNIT MNIT WHERE MNIT.MNIT_NIT='" + lineas.Tables[0].Rows[i].ItemArray[2].ToString() + "' ");
//Vamos a crear una fila para nuestro DataTable resultado, que almacene los resultados de las operaciones anteriores
DataRow fila;
fila = resultado.NewRow();
string ValorFormato = null;
ValorFormato = String.Format("{0:C}", valorcomparendo);
fila["FECHA"] = lineas.Tables[0].Rows[i].ItemArray[0].ToString();
fila["NUMCOMPARENDO"] = lineas.Tables[0].Rows[i].ItemArray[4].ToString();
fila["INFRACCION"] = infraccion.ToString();
fila["VALOR"] = ValorFormato.ToString();
fila["CONDUCTOR"] = nombreco.ToString();
fila["ESTADO"] = estadoIn.ToString();
resultado.Rows.Add(fila);
}
//fin sentencia FOR
Grid.DataSource = resultado;
Grid.DataBind();
StringBuilder SB = new StringBuilder();
StringWriter SW = new StringWriter(SB);
HtmlTextWriter htmlTW = new HtmlTextWriter(SW);
tabPreHeader.RenderControl(htmlTW);
Grid.RenderControl(htmlTW);
tabFirmas.RenderControl(htmlTW);
string strRep;
strRep = SB.ToString();
Session.Clear();
Session["Rep"] = strRep;
toolsHolder.Visible = true;
}
protected void generar(Object Sender, EventArgs e)
{
string [] pr = new string[2];
pr[0] = pr[1] = "";
Press frontEnd = new Press(new DataSet(), reportTitle);
frontEnd.PreHeader(tabPreHeader, Grid.Width, pr);
frontEnd.Firmas(tabFirmas, Grid.Width);
lb.Text = "";
///////////////////////////////////////////////////////
this.PrepararTabla();
lineas = new DataSet();
string fecha = Convert.ToDateTime(FechaInicio.Text).ToString("yyyy-MM-dd");
string fecha1 = Convert.ToDateTime(FechaFinal.Text).ToString("yyyy-MM-dd");
try
{
DBFunctions.Request(lineas, IncludeSchema.NO, "select distinct mbus.mbus_numero as NUMEROBUS,drut.mcat_placa as PLACA,tciu.tciu_nombre as CIUDADORIGEN,tciu1.tciu_nombre as CIUDADDESTINO,mrut.mrut_valor as VALOR,drut.drut_horasal as HORASALIDA,drut.drut_fecha as FECHA,drut.drut_codigo as CODIGORUTA,drut.drut_planilla as NUMEROPLANILLA,drut.drut_codrelevante as RELEVANTE from dbxschema.druta drut,dbxschema.druta drut1,dbxschema.dtiquete dtiq,dbxschema.mruta mrut, dbxschema.tciudad tciu,dbxschema.tciudad tciu1,dbxschema.tciudad tciu2,dbxschema.mbusafiliado mbus where drut.drut_fecha between '" + fecha + "' and '" + fecha1 + "' and drut.drut_codigo=drut.drut_codigo and drut.mrut_codigo=mrut.mrut_codigo and mrut.tciu_cod=tciu.tciu_codigo and mrut.tciu_coddes=tciu1.tciu_codigo and mbus.mcat_placa=drut.mcat_placa and tciu.tciu_nombre=(select tciu.TCIU_nombre from dbxschema.MOFICINA mofi,dbxschema.tciudad tciu where mofi_codigo='" + ddlagencia.SelectedValue + "'and tciu.tciu_codigo=mofi.tciu_codigo)order by drut.drut_fecha,tciu1.tciu_nombre");
}
catch (Exception t)
{
Response.Write("<script language:javascript>alert('No se Encuentra Ningun Reporte De esta Bus'); </script>" + t.Message);
}
for (int i = 0; i < lineas.Tables[0].Rows.Count; i++)
{
//Vamos a crear una fila para nuestro DataTable resultado, que almacene los resultados de las operaciones anteriores
string lineas1 = DBFunctions.SingleData("Select count (*) from DBXSCHEMA.DTIQUETE where drut_codigo=" + lineas.Tables[0].Rows[i][7].ToString() + "");
string lineas2 = DBFunctions.SingleData("Select count(*)from dbxschema.dtiquete where drut_codigo=" + lineas.Tables[0].Rows[i].ItemArray[7].ToString() + " and test_codigo='RS'");
int puestosre = 0;
int puestos = 0;
if (lineas2.Length == 0)
{
puestosre = 0;
puestos = Convert.ToInt32(lineas1);
}
else
{
puestos = Convert.ToInt32(lineas1);
puestosre = Convert.ToInt32(lineas2);
}
string capacidad = DBFunctions.SingleData("SELECT pcat.pcat_capacidad FROM dbxschema.pcatalogovehiculo pcat,dbxschema.druta druta,dbxschema.mruta mruta,dbxschema.mbusafiliado mbus,dbxschema.mcatalogovehiculo mcat WHERE druta.drut_codigo=" + lineas.Tables[0].Rows[i][7].ToString() + " and druta.mrut_codigo=mruta.mrut_codigo and druta.mcat_placa=mbus.mcat_placa and mbus.mcat_placa=mcat.mcat_placa and pcat.pcat_codigo=mcat.pcat_codigo");
string disponibilidad = ((System.Convert.ToInt32(capacidad)) - (System.Convert.ToInt32(DBFunctions.SingleData("SELECT COUNT(*) FROM dpuestoruta WHERE drut_codigo=" + lineas.Tables[0].Rows[i][7].ToString() + " AND (test_codigo<>'LI')").Trim()))).ToString();
DataRow fila;
fila = resultado.NewRow();
fila["NUMERO BUS"] = lineas.Tables[0].Rows[i].ItemArray[0].ToString();
fila["PLACA"] = lineas.Tables[0].Rows[i].ItemArray[1].ToString();
fila["ORIGEN"] = lineas.Tables[0].Rows[i].ItemArray[2].ToString();
fila["DESTINO"] = lineas.Tables[0].Rows[i].ItemArray[3].ToString();
fila["VALOR"] = lineas.Tables[0].Rows[i].ItemArray[4].ToString();
fila["HORA SALIDA"] = lineas.Tables[0].Rows[i].ItemArray[5].ToString();
fila["FECHA"] = lineas.Tables[0].Rows[i].ItemArray[6].ToString();
fila["CODIGO RUTA"] = lineas.Tables[0].Rows[i].ItemArray[7].ToString();
fila["NUMERO PLANILLA"] = lineas.Tables[0].Rows[i].ItemArray[8].ToString();
fila["RELEVANTE"] = lineas.Tables[0].Rows[i].ItemArray[9].ToString();
fila["NUMERO DE PASAJEROS"] = puestos - puestosre;
fila["DISPONIBLES"] = disponibilidad;
fila["TOTAL"] = puestos * Convert.ToDouble(lineas.Tables[0].Rows[i].ItemArray[4]);
resultado.Rows.Add(fila);
}
//fin sentencia FOR
Grid.DataSource = resultado;
Grid.DataBind();
StringBuilder SB = new StringBuilder();
StringWriter SW = new StringWriter(SB);
HtmlTextWriter htmlTW = new HtmlTextWriter(SW);
tabPreHeader.RenderControl(htmlTW);
Grid.RenderControl(htmlTW);
tabFirmas.RenderControl(htmlTW);
string strRep;
strRep = SB.ToString();
Session.Clear();
Session["Rep"] = strRep;
toolsHolder.Visible = true;
}
private void imgWord_Click(object sender, System.Web.UI.ImageClickEventArgs e)
{
Response.Clear();
Response.Charset = "";
//set the response mime type for excel
Response.ContentType = "application/vnd.ms-word";
Response.AddHeader("content-disposition","attachment; filename=QueryResults.doc");
DataGrid dgQuery = new DataGrid();
DataGrid dgQuery2 = new DataGrid();
if (IsDefault == true)
{
dgQuery.DataSource = DSOrders.tbl_Orders;
dgQuery.DataBind();
dgQuery2.DataSource = DSOrders.tbl_OrderDetails;
dgQuery2.DataBind();
}
else
{
dgQuery.DataSource = dsBrowseGrid.Tables["Data"].DefaultView;
dgQuery.ItemDataBound += new DataGridItemEventHandler(dgQuery_ItemDataBound);
dgQuery.DataBind();
}
//create a string writer
System.IO.StringWriter stringWrite = new StringWriter();
// Ceate an htmltextwriter which uses the stringwriter
System.Web.UI.HtmlTextWriter htmlWriter = new System.Web.UI.HtmlTextWriter(stringWrite);
dgQuery.RenderControl(htmlWriter);
if (IsDefault == true)
{
dgQuery2.RenderControl(htmlWriter);
}
// Output table as HTML
Response.Write(stringWrite.ToString());
Response.End();
}
/// <summary>
/// 数据导出到Excel
/// </summary>
/// <returns></returns>
private void OutputDataToExcle(string strFilename)
{
string sWhereSQL = " And a.OpCode='" + SysClass.SysGlobal.GetCurrentOpCode() + "' And SignStatus=1"
+ " And a.SalaryRecGuid in (Select SalaryRecGuid From UserUserImportRec_Info where ApprovalStatus=1)";
//月份起始和月份结束同时不为空时，按区间查找数据
if (SysClass.SysUserSalary.UserSalary_SearchText.Length > 0 && SysClass.SysUserSalary.UserSalary_SearchText2.Length > 0)
{
sWhereSQL += " And a.SalaryYears >= '" + SysClass.SysUserSalary.UserSalary_SearchText + "'";
sWhereSQL += " And a.SalaryYears <= '" + SysClass.SysUserSalary.UserSalary_SearchText2 + "'";
}
//仅月份起始不为空时，只按月份起始查找数据
else if (SysClass.SysUserSalary.UserSalary_SearchText.Length > 0)
{
sWhereSQL += " And a.SalaryYears = '" + SysClass.SysUserSalary.UserSalary_SearchText + "'";
}
//仅月份结束不为空时，只按月份结束查找数据
else if (SysClass.SysUserSalary.UserSalary_SearchText2.Length > 0)
{
sWhereSQL += " And a.SalaryYears = '" + SysClass.SysUserSalary.UserSalary_SearchText2 + "'";
}
//Add by lk 20160118 start
switch (SysClass.SysUserSalary.UserSalary_FlgValue)
{
//没有选中任何一项
case 0:
sWhereSQL += " And c.Description = ''";
break;
//仅【工资】被选中
case 1:
sWhereSQL += " And c.Description like '%" + chkGongzi.Text + "%'";
break;
//仅【奖金】被选中
case 2:
sWhereSQL += " And c.Description like '%" + chkJiangjin.Text + "%'";
break;
//【工资】和【奖金】被选中
case 3:
sWhereSQL += " And (c.Description like '%" + chkGongzi.Text + "%'";
sWhereSQL += " or c.Description like '%" + chkJiangjin.Text + "%')";
break;
//仅【报销】被选中
case 4:
sWhereSQL += " And c.Description like '%" + chkBaoxiao.Text + "%'";
break;
//【工资】和【报销】被选中
case 5:
sWhereSQL += " And (c.Description like '%" + chkGongzi.Text + "%'";
sWhereSQL += " or c.Description like '%" + chkBaoxiao.Text + "%')";
break;
//【奖金】和【报销】被选中
case 6:
sWhereSQL += " And (c.Description like '%" + chkJiangjin.Text + "%'";
sWhereSQL += " or c.Description like '%" + chkBaoxiao.Text + "%')";
break;
//【工资】【奖金】【报销】都被选中
case 7:
break;
}
//Add by lk 20160118 end
DataSet ds = SysClass.SysUserSalary.GetUserSalaryLstByDataSet(sWhereSQL);
//创建DataTable对象
DataTable dt = new DataTable("output");
//构建表结构,往表中添加列
dt.Columns.Add("序号", typeof(int));
dt.Columns.Add("工号", typeof(string));
dt.Columns.Add("姓名", typeof(string));
dt.Columns.Add("工资年月", typeof(string));
dt.Columns.Add("发放金额", typeof(decimal));
dt.Columns.Add("签收状态", typeof(string));
dt.Columns.Add("签收时间", typeof(string));
dt.Columns.Add("备注", typeof(string));
//清空合计结果
mysum = 0;
for (int iLoop = 0; iLoop < ds.Tables[0].Rows.Count; iLoop++)
{
//创建DataRow对象
DataRow dr = dt.NewRow();
//新增数据
dr[0] = iLoop + 1;
dr[1] = ds.Tables[0].Rows[iLoop]["OpCode"].ToString();
dr[2] = ds.Tables[0].Rows[iLoop]["OpName"].ToString();
dr[3] = ds.Tables[0].Rows[iLoop]["SalaryYears"].ToString();
dr[4] = ds.Tables[0].Rows[iLoop]["TotalSalary"].ToString();
if (ds.Tables[0].Rows[iLoop]["SignStatus"].ToString() == "1")
{
dr[5] = "已签收";
}
else
{
dr[5] = "未签收";
}
dr[6] = ds.Tables[0].Rows[iLoop]["SignDate"].ToString();
dr[7] = ds.Tables[0].Rows[iLoop]["Description"].ToString();
//把数据添加到表结构中
dt.Rows.Add(dr);
//合计结果累加
mysum += double.Parse(ds.Tables[0].Rows[iLoop]["TotalSalary"].ToString());
}
//添加合计部分数据
DataRow foot = dt.NewRow();
foot[3] = "总合计：";
foot[4] = mysum.ToString("0.00");
dt.Rows.Add(foot);
System.Web.UI.WebControls.DataGrid dgExport = null;
// 当前对话
System.Web.HttpContext curContext = System.Web.HttpContext.Current;
// IO用于导出并返回excel文件
System.IO.StringWriter strWriter = null;
System.Web.UI.HtmlTextWriter htmlWriter = null;
// 设置编码和附件格式
curContext.Response.ContentType = "application/vnd.ms-excel";
curContext.Response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312");
curContext.Response.Charset = "gb2312";
// 导出excel文件
strWriter = new System.IO.StringWriter();
htmlWriter = new System.Web.UI.HtmlTextWriter(strWriter);
// 为了解决dgData中可能进行了分页的情况，需要重新定义一个无分页的DataGrid
dgExport = new System.Web.UI.WebControls.DataGrid();
dgExport.DataSource = dt.DefaultView;
dgExport.AllowPaging = false;
dgExport.DataBind();
// 返回客户端
dgExport.RenderControl(htmlWriter);
curContext.Response.Clear();
curContext.Response.Buffer = true;
curContext.Response.Write("上海动车段工资电子签收系统");
curContext.Response.AddHeader("Content-Disposition", "attachment;filename=" + strFilename);
curContext.Response.Write("<meta http-equiv=\"content-type\" content=\"application/ms-excel; charset=gb2312\"/>" + strWriter.ToString());
curContext.Response.Flush();
curContext.Response.End();
}
public void generar(Object Sender, EventArgs e)
{
string [] pr = new string[2];
pr[0] = pr[1] = "";
Press frontEnd = new Press(new DataSet(), reportTitle);
frontEnd.PreHeader(tabPreHeader, Grid.Width, pr);
frontEnd.Firmas(tabFirmas, Grid.Width);
lb.Text = "";
Año = Convert.ToInt32(año.SelectedValue.ToString());
Mes = Convert.ToInt32(DBFunctions.SingleData("SELECT pmes_mes FROM DBXSCHEMA.pmes WHERE pmes_nombre='" + mes.SelectedValue.ToString() + "' "));
dia = Convert.ToInt32(Dia.Text.ToString());
///////////////////////////////////////////////////////
this.PrepararTabla();
this.PrepararTabla1();
this.PrepararTabla2();
this.PrepararTabla4();
lineas = new DataSet();
lineas2 = new DataSet();
lineas3 = new DataSet();
lineas4 = new DataSet();
DBFunctions.Request(lineas, IncludeSchema.NO, "Select MREM.MCAT_PLACA AS PLACA,MREM.NUM_REM AS NUMERO,MREM.VALO_FLET AS REMESA from DBXSCHEMA.MREMESA MREM where year(MREM_FECHA)=" + Año + " AND MONTH(MREM_FECHA)=" + Mes + " AND DAY(MREM_FECHA)=" + dia + " "); //remesa
DBFunctions.Request(lineas2, IncludeSchema.NO, "SELECT MAN.MCAT_PLACA AS PLACA,MAN.MAN_CODIGO AS NUMERO,MAN.MAN_VALOTOTAL AS VALOR from DBXSCHEMA.MANTICIPO MAN WHERE year(MAN_FECHA)=" + Año + " AND MONTH(MAN_FECHA)=" + Mes + " AND DAY(MAN_FECHA)=" + dia + " "); //anticipo
DBFunctions.Request(lineas3, IncludeSchema.NO, "select MAU.MCAT_PLACA AS PLACA,MAU.MAU_CODIGO AS NUMERO,MAU.MAU_VALOTOT AS VALOR from DBXSCHEMA.MAUTORIZACION MAU WHERE year(MAU_FECHA)=" + Año + " AND MONTH(MAU_FECHA)=" + Mes + " AND DAY(MAU_FECHA)=" + dia + " "); //autorizacion de servicio
DBFunctions.Request(lineas4, IncludeSchema.NO, "select DRUT.MCAT_PLACA,DRUT.DRUT_PLANILLA,DRUT.DRUT_CODIGO from DBXSCHEMA.DRUTA DRUT WHERE year(DRUT.DRUT_FECHA)=" + Año + " AND MONTH(DRUT.DRUT_FECHA)=" + Mes + " AND DAY(DRUT.DRUT_FECHA)=" + dia + " "); // tiquetes
/////////////////////////TABLAS//////////////////////////
/////////////////// TABLA 1 /////////////////////////////
for (int i = 0; i < lineas.Tables[0].Rows.Count; i++)
{
//Vamos a crear una fila para nuestro DataTable resultado, que almacene los resultados de las operaciones anteriores
DataRow fila;
fila = resultado.NewRow();
double valor = 0;
string ValorFormato = null;
valor = Convert.ToDouble(lineas.Tables[0].Rows[i].ItemArray[2].ToString());
ValorFormato = String.Format("{0:C}", valor);
fila["BUS"] = lineas.Tables[0].Rows[i].ItemArray[0].ToString();
fila["NUMERO"] = lineas.Tables[0].Rows[i].ItemArray[1].ToString();
fila["VALOR"] = ValorFormato.ToString();
resultado.Rows.Add(fila);
}
///////////////////////TABLA 2///////////////////////////
for (int a = 0; a < lineas2.Tables[0].Rows.Count; a++)
{
//Vamos a crear una fila para nuestro DataTable resultado, que almacene los resultados de las operaciones anteriores
DataRow fila2;
fila2 = resultado2.NewRow();
double valor2 = 0;
string ValorFormato2 = null;
valor2 = Convert.ToDouble(lineas2.Tables[0].Rows[a].ItemArray[2].ToString());
ValorFormato2 = String.Format("{0:C}", valor2);
fila2["BUS1"] = lineas2.Tables[0].Rows[a].ItemArray[0].ToString();
fila2["NUMERO1"] = lineas2.Tables[0].Rows[a].ItemArray[1].ToString();
fila2["VALOR1"] = ValorFormato2.ToString();
resultado2.Rows.Add(fila2);
}
/////////////////// TABLA 3 /////////////////////
for (int b = 0; b < lineas3.Tables[0].Rows.Count; b++)
{
//Vamos a crear una fila para nuestro DataTable resultado, que almacene los resultados de las operaciones anteriores
DataRow fila3;
fila3 = resultado3.NewRow();
double valor3 = 0;
string ValorFormato3 = null;
valor3 = Convert.ToDouble(lineas3.Tables[0].Rows[b].ItemArray[2].ToString());
ValorFormato3 = String.Format("{0:C}", valor3);
fila3["BUS2"] = lineas3.Tables[0].Rows[b].ItemArray[0].ToString();
fila3["NUMERO2"] = lineas3.Tables[0].Rows[b].ItemArray[1].ToString();
fila3["VALOR2"] = ValorFormato3.ToString();
resultado3.Rows.Add(fila3);
}
/////////////////// TABLA 4 /////////////////////
for (int c = 0; c < lineas4.Tables[0].Rows.Count; c++)
{
int codigoruta = Convert.ToInt32(lineas4.Tables[0].Rows[c].ItemArray[2].ToString());
int Puestos = Convert.ToInt32(DBFunctions.SingleData("SELECT count(*) AS NUMERO from DBXSCHEMA.DTIQUETE where DRUT_CODIGO=" + codigoruta + " and TTIQ_CODIGO=1"));
int codigoMruta = Convert.ToInt32(DBFunctions.SingleData("select MRUT_CODIGO FROM DBXSCHEMA.DRUTA WHERE DRUT_CODIGO=" + codigoruta + " "));
double precio = Convert.ToDouble(DBFunctions.SingleData("select MRUT_VALOR from DBXSCHEMA.MRUTA WHERE MRUT_CODIGO=" + codigoMruta + ""));
double total = precio * Puestos;
//Vamos a crear una fila para nuestro DataTable resultado, que almacene los resultados de las operaciones anteriores
DataRow fila4;
fila4 = resultado4.NewRow();
string ValorFormato4 = null;
ValorFormato4 = String.Format("{0:C}", total);
fila4["BUS4"] = lineas4.Tables[0].Rows[c].ItemArray[0].ToString();
fila4["TOTAL"] = Puestos;
fila4["VALOR4"] = ValorFormato4.ToString();
resultado4.Rows.Add(fila4);
}
///////////// FIN TABLAS ////////////////////
Grid.DataSource = resultado;
Grid.DataBind();
StringBuilder SB = new StringBuilder();
StringWriter SW = new StringWriter(SB);
HtmlTextWriter htmlTW = new HtmlTextWriter(SW);
tabPreHeader.RenderControl(htmlTW);
Grid.RenderControl(htmlTW);
tabFirmas.RenderControl(htmlTW);
string strRep;
strRep = SB.ToString();
Session.Clear();
Session["Rep"] = strRep;
toolsHolder.Visible = true;
//////////////////////////////
Grid1.DataSource = resultado2;
Grid1.DataBind();
Grid1.RenderControl(htmlTW);
//////////////////////////////
Grid2.DataSource = resultado3;
Grid2.DataBind();
Grid2.RenderControl(htmlTW);
//////////////////////////////
Grid3.DataSource = resultado4;
Grid3.DataBind();
Grid3.RenderControl(htmlTW);
////////////////////////////////
}
/// <summary>
/// ds2012
/// 循环遍历DataTable数据，导出Excel
/// </summary>
/// <param name="dt"></param>
/// <param name="title"></param>
/// <param name="colsName"></param>
public static void OutToExcel(System.Data.DataTable dt, string title, string[] colsName)
{
System.Web.UI.Page page = new System.Web.UI.Page();
System.Data.DataTable ddt = new System.Data.DataTable();
for (int i = 0; i < colsName.Length; i++)
{
DataColumn dc = new DataColumn(colsName[i].Split('=')[1]);
ddt.Columns.Add(dc);
}
for (int k = 0; k < dt.Rows.Count; k++)
{
DataRow dr = ddt.NewRow();
for (int i = 0; i < colsName.Length; i++)
{
dr[colsName[i].Split('=')[1]] = dt.Rows[k][colsName[i].Split('=')[0]];
}
ddt.Rows.Add(dr);
}
System.Web.UI.WebControls.DataGrid dataGrid = new System.Web.UI.WebControls.DataGrid();
dataGrid.DataSource = ddt.DefaultView;
dataGrid.AllowPaging = false;
dataGrid.HeaderStyle.BackColor = System.Drawing.Color.Gray;
dataGrid.HeaderStyle.HorizontalAlign = HorizontalAlign.Center;
dataGrid.HeaderStyle.Font.Bold = true;
dataGrid.ItemStyle.HorizontalAlign = HorizontalAlign.Left;
dataGrid.DataBind();
System.IO.StringWriter tw = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw);
dataGrid.RenderControl(hw);
if (!Directory.Exists(page.Server.MapPath("../UserExcel/")))
{
Directory.CreateDirectory(page.Server.MapPath("../UserExcel/"));
}
string fullpath = page.Server.MapPath("../UserExcel/FileName.xls");
System.IO.StreamWriter sw = System.IO.File.CreateText(fullpath);
sw.Write(tw.ToString());
sw.Close();
string where = DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString() + DateTime.Now.Day.ToString() + DateTime.Now.Hour.ToString() + DateTime.Now.Minute.ToString() + DateTime.Now.Second.ToString();
//压缩后的目标文件
string zipPath = page.Server.MapPath("../UserExcel/") + where + ".rar";
ZipClass Zc = new ZipClass();
string err = "";
Zc.ZipFile(fullpath, zipPath, out err);
//删除压缩前的文件
System.IO.File.Delete(fullpath);
System.IO.FileInfo file = new System.IO.FileInfo(zipPath);
System.Web.HttpContext.Current.Response.Clear();
System.Web.HttpContext.Current.Response.ClearContent();
System.Web.HttpContext.Current.Response.ClearHeaders();
System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(file.Name).Replace('+', ' '));
System.Web.HttpContext.Current.Response.AddHeader("Content-Length", file.Length.ToString());
System.Web.HttpContext.Current.Response.AddHeader("Content-Transfer-Encoding", "binary");
System.Web.HttpContext.Current.Response.ContentType = "application/zip";
System.Web.HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("utf-8");
System.Web.HttpContext.Current.Response.WriteFile(file.FullName);
System.Web.HttpContext.Current.Response.Flush();
System.Web.HttpContext.Current.Response.End();
}
private static void DataTable2Excel(System.Data.DataTable dtData)
{
System.Web.UI.WebControls.DataGrid dgExport = null;
// 当前对话
System.Web.HttpContext curContext = System.Web.HttpContext.Current;
// IO用于导出并返回excel文件
System.IO.StringWriter strWriter = null;
System.Web.UI.HtmlTextWriter htmlWriter = null;
if (dtData != null)
{
// <bug>
// BUG NO:ITC-1103-0261
// REASON:中文编码
// </bug>
curContext.Response.ContentType = "application/vnd.ms-excel";
curContext.Response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312");
curContext.Response.Charset = "UTF-8";
curContext.Response.Write("<meta http-equiv=Content-Type content=text/html;charset=gb2312 >");
// 导出excel文件
strWriter = new System.IO.StringWriter();
htmlWriter = new System.Web.UI.HtmlTextWriter(strWriter);
// 为了解决dgData中可能进行了分页的情况，需要重新定义一个无分页的DataGrid
dgExport = new System.Web.UI.WebControls.DataGrid();
dgExport.DataSource = dtData.DefaultView;
dgExport.AllowPaging = false;
dgExport.DataBind();
for (int i = 0; i < dgExport.Items.Count; i++)
{
for (int j = 0; j < 11; j++)
{
dgExport.Items[i].Cells[j].Attributes.Add("style", "vnd.ms-excel.numberformat:@");
}
}
// 返回客户端
dgExport.RenderControl(htmlWriter);
curContext.Response.Write(strWriter.ToString());
curContext.Response.End();
}
}
/// <summary>
/// 数据导出到Excel
/// </summary>
/// <returns></returns>
private void OutputDataToExcle(string strFilename)
{
string sWhereSQL = " ";
if (SysClass.SysUserSalary.UserSalary_SignStatus != "-1")
{
sWhereSQL += " And SignStatus=" + SysClass.SysUserSalary.UserSalary_SignStatus;
}
if (SysClass.SysUserSalary.UserSalary_UserSalaryOpCode.Length > 0)
{
sWhereSQL += " And a.OpCode = '" + SysClass.SysUserSalary.UserSalary_UserSalaryOpCode + "'";
}
//月份起始和月份结束同时不为空时，按区间查找数据
if (SysClass.SysUserSalary.UserSalary_SearchText.Length > 0 && SysClass.SysUserSalary.UserSalary_SearchText2.Length > 0)
{
sWhereSQL += " And a.SalaryYears >= '" + SysClass.SysUserSalary.UserSalary_SearchText + "'";
if (SysClass.SysUserSalary.UserSalary_ImportRecIndex > 0)
{
sWhereSQL += " And a.SalaryDate>='" + SysClass.SysUserSalary.UserSalary_ImportRecText + "'";
}
sWhereSQL += " And a.SalaryYears <= '" + SysClass.SysUserSalary.UserSalary_SearchText2 + "'";
if (SysClass.SysUserSalary.UserSalary_ImportRecIndex2 > 0)
{
sWhereSQL += " And a.SalaryDate <= DateAdd(mi,3,'" + SysClass.SysUserSalary.UserSalary_ImportRecText2 + "')";
}
}
//仅月份起始不为空时，只按月份起始查找数据
else if (SysClass.SysUserSalary.UserSalary_SearchText.Length > 0)
{
sWhereSQL += " And a.SalaryYears = '" + SysClass.SysUserSalary.UserSalary_SearchText + "'";
if (SysClass.SysUserSalary.UserSalary_ImportRecIndex > 0)
{
sWhereSQL += " And a.SalaryRecGuid ='" + SysClass.SysUserSalary.UserSalary_ImportRecValue + "'";
}
}
//仅月份结束不为空时，只按月份结束查找数据
else if (SysClass.SysUserSalary.UserSalary_SearchText2.Length > 0)
{
sWhereSQL += " And a.SalaryYears = '" + SysClass.SysUserSalary.UserSalary_SearchText2 + "'";
if (SysClass.SysUserSalary.UserSalary_ImportRecIndex2 > 0)
{
sWhereSQL += " And a.SalaryRecGuid ='" + SysClass.SysUserSalary.UserSalary_ImportRecValue2 + "'";
}
}
//如果不是从属与最高部门的员工，此页面只能查询其所属子部门员工的数据！
SqlDataReader sdrUser = SysClass.SysUser.GetUserInfoByReader(SysClass.SysGlobal.GetCurrentOpCode());
if (sdrUser.Read())
{
SqlDataReader sdrOrg = SysClass.SysOrgan.GetSingleOrganByReader(Convert.ToInt32(sdrUser["OrganID"]));
if (sdrOrg.Read())
{
if (sdrOrg["POrganID"].ToString() != "0")
{
sWhereSQL += " And b.OrganID = " + Convert.ToInt32(sdrUser["OrganID"]);
}
}
sdrOrg.Close();
}
sdrUser.Close();
//Add by lk 20160118 start
switch (SysClass.SysUserSalary.UserSalary_FlgValue)
{
//没有选中任何一项
case 0:
sWhereSQL += " And d.Description = ''";
break;
//仅【工资】被选中
case 1:
sWhereSQL += " And d.Description like '%" + chkGongzi.Text + "%'";
break;
//仅【奖金】被选中
case 2:
sWhereSQL += " And d.Description like '%" + chkJiangjin.Text + "%'";
break;
//【工资】和【奖金】被选中
case 3:
sWhereSQL += " And (d.Description like '%" + chkGongzi.Text + "%'";
sWhereSQL += " or d.Description like '%" + chkJiangjin.Text + "%')";
break;
//仅【报销】被选中
case 4:
sWhereSQL += " And d.Description like '%" + chkBaoxiao.Text + "%'";
break;
//【工资】和【报销】被选中
case 5:
sWhereSQL += " And (d.Description like '%" + chkGongzi.Text + "%'";
sWhereSQL += " or d.Description like '%" + chkBaoxiao.Text + "%')";
break;
//【奖金】和【报销】被选中
case 6:
sWhereSQL += " And (d.Description like '%" + chkJiangjin.Text + "%'";
sWhereSQL += " or d.Description like '%" + chkBaoxiao.Text + "%')";
break;
//【工资】【奖金】【报销】都被选中
case 7:
break;
}
//Add by lk 20160118 end
string sSQL = "select a.ID, a.OpCode, b.OpName, b.Sex, b.IdNumber, c.OrganName, a.SalaryYears, a.SalaryDate, a.TotalSalary,d.Description"
+ ", (Case IsNull(SignStatus,0) when 0 then a.TotalSalary else 0 end) As UnSignTotalSalary"
+ ", (Case IsNull(SignStatus,0) when 1 then a.TotalSalary else 0 end) As SignTotalSalary";
sSQL += " from UserSalary_Info a "
+ " left join SysUser_Info b on b.Status=0 And a.OpCode=b.OpCode"
+ " left join SysOrgan_Info c on c.Status=0 And b.OrganID=c.ID"
//Add by lk 20160118 start
+ " left join UserUserImportRec_Info d on d.SalaryRecGuid=a.SalaryRecGuid"
//Add by lk 20160118 end
+ " Where a.Status=0 " + sWhereSQL;
sSQL += " Order By a.SalaryYears Desc,b.OpName";
DataSet ds = CyxPack.OperateSqlServer.DataCommon.GetDataByDataSet(sSQL);
//创建DataTable对象
DataTable dt = new DataTable("output");
//构建表结构,往表中添加列
dt.Columns.Add("序号", typeof(int));
dt.Columns.Add("工号", typeof(string));
dt.Columns.Add("姓名", typeof(string));
dt.Columns.Add("单位部门", typeof(string));
dt.Columns.Add("工资年月", typeof(string));
dt.Columns.Add("发放日期", typeof(string));
dt.Columns.Add("发放金额", typeof(decimal));
dt.Columns.Add("未签收金额", typeof(decimal));
dt.Columns.Add("已签收金额", typeof(decimal));
dt.Columns.Add("备注", typeof(string));
//清空合计结果
mysum = 0;
mysum1 = 0;
mysum2 = 0;
for (int iLoop = 0; iLoop < ds.Tables[0].Rows.Count; iLoop++)
{
//创建DataRow对象
DataRow dr = dt.NewRow();
//新增数据
dr[0] = iLoop + 1;
dr[1] = ds.Tables[0].Rows[iLoop]["OpCode"].ToString();
dr[2] = ds.Tables[0].Rows[iLoop]["OpName"].ToString();
dr[3] = ds.Tables[0].Rows[iLoop]["OrganName"].ToString();
dr[4] = ds.Tables[0].Rows[iLoop]["SalaryYears"].ToString();
if (ds.Tables[0].Rows[iLoop]["SalaryDate"].ToString().Length > 0)
{
dr[5] = DateTime.Parse(ds.Tables[0].Rows[iLoop]["SalaryDate"].ToString()).ToString("yyyy-MM-dd");
}
else
{
dr[5] = "";
}
dr[6] = ds.Tables[0].Rows[iLoop]["TotalSalary"].ToString();
dr[7] = ds.Tables[0].Rows[iLoop]["UnSignTotalSalary"].ToString();
dr[8] = ds.Tables[0].Rows[iLoop]["SignTotalSalary"].ToString();
dr[9] = ds.Tables[0].Rows[iLoop]["Description"].ToString();
//把数据添加到表结构中
dt.Rows.Add(dr);
//合计结果累加
mysum += double.Parse(ds.Tables[0].Rows[iLoop]["TotalSalary"].ToString());
mysum1 += double.Parse(ds.Tables[0].Rows[iLoop]["UnSignTotalSalary"].ToString());
mysum2 += double.Parse(ds.Tables[0].Rows[iLoop]["SignTotalSalary"].ToString());
}
//添加合计部分数据
DataRow foot = dt.NewRow();
foot[5] = "总合计：";
foot[6] = mysum.ToString("0.00");
foot[7] = mysum1.ToString("0.00");
foot[8] = mysum2.ToString("0.00");
dt.Rows.Add(foot);
System.Web.UI.WebControls.DataGrid dgExport = null;
// 当前对话
System.Web.HttpContext curContext = System.Web.HttpContext.Current;
// IO用于导出并返回excel文件
System.IO.StringWriter strWriter = null;
System.Web.UI.HtmlTextWriter htmlWriter = null;
// 设置编码和附件格式
curContext.Response.ContentType = "application/vnd.ms-excel";
curContext.Response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312");
curContext.Response.Charset = "gb2312";
// 导出excel文件
strWriter = new System.IO.StringWriter();
htmlWriter = new System.Web.UI.HtmlTextWriter(strWriter);
// 为了解决dgData中可能进行了分页的情况，需要重新定义一个无分页的DataGrid
dgExport = new System.Web.UI.WebControls.DataGrid();
dgExport.DataSource = dt.DefaultView;
dgExport.AllowPaging = false;
dgExport.DataBind();
// 返回客户端
dgExport.RenderControl(htmlWriter);
curContext.Response.Clear();
curContext.Response.Buffer = true;
curContext.Response.Write("上海动车段工资电子签收系统");
curContext.Response.AddHeader("Content-Disposition", "attachment;filename=" + strFilename);
curContext.Response.Write("<meta http-equiv=\"content-type\" content=\"application/ms-excel; charset=gb2312\"/>" + strWriter.ToString());
curContext.Response.Flush();
curContext.Response.End();
}
public override void ExecuteResult(ControllerContext context)
{
var Response = context.HttpContext.Response;
Response.Buffer = true;
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("Content-Disposition", "attachment;filename=CMSOrganizations.xls");
Response.Charset = "";
var d = from om in DbUtil.Db.OrganizationMembers
where om.OrganizationId == oid
select new
{
om.PeopleId,
om.OrganizationId,
Groups = string.Join(",", om.OrgMemMemTags.Select(mt => mt.MemberTag.Name).ToArray()),
};
var dg = new DataGrid();
dg.DataSource = d;
dg.DataBind();
dg.RenderControl(new HtmlTextWriter(Response.Output));
}
/// <summary>
/// 数据导出到Excel
/// </summary>
/// <returns></returns>
private void OutputDataToExcle(string strFilename)
{
string sWhereSQL = " And SignStatus=0";
if (SysClass.SysUserSalary.UserSalary_UserSalaryOpCode.Length > 0)
{
sWhereSQL = sWhereSQL + " And a.OpCode = '" + SysClass.SysUserSalary.UserSalary_UserSalaryOpCode + "'";
}
//月份起始和月份结束同时不为空时，按区间查找数据
if (SysClass.SysUserSalary.UserSalary_SearchText.Length > 0 && SysClass.SysUserSalary.UserSalary_SearchText2.Length > 0)
{
sWhereSQL += " And a.SalaryYears >= '" + SysClass.SysUserSalary.UserSalary_SearchText + "'";
if (SysClass.SysUserSalary.UserSalary_ImportRecIndex > 0)
{
sWhereSQL += " And a.SalaryDate>='" + SysClass.SysUserSalary.UserSalary_ImportRecText + "'";
}
sWhereSQL += " And a.SalaryYears <= '" + SysClass.SysUserSalary.UserSalary_SearchText2 + "'";
if (SysClass.SysUserSalary.UserSalary_ImportRecIndex2 > 0)
{
sWhereSQL += " And a.SalaryDate <= DateAdd(mi,3,'" + SysClass.SysUserSalary.UserSalary_ImportRecText2 + "')";
}
}
//仅月份起始不为空时，只按月份起始查找数据
else if (SysClass.SysUserSalary.UserSalary_SearchText.Length > 0)
{
sWhereSQL += " And a.SalaryYears = '" + SysClass.SysUserSalary.UserSalary_SearchText + "'";
if (SysClass.SysUserSalary.UserSalary_ImportRecIndex > 0)
{
sWhereSQL += " And a.SalaryRecGuid ='" + SysClass.SysUserSalary.UserSalary_ImportRecValue + "'";
}
}
//仅月份结束不为空时，只按月份结束查找数据
else if (SysClass.SysUserSalary.UserSalary_SearchText2.Length > 0)
{
sWhereSQL += " And a.SalaryYears = '" + SysClass.SysUserSalary.UserSalary_SearchText2 + "'";
if (SysClass.SysUserSalary.UserSalary_ImportRecIndex2 > 0)
{
sWhereSQL += " And a.SalaryRecGuid ='" + SysClass.SysUserSalary.UserSalary_ImportRecValue2 + "'";
}
}
//如果不是从属与最高部门的员工，此页面只能查询其所属子部门员工的数据！
SqlDataReader sdrUser = SysClass.SysUser.GetUserInfoByReader(SysClass.SysGlobal.GetCurrentOpCode());
if (sdrUser.Read())
{
SqlDataReader sdrOrg = SysClass.SysOrgan.GetSingleOrganByReader(Convert.ToInt32(sdrUser["OrganID"]));
if (sdrOrg.Read())
{
if (sdrOrg["POrganID"].ToString() != "0")
{
sWhereSQL += " And b.OrganID = " + Convert.ToInt32(sdrUser["OrganID"]);
}
}
sdrOrg.Close();
}
sdrUser.Close();
int _SalaryValue = SysClass.SysWarning.GetSalaryValueByOrganID(SysClass.SysGlobal.GetCurrentUserOrganID());
sWhereSQL = sWhereSQL + " And DateDiff(Day, a.SalaryDate, GetDate()) >= " + _SalaryValue.ToString() + "";
DataSet ds = SysClass.SysUserSalary.GetSalaryNotSignLstByDataSet(sWhereSQL);
//创建DataTable对象
DataTable dt = new DataTable("output");
//构建表结构,往表中添加列
dt.Columns.Add("序号", typeof(int));
dt.Columns.Add("工号", typeof(string));
dt.Columns.Add("姓名", typeof(string));
dt.Columns.Add("单位部门", typeof(string));
dt.Columns.Add("工资年月", typeof(string));
dt.Columns.Add("发放日期", typeof(string));
dt.Columns.Add("发放金额", typeof(decimal));
dt.Columns.Add("签收状态", typeof(string));
dt.Columns.Add("未签收天数", typeof(int));
for (int iLoop = 0; iLoop < ds.Tables[0].Rows.Count; iLoop++)
{
//创建DataRow对象
DataRow dr = dt.NewRow();
//新增数据
dr[0] = iLoop + 1;
dr[1] = ds.Tables[0].Rows[iLoop]["OpCode"].ToString();
dr[2] = ds.Tables[0].Rows[iLoop]["OpName"].ToString();
dr[3] = ds.Tables[0].Rows[iLoop]["OrganName"].ToString();
dr[4] = ds.Tables[0].Rows[iLoop]["SalaryYears"].ToString();
if (ds.Tables[0].Rows[iLoop]["SalaryDate"].ToString().Length > 0)
{
dr[5] = DateTime.Parse(ds.Tables[0].Rows[iLoop]["SalaryDate"].ToString()).ToString("yyyy-MM-dd");
}
else
{
dr[5] = "";
}
dr[6] = ds.Tables[0].Rows[iLoop]["TotalSalary"].ToString();
if (ds.Tables[0].Rows[iLoop]["SignStatus"].ToString() == "1")
{
dr[7] = "已签收";
}
else
{
dr[7] = "未签收";
}
dr[8] = ds.Tables[0].Rows[iLoop]["NotSingDay"].ToString();
//把数据添加到表结构中
dt.Rows.Add(dr);
}
System.Web.UI.WebControls.DataGrid dgExport = null;
// 当前对话
System.Web.HttpContext curContext = System.Web.HttpContext.Current;
// IO用于导出并返回excel文件
System.IO.StringWriter strWriter = null;
System.Web.UI.HtmlTextWriter htmlWriter = null;
// 设置编码和附件格式
curContext.Response.ContentType = "application/vnd.ms-excel";
curContext.Response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312");
curContext.Response.Charset = "gb2312";
// 导出excel文件
strWriter = new System.IO.StringWriter();
htmlWriter = new System.Web.UI.HtmlTextWriter(strWriter);
// 为了解决dgData中可能进行了分页的情况，需要重新定义一个无分页的DataGrid
dgExport = new System.Web.UI.WebControls.DataGrid();
dgExport.DataSource = dt.DefaultView;
dgExport.AllowPaging = false;
dgExport.DataBind();
// 返回客户端
dgExport.RenderControl(htmlWriter);
curContext.Response.Clear();
curContext.Response.Buffer = true;
curContext.Response.Write("上海动车段工资电子签收系统");
curContext.Response.AddHeader("Content-Disposition", "attachment;filename=" + strFilename);
curContext.Response.Write("<meta http-equiv=\"content-type\" content=\"application/ms-excel; charset=gb2312\"/>" + strWriter.ToString());
curContext.Response.Flush();
curContext.Response.End();
}