protected void bExcelXML_Click(object sender, ImageClickEventArgs e)
{
lock (Database.lockObjectDB)
{
DataSet ds1 = new DataSet();
string str = lbSearch.Text.Trim();
if (str.Length > 0)
{
str += "and " + branchSearch;
}
else
{
str += "where " + branchSearch;
}
//res = Database.ExecuteQuery(String.Format("select pan,fio,company,prod_name,status,datestart,dateend,passport,account,dateprod,invoice,date_courier,depbranchinit,depbranchcard,datereceipt from V_Cards {0} {1}", str, lbSort.Text), ref ds1, null);
res = Database.ExecuteQuery(String.Format("select * from V_Cards {0} {1}", str, lbSort.Text), ref ds1, null);
if (ds1 == null || ds1.Tables.Count == 0)
{
return;
}
Response.ClearHeaders();
Response.ClearContent();
DialogUtils.SetCookieResponse(Response);
Response.Buffer = true;
Response.HeaderEncoding = System.Text.Encoding.Default;
Response.AddHeader("Content-Disposition", "attachment; filename=cards.xls");
Response.ContentType = "application/vnd.ms_excel";
Response.Cache.SetCacheability(HttpCacheability.NoCache);
Response.BufferOutput = false;
using (XmlTextWriter x = new XmlTextWriter(Response.OutputStream, Encoding.UTF8))
{
x.WriteRaw("<?xml version=\"1.0\"?><?mso-application progid=\"Excel.Sheet\"?>");
x.WriteRaw("<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\" ");
x.WriteRaw("xmlns:o=\"urn:schemas-microsoft-com:office:office\" ");
x.WriteRaw("xmlns:x=\"urn:schemas-microsoft-com:office:excel\">");
x.WriteRaw("<Styles><Style ss:ID='sText'><NumberFormat ss:Format='@'/></Style>");
x.WriteRaw("<Style ss:ID='sDate'><NumberFormat ss:Format='[$-409]dd.mm.yyyy;@'/></Style></Styles>");
x.WriteRaw("<Worksheet ss:Name='Cards'>");
x.WriteRaw("<Table>");
//string[] colTypes = new string[ds1.Tables[0].Columns.Count];
//for (int i = 0; i < gvCard.Columns.Count; i++)
//{
// BoundField bf = gvCard.Columns[i] as BoundField;
// string coltype = bf.ty
// if (coltype.Contains("datetime"))
// {
// colTypes[i] = "DateTime";
// x.WriteRaw("<Column ss:StyleID='sText'/>");
// }
// else if (coltype.Contains("string"))
// {
// colTypes[i] = "String";
// x.WriteRaw("<Column ss:StyleID='sText'/>");
// }
// else
// {
// x.WriteRaw("<Column/>");
// if (coltype.Contains("boolean"))
// colTypes[i] = "Boolean";
// else
// colTypes[i] = "Number";
// }
//}
x.WriteRaw("<Row>");
for (int i = 0; i < gvCard.Columns.Count; i++)
{
BoundField bf = gvCard.Columns[i] as BoundField;
if (bf != null && bf.Visible)
{
x.WriteRaw("<Cell ss:StyleID='sText'><Data ss:Type='String'>");
x.WriteRaw(bf.HeaderText);
x.WriteRaw("</Data></Cell>");
}
TemplateField tf = gvCard.Columns[i] as TemplateField;
if (tf != null && tf.Visible && tf.HeaderText.Length > 0)
{
x.WriteRaw("<Cell ss:StyleID='sText'><Data ss:Type='String'>");
x.WriteRaw(tf.HeaderText);
x.WriteRaw("</Data></Cell>");
}
}
x.WriteRaw("</Row>");
foreach (DataRow dr in ds1.Tables[0].Rows)
{
x.WriteRaw("<Row>");
for (int i = 0; i < gvCard.Columns.Count; i++)
{
BoundField bf = gvCard.Columns[i] as BoundField;
if (bf != null && bf.Visible)
{
x.WriteRaw("<Cell><Data ss:Type='String'>");
if (ds1.Tables[0].Columns[bf.DataField].DataType.ToString().ToLower().Contains("datetime"))
{
try
{
x.WriteRaw(((DateTime)dr[bf.DataField]).ToShortDateString());
}
catch
{
x.WriteRaw((dr.IsNull(bf.DataField)) ? "" : (dr[bf.DataField]).ToString().Trim());
}
}
else
{
x.WriteRaw((dr.IsNull(bf.DataField)) ? "" : (dr[bf.DataField]).ToString().Trim());
}
x.WriteRaw("</Data></Cell>");
}
TemplateField tf = gvCard.Columns[i] as TemplateField;
if (tf != null && tf.Visible && tf.HeaderText.Length > 0)
{
x.WriteRaw("<Cell><Data ss:Type='String'>");
x.WriteRaw((dr.IsNull(tf.SortExpression)) ? "" : (dr[tf.SortExpression]).ToString().Trim());
x.WriteRaw("</Data></Cell>");
}
}
x.WriteRaw("</Row>");
}
x.WriteRaw("</Table></Worksheet></Workbook>");
}
Response.Flush();
Response.End();
}
}