using KYZWeb.Common;
using Liger.Data;
//using Microsoft.Office.Interop.Excel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Web;
namespace KYZWeb.Modules.DeviceMan
{
/// <summary>
/// ExportHandler 的摘要说明
/// </summary>
public class ExportHandler : IHttpHandler
{
public static DbContext DB = DbHelper.Db;
public void ProcessRequest(HttpContext context)
{
context.Response.ContentType = "text/plain";
string startdatetime = context.Request.Params["startdatetime"];
string enddatetime = context.Request.Params["enddatetime"];
string user_name = context.Request.Params["user_name"];
StringBuilder sql = new StringBuilder();
sql.Append(@" select * ");
sql.Append(" from dbo.vi_card_run WHERE 1=1");
if (!string.IsNullOrEmpty(startdatetime))
{
sql.Append(" and card_datetime >= '" + startdatetime + "'");
}
if (!string.IsNullOrEmpty(enddatetime))
{
sql.Append(" and card_datetime <= '" + enddatetime + "'");
}
if (!string.IsNullOrEmpty(user_name))
{
sql.Append(" and user_name like '%" + user_name + "%'");
}
sql.Append(" order by card_datetime desc");
//创建command
var dt = DB.Db.ExecuteDataSet(CommandType.Text, sql.ToString()).Tables[0];
StringWriter sw = new StringWriter();
//sw.WriteLine("姓名\t工号\t打卡时间\t打卡地点\t打卡设备");
sw.WriteLine("<html xmlns:x=\"urn:schemas-microsoft-com:office:excel\">");
sw.WriteLine("<head>");
sw.WriteLine("<!--[if gte mso 9]>");
sw.WriteLine("<xml>");
sw.WriteLine(" <x:ExcelWorkbook>");
sw.WriteLine(" <x:ExcelWorksheets>");
sw.WriteLine(" <x:ExcelWorksheet>");
sw.WriteLine(" <x:Name>" + "sheetName" + "</x:Name>");
sw.WriteLine(" <x:WorksheetOptions>");
sw.WriteLine(" <x:Print>");
sw.WriteLine(" <x:ValidPrinterInfo />");
sw.WriteLine(" </x:Print>");
sw.WriteLine(" </x:WorksheetOptions>");
sw.WriteLine(" </x:ExcelWorksheet>");
sw.WriteLine(" </x:ExcelWorksheets>");
sw.WriteLine("</x:ExcelWorkbook>");
sw.WriteLine("</xml>");
sw.WriteLine("<![endif]-->");
sw.WriteLine("</head>");
sw.WriteLine("<body>");
sw.WriteLine("<table border='1'>");
sw.WriteLine(" <tr >");
sw.WriteLine(" <td><strong>姓名</strong></td>");
sw.WriteLine(" <td><strong>工号</strong></td>");
sw.WriteLine(" <td><strong>打卡时间</strong></td>");
sw.WriteLine(" <td><strong>打卡地点</strong></td>");
sw.WriteLine(" <td><strong>打卡设备</strong></td>");
sw.WriteLine(" </tr>");
foreach (DataRow dr in dt.Rows)
{
//sw.WriteLine(dr["user_name"] + "\t" + dr["user_code"] + "\t" + dr["card_datetime"]+"\t"+dr["card_address"]+"\t"+dr["card_device_name"]);
sw.WriteLine(" <tr>");
sw.WriteLine(" <td>" + dr["user_name"] + "</td>");
sw.WriteLine(" <td>" + dr["user_code"] + "</td>");
sw.WriteLine(" <td>" + dr["card_datetime"] + "</td>");
sw.WriteLine(" <td>" + dr["area_name"] + "</td>");
sw.WriteLine(" <td>" + dr["device_name"] + "</td>");
sw.WriteLine(" </tr>");
}
sw.WriteLine("</table>");
sw.WriteLine("</body>");
sw.WriteLine("</html>");
sw.Close();
context.Response.Clear();
context.Response.Buffer = true;
context.Response.Charset = "UTF-8";
context.Response.AddHeader("Content-Disposition", "attachment; filename=" + "card" + ".xls");
context.Response.ContentType = "application/ms-excel";
context.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
context.Response.Write(sw);
context.Response.End();
//context.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
//context.Response.AddHeader("Content-Disposition", "attachment; filename=" + System.Web.HttpUtility.UrlEncode("card", System.Text.Encoding.UTF8) + ".xls");
//context.Response.ContentType = "application/ms-excel";
//context.Response.Write(sw);
//context.Response.End();
//DataTabletoExcel(dt, "C:\\中国.XLS"); //调用自定义的函数，当然输出文件你可以随便写
}
public static void DataTabletoExcel(System.Data.DataTable tmpDataTable, string strFileName)
{
if (tmpDataTable == null)
return;
int rowNum = tmpDataTable.Rows.Count;
int columnNum = tmpDataTable.Columns.Count;
int rowIndex = 1;
int columnIndex = 0;
//Application xlApp = new ApplicationClass();
//xlApp.DefaultFilePath = "";
//xlApp.DisplayAlerts = true;
//xlApp.SheetsInNewWorkbook = 1;
//Workbook xlBook = xlApp.Workbooks.Add(true);
////将DataTable的列名导入Excel表第一行
//foreach (DataColumn dc in tmpDataTable.Columns)
//{
// columnIndex++;
// xlApp.Cells[rowIndex, columnIndex] = dc.ColumnName;
//}
////将DataTable中的数据导入Excel中
//for (int i = 0; i < rowNum; i++)
//{
// rowIndex++;
// columnIndex = 0;
// for (int j = 0; j < columnNum; j++)
// {
// columnIndex++;
// xlApp.Cells[rowIndex, columnIndex] = tmpDataTable.Rows[i][j].ToString();
// }
//}
////xlBook.SaveCopyAs(HttpUtility.UrlDecode(strFileName, System.Text.Encoding.UTF8));
//xlBook.SaveCopyAs(strFileName);
}
public bool IsReusable
{
get
{
return false;
}
}
}
}