Producing dynamically generated range names in Excel using c#

Question

What is the best way to produce several tables of unknown size on the same worksheet? Values will be pulled from an oracle database and are used as values on several tables. Is it possible to create dynamic named ranges or is some other method desirable? I have some experience with c# but do not have access to VSTO 2005.

Any help or suggestions would be greatly appreciated.

I am willing to explain the problem further if requested.

Answer 1

You could store the current value of the lowest cell block being used, and keep adding the tables growing down, or like Mike Rosenblum said, making multiple sheets in the same workbook would make the document a lot less bloated. Let me know if I misunderstood your question.

Answer 2

1. create a range object that should be named
2. assign a name to its Name property

3. assign a values or a value[,] to the Value2 property

   object[,] values = { { 111, 222, 333 }, { 444, 555, 666 }, { 101, 202, 303 }, 
                        { 404, 505, 606 }, { 111, 222, 333 }, { 444, 555, 666 },
                        { 101, 202, 303 }, { 404, 505, 606 } };
   
   
   Application excel = new Application();
   Workbook workbook = excel.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
   Worksheet sheet = (Worksheet)workbook.Worksheets[1];
   
   
   int rows = values.GetUpperBound(0) - values.GetLowerBound(0) + 1;
   int cols = values.GetUpperBound(1) - values.GetLowerBound(1) + 1;
   
   
   // assign a name to an area of cells and fill it with values
   Range dest = sheet.get_Range(sheet.Cells[1, 1], sheet.Cells[rows, cols]);
   dest.Name = "SORUCE_RANGE";
   dest.Value2 = values;
   
   
   // assign a name to a single cell
   dest = (Range) sheet.Cells[5, 7];
   dest.Name = "MY_DESTINATION";
   dest.NumberFormatLocal = "TT.MM.JJJJ hh:mm:ss"; //german format syntax
   dest.Value2 = DateTime.Now;
   
   
   // clean up (best in finally block)
   workbook.Close(false, null, null);
   excel.Quit();
   System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
   System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
   

I hope it helps!

Answer 3

Not knowing your requirements, it seems you want to use a List or Table for each table. Think of list as a small database table stored within a worksheet. You can refer to the list's range, you can add records, delete records, insert additional columns, and so on.

What you can do is define a list for each table, generate the data, then insert that data into the list.

List Management – Data/ List/ Create List or Ctrl+L applies list tools to lists in Excel. List range is marked with a resizable blue border. Features include AutoFilter headers, Insert Row and Total Row. List can be imported/exported to both XML and SharePoint sources.

You can find VBA code examples for Tables in Excel 2007 or a List in Excel 2003 at http://www.rondebruin.nl/tablecode.htm

Answer 4

Open a blank excel file, make a sample of the result what you would like to get and save it in XML. Use that file as a base/template for generating your XML.

Pros:

• It doesn't require Excel to be installed on the machine to generate it the XML.
• It doesn't create and instance of an Excel Object
• It doesn't make calls to the Excel COM object (usually expensive)

Cons:

• I don't know if it's possible to create more than one worksheet    :)

In a ASP.NET, you can build it and can declare in the header the content-type as application/vnd.ms-excel. That way will most browsers will understand its an Excel find and will try opening with Excel.