C # excel cell multicolor text

I wrote an app in C # that writes some data (strings) to excel-document. I need to write multiple lines in one cell. After that I try to color these lines. In my example, there are four different lines that need to be colored differently in the same cell . The first one should be blue, the second one should be green, the third one should be black and the last one should be red. When I try to do it like in the example, always the first and last are correct, but all others are false. For this project I am using Windows 7 Professional, Microsoft Excel 2010, and Visual Studio 2012 Ultimate. How can I fix my source?

This is my source:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.IO;
using System.Reflection;
using System.Windows;
using System.Runtime.InteropServices;
using Microsoft.Office.Interop.Excel;

namespace WindowsFormsApplication2
{
    public partial class Form1 : Form
    {
        private Microsoft.Office.Interop.Excel.Application excel = null;
        private Microsoft.Office.Interop.Excel.Workbook workbook = null;
        public Microsoft.Office.Interop.Excel.Worksheet _sheet = null;
        public Form1()
        {
            myMethod();
        }

        private void myMethod()
        {
            excel = new Microsoft.Office.Interop.Excel.ApplicationClass();

            object missing = Missing.Value;
            excel.Workbooks.Add(missing);
            workbook = excel.ActiveWorkbook;
            workbook.Worksheets.Add(missing, missing, missing);

            _sheet =
                    (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];
            _sheet.Name = "Sheet";

            string[] part = { "exampleBluee", "exampleGreen", "exampleBlack", "exampleReddd" };
            string[] faults = { "f1", "f2", "f3", "f4" };
            _sheet.Columns.WrapText = true;

            Microsoft.Office.Interop.Excel.Range position = _sheet.get_Range("A1");
            position.VerticalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
            for (int i = 0; i < 4; i++)
            {
                position.Value2 += part[i] + " ";
                int fehler = i;

                // default color
                var myColor = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Pink);

                if (faults[fehler] == "f1")
                {
                    myColor = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.RoyalBlue);
                }
                else if (faults[fehler] == "f2")
                {
                    myColor = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Green);
                }
                else if (faults[fehler] == "f3")
                {
                    myColor = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
                }
                else if (faults[fehler] == "f4")
                {
                    myColor = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
                }

                var nLenTotal = position.Value2.ToString().Length;
                int lenTxt = part[i].Length;
                // expected colorized text. (multicolor text in one cell)
                position.Characters[nLenTotal - lenTxt, lenTxt].Font.Color = myColor;
            }
            ((Microsoft.Office.Interop.Excel.Range)_sheet.Columns[1]).EntireRow.ColumnWidth = 15;

            saveExcel();
        }
        public void saveExcel()
        {
            string appPath = System.IO.Path.GetDirectoryName(
                Assembly.GetEntryAssembly().Location);
            string filename = Path.Combine(appPath, "MyDocument.xlsx");

            try
            {
                workbook.SaveAs(filename);
                workbook.Close();
            }
            catch (Exception)
            {
                workbook.Close();
            }
        }
    }
}

      

+3


source to share


1 answer


Your code works correctly for every loop by coloring the correct text. However, when you add the next section of text, the first color grows to consume all the text up to the insertion point. To avoid this, add your text by adding it in color.



        for (int i = 0; i < 4; i++)
        {//Add all the text to the cell
            position.Value += part[i] + " ";
        }

        for (int i = 0; i < 4; i++)
        {//Loop through and color the text sections
            int fehler = i;

            // default color
            var myColor = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Pink);

        /* Code removed for ease of display */
        }

      

0


source







All Articles