Professional Web Applications Themes

Unable to set the XValues property of the Series class - PERL Modules

Hi All, I have tried umpteen ways of setting the XValues in an Excel Spreadsheet from Perl 5.8.7 using Win32::OLE, and I cannot get anything to work. I'm beginning to wonder if there is a bug either in Perl/OLE or in Excel. If I write $series->{XValues} = 23; I get a single label equal to 23. If I write $series->{XValues} = (23, 45); I also get a single label equal to 23. There are 101 data values. If I write $series->{XValues} = [23, 45]; I get a single label equal to 45. If I manually define a reference to a ...

  1. #1

    Default Unable to set the XValues property of the Series class

    Hi All,

    I have tried umpteen ways of setting the XValues in an Excel
    Spreadsheet from Perl 5.8.7 using Win32::OLE, and I cannot get anything
    to work. I'm beginning to wonder if there is a bug either in Perl/OLE
    or in Excel.

    If I write

    $series->{XValues} = 23;

    I get a single label equal to 23.

    If I write

    $series->{XValues} = (23, 45);

    I also get a single label equal to 23. There are 101 data values.

    If I write

    $series->{XValues} = [23, 45];

    I get a single label equal to 45.

    If I manually define a reference to a label array with 101 elements, I
    get an error:

    "Unable to set the XValues property of the Series class"

    I also get that error if I try to feed it an array by other means.
    Evidently, Excel won't handle an array of that size.

    The alternative appears to be to use a range of cells on a sheet. But
    in that case, I get another error:

    Error: Win32::OLE(0.1702) error 0x80020005: "Type mismatch"
    in PROPERTYPUTREF "XValues"

    BTW, the data plots fine.

    I am pulling my hair out here! Any help is greatly appreciated.

    Thanks.

    Darrell


    # Create an Excel workbook.
    my $excel = Win32::OLE->new("Excel.Application");
    $excel->{Visible} = 1;
    my $book = $excel->Workbooks->Add;
    my $sheets = $book->Worksheets;

    # Create a sheet for each file.
    for (my $i = 0; $i < dataFiles; $i++)
    {
    # Read a data file.
    < SNIPPED >

    $j = 0;
    while (<FILE>)
    {
    chomp;
    push labels, [$j];
    # push labels, \$j;
    # push labels, $j;
    push data, [$_];
    $j++;
    }

    # Create a sheet with the data.
    my $sheet = $sheets->Add;
    my $nCells = data;
    my $labelRange = $sheet->Range("A1:A$nCells");
    my $dataRange = $sheet->Range("B1:B$nCells");
    $labelRange->{Value} = \labels;
    $dataRange->{Value} = \data;

    # Create a chart.
    my $chart = $excel->Charts->Add;
    $chart->{ChartType} = xlColumnClustered;
    $chart->SetSourceData({Source => $dataRange, PlotBy => xlColumns});
    # $chart->SeriesCollection->NewSeries();
    # my $series = $chart->SeriesCollection(1);
    my $series = $chart->SeriesCollection->Item(1);
    print "name = ", $sheet->{'Name'}, "\n";
    # $series->{HasErrorBars} = 1;

    # $series->{XValues} = labels;
    # $series->{XValues} = \labels;
    # $series->{XValues} = $sheet->Range("A1:A$nCells")->{Value};
    # $series->{XValues} = $sheet->Range("A1");
    # $series->{XValues} = $sheet->Range("A1:A$nCells");
    # $series->{XValues} = "='$sheet->{'Name'}'!A1:A$nCells";
    print "='$sheet->{'Name'}'!\$A\$1:\$A\$$nCells\n";
    $series->{XValues} = "='$sheet->{'Name'}'!\$A\$1:\$A\$$nCells";
    # $series->{XValues} = $labelRange;
    # $series->{XValues} = $labelRange->{Value};
    # $series->{XValues} = "=A1:A$nCells";

    my $error = Win32::OLE->LastError();
    print "Error: $error\n";

    $chart->{HasTitle} = 1;
    $chart->ChartTitle->{Text} = $title;

    drphilosopher@yahoo.com Guest

  2. #2

    Default Re: Unable to set the XValues property of the Series class

    Evidently, no body knows the answer to this problem.

    Darrell

    drphilosopher@yahoo.com Guest

Similar Threads

  1. Help Anyone?How do I access Inner Class Property
    By Nigil LaVey in forum ASP.NET Building Controls
    Replies: 0
    Last Post: November 3rd, 12:27 PM
  2. Binding a textbox to a class property
    By Jonathan Byrne in forum ASP.NET Building Controls
    Replies: 0
    Last Post: August 11th, 06:35 AM
  3. Unable to set Text property of a Combo Box
    By May in forum Microsoft Access
    Replies: 8
    Last Post: August 1st, 09:10 PM
  4. undefined class property
    By lawrence in forum PHP Development
    Replies: 0
    Last Post: July 12th, 06:30 PM
  5. Class property misunderstanding
    By Kevin Spencer in forum ASP.NET General
    Replies: 1
    Last Post: June 26th, 05:28 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139