C# Excel单元格多彩文本

Question

我用C#编写了一个应用程序，将一些数据（字符串）写入Excel文档中。我需要将几个字符串写入一个单元格中。接下来我想给这些字符串着色。在我的示例中，有四个不同的字符串需要在一个单元格中以不同的颜色显示。第一个应该是蓝色的，第二个应该是绿色的，第三个应该是黑色的，最后一个应该是红色的。当我按照示例尝试时，总是只有第一个和最后一个是正确的，而其他所有的都是错误的。我使用的是Windows 7 Professional、Microsoft Excel 2010和Visual Studio 2012 Ultimate。如何修复我的源代码？

这是我的源代码：

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();
            }
        }
    }
}

- user4214499

---

看这里：https://dev59.com/P2DVa4cB1Zd3GeqPh-xM - Maciej Los

Answer 1

你的代码在每个循环中正确地着色了正确的文本。但是，当你附加下一节文本时，第一个颜色会增长并占据所有文本直到插入点。为避免这种情况，请将添加文本与着色分开处理。

        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 */
        }

- Brian from state farm