public string GenerateExcelReport(MovementAnalysisReport Report)
{
Workbook book = new Workbook();
string str_excelfilename = "MovementReport" + ".xls";
string str_excelpath = Server.MapPath("~/Report/") + "\\" + str_excelfilename;
book.Properties.Author = "KS";
book.Properties.LastAuthor = "Admin";
book.ExcelWorkbook.WindowHeight = 4815;
book.ExcelWorkbook.WindowWidth = 11295;
book.ExcelWorkbook.WindowTopX = 120;
book.ExcelWorkbook.WindowTopY = 105;
GenerateStyles(book.Styles);
GenerateWorksheet_printregister(book.Worksheets, Report);
book.Save(str_excelpath);
return(str_excelfilename);
}
public ActionResult exportMovementAnalysis(MovementAnalysisReport Report)
{
string filename = GenerateExcelReport(Report);
var f = File(Path.Combine(Server.MapPath("~/Report/"), filename), "application/vnd.ms-excel");
f.FileDownloadName = "MovementAnalysis.xls";
return(f);
//string ext = System.IO.Path.GetExtension(filename).ToLower();
//Microsoft.Win32.RegistryKey regKey = Microsoft.Win32.Registry.ClassesRoot.OpenSubKey(ext);
//string mimeType = "";
//if (regKey != null && regKey.GetValue("Content Type") != null)
// mimeType = regKey.GetValue("Content Type").ToString();
// return RedirectToAction("MovementAnalysisReport", "Reports");
}
private void GenerateWorksheet_printregister(WorksheetCollection sheets, MovementAnalysisReport Report)
{
var dataListStore = new List <MovementAnalysisStore_Result>();
var dataListKitchen = new List <MovementAnalysisKitchen_Result>();
if (Report.Type == "Store")
{
dataListStore = db.MovementAnalysisStore(Report.DateFrom, Report.DateTo, Report.RawMaterialId).ToList();
}
else
{
dataListKitchen = db.MovementAnalysisKitchen(Report.DateFrom, Report.DateTo, Report.RawMaterialId).ToList();
}
Worksheet sheet = sheets.Add("MA");
sheet.Table.Columns.Add(150);
sheet.Table.Columns.Add(200);
sheet.Table.Columns.Add(80);
sheet.Table.Columns.Add(100);
sheet.Table.Columns.Add(100);
sheet.Table.Columns.Add(100);
sheet.Table.Columns.Add(100);
sheet.Table.Columns.Add(100);
sheet.Table.Columns.Add(100);
sheet.Table.Columns.Add(80);
sheet.Table.Columns.Add(200);
WorksheetRow Row = sheet.Table.Rows.Add();
Row.Cells.Add("Date", DataType.String, "s56");
Row.Cells.Add("Name", DataType.String, "s56");
Row.Cells.Add("Op Stock", DataType.String, "s56");
Row.Cells.Add("Op Value", DataType.String, "s56");
Row.Cells.Add("In Qty", DataType.String, "s56");
Row.Cells.Add("In Value", DataType.String, "s56");
Row.Cells.Add("Out Qty", DataType.String, "s56");
Row.Cells.Add("Out value", DataType.String, "s56");
Row.Cells.Add("Cls Stock", DataType.String, "s56");
Row.Cells.Add("Cls Value", DataType.String, "s56");
Row.Cells.Add("Remarks", DataType.String, "s56");
DateTime frDate = Convert.ToDateTime(Report.DateFrom);
decimal opStck = 0, clsStck = 0;
decimal opStckVal = 0, clsStckVal = 0;
int chkRawMatId = 0;
if (dataListStore.Count > 0)
{
foreach (var li in dataListStore)
{
if (chkRawMatId == 0 || chkRawMatId != li.MaterialId)
{
if (db.vwStockTransactions.Where(p => p.Date < frDate && p.MaterialId == li.MaterialId).Count() > 0)
{
opStck = Convert.ToDecimal(db.vwStockTransactions.Where(p => p.Date < frDate && p.MaterialId == li.MaterialId).Sum(p => p.inQty)) - Convert.ToDecimal(db.vwStockTransactions.Where(p => p.Date < frDate && p.MaterialId == li.MaterialId).Sum(p => p.outQty));
opStckVal = Convert.ToDecimal(db.vwStockTransactions.Where(p => p.Date < frDate && p.MaterialId == li.MaterialId).Sum(p => p.inVal)) - Convert.ToDecimal(db.vwStockTransactions.Where(p => p.Date < frDate && p.MaterialId == li.MaterialId).Sum(p => p.outVal));
}
else
{
opStck = 0;
opStckVal = 0;
}
chkRawMatId = li.MaterialId;
}
Row = sheet.Table.Rows.Add();
Row.Cells.Add(li.Date, DataType.String, "s46");
Row.Cells.Add(li.Name, DataType.String, "s46");
Row.Cells.Add(opStck.ToString("0.00"), DataType.String, "s45");
Row.Cells.Add(opStckVal.ToString("0.00"), DataType.String, "s45");
Row.Cells.Add(li.inQty.ToString(), DataType.String, "s45");
Row.Cells.Add(li.inVal.ToString(), DataType.String, "s45");
Row.Cells.Add(li.outQty.ToString(), DataType.String, "s45");
Row.Cells.Add(li.outVal.ToString(), DataType.String, "s45");
clsStck = Convert.ToDecimal(opStck + li.inQty - li.outQty);
opStck = clsStck;
clsStckVal = Convert.ToDecimal(opStckVal + li.inVal - li.outVal);
opStckVal = clsStckVal;
Row.Cells.Add(clsStck.ToString("0.00"), DataType.String, "s45");
Row.Cells.Add(clsStckVal.ToString("0.00"), DataType.String, "s45");
Row.Cells.Add(li.Remarks.ToString(), DataType.String, "s46");
}
}
if (dataListKitchen.Count > 0)
{
foreach (var li in dataListKitchen)
{
if (chkRawMatId == 0 || chkRawMatId != li.MaterialId)
{
if (db.vwStockTransactions.Where(p => p.Date < frDate && p.MaterialId == li.MaterialId).Count() > 0)
{
opStck = Convert.ToDecimal(db.vwStockTransactionsKitchens.Where(p => p.Date < frDate && p.MaterialId == li.MaterialId).Sum(p => p.inQty)) - Convert.ToDecimal(db.vwStockTransactionsKitchens.Where(p => p.Date < frDate && p.MaterialId == li.MaterialId).Sum(p => p.outQty));
opStckVal = Convert.ToDecimal(db.vwStockTransactionsKitchens.Where(p => p.Date < frDate && p.MaterialId == li.MaterialId).Sum(p => p.inVal)) - Convert.ToDecimal(db.vwStockTransactionsKitchens.Where(p => p.Date < frDate && p.MaterialId == li.MaterialId).Sum(p => p.outVal));
}
else
{
opStck = 0;
opStckVal = 0;
}
chkRawMatId = li.MaterialId;
}
Row = sheet.Table.Rows.Add();
Row.Cells.Add(li.Date, DataType.String, "s46");
Row.Cells.Add(li.Name, DataType.String, "s46");
Row.Cells.Add(opStck.ToString("0.00"), DataType.String, "s45");
Row.Cells.Add(opStckVal.ToString("0.00"), DataType.String, "s45");
Row.Cells.Add(li.inQty.ToString(), DataType.String, "s45");
Row.Cells.Add(li.inVal.ToString(), DataType.String, "s45");
Row.Cells.Add(li.outQty.ToString(), DataType.String, "s45");
Row.Cells.Add(li.outVal.ToString(), DataType.String, "s45");
clsStck = Convert.ToDecimal(opStck + li.inQty - li.outQty);
opStck = clsStck;
clsStckVal = Convert.ToDecimal(opStckVal + li.inVal - li.outVal);
opStckVal = clsStckVal;
Row.Cells.Add(clsStck.ToString("0.00"), DataType.String, "s45");
Row.Cells.Add(clsStckVal.ToString("0.00"), DataType.String, "s45");
Row.Cells.Add(li.Remarks.ToString(), DataType.String, "s46");
}
}
sheet.Options.Selected = true;
sheet.Options.ProtectObjects = false;
sheet.Options.ProtectScenarios = false;
sheet.Options.PageSetup.Layout.Orientation = CarlosAg.ExcelXmlWriter.Orientation.Landscape;
sheet.Options.PageSetup.PageMargins.Bottom = 1F;
sheet.Options.PageSetup.PageMargins.Left = 1F;
sheet.Options.PageSetup.PageMargins.Right = 1F;
sheet.Options.PageSetup.PageMargins.Top = 1F;
sheet.Options.Print.PaperSizeIndex = 9;
sheet.Options.Print.HorizontalResolution = 120;
sheet.Options.Print.VerticalResolution = 72;
sheet.Options.Print.ValidPrinterInfo = true;
}