protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
try
{
MovieCatalogBL context = new MovieCatalogBL();
var query = context.GetMoviesExpiry();
ListView2.DataSource = query;
ListView2.DataBind();
}
catch (Exception)
{
lblMessage2.Text = "An error occurred while retrieving movies. Please try again.";
lblMessage2.Attributes.Add("style", "color:red; font: bold 14px/16px Sans-Serif,Arial");
}
}
}
protected void btnExportToExcel_Click(object sender, EventArgs e)
{
try
{
MovieCatalogBL context = new MovieCatalogBL();
List<Movie> movieList = context.GetMoviesExpiry().ToList();
if (movieList.Count > 0)
{
string path = Server.MapPath("~/exportedfiles/");
if (!Directory.Exists(path)) // CHECK IF THE FOLDER EXISTS. IF NOT, CREATE A NEW FOLDER.
{
Directory.CreateDirectory(path);
}
// "File.Delete" method does not throw an exception when a file doesn't exist.
File.Delete(path + "MoviesToExpire.xls"); // DELETE THE FILE BEFORE CREATING A NEW ONE.
// ADD A WORKBOOK USING THE EXCEL APPLICATION.
Excel.Application xlAppToExport = new Excel.Application();
xlAppToExport.Workbooks.Add("");
// ADD A WORKSHEET.
Excel.Worksheet xlWorkSheetToExport = default(Excel.Worksheet);
xlWorkSheetToExport = (Excel.Worksheet)xlAppToExport.Sheets["Sheet1"];
// ROW ID FROM WHERE THE DATA STARTS SHOWING.
int iRowCnt = 4;
// SHOW THE HEADER.
xlWorkSheetToExport.Cells[1, 1] = "Movies To Expire in next 30 days.";
Excel.Range range = xlWorkSheetToExport.Cells[1, 1] as Excel.Range;
range.EntireRow.Font.Name = "Arial";
range.EntireRow.Font.Bold = true;
range.EntireRow.Font.Size = 20;
// Depends how long the title is
xlWorkSheetToExport.Range["A1:F1"].MergeCells = true; // MERGE CELLS OF THE HEADER.
// SHOW COLUMNS ON THE TOP.
xlWorkSheetToExport.Cells[iRowCnt - 1, 1] = "Title";
xlWorkSheetToExport.Cells[iRowCnt - 1, 2] = "Country";
xlWorkSheetToExport.Cells[iRowCnt - 1, 3] = "Genre";
xlWorkSheetToExport.Cells[iRowCnt - 1, 4] = "Year Of Production";
xlWorkSheetToExport.Cells[iRowCnt - 1, 5] = "Expiry Date";
foreach (var item in movieList)
{
xlWorkSheetToExport.Cells[iRowCnt, 1] = item.OriginalName;
xlWorkSheetToExport.Cells[iRowCnt, 2] = item.Country;
xlWorkSheetToExport.Cells[iRowCnt, 3] = item.Genre;
xlWorkSheetToExport.Cells[iRowCnt, 4] = item.Year;
xlWorkSheetToExport.Cells[iRowCnt, 5] = item.ExpireDate;
iRowCnt = iRowCnt + 1;
}
// FINALLY, FORMAT THE EXCEL SHEET USING EXCEL'S AUTOFORMAT FUNCTION.
Excel.Range range1 = xlAppToExport.ActiveCell.Worksheet.Cells[4, 1] as Excel.Range;
range1.AutoFormat(ExcelAutoFormat.xlRangeAutoFormatList3);
// SAVE THE FILE IN A FOLDER. (XLS or XLSX format)
xlWorkSheetToExport.SaveAs(path + "MoviesToExpire.xls");
// CLEAR.
xlAppToExport.Workbooks.Close();
xlAppToExport.Quit();
xlAppToExport = null;
xlWorkSheetToExport = null;
lblMessage2.Text = "Data Exported.";
lblMessage2.Attributes.Add("style", "color:green; font: bold 14px/16px Sans-Serif,Arial");
}
}
catch (IOException)
{
lblMessage2.Text = "There was an error while exporting data. Check if file is in use by another application (MS Excel).";
lblMessage2.Attributes.Add("style", "color:red; font: bold 14px/16px Sans-Serif,Arial");
}
catch (Exception ex)
{
lblMessage2.Text = "There was an error while exporting data. Try again." + "<br/>" + ex.ToString(); ;
lblMessage2.Attributes.Add("style", "color:red; font: bold 14px/16px Sans-Serif,Arial");
}
}