angular

in component

downloadCartToExcel(): void {
  this.loadContent = false;
  this.fileService.exportCartToExcel(this.cartId).subscribe(data => {
    const fileName = `DD_ShoppingCartItems_${this.cartName}.xlsx`;

    this.fileService.saveAsExcelFile(data, fileName);
    this.loadContent = true;


  },
    (error: any) => {
      console.error(error);
      this.loadContent = true;
    });
}

in service file

import * as _filesaver from 'file-saver';
const EXCEL_TYPE = 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8';
const IMAGE_TYPE = 'image/png';
const PDF_TYPE = 'application/pdf';
const CSV_TYPE = 'application/csv';
const JSON_TYPE = 'application/json';

exportCartToExcel(cartId: string): Observable<any> {
    return this.http.get('/api/ExportExcel/exportcarttoexcel?cartId=' + cartId,
    { responseType: 'blob', headers: {'Accept': 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8'} })
        .pipe(catchError(this.authService.handleError));
}

saveAsExcelFile(buffer: any, fileName: string): void {
    const data: Blob = new Blob([buffer], {
        type: EXCEL_TYPE
    });
    _filesaver.saveAs(data, fileName);
}

controller

[HttpGet("exportcarttoexcel")]
[NoCache]
public IActionResult ExportCartToExcel(string cartId)
{
    FileResult file = null;
    try
    {
        var cart = _dickerDataContext.Carts.Where(x => x.CartId == Convert.ToInt32(cartId)).SingleOrDefault();
        if (cart == null) return file;
        using (ExcelPackage pck = new ExcelPackage())
        {
            _excelManager.AddWorksheetForCarts(pck, int.Parse(cartId), cart.CartName);
            return File(pck.GetAsByteArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        }
    }
    catch (Exception ex)
    {
        _dickerGlobal.AddWebLog(LogType.WebsiteError, ControllerContext, "Export cart to excel failed", ex.Message);
        return BadRequest(new ApiResponse { Status = false, ResponseMessage = ex.Message });
    }
}

Service file

public ExcelPackage AddWorksheetForCarts(ExcelPackage pck, int cartId, string CartName)
{
    // Create Headers
    string[] headerList = { "Cart Name", "Parent Code", "Stock Code", "Item Desc", "RRP (Ex tax)", "RRP (Inc tax)", "Reseller Price(Ex tax)", "Quantity", "Subtotal (Ex tax)" };

    // Capture Number Of Columns
    // We'll Use This Later When Creating Rows
    int iColCount = headerList.Length;

    ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Cart Name:" + CartName);
    ws.PrinterSettings.FitToWidth = 1;
    ws.PrinterSettings.FitToPage = true;
    ws.PrinterSettings.FitToHeight = 0;
    using (ExcelRange rng = ws.Cells[ws.Cells.Start.Row, ws.Cells.Start.Column, ws.Cells.End.Row, ws.Cells.End.Column])
    {
        rng.Style.Fill.PatternType = ExcelFillStyle.Solid;
        rng.Style.Fill.BackgroundColor.SetColor(Color.White);
    }
    ws.Column(1).Width = 25; // B
    ws.Column(2).Width = 20; // C
    ws.Column(3).Width = 90; // D
    ws.Column(4).Width = 20; // E
    ws.Column(5).Width = 15; // F
    ws.Column(6).Width = 20; // G
    ws.Column(7).Width = 10; // H
    ws.Column(8).Width = 20; // I
    // Loop Through The Specifications
    ws.Row(1).Height = 80;

    // Add Dicker Logo
    AddImage(ws, 1, 1, Global.CurrentUrl + "images/DickerData_Logo_BlackRed.png", 160, 90);

    // Add Contact Banner
    if (Global.CompanyCode == Global.NZ)
    {
        AddImage(ws, 1, 2, Global.CurrentUrl + "images/newsletter/contact-banner-nz.png", 813, 90);
    }
    else
    {
        AddImage(ws, 1, 2, Global.CurrentUrl + "images/newsletter/contact-banner.png", 813, 90);
    }

    int rowCounter = 2;
    // Write the Headers
    ws.Cells["A" + rowCounter].Value = "Cart Name";
    ws.Cells["B" + rowCounter].Value = "Stock Code";
    ws.Cells["C" + rowCounter].Value = "Item Desc";
    ws.Cells["D" + rowCounter].Value = "RRP (Ex tax)";
    ws.Cells["E" + rowCounter].Value = "RRP (Inc tax)";
    ws.Cells["F" + rowCounter].Value = "Reseller Price (Ex tax)";
    ws.Cells["G" + rowCounter].Value = "Quantity";
    ws.Cells["H" + rowCounter].Value = "Subtotal (Ex tax)";

    // Format Column Titles
    using (ExcelRange rng = ws.Cells["A2:J2"])
    {
        rng.Style.Font.Bold = true;
        rng.Style.Fill.PatternType = ExcelFillStyle.Solid;
        rng.Style.Fill.BackgroundColor.SetColor(Color.Maroon);
        rng.Style.Font.Color.SetColor(Color.White);
    }

    // Get Cart Data And Print To Screen
    var cartProductList = _dickerDataContext.CartItems
    .Join(_dickerDataContext.CartItemStatus, x => x.Status, y => y.StatusId,
    (y, x) => new
    {
        CartId = y.CartId,
        ParentStockCode = y.ParentStockCode,
        StockCode = y.StockCode,
        Description = y.Description,
        ResellerPrice = y.ResellerPrice,
        RetailPrice = y.RetailPrice,
        Quantity = y.Quantity,
        TotalPrice = y.TotalPrice,
        Status = x.StatusId,
        StatusDesc = x.Description,
    })
    .Join(_dickerDataContext.Carts,
    y => y.CartId, x => x.CartId,
    (y, x) => new
    {
        CartId = y.CartId,
        CartName = x.CartName,

        ParentStockCode = y.ParentStockCode,
        StockCode = y.StockCode,
        Description = y.Description,
        ResellerPrice = y.ResellerPrice,
        RetailPrice = y.RetailPrice,
        Quantity = y.Quantity,
        TotalPrice = y.TotalPrice,
        StatusDesc = y.Description
    })
    .Where(x => x.CartId == cartId)
    .OrderBy(x => x.ParentStockCode)
    .ThenBy(x => x.StockCode);

    // Use These to Sum The Totals
    decimal cumulativeQuantity = 0;
    decimal cumulativeTotalPrice = 0;
    decimal dRetailPrice = 0.0m;
    rowCounter++;
    // Write rows.
    foreach (var obj in cartProductList)
    {
        string cartName = obj.CartName;
        string parentCode = obj.ParentStockCode;
        string stockCode = obj.StockCode;
        string desc = obj.Description;
        string rrpEx = obj.RetailPrice.ToString() ?? string.Empty;
        decimal.TryParse(rrpEx, out dRetailPrice);
        string rrpInc = Convert.ToString(dRetailPrice * Global.CountryGST());
        string cost = obj.ResellerPrice.ToString();
        string qty = obj.Quantity.ToString();
        string total = obj.TotalPrice.ToString();

        cumulativeQuantity += decimal.Parse(qty);
        cumulativeTotalPrice += decimal.Parse(total);

        // Create Row
        string[] rowList = { cartName, parentCode, stockCode, desc, rrpEx, qty, total };

        //ws.Cells["A" + rowCounter].Value = cartNumber;
        ws.Cells["A" + rowCounter].Value = cartName;
        ws.Cells["B" + rowCounter].Value = stockCode;
        ws.Cells["C" + rowCounter].Value = desc;
        ws.Cells["D" + rowCounter].Value = _dBManager.FormatCurrency(rrpEx); // string.Format("${0:#,###,###.##}", rrpEx);
        ws.Cells["E" + rowCounter].Value = _dBManager.FormatCurrency(rrpInc); // string.Format("${0:#,###,###.##}", rrpInc);
        ws.Cells["F" + rowCounter].Value = _dBManager.FormatCurrency(cost); // string.Format("${0:#,###,###.##}", cost);
        ws.Cells["G" + rowCounter].Value = qty;
        ws.Cells["H" + rowCounter].Value = _dBManager.FormatCurrency(total); // string.Format("${0:#,###,###.##}", total);
        rowCounter++;
    }

    // Add Totals
    rowCounter++;
    using (ExcelRange rng = ws.Cells["A" + rowCounter + ":J" + rowCounter])
    {
        rng.Style.Font.Bold = true;
        rng.Style.Fill.PatternType = ExcelFillStyle.Solid;
        rng.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
        rng.Style.Fill.BackgroundColor.SetColor(Color.Maroon);
        rng.Style.Font.Color.SetColor(Color.White);
    }
    ws.Cells["A" + rowCounter].Value = "Total";
    ws.Cells["G" + rowCounter].Value = cumulativeQuantity;
    ws.Cells["H" + rowCounter].Value = _dBManager.FormatCurrency(cumulativeTotalPrice.ToString()); //string.Format("${0:#,###,###.##}", cumulativeTotalPrice); ;

    // Clean Up
    cartProductList = null;

    return pck;
}

public void AddImage(ExcelWorksheet ws, int rowIndex, int colIndex, String imageFile = "", int iWidth = 0, int iHeight = 0)
{
    // Exit If Image Is Empty
    if (string.IsNullOrEmpty(imageFile))
        return;
 
    // Create Are Image Handles
    ExcelPicture picture;
    Image image;
 
    // Source Image File From URL
    var request = WebRequest.Create(imageFile);
 
    ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls | SecurityProtocolType.Tls11 | SecurityProtocolType.Tls12;
 
    using (var response = request.GetResponse())
    {
        using (Stream stream = response.GetResponseStream())
        {
            image = Bitmap.FromStream(stream);
        }
    }
 
    // If Image Exists
    // Insert into Excel
    if (image != null)
    {
        string uniqueId = DateTime.Now.Ticks.ToString();
        try
        {
            picture = ws.Drawings.AddPicture("pic" + uniqueId, image);
            picture.SetPosition(rowIndex - 1, 5, colIndex - 1, 15);
            picture.SetSize(iWidth, iHeight);
        }
        catch (Exception ex)
        {
            _dickerDataLogger.Add(LogType.Downloads, "Export", "ExportAllCartsToExcel", ex.Message, _currentUser.AccountId, _currentUser.LoginId);
        }
    }
}