Disabling Copying From an Excel Sheet Using Aspose

Aspose.Cells Product Family
1

Hi Team ,
My requirement would be to disable the Copy Option in an Excel sheet Using Aspose Cells for .net . I have tried the following Options using Code

Worksheet sheet = wBook.Worksheets[wBook.Worksheets.ActiveSheetIndex];

               Style style;
         StyleFlag flag = new StyleFlag();

                            for (int i = 0; i < 255; i++)
                            {
                                style = sheet.Cells.Columns[i].Style;
                                style.IsLocked=true;

                              flag.Locked=true;
                                // Get style of the column
                                sheet.Cells.Columns[(byte)i].ApplyStyle(style,flag);
                                
                            }
                            Aspose.Cells.Protection protection = sheet.Protection;
                           sheet.Protect(ProtectionType.All);
                           wBook.Save(@"C:\Test.xls");

The above Piece of Code Only disables editing Option But I can Copy the contents from it ,Please let me Know how can I disable Copying using Aspose Methods?

2

Hi,


How could you do this in MS Excel manually, I think this is not possible (Disable Copy/Paste options in MS Excel) even protecting worksheets. If you are able to perform your task in Ms Excel manually, save the file and post us here with all the details, we will check on how to do it via Aspose.Cells APIs. Aspose.Cells follows MS Excel standards and specifications. Also disabling copy/paste option can be distinguished as MS Excel application level feature and there is nothing to do with the file formats level attributes. Aspose.Cells is a spreadsheet management library used to create or manipulate Ms Excel file formats (XLS, XLSX, XLSM, CSV, Tab Delimited, ODS, SpreadsheetML etc.).

By the way, you may encrypt or password protect your Excel file if it helps, see the document for your reference:


Thank you

3

Hi Amjad,

Thanks for Reply yes we were not able to disable Copy Option in Excel Manually ,However our intention was not to Copy Contents from Excel Sheet Generated By Aspose,

The following Piece of Code was used



Worksheet sheet = wBook.Worksheets[0];

sheet.Protect(ProtectionType.All);

sheet.Protection.AllowSelectingLockedCell = false;

sheet.Protection.AllowSelectingUnlockedCell = false;



sheet.Protection.Password = “Ns@CRTY78”;





wBook.Save(saveStream, SaveFormat.Xlsx);



output =saveStream.ToArray();

excelFileName = ExcelFileName;

excelFileName = ExcelFileName.Replace(".xls",("." + extension));





Now the Problem is that Alignment of Charts (Getting Expanded) and Table values aligment is Changed After Applying Protection Properties ,Can you review the attachment and Suggest necessary Changes .The Aspose version of Dll used is 5.1.2.0

4
Hi,

Dharshan:
....Now the Problem is that Alignment of Charts (Getting Expanded) and Table values aligment is Changed After Applying Protection Properties ,Can you review the attachment and Suggest necessary Changes .The Aspose version of Dll used is 5.1.2.0

Well, since you are using some older version of the product (v5.1.2.0) and we are not sure why you are getting the alignment issue when generating XLSX file format. The issue does not look like related to Protecting worksheets, I think you may simply open and re-save the file (without protecting worksheet) to get similar output (with alignment issue). I am afraid, we cannot evaluate your issue in older version that you are using, it might be an issue in your older version. We can only recommend you to kindly try using our latest version/fix: Aspose.Cells for .NET v8.6.3, you may get it here:
(Note: you might not use our latest version with your existing license, so you would need to upgrade to latest version. You may comment the licensing code to just check (in a separate project) if latest version fixes your issue.)

FYI, I am afraid, we cannot evaluate or fix any issues (if found) in older versions. The fixes are only based on latest APIs set of the component, so we would recommend you to kindly try our latest version of the product in which we included several enhancements and other fixes and it should work fine. And, if we found any issue, we can provide you the fix based on latest versions.

If you still find the issue with latest version v8.6.3, kindly provide a sample console application using v8.6.3 (with input and output files) here to reproduce the issue on our end, we will check it soon.

Thank you.

5

Thanks for your Suggestion ,We tried with Latest Version of Dll 8.6.3.0 FROM folder structure
Aspose.Cells_8.6.3\net3.5

We are receiving the Following error while Compiling the dll,Kindy help on this issue

SGEN : error : Could not load file or assembly ‘file:///C:\lib\Binaries\Aspose.Cells.DLL’ or one of its dependencies. Operation is not supported. (Exception from HRESULT: 0x80131515)
License Version is 3.0 and Subscription expiry is

20160901

6

Hi,


I think it looks like a compatibility issue with your target .NET framework (in your project) that you are using. Please make sure that you are loading/referencing correct product binaries. Please note Aspose.Cells.Dll assembly from net2.0 folder can be used for 2.0, 3.0, 3.5 and 4.0 etc. .NET frameworks (it is backward compatible to work with any regular .NET framework versions >= 2.0), so you may use .NET 2.0 assembly of Aspose.Cells with your regular .NET frameworks in your project. Also, if your application’s target platform is set as .NET Framework 4.0 or greater, you may also set referencing to Aspose.Cells.dll from net4.0 folder present @ your installation folder.

Hope, this helps a bit.

Thank you.
7

Thanks Amjad ,Now Excel Aligment is fine and required Functionality is also achieved using Latest Aspose

8

Hi,


Good to know that your issue is sorted out by the latest version/fix. Feel free to contact us if you have further queries or issue, we will be happy to assist you soon.

Thank you.
9

Hi Team,



I have attached a report from the protected sheet where it is written as Market Commentary ,Sector Commentary if you click on it Width of region would expand and show the exact Lines in it ,But after Protecting whole sheet expansion of the commentary region is not Possible ,Is there any way using Aspose dll to restrict the protection in Specified regions ?

10

Hi,


Thanks for the template file.

Please see the document thoroughly on Protecting Worksheets:

You may write your own code by using Aspose.Cells APIs to protect/unprotect your desired cells/range of cells, row(s) or column(s) in a worksheet accordingly for your requirements.
See the following sample code for your reference:
e.g
Sample code:

…

Workbook wb = new Workbook(stringFilePath);


// Create a worksheet object and obtain the first sheet.

Worksheet sheet = wb.Worksheets[0];


// Define the style object.

Style style;


//Define the styleflag object

StyleFlag styleflag;


// Specify your desired range of cells which you need to unprotect in the worksheet.
///e.g A11:S16

Range range = sheet.Cells.CreateRange(“A11”, “S16”);
style = workbook.CreateStyle();

style.IsLocked = false;
styleflag = new StyleFlag();
styleflag.Locked = true;
range.ApplyStyle(style, styleFlag);


// Finally, Protect the sheet now.

sheet.Protect(ProtectionType.All);


// Save the excel file.

wb.Save(“e:\test2\out1.xlsx”);

using the above code, all the worksheet cells would be protected in the workbook except the range A11:S16.

Hope, this helps a bit.

Thank you.

11

Thanks Amjad we were able to protect only specific rows as suggested in the previous reply.



Could you please assist us in fixing /removing the Overlapping text Boxes as attached in the report .



Here is the piece of Code of used for Creating Text Boxes using Latest version of Aspose









private static void CreateCommentaryTextboxes(Workbook workbook)

{



FindOptions findOptions = new FindOptions();

findOptions.LookAtType = LookAtType.Contains;

findOptions.CaseSensitive = false;

foreach (Worksheet sheet in workbook.Worksheets)

{

Cell cell = sheet.Cells.Find("##Commentary##", null,findOptions);



while (cell != null)

{

string commentaryblock = cell.Value.ToString();

string[] separators = new string[] { “##” };

string[] parts = commentaryblock.Split(separators, StringSplitOptions.None);



int top = cell.Row;

int left = cell.Column;

int height = Convert.ToInt32(parts[2]);

int width = Convert.ToInt32(parts[3]);

string text = parts[4];



int box = sheet.TextBoxes.Add(top, left, height, width);







Style style;

style = cell.GetStyle();

sheet.TextBoxes[box].Font.Color = style.Font.Color; cell.GetStyle();

sheet.TextBoxes[box].Font.Name = style.Font.Name;

sheet.TextBoxes[box].Font.IsBold = style.Font.IsBold;

sheet.TextBoxes[box].Font.IsItalic = style.Font.IsItalic;

sheet.TextBoxes[box].Font.Size = style.Font.Size;

sheet.TextBoxes[box].Placement = PlacementType.FreeFloating;

sheet.TextBoxes[box].IsTextWrapped = true;

sheet.TextBoxes[box].LineFormat.IsVisible = false;

sheet.TextBoxes[box].TextFrame.AutoSize =true;

sheet.TextBoxes[box].Text = text;



sheet.Cells.ClearContents(cell.Row, cell.Column, cell.Row, cell.Column);

cell = sheet.Cells.Find("##Commentary##", cell,findOptions);

}

}

12

Hi Dharshan,


Thank you for writing back.

I have checked your code snippet and the spreadsheet, however, I believe the spreadsheet is the output to the process whereas we require the input spreadsheet to successfully execute your provide code in order to find out the problem cause. Please be kind enough to share the input spreadsheet for investigation.

That said, we also request you to please create a new thread with appropriate title for every distinct problem you encounter with Aspose APIs. This will simply help us track your requests more efficiently.
13
Hi,
Dharshan:
....Could you please assist us in fixing /removing the Overlapping text Boxes as attached in the report .

Here is the piece of Code of used for Creating Text Boxes using Latest version of Aspose

private static void CreateCommentaryTextboxes(Workbook workbook)
{

        FindOptions findOptions = new FindOptions();
        findOptions.LookAtType = LookAtType.Contains;
        findOptions.CaseSensitive = false;
        foreach (Worksheet sheet in workbook.Worksheets)
        {
            Cell cell = sheet.Cells.Find("##Commentary##", null,findOptions);

            while (cell != null)
            {
                string commentaryblock = cell.Value.ToString();
                string[] separators = new string[] { "##" };
                string[] parts = commentaryblock.Split(separators, StringSplitOptions.None);

                int top = cell.Row;
                int left = cell.Column;
                int height = Convert.ToInt32(parts[2]);
                int width = Convert.ToInt32(parts[3]);
                string text = parts[4];

                int box = sheet.TextBoxes.Add(top, left, height, width);

               

                Style style;
                style = cell.GetStyle();
                sheet.TextBoxes[box].Font.Color = style.Font.Color; cell.GetStyle();
                sheet.TextBoxes[box].Font.Name = style.Font.Name;
                sheet.TextBoxes[box].Font.IsBold = style.Font.IsBold;
                sheet.TextBoxes[box].Font.IsItalic = style.Font.IsItalic;
                sheet.TextBoxes[box].Font.Size = style.Font.Size;
                sheet.TextBoxes[box].Placement = PlacementType.FreeFloating;
                sheet.TextBoxes[box].IsTextWrapped = true;
                sheet.TextBoxes[box].LineFormat.IsVisible = false;
                sheet.TextBoxes[box].TextFrame.AutoSize =true;
                sheet.TextBoxes[box].Text = text;
               
                sheet.Cells.ClearContents(cell.Row, cell.Column, cell.Row, cell.Column);
                cell = sheet.Cells.Find("##Commentary##", cell,findOptions);
            }
        }</div></BLOCKQUOTE></div><div><br></div><div>Please see your other thread for your reference:</div><div><a href="https://forum.aspose.com/t/36570">https://forum.aspose.com/t/36570</a></div><div><br></div><div>Thank you.</div>