Exporting EXCEL

[darkelflemurian]

Hello everyone, i have the following problem and I come here looking for some advice.

I am using c#, but i have a dll that creates the excel file in Visual Basic.

i have a datagridview and the information there is exported to excel file. No problem there.

the problems is the following, in my datagridview i have a field that has almost 20 numbers(15 to 17).
in my datagrid the information is being displayed fine from the database. example

| col_Number |
|20120217082628888|

but the problem is when i export this data to excel.

i am using a template and the column where that number goes is currently formatted as general and the data is displayed like this

| col_Number |
| 2.01202E+16 |

if I apply the number format in the template the information goes like this

| col_Number |
|20120217082628800.00|

from the database the columns is

varchar(50)

, if i try to convert that to

bigint

, the winform gives me an error.

what i see is that when i convert to number, automatically is taking the last two digits away and replacing them with 0
20120217082628888 -- ORIGINAL
20120217082628800.00 -- AFTER NUMBER FORMAT

the code that I use in my dll to paste the information is the following.

For row As Integer = 1 To table2.Rows.Count - 1
                For cols As Integer = 0 To table2.Columns.Count - 1
                    exlWsh.Cells(row + 1, cols + 1) = table2.Rows(row)(cols)
                Next
            Next

i dont do any format in any part of the code, that is done directly in the excel file.

any help will be appreciated.

Thanks...

[thines01]

Is it going to be used in a calculation after it reaches Excel?
If not, can your template treat it as a string?

[darkelflemurian]

Thanks for the reply first of all.

no calculations. and the thing is that even when i treat the information as string, it shows up like this
| col_Number |
| 2.01202E+16 |

Is it going to be used in a calculation after it reaches Excel?
If not, can your template treat it as a string?

[thines01]

Can you wrap it in this?:

string strCell = "=\"20120217082628800\"";

[darkelflemurian]

Can you wrap it in this?:

string strCell = "=\"20120217082628800\"";

it did not work. is there a way i can extract the value of the current cell in the datatable. somethign like this (i know this is nor correct) this is in visual Basic:

exlWsh.Cells(row + 1, cols + 1) = table2.Rows(row)(cols). Value

[thines01]

it did not work.

What do you mean?
Did it throw an exception?
Did the value remain the same?

Are you populating .Value2 on the spreadsheet or just the whole cell?

[darkelflemurian]

What do you mean?
Did it throw an exception?
Did the value remain the same?

Are you populating .Value2 on the spreadsheet or just the whole cell?

just the whole cell, I am using this right now

For row As Integer = 1 To table2.Rows.Count - 1
       For cols As Integer = 0 To table2.Columns.Count - 1
         exlWsh.Cells(row + 1, cols + 1) = table2.Rows(row)(cols)
       Next
    Next

[thines01]

Can you change it to .Value2
?

[darkelflemurian]

Can you change it to .Value2
?

is giving me the following exception
Public member 'Value2' on type 'String' not found.

and my code looks like

For row As Integer = 1 To table2.Rows.Count - 1
                For cols As Integer = 0 To table2.Columns.Count - 1
                    exlWsh.Cells(row + 1, cols + 1) = table2.Rows(row)(cols).Value2
                Next
            Next

[thines01]

Put that on the CELL in Excel, not on the table row.

[darkelflemurian]

Put that on the CELL in Excel, not on the table row.

you mean in the excel file or in my code after

exlWsh.Cells

[thines01]

Yes, in your code. Affix the .Value2 to the end of the cell you are populating.

[darkelflemurian]

Put that on the CELL in Excel, not on the table row.

that goes in the excel file or in my code?

and if is in code can you tell me how it will looks like?

thanks...

[thines01]

It goes in your code.
At the end of the code populating the cell after the (), hit the period key.
You will see a menu pop up. One of the selections will be Value2.

[darkelflemurian]

It goes in your code.
At the end of the code populating the cell after the (), hit the period key.
You will see a menu pop up. One of the selections will be Value2.

i tried what you said but i was only getting the pop up with the code like this

exlWsh.Cells.Value2(row + 1, cols + 1)

and if I run the program like that, eventually tells me that there is Insufficient memory to keep executing the program.

[thines01]

Nope.
The parentheses go after the word Cells and the Value2 goes after that with no parentheses.
...then you give THAT a string value or whatever you're trying to put in it.

[darkelflemurian]

Nope.
The parentheses go after the word Cells and the Value2 goes after that with no parentheses.
...then you give THAT a string value or whatever you're trying to put in it.

it gives me the following exception

Public member 'Values2' on type 'Range' not found.

[thines01]

Then convert it to a cell or use this:

CType(rowCurrent.Cells(1, 1), Range).Value2 = "Some Value"

Sorry for posting VB in the C# forum.

[darkelflemurian]

Then convert it to a cell or use this:

CType(rowCurrent.Cells(1, 1), Range).Value2 = "Some Value"

Sorry for posting VB in the C# forum.

I got the same result.

| COL_NUMBER |
| 2.01202E+16 |

[thines01]

Did you wrap the extra quotes around it?