Can the line chart y axis use a logarithmic scale? about axlsx HOT 15 CLOSED

Hi Bharat,

You most certainly can. In fact, all axis should support scaling.
Here is an example of how to do it.

##Generating A Line Chart with logarithmic value axis scaling
require 'axlsx.rb'
p = Axlsx::Package.new
wb = p.workbook
wb.add_worksheet(:name => "Line Chart") do |sheet|
  sheet.add_row ["First", 1, 5, 7, 9]
  sheet.add_row ["Second", 5, 2, 14, 9]
  sheet.add_chart(Axlsx::Line3DChart, :title => "example 6: Line Chart", :rotX => 30, :rotY => 20) do |chart|
    chart.start_at 0, 2
    chart.end_at 10, 15
    chart.add_series :data => sheet["B1:E1"], :title => sheet["A1"]
    chart.add_series :data => sheet["B2:E2"], :title => sheet["A2"]
    chart.valAxis.scaling.logBase = 5
  end
end
p.serialize 'log_scale.xlsx'

You can find more information in the docs under Alxsx::Scaling

from axlsx.

Hey thanks Randy for your prompt response. I will give this a shot and keep you posted.
Regards,
Bharat

From: Randy Morgan [email protected]
To: Bharat Ruparel [email protected]
Sent: Friday, March 23, 2012 12:25 PM
Subject: Re: [axlsx] Can the line chart y axis use a logarithmic scale? (#60)

Hi Bharat,

You most certainly can. In fact, all axis should support scaling.
Here is an example of how to do it.

##Generating A Line Chart with logarithmic value axis scaling
require 'axlsx.rb'
p = Axlsx::Package.new
wb = p.workbook
wb.add_worksheet(:name => "Line Chart") do |sheet|
 sheet.add_row ["First", 1, 5, 7, 9]
 sheet.add_row ["Second", 5, 2, 14, 9]
 sheet.add_chart(Axlsx::Line3DChart, :title => "example 6: Line Chart", :rotX => 30, :rotY => 20) do |chart|
  chart.start_at 0, 2
  chart.end_at 10, 15
  chart.add_series :data => sheet["B1:E1"], :title => sheet["A1"]
  chart.add_series :data => sheet["B2:E2"], :title => sheet["A2"]
  chart.valAxis.scaling.logBase = 5
 end
end
p.serialize 'log_scale.xlsx'

You can find more information in the docs under Alxsx::Scaling

Reply to this email directly or view it on GitHub:
#60 (comment)

from axlsx.

I tried it. It works! Thank you. Let me know how can I help.

from axlsx.

Apologies, but now that the graph is working.

1. I need to apply specific colors to the time series that I plot in my graph. Please allow me to explain: In Oil and Gas Industry, where I work, color "red" represents "gas", "green" represents "oil" and "blue" represents "water". How can I specify that for the line or 3d line graph?
2. The horizontal (or x-axis) goes from 1 to 150 or 240 360 etc. Obviously, I need to apply scaling. How do I define the x axis to be devided in resonable time intervals?
3. The Line3DChart is a bit of an overkill in my case, can I create a simple Line Chart which plots a series of values, e.g., 150, in the example above in evenly spaced x axis (say 10 to 15 units instead of 150 units as is happening right now). I am not very knowledgeable in Excel so this may be a beginner question. I changed the chart type from Axlsx::Line3DChart to Axlsx::LineSeries and got an error.
4. I tried to mimick your example code for for showing hiding gridlines as below:

wb.add_worksheet(:name => "Line Chart") do |sheet|
sheet.add_row ["First", 1, 5, 7, 9]
sheet.add_row ["Second", 5, 2, 14, 9]
sheet.add_chart(Axlsx::Line3DChart, :title => "example 6") do |chart|
chart.start_at 0, 2
chart.end_at 10, 15
chart.add_series :data => sheet["B1:E1"], :title => sheet["A1"]
chart.add_series :data => sheet["B2:E2"], :title => sheet["A2"]
chart.valAxis.gridlines = false
chart.catAxis.gridlines = false
end
end
p.serialize 'chart_grid.xlsx'

This works fine as a stand alone Ruby example.

But when I include this in my rails application, I get error at this line:

chart.valAxis.gridlines = false

Please note that I am requiring the axlsx package as shown below:

require 'axlsx'

Is that not enough?

Thanks.

Bharat

from axlsx.

@bruparel

Hey mate. Glad to hear things are starting to work out for you.
Let's take these one at a time:

1. Colors for data - the short answer is not yet. It is possible, but I have not implemented colors for data series yet. Looking at the ECMA 376 specification, this is doable, but not trivial.

2. The "X-Axis" in a line chart is the catAxis. As it extends the axis class, you have access to the scaling object via
   chart.calAxis.scaling (See Axlsx::Scaling in the docs) which gives you max / min properties as well.

3. LineSeries is not a chart type, it is a series of data to be used in a chart. If you want to flatten the chart all you need to do is specify :rotX => 0, :rotY => 0 (which basically says don't rotate the chart!)

4. The current release (1.0.18) does not support this.
   If you check the Change Log section at the end of the README, you can see that the feature was implemented after the last official release. So for now, you need to change your Gemfile to read off of github.

   gem 'axlsx', :git => 'https://github.com/randym/axlsx'

and run

 bundle update axlsx  

Best

@randym

from axlsx.

@bruparel Greets mate. Just wanted to let you know that I've just finished implementing colors for charts. I'll do an official release later next week, but it is on master now if you would like to have a go. Take a look at examples/chart_colors.rb for an example on how to do it.

from axlsx.

Hey Thanks Randy!  Great news!!
I will be sure to check it out next week.  In the meantime, would you be able to point me to a suitable tutorial for drawing charts using axlsx?  I can create Excel worksheets without any problems, but I get error messages when trying to create a Chart in it.
Regards,
Bharat

From: Randy Morgan [email protected]
To: Bharat Ruparel [email protected]
Sent: Saturday, April 28, 2012 9:53 AM
Subject: Re: [axlsx] Can the line chart y axis use a logarithmic scale? (#60)

@bruparel Greets mate. Just wanted to let you know that I've just finished implementing colors for charts. I'll do an official release later next week, but it is on master now if you would like to have a go. Take a look at examples/chart_colors.rb for an example on how to do it.

Reply to this email directly or view it on GitHub:
#60 (comment)

from axlsx.

I'd be happy to put together an example. Can you tell me what kind of chart you are trying to make? Maybe even a screen shot of something you have done via the excel UI?

from axlsx.

Hey Thanks Randy.  I will try to put together a Chart in Excel to show you what I am trying to do.  It is a line chart with logarithmic Y axis.  I have it working perfectly using HiCharts with my Rails application.  Hi Charts as you may know, lets you download a bit map image of the graph.  Will that be sufficient?  My Excel worksheet will have the data supporting it. and I can send you the snippets of my code that creates it (basically it follows what you explain in your articles :)
Regards,
Bharat

From: Randy Morgan [email protected]
To: Bharat Ruparel [email protected]
Sent: Saturday, April 28, 2012 10:21 PM
Subject: Re: [axlsx] Can the line chart y axis use a logarithmic scale? (#60)

I'd be happy to put together an example. Can you tell me what kind of chart you are trying to make? Maybe even a screen shot of something you have done via the excel UI?

Reply to this email directly or view it on GitHub:
#60 (comment)

from axlsx.

An sample excel file would be great.

from axlsx.

Incoming soon.  I promise :)

From: Randy Morgan [email protected]
To: Bharat Ruparel [email protected]
Sent: Saturday, April 28, 2012 10:57 PM
Subject: Re: [axlsx] Can the line chart y axis use a logarithmic scale? (#60)

An sample excel file would be great.

Reply to this email directly or view it on GitHub:
#60 (comment)

from axlsx.

Hello Randy,
I am attaching the following for your perusal:

1.  Chart.jpeg - this is the Hicharts produced chart in my Rails application
2.  gas_secondary_gross_ngl.xlsx - Excel file produced by my coding using your wonderful gem.  The graph is produced using the last three columns of the middle tab (Typewell Output) Oil, Gas, and Water respectively.
3.  export.rb - This contains the code that I wrote to produce the Excel Worksheet.  It is a method on my controller.
   I really appreciate your help and willingness to debug.
   Thanks.
   Bharat

From: Randy Morgan [email protected]
To: Bharat Ruparel [email protected]
Sent: Saturday, April 28, 2012 10:57 PM
Subject: Re: [axlsx] Can the line chart y axis use a logarithmic scale? (#60)

An sample excel file would be great.

Reply to this email directly or view it on GitHub:
#60 (comment)

from axlsx.

Not seeing the attachments here mate. Could you email them to me directly please?

from axlsx.

I am sending the attachments at this address: [email protected]
Is that OK?
Thanks.
Bharat

From: Randy Morgan [email protected]
To: Bharat Ruparel [email protected]
Sent: Sunday, April 29, 2012 9:53 AM
Subject: Re: [axlsx] Can the line chart y axis use a logarithmic scale? (#60)

Not seeing the attachments here mate. Could you email them to me directly please?

Reply to this email directly or view it on GitHub:
#60 (comment)

from axlsx.

Can we set interval to :auto, I am facing problem with the x axis intervals, which is date series.

from axlsx.