List Validation with Tables (ListObject) and Names Issue

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