C# HSSFWorkbook.GetCustomPalette方法代码示例

        public WorkbookExcelRender()
        {
            _workbook = new HSSFWorkbook();
            
            _isDisposed = false;

            OddCellStyle = _workbook.CreateCellStyle();
            EvenCellStyle = _workbook.CreateCellStyle();
            OddCellStyleCenterAligned = _workbook.CreateCellStyle();
            EvenCellStyleCenterAligned = _workbook.CreateCellStyle();

            _nextCellIndex = -1;

            RTFHelper = new RTFHelper()
            {
                NegFont = _workbook.CreateFont(),
                NormalFont = _workbook.CreateFont(),
                PosFont = _workbook.CreateFont(),
                NegFontCrossed = _workbook.CreateFont(),
                NormalFontCrossed = _workbook.CreateFont(),
                PosFontCrossed = _workbook.CreateFont(),
                Pallete = _workbook.GetCustomPalette(),
                RTFRenderer = new RtfTextRender()
            };

            InitDefaultRTFHelper();
            InitDefaultCellStyles();
        }

        /// <summary>
        /// Lookup RGB from .NET system colour in Excel pallete - or nearest match.
        /// </summary>
        public static short GetXLColor(HSSFWorkbook xlWorkbook, Color color)
        {
            if (color == Color.Empty) {
            // COLOR_NORMAL=unspecified.
            return HSSFColor.COLOR_NORMAL;
              }

              HSSFPalette XlPalette = xlWorkbook.GetCustomPalette();
              HSSFColor XlColour = XlPalette.FindColor(color.R, color.G, color.B);

              if (XlColour == null) {
            XlColour = XlPalette.FindSimilarColor(color.R, color.G, color.B);
            return XlColour.GetIndex();
              } else {
            return XlColour.GetIndex();
              }
        }

        public void TestCustomPalette()
        {
            //reading sample xls
            HSSFWorkbook book = HSSFTestDataSamples.OpenSampleWorkbook("Simple.xls");

            //creating custom palette
            HSSFPalette palette = book.GetCustomPalette();
            palette.SetColorAtIndex((short)0x12, (byte)101, (byte)230, (byte)100);
            palette.SetColorAtIndex((short)0x3b, (byte)0, (byte)255, (byte)52);

            //writing to disk; reading in and verifying palette
            string tmppath = TempFile.GetTempFilePath("TestCustomPalette", ".xls");
            FileStream fos = new FileStream(tmppath, FileMode.OpenOrCreate);
            book.Write(fos);
            fos.Close();

            FileStream fis = new FileStream(tmppath, FileMode.Open,FileAccess.Read);
            book = new HSSFWorkbook(fis);
            fis.Close();

            palette = book.GetCustomPalette();
            HSSFColor color = palette.GetColor(HSSFColor.CORAL.index);  //unmodified
            Assert.IsNotNull(color, "Unexpected null in custom palette (unmodified index)");
            short[] expectedRGB = HSSFColor.CORAL.triplet;
            short[] actualRGB = color.GetTriplet();
            String msg = "Expected palette position to remain unmodified";
            Assert.AreEqual(expectedRGB[0], actualRGB[0], msg);
            Assert.AreEqual(expectedRGB[1], actualRGB[1], msg);
            Assert.AreEqual(expectedRGB[2], actualRGB[2], msg);

            color = palette.GetColor((short)0x12);
            Assert.IsNotNull(color, "Unexpected null in custom palette (modified)");
            actualRGB = color.GetTriplet();
            msg = "Expected palette modification to be preserved across save";
            Assert.AreEqual((short)101, actualRGB[0], msg);
            Assert.AreEqual((short)230, actualRGB[1], msg);
            Assert.AreEqual((short)100, actualRGB[2], msg);
        }

        public void TestFindSimilar()
        {
            HSSFWorkbook book = new HSSFWorkbook();
            HSSFPalette p = book.GetCustomPalette();


            // Add a few edge colours in
            p.SetColorAtIndex((short)8, unchecked((byte)-1), (byte)0, (byte)0);
            p.SetColorAtIndex((short)9, (byte)0, unchecked((byte)-1), (byte)0);
            p.SetColorAtIndex((short)10, (byte)0, (byte)0, unchecked((byte)-1));

            // And some near a few of them
            p.SetColorAtIndex((short)11, unchecked((byte)-1), (byte)2, (byte)2);
            p.SetColorAtIndex((short)12, unchecked((byte)-2), (byte)2, (byte)10);
            p.SetColorAtIndex((short)13, unchecked((byte)-4), (byte)0, (byte)0);
            p.SetColorAtIndex((short)14, unchecked((byte)-8), (byte)0, (byte)0);

            Assert.AreEqual(
                    "FFFF:0:0", p.GetColor((short)8).GetHexString()
            );

            // Now Check we get the right stuff back
            Assert.AreEqual(
                    p.GetColor((short)8).GetHexString(),
                    p.FindSimilarColor(unchecked((byte)-1), (byte)0, (byte)0).GetHexString()
            );
            Assert.AreEqual(
                    p.GetColor((short)8).GetHexString(),
                    p.FindSimilarColor(unchecked((byte)-2), (byte)0, (byte)0).GetHexString()
            );
            Assert.AreEqual(
                    p.GetColor((short)8).GetHexString(),
                    p.FindSimilarColor(unchecked((byte)-1), (byte)1, (byte)0).GetHexString()
            );
            Assert.AreEqual(
                    p.GetColor((short)11).GetHexString(),
                    p.FindSimilarColor(unchecked((byte)-1), (byte)2, (byte)1).GetHexString()
            );
            Assert.AreEqual(
                    p.GetColor((short)12).GetHexString(),
                    p.FindSimilarColor(unchecked((byte)-1), (byte)2, (byte)10).GetHexString()
            );
        }

 /// <summary>
 /// get the color value for the font
 /// </summary>
 /// <param name="wb">HSSFWorkbook</param>
 /// <returns></returns>
 public HSSFColor GetHSSFColor(HSSFWorkbook wb)
 {
     HSSFPalette pallette = wb.GetCustomPalette();
     return pallette.GetColor(Color);
 }

        void BuildStyle_Font(HSSFWorkbook workbook, StringBuilder style,
                HSSFFont font)
        {
            switch (font.Boldweight)
            {
                case (short)FontBoldWeight.BOLD:
                    style.Append("font-weight: bold; ");
                    break;
                case (short)FontBoldWeight.NORMAL:
                    // by default, not not increase HTML size
                    // style.Append( "font-weight: normal; " );
                    break;
            }

            HSSFColor fontColor = workbook.GetCustomPalette().GetColor(font.Color);
            if (fontColor != null)
                style.Append("color: " + ExcelToHtmlUtils.GetColor(fontColor) + "; ");

            if (font.FontHeightInPoints != 0)
                style.Append("font-size: " + font.FontHeightInPoints + "pt; ");

            if (font.IsItalic)
            {
                style.Append("font-style: italic; ");
            }
        }

        private void BuildStyle_Border(HSSFWorkbook workbook, StringBuilder style,
                String type, BorderStyle xlsBorder, short borderColor)
        {
            if (xlsBorder == BorderStyle.NONE)
                return;

            StringBuilder borderStyle = new StringBuilder();
            borderStyle.Append(ExcelToHtmlUtils.GetBorderWidth(xlsBorder));
            borderStyle.Append(' ');
            borderStyle.Append(ExcelToHtmlUtils.GetBorderStyle(xlsBorder));

            HSSFColor color = workbook.GetCustomPalette().GetColor(borderColor);
            if (color != null)
            {
                borderStyle.Append(' ');
                borderStyle.Append(ExcelToHtmlUtils.GetColor(color));
            }

            style.Append("border-" + type + ": " + borderStyle + "; ");
        }

        protected String BuildStyle(HSSFWorkbook workbook, HSSFCellStyle cellStyle)
        {
            StringBuilder style = new StringBuilder();
            HSSFPalette palette = workbook.GetCustomPalette();
            style.Append("white-space: pre-wrap; ");
            ExcelToHtmlUtils.AppendAlign(style, cellStyle.Alignment);

            if (cellStyle.FillPattern == FillPatternType.NO_FILL)
            {
                // no fill
            }
            else if (cellStyle.FillPattern == FillPatternType.SOLID_FOREGROUND)
            {
                //cellStyle.
                //HSSFColor.
                HSSFColor foregroundColor = palette.GetColor(cellStyle.FillForegroundColor);
                if (foregroundColor != null)
                    style.Append("background-color: " + ExcelToHtmlUtils.GetColor(foregroundColor) + "; ");
            }
            else
            {
                HSSFColor backgroundColor = palette.GetColor(cellStyle.FillBackgroundColor);
                if (backgroundColor != null)
                    style.Append("background-color: " + ExcelToHtmlUtils.GetColor(backgroundColor) + "; ");
            }

            BuildStyle_Border(workbook, style, "top", cellStyle.BorderTop, cellStyle.TopBorderColor);
            BuildStyle_Border(workbook, style, "right", cellStyle.BorderRight, cellStyle.RightBorderColor);
            BuildStyle_Border(workbook, style, "bottom", cellStyle.BorderBottom, cellStyle.BottomBorderColor);
            BuildStyle_Border(workbook, style, "left", cellStyle.BorderLeft, cellStyle.LeftBorderColor);

            HSSFFont font = cellStyle.GetFont(workbook) as HSSFFont;
            BuildStyle_Font(workbook, style, font);

            return style.ToString();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            string fileName;
            //fileName = System.IO.Path.GetDirectoryName(sourceFileTexBox.Text) + "\\";
            fileName = "list_" + System.IO.Path.GetFileName(sourceFileTexBox.Text);
            fileName += ".xls";

            SaveFileDialog saveFileDia = new SaveFileDialog();
            saveFileDia.Filter = "Excel文件|*.xls";
            saveFileDia.FileName = fileName;

            DialogResult result = saveFileDia.ShowDialog();
            if (DialogResult.OK == result)
            {
                fileName = saveFileDia.FileName;
                if (File.Exists(fileName))
                {
                    MessageBox.Show("目标文件已经存在！", "错误");
                    return;
                }

                try
                {
                    IWorkbook wb = new HSSFWorkbook();

                    ISheet tb = wb.CreateSheet(System.IO.Path.GetFileName(sourceFileTexBox.Text));
                    tb.DisplayGridlines = false;

                    tb.CreateRow(5).CreateCell(29).SetCellValue("備考");
                    tb.GetRow(5).CreateCell(3).SetCellValue("No.");
                    tb.GetRow(5).CreateCell(5).SetCellValue("関数名");
                    tb.GetRow(5).CreateCell(17).SetCellValue("テスト方法");

                    ICellStyle bg = (HSSFCellStyle)wb.CreateCellStyle();
                    IFont ft=wb.CreateFont();
                    ft.Color = NPOI.HSSF.Util.HSSFColor.White.Index;
                    bg.SetFont(ft);
                    HSSFWorkbook wob = new HSSFWorkbook();
                    HSSFPalette pa=wob.GetCustomPalette();
                    NPOI.HSSF.Util.HSSFColor XlColour = pa.FindSimilarColor(23, 55, 93);
                    bg.FillForegroundColor = XlColour.Indexed; //15000;
                    bg.FillPattern = FillPattern.SolidForeground;

                    tb.CreateRow(3);
                    for (int i = 0; i < 50; i++)
                    {
                        tb.GetRow(3).CreateCell(i).CellStyle = bg;
                    }
                    tb.GetRow(3).GetCell(1).SetCellValue(System.IO.Path.GetFileName(sourceFileTexBox.Text));

                    ICellStyle Border3 = (HSSFCellStyle)wb.CreateCellStyle();
                    Border3.BorderTop = NPOI.SS.UserModel.BorderStyle.Thick;
                    Border3.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thick;
                    Border3.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;

                    tb.GetRow(5).GetCell(3).CellStyle = Border3;

                    ICellStyle Border2 = (HSSFCellStyle)wb.CreateCellStyle();
                    Border2.BorderTop = NPOI.SS.UserModel.BorderStyle.Thick;
                    Border2.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;

                    ICellStyle Border4 = (HSSFCellStyle)wb.CreateCellStyle();
                    Border4.BorderTop = NPOI.SS.UserModel.BorderStyle.Thick;
                    Border4.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                    Border4.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;

                    ICellStyle Border5 = (HSSFCellStyle)wb.CreateCellStyle();
                    Border5.BorderTop = NPOI.SS.UserModel.BorderStyle.Thick;
                    Border5.BorderRight = NPOI.SS.UserModel.BorderStyle.Thick;
                    Border5.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;

                    ICellStyle Border6 = (HSSFCellStyle)wb.CreateCellStyle();
                    Border6.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
                    Border6.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thick;
                    Border6.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;

                     ICellStyle Border7 = (HSSFCellStyle)wb.CreateCellStyle();
                    Border7.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
                    Border7.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                    Border7.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;

                    ICellStyle Border8 = (HSSFCellStyle)wb.CreateCellStyle();
                    Border8.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
                    Border8.BorderRight = NPOI.SS.UserModel.BorderStyle.Thick;
                    Border8.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;

                    ICellStyle Border9 = (HSSFCellStyle)wb.CreateCellStyle();
                    Border9.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
                    Border9.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thick;
                    Border9.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thick;

                    ICellStyle Border10 = (HSSFCellStyle)wb.CreateCellStyle();
                    Border10.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
                    Border10.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thick;

                    ICellStyle Border11 = (HSSFCellStyle)wb.CreateCellStyle();
                    Border11.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
                    Border11.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                    Border11.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thick;

//.........这里部分代码省略.........

 private HSSFColor GetColor(HSSFWorkbook workbook, byte red, byte green, byte blue)
 {
     HSSFPalette palette = workbook.GetCustomPalette();
     HSSFColor colour = palette.FindSimilarColor(red, green, blue);
     if (colour == null)
     {
        // NPOI.HSSF.Record.PaletteRecord.STANDARD_PALETTE_SIZE++;
         colour = palette.AddColor(red, green, blue);
     }
     return colour;
 }

        public static MemoryStream ExportToExcel(List<Group> list, List<string> date)
        {
            try
            {
                //文件流对像
                MemoryStream stream = new MemoryStream();

                //打开Excel对象
                HSSFWorkbook workbook = new HSSFWorkbook();

                Font aFont = workbook.CreateFont();
                aFont.FontName = "Calibri";
                aFont.FontHeightInPoints = 14;

                //aFont.Boldweight = short.MaxValue;

                CellStyle projectStyle = workbook.CreateCellStyle();
                //对齐方式
                projectStyle.Alignment = HorizontalAlignment.CENTER;
                projectStyle.VerticalAlignment = VerticalAlignment.BOTTOM;
                ////边框
                projectStyle.BorderTop = CellBorderType.MEDIUM;
                projectStyle.BorderRight = CellBorderType.MEDIUM;
                projectStyle.BorderBottom = CellBorderType.THIN;
                projectStyle.BorderLeft = CellBorderType.MEDIUM;
                //字体
                projectStyle.SetFont(aFont);

                //背景颜色
                HSSFPalette palette = workbook.GetCustomPalette();
                palette.SetColorAtIndex((short)9, (byte)(240), (byte)(240), (byte)(240));
                palette.SetColorAtIndex((short)10, (byte)(220), (byte)(220), (byte)(220));

                projectStyle.FillForegroundColor=(short)9;
                projectStyle.FillPattern = FillPatternType.SOLID_FOREGROUND;
                //projectStyle.FillBackgroundColor = (short)9;

                CellStyle headStyle = workbook.CreateCellStyle();
                headStyle.Alignment = HorizontalAlignment.CENTER;
                headStyle.VerticalAlignment = VerticalAlignment.BOTTOM;
                headStyle.BorderTop = CellBorderType.MEDIUM;
                headStyle.BorderRight = CellBorderType.THIN;

                headStyle.SetFont(aFont);

                //headStyle.FillForegroundColor = HSSFColor.GREY_25_PERCENT.index;
                //headStyle.FillPattern = FillPatternType.SQUARES;
                //headStyle.FillBackgroundColor = HSSFColor.GREY_25_PERCENT.index;
                headStyle.FillForegroundColor = (short)9;
                headStyle.FillPattern = FillPatternType.SOLID_FOREGROUND;

                CellStyle dateStyle = workbook.CreateCellStyle();
                dateStyle.Alignment = HorizontalAlignment.CENTER;
                dateStyle.VerticalAlignment = VerticalAlignment.BOTTOM;
                dateStyle.BorderTop = CellBorderType.MEDIUM;
                dateStyle.BorderRight = CellBorderType.MEDIUM;
                dateStyle.BorderLeft = CellBorderType.MEDIUM;

                Font bFont = workbook.CreateFont();
                bFont.FontName = "Calibri";
                bFont.FontHeightInPoints = 12;
                dateStyle.SetFont(bFont);

                CellStyle projectstyle = workbook.CreateCellStyle();
                //对齐方式
                projectstyle.Alignment = HorizontalAlignment.CENTER;
                projectstyle.VerticalAlignment = VerticalAlignment.CENTER;

                ////边框
                projectstyle.BorderRight = CellBorderType.MEDIUM;
                projectstyle.BorderBottom = CellBorderType.MEDIUM;
                //projectstyle.BorderLeft = CellBorderType.MEDIUM;

                projectstyle.SetFont(bFont);

                CellStyle nameStyle = workbook.CreateCellStyle();
                nameStyle.SetFont(bFont);
                //对齐方式
                nameStyle.Alignment = HorizontalAlignment.CENTER;
                nameStyle.VerticalAlignment = VerticalAlignment.CENTER;
                ////边框
                nameStyle.BorderTop = CellBorderType.THIN;
                nameStyle.BorderRight = CellBorderType.THIN;
                nameStyle.BorderBottom = CellBorderType.THIN;
                nameStyle.BorderLeft = CellBorderType.MEDIUM;

                CellStyle itemStyle = workbook.CreateCellStyle();
                nameStyle.SetFont(bFont);
                //对齐方式
                //itemStyle.Alignment = HorizontalAlignment.CENTER;
                itemStyle.VerticalAlignment = VerticalAlignment.BOTTOM;
                ////边框
                itemStyle.BorderTop = CellBorderType.THIN;
                itemStyle.BorderRight = CellBorderType.THIN;
                itemStyle.BorderBottom = CellBorderType.THIN;
                itemStyle.BorderLeft = CellBorderType.THIN;

                CellStyle datestyle = workbook.CreateCellStyle();
                datestyle.SetFont(bFont);
                //对齐方式
//.........这里部分代码省略.........

        public static MemoryStream ExportDataToExcel(List<Tuple<string, string, string>> list)
        {
            try
            {
                //File stream object
                MemoryStream stream = new MemoryStream();

                //Open Excel object
                HSSFWorkbook workbook = new HSSFWorkbook();

                //custom color
                HSSFPalette palette = workbook.GetCustomPalette();
                palette.SetColorAtIndex((short)15, (byte)(184), (byte)(134), (byte)(11));
                palette.SetColorAtIndex((short)16, (byte)(255), (byte)(240), (byte)(215));

                //Title Style
                CellStyle titlestyle = workbook.CreateCellStyle();
                //Alignment
                titlestyle.Alignment = HorizontalAlignment.CENTER;
                titlestyle.VerticalAlignment = VerticalAlignment.CENTER;
                //Font
                Font titlefont = workbook.CreateFont();
                titlefont.FontHeightInPoints = 16;
                titlefont.FontName = "Calibri";
                titlefont.Color = HSSFColor.WHITE.index;
                titlefont.Boldweight = (short)FontBoldWeight.BOLD;
                titlestyle.SetFont(titlefont);
                //Background color
                titlestyle.FillForegroundColor = (short)15;
                titlestyle.FillPattern = FillPatternType.SOLID_FOREGROUND;
                //Border
                titlestyle.BorderTop = CellBorderType.THIN;
                titlestyle.BorderRight = CellBorderType.THIN;
                titlestyle.BorderBottom = CellBorderType.THIN;
                titlestyle.BorderLeft = CellBorderType.THIN;

                //Head Style
                CellStyle headstyle = workbook.CreateCellStyle();
                //Alignment
                headstyle.Alignment = HorizontalAlignment.CENTER;
                headstyle.VerticalAlignment = VerticalAlignment.CENTER;
                //Font
                Font headfont = workbook.CreateFont();
                headfont.FontHeightInPoints = 14;
                headfont.FontName = "Calibri";
                headfont.Boldweight = (short)FontBoldWeight.BOLD;
                headstyle.SetFont(headfont);
                //Background color
                headstyle.FillForegroundColor = (short)16;
                headstyle.FillPattern = FillPatternType.SOLID_FOREGROUND;
                //Border
                headstyle.BorderTop = CellBorderType.THIN;
                headstyle.BorderRight = CellBorderType.THIN;
                headstyle.BorderBottom = CellBorderType.THIN;
                headstyle.BorderLeft = CellBorderType.THIN;

                //Name Style
                CellStyle namestyle = workbook.CreateCellStyle();
                //Alignment
                namestyle.Alignment = HorizontalAlignment.CENTER;
                namestyle.VerticalAlignment = VerticalAlignment.CENTER;
                //Font
                Font namefont = workbook.CreateFont();
                namefont.FontHeightInPoints = 11;
                namefont.FontName = "Calibri";
                namefont.Boldweight = (short)FontBoldWeight.BOLD;
                namestyle.SetFont(namefont);
                //Border
                namestyle.BorderTop = CellBorderType.THIN;
                namestyle.BorderRight = CellBorderType.THIN;
                namestyle.BorderBottom = CellBorderType.THIN;
                namestyle.BorderLeft = CellBorderType.THIN;

                //Body Style
                CellStyle bodystyle = workbook.CreateCellStyle();
                //Alignment
                bodystyle.VerticalAlignment = VerticalAlignment.CENTER;
                //Font
                Font bodyfont = workbook.CreateFont();
                bodyfont.FontHeightInPoints = 12;
                bodyfont.FontName = "Times New Roman";
                bodystyle.SetFont(bodyfont);
                //Border
                bodystyle.BorderTop = CellBorderType.THIN;
                bodystyle.BorderRight = CellBorderType.THIN;
                bodystyle.BorderBottom = CellBorderType.THIN;
                bodystyle.BorderLeft = CellBorderType.THIN;
                //Line Feed
                bodystyle.WrapText = true;
                //Sheet's object of Excel
                Sheet sheet = workbook.CreateSheet("sheet1");
                sheet.SetColumnWidth(0, (short)(35.7 * 160));
                sheet.SetColumnWidth(1, (short)(35.7 * 400));
                sheet.SetColumnWidth(2, (short)(35.7 * 600));

                //Export to Excel
                Row row;
                Cell cell;
                //cell.SetCellType();
                int count = 2;
//.........这里部分代码省略.........