Page Index Toggle Pages: 1 Send TopicPrint
Normal Topic how to create charts for some ranges (Read 1619 times)
ABaroughi
YaBB Newbies
*
Offline


I Love MindFusion!

Posts: 2
Joined: Jul 4th, 2014
how to create charts for some ranges
Jul 16th, 2014 at 11:36am
Print Post  
Sir,

The translator we use to import old .xls files cannot import charts. We want to find from code behind where the range in first and second columns end, and add new chart on forth column showing the range. Kindly help me in this regard.
  
Back to top
 
IP Logged
 
Meppy
God Member
*****
Offline


MindFusion support

Posts: 1783
Joined: Jul 20th, 2005
Re: how to create charts for some ranges
Reply #1 - Jul 16th, 2014 at 12:38pm
Print Post  
Hi,

You can import the xlsx file produced by Office Binary Translator by using the ExcelImporter class:

Code
Select All
var workbook = new Workbook();
var importer = new ExcelImporter();
importer.Import(xlsxFile, workbook); 


The worksheets in the workbook can be accessed through the Worksheets collection by index or by name. For example, to obtain the first worksheet in the workbook, use the following code:

Code
Select All
var worksheet = workbook.Worksheets[0]; 


To find the range of cells in the first two columns, which contain data, you can traverse the worksheet cells and inspect their Column and Data properties:

Code
Select All
int maxRow = -1;
foreach (var cell in worksheet.Cells)
{
	if ((cell.Column == 0 || cell.Column == 1) && cell.Data != null)
		maxRow = Math.Max(maxRow, cell.Row);
}

if (maxRow != -1)
{
	var range = worksheet.CellRanges[0, 0, 1, maxRow];
} 


To add a new chart in the worksheet, call the AddChart method of the Drawing object of the worksheet. You can supply the origin column and row of the chart as parameters:

Code
Select All
var chart = worksheet.Drawing.AddChart(3, 0); 


To specify the data displayed in the chart, call the SetDataSource method:

Code
Select All
chart.SetDataSource(range.ToString(), PlotBy.Column, null, null); 


After you are done, use an ExcelExporter object to save the modified workbook back to the .xlsx file:

Code
Select All
var exporter = new ExcelExporter();
exporter.Export(workbook, xlsxFile); 


Below you can find an image illustrating the result:

Regards,
Meppy
  

image.png ( 15 KB | 21 Downloads )
image.png
Back to top
 
IP Logged
 
Page Index Toggle Pages: 1
Send TopicPrint