這段時(shí)間一直在做office的工作。前2天獲取單元格的顏色的問(wèn)題一直沒(méi)搞明白。
開(kāi)始我想用的就是Npoi.主要前一部分的工作都是用Npoi完成的
row.GetCell(j).CellStyle.FillBackgroundColorColor 獲取IColor接口。通過(guò)IColor的RGB屬性獲取可是經(jīng)過(guò)大量用例測(cè)試這里獲取的rgb并不準(zhǔn)確只有部分顏色對(duì)的上。
如圖
后來(lái)我甚至問(wèn)了npoi的創(chuàng)始人也沒(méi)有給我一個(gè)明確的回復(fù)。
我自己猜測(cè)因?yàn)閞ow.GetCell(j).CellStyle.FillBackgroundColor 是short類(lèi)型npoi是不是只支持他枚舉的顏色
后來(lái)經(jīng)過(guò)翻閱官網(wǎng)的demo發(fā)現(xiàn)npoi可以通過(guò)rgb設(shè)置顏色
- /* ====================================================================
- 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可以通過(guò)public HSSFColor GetColor(short index);方法將short轉(zhuǎn)化為HSSFColor而通過(guò)HSSFColor類(lèi)的public virtual short[] GetTriplet();方法可以獲取rgb.
但是這里存在2個(gè)問(wèn)題
1.
palette.SetColorAtIndex(HSSFColor.PINK.index, (byte)255, (byte)1, (byte)222);這里是設(shè)置的時(shí)候固定的設(shè)置。而人工操作能否有這種固定的設(shè)置。
2.
支持excel2007的XSSFWorkbook并沒(méi)有GetCustomPalette方法。而通過(guò)反編譯器我也沒(méi)找到能獲取Palette的類(lèi)似的類(lèi)
后通過(guò)官網(wǎng)excel2003和excel2007的demo如下code
2003
2007
npoi to excel2007無(wú)法獲取單元格rgb的顏色 如果顏色不一樣會(huì)向npoi支持的short轉(zhuǎn)化
實(shí)在沒(méi)法了。只有祭出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;
- //打開(kāi)文件，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;// 創(chuàng)建一個(gè)空的單元格對(duì)象
- 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的這個(gè)color的范圍是整個(gè)顏色的范圍OK問(wèn)題解決?？墒莿?dòng)用了com組件，如果大家有更好的辦法歡迎留言。
本站僅提供存儲(chǔ)服務(wù)，所有內(nèi)容均由用戶發(fā)布，如發(fā)現(xiàn)有害或侵權(quán)內(nèi)容，請(qǐng)
點(diǎn)擊舉報(bào)。
