Excel 2003 -> Excel 2007- Chart DataLabels problems

Hi,

We are using Aspose.Cells to generate reports with charts. And print these to PDF using Excel 2007.

Because our customers are still using Excel 2003 on their PC's we try to check if the reports look the same in both Excel 2003 and Excel 2007.

Only when opening the generate Excel file in Excel 2007 the datalabels are completely different then expected.
In the designer file we have defined that only the percentage should be shown on every "slice" of the circlediagram.

In the code I loop through the datalabels of the NSeries and the ASeries and also tell it to only display the percentage.

If Not objASeries Is Nothing Then
objASeries.DataLabels.IsPercentageShown = True
objASeries.DataLabels.IsLegendKeyShown = False
objASeries.DataLabels.IsSeriesNameShown = False
objASeries.DataLabels.IsValueShown = False
End If

Dim objDataLabels As DataLabels = objExcelChart.NSeries(i - intNSeriesColumnIndex).DataLabels
objDataLabels.IsPercentageShown = True
objDataLabels.IsLegendKeyShown = False
objDataLabels.IsSeriesNameShown = False
objDataLabels.IsValueShown = False

I have include the generated xls, which looks totally different when opened in Excel 2003 and Excel 2007.

Here an example of how it looks (and should look) in Excel 2003:
Here an example how it looks in 2007, notice how the datalabel disappeared from the biggest slice (40%):\

Could you please tell me what I am doing wrong?

Hi,

Thanks for considering Aspose.

Yes we found a little difference related DataLabels, We will check and get back to you soon.

Thank you.

Hi,

Please try this fix.

Thank you for your quick fix.

At the first glance it seems to work, I will test it more throughly and will report back any findings.

With kind regards,

Sjoerd van Loon

Warren:

Hi,

Please try this fix.

Ok I have been working with the new 4.4.0.19 version of the Aspose.Cells.dll.

Only it seems that something changed in the ImportDataTable function between versions 4.4.0.17 and 4.4.0.19.

I define cells in the Excel template with DataRanges (like “DataRange3” and “FilterDataRange”) and put my datatable into those dataranges in the following steps:

  • Find the datarange
  • Fill DataTable with data from the database
  • Insert new rows to the excelsheet, after the datarange (InsertRows)
  • Import the datatable into the worksheet starting by the FirstRow of the datarange to numberofrows of the datatable (ImportDataTable)
  • Reset the datarange to have the new “size”
' Fill the datatable

objDataTable = New DataTable

objDataTable.Load(objCommand.ExecuteReader())

' Fill excel with the data and set the data range

Dim intNumberOfColumns As Integer = objDataTable.Columns.Count

Dim intNumberOfRows As Integer = objDataTable.Rows.Count

objRange.Worksheet.Cells.InsertRows(objRange.FirstRow + 1, intNumberOfRows, False)

If intNumberOfColumns > 0 AndAlso intNumberOfRows > 0 Then

objRange.Worksheet.Cells.ImportDataTable(objDataTable, False, objRange.FirstRow, objRange.FirstColumn, False)

Dim strOutputRangeName As String = objRange.Name

objRange = objRange.Worksheet.Cells.CreateRange(objRange.FirstRow, objRange.FirstColumn, intNumberOfRows, intNumberOfColumns)

objRange.Name = strOutputRangeName

End If

But with the 4.4.0.19 version I get the following exception:

System.ArgumentOutOfRangeException: Index was out of range. Must be non-negative and less than the size of the collection. Parameter name: index at System.Collections.ArrayList.get_Item(Int32 index) at Aspose.Cells.Cells.ImportDataTable(DataTable dataTable, Boolean isFieldNameShown, Int32 firstRow, Int32 firstColumn, Int32 rowNumber, Int32 columnNumber, Boolean insertRows, String dateFormatString, Boolean convertStringToNumber) at Aspose.Cells.Cells.ImportDataTable(DataTable dataTable, Boolean isFieldNameShown, Int32 firstRow, Int32 firstColumn, Boolean insertRows)

Even thoug the FirstRow and Column were ‘0’ and ‘0’, and the number of rows 4 and the number of column 2.

When I use the 4.4.0.17 version of the DLL it does work correclty.

Do I need a new approach with this new DLL?

Kind regards,

Sjoerd van Loon

Hi,

Could you create a sample console app that could produce such error as I have done some initial testation with ImportDataTable() method and don't find the problem with 4.4.0.19. If possible you may create a fake datatable with sample columns and fill it with data at runtime not using database table.

Thank you.

Hi,

I did not make a sample console app, because I have been looking at the problem together wit a colleague to create a workaround.

It look like the bug is really somewhere in the ImportDataTable, because when we switch on the ShowFieldHeader flag the code does work. So it looks to us that when the ShowFieldHeader flag is false a 1 too much is substracted from an index of an arraylist.

Now I am able to workaround the problem.
objRange.Worksheet.Cells.InsertRows(objRange.FirstRow + 1, intNumberOfRows, False)objRange.Worksheet.Cells.ImportDataTable(objDataTable, True, objRange.FirstRow, objRange.FirstColumn, False)

So now the first boolean is set to true, the headers are also generated (something I did not want, but for now I’ll manage). So I have to do some extra tricks to select the correct data.

Would you look into if this could be fixed in a next patch?

Kind regards,

Sjoerd van Loon

Hi,

Thanks for considering Aspose.

Following is my code which works fine without any problem / error.

Workbook excel = new Workbook();
Cells cells = excel.Worksheets[0].Cells;
//Set default row height
cells.StandardHeight = 20;
//Set row height
cells.SetRowHeight(2, 20.5);
//Set default colum width
cells.StandardWidth = 15;
//Set column width
cells.SetColumnWidth(3, 12.57);
//Merge cells
cells.Merge(5, 4, 2, 2);
//Import data
DataTable dt = new DataTable("Products");
dt.Columns.Add("Product_ID",typeof(Int32));
dt.Columns.Add("Product_Name",typeof(string));
dt.Columns.Add("Units_In_Stock",typeof(Int32));
DataRow dr = dt.NewRow();
dr[0] = 1;
dr[1] = "Aniseed Syrup";
dr[2] = 15;
dt.Rows.Add(dr);

dr = dt.NewRow();
dr[0] = 2;
dr[1] = "Boston Crab Meat";
dr[2] = 123;
dt.Rows.Add(dr);
cells.ImportDataTable(dt, false, 0, 0,false);

DataTable outDataTable = cells.ExportDataTable(0, 0, 1, cells.MaxDataRow +1);
Worksheet sheet2 = excel.Worksheets[excel.Worksheets.Add()];
sheet2.Cells.ImportDataTable(outDataTable,false,0,0,false);

excel.Save("d:\\test\\outbook.xls");
Could you try this and tell us if it is fine.
Thank you.

Hi,

Please try this fix.There is a bug in importdatatable method after i changed it.