Applying multiple colors to the single cell in EPPlus?

Question

I am using EPPlus to generate report file. There is a column which does not intended to be used for any filtering/sorting logic but contains line-by-line data about the subject of the row.

Customer wants it to be filled by different colors as shown in the attached picture.

I've already found similar questions and correct answers here and here, but these answes works only for the case when the whole cell need to be filled by the single color.

I wonder if there a way to implement my case programmatically with EPPlus or the only way to achive such feature is to modify the cell's metadata manually via OpenXML library or even by modifying underlying XML-file?

Answer 1

Create a Console app named MultipleColorsInACell and copy and paste this below code.

using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System;
using System.Data;
using System.IO;

namespace MultipleColorsInACell
{
    class Program
    {
        static void Main(string[] args)
        {
            try
            {
                WorkbookPart workbookPart = null;

                try
                {
                    using (var memoryStream = new MemoryStream())
                    {
                        using (var excel = SpreadsheetDocument.Create(memoryStream, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook, true))
                        {
                            workbookPart = excel.AddWorkbookPart();
                            workbookPart.Workbook = new Workbook();
                            uint sheetId = 1;
                            excel.WorkbookPart.Workbook.Sheets = new Sheets();
                            Sheets sheets = excel.WorkbookPart.Workbook.GetFirstChild<Sheets>();


                            string relationshipId = "rId1";
                            WorksheetPart wSheetPart = workbookPart.AddNewPart<WorksheetPart>(relationshipId);
                            string sheetName = "MultipleColorsInACell";
                            Sheet sheet = new Sheet() { Id = relationshipId, SheetId = sheetId, Name = sheetName };
                            sheets.Append(sheet);

                            Worksheet worksheet = new Worksheet();

                            wSheetPart.Worksheet = worksheet;

                            SheetData sheetData = new SheetData();
                            worksheet.Append(sheetData);

                            string[] excelColumns = new string[] { "A", "B", "C", "D", "E", "F", "G" };


                            Row row1 = new Row() { RowIndex = (UInt32Value)5U, Spans = new ListValue<StringValue>() { InnerText = "9:9" }, DyDescent = 0.3D };

                            Cell cell1 = new Cell() { CellReference = "I5", DataType = CellValues.SharedString };
                            CellValue cellValue1 = new CellValue();
                            cellValue1.Text = "0";

                            cell1.Append(cellValue1);

                            row1.Append(cell1);

                            sheetData.Append(row1);

                            SharedStringTablePart sharedStringTablePart1 = workbookPart.AddNewPart<SharedStringTablePart>("rId4");
                            GenerateSharedStringTablePart1Content(sharedStringTablePart1);


                            excel.Close();
                        }

                        FileStream fileStream = new FileStream(AppDomain.CurrentDomain.BaseDirectory + "MultipleColorsInACell.xlsx", FileMode.Create, FileAccess.Write);
                        memoryStream.WriteTo(fileStream);
                        fileStream.Close();
                        memoryStream.Close();
                    }
                }
                catch (Exception ex)
                {

                    throw ex;
                }
            }
            catch (Exception ex)
            {

                // logging, etc.
            }
        }


        // Generates content of sharedStringTablePart1.
        private static void GenerateSharedStringTablePart1Content(SharedStringTablePart sharedStringTablePart1)
        {
            SharedStringTable sharedStringTable1 = new SharedStringTable() { Count = (UInt32Value)1U, UniqueCount = (UInt32Value)1U };

            SharedStringItem sharedStringItem1 = new SharedStringItem();

            Run run1 = new Run();

            RunProperties runProperties1 = new RunProperties();
            FontSize fontSize4 = new FontSize() { Val = 11D };
            Color color4 = new Color() { Rgb = "0A0A09" };
            RunFont runFont1 = new RunFont() { Val = "Calibri" };
            FontFamily fontFamily1 = new FontFamily() { Val = 2 };
            FontScheme fontScheme5 = new FontScheme() { Val = FontSchemeValues.Minor };

            runProperties1.Append(fontSize4);
            runProperties1.Append(color4);
            runProperties1.Append(runFont1);
            runProperties1.Append(fontFamily1);
            runProperties1.Append(fontScheme5);
            Text text1 = new Text();
            text1.Text = "Requested by John S. (regular officer) - 20.10.19,";

            run1.Append(runProperties1);
            run1.Append(text1);

            Run run2 = new Run();

            RunProperties runProperties2 = new RunProperties();
            FontSize fontSize5 = new FontSize() { Val = 11D };
            Color color5 = new Color() { Rgb = "4A79B1" };
            RunFont runFont2 = new RunFont() { Val = "Calibri" };
            FontFamily fontFamily2 = new FontFamily() { Val = 2 };
            FontScheme fontScheme6 = new FontScheme() { Val = FontSchemeValues.Minor };

            runProperties2.Append(fontSize5);
            runProperties2.Append(color5);
            runProperties2.Append(runFont2);
            runProperties2.Append(fontFamily2);
            runProperties2.Append(fontScheme6);
            Text text2 = new Text() { Space = SpaceProcessingModeValues.Preserve };
            text2.Text = "Approved by Franchesco N. (supervising manager) - 22.10.19,";

            run2.Append(runProperties2);
            run2.Append(text2);

            Run run3 = new Run();

            RunProperties runProperties3 = new RunProperties();
            FontSize fontSize6 = new FontSize() { Val = 11D };
            Color color6 = new Color() { Rgb = "728D3C" };
            RunFont runFont3 = new RunFont() { Val = "Calibri" };
            FontFamily fontFamily3 = new FontFamily() { Val = 2 };
            FontScheme fontScheme7 = new FontScheme() { Val = FontSchemeValues.Minor };

            runProperties3.Append(fontSize6);
            runProperties3.Append(color6);
            runProperties3.Append(runFont3);
            runProperties3.Append(fontFamily3);
            runProperties3.Append(fontScheme7);
            Text text3 = new Text();
            text3.Text = "Signed by Koglovomor I. (CTO) - 22.10.19";

            run3.Append(runProperties3);
            run3.Append(text3);

            sharedStringItem1.Append(run1);
            sharedStringItem1.Append(run2);
            sharedStringItem1.Append(run3);

            sharedStringTable1.Append(sharedStringItem1);

            sharedStringTablePart1.SharedStringTable = sharedStringTable1;
        }

    }
}