How to Programmatically Edit Excel Sheets

How to programmatically edit Excel sheets?

xlutils has a copy module that may be interseting for you

Updating an excel document programmatically

I use and am happy with EPPlus http://epplus.codeplex.com/ for parsing, editing and creating xlsx files.

This question had helped me out: Create Excel (.XLS and .XLSX) file from C#

best way to programmatically modify excel spreadsheets

I ended up using Aspose.Cells as I mentioned in my original post, since it seemed like the easiest path. I'm very happy with the way it turned out, and their support is very good. I had to create a wrapper around it in C# that exported a COM interface to my C++ application.

Programmatically modifying a Sharepoint Online Excel file

You can download the file, modify it, upload the file and replace it

Here is how to connect to a sharepoint site and upload a file

Use the Microsoft.SharePoint.Client namespace

using SP = Microsoft.SharePoint.Client;
...
using (var context = new SP.ClientContext(new Uri(<YOURSITEURL>))) {
    var web = context.Web;
    context.Credentials = new NetworkCredential(<NETWORK_USERNAME>, <NETWORK_PASS>, <DOMAIN_NAME>);
    context.Load(web);
    try
    {
        context.ExecuteQuery();
    } catch (Exception ex) {
    }
    var file = web.GetFileByServerRelativeUrl(new Uri(<FILE_URL>).AbsolutePath);
    context.Load(file);
    try
    {
        context.ExecuteQuery();
        file.SaveBinary(new SP.FileSaveBinaryInformation() { Content = Encoding.UTF8.GetBytes(<NEW_FILE>) });
        try
        {
            context.ExecuteQuery();
        }
        catch (Exception ex)
        {
        }
    }
}

Programmatically Edit a Protected microsoft Excel Sheet

If you know the password,you can unprotect the sheet, chnage the cell and protect it again. You can evem record a macro like this. If you need the same file extension you can run it from a different workbook,alternatively. Again, this can be recorded.

Is there a way to programatically edit Office 365 Excel documents?

I'm aware of 4 options to programmatically modify Excel Online via JavaScript or REST:

1. Office Add-ins platform: https://dev.office.com/docs/add-ins/overview/office-add-ins
2. Excel Services JavaScript API - EWA Excel Web Access Namespace (not updated in couple years): https://msdn.microsoft.com/en-us/library/hh315812(v=office.14).aspx
3. Excel Services REST API (via SharePoint Online): https://msdn.microsoft.com/en-us/library/ee556842(v=office.14).aspx
4. Microsoft Graph REST API (Excel Objects are currently in Beta): https://graph.microsoft.io/en-us/docs/api-reference/beta/resources/excel

Editing Excel with ExcelJS

Thanks for the reply I got it working and here is my code.

var Excel = require('exceljs');
var workbook = new Excel.Workbook();
workbook.xlsx.readFile('file.xlsx')//Change file name here or give file path
.then(function() {
    var worksheet = workbook.getWorksheet('sheet');
    var i=1;
    worksheet.eachRow({ includeEmpty: false }, function(row, rowNumber) {
      r=worksheet.getRow(i).values;
      r1=r[2];// Indexing a column
      console.log(r1);
      i++;
    }); 
    worksheet.getCell('B3').value = "abc";//Change the cell number here
return workbook.xlsx.writeFile('file.xlsx')//Change file name here or give     file path
   });

I was missing this return statement that saves my edited file.

Modify excel cell

The problem in both cases is that the modified workbook is not saved back to the stream:

MemoryStream ms = new MemoryStream();
using (FileStream fs = File.OpenRead(@"Path\Test.xlsx"))
using (ExcelPackage excelPackage = new ExcelPackage(fs))
{
    ExcelWorkbook excelWorkBook = excelPackage.Workbook;
    ExcelWorksheet excelWorksheet = excelWorkBook.Worksheets.First();
    excelWorksheet.Cells[1, 1].Value = "Test";
    excelWorksheet.Cells[3, 2].Value = "Test2";
    excelWorksheet.Cells[3, 3].Value = "Test3";

    excelPackage.SaveAs(ms); // This is the important part.
}

ms.Position = 0;
return new FileStreamResult(ms, "application/xlsx")
{
    FileDownloadName = "Tester.xlsx"
};

Is posible edit opened excel file

Whole magic is:

            try
            {
                xlApp = (Excel.Application)System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application");
            }
            catch (Exception ex)
            {
                if (ex.ToString().Contains("0x800401E3 (MK_E_UNAVAILABLE)"))
                {
                    xlApp = new Excel.Application();
                }
                else
                {
                    throw;
                }
            }

this will get opened excel application. Then all changes are made in this instance of Excel. So everything is edited, is seen in opened excel application.

thanks guys

---

Related Topics