Hi,
We have bought Aspose Total for .NET and read the documentation. But, still we are with no answers for some things. We have to create slides with data from database. And in slides we have chart, text, metrics etc…
Below are the two examples for the test development we have to do… Please help us in right direction on how to achieve this.
img1:
img2
It’s an urgent requirement. Please help me out!!!
thanks in advance.
-Praveen.
Hi Praveen,
Thanks for your interest in Aspose.Slides.
I have observed the images shared by you. I regret to inform you that Aspose.Slides for .NET does not support creating charts in it. You can only add charts in presentation using OLE Object or as image. There is a separate component Aspose.Cells where by you can create charts even by importing data from some database. Please follow documentation link 1 and link 2 to see how to add OLE Objects in Aspose.Slides.
I will request Aspose.Cells team to provide further technical help for creating charts from your data residing in database.
Thanks and Regards,
Thanks for the reply.
Yes, we are using Aspose Cells to generate the chart and upload it to the slide and save it as ppt.
But, my concern is about the text next to the chart and the styles.
In the documentation it’s difficult for me to find the tips on how to apply, font, background and different styles…
What way you can help me to make the images to be real time through code without any issues… in limited time?
-Praveen.
Hi,
Please create your desired chart(s) in MS Excel manually and post the excel file (containing your desired charts) here, we will check and let you know how you may create the chart(s) using Aspose.Cells API.
Thank you.
Hi,
I thought Aspose.Cells team gives some reply. But, unfortunately no…
Here is the same code used to generate the img2…
protected void Page_Load(object sender, EventArgs e)
{
Run();
//Test();
}
private void Test()
{
//Instantiate a Presentation object that represents a PPT file
Presentation pres = new Presentation("c:\\aspose\\output.ppt");
//Accessing a slide using its slide position
Slide slide = pres.GetSlideByPosition(1);
//Adding a line shape into the slide with its start and end points
slide.Shapes.AddLine(new Point(1000, 2000), new Point(4700, 2000));
slide.Shapes.AddEllipse(2300, 1200, 1000, 2000);
slide.Shapes.AddRectangle(1400, 1100, 3000, 2000);
//Writing the presentation as a PPT file
pres.Write("c:\\aspose\\outputmodified.ppt");
}
public void Run()
{
//Step - 1: Create an excel chart using Aspose.Cells
//--------------------------------------------------
//Create a workbook
Workbook wb = new Workbook();
//Add an excel chart
int chartRows = 55;
int chartCols = 25;
int chartSheetIndex = AddExcelChartInWorkbook(wb, chartRows, chartCols);
//Step - 2: Set the OLE size of the chart. using Aspose.Cells
//-----------------------------------------------------------
wb.Worksheets.SetOleSize(0, chartRows, 0, chartCols);
//Step - 3: Get the image of the chart with Aspose.Cells
//-----------------------------------------------------------
Bitmap imgChart = wb.Worksheets[chartSheetIndex].Charts[0].ToImage();
//Save the workbook to stream
MemoryStream wbStream = wb.SaveToStream();
//Step - 4 AND 5
//-----------------------------------------------------------
//Step - 4: Embed the chart as an OLE object inside .ppt presentation using Aspose.Slides
//-----------------------------------------------------------
//Step - 5: Replace the object changed image with the image obtained in step 3 to cater Object Changed Issue
//-----------------------------------------------------------
//Create a presentation
Presentation pres = new Presentation();
Slide sld = pres.GetSlideByPosition(1);
Aspose.Slides.Rectangle rectHeading = sld.Shapes.AddRectangle(0, 0, pres.SlideSize.Width, 300);
rectHeading.FillFormat.Type = FillType.NoFill;
rectHeading.AddTextFrame("Crispin Porter + Bogusky Reviewing MSFT:");
rectHeading.LineFormat.ShowLines = false;
//Add the workbook on slide
AddExcelChartInPresentation(pres, sld, wbStream, imgChart);
//Step - 6: Write the output presentation on disk
//-----------------------------------------------------------
Aspose.Slides.Table table = sld.Shapes.AddTable(pres.SlideSize.Width - 1500, 400, 1300, 500, 1, 4,1, Color.Gray);
for (int i = 0; i < table.RowsNumber; i++)
{
TextFrame frame = table.GetCell(0, i).TextFrame;
Portion p = frame.Paragraphs[0].Portions[0];
p.FontColor = Color.Gray;
switch (i)
{
case 0:
frame.FillFormat.Type = FillType.Pattern;
frame.FillFormat.PatternStyle = PatternStyle.Trellis;
p.Text = "Overall Mean";
p.FontHeight = 14;
p.FontBold = true;
frame.FillFormat.BackColor = Color.LightGray;break;
case 1:
p.FontHeight = 14;
p.Text = "7.6";
p.FontBold = true;
break;
case 2:
p.FontHeight = 10;
p.FontColor = Color.Gray;
p.Text = "FY10 Goal: 7.2";break;
case 3:
p.FontHeight = 10;
p.FontColor = Color.Gray;
p.Text = "2009 Overall Mean = N/A "; break;
}
}
Aspose.Slides.Table tableRating = sld.Shapes.AddTable(pres.SlideSize.Width - 1500, 1200, 1300, 1600, 1, 9, 0, Color.Gray);
for (int i = 0; i < tableRating.RowsNumber; i++ )
{
Portion p = tableRating.GetCell(0, i).TextFrame.Paragraphs[0].Portions[0];
p.FontHeight = 9;
p.FontColor = Color.Gray;
switch (i)
{
case 0: p.Text = "9 = Exceptional"; break;
case 1: p.Text = "8 = Very Good"; break;
case 2: p.Text = "7 = Good"; break;
case 3: p.Text = "6 = Slightly Above Average"; break;
case 4: p.Text = "5 = Average"; break;
case 5: p.Text = "4 = Below Average"; break;
case 6: p.Text = "3 = Poor"; break;
case 7: p.Text = "2 = Very Poor"; break;
case 8: p.Text = "1 = Unacceptable"; break;
}
}
/*
tableRating.GetCell(0, 1).TextFrame.Paragraphs[0].Portions[0].Text = "8 = Very Good";
tableRating.GetCell(0, 2).TextFrame.Paragraphs[0].Portions[0].Text = "7 = Good";
tableRating.GetCell(0, 3).TextFrame.Paragraphs[0].Portions[0].Text = "6 = Slightly Above Average";
tableRating.GetCell(0, 4).TextFrame.Paragraphs[0].Portions[0].Text = "5 = Average";
tableRating.GetCell(0, 5).TextFrame.Paragraphs[0].Portions[0].Text = "4 = Below Average";
tableRating.GetCell(0, 6).TextFrame.Paragraphs[0].Portions[0].Text = "3 = Poor";
tableRating.GetCell(0, 7).TextFrame.Paragraphs[0].Portions[0].Text = "2 = Very Poor";
tableRating.GetCell(0, 8).TextFrame.Paragraphs[0].Portions[0].Text = "1 = Unacceptable";
*/
pres.Write("c:\\aspose\\output.ppt");
}
int AddExcelChartInWorkbook(Workbook wb, int chartRows, int chartCols)
{
//Array of cell names
string[] cellsName = new string[]
{
"A1", "A2", "A3", "A4",
"B1", "B2", "B3", "B4",
"C1", "C2", "C3", "C4",
"D1", "D2", "D3", "D4",
"E1", "E2", "E3", "E4"
};
//Array of cell data
int[] cellsValue = new int[]
{
67,86,68,91,
44,64,89,48,
46,97,78,60,
43,29,69,26,
24,40,38,25
};
//Add a new worksheet to populate cells with data
int dataSheetIdx = wb.Worksheets.Add();
Worksheet dataSheet = wb.Worksheets[dataSheetIdx];
string sheetName = "DataSheet";
dataSheet.Name = sheetName;
//Populate DataSheet with data
for (int i = 0; i < cellsName.Length; i++)
{
string cellName = cellsName[i];
int cellValue = cellsValue[i];
dataSheet.Cells[cellName].PutValue(cellValue);
}
//Add a chart sheet
int chartSheetIdx = wb.Worksheets.Add(SheetType.Chart);
Worksheet chartSheet = wb.Worksheets[chartSheetIdx];
chartSheet.Name = "ChartSheet";
//Add a chart in ChartSheet with data series from DataSheet+
int chartIdx = chartSheet.Charts.Add(ChartType.Bar3DClustered, 0, chartRows, 0, chartCols);
Chart chart = chartSheet.Charts[chartIdx];
chart.NSeries.Add(sheetName + "!A1:E1", false);
chart.NSeries.Add(sheetName + "!A2:E2", false);
chart.NSeries.Add(sheetName + "!A3:E3", false);
chart.NSeries.Add(sheetName + "!A4:E4", false);
//Set ChartSheet an active sheet
wb.Worksheets.ActiveSheetIndex = chartSheetIdx;
return chartSheetIdx;
}
void AddExcelChartInPresentation(Presentation pres, Slide sld, Stream wbStream, Bitmap imgChart)
{
Aspose.Slides.Picture pic = new Aspose.Slides.Picture(pres, imgChart);
int picId = pres.Pictures.Add(pic);
int slideWidth = pres.SlideSize.Width;
int slideHeight = pres.SlideSize.Height;
int x = 0;
byte[] chartOleData = new byte[wbStream.Length];
wbStream.Position = 0;
wbStream.Read(chartOleData, 0, chartOleData.Length);
OleObjectFrame oof = sld.Shapes.AddOleObjectFrame(x, 400, slideWidth - 2000, slideHeight - 2000,
"Excel.Sheet.8", chartOleData);
oof.PictureId = picId;
oof.LineFormat.ShowLines = false;
}
{
Run();
//Test();
}
private void Test()
{
//Instantiate a Presentation object that represents a PPT file
Presentation pres = new Presentation("c:\\aspose\\output.ppt");
//Accessing a slide using its slide position
Slide slide = pres.GetSlideByPosition(1);
//Adding a line shape into the slide with its start and end points
slide.Shapes.AddLine(new Point(1000, 2000), new Point(4700, 2000));
slide.Shapes.AddEllipse(2300, 1200, 1000, 2000);
slide.Shapes.AddRectangle(1400, 1100, 3000, 2000);
//Writing the presentation as a PPT file
pres.Write("c:\\aspose\\outputmodified.ppt");
}
public void Run()
{
//Step - 1: Create an excel chart using Aspose.Cells
//--------------------------------------------------
//Create a workbook
Workbook wb = new Workbook();
//Add an excel chart
int chartRows = 55;
int chartCols = 25;
int chartSheetIndex = AddExcelChartInWorkbook(wb, chartRows, chartCols);
//Step - 2: Set the OLE size of the chart. using Aspose.Cells
//-----------------------------------------------------------
wb.Worksheets.SetOleSize(0, chartRows, 0, chartCols);
//Step - 3: Get the image of the chart with Aspose.Cells
//-----------------------------------------------------------
Bitmap imgChart = wb.Worksheets[chartSheetIndex].Charts[0].ToImage();
//Save the workbook to stream
MemoryStream wbStream = wb.SaveToStream();
//Step - 4 AND 5
//-----------------------------------------------------------
//Step - 4: Embed the chart as an OLE object inside .ppt presentation using Aspose.Slides
//-----------------------------------------------------------
//Step - 5: Replace the object changed image with the image obtained in step 3 to cater Object Changed Issue
//-----------------------------------------------------------
//Create a presentation
Presentation pres = new Presentation();
Slide sld = pres.GetSlideByPosition(1);
Aspose.Slides.Rectangle rectHeading = sld.Shapes.AddRectangle(0, 0, pres.SlideSize.Width, 300);
rectHeading.FillFormat.Type = FillType.NoFill;
rectHeading.AddTextFrame("Crispin Porter + Bogusky Reviewing MSFT:");
rectHeading.LineFormat.ShowLines = false;
//Add the workbook on slide
AddExcelChartInPresentation(pres, sld, wbStream, imgChart);
//Step - 6: Write the output presentation on disk
//-----------------------------------------------------------
Aspose.Slides.Table table = sld.Shapes.AddTable(pres.SlideSize.Width - 1500, 400, 1300, 500, 1, 4,1, Color.Gray);
for (int i = 0; i < table.RowsNumber; i++)
{
TextFrame frame = table.GetCell(0, i).TextFrame;
Portion p = frame.Paragraphs[0].Portions[0];
p.FontColor = Color.Gray;
switch (i)
{
case 0:
frame.FillFormat.Type = FillType.Pattern;
frame.FillFormat.PatternStyle = PatternStyle.Trellis;
p.Text = "Overall Mean";
p.FontHeight = 14;
p.FontBold = true;
frame.FillFormat.BackColor = Color.LightGray;break;
case 1:
p.FontHeight = 14;
p.Text = "7.6";
p.FontBold = true;
break;
case 2:
p.FontHeight = 10;
p.FontColor = Color.Gray;
p.Text = "FY10 Goal: 7.2";break;
case 3:
p.FontHeight = 10;
p.FontColor = Color.Gray;
p.Text = "2009 Overall Mean = N/A "; break;
}
}
Aspose.Slides.Table tableRating = sld.Shapes.AddTable(pres.SlideSize.Width - 1500, 1200, 1300, 1600, 1, 9, 0, Color.Gray);
for (int i = 0; i < tableRating.RowsNumber; i++ )
{
Portion p = tableRating.GetCell(0, i).TextFrame.Paragraphs[0].Portions[0];
p.FontHeight = 9;
p.FontColor = Color.Gray;
switch (i)
{
case 0: p.Text = "9 = Exceptional"; break;
case 1: p.Text = "8 = Very Good"; break;
case 2: p.Text = "7 = Good"; break;
case 3: p.Text = "6 = Slightly Above Average"; break;
case 4: p.Text = "5 = Average"; break;
case 5: p.Text = "4 = Below Average"; break;
case 6: p.Text = "3 = Poor"; break;
case 7: p.Text = "2 = Very Poor"; break;
case 8: p.Text = "1 = Unacceptable"; break;
}
}
/*
tableRating.GetCell(0, 1).TextFrame.Paragraphs[0].Portions[0].Text = "8 = Very Good";
tableRating.GetCell(0, 2).TextFrame.Paragraphs[0].Portions[0].Text = "7 = Good";
tableRating.GetCell(0, 3).TextFrame.Paragraphs[0].Portions[0].Text = "6 = Slightly Above Average";
tableRating.GetCell(0, 4).TextFrame.Paragraphs[0].Portions[0].Text = "5 = Average";
tableRating.GetCell(0, 5).TextFrame.Paragraphs[0].Portions[0].Text = "4 = Below Average";
tableRating.GetCell(0, 6).TextFrame.Paragraphs[0].Portions[0].Text = "3 = Poor";
tableRating.GetCell(0, 7).TextFrame.Paragraphs[0].Portions[0].Text = "2 = Very Poor";
tableRating.GetCell(0, 8).TextFrame.Paragraphs[0].Portions[0].Text = "1 = Unacceptable";
*/
pres.Write("c:\\aspose\\output.ppt");
}
int AddExcelChartInWorkbook(Workbook wb, int chartRows, int chartCols)
{
//Array of cell names
string[] cellsName = new string[]
{
"A1", "A2", "A3", "A4",
"B1", "B2", "B3", "B4",
"C1", "C2", "C3", "C4",
"D1", "D2", "D3", "D4",
"E1", "E2", "E3", "E4"
};
//Array of cell data
int[] cellsValue = new int[]
{
67,86,68,91,
44,64,89,48,
46,97,78,60,
43,29,69,26,
24,40,38,25
};
//Add a new worksheet to populate cells with data
int dataSheetIdx = wb.Worksheets.Add();
Worksheet dataSheet = wb.Worksheets[dataSheetIdx];
string sheetName = "DataSheet";
dataSheet.Name = sheetName;
//Populate DataSheet with data
for (int i = 0; i < cellsName.Length; i++)
{
string cellName = cellsName[i];
int cellValue = cellsValue[i];
dataSheet.Cells[cellName].PutValue(cellValue);
}
//Add a chart sheet
int chartSheetIdx = wb.Worksheets.Add(SheetType.Chart);
Worksheet chartSheet = wb.Worksheets[chartSheetIdx];
chartSheet.Name = "ChartSheet";
//Add a chart in ChartSheet with data series from DataSheet+
int chartIdx = chartSheet.Charts.Add(ChartType.Bar3DClustered, 0, chartRows, 0, chartCols);
Chart chart = chartSheet.Charts[chartIdx];
chart.NSeries.Add(sheetName + "!A1:E1", false);
chart.NSeries.Add(sheetName + "!A2:E2", false);
chart.NSeries.Add(sheetName + "!A3:E3", false);
chart.NSeries.Add(sheetName + "!A4:E4", false);
//Set ChartSheet an active sheet
wb.Worksheets.ActiveSheetIndex = chartSheetIdx;
return chartSheetIdx;
}
void AddExcelChartInPresentation(Presentation pres, Slide sld, Stream wbStream, Bitmap imgChart)
{
Aspose.Slides.Picture pic = new Aspose.Slides.Picture(pres, imgChart);
int picId = pres.Pictures.Add(pic);
int slideWidth = pres.SlideSize.Width;
int slideHeight = pres.SlideSize.Height;
int x = 0;
byte[] chartOleData = new byte[wbStream.Length];
wbStream.Position = 0;
wbStream.Read(chartOleData, 0, chartOleData.Length);
OleObjectFrame oof = sld.Shapes.AddOleObjectFrame(x, 400, slideWidth - 2000, slideHeight - 2000,
"Excel.Sheet.8", chartOleData);
oof.PictureId = picId;
oof.LineFormat.ShowLines = false;
}
Please let me know, your feedback.. It's urgent for me. Thanks for the help.
About img1, how to get the mixed colors int he bar... It has more than one color in the single bar. Can you please suggest me what to use?
Hi
Please use 100% stacked Bar with 3D effects chart type instead of 3DBar clustered for your requirement. I have modified your code a bit (for image1) and added a few lines (at the end) for some further formattings for your reference, see the updated code:
Workbook wb = new Workbook();
wb.Worksheets.Clear();
//Array of cell names
string[] cellsName = new string[]
{
“A1”, “A2”, “A3”, “A4”,
“B1”, “B2”, “B3”, “B4”,
“C1”, “C2”, “C3”, “C4”,
“D1”, “D2”, “D3”, “D4”,
“E1”, “E2”, “E3”, “E4”
};
//Array of cell data
int[] cellsValue = new int[]
{
67,86,68,91,
44,64,89,48,
46,97,78,60,
43,29,69,26,
24,40,38,25
};
//Add a new worksheet to populate cells with data
int dataSheetIdx = wb.Worksheets.Add();
Worksheet dataSheet = wb.Worksheets[dataSheetIdx];
string sheetName = “DataSheet”;
dataSheet.Name = sheetName;
//Populate DataSheet with data
for (int i = 0; i < cellsName.Length; i++)
{
string cellName = cellsName[i];
int cellValue = cellsValue[i];
dataSheet.Cells[cellName].PutValue(cellValue);
}
//Add a chart sheet
int chartSheetIdx = wb.Worksheets.Add(SheetType.Chart);
Worksheet chartSheet = wb.Worksheets[chartSheetIdx];
chartSheet.Name = “ChartSheet”;
//Add a chart in ChartSheet with data series from DataSheet+
int chartIdx = chartSheet.Charts.Add(ChartType.Bar3D100PercentStacked, 0,0, 20, 12);
Chart chart = chartSheet.Charts[chartIdx];
chart.NSeries.Add(sheetName + “!A1:E1”, false);
chart.NSeries.Add(sheetName + “!A2:E2”, false);
chart.NSeries.Add(sheetName + “!A3:E3”, false);
chart.NSeries.Add(sheetName + “!A4:E4”, false);
//Apply some sample Formattings…
//Set Properties of CategoryAxis labels.
chart.CategoryAxis.TickLabels.Font.Size = 8;
chart.CategoryAxis.TickLabels.Font.Name = “Tahoma”;
//Set Properties of CategoryAxis labels.
chart.ValueAxis.TickLabels.Font.Size = 8;
chart.ValueAxis.TickLabels.Font.Name = “Arial”;
//Format the second Series.
chart.NSeries[1].Area.ForegroundColor = System.Drawing.Color.Red;
wb.Save(“e:\test\MyChartWorkbook.xls”);
Hope, it helps you.
Thank you.
Yes. After composed the email, I have seen all ChartTypes available and got it. Thanks alot for the quick reply. How to remove the border for the chart? When I save it as image to upload to slide, I am getting border around it… Can we remove it?
Hi,
I think you may try:
chart.ChartArea.Border.IsVisible = false;
chart.PlotArea.Border.IsVisible = false;
chart.Walls.Border.IsVisible = false;
Thank you.
wow. You know what, that worked great.!!!
Seems like I am pretty much close. Can you please tell me how to get rid of the backround of the chart? It is always getting the gray background even if I didn’t mention anything.
I have applied below code:
chart.ChartArea.Area.BackgroundColor = Color.White;
chart.PlotArea.Area.BackgroundColor = Color.White;
chart.Walls.BackgroundColor = Color.White;
chart.Legend.Area.BackgroundColor = Color.White;
But, nothing works…What could be the issue?
thanks
=Praveen.
Hi,
Please use ForegroundColor instead of BackgroundColor attribute. I think you may try:
chart.ChartArea.Area.ForegroundColor = Color.White;
chart.PlotArea.Area.ForegroundColor = Color.Transparent;
chart.Walls.ForegroundColor = Color.Transparent;
chart.Floor.ForegroundColor = Color.Transparent;
chart.Legend.Area.ForegroundColor = Color.Transparent;
Thank you.
Is there any way we can color each bar with different color?
This is what I had in my requirements…
Image of requirement
Thanks
-Praveen
Hi,
I think you may see the following sample code segment for your reference:
//Set fill colors for the first, second and third data points in the series.
ASeries series = chart.NSeries[0];
series.Points[0].Area.ForegroundColor = System.Drawing.Color.Red;
series.Points[1].Area.ForegroundColor = System.Drawing.Color.Green;
series.Points[2].Area.ForegroundColor = System.Drawing.Color.Blue;
Thank you.