We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy.
Unfortunately, activation email could not send to your email. Please try again.

Using XlsIO in ASPX

Thread ID:

Created:

Updated:

Platform:

Replies:

57881 Mar 13,2007 10:33 AM Mar 15,2007 09:17 AM ASP.NET Web Forms (Classic) 6
loading
Tags: XlsIO
Dave
Asked On March 13, 2007 10:33 AM

Morning,
I have a need to programmatically manipulate an Excel object to some very specific requirements. I have done this with Excel interop and the customer liked what they saw however we all know the issues with interop. Thus we purchased Syncfusion but can’t seem to get the same kind of detailed control over the Syncfusion’s Excel object.

When the data is returned form the database, I need to add a row if there are certain type of records, if not I don’t need to add this header row. The rest of the records returned can be one of three types all needing their own header row. Attached is a sample spreadsheet. They want it exactly as it is shown. Like I previously mentioned, I did a proof of concept using interop and they liked the results so I know it can be done.

Using interop I used this:

//Excel Objects
Microsoft.Office.Interop.Excel.Application oXLApplication;
Microsoft.Office.Interop.Excel._Workbook oWorkBook;
Microsoft.Office.Interop.Excel._Worksheet oSheet;

//Range objects for cell manipulation
Microsoft.Office.Interop.Excel.Range oRng;
Microsoft.Office.Interop.Excel.Range oCV;
Microsoft.Office.Interop.Excel.Range[] oRngCV = new Microsoft.Office.Interop.Excel.Range[0];

Using Syncfusion:

GroupingGridExcelConverterControl ggExcelConverter = new GroupingGridExcelConverterControl();
ExcelEngine oExcelEngine = new ExcelEngine();
IApplication oApplication = new ExcelEngine().Excel;
IWorkbook oWorkbook = new ExcelEngine().Excel.Workbooks.Add();
IWorksheet oSheet = new ExcelEngine().Excel.ActiveSheet;

These declarations seem to work fine. This issue is when I try to declare the Range objects. I need two range objects and a range array. Any assistance would be greatly appreciated.

Thanks,
Dave

SampleReport0.zip

Administrator [Syncfusion]
Replied On March 13, 2007 12:11 PM

First of all your code is not good. It looks like you are new in C#. There is no need to create ExcelEngine 4 times. You need to create it once and store somewhere so your code will look like
ExcelEngine oExcelEngine = new ExcelEngine();
IApplication oApplication = oExcelEngine.Excel;
IWorkbook oWorkbook = oApplication.Workbooks.Add();
IWorksheet oSheet = oWorkbook.ActiveSheet; // or IWorksheet oSheet = oWorkbook.Worksheets[0];

And now regarding your question:
IRange range; - declares single range variable. Then you can assign some value to this variable using something like this
range = oSheet.Range[1,1]; or range = oSheet.Range["A1"];

and to declare array you can simply use:
IRange[] array = new IRange[count];

Melba Winshia [Syncfusion]
Replied On March 14, 2007 07:19 AM

Hi Dave,

Thanks for your interest in Essential XlsIO.

I am not sure if I understand your requirements correctly. If you want to populate a range of cells with the data contained within an object type you can use the following code snippet

[C#]

Object[,] data;
IRange range;

data = new Object[2,5];

// Formatting
range = sheet.Range[1, 1, 9,9];
range.CellStyle.Font.Size = 8;
range.CellStyle.Font.FontName = "Verdana";

// Import data
sheet.ImportArray(data, 1, 1);

If this is not the case could you please provide more details regarding "I need two range objects and a range array"? This would help us in investigating further on this issue.

Kindly let me know if you have any other questions.

Thanks,
Melba

David
Replied On March 14, 2007 08:52 AM

Actually I tried that and kept getting object not set to reference error.

>First of all your code is not good. It looks like you are new in C#. There is no need to create ExcelEngine 4 times. You need to create it once and store somewhere so your code will look like
ExcelEngine oExcelEngine = new ExcelEngine();
IApplication oApplication = oExcelEngine.Excel;
IWorkbook oWorkbook = oApplication.Workbooks.Add();
IWorksheet oSheet = oWorkbook.ActiveSheet; // or IWorksheet oSheet = oWorkbook.Worksheets[0];

And now regarding your question:
IRange range; - declares single range variable. Then you can assign some value to this variable using something like this
range = oSheet.Range[1,1]; or range = oSheet.Range["A1"];

and to declare array you can simply use:
IRange[] array = new IRange[count];

David
Replied On March 14, 2007 09:06 AM

Melba,
Boy do I feel embarrassed! After looking at my code closer, I use a regular array and not a range at all. Also looking closer at my code I discovered the range array was declared and populated but never used. I had put the Excel prototype together in a hurry and left some code fragments in there and didn't clean them up before using it as a model for developing the Essential XlsIO solution.

Let me work on this more and I'll let you know how it goes.

Dave

>Hi Dave,

Thanks for your interest in Essential XlsIO.

I am not sure if I understand your requirements correctly. If you want to populate a range of cells with the data contained within an object type you can use the following code snippet

[C#]

Object[,] data;
IRange range;

data = new Object[2,5];

// Formatting
range = sheet.Range[1, 1, 9,9];
range.CellStyle.Font.Size = 8;
range.CellStyle.Font.FontName = "Verdana";

// Import data
sheet.ImportArray(data, 1, 1);

If this is not the case could you please provide more details regarding "I need two range objects and a range array"? This would help us in investigating further on this issue.

Kindly let me know if you have any other questions.

Thanks,
Melba

David
Replied On March 14, 2007 09:17 AM

Strange - I tried this again and now it doesn't throw an error. I must have missed something the first time. Thanks for the advice!


Dave

>Actually I tried that and kept getting object not set to reference error.

>First of all your code is not good. It looks like you are new in C#. There is no need to create ExcelEngine 4 times. You need to create it once and store somewhere so your code will look like
ExcelEngine oExcelEngine = new ExcelEngine();
IApplication oApplication = oExcelEngine.Excel;
IWorkbook oWorkbook = oApplication.Workbooks.Add();
IWorksheet oSheet = oWorkbook.ActiveSheet; // or IWorksheet oSheet = oWorkbook.Worksheets[0];

And now regarding your question:
IRange range; - declares single range variable. Then you can assign some value to this variable using something like this
range = oSheet.Range[1,1]; or range = oSheet.Range["A1"];

and to declare array you can simply use:
IRange[] array = new IRange[count];

Melba Winshia [Syncfusion]
Replied On March 15, 2007 09:17 AM

Hi Dave,

Thanks for the update!

Glad to know that it works fine.

Kindly let me know if you have any other questions.

Regards,
Melba

CONFIRMATION

This post will be permanently deleted. Are you sure you want to continue?

Sorry, An error occured while processing your request. Please try again later.

You are using an outdated version of Internet Explorer that may not display all features of this and other websites. Upgrade to Internet Explorer 8 or newer for a better experience.

;