List Validation with Tables (ListObject) and Names Issue

Aspose.Cells Product Family
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

41

I see that it’s using SUMIFS which can take multiple parameter as a where clause and it’s referring to dropdown cell. So it looks easy to create a chart on I see that tables have really complicated formula. Can we simplified it?

42

Hi,

Thanks for your posting and using Aspose.Cells.

You will be able to understand your formulas after some trial and error methods and playing with them.

You can use the following MS-Excel features.

  • Find Options
  • Show Formulas
  • Evaluate Formula

The third one i.e Evaluate Formula is most important. It will help you understand how your formulas are being evaluated and how you can simplify them. I have attached the screenshots for your reference.

There are two other useful features which will be helpful for you.

  • Trace Precedents (It will tell you the parent cells of your formulas)
  • Trace Dependents (It will tell you the child cells of your current cell or formula)

Please see this article for your reference.

( Display the relationships between formulas and cells - Microsoft Support )

43

Hi,

To resolve my issue as mentioned earlier, I am creating multiple sheet and put it together in workbook. And while creating multiple worksheet, I am adding drowdown in each sheet to keep all sheets consistent.

here is my code for it.



int columnIndex = workbook.Worksheets.GetRangeByName(“MyRange”).FirstColumn;

workbook.Worksheets[0].Cells.HideColumn(columnIndex);

var comboBox = workbook.Worksheets[0].Shapes.AddComboBox(5, 0, 5, 0, 25, 250);

comboBox.InputRange = “=MyRange”;

comboBox.LinkedCell = “=A6”;

comboBox.SelectedIndex = arry.IndexOf(item);

comboBox.Name = “ddlName”;

Cell cell = workbook.Worksheets[0].Cells[“A6”];

Style style = cell.GetStyle();

style.Font.Color = Color.AliceBlue;

cell.SetStyle(style);



Now I need to write a VBA or macro code to activate sheet based on dropdown selection and my sheet names are same as dropdown values.



I am trying like this but it’s not working. This code is also in for loop to create macro on each sheet.

Can you give me some suggestion also I am attaching an excel with this one?



System.Text.StringBuilder sb;

// Aspose.Cells.Vba.VbaModule xlModule;



sb = new System.Text.StringBuilder();

//build string with module code

sb.Append(“Sub ddlName_Change()” + “\n”);

sb.Append(“Dim dd As DropDown” + “\n”);

sb.Append("Set dd = " + workbook.Worksheets[0].Name + “.Shapes(“ddlName”).OLEFormat.Object” + “\n”);

sb.Append(“Sheets(dd.List(dd.ListIndex)).Activate” + “\n”);

sb.Append(“End Sub”);



int idx = workbook.VbaProject.Modules.Add(workbook.Worksheets[0]);

Aspose.Cells.Vba.VbaModule module = workbook.VbaProject.Modules[idx];

//add the cmbbox macro to the spreadsheet

module.Codes = sb.ToString();

44

Hi,


Thanks for the template file and sample code with details.

Well, I have evaluated your template file a bit and found the vba codes/macro is not assigned/attached to the form control (dropdown list), so you got to attach the macro to the control to get it working. See the document for your reference here:

Hope, this helps a bit.

Thank you.

45

Thanks a lot. It worked for me.

I am facing one more issue is that when I am looping through I am rendering report which give me a byte[]. So I need to create a workbook with different worksheets but I see that I can’t write a bytes to worksheet. Every time I need to create a workbook and after that I need to merge all workbooks together. Is there any way that initially I create a n empty workbook and after that in for loop I will add worksheet with bytes written to it.

Here is my code in for loop.

using (var ms = new MemoryStream())
{
ms.Write(bytes, 0, bytes.Length);
ms.Position = 0;
var workbook = new Workbook(ms);

workbookList.Add(workbook);
}

46

Hi,


Good to know that your original issue is sorted out now.

Regarding your recent question, I am not entirely certain about your needs. Also, it is always better to start a new thread for every distinct problem/ issue or query. Anyways, what I understand from your post is you want to merge/ combine Excel workbooks (each Workbook might have different worksheets in it) into single workbook. I recommend you to try using Workbook.Combine() method for it, see the document for your reference:
http://www.aspose.com/docs/display/cellsnet/Combine+Multiple+Workbooks+into+a+Single+Workbook

Alternatively, you may try copying worksheets (using Worksheet.Copy() method) in each workbook to merge into an empty Workbook, see the thread (although the code segment is in JAVA but you may easily convert it to .NET):
https://forum.aspose.com/t/87204

Let us know if I can be of any further help.

Thank you.

47

Thanks for your quick reply.

My question is that , I am rendering report in for loop with all filtered dataset, which returns me bytes[] . So is it possible that I can write this bytes in excel worksheet . So At end of for loop I will have only one workbook with different sheets.

Currently I don’t see any methods which writes bytes[] to worksheet, I have to create workbook every time and after for loop completion , I need to merge or copy it as you suggested.

But I want to do like this way.

create a workbook outside for loop.

In for loop add new worksheet every time and writes byte[] in worksheet. So I don’t have to do copy or merge.

48

Hi Saurabh,


I am afraid, you cannot directly create a Worksheet from an array of bytes. However, if your byte array contains data in any of the supported formats (array, DataTable, DataSet and so on), you can import the data into a Worksheet by adding a new instance to the WorksheetCollection. In case the byte array contains a spreadsheet, you have to create a new instance of Workbook from it and then use Worksheet.Copy operation to copy the data worksheet(s) from newly created Workbook to source spreadsheet as suggested by Amjad.

Hope this helps.