导出按钮
<a href="#" id="A1" class="duo_teechar_btn" onclick="ExportReport();return false;"><span style="line-height:40px;">导出</span></a>
js方法
// 导出开始
function ExportReport() {
var BegDate = $("#txtBegDate").val();
var VSRHContent = $("#QueryVSRHContent").val();
var CompanyName = $("#QueryCompanyName").val();
var Enddate = $("#txtEnddate").val();
var VSRHPhone = $("#QueryVSRHPhone").val();
var VSRHState = $("#ddlSendStatus").val();
$.ajax(
{
type: 'POST',
url: 'VoiceAlarmRecord.aspx',
dataType: 'text',
data: { Action: 'ExportReport', BegDate: BegDate, VSRHContent: VSRHContent, CompanyName: CompanyName, Enddate: Enddate, VSRHPhone: VSRHPhone, VSRHState: VSRHState },
cache: false,
beforeSend: function ShowdivShowChangeQueryInfo() {
topTip.setData({
tipText: "正在生成文件，请稍后...",
tipType: "loading"
}).showTopTip();
},
success: ExportReportCallBack,
error: function (XMLHttpRequest, textStatus, errorThrown) {
debugger;
topTip.setData({
tipText: "网络错误，请重试！",
tipType: "error"
}).autoShowTopTip();
}
});
}
function ExportReportCallBack(strs) {
if (strs != "Empty") {
topTip.setData({
tipText: "文件生成成功，请注意查收！",
tipType: "ok"
}).autoShowTopTip();
window.open("../../" + strs, "_blank", "", false);
}
else {
topTip.setData({
tipText: "没有匹配的数据！",
tipType: "error"
}).autoShowTopTip();
}
}
// 导出结束
CS后台
//导出
private void ExportDataToExcel(string BegDate, string VSRHContent, string CompanyName, string Enddate, string VSRHPhone, string VSRHState)
{
int? rowCoun = 1;
string returnMsg = string.Empty;
DataSet ds = QueryVoiceAlarmRecord.ExportVoiceAlarmInfo(ref rowCoun, BegDate, VSRHContent, CompanyName, Enddate, VSRHPhone, VSRHState);
if (ds != null && ds.Tables[0].Rows.Count > 0)
{
DataTable dt = DealExportDataInfo(ds.Tables[0]);
#region 原导出日志文档方式，直接导出然后压缩
int TotalCount = dt.Rows.Count;
int allpage = CommonHelper.ExcelCountPage(Convert.ToInt32(TotalCount));
ArrayList FileNames = new ArrayList();
//DataTable dtTmp = dt.Clone();
DataTable dtTmp = CreateTable();
Dictionary<string, string> dictTitle = TableTitle();
for (int i = 1; i <= allpage; i++)
{
Random rnd = new Random();
int n = rnd.Next(1, 1000);
for (int j = 65535 * (i - 1); j < 65535 * i; j++)
{
if (j >= dt.Rows.Count)
{
break;
}
else
{
DataRow dr = dtTmp.NewRow();
for (var columnIndex = 0; columnIndex < dtTmp.Columns.Count; columnIndex++)
{
if (dictTitle.ContainsKey(dtTmp.Columns[columnIndex].ColumnName))
dr[columnIndex] = dt.Rows[j][dictTitle[dtTmp.Columns[columnIndex].ColumnName]];
}
dr["序号"] = j + 1;
//dtTmp.ImportRow(dt.Rows[j]);
dtTmp.Rows.Add(dr);
}
}
if (dtTmp != null && dtTmp.Rows.Count > 0)
{
string strFileName = ExportDataToExcel("语音报警记录" + DateTime.Now.ToString("yyyyMMddHHmmssffff") + n.ToString(), dtTmp);
FileNames.Add(strFileName);
}
if (FileNames.Count > 1)
{
SaveZip(FileNames, "语音报警记录");
}
else
{
Response.Write("excel/" + FileNames[0]);
Response.End();
}
}
#endregion
}
else
{
Response.Write("Empty");
}
}
protected DataTable DealExportDataInfo(DataTable dt)
{
//ToDictionary(Item => Item.EventID,Item=>Item.EventName); 生成Dictionary<EventID,EquipAnomalyEvent>
//ToDictionary(Item => Item.EventID,Item=>Item.EventName,Item=>Item.EventName); 生成Dictionary<EventID,EventName>
var dicEquipAnomalyEvents = EquipAnomalyEvent.GetEquipAnomalyEvents().ToDictionary(Item => Item.EventID, Item => Item.EventName);
var dicNodeAnomalyEvents = NodeAnomalyEvent.GetNodeAnomalyEvents().ToDictionary(Item => Item.EventID, Item => Item.EventName);
foreach (DataRow dr in dt.Rows)
{
dr["VSRH_State"] = dr["VSRH_State"].ToString() == "0" ? "发送失败" : dr["VSRH_State"].ToString() == "1" ? "发送成功" : "";
}
dt.AcceptChanges();
return dt;
}
public void SaveZip(ArrayList strName, string fileName)
{
Response.Clear();
Response.ContentType = "application/zip";
Response.AddHeader("content-disposition", "filename=DotNetZip.zip");
string name = fileName + DateTime.Now.ToString("yyyyMMddHHmmssffff") + ".zip";
using (ZipFile zip = new ZipFile(System.Text.Encoding.Default))//解决中文乱码问题
{
for (int i = 0; i < strName.Count; i++)
{
zip.AddFile(Server.MapPath("/") + "excel\\" + strName[i], "");
}
zip.Save(Server.MapPath("/") + "excel\\" + name);
}
Response.Write("excel/" + name);
Response.End();
}
/// <summary>
/// 导出数据
/// </summary>
/// <param name="strFilename">文件名</param>
/// <param name="dt">导出的数据集</param>
private string ExportDataToExcel(string strFilename, DataTable dt)
{
string strFile = "";
string path = "";
strFile = strFilename + ".xls";
path = Server.MapPath("~\\excel\\" + strFile);
AsposeExcel.OutFileToDisk(dt, "语音报警记录", path);
return strFile;
}
// 序号
//语音内容
//电话号码
//入表时间
//发送时间
//发送状态
//重发次数
//所属单位
//所属网关
//所属传感器
public Dictionary<string, string> TableTitle()
{
Dictionary<string, string> dict = new Dictionary<string, string>();
dict.Add("语音内容", "VSRH_Content");
dict.Add("电话号码", "VSRH_Phone");
dict.Add("入表时间", "VSRH_InsertDateTime");
dict.Add("发送时间", "VSRH_SendDateTime");
dict.Add("发送状态", "VSRH_State");
dict.Add("重发次数", "VSRN_FailedTimes");
dict.Add("所属单位", "Company_Name");
dict.Add("所属网关", "Equip_No");
dict.Add("所属传感器", "Node_No");
return dict;
}
public DataTable CreateTable()
{
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("所属网关");
dt.Columns.Add("所属传感器");
dt.AcceptChanges();
return dt;
}
BLL
public DataSet ExportVoiceAlarmInfo(ref int? rowCoun, string BegDate, string VSRHContent, string CompanyName, string Enddate, string VSRHPhone, string VSRHState)
{
VoiceAlarmRecordDAL _VoiceAlarmRecordDAL = new VoiceAlarmRecordDAL();
Node_InfoDAL Node_InfoDAL = new Node_InfoDAL();
User_Info user = LoginHandler.CurrentUser;
if (user.Company_ID == 0)
{
return _VoiceAlarmRecordDAL.ExportVoiceAlarmInfo(ref rowCoun, BegDate, VSRHContent, CompanyName, Enddate, VSRHPhone, VSRHState, user.User_ID.ToString(), "0");
}
else
{
return _VoiceAlarmRecordDAL.ExportVoiceAlarmInfo(ref rowCoun, BegDate, VSRHContent, CompanyName, Enddate, VSRHPhone, VSRHState, user.User_ID.ToString(), Role_InfoDAL.GetAllEneterIdsByCompanyID(user.Company_ID.ToString()));
}
}
DAL
public DataSet ExportVoiceAlarmInfo( ref int? rowCount, string BegDate, string VSRHContent, string CompanyName, string Enddate, string VSRHPhone, string VSRHState ,string strUserId, string strCompanyIDs)
{
StringBuilder strSql = new StringBuilder();
strSql.Append(" select a.VSRH_Content,a.VSRH_Phone,a.VSRH_InsertDateTime,a.VSRH_SendDateTime,a.VSRH_State,a.VSRN_FailedTimes,b.Company_Name,c.Equip_No,d.Node_No");
strSql.Append(" from Voice_SendRecorderHis a left join Company_Info b on a.VSRH_CompanyID=b.Company_ID left join Equip_Info c on c.Equip_ID=a.VSRH_EquipId left join Node_Info d on d.Node_ID=a.VSRH_NodeId ");
strSql.Append(" where 1=1 ");
if (VSRHContent != "")
strSql.Append(" and a.VSRH_Content like '%" + VSRHContent + "%'");
if (CompanyName != "")
strSql.Append(" and b.Company_Name like '%" + CompanyName + "%'");
if (VSRHPhone != "")
strSql.Append(" and a.VSRH_Phone like '%" + VSRHPhone + "%'");
if (VSRHState != "")
strSql.Append(" and a.VSRH_State like '%" + VSRHState + "%'");
if (BegDate != "")
strSql.Append(" and a.VSRH_SendDateTime>='" + BegDate + "'");
if (Enddate != "")
strSql.Append(" and a.VSRH_SendDateTime<='" + Enddate + "'");
if (Role_InfoDAL.ExitSupper(strUserId))
{
}
else if (Role_InfoDAL.ExitBusiness(strUserId) || Role_InfoDAL.ExitMaintenance(strUserId))
{
//string strUserIDsandCompanyIDs = User_InfoDAL.GetUserIDAndCompanyIDForBOMByUserID(strUserId);
//if (strUserIDsandCompanyIDs != "")
//{
// string[] UserIDandCompanyIDs = strUserIDsandCompanyIDs.Split('|');
// strSql.Append(" and (GNH_UserId in ('" + UserIDandCompanyIDs[0] + "')");
// if (UserIDandCompanyIDs[1] != null && UserIDandCompanyIDs[1] != "")
// {
// strSql.Append(" or GNH_CompanyID in ('" + UserIDandCompanyIDs[1] + "'))");
// }
// else
// {
// strSql.Append(" )");
// }
//}
//else return null;
}
else
{
if (strCompanyIDs == "0")
{
DataSet dt = new DataSet();
return dt;
}
else
{
strSql.Append(" and a.VSRH_CompanyID in ('" + strCompanyIDs + "')");
}
}
strSql.Append(" order by a.VSRH_SendDateTime");
string sql = strSql.ToString();
return DBCommon.GetDataSet(sql);
}
网友评论