using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Text;
using System.Data;
/// <summary>
///ExportExcel 的摘要说明
/// </summary>
public class ExportExcel : System.Web.UI.Page
{
public ExportExcel()
{
//
//TODO: 在此处添加构造函数逻辑
//
}
/// <summary>
/// 拼写数据
/// </summary>
/// <param name="list">数据集合</param>
/// <param name="tableName">表名称</param>
/// <param name="TableHead">列名拼接字符串,以','分割</param>
protected string GetExportExcel(IList<object[]> list, string tableName, string TableHead)
{
int num = 1;
if (list != null)
{
num = list.Count + 1;
}
else
{
list = new List<object[]>();
}
StringBuilder excel = new StringBuilder();
string[] Heads = TableHead.Split(',');
//输出表名
//excel.Append(this.ExportFile());
excel.Append("<Worksheet ss:Name=\"" + tableName + "\">");
excel.Append("<Table ss:ExpandedColumnCount=\"" + Heads.Length + "\" ss:ExpandedRowCount=\""
+ num + "\" x:FullColumns=\"1\" x:FullRows=\"1\">");
//输出列名
excel.Append(this.GetTableHead(Heads));
//输出数据
//excel.Append(this.ExportData(list));
foreach (var item in list)
{
excel.Append("<Row ss:AutoFitHeight=\"0\">");
for (int i = 0; i < Heads.Length; i++)
{
string data = (item[i] == null) ? "" : item[i].ToString();
excel.Append("<Cell><Data ss:Type=\"String\">" + data + "</Data></Cell>");
}
excel.Append("</Row>");
}
excel.Append("</Table>");
excel.Append("</Worksheet>");
//excel.Append("</Workbook>");
return excel.ToString(); ;
}
/// <summary>
/// 拼写数据
/// </summary>
/// <param name="list">数据集合</param>
/// <param name="tableName">表名称</param>
/// <param name="TableHead">列名拼接字符串,以','分割</param>
/// <param name="caption">标题</param>
protected string GetExportExcel(IList<object[]> list, string tableName, string TableHead, string caption)
{
int num = 1;
if (list != null)
{
num = list.Count + 2;
}
else
{
list = new List<object[]>();
}
StringBuilder excel = new StringBuilder();
string[] Heads = TableHead.Split(',');
//输出表名
//excel.Append(this.ExportFile());
excel.Append("<Worksheet ss:Name=\"" + tableName + "\">");
excel.Append("<Table ss:ExpandedColumnCount=\"" + Heads.Length + "\" ss:ExpandedRowCount=\""
+ num + "\" x:FullColumns=\"1\" x:FullRows=\"1\">");
//拼接表名
excel.Append("<Row ss:AutoFitHeight=\"0\">");
excel.Append("<Cell ss:MergeAcross=\"" + (Heads.Length - 1) + "\" ss:StyleID=\"s63\">");
excel.Append("<Data ss:Type=\"String\">" + caption + "</Data>");
excel.Append("</Cell>");
excel.Append("</Row>");
//输出列名
excel.Append(this.GetTableHead(Heads));
//输出数据
//excel.Append(this.ExportData(list));
foreach (var item in list)
{
excel.Append("<Row ss:AutoFitHeight=\"0\">");
for (int i = 0; i < Heads.Length; i++)
{
string data = (item[i] == null) ? "" : item[i].ToString();
excel.Append("<Cell><Data ss:Type=\"String\">" + data + "</Data></Cell>");
}
excel.Append("</Row>");
}
excel.Append("</Table>");
excel.Append("</Worksheet>");
//excel.Append("</Workbook>");
return excel.ToString();
}
/// <summary>
/// 拼写数据 (dataset)
/// </summary>
/// <param name="ds"></param>
/// <returns></returns>
public static string GetExportExcel(DataSet ds)
{
StringBuilder excel = new StringBuilder();
excel.Append("<?xml version=\"1.0\"?>");
excel.Append("<?mso-application progid=\"Excel.Sheet\"?>");
excel.Append("<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"");
excel.Append(" xmlns:o=\"urn:schemas-microsoft-com:office:office\"");
excel.Append(" xmlns:x=\"urn:schemas-microsoft-com:office:excel\"");
excel.Append(" xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\"");
excel.Append(" xmlns:html=\"http://www.w3.org/TR/REC-html40\">");
excel.Append("<Styles>");
excel.Append("<Style ss:ID=\"Default\" ss:Name=\"Normal\">");
excel.Append("<Alignment ss:Vertical=\"Center\"/>");
excel.Append("<Borders/>");
excel.Append("<Font ss:FontName=\"宋体\" x:CharSet=\"134\" ss:Size=\"11\" ss:Color=\"#000000\"/>");
excel.Append("<Interior/>");
excel.Append("<NumberFormat/>");
excel.Append("<Protection/>");
excel.Append("</Style>");
excel.Append("</Styles>");
foreach (DataTable dt in ds.Tables)
{
excel.Append("<Worksheet ss:Name=\"" + dt.TableName + "\">");
excel.Append("<Table ss:ExpandedColumnCount=\"" + dt.Columns.Count + "\" ss:ExpandedRowCount=\"" + dt.Rows.Count + 1 + "\" x:FullColumns=\"1\" x:FullRows=\"1\">");
excel.Append("<Row ss:AutoFitHeight=\"0\">");
for (int i = 0; i < dt.Columns.Count; i++)
{
excel.Append("<Cell><Data ss:Type=\"String\">" + dt.Columns[i].ColumnName + "</Data></Cell>");
}
excel.Append("</Row>");
for (int i = 0; i < dt.Rows.Count; i++)
{
excel.Append("<Row ss:AutoFitHeight=\"0\">");
for (int j = 0; j < dt.Columns.Count; j++)
{
excel.Append("<Cell><Data ss:Type=\"String\">" + dt.Rows[i][j].ToString() + "</Data></Cell>");
}
excel.Append("</Row>");
}
excel.Append("</Table>");
excel.Append("</Worksheet>");
}
excel.Append("</Workbook>");
return excel.ToString(); ;
}
/// <summary>
/// 拼写文件格式
/// </summary>
/// <param name="TableName">表名称</param>
/// <returns></returns>
protected string ExportFile()
{
StringBuilder excel = new StringBuilder();
//拼写文件格式
excel.Append("<?xml version=\"1.0\"?>");
excel.Append("<?mso-application progid=\"Excel.Sheet\"?>");
excel.Append("<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"");
excel.Append(" xmlns:o=\"urn:schemas-microsoft-com:office:office\"");
excel.Append(" xmlns:x=\"urn:schemas-microsoft-com:office:excel\"");
excel.Append(" xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\"");
excel.Append(" xmlns:html=\"http://www.w3.org/TR/REC-html40\">");
excel.Append("<Styles>");
excel.Append("<Style ss:ID=\"Default\" ss:Name=\"Normal\">");
excel.Append("<Alignment ss:Vertical=\"Center\"/>");
excel.Append("<Borders/>");
excel.Append("<Font ss:FontName=\"宋体\" x:CharSet=\"134\" ss:Size=\"11\" ss:Color=\"#000000\"/>");
excel.Append("<Interior/>");
excel.Append("<NumberFormat/>");
excel.Append("<Protection/>");
excel.Append("</Style>");
excel.Append("<Style ss:ID=\"s63\">");
excel.Append(" <Alignment ss:Horizontal=\"Center\" ss:Vertical=\"Center\"/>");
excel.Append("</Style>");
excel.Append("</Styles>");
return excel.ToString();
}
/// <summary>
/// 得到列名
/// </summary>
/// <param name="Heads">列名数组</param>
/// <returns></returns>
protected string GetTableHead(string[] Heads)
{
StringBuilder head = new StringBuilder();
head.Append("<Row ss:AutoFitHeight=\"0\">");
//拼写表头
//string[] Heads = TableHead.Split(',');
for (int i = 0; i < Heads.Length; i++)
{
head.Append("<Cell><Data ss:Type=\"String\">" + Heads[i] + "</Data></Cell>");
}
head.Append("</Row>");
return head.ToString();
}
}
//dataset 调用
/// <summary>
/// 历史数据
/// </summary>
void History()
{
string year = Server.UrlDecode(Request["year"]);
string fileName = "历史数据-" + year + "期";
string tableName = "历史数据-" + year + "期";
string tableColumns = "站名,期号,供压,回压,供温,回温,瞬流,瞬热,累流,流差,累热,热差,热指标";
PageModel pageModel = new PageModel("stationName", 0, 1, int.MaxValue);
DataTable dt = new BrowserHistory().GetHistory_Condition_P(HttpContext.Current.User.Identity.Name, year, "", pageModel);
IList<object[]> list = new List<object[]>();
for (int i = 0; i < dt.Rows.Count; i++)
{
object[] o = { dt.Rows[i]["stationName"],
dt.Rows[i]["Period"],
dt.Rows[i]["AI_Value1"],
dt.Rows[i]["AI_Value3"],
dt.Rows[i]["AI_Value2"],
dt.Rows[i]["AI_Value4"],
dt.Rows[i]["AI_Value5"],
dt.Rows[i]["AI_Value6"],
dt.Rows[i]["AI_Value7"],
dt.Rows[i]["AI_Value9"],
dt.Rows[i]["AI_Value8"],
dt.Rows[i]["AI_Value10"],
dt.Rows[i]["HeatTarget"]
};
list.Add(o);
}
// 把文件流发送到客户端
this.Page.Response.Clear();
string data = this.ExportFile() + GetExportExcel(list, tableName, tableColumns) + "</Workbook>";
Export(data, fileName);
// 停止页面的执行
this.Page.Response.End();
}
/// <summary>
/// 历史数据(全部)
/// </summary>
/// <returns></returns>
void HistoryAll()
{
string year = Server.UrlDecode(Request["year"]);
string fileName = "历史全部数据-" + year + "期";
DataSet ds = new Tscc.BLL.BrowserHistory().HisDataBranchByPeriod(year);
ds.Tables[0].TableName = "历史全部数据-" + year + "期";
// 把文件流发送到客户端
this.Page.Response.Clear();
string data = GetExportExcel(ds);
Export(data, fileName);
// 停止页面的执行
this.Page.Response.End();
}
//对象调用
public void EquipmentWorkList()
{
int pageIndex = Convert.ToInt32(Server.UrlDecode(Request["pageIndex"]));
int vpnUserID = Convert.ToInt32(Server.UrlDecode(Request["vpnUserID"]));
string StartDate = Server.UrlDecode(Request["StartDate"]) + ":00:00";
string EndDate = Server.UrlDecode(Request["EndDate"]) + ":00:00";
string fileName = "设备启停记录";
string tableName = "设备启停记录";
string tableColumns = "采集时间,设备,状态";
WebMonitor.EquipmentWorkList equipmentInfo = new WebMonitor.EquipmentWorkList();
equipmentInfo.PageSize = pagesize * pageIndex;
IList<xLinkSystemORM.HistoryDiData> equipmentList = equipmentInfo.GetEquipmentWorkList(vpnUserID, Convert.ToDateTime(StartDate), Convert.ToDateTime(EndDate));
IList<object[]> list = new List<object[]>();
foreach (var item in equipmentList)
{
object[] o = { item.CapTime, item.DiValue, item.RealValue };
list.Add(o);
}
// 把文件流发送到客户端
this.Page.Response.Clear();
string data = this.ExportFile() + GetExportExcel(list, tableName, tableColumns) + "</Workbook>";
Export(data, fileName);
// 停止页面的执行
this.Page.Response.End();
}
//前台调用
function ExportExcel_Click(sender, args) {
/// <summary>
/// 导出Excel
/// </summary>
var count = $find("GridViewForUser").get_rows().length;
if (count == 0) {
$("#Msg").text("请先查询出数据,再进行导出!");
} else {
$("#Msg").text("");
var pageIndex = $("#GridViewForUser").find("span.pcontrol span").text();
var pageSize = $("#GridViewForUser").find("div.pGroup select").val(); //分页尺寸
var arrayNo = $("#Machine").val();
var departmentID = $("#AddDepartmentDropDownList").val();
var startDate = $("#DateTextBox").val();
var url = "AJAXServer/ExportExcel.aspx?AjaxType=CompanyDailyReporting&PageIndex=" + pageIndex + "&ArrayNo="
+ arrayNo + "&departmentID=" + departmentID + "&StartDate=" + startDate + "&pagesize=" + escape($.trim(pageSize));
window.location.href = url;
}
}