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
}
