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();
}
}
