protected void btnExportExcel_ServerClick(object sender, EventArgs e)
{
HttpCookie getCookies = Request.Cookies["UserLogin"];
if (getCookies != null)
{
var timeSearch = string.Empty;
if (string.IsNullOrEmpty(txtTimeSelect.Value.Trim()))
{
timeSearch = "NowMonth";
}
else
{
timeSearch = txtTimeSelect.Value.Trim();
}
var getFenXiaoExtractData = SearchDataClass.ExportFenXiaoExtractInfoData(timeSearch);
//创建一个workbookdesigner对象
WorkbookDesigner designer = new WorkbookDesigner();
//制定报表模板
designer.Open(Server.MapPath(@"model\FenXiaoExtractList.xls"));
//设置实体类对象
designer.SetDataSource("Export", getFenXiaoExtractData);
//根据数据源处理生成报表内容
designer.Process();
//客户端保存的文件名
string fileName = HttpUtility.UrlEncode("月分销提成金额报表统计导出") + "_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
designer.Save(fileName, SaveType.OpenInExcel, FileFormatType.Excel2003, Response);
Response.Flush();
Response.Close();
designer = null;
Response.End();
}
}
public static void StartCreate(List <ReportTemplate> reportlist)
{
//将list集合转为DataTable方便模板方法使用
ListToDataTableHelper listHelper = new ListToDataTableHelper();
DataTable dt = listHelper.ToDataTable(reportlist);
string basepath = Directory.GetCurrentDirectory();
string path = $"{basepath}\\SummaryTemplate.xlsx";
WorkbookDesigner designer = new WorkbookDesigner();
designer.Open(path);
designer.SetDataSource(dt);
//根据数据源处理生成报表内容
designer.Process();
//保存Excel文件
string fileToSave = GetFilePath();
string filename = "项目统计表-江宁分公司- " + DateTime.Now.ToString("yyyyMMddhhmmss") + ".xls";
string fullpath = fileToSave + "\\" + filename;//完整路径
designer.Save(fullpath, FileFormatType.Excel2003);
//提示“保存成功，是否立即打开”
if (MessageBox.Show("已为您保存至:" + fullpath + "\r\n是否立即打开？", "保存成功！", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes)
{
//调用系统进程打开文件
System.Diagnostics.Process.Start(fullpath);
}
}
protected void btnExportExcel_ServerClick(object sender, EventArgs e)
{
HttpCookie getCookies = Request.Cookies["UserLogin"];
if (getCookies != null)
{
//var timeSearch = string.Empty;
//if (string.IsNullOrEmpty(txtTimeSelect.Value.Trim()))
//{
// timeSearch = "NowMonth";
//}
//else
//{
// timeSearch = txtTimeSelect.Value.Trim();
//}
var getMemberExtractData = SearchDataClass.GetSearchSaleCheckData(drpShopSet.SelectedValue, txtTimeSelect.Value.Trim());
//创建一个workbookdesigner对象
WorkbookDesigner designer = new WorkbookDesigner();
//制定报表模板
designer.Open(Server.MapPath(@"model\cocheckreport.xls"));
//设置实体类对象
designer.SetDataSource("Export", getMemberExtractData);
//根据数据源处理生成报表内容
designer.Process();
//客户端保存的文件名
string fileName = HttpUtility.UrlEncode("厂家核对报表导出") + "_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
designer.Save(fileName, SaveType.OpenInExcel, FileFormatType.Excel2003, Response);
Response.Flush();
Response.Close();
designer = null;
Response.End();
}
}
/// <summary>
/// ImpExcel
/// </summary>
private void ImpExcel()
{
string sql = string.Empty;
string path = RequestData.Get <string>("path");
string fileName = RequestData.Get <string>("fileName");
string xlsName = fileName + "_" + System.DateTime.Now.ToString("yyyMMddhhmmss");
DataTable forExcelDt = DataHelper.QueryDataTable(sql);
if (forExcelDt.Rows.Count > 0)
{
forExcelDt.TableName = "data";
WorkbookDesigner designer = new WorkbookDesigner();
string xlsMdlPath = Server.MapPath(path);
designer.Open(xlsMdlPath);
designer.SetDataSource(forExcelDt);
designer.Process();
Aspose.Cells.Worksheet ws = designer.Workbook.Worksheets.GetSheetByCodeName(fileName);
string newXls = xlsName + ".xls";
System.IO.DirectoryInfo xlspath = new System.IO.DirectoryInfo(Server.MapPath("/Excel/tempexcel"));
ExcelHelper.deletefile(xlspath);
designer.Save(Server.MapPath("/Excel/tempexcel") + "\\" + newXls, FileFormatType.Excel2003);
this.PageState.Add("fileName", "/Excel/tempexcel/" + newXls);
}
}
public string ExcuteXls <T>(List <T> list, Func <WorkbookDesigner, List <T>, WorkbookDesigner> func) where T : new()
{
this.DeleteOutPutFiles();
var designer = new WorkbookDesigner();
if (File.Exists(this._templateFile))
{
designer.Open(this._templateFile);
}
designer = func(designer, list);
designer.Save(HttpContext.Current.Server.MapPath($"~//App_Data//Out//{this._fileName}"), FileFormatType.Excel2003XML);
return(HttpContext.Current.Server.MapPath($"~//App_Data//Out//{this._fileName}"));
}
//
private void CrateExcel(DataTable forExcelDt, string SheetName, string FullPath, string xlsName)
{
forExcelDt.TableName = "data";
WorkbookDesigner designer = new WorkbookDesigner();
string xlsMdlPath = Server.MapPath(FullPath);
designer.Open(xlsMdlPath);
designer.SetDataSource(forExcelDt);
designer.Process();
Aspose.Cells.Worksheet ws = designer.Workbook.Worksheets.GetSheetByCodeName(SheetName);
string newXls = xlsName + ".xls";
System.IO.DirectoryInfo xlspath = new System.IO.DirectoryInfo(Server.MapPath("../Excel/tempexcel"));
ExcelHelper.deletefile(xlspath);
designer.Save(Server.MapPath("../Excel/tempexcel") + "\\" + newXls, FileFormatType.Excel2003);
this.PageState.Add("fileName", "../Excel/tempexcel/" + newXls);
}
private void ImpExcel()
{
string where = string.Empty;
//权限过滤
var Ent = SurveyQuestion.TryFind(SurveyId);
if (Ent != null && Ent.IsFixed == "2")
{
CommPowerSplit PS = new CommPowerSplit();
if (PS.IsInAdminsRole(UserInfo.UserID) || PS.IsAdmin(UserInfo.LoginName) || PS.IsHR(UserInfo.UserID, UserInfo.LoginName))
{
}
else
{
UserContextInfo UC = new UserContextInfo();
where += " and D.Pk_corp='" + UC.GetUserCurrentCorpId(UserInfo.UserID) + "' ";
}
}
tmpSQL = tmpSQL.Replace("##QUERY##", where);
tmpSQL = tmpSQL.Replace("HR_OA_MiddleDB", Global.HR_OA_MiddleDB);
string sql = string.Format(tmpSQL, SurveyId);
string path = RequestData.Get <string>("path");
string fileName = RequestData.Get <string>("fileName");
string xlsName = fileName + "_" + System.DateTime.Now.ToString("yyyMMddhhmmss");
DataTable forExcelDt = DataHelper.QueryDataTable(sql);
if (forExcelDt.Rows.Count > 0)
{
forExcelDt.TableName = "data";
WorkbookDesigner designer = new WorkbookDesigner();
string xlsMdlPath = Server.MapPath(path);
designer.Open(xlsMdlPath);
designer.SetDataSource(forExcelDt);
designer.Process();
Aspose.Cells.Worksheet ws = designer.Workbook.Worksheets.GetSheetByCodeName(fileName);
string newXls = xlsName + ".xls";
System.IO.DirectoryInfo xlspath = new System.IO.DirectoryInfo(Server.MapPath("../Excel/tempexcel"));
ExcelHelper.deletefile(xlspath);
designer.Save(Server.MapPath("../Excel/tempexcel") + "\\" + newXls, FileFormatType.Excel2003);
this.PageState.Add("fileName", "/Excel/tempexcel/" + newXls);
}
}
public string ExcuteXls <T>(List <T> list) where T : new()
{
this.DeleteOutPutFiles();
var designer = new WorkbookDesigner();
if (File.Exists(this._templateFile))
{
designer.Open(this._templateFile);
}
if (list != null)
{
designer.SetDataSource("Datas", list);
designer.Process();
designer.Save(this._fileName, FileFormatType.Excel2003XML);
}
return(HttpContext.Current.Server.MapPath($"~//App_Data//Out//{this._fileName}"));
}
protected void btnExportExcel_ServerClick(object sender, EventArgs e)
{
HttpCookie getCookies = Request.Cookies["UserLogin"];
if (getCookies != null)
{
DateTime dt = DateTime.Now;
DateTime dtBegin = new DateTime();
DateTime dtEnd = new DateTime();
if (string.IsNullOrEmpty(txtTimeSelect.Value.Trim()))
{
DateTime startMonth = dt.AddDays(1 - dt.Day); //本月月初
DateTime endMonth = startMonth.AddMonths(1).AddDays(-1); //本月月末
dtBegin = startMonth;
dtEnd = endMonth;
}
else
{
string[] strDate = txtTimeSelect.Value.Trim().Split('-');
dtBegin = Convert.ToDateTime(strDate[0]);
dtEnd = Convert.ToDateTime(strDate[1]);
}
var getFinanceMonthData = SearchDataClass.ExportFinanceMonthInfoListData(dtBegin, dtEnd);
//创建一个workbookdesigner对象
WorkbookDesigner designer = new WorkbookDesigner();
//制定报表模板
designer.Open(Server.MapPath(@"model\FinanceMonthList.xls"));
//设置实体类对象
designer.SetDataSource("Export", getFinanceMonthData);
//根据数据源处理生成报表内容
designer.Process();
//客户端保存的文件名
string fileName = HttpUtility.UrlEncode("月销售金额报表统计导出") + "_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
designer.Save(fileName, SaveType.OpenInExcel, FileFormatType.Excel2003, Response);
Response.Flush();
Response.Close();
designer = null;
Response.End();
}
}
public static BatchDownloadResponse DataTableToExcel(string templateFileName, string fileName, DataTable dt)
{
if (!string.IsNullOrEmpty(templateFileName))
{
templateFileName = templateFileName.Replace("\\\\", "\\");
}
if (!string.IsNullOrEmpty(fileName))
{
fileName = fileName.Replace("\\\\", "\\");
}
BatchDownloadResponse response = new BatchDownloadResponse(fileName);
if (dt == null)
{
response.AddError(0, "数据为空，请检查！");
return(response);
}
string licPath = System.Configuration.ConfigurationManager.AppSettings["AsposeLicPath"];
License lic = new License();
if (!string.IsNullOrEmpty(licPath))
{
lic.SetLicense(licPath);
}
WorkbookDesigner wd = new WorkbookDesigner();
wd.Open(templateFileName);
wd.SetDataSource(dt);
wd.Process();
wd.Save(fileName, FileFormatType.Excel2007Xlsx);
wd = null;
FileInfo fi = new FileInfo(fileName);
response.FileName = fi.Name;
return(response);
}
public void ExportXls()
{
HBBLL hb = new HBBLL();
DataTable dt = Util.LinqQueryToDataTable <LeaderDownMine>(ldm);
dt.TableName = "tt";
var s = Aspose.Cells.CellsHelper.GetVersion();
WorkbookDesigner designer = new WorkbookDesigner();
designer.Open(MapPath("tt.xls"));
//数据源
designer.SetDataSource(dt);
//报表标题
designer.SetDataSource("Title", string.Format("{0}副总以上领导下井人员情况汇总表", cbbDept.SelectedItem.Value == "-1"?"各矿":cbbDept.SelectedItem.Text));
designer.SetDataSource("DownDate", string.Format("{0}年{1}月)", cboYear.SelectedItem.Value, cboMonth.SelectedItem.Value.PadLeft(2, '0')));
designer.Process();
designer.Save(string.Format("各矿副总以上领导下井人员情况汇总表{0}.xls", DateTime.Now.ToString("yyyyMMddHHmmssffff")), SaveType.OpenInExcel, FileFormatType.Excel97To2003, Response);
Response.Flush();
Response.Close();
designer = null;
Response.End();
}
private void ImpExcel()
{
string WorkFlowState = RequestData.Get <string>("WorkFlowState");
string where = string.Empty;
//审批意见
if (!string.IsNullOrEmpty(WorkFlowState))
{
where += " and A.WorkFlowState='" + WorkFlowState + "' ";
}
CommPowerSplit ps = new CommPowerSplit();
if (ps.IsNoticeRole(UserInfo.UserID, UserInfo.LoginName) || ps.IsHR(UserInfo.UserID, UserInfo.LoginName))
{ //管理员或HR组
}
else
{
where += AppealUsrAuth();
}
//A.TravelMoney, 旅游费用/每人
string sql = @"select distinct
A.UserName,A.WorkNo,A.CompanyName,A.DeptName,A.Sex,
A.TravelAddr,A.TravelTime as TimeSeg, A.XLMoney as TravelMoney,
case
when HaveFamily='Y' then '是'
when HaveFamily='N' then '否'
end As IsFamily, convert(varchar(10),A.ApplyTime ,120) As ApplyTime,
C.Indutydate As IndutyDate,
datediff(year,C.Indutydate,getdate()) As WorkYear,
B.Name As Fname,B.Sex As OSex, B.Age As OAge,cast(B.Height as varchar(10)) Height,
Case
when WorkFlowState='1' then '未处理'
when WorkFlowState='-1' then '不同意'
when WorkFlowState='2' then '同意'
when WorkFlowState='Exception' then '异常'
End As State
from FL_Culture..UsrTravelWelfare As A
left join FL_Culture..UsrTravelInfo As B
on A.Id=B.WelfareTravelId
left join FL_PortalHR..SysUser As C
on C.UserID=A.UserId
where (WorkFlowState='1' or WorkFlowState='2' or WorkFlowState='-1') and A.Id is not null";
sql = sql.Replace("FL_PortalHR", Global.AimPortalDB);
sql += where;
string path = RequestData.Get <string>("path");
string fileName = RequestData.Get <string>("fileName");
string xlsName = fileName + "_" + System.DateTime.Now.ToString("yyyMMddhhmmss");
DataTable forExcelDt = DataHelper.QueryDataTable(sql);
forExcelDt = DtDetail(forExcelDt);
if (forExcelDt.Rows.Count > 0)
{
forExcelDt.TableName = "data";
WorkbookDesigner designer = new WorkbookDesigner();
string xlsMdlPath = Server.MapPath(path);
designer.Open(xlsMdlPath);
designer.SetDataSource(forExcelDt);
designer.Process();
Aspose.Cells.Worksheet ws = designer.Workbook.Worksheets.GetSheetByCodeName(fileName);
string newXls = xlsName + ".xls";
System.IO.DirectoryInfo xlspath = new System.IO.DirectoryInfo(Server.MapPath("../Excel/tempexcel"));
ExcelHelper.deletefile(xlspath);
designer.Save(Server.MapPath("../Excel/tempexcel") + "\\" + newXls, FileFormatType.Excel2003);
this.PageState.Add("fileName", "../Excel/tempexcel/" + newXls);
}
}
protected void btnExportExcel_ServerClick(object sender, EventArgs e)
{
HttpCookie getCookies = Request.Cookies["UserLogin"];
if (getCookies != null)
{
//var timeSearch = string.Empty;
//if (string.IsNullOrEmpty(txtTimeSelect.Value.Trim()))
//{
// timeSearch = "NowMonth";
//}
//else
//{
// timeSearch = txtTimeSelect.Value.Trim();
//}
var getGoodsShipData = SearchDataClass.GetSearchGoodsShipData(txtOrderNo.Value, txtGoodsName.Value, txtBuyName.Value,
txtCompany.Value, txtGuDong.Value, txtZhanZhang.Value, txtTimeSelect.Value.Trim(), drpShopSet.SelectedValue, drpGoodShipStatus.SelectedValue);
//创建一个workbookdesigner对象
WorkbookDesigner designer = new WorkbookDesigner();
//制定报表模板
designer.Open(Server.MapPath(@"model\goodshipreport.xls"));
List <SearchGoodsShipData> newGoodShip = new List <SearchGoodsShipData>();
if (getGoodsShipData.Count > 0)
{
foreach (var item in getGoodsShipData)
{
SearchGoodsShipData info = new SearchGoodsShipData();
info.ShipStatus = item.GoodShipStatus == '0' ? "未发货" : "已发货";
info.GoodShipNo = item.GoodShipNo;
info.SjGuDongInfo = item.SjGuDongInfo == null ? "无上级股东信息" : item.SjGuDongInfo;
info.SjZhanZhangInfo = item.SjZhanZhangInfo == null ? "无上级站长信息" : item.SjZhanZhangInfo;
info.OrderNo = item.OrderNo;
info.OrderPrice = item.OrderPrice;
info.name = item.name;
info.tel = item.tel;
info.GoodsTitle = item.GoodsTitle;
info.GoodsCode = item.GoodsCode;
info.BuySumQty = item.BuySumQty;
info.housemoney = item.housemoney;
info.areacity = item.areacity;
info.address = item.address;
info.GoodsCompany = item.GoodsCompany;
info.GoodsPrice = item.GoodsPrice;
info.GoodsCost = item.GoodsCost;
info.GoodsSpec = item.GoodsSpec;
info.PaymentTime = item.PaymentTime;
info.juese = item.juese;
newGoodShip.Add(info);
}
}
//设置实体类对象
designer.SetDataSource("Export", newGoodShip);
//根据数据源处理生成报表内容
designer.Process();
//客户端保存的文件名
string fileName = HttpUtility.UrlEncode("商品发货表导出") + "_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
designer.Save(fileName, SaveType.OpenInExcel, FileFormatType.Excel2003, Response);
Response.Flush();
Response.Close();
designer = null;
Response.End();
}
}
/// <summary>
/// 热销排行榜导出
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void btnSkuExport_ServerClick(object sender, EventArgs e)
{
if (!string.IsNullOrEmpty(txtTimeSelect.Value))
{
HttpCookie getCookies = Request.Cookies["UserLogin"];
if (getCookies != null)
{
string[] strDate = txtTimeSelect.Value.Split('-');
var dtBegin = DateTime.Parse(strDate[0]).ToString("yyyy-MM-dd 00:00:00");
var dtEnd = DateTime.Parse(strDate[1]).ToString("yyyy-MM-dd 23:59:59");
using (EnshineUnionDataContext db = new EnshineUnionDataContext())
{
var getTopList =
(
//from a in psdc.ordersdetails
// join b in psdc.goods on a.buygoodsid equals b.goodsId into b_join
// from b in b_join.DefaultIfEmpty()
// where
// a.ordertime >= Convert.ToDateTime(dtBegin) && a.ordertime <= Convert.ToDateTime(dtEnd)
// //& a.CustomerID == customerid
// group new { a, b } by new
// {
// b.goodstitle,
// b.goodscode,
// b.goodsprice,
// b.goodscost,
// b.goodscompany,
// b.goodsspec
// } into g
// select new
// {
// GoodsCode = g.Key.goodscode,
// Goodsprice = g.Key.goodsprice,
// Goodscost = g.Key.goodscost,
// Goodscompany = g.Key.goodscompany,
// Goodsspec = g.Key.goodsspec,
// SumQty = g.Sum(p => p.a.buysumqty),
// GoodsTitle = g.Key.goodstitle
// }
(
from c in db.orders
join a in db.ordersdetails on c.orderno equals a.orderno
join b in db.goods
on new { a.buygoodsid, selectytsort = Convert.ToChar(a.selectytsort.ToString()) }
equals new { buygoodsid = b.goodsId, selectytsort = b.selectytsort }
where
c.ordertime >= Convert.ToDateTime(dtBegin) && c.ordertime <= Convert.ToDateTime(dtEnd)
group new { b, a } by new
{
b.goodscode,
b.goodsprice,
b.goodscost
} into g
select new
{
GoodsTitle = g.Max(p => p.b.goodstitle),
GoodsCode = g.Key.goodscode,
Goodsprice = (System.Decimal?)g.Key.goodsprice,
Goodscost = (System.Decimal?)g.Key.goodscost,
SumQty = (System.Int32?)g.Sum(p => p.a.buysumqty)
}
).Union
(
from c in db.orders
join a in db.ordersdetails on c.orderno equals a.orderno
join b in db.goodstuan
on new { a.buygoodsid, a.selectytsort }
equals new { buygoodsid = b.Id, b.selectytsort }
where
c.ordertime >= Convert.ToDateTime(dtBegin) && c.ordertime <= Convert.ToDateTime(dtEnd)
group new { b, a } by new
{
b.goodscode,
b.price,
b.costprice
} into g
select new
{
GoodsTitle = g.Max(p => p.b.title),
GoodsCode = g.Key.goodscode,
Goodsprice = (System.Decimal?)g.Key.price,
Goodscost = (System.Decimal?)g.Key.costprice,
SumQty = (System.Int32?)g.Sum(p => p.a.buysumqty)
}
)
).ToList();
var getData = getTopList.OrderByDescending(p => p.SumQty).ToList();
if (getData.Count() > 0)
{
// 创建一个workbookdesigner对象
WorkbookDesigner designer = new WorkbookDesigner();
// 制定报表模板
designer.Open(Server.MapPath(@"model\HotSkuExport.xls"));
//设置实体类对象
designer.SetDataSource("Export", getData);
// 根据数据源处理生成报表内容
designer.Process();
// 客户端保存的文件名
string fileName = DateTime.Parse(strDate[0]).ToString("yyyyMMdd") + "_" + DateTime.Parse(strDate[1]).ToString("yyyyMMdd") +
HttpUtility.UrlEncode("热销产品统计数据导出.xls");
designer.Save(fileName, SaveType.OpenInExcel, FileFormatType.Excel2003, Response);
Response.Flush();
Response.Close();
designer = null;
Response.End();
}
}
}
}
else
{
ClientScript.RegisterStartupScript(Page.GetType(), "", "<script>alert('请选择时间后在进行导出。');</script>");
}
}
private void ImpExcel()
{
var year = RequestData.Get("year") + "";
var WorkFlowState = RequestData.Get("WorkFlowState") + "";
var WelfareType = RequestData.Get("WelfareType") + "";
var month = RequestData.Get("month") + "";
var type = RequestData.Get("type") + "";
var DealState = RequestData.Get("DealState") + "";
string where = string.Empty;
if (string.IsNullOrEmpty(year))
{
year = DateTime.Now.Year + "";
where += " and Year(ApplyTime)=" + year + " ";
}
else
{
where += " and Year(ApplyTime)=" + year + " ";
}
//处理结果
if (!string.IsNullOrEmpty(DealState))
{
where += " and WorkFlowState='" + DealState + "' ";
}
//月份
if (!string.IsNullOrEmpty(month))
{
where += " and Month(ApplyTime)=" + month + " ";
}
//未处理
if (type == "n")
{
where += " and A.WorkFlowState='1' ";
}
else //已处理
{
where += " and (WorkFlowState='2' or WorkFlowState='-1') ";
}
//审批意见
if (!string.IsNullOrEmpty(WorkFlowState))
{
where += " and A.WorkFlowState='" + WorkFlowState + "' ";
}
CommPowerSplit ps = new CommPowerSplit();
if (!ps.IsNoticeRole(UserInfo.UserID, UserInfo.LoginName))
{
where += AppealUsrAuth();
}
string SQL = @" select distinct
Year(A.ApplyTime) As Year,Month(A.ApplyTime) As Month, A.UserName,A.WorkNo,A.CompanyName,A.DeptName,
convert(varchar(10),A.IndutyData,120) As IndutyDate,
A.Sex,A.OtherUserName,A.OtherIdentityCard,A.OSex,
C.UsrName as ChildName,C.Sex As ChildSex,C.IDCartNo AS ChildIDCard,C.IDType,
case
when A.WorkFlowState='2' then '同意'
when A.WorkFlowState='-1' then '不同意'
end As State,
case
when A.IsSingleChild='Y' then '是'
when A.IsSingleChild='N' then '否'
end As IsSingleChild,
case
when A.IsDoubleWorker='Y' then '是'
when A.IsDoubleWorker='N' then '否'
end As IsDoubleWorker,
OtherUserWorkNo
from FL_Culture..UsrChildWelfare As A
left join FL_Culture..UsrWelfareChildInfo As C
on C.ChildWelfareId=A.Id
where A.Id is not null ";
SQL += where;
//导出SQL
string childSQL = string.Empty, dbleSQL = string.Empty;
if (WelfareType == "double")
{
dbleSQL = SQL + " and C.BeRelation='配偶' ";
}
else if (WelfareType == "child")
{
childSQL = SQL + " and C.BeRelation='子女' ";
}
else
{
dbleSQL = SQL + " and C.BeRelation='配偶' ";
childSQL = SQL + " and C.BeRelation='子女' ";
}
string xlsNameDouble = "员工保险汇总表_配偶保险" + "_" + System.DateTime.Now.ToString("yyyMMddhhmmss");
string xlsNameChild = "员工保险汇总表_子女保险" + "_" + System.DateTime.Now.ToString("yyyMMddhhmmss");
DataTable dble = null;
DataTable child = null;
string url = System.Configuration.ConfigurationManager.AppSettings["SurveyUrl"] + "";
if (string.IsNullOrEmpty(WelfareType))
{
child = DataHelper.QueryDataTable(childSQL);
dble = DataHelper.QueryDataTable(dbleSQL);
string fileName = string.Empty;
{
string FullPath = @"/Excel/EmpChild.xls";
if (url.Contains("FD"))
{
FullPath = @"/FD/Excel/EmpChild.xls";
}
child.TableName = "data";
WorkbookDesigner designer = new WorkbookDesigner();
string xlsMdlPath = Server.MapPath(FullPath);
designer.Open(xlsMdlPath);
designer.SetDataSource(child);
designer.Process();
Aspose.Cells.Worksheet ws = designer.Workbook.Worksheets.GetSheetByCodeName("子女保险");
string newXls = xlsNameChild + ".xls";
System.IO.DirectoryInfo xlspath = new System.IO.DirectoryInfo(Server.MapPath("../Excel/tempexcel"));
ExcelHelper.deletefile(xlspath);
designer.Save(Server.MapPath("../Excel/tempexcel") + "\\" + newXls, FileFormatType.Excel2003);
if (!string.IsNullOrEmpty(fileName))
{
fileName += "|" + "/Excel/tempexcel/" + newXls; // | 文件分割
}
else
{
fileName += "/Excel/tempexcel/" + newXls;
}
}
{ //double
string FullPath = @"../Excel/EmpDouble.xls";
if (url.Contains("FD"))
{
FullPath = @"/FD/Excel/EmpDouble.xls";
}
dble.TableName = "data";
WorkbookDesigner designer = new WorkbookDesigner();
string xlsMdlPath = Server.MapPath(FullPath);
designer.Open(xlsMdlPath);
designer.SetDataSource(dble);
designer.Process();
Aspose.Cells.Worksheet ws = designer.Workbook.Worksheets.GetSheetByCodeName("配偶保险");
string newXls = xlsNameDouble + ".xls";
System.IO.DirectoryInfo xlspath = new System.IO.DirectoryInfo(Server.MapPath("../Excel/tempexcel"));
if (xlspath.GetFiles(xlsNameChild + ".xls").Length <= 0)
{
ExcelHelper.deletefile(xlspath);
}
designer.Save(Server.MapPath("../Excel/tempexcel") + "\\" + newXls, FileFormatType.Excel2003);
if (!string.IsNullOrEmpty(fileName))
{
fileName += "|" + "/Excel/tempexcel/" + newXls;
}
else
{
fileName += "/Excel/tempexcel/" + newXls;
}
}
this.PageState.Add("fileName", fileName);
}
else if (WelfareType == "double")
{
string FullPath = @"/Excel/EmpDouble.xls";
if (url.Contains("FD"))
{
FullPath = @"/FD/Excel/EmpDouble.xls";
}
dble = DataHelper.QueryDataTable(dbleSQL);
CrateExcel(dble, "配偶保险", FullPath, xlsNameDouble);
}
else if (WelfareType == "child")
{
string FullPath = @"/Excel/EmpChild.xls";
if (url.Contains("FD"))
{
FullPath = @"/FD/Excel/EmpChild.xls";
}
child = DataHelper.QueryDataTable(childSQL);
CrateExcel(child, "子女保险", FullPath, xlsNameChild);
}
}
