这段时间一直在做office的工作。前2天获取单元格的颜色的问题一直没搞明白。

开始我想用的就是Npoi.主要前一部分的工作都是用Npoi完成的

row.GetCell(j).CellStyle.FillBackgroundColorColor 获取IColor接口。通过IColor的RGB属性获取可是经过大量用例测试这里获取的rgb并不准确只有部分颜色对的上。

如图

后来我甚至问了npoi的创始人也没有给我一个明确的回复。

我自己猜测因为row.GetCell(j).CellStyle.FillBackgroundColor 是short类型npoi是不是只支持他枚举的颜色

后来经过翻阅官网的demo发现npoi可以通过rgb设置颜色

/* ====================================================================
   Licensed to the Apache Software Foundation (ASF) under one or more
   contributor license agreements.  See the NOTICE file distributed with
   this work for additional information regarding copyright ownership.
   The ASF licenses this file to You under the Apache License, Version 2.0
   (the "License"); you may not use this file except in compliance with
   the License.  You may obtain a copy of the License at

       http://www.apache.org/licenses/LICENSE-2.0

   Unless required by applicable law or agreed to in writing, software
   distributed under the License is distributed on an "AS IS" BASIS,
   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
   See the License for the specific language governing permissions and
   limitations under the License.
==================================================================== */

/* ================================================================
 * Author: Tony Qu 
 * Author's email: tonyqus (at) gmail.com 
 * NPOI HomePage: http://www.codeplex.com/npoi
 * Contributors:
 * 
 * ==============================================================*/

using System;
using System.Collections.Generic;
using System.Text;

using System.IO;
using NPOI.HSSF.UserModel;
using NPOI.HPSF;
using NPOI.POIFS.FileSystem;
using NPOI.SS.UserModel;
using NPOI.HSSF.Util;

namespace CustomColorInXls
{
    class Program
    {
        static void Main(string[] args)
        {
            InitializeWorkbook();


            HSSFPalette palette = workbook.GetCustomPalette();
            palette.SetColorAtIndex(HSSFColor.PINK.index, (byte)255, (byte)1, (byte)222);
           //HSSFColor  palette.GetColor()
            //HSSFColor myColor = palette.AddColor((byte)253, (byte)0, (byte)0);

            ISheet sheet1 = workbook.CreateSheet("Sheet1");
            ICellStyle style1 = workbook.CreateCellStyle();
            style1.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.PINK.index;
            style1.FillPattern = FillPatternType.SOLID_FOREGROUND;
            sheet1.CreateRow(0).CreateCell(0).CellStyle = style1;
            short c = sheet1.GetRow(0).Cells[0].CellStyle.FillForegroundColor;
            short []sh = palette.GetColor(c).GetTriplet();

            WriteToFile();
        }

        static HSSFWorkbook workbook;

        static void WriteToFile()
        {
            //Write the stream data of workbook to the root directory
            FileStream file = new FileStream(@"test.xls", FileMode.Create);
            workbook.Write(file);
            file.Close();
        }

        static void InitializeWorkbook()
        {
            workbook = new HSSFWorkbook();

            ////create a entry of DocumentSummaryInformation
            DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
            dsi.Company = "NPOI Team";
            workbook.DocumentSummaryInformation = dsi;

            ////create a entry of SummaryInformation
            SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
            si.Subject = "NPOI SDK Example";
            workbook.SummaryInformation = si;
        }
    }
}

但是这里存在2个问题

1.

palette.SetColorAtIndex(HSSFColor.PINK.index, (byte)255, (byte)1, (byte)222);这里是设置的时候固定的设置。而人工操作能否有这种固定的设置。

2.

支持excel2007的XSSFWorkbook并没有GetCustomPalette方法。而通过反编译器我也没找到能获取Palette的类似的类

后通过官网excel2003和excel2007的demo如下code

2003

2007

npoi to excel2007无法获取单元格rgb的颜色 如果颜色不一样会向npoi支持的short转化

实在没法了。只有祭出com组件了。

代码如下:

Microsoft.Office.Interop.Excel.Application application = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook workbook = null;
Microsoft.Office.Interop.Excel.Worksheet worksheet = null;
//打开文件，n.FullPath是文件路径  
workbook = application.Application.Workbooks.Open(copyPath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
Microsoft.Office.Interop.Excel.Range range = null;// 创建一个空的单元格对象
range = worksheet.get_Range(worksheet.Cells[rowNum + 1, ColumnNum + 1], worksheet.Cells[rowNum + 1, ColumnNum + 1]);
if (range.Value2 != null)
{
    string content = range.Value2.ToString();
}
string color = range.Interior.Color.ToString();
 Common com = new Common();
Color col = com.RGB(int.Parse(color));
return new byte[3] { col.R, col.G, col.B };

 public Color RGB(int color)
        {
            int r = 0xFF & color;
            int g = 0xFF00 & color;
            g >>= 8;
            int b = 0xFF0000 & color;
            b >>= 16;
            return Color.FromArgb(r, g, b);
        }

c#如何获取excel单元格的RGB颜色