How can I show large values in millions,Thousands etc?

Aspose.Cells Product Family
1

I have large numbers as data in my aspose chart.How can I show that values in millions ?

Eg : 3209496298.35049 should be show as 3,209 M

Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];

worksheet.Cells[0, 0].PutValue(“Apple”);
worksheet.Cells[0, 1].PutValue(3249);
worksheet.Cells[0, 2].PutValue(3209496298.35049);

worksheet.Cells[1, 0].PutValue(“Orange”);
worksheet.Cells[1, 1].PutValue(12123123);
worksheet.Cells[1, 2].PutValue(20);
int chartIndex = worksheet.Charts.Add(ChartType.Column3DClustered, 5, 0, 15, 5);

Chart chart = worksheet.Charts[chartIndex];

chart.Title.Text = “Quantity for period 6/2016”;
chart.Title.Font.Size = 6;

chart.NSeries.CategoryData = “A1:A2”;
chart.CategoryAxis.TickLabels.Font.Size = 6;

chart.ChartArea.BackgroundMode = BackgroundMode.Transparent;
chart.NSeries.Add(“B1:B2”, true);
chart.NSeries.Add(“C1:C2”, true);
chart.NSeries[0].XValues = “A1:A2”;
chart.NSeries[1].XValues = “A1:A2”;
chart.NSeries[0].Name = “Quantity Balance”;
chart.NSeries[1].Name = “Net Available”;
chart.NSeries[0].DataLabels.ShowValue = true;
chart.NSeries[0].DataLabels.Font.Size = 6;
chart.NSeries[0].Area.ForegroundColor = Color.Red;
chart.NSeries[1].DataLabels.ShowValue = true;
chart.NSeries[1].DataLabels.Font.Size = 6;
chart.NSeries[1].Area.ForegroundColor = Color.Green;
chart.Legend.Position = LegendPositionType.Bottom;
chart.Legend.Font.Size = 6;
ImageOrPrintOptions options = new ImageOrPrintOptions()
{
VerticalResolution = 173,
HorizontalResolution = 200,
SmoothingMode = System.Drawing.Drawing2D.SmoothingMode.HighQuality
};
Image chartImage = chart.ToImage(options);
byte[] newImage = ConvertImageToByte(chartImage);
string image = Convert.ToBase64String(newImage);

dataUrl = “data:image/png;base64,” + image;

This Topic is created by Amjad_Sahi using the Email to Topic plugin.

2

@anishsm.

Well, you got to use the formulas to accomplish the task, see the following code segment for your reference:
e.g
Sample code:

 Workbook workbook = new Workbook();
            Worksheet worksheet = workbook.Worksheets[0];

            worksheet.Cells[0, 0].PutValue("Apple");
            worksheet.Cells[0, 1].PutValue(3249);
            worksheet.Cells[0, 2].PutValue(3209496298.35049);
            var formula1 = "=ROUND((C1/1000000),1)&\" M\"";
            var result1 = worksheet.CalculateFormula(formula1);
            worksheet.Cells[0, 2].PutValue(result1);

Hope, this helps a bit.

3

@anishsm,

I do not think you may directly format data as “xxxx M” because MS Excel does not know it. Also, it (e.g 3,209 M) will take it as string value and not numeric value. Anyways, if you need to add your desired custom labels for the column/bars, you may try to add the following lines of code before rendering to image:
e.g
Sample code:

chart1.PlotArea.Border.FormattingType = ChartLineFormattingType.None;

int cnt = 1;
foreach (Series s in chart1.NSeries)
{
int pointCount = s.Points.Count;
for (int i = 0; i < pointCount; i++)
{
ChartPoint pointIndex = s.Points[i];

                double d = worksheet1.Cells[i, cnt].DoubleValue; 

                var formula1 = "=ROUND((" + d + "/1000000),1)&\" M\"";
                var result1 = worksheet1.CalculateFormula(formula1);
                pointIndex.DataLabels.Text = result1.ToString();
            }

            cnt++;


        }

            ImageOrPrintOptions options1 = new ImageOrPrintOptions()
            {
                VerticalResolution = 173,
                HorizontalResolution = 200,
                SmoothingMode = System.Drawing.Drawing2D.SmoothingMode.HighQuality
            };

Hope, this helps a bit.

4

It worked fine.How can i apply this formula in Y axis scale values?

5

@anishsm,

The Y-axis scale values comes from the source cells, so you got to update those source cells. I am not sure if you could change Y-axis scale values according to your custom needs, it might not be possible. Could you create the chart with your (custom) desired Y axis scale values in MS Excel manually, save the file and provide us here, we will check on how to do it via Aspose.Cells APIs. If MS Excel could accomplish the task, then Aspose.Cells can do it.

6

demo.zip (8.6 KB)
Y-axis data should also be like 32 M etc

7

@anishsm,

I requested your to provide a sample file having your desired chart (with your custom ticket labels shown on Y-axis) but you have not provided so far. Anyways, in MS Excel, it looks like showing values like " ### M" may not be possible directly. But I think you may try to add a line to your code before rendering to image file format, it will somehow show data in millions (unit) if not in your exact format, like “### M”:
e.g
Sample code:

chart1.ValueAxis.DisplayUnit = DisplayUnitType.Millions;

Hope, this helps a bit.

8

This showed values in Millions. But actually I don’t know how to created an excel with desired formatted Y-axis

9

@anishsm,

I think this is not possible in MS Excel as I also told you earlier. If you know or find some way to do it in MS Excel, let us know with details and sample file. Please note, if something is not possible in MS Excel, Aspose.Cells also cannot do it as after all it has to follow MS Excel standards and specifications for the file formats.