0

I am writing a C# app where I need to paste/link tables/ranges from existing Excel documents.

Functionality that I am looking for is this:

  • user can select a range of cells in an open Excel doc and do a Copy
  • user switches to my C# app and does a past-link ... my app shows the table from Excel.
  • user can edit the source Excel doc - this does not automatically get reflected in the C# app. But I want to provide a Refresh button that when clicked will update the C# app based on the latest data from the linked Excel sheet.

I have figured out how to do a basic copy/paste. I cannot figure out how to do this paste-link. Please note I do not want to ask user in my C# app for any cell ranges..I simply want to do paste-link of what is already in the clipboard...

Any ideas if this can be done...it is all Microsoft so I would be surprised if it can't be.. but I am a C# novice.

Thanks for all input.

Franek Kuciapa
  • 141
  • 1
  • 8

1 Answers1

0

I figured it out. Here are the steps.

  1. User copies a range in Excel sheet. It goes to Clipboard in a number of formats but CSV and ObjectLink formats are of particular interest.
  2. In C# app, trigger a Paste-Link function (this is any button).
    • Retrieve data from Clipboard using ObjectLink format. This comes out as text which contains:
      • Excel version identifier
      • Path to the excel file
      • The sheet name and the selected range in R1C1 notation
    • Save the ObjectLink data in your C# app, we will use it later as part of refresh
    • Retrieve the data from clipboard using CSV format. Parse it out and present in C# app. I converted it to HTML since this is what I am building
  3. Modify the original source excel file - change something in the cells that were part of the original range - save the file.
  4. Go back to C# app, trigger Refresh functionality (this is any button). IN your code do the following:
    • Using ObjectLink data saved in step 2, open the Excel sheet in the background using Excel Interop API tools. Select the sheet and range. Copy the range programmatically to clipboard.
    • invoke the same copy from clipboard as used in the last step of 2. Basically get the updated Excel data in CSV format from clipboard and replace the original representation you built during step 1.

This works like a charm although the COM part of opening an excel doc from C# is a bit slow I have to admit.

I have not found any references to this procedure on the net...works for me like a charm.

Cheers.

Franek Kuciapa
  • 141
  • 1
  • 8