top of page

Handling Excel files in Web API: Common Approaches

Handling Excel files on web API means creating a web service that can accept, process, and return Excel files as input or output.


A web service is a software system that allows communication between different applications over the internet using standard protocols such as HTTP. An Excel file is a spreadsheet document that can store data in rows and columns, and perform calculations and analysis on the data.


To handle Excel files on web API, you need to use a library that can read and write Excel files in your programming language, and a framework that can create a web service that can handle HTTP requests with Excel files as multipart/form-data content type.


Handling Excel files in Web API: Common Approaches

There are different ways to handle Excel files in web API, depending on your requirements and preferences. Some of the common approaches are:

  1. Use Multipart/form-data content type

  2. Use Binary Data

  3. Use Base64 Encoding

  4. Use RPA API

  5. Use ExcelDataReader or FastExcel

  6. Use Custom Functions


1: Use Multipart/form-data Content Type

This is a standard way of sending files as part of an HTTP request. You can use this approach to upload or download Excel files to or from a web API. You can also use this approach to send additional data along with the file, such as metadata or parameters.


For example, to upload an Excel file and some metadata using C#, you can use the HttpClient class and the MultipartFormDataContent class:

using (var client = new HttpClient()) 
{     
    // Create a multipart/form-data content
    var content = new MultipartFormDataContent();      
    
    // Add the Excel file as a stream content
    var fileContent = new StreamContent(File.OpenRead("Book.xlsx"));     
    fileContent.Headers.ContentType = MediaTypeHeaderValue.Parse("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");     
    content.Add(fileContent, "file", "Book.xlsx");      
    
    // Add some metadata as string content
    var metadataContent = new StringContent(JsonConvert.SerializeObject(new { Title = "Book", Author = "John" }));     
    metadataContent.Headers.ContentType = MediaTypeHeaderValue.Parse("application/json");     
    content.Add(metadataContent, "metadata");      
    
    // Post the content to the web API
    var response = await client.PostAsync("https://example.com/api/upload", content);      
    
    // Handle the response
    if (response.IsSuccessStatusCode)     
    {         
        Console.WriteLine("File uploaded successfully.");     
    }     
    else     
    {         
        Console.WriteLine("File upload failed: " + response.ReasonPhrase);     
    } 
} 

To download an Excel file and some metadata using C#, you can use the HttpClient class and the MultipartMemoryStreamProvider class:

using (var client = new HttpClient()) 
{     
    // Get the content from the web API
    var response = await client.GetAsync("https://example.com/api/download");      
    
    // Handle the response
    if (response.IsSuccessStatusCode)     
    {         
        // Read the content as a multipart/form-data
        var provider = await response.Content.ReadAsMultipartAsync();          
        
        // Get the Excel file as a stream content
        var fileContent = provider.Contents.FirstOrDefault(c => c.Headers.ContentDisposition.Name == "\"file\"");         
        if (fileContent != null)         
        {             
            // Save the file to disk
            using (var fileStream = File.Create("Book.xlsx"))             
            {                 
                await fileContent.CopyToAsync(fileStream);             
            }         
        }         
        
        // Get the metadata as a string contentvar metadataContent = provider.Contents.FirstOrDefault(c => c.Headers.ContentDisposition.Name == "\"metadata\"");         
        if (metadataContent != null)         
        {             
            // Deserialize the metadata to an object
            var metadata = JsonConvert.DeserializeObject(await metadataContent.ReadAsStringAsync());             
            Console.WriteLine("Metadata: " + metadata);         
        }          
        Console.WriteLine("File downloaded successfully.");     
        }     
        else     
        {         
            Console.WriteLine("File download failed: " + response.ReasonPhrase);     
        } 
}


2: Use Binary Data

This is a simple way of sending or receiving Excel files as raw binary data. You can use this approach to stream Excel files to or from a web API without any encoding or decoding. However, this approach does not allow you to send any additional data along with the file.


For example, to upload an Excel file using C#, you can use the HttpClient class and the StreamContent class:

using (var client = new HttpClient()) 
{     
    // Create a stream content from the Excel file
    var content = new StreamContent(File.OpenRead("Book.xlsx"));     
    content.Headers.ContentType = MediaTypeHeaderValue.Parse("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");      
    
    // Post the content to the web API
    var response = await client.PostAsync("https://example.com/api/upload", content);      
    
    // Handle the response
    if (response.IsSuccessStatusCode)     
    {         
        Console.WriteLine("File uploaded successfully.");     
    }     
    else     
    {         
        Console.WriteLine("File upload failed: " + response.ReasonPhrase);     
    } 
} 

To download an Excel file using C#, you can use the HttpClient class and the ReadAsStreamAsync method:

using (var client = new HttpClient()) 
{     
    // Get the content from the web API
    var response = await client.GetAsync("https://example.com/api/download");      
    
    // Handle the response
    if (response.IsSuccessStatusCode)     
    {         
        // Read the content as a stream
        var stream = await response.Content.ReadAsStreamAsync();          
        
        // Save the stream to disk
        using (var fileStream = File.Create("Book.xlsx"))         
        {             
            await stream.CopyToAsync(fileStream);         
        }          
        Console.WriteLine("File downloaded successfully.");     
    }     
    else     
    {         
        Console.WriteLine("File download failed: " + response.ReasonPhrase);     
    } 
}


3: Use Base64 Encoding

This is a way of converting binary data into ASCII text. You can use this approach to send or receive Excel files as strings. This can be useful if you need to pass the file through a channel that does not support binary data, such as JSON. However, this approach increases the size of the file by about 33% and requires encoding and decoding.


For example, to upload an Excel file as a base64 string using C#, you can use the HttpClient class and the Convert class:

using (var client = new HttpClient()) 
{     
    // Convert the Excel file to a base64 string
    var bytes = File.ReadAllBytes("Book.xlsx");     
    var base64 = Convert.ToBase64String(bytes);      
    
    // Create a string content from the base64 string
    var content = new StringContent(base64);     
    content.Headers.ContentType = MediaTypeHeaderValue.Parse("text/plain");      
    
    // Post the content to the web API
    var response = await client.PostAsync("https://example.com/api/upload", content);      
    
    // Handle the response
    if (response.IsSuccessStatusCode)     
    {         
        Console.WriteLine("File uploaded successfully.");     
    }     
    else     
    {         
        Console.WriteLine("File upload failed: " + response.ReasonPhrase);     
    } 
} 

To download an Excel file as a base64 string using C#, you can use the HttpClient class and the Convert class:

using (var client = new HttpClient()) 
{     
    // Get the content from the web API
    var response = await client.GetAsync("https://example.com/api/download");      
    
    // Handle the response
    if (response.IsSuccessStatusCode)     
    {         
        // Read the content as a base64 string
        var base64 = await response.Content.ReadAsStringAsync();          
        
        // Convert the base64 string to bytes
        var bytes = Convert.FromBase64String(base64);          
        
        // Save the bytes to disk         
        File.WriteAllBytes("Book.xlsx", bytes);          
        
        Console.WriteLine("File downloaded successfully.");     
    }     
    else     
    {         
        Console.WriteLine("File download failed: " + response.ReasonPhrase);     
    } 
}


4: Use RPA API

This is a way of automating Excel using RPA tools and frameworks, such as UiPath or Apache POI. You can use this approach to manipulate Excel files programmatically using RPA API and other API tools. This can be useful if you need to perform complex operations on Excel files, such as formatting, filtering, or calculations. However, this approach requires coding skills and may not be suitable for business users.


For example, to read an Excel file using UiPath, you can use the Read Range activity:

//Create a variable of type DataTable to store the data
DataTable dt = new DataTable();

//Use the Read Range activity inside an Excel Application Scope
//Specify the file path and the range of cells to read
//Assign the output to the variable dt
ExcelApplicationScope excelScope = new ExcelApplicationScope();
excelScope.WorkbookPath = "C:\\Users\\Documents\\Sample.xlsx";
excelScope.Visible = false;
ReadRange readRange = new ReadRange();
readRange.SheetName = "Sheet1";
readRange.Range = "A1:C10";
readRange.DataTable = dt;

//Use the dt variable in other activities as needed

This activity reads a range of cells from an Excel workbook and returns a DataTable object that can be used in other activities.


To write an Excel file using UiPath, you can use the Write Range activity:

//Create a variable of type DataTable with some data
DataTable dt = new DataTable();
dt.Columns.Add("Name");
dt.Columns.Add("Age");
dt.Columns.Add("Gender");
dt.Rows.Add("Alice", 25, "F");
dt.Rows.Add("Bob", 30, "M");
dt.Rows.Add("Charlie", 35, "M");

//Use the Write Range activity inside an Excel Application Scope
//Specify the file path and the range of cells to write
//Assign the input as the variable dt
ExcelApplicationScope excelScope = new ExcelApplicationScope();
excelScope.WorkbookPath = "C:\\Users\\Documents\\Output.xlsx";
excelScope.Visible = false;
WriteRange writeRange = new WriteRange();
writeRange.SheetName = "Sheet1";
writeRange.Range = "A1:C3";
writeRange.DataTable = dt;

This activity writes a DataTable object to a range of cells in an Excel workbook.


5: Use ExcelDataReader or FastExcel - a third-party library

These are third-party libraries that allow you to read and write Excel files in C# .NET. These libraries provide efficient and easy-to-use APIs to work with large Excel files.


For example, to read an Excel file using ExcelDataReader, you can use the following code:

using (var stream = File.Open("Book.xlsx", FileMode.Open, FileAccess.Read)) {     
    // Auto-detect format, supports:
    //  - Binary Excel files (2.0-2003 format; *.xls)
    //  - OpenXml Excel files (2007 format; *.xlsx)
    using (var reader = ExcelReaderFactory.CreateReader(stream))     
    {         
        // Choose one of either 1 or 2:
        // 1. Use the reader methods
        do         
        {             
            while (reader.Read())             
            {                 
                // reader.GetDouble(0);             
            }         
        } while (reader.NextResult());          
        
        // 2. Use the AsDataSet extension method
        var result = reader.AsDataSet();          
        
        // The result of each spreadsheet is in result.Tables     
    } 
}


6: Using Custom Functions

Use the custom functions feature in Office Add-ins to receive and handle data from external sources such as the web or a server. You can use fetch or XmlHttpRequest (XHR) to request data from an API and return a JavaScript Promise to Excel.


For example, to create a custom function that gets the current temperature of a city using an API, you can use the following code:

/**  
* Gets the current temperature of a city.  
* @customfunction  * @param {string} city The name of the city.  
* @returns {number} The temperature in Celsius.  
*/
function getTemperature(city) {   
    // Create a URL to the API endpoint
    var url = "https://api.openweathermap.org/data/2.5/weather?q=" + city + "&appid=YOUR_API_KEY&units=metric";    
    
    // Use fetch to get the data from the API
    return fetch(url)     
        .then(function(response) {       
            // Check if the response is OK
            if (response.ok) {         
                // Parse the response as JSON
                return response.json();       
            } else {         
                // Throw an error if the response is not OK
                throw new Error("Could not get data from the API: " + response.statusText);       
            }     
        })     
        .then(function(data) {       
            // Return the temperature from the data
            return data.main.temp;     
        })     
        .catch(function(error) {       
            // Return the error message
            return error.message;     
        }); 
} 

To use this custom function in Excel, you can enter =CONTOSO.GETTEMPERATURE("London") in a cell and press Enter. The cell will display the current temperature of London in Celsius.


Things to Avoid while Working with Excel Files in Web API

There are some things that you should avoid while working with Excel files in Web API, such as:


1. Overusing Dialog Boxes

Dialog boxes are overlapping UI elements that can disrupt the user experience and cause performance issues. You should avoid opening a dialog box from a task pane unless your scenario requires it. You should also design your dialog box UI to be clear, concise, and responsive.


2. Sending Large Files

Large files can take a long time to upload or download and consume a lot of bandwidth and memory. You should try to reduce the size of your Excel files by removing unnecessary data, formatting, or formulas. You should also use compression techniques such as gzip or deflate to reduce the size of your HTTP requests and responses.


3. Ignoring Errors and Exceptions

Errors and exceptions can occur when working with Excel files in Web API, such as invalid file format, corrupted file, timeout, network failure, or access denied. You should handle these errors and exceptions gracefully and provide meaningful feedback to the user. You should also use logging and debugging tools to identify and fix the root cause of the errors.


4. Using Incompatible Formats or Libraries

Excel files can have different formats depending on the version of Excel or the file extension. For example, .xls files are binary files that use the Excel 97-2003 format, while .xlsx files are XML-based files that use the Open XML format. You should use compatible formats or libraries when working with Excel files in Web API. For example, you should use ExcelDataReader for .xls files and EPPlus for .xlsx files in C# .NET.


5. Modifying the Workbook Structure without Protection

The workbook structure refers to the arrangement of worksheets, charts, tables, and other objects in a workbook. Modifying the workbook structure without protection can cause data loss, corruption, or inconsistency. You should protect the workbook structure before making any changes to it using the Workbook.protect method. This method prevents users or add-ins from adding, deleting, hiding, moving, or renaming worksheets.


Conclusion

These approaches aim to ensure efficient processing, maintain data integrity, and provide a seamless user experience. By following these approaches, you can ensure that your web API efficiently handles Excel files, maintains data integrity, and provides a smooth user experience for file uploads and processing.

0 comments

Kommentarer


bottom of page