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?

[thines01]

Did you wrap the extra quotes around it?

Dim rowCurrent As Range = ws.Rows(1, Missing.Value)

      CType(rowCurrent.Cells(1, 1), Range).Value2 = "Name"
      CType(rowCurrent.Cells(1, 2), Range).Value2 = "Age"
      CType(rowCurrent.Cells(1, 3), Range).Value2 = "Gender"
      CType(rowCurrent.Cells(1, 4), Range).Value2 = "=""20120217082628800"""

[darkelflemurian]

Did you wrap the extra quotes around it?

How should I do that if I have the following

CType(rowCurrent.Cells(row + 1, cols + 1), Range).Value2 = table2.Rows(row)(cols)

[thines01]

Dim strFake As String = "20120217082628800"
      CType(rowCurrent.Cells(1, 4), Range).Value2 = String.Format("=""{0}""", strFake)
      ' OR
      CType(rowCurrent.Cells(1, 4), Range).Value2 = "=" + strFake + """"

[darkelflemurian]

Dim strFake As String = "20120217082628800"
      CType(rowCurrent.Cells(1, 4), Range).Value2 = String.Format("=""{0}""", strFake)
      ' OR
      CType(rowCurrent.Cells(1, 4), Range).Value2 = "=" + strFake + """"

I got my code like this

CType(rowCurrent.Cells(row + 1, cols + 1), Range).Value2 = "=" + table2.Rows(row)(cols).ToString + """"

and i got the following exception
Exception from HRESULT: 0x800A03EC

[thines01]

You forgot the () on the ToString()

[darkelflemurian]

You forgot the () on the ToString()

the same result, do you happen to know another way to do this?

[thines01]

Yes, I know of a multitude of better ways to do this.
...all of which are more complicated than string formatting.

You're almost finished.

I have shown you a screen-shot of the other part of this working without fail.

I SEE THE PROBLEM

Try this:

CType(rowCurrent.Cells(1, 4), Range).Value2 = "="" + strFake + """

Sometimes the VB editor modifies quotes and parentheses on its own.

[darkelflemurian]

Yes, I know of a multitude of better ways to do this.
...all of which are more complicated than string formatting.

You're almost finished.

I have shown you a screen-shot of the other part of this working without fail.

I SEE THE PROBLEM

Try this:

CType(rowCurrent.Cells(1, 4), Range).Value2 = "="" + strFake + """

Sometimes the VB editor modifies quotes and parentheses on its own.

I did what you just suggested and worked. so the only problem would be to get the information from the datatable. what do you recomend me to do

[thines01]

1st: Did you see my last edit?
I had a typo in the number of quotes surrounding the value.

As far as getting the data from the database, I do it in a different manner that probably will not serve you well for this project.

Please complete it using your current path and save it for reference.
After it is working to your satisfaction you can begin exploring other techniques.

[darkelflemurian]

1st: Did you see my last edit?
I had a typo in the number of quotes surrounding the value.

As far as getting the data from the database, I do it in a different manner that probably will not serve you well for this project.

Please complete it using your current path and save it for reference.
After it is working to your satisfaction you can begin exploring other techniques.

Now I cannot make it work. I tried the following

WAIT, I THINK I SEE MY PROBLEM