List Validation with Tables (ListObject) and Names Issue

Aspose.Cells Product Family
21

Hi Saurabh,


I am afraid, I was not able to achieve the desired results while using Aspose.Cells APIs therefore I have logged an investigative ticket CELLSNET-44428 for the product team to review this requirement and provide solution, if possible. As soon as we get any news in this regard, we will post here for your kind reference.

Thank you for your patience with us.
22

Thanks for it.

So do you know around how much time it will take as this is an urgent item to work on and deliver?



Thanks

23

Hi Saurabh,


Well, the ticket is currently pending for analysis and is in the queue with other priority tasks. Once we have completed the preliminary investigation of this scenario, we will be in a better position to comment if the said feature is already available or we need to implement it. If it is the later case, we require more time for the feasibility analysis as well as implementation.

We will keep you posted with updates in this regard.
24

Thanks for your quick response.

Is there a way I can hide-show tab in excel based on dropdown selection?



I have one more question is that Is there any way I can export chart to excel as a live chart not as an image?



Thanks

25

Hi,

Thanks for using Aspose.Cells.

Please try the following code for your needs.

C#

// Create a workbook object.
Workbook workbook = new Workbook();

// Get the first worksheet.
Worksheet worksheet = workbook.Worksheets[0];

// Create a range in the second worksheet.
Range range = worksheet.Cells.CreateRange(“C21”, “C24”);

// Name the range.
range.Name = “MyRange”;

// Fill different cells with data in the range.
range[0, 0].PutValue(“North”);
range[1, 0].PutValue(“South”);
range[2, 0].PutValue(“East”);
range[3, 0].PutValue(“West”);

ComboBox comboBox = worksheet.Shapes.AddComboBox(15, 0, 2, 0, 17, 64);
comboBox.InputRange = “=MyRange”;
comboBox.LinkedCell = “=B16”;
comboBox.SelectedIndex = 0;
Cell cell = worksheet.Cells[“B16”];
Style style = cell.GetStyle();
style.Font.Color = Color.White;
cell.SetStyle(style);

// comboBox.SelectedValue =
worksheet.Cells[“C16”].Formula = “=INDEX(Sheet1!$C$21:$C$24,$B$16,1)”;

// Put some data for chart source.
// Data Headers
worksheet.Cells[“D15”].PutValue(“Jan”);
worksheet.Cells[“D20”].PutValue(“Jan”);

worksheet.Cells[“E15”].PutValue(“Feb”);
worksheet.Cells[“E20”].PutValue(“Feb”);

worksheet.Cells[“F15”].PutValue(“Mar”);
worksheet.Cells[“F20”].PutValue(“Mar”);

worksheet.Cells[“G15”].PutValue(“Apr”);
worksheet.Cells[“G20”].PutValue(“Apr”);

worksheet.Cells[“H15”].PutValue(“May”);
worksheet.Cells[“H20”].PutValue(“May”);

worksheet.Cells[“I15”].PutValue(“Jun”);
worksheet.Cells[“I20”].PutValue(“Jun”);

// Data
worksheet.Cells[“D21”].PutValue(304);
worksheet.Cells[“D22”].PutValue(402);
worksheet.Cells[“D23”].PutValue(321);
worksheet.Cells[“D24”].PutValue(123);

worksheet.Cells[“E21”].PutValue(300);
worksheet.Cells[“E22”].PutValue(500);
worksheet.Cells[“E23”].PutValue(219);
worksheet.Cells[“E24”].PutValue(422);

worksheet.Cells[“F21”].PutValue(222);
worksheet.Cells[“F22”].PutValue(331);
worksheet.Cells[“F23”].PutValue(112);
worksheet.Cells[“F24”].PutValue(350);

worksheet.Cells[“G21”].PutValue(100);
worksheet.Cells[“G22”].PutValue(200);
worksheet.Cells[“G23”].PutValue(300);
worksheet.Cells[“G24”].PutValue(400);

worksheet.Cells[“H21”].PutValue(200);
worksheet.Cells[“H22”].PutValue(300);
worksheet.Cells[“H23”].PutValue(400);
worksheet.Cells[“H24”].PutValue(500);

worksheet.Cells[“I21”].PutValue(400);
worksheet.Cells[“I22”].PutValue(200);
worksheet.Cells[“I23”].PutValue(200);
worksheet.Cells[“I24”].PutValue(100);

// Dynamically load data on selection of Dropdown value
worksheet.Cells[“D16”].Formula = “=IFERROR(VLOOKUP($C$16,$C$21:$I$24,2,FALSE),0)”;
worksheet.Cells[“E16”].Formula = “=IFERROR(VLOOKUP($C$16,$C$21:$I$24,3,FALSE),0)”;
worksheet.Cells[“F16”].Formula = “=IFERROR(VLOOKUP($C$16,$C$21:$I$24,4,FALSE),0)”;
worksheet.Cells[“G16”].Formula = “=IFERROR(VLOOKUP($C$16,$C$21:$I$24,5,FALSE),0)”;
worksheet.Cells[“H16”].Formula = “=IFERROR(VLOOKUP($C$16,$C$21:$I$24,6,FALSE),0)”;
worksheet.Cells[“I16”].Formula = “=IFERROR(VLOOKUP($C$16,$C$21:$I$24,7,FALSE),0)”;

// Create Chart
int index = worksheet.Charts.Add(ChartType.Column, 0, 3, 12, 9);
Chart chart = worksheet.Charts[index];
chart.NSeries.Add("=‘Sheet1’!$D$16:$I$16", false);
chart.NSeries[0].Name = “=C16”;
chart.NSeries.CategoryData = “=$D$15:$I$15”;


workbook.Save(path + “dest.xlsx”);

26
Hi Saurabh,

Saurabh Shah:
Is there a way I can hide-show tab in excel based on dropdown selection?

I believe the above requirement can be accomplished using the VBA/macros, however, Aspose.Cells APIs do not provide the ability to execute the macros. If you search the internet you will find macro examples that could allow you to hide/un-hide a worksheet tab based on a control.

Saurabh Shah:
I have one more question is that Is there any way I can export chart to excel as a live chart not as an image?

To what format do you wish to export the chart? The current implementation allows to export the charts to images & PDF formats. Moreover, if you wish to export the chart to another spreadsheet, you may copy the worksheet containing the chart as well as its data source to another workbook.
27

Hi,

I am using .NET rdlc for chart and table to generating report.

So when I render rdlc and export to excel( custom code) it exports as an image so is that way I can export as a excel chart so when user clicks on chart it shows select data and other options.

28

Hi Shakil,
Sorry for misunderstanding. I am able to reply to here. but I didn’t see that it’s on 2nd page.

I sent my question to you in email.

Thanks

29

Hi,
I have implemented a code which you posted but I notice that it filters only one row means if you have multiple rows with north, it’s returning only first one.

30

Hi,

Thanks for using Aspose.Cells.

Saurabh Shah:
Hi,

I am not able to post on forum it shows it’s a duplicate post.



Thanks for this one. It will really help a lot.

So sending you an email.



Here I see that you are creating chart on a run time. but I have
around 6 charts and 6 tables on my report to generate. So I am creating
those in RDLC in .NET , render those one and export to excel ( custom
code). So how can I set ranges on run time when I export to excel. So
when I change dropdown value ,chart and tables need to be updated.



I have executed the code which we have provided previously in this post and attached the output excel file.

For your requirements, please provide me the generated excel file via RDLC. Then manually fix the generated excel file via Microsoft Excel and attach here these files.

1 - Excel file generated via your RDLC code
2 - The same excel file manually fixed by you via Microsoft Excel

Once we will get your final expected output excel file, we will be in a better position to replicate the similar thing via Aspose.Cells code.

31

Replied you in email which sample excel.

Thanks
Saurabh Shah

32

Hi,

Thanks for using Aspose.Cells.

Saurabh Shah:
Hi,

As per your reply, here is a sample excel sheet, We need to achieve
this type of behavior through ASPOSE if possible. if not we are planning
generate RDLC report for each item of dropdown and put all sheets
together in one workbook and show or hide based on dropdown selection.
Through RDLC , while exporting charts and tables it exports in excel as
an image so I am not sure if we can make it live chart through ASPOSE. I
find on some forum which mention that we can export chart as a live
chart through ASPOSE. If this is possible I don’t have to generate RDLC
for each item of dropdown.

Please see attached sample excel which we would like to achieve.



Yes, you can do it using Aspose.Cells for Reporting Services. Please see the following documentation article that explains this thing.

( http://www.aspose.com/docs/display/cellsreportingservices/Support+for+Editable+Charts )

I am studying your provided excel file. It has lots of formulas and it looks quite complex. To simplify things, could you provide me excel file which has just 2 or 3 charts that could be changed with your dropdown. It would also be helpful if you could also let me know the exact steps you took manually to create such a file with Microsoft Excel.

Also, let me know if your excel file is private, I want to attach it on the forum. You are not the owner of this thread, so if I will make your attachment private, then you and other people who subscribed to this thread will have access to your file.

It is always a good thing to create new thread instead of posting in the old threads. So making this thread private now is not an option.

33

Thanks for your reply.

Yes this excel is private so I sent you in email.
It was created by someone who is not working anymore with us. So I don’t know how to create it in excel.
I will have a look for editable charts through aspose reporting service.
Can you please do some research on existing excel which i provided that will be great to resolve the issue. .

Thanks
34

Hi,

Thanks for your posting and using Aspose.Cells.

Sure, I will do some research on it and try to replicate the sample excel file via Aspose.Cells APIs and update you asap.

35

Hi,

Thanks for considering Aspose.Cells.

I have looked into your sample excel file and found it is possible to do it with Aspose.Cells. You can create dropdown lists and charts with Aspose.Cells APIs. Your excel file actually depends on some excel formulas that change the values of the series range when the dropdown list value changes.

For example, if your Value Series points to range T1:T10, then it will never change even when the dropdown list value changes, but the values inside this range T1:T10 will be changed because of your excel formulas.

I have explained all these things in detail in the screenshots attached by me. So your task is to understand your excel formulas that are the source of all these dynamics. Once you will understand it, you will be able to create such dynamically changing charts with Aspose.Cells APIs.

36

Thanks a lot. I will see formulas for it.
Previously I asked question that can we export charts as editable excel chart if possible , I saw your reply that it’s possible through Aspose reporting service not through Aspose Cells for .Net. Correct me If I am wrong.

So I was looking into it to get first through tria version of reporting service but I don’t find any code for it.

So currently in code, I am rendering rdlc file to excel with below code.

string mimeType;
string encoding;
string fileNameExtension;
string[] streams;
Warning[] warnings;

            byte[] bytes = report.Render("EXCELOPENXML", null, out mimeType, out encoding, out fileNameExtension, out streams, out warnings);

var fileName = ReportPath + @"" + FileName + “.xlsx”;
using (var fs = new FileStream(serverfileName, FileMode.Create))
{
fs.Write(bytes, 0, bytes.Length);
fs.Close();
}

where report is Microsoft.Reporting.WebForms.LocalReport
and after that I write those bytes.
So how can I use Aspose reporting service to integrate with my code to render rdlc.

Thanks

37

Hi,

Thanks for your posting and using Aspose.Cells.

It is possible to make your excel report using Aspose.Cells for .NET, the only thing is you need to understand your formulas as I have shown in my screenshots.

I have looked into your question in the perspective of Aspose.Cells for Reporting Services and found, it does not have any programming interface that you could use in C# or VB.NET to export your RDL or RDLC files to excel files.

It only integrates with Microsoft Report Viewer in Local Mode. Please note Microsoft Report Viewer is a powerful .NET control allowing the use of RDL and RDLC reports in WinForms and ASP.NET applications.

Since you are exporting your RDLC via code, therefore it is not possible with Aspose.Cells for Reporting Services.

I have attached the help document, please read the section relating to what you can do with Aspose.Cells for Reporting Services.

  • 3.3. Integrate with Microsoft Report Viewer in Local Mode

As a workaround, please export your RDLC to XLS format via your sample code and then convert your XLS file to XLSX format using Aspose.Cells for .NET.

The following documentation article explains how you can export your RDL to XLS format.

( https://msdn.microsoft.com/en-us/library/ms251839%28v=vs.90%29.aspx )

From this article, it seems, if you integrate Aspose.Cells for Reporting Services with the ReportViewer control, then you can also directly convert your RDL or RDLC to XLSX format. Because, you will always use ReportViewer1.LocalReport.Render() method with different parameters.

38

Thanks for your response.

I have implemented a code as you suggested.
I am rendering report to XLS and then save it in XLSX format. Still it’s rendering charts and tables as an image not an excel chart.

Can you please suggest me something?

Thanks

39

Hi,

Thanks for your posting and using Aspose.Cells.

I am afraid, the solution of converting xls to xlsx will not work because the xls generated by report viewer has chart images not editable charts.

Also Aspose.Cells for Reporting Services when integrated with Report Viewer, does not generate editable charts but only generates chart images. Besides, it works only with Report Viewer 2005 and 2008.

Please check this article for your reference.

( Integrate with Microsoft Report Viewer in Local Mode|Documentation )

However, you can easily generate your charts on runtime using Aspose.Cells for .NET.

I have generated the first chart of your excel file with Aspose.Cells for .NET for demo purpose. The chart is almost similar to original chart and it also gets changed when the value of dropdown changes. You can make this chart 100% similar but it will need some efforts from your end. What you can do is check the values of your original chart in Debug or Quick Watch windows via Aspose.Cells APIs and then use those same values to create your chart.

Please see the following sample code and the screenshot showing the original and generated chart for your reference. I will also send you the output excel file via email.

It is always a good idea to create new threads for your issues so that we do not need to use emails or passwords or we could avoid unnecessary notifications to other users.

C#

//Load your workbook
Workbook wb = new Workbook(“Sample Excel.xlsx”);

//Access your worksheet
Worksheet ws = wb.Worksheets[“Brand Report”];

//This is your original chart
Chart c = ws.Charts[0];

//Please check the values of your chart in Quick Watch or Debug Window
for(int i=0; i<c.NSeries.Count; i++)
{
Debug.WriteLine(i + “: " + c.NSeries[i].Values);
Debug.WriteLine(i + “: " + c.NSeries[i].Type);
Debug.WriteLine(i + “: " + c.NSeries[i].Name);
Debug.WriteLine(i + “: " + c.NSeries[i].PlotOnSecondAxis);
Debug.WriteLine(”-------------------------------”);

}

//Now create your own chart after observing the values of your original chart
//in Quick Watch or Debug windows
CellArea ca = CellArea.CreateCellArea(“Q7”, “AE25”);

int idx = ws.Charts.Add(ChartType.LineWithDataMarkers, ca.StartRow, ca.StartColumn, ca.EndRow, ca.EndColumn);
Chart ch = ws.Charts[idx];

ch.NSeries.Add(”=‘Brand Report’!$AH$166:$AH$184”, true);
ch.NSeries.Add(“=‘Brand Report’!$AJ$166:$AJ$184”, true);
ch.NSeries.Add(“=‘Brand Report’!$AK$166:$AK$184”, true);
ch.NSeries.Add(“=‘Brand Report’!$AL$166:$AL$184”, true);
ch.NSeries.Add(“=‘Brand Report’!$AM$166:$AM$184”, true);
ch.NSeries.Add(“=‘Brand Report’!$AD$166:$AD$184”, true);
ch.NSeries.Add(“=‘Brand Report’!$AD$114:$AD$132”, true);

ch.NSeries[0].Type = ChartType.Area;
ch.NSeries[1].Type = ChartType.ColumnStacked;
ch.NSeries[2].Type = ChartType.ColumnStacked;
ch.NSeries[3].Type = ChartType.ColumnStacked;
ch.NSeries[4].Type = ChartType.ColumnStacked;
ch.NSeries[5].Type = ChartType.LineWithDataMarkers;
ch.NSeries[6].Type = ChartType.LineWithDataMarkers;

ch.NSeries[0].Name = “Forecast”;
ch.NSeries[1].Name = “=‘Brand Report’!$AJ$64”;
ch.NSeries[2].Name = “=‘Brand Report’!$AK$64”;
ch.NSeries[3].Name = “=‘Brand Report’!$AL$64”;
ch.NSeries[4].Name = “=‘Brand Report’!$AM$64”;
ch.NSeries[5].Name = “Avg Price TY”;
ch.NSeries[6].Name = “Avg Price LY”;

ch.SecondValueAxis.IsVisible = true;

ch.NSeries[0].PlotOnSecondAxis = false;
ch.NSeries[1].PlotOnSecondAxis = false;
ch.NSeries[2].PlotOnSecondAxis = false;
ch.NSeries[3].PlotOnSecondAxis = false;
ch.NSeries[4].PlotOnSecondAxis = false;
ch.NSeries[5].PlotOnSecondAxis = true;
ch.NSeries[6].PlotOnSecondAxis = true;

ch.Legend.Position = LegendPositionType.Bottom;

ch.NSeries.CategoryData = “=‘Brand Report’!$AI$166:$AI$184”;
ch.NSeries.SecondCategoryData = “=‘Brand Report’!$AI$166:$AI$184”;
ch.NSeries.IsColorVaried = false;

//Save the output
wb.Save(“output.xlsx”);

Debug Output
0: =‘Brand Report’!$AH$166:$AH$184
0: Area
0: Forecast
0: False
-------------------------------
1: =‘Brand Report’!$AJ$166:$AJ$184
1: ColumnStacked
1: =‘Brand Report’!$AJ$64
1: False
-------------------------------
2: =‘Brand Report’!$AK$166:$AK$184
2: ColumnStacked
2: =‘Brand Report’!$AK$64
2: False
-------------------------------
3: =‘Brand Report’!$AL$166:$AL$184
3: ColumnStacked
3: =‘Brand Report’!$AL$64
3: False
-------------------------------
4: =‘Brand Report’!$AM$166:$AM$184
4: ColumnStacked
4: =‘Brand Report’!$AM$64
4: False
-------------------------------
5: =‘Brand Report’!$AD$166:$AD$184
5: LineWithDataMarkers
5: Avg Price TY
5: True
-------------------------------
6: =‘Brand Report’!$AD$114:$AD$132
6: LineWithDataMarkers
6: Avg Price LY
6: True

40

Thanks for your quick feedback.
So now I able to see hoe chart is changing?
But I still have one doubt is that when we change dropdown value how does cell value changes.
I see formula in cell AJ166 like this :
=SUMIFS(ItemData!$K:$K,ItemData!$A:$A,$AA166,ItemData!$C:$C,$B$7)

So when I change dropdown value I see value in cell D5 got changed to be that index which item is selected. but how does D5 changes values in cell AJ166?

Thanks