/// <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 ExcelWriter(string filepath, string targetpath) { _filepath = filepath; _targetpath = targetpath; designer.Open(_filepath); isOpen = true; }
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); } }
/// <summary> /// 生成Excel文档并打印 /// </summary> /// <param name="ChineseName"></param> /// <param name="TemplateFilePath"></param> /// <param name="ds"></param> /// <param name="dt"></param> /// <returns></returns> public string GetExportOPenfileByFilePath(string ChineseName, string TemplateFilePath, DataSet ds, DataTable dt, int PrintCount) { string strhtml = string.Empty; WorkbookDesigner designer = new WorkbookDesigner(); designer.Open(TemplateFilePath); if (ds != null && ds.Tables.Count > 0) { for (int i = 0; i < ds.Tables.Count; i++) { designer.SetDataSource(ds.Tables[i]); } } if (dt != null && dt.Rows.Count > 0) { DataRow dr = dt.Rows[0]; for (int k = 0; k < dt.Columns.Count; k++) { designer.SetDataSource(dt.Columns[k].ToString(), dr[k].ToString()); } } designer.Process(); //新工作表 string file = DateTime.Now.ToString("yyyyMMddHHmmssffffff") + "_" + ChineseName + ".xlsx"; string strPath = string.Empty; string strLocalPath = ""; if (HttpContext.Current != null) { strPath = HttpContext.Current.Request.PhysicalApplicationPath + "\\print\\" + DateTime.Now.ToString("yyyyMMdd"); strLocalPath += "/print/" + DateTime.Now.ToString("yyyyMMdd") + "/" + file; } else { strPath = Directory.GetParent(AppDomain.CurrentDomain.BaseDirectory).FullName + "\\print\\" + DateTime.Now.ToString("yyyyMMdd"); strLocalPath += Directory.GetParent(AppDomain.CurrentDomain.BaseDirectory).FullName + "\\print\\" + DateTime.Now.ToString("yyyyMMdd") + "\\" + file; } if (!Directory.Exists(strPath)) { Directory.CreateDirectory(strPath); } strPath += "\\" + file; try { designer.Workbook.Save(strPath, FileFormatType.Xlsx); if (_IsPrint) { PrintExcelBySheet(designer.Workbook.Worksheets[0], _PrintName, file, PrintCount); } } catch (Exception ex) { ErrorLog.WriteErrorMessage(ErrorLog.LogType.baselog, ex.ToString()); } return(strLocalPath); }
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(); } }
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 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}")); }
/// <summary> /// 导出Excel Aspose 模板模式 /// </summary> /// <param name="model">要导入的数据</param> /// <param name="templateFileName">完整文件路径</param> /// <param name="sheetName">表名</param> /// <returns></returns> private static MemoryStream OuModelFileToStream(DataTable model, string templateFileName, string sheetName) { WorkbookDesigner designer = new WorkbookDesigner(); //读取模板文件 designer.Open(templateFileName); //将数据导入进去 designer.SetDataSource(model); designer.Process(); //判断表名是否为空 if (!string.IsNullOrEmpty(sheetName)) { designer.Workbook.Worksheets[0].Name = sheetName; } //返回文件流 return(designer.Workbook.SaveToStream()); }
// 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); }
/// <summary> /// excel导出 /// </summary> /// <param name="list"></param> /// <param name="excelMb">excel模板路径</param> public static void ExportExcel(List <Email> list, string excelMb, string senderEmail) { DataTable dt = myUtils.ArrayToDataTable(list); dt.TableName = "tb"; WorkbookDesigner designer = new WorkbookDesigner(); if (!File.Exists(excelMb)) { return; } designer.Open(excelMb); //数据源 designer.SetDataSource(dt); designer.SetDataSource("SenderEmail", senderEmail); designer.SetDataSource("SenderDate", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")); designer.Process(); //文件名称 String fileName = SysConstant.APP_NAME; string file = myUtils.getCache(SysConstant.CACHE_DESKPATH.ToString()) + "/" + fileName + SysConstant.FILE_EXCEL_SUFFIX; MemoryStream stream = designer.Workbook.SaveToStream(); if (File.Exists(file)) { string newName = fileName + "_" + DateTime.Now.ToString("yyyyMMddHHmmss"); file = myUtils.getCache(SysConstant.CACHE_DESKPATH.ToString()) + "/" + newName + SysConstant.FILE_EXCEL_SUFFIX; } using (FileStream fs = new FileStream(file, FileMode.Create, FileAccess.Write, FileShare.Write)) { byte[] data = stream.ToArray(); fs.Lock(0, data.Length);//给文件流加锁 fs.Write(data, 0, data.Length); fs.Flush(); fs.Dispose(); } }
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}")); }
/// <summary> /// 模板生成excel /// </summary> /// <param name="ds">数据源(模版中用 &=tablename.columnname 来定义表名和列名)</param> /// <param name="param">参数(模版中用 &=$paramname 来定义)</param> /// <param name="templateFilePath">模版绝对路径</param> /// <returns></returns> public static byte[] OutputExcelTemplate(DataSet ds, Dictionary <string, object> param, string templateFilePath) { //创建一个workbookdesigner对象 WorkbookDesigner designer = new WorkbookDesigner(); designer.Open(templateFilePath); //设置datatable对象 designer.SetDataSource(ds); if (param != null) { foreach (var keyvalue in param) { designer.SetDataSource(keyvalue.Key, keyvalue.Value); } } //赋值 designer.Process(); return(designer.Workbook.SaveToStream().GetBuffer()); }
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 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(); }
/// <summary> /// 导出Excel Aspose 模板模式 /// </summary> /// <param name="model">要导入的数据</param> /// <param name="templateFileName">完整文件路径</param> /// <param name="sheetName">表名</param> /// <returns></returns> private static MemoryStream OuModelFileToStreamX(DataSet model, string templateFileName, List <string> sheetNames) { WorkbookDesigner designer = new WorkbookDesigner(); //读取模板文件 designer.Open(templateFileName); //将数据导入进去 designer.SetDataSource(model); designer.Process(); if (designer.Workbook.Worksheets.Count >= sheetNames.Count) { for (int i = 0; i < sheetNames.Count; i++) { string sheetName = sheetNames[i]; //判断表名是否为空 if (!string.IsNullOrEmpty(sheetName)) { designer.Workbook.Worksheets[i].Name = sheetName; } } } //返回文件流 return(designer.Workbook.SaveToStream()); }
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); }
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>"); } }
public static void BuildExcelByTemplate(string urlTemplate, DataTable dataHeader, DataTable dataContent) { #region //WorkbookDesigner designer = default(WorkbookDesigner); //try //{ // Aspose.Cells.License l = new Aspose.Cells.License(); // string strLicense = @"~/Content/Template/ReportLicenses/Aspose.Cells.lic"; // l.SetLicense(HostingEnvironment.MapPath(strLicense)); // string targetLocation = HostingEnvironment.MapPath(urlTemplate); // designer = new WorkbookDesigner(); // designer.Open(targetLocation); // designer.SetDataSource(dataHeader); // if (dataContent != null) // { // int intCols = dataContent.Columns.Count; // for (int i = 0; i <= intCols - 1; i++) // { // designer.SetDataSource(dataContent.Columns[i].ColumnName.ToString(), dataContent.Rows[0].ItemArray[i].ToString()); // } // } // designer.Process(); // designer.Workbook.CalculateFormula(); // string ext = urlTemplate.Substring(urlTemplate.LastIndexOf(".") + 1).ToLower(); // XlsSaveOptions save = new XlsSaveOptions(SaveFormat.Xlsx); // switch (ext) // { // case "xls": // save = new XlsSaveOptions(SaveFormat.Excel97To2003); // break; // case "xlsx": // save = new XlsSaveOptions(SaveFormat.Xlsx); // break; // } // designer.Workbook.Save("E:\\VNPTSOFTWARE\\a.xlsx"); // var b = designer.Workbook.SaveToStream(); // return b.ToArray(); //} //catch (Exception ex) //{ // throw ex; //} #endregion WorkbookDesigner designer = default(WorkbookDesigner); try { Aspose.Cells.License l = new Aspose.Cells.License(); string strLicense = @"/Content/Template/ReportLicenses/Aspose.Cells.lic"; l.SetLicense(HostingEnvironment.MapPath(strLicense)); designer = new WorkbookDesigner(); designer.Open(HostingEnvironment.MapPath(urlTemplate)); designer.SetDataSource(dataHeader); if (dataContent != null) { int intCols = dataContent.Columns.Count; for (int i = 0; i <= intCols - 1; i++) { designer.SetDataSource(dataContent.Columns[i].ColumnName.ToString(), dataContent.Rows[0].ItemArray[i].ToString()); } } designer.Process(); designer.Workbook.CalculateFormula(); designer.Workbook.Save("E:\\VNPTSOFTWARE\\aaxx.xlsx"); //var b = designer.Workbook.SaveToStream(); //return b.ToArray(); } catch (Exception ex) { throw ex; } }
/// <summary> /// 生成Excel文件 /// </summary> /// <param name="TemplateFilePath">物理模板路径</param> /// <param name="ds"></param> /// <param name="dt"></param> /// <param name="ChineseName"></param> /// <returns></returns> private string CreateExcelByData(string TemplateFilePath, DataSet ds, DataTable dt, string ChineseName) { string strLocalPath = string.Empty; designer = new WorkbookDesigner(); if (File.Exists(TemplateFilePath)) { designer.Open(TemplateFilePath); if (ds != null && ds.Tables.Count > 0) { for (int i = 0; i < ds.Tables.Count; i++) { designer.SetDataSource(ds.Tables[i]); } } if (dt != null && dt.Rows.Count > 0) { DataRow dr = dt.Rows[0]; for (int k = 0; k < dt.Columns.Count; k++) { designer.SetDataSource(dt.Columns[k].ToString(), dr[k].ToString()); } } designer.Process(); //新工作表 string file = DateTime.Now.ToString("yyyyMMddHHmmssffffff"); if (ChineseName.Length > 0) { file += "_" + ChineseName + ".xlsx"; } else { file += ".xlsx"; } _FileName = file; string strPath = string.Empty; if (HttpContext.Current != null) { strPath = HttpContext.Current.Request.PhysicalApplicationPath + "\\print\\" + DateTime.Now.ToString("yyyyMMdd"); strLocalPath += "/print/" + DateTime.Now.ToString("yyyyMMdd") + "/" + file; } else { strPath = Directory.GetParent(AppDomain.CurrentDomain.BaseDirectory).FullName + "\\print\\" + DateTime.Now.ToString("yyyyMMdd"); strLocalPath += Directory.GetParent(AppDomain.CurrentDomain.BaseDirectory).FullName + "\\print\\" + DateTime.Now.ToString("yyyyMMdd") + "\\" + file; } if (!Directory.Exists(strPath)) { Directory.CreateDirectory(strPath); } strPath += "\\" + file; try { designer.Workbook.Save(strPath, FileFormatType.Xlsx); _DPath = strPath; _VPath = strLocalPath; } catch (Exception ex) { ErrorLog.WriteErrorMessage(ErrorLog.LogType.baselog, ex.ToString()); } } return(strLocalPath); }
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); } }