I know how to create a new excel and export data into it. But how can I export data to an already existing excel which have some formats and data validation?
Advertisement
Answer
Take a look to NuGet library ClosedXML .
I used it in the past and it’s really straightforward.
Install the package in your solution: from VS: Tools > NuGet Package Manager > Manage NuGet Packages for Solution. Alteratively, run this command from Package Manager Console: Install-Package ClosedXML -Version 0.96.0
In order to open and edit an existing file, use the following code:
string filePath = @"C:pathtofile.xlsx";
IXLWorkbook workbook = new XLWorkbook(filePath);
IXLWorksheet worksheet = workbook.Worksheet(1); // Indexing from 1
worksheet.Range("A1").Value = 10;
worksheet.Range("A2").Value = 15;
worksheet.Range("A3").Value = 20;
worksheet.Range("A4").Value = 30;
workbook.Save(); // Remember to call Save method to save changes.
Please note: for some (probably good?) reason, sheets indexing starts from 1 instead of 0.
Remember to add the using statement: using ClosedXML.Excel;
.
That’s all! Feel free to comment / vote if you need clarifications.