Geek Answers Handbook

Creating dynamically generated range names in Excel using C #

What is the best way to create multiple tables of unknown size on a single sheet? Values ​​are pulled from the oracle database and used as values ​​for multiple tables. Is it possible to create dynamic named ranges, or is some other method desirable? I have experience with C #, but I do not have access to VSTO 2005.

Any help or suggestions would be greatly appreciated.

I am ready to explain the problem further if necessary.

+4
source share
4 answers

Open an empty excel file, make a sample of the result that you want to get, and save it in XML. Use this file as a base / template to generate your XML.

Pros:

  • Excel is not required on the computer to generate XML.
  • It does not create an instance of an Excel object either
  • It does not call Excel COM object calls (usually expensive)

Minuses:

  • I do not know if it is possible to create more than one sheet :)

In ASP.NET you can create it and you can declare the content type in the header as application / vnd.ms-excel. Thus, most browsers will understand its search in Excel and try to open it using Excel.

+1
source

You can keep the current value of the bottom block of the cell used and continue to add tables growing down, or, as Mike Rosenblum said, make several sheets in one book to make the document much more bloated. Let me know if I misunderstood your question.

0
source
  • create a range object to be named
  • assign a name to the Name property

  • assign values ​​or value [,] to property Value2

    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);

Hope this helps!

0
source

Not knowing your requirements, it seems you want to use a list or table for each table. Think of the list as a small database table stored on a worksheet. You can access the list range, add records, delete records, insert additional columns, etc.

What you can do is define a list for each table, generate data, and then paste this data into the list.

List management - data / list / create List or Ctrl + L applies the list tools of lists in Excel. The list of ranges is marked with a variable blue border. Features include AutoFilter headers, Insert row and summary row. The list can be imported / exported both in XML and in SharePoint Sources.

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

0
source

More articles:


All Articles