c#如何擷取excel儲存格的RGB顏色

標籤：npoi擷取儲存格顏色用rgb表示 co

這段時間一直在做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組件，如果大家有更好的辦法歡迎留言。

c#如何擷取excel儲存格的RGB顏色