public ActionResult PrintReportEmployeeLoansBetweenTwoDates(ReportEmployeeLoansBetweenTwoDatesFilter model) { try { ViewBag.Title = "Report"; List <ReportEmployeeLoansBetweenTwoDatesResult> result = DbServices.ReportEmployeeLoansBetweenTwoDates(model, db); return(PartialView(result)); } catch (CfException exc) { return(View()); } }
public ActionResult ReportEmployeeLoansBetweenTwoDates(string operationType, ReportEmployeeLoansBetweenTwoDatesFilter model) { try { if (operationType != null && operationType == "print") { return(RedirectToAction("PrintReportEmployeeLoansBetweenTwoDates", model)); } else if (operationType != null && operationType == "csv") { return(RedirectToAction("GetCSVEmployeeLoansBetweenTwoDates", model)); } List <ReportEmployeeLoansBetweenTwoDatesResult> result = DbServices.ReportEmployeeLoansBetweenTwoDates(model, db); return(PartialView("ReportEmployeeLoansBetweenTwoDatesResult", result)); } catch (CfException exc) { ViewBag.ReportEmployeeLoansBetweenTwoDates = reportEmployeeLoansBetweenTwoDates; return(View()); } }
public FileContentResult GetCSVEmployeeLoansBetweenTwoDates() { ReportEmployeeLoansBetweenTwoDatesFilter filters = new ReportEmployeeLoansBetweenTwoDatesFilter(); try { List <ReportEmployeeLoansBetweenTwoDatesResult> result = DbServices.ReportEmployeeLoansBetweenTwoDates(filters); //System.IO.StreamWriter excelDoc; using (var stream = new MemoryStream()) { using (var excelDoc = new StreamWriter(stream, Encoding.UTF8)) { #region startExcelXml const string startExcelXML = "<xml version>\r\n<Workbook " + "xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"\r\n" + " xmlns:o=\"urn:schemas-microsoft-com:office:office\"\r\n " + "xmlns:x=\"urn:schemas- microsoft-com:office:" + "excel\"\r\n xmlns:ss=\"urn:schemas-microsoft-com:" + "office:spreadsheet\">\r\n <Styles>\r\n " + "<Style ss:ID=\"Default\" ss:Name=\"Normal\">\r\n " + "<Alignment ss:Vertical=\"Bottom\"/>\r\n <Borders/>" + "\r\n <Font/>\r\n <Interior/>\r\n <NumberFormat/>" + "\r\n <Protection/>\r\n </Style>\r\n " + "<Style ss:ID=\"BoldColumn\">\r\n <Font " + "x:Family=\"Swiss\" ss:Bold=\"1\"/>\r\n </Style>\r\n " + "<Style ss:ID=\"StringLiteral\">\r\n <NumberFormat" + " ss:Format=\"@\"/>\r\n </Style>\r\n <Style " + "ss:ID=\"Decimal\">\r\n <NumberFormat " + "ss:Format=\"0.0000\"/>\r\n </Style>\r\n " + "<Style ss:ID=\"Integer\">\r\n <NumberFormat " + "ss:Format=\"0\"/>\r\n </Style>\r\n <Style " + "ss:ID=\"DateLiteral\">\r\n <NumberFormat " + "ss:Format=\"mm/dd/yyyy;@\"/>\r\n </Style>\r\n " + "</Styles>\r\n "; #endregion const string endExcelXML = "</Workbook>"; int rowCount = 0; int sheetCount = 1; excelDoc.Write(startExcelXML); excelDoc.Write("<Worksheet ss:Name=\"Sheet" + sheetCount + "\">"); excelDoc.Write("<Table>"); #region First Row excelDoc.Write("<Row>"); DateTime d = DateTime.Now; //elapsedTime = (DateTime.Now - d).TotalSeconds; System.Data.DataTable table = new System.Data.DataTable("Table"); table.Columns.Add("1"); table.Columns.Add("2"); table.Columns.Add("3"); table.Columns.Add("4"); table.Columns.Add("5"); table.Columns.Add("6"); table.Columns.Add("7"); foreach (ReportEmployeeLoansBetweenTwoDatesResult item in result) { table.Rows.Add( item.ProductEmployeeId.ToString(), item.ProductEmployeeFullName, item.ProductProductTypeName, item.ProductAmount, item.LoanDecisionDate.ToString(), item.Installment, item.LoanDecisionNumber ); } DataSet set = new DataSet(); set.Tables.Add(table); for (int x = 0; x < set.Tables[0].Columns.Count; x++) { excelDoc.Write("<Cell ss:StyleID=\"BoldColumn\"><Data ss:Type=\"String\">"); excelDoc.Write(set.Tables[0].Columns[x].ColumnName); excelDoc.Write("</Data></Cell>"); } excelDoc.Write("</Row>"); #endregion for (int x = 0; x < set.Tables[0].DefaultView.Count; x++) { rowCount++; #region 64000 if (rowCount == 64000) { rowCount = 0; sheetCount++; excelDoc.Write("</Table>"); excelDoc.Write(" </Worksheet>"); excelDoc.Write("<Worksheet ss:Name=\"Sheet" + sheetCount + "\">"); excelDoc.Write("<Table>"); } #endregion excelDoc.Write("<Row>"); //ID=" + rowCount + " for (int y = 0; y < set.Tables[0].Columns.Count; y++) { System.Type rowType = null; rowType = set.Tables[0].DefaultView[x][y].GetType(); switch (rowType.ToString()) { case "System.String": string XMLstring = ""; XMLstring = set.Tables[0].DefaultView[x][y].ToString(); XMLstring = XMLstring.Trim(); XMLstring = XMLstring.Replace("&", "&"); XMLstring = XMLstring.Replace(">", ">"); XMLstring = XMLstring.Replace("<", "<"); excelDoc.Write("<Cell ss:StyleID=\"StringLiteral\">" + "<Data ss:Type=\"String\">"); excelDoc.Write(XMLstring); excelDoc.Write("</Data></Cell>"); break; case "System.DateTime": DateTime XMLDate = DateTime.Now; XMLDate = (DateTime)set.Tables[0].DefaultView[x][y]; string XMLDatetoString = ""; XMLDatetoString = XMLDate.Year.ToString() + "-" + (XMLDate.Month < 10 ? "0" + XMLDate.Month.ToString() : XMLDate.Month.ToString()) + "-" + (XMLDate.Day < 10 ? "0" + XMLDate.Day.ToString() : XMLDate.Day.ToString()) + "T" + (XMLDate.Hour < 10 ? "0" + XMLDate.Hour.ToString() : XMLDate.Hour.ToString()) + ":" + (XMLDate.Minute < 10 ? "0" + XMLDate.Minute.ToString() : XMLDate.Minute.ToString()) + ":" + (XMLDate.Second < 10 ? "0" + XMLDate.Second.ToString() : XMLDate.Second.ToString()) + ".000"; excelDoc.Write("<Cell ss:StyleID=\"DateLiteral\">" + "<Data ss:Type=\"DateTime\">"); excelDoc.Write(XMLDatetoString); excelDoc.Write("</Data></Cell>"); break; case "System.Boolean": excelDoc.Write("<Cell ss:StyleID=\"StringLiteral\">" + "<Data ss:Type=\"String\">"); //excelDoc.Write(x[y].ToString()); excelDoc.Write(set.Tables[0].DefaultView[x][y].ToString()); excelDoc.Write("</Data></Cell>"); break; case "System.Int16": case "System.Int32": excelDoc.Write("<Cell ss:StyleID=\"Integer\">" + "<Data ss:Type=\"Number\">"); // excelDoc.Write(x[y].ToString()); excelDoc.Write(set.Tables[0].DefaultView[x][y].ToString()); excelDoc.Write("</Data></Cell>"); break; case "System.Int64": case "System.Byte": excelDoc.Write("<Cell ss:StyleID=\"Integer\">" + "<Data ss:Type=\"Number\">"); // excelDoc.Write(x[y].ToString()); excelDoc.Write(set.Tables[0].DefaultView[x][y].ToString()); excelDoc.Write("</Data></Cell>"); break; case "System.Decimal": excelDoc.Write("<Cell ss:StyleID=\"Decimal\">" + "<Data ss:Type=\"Number\">"); // excelDoc.Write(x[y].ToString()); excelDoc.Write(set.Tables[0].DefaultView[x][y].ToString()); excelDoc.Write("</Data></Cell>"); break; case "System.Double": excelDoc.Write("<Cell ss:StyleID=\"Decimal\">" + "<Data ss:Type=\"Number\">"); // excelDoc.Write(x[y].ToString()); excelDoc.Write(set.Tables[0].DefaultView[x][y].ToString()); excelDoc.Write("</Data></Cell>"); break; case "System.DBNull": excelDoc.Write("<Cell ss:StyleID=\"StringLiteral\">" + "<Data ss:Type=\"String\">"); excelDoc.Write(""); excelDoc.Write("</Data></Cell>"); break; default: throw (new Exception(rowType.ToString() + " not handled.")); } } excelDoc.Write("</Row>"); } excelDoc.Write("</Table>"); excelDoc.Write(" </Worksheet>"); excelDoc.Write(endExcelXML); excelDoc.Close(); } return(File(new MemoryStream(stream.ToArray()).ToArray(), "application/vnd.ms-excel", "EmployeeLoansBetweenTwoDates.xls")); } // MessageBox.Show("Data exported to excel file successfuly"); } catch (Exception ex) { return(File(new System.Text.UTF8Encoding().GetBytes(ex.Message), "application/vnd.ms-excel", "Error.xls")); } }