using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Data.OleDb;
using System.IO;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using CtrlSoftDB;
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using PDA_Public;
using NPOI.SS.UserModel;
public partial class OutExcel : System.Web.UI.Page
{
private OleDatabase db;
protected void Page_Load(object sender, EventArgs e)
{
//在此处放置用户代码以初始化页面
string ConnStr = ConfigurationSettings.AppSettings["ConnStr"];
db = new OleDatabase(ConnStr);
string thetype = Request["type"];
string Rand = Request["Rand"];
if (thetype.Equals("XY_Visit"))
{
string Search_Type = pieces.getRequest(this, "Search_Type");
string Search_VISITTYPE = pieces.getRequest(this, "Search_VISITTYPE");
string Search_CLASSID = pieces.getRequest(this, "Search_CLASSID");
string Search_FromID = pieces.getRequest(this, "Search_FromID");
string Search_USERNAME = pieces.getRequest(this, "Search_USERNAME");
string Search_btm = pieces.getRequest(this, "Search_btm");
string Search_etm = pieces.getRequest(this, "Search_etm");
try
{
string tablename = "YY_VISITINFO_" + DateTime.Parse(Search_btm).ToString("yyyyMM");
string sql;
if (Search_Type == "PV")
{
sql = "SELECT a.*,b.CLASSNAME,c.XYNAME FROM " + tablename + " a left join XY_CLASS b on a.CLASSID=b.ID left join JC_FROM c on a.FromID=c.ID where 1=1";
if (Search_VISITTYPE != "")
{
sql += " and a.VISITTYPE=" + Search_VISITTYPE + "";
}
if (Search_CLASSID != "")
{
sql += " and (a.CLASSID=" + Search_CLASSID + " or CHARINDEX( '," + Search_CLASSID + ",',',' + b.PARENTPATH + ',')>0)";
}
if (Search_FromID != "")
{
sql += " and a.FromID=" + Search_FromID + "";
}
if (Search_USERNAME != "")
{
sql += " and a.USERNAME like '%" + Search_USERNAME + "%'";
}
if (Search_btm != "")
{
sql += " and cast(CONVERT(varchar, a.VISITTIME, 23) as datetime)>='" + Search_btm + "'";
}
if (Search_etm != "")
{
sql += " and cast(CONVERT(varchar, a.VISITTIME, 23) as datetime)<='" + Search_etm + "'";
}
sql += "order by a.id desc";
}
else
{
sql =
"SELECT VISITDay,IP from (SELECT cast(CONVERT(varchar, a.VISITTIME, 23) as datetime) as VISITDay,a.IP FROM " + tablename + " a left join XY_CLASS b on a.CLASSID=b.ID where 1=1";
if (Search_VISITTYPE != "")
{
sql += " and a.VISITTYPE=" + Search_VISITTYPE + "";
}
if (Search_CLASSID != "")
{
sql += " and (a.CLASSID=" + Search_CLASSID + " or CHARINDEX( '," + Search_CLASSID + ",',',' + b.PARENTPATH + ',')>0)";
}
if (Search_FromID != "")
{
sql += " and a.FromID=" + Search_FromID + "";
}
if (Search_USERNAME != "")
{
sql += " and a.USERNAME like '%" + Search_USERNAME + "%'";
}
if (Search_btm != "")
{
sql += " and cast(CONVERT(varchar, a.VISITTIME, 23) as datetime)>='" + Search_btm + "'";
}
if (Search_etm != "")
{
sql += " and cast(CONVERT(varchar, a.VISITTIME, 23) as datetime)<='" + Search_etm + "'";
}
sql += "group by cast(CONVERT(varchar, a.VISITTIME, 23) as datetime) ,a.IP) t order by VISITDay desc";
}
DataSet ds = db.GetDataSet(sql);
DataTable dt = ds.Tables[0];
if (dt.Rows.Count == 0)
{
Page.RegisterClientScriptBlock("", "<script>alert(\"查无数据。\");parent.closedialog();</script>");
}
else
{
string SheetName = Search_Type + "VISITINFO";
string filename = SheetName + DateTime.Now.ToString("yyyyMMddHHmmss");
if (File.Exists(Server.MapPath("Excel/" + filename + ".xls")))
{
File.Delete(Server.MapPath("Excel/" + filename + ".xls"));
}
IWorkbook workbook = new HSSFWorkbook();
ICellStyle[] style = GetHSSFCellStyle(workbook);
FileStream fs =
new FileStream(Server.MapPath("Excel/" + filename + ".xls"), FileMode.Create, FileAccess.ReadWrite);
int nowpage = 0;
int pagecount = 60000;
while (nowpage * pagecount < dt.Rows.Count)
{
string pagename;
if (nowpage == 0)
pagename = SheetName;
else
pagename = SheetName + (nowpage + 1);
ISheet sheet = workbook.CreateSheet(pagename);
for (int i = 0; i < 8; i++)
{
sheet.SetColumnWidth(i, 4800);
}
int rowindex = 0;
int maxcount = Math.Min(dt.Rows.Count, (nowpage + 1) * pagecount);
if (Search_Type == "PV")
{
CreateRow(ref sheet, rowindex++, style, new string[] { "访问时间", "登录手机", "访问端", "访问栏目", "访问来源", "访问IP", "浏览器", "访问URL" }, 0);
}
else
{
CreateRow(ref sheet, rowindex++, style, new string[] { "访问时间", "访问IP"}, 0);
}
for (int i = nowpage * pagecount; i < maxcount; i++)
{
DataRow row = dt.Rows[i];
if (Search_Type == "PV")
{
CreateRow(ref sheet, rowindex++, style,
new string[] { row["VISITTIME"].ToString().Trim(),
row["USERNAME"].ToString().Trim(),
pieces.VISITTYPEtostr(row["VISITTYPE"].ToString().Trim()),
row["CLASSNAME"].ToString().Trim(),
row["XYNAME"].ToString().Trim(),
row["IP"].ToString().Trim(),
row["MOBILEAGENT"].ToString().Trim(),
row["Visit_URL"].ToString().Trim() },
2);
}
else
{
CreateRow(ref sheet, rowindex++, style,
new string[] { row["VISITDay"].ToString().Trim(),
row["IP"].ToString().Trim() },
2);
}
}
nowpage++;
}
workbook.Write(fs);
fs.Flush();
workbook = null;
fs.Close();
fs.Dispose();
Page.RegisterClientScriptBlock("", "<script>parent.closedialog();window.location.replace(\"Excel/" + filename + ".xls\");</script>");
}
}
catch (Exception exp)
{
Page.RegisterClientScriptBlock("", "<script>alert(\"导出出现以下错误:" + exp.Message + "请与管理员联系。\");parent.closedialog();</script>");
}
}
else if (thetype.Equals("GKDYY_ZCXX"))
{
string Search_Title = pieces.getRequest(this, "Search_Title");
try
{
string sql = "select a.* from GKDYY_USER a where 1=1";
if (Search_Title != "")
{
sql = sql + "and a.USERNAME like '%" + Search_Title + "%'";
}
sql = sql + " order by a.CreateTime desc";
DataSet ds = db.GetDataSet(sql);
DataTable dt = ds.Tables[0];
if (dt.Rows.Count == 0)
{
Page.RegisterClientScriptBlock("", "<script>alert(\"查无数据。\");parent.closedialog();</script>");
}
else
{
string SheetName = "USER";
string filename = SheetName + DateTime.Now.ToString("yyyyMMddHHmmss");
if (File.Exists(Server.MapPath("Excel/" + filename + ".xls")))
{
File.Delete(Server.MapPath("Excel/" + filename + ".xls"));
}
IWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet(SheetName);
ICellStyle[] style = GetHSSFCellStyle(workbook);
int rowindex = 0;
for (int i = 0; i < 4; i++)
{
sheet.SetColumnWidth(i, 5800);
}
CreateRow(ref sheet, rowindex++, style, new string[] { "用户名", "登录次数", "创建时间", "最后登录时间"}, 0);
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow row = dt.Rows[i];
CreateRow(ref sheet, rowindex++, style,
new string[] {
row["USERNAME"].ToString().Trim(),
row["LOGINTIMES"].ToString().Trim(),
row["CreateTime"].ToString().Trim(),
row["LASTLOGINTIME"].ToString().Trim(),
},
1);
}
FileStream fs =
new FileStream(Server.MapPath("Excel/" + filename + ".xls"), FileMode.Create, FileAccess.ReadWrite);
workbook.Write(fs);
fs.Flush();
workbook = null;
fs.Close();
fs.Dispose();
Page.RegisterClientScriptBlock("", "<script>parent.closedialog();window.location.replace(\"Excel/" + filename + ".xls\");</script>");
}
}
catch (Exception exp)
{
Page.RegisterClientScriptBlock("", "<script>alert(\"导出出现以下错误:" + exp.Message + "请与管理员联系。\");parent.closedialog();</script>");
}
}
else if (thetype.Equals("YY_RevSMS"))
{
string search_mobilenum = pieces.getRequest(this, "search_mobilenum");
string Search_btm = pieces.getRequest(this, "Search_btm");
string Search_etm = pieces.getRequest(this, "Search_etm");
try
{
string sql = "SELECT * FROM YY_RevSMS where 1=1";
if (search_mobilenum != "")
{
sql = sql + " and (MOBILENUM like '%" + search_mobilenum + "%') ";
}
if (Search_btm != "")
{
sql = sql + " and cast(CONVERT(varchar, CreateTime, 23) as datetime)>='" + Search_btm + "'";
}
if (Search_etm != "")
{
sql = sql + " and cast(CONVERT(varchar, CreateTime, 23) as datetime)<='" + Search_etm + "'";
}
sql = sql + " order by ID";
DataSet ds = db.GetDataSet(sql);
DataTable dt = ds.Tables[0];
if (dt.Rows.Count == 0)
{
Page.RegisterClientScriptBlock("", "<script>alert(\"查无数据。\");parent.closedialog();</script>");
}
else
{
string SheetName = "RevSMS";
string filename = SheetName + DateTime.Now.ToString("yyyyMMddHHmmss");
if (File.Exists(Server.MapPath("Excel/" + filename + ".xls")))
{
File.Delete(Server.MapPath("Excel/" + filename + ".xls"));
}
IWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet(SheetName);
ICellStyle[] style = GetHSSFCellStyle(workbook);
int rowindex = 0;
for (int i = 0; i < 9; i++)
{
sheet.SetColumnWidth(i, 4800);
}
CreateRow(ref sheet, rowindex++, style, new string[] { "短信接收时间", "发送手机", "短信内容" }, 0);
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow row = dt.Rows[i];
CreateRow(ref sheet, rowindex++, style,
new string[] {
row["SMSTIME"].ToString().Trim(),
row["MOBILENUM"].ToString().Trim(),
row["Content"].ToString().Trim(),
},
1);
}
FileStream fs =
new FileStream(Server.MapPath("Excel/" + filename + ".xls"), FileMode.Create, FileAccess.ReadWrite);
workbook.Write(fs);
fs.Flush();
workbook = null;
fs.Close();
fs.Dispose();
Page.RegisterClientScriptBlock("", "<script>parent.closedialog();window.location.replace(\"Excel/" + filename + ".xls\");</script>");
}
}
catch (Exception exp)
{
Page.RegisterClientScriptBlock("", "<script>alert(\"导出出现以下错误:" + exp.Message + "请与管理员联系。\");parent.closedialog();</script>");
}
}
else if (thetype.Equals("mb"))
{
try
{
string filename = "导入数据模板";
IWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet(filename);
ICellStyle[] style = GetHSSFCellStyle(workbook);
FileStream fs = new FileStream(Server.MapPath("Excel/" + filename + ".xlsx"), FileMode.Create, FileAccess.ReadWrite);
workbook.Write(fs);
fs.Flush();
workbook = null;
fs.Close();
fs.Dispose();
Page.RegisterClientScriptBlock("", "<script>parent.closedialog();window.location.replace(\"Excel/" + filename + ".xls\");</script>");
}
catch (Exception exp)
{
Page.RegisterClientScriptBlock("", "<script>alert(\"导出出现以下错误:" + Public_Class.NoHTML(exp.Message) + "请与管理员联系。\");parent.closedialog();</script>");
}
}
}
private void CreateRow(ref ISheet sheet, int rowindex,ICellStyle[] style, string[] v, int[] s)
{
IRow hr = sheet.CreateRow(rowindex);
for (int i = 0; i < v.Length; i++)
{
ICell ic = hr.CreateCell(i);
ic.CellStyle = style[s[i]];
if (v[i].Length > 0)
ic.SetCellValue(v[i]);
}
}
private void CreateRow(ref ISheet sheet, int rowindex, ICellStyle[] style, string[] v, int s)
{
IRow hr = sheet.CreateRow(rowindex);
for (int i = 0; i < v.Length; i++)
{
ICell ic = hr.CreateCell(i);
ic.CellStyle = style[s];
if (v[i].Length > 0)
ic.SetCellValue(v[i]);
}
}
private ICellStyle[] GetHSSFCellStyle(IWorkbook workbook)
{
ICellStyle[] style = new ICellStyle[5];
style[0] = workbook.CreateCellStyle();
style[0].Alignment = HorizontalAlignment.CENTER ;// 左右居中
style[0].VerticalAlignment =VerticalAlignment.CENTER;// 上下居中
style[0].WrapText = true;
style[0].LeftBorderColor =HSSFColor.BLACK.index;
style[0].BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN;
style[0].RightBorderColor = HSSFColor.BLACK.index;
style[0].BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;
style[0].BottomBorderColor = HSSFColor.BLACK.index; // 设置单元格的边框颜色.
style[0].BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;
style[0].TopBorderColor = HSSFColor.BLACK.index; // 设置单元格的边框颜色.
style[0].BorderTop = NPOI.SS.UserModel.BorderStyle.THIN;
style[0].FillPattern =FillPatternType.SOLID_FOREGROUND;
style[0].FillForegroundColor = HSSFColor.WHITE.index;
IFont headfont = workbook.CreateFont();
headfont.Boldweight = (short)FontBoldWeight.BOLD;
style[0].SetFont(headfont);
style[1] = workbook.CreateCellStyle();
style[1].Alignment = HorizontalAlignment.CENTER;// 左右居中
style[1].VerticalAlignment = VerticalAlignment.CENTER;// 上下居中
style[1].WrapText = true;
style[1].LeftBorderColor = HSSFColor.BLACK.index;
style[1].BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN;
style[1].RightBorderColor = HSSFColor.BLACK.index;
style[1].BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;
style[1].BottomBorderColor = HSSFColor.BLACK.index; // 设置单元格的边框颜色.
style[1].BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;
style[1].TopBorderColor = HSSFColor.BLACK.index; // 设置单元格的边框颜色.
style[1].BorderTop = NPOI.SS.UserModel.BorderStyle.THIN;
style[1].FillPattern = FillPatternType.SOLID_FOREGROUND;
style[1].FillForegroundColor = HSSFColor.WHITE.index;// 设置单元格的背景颜色.
style[2] = workbook.CreateCellStyle();
style[2].Alignment = HorizontalAlignment.LEFT;// 左右居中
style[2].VerticalAlignment = VerticalAlignment.CENTER;// 上下居中
style[2].WrapText = true;
style[2].LeftBorderColor = HSSFColor.BLACK.index;
style[2].BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN;
style[2].RightBorderColor = HSSFColor.BLACK.index;
style[2].BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;
style[2].BottomBorderColor = HSSFColor.BLACK.index; // 设置单元格的边框颜色.
style[2].BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;
style[2].TopBorderColor = HSSFColor.BLACK.index; // 设置单元格的边框颜色.
style[2].BorderTop = NPOI.SS.UserModel.BorderStyle.THIN;
style[2].FillPattern = FillPatternType.SOLID_FOREGROUND;
style[2].FillForegroundColor = HSSFColor.WHITE.index;// 设置单元格的背景颜色.
style[3] = workbook.CreateCellStyle();
style[3].Alignment = HorizontalAlignment.LEFT;// 左右居中
style[3].VerticalAlignment = VerticalAlignment.CENTER;// 上下居中
style[3].WrapText = true;
style[3].LeftBorderColor = HSSFColor.BLACK.index;
style[3].BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN;
style[3].RightBorderColor = HSSFColor.BLACK.index;
style[3].BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;
style[3].BottomBorderColor = HSSFColor.BLACK.index; // 设置单元格的边框颜色.
style[3].BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;
style[3].TopBorderColor = HSSFColor.BLACK.index; // 设置单元格的边框颜色.
style[3].BorderTop = NPOI.SS.UserModel.BorderStyle.THIN;
style[3].FillPattern = FillPatternType.SOLID_FOREGROUND;
style[3].FillForegroundColor = HSSFColor.YELLOW.index;// 设置单元格的背景颜色.
style[4] = workbook.CreateCellStyle();
style[4].Alignment = HorizontalAlignment.LEFT;// 左右居中
style[4].VerticalAlignment = VerticalAlignment.CENTER;// 上下居中
style[4].WrapText = true;
style[4].LeftBorderColor = HSSFColor.BLACK.index;
style[4].BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN;
style[4].RightBorderColor = HSSFColor.BLACK.index;
style[4].BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;
style[4].BottomBorderColor = HSSFColor.BLACK.index; // 设置单元格的边框颜色.
style[4].BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;
style[4].TopBorderColor = HSSFColor.BLACK.index; // 设置单元格的边框颜色.
style[4].BorderTop = NPOI.SS.UserModel.BorderStyle.THIN;
style[4].FillPattern = FillPatternType.SOLID_FOREGROUND;
style[4].FillForegroundColor = HSSFColor.RED.index;// 设置单元格的背景颜色.
return style;
}
private string NetworkTypetostr(string index)
{
if (index == "1")
return "2G";
else if (index == "2")
return "3G";
else
return "";
}
}