I got a problem with setting style.ForeGroundColor to Color.LightGray. Whenever I set to LightGray the output show only black color. But when I reset to Gray It's show correctly. My code is like that.
style.ForegroundColor = Color.LightGray
style.Pattern = BackgroundType.Solid
How can I do to use LightGray Color?
Thanks,
John
Hi John,
Thanks for considering Aspose.
Well the color LightGray, which you are implementing, is not in the color palette. so you have to add it into the color palette for your need.
Please try the sample code:
Workbook wb= new Workbook();
wb.Open("d:\\colortest.xls");
Cells cells = wb.Worksheets[0].Cells;
wb.ChangePalette(Color.LightGray,55);
cells[0,0].PutValue("Testing");
cells[0, 0].Style.ForegroundColor = Color.LightGray;
cells[0, 0].Style.Pattern = BackgroundType.Solid;
wb.Save(@"d:\colortest1.xls");
Regards
Amjad Sahi
Aspose Nanjing Team
Thanks Amjad Sahi,
Your sample code work. But I want to set the color in the set of Styles. When I try to set the color in the set of styles. It's render the last setting color of style. How can I slove that problem?
Thanks,
John
Hi John,
Are you talking about using different Style objects? Well, you may use them and add different colors into the color palette at different indexes, but the Workbook.ChangePalette() method's color int argument should not be greater than 55. You may try the following sample code for your reference:
Workbook wb= new Workbook();
wb.Open("d:\\colortest.xls");
wb.ChangePalette(Color.LightGray,54);
Cells cells = wb.Worksheets[0].Cells;
Cell cell = cells[0,0];
cell.PutValue("Testing");
Styles styles = wb.Styles;
int index = styles.Add();
Style style = styles[index];
style.ForegroundColor = Color.LightGray;
style.Pattern = BackgroundType.Solid;
style.Font.Color = Color.Red;
style.HorizontalAlignment = TextAlignmentType.Center;
cell.Style = style;
wb.ChangePalette(Color.DarkOliveGreen,55);
cell = cells[0,2];
cell.PutValue("Hello");
index = styles.Add();
Style style1 = styles[index];
style1.ForegroundColor = Color.DarkOliveGreen;
style1.Pattern = BackgroundType.Solid;
style1.Font.Color = Color.Blue;
style1.HorizontalAlignment = TextAlignmentType.Center;
cell.Style = style1;
wb.Save(@"d:\colortest1.xls");
If you have further queries, feel free to contact us.
Regards
Amjad Sahi
Aspose Nanjing Team
Hi Amjad Sahi,
Your code seem like, I need to change the palette every time when I change the color for each cell. I wanna to do it following way, But It's didn't work properly yet. here my sample code.
Workbook workbook = new workbook;
Aspose.Cells.Style style;
int styleIndex = workbook.Styles.Add();
style = workbook.Styles[styleIndex];
style.Font.Size = 10;
style.Font.IsBold = true;
style.Font.Color = Color.Black;
workbook.ChangePalette(Color.LightGray, 54);
style.ForegroundColor = Color.LightGray;
style.Pattern = BackgroundType.Solid;
style.Name = "GrayBlackLeft";
styleIndex = workbook.Styles.Add();
style = workbook.Styles[styleIndex];
style.Font.Size = 10;
style.Font.IsBold = true;
style.Font.Color = Color.Blue;
workbook.ChangePalette(Color.LightYellow, 36);
style.ForegroundColor = Color.Yellow;
style.Pattern = BackgroundType.Solid;
style.HorizontalAlignment = TextAlignmentType.Right;
style.Name = "YellowBlueLeft";
workbook.Worksheets["Staff Ratings"].Cells[0,1].Style = workbook.Styles["GrayBlackLeft"];
workbook.Worksheets["Staff Ratings"].Cells[0,2].Style = workbook.Styles["YellowBlueLeft"];
That's code didn't render color properly yet. Any idea?
Thanks,
John
Hi John,
Well the color palette has 56 colors which are indexed (each color is placed on different indexes i.e., 0-55) when you need a color that is not in the palette, you have to embed it in the palette changing and replacing with the existing color using Workbook.ChangePalette() method.
I use your following code and results are perfect (see the attachment xls file)
Workbook workbook = new Workbook();
Aspose.Cells.Style style;
int styleIndex = workbook.Styles.Add();
style = workbook.Styles[styleIndex];
style.Font.Size = 10;
style.Font.IsBold = true;
style.Font.Color = Color.Black;
workbook.ChangePalette(Color.LightGray, 54);
style.ForegroundColor = Color.LightGray;
style.Pattern = BackgroundType.Solid;
style.Name = "GrayBlackLeft";
styleIndex = workbook.Styles.Add();
style = workbook.Styles[styleIndex];
style.Font.Size = 10;
style.Font.IsBold = true;
style.Font.Color = Color.Blue;
workbook.ChangePalette(Color.LightYellow, 36);
style.ForegroundColor = Color.LightYellow;
style.Pattern = BackgroundType.Solid;
style.HorizontalAlignment = TextAlignmentType.Right;
style.Name = "YellowBlueLeft";
workbook.Worksheets["Sheet1"].Cells[0,1].Style = workbook.Styles["GrayBlackLeft"];
workbook.Worksheets["Sheet1"].Cells[0,2].Style = workbook.Styles["YellowBlueLeft"];
workbook.Save("d:\\testpalette.xls");
Which version of Aspose.Cells you are using? I am using the latest 4.1.
Regards
Amjad Sahi
Aspose Nanjing Team
Hi Amjad Sahi,
ForeGroundColor is OK Now. Thanks for your support.
Now I need to protect specific cell, not the whole sheet.
Can you please tell me how can it be done? I studied the demos and it's only show
how to protect the whole sheet. I wanna do like setting the A1 is read only and A2 can be input by the
user. Looking for your reply.
Thanks,
John
Hi John,
To Protect a single cell "A1" in a worksheet, please refer the following code:
Workbook wb = new Workbook();
Worksheet sheet = wb.Worksheets[0];
Style style;
for(int i = 0; i <= 255; i ++)
{
style = sheet.Cells.Columns[(byte)i].Style;
style.IsLocked = false;
}
style = sheet.Cells["A1"].Style;
style.IsLocked = true;
sheet.Cells["A1"].Style = style;
Aspose.Cells.Protection protection = sheet.Protection;
wb.Save("d:\\lockedcell.xls", FileFormatType.ExcelXP);
To Protect a whole row "First row" in a worksheet, please refer the following code:
Workbook wb = new Workbook();
Worksheet sheet = wb.Worksheets[0];
Style style;
StyleFlag flag;
for(int i = 0; i <= 255; i ++)
{
style = sheet.Cells.Columns[(byte)i].Style;
style.IsLocked = false;
flag = new StyleFlag();
flag.Locked = true;
sheet.Cells.Columns[(byte)i].ApplyStyle(style, flag);
}
style = sheet.Cells.Rows[0].Style;
style.IsLocked = true;
flag = new StyleFlag();
flag.Locked = true;
sheet.Cells.Rows[0].ApplyStyle(style, flag);
Aspose.Cells.Protection protection = sheet.Protection;
wb.Save("d:\\lockedrow.xls", FileFormatType.ExcelXP);
Regards
Amjad Sahi
Aspose Nanjing Team
Hi Amjad Sahi,
Now I can sloved all the problems. Thanks for your support .
But I think I still need to do some investigation and evaluation.
After that I can confrim my manager to purchase it.
Thanks a lot,
John
Hi John,
Thanks for evaluating our product.
Customers do evaluate our product and help us to enhance the product.
Feel free to contact us any time, We feel happy supporting customers.
Regards
Amjad Sahi
Aspose Nanjing Team
Hi Amjad Sahi,
I create some cells like dropdownlist. For the list value I've created the Cells.Range and put the
values into the range. But it doen't actually meet the requirement yet. Coz I need to fill the dropdown
values from database. The database fields like that.
Id Name
-- -----
1 Cindy
2 Gina
3 Henry
In the dropdownlist I wanna show Cindy, Gina, Henry. But when the program read from the saved
excel file, it's only need to read "Id". How can I slove that problem? Waiting for your response.
Thanks for your support,
John
Hi John,
Aspose.Cells does not support for creating dropdownlists in excel files yet. I am not very clear about your requirement. Could you elaborate your need and post us your sample code with template file so that we may sort out the things Thank you.
Regards
Amjad Sahi
Aspose Nanjing Team.
Hi Amjad Sahi,
Here is my requirement detail.
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];
worksheet.Name = "Staff Ratings";
//add validation here.
Range range = worksheet.Cells.CreatRange("A1", "A4");
range.Name = "StaffRating";
// These four names are from database. But I also need to store "Staff ID"
range[0,1].PutValue("Cindy");
range[0,2].PutValue("Gina");
range[0,3].PutValue("Henry");
range[0,4].PutValue("Clara");
Validations validations = worksheet.Validations;
Validation validation = validations[validations.Add()];
validation.Type = Aspose.Cells.ValidationType.List;
validation.Operator = OperatorType.None;
validation.InCellDropDown = true;
validation.Formula1 = "=StaffRating";
CellArea area;
area.StartRow = 0;
area.EndRow = 3;
area.StartColumn = 0;
area.EndColumn = 0;
validation.AreaList.Add(area);
In that way I can create a cell like combo box. The user can be select "Staff" from that combo box
and save the excel file. And my program will be provide "Import" Button. In that way user can be
import excel file to database. While importing excel file, my program need to read excel file and
need to know which "Staff" user selected. In that case I need to know "Staff ID" not "Name".
That's why I need to know that I'm able to store "Staff ID" including with "Name" In
Worksheet.Cells.Range. Looking for your reply.
Thanks,
John
Hi John,
When you use list validation, the source range should be a single column or a single row (You can implement in MS Excel and see the limitation). So, you cannot provide list source comprising of two columns or multiple rows as your data is consisted of.
I think you have to manually do your task and implement it in your code.
Regards
Amjad Sahi
Aspose Nanjing Team