search

randym / axlsx

xlsx generation with charts, images, automated column width, customizable styles and full schema validation. Axlsx excels at helping you generate beautiful Office Open XML Spreadsheet documents without having to understand the entire ECMA specification. Check out the README for some examples of how easy it is. Best of all, you can validate your xlsx file before serialization so you know for sure that anything generated is going to load on your client's machine.
MIT License
2.62k stars 697 forks source link

Can the line chart y axis use a logarithmic scale? #60

Closed bruparel closed 12 years ago

bruparel commented 12 years ago

Hello Randy, This looks very interesting. I would like to display the line charts using a logarithmic Y axis scale. Can that be done? If you show me how, I am willing to give it a shot. My Excel knowledge is minimal though. Thanks. Bharat

randym commented 12 years ago

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

bruparel commented 12 years ago

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

From: Randy Morgan reply@reply.github.com To: Bharat Ruparel bcruparel@yahoo.com 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: https://github.com/randym/axlsx/issues/60#issuecomment-4663868

bruparel commented 12 years ago

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

bruparel commented 12 years ago

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

randym commented 12 years ago

@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

randym commented 12 years ago

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

bruparel commented 12 years ago

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 reply@reply.github.com To: Bharat Ruparel bcruparel@yahoo.com 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: https://github.com/randym/axlsx/issues/60#issuecomment-5397302

randym commented 12 years ago

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?

bruparel commented 12 years ago

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 reply@reply.github.com To: Bharat Ruparel bcruparel@yahoo.com 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: https://github.com/randym/axlsx/issues/60#issuecomment-5402303

randym commented 12 years ago

An sample excel file would be great.

bruparel commented 12 years ago

Incoming soon.  I promise :)

From: Randy Morgan reply@reply.github.com To: Bharat Ruparel bcruparel@yahoo.com 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: https://github.com/randym/axlsx/issues/60#issuecomment-5402475

bruparel commented 12 years ago

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 reply@reply.github.com To: Bharat Ruparel bcruparel@yahoo.com 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: https://github.com/randym/axlsx/issues/60#issuecomment-5402475

randym commented 12 years ago

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

bruparel commented 12 years ago

I am sending the attachments at this address: digital.ipseity@gmail.com Is that OK? Thanks. Bharat

From: Randy Morgan reply@reply.github.com To: Bharat Ruparel bcruparel@yahoo.com 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: https://github.com/randym/axlsx/issues/60#issuecomment-5405487

jbmyid commented 10 years ago

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