using (ExcelPackage excelPackage = new ExcelPackage())
{
ExcelWorksheet ExcelWorkSheet = null;
ExcelWorkSheet = excelPackage.Workbook.Worksheets.Add(dataTable.TableName);
for (int col = 1; col <= dataTable.Columns.Count; col++)
{
ExcelWorkSheet.Cells[1, col].Value = dataTable.Columns[col - 1].ColumnName;
}
for (int row = 0; row < dataTable.Rows.Count; row++)
{
for (int col = 0; col < dataTable.Columns.Count; col++)
{
string rowData = dataTable.Rows[row][col].ToString();
List<string> strDocData = new List<string>();
string[] strtempData;
if (!string.IsNullOrEmpty(rowData) && rowData.StartsWith("Documents/"))
{
string[] documentData = rowData.Split(',');
documentData.ToList().ForEach(item =>
{
strtempData = item.Split(new string[] { "@@" }, StringSplitOptions.None);
strDocData.Add(strtempData[1]);
});
rowData = string.Join(", ", strDocData);
}
ExcelWorkSheet.Cells[row + 2, col + 1].Value = rowData;
ExcelWorkSheet.Cells[row + 2, col + 1].AutoFitColumns();
}
}
byte[] byteArray = excelPackage.GetAsByteArray();
Response.ClearHeaders();
Response.Clear();
Response.Buffer = true;
string webURL = CacheHelper.GetItem("WebURL").ToString();
string Path = CacheHelper.GetItem("DownloadFilePath");
string dataExportPath = System.Web.Hosting.HostingEnvironment.ApplicationPhysicalPath + "" + "SearchFilterDataExport" + "\\" + UserInfo.GetUserId();
if (!Directory.Exists(dataExportPath))
{
Directory.CreateDirectory(dataExportPath);
}
System.IO.DirectoryInfo di = new DirectoryInfo(dataExportPath);
foreach (FileInfo file in di.GetFiles())
{
file.Delete();
}
excelName = WebHelper.ValidateFileName(excelName);
System.IO.File.WriteAllBytes(dataExportPath + "\\" + excelName + ".xlsx", byteArray);
return webURL + "/SearchFilterDataExport/" + UserInfo.GetUserId() + "/" + excelName + ".xlsx";
}