Hi,
I want to read data in an Excel sheet, modify the data as per my requirements with an API call and then save the updated Excel sheet in C# using Aspose.Cells. The formatting and layout of the output Excel sheet should remain same as the input sheet.
Reading and updating data cell by cell is not feasible as I need to send data using a rest API call and update it after I get the response from the API. I cannot send request for each cell one by one. Is there any other way such as updating a complete row in sheet or bulk of cells?
To read and update data in an Excel sheet using Aspose.Cells in C#, you can utilize the Workbook and Worksheet classes to handle bulk updates efficiently. Instead of updating each cell individually, you can modify a range of cells at once, which is more suitable for your use case where you need to send data via an API call.
Here’s a step-by-step approach to achieve this:
Load the Workbook: Start by loading your existing Excel file into a Workbook object.
Access the Worksheet: Get the specific worksheet you want to modify.
Define the Range: Create a range of cells that you want to update.
Update the Range: Use the PutValue method to update the values in the defined range.
Save the Workbook: Finally, save the updated workbook to retain the changes.
Here’s a sample code snippet demonstrating this process:
using System;
using Aspose.Cells;
namespace UpdateExcelExample
{
class Program
{
static void Main(string[] args)
{
// Load the existing workbook
Workbook workbook = new Workbook("input.xlsx");
Worksheet worksheet = workbook.Worksheets[0];
// Define the range you want to update (e.g., A1:C3)
Range range = worksheet.Cells.CreateRange("A1", "C3");
// Example data to update the range
object[,] data = new object[,]
{
{ "USA", "Canada", "Mexico" },
{ "UK", "Germany", "France" },
{ "India", "China", "Japan" }
};
// Update the range with new data
range.CopyData(data);
// Save the updated workbook
workbook.Save("output.xlsx");
Console.WriteLine("Excel sheet updated successfully.");
}
}
}
Key Points:
The CreateRange method allows you to specify the range of cells you want to update.
The CopyData method can be used to update multiple cells at once, which is efficient for your scenario.
Ensure that the formatting and layout of the original sheet are preserved by saving the workbook in the same format.
This approach will help you efficiently read, modify, and save your Excel data while maintaining the original formatting. If you have any further questions or need additional examples, feel free to ask!
Is there any way that I extract data in a cells range as a text (but in cells layout such as in CSV format), send data to the API, and then write the updated data to the cells range in the sheet again?
Sure. You may export the data in your range to csv, and then send the csv content to your api, and then import the updated csv content into specified range again. Code example of exporting and importing:
TxtSaveOptions sopts = new TxtSaveOptions();
sopts.ExportArea = CellArea.CreateCellArea("A3", "C5"); //assume the range is A3:C5
sopts.Separator = ',';
wb.Save("res.csv", sopts); //or you may save to memory stream here
TxtLoadOptions lopts = new TxtLoadOptions();
lopts.Separator = ',';
cells.ImportCSV("res.csv", lopts, 2, 0); //importing the data to range starts from A3
Thanks Johnson for your prompt response, which is really helpful.
One more question, instead of saving cell’s data in .csv file and then reading .csv into a String object, can I export cell’s data directly from Excel sheet to a String object in CSV format?
Sets consent for sending user data to Google for online advertising purposes.
Sets consent for personalized advertising.
Cookie Notice
To provide you with the best experience, we use cookies for personalization, analytics, and ads. By using our site, you agree to our cookie policy.
More info
Enables storage, such as cookies, related to analytics.
Enables storage, such as cookies, related to advertising.
Sets consent for sending user data to Google for online advertising purposes.
Sets consent for personalized advertising.
Cookie Notice
To provide you with the best experience, we use cookies for personalization, analytics, and ads. By using our site, you agree to our cookie policy.
More info
Enables storage, such as cookies, related to analytics.
Enables storage, such as cookies, related to advertising.
Sets consent for sending user data to Google for online advertising purposes.