Accessing Named Ranges

Aspose.com » Public Forums for Customers an... » Aspose.Cells Product Family » Re: Accessing Named Ranges

Last post 09-06-2010, 3:19 AM by Salman Shakeel. 7 replies.


	Sort Posts: Previous Next

09-02-2010, 2:00 PM 256874

JohnHennesey
Joined on 08-31-2010
Posts 17

Accessing Named Ranges

I am trying to reference a named range created in my excel sheet - but am unable to find any documentation for .Net on how to do this. I see create, but no get. Is this possbile? The scenario : I have a template .xls, load it into a memorystream, and want to substitute values into the named ranges before rendering it out to the client. I'm sure it's possible, but I just can't seem to find it.

I am using the GridWeb control. ??

Thanks,

John

09-02-2010, 2:54 PM 256887 in reply to 256874

Salman Shakeel
Joined on 01-08-2010
Posts 1,007

Re: Accessing Named Ranges

Hi John,

You can use following to get the name range.

Range[] range = workbook.Worksheets.GetNamedRanges();

For details, please follow the links below:

http://www.aspose.com/documentation/.net-components/aspose.cells-for-.net/named-ranges.html

http://www.aspose.com/documentation/.net-components/aspose.cells-for-.net/named-ranges-1.html

Thanks,

Sincere Regards,

Salman Shakeel
Support Developer,
Aspose Sialkot Team
Contact Us

09-02-2010, 3:09 PM 256888 in reply to 256887

JohnHennesey
Joined on 08-31-2010
Posts 17

Re: Accessing Named Ranges

Thank you for your response - I do not see this with the GridWeb control. Here is my code:

GridWeb1.WebWorksheets.ImportExcelFile(Server.MapPath("Filename.xlsx"));

and from here I do not see a names property...

GridWeb.WebWorksheets.Name - missing.

Thank you,

John

09-03-2010, 1:57 AM 256919 in reply to 256888

Salman Shakeel
Joined on 01-08-2010
Posts 1,007

Re: Accessing Named Ranges

Attachment: Present (inaccessible)

Aspose.Cells.GridWeb v2.3.1.2009.zip

Hi John,

Named Range feature has now been supported. Please use the updated version of Aspose.Cells.GridWeb v2.3.1.2009. Two new classes (Name and NameCollectioin) have been introduced. Following code snippet will help you.

GridWeb1.WebWorksheets.Clear();
GridWeb1.WebWorksheets.ImportExcelFile(@"c:\excel\test.xls");
GridWeb1.WebWorksheets[0].Cells["g6"].Formula = "=sum(hh)";

int index = GridWeb1.WebWorksheets.Names.Add("zz", "sheet1!a2:b5");
Name name = GridWeb1.WebWorksheets.Names[index];
GridWeb1.WebWorksheets[0].Cells["g7"].Formula = "=sum(zz)";
GridWeb1.WebWorksheets.RunAllFormulas();
GridWeb1.WebWorksheets.SaveToExcelFile(@"c:\excel\test1.xls");

Thanks,

Sincere Regards,

Salman Shakeel
Support Developer,
Aspose Sialkot Team
Contact Us

09-03-2010, 8:44 AM 256982 in reply to 256919

JohnHennesey
Joined on 08-31-2010
Posts 17

Re: Accessing Named Ranges

That was fast! Thank you very much for your response. Is there any way to update the named range (natively with the Name object returned), or do I have to parse the RefersTo property? I poked around trying to cast it to different things and didn't find any easy way. Here is the scenario:

When a user goes to the aspx page it loads the .xlsx into a gridweb control, and I need to then put values into the named ranges from the current object they are working with. For example the spreadsheet may have a named range called "FirstName", and I would like to populate it with the customers First Name. PsuedoCode would look like:

GridWeb1.WebWorksheets.Names["FirstName"].PutValue(customer.FirstName)

whereas PutValue could also be StringText, Text, etc. And if in the case of a named range referring to multiple cells it could return a Cells collection which would force:

GridWeb1.WebWorksheets.Names["FirstName"].Cells[0,0].PutValue(customer.FirstName)

I think the 2nd option makes more sense as a range truly is a collection.

Thanks for your help!

John

09-03-2010, 12:56 PM 257029 in reply to 256919

JohnHennesey
Joined on 08-31-2010
Posts 17

Re: Accessing Named Ranges

I also now get this error when I placed the updated assembly in my project. I reverted and tried again twice in hopes that it would work - no luck (with it working correctly when I reverted).

Any suggestions?

Server Error in '/[Application Name]' Application.

Unable to serialize the session state. In 'StateServer' and 'SQLServer' mode, ASP.NET will serialize the session state objects, and as a result non-serializable objects or MarshalByRef objects are not permitted. The same restriction applies if similar serialization is done by the custom session state store in 'Custom' mode.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Web.HttpException: Unable to serialize the session state. In 'StateServer' and 'SQLServer' mode, ASP.NET will serialize the session state objects, and as a result non-serializable objects or MarshalByRef objects are not permitted. The same restriction applies if similar serialization is done by the custom session state store in 'Custom' mode.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[SerializationException: Type 'Aspose.Cells.GridWeb.Data.NameCollection' in Assembly 'Aspose.Cells.GridWeb, Version=2.3.1.2009, Culture=neutral, PublicKeyToken=699ccf88ff76f11a' is not marked as serializable.]

System.Runtime.Serialization.FormatterServices.InternalGetSerializableMembers(RuntimeType type) +7736363

System.Runtime.Serialization.FormatterServices.GetSerializableMembers(Type type, StreamingContext context) +258

System.Runtime.Serialization.Formatters.Binary.WriteObjectInfo.InitMemberInfo() +111

System.Runtime.Serialization.Formatters.Binary.WriteObjectInfo.InitSerialize(Object obj, ISurrogateSelector surrogateSelector, StreamingContext context, SerObjectInfoInit serObjectInfoInit, IFormatterConverter converter, ObjectWriter objectWriter) +161

System.Runtime.Serialization.Formatters.Binary.WriteObjectInfo.Serialize(Object obj, ISurrogateSelector surrogateSelector, StreamingContext context, SerObjectInfoInit serObjectInfoInit, IFormatterConverter converter, ObjectWriter objectWriter) +51

System.Runtime.Serialization.Formatters.Binary.ObjectWriter.Write(WriteObjectInfo objectInfo, NameInfo memberNameInfo, NameInfo typeNameInfo) +7634340

System.Runtime.Serialization.Formatters.Binary.ObjectWriter.Serialize(Object graph, Header[] inHeaders, __BinaryWriter serWriter, Boolean fCheck) +461

System.Runtime.Serialization.Formatters.Binary.BinaryFormatter.Serialize(Stream serializationStream, Object graph, Header[] headers, Boolean fCheck) +134

System.Web.Util.AltSerialization.WriteValueToStream(Object value, BinaryWriter writer) +1577

[HttpException (0x80004005): Unable to serialize the session state. In 'StateServer' and 'SQLServer' mode, ASP.NET will serialize the session state objects, and as a result non-serializable objects or MarshalByRef objects are not permitted. The same restriction applies if similar serialization is done by the custom session state store in 'Custom' mode.]

System.Web.Util.AltSerialization.WriteValueToStream(Object value, BinaryWriter writer) +1662

System.Web.SessionState.SessionStateItemCollection.WriteValueToStreamWithAssert(Object value, BinaryWriter writer) +34

System.Web.SessionState.SessionStateItemCollection.Serialize(BinaryWriter writer) +606

System.Web.SessionState.SessionStateUtility.Serialize(SessionStateStoreData item, Stream stream) +239

System.Web.SessionState.SessionStateUtility.SerializeStoreData(SessionStateStoreData item, Int32 initialStreamSize, Byte[]& buf, Int32& length) +72

System.Web.SessionState.OutOfProcSessionStateStore.SetAndReleaseItemExclusive(HttpContext context, String id, SessionStateStoreData item, Object lockId, Boolean newItem) +87

System.Web.SessionState.SessionStateModule.OnReleaseState(Object source, EventArgs eventArgs) +560

System.Web.SyncEventExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +68

System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +75

09-05-2010, 6:41 PM 257127 in reply to 257029

Salman Shakeel
Joined on 01-08-2010
Posts 1,007

Re: Accessing Named Ranges

Hi John,

Please forward us the code in a sample project so that we should investigate the issue.

Thanks,

Sincere Regards,

Salman Shakeel
Support Developer,
Aspose Sialkot Team
Contact Us

09-06-2010, 3:19 AM 257165 in reply to 257127

Salman Shakeel
Joined on 01-08-2010
Posts 1,007

Re: Accessing Named Ranges

Attachment: Present (inaccessible)

Hi Jhon,

Please use the updated version Aspose.Cells.GridWeb v2.3.1.2010 attached. Serialization in session state mode has been fixed. A new method "ParseRefersTo" has been introduced to parse the name object. Following code snippet will help you.

int index = GridWeb1.WebWorksheets.Names.Add("zz", "Sheet2!B3:G4");

Name name = GridWeb1.WebWorksheets.Names[index];
string sheetName;
int row1, row2, col1, col2;
name.ParseRefersTo(out sheetName, out row1, out col1, out row2, out col2);
   if (row1 >= 0)
            {
                for (int row = row1; row <= row2; row++)
                {
                    for (int col = col1; col <= col2; col++)
                    {
                        GridWeb1.WebWorksheets[sheetName].Cells[row, col].PutValue(row * col);
                    }
                }
            }

Thanks,

Sincere Regards,

Salman Shakeel
Support Developer,
Aspose Sialkot Team
Contact Us