How To Download An Excel File In Angular 2
Today, I want to talk about the second method that exports data with an excel file in Angular 2. I mentioned this method in my last post. We can do it in several ways on the server side, it depends on to your web project. I will show you how to do it in an asp.net mvc project. This code is written in c# and its converting the data to excel file. You must add Excel dll files to your references to use this code.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 |
using System; using System.Collections.Generic; using OfficeOpenXml; using OfficeOpenXml.Style; namespace Utility { public class ExcelExport { private int rowIndex = 2; private ExcelRange cell; private ExcelFill fill; private Border border; public List<string> sutunList; public string title { get; set; } public string RaporBaslik { get; set; } public string RaporBaslikIcerik { get; set; } private IMcxSchema _schema; private List<IMcxFeature> _featureList; public ExcelExport(IMcxSchema schema, List<IMcxFeature> featureList) { sutunList = new List<string>(); _schema = schema; _featureList = featureList; } public void CreateSheetHeader(string date, ExcelWorksheet sheet) { sheet.Cells[rowIndex, 2, rowIndex, _schema.FieldCount + 2].Merge = true; cell = sheet.Cells[rowIndex, 2]; cell.Value = DateTime.Now.ToString("dd.MM.yyyy"); cell.Style.Font.Bold = true; cell.Style.Font.Size = 13; cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Right; rowIndex += 2; sheet.Cells[rowIndex, 2, rowIndex, _schema.FieldCount + 2].Merge = true; cell = sheet.Cells[rowIndex, 2]; cell.Value = "T.C."; cell.Style.Font.Bold = true; cell.Style.Font.Size = 20; cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; rowIndex++; sheet.Cells[rowIndex, 2, rowIndex, _schema.FieldCount + 2].Merge = true; cell = sheet.Cells[rowIndex, 2]; cell.Value = title; cell.Style.Font.Bold = true; cell.Style.Font.Size = 14; cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; rowIndex++; sheet.Cells[rowIndex, 2, rowIndex, _schema.FieldCount + 2].Merge = true; cell = sheet.Cells[rowIndex, 2]; cell.Value = RaporBaslikIcerik; cell.Style.Font.Bold = true; cell.Style.Font.Size = 13; cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; rowIndex++; sheet.Cells[rowIndex, 2, rowIndex, _schema.FieldCount + 2].Merge = true; cell = sheet.Cells[rowIndex, 2]; cell.Value = RaporBaslik; cell.Style.Font.Bold = true; cell.Style.Font.Size = 13; cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; rowIndex += 2; } public void CreateTableHeader(ExcelWorksheet sheet) { int colIndex = 2; cell = sheet.Cells[rowIndex, colIndex]; cell.Value = "#"; cell.Style.Font.Bold = true; cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; cell.Style.VerticalAlignment = ExcelVerticalAlignment.Center; fill = cell.Style.Fill; fill.PatternType = ExcelFillStyle.Solid; fill.BackgroundColor.SetColor(System.Drawing.Color.LightGray); border = cell.Style.Border; border.Bottom.Style = border.Top.Style = border.Left.Style = border.Right.Style = ExcelBorderStyle.Thin; colIndex++; foreach (var item in _schema.Fields) { cell = sheet.Cells[rowIndex, colIndex]; cell.Value = item.FieldName; cell.Style.Font.Bold = true; cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; cell.Style.VerticalAlignment = ExcelVerticalAlignment.Center; fill = cell.Style.Fill; fill.PatternType = ExcelFillStyle.Solid; fill.BackgroundColor.SetColor(System.Drawing.Color.LightGray); border = cell.Style.Border; border.Bottom.Style = border.Top.Style = border.Left.Style = border.Right.Style = ExcelBorderStyle.Thin; colIndex++; } rowIndex++; } public void CreateTableBody(ExcelWorksheet sheet) { int sort = 1; foreach (var feature in _featureList) { int colIndex = 2; cell = sheet.Cells[rowIndex, colIndex]; cell.Value = sort.ToString(); cell.Style.Font.Bold = true; cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; cell.Style.VerticalAlignment = ExcelVerticalAlignment.Center; fill = cell.Style.Fill; fill.PatternType = ExcelFillStyle.Solid; fill.BackgroundColor.SetColor(System.Drawing.Color.White); border = cell.Style.Border; border.Bottom.Style = border.Top.Style = border.Left.Style = border.Right.Style = ExcelBorderStyle.Thin; sort++; colIndex++; foreach (var field in feature.Schema.Fields) { cell = sheet.Cells[rowIndex, colIndex]; //if (feature[field.FieldName] == null) continue; cell.Value = feature[field.FieldName] + ""; cell.Style.Font.Bold = true; cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; cell.Style.VerticalAlignment = ExcelVerticalAlignment.Center; var cellValLength = (feature[field.FieldName] + "").Length; sheet.Column(colIndex).Width = sheet.Column(colIndex).Width < (cellValLength + 7) ? (cellValLength + 7) : sheet.Column(colIndex).Width; fill = cell.Style.Fill; fill.PatternType = ExcelFillStyle.Solid; fill.BackgroundColor.SetColor(System.Drawing.Color.White); border = cell.Style.Border; border.Bottom.Style = border.Top.Style = border.Left.Style = border.Right.Style = ExcelBorderStyle.Thin; colIndex++; } rowIndex++; } } public bool sutunAdKontrol(string sutunAd) { if (sutunList.Contains(sutunAd)) return true; else return false; } public byte[] GenerateExcel() { byte[] excelBytes; using (var excelPackage = new ExcelPackage()) { excelPackage.Workbook.Properties.Author = "Report Project"; excelPackage.Workbook.Properties.Title = "Case Report"; var sheet = excelPackage.Workbook.Worksheets.Add("Case Report"); sheet.Name = "Case Report"; CreateSheetHeader(DateTime.Now.ToString("dd.MM.yyyy"), sheet); CreateTableHeader(sheet); CreateTableBody(sheet); excelBytes = excelPackage.GetAsByteArray(); } return excelBytes; } } } |
And then we will send the data with our controller method, file must be send in the httpResponseMessage byteArrayContent. We will use this as a service in Angular to fetch the file for downloading.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
public HttpResponseMessage ExcelExportingJson(int userId) { // get data from db .. List<Feature> _featureList; _featureList = getData(userId); ExcelExport exc = new ExcelExport(_featureList.FirstOrDefault().Schema, _featureList); exc.RaporBaslik = "Case Report"; exc.RaporBaslikIcerik = ""; exc.title = "AFAD"; HttpResponseMessage response = new HttpResponseMessage(); response.StatusCode = HttpStatusCode.OK; response.Content = new ByteArrayContent(exc.GenerateExcel()); response.Content.Headers.Add("content-disposition", "attachment; filename=aa.xlsx"); response.Content.Headers.ContentType = new MediaTypeHeaderValue("application/octet-stream"); response.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment"); return response; } |
We should prepare an Angular service to get and download the excel file.
1 2 3 4 5 6 7 8 9 10 |
public ExcelExport2(userId:number):Observable<Blob> { const header = new HttpHeaders().set('content-type', 'application/octet-stream'); return this.http.get(url + 'rapor/ExcelExportingJson' + '?userId=' + userId , {header, responseType: ResponseContentType.Blob}); } // naming excel file service public toExportFileName(excelFileName: string,extensionName: string): string { return `${excelFileName}_${new Date().getTime()}.${extensionName}`; } |
And then this code will be in your html file and component.ts file.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
----- html code ------- <div class="col-md-2" *ngIf="totalRecords > 0"> <button class="btn btn-secondary btn-block" id="btnExc" (click)="excel2()"><span class="glyphicon glyphicon-download"></span> Excele Aktar (Server)</button> </div> ----- component code ------- excel2(){ this.service.ExcelExport2(this.selectedUserKod) .subscribe(res => { const data = new Blob([res._body], {type: 'application/octet-stream'}); var fileName = this.service.toExportFileName("caseReport","xlsx"); saveAs(data, fileName); }); } |
We need to install file-saver component to save bytestream as an excel file. You can do it with this command in node.js “npm install file-saver –save”.
You should have a look at my another post about Download Pdf File In Angular2
If you have question do not forget to write from the chat button next to it or from the comment.
1 Response
[…] That’s all. I will explain downloading excel file from server in my next post […]