public void Excelvalidation(string path, int sno, Excel.Sheets val_sheetlist)
{
#region Initial
Excel.Worksheet valsheet = (Excel.Worksheet)val_sheetlist[1];
FileInfo finfo = new FileInfo(path);
Microsoft.Office.Interop.Excel.Application xlApp = null;
Excel.Workbook xlWorkbook = null;
Excel.Sheets xlSheets = null;
Excel.Worksheet xlNewSheet = null;
Excel.Worksheet xlsummarysheet = null;
Excel.Worksheet xlscvsheet = null;
string ErrMsg = "", hidelist = "";
int i = 1;
try
{
valsheet.Cells[sno, 1] = finfo.Name;
xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
return;
xlWorkbook = xlApp.Workbooks.Open(path, 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false);
int boindex = 0, sumindex = 0, cvrindex = 0;
for (int l = 1; l <= xlWorkbook.Sheets.Count; l++)
{
if (xlWorkbook.Sheets[l].Name.ToString().Trim().Replace(" ", "").ToLower() == "boxoffice")
{
boindex = l;
}
if (xlWorkbook.Sheets[l].Name.ToString().Trim().Replace(" ", "").ToLower() == "coversheet")
{
cvrindex = l;
}
if (xlWorkbook.Sheets[l].Name.ToString().Trim().Replace(" ", "").ToLower() == "summary")
{
sumindex = l;
}
}
xlSheets = xlWorkbook.Sheets as Excel.Sheets;
if (boindex != 0)
{
xlNewSheet = (Excel.Worksheet)xlSheets[boindex];
xlNewSheet.Unprotect("7135");
}
if (boindex != 0)
{
xlsummarysheet = (Excel.Worksheet)xlSheets[sumindex];
xlsummarysheet.Unprotect("7135");
}
#endregion
if (1 == valstatus)
{
hidelist = "2,3";
lnkvalexcel.Visible = true;
#region Validate process
if (xlNewSheet.ProtectContents)
{
ErrMsg = "File is protcted";
}
else if (boindex == 0 && sumindex == 0 && cvrindex == 0)
{
ErrMsg = "Sheet name not found!";
}
//if (!xlNewSheet.ProtectContents && (boindex != 0 && sumindex != 0 && cvrindex != 0))
if ((boindex != 0 && sumindex != 0 && cvrindex != 0))
{
string sch_day = "", sch_date = "", sch_time = "", tot_att = "", gross_sales = "", tot_exp = "", gross_rec = "",
Discount = "", SummaryMsg = "";
string royalty = "", gurantee = "", var_exp_act = "", share1 = "", share2 = "", share3 = "", iferr = "";
if (WriteError == 0)
{
#region Write Error
royalty = xlsummarysheet.Cells[31, 2].Text.Trim();
gurantee = xlsummarysheet.Cells[34, 2].Text.Trim();
var_exp_act = xlsummarysheet.Cells[54, 2].Text.Trim();
share1 = xlsummarysheet.Cells[90, 2].Text.Trim();
share2 = xlsummarysheet.Cells[93, 2].Text.Trim();
share3 = xlsummarysheet.Cells[96, 2].Text.Trim();
if (royalty.AutoformatDecimal() == 0)
SummaryMsg += "Royalty Zero,";
if (gurantee.AutoformatDecimal() == 0)
SummaryMsg += "Guarantee Zero,";
if (var_exp_act.AutoformatDecimal() == 0)
SummaryMsg += "Expenses Zero,";
//if (share1.AutoformatDecimal() + share2.AutoformatDecimal() + share3.AutoformatDecimal() != 100)
// SummaryMsg += "Total share Not equal to 100,";
iferr = "";
for (i = 3; i < 13; i++)
{
ErrMsg = ""; sch_day = ""; sch_date = ""; sch_time = ""; tot_att = ""; gross_sales = ""; tot_exp = "0"; gross_rec = "";
sch_date = (xlNewSheet.Cells[1, i].Value != null) ? Convert.ToString(xlNewSheet.Cells[1, i].Value).Trim() : sch_date;
sch_day = Convert.ToString(xlNewSheet.Cells[2, i].Text).ToString().Trim();
sch_time = Convert.ToString(xlNewSheet.Cells[3, i].Text).ToString().Trim();
tot_att = Convert.ToString(xlNewSheet.Cells[9, i].Value).ToString().Trim();
gross_sales = Convert.ToString(xlNewSheet.Cells[13, i].Value).ToString().Trim();
//tot_exp = Convert.ToString(xlNewSheet.Cells[26, i].Value).ToString().Trim();
gross_rec = (xlNewSheet.Cells[31, i].Value != null) ? Convert.ToString(xlNewSheet.Cells[31, i].Value.ToString()).Trim() : gross_rec;
sch_date = (sch_date.ToLower().Contains("perf") == true) ? "0" : "1";
sch_day = (sch_day.Contains("Day") == true) ? "0" : "1";
sch_time = (sch_time.Contains("time") == true) ? "0" : "1";
tot_att = (tot_att.AutoformatInt() == 0) ? "0" : "1";
gross_sales = (gross_sales.AutoformatDecimal() == 0) ? "0" : "1";
//tot_exp = (tot_exp.AutoformatDecimal() == 0) ? "0" : "1";
gross_rec = (gross_rec.AutoformatDecimal() == 0) ? "0" : "1";
string ticket = "", price = "", ps_tkt_price = "";
for (int j = 100; j <= 110; j++)
{
ticket = xlNewSheet.Cells[j, 1].Text.Trim();
price = xlNewSheet.Cells[j, i].Text.Trim();
price = (price.AutoformatDecimal() == null) ? "0" : price.AutoformatDecimal().ToString();
if ((ticket.Contains("#") == true && price.AutoformatDecimal() > 0) || (ticket != "" && ticket.Contains("#") == false && price.AutoformatDecimal() == 0))
{
ps_tkt_price = "0";
}
}
int totcnt = Convert.ToInt32(sch_date.AutoformatInt() + sch_day.AutoformatInt() + sch_time.AutoformatInt() + tot_att.AutoformatInt() +
gross_sales.AutoformatInt() + tot_exp.AutoformatInt() + gross_rec.AutoformatInt());
if (totcnt < 6 && totcnt != 0)
{
if (sch_date.AutoformatInt() == 0)
{
//ErrMsg += "Schedule date error,";
}
if (sch_day.AutoformatInt() == 0)
{
ErrMsg += "Schedule day error,";
}
if (tot_att.AutoformatInt() == 0)
{
ErrMsg += "Total attendance error,";
}
if (gross_sales.AutoformatInt() == 0)
{
ErrMsg += "Gross sales error,";
}
//if (tot_exp.AutoformatInt() == 0)
//{
// ErrMsg += "Total expenses error,";
//}
if (gross_rec.AutoformatInt() == 0)
{
ErrMsg += "Gross receipts error,";
}
}
if (Discount.AutoformatInt() == 0 && i == 3)
{
ErrMsg += "Discount extra columns,";
}
if (ps_tkt_price.AutoformatInt() == 0 && totcnt == 7)
{
ErrMsg += "Ticket price mismatching,";
}
valsheet.Cells[sno, i] = ErrMsg;
if (string.IsNullOrEmpty(ErrMsg) == false && string.IsNullOrEmpty(iferr) == true)
iferr = ErrMsg;
}
valsheet.Cells[sno, 13] = SummaryMsg;
#endregion
}
xlscvsheet = (Excel.Worksheet)xlSheets[cvrindex];
string recid = "";
#region GetMySQLRecID
string shname = "", city = "", venue = "", presenter = "";
DateTime opdate, endate;
shname = xlscvsheet.Cells[1, 1].Text;
city = xlscvsheet.Cells[3, 2].Text;
venue = xlscvsheet.Cells[4, 2].Text;
presenter = xlscvsheet.Cells[5, 2].Text;
if (!string.IsNullOrEmpty(xlscvsheet.Cells[8, 1].Text))
{
opdate = Convert.ToDateTime(xlscvsheet.Cells[8, 1].Text);
endate = Convert.ToDateTime(xlscvsheet.Cells[8, 2].Text);
valsheet.Cells[sno, 14] = shname;
valsheet.Cells[sno, 15] = city;
valsheet.Cells[sno, 16] = opdate;
valsheet.Cells[sno, 17] = endate;
if (AllNew != 0)
{
MasterDataLayer.MasterData objmst = new MasterDataLayer.MasterData();
DataTable dt = new DataTable();
dt = objmst.GetMysql_Recordid(shname, city, opdate, endate);
if (dt.Rows.Count > 0)
{
recid = dt.Rows[0]["RecordID"].ToString();
}
}
}
else
{
recid = "Opening date not found!";
}
valsheet.Cells[sno, 2] = recid;
#endregion
if (!string.IsNullOrEmpty(ErrMsg) || !string.IsNullOrEmpty(SummaryMsg))
{
hdnvalidation_status.Value = "1";
}
else if (recid.ToLower().Contains("new") == false && string.IsNullOrEmpty(iferr) == true)
{
//xlWorkbook.SaveAs(Saveaspath + finfo.Name);
valsheet.Cells[sno, 25] = "Pass";
}
else { valsheet.Cells[sno, 25] = "Fail"; }
}
else
{
valsheet.Cells[sno, i + 1] = ErrMsg;
}
#endregion
}
else
{
lnkexcelclms.Visible = true;
hidelist = "1";
write_excel_columns(finfo, xlNewSheet, xlsummarysheet, sno, val_sheetlist);
}
// objcf.HideDBData_XLSheets(val_sheetlist, "1");
// xlWorkbook.Save();
xlWorkbook.Close(false, Type.Missing, Type.Missing);
xlApp.Quit();
GC.Collect();
}
catch (Exception ex)
{
valsheet.Cells[sno, i + 1] = ex.Message + " File Name: " + finfo.Name;
xlWorkbook.Save();
xlWorkbook.Close(Type.Missing, Type.Missing, Type.Missing);
xlApp.Quit();
GC.Collect();
// throw new Exception(ex.Message + " File Name: " + finfo.Name);
}
finally
{
// objcf.HideDBData_XLSheets(val_sheetlist, hidelist);
xlApp = null;
}
}