这段时间一直在做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;
        }
    }
}

并且palettle能够通过public HSSFColor GetColor(short index);方法将short转化为HSSFColor而通过HSSFColor类的public virtual short[] GetTriplet();方法能够获取rgb.

可是这里存在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 };

RGB方法例如以下:

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

string color的这个color的范围是整个颜色的范围OK问题解决。但是动用了com组件。假设大家有更好的办法欢迎留言。