public string DownloadBillingInfo(BillingExportFilter filter) { IDataReader dataReader = null; try { ExcelPackage excelPackage = new ExcelPackage(); ExcelWorksheet workSheet = excelPackage.Workbook.Worksheets.Add("Report Data"); int rowIndex = 1; int colIndex = 1; workSheet.Cells[rowIndex, colIndex].Value = "Billing ID"; colIndex++; workSheet.Cells[rowIndex, colIndex].Value = "Item Name"; colIndex++; workSheet.Cells[rowIndex, colIndex].Value = "Item Price"; colIndex++; workSheet.Cells[rowIndex, colIndex].Value = "Quantity"; colIndex++; workSheet.Cells[rowIndex, colIndex].Value = "Amount"; colIndex++; workSheet.Cells[rowIndex, colIndex].Value = "User"; colIndex++; workSheet.Cells[rowIndex, colIndex].Value = "Created Time"; colIndex++; workSheet.Cells[rowIndex, colIndex].Value = "Comments"; rowIndex = 2; bool recordExists = false; DateTime createdDateTime; Database db = DatabaseFactory.CreateDatabase("DbConnection"); dataReader = BillingDAL.GetBillingInfoForExport(db, filter.FromDate, filter.ToDate); while (dataReader.Read()) { colIndex = 1; recordExists = true; workSheet.Cells[rowIndex, colIndex].Value = Common.GetInt64(dataReader, "FBILLINGID"); colIndex++; workSheet.Cells[rowIndex, colIndex].Value = Common.GetString(dataReader, "FITEMNAME"); colIndex++; decimal itemPrice = Common.GetDecimal(dataReader, "FITEMPRICE"); int quantity = Common.GetInt32(dataReader, "FQTY"); workSheet.Cells[rowIndex, colIndex].Value = itemPrice; colIndex++; workSheet.Cells[rowIndex, colIndex].Value = quantity; colIndex++; workSheet.Cells[rowIndex, colIndex].Value = itemPrice * quantity; colIndex++; workSheet.Cells[rowIndex, colIndex].Value = Common.GetString(dataReader, "FUSERNAME"); colIndex++; DateTime.TryParseExact(Common.GetInt32(dataReader, "FCREATEDDATE").ToString() + Common.GetInt32(dataReader, "FCREATEDTIME").ToString().PadLeft(6, '0'), "yyyyMMddHHmmss", CultureInfo.InvariantCulture, DateTimeStyles.None, out createdDateTime); workSheet.Cells[rowIndex, colIndex].Value = createdDateTime.ToString("yyyy-MMM-dd hh:mm:ss tt"); colIndex++; workSheet.Cells[rowIndex, colIndex].Value = Common.GetString(dataReader, "FCOMMENT"); rowIndex++; } dataReader.Close(); if (recordExists) { workSheet.Cells.AutoFitColumns(); string fileName = "BillingData_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx"; string excelFolderPath = ConfigurationManager.AppSettings["LogFileLocation"].ToString(); FileInfo excelFile = new FileInfo(excelFolderPath + "/" + fileName); excelPackage.SaveAs(excelFile); return(fileName); } return(string.Empty); } catch (Exception ex) { Common.LogException(ex); throw new WebFaultException <string>(ex.Message, HttpStatusCode.InternalServerError); } finally { if (dataReader != null && !dataReader.IsClosed) { dataReader.Close(); } } }