C# XSSFWorkbook.CreateCellStyle方法代码示例

        /// <summary>
        /// DataTable导出到Excel的MemoryStream
        /// </summary>
        /// <param name="dtSource">源DataTable</param>
        /// <param name="strHeaderText">表头文本</param>
        static void ExportDTI(DataTable dtSource, string strHeaderText, FileStream fs)
        {
            XSSFWorkbook workbook = new XSSFWorkbook();
            XSSFSheet sheet = workbook.CreateSheet() as XSSFSheet;

            #region 右击文件 属性信息

            //{
            //    DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
            //    dsi.Company = "http://www.yongfa365.com/";
            //    workbook.DocumentSummaryInformation = dsi;

            //    SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
            //    si.Author = "柳永法"; //填加xls文件作者信息
            //    si.ApplicationName = "NPOI测试程序"; //填加xls文件创建程序信息
            //    si.LastAuthor = "柳永法2"; //填加xls文件最后保存者信息
            //    si.Comments = "说明信息"; //填加xls文件作者信息
            //    si.Title = "NPOI测试"; //填加xls文件标题信息
            //    si.Subject = "NPOI测试Demo"; //填加文件主题信息
            //    si.CreateDateTime = DateTime.Now;
            //    workbook.SummaryInformation = si;
            //}

            #endregion

            XSSFCellStyle dateStyle = workbook.CreateCellStyle() as XSSFCellStyle;
            XSSFDataFormat format = workbook.CreateDataFormat() as XSSFDataFormat;
            dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");

            //取得列宽
            int[] arrColWidth = new int[dtSource.Columns.Count];
            foreach (DataColumn item in dtSource.Columns)
            {
                arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
            }
            for (int i = 0; i < dtSource.Rows.Count; i++)
            {
                for (int j = 0; j < dtSource.Columns.Count; j++)
                {
                    int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
                    if (intTemp > arrColWidth[j])
                    {
                        arrColWidth[j] = intTemp;
                    }
                }
            }
            int rowIndex = 0;

            foreach (DataRow row in dtSource.Rows)
            {
                #region 新建表，填充表头，填充列头，样式

                if (rowIndex == 0)
                {
                    #region 表头及样式
                    //{
                    //    XSSFRow headerRow = sheet.CreateRow(0) as XSSFRow;
                    //    headerRow.HeightInPoints = 25;
                    //    headerRow.CreateCell(0).SetCellValue(strHeaderText);

                    //    XSSFCellStyle headStyle = workbook.CreateCellStyle() as XSSFCellStyle;
                    //    headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
                    //    XSSFFont font = workbook.CreateFont() as XSSFFont;
                    //    font.FontHeightInPoints = 20;
                    //    font.Boldweight = 700;
                    //    headStyle.SetFont(font);

                    //    headerRow.GetCell(0).CellStyle = headStyle;

                    //    //sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1));
                    //    //headerRow.Dispose();
                    //}

                    #endregion


                    #region 列头及样式

                    {
                        XSSFRow headerRow = sheet.CreateRow(0) as XSSFRow;


                        XSSFCellStyle headStyle = workbook.CreateCellStyle() as XSSFCellStyle;
                        headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
                        XSSFFont font = workbook.CreateFont() as XSSFFont;
                        font.FontHeightInPoints = 10;
                        font.Boldweight = 700;
                        headStyle.SetFont(font);


                        foreach (DataColumn column in dtSource.Columns)
                        {
                            headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
                            headerRow.GetCell(column.Ordinal).CellStyle = headStyle;

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

        static void Main(string[] args)
        {
            IWorkbook wb = new XSSFWorkbook();

            // Create a Worksheet
            ISheet ws = wb.CreateSheet("Sheet1");


            // Aqua background
            ICellStyle style = wb.CreateCellStyle();
            style.FillBackgroundColor = IndexedColors.Aqua.Index;
            style.FillPattern = FillPattern.BigSpots;

            IRow row = ws.CreateRow(0);
            ICell cell = row.CreateCell(1);
            cell.SetCellValue("X");
            cell.CellStyle = style;            

            // Orange "foreground", foreground being the fill foreground not the font color.
            style = wb.CreateCellStyle();
            style.FillBackgroundColor = IndexedColors.Orange.Index;
            style.FillPattern = FillPattern.SolidForeground;
           
            cell = row.CreateCell(2);
            cell.SetCellValue("X");
            cell.CellStyle = style;

            FileStream sw = File.Create("test.xlsx");
            wb.Write(sw);
            sw.Close();

            
        }

        static void Main(string[] args)
        {
            IWorkbook workbook = new XSSFWorkbook();
            ISheet s1 = workbook.CreateSheet("Sheet1");

            ICellStyle rowstyle = workbook.CreateCellStyle();
            rowstyle.FillForegroundColor = IndexedColors.Red.Index;
            rowstyle.FillPattern = FillPattern.SolidForeground;

            ICellStyle c1Style = workbook.CreateCellStyle();
            c1Style.FillForegroundColor = IndexedColors.Yellow.Index;
            c1Style.FillPattern = FillPattern.SolidForeground;

            IRow r1 = s1.CreateRow(1);
            IRow r2= s1.CreateRow(2);
            r1.RowStyle = rowstyle;
            r2.RowStyle = rowstyle;

            ICell c1 = r2.CreateCell(2);
            c1.CellStyle = c1Style;
            c1.SetCellValue("Test");

            ICell c4 = r2.CreateCell(4);
            c4.CellStyle = c1Style;

            using(var fs=File.Create("test.xlsx"))
            {
                workbook.Write(fs);
            }
        }

 static void Main(string[] args)
 {
     IWorkbook workbook = new XSSFWorkbook();
     ISheet sheet1 = workbook.CreateSheet("Sheet1");
     int x = 1;
     for (int i = 0; i < 15; i++)
     {
         IRow row = sheet1.CreateRow(i);
         for (int j = 0; j < 15; j++)
         {
             ICell cell = row.CreateCell(j);
             if (x % 2 == 0)
             {
                 //fill background with blue
                 ICellStyle style1 = workbook.CreateCellStyle();
                 style1.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.BLUE.index2;
                 style1.FillPattern = FillPatternType.SOLID_FOREGROUND;
                 cell.CellStyle = style1;
             }
             else
             {
                 //fill background with yellow
                 ICellStyle style1 = workbook.CreateCellStyle();
                 style1.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.YELLOW.index2;
                 style1.FillPattern = FillPatternType.SOLID_FOREGROUND;
                 cell.CellStyle = style1;
             }
             x++;
         }
     }
     FileStream sw = File.Create("test.xlsx");
     workbook.Write(sw);
     sw.Close();
 }

        static void Main(string[] args)
        {
            IWorkbook workbook = new XSSFWorkbook();
            ISheet sheet1 = workbook.CreateSheet("Sheet1");

            //font style1: underlined, italic, red color, fontsize=20
            IFont font1 = workbook.CreateFont();
            font1.Color = IndexedColors.Red.Index;
            font1.IsItalic = true;
            font1.Underline = FontUnderlineType.Double;
            font1.FontHeightInPoints = 20;

            //bind font with style 1
            ICellStyle style1 = workbook.CreateCellStyle();
            style1.SetFont(font1);

            //font style2: strikeout line, green color, fontsize=15, fontname='宋体'
            IFont font2 = workbook.CreateFont();
            font2.Color = IndexedColors.OliveGreen.Index;
            font2.IsStrikeout = true;
            font2.FontHeightInPoints = 15;
            font2.FontName = "宋体";

            //bind font with style 2
            ICellStyle style2 = workbook.CreateCellStyle();
            style2.SetFont(font2);

            //apply font styles
            ICell cell1 = sheet1.CreateRow(1).CreateCell(1);
            cell1.SetCellValue("Hello World!");
            cell1.CellStyle = style1;
            ICell cell2 = sheet1.CreateRow(3).CreateCell(1);
            cell2.SetCellValue("早上好！");
            cell2.CellStyle = style2;

            ////cell with rich text 
            ICell cell3 = sheet1.CreateRow(5).CreateCell(1);
            XSSFRichTextString richtext = new XSSFRichTextString("Microsoft OfficeTM");

            //apply font to "Microsoft Office"
            IFont font4 = workbook.CreateFont();
            font4.FontHeightInPoints = 12;
            richtext.ApplyFont(0, 16, font4);
            //apply font to "TM"
            IFont font3 = workbook.CreateFont();
            font3.TypeOffset = FontSuperScript.Super;
            font3.IsItalic = true;
            font3.Color = IndexedColors.Blue.Index;
            font3.FontHeightInPoints = 8;
            richtext.ApplyFont(16, 18, font3);

            cell3.SetCellValue(richtext);

            FileStream sw = File.Create("test.xlsx");
            workbook.Write(sw);
            sw.Close();
        }

        static void Main(string[] args)
        {
            IWorkbook wb = new XSSFWorkbook();

            // Create a Worksheet
            ISheet ws = wb.CreateSheet("Sheet1");

            ICellStyle style = wb.CreateCellStyle();

            //Setting the line of the top border
            style.BorderTop = BorderStyle.Thick;
            style.TopBorderColor = 256;

            style.BorderLeft = BorderStyle.Thick;
            style.LeftBorderColor = 256;

            style.BorderRight = BorderStyle.Thick;
            style.RightBorderColor = 256;

            style.BorderBottom = BorderStyle.Thick;
            style.BottomBorderColor = 256;

            IRow row = ws.CreateRow(0);
            ICell cell = row.CreateCell(1);
            cell.CellStyle = style;

            FileStream sw = File.Create("test.xlsx");
            wb.Write(sw);
            sw.Close();
        }

        static void Main(string[] args)
        {
            IWorkbook wb = new XSSFWorkbook();

            // Create a Worksheet
            ISheet ws = wb.CreateSheet("Sheet1");

            // Create a new font and alter it
            IFont font = wb.CreateFont();
            font.FontHeightInPoints = 24;
            font.FontName = "Courier New";
            font.IsItalic = true;
            font.IsStrikeout = true;            

            // Fonts are set into a style so create a new one to use.
            ICellStyle style = wb.CreateCellStyle();
            style.SetFont(font);

            IRow row = ws.CreateRow(0);

            // Create a cell and put a value in it.
            ICell cell = row.CreateCell(1);
            cell.SetCellValue("Thisi s a test of fonts");
            cell.CellStyle = style;

            FileStream sw = File.Create("test.xlsx");
            wb.Write(sw);
            sw.Close();
        }

        static void Main(string[] args)
        {
            IWorkbook workbook = new XSSFWorkbook();

            ISheet sheet = workbook.CreateSheet("Sheet1");
            //increase the width of Column A
            sheet.SetColumnWidth(0, 5000);
            //create the format instance
            IDataFormat format = workbook.CreateDataFormat();

            // Create a row and put some cells in it. Rows are 0 based.
            ICell cell = sheet.CreateRow(0).CreateCell(0);
            //number format with 2 digits after the decimal point - "1.20"
            SetValueAndFormat(workbook, cell, 1.2, HSSFDataFormat.GetBuiltinFormat("0.00"));
            
            //RMB currency format with comma    -   "¥20,000"
            ICell cell2 = sheet.CreateRow(1).CreateCell(0);
            SetValueAndFormat(workbook, cell2, 20000, format.GetFormat("¥#,##0"));

            //scentific number format   -   "3.15E+00"
            ICell cell3 = sheet.CreateRow(2).CreateCell(0);
            SetValueAndFormat(workbook, cell3, 3.151234, format.GetFormat("0.00E+00"));

            //percent format, 2 digits after the decimal point    -  "99.33%"
            ICell cell4 = sheet.CreateRow(3).CreateCell(0);
            SetValueAndFormat(workbook, cell4, 0.99333, format.GetFormat("0.00%"));

            //phone number format - "021-65881234"
            ICell cell5 = sheet.CreateRow(4).CreateCell(0);
            SetValueAndFormat(workbook, cell5, 02165881234,format.GetFormat("000-00000000"));

            //Chinese capitalized character number - 壹贰叁 元
            ICell cell6 = sheet.CreateRow(5).CreateCell(0);
            SetValueAndFormat(workbook, cell6, 123, format.GetFormat("[DbNum2][$-804]0 元"));

            //Chinese date string
            ICell cell7 = sheet.CreateRow(6).CreateCell(0);
            SetValueAndFormat(workbook, cell7, new DateTime(2004, 5, 6), format.GetFormat("yyyy年m月d日"));
            cell7.SetCellValue(new DateTime(2004, 5, 6));

            //Chinese date string
            ICell cell8 = sheet.CreateRow(7).CreateCell(0);
            SetValueAndFormat(workbook,cell8,new DateTime(2005, 11, 6),format.GetFormat("yyyy年m月d日"));

            //formula value with datetime style 
            ICell cell9 = sheet.CreateRow(8).CreateCell(0);
            cell9.CellFormula = "DateValue(\"2005-11-11\")+TIMEVALUE(\"11:11:11\")";
            ICellStyle cellStyle9 = workbook.CreateCellStyle();
            cellStyle9.DataFormat = HSSFDataFormat.GetBuiltinFormat("m/d/yy h:mm");
            cell9.CellStyle = cellStyle9;

            //display current time
            ICell cell10 = sheet.CreateRow(9).CreateCell(0);
            SetValueAndFormat(workbook, cell10, DateTime.Now, format.GetFormat("[$-409]h:mm:ss AM/PM;@"));

            FileStream sw = File.Create("test.xlsx");
            workbook.Write(sw);
            sw.Close();
        }

        static void Main(string[] args)
        {
            IWorkbook workbook = new XSSFWorkbook();
            ////cell style for hyperlinks
            ////by default hyperlinks are blue and underlined
            ICellStyle hlink_style = workbook.CreateCellStyle();
            IFont hlink_font = workbook.CreateFont();
            hlink_font.Underline = (byte)FontUnderlineType.SINGLE;
            hlink_font.Color = HSSFColor.BLUE.index;
            hlink_style.SetFont(hlink_font);

            ICell cell;
            ISheet sheet = workbook.CreateSheet("Hyperlinks");

            //URL
            cell = sheet.CreateRow(0).CreateCell(0);
            cell.SetCellValue("URL Link");
            XSSFHyperlink link = new XSSFHyperlink(HyperlinkType.URL);
            link.Address = ("http://poi.apache.org/");
            cell.Hyperlink = (link);
            cell.CellStyle = (hlink_style);

            //link to a file in the current directory
            cell = sheet.CreateRow(1).CreateCell(0);
            cell.SetCellValue("File Link");
            link = new XSSFHyperlink(HyperlinkType.FILE);
            link.Address = ("link1.xls");
            cell.Hyperlink = (link);
            cell.CellStyle = (hlink_style);

            //e-mail link
            cell = sheet.CreateRow(2).CreateCell(0);
            cell.SetCellValue("Email Link");
            link = new XSSFHyperlink(HyperlinkType.EMAIL);
            //note, if subject contains white spaces, make sure they are url-encoded
            link.Address = ("mailto:[email protected]?subject=Hyperlinks");
            cell.Hyperlink = (link);
            cell.CellStyle = (hlink_style);

            //link to a place in this workbook

            //Create a target sheet and cell
            ISheet sheet2 = workbook.CreateSheet("Target ISheet");
            sheet2.CreateRow(0).CreateCell(0).SetCellValue("Target ICell");

            cell = sheet.CreateRow(3).CreateCell(0);
            cell.SetCellValue("Worksheet Link");
            link = new XSSFHyperlink(HyperlinkType.DOCUMENT);
            link.Address = ("'Target ISheet'!A1");
            cell.Hyperlink = (link);
            cell.CellStyle = (hlink_style);

            FileStream sw = File.Create("test.xlsx");
            workbook.Write(sw);
            sw.Close();
        }

        /// <summary>
        /// 读取DataTable的数据,并把数据写入excel
        /// </summary>
        /// <param name="filePath">文件路径</param>
        /// <param name="fileName">文件名</param>
        /// <param name="sheetName">excel里第一个sheet的名称</param>
        /// <param name="DataTable1">要写入的DataTable</param>
        /// <param name="freezePaneRow">冻结拆分窗口的行数</param>
        /// <returns></returns>
        public string createExcel(string filePath, string fileName, string sheetName, DataTable DataTable1, int freezePaneRow)
        {
            string result = null;
            try
            {
                XSSFWorkbook xssfworkbook = new XSSFWorkbook();
                ISheet sheet = xssfworkbook.CreateSheet(sheetName);

                //设置居中
                ICellStyle styleCell = xssfworkbook.CreateCellStyle();
                styleCell.VerticalAlignment = VerticalAlignment.Center;
                styleCell.Alignment = HorizontalAlignment.Center;

                for (int i = 0; i < DataTable1.Rows.Count; i++)
                {
                    IRow row1 = sheet.CreateRow(i);
                    for (int j = 0; j < DataTable1.Columns.Count; j++)
                    {
                        ICell cell = row1.CreateCell(j);
                        cell.SetCellValue(DataTable1.Rows[i][j].ToString());
                        sheet.GetRow(i).GetCell(j).CellStyle = styleCell;
                    }
                }

                //自适应列宽
                for (int i = 0; i < DataTable1.Columns.Count; i++) {
                    sheet.AutoSizeColumn(i);
                }
                //冻结拆分窗口
                sheet.CreateFreezePane(1, freezePaneRow);

                MemoryStream stream = new MemoryStream();
                xssfworkbook.Write(stream);
                var buf = stream.ToArray();
                stream.Close();
                stream.Dispose();
                if (!Directory.Exists(filePath)) {
                    Directory.CreateDirectory(filePath);
                }

                using (FileStream fs = new FileStream(filePath + fileName, FileMode.Create, FileAccess.Write))
                {
                    fs.Write(buf, 0, buf.Length);
                    fs.Flush();
                    fs.Close();
                    fs.Dispose();
                }

            }
            catch (Exception e) {
                result = e.Message;
            }
            return result;
        }

        protected void btn_click(object sender, EventArgs e)
        {
            //FileStream fs = new FileStream(Server.MapPath(@"\Content\Sample.xlsx"), FileMode.Open, FileAccess.Read);
            //XSSFWorkbook temWorkBook = new XSSFWorkbook(fs);
            //ISheet nsheet = temWorkBook.GetSheet("Sheet1");
            //IRow datarow = nsheet.GetRow(4);

            //datarow.GetCell(0).SetCellValue(77);
            //nsheet.ForceFormulaRecalculation = true;

            //using (var ms = new MemoryStream())
            //{
            //    temWorkBook.Write(ms);

            //    Response.Clear();
            //    Response.ContentType = "Application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            //    Response.AppendHeader("Content-Disposition","inline;filename=Sample"+DateTime.Now.ToString("yyyyMMMdd")+".xlsx");
            //    Response.BinaryWrite(ms.ToArray());
            //    Response.End();
            //}

            IWorkbook workbook = new XSSFWorkbook();
            ISheet sheet1 = workbook.CreateSheet("New Sheet");
            ISheet sheet2 = workbook.CreateSheet("Second Sheet");
            ICell cell1 = sheet1.CreateRow(0).CreateCell(0);
            IFont fontBold = workbook.CreateFont();
            fontBold.Boldweight = (short)FontBoldWeight.Bold;
            ICellStyle style1 = workbook.CreateCellStyle();
            style1.SetFont(fontBold);
            cell1.CellStyle = style1;
            cell1.SetCellValue("sample value");
            int x = 1;
            for (int i = 1; i <= 15; i++)
            {
                IRow row = sheet1.CreateRow(i);
                for(int j = 0;j < 15;j++)
                {
                    row.CreateCell(j).SetCellValue(x++);
                }
            }
            using (var ms = new MemoryStream())
            {
                workbook.Write(ms);

                Response.Clear();
                Response.ContentType = "Application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                Response.AppendHeader("Content-Disposition", "inline;filename=Sample" + DateTime.Now.ToString("yyyyMMMdd") + ".xlsx");
                Response.BinaryWrite(ms.ToArray());
                Response.End();
            }
        }

        public void TestCloneStyleSameWB()
        {
            XSSFWorkbook wb = new XSSFWorkbook();
            Assert.AreEqual(1, wb.NumberOfFonts);

            XSSFFont fnt = (XSSFFont)wb.CreateFont();
            fnt.FontName = ("TestingFont");
            Assert.AreEqual(2, wb.NumberOfFonts);

            XSSFCellStyle orig = (XSSFCellStyle)wb.CreateCellStyle();
            orig.Alignment = (HorizontalAlignment.Right);
            orig.SetFont(fnt);
            orig.DataFormat = (short)18;

            Assert.AreEqual(HorizontalAlignment.Right, orig.Alignment);
            Assert.AreEqual(fnt, orig.GetFont());
            Assert.AreEqual(18, orig.DataFormat);

            XSSFCellStyle clone = (XSSFCellStyle)wb.CreateCellStyle();
            Assert.AreNotEqual(HorizontalAlignment.Right, clone.Alignment);
            Assert.AreNotEqual(fnt, clone.GetFont());
            Assert.AreNotEqual(18, clone.DataFormat);

            clone.CloneStyleFrom(orig);
            Assert.AreEqual(HorizontalAlignment.Right, clone.Alignment);
            Assert.AreEqual(fnt, clone.GetFont());
            Assert.AreEqual(18, clone.DataFormat);
            Assert.AreEqual(2, wb.NumberOfFonts);

            clone.Alignment = HorizontalAlignment.Left;
            clone.DataFormat = 17;
            Assert.AreEqual(HorizontalAlignment.Right, orig.Alignment);
            Assert.AreEqual(18, orig.DataFormat);

            Assert.IsNotNull(XSSFTestDataSamples.WriteOutAndReadBack(wb));
        }

        public void TestCloneStyleDiffWB()
        {
            XSSFWorkbook wbOrig = new XSSFWorkbook();
            Assert.AreEqual(1, wbOrig.NumberOfFonts);
            Assert.AreEqual(0, wbOrig.GetStylesSource().GetNumberFormats().Count);

            XSSFFont fnt = (XSSFFont)wbOrig.CreateFont();
            fnt.FontName = ("TestingFont");
            Assert.AreEqual(2, wbOrig.NumberOfFonts);
            Assert.AreEqual(0, wbOrig.GetStylesSource().GetNumberFormats().Count);

            XSSFDataFormat fmt = (XSSFDataFormat)wbOrig.CreateDataFormat();
            fmt.GetFormat("MadeUpOne");
            fmt.GetFormat("MadeUpTwo");

            XSSFCellStyle orig = (XSSFCellStyle)wbOrig.CreateCellStyle();
            orig.Alignment = (HorizontalAlignment.Right);
            orig.SetFont(fnt);
            orig.DataFormat = (fmt.GetFormat("Test##"));

            Assert.IsTrue(HorizontalAlignment.Right == orig.Alignment);
            Assert.IsTrue(fnt == orig.GetFont());
            Assert.IsTrue(fmt.GetFormat("Test##") == orig.DataFormat);

            Assert.AreEqual(2, wbOrig.NumberOfFonts);
            Assert.AreEqual(3, wbOrig.GetStylesSource().GetNumberFormats().Count);


            // Now a style on another workbook
            XSSFWorkbook wbClone = new XSSFWorkbook();
            Assert.AreEqual(1, wbClone.NumberOfFonts);
            Assert.AreEqual(0, wbClone.GetStylesSource().GetNumberFormats().Count);
            Assert.AreEqual(1, wbClone.NumCellStyles);

            XSSFDataFormat fmtClone = (XSSFDataFormat)wbClone.CreateDataFormat();
            XSSFCellStyle clone = (XSSFCellStyle)wbClone.CreateCellStyle();

            Assert.AreEqual(1, wbClone.NumberOfFonts);
            Assert.AreEqual(0, wbClone.GetStylesSource().GetNumberFormats().Count);

            Assert.IsFalse(HorizontalAlignment.Right == clone.Alignment);
            Assert.IsFalse("TestingFont" == clone.GetFont().FontName);

            clone.CloneStyleFrom(orig);

            Assert.AreEqual(2, wbClone.NumberOfFonts);
            Assert.AreEqual(2, wbClone.NumCellStyles);
            Assert.AreEqual(1, wbClone.GetStylesSource().GetNumberFormats().Count);

            Assert.AreEqual(HorizontalAlignment.Right, clone.Alignment);
            Assert.AreEqual("TestingFont", clone.GetFont().FontName);
            Assert.AreEqual(fmtClone.GetFormat("Test##"), clone.DataFormat);
            Assert.IsFalse(fmtClone.GetFormat("Test##") == fmt.GetFormat("Test##"));

            // Save it and re-check
            XSSFWorkbook wbReload = (XSSFWorkbook)XSSFTestDataSamples.WriteOutAndReadBack(wbClone);
            Assert.AreEqual(2, wbReload.NumberOfFonts);
            Assert.AreEqual(2, wbReload.NumCellStyles);
            Assert.AreEqual(1, wbReload.GetStylesSource().GetNumberFormats().Count);

            XSSFCellStyle reload = (XSSFCellStyle)wbReload.GetCellStyleAt((short)1);
            Assert.AreEqual(HorizontalAlignment.Right, reload.Alignment);
            Assert.AreEqual("TestingFont", reload.GetFont().FontName);
            Assert.AreEqual(fmtClone.GetFormat("Test##"), reload.DataFormat);
            Assert.IsFalse(fmtClone.GetFormat("Test##") == fmt.GetFormat("Test##"));

            Assert.IsNotNull(XSSFTestDataSamples.WriteOutAndReadBack(wbOrig));
            Assert.IsNotNull(XSSFTestDataSamples.WriteOutAndReadBack(wbClone));
        }

        public void TestDefaultStyles()
        {

            XSSFWorkbook wb1 = new XSSFWorkbook();

            XSSFCellStyle style1 = (XSSFCellStyle)wb1.CreateCellStyle();
            Assert.AreEqual(IndexedColors.Automatic.Index, style1.FillBackgroundColor);
            Assert.IsNull(style1.FillBackgroundColorColor);

            Assert.IsNotNull(XSSFTestDataSamples.WriteOutAndReadBack(wb1));

            //compatibility with HSSF
            HSSFWorkbook wb2 = new HSSFWorkbook();
            HSSFCellStyle style2 = (HSSFCellStyle)wb2.CreateCellStyle();
            Assert.AreEqual(style2.FillBackgroundColor, style1.FillBackgroundColor);
            Assert.AreEqual(style2.FillForegroundColor, style1.FillForegroundColor);
            Assert.AreEqual(style2.FillPattern, style1.FillPattern);

            Assert.AreEqual(style2.LeftBorderColor, style1.LeftBorderColor);
            Assert.AreEqual(style2.TopBorderColor, style1.TopBorderColor);
            Assert.AreEqual(style2.RightBorderColor, style1.RightBorderColor);
            Assert.AreEqual(style2.BottomBorderColor, style1.BottomBorderColor);

            Assert.AreEqual(style2.BorderBottom, style1.BorderBottom);
            Assert.AreEqual(style2.BorderLeft, style1.BorderLeft);
            Assert.AreEqual(style2.BorderRight, style1.BorderRight);
            Assert.AreEqual(style2.BorderTop, style1.BorderTop);
        }

        public void TestGetFillForegroundColor()
        {

            XSSFWorkbook wb = new XSSFWorkbook();
            StylesTable styles = wb.GetStylesSource();
            Assert.AreEqual(1, wb.NumCellStyles);
            Assert.AreEqual(2, styles.GetFills().Count);

            XSSFCellStyle defaultStyle = (XSSFCellStyle)wb.GetCellStyleAt((short)0);
            Assert.AreEqual(IndexedColors.Automatic.Index, defaultStyle.FillForegroundColor);
            Assert.AreEqual(null, defaultStyle.FillForegroundColorColor);
            Assert.AreEqual(FillPattern.NoFill, defaultStyle.FillPattern);

            XSSFCellStyle customStyle = (XSSFCellStyle)wb.CreateCellStyle();

            customStyle.FillPattern = (FillPattern.SolidForeground);
            Assert.AreEqual(FillPattern.SolidForeground, customStyle.FillPattern);
            Assert.AreEqual(3, styles.GetFills().Count);

            customStyle.FillForegroundColor = (IndexedColors.BrightGreen.Index);
            Assert.AreEqual(IndexedColors.BrightGreen.Index, customStyle.FillForegroundColor);
            Assert.AreEqual(4, styles.GetFills().Count);

            for (int i = 0; i < 3; i++)
            {
                XSSFCellStyle style = (XSSFCellStyle)wb.CreateCellStyle();

                style.FillPattern = (FillPattern.SolidForeground);
                Assert.AreEqual(FillPattern.SolidForeground, style.FillPattern);
                Assert.AreEqual(4, styles.GetFills().Count);

                style.FillForegroundColor = (IndexedColors.BrightGreen.Index);
                Assert.AreEqual(IndexedColors.BrightGreen.Index, style.FillForegroundColor);
                Assert.AreEqual(4, styles.GetFills().Count);
            }

            Assert.IsNotNull(XSSFTestDataSamples.WriteOutAndReadBack(wb));
        }