1

I'm just starting to work on a project that uses SpreadsheetGear. CopyFromDataTable is used to get all the data from a database. There are a lot of fields to work with; we're formatting them, hiding certain ranges if they're empty, etc. As an example:

SpreadsheetGear.IRange cells = worksheet.Cells;    
cells["G1"].EntireColumn.NumberFormat = "m/d/yyyy";

So if the the columns are rearranged or one is removed, it appears I'd have to go back and adjust all of the hardcoded values to reflect the cell shift. Is there any way to reference the column by its first cell's name to possibly make things more modular?

I found this, but it still requires hardcoding the column.

John Straka
  • 1,874
  • 5
  • 28
  • 49

2 Answers2

1

A great alternative for the spreadsheetgear is http://officehelper.codeplex.com. Using this library you can create a template xlsx, where you do all your formatting and then you can use parts from this "template" document to create your final, to be reported xlsx files, whitout the need of programtically setting the number format, like in your example.

aron.sinoai
  • 29
  • 1
0

One possibility is to search for the heading/fieldnames

Sub test()
Dim strFieldName As String
Dim rngHeading As Range

With Sheet1

    strFieldName = "Heading 3" 'find Heading Name
    With .Rows("1:1") 'search row 1 headings

        Set rngHeading = .Find(What:=strFieldName, LookIn:=xlValues, lookAt:=xlWhole, _
        SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)

        'check if found
        If Not rngHeading Is Nothing Then

        MsgBox rngHeading.Address
        MsgBox rngHeading.Row
        MsgBox rngHeading.Column
        rngHeading.EntireColumn.NumberFormat = "m/d/yyyy"

        End If

    End With

End With

End Sub
  • Thanks for the answer - haven't forgotten about this but haven't had the time to test this out yet, will get to it eventually and accept answer if it does. – John Straka May 03 '11 at 19:10