Error: Changing Colors

[cleitonm]

Hi.

I'm using Aspose.Cells for .net. (last release)

I send one .xls file to protect and after this process, the background color of some cells changed.

Is there a fix to this bug or a way to resolve this problem?

Thanks

[Warren]

Hi,

Pease try this fix.

If the error still occures, please post your xls file and sample codes.

[cleitonm]

Hi,

I tried to execute the protection with your release, but the error still occurs. I'm sending the file attached, please save the file with .xls and execute it in Aspose Cells and you'll see the change in the first cell.

Thanks

[Warren]

Hi,

We checked your file with the previous attached fix. It works fine. See following codes:

Workbook workbook = new Workbook();
workbook.Open(@"F:\FileTemp\testColors.xls");
workbook.Worksheets[0].Protect(ProtectionType.Contents);
workbook.Save(@"F:\FileTemp\dest.xls");

Please check your reference path of Aspose.Cells.

[cleitonm]

Hi,

I tried to execute your sample and it works, but I need to protect only formulas and i'm using the code:

sheets = workbook.Worksheets;

for(int i=0;i<sheets.Count;i++)
{
sheet = sheets[i];
cells = sheet.Cells;
for(int j=0;j<cells.Count;j++)
{
cell = cells[j];
if(cell.IsFormula)
{
cell.Style.IsLocked = true;
}
else if(cell.Value != null)
{
cell.Style.IsLocked = false;
}
}
}
workbook.Save(filedest);

and in this case it not works. Could you possibly test this code in your application?

Thanks

[amjad.sahi]

Hi,

Thanks for considering Aspose.

I think you can try to change your code to lock a few cells in a worksheet only i.e. only cells having formula. Following code locks a few cells in the first worksheet only, kindly refer to it.

// Create a new workbook.
Workbook wb = new Workbook();

wb.Open("d:\\test\\mybook.xls");
// Create a worksheet object and obtain the first sheet.
Worksheet sheet = wb.Worksheets[0];
// Define the style object.
Style style;
// First you need to unlock all the cells in a worksheet, all the cells are locked by default.

//Loop through all the columns in the worksheet and unlock them.
for(int i = 0; i <= 255; i ++)
{

style = sheet.Cells.Columns[(byte)i].Style;
style.IsLocked = false;

}

Cells cells = sheet.Cells;
for(int j=0;j<cells.Count;j++)
{
cell = cells[j];
if(cell.IsFormula)
{
cell.Style.IsLocked = true;
}

}

// Finally, Protect the sheet now.
Aspose.Cells.Protection protection = sheet.Protection;

// Save the excel file.
wb.Save("d:\\test\\lockedcells.xls", FileFormatType.ExcelXP);

For reference, please check:

Protecting Worksheets

Thank you.

[cleitonm]

Hi,

I'm trying to execute the code following your sample and protect only cells. The problem persists, you can get my sheet and execute with your code, you'll see that the first cell is locked (but i need unlock because it isn't a formula). If I change the code including (cell.Style.IsLocked = false;) the problem (change the color) occurs again.

I can't unlock all the cells because I need to lock cells with formulas and white spaces (null).

Please, try to make this test including a password in the worksheet:

sheet.Protect(ProtectionType.All, password, "");

Thanks a lot.

[amjad.sahi]

Hi,

Thanks for considering Aspose.

Following is my code which works fine. I use a template file and lock only those cells which have formulas or the null cells. Attached are input and output files.

// Create a new workbook.
Workbook wb = new Workbook();
wb.Open("d:\\test\\bk_withformulas.xls");
// Create a worksheet object and obtain the first sheet.
Worksheet sheet = wb.Worksheets[0];
Cells cells = sheet.Cells;
Cell cell;

for(int j=0;j<cells.Count;j++)
{
cell = cells[j];
if((cell.IsFormula==true)||(cell.Type ==CellValueType.IsNull))
{
cell.Style.IsLocked = true;
}
else if(cell.Value != null)
{
cell.Style.IsLocked = false;
}
}

sheet.Protect(ProtectionType.All,"007","");
// Save the excel file.
wb.Save("d:\\test\\locking_cells.xls",FileFormatType.ExcelXP);

Thank you.

[cleitonm]

Hi,

Please, could you possibly test with testColors.txt (rename to .xls) attached in previous message and send me the result?

Thanks

[amjad.sahi]

Hi,

Could you post your template file (again) with sample code here. We will check it soon.

Thank you.

[Warren]

Hi,

Please try this fix.

[cleitonm]

Hi,

Now this problem was fixed. Thanks a lot.

I have another problem: After execute sheet.Unprotect(password) in attached file (x.xls) I'm receiving the message "Too many different cell formats" error and some values like date and text was corrupted. This file doesn't protected by sheet.Protect(password) because we have a process that try to unprotect sheets without know your status protection.

Please, could you can execute this test in your environment and analysis the error?

If you prefer, i can open another topic about this error in this forum.

Thanks.

[Warren]

Hi,

After checking your file with the following codes, I could not get any error.

Workbook workbook = new Workbook();
workbook.Open(@"F:\FileTemp\x.xls");
for (int i = 0; i < workbook.Worksheets.Count; i++)
{
workbook.Worksheets[i].Unprotect("xxx");
}

workbook.Save(@"F:\FileTemp\dest.xls");

So please post your codes or simple project. We will check it soon.

And you can user Worksheet.IsProtected to get the protection status.