Create Excel document with EPPlus and assign user with Information Rights Management - c#

Is there a way, using EPPlus, to create an Excel document and protect it's content so that only specific user(s) can access the information inside it. This is possible when manually creating an Excel document and using the IRM capabilities within Excel, but I've not yet found a way of doing this at the point the Excel document is created using EPPlus.
I have tried embedding a VBA sub routine using Workbook_Open(), but many companies set policies to prevent running Macros on Office documents when they open because of the threat of viruses/malware.
IRM primarily controls WHO can access the content of the document, which is different to the Worksheet Protection capabilities in EPPlus that restrict WHAT someone can do.

Related

Open xml sdk with Excel Add In

I want to use open xml sdk in excel add-In.
But
SpreadsheetDocument.Open()
method fails as the subject document is currently open.
To describe more.
I open any excel document, and have my custom excel add-in in it.from the add-in I want to gather some data from the spreadsheet using open xml sdk.
Is there any way around this, to use open xml sdk in excel add-ins and access current workbook using the same?
You can gather data from Excel using the Excel Object Model also, and is far more convenient. But as you are using Open XML SDK and calling it to open the same document again from within Excel, you should Open it as ReadOnly.
SpreadsheetDocument.Open(path, isEditable)
Set the current spreadsheet's full path in path and set isEditble to False
Get more info at MSDN

How to : Visual Studio 2010 create Sharepoint List and define data source?

I have this ugly requirement of creating a SharePoint list (could be a custom or an calendar) which displays the companies upcoming events, the problem is that x department updates an excel sheet weekly which would be the data source to this SharePoint List.
And requirement also include allowing users to "Connect to outlook" option enabled.
From my very limited experience with SharePoint I believe that I should create the SharePoint list in Visual Studio(2010) which I am not sure about how to do and later use odbc to connect to the excel sheet to use as a data source.
I am not sure if its possible to define a data source to a list.
Please point me to the right direction and help me with tutorials.
Thanks a lot in advance.
Regards,
Vishal
You should define the list in Visual Studio project and some functionality to import data into the list from Excel file.
Defining list in Visual Studio is quite simple and well documented action.
There can be several ways to import content of Excel file into the list.
I did this as general functionality by defining SharePoint custom action (Ribbon button) and application page. In the application page is upload control for uloading excel file. After upload I use ODBC to access content of Excel. Beware that using ODBC needs Excel file to be saved to disk on server side as ODBC requires disk file.
Other option is to create document library and associated Event receiver that will handle ItemAdded event and do the same as in previous point.
To access content of Excel file you may use OpenXML SDK but it is limited to XLSX format (Excel 2007+ format). Advantage is that you do not need to save the file to disk.

How do I create Excel worksheets at runtime that cache a data set in the data island

I have created an excel add-in that allows data to be downloaded into any excel workbook. The downloaded data is always placed in a new worksheet, but the workbook can also have worksheets that are not managed by the add-in.
Now I would like to store settings for each managed sheet (so I can refresh the sheet at a later date from the same data source etc.), preferably using the [cached] attribute to store to the VSTO datastore for each sheet. The problem is I don't seem to have access to a DataStore at the add-in level (I can create a Visual Studio project specifically for a workbook and create one there, but not in the project for the addin).
How can I dynamically create a datastore from the add-in / application level? Any hints / advice / links to tutorials are welcome, as I am finding it hard to find any information on this subject.
Short answer: I can't use the data island from the application level.

Write ExcelFile

I have a scenario where I need to store all data of current workbook including template to a new excel workbook/ worksheet and store that new file to database in BLOB. I am using C#. Any demo available ?
Consider using the Open XML SDK to read and write Excel documents. It can read and write Office 2007 and 2010 compatible documents without the need for Excel to be installed on the machine/server. Another approach would be to automate Excel (instantiate an Excel.Application object, and then programmatically drive Excel to do the work).
I usually recommend option 1.
Have a look at that question.
The accepted answer recommends using an open source library called NPOI for manipulating spreadsheets.

Get Worksheets in Excel Workbook without opening file

I'm building an ASP.NET MVC 2 intranet application in which users will upload data files. If a file is an excel spreadsheet, the user should be given the option to select which worksheet they would like to import. Some of these files can be very large, so will be imported asynchronously. But it would be great to grab a list of the worksheets without saving / opening the file.
Is this possible? Or can anyone suggest a decent workaround (without resorting to accepting worksheet names in a text box)?
You can access that information by either using the System.IO.Packaging library, which would allow you to access the XML that makes up the Excel document and thus allow you to see what worksheets make up the Excel document.
The other option is to use the Open XML SDK 2.0 and access the Worksheets via strongly typed objects, ie a WorkSheetPart. Neither will be fast or clean as it will be a good amount of work, but are two other options in addition to Alex's suggestion.
The only workaround I can think of, although I hesitate to call it decent, involves using Silverlight and relies on the Excel files being in the 2007 format (Open XML or whatever it's called). Then you can open the file locally with Silverlight and (hackishly) get a list of the worksheets. You can also use Silverlight to do the actual upload. Or you could extract the relevant worksheets and only upload those.
If you are simply importing the data to a Database you can use the OLE JET 4.0 Driver and access the schema using System.Data.SqlConnection.GetSchema (i think that is the function name).

Resources