Read and Excel file using web API get method.

Rupa Maya

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??

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Laxmidhar Sahoo6y ago
1. Create api with post method like
2. [AcceptVerbs("POST")]
  //Descripbed your method here with bellow code and the name should be exportReport
4. //path to store Excel file temporarily
5. string tempPathExcelFile = AppDomain.CurrentDomain.BaseDirectory + DateTime.Now.Hour + DateTime.Now.Minute +
6. DateTime.Now.Second + DateTime.Now.Millisecond +
7. "_temp";
8. try
9. {
10. //Get Excel using Microsoft.Office.Interop.Excel;
11. Excel.Workbook workbook = ExportDataSetToExcel();
12. workbook.SaveAs(tempPathExcelFile, workbook.FileFormat);
13. tempPathExcelFile = workbook.FullName;
14. workbook.Close();
15. byte[] fileBook = File.ReadAllBytes(tempPathExcelFile);
16. MemoryStream stream = new MemoryStream();
17. string excelBase64String = Convert.ToBase64String(fileBook);
18. StreamWriter excelWriter = new StreamWriter(stream);
19. excelWriter.Write(excelBase64String);
20. excelWriter.Flush();
21. stream.Position = 0;
22. HttpResponseMessage httpResponseMessage = new HttpResponseMessage();
23. httpResponseMessage.Content = new StreamContent(stream);
24. httpResponseMessage.Content.Headers.Add("x-filename", "ExcelReport.xlsx");
25. httpResponseMessage.Content.Headers.ContentType = new MediaTypeHeaderValue("application/vnd.ms-excel");
26. httpResponseMessage.Content.Headers.ContentDisposition =
27. new ContentDispositionHeaderValue("attachment");
28. httpResponseMessage.Content.Headers.ContentDisposition.FileName = "ExcelReport.xlsx";
29. httpResponseMessage.StatusCode = HttpStatusCode.OK;
30. return httpResponseMessage;
32. }
33. catch (Exception ex)
34. {
35. _logger.ErrorException(errorMessage, ex);
36. return ReturnError(ErrorType.Error, errorMessage);
37. }
38. finally
39. {
40. if (File.Exists(tempPathExcelFile))
41. {
42. File.Delete(tempPathExcelFile);
43. }
44. }
46. //Javascript Code
47. $.ajax({
48. url: "/api/exportReport",
49. type: 'POST',
50. headers: {
51. Accept: "application/vnd.ms-excel; base64",
52. },
53. success: function (data) {
54. var uri = 'data:application/vnd.ms-excel;base64,' + data;
55. var link = document.createElement("a");
56. link.href = uri;
57. link.style = "visibility:hidden";
58. link.download = "ExcelReport.xlsx";
59. document.body.appendChild(link);
60. link.click();
61. document.body.removeChild(link);
62. },
63. error: function () {
64. console.log('error Occured while Downloading CSV file.');
65. },
66. });
67. In the end create an empty anchor tag at the end of your html file.

Rupa Maya6y ago

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

Laxmidhar Sahoo6y ago

Yes you can .Some example in the bellow url

https://stackoverflow.com/questions/14831860/download-excel-file-from-page-via-webapi-call

Rupa Maya6y ago

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 Sahoo6y ago

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/

Take a look into https://jspreadsheets.com/

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();
}
}