Ejemplo n.º 1
0
 /// <summary>
 /// 设置excel中单元格的样式
 /// djin--2012-5-25
 /// </summary>
 /// <param name="hssfworkbook"></param>
 /// <param name="options"></param>
 /// <returns></returns>
 public NPOI.SS.UserModel.CellStyle setCellstyle(HSSFWorkbook hssfworkbook, string[] options)
 {
     NPOI.SS.UserModel.CellStyle style3 = hssfworkbook.CreateCellStyle();
     foreach (string s in options)
     {
         if (s == "Border")
         {
             style3.BorderBottom = CellBorderType.THIN;
             style3.BorderLeft   = CellBorderType.THIN;
             style3.BorderRight  = CellBorderType.THIN;
             style3.BorderTop    = CellBorderType.THIN;
         }
         if (s == "Center")
         {
             style3.VerticalAlignment = VerticalAlignment.CENTER;
             style3.Alignment         = HorizontalAlignment.CENTER;
         }
         if (s == "DateTime")
         {
             NPOI.SS.UserModel.DataFormat format = hssfworkbook.CreateDataFormat();
             style3.DataFormat = format.GetFormat("yyyy-m-d");
         }
     }
     return(style3);
 }
Ejemplo n.º 2
0
        public void TestDataStyle()

        {
            string filepath = TempFile.GetTempFilePath("TestWriteSheetStyleDate",
                                                       ".xls");
            FileStream   out1 = new FileStream(filepath, FileMode.OpenOrCreate);
            HSSFWorkbook wb   = new HSSFWorkbook();

            NPOI.SS.UserModel.Sheet     s  = wb.CreateSheet();
            NPOI.SS.UserModel.CellStyle cs = wb.CreateCellStyle();
            Row row = s.CreateRow(0);

            // with Date:
            Cell cell = row.CreateCell(1);

            cs.DataFormat  = (HSSFDataFormat.GetBuiltinFormat("m/d/yy"));
            cell.CellStyle = (cs);
            cell.SetCellValue(DateTime.Now);

            // with Calendar:
            cell           = row.CreateCell(2);
            cs.DataFormat  = (HSSFDataFormat.GetBuiltinFormat("m/d/yy"));
            cell.CellStyle = (cs);
            cell.SetCellValue(DateTime.Now);

            wb.Write(out1);
            out1.Close();
            SanityChecker sanityChecker = new SanityChecker();

            sanityChecker.CheckHSSFWorkbook(wb);

            Assert.AreEqual(0, s.LastRowNum, "LAST ROW ");
            Assert.AreEqual(0, s.FirstRowNum, "FIRST ROW ");
        }
Ejemplo n.º 3
0
        /// <summary>
        /// Creates the excel workbook.
        /// </summary>
        /// <param name="subject">The subject.</param>
        public void CreateWorkbook(string subject)
        {
            //Creating the excel workbook
            NPOI.HSSF.UserModel.HSSFWorkbook wb = new NPOI.HSSF.UserModel.HSSFWorkbook();

            //Creating summary information to the document
            NPOI.HPSF.DocumentSummaryInformation dsi = NPOI.HPSF.PropertySetFactory.CreateDocumentSummaryInformation();
            dsi.Company = "Thoris";

            //Applying summary information to the document
            wb.DocumentSummaryInformation = dsi;

            //Creating summary information for the data
            NPOI.HPSF.SummaryInformation si = NPOI.HPSF.PropertySetFactory.CreateSummaryInformation();
            si.Subject = subject;

            //Applying summary information to the data
            wb.SummaryInformation = si;

            _workbook = wb;



            //Creating the current styles
            Font font = _workbook.CreateFont();

            font.Boldweight = (short)FontBoldWeight.BOLD;

            NPOI.SS.UserModel.CellStyle cellStyle = _workbook.CreateCellStyle();
            cellStyle.SetFont(font);

            _headerStyle = CreateHeaderStyle(_workbook);
            _dataStyle   = CreateStyle(_workbook, false);
        }
Ejemplo n.º 4
0
        public void TestDataStyle()
        {
            HSSFWorkbook wb = new HSSFWorkbook();

            NPOI.SS.UserModel.Sheet     s  = wb.CreateSheet();
            NPOI.SS.UserModel.CellStyle cs = wb.CreateCellStyle();
            Row row = s.CreateRow((short)0);

            // with Date:
            cs.DataFormat = (HSSFDataFormat.GetBuiltinFormat("m/d/yy"));
            row.RowStyle  = (cs);
            row.CreateCell(0);


            // with Calendar:
            row           = s.CreateRow((short)1);
            cs.DataFormat = (HSSFDataFormat.GetBuiltinFormat("m/d/yy"));
            row.RowStyle  = (cs);
            row.CreateCell(0);

            wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);

            SanityChecker sanityChecker = new SanityChecker();

            sanityChecker.CheckHSSFWorkbook(wb);

            Assert.AreEqual(1, s.LastRowNum, "LAST ROW ");
            Assert.AreEqual(0, s.FirstRowNum, "FIRST ROW ");
        }
Ejemplo n.º 5
0
        public void TestWriteSheetFont()
        {
            HSSFWorkbook wb = new HSSFWorkbook();

            NPOI.SS.UserModel.Sheet s = wb.CreateSheet();
            Row  r   = null;
            Cell c   = null;
            Font fnt = wb.CreateFont();

            NPOI.SS.UserModel.CellStyle cs = wb.CreateCellStyle();

            fnt.Color      = (NPOI.HSSF.Util.HSSFColor.RED.index);
            fnt.Boldweight = (short)FontBoldWeight.BOLD;
            cs.SetFont(fnt);
            for (short rownum = (short)0; rownum < 100; rownum++)
            {
                r          = s.CreateRow(rownum);
                r.RowStyle = (cs);
                r.CreateCell(0);
            }
            wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);

            SanityChecker sanityChecker = new SanityChecker();

            sanityChecker.CheckHSSFWorkbook(wb);
            Assert.AreEqual(99, s.LastRowNum, "LAST ROW == 99");
            Assert.AreEqual(0, s.FirstRowNum, "FIRST ROW == 0");
        }
Ejemplo n.º 6
0
            private static NPOI.SS.UserModel.CellStyle CreateStyle(HSSFWorkbook wb, HorizontalAlignment h_align, short color,
                                                                   bool bold)
            {
                Font font = wb.CreateFont();

                if (bold)
                {
                    font.Boldweight = (short)FontBoldWeight.BOLD;
                }

                NPOI.SS.UserModel.CellStyle cellStyle = wb.CreateCellStyle();
                cellStyle.SetFont(font);
                cellStyle.FillForegroundColor = (color);
                cellStyle.FillPattern         = FillPatternType.SOLID_FOREGROUND;
                cellStyle.VerticalAlignment   = NPOI.SS.UserModel.VerticalAlignment.CENTER;
                cellStyle.Alignment           = (h_align);
                cellStyle.BorderLeft          = (CellBorderType.THIN);
                cellStyle.LeftBorderColor     = (HSSFColor.BLACK.index);
                cellStyle.BorderTop           = (CellBorderType.THIN);
                cellStyle.TopBorderColor      = (HSSFColor.BLACK.index);
                cellStyle.BorderRight         = (CellBorderType.THIN);
                cellStyle.RightBorderColor    = (HSSFColor.BLACK.index);
                cellStyle.BorderBottom        = (CellBorderType.THIN);
                cellStyle.BottomBorderColor   = (HSSFColor.BLACK.index);

                return(cellStyle);
            }
Ejemplo n.º 7
0
        public void TestHashEquals()
        {
            HSSFWorkbook wb = new HSSFWorkbook();

            NPOI.SS.UserModel.Sheet     s   = wb.CreateSheet();
            NPOI.SS.UserModel.CellStyle cs1 = wb.CreateCellStyle();
            NPOI.SS.UserModel.CellStyle cs2 = wb.CreateCellStyle();
            Row  row   = s.CreateRow(0);
            Cell cell1 = row.CreateCell(1);
            Cell cell2 = row.CreateCell(2);

            cs1.DataFormat = (HSSFDataFormat.GetBuiltinFormat("m/d/yy"));
            cs2.DataFormat = (HSSFDataFormat.GetBuiltinFormat("m/dd/yy"));

            cell1.CellStyle = (cs1);
            cell1.SetCellValue(DateTime.Now);

            cell2.CellStyle = (cs2);
            cell2.SetCellValue(DateTime.Now);

            Assert.AreEqual(cs1.GetHashCode(), cs1.GetHashCode());
            Assert.AreEqual(cs2.GetHashCode(), cs2.GetHashCode());
            Assert.IsTrue(cs1.Equals(cs1));
            Assert.IsTrue(cs2.Equals(cs2));

            // Change cs1, hash will alter
            int hash1 = cs1.GetHashCode();

            cs1.DataFormat = (HSSFDataFormat.GetBuiltinFormat("m/dd/yy"));
            Assert.IsFalse(hash1 == cs1.GetHashCode());
        }
Ejemplo n.º 8
0
        public void TestCloneStyleSameWB()
        {
            HSSFWorkbook wb  = new HSSFWorkbook();
            Font         fnt = wb.CreateFont();

            fnt.FontName = ("TestingFont");
            Assert.AreEqual(5, wb.NumberOfFonts);

            NPOI.SS.UserModel.CellStyle orig = wb.CreateCellStyle();
            orig.Alignment = (HorizontalAlignment.RIGHT);
            orig.SetFont(fnt);
            orig.DataFormat = ((short)18);

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

            NPOI.SS.UserModel.CellStyle clone = wb.CreateCellStyle();
            Assert.AreNotEqual(HorizontalAlignment.RIGHT, clone.Alignment);
            Assert.AreNotEqual(fnt, clone.GetFont(wb));
            Assert.AreNotEqual(18, clone.DataFormat);

            clone.CloneStyleFrom(orig);
            Assert.AreEqual(HorizontalAlignment.RIGHT, clone.Alignment);
            Assert.AreEqual(fnt, clone.GetFont(wb));
            Assert.AreEqual(18, clone.DataFormat);
            Assert.AreEqual(5, wb.NumberOfFonts);
        }
Ejemplo n.º 9
0
        /// <summary>
        /// Returns a map containing the format properties of the given cell style.
        /// </summary>
        /// <param name="style">cell style</param>
        /// <returns>map of format properties (String -&gt; Object)</returns>
        private static Hashtable GetFormatProperties(NPOI.SS.UserModel.CellStyle style)
        {
            Hashtable properties = new Hashtable();

            PutShort(properties, ALIGNMENT, (short)style.Alignment);
            PutShort(properties, BORDER_BOTTOM, (short)style.BorderBottom);
            PutShort(properties, BORDER_LEFT, (short)style.BorderLeft);
            PutShort(properties, BORDER_RIGHT, (short)style.BorderRight);
            PutShort(properties, BORDER_TOP, (short)style.BorderTop);
            PutShort(properties, BOTTOM_BORDER_COLOR, style.BottomBorderColor);
            PutShort(properties, DATA_FORMAT, style.DataFormat);
            PutShort(properties, FILL_BACKGROUND_COLOR, style.FillBackgroundColor);
            PutShort(properties, FILL_FOREGROUND_COLOR, style.FillForegroundColor);
            PutShort(properties, FILL_PATTERN, (short)style.FillPattern);
            PutShort(properties, FONT, style.FontIndex);
            PutBoolean(properties, HIDDEN, style.IsHidden);
            PutShort(properties, INDENTION, style.Indention);
            PutShort(properties, LEFT_BORDER_COLOR, style.LeftBorderColor);
            PutBoolean(properties, LOCKED, style.IsLocked);
            PutShort(properties, RIGHT_BORDER_COLOR, style.RightBorderColor);
            PutShort(properties, ROTATION, style.Rotation);
            PutShort(properties, TOP_BORDER_COLOR, style.TopBorderColor);
            PutShort(properties, VERTICAL_ALIGNMENT, (short)style.VerticalAlignment);
            PutBoolean(properties, WRAP_TEXT, style.WrapText);
            return(properties);
        }
Ejemplo n.º 10
0
        private void SetCoutent(List <CustomerInfo> list, Sheet sheet)
        {
            NPOI.SS.UserModel.CellStyle styleCoutent = hssWorkBook.CreateCellStyle();
            styleCoutent.BorderBottom = CellBorderType.THIN;
            styleCoutent.BorderLeft   = CellBorderType.THIN;
            styleCoutent.BorderRight  = CellBorderType.THIN;
            styleCoutent.BorderTop    = CellBorderType.THIN;

            for (int i = 0; i < list.Count; i++)
            {
                Row  newrow = sheet.CreateRow(i + 3);
                Cell cell   = newrow.CreateCell(0);
                cell.SetCellValue((i + 1).ToString());
                cell.CellStyle = styleCoutent;

                Cell cell1 = newrow.CreateCell(1);
                cell1.SetCellValue(list[i].CustomerID);
                cell1.CellStyle = styleCoutent;
                Cell cell2 = newrow.CreateCell(2);
                cell2.SetCellValue(list[i].Email);
                cell2.CellStyle = styleCoutent;
                Cell cell3 = newrow.CreateCell(3);
                cell3.SetCellValue(list[i].StausString);
                cell3.CellStyle = styleCoutent;
            }
        }
Ejemplo n.º 11
0
        /**
         *  This method attempt to find an already existing HSSFCellStyle that matches
         *  what you want the style to be. If it does not find the style, then it
         *  Creates a new one. If it does Create a new one, then it applies the
         *  propertyName and propertyValue to the style. This is necessary because
         *  Excel has an upper limit on the number of Styles that it supports.
         *
         *@param  workbook               The workbook that is being worked with.
         *@param  propertyName           The name of the property that is to be
         *      changed.
         *@param  propertyValue          The value of the property that is to be
         *      changed.
         *@param  cell                   The cell that needs it's style changes
         *@exception  NestableException  Thrown if an error happens.
         */
        public static void SetCellStyleProperty(NPOI.SS.UserModel.Cell cell, HSSFWorkbook workbook, String propertyName, Object propertyValue)
        {
            NPOI.SS.UserModel.CellStyle originalStyle = cell.CellStyle;
            NPOI.SS.UserModel.CellStyle newStyle      = null;
            Hashtable values = GetFormatProperties(originalStyle);

            values[propertyName] = propertyValue;

            // index seems like what  index the cellstyle is in the list of styles for a workbook.
            // not good to compare on!
            short numberCellStyles = workbook.NumCellStyles;

            for (short i = 0; i < numberCellStyles; i++)
            {
                NPOI.SS.UserModel.CellStyle wbStyle = workbook.GetCellStyleAt(i);
                Hashtable wbStyleMap = GetFormatProperties(wbStyle);

                if (wbStyleMap.Equals(values))
                {
                    newStyle = wbStyle;
                    break;
                }
            }

            if (newStyle == null)
            {
                newStyle = workbook.CreateCellStyle();
                SetFormatProperties(newStyle, workbook, values);
            }

            cell.CellStyle = (newStyle);
        }
Ejemplo n.º 12
0
        public void TestDateFormulas()
        {
            HSSFWorkbook wb = new HSSFWorkbook();

            NPOI.SS.UserModel.Sheet s = wb.CreateSheet("TestSheet1");
            Row  r = null;
            Cell c = null;

            r = s.CreateRow(0);
            c = r.CreateCell(0);

            NPOI.SS.UserModel.CellStyle cellStyle = wb.CreateCellStyle();
            cellStyle.DataFormat = (HSSFDataFormat.GetBuiltinFormat("m/d/yy h:mm"));
            c.SetCellValue(new DateTime());
            c.CellStyle = (cellStyle);

            // Assert.AreEqual("Checking hour = " + hour, date.GetTime().GetTime(),
            //              NPOI.SS.UserModel.DateUtil.GetJavaDate(excelDate).GetTime());

            for (int k = 1; k < 100; k++)
            {
                r             = s.CreateRow(k);
                c             = r.CreateCell(0);
                c.CellFormula = ("A" + (k) + "+1");
                c.CellStyle   = cellStyle;
            }

            HSSFTestDataSamples.WriteOutAndReadBack(wb);
        }
Ejemplo n.º 13
0
        public void TestDoesNoHarmIfNothingToDo()
        {
            HSSFWorkbook wb = new HSSFWorkbook();

            Font f = wb.CreateFont();

            f.FontName = ("Testing");
            NPOI.SS.UserModel.CellStyle s = wb.CreateCellStyle();
            s.SetFont(f);

            Assert.AreEqual(5, wb.NumberOfFonts);
            Assert.AreEqual(22, wb.NumCellStyles);

            // Optimise fonts
            HSSFOptimiser.OptimiseFonts(wb);

            Assert.AreEqual(5, wb.NumberOfFonts);
            Assert.AreEqual(22, wb.NumCellStyles);

            Assert.AreEqual(f, s.GetFont(wb));

            // Optimise styles
            HSSFOptimiser.OptimiseCellStyles(wb);

            Assert.AreEqual(5, wb.NumberOfFonts);
            Assert.AreEqual(22, wb.NumCellStyles);

            Assert.AreEqual(f, s.GetFont(wb));
        }
Ejemplo n.º 14
0
 public WorkbookFormatter(HSSFWorkbook wb)
 {
     _wb      = wb;
     _style_1 = CreateStyle(wb, HorizontalAlignment.LEFT);
     _style_2 = CreateStyle(wb, HorizontalAlignment.CENTER);
     _style_3 = CreateStyle(wb, HorizontalAlignment.CENTER, HSSFColor.GREY_25_PERCENT.index, true);
     _style_4 = CreateHeaderStyle(wb);
 }
Ejemplo n.º 15
0
            private static void WriteOtherSettings(NPOI.SS.UserModel.Sheet sheet, NPOI.SS.UserModel.CellStyle style,
                                                   String strStettings)
            {
                Row  row  = sheet.GetRow(sheet.PhysicalNumberOfRows - 1);
                Cell cell = row.CreateCell(5);

                cell.CellStyle = (style);
                SetCellValue(cell, strStettings);
            }
Ejemplo n.º 16
0
        /// <summary>
        /// Creates the header style.
        /// </summary>
        /// <param name="wb">The wb.</param>
        /// <returns></returns>
        public static NPOI.SS.UserModel.CellStyle CreateHeaderStyle(HSSFWorkbook wb)
        {
            Font font = wb.CreateFont();

            font.Boldweight = (short)FontBoldWeight.BOLD;

            NPOI.SS.UserModel.CellStyle cellStyle = wb.CreateCellStyle();
            cellStyle.SetFont(font);
            return(cellStyle);
        }
Ejemplo n.º 17
0
 public ValidationAdder(NPOI.SS.UserModel.Sheet fSheet, NPOI.SS.UserModel.CellStyle style_1, NPOI.SS.UserModel.CellStyle style_2,
                        NPOI.SS.UserModel.CellStyle cellStyle, int validationType)
 {
     _sheet           = (HSSFSheet)fSheet;
     _style_1         = style_1;
     _style_2         = style_2;
     _cellStyle       = cellStyle;
     _validationType  = validationType;
     _currentRowIndex = fSheet.PhysicalNumberOfRows;
 }
Ejemplo n.º 18
0
        public void TestDefaultColumnStyle()
        {
            HSSFWorkbook wb = new HSSFWorkbook();

            NPOI.SS.UserModel.CellStyle style = wb.CreateCellStyle();
            NPOI.SS.UserModel.Sheet     s     = wb.CreateSheet();
            s.SetDefaultColumnStyle((short)0, style);
            Row  r = s.CreateRow(0);
            Cell c = r.CreateCell(0);

            Assert.AreEqual(style.Index, c.CellStyle.Index, "style should Match");
        }
Ejemplo n.º 19
0
        public void TestWriteSheetStyle()
        {
            string filepath = TempFile.GetTempFilePath("TestWriteSheetStyle",
                                                       ".xls");
            FileStream   out1 = new FileStream(filepath, FileMode.OpenOrCreate);
            HSSFWorkbook wb   = new HSSFWorkbook();

            NPOI.SS.UserModel.Sheet s = wb.CreateSheet();
            Row  r   = null;
            Cell c   = null;
            Font fnt = wb.CreateFont();

            NPOI.SS.UserModel.CellStyle cs  = wb.CreateCellStyle();
            NPOI.SS.UserModel.CellStyle cs2 = wb.CreateCellStyle();

            cs.BorderBottom        = (CellBorderType.THIN);
            cs.BorderLeft          = (CellBorderType.THIN);
            cs.BorderRight         = (CellBorderType.THIN);
            cs.BorderTop           = (CellBorderType.THIN);
            cs.FillForegroundColor = ( short )0xA;
            cs.FillPattern         = FillPatternType.SOLID_FOREGROUND;
            fnt.Color               = ( short )0xf;
            fnt.IsItalic            = (true);
            cs2.FillForegroundColor = ( short )0x0;
            cs2.FillPattern         = FillPatternType.SOLID_FOREGROUND;
            cs2.SetFont(fnt);
            for (short rownum = ( short )0; rownum < 100; rownum++)
            {
                r = s.CreateRow(rownum);

                // r.SetRowNum(( short ) rownum);
                for (short cellnum = ( short )0; cellnum < 50; cellnum += 2)
                {
                    c = r.CreateCell(cellnum);
                    c.SetCellValue(rownum * 10000 + cellnum
                                   + ((( double )rownum / 1000)
                                      + (( double )cellnum / 10000)));
                    c.CellStyle = (cs);
                    c           = r.CreateCell(cellnum + 1);
                    c.SetCellValue("TEST");
                    c.CellStyle = (cs2);
                }
            }
            wb.Write(out1);
            out1.Close();
            SanityChecker sanityChecker = new SanityChecker();

            sanityChecker.CheckHSSFWorkbook(wb);
            Assert.AreEqual(99, s.LastRowNum, "LAST ROW == 99");
            Assert.AreEqual(0, s.FirstRowNum, "FIRST ROW == 0");

            // assert((s.LastRowNum == 99));
        }
Ejemplo n.º 20
0
        /// <summary>
        /// Creates the style.
        /// </summary>
        /// <param name="wb">The wb.</param>
        /// <param name="bold">if set to <c>true</c> [bold].</param>
        /// <returns></returns>
        public static NPOI.SS.UserModel.CellStyle CreateStyle(HSSFWorkbook wb, bool bold)
        {
            Font font = wb.CreateFont();

            if (bold)
            {
                font.Boldweight = (short)FontBoldWeight.BOLD;
            }

            NPOI.SS.UserModel.CellStyle cellStyle = wb.CreateCellStyle();
            cellStyle.SetFont(font);

            return(cellStyle);
        }
Ejemplo n.º 21
0
        public void TestHSSFCellToStringWithDataFormat()
        {
            HSSFWorkbook wb   = new HSSFWorkbook();
            Cell         cell = wb.CreateSheet("Sheet1").CreateRow(0).CreateCell(0);

            cell.SetCellValue(new DateTime(2009, 8, 20));
            NPOI.SS.UserModel.CellStyle cellStyle = wb.CreateCellStyle();
            cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("m/d/yy");
            cell.CellStyle       = cellStyle;
            Assert.AreEqual("8/20/09", cell.ToString());

            NPOI.SS.UserModel.CellStyle cellStyle2 = wb.CreateCellStyle();
            DataFormat format = wb.CreateDataFormat();

            cellStyle2.DataFormat = format.GetFormat("YYYY-mm/dd");
            cell.CellStyle        = cellStyle2;
            Assert.AreEqual("2009-08/20", cell.ToString());
        }
Ejemplo n.º 22
0
        public void TestWriteDataFormat()
        {
            string       filepath = TempFile.GetTempFilePath("TestWriteSheetSimple", ".xls");
            FileStream   out1     = new FileStream(filepath, FileMode.OpenOrCreate);
            HSSFWorkbook wb       = new HSSFWorkbook();

            NPOI.SS.UserModel.Sheet s = wb.CreateSheet();
            Row        r      = null;
            Cell       c      = null;
            DataFormat format = wb.CreateDataFormat();

            NPOI.SS.UserModel.CellStyle cs = wb.CreateCellStyle();

            short df = format.GetFormat("0.0");

            cs.DataFormat = (df);

            r           = s.CreateRow(0);
            c           = r.CreateCell(0);
            c.CellStyle = (cs);
            c.SetCellValue(1.25);

            wb.Write(out1);
            out1.Close();

            FileStream      stream   = new FileStream(filepath, FileMode.OpenOrCreate);
            POIFSFileSystem fs       = new POIFSFileSystem(stream);
            HSSFWorkbook    workbook = new HSSFWorkbook(fs);

            NPOI.SS.UserModel.Sheet sheet = workbook.GetSheetAt(0);
            Cell cell =
                sheet.GetRow(0).GetCell(0);

            format = workbook.CreateDataFormat();

            Assert.AreEqual(1.25, cell.NumericCellValue, 1e-10);

            Assert.AreEqual(format.GetFormat(df), "0.0");

            Assert.AreEqual(format, workbook.CreateDataFormat());

            stream.Close();
        }
Ejemplo n.º 23
0
        public void TestWriteSheetFont()
        {
            string filepath = TempFile.GetTempFilePath("TestWriteSheetFont",
                                                       ".xls");
            FileStream   out1 = new FileStream(filepath, FileMode.OpenOrCreate);
            HSSFWorkbook wb   = new HSSFWorkbook();

            NPOI.SS.UserModel.Sheet s = wb.CreateSheet();
            Row  r   = null;
            Cell c   = null;
            Font fnt = wb.CreateFont();

            NPOI.SS.UserModel.CellStyle cs = wb.CreateCellStyle();

            fnt.Color      = (NPOI.HSSF.Util.HSSFColor.RED.index);
            fnt.Boldweight = (short)FontBoldWeight.BOLD;
            cs.SetFont(fnt);
            for (short rownum = ( short )0; rownum < 100; rownum++)
            {
                r = s.CreateRow(rownum);

                // r.SetRowNum(( short ) rownum);
                for (short cellnum = ( short )0; cellnum < 50; cellnum += 2)
                {
                    c = r.CreateCell(cellnum);
                    c.SetCellValue(rownum * 10000 + cellnum
                                   + ((( double )rownum / 1000)
                                      + (( double )cellnum / 10000)));
                    c = r.CreateCell(cellnum + 1);
                    c.SetCellValue("TEST");
                    c.CellStyle = (cs);
                }
            }
            wb.Write(out1);
            out1.Close();
            SanityChecker sanityChecker = new SanityChecker();

            sanityChecker.CheckHSSFWorkbook(wb);
            Assert.AreEqual(99, s.LastRowNum, "LAST ROW == 99");
            Assert.AreEqual(0, s.FirstRowNum, "FIRST ROW == 0");

            // assert((s.LastRowNum == 99));
        }
Ejemplo n.º 24
0
        public void TestCloneStyleDiffWB()
        {
            HSSFWorkbook wbOrig = new HSSFWorkbook();

            Font fnt = wbOrig.CreateFont();

            fnt.FontName = ("TestingFont");
            Assert.AreEqual(5, wbOrig.NumberOfFonts);

            DataFormat fmt = wbOrig.CreateDataFormat();

            fmt.GetFormat("MadeUpOne");
            fmt.GetFormat("MadeUpTwo");

            NPOI.SS.UserModel.CellStyle orig = wbOrig.CreateCellStyle();
            orig.Alignment = (HorizontalAlignment.RIGHT);
            orig.SetFont(fnt);
            orig.DataFormat = (fmt.GetFormat("Test##"));

            Assert.AreEqual(HorizontalAlignment.RIGHT, orig.Alignment);
            Assert.AreEqual(fnt, orig.GetFont(wbOrig));
            Assert.AreEqual(fmt.GetFormat("Test##"), orig.DataFormat);

            // Now a style on another workbook
            HSSFWorkbook wbClone = new HSSFWorkbook();

            Assert.AreEqual(4, wbClone.NumberOfFonts);
            DataFormat fmtClone = wbClone.CreateDataFormat();

            NPOI.SS.UserModel.CellStyle clone = wbClone.CreateCellStyle();
            Assert.AreEqual(4, wbClone.NumberOfFonts);

            Assert.AreNotEqual(HorizontalAlignment.RIGHT, clone.Alignment);
            Assert.AreNotEqual("TestingFont", clone.GetFont(wbClone).FontName);

            clone.CloneStyleFrom(orig);
            Assert.AreEqual(HorizontalAlignment.RIGHT, clone.Alignment);
            Assert.AreEqual("TestingFont", clone.GetFont(wbClone).FontName);
            Assert.AreEqual(fmtClone.GetFormat("Test##"), clone.DataFormat);
            Assert.AreNotEqual(fmtClone.GetFormat("Test##"), fmt.GetFormat("Test##"));
            Assert.AreEqual(5, wbClone.NumberOfFonts);
        }
Ejemplo n.º 25
0
        public void TestFormulaStyle()
        {
            HSSFWorkbook wb = new HSSFWorkbook();

            NPOI.SS.UserModel.Sheet s = wb.CreateSheet("TestSheet1");
            Row  r = null;
            Cell c = null;

            NPOI.SS.UserModel.CellStyle cs = wb.CreateCellStyle();
            Font f = wb.CreateFont();

            f.FontHeightInPoints = ((short)20);
            f.Color                = (HSSFColor.RED.index);
            f.Boldweight           = (short)FontBoldWeight.BOLD;
            f.FontName             = ("Arial Unicode MS");
            cs.FillBackgroundColor = ((short)3);
            cs.SetFont(f);
            cs.BorderTop    = CellBorderType.THIN;
            cs.BorderRight  = CellBorderType.THIN;
            cs.BorderLeft   = CellBorderType.THIN;
            cs.BorderBottom = CellBorderType.THIN;

            r             = s.CreateRow(0);
            c             = r.CreateCell(0);
            c.CellStyle   = (cs);
            c.CellFormula = ("2*3");

            wb = WriteOutAndReadBack(wb);
            s  = wb.GetSheetAt(0);
            r  = s.GetRow(0);
            c  = r.GetCell(0);

            Assert.IsTrue((c.CellType == NPOI.SS.UserModel.CellType.FORMULA), "Formula Cell at 0,0");
            cs = c.CellStyle;

            Assert.IsNotNull(cs, "Formula Cell Style");
            Assert.AreEqual(cs.FontIndex, f.Index, "Font Index Matches");
            Assert.AreEqual((short)cs.BorderTop, (short)1, "Top Border");
            Assert.AreEqual((short)cs.BorderLeft, (short)1, "Left Border");
            Assert.AreEqual((short)cs.BorderRight, (short)1, "Right Border");
            Assert.AreEqual((short)cs.BorderBottom, (short)1, "Bottom Border");
        }
Ejemplo n.º 26
0
        private static void AddDateTimeValidations(WorkbookFormatter wf, HSSFWorkbook wb)
        {
            wf.CreateSheet("Dates and Times");

            DataFormat dataFormat = wb.CreateDataFormat();
            short      fmtDate    = dataFormat.GetFormat("m/d/yyyy");
            short      fmtTime    = dataFormat.GetFormat("h:mm");

            NPOI.SS.UserModel.CellStyle cellStyle_date = wb.CreateCellStyle();
            cellStyle_date.DataFormat = (fmtDate);
            NPOI.SS.UserModel.CellStyle cellStyle_time = wb.CreateCellStyle();
            cellStyle_time.DataFormat = (fmtTime);

            wf.CreateDVTypeRow("Date ( cells are already formated as date - m/d/yyyy)");
            wf.CreateHeaderRow();

            ValidationAdder va = wf.CreateValidationAdder(cellStyle_date, DVConstraint.ValidationType.DATE);

            va.AddValidation(DVConstraint.OperatorType.BETWEEN, "2004/01/02", "2004/01/06", HSSFDataValidation.ERRORSTYLE.STOP, "Between 1/2/2004 and 1/6/2004 ", "Error box type = STOP", true, true, true);
            va.AddValidation(DVConstraint.OperatorType.NOT_BETWEEN, "2004/01/01", "2004/01/06", HSSFDataValidation.ERRORSTYLE.INFO, "Not between 1/2/2004 and 1/6/2004 ", "Error box type = INFO", false, true, true);
            va.AddValidation(DVConstraint.OperatorType.EQUAL, "2004/03/02", null, HSSFDataValidation.ERRORSTYLE.WARNING, "Equal to 3/2/2004", "Error box type = WARNING", false, false, true);
            va.AddValidation(DVConstraint.OperatorType.NOT_EQUAL, "2004/03/02", null, HSSFDataValidation.ERRORSTYLE.WARNING, "Not equal to 3/2/2004", "-", false, false, false);
            va.AddValidation(DVConstraint.OperatorType.GREATER_THAN, "=DATEVALUE(\"4-Jul-2001\")", null, HSSFDataValidation.ERRORSTYLE.WARNING, "Greater than DATEVALUE('4-Jul-2001')", "-", true, false, false);
            va.AddValidation(DVConstraint.OperatorType.LESS_THAN, "2004/03/02", null, HSSFDataValidation.ERRORSTYLE.WARNING, "Less than 3/2/2004", "-", true, true, false);
            va.AddValidation(DVConstraint.OperatorType.GREATER_OR_EQUAL, "2004/03/02", null, HSSFDataValidation.ERRORSTYLE.STOP, "Greater than or equal to 3/2/2004", "Error box type = STOP", true, false, true);
            va.AddValidation(DVConstraint.OperatorType.LESS_OR_EQUAL, "2004/03/04", null, HSSFDataValidation.ERRORSTYLE.STOP, "Less than or equal to 3/4/2004", "-", false, true, false);

            // "Time" validation type
            wf.CreateDVTypeRow("Time ( cells are already formated as time - h:mm)");
            wf.CreateHeaderRow();

            va = wf.CreateValidationAdder(cellStyle_time, DVConstraint.ValidationType.TIME);
            va.AddValidation(DVConstraint.OperatorType.BETWEEN, "12:00", "16:00", HSSFDataValidation.ERRORSTYLE.STOP, "Between 12:00 and 16:00 ", "Error box type = STOP", true, true, true);
            va.AddValidation(DVConstraint.OperatorType.NOT_BETWEEN, "12:00", "16:00", HSSFDataValidation.ERRORSTYLE.INFO, "Not between 12:00 and 16:00 ", "Error box type = INFO", false, true, true);
            va.AddValidation(DVConstraint.OperatorType.EQUAL, "13:35", null, HSSFDataValidation.ERRORSTYLE.WARNING, "Equal to 13:35", "Error box type = WARNING", false, false, true);
            va.AddValidation(DVConstraint.OperatorType.NOT_EQUAL, "13:35", null, HSSFDataValidation.ERRORSTYLE.WARNING, "Not equal to 13:35", "-", false, false, false);
            va.AddValidation(DVConstraint.OperatorType.GREATER_THAN, "12:00", null, HSSFDataValidation.ERRORSTYLE.WARNING, "Greater than 12:00", "-", true, false, false);
            va.AddValidation(DVConstraint.OperatorType.LESS_THAN, "=1/2", null, HSSFDataValidation.ERRORSTYLE.WARNING, "Less than (1/2) -> 12:00", "-", true, true, false);
            va.AddValidation(DVConstraint.OperatorType.GREATER_OR_EQUAL, "14:00", null, HSSFDataValidation.ERRORSTYLE.STOP, "Greater than or equal to 14:00", "Error box type = STOP", true, false, true);
            va.AddValidation(DVConstraint.OperatorType.LESS_OR_EQUAL, "14:00", null, HSSFDataValidation.ERRORSTYLE.STOP, "Less than or equal to 14:00", "-", false, true, false);
        }
Ejemplo n.º 27
0
            /**
             * Writes plain text values into cells in a tabular format to form comments readable from within
             * the spreadsheet.
             */
            private static void WriteDataValidationSettings(NPOI.SS.UserModel.Sheet sheet, NPOI.SS.UserModel.CellStyle style_1,
                                                            NPOI.SS.UserModel.CellStyle style_2, String strCondition, bool allowEmpty, bool inputBox,
                                                            bool errorBox)
            {
                Row row = sheet.CreateRow(sheet.PhysicalNumberOfRows);
                // condition's string
                Cell cell = row.CreateCell(1);

                cell.CellStyle = (style_1);
                SetCellValue(cell, strCondition);
                // allow empty cells
                cell           = row.CreateCell(2);
                cell.CellStyle = (style_2);
                SetCellValue(cell, ((allowEmpty) ? "yes" : "no"));
                // show input box
                cell           = row.CreateCell(3);
                cell.CellStyle = (style_2);
                SetCellValue(cell, ((inputBox) ? "yes" : "no"));
                // show error box
                cell           = row.CreateCell(4);
                cell.CellStyle = (style_2);
                SetCellValue(cell, ((errorBox) ? "yes" : "no"));
            }
Ejemplo n.º 28
0
            private static NPOI.SS.UserModel.CellStyle CreateHeaderStyle(HSSFWorkbook wb)
            {
                Font font = wb.CreateFont();

                font.Color      = (HSSFColor.WHITE.index);
                font.Boldweight = (short)FontBoldWeight.BOLD;

                NPOI.SS.UserModel.CellStyle cellStyle = wb.CreateCellStyle();
                cellStyle.FillForegroundColor = (HSSFColor.BLUE_GREY.index);
                cellStyle.FillPattern         = (FillPatternType.SOLID_FOREGROUND);
                cellStyle.Alignment           = (HorizontalAlignment.CENTER);
                cellStyle.VerticalAlignment   = (NPOI.SS.UserModel.VerticalAlignment.CENTER);
                cellStyle.BorderLeft          = (CellBorderType.THIN);
                cellStyle.LeftBorderColor     = (HSSFColor.WHITE.index);
                cellStyle.BorderTop           = (CellBorderType.THIN);
                cellStyle.TopBorderColor      = (HSSFColor.WHITE.index);
                cellStyle.BorderRight         = (CellBorderType.THIN);
                cellStyle.RightBorderColor    = (HSSFColor.WHITE.index);
                cellStyle.BorderBottom        = (CellBorderType.THIN);
                cellStyle.BottomBorderColor   = (HSSFColor.WHITE.index);
                cellStyle.SetFont(font);
                return(cellStyle);
            }
Ejemplo n.º 29
0
 /// <summary>
 /// Sets the format properties of the given style based on the given map.
 /// </summary>
 /// <param name="style">The cell style</param>
 /// <param name="workbook">The parent workbook.</param>
 /// <param name="properties">The map of format properties (String -&gt; Object).</param>
 private static void SetFormatProperties(
     NPOI.SS.UserModel.CellStyle style, HSSFWorkbook workbook, Hashtable properties)
 {
     style.Alignment           = (NPOI.SS.UserModel.HorizontalAlignment)GetShort(properties, ALIGNMENT);
     style.BorderBottom        = (NPOI.SS.UserModel.CellBorderType)GetShort(properties, BORDER_BOTTOM);
     style.BorderLeft          = (NPOI.SS.UserModel.CellBorderType)GetShort(properties, BORDER_LEFT);
     style.BorderRight         = (NPOI.SS.UserModel.CellBorderType)GetShort(properties, BORDER_RIGHT);
     style.BorderTop           = (NPOI.SS.UserModel.CellBorderType)GetShort(properties, BORDER_TOP);
     style.BottomBorderColor   = (GetShort(properties, BOTTOM_BORDER_COLOR));
     style.DataFormat          = (GetShort(properties, DATA_FORMAT));
     style.FillBackgroundColor = (GetShort(properties, FILL_BACKGROUND_COLOR));
     style.FillForegroundColor = (GetShort(properties, FILL_FOREGROUND_COLOR));
     style.FillPattern         = (NPOI.SS.UserModel.FillPatternType)GetShort(properties, FILL_PATTERN);
     style.SetFont(workbook.GetFontAt(GetShort(properties, FONT)));
     style.IsHidden          = (GetBoolean(properties, HIDDEN));
     style.Indention         = (GetShort(properties, INDENTION));
     style.LeftBorderColor   = (GetShort(properties, LEFT_BORDER_COLOR));
     style.IsLocked          = (GetBoolean(properties, LOCKED));
     style.RightBorderColor  = (GetShort(properties, RIGHT_BORDER_COLOR));
     style.Rotation          = (GetShort(properties, ROTATION));
     style.TopBorderColor    = (GetShort(properties, TOP_BORDER_COLOR));
     style.VerticalAlignment = (NPOI.SS.UserModel.VerticalAlignment)GetShort(properties, VERTICAL_ALIGNMENT);
     style.WrapText          = (GetBoolean(properties, WRAP_TEXT));
 }
Ejemplo n.º 30
0
    /// <summary>
    /// 金董春:添加打印功能,添加了TransportationBill.xls模板打印功能 2012-5-28
    /// </summary>
    /// <param name="list"></param>
    /// <returns></returns>
    public string WriteToFile(IList <object> list)
    {
        string path = Server.MapPath(".") + @"\Reports\Templates\YFKExcelTemplates\TransportationBill.xls";

        if (File.Exists(path))
        {
            TransportationBill tb       = (TransportationBill)list[0];
            string             filename = @"/Reports/Templates/TempFiles/temp_" + DateTime.Now.ToString("yyyyMMddhhmmss") + tb.BillNo + ".xls";
            string             _wpath   = Server.MapPath(".") + filename;
            File.Copy(path, _wpath);
            FileStream   file         = new FileStream(_wpath, FileMode.Open, FileAccess.ReadWrite, FileShare.Read);
            HSSFWorkbook hssfworkbook = new HSSFWorkbook(file);
            Sheet        sheet        = hssfworkbook.GetSheet("sheet1");
            NPOI.SS.UserModel.CellStyle normalStyle = setCellstyle(hssfworkbook, new string[] { "Border", "Center" });
            NPOI.SS.UserModel.CellStyle dateStyle   = setCellstyle(hssfworkbook, new string[] { "Border", "Center", "DateTime" });
            Cell cell = sheet.GetRow(2).GetCell(1);
            cell.SetCellValue(tb.BillNo);
            cell = sheet.GetRow(2).GetCell(6);
            cell.SetCellValue(tb.ReferenceBillNo);
            cell = sheet.GetRow(3).GetCell(1);
            cell.SetCellValue(tb.Status);
            int     i   = 10;
            decimal cnt = 0;
            foreach (TransportationBillDetail tbd in tb.TransportationBillDetails)
            {
                Row row = sheet.CreateRow(i);

                TransportationOrder tord = TheTransportationOrderMgr.LoadTransportationOrder(tbd.ActBill.OrderNo);
                row.CreateCell(0).SetCellValue(tord.CreateDate);                                                              //运输日期
                row.CreateCell(1).SetCellValue(tord.TransportationRoute != null ? tord.TransportationRoute.Description : ""); //运输路线
                row.CreateCell(2).SetCellValue(tbd.ActBill.PricingMethod != null ? tbd.ActBill.PricingMethod : "");           //运输形式
                row.CreateCell(3).SetCellValue(tord.OrderNo);                                                                 //运单号码
                row.CreateCell(4).SetCellValue(tbd.ActBill.EffectiveDate);                                                    //生效日期
                row.CreateCell(5).SetCellValue(tbd.ActBill.UnitPrice.ToString("F2"));                                         //单价
                row.CreateCell(6).SetCellValue(tbd.ActBill.Currency.Name);                                                    //币种
                row.CreateCell(7).SetCellValue(tbd.ActBill.BillQty.ToString("F0"));                                           //开票数
                row.CreateCell(8).SetCellValue(tbd.ActBill.BillAmount.ToString("F2"));                                        //金额
                cnt = Convert.ToInt32(tbd.ActBill.BillAmount) + cnt;
                for (int y = 0; y < 9; y++)
                {
                    row.GetCell(y).CellStyle = normalStyle;
                }
                row.GetCell(0).CellStyle = dateStyle;
                row.GetCell(4).CellStyle = dateStyle;
                i++;
            }
            if (i <= 20)
            {
                for (int j = i; j < 21; j++)
                {
                    Row row = sheet.CreateRow(j);
                    for (int y = 0; y < 9; y++)
                    {
                        row.CreateCell(y).CellStyle = normalStyle;
                    }
                }
                i = 20;
            }
            Row _row = sheet.CreateRow(i + 1);
            sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(i + 1, i + 1, 6, 7));
            _row.CreateCell(6).SetCellValue("合计发票金额:");
            _row.GetCell(6).CellStyle.Alignment = HorizontalAlignment.RIGHT;
            _row.CreateCell(8).SetCellValue(cnt.ToString("F2"));
            MemoryStream ms = new MemoryStream();
            hssfworkbook.Write(ms);

            // Response.AddHeader("Content-Disposition", string.Format("attachment;filename=TempWorkBook.xls"));
            // Response.BinaryWrite(ms.ToArray());Reports/Templates/TempFiles

            FileStream f    = new FileStream(_wpath, FileMode.Open, FileAccess.Write);
            byte[]     data = ms.ToArray();
            f.Write(data, 0, data.Length);
            f.Close();
            f.Dispose();
            hssfworkbook = null;
            ms.Close();
            ms.Dispose();
            return("http://" + Request.Url.Authority + filename);
        }

        return("");
    }