Exemplo n.º 1
0
        /// <summary>
        /// 向Excel中插入图片
        /// </summary>
        /// <param name="pictureName">图片的绝对路径加文件名</param>
        public void InsertPictures(string pictureName)
        {
            try
            {
                if (_myExcel is HSSFWorkbook)
                {
                    HSSFPatriarch patriarch = (HSSFPatriarch)_activeSheet.CreateDrawingPatriarch();
                    //create the anchor
                    HSSFClientAnchor anchor;
                    anchor            = new HSSFClientAnchor(10, 10, 0, 0, 1, 1, 11, 26);
                    anchor.AnchorType = 2;

                    HSSFPicture picture = (HSSFPicture)patriarch.CreatePicture(anchor, LoadImage(pictureName, _myExcel));
                    picture.LineStyle = LineStyle.DashDotGel;
                }
                else if (_myExcel is XSSFWorkbook)
                {
                    XSSFDrawing drawing = (XSSFDrawing)_activeSheet.CreateDrawingPatriarch();
                    //create the anchor
                    XSSFClientAnchor anchor;
                    anchor            = new XSSFClientAnchor(10, 10, 0, 0, 1, 1, 11, 26);
                    anchor.AnchorType = 2;

                    XSSFPicture picture = (XSSFPicture)drawing.CreatePicture(anchor, LoadImage(pictureName, _myExcel));
                    picture.LineStyle = LineStyle.DashDotGel;
                }
            }
            catch (Exception exception)
            {
                //Log.Write.Error("InsertPictures to file fail", exception);
            }
        }
Exemplo n.º 2
0
        /*泛类型行不通
         * public ISheet pictureDataToSheet<T>(ISheet sheet, T pictureNPOI,int startRow,int startCol, int endRow,int endCol)
         * //   where T: XSSFPicture, HSSFPicture,类型应该只有一种的原因吧,无法执行类型约束为两个类,因为类的约束必须放在第一个
         * {
         *
         *  XSSFPicture pictureNPOI_XSSFPicture = pictureNPOI as XSSFPicture;
         *  HSSFPalette pictureNPOI_HSSFPalette = pictureNPOI as HSSFPalette;
         *  //XSSFPicture,HSSFPalette是类,只能有一种类型,正好是泛类型要解决的
         *  //方法和使用一样,但是T的类型取决类申城的Isheet的类型
         *  //应该使用重载
         *  if (true)
         *  {
         *
         *  }
         *  else
         *  {
         *      return null;
         *  }
         *  workbook.AddPicture(pictureNPOI.)
         * }
         */

        /// <summary>
        /// Excel sheet中插入图片
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="pictureNPOI"></param>
        /// <param name="dx1"></param>
        /// <param name="dy1"></param>
        /// <param name="dx2"></param>
        /// <param name="dy2"></param>
        /// <param name="startRow"></param>
        /// <param name="startCol"></param>
        /// <param name="endRow"></param>
        /// <param name="endCol"></param>
        /// <returns></returns>
        //重载
        public void pictureDataToSheet(ISheet sheet, byte[] pictureNPOI, int dx1, int dy1, int dx2, int dy2, int startRow, int startCol, int endRow, int endCol)
        {
            /*将实际图片转换为pictureData时使用,但是pictureNPOI本身就是picture
             * byte[] pictureByte=
             * workbook.AddPicture(, PictureType.PNG);
             */
            //判断是否有sheet
            //无,则创建
            if (sheet == null)
            {
                sheet = this.workbook.CreateSheet();
            }

            //执行向sheet写图片
            //创建DrawingPatriarch,存放的容器
            IDrawing patriarch = sheet.CreateDrawingPatriarch();
            ///System.InvalidCastException:“无法将类型为“NPOI.XSSF.UserModel.XSSFDrawing”的对象强制转换为类型“NPOI.HSSF.UserModel.HSSFPatriarch”。”
            ///            HSSFPatriarch patriarch = (HSSFPatriarch)sheetA.CreateDrawingPatriarch();
            ///    根据报错改为如下
            // IDrawing patriarch = sheet.CreateDrawingPatriarch();

            //XSSFClientAnchor anchor = new XSSFClientAnchor(dx1, dy1, dx2, dy2, startCol, startRow, endCol, endRow);
            IClientAnchor anchor = patriarch.CreateAnchor(dx1, dy1, dx2, dy2, startCol, startRow, endCol, endRow);

            //将图片文件读入workbook,用索引指向该文件
            int pictureIdx = workbook.AddPicture(pictureNPOI, PictureType.PNG);

            //根据读入图片和anchor把图片插到相应的位置
            IPicture pict = patriarch.CreatePicture(anchor, pictureIdx);
            //原始大小显示,重载可指定缩放
            //pict.Resize(0.9);
            //return sheet;
        }
Exemplo n.º 3
0
        public void TestCreateDrawings()
        {
            IWorkbook     workbook = new HSSFWorkbook();
            ISheet        sheet    = workbook.CreateSheet();
            HSSFPatriarch p1       = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
            HSSFPatriarch p2       = (HSSFPatriarch)sheet.CreateDrawingPatriarch();

            Assert.AreSame(p1, p2);
        }
Exemplo n.º 4
0
        /// <summary>
        /// XLS类型Excle文件添加图片
        /// </summary>
        /// <param name="intRowIndex">插入图片的行数</param>
        /// <param name="pictureIndex">图片的顺序</param>
        private void HSSFCreatePicture(int intRowIndex, int pictureIndex)
        {
            HSSFPatriarch    patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
            HSSFClientAnchor anchor    = new HSSFClientAnchor();

            anchor.Row1 = intRowIndex;
            //anchor.AnchorType = 2;
            HSSFPicture picture = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIndex);

            picture.LineStyle = LineStyle.Solid;
            picture.Resize();//显示图片的原始尺寸
        }
Exemplo n.º 5
0
 /// <summary>
 /// create xls
 /// using NPOI
 /// </summary>
 /// <param name="svgDocs"></param>
 /// <param name="stream"></param>
 public static void CreateExcelStreamBySvgs(List <SvgDocument> svgDocs, Stream stream)
 {
     using (stream)
     {
         IWorkbook        workbook  = new HSSFWorkbook();
         ISheet           sheet     = workbook.CreateSheet("Sheet1");
         IDrawing         patriarch = sheet.CreateDrawingPatriarch();
         HSSFClientAnchor anchor;
         IPicture         pic;
         IRow             row = null;
         for (int i = 0; i < svgDocs.Count; i++)
         {
             using (MemoryStream ms = new MemoryStream())
             {
                 using (System.Drawing.Bitmap image = svgDocs[i].Draw())
                 {
                     image.Save(ms, ImageFormat.Bmp);
                     ms.Seek(0, SeekOrigin.Begin);
                     int index = workbook.AddPicture(ms.ToArray(), PictureType.JPEG);
                     row = sheet.CreateRow(i);
                     row.HeightInPoints = convertPixelToPoints(image.Height) + 10;
                     anchor             = new HSSFClientAnchor(0, 0, 0, 0, 0, i, 0, i);
                     pic = patriarch.CreatePicture(anchor, index);
                     pic.Resize();
                 }
             }
         }
         workbook.Write(stream);
     }
 }
        /// <summary>
        /// 尝试添加图片到工作表当中
        /// </summary>
        /// <param name="sheet">工作表</param>
        /// <param name="row">行索引</param>
        /// <param name="col">列索引</param>
        /// <param name="pictureBytes">图片数据</param>
        /// <param name="pictureType">图片类型</param>
        /// <returns>添加成功则返回true</returns>
        public static bool TryAddPicture(this ISheet sheet, int row, int col, byte[] pictureBytes,
                                         PictureType pictureType = PictureType.PNG)
        {
            if (sheet is null)
            {
                throw new ArgumentNullException(nameof(sheet));
            }

            try
            {
                var pictureIndex = sheet.Workbook.AddPicture(pictureBytes, pictureType);

                var clientAnchor = sheet.Workbook.GetCreationHelper().CreateClientAnchor();
                clientAnchor.Row1 = row;
                clientAnchor.Col1 = col;

                var picture = (sheet.DrawingPatriarch ?? sheet.CreateDrawingPatriarch())
                              .CreatePicture(clientAnchor, pictureIndex);
                picture.Resize();
                return(true);
            }
            catch (Exception e)
            {
                Debug.WriteLine(e);
            }
            return(false);
        }
        /// <summary>
        /// 将图片导出到Excel
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="xssfworkbook"></param>
        /// <param name="imageName"></param>
        private void ExportImgToExcel(ISheet sheet, HSSFWorkbook xssfworkbook, string imageName)
        {
            try
            {
                string picName   = string.IsNullOrEmpty(imageName) ? @"Image\sjlogo.png" : imageName;
                var    picType   = string.IsNullOrEmpty(imageName) ? PictureType.PNG : GetPicType(imageName);
                Image  imgOutput = Bitmap.FromFile(Path.Combine(Directory.GetCurrentDirectory(), picName));

                //Image imgOutput = System.Drawing.Bitmap.FromStream()
                Image img = imgOutput.GetThumbnailImage(160, 115, null, IntPtr.Zero);
                //图片转换为文件流
                MemoryStream ms = new MemoryStream();
                img.Save(ms, ImageFormat.Bmp);
                BinaryReader br       = new BinaryReader(ms);
                var          picBytes = ms.ToArray();
                ms.Close();
                //插入图片
                if (picBytes != null && picBytes.Length > 0)
                {
                    int pictureIdx = xssfworkbook.AddPicture(picBytes, picType);  //添加图片

                    HSSFPatriarch    patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
                    HSSFClientAnchor anchor    = new HSSFClientAnchor(200, 90, 240, 200, 0, 0, 1, 0);// 90 200 图片高度110
                    // new HSSFClientAnchor(X1, Y1,  X2, Y2,  列索引1,行索引1 , 列索引2, 行索引2); 行列索引从0开始 ,行列索引指的是 图片左上角所在单元格的行列和 图片右下角所在单元格的行列
                    //X: 0-1024  Y:0-256 ; X1\X2相对本单元格,距离Y轴的偏移量,最大值1023;Y1\Y2相对本单元格,距离X轴的偏移量,最大值255;
                    HSSFPicture picture = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
                    //picture.Resize(); //使图像恢复到原始大小
                    picBytes = null;
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
Exemplo n.º 8
0
        /// 向sheet插入图片
        ///
        ///
        ///
        private void AddPieChart(ISheet sheet, HSSFWorkbook workbook, string fileurl, int row, int col, string mappath)
        {
            try
            {
                if (string.IsNullOrEmpty(fileurl))
                {
                    return;
                }
                string path     = mappath + fileurl.Replace("/", @"\");
                string FileName = path;
                if (!File.Exists(FileName))
                {
                    return;
                }
                byte[] bytes = File.ReadAllBytes(FileName);

                if (!string.IsNullOrEmpty(FileName))
                {
                    int              pictureIdx = workbook.AddPicture(bytes, PictureType.JPEG);
                    HSSFPatriarch    patriarch  = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
                    HSSFClientAnchor anchor     = new HSSFClientAnchor(70, 10, 0, 0, col, row, col + 1, row + 1);
                    HSSFPicture      pict       = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
                    // pict.Resize();这句话一定不要,这是用图片原始大小来显示
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
Exemplo n.º 9
0
 //向excel中提交图片
 private void AddCellPicture(ISheet sheet, HSSFWorkbook workbook, string fileurl, int row, int col)
 {
     try
     {
         //由于File类只能读取本地资源,所以在配置文件中配置了物理路径的前半部分
         string DiscPath = ConfigurationManager.AppSettings["PictureDiscPath"];
         // string FileName = DiscPath.Replace("\\", "/") + fileurl.Replace("http://www.bolioptics.com/", "");
         string   FileName = fileurl;
         FileInfo file     = new FileInfo(FileName);
         if (file.Exists == true)
         {
             byte[] bytes = System.IO.File.ReadAllBytes(FileName);
             if (!string.IsNullOrEmpty(FileName))
             {
                 int              pictureIdx = workbook.AddPicture(bytes, NPOI.SS.UserModel.PictureType.JPEG);
                 HSSFPatriarch    patriarch  = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
                 HSSFClientAnchor anchor     = new HSSFClientAnchor(0, 0, 0, 0, col, row, col + 1, row + 1);
                 HSSFPicture      pict       = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
             }
         }
     }
     catch (Exception ex)
     {
         throw ex;
     }
 }
Exemplo n.º 10
0
        //导出excel
        public ActionResult GetExcel(int jiedian, int data, DateTime today)
        {
            string title = today.ToString("yyyy-MM-dd ") + jiedian + "号节点" + "数据统计图";

            title = title + Guid.NewGuid().ToString();

            IWorkbook hssfworkbook = new HSSFWorkbook();
            ISheet    sheet        = hssfworkbook.CreateSheet("智能水产数据");

            string path = Server.MapPath("1.jpg");

            path = path.Replace("Home\\", "");

            byte[] bytes = System.IO.File.ReadAllBytes(path);

            int pictureIdx = hssfworkbook.AddPicture(bytes, PictureType.JPEG);

            HSSFPatriarch    patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
            HSSFClientAnchor anchor    = new HSSFClientAnchor(0, 0, 1023, 0, 0, 0, 10, 20);
            HSSFPicture      pict      = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);

            string writepath = Server.MapPath("upload/" + title + ".xls");

            writepath = writepath.Replace("Home\\", "");

            using (FileStream write = System.IO.File.OpenWrite(writepath))
            {
                hssfworkbook.Write(write);
            }
            return(Content("/upload/" + title + ".xls"));
        }
Exemplo n.º 11
0
        private void exportXLSX_Click(object sender, RoutedEventArgs e)
        {
            var newFile = @"newbook.core.xlsx";

            using (var fs = new FileStream(newFile, FileMode.Create, FileAccess.Write))
            {
                // XSSFWorkbook : *.xlsx >= Excel2007
                // HSSFWorkbook : *.xls  < Excel2007
                IWorkbook workbook = new XSSFWorkbook();

                ISheet sheet1 = workbook.CreateSheet("Sheet Name");

                // 所有索引都从0开始

                // 合并单元格
                sheet1.AddMergedRegion(new CellRangeAddress(0, 0, 0, 10));

                var  rowIndex = 0;
                IRow row      = sheet1.CreateRow(rowIndex); //创建行
                row.Height = 30 * 80;
                row.CreateCell(0).SetCellValue("this is content");
                sheet1.AutoSizeColumn(0); //按照值的长短 自动调节列的大小
                rowIndex++;

                // 插入图片
                byte[]             data    = File.ReadAllBytes(@"image.jpg");
                int                picInd  = workbook.AddPicture(data, NPOI.SS.UserModel.PictureType.JPEG);
                XSSFCreationHelper helper  = workbook.GetCreationHelper() as XSSFCreationHelper;
                XSSFDrawing        drawing = sheet1.CreateDrawingPatriarch() as XSSFDrawing;
                XSSFClientAnchor   anchor  = helper.CreateClientAnchor() as XSSFClientAnchor;
                anchor.Col1 = 10;
                anchor.Row1 = 0;
                XSSFPicture pict = drawing.CreatePicture(anchor, picInd) as XSSFPicture;
                pict.Resize();

                // 新建sheet
                var sheet2 = workbook.CreateSheet("Sheet2");
                // 更改样式
                var style1 = workbook.CreateCellStyle();
                style1.FillForegroundColor = HSSFColor.Blue.Index2;
                style1.FillPattern         = FillPattern.SolidForeground;

                var style2 = workbook.CreateCellStyle();
                style2.FillForegroundColor = HSSFColor.Yellow.Index2;
                style2.FillPattern         = FillPattern.SolidForeground;

                var cell2 = sheet2.CreateRow(0).CreateCell(0);
                cell2.CellStyle = style1;
                cell2.SetCellValue(0);

                cell2           = sheet2.CreateRow(1).CreateCell(0);
                cell2.CellStyle = style2;
                cell2.SetCellValue(1);


                //保存
                workbook.Write(fs);
            }
            txtStatus.Text = "writing xlsx successful!";
        }
Exemplo n.º 12
0
 /// <summary>
 /// 处理图片
 /// </summary>
 /// <param name="sheet"></param>
 /// <param name="ExportPictures"></param>
 /// <returns></returns>
 private void ProcessPicture(ISheet sheet, IEnumerable <ExportPictureEntity> ExportPictures)
 {
     if (ExportPictures == null || ExportPictures.Any() == false)
     {
         return;
     }
     foreach (var item in ExportPictures)
     {
         IRow row = sheet.GetRow(item.RowIndex);
         if (row == null)
         {
             row = sheet.CreateRow(item.RowIndex);
         }
         ICell cell = row.GetCell(item.ColIndex);
         if (cell == null)
         {
             cell = row.CreateCell(item.ColIndex);
         }
         PictureEntity entity = cell.GetPictureData(item.Url, item.UrlType);
         if (entity == null)
         {
             continue;
         }
         IPicture pic = sheet.CreateDrawingPatriarch().CreatePicture(entity.Anchor, entity.PictureIndex);
         if (item.Scale != 1)
         {
             pic.Resize(item.Scale);
         }
     }
 }
Exemplo n.º 13
0
        static void Main(string[] args)
        {
            IWorkbook wb     = new XSSFWorkbook();
            ISheet    sheet1 = wb.CreateSheet("First Sheet");

            //add picture data to this workbook.
            byte[] bytes      = File.ReadAllBytes("../../data/aspose.png");
            int    pictureIdx = wb.AddPicture(bytes, PictureType.PNG);

            ICreationHelper helper = wb.GetCreationHelper();

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

            // add a picture shape
            IClientAnchor anchor = helper.CreateClientAnchor();

            //set top-left corner of the picture,
            //subsequent call of Picture#resize() will operate relative to it
            anchor.Col1 = 3;
            anchor.Row1 = 2;
            IPicture pict = drawing.CreatePicture(anchor, pictureIdx);

            //auto-size picture relative to its top-left corner
            pict.Resize();

            FileStream sw = File.Create("../../data/image.xlsx");

            wb.Write(sw);
            sw.Close();
        }
Exemplo n.º 14
0
        /// <summary>
        /// 设置批注
        /// </summary>
        /// <param name="cell"></param>
        /// <param name="suffix"></param>
        /// <param name="comment"></param>
        /// <param name="col1"></param>
        /// <param name="row1"></param>
        /// <param name="col2"></param>
        /// <param name="row2"></param>
        public static void SetCellComment(this ICell cell, CommentEntity entitiy)
        {
            ISheet        sheet        = cell.Sheet;
            IClientAnchor clientAnchor = sheet.Workbook.GetCreationHelper().CreateClientAnchor();

            clientAnchor.AnchorType = AnchorType.MoveDontResize.GetType().ToInt();
            clientAnchor.Dx1        = entitiy.Dx1;
            clientAnchor.Dy1        = entitiy.Dy1;
            clientAnchor.Dx2        = entitiy.Dx2;
            clientAnchor.Dy2        = entitiy.Dy2;
            clientAnchor.Col1       = cell.ColumnIndex;
            clientAnchor.Row1       = cell.RowIndex;
            clientAnchor.Col2       = cell.ColumnIndex + entitiy.Width;
            clientAnchor.Row2       = cell.RowIndex + entitiy.Height;

            IDrawing draw    = sheet.CreateDrawingPatriarch();
            IComment comment = draw.CreateCellComment(clientAnchor);

            comment.Visible = false;
            if (sheet.Workbook is HSSFWorkbook)
            {
                comment.String = new HSSFRichTextString(entitiy.Text);
            }
            else
            {
                comment.String = new XSSFRichTextString(entitiy.Text);
            }
            cell.CellComment = comment;
        }
Exemplo n.º 15
0
        private static void DrawSheet3(ISheet sheet3)
        {
            // Create a row and size one of the cells reasonably large
            IRow row = sheet3.CreateRow(2);

            row.HeightInPoints = 140;
            row.CreateCell(1);
            sheet3.SetColumnWidth(2, 9000);

            // Create the Drawing patriarch.  This is the top level container for
            // all shapes. This will clear out any existing shapes for that sheet.
            HSSFPatriarch patriarch = (HSSFPatriarch)sheet3.CreateDrawingPatriarch();

            // Create a shape group.
            HSSFShapeGroup group = patriarch.CreateGroup(
                new HSSFClientAnchor(0, 0, 900, 200, (short)2, 2, (short)2, 2));

            // Create a couple of lines in the group.
            HSSFSimpleShape shape1 = group.CreateShape(new HSSFChildAnchor(3, 3, 500, 500));

            shape1.ShapeType = (HSSFSimpleShape.OBJECT_TYPE_LINE);
            ((HSSFChildAnchor)shape1.Anchor).SetAnchor((short)3, 3, 500, 500);
            HSSFSimpleShape shape2 = group.CreateShape(new HSSFChildAnchor((short)1, 200, 400, 600));

            shape2.ShapeType = (HSSFSimpleShape.OBJECT_TYPE_LINE);
        }
Exemplo n.º 16
0
        public void TestQuickGuide()
        {
            IWorkbook wb = _testDataProvider.CreateWorkbook();

            ICreationHelper factory = wb.GetCreationHelper();

            ISheet sheet = wb.CreateSheet();

            ICell cell = sheet.CreateRow(3).CreateCell(5);

            cell.SetCellValue("F4");

            IDrawing drawing = sheet.CreateDrawingPatriarch();

            IClientAnchor   anchor  = factory.CreateClientAnchor();
            IComment        comment = drawing.CreateCellComment(anchor);
            IRichTextString str     = factory.CreateRichTextString("Hello, World!");

            comment.String = (str);
            comment.Author = ("Apache POI");
            //assign the comment to the cell
            cell.CellComment = (comment);

            wb      = _testDataProvider.WriteOutAndReadBack(wb);
            sheet   = wb.GetSheetAt(0);
            cell    = sheet.GetRow(3).GetCell(5);
            comment = cell.CellComment;
            Assert.IsNotNull(comment);
            Assert.AreEqual("Hello, World!", comment.String.String);
            Assert.AreEqual("Apache POI", comment.Author);
            Assert.AreEqual(3, comment.Row);
            Assert.AreEqual(5, comment.Column);
        }
Exemplo n.º 17
0
        private IWorkbook OpenExcel(String filePath, params Byte[][] imageData)
        {
            IWorkbook workBook = null;

            if (File.Exists(filePath))
            {
                using (FileStream stream = new FileStream(filePath, FileMode.Open,
                                                          FileAccess.ReadWrite, FileShare.ReadWrite, 8096,
                                                          FileOptions.WriteThrough))
                {
                    workBook = GenerationWorkBook(filePath, stream);
                    Int32 index = workBook.AddPicture(imageData[0], PictureType.PNG);

                    ISheet   sheet     = workBook.GetSheetAt(0);
                    IDrawing patriarch = sheet.CreateDrawingPatriarch();

                    Int32         lastRowNum = sheet.LastRowNum;
                    IClientAnchor anchor     = GenerationClientAnchor(filePath, 0, 0, 0, 0, 1, lastRowNum + 2, 8, lastRowNum + 25);

                    IPicture pic = patriarch.CreatePicture(anchor, index);
                    File.Delete(filePath);
                    using (FileStream newStream = new FileStream(filePath, FileMode.Create, FileAccess.ReadWrite))
                    {
                        workBook.Write(newStream);
                    }
                }
            }
            return(workBook);
        }
Exemplo n.º 18
0
        static void Main(string[] args)
        {
            IWorkbook wb    = new XSSFWorkbook();
            ISheet    sheet = wb.CreateSheet("linechart");


            // Create a row and put some cells in it. Rows are 0 based.
            IRow  row;
            ICell cell;

            for (int rowIndex = 0; rowIndex < NUM_OF_ROWS; rowIndex++)
            {
                row = sheet.CreateRow((short)rowIndex);
                for (int colIndex = 0; colIndex < NUM_OF_COLUMNS; colIndex++)
                {
                    cell = row.CreateCell((short)colIndex);
                    cell.SetCellValue(colIndex * (rowIndex + 1));
                }
            }

            IDrawing      drawing = sheet.CreateDrawingPatriarch();
            IClientAnchor anchor1 = drawing.CreateAnchor(0, 0, 0, 0, 0, 5, 10, 15);

            CreateChart(drawing, sheet, anchor1, "title1", "title2");
            IClientAnchor anchor2 = drawing.CreateAnchor(0, 0, 0, 0, 0, 20, 10, 35);

            CreateChart(drawing, sheet, anchor2, "s1", "s2");
            using (FileStream fs = File.Create("test.xlsx"))
            {
                wb.Write(fs);
            }
        }
Exemplo n.º 19
0
        public static int LoadImage(string path, ISheet sheet, int row, int col, string name)
        {
            var w             = (int)(sheet.GetColumnWidth(col) / 36.56) - 1;//36.56
            var h             = (int)(sheet.GetRow(row).Height / 15) - 1;
            var fileExtension = Path.GetExtension(path + name);
            var pPath         = string.Format("{0}{1:yyyyMMddHHmmssfff}{2}", path, DateTime.Now, fileExtension);
            // bool b = Picture.GetPicThumbnail_Filling(path + name, pPath, h, w, 90);
            var id = 0;

            using (var file = new FileStream(pPath, FileMode.Open, FileAccess.Read))
            {
                var buffer = new byte[file.Length];
                file.Read(buffer, 0, (int)file.Length);
                id = sheet.Workbook.AddPicture(buffer, PictureType.JPEG);
                var patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
                //create the anchor
                var anchor = new HSSFClientAnchor(10, 4, 1023, 255, col, row, col, row);
                anchor.AnchorType = (AnchorType)1;
                //load the picture and get the picture index in the workbook
                var picture = (HSSFPicture)patriarch.CreatePicture(anchor, id);
                //Reset the image to the original size.
                //picture.Resize();   //Note: Resize will reset client anchor you set.
                //picture.LineStyle = LineStyle.DashDotGel;
            }
            //File.Delete(pPath);
            return(id);
        }
Exemplo n.º 20
0
        /// <summary>
        /// 更新Excel表格
        /// </summary>
        /// <param name="outputFile">需更新的excel表格路径</param>
        /// <param name="sheetname">sheet名</param>
        /// <param name="updateData">需更新的img数据</param>
        /// <param name="coluid">需更新的列号</param>
        /// <param name="rowid">需更新的开始行号</param>
        /// <param name="IsTrue">true:以横向输出; false:以横向输出</param>
        public static void UpdateImgToExcel(string outputFile, string sheetname, byte[] updateData, int beginCol, int beginRow, int endCol, int endRow)
        {
            try
            {
                FileStream readfile = new FileStream(outputFile, FileMode.Open, FileAccess.Read);

                HSSFWorkbook hssfworkbook = new HSSFWorkbook(readfile);
                ISheet       sheet1       = hssfworkbook.GetSheet(sheetname);

                int pictureIdx = hssfworkbook.AddPicture(updateData, PictureType.JPEG);

                HSSFPatriarch patriarch = (HSSFPatriarch)sheet1.CreateDrawingPatriarch();
                // 插图片的位置  HSSFClientAnchor(dx1,dy1,dx2,dy2,col1,row1,col2,row2) 后面再作解释
                HSSFClientAnchor anchor = new HSSFClientAnchor(30, 30, 30, 30, beginCol, beginRow, endCol, endRow);
                //把图片插到相应的位置
                HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);

                readfile.Close();
                FileStream writefile = new FileStream(outputFile, FileMode.Create, FileAccess.Write);
                hssfworkbook.Write(writefile);
                writefile.Close();
            }
            catch (Exception ex)
            {
                //wl.WriteLogs(ex.ToString());
            }
        }
Exemplo n.º 21
0
        static void Main(string[] args)
        {
            IWorkbook workbook = new XSSFWorkbook();
            ISheet    sheet1   = workbook.CreateSheet("PictureSheet");

            IDrawing patriarch = sheet1.CreateDrawingPatriarch();
            //create the anchor
            XSSFClientAnchor anchor = new XSSFClientAnchor(500, 200, 0, 0, 2, 2, 4, 7);

            anchor.AnchorType = AnchorType.MoveDontResize;
            //load the picture and get the picture index in the workbook
            //first picture
            int         imageId = LoadImage("../../image/HumpbackWhale.jpg", workbook);
            XSSFPicture picture = (XSSFPicture)patriarch.CreatePicture(anchor, imageId);

            //Reset the image to the original size.
            //picture.Resize();   //Note: Resize will reset client anchor you set.
            picture.LineStyle = LineStyle.DashDotGel;
            picture.Resize();
            //second picture
            int imageId2              = LoadImage("../../image/HumpbackWhale.jpg", workbook);
            XSSFClientAnchor anchor2  = new XSSFClientAnchor(500, 200, 0, 0, 5, 10, 7, 15);
            XSSFPicture      picture2 = (XSSFPicture)patriarch.CreatePicture(anchor2, imageId2);

            picture.LineStyle = LineStyle.DashDotGel;


            using (FileStream sw = File.Create("test.xlsx"))
            {
                workbook.Write(sw);
            }
        }
Exemplo n.º 22
0
        public void AttemptToSave2CommentsWithSameCoordinates()
        {
            IWorkbook       wb        = _testDataProvider.CreateWorkbook();
            ISheet          sh        = wb.CreateSheet();
            ICreationHelper factory   = wb.GetCreationHelper();
            IDrawing        patriarch = sh.CreateDrawingPatriarch();

            patriarch.CreateCellComment(factory.CreateClientAnchor());

            try
            {
                patriarch.CreateCellComment(factory.CreateClientAnchor());
                _testDataProvider.WriteOutAndReadBack(wb);
                Assert.Fail("Expected InvalidOperationException(found multiple cell comments for cell $A$1");
            }
            catch (InvalidOperationException e)
            {
                // HSSFWorkbooks fail when writing out workbook
                Assert.AreEqual(e.Message, "found multiple cell comments for cell A1");
            }
            catch (ArgumentException e)
            {
                // XSSFWorkbooks fail when creating and setting the cell address of the comment
                Assert.AreEqual(e.Message, "Multiple cell comments in one cell are not allowed, cell: A1");
            }
            finally
            {
                wb.Close();
            }
        }
Exemplo n.º 23
0
        private static void PostilAdd(Tk5ListMetaData metaInfos, ImportError importError, IWorkbook workBook, ISheet sheet)
        {
            IDrawing part = sheet.CreateDrawingPatriarch();
            Dictionary <string, int> indexOfName = new Dictionary <string, int>();
            int i = 0;

            foreach (var info in metaInfos.Table.TableList)
            {
                indexOfName.Add(info.DisplayName, i);
                i++;
            }

            foreach (var err in importError)
            {
                IRow            row     = sheet.GetRow(err.IndexOfRow);
                IComment        comment = null;
                ICell           cell    = row.GetCell(indexOfName[err.ColumnName]);
                ICreationHelper factory = workBook.GetCreationHelper();
                IClientAnchor   anchor  = null;
                anchor           = factory.CreateClientAnchor();
                anchor.Col1      = cell.ColumnIndex + 2;
                anchor.Col2      = cell.ColumnIndex + 4;
                anchor.Row1      = row.RowNum;
                anchor.Row2      = row.RowNum + 3;
                comment          = part.CreateCellComment(anchor);
                comment.Author   = "mitu";
                comment.String   = new HSSFRichTextString(err.ErrorMsg);
                cell.CellComment = comment;
            }
        }
Exemplo n.º 24
0
        public static void inserirImagem(string imgPath)
        {
            //Wait the page to complete
            //Browser.Wait.Until(wd => Browser.JSexec.ExecuteScript("return document.readyState") == "complete");

            ISheet        sheet1    = XLSUtils.hssfworkbook.GetSheet("Telas");
            HSSFPatriarch patriarch = (HSSFPatriarch)sheet1.CreateDrawingPatriarch();
            //create the anchor
            HSSFClientAnchor anchor;

            int larguraImg = 2 * 9;
            int alturaImg  = 2 * 16;

            //anchor = new HSSFClientAnchor(0, (alturaImg * imgsQtd) + 3, larguraImg, (alturaImg * (imgsQtd + 1)) + 3, 0, (alturaImg * imgsQtd) + 3, larguraImg, (alturaImg * (imgsQtd + 1)) + 3);
            anchor            = new HSSFClientAnchor(0, 0, larguraImg, alturaImg, 0, alturaImg * imgsQtd, larguraImg, alturaImg * (imgsQtd + 1));
            anchor.Row1       = anchor.Row1 + 3;
            anchor.AnchorType = AnchorType.MoveAndResize;
            //load the picture and get the picture index in the workbook
            HSSFPicture picture = (HSSFPicture)patriarch.CreatePicture(anchor, XLSUtils.LoadImage(imgPath, XLSUtils.hssfworkbook));

            //Reset the image to the original size.
            //picture.Resize();   //Note: Resize will reset client anchor you set.
            picture.LineStyle = LineStyle.DashDotGel;
            FunctionalTestCase.imgsQtd++;
            sheet1.CreateRow(anchor.Row1 - 2).CreateCell(0).SetCellValue("Tela " + imgsQtd.ToString("00"));

            XLSUtils.WriteToFile(currentFile);

            registrarTela();
        }
Exemplo n.º 25
0
        private static ISheet IncluirLogoVLI(HSSFWorkbook workbook, ISheet sheet)
        {
            var merge = new NPOI.SS.Util.CellRangeAddress(1, 2, 1, 2);

            sheet.AddMergedRegion(merge);

            var diretorioAtual = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location);

            var caminho = $"{diretorioAtual}/Recursos/logoVLI.png";

            byte[] data = ArquivosUtil.RetornarArquivo(caminho);

            int             pictureIndex = workbook.AddPicture(data, PictureType.JPEG);
            ICreationHelper helper       = workbook.GetCreationHelper();
            IDrawing        drawing      = sheet.CreateDrawingPatriarch();

            IClientAnchor anchor = helper.CreateClientAnchor();

            anchor.Col1 = 1;
            anchor.Row1 = 1;
            IPicture picture = drawing.CreatePicture(anchor, pictureIndex);

            picture.Resize(1.8, 1.8); /*Não mudar o tamanho da imagem física. Aparecerá sobrepondo as outras células ou fixa apenas na célula alocada(mesmo sendo mesclada)*/

            return(sheet);
        }
Exemplo n.º 26
0
        public void GetCellComment()
        {
            IWorkbook       wb      = _testDataProvider.CreateWorkbook();
            ISheet          sheet   = wb.CreateSheet();
            ICreationHelper factory = wb.GetCreationHelper();
            IRow            row     = sheet.CreateRow(0);
            ICell           cell    = row.CreateCell(1);

            // cell does not have a comment
            Assert.IsNull(cell.CellComment);

            // add a cell comment
            IClientAnchor anchor = factory.CreateClientAnchor();

            anchor.Col1 = cell.ColumnIndex;
            anchor.Col2 = cell.ColumnIndex + 1;
            anchor.Row1 = row.RowNum;
            anchor.Row2 = row.RowNum + 3;
            IDrawing        drawing = sheet.CreateDrawingPatriarch();
            IComment        comment = drawing.CreateCellComment(anchor);
            IRichTextString str     = factory.CreateRichTextString("Hello, World!");

            comment.String   = str;
            comment.Author   = "Apache POI";
            cell.CellComment = comment;
            // ideally assertSame, but XSSFCell creates a new XSSFCellComment wrapping the same bean for every call to getCellComment.
            Assert.AreEqual(comment, cell.CellComment);
            wb.Close();
        }
Exemplo n.º 27
0
        private static void DrawSheet4(ISheet sheet4, HSSFWorkbook wb)
        {
            // Create the Drawing patriarch.  This is the top level container for
            // all shapes. This will clear out any existing shapes for that sheet.
            HSSFPatriarch patriarch = (HSSFPatriarch)sheet4.CreateDrawingPatriarch();

            // Create a couple of textboxes
            HSSFTextbox textbox1 = (HSSFTextbox)patriarch.CreateTextbox(
                new HSSFClientAnchor(0, 0, 0, 0, (short)1, 1, (short)2, 2));

            textbox1.String = new HSSFRichTextString("This is a test");
            HSSFTextbox textbox2 = (HSSFTextbox)patriarch.CreateTextbox(
                new HSSFClientAnchor(0, 0, 900, 100, (short)3, 3, (short)3, 4));

            textbox2.String = new HSSFRichTextString("Woo");
            textbox2.SetFillColor(200, 0, 0);
            textbox2.LineStyle = LineStyle.DotGel;

            // Create third one with some fancy font styling.
            HSSFTextbox textbox3 = (HSSFTextbox)patriarch.CreateTextbox(
                new HSSFClientAnchor(0, 0, 900, 100, (short)4, 4, (short)5, 4 + 1));
            IFont font = wb.CreateFont();

            font.IsItalic  = true;
            font.Underline = (byte)FontUnderlineType.DOUBLE;
            HSSFRichTextString str = new HSSFRichTextString("Woo!!!");

            str.ApplyFont(2, 5, font);
            textbox3.String    = str;
            textbox3.FillColor = 0x08000030;
            textbox3.LineStyle = LineStyle.None; // no line around the textbox.
            textbox3.IsNoFill  = true;           // make it transparent
        }
Exemplo n.º 28
0
        /// <summary>
        /// 导出按钮监听
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btn_ExTWO_Click(object sender, EventArgs e)
        {
            SaveFileDialog sfd = new SaveFileDialog();

            sfd.Filter = @"Excel|*.xls";
            sfd.ShowDialog();
            string    path        = sfd.FileName;
            PlotModel pm          = plotView1.Model;
            var       pngExporter = new PngExporter {
                Width = 1000, Height = 400, Background = OxyColors.White
            };
            Bitmap       bitmap = pngExporter.ExportToBitmap(pm);
            Image        image  = bitmap;
            MemoryStream ms     = new MemoryStream();

            image.Save(ms, System.Drawing.Imaging.ImageFormat.Gif);
            byte[]    bytes    = ms.ToArray();
            IWorkbook workbook = new HSSFWorkbook();

            workbook.CreateSheet("sheet1");
            if (path != "")
            {
                using (FileStream fs = File.Create(path))
                {
                    ISheet           sheet  = workbook.GetSheetAt(0);
                    HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, 0, 5, 6, 10);
                    int      pic            = workbook.AddPicture(bytes, PictureType.PNG);
                    IDrawing patriarch      = sheet.CreateDrawingPatriarch();
                    IPicture ipic           = patriarch.CreatePicture(anchor, pic);
                    ipic.Resize();
                    workbook.Write(fs);
                }
                MessageBox.Show("保存成功!");
            }
        }
Exemplo n.º 29
0
        public void TableToExcel(DataTable dt, Image img)
        {
            if (Path.GetExtension(fileName) == ".xls")
            {
                workbook = new HSSFWorkbook();
            }
            else if (Path.GetExtension(fileName) == ".xlsx")
            {
                workbook = new XSSFWorkbook();
            }
            else
            {
                return;
            }
            ISheet sheet = workbook.CreateSheet(string.IsNullOrEmpty(dt.TableName) ? "Sheet1" : dt.TableName);


            MemoryStream ms = new MemoryStream();

            img.Save(ms, System.Drawing.Imaging.ImageFormat.Png);

            HSSFPatriarch    patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
            HSSFClientAnchor anchor    = new HSSFClientAnchor(0, 0, 1023, 255, 2, 2, 14, 34);
            HSSFPicture      pict      = (HSSFPicture)patriarch.CreatePicture(anchor, workbook.AddPicture(ms.ToArray(), PictureType.PNG));

            pict.Resize();
            ms.Close();
            //表头
            IRow row = sheet.CreateRow(anchor.Row2 + 1);

            for (int i = 0; i < dt.Columns.Count; i++)
            {
                ICell cell = row.CreateCell(i);
                cell.SetCellValue(dt.Columns[i].ColumnName);
            }
            //行数据
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                IRow ir = sheet.CreateRow(i + anchor.Row2 + 2);
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    ICell cell = ir.CreateCell(j);
                    cell.SetCellValue(dt.Rows[i][j].ToString());
                }
            }

            //转为字节数组
            MemoryStream stream = new MemoryStream();

            workbook.Write(stream);
            var buf = stream.ToArray();

            //保存为Excel文件
            using (FileStream fs = new FileStream(fileName, FileMode.Create, FileAccess.Write))
            {
                fs.Write(buf, 0, buf.Length);
                fs.Flush();
            }
        }
Exemplo n.º 30
0
        /// <summary>
        /// 样表
        /// </summary>
        /// <param name="entity">实体</param>
        /// <returns></returns>
        private ActionResult ImportDownload(BF_IMPORT.Entity entity)
        {
            try
            {
                List <string>            commentList = new List <string>();
                DataTable                dt          = BF_IMPORT.GetSampleTable(entity, out commentList);
                string                   filename    = HttpUtility.UrlEncode(string.Format("{0}_{1}.xlsx", entity.NAME, DateTime.Now.ToString("yyyyMMddHHmmss")), Encoding.UTF8);
                string                   path        = System.Web.HttpContext.Current.Server.MapPath("~/tmp/");
                Library.Export.ExcelFile export      = new Library.Export.ExcelFile(path);
                string                   fullName    = export.ToExcel(dt);
                if (string.IsNullOrWhiteSpace(fullName) == true || System.IO.File.Exists(fullName) == false)
                {
                    return(ShowAlert("未生成Excel文件"));
                }

                IWorkbook workBook;
                using (FileStream fs = System.IO.File.OpenRead(fullName))
                {
                    workBook = new XSSFWorkbook(fs);
                    ISheet sheet = workBook.GetSheetAt(0);
                    IRow   row   = sheet.GetRow(0);

                    XSSFDrawing patriarch = (XSSFDrawing)sheet.CreateDrawingPatriarch();
                    for (int c = 0; c < row.Cells.Count; c++)
                    {
                        if (string.IsNullOrWhiteSpace(commentList[c]) == false)
                        {
                            IComment comment = patriarch.CreateCellComment(new XSSFClientAnchor(0, 50, 0, 50, c, 0, c + 3, 5));
                            comment.Author           = "编辑提示";
                            comment.String           = new XSSFRichTextString("【编辑提示】\r\n" + commentList[c]);
                            row.Cells[c].CellComment = comment;
                        }
                    }
                }

                //重写文件
                using (FileStream fs = System.IO.File.Create(fullName))
                {
                    workBook.Write(fs);
                    fs.Close();
                }

                System.Web.HttpContext.Current.Response.Buffer = true;
                System.Web.HttpContext.Current.Response.Clear();//清除缓冲区所有内容
                System.Web.HttpContext.Current.Response.ContentType = "application/octet-stream";
                System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=" + filename);
                System.Web.HttpContext.Current.Response.WriteFile(fullName);
                System.Web.HttpContext.Current.Response.Flush();
                System.Web.HttpContext.Current.Response.End();
                //删除文件
                export.Delete(fullName);
            }
            catch (Exception ex)
            {
                ShowAlert("生成样表出错:" + ex.Message);
            }
            return(View());
        }
Exemplo n.º 31
0
        private static void DrawSheet1(ISheet sheet1)
        {
            // Create a row and size one of the cells reasonably large.
            IRow row = sheet1.CreateRow(2);
            row.Height = ((short)2800);
            row.CreateCell(1);
            sheet1.SetColumnWidth(2, 9000);

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

            // Draw some lines and an oval.
            DrawLinesToCenter(patriarch);
            DrawManyLines(patriarch);
            DrawOval(patriarch);
            DrawPolygon(patriarch);

            // Draw a rectangle.
            HSSFSimpleShape rect = patriarch.CreateSimpleShape(new HSSFClientAnchor(100, 100, 900, 200, (short)0, 0, (short)0, 0));
            rect.ShapeType = (HSSFSimpleShape.OBJECT_TYPE_RECTANGLE);
        }
Exemplo n.º 32
0
        private static void DrawSheet4(ISheet sheet4, HSSFWorkbook wb)
        {
            // Create the Drawing patriarch.  This is the top level container for
            // all shapes. This will clear out any existing shapes for that sheet.
            HSSFPatriarch patriarch = (HSSFPatriarch)sheet4.CreateDrawingPatriarch();

            // Create a couple of textboxes
            HSSFTextbox textbox1 = (HSSFTextbox)patriarch.CreateTextbox(
                    new HSSFClientAnchor(0, 0, 0, 0, (short)1, 1, (short)2, 2));
            textbox1.String = new HSSFRichTextString("This is a test");
            HSSFTextbox textbox2 = (HSSFTextbox)patriarch.CreateTextbox(
                    new HSSFClientAnchor(0, 0, 900, 100, (short)3, 3, (short)3, 4));
            textbox2.String = new HSSFRichTextString("Woo");
            textbox2.SetFillColor(200, 0, 0);
            textbox2.LineStyle = LineStyle.DotGel;
             
            // Create third one with some fancy font styling.
            HSSFTextbox textbox3 = (HSSFTextbox)patriarch.CreateTextbox(
                    new HSSFClientAnchor(0, 0, 900, 100, (short)4, 4, (short)5, 4 + 1));
            IFont font = wb.CreateFont();
            font.IsItalic = true;
            font.Underline = FontUnderlineType.Double;
            HSSFRichTextString str = new HSSFRichTextString("Woo!!!");
            str.ApplyFont(2, 5, font);
            textbox3.String = str;
            textbox3.FillColor = 0x08000030;
            textbox3.LineStyle = LineStyle.None;  // no line around the textbox.
            textbox3.IsNoFill = true;    // make it transparent
        }
Exemplo n.º 33
0
        private static void DrawSheet2(ISheet sheet2)
        {
            // Create a row and size one of the cells reasonably large.
            IRow row = sheet2.CreateRow(2);
            row.CreateCell(1);
            row.HeightInPoints = 240;
            sheet2.SetColumnWidth(2, 9000);

            // Create the Drawing patriarch.  This is the top level container for
            // all shapes. This will clear out any existing shapes for that sheet.
            HSSFPatriarch patriarch = (HSSFPatriarch)sheet2.CreateDrawingPatriarch();

            // Draw a grid in one of the cells.
            DrawGrid(patriarch);
        }
Exemplo n.º 34
0
        /// <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);
                                }
                                
                            }
                            
                            
                        }
                        
                    }
                }
            }
        }
Exemplo n.º 35
0
        private static void DrawSheet3(ISheet sheet3)
        {
            // Create a row and size one of the cells reasonably large
            IRow row = sheet3.CreateRow(2);
            row.HeightInPoints = 140;
            row.CreateCell(1);
            sheet3.SetColumnWidth(2, 9000);

            // Create the Drawing patriarch.  This is the top level container for
            // all shapes. This will clear out any existing shapes for that sheet.
            HSSFPatriarch patriarch = (HSSFPatriarch)sheet3.CreateDrawingPatriarch();

            // Create a shape group.
            HSSFShapeGroup group = patriarch.CreateGroup(
                    new HSSFClientAnchor(0, 0, 900, 200, (short)2, 2, (short)2, 2));

            // Create a couple of lines in the group.
            HSSFSimpleShape shape1 = group.CreateShape(new HSSFChildAnchor(3, 3, 500, 500));
            shape1.ShapeType = (HSSFSimpleShape.OBJECT_TYPE_LINE);
            ((HSSFChildAnchor)shape1.Anchor).SetAnchor((short)3, 3, 500, 500);
            HSSFSimpleShape shape2 = group.CreateShape(new HSSFChildAnchor((short)1, 200, 400, 600));
            shape2.ShapeType = (HSSFSimpleShape.OBJECT_TYPE_LINE);

        }
Exemplo n.º 36
0
 /// <summary>
 /// 插入图片
 /// </summary>
 /// <param name="wk"></param>
 /// <param name="tb"></param>
 /// <param name="imgBytes">图片的byte数组</param>
 /// <param name="anchor">图片插入的大小和位置</param>
 /// <param name="isResize">是否自动调节大小</param>
 public static void Picture(ISheet tb, byte[] imgBytes, HSSFClientAnchor anchor, bool isResize)
 {
     //byte[] bytes = File.ReadAllBytes("D://1.png");
     int pictureIdx = wb.AddPicture(imgBytes, PictureType.PNG);
     var patriarch = tb.CreateDrawingPatriarch();
     //var anchor = new HSSFClientAnchor(0, 0, 1023, 0, 0, 44, 10, 86);
     var pict = patriarch.CreatePicture(anchor, pictureIdx);
     if (isResize)
     {
         pict.Resize();
     }
 }
Exemplo n.º 37
0
        private static void PostilAdd(Tk5ListMetaData metaInfos, ImportError importError, IWorkbook workBook, ISheet sheet)
        {
            IDrawing part = sheet.CreateDrawingPatriarch();
            Dictionary<string, int> indexOfName = new Dictionary<string, int>();
            int i = 0;

            foreach (var info in metaInfos.Table.TableList)
            {
                indexOfName.Add(info.DisplayName, i);
                i++;
            }

            foreach (var err in importError)
            {
                IRow row = sheet.GetRow(err.IndexOfRow);
                IComment comment = null;
                ICell cell = row.GetCell(indexOfName[err.ColumnName]);
                ICreationHelper factory = workBook.GetCreationHelper();
                IClientAnchor anchor = null;
                anchor = factory.CreateClientAnchor();
                anchor.Col1 = cell.ColumnIndex + 2;
                anchor.Col2 = cell.ColumnIndex + 4;
                anchor.Row1 = row.RowNum;
                anchor.Row2 = row.RowNum + 3;
                comment = part.CreateCellComment(anchor);
                comment.Author = "mitu";
                comment.String = new HSSFRichTextString(err.ErrorMsg);
                cell.CellComment = comment;
            }
        }
Exemplo n.º 38
0
        private void handleResize(IWorkbook wb, ISheet sheet, IRow row)
        {
            IDrawing Drawing = sheet.CreateDrawingPatriarch();
            ICreationHelper CreateHelper = wb.GetCreationHelper();

            byte[] bytes = HSSFITestDataProvider.Instance.GetTestDataFileContent("logoKarmokar4.png");

            row.HeightInPoints = (/*setter*/GetImageSize(bytes).Y);

            int pictureIdx = wb.AddPicture(bytes, PictureType.PNG);

            //add a picture shape
            IClientAnchor anchor = CreateHelper.CreateClientAnchor();
            //set top-left corner of the picture,
            //subsequent call of Picture#resize() will operate relative to it
            anchor.Col1 = (/*setter*/0);
            anchor.Row1 = (/*setter*/0);

            IPicture pict = Drawing.CreatePicture(anchor, pictureIdx);

            //auto-size picture relative to its top-left corner
            pict.Resize();
        }