public ActionResult Curexport_report() { dbtestEntities1 db = new dbtestEntities1(); DateTime? dateT = DateTime.Parse("2020-07-31"); var cr = db.EXCHANGERATES; Curexport_excel(cr.ToList()); return(RedirectToAction("Contact", "Home", new { flg = "1", active = "2", msg = "Upload file sucess!" })); }
public JsonResult Api_curentcy() { dbtestEntities1 db = new dbtestEntities1(); DateTime? dateT = DateTime.Parse("2020-07-31"); var cr = db.EXCHANGERATES.Select(s => new { s.EXC_DATE , s.CAD , s.HKD , s.ISK , s.PHP , s.DKK , s.HUF , s.CZK , s.GBP , s.RON , s.SEK , s.IDR , s.INR , s.BRL , s.RUB , s.HRK , s.JPY , s.THB , s.CHF , s.EUR , s.MYR , s.BGN , s.TRY , s.CNY , s.NOK , s.NZD , s.ZAR , s.USD , s.MXN , s.SGD , s.AUD , s.ILS , s.KRW , s.PLN }); return(Json(cr, JsonRequestBehavior.AllowGet)); }
public void Curexport_excel(List <EXCHANGERATES> dg) { ExcelPackage.LicenseContext = LicenseContext.Commercial; ExcelPackage.LicenseContext = LicenseContext.NonCommercial; int _head = 3; int _rows = 5; int _filt = _rows - 1; #region generate using (ExcelPackage excelPackage = new ExcelPackage()) { List <object[]> col = new List <object[]> { new object[] { #region header "EXC_DATE" , "CAD" , "HKD" , "ISK" , "PHP" , "DKK" , "HUF" , "CZK" , "GBP" , "RON" , "SEK" , "IDR" , "INR" , "BRL" , "RUB" , "HRK" , "JPY" , "THB" , "CHF" , "EUR" , "MYR" , "BGN" , "TRY" , "CNY" , "NOK" , "NZD" , "ZAR" , "USD" , "MXN" , "SGD" , "AUD" , "ILS" , "KRW" , "PLN" } #endregion }; dbtestEntities1 db = new dbtestEntities1(); //DateTime? dateT = DateTime.Parse("2020-07-31"); var cr = db.EXCHANGERATES.Select(s => new { #region content s.EXC_DATE , s.CAD , s.HKD , s.ISK , s.PHP , s.DKK , s.HUF , s.CZK , s.GBP , s.RON , s.SEK , s.IDR , s.INR , s.BRL , s.RUB , s.HRK , s.JPY , s.THB , s.CHF , s.EUR , s.MYR , s.BGN , s.TRY , s.CNY , s.NOK , s.NZD , s.ZAR , s.USD , s.MXN , s.SGD , s.AUD , s.ILS , s.KRW , s.PLN #endregion }).OrderByDescending(o => o.EXC_DATE); int _crow = col[0].Count(); int _ccol = cr.Count(); ExcelWorksheet worksheet = excelPackage.Workbook.Worksheets.Add("Exchenge report"); //add all the content from the List<Book> collection, starting at cell A1 worksheet.Cells["A" + _head].LoadFromArrays(col); worksheet.Cells["A" + _rows].LoadFromCollection(cr); worksheet.Column(1).Style.Numberformat.Format = "yyyy-MM-dd"; #region Filter worksheet.Cells["A" + _filt].AutoFilter = true; #endregion #region width col worksheet.Column(1).Width = 15; for (int i = 2; i <= _ccol; i++) { worksheet.Column(i).Width = 8.11; } #endregion #region height worksheet.Row(_filt).Height = 10.25; #endregion byte[] bin = excelPackage.GetAsByteArray(); //clear the buffer stream Response.ClearHeaders(); Response.Clear(); Response.Buffer = true; Response.ContentType = "application/vnd.openxmlformatsofficedocument.spreadsheetml.sheet"; Response.AddHeader("content-length", bin.Length.ToString()); Response.AddHeader("content-disposition", "attachment; filename=\"ExcelDemo.xlsx\""); Response.OutputStream.Write(bin, 0, bin.Length); //cleanup Response.Flush(); System.Web.HttpContext.Current.ApplicationInstance.CompleteRequest(); } #endregion }