how to read local excel file using webapi?

Question

public class ExcelParserController : ApiController
{
    [HttpGet]
    public IEnumerable<string> Get(string templatePath)
    {
        ExcelParser excelParser = new ExcelParser();
        return excelParser.Parse(templatePath);
    }
}

This is my first idea. But I cannot know how to read local excel file from server. templatePath is like "C:\1.xlsx". How to open local file by using webapi?

Answer 1

You can use the OpenXML SDK to access Office documents including Excel files.

Here's a sample from the documentation: https://msdn.microsoft.com/EN-US/library/office/gg575571.aspx

public static void OpenSpreadsheetDocument(string filepath)
{
    // Open a SpreadsheetDocument based on a filepath.
    using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(filepath, false))
    {
        WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
        WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();
        SheetData sheetData = worksheetPart.Worksheet.Elements<SheetData>().First();
        string text;
        foreach (Row r in sheetData.Elements<Row>())
        {
            foreach (Cell c in r.Elements<Cell>())
            {
                text = c.CellValue.Text;
                Console.Write(text + " ");
             }
        }
    }
}

Answer 2

You can go for ASPOSE.CELL you can utilize it's API, they also provide trial, so you can check if it helps in your work. Aspose.cells

They also provide many tutorials.

Answer 3

A quick search of the internet and you come across the following article with simple examples on how to read xls and xlsx files using c#.

CodeProjects article on reading/writing Excel files

Take one of these examples and adopt it to fit your needs, where the example asks you to specify the path to the file, simply pass in your "templatePath" string.

Once the excel data has been read into your NPOI workbook or your dataset (Depending on the option you take) you should be able to use linq to return the IEnumerable data that you require.