public void TestGetAnchorHeightInPoints()
        {
            HSSFWorkbook wb = new HSSFWorkbook();
            HSSFSheet sheet = (HSSFSheet)wb.CreateSheet("Test");
            HSSFClientAnchor a = new HSSFClientAnchor(0, 0, 1023, 255, (short)0, 0, (short)0, 0);
            float p = a.GetAnchorHeightInPoints(sheet);
            Assert.AreEqual(12.7, p, 0.001);

            sheet.CreateRow(0).HeightInPoints = (14);
            a = new HSSFClientAnchor(0, 0, 1023, 255, (short)0, 0, (short)0, 0);
            p = a.GetAnchorHeightInPoints(sheet);
            Assert.AreEqual(13.945, p, 0.001);

            a = new HSSFClientAnchor(0, 0, 1023, 127, (short)0, 0, (short)0, 0);
            p = a.GetAnchorHeightInPoints(sheet);
            Assert.AreEqual(6.945, p, 0.001);

            a = new HSSFClientAnchor(0, 126, 1023, 127, (short)0, 0, (short)0, 0);
            p = a.GetAnchorHeightInPoints(sheet);
            Assert.AreEqual(0.054, p, 0.001);

            a = new HSSFClientAnchor(0, 0, 1023, 0, (short)0, 0, (short)0, 1);
            p = a.GetAnchorHeightInPoints(sheet);
            Assert.AreEqual(14.0, p, 0.001);

            sheet.CreateRow(0).HeightInPoints = (12);
            a = new HSSFClientAnchor(0, 127, 1023, 127, (short)0, 0, (short)0, 1);
            p = a.GetAnchorHeightInPoints(sheet);
            Assert.AreEqual(12.372, p, 0.001);

        }
示例#2
0
        public HSSFShapeGroup(EscherContainerRecord spgrContainer, ObjRecord objRecord)
            : base(spgrContainer, objRecord)
        {
            // read internal and external coordinates from spgrContainer
            EscherContainerRecord spContainer = spgrContainer.ChildContainers[0];

            _spgrRecord = (EscherSpgrRecord)spContainer.GetChild(0);
            foreach (EscherRecord ch in spContainer.ChildRecords)
            {
                switch (ch.RecordId)
                {
                case EscherSpgrRecord.RECORD_ID:
                    break;

                case EscherClientAnchorRecord.RECORD_ID:
                    anchor = new HSSFClientAnchor((EscherClientAnchorRecord)ch);
                    break;

                case EscherChildAnchorRecord.RECORD_ID:
                    anchor = new HSSFChildAnchor((EscherChildAnchorRecord)ch);
                    break;

                default:
                    break;
                }
            }
        }
示例#3
0
        public void Test44916()
        {

            HSSFWorkbook wb = new HSSFWorkbook();
            NPOI.SS.UserModel.ISheet sheet = wb.CreateSheet();

            // 1. Create drawing patriarch
            IDrawing patr = sheet.CreateDrawingPatriarch();

            // 2. Try to re-get the patriarch
            IDrawing existingPatr;
            try
            {
                existingPatr = sheet.DrawingPatriarch;
            }
            catch (NullReferenceException)
            {
                throw new AssertionException("Identified bug 44916");
            }

            // 3. Use patriarch
            HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 600, 245, (short)1, 1, (short)1, 2);
            anchor.AnchorType = (AnchorType)(3);
            byte[] pictureData = HSSFTestDataSamples.GetTestDataFileContent("logoKarmokar4.png");
            int idx1 = wb.AddPicture(pictureData, PictureType.PNG);
            patr.CreatePicture(anchor, idx1);

            // 4. Try to re-use patriarch later
            existingPatr = sheet.DrawingPatriarch;
            Assert.IsNotNull(existingPatr);
        }
示例#4
0
 /// <summary>
 /// Creates a simple shape.  This includes such shapes as lines, rectangles,
 /// and ovals.
 /// </summary>
 /// <param name="anchor">the client anchor describes how this Group is attached
 /// to the sheet.</param>
 /// <returns>the newly created shape.</returns>
 public HSSFSimpleShape CreateSimpleShape(HSSFClientAnchor anchor)
 {
     HSSFSimpleShape shape = new HSSFSimpleShape(null, anchor);
     shape.Anchor = anchor;
     shapes.Add(shape);
     return shape;
 }
示例#5
0
        protected override EscherContainerRecord CreateSpContainer()
        {
            EscherContainerRecord  spContainer = new EscherContainerRecord();
            EscherSpRecord         sp          = new EscherSpRecord();
            EscherOptRecord        opt         = new EscherOptRecord();
            EscherClientDataRecord clientData  = new EscherClientDataRecord();

            spContainer.RecordId = (EscherContainerRecord.SP_CONTAINER);
            spContainer.Options  = ((short)0x000F);
            sp.RecordId          = (EscherSpRecord.RECORD_ID);
            sp.Options           = ((short)((EscherAggregate.ST_HOSTCONTROL << 4) | 0x2));

            sp.Flags     = (EscherSpRecord.FLAG_HAVEANCHOR | EscherSpRecord.FLAG_HASSHAPETYPE);
            opt.RecordId = (EscherOptRecord.RECORD_ID);
            opt.AddEscherProperty(new EscherBoolProperty(EscherProperties.PROTECTION__LOCKAGAINSTGROUPING, 17039620));
            opt.AddEscherProperty(new EscherBoolProperty(EscherProperties.TEXT__SIZE_TEXT_TO_FIT_SHAPE, 0x00080008));
            opt.AddEscherProperty(new EscherBoolProperty(EscherProperties.LINESTYLE__NOLINEDRAWDASH, 0x00080000));
            opt.AddEscherProperty(new EscherSimpleProperty(EscherProperties.GROUPSHAPE__PRINT, 0x00020000));

            HSSFClientAnchor userAnchor = (HSSFClientAnchor)Anchor;

            userAnchor.AnchorType = (1);
            EscherRecord anchor = userAnchor.GetEscherAnchor();

            clientData.RecordId = (EscherClientDataRecord.RECORD_ID);
            clientData.Options  = ((short)0x0000);

            spContainer.AddChildRecord(sp);
            spContainer.AddChildRecord(opt);
            spContainer.AddChildRecord(anchor);
            spContainer.AddChildRecord(clientData);

            return(spContainer);
        }
示例#6
0
 /// <summary>
 /// Creates a new Group record stored Under this patriarch.
 /// </summary>
 /// <param name="anchor">the client anchor describes how this Group is attached
 /// to the sheet.</param>
 /// <returns>the newly created Group.</returns>
 public HSSFShapeGroup CreateGroup(HSSFClientAnchor anchor)
 {
     HSSFShapeGroup group = new HSSFShapeGroup(null, anchor);
     group.Anchor = anchor;
     shapes.Add(group);
     return group;
 }
示例#7
0
        /// <summary>
        /// Creates a new Group record stored Under this patriarch.
        /// </summary>
        /// <param name="anchor">the client anchor describes how this Group is attached
        /// to the sheet.</param>
        /// <returns>the newly created Group.</returns>
        public HSSFShapeGroup CreateGroup(HSSFClientAnchor anchor)
        {
            HSSFShapeGroup group = new HSSFShapeGroup(null, anchor);

            AddShape(group);
            OnCreate(group);
            return(group);
        }
示例#8
0
        /// <summary>
        /// Creates a new Group record stored Under this patriarch.
        /// </summary>
        /// <param name="anchor">the client anchor describes how this Group is attached
        /// to the sheet.</param>
        /// <returns>the newly created Group.</returns>
        public HSSFShapeGroup CreateGroup(HSSFClientAnchor anchor)
        {
            HSSFShapeGroup group = new HSSFShapeGroup(null, anchor);

            group.Anchor = anchor;
            shapes.Add(group);
            return(group);
        }
示例#9
0
        /// <summary>
        /// Creates a simple shape.  This includes such shapes as lines, rectangles,
        /// and ovals.
        /// </summary>
        /// <param name="anchor">the client anchor describes how this Group is attached
        /// to the sheet.</param>
        /// <returns>the newly created shape.</returns>
        public HSSFSimpleShape CreateSimpleShape(HSSFClientAnchor anchor)
        {
            HSSFSimpleShape shape = new HSSFSimpleShape(null, anchor);

            shape.Anchor = anchor;
            AddShape(shape);
            return(shape);
        }
示例#10
0
        /// <summary>
        /// Creates a simple shape.  This includes such shapes as lines, rectangles,
        /// and ovals.
        /// </summary>
        /// <param name="anchor">the client anchor describes how this Group is attached
        /// to the sheet.</param>
        /// <returns>the newly created shape.</returns>
        public HSSFSimpleShape CreateSimpleShape(HSSFClientAnchor anchor)
        {
            HSSFSimpleShape shape = new HSSFSimpleShape(null, anchor);

            AddShape(shape);
            //open existing file
            OnCreate(shape);
            return(shape);
        }
示例#11
0
        /// <summary>
        /// Creates a picture.
        /// </summary>
        /// <param name="anchor">the client anchor describes how this Group is attached
        /// to the sheet.</param>
        /// <param name="pictureIndex">Index of the picture.</param>
        /// <returns>the newly created shape.</returns>
        public IPicture CreatePicture(HSSFClientAnchor anchor, int pictureIndex)
        {
            HSSFPicture shape = new HSSFPicture(null, (HSSFClientAnchor)anchor);

            shape.PictureIndex = pictureIndex;
            AddShape(shape);

            //open existing file
            OnCreate(shape);
            return(shape);
        }
示例#12
0
        /// <summary>
        /// Calculate the preferred size for this picture.
        /// </summary>
        /// <param name="scale">the amount by which image dimensions are multiplied relative to the original size.</param>
        /// <returns>HSSFClientAnchor with the preferred size for this image</returns>
        public HSSFClientAnchor GetPreferredSize(double scale)
        {
            HSSFClientAnchor anchor = (HSSFClientAnchor)Anchor;

            Size   size         = GetImageDimension();
            double scaledWidth  = size.Width * scale;
            double scaledHeight = size.Height * scale;

            float w = 0;

            //space in the leftmost cell
            w += GetColumnWidthInPixels(anchor.Col1) * (1 - (float)anchor.Dx1 / 1024);
            short col2 = (short)(anchor.Col1 + 1);
            int   dx2  = 0;

            while (w < scaledWidth)
            {
                w += GetColumnWidthInPixels(col2++);
            }

            if (w > scaledWidth)
            {
                //calculate dx2, offset in the rightmost cell
                col2--;
                double cw    = GetColumnWidthInPixels(col2);
                double delta = w - scaledWidth;
                dx2 = (int)((cw - delta) / cw * 1024);
            }
            anchor.Col2 = col2;
            anchor.Dx2  = dx2;

            float h = 0;

            h += (1 - (float)anchor.Dy1 / 256) * GetRowHeightInPixels(anchor.Row1);
            int row2 = anchor.Row1 + 1;
            int dy2  = 0;

            while (h < scaledHeight)
            {
                h += GetRowHeightInPixels(row2++);
            }
            if (h > scaledHeight)
            {
                row2--;
                double ch    = GetRowHeightInPixels(row2);
                double delta = h - scaledHeight;
                dy2 = (int)((ch - delta) / ch * 256);
            }
            anchor.Row2 = row2;
            anchor.Dy2  = dy2;

            return(anchor);
        }
示例#13
0
        /// <summary>
        /// Creates a picture.
        /// </summary>
        /// <param name="anchor">the client anchor describes how this Group is attached
        /// to the sheet.</param>
        /// <param name="pictureIndex">Index of the picture.</param>
        /// <returns>the newly created shape.</returns>
        public IPicture CreatePicture(HSSFClientAnchor anchor, int pictureIndex)
        {
            HSSFPicture shape = new HSSFPicture(null, (HSSFClientAnchor)anchor);

            shape.PictureIndex = pictureIndex;
            shape.Anchor       = (HSSFClientAnchor)anchor;
            AddShape(shape);

            EscherBSERecord bse = (_sheet.Workbook as HSSFWorkbook).Workbook.GetBSERecord(pictureIndex);

            bse.Ref = (bse.Ref + 1);
            return(shape);
        }
示例#14
0
        public void TestShapeId()
        {

            HSSFClientAnchor anchor = new HSSFClientAnchor();
            AbstractShape shape;
            CommonObjectDataSubRecord cmo;

            shape = new TextboxShape(new HSSFTextbox(null, anchor), 1025);
            cmo = (CommonObjectDataSubRecord)shape.ObjRecord.SubRecords[(0)];
            Assert.AreEqual(1, cmo.ObjectId);

            shape = new PictureShape(new HSSFPicture(null, anchor), 1026);
            cmo = (CommonObjectDataSubRecord)shape.ObjRecord.SubRecords[(0)];
            Assert.AreEqual(2, cmo.ObjectId);

            shape = new CommentShape(new HSSFComment(null, anchor), 1027);
            cmo = (CommonObjectDataSubRecord)shape.ObjRecord.SubRecords[(0)];
            Assert.AreEqual(1027, cmo.ObjectId);
        }
示例#15
0
        /**
         * Resize the image
         * <p>
         * Please note, that this method works correctly only for workbooks
         * with default font size (Arial 10pt for .xls).
         * If the default font is changed the resized image can be streched vertically or horizontally.
         * </p>
         * <p>
         * <code>resize(1.0,1.0)</code> keeps the original size,<br/>
         * <code>resize(0.5,0.5)</code> resize to 50% of the original,<br/>
         * <code>resize(2.0,2.0)</code> resizes to 200% of the original.<br/>
         * <code>resize({@link Double#MAX_VALUE},{@link Double#MAX_VALUE})</code> resizes to the dimension of the embedded image.
         * </p>
         *
         * @param scaleX the amount by which the image width is multiplied relative to the original width.
         * @param scaleY the amount by which the image height is multiplied relative to the original height.
         */
        public void Resize(double scaleX, double scaleY)
        {
            HSSFClientAnchor anchor = (HSSFClientAnchor)ClientAnchor;

            anchor.AnchorType = AnchorType.MoveDontResize;

            HSSFClientAnchor pref = GetPreferredSize(scaleX, scaleY) as HSSFClientAnchor;

            int row2 = anchor.Row1 + (pref.Row2 - pref.Row1);
            int col2 = anchor.Col1 + (pref.Col2 - pref.Col1);

            anchor.Col2 = ((short)col2);
            // anchor.setDx1(0);
            anchor.Dx2 = (pref.Dx2);

            anchor.Row2 = (row2);
            // anchor.setDy1(0);
            anchor.Dy2 = (pref.Dy2);
        }
示例#16
0
        /// <summary>
        /// Reset the image to the original size.
        /// </summary>
        public void Resize(double scale)
        {
            HSSFClientAnchor anchor = (HSSFClientAnchor)Anchor;

            anchor.AnchorType = 2;

            IClientAnchor pref = GetPreferredSize(scale);

            int row2 = anchor.Row1 + (pref.Row2 - pref.Row1);
            int col2 = anchor.Col1 + (pref.Col2 - pref.Col1);

            anchor.Col2 = col2;
            anchor.Dx1  = 0;
            anchor.Dx2  = pref.Dx2;

            anchor.Row2 = row2;
            anchor.Dy1  = 0;
            anchor.Dy2  = pref.Dy2;
        }
示例#17
0
        static void Main(string[] args)
        {
            InitializeWorkbook();

            ISheet sheet1 = hssfworkbook.CreateSheet("PictureSheet");
            
            
            HSSFPatriarch patriarch = (HSSFPatriarch)sheet1.CreateDrawingPatriarch();
            //create the anchor
            HSSFClientAnchor anchor;
            anchor = new HSSFClientAnchor(500, 200, 0, 0, 2, 2, 4, 7);
            anchor.AnchorType = 2;
            //load the picture and get the picture index in the workbook
            HSSFPicture picture = (HSSFPicture)patriarch.CreatePicture(anchor, LoadImage("../../image/HumpbackWhale.jpg", hssfworkbook));
            //Reset the image to the original size.
            //picture.Resize();   //Note: Resize will reset client anchor you set.
            picture.LineStyle = LineStyle.DashDotGel;

            WriteToFile();
        }
示例#18
0
        public override bool Equals(Object obj)
        {
            if (obj == null)
            {
                return(false);
            }
            if (obj == this)
            {
                return(true);
            }
            if (obj.GetType() != GetType())
            {
                return(false);
            }
            HSSFClientAnchor anchor = (HSSFClientAnchor)obj;

            return(anchor.Col1 == Col1 && anchor.Col2 == Col2 && anchor.Dx1 == Dx1 &&
                   anchor.Dx2 == Dx2 && anchor.Dy1 == Dy1 && anchor.Dy2 == Dy2 &&
                   anchor.Row1 == Row1 && anchor.Row2 == Row2 && anchor.AnchorType == AnchorType);
        }
示例#19
0
 public HSSFShapeGroup(EscherContainerRecord spgrContainer, ObjRecord objRecord)
     : base(spgrContainer, objRecord)
 {
     // read internal and external coordinates from spgrContainer
     EscherContainerRecord spContainer = spgrContainer.ChildContainers[0];
     _spgrRecord = (EscherSpgrRecord)spContainer.GetChild(0);
     foreach (EscherRecord ch in spContainer.ChildRecords)
     {
         switch (ch.RecordId)
         {
             case EscherSpgrRecord.RECORD_ID:
                 break;
             case EscherClientAnchorRecord.RECORD_ID:
                 anchor = new HSSFClientAnchor((EscherClientAnchorRecord)ch);
                 break;
             case EscherChildAnchorRecord.RECORD_ID:
                 anchor = new HSSFChildAnchor((EscherChildAnchorRecord)ch);
                 break;
         }
     }
 }
示例#20
0
        public void TestDefaultValues()
        {
            HSSFClientAnchor clientAnchor = new HSSFClientAnchor();
            Assert.AreEqual(clientAnchor.AnchorType, 0);
            Assert.AreEqual(clientAnchor.Col1, 0);
            Assert.AreEqual(clientAnchor.Col2, 0);
            Assert.AreEqual(clientAnchor.Dx1, 0);
            Assert.AreEqual(clientAnchor.Dx2, 0);
            Assert.AreEqual(clientAnchor.Dy1, 0);
            Assert.AreEqual(clientAnchor.Dy2, 0);
            Assert.AreEqual(clientAnchor.Row1, 0);
            Assert.AreEqual(clientAnchor.Row2, 0);

            clientAnchor = new HSSFClientAnchor(new EscherClientAnchorRecord());
            Assert.AreEqual(clientAnchor.AnchorType, 0);
            Assert.AreEqual(clientAnchor.Col1, 0);
            Assert.AreEqual(clientAnchor.Col2, 0);
            Assert.AreEqual(clientAnchor.Dx1, 0);
            Assert.AreEqual(clientAnchor.Dx2, 0);
            Assert.AreEqual(clientAnchor.Dy1, 0);
            Assert.AreEqual(clientAnchor.Dy2, 0);
            Assert.AreEqual(clientAnchor.Row1, 0);
            Assert.AreEqual(clientAnchor.Row2, 0);

            HSSFChildAnchor childAnchor = new HSSFChildAnchor();
            Assert.AreEqual(childAnchor.Dx1, 0);
            Assert.AreEqual(childAnchor.Dx2, 0);
            Assert.AreEqual(childAnchor.Dy1, 0);
            Assert.AreEqual(childAnchor.Dy2, 0);

            childAnchor = new HSSFChildAnchor(new EscherChildAnchorRecord());
            Assert.AreEqual(childAnchor.Dx1, 0);
            Assert.AreEqual(childAnchor.Dx2, 0);
            Assert.AreEqual(childAnchor.Dy1, 0);
            Assert.AreEqual(childAnchor.Dy2, 0);
        }
示例#21
0
        /// <summary>
        /// 获取并填充二维条码图片
        /// </summary>
        /// <param name="barCode">条码</param>
        /// <param name="col1">图片填充起始列,从0开始</param>
        /// <param name="row1">图片填充起始行,从0开始</param>
        /// <param name="col2">图片填充终止列,从0开始</param>
        /// <param name="row2">图片填充终止行,从0开始</param>
        protected void Fill2DBarCodeImage(int pageIndex, int col1, int row1, int col2, int row2, string barCode, int qRCodeScale = 6)
        {
            QRCodeEncoder qrCodeEncoder = new QRCodeEncoder();
            qrCodeEncoder.QRCodeEncodeMode = QRCodeEncoder.ENCODE_MODE.ALPHA_NUMERIC;
            qrCodeEncoder.QRCodeScale = qRCodeScale;
            qrCodeEncoder.QRCodeVersion = 1;
            qrCodeEncoder.QRCodeErrorCorrect = QRCodeEncoder.ERROR_CORRECTION.Q;
            var image = qrCodeEncoder.Encode(barCode);

            MemoryStream ms = new MemoryStream();
            image.Save(ms, System.Drawing.Imaging.ImageFormat.Png);
            var patriarch = this.sheet.CreateDrawingPatriarch();

            int _rowIndex = this.GetRowIndexAbsolute(pageIndex, 0);
            HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, col1, row1 + _rowIndex, col2 + 1, row2 + _rowIndex + 1);
            anchor.AnchorType = 2;
            int index = this.workbook.AddPicture(ms.ToArray(), NPOI.SS.UserModel.PictureType.PNG);
            var signaturePicture = patriarch.CreatePicture(anchor, index);
            //signaturePicture.Resize();
        }
示例#22
0
        /// <summary>
        /// Creates a simple shape.  This includes such shapes as lines, rectangles,
        /// and ovals.
        /// </summary>
        /// <param name="anchor">the client anchor describes how this Group is attached
        /// to the sheet.</param>
        /// <returns>the newly created shape.</returns>
        public HSSFSimpleShape CreateSimpleShape(HSSFClientAnchor anchor)
        {
            HSSFSimpleShape shape = new HSSFSimpleShape(null, anchor);

            AddShape(shape);
            //open existing file
            OnCreate(shape);
            return shape;
        }
示例#23
0
        /// <summary>
        /// Creates a new Group record stored Under this patriarch.
        /// </summary>
        /// <param name="anchor">the client anchor describes how this Group is attached
        /// to the sheet.</param>
        /// <returns>the newly created Group.</returns>
        public HSSFShapeGroup CreateGroup(HSSFClientAnchor anchor)
        {
            HSSFShapeGroup group = new HSSFShapeGroup(null, anchor);

            AddShape(group);
            OnCreate(group);
            return group;
        }
示例#24
0
        public void TestBug45312()
        {
            HSSFWorkbook wb = new HSSFWorkbook();
            try
            {
                HSSFSheet sheet = wb.CreateSheet() as HSSFSheet;
                HSSFPatriarch patriarch = sheet.CreateDrawingPatriarch() as HSSFPatriarch;

                {
                    HSSFClientAnchor a1 = new HSSFClientAnchor();
                    a1.SetAnchor((short)1, 1, 0, 0, (short)1, 1, 512, 100);
                    HSSFSimpleShape shape1 = patriarch.CreateSimpleShape(a1);
                    shape1.ShapeType = (/*setter*/HSSFSimpleShape.OBJECT_TYPE_LINE);
                }
                {
                    HSSFClientAnchor a1 = new HSSFClientAnchor();
                    a1.SetAnchor((short)1, 1, 512, 0, (short)1, 1, 1024, 100);
                    HSSFSimpleShape shape1 = patriarch.CreateSimpleShape(a1);
                    shape1.FlipVertical=(true);
                    shape1.ShapeType = (/*setter*/HSSFSimpleShape.OBJECT_TYPE_LINE);
                }

                {
                    HSSFClientAnchor a1 = new HSSFClientAnchor();
                    a1.SetAnchor((short)2, 2, 0, 0, (short)2, 2, 512, 100);
                    HSSFSimpleShape shape1 = patriarch.CreateSimpleShape(a1);
                    shape1.ShapeType = (/*setter*/HSSFSimpleShape.OBJECT_TYPE_LINE);
                }
                {
                    HSSFClientAnchor a1 = new HSSFClientAnchor();
                    a1.SetAnchor((short)2, 2, 0, 100, (short)2, 2, 512, 200);
                    HSSFSimpleShape shape1 = patriarch.CreateSimpleShape(a1);
                    shape1.FlipHorizontal = (/*setter*/true);
                    shape1.ShapeType = (/*setter*/HSSFSimpleShape.OBJECT_TYPE_LINE);
                }

                /*OutputStream stream = new FileOutputStream("/tmp/45312.xls");
                try {
                    wb.Write(stream);
                } finally {
                    stream.Close();
                }*/

                CheckWorkbookBack(wb);
            }
            finally
            {
                //wb.Close();
            }
        }
示例#25
0
        /**
         * create a rectangle, save the workbook, read back and verify that all shape properties are there
         */
        public void TestReadWriteRectangle()
        {

            HSSFWorkbook wb = new HSSFWorkbook();
            HSSFSheet sheet = wb.CreateSheet() as HSSFSheet;

            HSSFPatriarch drawing = sheet.CreateDrawingPatriarch() as HSSFPatriarch;
            HSSFClientAnchor anchor = new HSSFClientAnchor(10, 10, 50, 50, (short)2, 2, (short)4, 4);
            anchor.AnchorType = (AnchorType)(2);
            Assert.AreEqual(anchor.AnchorType, 2);

            HSSFSimpleShape rectangle = drawing.CreateSimpleShape(anchor);
            rectangle.ShapeType=(HSSFSimpleShape.OBJECT_TYPE_RECTANGLE);
            rectangle.LineWidth=(10000);
            rectangle.FillColor=(777);
            Assert.AreEqual(rectangle.FillColor, 777);
            Assert.AreEqual(10000, rectangle.LineWidth);
            rectangle.LineStyle= (LineStyle)(10);
            Assert.AreEqual(10, rectangle.LineStyle);
            Assert.AreEqual(rectangle.WrapText, HSSFSimpleShape.WRAP_SQUARE);
            rectangle.LineStyleColor=(1111);
            rectangle.IsNoFill=(true);
            rectangle.WrapText=(HSSFSimpleShape.WRAP_NONE);
            rectangle.String=(new HSSFRichTextString("teeeest"));
            Assert.AreEqual(rectangle.LineStyleColor, 1111);
            Assert.AreEqual(((EscherSimpleProperty)((EscherOptRecord)HSSFTestHelper.GetEscherContainer(rectangle).GetChildById(EscherOptRecord.RECORD_ID))
                    .Lookup(EscherProperties.TEXT__TEXTID)).PropertyValue, "teeeest".GetHashCode());
            Assert.AreEqual(rectangle.IsNoFill, true);
            Assert.AreEqual(rectangle.WrapText, HSSFSimpleShape.WRAP_NONE);
            Assert.AreEqual(rectangle.String.String, "teeeest");

            wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
            sheet = wb.GetSheetAt(0) as HSSFSheet;
            drawing = sheet.DrawingPatriarch as HSSFPatriarch;
            Assert.AreEqual(1, drawing.Children.Count);

            HSSFSimpleShape rectangle2 =
                    (HSSFSimpleShape)drawing.Children[0];
            Assert.AreEqual(HSSFSimpleShape.OBJECT_TYPE_RECTANGLE,
                    rectangle2.ShapeType);
            Assert.AreEqual(10000, rectangle2.LineWidth);
            Assert.AreEqual(10, (int)rectangle2.LineStyle);
            Assert.AreEqual(anchor, rectangle2.Anchor);
            Assert.AreEqual(rectangle2.LineStyleColor, 1111);
            Assert.AreEqual(rectangle2.FillColor, 777);
            Assert.AreEqual(rectangle2.IsNoFill, true);
            Assert.AreEqual(rectangle2.String.String, "teeeest");
            Assert.AreEqual(rectangle.WrapText, HSSFSimpleShape.WRAP_NONE);

            rectangle2.FillColor=(3333);
            rectangle2.LineStyle = (LineStyle)(9);
            rectangle2.LineStyleColor=(4444);
            rectangle2.IsNoFill=(false);
            rectangle2.LineWidth=(77);
            rectangle2.Anchor.Dx1=2;
            rectangle2.Anchor.Dx2=3;
            rectangle2.Anchor.Dy1=(4);
            rectangle2.Anchor.Dy2=(5);
            rectangle.WrapText=(HSSFSimpleShape.WRAP_BY_POINTS);
            rectangle2.String=(new HSSFRichTextString("test22"));

            wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
            sheet = wb.GetSheetAt(0) as HSSFSheet;
            drawing = sheet.DrawingPatriarch as HSSFPatriarch;
            Assert.AreEqual(1, drawing.Children.Count);
            rectangle2 = (HSSFSimpleShape)drawing.Children[0];
            Assert.AreEqual(HSSFSimpleShape.OBJECT_TYPE_RECTANGLE, rectangle2.ShapeType);
            Assert.AreEqual(rectangle.WrapText, HSSFSimpleShape.WRAP_BY_POINTS);
            Assert.AreEqual(77, rectangle2.LineWidth);
            Assert.AreEqual(9, rectangle2.LineStyle);
            Assert.AreEqual(rectangle2.LineStyleColor, 4444);
            Assert.AreEqual(rectangle2.FillColor, 3333);
            Assert.AreEqual(rectangle2.Anchor.Dx1, 2);
            Assert.AreEqual(rectangle2.Anchor.Dx2, 3);
            Assert.AreEqual(rectangle2.Anchor.Dy1, 4);
            Assert.AreEqual(rectangle2.Anchor.Dy2, 5);
            Assert.AreEqual(rectangle2.IsNoFill, false);
            Assert.AreEqual(rectangle2.String.String, "test22");

            HSSFSimpleShape rect3 = drawing.CreateSimpleShape(new HSSFClientAnchor());
            rect3.ShapeType=(HSSFSimpleShape.OBJECT_TYPE_RECTANGLE);
            wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);

            drawing = (wb.GetSheetAt(0) as HSSFSheet).DrawingPatriarch as HSSFPatriarch;
            Assert.AreEqual(drawing.Children.Count, 2);
        }
示例#26
0
        /**
     * Adds a new OLE Package Shape 
     * 
     * @param anchor       the client anchor describes how this picture is
     *                     attached to the sheet.
     * @param storageId    the storageId returned by {@Link HSSFWorkbook.AddOlePackage}
     * @param pictureIndex the index of the picture (used as preview image) in the
     *                     workbook collection of pictures.
     *
     * @return newly Created shape
     */
        public HSSFObjectData CreateObjectData(HSSFClientAnchor anchor, int storageId, int pictureIndex)
        {
            ObjRecord obj = new ObjRecord();

            CommonObjectDataSubRecord ftCmo = new CommonObjectDataSubRecord();
            ftCmo.ObjectType = (/*setter*/CommonObjectType.Picture);
            // ftCmo.ObjectId=(/*setter*/oleShape.ShapeId); ... will be Set by onCreate(...)
            ftCmo.IsLocked = (/*setter*/true);
            ftCmo.IsPrintable = (/*setter*/true);
            ftCmo.IsAutoFill = (/*setter*/true);
            ftCmo.IsAutoline = (/*setter*/true);
            ftCmo.Reserved1 = (/*setter*/0);
            ftCmo.Reserved2 = (/*setter*/0);
            ftCmo.Reserved3 = (/*setter*/0);
            obj.AddSubRecord(ftCmo);

            // FtCf (pictFormat) 
            FtCfSubRecord ftCf = new FtCfSubRecord();
            HSSFPictureData pictData = Sheet.Workbook.GetAllPictures()[(pictureIndex - 1)] as HSSFPictureData;
            switch ((PictureType)pictData.Format)
            {
                case PictureType.WMF:
                case PictureType.EMF:
                    // this needs patch #49658 to be applied to actually work 
                    ftCf.Flags = (/*setter*/FtCfSubRecord.METAFILE_BIT);
                    break;
                case PictureType.DIB:
                case PictureType.PNG:
                case PictureType.JPEG:
                case PictureType.PICT:
                    ftCf.Flags = (/*setter*/FtCfSubRecord.BITMAP_BIT);
                    break;
            }
            obj.AddSubRecord(ftCf);
            // FtPioGrbit (pictFlags)
            FtPioGrbitSubRecord ftPioGrbit = new FtPioGrbitSubRecord();
            ftPioGrbit.SetFlagByBit(FtPioGrbitSubRecord.AUTO_PICT_BIT, true);
            obj.AddSubRecord(ftPioGrbit);

            EmbeddedObjectRefSubRecord ftPictFmla = new EmbeddedObjectRefSubRecord();
            ftPictFmla.SetUnknownFormulaData(new byte[] { 2, 0, 0, 0, 0 });
            ftPictFmla.OLEClassName = (/*setter*/"Paket");
            ftPictFmla.SetStorageId(storageId);

            obj.AddSubRecord(ftPictFmla);
            obj.AddSubRecord(new EndSubRecord());

            String entryName = "MBD" + HexDump.ToHex(storageId);
            DirectoryEntry oleRoot;
            try
            {
                DirectoryNode dn = (_sheet.Workbook as HSSFWorkbook).RootDirectory;
                if (dn == null) throw new FileNotFoundException();
                oleRoot = (DirectoryEntry)dn.GetEntry(entryName);
            }
            catch (FileNotFoundException e)
            {
                throw new InvalidOperationException("trying to add ole shape without actually Adding data first - use HSSFWorkbook.AddOlePackage first", e);
            }

            // create picture shape, which need to be minimal modified for oleshapes
            HSSFPicture shape = new HSSFPicture(null, anchor);
            shape.PictureIndex = (/*setter*/pictureIndex);
            EscherContainerRecord spContainer = shape.GetEscherContainer();
            EscherSpRecord spRecord = spContainer.GetChildById(EscherSpRecord.RECORD_ID) as EscherSpRecord;
            spRecord.Flags = (/*setter*/spRecord.Flags | EscherSpRecord.FLAG_OLESHAPE);

            HSSFObjectData oleShape = new HSSFObjectData(spContainer, obj, oleRoot);
            AddShape(oleShape);
            OnCreate(oleShape);


            return oleShape;
        }
示例#27
0
 private void InsertImageToCell(MemoryStream imageStream, int left, int top)
 {
     //store the coordinates of which cell and where in the cell the image goes
     HSSFClientAnchor anchor = new HSSFClientAnchor(1, 1, 0, 0, left, top, left + 1, top + 1);
     //types are 0, 2, and 3. 0 resizes within the cell, 2 doesn't
     anchor.AnchorType = 2;
     //add the byte array and encode it for the excel file
     int index = Book.AddPicture(imageStream.ToArray(), PictureType.PNG);
     IPicture signaturePicture = patriarch.CreatePicture(anchor, index);
 }
示例#28
0
        /// <summary>
        /// DataTable导出到Excel的MemoryStream
        /// </summary>
        /// <param name="dtSource">源DataTable</param>
        /// <param name="strHeaderText">表头文本</param>
        public static MemoryStream Export(DataTable dtSource, string strHeaderText = "统计数据", string strImageFileName = "")
        {
            IWorkbook workbook = new HSSFWorkbook();
            #region 填充图片
            if (strImageFileName != "")
            {
                ISheet sheet1 = workbook.CreateSheet("图标");
                byte[] bytes = System.IO.File.ReadAllBytes(strImageFileName);
                int pictureIdx = workbook.AddPicture(bytes, PictureType.JPEG);

                // Create the drawing patriarch.  This is the top level container for all shapes.
                HSSFPatriarch patriarch = (HSSFPatriarch)sheet1.CreateDrawingPatriarch();

                //add a picture
                HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, 0, 0, 20, 20);
                HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
                pict.Resize();
                //rowIndex += 20;
            }
            #endregion
            if (strHeaderText == "")
            {
                strHeaderText = "统计数据";
            }
            ISheet sheet = workbook.CreateSheet(strHeaderText);


            ICellStyle dateStyle = workbook.CreateCellStyle();
            IDataFormat format = workbook.CreateDataFormat();
            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;
                arrColWidth[item.Ordinal] = arrColWidth[item.Ordinal] > 50 ? 50 : arrColWidth[item.Ordinal];
            }
            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] && intTemp < 50)
                    {
                        arrColWidth[j] = intTemp;
                    }
                }
            }
            int rowIndex = 0;
            foreach (DataRow row in dtSource.Rows)
            {
                #region 新建表,填充表头,填充列头,样式
                if (rowIndex == 65535 || rowIndex == 0)
                {
                    if (rowIndex != 0)
                    {
                        sheet = workbook.CreateSheet();
                    }
                    if (strHeaderText != "")
                    {
                        #region 表头及样式
                        {
                            IRow headerRow = sheet.CreateRow(0);
                            headerRow.HeightInPoints = 25;
                            headerRow.CreateCell(0).SetCellValue(strHeaderText);

                            ICellStyle headStyle = workbook.CreateCellStyle();
                            headStyle.Alignment = HorizontalAlignment.Center;
                            IFont font = workbook.CreateFont();
                            font.FontHeightInPoints = 20;
                            font.Boldweight = 700;
                            headStyle.SetFont(font);
                            headerRow.GetCell(0).CellStyle = headStyle;
                            sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
                            //headerRow.Dispose();
                        }
                        #endregion
                        rowIndex = 1;
                    }
                    else
                    {
                        rowIndex = 0;
                    }


                    #region 列头及样式
                    {
                        IRow headerRow = sheet.CreateRow(rowIndex);
                        ICellStyle headStyle = workbook.CreateCellStyle();
                        headStyle.Alignment = HorizontalAlignment.Center;
                        IFont font = workbook.CreateFont();
                        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;

                            //设置列宽
                            sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
                        }
                        //headerRow.Dispose();
                    }
                    #endregion

                    rowIndex++;



                }
                #endregion



                #region 填充内容
                IRow dataRow = sheet.CreateRow(rowIndex);
                foreach (DataColumn column in dtSource.Columns)
                {
                    ICell newCell = dataRow.CreateCell(column.Ordinal);

                    string drValue = row[column].ToString();

                    switch (column.DataType.ToString())
                    {
                        case "System.String"://字符串类型
                            newCell.SetCellValue(drValue);
                            break;
                        case "System.DateTime"://日期类型
                            DateTime dateV;
                            DateTime.TryParse(drValue, out dateV);
                            newCell.SetCellValue(dateV);

                            newCell.CellStyle = dateStyle;//格式化显示
                            break;
                        case "System.Boolean"://布尔型
                            bool boolV = false;
                            bool.TryParse(drValue, out boolV);
                            newCell.SetCellValue(boolV);
                            break;
                        case "System.Int16"://整型
                        case "System.Int32":
                        case "System.Int64":
                        case "System.Byte":
                            int intV = 0;
                            int.TryParse(drValue, out intV);
                            newCell.SetCellValue(intV);
                            break;
                        case "System.Decimal"://浮点型
                        case "System.Double":
                            double doubV = 0;
                            double.TryParse(drValue, out doubV);
                            newCell.SetCellValue(doubV);
                            break;
                        case "System.DBNull"://空值处理
                            newCell.SetCellValue("");
                            break;
                        default:
                            newCell.SetCellValue("");
                            break;
                    }

                }
                #endregion

                rowIndex++;
            }


            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;

                //sheet.Dispose();
                //workbook.Dispose();//一般只用写这一个就OK了,他会遍历并释放所有资源,但当前版本有问题所以只释放sheet
                return ms;
            }
        }
示例#29
0
        private void CargaHojaExcel(DataTable tablaExcel, string titulo, string nombreHoja, bool incluirGrafico)
        {
            #region --[Estilos]--
            IFont fuenteTitulo = excelFile.CreateFont();
            fuenteTitulo.FontName = "Calibri";
            //fuenteTitulo.FontHeight = (short)FontSize.Large.GetHashCode();
            fuenteTitulo.Boldweight = (short)FontBoldWeight.BOLD.GetHashCode();

            IFont unaFuente = excelFile.CreateFont();
            unaFuente.FontName = "Tahoma";
            //unaFuente.FontHeight = (short)FontSize.Medium.GetHashCode();

            IFont fuenteEncabezado = excelFile.CreateFont();
            fuenteEncabezado.FontName = "Tahoma";
            //fuenteEncabezado.FontHeight = (short)FontSize.Medium.GetHashCode(); ;
            fuenteEncabezado.Boldweight = (short)FontBoldWeight.BOLD.GetHashCode();

            ICellStyle unEstiloDecimal = excelFile.CreateCellStyle();
            IDataFormat format = excelFile.CreateDataFormat();
            unEstiloDecimal.DataFormat = format.GetFormat("0.00");
            unEstiloDecimal.SetFont(unaFuente);
            #endregion

            #region --[Hoja]--
            ISheet hojaExcel = excelFile.CreateSheet(nombreHoja);

            NPOI.SS.Util.CellRangeAddress rango = new NPOI.SS.Util.CellRangeAddress(0, 0, 0, tablaExcel.Columns.Count - 1);
            hojaExcel.AddMergedRegion(rango);
            hojaExcel.AutoSizeColumn(0);
            hojaExcel.AutoSizeColumn(1);

            int idxAux = 0;
            IRow fila = hojaExcel.CreateRow(idxAux);
            fila.CreateCell(idxAux).SetCellValue(titulo);
            fila.Cells[idxAux].CellStyle.SetFont(fuenteTitulo);
            idxAux++;

            fila = hojaExcel.CreateRow(idxAux);
            idxAux++;
            RptIndicadores alumno = null;
            int idAlumno = 0;
            string nombre = string.Empty;

            hojaExcel.AutoSizeColumn(0);

            //--Agrego los encabezados--
            #region --[Encabezados]--
            for (int i = 0; i < tablaExcel.Columns.Count; i++)
            {
                fila.CreateCell(i).CellStyle.Alignment = HorizontalAlignment.CENTER;
                nombre = tablaExcel.Columns[i].ColumnName;
                alumno = new RptIndicadores();
                int.TryParse(tablaExcel.Columns[i].ColumnName, out idAlumno);
                if (idAlumno > 0)
                {
                    alumno = lista.Find(p => p.idAlumno == Convert.ToInt16(idAlumno));
                    nombre = alumno.alumnoApellido.Trim() + " " + alumno.alumnoNombre.Trim();
                }
                fila.CreateCell(i).SetCellValue(nombre);
                fila.Cells[i].CellStyle.SetFont(fuenteEncabezado);
                hojaExcel.AutoSizeColumn(i);
            }
            #endregion

            //--Agrego los datos hoja--
            #region --[Datos]--
            decimal valorDato = 0;
            int cantFilas = 0;
            try
            {
                for (int i = 0; i < tablaExcel.Rows.Count; i++)
                {
                    fila = hojaExcel.CreateRow(idxAux);
                    idxAux++;
                    cantFilas++;
                    for (int j = 0; j < tablaExcel.Columns.Count; j++)
                    {
                        try
                        {
                            valorDato = decimal.Parse(tablaExcel.Rows[i][j].ToString());
                            fila.CreateCell(j).SetCellValue(Convert.ToDouble(valorDato));
                            fila.Cells[j].CellStyle = unEstiloDecimal;
                            fila.Cells[j].SetCellType(CellType.NUMERIC);
                        }
                        catch
                        {
                            fila.CreateCell(j).SetCellValue(tablaExcel.Rows[i][j].ToString());
                            fila.Cells[j].CellStyle.SetFont(fuenteEncabezado);
                        }
                    }
                }
            }
            catch (Exception ex)
            { throw ex; }
            #endregion

            //Acomodo las columnas
            for (int j = 0; j < tablaExcel.Columns.Count; j++)
            {
                hojaExcel.AutoSizeColumn(j);
            }
            hojaExcel.AutoSizeColumn(0);
            #endregion

            if (incluirGrafico)
            {
                #region --[Grafico]--
                //map the path to the img folder
                //string imagesPath = System.IO.Path.Combine(Server.MapPath("~"), "img");
                string imagesPath = System.IO.Path.GetFullPath(NombrePNG);
                //grab the image file
                //create an image from the path
                System.Drawing.Image image = System.Drawing.Image.FromFile(imagesPath);
                MemoryStream ms = new MemoryStream();
                //pull the memory stream from the image (I need this for the byte array later)
                image.Save(ms, System.Drawing.Imaging.ImageFormat.Png);
                //the drawing patriarch will hold the anchor and the master information
                var patriarch = hojaExcel.CreateDrawingPatriarch();
                //store the coordinates of which cell and where in the cell the image goes
                HSSFClientAnchor anchor = new HSSFClientAnchor(20, 0, 40, 20, 3, cantFilas + 3, 4, cantFilas + 10);
                //types are 0, 2, and 3. 0 resizes within the cell, 2 doesn't
                anchor.AnchorType = 2;
                //add the byte array and encode it for the excel file
                int index = excelFile.AddPicture(ms.ToArray(), PictureType.PNG);
                var picture = patriarch.CreatePicture(anchor, index);
                picture.Resize();
                picture.LineStyle = HSSFPicture.LINESTYLE_DASHDOTGEL;
                hojaExcel.ForceFormulaRecalculation = true;
                #endregion
            }
        }
示例#30
0
 /// <summary>
 /// Creates a polygon
 /// </summary>
 /// <param name="anchor">the client anchor describes how this Group Is attached
 /// to the sheet.</param>
 /// <returns>the newly Created shape.</returns>
 public HSSFPolygon CreatePolygon(HSSFClientAnchor anchor)
 {
     HSSFPolygon shape = new HSSFPolygon(null, anchor);
     shape.Anchor = anchor;
     shapes.Add(shape);
     return shape;
 }
示例#31
0
 /// <summary>
 /// Constructs a textbox Under the patriarch.
 /// </summary>
 /// <param name="anchor">the client anchor describes how this Group Is attached
 /// to the sheet.</param>
 /// <returns>the newly Created textbox.</returns>
 public HSSFTextbox CreateTextbox(HSSFClientAnchor anchor)
 {
     HSSFTextbox shape = new HSSFTextbox(null, anchor);
     shape.Anchor = anchor;
     shapes.Add(shape);
     return shape;
 }
        /*
        * 添加电子印章 
        *
        */
        private void AddSeal(string sealImg, PictureType format, ClientAnchor anchor)
        {
            //add picture data to this workbook.
            byte[] bytes = System.IO.File.ReadAllBytes(reportTemplateFolder + sealImg);
            int pictureIdx = this.workbook.AddPicture(bytes, format);

            // Create the drawing patriarch.  This is the top level container for all shapes. 
            Drawing patriarch = this.sheet.CreateDrawingPatriarch();

            //add a picture
            if (anchor == null)
            {
                anchor = new HSSFClientAnchor(0, 0, 0, 0, 5, 40, 50, 40);
            }
            Picture pict = patriarch.CreatePicture(anchor, pictureIdx);
            pict.Resize();
        }
示例#33
0
        public void TestBSEPictureRef()
        {
            HSSFWorkbook wb = new HSSFWorkbook();

            HSSFSheet sh = wb.CreateSheet("Pictures") as HSSFSheet;
            HSSFPatriarch dr = sh.CreateDrawingPatriarch() as HSSFPatriarch;
            HSSFClientAnchor anchor = new HSSFClientAnchor();

            InternalSheet ish = HSSFTestHelper.GetSheetForTest(sh);

            //register a picture
            byte[] data1 = new byte[] { 1, 2, 3 };
            int idx1 = wb.AddPicture(data1, PictureType.JPEG);
            Assert.AreEqual(1, idx1);
            HSSFPicture p1 = dr.CreatePicture(anchor, idx1) as HSSFPicture;

            EscherBSERecord bse = wb.Workbook.GetBSERecord(idx1);

            Assert.AreEqual(bse.Ref, 1);
            dr.CreatePicture(new HSSFClientAnchor(), idx1);
            Assert.AreEqual(bse.Ref, 2);

            HSSFShapeGroup gr = dr.CreateGroup(new HSSFClientAnchor());
            gr.CreatePicture(new HSSFChildAnchor(), idx1);
            Assert.AreEqual(bse.Ref, 3);
        }
示例#34
0
        private static void CreateRowItem(this ISheet target, IWorkbook workbook, DataTable dataSource)
        {
            IRow row = null;
            ICell cell = null;
            ICellStyle cellStyle = null;
            IDrawing drawing = null;
            IPicture picture = null;

            cellStyle = workbook.CreateCellStyle();
            cellStyle.Alignment = HorizontalAlignment.CENTER;
            cellStyle.VerticalAlignment = VerticalAlignment.CENTER;

            for (int rowIndex = 0; rowIndex < dataSource.Rows.Count; rowIndex++)
            {
                row = target.CreateRow(rowIndex + 1);

                for (int columnIndex = 0; columnIndex < dataSource.Columns.Count; columnIndex++)
                {
                    cell = row.CreateCell(columnIndex);

                    if (dataSource.Columns[columnIndex].DataType == typeof(byte[]))
                    {
                        byte[] image = dataSource.Rows[rowIndex][columnIndex] as byte[];

                        if (image != null && image.Length > 0)
                        {
                            int pictureIndex = workbook.AddPicture(dataSource.Rows[rowIndex][columnIndex] as byte[], PictureType.JPEG);

                            drawing = target.CreateDrawingPatriarch();
                            HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, columnIndex, rowIndex + 1, columnIndex, rowIndex + 1);
                            picture = drawing.CreatePicture(anchor, pictureIndex);
                            picture.Resize();
                        }
                    }

                    else
                    {
                        cell.SetCellValue(dataSource.Rows[rowIndex][columnIndex].ToString());
                    }

                    cell.CellStyle = cellStyle;
                }
            }
        }
示例#35
0
        /// <summary>
        /// Creates a picture.
        /// </summary>
        /// <param name="anchor">the client anchor describes how this Group is attached
        /// to the sheet.</param>
        /// <param name="pictureIndex">Index of the picture.</param>
        /// <returns>the newly created shape.</returns>
        public IPicture CreatePicture(HSSFClientAnchor anchor, int pictureIndex)
        {
            HSSFPicture shape = new HSSFPicture(null, (HSSFClientAnchor)anchor);
            shape.PictureIndex=pictureIndex;
            shape.Anchor = (HSSFClientAnchor)anchor;
            AddShape(shape);

            EscherBSERecord bse = (_sheet.Workbook as HSSFWorkbook).Workbook.GetBSERecord(pictureIndex);
            bse.Ref = (bse.Ref + 1);
            return shape;
        }
示例#36
0
 /// <summary>
 /// Creates a picture.
 /// </summary>
 /// <param name="anchor">the client anchor describes how this Group is attached
 /// to the sheet.</param>
 /// <param name="pictureIndex">Index of the picture.</param>
 /// <returns>the newly created shape.</returns>
 public HSSFPicture CreatePicture(HSSFClientAnchor anchor, int pictureIndex)
 {
     HSSFPicture shape = new HSSFPicture(null, anchor);
     shape.PictureIndex=pictureIndex;
     shape.Anchor = anchor;
     shape.Patriarch = this;
     shapes.Add(shape);
     return shape;
 }
示例#37
0
        /**
         * Adds a new OLE Package Shape
         *
         * @param anchor       the client anchor describes how this picture is
         *                     attached to the sheet.
         * @param storageId    the storageId returned by {@Link HSSFWorkbook.AddOlePackage}
         * @param pictureIndex the index of the picture (used as preview image) in the
         *                     workbook collection of pictures.
         *
         * @return newly Created shape
         */
        public HSSFObjectData CreateObjectData(HSSFClientAnchor anchor, int storageId, int pictureIndex)
        {
            ObjRecord obj = new ObjRecord();

            CommonObjectDataSubRecord ftCmo = new CommonObjectDataSubRecord();

            ftCmo.ObjectType = (/*setter*/ CommonObjectType.Picture);
            // ftCmo.ObjectId=(/*setter*/oleShape.ShapeId); ... will be Set by onCreate(...)
            ftCmo.IsLocked    = (/*setter*/ true);
            ftCmo.IsPrintable = (/*setter*/ true);
            ftCmo.IsAutoFill  = (/*setter*/ true);
            ftCmo.IsAutoline  = (/*setter*/ true);
            ftCmo.Reserved1   = (/*setter*/ 0);
            ftCmo.Reserved2   = (/*setter*/ 0);
            ftCmo.Reserved3   = (/*setter*/ 0);
            obj.AddSubRecord(ftCmo);

            // FtCf (pictFormat)
            FtCfSubRecord   ftCf     = new FtCfSubRecord();
            HSSFPictureData pictData = Sheet.Workbook.GetAllPictures()[(pictureIndex - 1)] as HSSFPictureData;

            switch ((PictureType)pictData.Format)
            {
            case PictureType.WMF:
            case PictureType.EMF:
                // this needs patch #49658 to be applied to actually work
                ftCf.Flags = (/*setter*/ FtCfSubRecord.METAFILE_BIT);
                break;

            case PictureType.DIB:
            case PictureType.PNG:
            case PictureType.JPEG:
            case PictureType.PICT:
                ftCf.Flags = (/*setter*/ FtCfSubRecord.BITMAP_BIT);
                break;
            }
            obj.AddSubRecord(ftCf);
            // FtPioGrbit (pictFlags)
            FtPioGrbitSubRecord ftPioGrbit = new FtPioGrbitSubRecord();

            ftPioGrbit.SetFlagByBit(FtPioGrbitSubRecord.AUTO_PICT_BIT, true);
            obj.AddSubRecord(ftPioGrbit);

            EmbeddedObjectRefSubRecord ftPictFmla = new EmbeddedObjectRefSubRecord();

            ftPictFmla.SetUnknownFormulaData(new byte[] { 2, 0, 0, 0, 0 });
            ftPictFmla.OLEClassName = (/*setter*/ "Paket");
            ftPictFmla.SetStorageId(storageId);

            obj.AddSubRecord(ftPictFmla);
            obj.AddSubRecord(new EndSubRecord());

            String         entryName = "MBD" + HexDump.ToHex(storageId);
            DirectoryEntry oleRoot;

            try
            {
                DirectoryNode dn = (_sheet.Workbook as HSSFWorkbook).RootDirectory;
                if (dn == null)
                {
                    throw new FileNotFoundException();
                }
                oleRoot = (DirectoryEntry)dn.GetEntry(entryName);
            }
            catch (FileNotFoundException e)
            {
                throw new InvalidOperationException("trying to add ole shape without actually Adding data first - use HSSFWorkbook.AddOlePackage first", e);
            }

            // create picture shape, which need to be minimal modified for oleshapes
            HSSFPicture shape = new HSSFPicture(null, anchor);

            shape.PictureIndex = (/*setter*/ pictureIndex);
            EscherContainerRecord spContainer = shape.GetEscherContainer();
            EscherSpRecord        spRecord    = spContainer.GetChildById(EscherSpRecord.RECORD_ID) as EscherSpRecord;

            spRecord.Flags = (/*setter*/ spRecord.Flags | EscherSpRecord.FLAG_OLESHAPE);

            HSSFObjectData oleShape = new HSSFObjectData(spContainer, obj, oleRoot);

            AddShape(oleShape);
            OnCreate(oleShape);


            return(oleShape);
        }
示例#38
0
        /// <summary>
        /// Creates a picture.
        /// </summary>
        /// <param name="anchor">the client anchor describes how this Group is attached
        /// to the sheet.</param>
        /// <param name="pictureIndex">Index of the picture.</param>
        /// <returns>the newly created shape.</returns>
        public IPicture CreatePicture(HSSFClientAnchor anchor, int pictureIndex)
        {
            HSSFPicture shape = new HSSFPicture(null, (HSSFClientAnchor)anchor);
            shape.PictureIndex = pictureIndex;
            AddShape(shape);

            //open existing file
            OnCreate(shape);
            return shape;
        }
        /// <summary>
        /// 当前列表导出Excel
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void lbt_importall_Click(object sender, EventArgs e)
        {
            string TimeRange="";
            if (ext_StartInputDt.SelectedDate != DateTime.MinValue && ext_EndInputDt.SelectedDate != DateTime.MinValue)
            {
                TimeRange = ext_StartInputDt.SelectedDate.ToShortDateString() + "至" + ext_EndInputDt.SelectedDate.ToShortDateString();
            }
            else if (ext_StartInputDt.SelectedDate != DateTime.MinValue && ext_EndInputDt.SelectedDate == DateTime.MinValue)
            {
                TimeRange = ext_StartInputDt.SelectedDate.ToShortDateString() + "至—";
            }
            else if (ext_StartInputDt.SelectedDate == DateTime.MinValue && ext_EndInputDt.SelectedDate != DateTime.MinValue)
            {
                TimeRange = "—至" + ext_EndInputDt.SelectedDate.ToShortDateString();
            }
            else
            {
                TimeRange = "";
            }
            DataSet ds = logic.complaint.outputExcel(strWhere());
            DataTable dtlist = ds.Tables[0], dtcompany = ds.Tables[1], dtcomplaint = ds.Tables[2], dtcategory = ds.Tables[3], dtdepartment = ds.Tables[4];
            dtlist.Columns["complaintdt"].ColumnName = "日期";
            dtlist.Columns["buyername"].ColumnName = "客户名称";
            dtlist.Columns["productname"].ColumnName = "投诉产品";
            dtlist.Columns["complaintname"].ColumnName = "投诉类别";
            dtlist.Columns["department"].ColumnName = "责任部门";
            dtlist.Columns["responsibler"].ColumnName = "责任人";
            dtlist.Columns["sellername"].ColumnName = "责任供应商";
            dtlist.Columns["levelname"].ColumnName = "严重级别";
            dtlist.Columns["result"].ColumnName = "处理结果";
            dtlist.Columns["inputname"].ColumnName = "录入人";
            dtlist.Columns["remarks"].ColumnName = "投诉问题详情";
            dtlist.Columns.Remove("buyerid");
            dtlist.Columns.Remove("sellerid");
            ExportFacade facade = new ExportFacade();
            HSSFWorkbook workbook = facade.InitializeWorkbook("杭州农副产品物流网络有限公司", logic.sysAdmin.AdminID.ToString(), "采购配送系统", "投诉管理");
            Sheet sheet1 = workbook.CreateSheet("投诉详细");
            facade.CreateRowHeader(workbook, sheet1, TimeRange + " 投诉列表", dtlist);
            facade.FillRowData(workbook, sheet1, 2, dtlist, null, null, null, null);
            Sheet sheet2 = workbook.CreateSheet("客户投诉");
            facade.CreateRowHeader(workbook, sheet2, TimeRange + " 客户投诉情况", dtcompany);
            facade.FillRowData(workbook, sheet2, 2, dtcompany, null, null, null, null);
            Sheet sheet3 = workbook.CreateSheet("投诉汇总");
            facade.CreateRowHeader(workbook, sheet3, TimeRange + " 投诉问题汇总", dtcomplaint);
            facade.FillRowData(workbook, sheet3, 2, dtcomplaint, null, null, null, null);
            #region 小类投诉情况
            GraphPane graphpane = new GraphPane();
            graphpane.Title.Text = "小类投诉情况";
            graphpane.Title.FontSpec.Size = 12f;
            graphpane.XAxis.Title.Text = "小类";
            graphpane.XAxis.Title.FontSpec.Size = 11f;
            graphpane.YAxis.Title.Text = ChangeStr("投诉数量");
            graphpane.YAxis.Title.FontSpec.Angle = 90;
            graphpane.YAxis.Title.FontSpec.Size = 11f;
            graphpane.XAxis.IsVisible = true;
            graphpane.YAxis.IsVisible = true;
            List<string> category=new List<string>();
            List<double> cnum = new List<double>();
            int maxcnum = 2;
            foreach (DataRow dr in dtcategory.Rows)
            {
                if(Convert.ToInt32( dr[1].ToString())>maxcnum)
                    maxcnum=Convert.ToInt32( dr[1].ToString());
                category.Add(ChangeStr( dr[0].ToString()));
                cnum.Add(Convert.ToDouble(dr[1].ToString()));
            }

            BarItem baritem = graphpane.AddBar(null,null,cnum.ToArray(), Color.Red);
            baritem.Bar.Fill = new Fill(Color.Red, Color.White, Color.Red);
            BarItem.CreateBarLabels(graphpane, false, "f0");
            graphpane.XAxis.Scale.TextLabels = category.ToArray();
            graphpane.XAxis.Scale.Max = category.ToArray().Length+1;
            graphpane.XAxis.Scale.MajorStep = 1;
            graphpane.XAxis.MinorTic.Size = 0;
            graphpane.XAxis.MajorTic.Size = 0;
            graphpane.XAxis.Cross = 0;
            graphpane.XAxis.Scale.FontSpec.Size = 10f;
            graphpane.XAxis.Scale.FontSpec.Family = "宋体";
            graphpane.XAxis.Type = AxisType.Text;
            graphpane.XAxis.MajorTic.IsOutside = false;
            graphpane.XAxis.MajorTic.IsOpposite = false;
            graphpane.YAxis.Scale.Max = maxcnum+2;
            graphpane.YAxis.MinorTic.Size = 0;
            graphpane.YAxis.MinorGrid.DashOff = 0;
            graphpane.YAxis.Scale.MajorStep = 1;
            graphpane.YAxis.MajorTic.IsOpposite = false;
            graphpane.YAxis.MajorTic.IsOutside = false;
            graphpane.Chart.Fill = new Fill(Color.White, Color.FromArgb(255, 255, 166), 90F);
            graphpane.Fill = new Fill(Color.White, Color.FromArgb(250, 250, 255),45.0f);
            graphpane.Fill.IsScaled = true;
            MemoryStream ms = new MemoryStream();
            //zgc.GetImage().Save(ms,System.Drawing.Imaging.ImageFormat.Jpeg);
            Bitmap map = graphpane.GetImage(750,550,17);
            map.Save(ms, System.Drawing.Imaging.ImageFormat.Jpeg);
            byte[] picbyte = ms.ToArray();
            int index = workbook.AddPicture(picbyte, NPOI.SS.UserModel.PictureType.JPEG);
            Sheet sheet4 = workbook.CreateSheet("小类投诉");
            facade.CreateRowHeader(workbook, sheet4, TimeRange + " 小类投诉情况", dtcategory);
            facade.FillRowData(workbook, sheet4, 2, dtcategory, null, null, null, null);
            HSSFPatriarch hssfpatriarch = (HSSFPatriarch)sheet4.CreateDrawingPatriarch();
            HSSFClientAnchor hssfanchor = new HSSFClientAnchor(0, 0, 0, 0, 4, 1, 18, 28);
            HSSFPicture hssfpic = (HSSFPicture)hssfpatriarch.CreatePicture(hssfanchor, index);
            #endregion
            #region 部门投诉情况
            GraphPane gp2 = new GraphPane();
            gp2.Title.Text = "部门投诉情况";
            gp2.XAxis.IsVisible = false;
            gp2.YAxis.IsVisible = false;
            gp2.Title.FontSpec.Size = 12f;
            gp2.Fill = new Fill(Color.White);
            gp2.Chart.Fill.Type = FillType.None;
            gp2.Legend.Position = LegendPos.Float;
            gp2.Legend.Location = new Location(0.95f, 0.08f, CoordType.PaneFraction, AlignH.Right, AlignV.Top);
            gp2.Legend.FontSpec.Size = 10f;
            gp2.Legend.IsHStack = false;
            List<double> comnum=new List<double>();
            List<string> dname=new List<string>();
            foreach(DataRow dr in dtdepartment.Rows )
            {
                gp2.AddPieSlice(Convert.ToDouble(dr[1].ToString()), GetRandomColor(), 0, dr[0].ToString()+" ("+dr[1].ToString()+")").LabelType=PieLabelType.Percent;
            }
            Bitmap bitmap = gp2.GetImage(700, 700, 14);
            MemoryStream mstream = new MemoryStream();
            bitmap.Save(mstream, System.Drawing.Imaging.ImageFormat.Jpeg);
            byte[] buffer = mstream.ToArray();
            int picindex = workbook.AddPicture(buffer, NPOI.SS.UserModel.PictureType.JPEG);
            Sheet sheet5 = workbook.CreateSheet("部门投诉");
            facade.CreateRowHeader(workbook, sheet5, TimeRange + " 责任部门投诉情况", dtdepartment);
            facade.FillRowData(workbook, sheet5, 2, dtdepartment, null, null, null, null);
            HSSFPatriarch patriarch = (HSSFPatriarch)sheet5.CreateDrawingPatriarch();
            HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, 4, 1, 15, 34);
            HSSFPicture pic = (HSSFPicture)patriarch.CreatePicture(anchor, picindex);
            #endregion
            facade.ExportByWeb(workbook, TimeRange.ToString() + "采购配送系统投诉统计", TimeRange.ToString() + "采购配送系统投诉统计.xls");
        }
    protected void lbtn_export_ribao_export_Click(object sender, EventArgs e)
    {
        string url = datemin.Value;

        if (url == "")
        {
            SystemTool.AlertShow(this, "开始日期不能为空");
            return;
        }
        url = datemax.Value;
        if (url == "")
        {
            SystemTool.AlertShow(this, "结束日期不能为空");
            return;
        }
        url = img1.ImageUrl;
        if (url == "")
        {
            SystemTool.AlertShow(this, "图片为空,请先刷新");
            return;
        }
        if (ViewState["date"].ToString() != datemin.Value || ViewState["date2"].ToString() != datemax.Value)
        {
            SystemTool.AlertShow(this, "当前图片与日期不一致,请先刷新后再导出此报表");
            return;
        }
        string imgurl = Server.MapPath("~/" + url);
        //Response.Write("<script>window.open('print.aspx?date="+datemin.Value+"&url="+url+"','_blank')</script>");
        string  sql = @"select facename,sensorNo,bracketNo,distance,max(zlmax) zlmax,AVG(zlavg) zlavg,min(zlmin) zlmin,MAX(cclmax) cclmax,AVG(cclavg) cclavg,MAX(mzlmax) mzlmax,AVG(mzlavg) mzlavg from prereport where reportdate>='" + datemin.Value + "' and reportdate<='" + datemax.Value + "' group by bracketNo,sensorNo,distance,facename order by bracketno";
        DataSet ds  = DB.ExecuteSqlDataSet(sql, null);

        if (ds.Tables[0].Rows.Count <= 0)
        {
            SystemTool.AlertShow(this, "报表为空,请在初撑力与末阻力页面查询数据后再导出此报表");
            return;
        }
        //整面最大值、最小值
        string  zhengmianmax = ds.Tables[0].Compute("Max(zlmax)", "true").ToString();
        string  zhengmianmin = ds.Tables[0].Compute("Min(zlmin)", "true").ToString();
        string  zhengmianavg = Convert.ToDecimal(ds.Tables[0].Compute("avg(zlavg)", "true").ToString()).ToString("0.00");
        string  shangbumax   = "0.00";
        string  shangbumin   = "0.00";
        string  shangbuavg   = "0.00";
        string  zhongbumax   = "0.00";
        string  zhongbumin   = "0.00";
        string  zhongbuavg   = "0.00";
        string  xiabumax     = "0.00";
        string  xiabumin     = "0.00";
        string  xiabuavg     = "0.00";
        string  sqlszx       = "select max(zlmax) a,min(zlmin) b,avg(zlavg) c from prereport where reportdate>='" + datemin.Value + "' and reportdate<='" + datemax.Value + "' and distance='上部'";
        DataSet dsszx        = DB.ExecuteSqlDataSet(sqlszx, null);

        if (dsszx.Tables[0].Rows.Count > 0)
        {
            shangbumax = Convert.ToDecimal(dsszx.Tables[0].Rows[0]["a"].ToString() == "" ? "0" : dsszx.Tables[0].Rows[0]["a"].ToString()).ToString("0.00");
            shangbumin = Convert.ToDecimal(dsszx.Tables[0].Rows[0]["b"].ToString() == "" ? "0" : dsszx.Tables[0].Rows[0]["b"].ToString()).ToString("0.00");
            shangbuavg = Convert.ToDecimal(dsszx.Tables[0].Rows[0]["c"].ToString() == "" ? "0" : dsszx.Tables[0].Rows[0]["c"].ToString()).ToString("0.00");
        }
        sqlszx = "select max(zlmax) a,min(zlmin) b,avg(zlavg) c from prereport where reportdate>='" + datemin.Value + "' and reportdate<='" + datemax.Value + "' and distance='中部'";
        dsszx  = DB.ExecuteSqlDataSet(sqlszx, null);
        if (dsszx.Tables[0].Rows.Count > 0)
        {
            zhongbumax = Convert.ToDecimal(dsszx.Tables[0].Rows[0]["a"].ToString() == "" ? "0" : dsszx.Tables[0].Rows[0]["a"].ToString()).ToString("0.00");
            zhongbumin = Convert.ToDecimal(dsszx.Tables[0].Rows[0]["b"].ToString() == "" ? "0" : dsszx.Tables[0].Rows[0]["b"].ToString()).ToString("0.00");
            zhongbuavg = Convert.ToDecimal(dsszx.Tables[0].Rows[0]["c"].ToString() == "" ? "0" : dsszx.Tables[0].Rows[0]["c"].ToString()).ToString("0.00");
        }
        sqlszx = "select max(zlmax) a,min(zlmin) b,avg(zlavg) c from prereport where reportdate>='" + datemin.Value + "' and reportdate<='" + datemax.Value + "' and distance='下部'";
        dsszx  = DB.ExecuteSqlDataSet(sqlszx, null);
        if (dsszx.Tables[0].Rows.Count > 0)
        {
            xiabumax = Convert.ToDecimal(dsszx.Tables[0].Rows[0]["a"].ToString() == "" ? "0" : dsszx.Tables[0].Rows[0]["a"].ToString()).ToString("0.00");
            xiabumin = Convert.ToDecimal(dsszx.Tables[0].Rows[0]["b"].ToString() == "" ? "0" : dsszx.Tables[0].Rows[0]["b"].ToString()).ToString("0.00");
            xiabuavg = Convert.ToDecimal(dsszx.Tables[0].Rows[0]["c"].ToString() == "" ? "0" : dsszx.Tables[0].Rows[0]["c"].ToString()).ToString("0.00");
        }
        NPOI.HSSF.UserModel.HSSFWorkbook book  = new NPOI.HSSF.UserModel.HSSFWorkbook();
        NPOI.SS.UserModel.ISheet         sheet = book.CreateSheet("sheet1");
        NPOI.SS.UserModel.ICellStyle     style = book.CreateCellStyle();
        //设置单元格的样式:水平对齐居中
        style.Alignment         = NPOI.SS.UserModel.HorizontalAlignment.Center;
        style.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
        style.BorderBottom      = NPOI.SS.UserModel.BorderStyle.Thin;
        style.BorderLeft        = NPOI.SS.UserModel.BorderStyle.Thin;
        style.BorderRight       = NPOI.SS.UserModel.BorderStyle.Thin;
        style.BorderTop         = NPOI.SS.UserModel.BorderStyle.Thin;

        NPOI.SS.UserModel.ICellStyle styleleft = book.CreateCellStyle();
        //设置单元格的样式:水平对齐居左
        styleleft.Alignment    = NPOI.SS.UserModel.HorizontalAlignment.Left;
        styleleft.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
        styleleft.BorderLeft   = NPOI.SS.UserModel.BorderStyle.Thin;
        styleleft.BorderRight  = NPOI.SS.UserModel.BorderStyle.Thin;
        styleleft.BorderTop    = NPOI.SS.UserModel.BorderStyle.Thin;
        NPOI.SS.UserModel.ICellStyle styleleftcenter = book.CreateCellStyle();
        //设置单元格的样式:居左居中
        styleleftcenter.Alignment         = NPOI.SS.UserModel.HorizontalAlignment.Left;
        styleleftcenter.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
        styleleftcenter.BorderBottom      = NPOI.SS.UserModel.BorderStyle.Thin;
        styleleftcenter.BorderLeft        = NPOI.SS.UserModel.BorderStyle.Thin;
        styleleftcenter.BorderRight       = NPOI.SS.UserModel.BorderStyle.Thin;
        styleleftcenter.BorderTop         = NPOI.SS.UserModel.BorderStyle.Thin;
        styleleftcenter.WrapText          = true;
        //将新的样式赋给单元格
        //cell.CellStyle = style;
        //设置一个合并单元格区域,使用上下左右定义CellRangeAddress区域
        //CellRangeAddress四个参数为:起始行,结束行,起始列,结束列
        //第一行
        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 9));
        NPOI.SS.UserModel.IRow  row   = sheet.CreateRow(0);
        NPOI.SS.UserModel.ICell cell0 = row.CreateCell(0);
        cell0.SetCellValue("综采支架压力综合日报表【报表日期" + datemin.Value + "-" + datemax.Value + "】");
        cell0.CellStyle             = style;
        row.CreateCell(1).CellStyle = style; row.CreateCell(2).CellStyle = style; row.CreateCell(3).CellStyle = style; row.CreateCell(4).CellStyle = style; row.CreateCell(5).CellStyle = style; row.CreateCell(6).CellStyle = style; row.CreateCell(7).CellStyle = style; row.CreateCell(8).CellStyle = style; row.CreateCell(9).CellStyle = style;
        //第二行
        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 1, 0, 9));
        NPOI.SS.UserModel.IRow  row1  = sheet.CreateRow(1);
        NPOI.SS.UserModel.ICell cell1 = row1.CreateCell(0);
        cell1.SetCellValue("单位:兆帕  工作面名称:" + ds.Tables[0].Rows[0]["facename"].ToString() + "  认证编号:  打印日期:" + DateTime.Now.ToString("yyyy-MM-dd"));
        cell1.CellStyle = style;
        row1.CreateCell(1).CellStyle = style; row1.CreateCell(2).CellStyle = style; row1.CreateCell(3).CellStyle = style; row1.CreateCell(4).CellStyle = style; row1.CreateCell(5).CellStyle = style; row1.CreateCell(6).CellStyle = style; row1.CreateCell(7).CellStyle = style; row1.CreateCell(8).CellStyle = style; row1.CreateCell(9).CellStyle = style;
        // 第三行
        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(2, 2, 0, 9));
        NPOI.SS.UserModel.IRow  row2  = sheet.CreateRow(2);
        NPOI.SS.UserModel.ICell cell2 = row2.CreateCell(0);
        cell2.CellStyle = style;
        row2.CreateCell(1).CellStyle = style; row2.CreateCell(2).CellStyle = style; row2.CreateCell(3).CellStyle = style; row2.CreateCell(4).CellStyle = style; row2.CreateCell(5).CellStyle = style; row2.CreateCell(6).CellStyle = style; row2.CreateCell(7).CellStyle = style; row2.CreateCell(8).CellStyle = style; row2.CreateCell(9).CellStyle = style;
        row2.Height = 4800;
        //将图片文件读入一个字符串
        byte[] bytes      = System.IO.File.ReadAllBytes(imgurl);
        int    pictureIdx = book.AddPicture(bytes, NPOI.SS.UserModel.PictureType.JPEG);

        NPOI.HSSF.UserModel.HSSFPatriarch patriarch = (NPOI.HSSF.UserModel.HSSFPatriarch)sheet.CreateDrawingPatriarch();
        // 插图片的位置  HSSFClientAnchor(dx1,dy1,dx2,dy2,col1,row1,col2,row2) 后面再作解释
        //dx1:图片左边相对excel格的位置(x偏移) 范围值为:0~1023;即输100 偏移的位置大概是相对于整个单元格的宽度的100除以1023大概是10分之一
        //dy1:图片上方相对excel格的位置(y偏移) 范围值为:0~256 原理同上。
        //dx2:图片右边相对excel格的位置(x偏移) 范围值为:0~1023; 原理同上。
        //dy2:图片下方相对excel格的位置(y偏移) 范围值为:0~256 原理同上。
        //col1和row1 :图片左上角的位置,以excel单元格为参考,比喻这两个值为(1,1),那么图片左上角的位置就是excel表(1,1)单元格的右下角的点(A,1)右下角的点。
        //col2和row2:图片右下角的位置,以excel单元格为参考,比喻这两个值为(2,2),那么图片右下角的位置就是excel表(2,2)单元格的右下角的点(B,2)右下角的点。
        NPOI.HSSF.UserModel.HSSFClientAnchor anchor = new NPOI.HSSF.UserModel.HSSFClientAnchor(10, 10, 10, 10, 0, 2, 9, 3);
        //把图片插到相应的位置
        NPOI.HSSF.UserModel.HSSFPicture pict = (NPOI.HSSF.UserModel.HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
        //第三行
        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(3, 3, 0, 9));
        NPOI.SS.UserModel.IRow  row3  = sheet.CreateRow(3);
        NPOI.SS.UserModel.ICell cell3 = row3.CreateCell(0);
        cell3.SetCellValue("监测数据统计表:");
        cell3.CellStyle = styleleft;
        row3.CreateCell(1).CellStyle = styleleft; row3.CreateCell(2).CellStyle = styleleft; row3.CreateCell(3).CellStyle = styleleft; row3.CreateCell(4).CellStyle = styleleft; row3.CreateCell(5).CellStyle = styleleft; row3.CreateCell(6).CellStyle = styleleft; row3.CreateCell(7).CellStyle = styleleft; row3.CreateCell(8).CellStyle = styleleft; row3.CreateCell(9).CellStyle = styleleft;
        //第四行
        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(4, 8, 0, 1));
        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(4, 4, 2, 3));
        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(4, 4, 4, 5));
        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(4, 4, 6, 7));
        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(4, 4, 8, 9));

        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(6, 6, 2, 3));
        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(6, 6, 4, 5));
        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(6, 6, 6, 7));
        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(6, 6, 8, 9));

        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(8, 8, 2, 3));
        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(8, 8, 4, 5));
        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(8, 8, 6, 7));
        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(8, 8, 8, 9));
        NPOI.SS.UserModel.IRow  row4   = sheet.CreateRow(4);
        NPOI.SS.UserModel.ICell cell40 = row4.CreateCell(0);
        cell40.SetCellValue("工作面工作阻力统计:");
        cell40.CellStyle = style;

        NPOI.SS.UserModel.ICell cell41 = row4.CreateCell(1); cell41.CellStyle = style;
        NPOI.SS.UserModel.ICell cell42 = row4.CreateCell(2); cell42.SetCellValue("整面"); cell42.CellStyle = style;
        NPOI.SS.UserModel.ICell cell43 = row4.CreateCell(3); cell43.CellStyle = style;
        NPOI.SS.UserModel.ICell cell44 = row4.CreateCell(4); cell44.SetCellValue("上部"); cell44.CellStyle = style;
        NPOI.SS.UserModel.ICell cell45 = row4.CreateCell(5); cell45.CellStyle = style;
        NPOI.SS.UserModel.ICell cell46 = row4.CreateCell(6); cell46.SetCellValue("中部"); cell46.CellStyle = style;
        NPOI.SS.UserModel.ICell cell47 = row4.CreateCell(7); cell47.CellStyle = style;
        NPOI.SS.UserModel.ICell cell48 = row4.CreateCell(8); cell48.SetCellValue("下部"); cell48.CellStyle = style;
        NPOI.SS.UserModel.ICell cell49 = row4.CreateCell(9); cell49.CellStyle = style;

        NPOI.SS.UserModel.IRow  row5   = sheet.CreateRow(5);
        NPOI.SS.UserModel.ICell cell50 = row5.CreateCell(0); cell50.CellStyle = style;
        NPOI.SS.UserModel.ICell cell51 = row5.CreateCell(1); cell51.CellStyle = style;
        NPOI.SS.UserModel.ICell cell52 = row5.CreateCell(2); cell52.SetCellValue("最大"); cell52.CellStyle = style;
        NPOI.SS.UserModel.ICell cell53 = row5.CreateCell(3); cell53.SetCellValue("最小"); cell53.CellStyle = style;
        NPOI.SS.UserModel.ICell cell54 = row5.CreateCell(4); cell54.SetCellValue("最大"); cell54.CellStyle = style;
        NPOI.SS.UserModel.ICell cell55 = row5.CreateCell(5); cell55.SetCellValue("最小"); cell55.CellStyle = style;
        NPOI.SS.UserModel.ICell cell56 = row5.CreateCell(6); cell56.SetCellValue("最大"); cell56.CellStyle = style;
        NPOI.SS.UserModel.ICell cell57 = row5.CreateCell(7); cell57.SetCellValue("最小"); cell57.CellStyle = style;
        NPOI.SS.UserModel.ICell cell58 = row5.CreateCell(8); cell58.SetCellValue("最大"); cell58.CellStyle = style;
        NPOI.SS.UserModel.ICell cell59 = row5.CreateCell(9); cell59.SetCellValue("最小"); cell59.CellStyle = style;
        NPOI.SS.UserModel.IRow  row6   = sheet.CreateRow(6);
        NPOI.SS.UserModel.ICell cell61 = row6.CreateCell(1); cell61.CellStyle = style;
        NPOI.SS.UserModel.ICell cell62 = row6.CreateCell(2); cell62.SetCellValue("平均"); cell62.CellStyle = style;
        NPOI.SS.UserModel.ICell cell63 = row6.CreateCell(3); cell63.CellStyle = style;
        NPOI.SS.UserModel.ICell cell64 = row6.CreateCell(4); cell64.SetCellValue("平均"); cell64.CellStyle = style;
        NPOI.SS.UserModel.ICell cell65 = row6.CreateCell(5); cell65.CellStyle = style;
        NPOI.SS.UserModel.ICell cell66 = row6.CreateCell(6); cell66.SetCellValue("平均"); cell66.CellStyle = style;
        NPOI.SS.UserModel.ICell cell67 = row6.CreateCell(7); cell67.CellStyle = style;
        NPOI.SS.UserModel.ICell cell68 = row6.CreateCell(8); cell68.SetCellValue("平均"); cell68.CellStyle = style;
        NPOI.SS.UserModel.ICell cell69 = row6.CreateCell(9); cell69.CellStyle = style;
        NPOI.SS.UserModel.IRow  row7   = sheet.CreateRow(7);
        NPOI.SS.UserModel.ICell cell70 = row7.CreateCell(0); cell70.CellStyle = style;
        NPOI.SS.UserModel.ICell cell71 = row7.CreateCell(1); cell71.CellStyle = style;
        NPOI.SS.UserModel.ICell cell72 = row7.CreateCell(2); cell72.SetCellValue(zhengmianmax); cell72.CellStyle = style;
        NPOI.SS.UserModel.ICell cell73 = row7.CreateCell(3); cell73.SetCellValue(zhengmianmin); cell73.CellStyle = style;
        NPOI.SS.UserModel.ICell cell74 = row7.CreateCell(4); cell74.SetCellValue(shangbumax); cell74.CellStyle = style;
        NPOI.SS.UserModel.ICell cell75 = row7.CreateCell(5); cell75.SetCellValue(shangbumin); cell75.CellStyle = style;
        NPOI.SS.UserModel.ICell cell76 = row7.CreateCell(6); cell76.SetCellValue(zhongbumax); cell76.CellStyle = style;
        NPOI.SS.UserModel.ICell cell77 = row7.CreateCell(7); cell77.SetCellValue(zhongbumin); cell77.CellStyle = style;
        NPOI.SS.UserModel.ICell cell78 = row7.CreateCell(8); cell78.SetCellValue(xiabumax); cell78.CellStyle = style;
        NPOI.SS.UserModel.ICell cell79 = row7.CreateCell(9); cell79.SetCellValue(xiabumin); cell79.CellStyle = style;
        NPOI.SS.UserModel.IRow  row8   = sheet.CreateRow(8);
        NPOI.SS.UserModel.ICell cell80 = row8.CreateCell(0); cell80.CellStyle = style;
        NPOI.SS.UserModel.ICell cell81 = row8.CreateCell(1); cell81.CellStyle = style;
        NPOI.SS.UserModel.ICell cell82 = row8.CreateCell(2); cell82.SetCellValue(zhengmianavg); cell82.CellStyle = style;
        NPOI.SS.UserModel.ICell cell83 = row8.CreateCell(3); cell83.CellStyle = style;
        NPOI.SS.UserModel.ICell cell84 = row8.CreateCell(4); cell84.SetCellValue(shangbuavg); cell84.CellStyle = style;
        NPOI.SS.UserModel.ICell cell85 = row8.CreateCell(5); cell85.CellStyle = style;
        NPOI.SS.UserModel.ICell cell86 = row8.CreateCell(6); cell86.SetCellValue(zhongbuavg); cell86.CellStyle = style;
        NPOI.SS.UserModel.ICell cell87 = row8.CreateCell(7); cell87.CellStyle = style;
        NPOI.SS.UserModel.ICell cell88 = row8.CreateCell(8); cell88.SetCellValue(xiabuavg); cell88.CellStyle = style;
        NPOI.SS.UserModel.ICell cell89 = row8.CreateCell(9); cell89.CellStyle = style;
        //第5行
        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(9, 10, 0, 0));
        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(9, 10, 1, 1));
        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(9, 10, 2, 3));
        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(9, 9, 4, 5));
        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(9, 9, 6, 7));
        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(9, 9, 8, 9));
        NPOI.SS.UserModel.IRow  row9    = sheet.CreateRow(9);
        NPOI.SS.UserModel.ICell cell90  = row9.CreateCell(0); cell90.SetCellValue("分机编号"); cell90.CellStyle = style;
        NPOI.SS.UserModel.ICell cell91  = row9.CreateCell(1); cell91.SetCellValue("支架编号"); cell91.CellStyle = style;
        NPOI.SS.UserModel.ICell cell92  = row9.CreateCell(2); cell92.SetCellValue("安装位置"); cell92.CellStyle = style;
        NPOI.SS.UserModel.ICell cell93  = row9.CreateCell(3); cell93.CellStyle = style;
        NPOI.SS.UserModel.ICell cell94  = row9.CreateCell(4); cell94.SetCellValue("工作阻力"); cell94.CellStyle = style;
        NPOI.SS.UserModel.ICell cell95  = row9.CreateCell(5); cell95.CellStyle = style;
        NPOI.SS.UserModel.ICell cell96  = row9.CreateCell(6); cell96.SetCellValue("初撑力"); cell96.CellStyle = style;
        NPOI.SS.UserModel.ICell cell97  = row9.CreateCell(7); cell97.CellStyle = style;
        NPOI.SS.UserModel.ICell cell98  = row9.CreateCell(8); cell98.SetCellValue("末阻力"); cell98.CellStyle = style;
        NPOI.SS.UserModel.ICell cell99  = row9.CreateCell(9); cell99.CellStyle = style;
        NPOI.SS.UserModel.IRow  row10   = sheet.CreateRow(10);
        NPOI.SS.UserModel.ICell cell100 = row10.CreateCell(0); cell100.CellStyle = style;
        NPOI.SS.UserModel.ICell cell101 = row10.CreateCell(1); cell101.CellStyle = style;
        NPOI.SS.UserModel.ICell cell102 = row10.CreateCell(2); cell102.CellStyle = style;
        NPOI.SS.UserModel.ICell cell103 = row10.CreateCell(3); cell103.CellStyle = style;
        NPOI.SS.UserModel.ICell cell104 = row10.CreateCell(4); cell104.SetCellValue("最大"); cell104.CellStyle = style;
        NPOI.SS.UserModel.ICell cell105 = row10.CreateCell(5); cell105.SetCellValue("平均"); cell105.CellStyle = style;
        NPOI.SS.UserModel.ICell cell106 = row10.CreateCell(6); cell106.SetCellValue("最大"); cell106.CellStyle = style;
        NPOI.SS.UserModel.ICell cell107 = row10.CreateCell(7); cell107.SetCellValue("平均"); cell107.CellStyle = style;
        NPOI.SS.UserModel.ICell cell108 = row10.CreateCell(8); cell108.SetCellValue("最大"); cell108.CellStyle = style;
        NPOI.SS.UserModel.ICell cell109 = row10.CreateCell(9); cell109.SetCellValue("平均"); cell109.CellStyle = style;
        //
        string jiancefenxi = "支架编号:";

        for (int i = 11; i < ds.Tables[0].Rows.Count + 11; i++)
        {
            sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(i, i, 2, 3));
            NPOI.SS.UserModel.IRow  row11   = sheet.CreateRow(i);
            NPOI.SS.UserModel.ICell cell110 = row11.CreateCell(0); cell110.SetCellValue(ds.Tables[0].Rows[i - 11]["sensorNo"].ToString()); cell110.CellStyle = style;
            NPOI.SS.UserModel.ICell cell111 = row11.CreateCell(1); cell111.SetCellValue(ds.Tables[0].Rows[i - 11]["bracketno"].ToString()); cell111.CellStyle = style;
            NPOI.SS.UserModel.ICell cell112 = row11.CreateCell(2); cell112.SetCellValue(ds.Tables[0].Rows[i - 11]["distance"].ToString()); cell112.CellStyle = style;
            NPOI.SS.UserModel.ICell cell113 = row11.CreateCell(3); cell113.CellStyle = style;
            NPOI.SS.UserModel.ICell cell114 = row11.CreateCell(4); cell114.SetCellValue(ds.Tables[0].Rows[i - 11]["zlmax"].ToString()); cell114.CellStyle = style;
            NPOI.SS.UserModel.ICell cell115 = row11.CreateCell(5); cell115.SetCellValue(Convert.ToDecimal(ds.Tables[0].Rows[i - 11]["zlavg"].ToString() == ""?"0.00":ds.Tables[0].Rows[i - 11]["zlavg"].ToString()).ToString("0.00")); cell115.CellStyle = style;
            NPOI.SS.UserModel.ICell cell116 = row11.CreateCell(6); cell116.SetCellValue(ds.Tables[0].Rows[i - 11]["cclmax"].ToString()); cell116.CellStyle = style;
            NPOI.SS.UserModel.ICell cell117 = row11.CreateCell(7); cell117.SetCellValue(Convert.ToDecimal(ds.Tables[0].Rows[i - 11]["zlavg"].ToString() == ""?"0.00":ds.Tables[0].Rows[i - 11]["cclavg"].ToString()).ToString("0.00")); cell117.CellStyle = style;
            NPOI.SS.UserModel.ICell cell118 = row11.CreateCell(8); cell118.SetCellValue(ds.Tables[0].Rows[i - 11]["mzlmax"].ToString()); cell118.CellStyle = style;
            NPOI.SS.UserModel.ICell cell119 = row11.CreateCell(9); cell119.SetCellValue(Convert.ToDecimal(ds.Tables[0].Rows[i - 11]["zlavg"].ToString() == ""?"0.00":ds.Tables[0].Rows[i - 11]["mzlavg"].ToString()).ToString("0.00")); cell119.CellStyle = style;
            if (ViewState["yujingzhi"].ToString() != "0")
            {
                decimal fenxi     = Convert.ToDecimal(ds.Tables[0].Rows[i - 11]["zlavg"].ToString() == "" ? "0.00" : ds.Tables[0].Rows[i - 11]["zlavg"].ToString());
                decimal yujingzhi = Convert.ToDecimal(ViewState["yujingzhi"].ToString());
                if (fenxi >= yujingzhi)
                {
                    jiancefenxi += "" + ds.Tables[0].Rows[i - 11]["bracketno"].ToString() + "超压(" + fenxi.ToString("0.00") + "),";
                }
            }
        }
        jiancefenxi += "请注意观察";
        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(ds.Tables[0].Rows.Count + 11, ds.Tables[0].Rows.Count + 11, 0, 9));
        NPOI.SS.UserModel.IRow  rowa   = sheet.CreateRow(ds.Tables[0].Rows.Count + 11);
        NPOI.SS.UserModel.ICell cella0 = rowa.CreateCell(0); cella0.SetCellValue("监测分析结论:"); cella0.CellStyle = styleleft;
        NPOI.SS.UserModel.ICell cella1 = rowa.CreateCell(1); cella1.CellStyle = style;
        NPOI.SS.UserModel.ICell cella2 = rowa.CreateCell(2); cella2.CellStyle = style;
        NPOI.SS.UserModel.ICell cella3 = rowa.CreateCell(3); cella3.CellStyle = style;
        NPOI.SS.UserModel.ICell cella4 = rowa.CreateCell(4); cella4.CellStyle = style;
        NPOI.SS.UserModel.ICell cella5 = rowa.CreateCell(5); cella5.CellStyle = style;
        NPOI.SS.UserModel.ICell cella6 = rowa.CreateCell(6); cella6.CellStyle = style;
        NPOI.SS.UserModel.ICell cella7 = rowa.CreateCell(7); cella7.CellStyle = style;
        NPOI.SS.UserModel.ICell cella8 = rowa.CreateCell(8); cella8.CellStyle = style;
        NPOI.SS.UserModel.ICell cella9 = rowa.CreateCell(9); cella9.CellStyle = style;
        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(ds.Tables[0].Rows.Count + 12, ds.Tables[0].Rows.Count + 12, 0, 9));
        NPOI.SS.UserModel.IRow rowb = sheet.CreateRow(ds.Tables[0].Rows.Count + 12);
        rowb.Height = 2400;
        NPOI.SS.UserModel.ICell cellb0 = rowb.CreateCell(0); cellb0.SetCellValue("" + jiancefenxi); cellb0.CellStyle = styleleftcenter;
        NPOI.SS.UserModel.ICell cellb1 = rowb.CreateCell(1); cellb1.CellStyle = style;
        NPOI.SS.UserModel.ICell cellb2 = rowb.CreateCell(2); cellb2.CellStyle = style;
        NPOI.SS.UserModel.ICell cellb3 = rowb.CreateCell(3); cellb3.CellStyle = style;
        NPOI.SS.UserModel.ICell cellb4 = rowb.CreateCell(4); cellb4.CellStyle = style;
        NPOI.SS.UserModel.ICell cellb5 = rowb.CreateCell(5); cellb5.CellStyle = style;
        NPOI.SS.UserModel.ICell cellb6 = rowb.CreateCell(6); cellb6.CellStyle = style;
        NPOI.SS.UserModel.ICell cellb7 = rowb.CreateCell(7); cellb7.CellStyle = style;
        NPOI.SS.UserModel.ICell cellb8 = rowb.CreateCell(8); cellb8.CellStyle = style;
        NPOI.SS.UserModel.ICell cellb9 = rowb.CreateCell(9); cellb9.CellStyle = style;
        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(ds.Tables[0].Rows.Count + 13, ds.Tables[0].Rows.Count + 13, 0, 9));
        NPOI.SS.UserModel.IRow  rowc   = sheet.CreateRow(ds.Tables[0].Rows.Count + 13);
        NPOI.SS.UserModel.ICell cellc0 = rowc.CreateCell(0); cellc0.SetCellValue("区队意见:"); cellc0.CellStyle = styleleft;
        NPOI.SS.UserModel.ICell cellc1 = rowc.CreateCell(1); cellc1.CellStyle = style;
        NPOI.SS.UserModel.ICell cellc2 = rowc.CreateCell(2); cellc2.CellStyle = style;
        NPOI.SS.UserModel.ICell cellc3 = rowc.CreateCell(3); cellc3.CellStyle = style;
        NPOI.SS.UserModel.ICell cellc4 = rowc.CreateCell(4); cellc4.CellStyle = style;
        NPOI.SS.UserModel.ICell cellc5 = rowc.CreateCell(5); cellc5.CellStyle = style;
        NPOI.SS.UserModel.ICell cellc6 = rowc.CreateCell(6); cellc6.CellStyle = style;
        NPOI.SS.UserModel.ICell cellc7 = rowc.CreateCell(7); cellc7.CellStyle = style;
        NPOI.SS.UserModel.ICell cellc8 = rowc.CreateCell(8); cellc8.CellStyle = style;
        NPOI.SS.UserModel.ICell cellc9 = rowc.CreateCell(9); cellc9.CellStyle = style;
        sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(ds.Tables[0].Rows.Count + 14, ds.Tables[0].Rows.Count + 14, 0, 9));
        NPOI.SS.UserModel.IRow rowd = sheet.CreateRow(ds.Tables[0].Rows.Count + 14);
        rowd.Height = 2400;
        NPOI.SS.UserModel.ICell celld0 = rowd.CreateCell(0); celld0.SetCellValue("               领导签字:____________部门签字:____________报表人:____________"); celld0.CellStyle = styleleft;
        NPOI.SS.UserModel.ICell celld1 = rowd.CreateCell(1); celld1.CellStyle = style;
        NPOI.SS.UserModel.ICell celld2 = rowd.CreateCell(2); celld2.CellStyle = style;
        NPOI.SS.UserModel.ICell celld3 = rowd.CreateCell(3); celld3.CellStyle = style;
        NPOI.SS.UserModel.ICell celld4 = rowd.CreateCell(4); celld4.CellStyle = style;
        NPOI.SS.UserModel.ICell celld5 = rowd.CreateCell(5); celld5.CellStyle = style;
        NPOI.SS.UserModel.ICell celld6 = rowd.CreateCell(6); celld6.CellStyle = style;
        NPOI.SS.UserModel.ICell celld7 = rowd.CreateCell(7); celld7.CellStyle = style;
        NPOI.SS.UserModel.ICell celld8 = rowd.CreateCell(8); celld8.CellStyle = style;
        NPOI.SS.UserModel.ICell celld9 = rowd.CreateCell(9); celld9.CellStyle = style;
        // 写入到客户端
        System.IO.MemoryStream ms = new System.IO.MemoryStream();
        book.Write(ms);
        Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", DateTime.Now.ToString("yyyyMMddHHmmssfff")));
        Response.BinaryWrite(ms.ToArray());
        book = null;
        ms.Close();
        ms.Dispose();
    }
        /// <summary>
        /// Creates an excel comment in each cell with an associated error
        /// </summary>
        /// <param name="excelSheet"></param>
        /// <param name="sheet"></param>
        private void HighlightErrors(ISheet excelSheet, ICOBieSheet<COBieRow> sheet)
        {
            //sort by row then column
            var errors = sheet.Errors.OrderBy(err => err.Row).ThenBy(err => err.Column);

            // The patriarch is a container for comments on a sheet
            IDrawing patr = excelSheet.CreateDrawingPatriarch();
            int sheetCommnetCount = 0;
            foreach (var error in errors)
            {
                if (error.Row > 0 && error.Column >= 0)
                {
                    if ((error.Row + 3) > 65280)//UInt16.MaxValue some reason the CreateCellComment has 65280 as the max row number
                    {
                        // TODO: Warn overflow of XLS 2003 worksheet
                        break;
                    }
                    //limit comments to 1000 per sheet
                    if (sheetCommnetCount == 999)
                        break;

                    IRow excelRow = excelSheet.GetRow(error.Row);
                    if (excelRow != null)
                    {
                        ICell excelCell = excelRow.GetCell(error.Column);
                        if (excelCell != null)
                        {
                            string description = error.ErrorDescription;
                            if(hasErrorLevel)
                            {
                                if (error.ErrorLevel == COBieError.ErrorLevels.Warning)
                                    description = "Warning: " + description;
                                else
                                    description = "Error: " + description;
                            }

                            if (excelCell.CellComment == null)
                            {
                                try
                                {
                                    // A client anchor is attached to an excel worksheet. It anchors against a top-left and bottom-right cell.
                                    // Create a comment 3 columns wide and 3 rows height
                                    IClientAnchor anchor = null;
                                    if (IsXlsx)
                                    {
                                        anchor =  new XSSFClientAnchor(0, 0, 0, 0, error.Column, error.Row, error.Column + 3, error.Row + 3);
                                    }
                                    else
                                    {
                                        anchor =  new HSSFClientAnchor(0, 0, 0, 0, error.Column, error.Row, error.Column + 3, error.Row + 3);
                                    }

                                    IComment comment = patr.CreateCellComment(anchor);

                                    IRichTextString str = null;
                                    if (IsXlsx)
                                    {
                                        str = new XSSFRichTextString(description); 
                                    }
                                    else
                                    {
                                        str = new HSSFRichTextString(description); 
                                    }

                                    comment.String = str;
                                    comment.Author = "XBim";
                                    excelCell.CellComment = comment;
                                    _commentCount++;
                                    sheetCommnetCount++;
                                }
                                catch (Exception ex)
                                {
                                    Console.WriteLine(ex.Message);
                                }
                            }
                            else
                            {
                                if (IsXlsx)
                                {
                                    ((XSSFRichTextString)excelCell.CellComment.String).Append(" Also " + description);
                                }
                                else
                                {
                                    description = excelCell.CellComment.String.ToString() + " Also " + description;
                                    excelCell.CellComment.String = new HSSFRichTextString(description);
                                }
                                
                            }
                            
                            
                        }
                        
                    }
                }
            }
        }
示例#42
0
        public void TestSetGetProperties()
        {
            HSSFWorkbook wb = new HSSFWorkbook();

            HSSFSheet sh = wb.CreateSheet("Pictures") as HSSFSheet;
            HSSFPatriarch dr = sh.CreateDrawingPatriarch() as HSSFPatriarch;
            HSSFClientAnchor anchor = new HSSFClientAnchor();

            //register a picture
            byte[] data1 = new byte[] { 1, 2, 3 };
            int idx1 = wb.AddPicture(data1, PictureType.JPEG);
            HSSFPicture p1 = dr.CreatePicture(anchor, idx1) as HSSFPicture;

            Assert.AreEqual(p1.FileName, "");
            p1.FileName = ("aaa");
            Assert.AreEqual(p1.FileName, "aaa");

            wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
            sh = wb.GetSheet("Pictures") as HSSFSheet;
            dr = sh.DrawingPatriarch as HSSFPatriarch;

            p1 = (HSSFPicture)dr.Children[0];
            Assert.AreEqual(p1.FileName, "aaa");
        }
        /*
         * 添加电子印章 
         * 
         */

        protected void AddSeal(User user, int row, int col)
        {
            ClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, col, row, col, row);

            string sealImg = this.GetSealImg(user);
            this.AddSeal(sealImg, anchor);
        }
 protected void AddSeal(string sealImg, int row, int col)
 {
     ClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, col, row, col, row);
     this.AddSeal(sealImg, anchor);
 }