Exporting data to Excel

[morpheus3230]

I think I read about this before but I want to make sure:

I am exporting data from a datagridview to an excel sheet, and I am coming across an apparent limitation as far as the maximum cell length for excel.

I am pretty sure you can normally put more than 255 characters into a cell in excel, but when I try to do the same thing programmatically, I get an error message saying that I exceeded the max limit for number of characters for a cell.

Is there any way around this?
Thanx
Iggy

[RobDog888]

Yes, it is a limitation when using ADO.NET and even the older ADO. The default data field type is set to Text (255) so if you have a Memo field in your database you will generate the error if the text is more then the 255. You can not change the data type of the Excel sheet but you can use other methods for getting the memo field into Excel like using the Excel Object Model.

[morpheus3230]

would you have any examples of how to do this?
Thank You

[RobDog888]

No, but I do have code on automating Excel from vb.net that should help get you started.

http://www.vbforums.com/showpost.php...38&postcount=3

[morpheus3230]

I will be honest, I have not seen anything to could help me, but I could be missing the obvious.

Here is my code that populates the excel sheet. could you please make suggestions?
Thanx

MyConnection = New OleDb.OleDbConnection( _
                            "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\ECN Database\Reports\" & strJob & ".xls" & _
                            ";Extended Properties=""Excel 8.0;HDR=No;IMEX=0""")
            MyConnection.Open()
 
            MyCommand = New OleDb.OleDbDataAdapter("SELECT * FROM [Sheet1$]", MyConnection)
            Dim cmdBuilder As New OleDbCommandBuilder(MyCommand)
            cmdBuilder.QuotePrefix = "["
            cmdBuilder.QuoteSuffix = "]"
 
            DSExcel = New DataSet
            MyCommand.FillSchema(DSExcel, SchemaType.Source, "excel")
            MyCommand.Fill(DSExcel, "excel")
 
            'Debug.WriteLine(DSData.Tables(0).Columns.Count)
            'Debug.WriteLine(DSData.Tables(0).Rows.Count)
 
            For i As Integer = 0 To DsecnEntriesV.Tables(0).Rows.Count - 1
                Dim dr As DataRow = DSExcel.Tables(0).NewRow
                For j As Integer = 0 To DsecnEntriesV.Tables(0).Columns.Count - 2
                    dr(j) = DsecnEntriesV.Tables(0).Rows(i).Item(j)
                Next
                DSExcel.Tables(0).Rows.Add(dr)
                dr = Nothing
            Next
            MyCommand.Update(DSExcel, "excel")
            MyConnection.Close()
            DSExcel.Tables(0).Clear()

[RobDog888]

Looks fine. Using ADO.NET does leave you with no options for formatting the excel sheet but if exporting the data is all thats needed then you should be good to go.

[morpheus3230]

but this is where I run into the maxlength issue. I have use this code many times before and it works fine...unless i run into 255+ characters.
so I guess I am trying to see how to modify the code to make it work :-(

[RobDog888]

Oh thats right. Forgot what the thread issue was.

I had a thread on this before but cant find it right now. I think I had solved it by using an ADO recordset but the problem was in VB 6 not .NET. So for this I know you can use the Excel Object Model with no issues but then using Excel seem to be something you dont want to do?

[morpheus3230]

i'll admit that I do not know what exactly you are talking about :-)

[RobDog888]

By adding a reference to Excel you can use ifts functions and properties to make it do what ever you want it to do, etc.

[morpheus3230]

I am not opposed to it at all. I actually do this later when I end up formating the workbook. I just never considered dumping the data that way, because it seemed tidious

[RobDog888]

Nothing more then looping like you were doing to populate your dataset.

Did you also try using an OleDbCommand object and passing Parameters already?

[morpheus3230]

:-) yeah I just realized that.
I did it the way you suggested and it worked fine.

what do you mean by oledbcommand object and passing parameters?
Obviously I have not, but if you explain how, I could try.

[RobDog888]

I remembered this thread - http://vbforums.com/showthread.php?t=422955

The sql insert into string may be too long as a result of the memo fields contents.


But if your going to be formatting the worksheet using the EOM then there is not too many reasons to not use it to insert your data unless you have a large sheet where performance may be an issue.

[morpheus3230]

many thanx