/// <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); } }
/*泛类型行不通 * 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; }
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); }
/// <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();//显示图片的原始尺寸 }
/// <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); } }
/// 向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; } }
//向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; } }
//导出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")); }
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!"; }
/// <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); } } }
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(); }
/// <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; }
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); }
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); }
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); }
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); } }
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); }
/// <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()); } }
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); } }
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(); } }
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; } }
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(); }
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); }
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(); }
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 }
/// <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("保存成功!"); } }
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(); } }
/// <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()); }
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); }
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 }
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); }
/// <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); } } } } } } }
/// <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(); } }
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; } }
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(); }