excel - 如何使用打开的 xml 编辑 Excel 工作表并为 Excel 工作表单元格提供背景颜色？

Question

我需要阅读 excel 表并更改某些特定单元格的颜色。

标签： excelopenxml

Answer 1

    using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(docName, true))
                  {
            WorksheetPart wbPart = spreadSheet.WorkbookPart.WorksheetParts.FirstOrDefault();
                  Row row1 = getRow(1, wbPart);
                           foreach (Cell c2 in row1.Elements<Cell>())
                                    {
                                        if (c2.CellReference.Value == "G1")
                                        {
                                            AddbackgroundFormat(spreadSheet, c2, "56BEFB");
                                        }
                }
              }

//Getting the row using row index
         static Row getRow(uint rowIndex, WorksheetPart worksheetPart)
                {
                    Worksheet worksheet = worksheetPart.Worksheet;
                    SheetData sheetData = worksheet.GetFirstChild<SheetData>();
                    Row row;
                    if (sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).Count() != 0)
                    {
                        row = sheetData.Elements<Row>().Where(r => r.RowIndex == rowIndex).First();
                    }
                    else
                    {
                        row = new Row() { RowIndex = rowIndex };
                        sheetData.Append(row);
                    }
                    return row;
                }

//Adding styles two cells using the color passed
                static void AddbackgroundFormat(SpreadsheetDocument document, Cell c, string colorCode)
                {
                    Fills fs = AddFill(document.WorkbookPart.WorkbookStylesPart.Stylesheet.Fills, colorCode);
                    AddCellFormat(document.WorkbookPart.WorkbookStylesPart.Stylesheet.CellFormats, document.WorkbookPart.WorkbookStylesPart.Stylesheet.Fills);
                    c.StyleIndex = (UInt32)(document.WorkbookPart.WorkbookStylesPart.Stylesheet.CellFormats.Elements<CellFormat>().Count() - 1);
                }
                static Fills AddFill(Fills fills1, string colorCode)
                {
                    Fill fill1 = new Fill();

                    PatternFill patternFill1 = new PatternFill() { PatternType = PatternValues.Solid };
                    ForegroundColor foregroundColor1 = new ForegroundColor() { Rgb = colorCode };
                    //BackgroundColor backgroundColor1 = new BackgroundColor() { Indexed = (UInt32Value)64U };

                    patternFill1.Append(foregroundColor1);
                  //  patternFill1.Append(backgroundColor1);

                    fill1.Append(patternFill1);
                    fills1.Append(fill1);
                    return fills1;
                }

                static void AddCellFormat(CellFormats cf, Fills fs)
                {
                    CellFormat cellFormat2 = new CellFormat() { NumberFormatId = 0, FontId = 0, FillId = (UInt32)(fs.Elements<Fill>().Count() - 1), BorderId = 0, FormatId = 0, ApplyFill = true };
                    cf.Append(cellFormat2);
                }