private void ExportReport() { DateTime start = Convert.ToDateTime(txtFrom.Text); var startDate = new DateTime(start.Year, start.Month, 1); DateTime end = Convert.ToDateTime(txtTo.Text); var endDate = end.AddMonths(1).AddDays(-1); //string customerIds = Request.Form[hfCustomerId.UniqueID]; string customerIds = txtSearch.Text; customerIds = customerIds.Trim(); customerIds = customerIds.Remove(customerIds.Length - 1); //dt = objAdm.ContentMgmntReport("400541,400479", startDate.ToShortDateString(), endDate.ToShortDateString()); dt = objAdm.ContentMgmntReport(customerIds, startDate.ToShortDateString(), endDate.ToShortDateString()); hfCustomerId.Value = string.Empty; txtSearch.Text = string.Empty; if (dt.Rows.Count > 0) { var pkg = new ExcelPackage(); pkg.Compression = CompressionLevel.Default; var wsPvt = pkg.Workbook.Worksheets.Add("Pivot Table"); var wsData = pkg.Workbook.Worksheets.Add("Source Data"); var PvtData = new PivotData(new[] { "Service Code", "User Name", "Month", "Reporting Manager" }, new SumAggregatorFactory("Total Hours")); PvtData.ProcessData(new DataTableReader(dt)); var pvtTbl = new PivotTable( new[] { "Service Code", "User Name" }, //rows new[] { "Month" }, // columns PvtData, new CustomSortKeyComparer(new[] { NaturalSortKeyComparer.Instance }), new CustomSortKeyComparer(new[] { NaturalSortKeyComparer.Instance }) ); //var pvtDataTableWr = new PivotTableDataTableWriter("PivotTable"); var excelPvtTblWr = new ExcelPivotTableWriter(wsPvt, wsData); excelPvtTblWr.Write(pvtTbl); string path = @"~/Files/result.xlsx"; if (File.Exists(path)) { File.Delete(path); } using (var excelFs = new FileStream(Server.MapPath(path), FileMode.Create, FileAccess.Write)) { pkg.SaveAs(excelFs); } WebClient req = new WebClient(); HttpResponse response = HttpContext.Current.Response; string filePath = path; response.Clear(); response.ClearContent(); response.ClearHeaders(); response.Buffer = true; response.AddHeader("Content-Disposition", "attachment;filename=result.xlsx"); byte[] data = req.DownloadData(Server.MapPath(filePath)); response.BinaryWrite(data); response.End(); } else { ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "No Data", "alert('No data found')", true); } }
public void Export3ItemGroupsToExcel(Stream stream, int year) { using (var package = new ExcelPackage(stream)) { var query = from item in _dataContext.ReportDatas where item.ChannelName.ToLower() == "direct" && (item.ItemGroupName.ToLower() == "prepenem" || item.ItemGroupName.ToLower() == "maxpenepm" || item.ItemGroupName.ToLower() == "combilipid") && (item.Year == year || item.Year == year - 1) select item; DataTable table = LinqExtension.LinqResultToDataTable(query); var pivotData = new PivotData(new[] { "ChannelName", "ItemGroupName", "ClientName", "Year", "Month" }, new SumAggregatorFactory("CifAmount") ); pivotData.ProcessData(new DataTableReader(table)); package.Compression = CompressionLevel.Default; var pivot = package.Workbook.Worksheets.Add("PivotTable"); var data = package.Workbook.Worksheets.Add("Data"); var pivotTable = new PivotTable( new[] { "ItemGroupName", "ClientName" }, //rows new[] { "Year", "Month" }, // columns pivotData ); var excelPivotTableWriter = new ExcelPivotTableWriter(pivot, data); excelPivotTableWriter.Write(pivotTable); //pkg.Workbook.Worksheets.Delete(wsData); package.Save(); } }