Read and Excel file using web API get method.

Question

I have the code to read an excel file normally i.e., using the C# console app but can we use the same logic using a web API controller?? and the read data must be displayed using the same web API method??

Laxmidhar Sahoo · Answer

Create api with post method like [ AcceptVerbs ( "POST" )] //Descripbed your method here with bellow code and the name should be exportReport //path to store Excel file temporarily string tempPathExcelFile = AppDomain.CurrentDomain.BaseDirectory + DateTime.Now.Hour + DateTime.Now.Minute + DateTime.Now.Second + DateTime.Now.Millisecond + "_temp" ; try { //Get Excel using Microsoft.Office.Interop.Excel; Excel.Workbook workbook = ExportDataSetToExcel(); workbook.SaveAs(tempPathExcelFile, workbook.FileFormat); tempPathExcelFile = workbook.FullName; workbook.Close(); byte [] fileBook = File.ReadAllBytes(tempPathExcelFile); MemoryStream stream = new MemoryStream(); string excelBase64String = Convert.ToBase64String(fileBook); StreamWriter excelWriter = new StreamWriter(stream); excelWriter.Write(excelBase64String); excelWriter.Flush(); stream.Position = 0; HttpResponseMessage httpResponseMessage = new HttpResponseMessage(); httpResponseMessage.Content = new StreamContent(stream); httpResponseMessage.Content.Headers.Add( "x-filename" , "ExcelReport.xlsx" ); httpResponseMessage.Content.Headers.ContentType = new MediaTypeHeaderValue( "application/vnd.ms-excel" ); httpResponseMessage.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue( "attachment" ); httpResponseMessage.Content.Headers.ContentDisposition.FileName = "ExcelReport.xlsx" ; httpResponseMessage.StatusCode = HttpStatusCode.OK; return httpResponseMessage; } catch (Exception ex) { _logger.ErrorException(errorMessage, ex); return ReturnError(ErrorType.Error, errorMessage); } finally { if (File.Exists(tempPathExcelFile)) { File.Delete(tempPathExcelFile); } } //Javascript Code $.ajax({ url: "/api/exportReport" , type: 'POST' , headers: { Accept: "application/vnd.ms-excel; base64" , }, success: function (data) { var uri = 'data:application/vnd.ms-excel;base64,' + data; var link = document.createElement( "a" ); link.href = uri; link.style = "visibility:hidden" ; link.download = "ExcelReport.xlsx" ; document.body.appendChild(link); link.click(); document.body.removeChild(link); }, error: function () { console.log( 'error Occured while Downloading CSV file.' ); }, }); In the end create an empty anchor tag at the end of your html file.

Rupa Maya · Answer

i want to only read the excel file i tried but im not getting any output in web api response

Laxmidhar Sahoo · Answer

hi Yes you can .Some example in the bellow url https://stackoverflow.com/questions/14831860/download-excel-file-from-page-via-webapi-call

Rupa Maya · Answer

Thanks, but i just want to read the excel file using web api method ? is there any way to read excel by creating get method but without using MVC?

Laxmidhar Sahoo · Answer

hi Same logic can use for create excel.But for display you may use the bellow code or lfollow the link some other method also there https://www.red-gate.com/simple-talk/dotnet/asp-net/building-an-excel-view-engine-for-asp-net-mvc/ or Take a look into https://jspreadsheets.com/ or https://handsontable.com/ public void GetExcel() { var list = GetData(); using (var excelPackage = new ExcelPackage()) { excelPackage.Workbook.Properties.Author = "Web App" ; excelPackage.Workbook.Properties.Title = "Export from the Web" ; var sheet = excelPackage.Workbook.Worksheets.Add( "Export Results" ); // output a line for the headers CreateHeader(sheet); sheet.Name = "export results" ; // all indexes start at 1 var rowIndex = 2; foreach (var item in list) { var col = 1; sheet.Cells[rowIndex, col++].Value = item.Description; sheet.Cells[rowIndex, col++].Value = item.ItemNumber; sheet.Cells[rowIndex, col++].Value = item.Quantity; sheet.Cells[rowIndex, col++].Value = item.Price; sheet.Cells[rowIndex, col++].Value = item.Total; rowIndex++; } sheet.Column(4).Style.Numberformat.Format = "$#,##0.00" ; sheet.Column(5).Style.Numberformat.Format = "$#,##0.00" ; // You could just save on ExcelPackage here but we need it in // memory to stream it back to the browser Response.ClearContent(); Response.BinaryWrite(excelPackage.GetAsByteArray()); Response.AddHeader( "content-disposition" , "attachment;filename=results.xlsx" ); Response.ContentType = "application/excel" ; Response.Flush(); Response.End(); } }