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); } }
/// <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); } }
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); }
/*泛类型行不通 * 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.) * } */ //重载 public ISheet pictureDataToSheet(ISheet sheet, byte[] pictureNPOI, 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(0, 0, 0, 0, startCol, startRow, endCol, endRow); //将图片文件读入workbook,用索引指向该文件 int pictureIdx = workbook.AddPicture(pictureNPOI, PictureType.PNG); //根据读入图片和anchor把图片插到相应的位置 XSSFPicture pict = (XSSFPicture)patriarch.CreatePicture(anchor, pictureIdx); return(sheet); }
public void Test44916() { HSSFWorkbook wb = new HSSFWorkbook(); NPOI.SS.UserModel.ISheet sheet = wb.CreateSheet(); // 1. Create drawing patriarch IDrawing patr = sheet.CreateDrawingPatriarch(); // 2. Try to re-get the patriarch IDrawing existingPatr; try { existingPatr = sheet.DrawingPatriarch; } catch (NullReferenceException) { throw new AssertionException("Identified bug 44916"); } // 3. Use patriarch HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 600, 245, (short)1, 1, (short)1, 2); anchor.AnchorType = (3); byte[] pictureData = HSSFTestDataSamples.GetTestDataFileContent("logoKarmokar4.png"); int idx1 = wb.AddPicture(pictureData, PictureType.PNG); patr.CreatePicture(anchor, idx1); // 4. Try to re-use patriarch later existingPatr = sheet.DrawingPatriarch; Assert.IsNotNull(existingPatr); }
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); }
/// <summary> /// 设置单元格值 /// </summary> /// <param name="cell">单元格</param> /// <param name="value">值</param> public static void SetValue(this ICell cell, object value) { if (cell == null) { return; } if (value == null) { cell.SetCellValue(string.Empty); return; } if (value.GetType().FullName.Equals("System.Byte[]")) { var pictureIndex = cell.Sheet.Workbook.AddPicture((byte[])value, PictureType.PNG); IClientAnchor anchor = cell.Sheet.Workbook.GetCreationHelper().CreateClientAnchor(); anchor.Col1 = cell.ColumnIndex; anchor.Col2 = cell.ColumnIndex + cell.GetSpan().ColumnSpan; anchor.Row1 = cell.RowIndex; anchor.Row2 = cell.RowIndex + cell.GetSpan().RowSpan; IDrawing drawing = cell.Sheet.CreateDrawingPatriarch(); IPicture picture = drawing.CreatePicture(anchor, pictureIndex); return; } TypeCode valueTypeCode = Type.GetTypeCode(value.GetType()); switch (valueTypeCode) { case TypeCode.String: cell.SetCellValue(Convert.ToString(value)); break; case TypeCode.DateTime: cell.SetCellValue(Convert.ToDateTime(value)); break; case TypeCode.Boolean: cell.SetCellValue(Convert.ToBoolean(value)); break; case TypeCode.Int16: case TypeCode.Int32: case TypeCode.Int64: case TypeCode.Byte: case TypeCode.Single: case TypeCode.Double: case TypeCode.Decimal: case TypeCode.UInt16: case TypeCode.UInt32: case TypeCode.UInt64: cell.SetCellValue(Convert.ToDouble(value)); break; default: cell.SetCellValue(string.Empty); break; } }
/// <summary> /// 在单元格中插入图片 /// </summary> private static void InsertImage(ICell cell, byte[] picBytes, PictureType pt) { var row1 = cell.RowIndex; var col1 = cell.ColumnIndex; var workbook = cell.Sheet.Workbook; var sheet = cell.Sheet; /* Calcualte the image size */ MemoryStream ms = new MemoryStream(picBytes); Image Img = Bitmap.FromStream(ms, true); double ImageOriginalWidth = Img.Width; double ImageOriginalHeight = Img.Height; double scale = cell.Sheet.GetColumnWidth(cell.ColumnIndex) / 36.5 / ImageOriginalWidth; cell.Row.Height = (short)(ImageOriginalHeight * scale * 15); //cell.Sheet.SetColumnWidth(cell.ColumnIndex, (int)(ImageOriginalWidth * scale * 36.5)); /* Add Picture to Workbook, Specify picture type as PNG and Get an Index */ int pictureIdx = workbook.AddPicture(picBytes, pt); //添加图片 /* Create the drawing container */ IDrawing drawing = sheet.CreateDrawingPatriarch(); /* Create an anchor point */ IClientAnchor anchor = drawing.CreateAnchor(0, 0, 0, 0, col1, row1, col1 + 1, row1 + 1); /* Invoke createPicture and pass the anchor point and ID */ IPicture picture = drawing.CreatePicture(anchor, pictureIdx); /* Call resize method, which resizes the image */ picture.Resize(1); picBytes = null; }
public static byte[] ExportByTemplateOption2(string templateFileName, ExcellExportOption option, List <SetExcelDto> setList, int insertPicRowIndex, byte[] picBytes, int insertTableRowIndex, DataTable table) { var workbook = OpenExcel(templateFileName); var sheet = workbook.GetSheetAt(0); FillSheet(sheet, table, insertTableRowIndex); SetupByOption(workbook, option); //指定单元格赋值 if (setList != null) { SetExcelValue(workbook, setList); } //插入图片 if (picBytes != null) { int pictureIndex = workbook.AddPicture(picBytes, PictureType.PNG); IDrawing patriarch = sheet.CreateDrawingPatriarch(); XSSFClientAnchor anchor = new XSSFClientAnchor(50, 50, 225, 225, 0, insertPicRowIndex, 1, insertPicRowIndex + 5); IPicture pict = patriarch.CreatePicture(anchor, pictureIndex); } byte[] data = null; using (var ms = new MemoryStream()) { workbook.Write(ms); ms.Flush(); data = ms.ToArray(); ms.Close(); ms.Dispose(); } workbook.Close(); return(data); }
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> /// 向excel添加图片 /// </summary> /// <param name="XlsFilePath"></param> /// <param name="pictureData"></param> /// <param name="sheetName"></param> public static void AddPicture(string XlsFilePath, byte[] pictureData, bool isNewSheet, string sheetName = "PicTure") { FileStream fs = File.OpenRead(XlsFilePath); //IWorkbook workbook = new XSSFWorkbook(fs); NPOI.XSSF.UserModel.XSSFWorkbook book = new NPOI.XSSF.UserModel.XSSFWorkbook(fs); // (NPOI.XSSF.UserModel.XSSFWorkbook)WorkbookFactory.Create(XlsFilePath); fs.Close(); int pictureIdx = book.AddPicture(pictureData, PictureType.JPEG); NPOI.SS.UserModel.ISheet sheet = null; if (!isNewSheet) { sheet = book.GetSheetAt(0); } else { sheet = book.CreateSheet(sheetName); } IDrawing patriarch = sheet.CreateDrawingPatriarch(); NPOI.XSSF.UserModel.XSSFClientAnchor anchor = new NPOI.XSSF.UserModel.XSSFClientAnchor(0, 0, 1023, 0, 0, 0, 1, 3); IPicture pict = patriarch.CreatePicture(anchor, pictureIdx); pict.Resize(); using (FileStream fileStream = File.Open(XlsFilePath, FileMode.OpenOrCreate, FileAccess.ReadWrite)) { book.Write(fileStream); fileStream.Close(); } }
/// <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 DrawGraphs(IWorkbook workBook, ISheet sheet, IClientAnchor anchor, Byte[] graphData, PictureType type = DefaultPictureType) { Int32 index = workBook.AddPicture(graphData, type); IDrawing patriarch = sheet.CreateDrawingPatriarch(); patriarch.CreatePicture(anchor, index); }
protected override void SetValue(ICell cell, object source) { NPOI.SS.Util.CellRangeAddress region = NPOIExcelUtil.GetRange(cell); ISheet sheet = cell.Sheet; IDrawing draw = sheet.DrawingPatriarch ?? sheet.CreateDrawingPatriarch(); IClientAnchor anchor = region != null? draw.CreateAnchor(20, 20, 0, 0, region.FirstColumn, region.FirstRow, region.LastColumn + 1, region.LastRow + 1) : draw.CreateAnchor(20, 20, 0, 0, cell.ColumnIndex, cell.RowIndex, cell.ColumnIndex + 1, cell.RowIndex + 1); draw.CreatePicture(anchor, sheet.Workbook.AddPicture(_func(source), PictureType.JPEG));//PNG、JPEG都没问题 }
/// <summary> /// create xls with table /// using NPOI /// </summary> /// <param name="svgDoc"></param> /// <param name="stream"></param> /// <param name="table"></param> public static void CreateExcelWithTableStreamBySvg(SvgDocument svgDoc, Stream stream, FileTable table) { using (stream) { IWorkbook workbook = new HSSFWorkbook(); ISheet sheet = workbook.CreateSheet("Sheet1"); IDrawing patriarch = sheet.CreateDrawingPatriarch(); HSSFClientAnchor anchor; IPicture pic; ICellStyle head_style = createHeadCellStyle(workbook); ICellStyle cell_style = createCellBorderStyle(workbook); IRow head_row = null; head_row = sheet.CreateRow(0); ICell blank = head_row.CreateCell(0); blank.CellStyle = head_style; for (int i = 0; i < table.series.Count; i++) { ICell head_cell = head_row.CreateCell(i + 1); head_cell.SetCellValue(table.series[i]); head_cell.CellStyle = head_style; } // create vertical yAxis int _ycount = 1; foreach (string key in table.xAxis.Keys) { IRow y_row = sheet.CreateRow(_ycount); ICell cell = y_row.CreateCell(0); cell.SetCellValue(table.xAxis[key]); cell.CellStyle = head_style; for (int i = 0; i < table.series.Count; i++) { ICell value_cell = y_row.CreateCell(i + 1); value_cell.SetCellValue(table.rows[i][key]); value_cell.CellStyle = cell_style; } _ycount++; } using (MemoryStream ms = new MemoryStream()) { using (System.Drawing.Bitmap image = svgDoc.Draw()) { image.Save(ms, ImageFormat.Bmp); ms.Seek(0, SeekOrigin.Begin); int index = workbook.AddPicture(ms.ToArray(), PictureType.JPEG); anchor = new HSSFClientAnchor(0, 0, 0, 0, table.series.Count + 2, 0, 100, 100); pic = patriarch.CreatePicture(anchor, index); pic.Resize(); } } workbook.Write(stream); } }
/// <summary> /// 在单元格中添加图片 /// </summary> /// <param name="col1"></param> /// <param name="row1"></param> /// <param name="col2"></param> /// <param name="row2"></param> /// <param name="dx1"></param> /// <param name="dy1"></param> /// <param name="dx2"></param> /// <param name="dy2"></param> /// <param name="picture"></param> /// <param name="sheetName"></param> public void InsertPictureInCell(int col1, int row1, int col2, int row2, int dx1, int dy1, int dx2, int dy2, byte[] picture, string sheetName) { int pictureIdx1 = this._workbook.AddPicture(picture, PictureType.PNG); ISheet sheet = this._workbook.GetSheet(sheetName); IDrawing patriarch = sheet.CreateDrawingPatriarch(); HSSFClientAnchor anchor = new HSSFClientAnchor(dx1, dy1, dx2, dy2, col1, row1, col2, row2); anchor.AnchorType = AnchorType.MoveDontResize; IPicture pict1 = patriarch.CreatePicture(anchor, pictureIdx1); }
public static ICell AddPicture(this ICell cell, byte[] bytes) { int pictureIdx = cell.Sheet.Workbook.AddPicture(bytes, PictureType.JPEG); IDrawing patriarch = cell.Sheet.CreateDrawingPatriarch(); // 插图片的位置 HSSFClientAnchor(dx1,dy1,dx2,dy2,col1,row1,col2,row2) 后面再作解释 if (cell is HSSFCell) { HSSFClientAnchor anchor = new HSSFClientAnchor(70, 10, 0, 0, cell.ColumnIndex, cell.RowIndex, cell.ColumnIndex + 1, cell.RowIndex + 1); //把图片插到相应的位置 HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx); } else { XSSFClientAnchor anchor = new XSSFClientAnchor(70, 10, 0, 0, cell.ColumnIndex, cell.RowIndex, cell.ColumnIndex + 1, cell.RowIndex + 1); //把图片插到相应的位置 XSSFPicture pict = (XSSFPicture)patriarch.CreatePicture(anchor, pictureIdx); } return(cell); }
public void Render(ICell cell) { NPOI.SS.Util.CellRangeAddress region = NPOIExcelUtil.GetRange(cell); ISheet sheet = cell.Sheet; IDrawing draw = sheet.DrawingPatriarch ?? sheet.CreateDrawingPatriarch(); IClientAnchor anchor = region != null? draw.CreateAnchor(20, 20, 0, 0, region.FirstColumn, region.FirstRow, region.LastColumn + 1, region.LastRow + 1) : PicArea != null? draw.CreateAnchor(20, 20, 0, 0, PicArea.ColIndex, PicArea.RowIndex, PicArea.ColIndex + PicArea.ColCount, PicArea.RowIndex + PicArea.RowCount) : draw.CreateAnchor(20, 20, 0, 0, cell.ColumnIndex, cell.RowIndex, cell.ColumnIndex + 1, cell.RowIndex + 1); draw.CreatePicture(anchor, sheet.Workbook.AddPicture(PicSource, PictureType.JPEG)); }
public void Test45829() { HSSFWorkbook wb = new HSSFWorkbook(); NPOI.SS.UserModel.ISheet sh1 = wb.CreateSheet(); IDrawing p1 = sh1.CreateDrawingPatriarch(); byte[] pictureData = HSSFTestDataSamples.GetTestDataFileContent("45829.png"); int idx1 = wb.AddPicture(pictureData, PictureType.PNG); IPicture pic = p1.CreatePicture(new HSSFClientAnchor(), idx1); pic.Resize(); }
private static void CreateRowItem(this ISheet target, IWorkbook workbook, DataTable dataSource) { IRow row = null; ICell cell = null; ICellStyle cellStyle = null; IDrawing drawing = null; IPicture picture = null; cellStyle = workbook.CreateCellStyle(); cellStyle.Alignment = HorizontalAlignment.CENTER; cellStyle.VerticalAlignment = VerticalAlignment.CENTER; for (int rowIndex = 0; rowIndex < dataSource.Rows.Count; rowIndex++) { row = target.CreateRow(rowIndex + 1); for (int columnIndex = 0; columnIndex < dataSource.Columns.Count; columnIndex++) { cell = row.CreateCell(columnIndex); if (dataSource.Columns[columnIndex].DataType == typeof(byte[])) { byte[] image = dataSource.Rows[rowIndex][columnIndex] as byte[]; if (image != null && image.Length > 0) { int pictureIndex = workbook.AddPicture(dataSource.Rows[rowIndex][columnIndex] as byte[], PictureType.JPEG); drawing = target.CreateDrawingPatriarch(); HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, columnIndex, rowIndex + 1, columnIndex, rowIndex + 1); picture = drawing.CreatePicture(anchor, pictureIndex); picture.Resize(); } } else { cell.SetCellValue(dataSource.Rows[rowIndex][columnIndex].ToString()); } cell.CellStyle = cellStyle; } } }
public void Render( jp.co.systembase.report.renderer.xlsx.component.Page page, jp.co.systembase.report.renderer.xlsx.component.Shape shape) { createImage(); int index; if ((bool)this.data) { index = page.Renderer.GetImageIndex(checkedImage); } else { index = page.Renderer.GetImageIndex(noCheckedImage); } if (index >= 0) { IDrawing p = page.Renderer.Sheet.CreateDrawingPatriarch(); p.CreatePicture(shape.GetXSSFClientAnchor(page.TopRow), index); } }
private static void InsertImage(XSSFWorkbook workbook, ISheet sheet, byte[] data, int row, int column) { try { int picInd = workbook.AddPicture(data, XSSFWorkbook.PICTURE_TYPE_PNG); IDrawing patriarch = sheet.CreateDrawingPatriarch(); XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, column, row, column + 1, row + 1) { AnchorType = AnchorType.MoveAndResize }; XSSFPicture picture = (XSSFPicture)patriarch.CreatePicture(anchor, picInd); //Reset the image to the original size. //picture.Resize(); //Note: Resize will reset client anchor you set. picture.LineStyle = LineStyle.DashDotGel; } catch { //ignore } }
/// <summary> /// 设置单元格值 /// </summary> /// <param name="cell">单元格</param> /// <param name="value">值</param> public static void SetValue(this ICell cell, object value) { if (null == cell) { return; } if (null == value) { cell.SetCellValue(string.Empty); return; } if (value is IRichTextString) { cell.SetCellValue((IRichTextString)value); } else if (value is Image) { int pictureIdx = cell.Sheet.Workbook.AddPicture(((Image)value).ToBuffer(), PictureType.PNG); IClientAnchor anchor = cell.Sheet.Workbook.GetCreationHelper().CreateClientAnchor(); anchor.Col1 = cell.ColumnIndex; anchor.Col2 = cell.ColumnIndex + cell.GetSpan().ColSpan; anchor.Row1 = cell.RowIndex; anchor.Row2 = cell.RowIndex + cell.GetSpan().RowSpan; IDrawing patriarch = cell.Sheet.CreateDrawingPatriarch(); IPicture pic = patriarch.CreatePicture(anchor, pictureIdx); } else { value.GetType() .Case(typeof(String)).Do(() => cell.SetCellValue(Convert.ToString(value))) .Case(typeof(DateTime)).Do(() => cell.SetCellValue(Convert.ToDateTime(value))) .Case(typeof(Boolean)).Do(() => cell.SetCellValue(Convert.ToBoolean(value))) .Case(typeof(Int16), typeof(Int32), typeof(Int64), typeof(Byte), typeof(Single), typeof(Double), typeof(Decimal), typeof(UInt16), typeof(UInt32), typeof(UInt64)).Do(() => cell.SetCellValue(Convert.ToDouble(value))); } }
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 = 2; //load the picture and get the picture index in the workbook XSSFPicture picture = (XSSFPicture)patriarch.CreatePicture(anchor, LoadImage("../../image/HumpbackWhale.jpg", workbook)); //Reset the image to the original size. //picture.Resize(); //Note: Resize will reset client anchor you set. picture.LineStyle = LineStyle.DashDotGel; FileStream sw = File.Create("test.xlsx"); workbook.Write(sw); sw.Close(); }
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(); }
/// <summary> /// 添加图片 /// </summary> /// <param name="sheet"></param> /// <param name="picInfo"></param> public static void AddPicture(this ISheet sheet, PictureInfo picInfo) { int pictureIdx = sheet.Workbook.AddPicture(picInfo.PictureData, PictureType.PNG); IClientAnchor anchor = sheet.Workbook.GetCreationHelper().CreateClientAnchor(); anchor.Col1 = picInfo.MinCol; anchor.Col2 = picInfo.MaxCol; anchor.Row1 = picInfo.MinRow; anchor.Row2 = picInfo.MaxRow; anchor.Dx1 = picInfo.PicturesStyle.AnchorDx1; anchor.Dx2 = picInfo.PicturesStyle.AnchorDx2; anchor.Dy1 = picInfo.PicturesStyle.AnchorDy1; anchor.Dy2 = picInfo.PicturesStyle.AnchorDy2; anchor.AnchorType = 2; IDrawing drawing = sheet.CreateDrawingPatriarch(); IPicture pic = drawing.CreatePicture(anchor, pictureIdx); if (sheet is HSSFSheet) { var shape = (HSSFShape)pic; shape.FillColor = picInfo.PicturesStyle.FillColor; shape.IsNoFill = picInfo.PicturesStyle.IsNoFill; shape.LineStyle = picInfo.PicturesStyle.LineStyle; shape.LineStyleColor = picInfo.PicturesStyle.LineStyleColor; shape.LineWidth = (int)picInfo.PicturesStyle.LineWidth; } else if (sheet is XSSFSheet) { var shape = (XSSFShape)pic; shape.FillColor = picInfo.PicturesStyle.FillColor; shape.IsNoFill = picInfo.PicturesStyle.IsNoFill; shape.LineStyle = picInfo.PicturesStyle.LineStyle; //shape.LineStyleColor = picInfo.PicturesStyle.LineStyleColor; shape.LineWidth = picInfo.PicturesStyle.LineWidth; } }
/// <summary> /// 添加图片 /// </summary> /// <param name="sheet">工作表</param> /// <param name="pictureInfo">图片信息</param> public static void AddPicture(this ISheet sheet, PictureInfo pictureInfo) { int pictureIndex = sheet.Workbook.AddPicture(pictureInfo.Data, PictureType.PNG); IClientAnchor anchor = sheet.Workbook.GetCreationHelper().CreateClientAnchor(); anchor.Col1 = pictureInfo.FirstColumn; anchor.Col2 = pictureInfo.LastColumn; anchor.Row1 = pictureInfo.FirstRow; anchor.Row2 = pictureInfo.LastRow; anchor.Dx1 = pictureInfo.Style.AnchorDx1; anchor.Dx2 = pictureInfo.Style.AnchorDx2; anchor.Dy1 = pictureInfo.Style.AnchorDy1; anchor.Dy2 = pictureInfo.Style.AnchorDy2; anchor.AnchorType = AnchorType.MoveDontResize; IDrawing drawing = sheet.CreateDrawingPatriarch(); IPicture picture = drawing.CreatePicture(anchor, pictureIndex); if (sheet is HSSFSheet) { var shape = (HSSFShape)picture; shape.FillColor = pictureInfo.Style.FillColor; shape.IsNoFill = pictureInfo.Style.IsNoFill; //shape.LineStyle = pictureInfo.Style.LineStyle; shape.LineStyleColor = pictureInfo.Style.LineStyleColor; shape.LineWidth = (int)pictureInfo.Style.LineWidth; } else if (sheet is XSSFSheet) { var shape = (XSSFShape)picture; shape.FillColor = pictureInfo.Style.FillColor; shape.IsNoFill = pictureInfo.Style.IsNoFill; //shape.LineStyle = pictureInfo.Style.LineStyle; //shape.LineStyleColor = pictureInfo.Style.LineStyleColor; shape.LineWidth = (int)pictureInfo.Style.LineWidth; } }
public void BaseTestResize(IClientAnchor referenceAnchor) { IWorkbook wb = _testDataProvider.CreateWorkbook(); ISheet sh1 = wb.CreateSheet(); IDrawing p1 = sh1.CreateDrawingPatriarch(); ICreationHelper factory = wb.GetCreationHelper(); byte[] pictureData = _testDataProvider.GetTestDataFileContent("logoKarmokar4.png"); int idx1 = wb.AddPicture(pictureData, PictureType.PNG); IPicture picture = p1.CreatePicture(factory.CreateClientAnchor(), idx1); picture.Resize(); IClientAnchor anchor1 = picture.GetPreferredSize(); //assert against what would BiffViewer print if we insert the image in xls and dump the file Assert.AreEqual(referenceAnchor.Col1, anchor1.Col1); Assert.AreEqual(referenceAnchor.Row1, anchor1.Row1); Assert.AreEqual(referenceAnchor.Col2, anchor1.Col2); Assert.AreEqual(referenceAnchor.Row2, anchor1.Row2); Assert.AreEqual(referenceAnchor.Dx1, anchor1.Dx1); Assert.AreEqual(referenceAnchor.Dy1, anchor1.Dy1); Assert.AreEqual(referenceAnchor.Dx2, anchor1.Dx2); Assert.AreEqual(referenceAnchor.Dy2, anchor1.Dy2); }
/// <summary> /// 插入图片 /// </summary> /// <param name="inputSheet"></param> /// <param name="lstInputRequest"></param> /// <returns></returns> public static bool TryInsertPictures(ISheet inputSheet, List <PictureInsertRequest> lstInputRequest) { try { //获取/创建 IDrawing patriarch = inputSheet.DrawingPatriarch; if (null == patriarch) { patriarch = inputSheet.CreateDrawingPatriarch(); } foreach (var oneRequest in lstInputRequest) { //第一步:将图片读入二进制数组 MemoryStream mStream = new MemoryStream(); oneRequest.UseImage.Save(mStream, System.Drawing.Imaging.ImageFormat.Png); byte[] bytePic = mStream.GetBuffer(); //第二步:图片加载进workbook int pictureIdx = inputSheet.Workbook.AddPicture(bytePic, PictureType.PNG); //第三步:设置锚点 IClientAnchor anchor = patriarch.CreateAnchor(0, 0, 0, 0, oneRequest.StartColumn, oneRequest.StartRow, oneRequest.EndColumn, oneRequest.EndRow); //第四步:创建图片 IPicture pict = patriarch.CreatePicture(anchor, pictureIdx); } return(true); } catch (Exception) { return(false); } }
public void TestAddPictures() { IWorkbook wb = new HSSFWorkbook(); ISheet sh = wb.CreateSheet("Pictures"); IDrawing dr = sh.CreateDrawingPatriarch(); Assert.AreEqual(0, ((HSSFPatriarch)dr).Children.Count); IClientAnchor anchor = wb.GetCreationHelper().CreateClientAnchor(); //register a picture byte[] data1 = new byte[] { 1, 2, 3 }; int idx1 = wb.AddPicture(data1, PictureType.JPEG); Assert.AreEqual(1, idx1); IPicture p1 = dr.CreatePicture(anchor, idx1); Assert.IsTrue(Arrays.Equals(data1, ((HSSFPicture)p1).PictureData.Data)); // register another one byte[] data2 = new byte[] { 4, 5, 6 }; int idx2 = wb.AddPicture(data2, PictureType.JPEG); Assert.AreEqual(2, idx2); IPicture p2 = dr.CreatePicture(anchor, idx2); Assert.AreEqual(2, ((HSSFPatriarch)dr).Children.Count); Assert.IsTrue(Arrays.Equals(data2, ((HSSFPicture)p2).PictureData.Data)); // confirm that HSSFPatriarch.Children returns two picture shapes Assert.IsTrue(Arrays.Equals(data1, ((HSSFPicture)((HSSFPatriarch)dr).Children[(0)]).PictureData.Data)); Assert.IsTrue(Arrays.Equals(data2, ((HSSFPicture)((HSSFPatriarch)dr).Children[(1)]).PictureData.Data)); // Write, read back and verify that our pictures are there wb = HSSFTestDataSamples.WriteOutAndReadBack((HSSFWorkbook)wb); IList lst2 = wb.GetAllPictures(); Assert.AreEqual(2, lst2.Count); Assert.IsTrue(Arrays.Equals(data1, (lst2[(0)] as HSSFPictureData).Data)); Assert.IsTrue(Arrays.Equals(data2, (lst2[(1)] as HSSFPictureData).Data)); // confirm that the pictures are in the Sheet's Drawing sh = wb.GetSheet("Pictures"); dr = sh.CreateDrawingPatriarch(); Assert.AreEqual(2, ((HSSFPatriarch)dr).Children.Count); Assert.IsTrue(Arrays.Equals(data1, ((HSSFPicture)((HSSFPatriarch)dr).Children[(0)]).PictureData.Data)); Assert.IsTrue(Arrays.Equals(data2, ((HSSFPicture)((HSSFPatriarch)dr).Children[(1)]).PictureData.Data)); // add a third picture byte[] data3 = new byte[] { 7, 8, 9 }; // picture index must increment across Write-read int idx3 = wb.AddPicture(data3, PictureType.JPEG); Assert.AreEqual(3, idx3); IPicture p3 = dr.CreatePicture(anchor, idx3); Assert.IsTrue(Arrays.Equals(data3, ((HSSFPicture)p3).PictureData.Data)); Assert.AreEqual(3, ((HSSFPatriarch)dr).Children.Count); Assert.IsTrue(Arrays.Equals(data1, ((HSSFPicture)((HSSFPatriarch)dr).Children[(0)]).PictureData.Data)); Assert.IsTrue(Arrays.Equals(data2, ((HSSFPicture)((HSSFPatriarch)dr).Children[(1)]).PictureData.Data)); Assert.IsTrue(Arrays.Equals(data3, ((HSSFPicture)((HSSFPatriarch)dr).Children[(2)]).PictureData.Data)); // write and read again wb = HSSFTestDataSamples.WriteOutAndReadBack((HSSFWorkbook)wb); IList lst3 = wb.GetAllPictures(); // all three should be there Assert.AreEqual(3, lst3.Count); Assert.IsTrue(Arrays.Equals(data1, (lst3[(0)] as HSSFPictureData).Data)); Assert.IsTrue(Arrays.Equals(data2, (lst3[(1)] as HSSFPictureData).Data)); Assert.IsTrue(Arrays.Equals(data3, (lst3[(2)] as HSSFPictureData).Data)); sh = wb.GetSheet("Pictures"); dr = sh.CreateDrawingPatriarch(); Assert.AreEqual(3, ((HSSFPatriarch)dr).Children.Count); // forth picture byte[] data4 = new byte[] { 10, 11, 12 }; int idx4 = wb.AddPicture(data4, PictureType.JPEG); Assert.AreEqual(4, idx4); dr.CreatePicture(anchor, idx4); Assert.AreEqual(4, ((HSSFPatriarch)dr).Children.Count); Assert.IsTrue(Arrays.Equals(data1, ((HSSFPicture)((HSSFPatriarch)dr).Children[(0)]).PictureData.Data)); Assert.IsTrue(Arrays.Equals(data2, ((HSSFPicture)((HSSFPatriarch)dr).Children[(1)]).PictureData.Data)); Assert.IsTrue(Arrays.Equals(data3, ((HSSFPicture)((HSSFPatriarch)dr).Children[(2)]).PictureData.Data)); Assert.IsTrue(Arrays.Equals(data4, ((HSSFPicture)((HSSFPatriarch)dr).Children[(3)]).PictureData.Data)); wb = HSSFTestDataSamples.WriteOutAndReadBack((HSSFWorkbook)wb); IList lst4 = wb.GetAllPictures(); Assert.AreEqual(4, lst4.Count); Assert.IsTrue(Arrays.Equals(data1, (lst4[(0)] as HSSFPictureData).Data)); Assert.IsTrue(Arrays.Equals(data2, (lst4[(1)] as HSSFPictureData).Data)); Assert.IsTrue(Arrays.Equals(data3, (lst4[(2)] as HSSFPictureData).Data)); Assert.IsTrue(Arrays.Equals(data4, (lst4[(3)] as HSSFPictureData).Data)); sh = wb.GetSheet("Pictures"); dr = sh.CreateDrawingPatriarch(); Assert.AreEqual(4, ((HSSFPatriarch)dr).Children.Count); Assert.IsTrue(Arrays.Equals(data1, ((HSSFPicture)((HSSFPatriarch)dr).Children[(0)]).PictureData.Data)); Assert.IsTrue(Arrays.Equals(data2, ((HSSFPicture)((HSSFPatriarch)dr).Children[(1)]).PictureData.Data)); Assert.IsTrue(Arrays.Equals(data3, ((HSSFPicture)((HSSFPatriarch)dr).Children[(2)]).PictureData.Data)); Assert.IsTrue(Arrays.Equals(data4, ((HSSFPicture)((HSSFPatriarch)dr).Children[(3)]).PictureData.Data)); }