Using XlsIO in ASPX

[Dave]

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]

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]

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]

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]

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]

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]

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