Updating a single bar in a series

prabu759101 · January 12, 2018, 1:58pm

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

shakeel.faiz · January 12, 2018, 2:20pm

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:

prabu759101 · February 6, 2018, 1:05pm

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:

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)

shakeel.faiz · February 7, 2018, 1:48am

@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:

Chandra1234 · April 30, 2019, 3:06pm

Hi ,
i am trying to update a one chartPoints color in a series . If chart containing more Than on series then working fine But if chart has single series then legends Name are coming different which i have set in series name .
Below is code
package Test.series.override;

import com.aspose.cells.Cell;
import com.aspose.cells.Cells;
import com.aspose.cells.Chart;
import com.aspose.cells.ChartCollection;
import com.aspose.cells.ChartPoint;
import com.aspose.cells.ChartPointCollection;
import com.aspose.cells.ChartType;
import com.aspose.cells.Color;
import com.aspose.cells.FillFormat;
import com.aspose.cells.FillType;
import com.aspose.cells.LegendPositionType;
import com.aspose.cells.PatternFill;
import com.aspose.cells.Series;
import com.aspose.cells.SeriesCollection;
import com.aspose.cells.Workbook;
import com.aspose.cells.Worksheet;
import com.aspose.cells.WorksheetCollection;

public class OverRideIssue {
public static void main(String args[] ) {
OverRideIssue p = new OverRideIssue();
p.TestIssue();
}
public void TestIssue () {
// initializing work-Book
Workbook workbook = new Workbook();
WorksheetCollection worksheets = workbook.getWorksheets();
Worksheet sheet = worksheets.get(0);

	// Adding some sample value to cells
	Cells cells = sheet.getCells();
	Cell cell = cells.get("A1");
	cell.setValue("Daily Rainfall");
	cell = cells.get("B1");
	cell.setValue("Particulate");
	Cell cell2 = cells.get("A2");
	cell2.setValue(1.9);
	cell2 = cells.get("B2");
	cell2.setValue(137);
	Cell cell3 = cells.get("A3");
	cell3.setValue(3.6);
	cell3 = cells.get("B3");
	cell3.setValue(128);
	Cell cell4 = cells.get("A4");
	cell4.setValue(4.1);
	cell4 = cells.get("B4");
	cell4.setValue(122);
	Cell cell5 = cells.get("A5");
	cell5.setValue(4.3);
	cell5 = cells.get("B5");
	cell5.setValue(117);
	Cell cell6 = cells.get("A6");
	cell6.setValue(5);
	cell6 = cells.get("B6");
	cell6.setValue(114);
	Cell cell7 = cells.get("A7");
	cell7.setValue(5.4);
	cell7 = cells.get("B7");
	cell7.setValue(114);
	Cell cell8 = cells.get("A8");
	cell8.setValue(5.7);
	cell8 = cells.get("B8");
	cell8.setValue(112);
	Cell cell9 = cells.get("A9");
	cell9.setValue(5.9);
	cell9 = cells.get("B9");
	cell9.setValue(110);
	Cell cell10 = cells.get("A10");
	cell10.setValue(7.3);
	cell10 = cells.get("B10");
	cell10.setValue(104);

	ChartCollection charts = sheet.getCharts();
	int chartIndex = charts.add(ChartType.BAR, 1, 3, 25, 12);
	Chart chart = charts.get(chartIndex);
	SeriesCollection serieses = chart.getNSeries();	
	
	// running correctly 
//	serieses.add("Sheet1!A2:B10", true);
	
	// for single issue is there
	serieses.add("Sheet1!B2:B10", true);
	
	chart.getNSeries().get(0).setXValues("Sheet1!A2:A10");
  for(int i = 0 ; serieses.getCount() > i ; i++ ) {
	  Series series = chart.getNSeries().get(i);
      series.setName("TEST"+i);
	  FillFormat fillPattern =  series.getArea().getFillFormat() ;
	  fillPattern.setType(FillType.PATTERN);
		PatternFill patFill = fillPattern.getPatternFill();
		Integer intColor = Integer.parseInt(Integer.toString(3*9999), 16);
		Color clr1 = Color.fromArgb(intColor);
		patFill.setBackgroundColor(clr1);
		patFill.setForegroundColor(clr1);
  }
  ChartPointCollection chartPoints = chart.getNSeries().get(0).getPoints();
	for (int k = 0; k < 1 ; k++) {
		ChartPoint chartPoint = chartPoints.get(k);			
		createColorFill(chartPoint.getArea().getFillFormat());			
	}
  chart.getLegend().setPosition(LegendPositionType.BOTTOM);
	try {
		workbook.save("D:\\Test.xlsx");
	} catch (Exception e) {
		e.printStackTrace();
	}
	System.out.println("Pyramid chart is successfully created.");
}
protected void createColorFill( FillFormat fillPattern) {
	fillPattern.setType(FillType.PATTERN);
	PatternFill patFill = fillPattern.getPatternFill();
	Integer intColor = Integer.parseInt("99CC00", 16);
	Color clr2 =  Color.fromArgb(intColor);
	patFill.setBackgroundColor(clr2);
	patFill.setForegroundColor(clr2);
}

}
image.png (7.4 KB)

amjad.sahi · April 30, 2019, 5:45pm

@Chandra1234,

Thanks for the sample code and screenshot.

Please notice the isVertical parameter of SeriesCollection.add method. For the chart’s source range, i.e., “Sheet1!B2:B10”, you need to set it to false to make it as horizontal range.
Please change the line of code, i.e.,

…
serieses.add(“Sheet1!B2:B10”, true);

to:

…
serieses.add(“Sheet1!B2:B10”, false);

Let us know if it does not work for your needs.