/// <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; } }
private CT_TwoCellAnchor CreateTwoCellAnchor(IClientAnchor anchor) { CT_TwoCellAnchor ctTwoCellAnchor = this.drawing.AddNewTwoCellAnchor(); XSSFClientAnchor xssfClientAnchor = (XSSFClientAnchor)anchor; ctTwoCellAnchor.from = xssfClientAnchor.GetFrom(); ctTwoCellAnchor.to = xssfClientAnchor.GetTo(); ctTwoCellAnchor.AddNewClientData(); xssfClientAnchor.SetTo(ctTwoCellAnchor.to); xssfClientAnchor.SetFrom(ctTwoCellAnchor.from); NPOI.OpenXmlFormats.Dml.Spreadsheet.ST_EditAs stEditAs; switch (anchor.AnchorType) { case 0: stEditAs = NPOI.OpenXmlFormats.Dml.Spreadsheet.ST_EditAs.twoCell; break; case 2: stEditAs = NPOI.OpenXmlFormats.Dml.Spreadsheet.ST_EditAs.oneCell; break; case 3: stEditAs = NPOI.OpenXmlFormats.Dml.Spreadsheet.ST_EditAs.absolute; break; default: stEditAs = NPOI.OpenXmlFormats.Dml.Spreadsheet.ST_EditAs.oneCell; break; } ctTwoCellAnchor.editAs = stEditAs; ctTwoCellAnchor.editAsSpecified = true; return(ctTwoCellAnchor); }
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(); }
////重载 //public ISheet pictureDataToSheet(ISheet sheet, HSSFPicture pictureNPOI, int startRow, int startCol, int endRow, int endCol) //{ // workbook.AddPicture(pictureNPOI.) //} /// <summary> /// 画矩形,2.0似乎还未实现,xslx格式未实现 /// </summary> /// <param name="sheet"></param> /// <param name="content_string"></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 ISheet shapJuxingToSheet(ISheet sheet, string content_string, int dx1, int dy1, int dx2, int dy2, int startRow, int startCol, int endRow, int endCol) { if (sheet == null) { sheet = this.workbook.CreateSheet(); } //创建DrawingPatriarch,存放的容器 IDrawing patriarch = sheet.CreateDrawingPatriarch(); //画图形的位置点 IClientAnchor anchor = patriarch.CreateAnchor(dx1, dy1, dx2, dy2, startCol, startRow, endCol, endRow); //将图片文件读入workbook,用索引指向该文件 //int pictureIdx = workbook.AddPicture(pictureNPOI, PictureType.TIFF); //IShape recl; //根据读入图片和anchor把图片插到相应的位置 //IPicture pict = patriarch.CreatePicture(anchor, pictureIdx); //原始大小显示,重载可指定缩放 //pict.Resize(0.9); return(sheet); }
/** * Create and Initialize a CT_TwoCellAnchor that anchors a shape against top-left and bottom-right cells. * * @return a new CT_TwoCellAnchor */ private CT_TwoCellAnchor CreateTwoCellAnchor(IClientAnchor anchor) { CT_TwoCellAnchor ctAnchor = drawing.AddNewTwoCellAnchor(); XSSFClientAnchor xssfanchor = (XSSFClientAnchor)anchor; ctAnchor.from = (xssfanchor.GetFrom()); ctAnchor.to = (xssfanchor.GetTo()); ctAnchor.AddNewClientData(); xssfanchor.SetTo(ctAnchor.to); xssfanchor.SetFrom(ctAnchor.from); ST_EditAs aditAs; switch (anchor.AnchorType) { case (int)AnchorType.DONT_MOVE_AND_RESIZE: aditAs = ST_EditAs.absolute; break; case (int)AnchorType.MOVE_AND_RESIZE: aditAs = ST_EditAs.twoCell; break; case (int)AnchorType.MOVE_DONT_RESIZE: aditAs = ST_EditAs.oneCell; break; default: aditAs = ST_EditAs.oneCell; break; } ctAnchor.editAs = aditAs; ctAnchor.editAsSpecified = true; return(ctAnchor); }
/// <summary> /// XSSFSheet清除指定区域的图片 /// </summary> /// <param name="sheet"></param> /// <param name="minRow"></param> /// <param name="maxRow"></param> /// <param name="minCol"></param> /// <param name="maxCol"></param> /// <param name="onlyInternal"></param> private static void RemovePictures(XSSFSheet sheet, int?minRow, int?maxRow, int?minCol, int?maxCol, bool onlyInternal) { List <POIXMLDocumentPart> documentPartList = sheet.GetRelations(); foreach (POIXMLDocumentPart documentPart in documentPartList) { if (documentPart is XSSFDrawing) { var drawing = (XSSFDrawing)documentPart; List <XSSFShape> shapeList = drawing.GetShapes(); for (int i = 0; i < shapeList.Count; i++) { XSSFShape shape = shapeList[i]; if (shape is XSSFPicture) { var picture = (XSSFPicture)shape; IClientAnchor anchor = picture.GetPreferredSize(); if (IsInternalOrIntersect(minRow, maxRow, minCol, maxCol, anchor.Row1, anchor.Row2, anchor.Col1, anchor.Col2, onlyInternal)) { throw new NotImplementedException("XSSFSheet未实现ClearPictures()方法!"); } } } } } }
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 void AddChart(IDrawing drawing, ISheet sheet, IClientAnchor anchor) { var chart = drawing.CreateChart(anchor); var legend = chart.GetOrCreateLegend(); legend.Position = LegendPosition.Bottom; var data = chart.ChartDataFactory.CreateLineChartData <string, double>(); var bottomAxis = chart.ChartAxisFactory.CreateCategoryAxis(AxisPosition.Bottom); var leftAxis = chart.ChartAxisFactory.CreateValueAxis(AxisPosition.Left); leftAxis.Crosses = AxisCrosses.AutoZero; var row = sheet.CreateRow(0); for (int i = 0; i < _errorList.Count; i++) { var cell = row.CreateCell(i); cell.SetCellValue(_errorList[i]); } var xAxis = DataSources.FromStringCellRange(sheet, new CellRangeAddress(0, 0, 0, _errorList.Count - 1)); FillChartDataCells(sheet, 1, _dichotomyIterations, xAxis, data); FillChartDataCells(sheet, 2, _goldenRatioIterations, xAxis, data); FillChartDataCells(sheet, 3, _fibonacciIterations, xAxis, data); FillChartDataCells(sheet, 4, _parabolaIterations, xAxis, data); FillChartDataCells(sheet, 5, _brentIterations, xAxis, data); chart.Plot(data, bottomAxis, leftAxis); }
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 void BaseTestResize(IPicture input, IPicture Compare, double scaleX, double scaleY) { input.Resize(scaleX, scaleY); IClientAnchor inpCA = input.ClientAnchor; IClientAnchor cmpCA = Compare.ClientAnchor; Size inpDim = ImageUtils.GetDimensionFromAnchor(input); Size cmpDim = ImageUtils.GetDimensionFromAnchor(Compare); double emuPX = Units.EMU_PER_PIXEL; Assert.AreEqual(inpDim.Height, cmpDim.Height, emuPX * 6, "the image height differs"); Assert.AreEqual(inpDim.Width, cmpDim.Width, emuPX * 6, "the image width differs"); Assert.AreEqual(inpCA.Col1, cmpCA.Col1, "the starting column differs"); Assert.AreEqual(inpCA.Dx1, cmpCA.Dx1, 1, "the column x-offset differs"); Assert.AreEqual(inpCA.Dy1, cmpCA.Dy1, 1, "the column y-offset differs"); Assert.AreEqual(inpCA.Col2, cmpCA.Col2, "the ending columns differs"); // can't compare row heights because of variable test heights input.Resize(); inpDim = ImageUtils.GetDimensionFromAnchor(input); Size imgDim = input.GetImageDimension(); Assert.AreEqual(imgDim.Height, inpDim.Height / emuPX, 1, "the image height differs"); Assert.AreEqual(imgDim.Width, inpDim.Width / emuPX, 1, "the image width differs"); }
private static void CreateChart(ISheet sheet, IDrawing drawing, IClientAnchor anchor, string serieTitle, int startDataRow, int endDataRow, int columnIndex) { XSSFChart chart = (XSSFChart)drawing.CreateChart(anchor); IBarChartData <string, double> barChartData = chart.ChartDataFactory.CreateBarChartData <string, double>(); IChartLegend legend = chart.GetOrCreateLegend(); legend.Position = LegendPosition.Bottom; IChartAxis bottomAxis = chart.ChartAxisFactory.CreateCategoryAxis(AxisPosition.Bottom); bottomAxis.MajorTickMark = AxisTickMark.None; IValueAxis leftAxis = chart.ChartAxisFactory.CreateValueAxis(AxisPosition.Left); leftAxis.Crosses = AxisCrosses.AutoZero; leftAxis.SetCrossBetween(AxisCrossBetween.Between); IChartDataSource <string> categoryAxis = DataSources.FromStringCellRange(sheet, new CellRangeAddress(startDataRow, endDataRow, 0, 0)); IChartDataSource <double> valueAxis = DataSources.FromNumericCellRange(sheet, new CellRangeAddress(startDataRow, endDataRow, columnIndex, columnIndex)); var serie = barChartData.AddSeries(categoryAxis, valueAxis); serie.SetTitle(serieTitle); chart.Plot(barChartData, bottomAxis, leftAxis); }
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); } }
static void CreateChart(IDrawing drawing, ISheet sheet, IClientAnchor anchor, string serie1, string serie2) { IChart chart = drawing.CreateChart(anchor); IChartLegend legend = chart.GetOrCreateLegend(); legend.Position = LegendPosition.TopRight; ILineChartData <double, double> data = chart.ChartDataFactory.CreateLineChartData <double, double>(); // Use a category axis for the bottom axis. IChartAxis bottomAxis = chart.GetChartAxisFactory().CreateCategoryAxis(AxisPosition.Bottom); IValueAxis leftAxis = chart.GetChartAxisFactory().CreateValueAxis(AxisPosition.Left); leftAxis.SetCrosses(AxisCrosses.AutoZero); IChartDataSource <double> xs = DataSources.FromNumericCellRange(sheet, new CellRangeAddress(0, 0, 0, NUM_OF_COLUMNS - 1)); IChartDataSource <double> ys1 = DataSources.FromNumericCellRange(sheet, new CellRangeAddress(1, 1, 0, NUM_OF_COLUMNS - 1)); IChartDataSource <double> ys2 = DataSources.FromNumericCellRange(sheet, new CellRangeAddress(2, 2, 0, NUM_OF_COLUMNS - 1)); var s1 = data.AddSeries(xs, ys1); s1.SetTitle(serie1); var s2 = data.AddSeries(xs, ys2); s2.SetTitle(serie2); chart.Plot(data, bottomAxis, leftAxis); }
/** * Create and Initialize a CT_TwoCellAnchor that anchors a shape against top-left and bottom-right cells. * * @return a new CT_TwoCellAnchor */ private CT_TwoCellAnchor CreateTwoCellAnchor(IClientAnchor anchor) { CT_TwoCellAnchor ctAnchor = drawing.AddNewTwoCellAnchor(); XSSFClientAnchor xssfanchor = (XSSFClientAnchor)anchor; ctAnchor.from = (xssfanchor.From); ctAnchor.to = (xssfanchor.To); ctAnchor.AddNewClientData(); xssfanchor.To = ctAnchor.to; xssfanchor.From = ctAnchor.from; ST_EditAs aditAs; switch (anchor.AnchorType) { case AnchorType.DontMoveAndResize: aditAs = ST_EditAs.absolute; break; case AnchorType.MoveAndResize: aditAs = ST_EditAs.twoCell; break; case AnchorType.MoveDontResize: aditAs = ST_EditAs.oneCell; break; default: aditAs = ST_EditAs.oneCell; break; } ctAnchor.editAs = aditAs; ctAnchor.editAsSpecified = true; return(ctAnchor); }
static void CreateChart(IDrawing drawing, ISheet sheet, IClientAnchor anchor, string serie1, string serie2, bool enableMajorGridline = false) { XSSFChart chart = (XSSFChart)drawing.CreateChart(anchor); chart.SetTitle("Test 1"); IChartLegend legend = chart.GetOrCreateLegend(); legend.Position = LegendPosition.TopRight; ILineChartData <double, double> data = chart.ChartDataFactory.CreateLineChartData <double, double>(); // Use a category axis for the bottom axis. IChartAxis bottomAxis = chart.ChartAxisFactory.CreateCategoryAxis(AxisPosition.Bottom); IValueAxis leftAxis = chart.ChartAxisFactory.CreateValueAxis(AxisPosition.Left); leftAxis.Crosses = AxisCrosses.AutoZero; IChartDataSource <double> xs = DataSources.FromNumericCellRange(sheet, new CellRangeAddress(0, 0, 0, NUM_OF_COLUMNS - 1)); IChartDataSource <double> ys1 = DataSources.FromNumericCellRange(sheet, new CellRangeAddress(1, 1, 0, NUM_OF_COLUMNS - 1)); IChartDataSource <double> ys2 = DataSources.FromNumericCellRange(sheet, new CellRangeAddress(2, 2, 0, NUM_OF_COLUMNS - 1)); var s1 = data.AddSeries(xs, ys1); s1.SetTitle(serie1); var s2 = data.AddSeries(xs, ys2); s2.SetTitle(serie2); chart.Plot(data, bottomAxis, leftAxis); //add major gridline, available since NPOI 2.5.5 var plotArea = chart.GetCTChart().plotArea; plotArea.catAx[0].AddNewMajorGridlines(); plotArea.valAx[0].AddNewMajorGridlines(); }
public override async Task <IWorkbook> DoExport(int projectId) { var workbook = await base.DoExport(projectId); var sheet = workbook.GetSheetAt(0); //获取最后一条报工记录 var lastReport = await ProcessTaskReportManager.GetAll().Where(o => o.ProcessTask.Part.ProjectId == projectId).LastOrDefaultAsync(); if (lastReport != null && lastReport.Files.Count > 0) { var maxRowNum = sheet.LastRowNum; sheet.CreateRow(maxRowNum + 10); var file = lastReport.Files[0]; var patriarch = sheet.CreateDrawingPatriarch(); sheet.AddMergedRegion(new CellRangeAddress(maxRowNum + 1, maxRowNum + 10, 0, 3)); if (!string.IsNullOrEmpty(file.FilePath)) { try { byte[] bytes = System.IO.File.ReadAllBytes(Common.PathHelper.VirtualPathToAbsolutePath(file.FilePath)); int pictureIdx = workbook.AddPicture(bytes, PictureType.JPEG); // 插图片的位置 HSSFClientAnchor(dx1,dy1,dx2,dy2,col1,row1,col2,row2) 后面再作解释 IClientAnchor anchor = patriarch.CreateAnchor(70, 10, 0, 0, 0, maxRowNum + 1, 3, maxRowNum + 10); //把图片插到相应的位置 patriarch.CreatePicture(anchor, pictureIdx); } catch (Exception ex) { } } } return(workbook); }
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(); }
public void TestFormulaCache() { IWorkbook wb = new XSSFWorkbook(); ISheet sheet = new SheetBuilder(wb, plotData).Build(); IDrawing Drawing = sheet.CreateDrawingPatriarch(); IClientAnchor anchor = Drawing.CreateAnchor(0, 0, 0, 0, 1, 1, 10, 30); IChart chart = Drawing.CreateChart(anchor); IChartAxis bottomAxis = chart.ChartAxisFactory.CreateValueAxis(AxisPosition.Bottom); IChartAxis leftAxis = chart.ChartAxisFactory.CreateValueAxis(AxisPosition.Left); IScatterChartData scatterChartData = chart.ChartDataFactory.CreateScatterChartData(); DataMarker xMarker = new DataMarker(sheet, CellRangeAddress.ValueOf("A1:E1")); DataMarker yMarker = new DataMarker(sheet, CellRangeAddress.ValueOf("A2:E2")); IScatterChartSerie serie = scatterChartData.AddSerie(xMarker, yMarker); chart.Plot(scatterChartData, bottomAxis, leftAxis); XSSFScatterChartData.Serie xssfScatterSerie = (XSSFScatterChartData.Serie)serie; XSSFNumberCache yCache = xssfScatterSerie.LastCalculatedYCache; Assert.AreEqual(5, yCache.PointCount); Assert.AreEqual(4.0, yCache.GetValueAt(3), 0.00001); Assert.AreEqual(16.0, yCache.GetValueAt(5), 0.00001); }
static void CreateChart(IDrawing drawing, ISheet sheet, IClientAnchor anchor, string serie1, string serie2) { IChart chart = drawing.CreateChart(anchor); IChartLegend legend = chart.GetOrCreateLegend(); legend.Position = LegendPosition.TopRight; ILineChartData<double, double> data = chart.ChartDataFactory.CreateLineChartData<double, double>(); // Use a category axis for the bottom axis. IChartAxis bottomAxis = chart.ChartAxisFactory.CreateCategoryAxis(AxisPosition.Bottom); IValueAxis leftAxis = chart.ChartAxisFactory.CreateValueAxis(AxisPosition.Left); leftAxis.Crosses = AxisCrosses.AutoZero; IChartDataSource<double> xs = DataSources.FromNumericCellRange(sheet, new CellRangeAddress(0, 0, 0, NUM_OF_COLUMNS - 1)); IChartDataSource<double> ys1 = DataSources.FromNumericCellRange(sheet, new CellRangeAddress(1, 1, 0, NUM_OF_COLUMNS - 1)); IChartDataSource<double> ys2 = DataSources.FromNumericCellRange(sheet, new CellRangeAddress(2, 2, 0, NUM_OF_COLUMNS - 1)); var s1 = data.AddSeries(xs, ys1); s1.SetTitle(serie1); var s2 = data.AddSeries(xs, ys2); s2.SetTitle(serie2); chart.Plot(data, bottomAxis, leftAxis); }
/// <summary> /// 删除指定区域的图片 /// </summary> /// <param name="sheet">工作表</param> /// <param name="firstRow">起始行索引</param> /// <param name="lastRow">结束行索引</param> /// <param name="firstColumn">起始列索引</param> /// <param name="lastColumn">结束列索引</param> /// <param name="onlyInternal">是否内部区域</param> private static void RemovePictures(XSSFSheet sheet, int?firstRow, int?lastRow, int?firstColumn, int?lastColumn, bool onlyInternal) { throw new NotImplementedException($"XSSFSheet 未实现RemovePictures()方法!"); var documentPartList = sheet.GetRelations(); foreach (var documentPart in documentPartList) { if (documentPart is XSSFDrawing drawing) { List <XSSFShape> shapes = drawing.GetShapes(); foreach (var shape in shapes) { if (shape is XSSFPicture picture) { IClientAnchor anchor = picture.GetPreferredSize(); if (IsInternalOrIntersect(firstRow, lastRow, firstColumn, lastColumn, anchor.Row1, anchor.Row2, anchor.Col1, anchor.Row2, onlyInternal)) { } } } } } }
/** * Creates a comment. * @param anchor the client anchor describes how this comment is attached * to the sheet. * @return the newly Created comment. */ public IComment CreateCellComment(IClientAnchor anchor) { XSSFClientAnchor ca = (XSSFClientAnchor)anchor; XSSFSheet sheet = (XSSFSheet)GetParent(); //create comments and vmlDrawing parts if they don't exist CommentsTable comments = sheet.GetCommentsTable(true); XSSFVMLDrawing vml = sheet.GetVMLDrawing(true); NPOI.OpenXmlFormats.Vml.CT_Shape vmlShape = vml.newCommentShape(); if (ca.IsSet()) { // convert offsets from emus to pixels since we get a DrawingML-anchor // but create a VML Drawing int dx1Pixels = ca.Dx1 / Units.EMU_PER_PIXEL; int dy1Pixels = ca.Dy1 / Units.EMU_PER_PIXEL; int dx2Pixels = ca.Dx2 / Units.EMU_PER_PIXEL; int dy2Pixels = ca.Dy2 / Units.EMU_PER_PIXEL; String position = ca.Col1 + ", " + dx1Pixels + ", " + ca.Row1 + ", " + dy1Pixels + ", " + ca.Col2 + ", " + dx2Pixels + ", " + ca.Row2 + ", " + dy2Pixels; vmlShape.GetClientDataArray(0).SetAnchorArray(0, position); } String ref1 = new CellReference(ca.Row1, ca.Col1).FormatAsString(); if (comments.FindCellComment(ref1) != null) { throw new ArgumentException("Multiple cell comments in one cell are not allowed, cell: " + ref1); } return(new XSSFComment(comments, comments.NewComment(ref1), vmlShape)); }
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 TestOneSeriePlot() { IWorkbook wb = new XSSFWorkbook(); ISheet sheet = new SheetBuilder(wb, plotData).Build(); IDrawing Drawing = sheet.CreateDrawingPatriarch(); IClientAnchor anchor = Drawing.CreateAnchor(0, 0, 0, 0, 1, 1, 10, 30); IChart chart = Drawing.CreateChart(anchor); IChartAxis bottomAxis = chart.ChartAxisFactory.CreateValueAxis(AxisPosition.Bottom); IChartAxis leftAxis = chart.ChartAxisFactory.CreateValueAxis(AxisPosition.Left); IScatterChartData <string, double> scatterChartData = chart.ChartDataFactory.CreateScatterChartData <string, double>(); IChartDataSource <String> xs = DataSources.FromStringCellRange(sheet, CellRangeAddress.ValueOf("A1:J1")); IChartDataSource <double> ys = DataSources.FromNumericCellRange(sheet, CellRangeAddress.ValueOf("A2:J2")); IScatterChartSeries <string, double> series = scatterChartData.AddSeries(xs, ys); Assert.IsNotNull(series); Assert.AreEqual(1, scatterChartData.GetSeries().Count); Assert.IsTrue(scatterChartData.GetSeries().Contains(series)); chart.Plot(scatterChartData, bottomAxis, leftAxis); }
/// <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; }
/// <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; }
/*泛类型行不通 * 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; }
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); }
private IComment insertComment(IDrawing Drawing, ICell cell, String message) { ICreationHelper factory = cell.Sheet.Workbook.GetCreationHelper(); IClientAnchor anchor = factory.CreateClientAnchor(); anchor.Col1 = (/*setter*/ cell.ColumnIndex); anchor.Col2 = (/*setter*/ cell.ColumnIndex + 1); anchor.Row1 = (/*setter*/ cell.RowIndex); anchor.Row2 = (/*setter*/ cell.RowIndex + 1); anchor.Dx1 = (/*setter*/ 100); anchor.Dx2 = (/*setter*/ 100); anchor.Dy1 = (/*setter*/ 100); anchor.Dy2 = (/*setter*/ 100); IComment comment = Drawing.CreateCellComment(anchor); IRichTextString str = factory.CreateRichTextString(message); comment.String = (/*setter*/ str); comment.Author = (/*setter*/ "fanfy"); cell.CellComment = (/*setter*/ comment); return(comment); }
/// <summary> /// Creates a polygon /// </summary> /// <param name="anchor">the client anchor describes how this Group is attached /// to the sheet.</param> /// <returns>the newly Created shape.</returns> public HSSFPolygon CreatePolygon(IClientAnchor anchor) { HSSFPolygon shape = new HSSFPolygon(null, (HSSFAnchor)anchor); shape.Anchor = (HSSFAnchor)anchor; AddShape(shape); return(shape); }
/// <summary> /// Constructs a textbox Under the patriarch. /// </summary> /// <param name="anchor">the client anchor describes how this Group is attached /// to the sheet.</param> /// <returns>the newly Created textbox.</returns> public HSSFSimpleShape CreateTextbox(IClientAnchor anchor) { HSSFTextbox shape = new HSSFTextbox(null, (HSSFAnchor)anchor); shape.Anchor = (HSSFAnchor)anchor; AddShape(shape); return(shape); }
/// <summary> /// 创建一个图表实例 /// </summary> /// <param name="excelChart"></param> /// <param name="sheet"></param> private void CreateChart(NPOIExcelChart excelChart, ISheet sheet, int startRow, int endRow) { if (_excelType != NPOIExcelType.XLS) { throw new NotImplementedException("只支持.xls文件作图"); } IDrawing drawing = sheet.CreateDrawingPatriarch(); IClientAnchor anchor = drawing.CreateAnchor(0, 0, 0, 0, 0, startRow, excelChart.Axis.Count, endRow); XSSFChart chart = drawing.CreateChart(anchor) as XSSFChart; chart.Title.String = excelChart.Title; IChartLegend legend = chart.GetOrCreateLegend(); legend.Position = LegendPosition.TopRight; //坐标轴 var axis = chart.ChartAxisFactory.CreateCategoryAxis(AxisPosition.Bottom); axis.IsVisible = true; //值轴 var data = chart.ChartAxisFactory.CreateValueAxis(AxisPosition.Left); data.IsVisible = true; data.Crosses = (AxisCrosses.AutoZero); IChartDataSource <string> xs = DataSources.FromArray <string>(excelChart.Axis.ToArray()); switch (excelChart.ExcelChartType) { case NPOIExcelChartType.Bar: var chartBarData = chart.ChartDataFactory.CreateBarChartData <string, double>(); foreach (var item in excelChart.Data) { var curData = DataSources.FromArray <double>(item.Value.ToArray()); var curSeries = chartBarData.AddSeries(xs, curData); curSeries.SetTitle(item.Key); } chart.Plot(chartBarData, axis, data); return; case NPOIExcelChartType.Line: var chartLineData = chart.ChartDataFactory.CreateLineChartData <string, double>(); foreach (var item in excelChart.Data) { var curData = DataSources.FromArray <double>(item.Value.ToArray()); var curSeries = chartLineData.AddSeries(xs, curData); curSeries.SetTitle(item.Key); } chart.Plot(chartLineData, axis, data); break; default: break; } }
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); }
public IPicture CreatePicture(IClientAnchor anchor, int pictureIndex) { return CreatePicture((XSSFClientAnchor)anchor, pictureIndex); }
/** * Constructs a textbox under the Drawing. * * @param anchor the client anchor describes how this group is attached * to the sheet. * @return the newly Created textbox. */ public ITextbox CreateTextbox(IClientAnchor anchor) { long shapeId = newShapeId(); CT_TwoCellAnchor ctAnchor = CreateTwoCellAnchor(anchor); CT_Shape ctShape = ctAnchor.AddNewSp(); ctShape.Set(XSSFSimpleShape.Prototype()); ctShape.nvSpPr.cNvPr.id=(uint)shapeId; XSSFTextBox shape = new XSSFTextBox(this, ctShape); shape.anchor = (XSSFClientAnchor)anchor; return shape; }
/// <summary> /// Creates a polygon /// </summary> /// <param name="anchor">the client anchor describes how this Group is attached /// to the sheet.</param> /// <returns>the newly Created shape.</returns> public HSSFPolygon CreatePolygon(IClientAnchor anchor) { HSSFPolygon shape = new HSSFPolygon(null, (HSSFAnchor)anchor); shape.Anchor = (HSSFAnchor)anchor; AddShape(shape); return shape; }
/// <summary> /// Constructs a textbox Under the patriarch. /// </summary> /// <param name="anchor">the client anchor describes how this Group is attached /// to the sheet.</param> /// <returns>the newly Created textbox.</returns> public HSSFSimpleShape CreateTextbox(IClientAnchor anchor) { HSSFTextbox shape = new HSSFTextbox(null, (HSSFAnchor)anchor); shape.Anchor = (HSSFAnchor)anchor; AddShape(shape); return shape; }
/// <summary> /// Constructs a cell comment. /// </summary> /// <param name="anchor">the client anchor describes how this comment is attached /// to the sheet.</param> /// <returns>the newly created comment.</returns> public IComment CreateCellComment(IClientAnchor anchor) { return CreateComment((HSSFAnchor)anchor); }
public IChart CreateChart(IClientAnchor anchor) { throw new RuntimeException("NotImplemented"); }
/** * Creates a comment. * @param anchor the client anchor describes how this comment is attached * to the sheet. * @return the newly Created comment. */ public IComment CreateCellComment(IClientAnchor anchor) { XSSFClientAnchor ca = (XSSFClientAnchor)anchor; XSSFSheet sheet = (XSSFSheet)GetParent(); //create comments and vmlDrawing parts if they don't exist CommentsTable comments = sheet.GetCommentsTable(true); XSSFVMLDrawing vml = sheet.GetVMLDrawing(true); NPOI.OpenXmlFormats.Vml.CT_Shape vmlShape = vml.newCommentShape(); if (ca.IsSet()) { String position = ca.Col1 + ", 0, " + ca.Row1 + ", 0, " + ca.Col2 + ", 0, " + ca.Row2 + ", 0"; vmlShape.GetClientDataArray(0).SetAnchorArray(0, position); } XSSFComment shape = new XSSFComment(comments, comments.CreateComment(), vmlShape); shape.Column = (ca.Col1); shape.Row = (ca.Row1); return shape; }
/** * Create and Initialize a CT_TwoCellAnchor that anchors a shape against top-left and bottom-right cells. * * @return a new CT_TwoCellAnchor */ private CT_TwoCellAnchor CreateTwoCellAnchor(IClientAnchor anchor) { CT_TwoCellAnchor ctAnchor = drawing.AddNewTwoCellAnchor(); XSSFClientAnchor xssfanchor = (XSSFClientAnchor)anchor; ctAnchor.from = (xssfanchor.From); ctAnchor.to = (xssfanchor.To); ctAnchor.AddNewClientData(); xssfanchor.To = ctAnchor.to; xssfanchor.From = ctAnchor.from; ST_EditAs aditAs; switch (anchor.AnchorType) { case AnchorType.DontMoveAndResize: aditAs = ST_EditAs.absolute; break; case AnchorType.MoveAndResize: aditAs = ST_EditAs.twoCell; break; case AnchorType.MoveDontResize: aditAs = ST_EditAs.oneCell; break; default: aditAs = ST_EditAs.oneCell; break; } ctAnchor.editAs = aditAs; ctAnchor.editAsSpecified = true; return ctAnchor; }
/// <summary> /// Creates a chart. /// </summary> /// <param name="anchor">the client anchor describes how this chart is attached to</param> /// <returns>the newly created chart</returns> public IChart CreateChart(IClientAnchor anchor) { int chartNumber = GetPackagePart().Package. GetPartsByContentType(XSSFRelation.CHART.ContentType).Count + 1; XSSFChart chart = (XSSFChart)CreateRelationship( XSSFRelation.CHART, XSSFFactory.GetInstance(), chartNumber); String chartRelId = chart.GetPackageRelationship().Id; XSSFGraphicFrame frame = CreateGraphicFrame((XSSFClientAnchor)anchor); frame.SetChart(chart, chartRelId); return chart; }
/** * Returns all charts in this Drawing. */ //public List<XSSFChart> GetCharts() //{ // List<XSSFChart> charts = new List<XSSFChart>(); // foreach (POIXMLDocumentPart part in GetRelations()) // { // if (part is XSSFChart) // { // charts.Add((XSSFChart)part); // } // } // return charts; //} /** * Create and Initialize a CT_TwoCellAnchor that anchors a shape against top-left and bottom-right cells. * * @return a new CT_TwoCellAnchor */ private CT_TwoCellAnchor CreateTwoCellAnchor(IClientAnchor anchor) { CT_TwoCellAnchor ctAnchor = drawing.AddNewTwoCellAnchor(); XSSFClientAnchor xssfanchor = (XSSFClientAnchor)anchor; ctAnchor.from = (xssfanchor.GetFrom()); ctAnchor.to = (xssfanchor.GetTo()); ctAnchor.AddNewClientData(); xssfanchor.SetTo(ctAnchor.to); xssfanchor.SetFrom(ctAnchor.from); ST_EditAs aditAs; switch (anchor.AnchorType) { case (int)AnchorType.DONT_MOVE_AND_RESIZE: aditAs = ST_EditAs.absolute; break; case (int)AnchorType.MOVE_AND_RESIZE: aditAs = ST_EditAs.twoCell; break; case (int)AnchorType.MOVE_DONT_RESIZE: aditAs = ST_EditAs.oneCell; break; default: aditAs = ST_EditAs.oneCell; break; } ctAnchor.editAs = aditAs; ctAnchor.editAsSpecified = true; return ctAnchor; }
/** * Creates a comment. * @param anchor the client anchor describes how this comment is attached * to the sheet. * @return the newly Created comment. */ public IComment CreateCellComment(IClientAnchor anchor) { XSSFClientAnchor ca = (XSSFClientAnchor)anchor; XSSFSheet sheet = (XSSFSheet)GetParent(); //create comments and vmlDrawing parts if they don't exist CommentsTable comments = sheet.GetCommentsTable(true); XSSFVMLDrawing vml = sheet.GetVMLDrawing(true); NPOI.OpenXmlFormats.Vml.CT_Shape vmlShape = vml.newCommentShape(); if (ca.IsSet()) { // convert offsets from emus to pixels since we get a DrawingML-anchor // but create a VML Drawing int dx1Pixels = ca.Dx1 / Units.EMU_PER_PIXEL; int dy1Pixels = ca.Dy1 / Units.EMU_PER_PIXEL; int dx2Pixels = ca.Dx2 / Units.EMU_PER_PIXEL; int dy2Pixels = ca.Dy2 / Units.EMU_PER_PIXEL; String position = ca.Col1 + ", " + dx1Pixels + ", " + ca.Row1 + ", " + dy1Pixels + ", " + ca.Col2 + ", " + dx2Pixels + ", " + ca.Row2 + ", " + dy2Pixels; vmlShape.GetClientDataArray(0).SetAnchorArray(0, position); } String ref1 = new CellReference(ca.Row1, ca.Col1).FormatAsString(); if (comments.FindCellComment(ref1) != null) { throw new ArgumentException("Multiple cell comments in one cell are not allowed, cell: " + ref1); } return new XSSFComment(comments, comments.NewComment(ref1), vmlShape); }
/** * Creates a chart. * @param anchor the client anchor describes how this chart is attached to * the sheet. * @return the newly Created chart * @see NPOI.xssf.usermodel.XSSFDrawing#CreateChart(ClientAnchor) */ public IChart CreateChart(IClientAnchor anchor) { //int chartNumber = GetPackagePart().Package. // GetPartsByContentType(XSSFRelation.CHART.ContentType).Count + 1; throw new NotImplementedException(); // XSSFChart chart = (XSSFChart)CreateRelationship( // XSSFRelation.CHART, XSSFFactory.GetInstance(), chartNumber); // String chartRelId = chart.GetPackageRelationship().GetId(); // XSSFGraphicFrame frame = CreateGraphicFrame(anchor); // frame.SetChart(chart, chartRelId); // return chart; }
// Set the comment on a sheet // private static void setComment(ISheet sheet, ICell cell, IDrawing drawing, String commentText, ICreationHelper helper, IClientAnchor anchor) { //System.out.println("Setting col: " + cell.getColumnIndex() + " and row " + cell.getRowIndex()); anchor.Col1 = (cell.ColumnIndex); anchor.Col2 = (cell.ColumnIndex); anchor.Row1 = (cell.RowIndex); anchor.Row2 = (cell.RowIndex); // get comment, or create if it does not exist // NOTE - only occurs if getCellComment is called first IComment comment = cell.CellComment; //Comment comment = null; if (comment == null) { comment = drawing.CreateCellComment(anchor); } comment.Author = ("Test"); // attach the comment to the cell comment.String = (helper.CreateRichTextString(commentText)); cell.CellComment = (comment); }
public XSSFCheckBox CreateCheckbox(IClientAnchor anchor) { // create drawing anchor.AnchorType = AnchorType.MoveDontResize; long shapeId = newShapeId(); CT_TwoCellAnchor ctAnchor = CreateTwoCellAnchor(anchor); ctAnchor.isAlternateContentChoice = true; CT_Shape ctShape = ctAnchor.AddNewSp(); ctShape.Set(XSSFCheckboxShape.GetPrototype()); ctShape.nvSpPr.cNvPr.id = (uint)shapeId; ctShape.nvSpPr.cNvPr.extLst = new CT_OfficeArtExtensionList { ext = new List<CT_OfficeArtExtension> { new CT_OfficeArtExtension{ uri = "{63B3BB69-23CF-44E3-9099-C40C66FF867C}", Any = "<a14:compatExt spid=\"_x0000_s" + shapeId + "\"/>", }, }, }; ctShape.nvSpPr.cNvPr.name = "Checkbox " + shapeId; // create vmlDrawing XSSFClientAnchor ca = (XSSFClientAnchor)anchor; XSSFSheet sheet = (XSSFSheet)GetParent(); XSSFVMLDrawing vml = sheet.GetVMLDrawing(true); NPOI.OpenXmlFormats.Vml.CT_Shape vmlShape = vml.newCheckboxShape(); if (ca.IsSet()) { // convert offsets from emus to pixels since we get a DrawingML-anchor // but create a VML Drawing int dx1Pixels = ca.Dx1 / Units.EMU_PER_PIXEL; int dy1Pixels = ca.Dy1 / Units.EMU_PER_PIXEL; int dx2Pixels = ca.Dx2 / Units.EMU_PER_PIXEL; int dy2Pixels = ca.Dy2 / Units.EMU_PER_PIXEL; String position = ca.Col1 + ", " + dx1Pixels + ", " + ca.Row1 + ", " + dy1Pixels + ", " + ca.Col2 + ", " + dx2Pixels + ", " + ca.Row2 + ", " + dy2Pixels; vmlShape.GetClientDataArray(0).SetAnchorArray(0, position); } String ref1 = new CellReference(ca.Row1, ca.Col1).FormatAsString(); XSSFCtrlProp ctrlProp = sheet.AddCtrlProp(); XSSFCheckBox shape = new XSSFCheckBox(this, ctShape, vmlShape, ctrlProp); shape.anchor = (XSSFClientAnchor)anchor; shape.SetText("Checkbox"); // create sheet NPOI.OpenXmlFormats.Spreadsheet.CT_Checkbox checkbox = sheet.AddNewCheckbox(); checkbox.name = "Check Box " + shapeId; checkbox.shapeId = shapeId.ToString(); String relId = ctrlProp.GetPackageRelationship().Id; checkbox.ctrlPropRelId = relId; checkbox.anchor = new OpenXmlFormats.Spreadsheet.CT_Anchor { from = new CT_Marker { col = anchor.Col1, colOff = 100000, row = anchor.Row1, rowOff = 300000, }, to = new CT_Marker { col = anchor.Col1, colOff = 130000, row = anchor.Row1, rowOff = 500000, } }; return shape; }