Free Support Forum - aspose.com

Percentage Data in Excel Sheet

Hi,I wants to put percentage data with "%" symbol in the excel sheet for chart generation.I have tried to use custom cell style like

chart.ValueAxis.TickLabels.NumberFormat ="#,###\"%\""

AND

{

Worksheet dataSheet = wb.Worksheets[dataSheetIdx];
Style stylePerc = wb.Styles[wb.Styles.Add()];
stylePerc.Custom = "#,###.###\"%\"";
dataSheet.Cells[pair.Key[0], pair.Key[1]].SetStyle(stylePerc);

}

but it is not what we required.I wants to achieve same functionality which we can achieve by converting cell type as "Percentage" in Excel sheet.

Can you please provide me the solution for that.

Thanks,

Amit

Hi,


I am not completely sure regarding your issue. I have tested your case and specify custom Percentage number formatting for tick labels in the chart and it works fine, here is my complete sample code (runnable) and please find attached the output file which is rendered fine.

Sample code:
Workbook workbook = new Workbook();
//Set default font
Style style = workbook.DefaultStyle;
style.Font.Name = “Tahoma”;
workbook.DefaultStyle = style;

Worksheet sheet = workbook.Worksheets[0];
//Set the name of worksheet
sheet.Name = “Data”;
sheet.IsGridlinesVisible = false;

Cells cells = workbook.Worksheets[0].Cells;
//Put a value into a cell
cells[“A1”].PutValue(“Brand Name”);
cells[“B1”].PutValue(“Vitamin A”);
cells[“C1”].PutValue(“Vitamin B1”);
cells[“D1”].PutValue(“Vitamin B2”);
cells[“E1”].PutValue(“Vitamin C”);
cells[“F1”].PutValue(“Vitamin D”);
cells[“G1”].PutValue(“Vitamin E”);

cells[“A2”].PutValue(“Brand A”);
cells[“B2”].PutValue(.10);
cells[“C2”].PutValue(.10);
cells[“D2”].PutValue(.10);
cells[“E2”].PutValue(.80);
cells[“F2”].PutValue(.10);
cells[“G2”].PutValue(.70);

cells[“A3”].PutValue(“Brand B”);
cells[“B3”].PutValue(.80);
cells[“C3”].PutValue(.75);
cells[“D3”].PutValue(.80);
cells[“E3”].PutValue(.10);
cells[“F3”].PutValue(.50);
cells[“G3”].PutValue(.15);

cells[“A4”].PutValue(“Brand C”);
cells[“B4”].PutValue(.40);
cells[“C4”].PutValue(.25);
cells[“D4”].PutValue(.40);
cells[“E4”].PutValue(.55);
cells[“F4”].PutValue(.30);
cells[“G4”].PutValue(.10);

int sheetIndex = workbook.Worksheets.Add();
Worksheet sheet2 = workbook.Worksheets[sheetIndex];
//Set the name of worksheet
sheet2.Name = “Chart”;
//Create chart
int chartIndex = sheet2.Charts.Add(ChartType.Bar, 1, 1, 25, 10);
Chart chart = sheet2.Charts[chartIndex];
//Set properties of chart
chart.IsRectangularCornered = false;
//Set properties of chart title
chart.Title.Text = “Nutritional Analysis”;
chart.Title.TextFont.Color = Color.Black;
chart.Title.TextFont.IsBold = true;
chart.Title.TextFont.Size = 12;
//Set properties of nseries
chart.NSeries.Add(“Data!B2:G4”, false);
chart.NSeries.CategoryData = “Data!B1:G1”;
chart.ValueAxis.TickLabels.NumberFormat = “0.0%”;
chart.PlotArea.Area.ForegroundColor = Color.Red;

for (int i = 0; i < chart.NSeries.Count; i++)
{
chart.NSeries[i].Name = cells[“A” + (i + 2).ToString()].Value.ToString();
}

workbook.Save(“e:\test2\percentageRadarChartTest.xlsx”);

If you still have any confusion or issue, please give us your complete sample code and attach your output Excel file. Also, attach your expected Excel file that you may create in MS Excel manually. We will check it soon.


Moreover, we recommend you to kindly see the topic if you need to format cells for your desired formatting for your complete reference:
http://www.aspose.com/docs/display/cellsnet/Setting+Display+Formats+of+Numbers++and++Dates

Thank you.

Hi,

Thanks for your response. The solution which you provided shows the value axis tick label value in percentage. But when we check the data in excel sheet it is like ".6", ".7" But we wants these values to be like "60%","70%".

In excel sheet if we put data like ".6" , ".7" and then convert the cell type as Percentage. It will automatically convert them into "60%","70%". I need same kind of functionality. Instead of setting the tick lavel value as Percentage.

Thanks,

Amit

Hi,


I have modified my previous code to accomplish your needs, here is the updated code (see the lines of code in Bold).

Sample code:

Workbook workbook = new Workbook();
//Set default font
Style style = workbook.DefaultStyle;
style.Font.Name = “Tahoma”;
workbook.DefaultStyle = style;

Worksheet sheet = workbook.Worksheets[0];
//Set the name of worksheet
sheet.Name = “Data”;
sheet.IsGridlinesVisible = false;

Cells cells = workbook.Worksheets[0].Cells;
//Put a value into a cell
cells[“A1”].PutValue(“Brand Name”);
cells[“B1”].PutValue(“Vitamin A”);
cells[“C1”].PutValue(“Vitamin B1”);
cells[“D1”].PutValue(“Vitamin B2”);
cells[“E1”].PutValue(“Vitamin C”);
cells[“F1”].PutValue(“Vitamin D”);
cells[“G1”].PutValue(“Vitamin E”);

cells[“A2”].PutValue(“Brand A”);
cells[“B2”].PutValue(.10);
cells[“C2”].PutValue(.10);
cells[“D2”].PutValue(.10);
cells[“E2”].PutValue(.80);
cells[“F2”].PutValue(.10);
cells[“G2”].PutValue(.70);

cells[“A3”].PutValue(“Brand B”);
cells[“B3”].PutValue(.80);
cells[“C3”].PutValue(.75);
cells[“D3”].PutValue(.80);
cells[“E3”].PutValue(.10);
cells[“F3”].PutValue(.50);
cells[“G3”].PutValue(.15);

cells[“A4”].PutValue(“Brand C”);
cells[“B4”].PutValue(.40);
cells[“C4”].PutValue(.25);
cells[“D4”].PutValue(.40);
cells[“E4”].PutValue(.55);
cells[“F4”].PutValue(.30);
cells[“G4”].PutValue(.10);


//Create a style object to apply Percentage formatting to the data in the range B2:G4
Style style2 = workbook.CreateStyle();
style2.Custom = “0.0%”;
cells.CreateRange(“B2:G4”).SetStyle(style2);



int sheetIndex = workbook.Worksheets.Add();
Worksheet sheet2 = workbook.Worksheets[sheetIndex];
//Set the name of worksheet
sheet2.Name = “Chart”;
//Create chart
int chartIndex = sheet2.Charts.Add(ChartType.Bar, 1, 1, 25, 10);
Chart chart = sheet2.Charts[chartIndex];
//Set properties of chart
chart.IsRectangularCornered = false;
//Set properties of chart title
chart.Title.Text = “Nutritional Analysis”;
chart.Title.TextFont.Color = Color.Black;
chart.Title.TextFont.IsBold = true;
chart.Title.TextFont.Size = 12;
//Set properties of nseries
chart.NSeries.Add(“Data!B2:G4”, false);
chart.NSeries.CategoryData = “Data!B1:G1”;
chart.PlotArea.Area.ForegroundColor = Color.Red;

for (int i = 0; i < chart.NSeries.Count; i++)
{
chart.NSeries[i].Name = cells[“A” + (i + 2).ToString()].Value.ToString();
}

workbook.Save(“e:\test2\percentageRadarChartTest.xlsx”);

Let me know if you have any other issue.

Thank you.