Missing border on bar chart bars

Aspose.Cells Product Family
1

Hi there,


I’m having an issue with 2 reports we’ve created. They both use aspose.cells (we have an aspose.total license though) to create a bar graph. The reports were displaying fine. Then out of nowhere, with no code changes being made, the borders around the bars disappeared. A co-worker had this same issue with reports he was working on and the issue went away after her rebooted his machine. However, this did not fix my issue.

Any suggestions?
2

Hi,


Do you convert your chart in the template file to image or render the Excel file (containing the chart) into PDF? Please provide your template Excel file containing the chart and sample code (runnable) to reproduce the issue on our end, we will check it soon.

Also, we recommend you to kindly try our latest version v8.6.1 if it makes any difference.

Thank you.
3

Hi,


Thanks for your quick reply. I will try the new dll asap. We do not use a template excel and create the whole graph with code. The graph is exported to an image then added to a pdf file elsewhere in the project. I have included the method we use to generate the images here: 
private String generateChartImages(DataTable chartDataSet, ChartData chartConfigInfo)
{
String sFileName="";
if (chartConfigInfo != null && chartDataSet != null)
{
String lang = chartDataSet.Rows[0][“Language”].ToString();


Workbook wrkbook = new Workbook(); // Instantiate a Workbook object
Worksheet wrksheet = wrkbook.Worksheets[0]; // Get the reference to default worksheet
Cells wCells = wrksheet.Cells;
wrksheet.Name = “Data”;

//place datatable in worksheet
wCells.ImportDataTable(chartDataSet, true, “A1”);
wCells.ConvertStringToNumericValue();

String sOutputPath = ConfigurationManager.AppSettings[“RootOutputPath”] + Config.getConfigValue(“MergeOutputPath”);
//wrkbook.Save(sOutputPath + “TestIni.xls”);

// Add chart to Excel document
int chartIndex = wrksheet.Charts.Add(ChartType.Column, 4,0,32, 13);
//int chartIndex = 0;
Chart chart = wrksheet.Charts[chartIndex];

int titleRowIndex = wCells.MinRow;
int titleRowLastColumnIndex = wCells.EndCellInRow(titleRowIndex).Column;







//Add Cell to chart range
DataPoint datapoint;
Cell pctTitleCell = null;
Cell cntTitleCell = null;

Aspose.Cells.Range dpRange = null;
int iPaletteInx = 54;

#region dpLoop
for (int dp = 0; dp < chartConfigInfo.ChartDataSets.Count; dp++)
{

datapoint = (DataPoint)chartConfigInfo.ChartDataSets[dp];
String fieldName = datapoint.FieldName;
String cntFieldName = Config.getConfigValue(“cOrgFieldName”, null, dp.ToString());

//Finding Datapoint in Imported DataSet
FindOptions findOption = new FindOptions();
findOption.SearchNext = true;
pctTitleCell = wCells.Find(fieldName, pctTitleCell, findOption);
cntTitleCell = wCells.Find(cntFieldName, cntTitleCell, findOption);

//Creating DataPoint Range
Int16 ptCol = (Int16)pctTitleCell.Column;
Int16 ptRow = (Int16)pctTitleCell.Row;
Int16 cntCol = (Int16)cntTitleCell.Column;


Cell rStartCell = wCells.GetCell((ptRow + 1), ptCol);
Cell rEndCell = wCells.EndCellInColumn(ptCol);
dpRange = wCells.CreateRange(rStartCell.Name, rEndCell.Name);

/*
* Adding Style to Range
/
//Aspose.Cells.Style percentFormat = new Aspose.Cells.Style();
//percentFormat.Number = 9;
//StyleFlag styleFlag = new StyleFlag();
//styleFlag.NumberFormat = true;
//dpRange.ApplyStyle(percentFormat, styleFlag);

/
* Make pctOrg_y1 pctOrg_y2 pctOrg_y3 pctOrg_y4 pctOrg
Percentage without percentage sign.
* */
Int32 colN = dpRange.ColumnCount;
Int32 rowN = dpRange.RowCount;

int stCol = dpRange.FirstColumn;
int stRow = dpRange.FirstRow;
Cell rngCell;
//Cell rngCell = wCells.GetCell(stRow, stCol);
//rngCell.Value = (object) (int) (rngCell.DoubleValue * 100);
for (int xi = 0; xi < colN; xi++ )
{
for (int xii = 0; xii < rowN; xii++)
{
rngCell = wCells.GetCell(stRow + xii, stCol + xi);
Type type = rngCell.Value.GetType();
if (type.Name.Equals(“String”))
{
if (rngCell.StringValue.Equals("<1%"))
{
if (lang.Equals(“2”))
rngCell.Value = (object)"<1 “;
else
rngCell.Value = (object)”<1";
}
}
else
rngCell.Value = (object)(int)(rngCell.DoubleValue * 100);
}
}

chart.NSeries.Add(rStartCell.Name + “:” + rEndCell.Name, true);



Aspose.Cells.Charts.DataLabels oDataLabels;
oDataLabels = chart.NSeries[dp].DataLabels;
oDataLabels.RotationAngle = 90;
oDataLabels.Position = Aspose.Cells.Charts.LabelPositionType.Above;
oDataLabels.IsAutomaticSize = true;
oDataLabels.ShowValue = true;
oDataLabels.TextFont.Name = “Arial”;
oDataLabels.TextFont.Size = 8;
test = oDataLabels.Text;



string dlText = Config.getConfigValue(“ChartDataLabelTemplate”, lang, true);
Int32 pointCount = chart.NSeries[dp].Points.Count;
int x = rStartCell.Row;
for (int p = 0; p < pointCount && x <= rEndCell.Row; p++)
{
DataLabels datalabel = chart.NSeries[dp].Points[p].DataLabels;
String labelText = null;
labelText = dlText.Replace("[!Value1]", wCells.GetCell(x, ptCol).StringValue);
labelText = labelText.Replace("[!Value2]", wCells.GetCell(x, cntCol).StringValue);
datalabel.Text = labelText;
x++;
}

chart.NSeries.IsColorVaried = true;
MyColor oChartClr = chartConfigInfo.ChartBackgroundColor;
if (!oChartClr.IsNamedColor && !ColorAdded2Palette(oChartClr.ColorString))
{
PaletteClrs.Add(oChartClr.ColorString);
wrkbook.ChangePalette(oChartClr.TheColor, 55);
}


//chart.ChartArea.Width = 10000;

// Set chart area properties
chart.ChartArea.Area.ForegroundColor = oChartClr.TheColor;
chart.ChartArea.Area.Formatting = Aspose.Cells.Charts.FormattingType.Custom;
chart.ChartArea.Border.IsVisible = false; // No borders
chart.ChartArea.Height = 10000;
//chart.ChartArea.Y = 400;


// Set chart’s plot area properties
chart.PlotArea.Area.ForegroundColor = oChartClr.TheColor;

chart.WallsAndGridlines2D = false;
chart.PlotArea.Border.IsVisible = false;
chart.PlotArea.Height = 3250;
chart.PlotArea.Width = 3400;
chart.PlotArea.X = 100;
chart.PlotArea.Y = 500;
chart.ShowLegend = false;


#region yAxis
chart.ValueAxis.IsVisible = true; // show the y-axis
chart.ValueAxis.MajorGridLines.IsVisible = false; // hide gridlines
chart.ValueAxis.MaxValue = 100;
//Y Axis Title
chart.ValueAxis.Title.Text = Config.getConfigValue(“yAxisTitle”, lang);
//Setting Series Labels
int nSeriesCount = chart.NSeries.Count;
SeriesCollection nSeries = chart.NSeries;
for (int n = 0; n < nSeriesCount; n++)
{
nSeries[n].Name = Config.getConfigValue(“series”, lang, Convert.ToString(n));
}
#endregion

#region xAxis
// Set the font of X-Axis
chart.CategoryAxis.TickLabels.Font.Name = “Arial”;
chart.CategoryAxis.TickLabels.Font.Size = 8;
// chart.CategoryAxis.TickLabels.Font.IsBold = true;

//X-Axis Labels
Cell binTitleCell = null;
binTitleCell = wCells.Find(“Bin”, binTitleCell, findOption);
int binColumnIndex = binTitleCell.Column;
int binRowCount = wrksheet.Cells.GetLastDataRow(binColumnIndex);
String categoryData = “{”;
String binID;
String binValue;
for (int row = 1; row <= binRowCount; row++)
{
binID = wrksheet.Cells.GetCell(row, binColumnIndex).StringValue;

binValue = Config.getConfigValue(“Bin”, lang, Convert.ToString(binID), true);
if (row < (binRowCount))
categoryData += binValue + “,”;
else
categoryData += binValue;
}
categoryData += “}”;
chart.NSeries.CategoryData = categoryData;

//X Axis Title
chart.CategoryAxis.Title.Text = Config.getConfigValue(“xAxisTitle”, lang);
chart.CategoryAxis.Title.TextVerticalAlignment = TextAlignmentType.Center;

// X-Axis TickMarkType
chart.CategoryAxis.MajorTickMark = TickMarkType.None;

#endregion

if (datapoint.DataColor.IsNamedColor && !ColorAdded2Palette(datapoint.DataColor.ColorString))
{
PaletteClrs.Add(datapoint.DataColor.ColorString);
wrkbook.ChangePalette(datapoint.DataColor.TheColor, iPaletteInx–);
}
chart.NSeries[dp].Area.ForegroundColor = datapoint.DataColor.TheColor;

}
#endregion

//Creating/Converting Excel to Chart Image File
#region ChartImageFile
//int chartIndex, iChartVals = 0;
//bool bCreateRow = true;
string oStartDir = AppDomain.CurrentDomain.BaseDirectory;
string sChartOutputDir = Config.getConfigValue(“ImageOutputPath”);
string sImgNameTemplate = Config.getConfigValue(“ImageTemplate”);



string sImgFName = sImgNameTemplate.Replace("[!Grade]", grade);
sImgFName = sImgFName.Replace("[!OrgId]", Mident);
sImgFName = sImgFName.Replace("[!ChartCode]", chartConfigInfo.Subject);
if (lang.Equals(“1”))
{
sImgFName = sImgFName.Replace("[!ChartCat]", chartConfigInfo.TitleEnglish);
}
else
{
sImgFName = sImgFName.Replace("[!ChartCat]", chartConfigInfo.TitleFrench);
}
string sDir = oStartDir + sChartOutputDir + Mident;
if (Directory.Exists(sDir) == false)
Directory.CreateDirectory(sDir);
sFileName = sDir + “\” + sImgFName;

ImageOrPrintOptions options = new ImageOrPrintOptions();

//Set Autofit options
options.IsCellAutoFit = false;

//Set Horizontal Resolution
options.HorizontalResolution = 300;

//Set Vertical Resolution
options.VerticalResolution = 300;

options.ImageFormat = System.Drawing.Imaging.ImageFormat.Jpeg;

//Set printing page type
options.PrintingPage = PrintingPageType.Default;


chart.ToImage(sFileName, options);
#endregion


//String sOutputPath = ConfigurationManager.AppSettings[“RootOutputPath”] + Config.getConfigValue(“MergeOutputPath”);
//wrkbook.Save(sOutputPath + “Test.xls”);
}
return sFileName;
}
4

Hi Brendan,


Thank you for sharing the source snippet, however, we were not able to execute it on our side to confirm the results against the latest build due to the dependency over the variables such as chartDataSet & chartConfigInfo. Could you be kind enough to remove these dependencies from your code to provide a standalone sample application that we could test? You may simulate the DataTable in your code and hard-code the values from chartConfigInfo for demonstration purposes.

Please also provide your feedback on the latest release.
5

Hi,


I’ve created a standalone project that illustrates both issues I’ve posted.

Thanks,

Brendan
6

I’ve also attached my output.

7

Hi Brendan,


Thank you for sharing the sample project.

We have evaluated the presented scenario while using the latest version of Aspose.Cells for .NET 8.6.1, and we are able to notice the issue, that is; borders of the chart bars do not render to the image, however, borders are visible in the resultant spreadsheet (attached). We have logged this case for the product team’s review under the ticket CELLSNET-44016. Please allow us some time to properly analyze the problem cause, and get back to you with updates in this regard.
8

Hi,


Any chance I can get an estimate on a fix? This is pretty crucial for us and ithas started to happen on more reports than just the ones I’ve mentioned earlier.

thanks,

Brendan
9

Hi Brendan,


Please note, the ticket logged earlier as CELLSNET-44016 is currently in analysis phase, and therefore we cannot comment on the release schedule for the fix unless we have investigated the matter. Please allow us some time for proper analysis. We will share our results here as soon as the investigation is complete.
10

Hi,

Thanks for using Aspose.Cells.

By default, the style of charts is same as excel 2007 or later version. The border of bar is none. You may meet your requirement by one of the following solution.

1) Sets the border of bars.

SeriesCollection nSeries = chart.NSeries;

for (int n = 0; n < nSeriesCount; n++)
{
//nSeries[n].Name = Config.getConfigValue(“series”, lang, Convert.ToString(n));

nSeries[n].Border.Color = Color.Black;
}

2) Creates 97-2003 format charts

Modify

Workbook wrkbook = new Workbook();

to

Workbook wrkbook = new Workbook(FileFormatType.Excel97To2003);