Ejemplo n.º 1
0
        /// <summary>
        /// 获取单元格样式
        /// </summary>
        /// <param name="hssfworkbook">workbook对象</param>
        /// <param name="font">单元格字体</param>
        /// <param name="fillBackgroundColor">单元格背景</param>
        /// <param name="ha">垂直对齐方式</param>
        /// <param name="va">水平对齐方式</param>
        /// <returns></returns>
        public static ICellStyle GetCellStyle(IWorkbook hssfworkbook, IFont font, HSSFColor fillBackgroundColor, HorizontalAlignment ha, VerticalAlignment va)
        {
            ICellStyle cellstyle = hssfworkbook.CreateCellStyle();

            cellstyle.Alignment         = ha;
            cellstyle.VerticalAlignment = va;
            if (fillBackgroundColor != null)
            {
                cellstyle.FillBackgroundColor = fillBackgroundColor.GetIndex();
            }
            if (font != null)
            {
                cellstyle.SetFont(font);
            }
            //有边框
            cellstyle.BorderBottom = BorderStyle.THIN;
            cellstyle.BorderLeft   = BorderStyle.THIN;
            cellstyle.BorderRight  = BorderStyle.THIN;
            cellstyle.BorderTop    = BorderStyle.THIN;
            return(cellstyle);
        }
Ejemplo n.º 2
0
        public void TestDefaultPalette()
        {
            PaletteRecord palette = new PaletteRecord();

            //make sure all the HSSFColor constants match
            Hashtable   colors  = HSSFColor.GetIndexHash();
            IEnumerator indexes = colors.Keys.GetEnumerator();

            while (indexes.MoveNext())
            {
                int       index          = (int)indexes.Current;
                HSSFColor c              = (HSSFColor)colors[index];
                short[]   rgbTriplet     = c.GetTriplet();
                byte[]    paletteTriplet = palette.GetColor((short)index);
                String    msg            = "Expected HSSFColor constant to match PaletteRecord at index 0x"
                                           + NPOI.Util.StringUtil.ToHexString(c.GetIndex());
                Assert.AreEqual(rgbTriplet[0], paletteTriplet[0] & 0xff, msg);
                Assert.AreEqual(rgbTriplet[1], paletteTriplet[1] & 0xff, msg);
                Assert.AreEqual(rgbTriplet[2], paletteTriplet[2] & 0xff, msg);
            }
        }
Ejemplo n.º 3
0
        public void Test48403()
        {
            HSSFWorkbook wb = new HSSFWorkbook();

            var         color   = new Rgb24(0, 0x6B, 0x6B); //decode("#006B6B");
            HSSFPalette palette = wb.GetCustomPalette();

            HSSFColor hssfColor = palette.FindColor(color.R, color.G, color.B);

            Assert.IsNull(hssfColor);

            palette.SetColorAtIndex(
                (short)(PaletteRecord.STANDARD_PALETTE_SIZE - 1),
                (byte)color.R, (byte)color.G,
                (byte)color.B);
            hssfColor = palette.GetColor((short)(PaletteRecord.STANDARD_PALETTE_SIZE - 1));
            Assert.IsNotNull(hssfColor);
            Assert.AreEqual(55, hssfColor.Indexed);
            CollectionAssert.AreEqual(new short[] { 0, 107, 107 }, hssfColor.GetTriplet());

            wb.Close();
        }
Ejemplo n.º 4
0
        protected override void AssertColour(String hexExpected, IColor actual)
        {
            Assert.IsNotNull(actual, "Colour must be given");

            if (actual is HSSFColor)
            {
                HSSFColor colour = (HSSFColor)actual;
                Assert.AreEqual(hexExpected, colour.GetHexString());
            }
            else
            {
                HSSFExtendedColor colour = (HSSFExtendedColor)actual;
                if (hexExpected.Length == 8)
                {
                    Assert.AreEqual(hexExpected, colour.ARGBHex);
                }
                else
                {
                    Assert.AreEqual(hexExpected, colour.ARGBHex.Substring(2));
                }
            }
        }
Ejemplo n.º 5
0
 private void PrintColor()
 {
     for (int i = sheet.FirstRowNum; i < sheet.LastRowNum; i++)
     {
         IRow row = sheet.GetRow(i);
         if (row == null)
         {
             continue;
         }
         for (int j = row.FirstCellNum; j < row.LastCellNum; ++j)
         {
             ICell cell = row.GetCell(j);
             if (cell == null)
             {
                 continue;
             }
             Console.Write(i + "," + j + ":");
             HSSFColor color = (HSSFColor)cell.CellStyle.FillForegroundColorColor;
             Console.Write("(" + color.RGB[0] + "," + color.RGB[1] + "," + color.RGB[2] + ") ");
         }
         Console.WriteLine();
     }
 }
Ejemplo n.º 6
0
        public static HSSFColor setColor(HSSFWorkbook workbook, byte r, byte g, byte b)
        {
            try
            {
                HSSFPalette palette = workbook.GetCustomPalette();

                HSSFColor color = null;

                color = palette.FindColor(r, g, b);
                if (color == null)
                {
                    palette.SetColorAtIndex(HSSFColor.Blue.Index, r, g, b);

                    color = palette.GetColor(HSSFColor.Blue.Index);
                }

                return(color);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
Ejemplo n.º 7
0
        /// <summary>
        /// 获取单元格样式
        /// </summary>
        /// <param name="hssfworkbook">Excel操作类</param>
        /// <param name="font">单元格字体</param>
        /// <param name="fillForegroundColor">图案的颜色</param>
        /// <param name="fillPattern">图案样式</param>
        /// <param name="fillBackgroundColor">单元格背景</param>
        /// <param name="ha">垂直对齐方式</param>
        /// <param name="va">垂直对齐方式</param>
        /// <returns></returns>
        private ICellStyle GetCellStyle(IWorkbook hssfworkbook, IFont font, HSSFColor fillForegroundColor, FillPattern fillPattern, HSSFColor fillBackgroundColor, HorizontalAlignment ha, VerticalAlignment va, bool haveBorder = true)
        {
            ICellStyle cellstyle = hssfworkbook.CreateCellStyle();

            cellstyle.FillPattern       = fillPattern;
            cellstyle.Alignment         = ha;
            cellstyle.VerticalAlignment = va;
            if (fillForegroundColor != null)
            {
                cellstyle.FillForegroundColor = fillForegroundColor.Indexed;
            }
            else
            {
                cellstyle.FillPattern = FillPattern.NoFill;
            }

            if (fillBackgroundColor != null)
            {
                cellstyle.FillBackgroundColor = fillBackgroundColor.Indexed;
            }

            if (font != null)
            {
                cellstyle.SetFont(font);
            }

            //有边框
            if (haveBorder)
            {
                cellstyle.BorderBottom = BorderStyle.Thin;
                cellstyle.BorderLeft   = BorderStyle.Thin;
                cellstyle.BorderRight  = BorderStyle.Thin;
                cellstyle.BorderTop    = BorderStyle.Thin;
            }

            return(cellstyle);
        }
Ejemplo n.º 8
0
        public override void SetFontColor(System.Drawing.Color color)
        {
            IWorkbook  workbook  = _npoiWorksheet.Workbook;
            ICellStyle cellStyle = workbook.CreateCellStyle();

            cellStyle.CloneStyleFrom(_npoiWorksheet.GetColumnStyle(_columnNum));
            if (workbook is HSSFWorkbook)
            {
                HSSFWorkbook hssfWorkbook = (HSSFWorkbook)workbook;
                HSSFPalette  palette      = hssfWorkbook.GetCustomPalette(); //调色板实例

                //palette.SetColorAtIndex((short)8, color.R, color.G, color.B);

                HSSFColor hssFColor = palette.FindSimilarColor(color.R, color.G, color.B);
                IFont     font      = cellStyle.GetFont(workbook);
                font.Color = hssFColor.Indexed;
                cellStyle.SetFont(font);
            }
            else
            {
                //No way!
            }
            _npoiWorksheet.SetDefaultColumnStyle(_columnNum, cellStyle);
        }
Ejemplo n.º 9
0
        protected byte[] GetRGBOrARGB()
        {
            if (IsIndexed && Index > 0)
            {
                int       indexNum  = Index;
                var       hashIndex = HSSFColor.GetIndexHash();
                HSSFColor indexed   = null;
                if (hashIndex.ContainsKey(indexNum))
                {
                    indexed = hashIndex[indexNum];
                }
                if (indexed != null)
                {
                    byte[] rgb = new byte[3];
                    rgb[0] = (byte)indexed.GetTriplet()[0];
                    rgb[1] = (byte)indexed.GetTriplet()[1];
                    rgb[2] = (byte)indexed.GetTriplet()[2];
                    return(rgb);
                }
            }

            // Grab the colour
            return(StoredRBG);
        }
Ejemplo n.º 10
0
        private HSSFFont MatchFont(Font font)
        {
            HSSFColor hssfColor = workbook.GetCustomPalette()
                                  .FindColor((byte)foreground.R, (byte)foreground.G, (byte)foreground.B);

            if (hssfColor == null)
            {
                hssfColor = workbook.GetCustomPalette().FindSimilarColor((byte)foreground.R, (byte)foreground.G, (byte)foreground.B);
            }
            bool     bold     = font.Bold;
            bool     italic   = font.Italic;
            HSSFFont hssfFont = (HSSFFont)workbook.FindFont(bold ? (short)NPOI.SS.UserModel.FontBoldWeight.Bold : (short)NPOI.SS.UserModel.FontBoldWeight.Normal,
                                                            hssfColor.GetIndex(),
                                                            (short)(font.Size * 20),
                                                            font.Name,
                                                            italic,
                                                            false,
                                                            (short)NPOI.SS.UserModel.FontSuperScript.None,
                                                            (byte)NPOI.SS.UserModel.FontUnderlineType.None
                                                            );

            if (hssfFont == null)
            {
                hssfFont             = (HSSFFont)workbook.CreateFont();
                hssfFont.Boldweight  = (short)(bold ? NPOI.SS.UserModel.FontBoldWeight.Bold : 0);
                hssfFont.Color       = (hssfColor.GetIndex());
                hssfFont.FontHeight  = ((short)(font.Size * 20));
                hssfFont.FontName    = font.Name;
                hssfFont.IsItalic    = (italic);
                hssfFont.IsStrikeout = (false);
                hssfFont.TypeOffset  = 0;
                hssfFont.Underline   = 0;
            }

            return(hssfFont);
        }
Ejemplo n.º 11
0
        public void SetColor(int k, int mrow, int mcol, short R, short G, short B)
        {
            k = k - 1;
            string Result = "";

            if (wb == null)
            {
                return;
            }
            ISheet sheet = wb.GetSheetAt(k);

            mrow = mrow - 1;
            mcol = mcol - 1;

            IRow row = sheet.GetRow(mrow);

            if (row == null)
            {
                row = sheet.CreateRow(mrow);
            }
            ICell cell = row.GetCell(mcol); //|| (cell.CellType != CellType.String)

            if ((cell == null))
            {
                cell = row.CreateCell(mcol);
            }
            ICellStyle s = wb.CreateCellStyle();
            //HSSFColor.GetIndexHash();

            HSSFPalette palette   = wb.GetCustomPalette();
            HSSFColor   hssFColor = palette.FindColor((Byte)R, (Byte)G, (Byte)B);

            s.FillForegroundColor = hssFColor.Indexed;
            s.FillPattern         = FillPattern.SolidForeground;
            cell.CellStyle        = s;
        }
Ejemplo n.º 12
0
        public static string GetColor(HSSFColor color)
        {
            StringBuilder stringBuilder = new StringBuilder(7);

            stringBuilder.Append('#');
            foreach (byte s in color.RGB)
            {
                //if (s < 10)
                //    stringBuilder.Append('0');

                stringBuilder.Append(s.ToString("x2"));
            }
            string result = stringBuilder.ToString();

            if (result.Equals("#ffffff"))
            {
                return("white");
            }

            if (result.Equals("#c0c0c0"))
            {
                return("silver");
            }

            if (result.Equals("#808080"))
            {
                return("gray");
            }

            if (result.Equals("#000000"))
            {
                return("black");
            }

            return(result);
        }
Ejemplo n.º 13
0
        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 + "; ");
        }
Ejemplo n.º 14
0
        /**
         * org.Openxmlformats.schemas.spreadsheetml.x2006.main.CTRPrElt to
         * org.Openxmlformats.schemas.Drawingml.x2006.main.CTFont adapter
         */
        private static void ApplyAttributes(CT_RPrElt pr, CT_TextCharacterProperties rPr)
        {
            if (pr.SizeOfBArray() > 0)
            {
                rPr.b = (/*setter*/ pr.GetBArray(0).val);
            }
            if (pr.SizeOfUArray() > 0)
            {
                ST_UnderlineValues u1 = pr.GetUArray(0).val;
                if (u1 == ST_UnderlineValues.single)
                {
                    rPr.u = (/*setter*/ ST_TextUnderlineType.sng);
                }
                else if (u1 == ST_UnderlineValues.@double)
                {
                    rPr.u = (/*setter*/ ST_TextUnderlineType.dbl);
                }
                else if (u1 == ST_UnderlineValues.none)
                {
                    rPr.u = (/*setter*/ ST_TextUnderlineType.none);
                }
            }
            if (pr.SizeOfIArray() > 0)
            {
                rPr.i = (/*setter*/ pr.GetIArray(0).val);
            }

            if (pr.SizeOfRFontArray() > 0)
            {
                CT_TextFont rFont = rPr.IsSetLatin() ? rPr.latin : rPr.AddNewLatin();
                rFont.typeface = (/*setter*/ pr.GetRFontArray(0).val);
            }

            if (pr.SizeOfSzArray() > 0)
            {
                int sz = (int)(pr.GetSzArray(0).val * 100);
                rPr.sz = (/*setter*/ sz);
            }

            if (pr.SizeOfColorArray() > 0)
            {
                CT_SolidColorFillProperties fill = rPr.IsSetSolidFill() ? rPr.solidFill : rPr.AddNewSolidFill();
                NPOI.OpenXmlFormats.Spreadsheet.CT_Color xlsColor = pr.GetColorArray(0);
                if (xlsColor.IsSetRgb())
                {
                    CT_SRgbColor clr = fill.IsSetSrgbClr() ? fill.srgbClr : fill.AddNewSrgbClr();
                    clr.val = (/*setter*/ xlsColor.rgb);
                }
                else if (xlsColor.IsSetIndexed())
                {
                    HSSFColor indexed = (HSSFColor)HSSFColor.GetIndexHash()[((int)xlsColor.indexed)];
                    if (indexed != null)
                    {
                        byte[] rgb = new byte[3];
                        rgb[0] = (byte)indexed.GetTriplet()[0];
                        rgb[1] = (byte)indexed.GetTriplet()[1];
                        rgb[2] = (byte)indexed.GetTriplet()[2];
                        CT_SRgbColor clr = fill.IsSetSrgbClr() ? fill.srgbClr : fill.AddNewSrgbClr();
                        clr.val = (/*setter*/ rgb);
                    }
                }
            }
        }
Ejemplo n.º 15
0
        void BuildStyle_Font(IWorkbook workbook, StringBuilder style,
                             IFont 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;
            }

            if (workbook is HSSFWorkbook)
            {
                HSSFColor fontColor = ((HSSFWorkbook)workbook).GetCustomPalette()?.GetColor(font.Color);
                if (fontColor != null)
                {
                    style.AppendFormat("color:{0}; ", ExcelToHtmlUtils.GetColor(fontColor));
                }
            }
            else
            {
                IndexedColors clr       = IndexedColors.ValueOf(font.Color);
                string        hexstring = null;
                if (clr != null)
                {
                    hexstring = clr.HexString;
                }
                else
                {
                    StylesTable st        = ((XSSFWorkbook)workbook).GetStylesSource();
                    XSSFColor   fontColor = null;
                    if (st != null && st.GetTheme() != null)
                    {
                        fontColor = st.GetTheme().GetThemeColor(font.Color);
                    }
                    else
                    {
                        fontColor = ((XSSFFont)font).GetXSSFColor();
                    }
                    if (fontColor != null)
                    {
                        hexstring = ExcelToHtmlUtils.GetColor(fontColor);
                    }
                }
                if (hexstring != null)
                {
                    style.AppendFormat("color:{0}; ", hexstring);
                }
            }
            if (font.FontHeightInPoints != 0)
            {
                style.Append("font-size: " + font.FontHeightInPoints + "pt; ");
            }

            if (font.IsItalic)
            {
                style.Append("font-style: italic; ");
            }
        }
Ejemplo n.º 16
0
        protected String BuildStyle(IWorkbook workbook, ICellStyle cellStyle)
        {
            StringBuilder style = new StringBuilder();

            if (workbook is HSSFWorkbook)
            {
                HSSFPalette palette = ((HSSFWorkbook)workbook).GetCustomPalette();
                style.Append("white-space: pre-wrap; ");
                ExcelToHtmlUtils.AppendAlign(style, cellStyle.Alignment);

                if (cellStyle.FillPattern == FillPattern.NoFill)
                {
                    // no fill
                }
                else if (cellStyle.FillPattern == FillPattern.SolidForeground)
                {
                    //cellStyle.
                    //HSSFColor.
                    HSSFColor foregroundColor = palette.GetColor(cellStyle.FillForegroundColor);
                    if (foregroundColor != null)
                    {
                        style.AppendFormat("background-color:{0}; ", ExcelToHtmlUtils.GetColor(foregroundColor));
                    }
                }
                else
                {
                    HSSFColor backgroundColor = palette.GetColor(cellStyle.FillBackgroundColor);
                    if (backgroundColor != null)
                    {
                        style.AppendFormat("background-color:{0}; ", ExcelToHtmlUtils.GetColor(backgroundColor));
                    }
                }
            }
            else
            {
                style.Append("white-space: pre-wrap; ");
                ExcelToHtmlUtils.AppendAlign(style, cellStyle.Alignment);

                if (cellStyle.FillPattern == FillPattern.NoFill)
                {
                    // no fill
                }
                else if (cellStyle.FillPattern == FillPattern.SolidForeground)
                {
                    //cellStyle
                    IndexedColors clr       = IndexedColors.ValueOf(cellStyle.FillForegroundColor);
                    string        hexstring = null;
                    if (clr != null)
                    {
                        hexstring = clr.HexString;
                    }
                    else
                    {
                        XSSFColor foregroundColor = (XSSFColor)cellStyle.FillForegroundColorColor;
                        if (foregroundColor != null)
                        {
                            hexstring = ExcelToHtmlUtils.GetColor(foregroundColor);
                        }
                    }
                    if (hexstring != null)
                    {
                        style.AppendFormat("background-color:{0}; ", hexstring);
                    }
                }
                else
                {
                    IndexedColors clr       = IndexedColors.ValueOf(cellStyle.FillBackgroundColor);
                    string        hexstring = null;
                    if (clr != null)
                    {
                        hexstring = clr.HexString;
                    }
                    else
                    {
                        XSSFColor backgroundColor = (XSSFColor)cellStyle.FillBackgroundColorColor;
                        if (backgroundColor != null)
                        {
                            hexstring = ExcelToHtmlUtils.GetColor(backgroundColor);
                        }
                    }
                    if (hexstring != null)
                    {
                        style.AppendFormat("background-color:{0}; ", hexstring);
                    }
                }
            }

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

            IFont font = cellStyle.GetFont(workbook);

            BuildStyle_Font(workbook, style, font);

            return(style.ToString());
        }
Ejemplo n.º 17
0
        //NPOI.HSSF.Util.HSSFColor.BLUE.index
        public static void SetBackColor(this ISheet ISheet1, int rowId, int columnId, HSSFColor color)
        {
            var style1 = ISheet1.Workbook.CreateFillColor(color);

            ISheet1.CreateRow(rowId).CreateCell(columnId).CellStyle = style1;
        }
Ejemplo n.º 18
0
        public static void SetBackColor(this ICell cell, HSSFColor color)
        {
            var style1 = cell.Sheet.Workbook.CreateFillColor(color);

            cell.CellStyle = style1;
        }
Ejemplo n.º 19
0
 public void SetFontColor(HSSFColor color)
 {
     fontColor = color;
 }
Ejemplo n.º 20
0
        public static MemoryStream ProductRenderToExcel(DataTable table, int count)
        {
            MemoryStream ms = new MemoryStream();

            using (table)
            {
                IWorkbook workbook  = new HSSFWorkbook();
                ISheet    sheet     = workbook.CreateSheet();
                IRow      headerRow = sheet.CreateRow(0);

                // handling header.
                HSSFColor color = new HSSFColor();
                foreach (DataColumn column in table.Columns)
                {
                    headerRow.CreateCell(column.Ordinal).SetCellValue(column.Caption);//If Caption not set, returns the ColumnName value

                    headerRow.Cells[column.Ordinal].CellStyle = GetCellStyle(workbook,
                                                                             GetFontStyle(workbook, "宋体", color, 12), null, FillPatternType.NO_FILL, null, HorizontalAlignment.CENTER, VerticalAlignment.CENTER);
                }

                // handling value.
                int rowIndex = 1;
                foreach (DataRow row in table.Rows)
                {
                    if (rowIndex == count)
                    {
                        IRow dataRow = sheet.CreateRow(rowIndex + 5);
                        foreach (DataColumn column in table.Columns)
                        {
                            dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
                            dataRow.Cells[column.Ordinal].CellStyle = GetCellStyle(workbook, null, null,
                                                                                   FillPatternType.NO_FILL, null,
                                                                                   HorizontalAlignment.LEFT,
                                                                                   VerticalAlignment.CENTER);
                        }
                        rowIndex += 6;
                    }
                    else if (rowIndex > count)
                    {
                        IRow dataRow = sheet.CreateRow(rowIndex);
                        foreach (DataColumn column in table.Columns)
                        {
                            dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
                            dataRow.Cells[column.Ordinal].CellStyle = GetCellStyle(workbook, null, null,
                                                                                   FillPatternType.NO_FILL, null,
                                                                                   HorizontalAlignment.LEFT,
                                                                                   VerticalAlignment.CENTER);
                            dataRow.Cells[column.Ordinal].CellStyle.WrapText = true;
                        }
                        dataRow.Height = 150 * 20;
                    }
                    else
                    {
                        IRow dataRow = sheet.CreateRow(rowIndex);
                        foreach (DataColumn column in table.Columns)
                        {
                            dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
                            dataRow.Cells[column.Ordinal].CellStyle = GetCellStyle(workbook, null, null,
                                                                                   FillPatternType.NO_FILL, null,
                                                                                   HorizontalAlignment.LEFT,
                                                                                   VerticalAlignment.CENTER);
                        }

                        rowIndex++;
                    }
                }
                AutoSizeColumns(sheet);
                sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 1, 2));
                sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 8, 17));
                sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 21, 22));
                sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 32, 34));
                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;
            }
            return(ms);
        }
Ejemplo n.º 21
0
 IndexedColors(int idx, HSSFColor color)
 {
     index          = idx;
     this.hssfColor = color;
 }
Ejemplo n.º 22
0
        /// <summary>
        /// 返回单元格样式
        /// </summary>
        /// <param name="workbook"></param>
        /// <param name="fontSize"></param>
        /// <param name="horizontalAlignment"></param>
        /// <param name="verticalAlignment"></param>
        /// <param name="color"></param>
        /// <returns></returns>
        private static ICellStyle GetCellStyle(IWorkbook workbook, double fontSize, HorizontalAlignment horizontalAlignment, VerticalAlignment verticalAlignment, HSSFColor color = null)
        {
            var style = workbook.CreateCellStyle();
            var font  = workbook.CreateFont();

            font.FontHeightInPoints = fontSize;
            if (color != null)
            {
                font.Color = color.Indexed;
            }
            style.VerticalAlignment = verticalAlignment;
            style.Alignment         = horizontalAlignment;
            style.SetFont(font);
            return(style);
        }
Ejemplo n.º 23
0
        public static short ConvertToColor(this string v)
        {
            int r = Convert.ToInt32("0x" + v.Substring(1, 2), 16);

            int g = Convert.ToInt32("0x" + v.Substring(3, 2), 16);

            int         b         = Convert.ToInt32("0x" + v.Substring(5, 2), 16);
            HSSFPalette mypalette = new HSSFPalette(new PaletteRecord());
            HSSFColor   hssFColor = mypalette.FindColor((Byte)r, (Byte)g, (Byte)(b));

            return(hssFColor.Indexed);

            /*switch (v)
             * {
             *  case "AQUA":
             *      return 49;
             *
             *  case "AUTOMATIC":
             *      return 64;
             *
             *  case "BLACK":
             *      return 8;
             *
             *  case "BLUE":
             *      return 39;
             *
             *  case "BLUE_GREY":
             *  case "BLUEGREY":
             *      return 54;
             *
             *  case "BRIGHT_GREEN":
             *  case "BRIGHTGREEN":
             *      return 35;
             *
             *  case "BROWN":
             *      return 60;
             *
             *  case "CORAL":
             *      return 29;
             *
             *  case "CORNFLOWER_BLUE":
             *  case "CORNFLOWERBLUE":
             *      return 24;
             *
             *  case "DARK_BLUE":
             *  case "DARKBLUE":
             *      return 32;
             *
             *  case "DARK_GREEN":
             *  case "DARKGREEN":
             *      return 58;
             *  case "DARK_RED":
             *  case "DARKRED":
             *      return 37;
             *
             *  case "DARK_TEAL":
             *  case "DARKTEAL":
             *      return 56;
             *
             *  case "DARK_YELLOW":
             *  case "DARKYELLOW":
             *      return 19;
             *
             *  case "GOLD":
             *      return 51;
             *
             *  case "GREEN":
             *      return 17;
             *  case "GREY_25_PERCENT":
             *  case "GREY25PERCENT":
             *      return 22;
             *
             *  case "GREY_40_PERCENT":
             *  case "GREY40PERCENT":
             *      return 55;
             *
             *  case "GREY_50_PERCENT":
             *  case "GREY50PERCENT":
             *      return 23;
             *
             *  case "GREY_80_PERCENT":
             *  case "GREY80PERCENT":
             *      return 63;
             *
             *  case "INDIGO":
             *      return 62;
             *
             *  case "LAVENDER":
             *      return 46;
             *
             *  case "LEMON_CHIFFON":
             *  case "LEMONCHIFFON":
             *      return 26;
             *
             *  case "LIGHT_BLUE":
             *  case "LIGHTBLUE":
             *      return 48;
             *
             *  case "LIGHT_CORNFLOWERBLUE":
             *  case "LIGHTCORNFLOWERBLUE":
             *      return 31;
             *
             *  case "LIGHT_GREEN":
             *  case "LIGHTGREEN":
             *      return 42;
             *
             *  case "LIGHT_ORANGE":
             *  case "LIGHTORANGE":
             *      return 52;
             *
             *  case "LIGHT_TURQUOISE":
             *  case "LIGHTTURQUOISE":
             *      return 27;
             *
             *  case "LIGHT_YELLOW":
             *  case "LIGHTYELLOW":
             *      return 43;
             *
             *  case "LIME":
             *      return 50;
             *
             *  case "MAROON":
             *      return 25;
             *
             *  case "OLIVE_GREEN":
             *  case "OLIVEGREEN":
             *      return 59;
             *
             *  case "ORANGE":
             *      return 53;
             *
             *  case "ORCHID":
             *      return 28;
             *
             *  case "PALE_BLUE":
             *  case "PALEBLUE":
             *      return 44;
             *
             *  case "PINK":
             *      return 33;
             *
             *  case "PLUM":
             *      return 25;
             *
             *  case "RED":
             *      return 10;
             *
             *  case "ROSE":
             *      return 45;
             *
             *  case "ROYAL_BLUE":
             *  case "ROYALBLUE":
             *      return 30;
             *
             *  case "SEA_GREEN":
             *  case "SEAGREEN":
             *      return 57;
             *
             *  case "SKY_BLUE":
             *  case "SKYBLUE":
             *      return 40;
             *
             *  case "TAN":
             *      return 47;
             *
             *  case "TEAL":
             *      return 38;
             *
             *  case "TURQUOISE":
             *      return 35;
             *
             *  case "VIOLET":
             *      return 36;
             *
             *  case "WHITE":
             *      return 9;
             *
             *  case "YELLOW":
             *      return 34;
             *
             *  default:
             *      return 32767;
             * };*/
        }
Ejemplo n.º 24
0
        /// <summary>  
        /// 导出Excel  
        /// </summary>  
        /// <param name="lists"></param>  
        /// <param name="head">中文列名对照</param>  
        /// <param name="fileName">文件名</param>  
        public static void ExportExcel(List <T> lists, Dictionary <string, string> head, string fileName)
        {
            HSSFWorkbook workbook  = new HSSFWorkbook();
            MemoryStream ms        = new MemoryStream();
            HSSFSheet    sheet     = null;
            HSSFRow      headerRow = null;

            //设置表头样式
            ICellStyle headStyle = workbook.CreateCellStyle();

            headStyle.VerticalAlignment = VerticalAlignment.CENTER;
            headStyle.Alignment         = HorizontalAlignment.CENTER;
            IFont font = workbook.CreateFont();

            font.FontHeight = 14 * 14;
            font.Boldweight = 1000;
            headStyle.SetFont(font);

            //设置分组样式
            HSSFPalette palette = workbook.GetCustomPalette(); //调色板实例

            palette.SetColorAtIndex((byte)8, (byte)204, (byte)204, (byte)0);
            HSSFColor hssFColor = palette.FindColor((byte)204, (byte)204, (byte)0);

            ICellStyle GroupStyle = workbook.CreateCellStyle();

            GroupStyle.FillPattern         = FillPatternType.SOLID_FOREGROUND;
            GroupStyle.FillForegroundColor = hssFColor.GetIndex();

            IFont Groupfont = workbook.CreateFont();

            Groupfont.Boldweight = 1000;
            GroupStyle.SetFont(Groupfont);

            bool h = false;

            Type type = typeof(T);

            PropertyInfo[] properties = type.GetProperties();

            var mod   = lists.Count() % 65535;
            var index = lists.Count() / 65535;

            if (mod > 0)
            {
                index = index + 1;
            }

            //没有数据时导出表头
            if (index == 0)
            {
                int i = 0;
                sheet     = workbook.CreateSheet("Sheet0") as HSSFSheet;
                headerRow = sheet.CreateRow(0) as HSSFRow;
                foreach (var dic in head)//循环列表头集合作为列数
                {
                    string[] names   = dic.Key.ToString().Split('@');
                    string   colname = dic.Value.ToString();

                    ICell cell = headerRow.CreateCell(i);
                    cell.CellStyle = headStyle;
                    cell.SetCellValue(colname);

                    i++;
                }
            }

            for (int idx = 1; idx <= index; idx++)
            {
                sheet     = workbook.CreateSheet("Sheet" + idx) as HSSFSheet;
                headerRow = sheet.CreateRow(0) as HSSFRow;
                var count = 65535;
                if (idx == index)
                {
                    count = mod;
                }
                for (var j = 0; j < count; j++)//循环记录总数作为行数
                {
                    HSSFRow dataRow = sheet.CreateRow(j + 1) as HSSFRow;
                    int     i       = 0;
                    foreach (var dic in head)//循环列表头集合作为列数
                    {
                        string[] names   = dic.Key.ToString().Split('@');
                        string   colname = dic.Value.ToString();
                        string   name    = names[0];
                        bool     isTrue  = false;//是否基础数据
                        if (names.Length == 2)
                        {
                            isTrue = bool.Parse(names[1]);
                        }
                        var    info     = properties.Where(x => x.Name == name).FirstOrDefault();
                        object value    = info == null ? null : info.GetValue(lists[65535 * (idx - 1) + j], null);
                        string colvalue = value == null ? "" : value.ToString();
                        //if (isTrue)//获取基础数据
                        //{
                        //    colvalue = HtmlExtensions.GetBasicObjectNameByValue(colvalue);
                        //}
                        if (!h)
                        {
                            if ((!name.Equals("IsGroup")))
                            {
                                ICell cell = headerRow.CreateCell(i);
                                cell.CellStyle = headStyle;
                                cell.SetCellValue(colname);
                                if (value != null)
                                {
                                    Type   t    = value.GetType();
                                    string strt = t.Name;
                                    if (t.Name == "Nullable`1")
                                    {
                                        strt = t.GetGenericArguments()[0].Name;
                                    }
                                    switch (strt)
                                    {
                                    case "Decimal":
                                    case "Double":
                                        dataRow.CreateCell(i).SetCellValue(Double.Parse(value.ToString()));
                                        break;

                                    case "Int":
                                        dataRow.CreateCell(i).SetCellValue(int.Parse(value.ToString()));
                                        break;

                                    case "Float":
                                        dataRow.CreateCell(i).SetCellValue(float.Parse(value.ToString()));
                                        break;

                                    default:
                                        dataRow.CreateCell(i).SetCellValue(colvalue);
                                        break;
                                    }
                                }
                                else
                                {
                                    dataRow.CreateCell(i).SetCellValue(colvalue);
                                }
                            }
                        }
                        else
                        {
                            if ((!name.Equals("IsGroup")))
                            {
                                if (value != null)
                                {
                                    Type   t    = value.GetType();
                                    string strt = t.Name;
                                    if (t.Name == "Nullable`1")
                                    {
                                        strt = t.GetGenericArguments()[0].Name;
                                    }
                                    switch (strt)
                                    {
                                    case "Decimal":
                                    case "Double":
                                        dataRow.CreateCell(i).SetCellValue(Double.Parse(value.ToString()));
                                        break;

                                    case "Int":
                                        dataRow.CreateCell(i).SetCellValue(int.Parse(value.ToString()));
                                        break;

                                    case "Float":
                                        dataRow.CreateCell(i).SetCellValue(float.Parse(value.ToString()));
                                        break;

                                    default:
                                        dataRow.CreateCell(i).SetCellValue(colvalue);
                                        break;
                                    }
                                }
                                else
                                {
                                    dataRow.CreateCell(i).SetCellValue(colvalue);
                                }
                            }
                        }
                        //========================对特定标志行设置颜色========================
                        if (name.Equals("IsGroup") && colvalue.Equals("Y"))
                        {
                            for (int m = 0; m < i; m++)
                            {
                                dataRow.GetCell(m).CellStyle = GroupStyle;
                            }
                        }
                        //================================end=================================
                        i++;
                    }
                    h = true;
                }
            }

            workbook.Write(ms);
            ms.Flush();
            ms.Position = 0;
            sheet       = null;
            headerRow   = null;
            workbook    = null;

            using (FileStream fs = new FileStream(fileName, FileMode.Create, FileAccess.Write))
            {
                byte[] data = ms.ToArray();
                fs.Write(data, 0, data.Length);
                fs.Flush();
            }

            ms.Close();
            ms.Dispose();
        }
Ejemplo n.º 25
0
        /// <summary>
        /// 获取字体样式
        /// </summary>
        /// <param name="hssfworkbook">Excel操作类</param>
        /// <param name="fontname">字体名</param>
        /// <param name="fontcolor">字体颜色</param>
        /// <param name="fontsize">字体大小</param>
        /// <returns></returns>
        public static IFont GetFontStyle(HSSFWorkbook hssfworkbook, string fontfamily, HSSFColor fontcolor, int fontsize)
        {
            IFont font1 = hssfworkbook.CreateFont();

            if (string.IsNullOrEmpty(fontfamily))
            {
                font1.FontName = fontfamily;
            }
            if (fontcolor != null)
            {
                font1.Color = fontcolor.Indexed; //.GetIndex();
            }
            font1.IsItalic           = true;
            font1.FontHeightInPoints = (short)fontsize;
            return(font1);
        }
Ejemplo n.º 26
0
 public void SetForegroundColor(HSSFColor color)
 {
     this.foregroundColor = color;
 }
Ejemplo n.º 27
0
 public void Compare(HSSFColor expected, HSSFColor palette)
 {
     Assert.AreEqual(expected.GetHexString(), palette.GetHexString());
 }
Ejemplo n.º 28
0
 public void SetBorderColor(HSSFColor topBorderColor, HSSFColor bottomBorderColor, HSSFColor leftBorderColor, HSSFColor rightBorderColor)
 {
     this.topBorderColor    = topBorderColor;
     this.bottomBorderColor = bottomBorderColor;
     this.leftBorderColor   = leftBorderColor;
     this.rightBorderColor  = rightBorderColor;
 }
Ejemplo n.º 29
0
        static CellFormatPart()
        {
            NAMED_COLORS = new Dictionary <String, Color>(CASE_INSENSITIVE_ORDER);

            Hashtable colors = HSSFColor.GetIndexHash();

            foreach (object v in colors.Values)
            {
                HSSFColor hc   = (HSSFColor)v;
                Type      type = hc.GetType();
                String    name = type.Name;
                if (name.Equals(name.ToUpper()))
                {
                    short[] rgb = hc.GetTriplet();
                    Color   c   = Color.FromArgb(rgb[0], rgb[1], rgb[2]);
                    if (!NAMED_COLORS.ContainsKey(name))
                    {
                        NAMED_COLORS.Add(name, c);
                    }
                    if (name.IndexOf('_') > 0)
                    {
                        if (!NAMED_COLORS.ContainsKey(name.Replace('_', ' ')))
                        {
                            NAMED_COLORS.Add(name.Replace('_', ' '), c);
                        }
                    }
                    if (name.IndexOf("_PERCENT") > 0)
                    {
                        if (!NAMED_COLORS.ContainsKey(name.Replace("_PERCENT", "%").Replace('_', ' ')))
                        {
                            NAMED_COLORS.Add(name.Replace("_PERCENT", "%").Replace('_', ' '), c);
                        }
                    }
                }
            }
            // A condition specification
            String condition = "([<>=]=?|!=|<>)    # The operator\n" +
                               "  \\s*([0-9]+(?:\\.[0-9]*)?)\\s*  # The constant to test against\n";

            String color =
                "\\[(black|blue|cyan|green|magenta|red|white|yellow|color [0-9]+)\\]";

            // A number specification
            // Note: careful that in something like ##, that the trailing comma is not caught up in the integer part

            // A part of a specification
            String part = "\\\\.                 # Quoted single character\n" +
                          "|\"([^\\\\\"]|\\\\.)*\"         # Quoted string of characters (handles escaped quotes like \\\") \n" +
                          "|_.                             # Space as wide as a given character\n" +
                          "|\\*.                           # Repeating fill character\n" +
                          "|@                              # Text: cell text\n" +
                          "|([0?\\#](?:[0?\\#,]*))         # Number: digit + other digits and commas\n" +
                          "|e[-+]                          # Number: Scientific: Exponent\n" +
                          "|m{1,5}                         # Date: month or minute spec\n" +
                          "|d{1,4}                         # Date: day/date spec\n" +
                          "|y{2,4}                         # Date: year spec\n" +
                          "|h{1,2}                         # Date: hour spec\n" +
                          "|s{1,2}                         # Date: second spec\n" +
                          "|am?/pm?                        # Date: am/pm spec\n" +
                          "|\\[h{1,2}\\]                   # Elapsed time: hour spec\n" +
                          "|\\[m{1,2}\\]                   # Elapsed time: minute spec\n" +
                          "|\\[s{1,2}\\]                   # Elapsed time: second spec\n" +
                          "|[^;]                           # A character\n" + "";

            String format = "(?:" + color + ")?                  # Text color\n" +
                            "(?:\\[" + condition + "\\])?                # Condition\n" +
                            "((?:" + part + ")+)                        # Format spec\n";

            RegexOptions flags = RegexOptions.IgnoreCase | RegexOptions.IgnorePatternWhitespace | RegexOptions.Compiled;

            COLOR_PAT         = new Regex(color, flags);
            CONDITION_PAT     = new Regex(condition, flags);
            SPECIFICATION_PAT = new Regex(part, flags);
            FORMAT_PAT        = new Regex(format, flags);

            // Calculate the group numbers of important groups.  (They shift around
            // when the pattern is Changed; this way we figure out the numbers by
            // experimentation.)

            COLOR_GROUP = FindGroup(FORMAT_PAT, "[Blue]@", "Blue");
            CONDITION_OPERATOR_GROUP = FindGroup(FORMAT_PAT, "[>=1]@", ">=");
            CONDITION_VALUE_GROUP    = FindGroup(FORMAT_PAT, "[>=1]@", "1");
            SPECIFICATION_GROUP      = FindGroup(FORMAT_PAT, "[Blue][>1]\\a ?", "\\a ?");
        }
Ejemplo n.º 30
0
 public void SetBackgroundColor(HSSFColor color)
 {
     this.backgroundColor = color;
 }