2018-12-27导出Excel

导出按钮

<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);
        }