none
i want to set a cell(A1) backgroundcolor=red ,how do it? RRS feed

  • Question

  • using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(Server.MapPath(@"sampledoc/dhy1.xlsx"), true))  
                {   
                    // Get the SharedStringTablePart. If it does not exist, create a new one.  
                    SharedStringTablePart shareStringPart;  
                    if (spreadSheet.WorkbookPart.GetPartsOfType<SharedStringTablePart>().Count() > 0)  
                    {  
                        shareStringPart = spreadSheet.WorkbookPart.GetPartsOfType<SharedStringTablePart>().First();  
                    }  
                    else  
                    {  
                        shareStringPart = spreadSheet.WorkbookPart.AddNewPart<SharedStringTablePart>();  
                    }  
                    // Insert a new worksheet.  
                    //WorksheetPart worksheetPart = InsertWorksheet(spreadSheet.WorkbookPart);  
                    WorksheetPart worksheetPart = spreadSheet.WorkbookPart.GetPartsOfType<WorksheetPart>().First();  
                    CellStyle cs = new CellStyle(new BackgroundColor() { Auto=true});  
                    // Insert the text into the SharedStringTablePart.  
                    for (int i = 0; i < CreateMockDataSet().Tables[0].Rows.Count; i++)  
                    {  
                        for (int j = 0; j < CreateMockDataSet().Tables[0].Columns.Count; j++)  
                        {  
                            int index = InsertSharedStringItem(CreateMockDataSet().Tables[0].Rows[i][j].ToString(), shareStringPart);  
                              
                            // Insert cell A1 into the new worksheet.  
                            Cell cell;//I want to set cell(A1) with red bgcolor ,how i do?  
                            switch (j)  
                            {  
                                case 0:  
                                    cell = InsertCellInWorksheet("A", (uint)i+1, worksheetPart);  
                                    cell.CellValue = new CellValue(index.ToString());  
                                    cell.DataType = new EnumValue<CellValues>(CellValues.SharedString);  
                                    break;  
                                case 1:  
                                    cell = InsertCellInWorksheet("B", (uint)i+1, worksheetPart);  
                                    cell.CellValue = new CellValue(index.ToString());  
                                    cell.DataType = new EnumValue<CellValues>(CellValues.SharedString);  
                                    break;  
                                case 2:  
                                    cell = InsertCellInWorksheet("C", (uint)i+1, worksheetPart);  
                                    cell.CellValue = new CellValue(index.ToString());  
                                    cell.DataType = new EnumValue<CellValues>(CellValues.SharedString);  
                                    break;  
                            }  
                        }  
                    }  
                    worksheetPart.Worksheet.Save();  
                }  
     
    Wednesday, January 14, 2009 8:28 AM

Answers

  • The easiest way to accomplish your task is to create a style that defines the color for a cell and then reference the style within the cell. For example the cell will have a style reference to the index of styles within Style.xml. Within Styles.xml you will need to look at the specific child of cellXfs that matches the index of the style. Once you go there you will see a fillId reference. Now you ned to go to the children of fills and find the index that was references in xf element (child of cellXfs). At that point the fill element should have details on the fill color.

    By the way an easy way to see what I am talking about is to use the Document Reflector tool that comes with the SDK.


    Zeyad Rajabi (MS)
    • Marked as answer by 杜宏宇 Friday, February 20, 2009 1:00 AM
    Friday, January 30, 2009 8:31 PM