Updating a single bar in a series


#1

Hi @Amjad_Sahi,

How to update a particular bar among the mutiple series as like in the attached image using Aspose cell.java .

excel.zip (10.2 KB)


#2

@prabu759101

Thanks for using Aspose APIs.

Please see the following sample code and its output Excel file and its screenshot. As you can see, the fourth data bar color is Yellow.

Download Link:
Output Excel File.zip (10.6 KB)

Java

//Load the sample Excel file
Workbook wb = new Workbook(dirPath + "excel_series.xlsx");

//Access the worksheet
Worksheet ws = wb.getWorksheets().get(0);

//Access the chart
Chart ch  = ws.getCharts().get(0);

//Access the series
Series srs = ch.getNSeries().get(0);

//Change the fourth chart point - data bar color to Yellow
ChartPoint cp = srs.getPoints().get(3);
cp.getArea().setForegroundColor(Color.getYellow());

//Save the workbook
wb.save(dirPath + "output.xlsx");

Screenshot:


#3

Hi @shakeel.faiz,
Good day. Thank you for the response. I am able to update the singe data point using aspose.
My new requirement is based upon the datapoint value, we need to insert the image in the bars. How can we do this using Aspose.

Steps in Excel:

  1. Click the data point --> in the series options click the picture or texture fill radio button —> From insert picture from option click the file button, then we are able to proceed the image inserted.

How we can do this using Aspose.

Herewith attached the excel.

Excel_Series.zip (834.0 KB)


#4

@prabu759101

Please use the ChartPoint.Area.FillFormat.FillType and ChartPoint.Area.FillFormat.ImageData properties for your needs. The sample code is given below. Please see the output Excel file and screenshot for a reference.

Download Link:
Input and Output Files-.zip (2.6 MB)

Java

//Load your source Excel file
Workbook wb = new Workbook(dirPath + "excel_series_image.xlsx");

//Access first worksheet
Worksheet ws = wb.getWorksheets().get(0);

//Access your chart
Chart ch = ws.getCharts().get(0);

//Access the series
Series srs = ch.getNSeries().get(0);

//Access the series points
ChartPoint o0 = srs.getPoints().get(0);
ChartPoint o1 = srs.getPoints().get(1);
ChartPoint o2 = srs.getPoints().get(2);
ChartPoint o3 = srs.getPoints().get(3);
ChartPoint o4 = srs.getPoints().get(4);
ChartPoint o5 = srs.getPoints().get(5);

//Read the bytes of your image
File fimg = new File(dirPath + "AsposeLogo.png");
int len = (int)fimg.length();
byte[] img = new byte[len];
FileInputStream fisImg = new FileInputStream(fimg);
fisImg.read(img, 0, len);
fisImg.close();

//Insert the image in the second data point bar
o1.getArea().getFillFormat().setFillType(FillType.TEXTURE);
o1.getArea().getFillFormat().setImageData(img);

//Save the output Excel file
wb.save(dirPath + "output.xlsx");

C#

//Load your source Excel file
Workbook wb = new Workbook("excel_series_image.xlsx");

//Access first worksheet
Worksheet ws = wb.Worksheets[0];

//Access your chart
Chart ch = ws.Charts[0];

//Access the series
Series srs = ch.NSeries[0];

//Access the series points
var o0 = srs.Points[0];
var o1 = srs.Points[1];
var o2 = srs.Points[2];
var o3 = srs.Points[3];
var o4 = srs.Points[4];
var o5 = srs.Points[5];

//Read the bytes of your image
byte[] img = File.ReadAllBytes("AsposeLogo.png");

//Insert the image in the second data point bar
o1.Area.FillFormat.FillType = FillType.Texture;
o1.Area.FillFormat.ImageData = img;

//Save the output Excel file
wb.Save("output.xlsx");

Screenshot: