Hi,
You could try to use some third party component for exporting data to
Excel file. I recommend you to use GemBox.Spreadsheet -- here is the
example of importing/exporting data from DataTable to Excel files
(XLS, XLSX, CSV):
http://www.gemboxsoftware.com/LDataTable.htm.
If you want to use free component, note that there is free version of
GemBox.Spreadsheet available -- you can even use it in your commercial
applications. Free version comes with 150 rows limit. You can find
free version here:
http://www.gemboxsoftware.com/GBSpreadsheetFree.htm.
Mario
GemBox Software
--
GemBox.Spreadsheet for .NET - Easily read and write Excel (XLS, XLSX
or CSV)
or export to HTML files from your .NET apps. Seehttp://
www.gemboxsoftware.com/GBSpreadsheet.htm
--
Hi Tammy,
Here is a method which works for me for reading data from Excel and
writing it to Sql Server. Note: More times than not, you have toprep
the data in the Excel sheet - meaning - eliminating unused rows at then
end of the dataset in Excel, eliminating unused columns also at the end
of the dataset in Excel, making sure that the datatypes in each column
in the Excel sheet are valid (General format usually works OK but
sometimes have to format date fields as dates explicitly, text fields as
text, explicityly, numeric fields as numbers excplicitly). The note
doesn't really have anything to do with "Missing ISAM", but just
something to be aware of.
-----------------------------------
Private Sub WriteDataToSqlServerFromExcel()
Dim da As OleDbDataAdapter, conn As OleDbConnection
conn = New OleDbConnection
conn.ConnectionString = "provider=Microsoft.Jet.OLEDB.4.0;data
source=C:\someDir\test1.xls;Extended Properties=Excel 8.0"
da = New OleDbDataAdapter
da.SelectCommand = New OleDbCommand
da.SelectCommand.Connection = conn
da.SelectCommand.CommandText = "Select * From [" Sheet1$]"
da.Fill(ds1, "Sheet1OLE")
Dim daS As New SqlDataAdapter, connS As New SqlConnection
connS.ConnectionString = "Data Source=MyServer;Initial
Catalog=MyDB;Integrated Security=True"
daS.SelectCommand = New SqlCommand
daS.SelectCommand.Connection = connS
daS.InsertCommand = New SqlCommand
daS.InsertCommand.Connection = connS
daS.SelectCommand.CommandText = "Select * From Sheet1DBTbl"
daS.Fill(ds1, "sht1DBtbl")
daS.InsertCommand.Parameters.Add("@fdl1", SqlDbType.VarChar, 50, "fld1")
daS.InsertCommand.Parameters.Add("@fdl2", SqlDbType.VarChar, 50, "fld2")
daS.InsertCommand.Parameters.Add("@fdl3", SqlDbType.VarChar, 50, "fld3")
..
daS.InsertCommand.CommandText = "Insert Into Sheet1DBTbl Select @fld1,
@fld2, @fld3, ..."
Dim reader As DataTableReader = ds1.Tables("Sheet1OLE").CreateDataReader
ds1.Tables("sht1DBtbl").Load(reader, LoadOption.Upsert)
daS.Update(ds1, "sht1DBtbl")
End Sub
-------------------------------------------
There are 2 caveats here - 1) Sheet1DBTbl is an existing table on the
server DB (if there isn't one there - you need to create it either at
the server or from code). 2) However many fields there are in the Excel
sheet (and in the table on the sql server - which must match the same
number of columns there are in the Excel sheet and the Server DB table)
-- you have to add the same number of parameters to the DataAdapter
InsertCommand. So the first half of the procedure above reads the data
from Excel into a memory table in the dataset (ds1) and the 2nd half of
the procedure writes the data from the OLE table to the Sql Server table
using a dataReader object.
One more note:
ds1.Tables("sht1DBtbl").Load(reader, LoadOption.Upsert)
(Except for the tableName -- use your own table name) this is the
correct syntax -- VB2005.
Rich
*** Sent via Developersdexhttp://
www.developersdex.com***
Thank you so much for yur reply!!!
I understand what you are doing but in my case I have an extra
problem. Sometimes the Excel file will have a minimum of 4 columns
(this ones will always be present) and then 5 other columns that might
all show up or not dependinf if the user select the checkbox. How do I
reflect that?
I cannot create a table manually in sql server, because i don't know
how many the user wants to see.
how can i do this with vb code?
thanks a lot for all ur time!!!!
Tammy- Hide quoted text -
- Show quoted text -
Thank you all for taking time to answer me.
Mario,
I do not need to export data to Excel.That part of my code works
perfect as it is.
Urkec and Rich,
My problem is importing back into a SQL SERVER TABLE that needs to be
created based on the EXCEL COLUMNS The Excel columns can vary from
only 4 to 9 and everything in between depending on what the user
selects in the code below.
HERE IS WHERE THE USER SELECTS WHICH FIELDS WILL BE SHOWN IN THE
DATAGRIDVIEW: (THIS WORKS PERFECT)
Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button3.Click
Cursor.Current = System.Windows.Forms.Cursors.WaitCursor
Dim cn As New SqlConnection("Data Source=" &
lstServers.SelectedValue & ";Initial Catalog=" &
lstDatabases.SelectedValue & ";Integrated Security=SSPI")
Dim cmd As New SqlCommand("usp_DR_Spam_BB_Search_get_recs",
cn)
cmd.CommandTimeout = 0
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.AddWithValue("@Matter",
lstDatabases.SelectedItem)
cmd.Parameters.AddWithValue("@FileSet",
lstFileSets.SelectedItem)
cn.Open()
Dim reader As SqlDataReader = cmd.ExecuteReader()
Dim ds As New DataSet()
Dim dt As New DataTable("Table1")
ds.Tables.Add(dt)
ds.Load(reader, LoadOption.PreserveChanges, ds.Tables(0))
DataGridView1.Font = New Font("SansSerif", 8.25,
FontStyle.Regular)
DataGridView1.DataSource = ds.Tables(0)
If DataGridView1.Rows.Count > 0 Then
If CheckBox1.CheckState = True Then
DataGridView1.Columns("Last Name").Visible = True
ElseIf CheckBox1.CheckState = False Then
DataGridView1.Columns("Last Name").Visible = False
End If
If CheckBox2.CheckState = True Then
DataGridView1.Columns("First Name").Visible = True
ElseIf CheckBox2.CheckState = False Then
DataGridView1.Columns("First Name").Visible = False
End If
If CheckBox3.CheckState = True Then
DataGridView1.Columns("Domain").Visible = True
ElseIf CheckBox3.CheckState = False Then
DataGridView1.Columns("Domain").Visible = False
End If
If CheckBox4.CheckState = True Then
DataGridView1.Columns("Email").Visible = True
ElseIf CheckBox4.CheckState = False Then
DataGridView1.Columns("Email").Visible = False
End If
If CheckBox5.CheckState = True Then
DataGridView1.Columns("Subject").Visible = True
ElseIf CheckBox5.CheckState = False Then
DataGridView1.Columns("Subject").Visible = False
End If
Else
MessageBox.Show("There are no records using the fileset
selected, please try with a different fileset")
End If
Dim rowNumber As Integer = 1
For Each row As DataGridViewRow In DataGridView1.Rows
If row.IsNewRow Then Continue For
row.HeaderCell.Value = rowNumber.ToString
rowNumber = rowNumber + 1
Next
DataGridView1.AutoResizeRowHeadersWidth(DataGridViewRowHeadersWidthSizeMode.AutoSizeToAllHeaders)
Cursor.Current = System.Windows.Forms.Cursors.Default
'cmd.Connection.Close()
End Sub
=====================================================================================================================================
HERE IS WHERE THE USER CAN EXPORT TO XLSX (THIS WORKS PERFECT)
Private Sub exportExcel(ByVal grdView As DataGridView, ByVal fileName
As String, _
ByVal fileExtension As String, ByVal filePath As String)
' Choose the path, name, and extension for the Excel file
Dim myFile As String = filePath & "\" & fileName &
fileExtension
' Open the file and write the headers
Dim fs As New IO.StreamWriter(myFile, False)
fs.WriteLine("<?xml version=""1.0""?>")
fs.WriteLine("<?mso-application progid=""Excel.Sheet""?>")
fs.WriteLine("<ss:Workbook xmlns:ss=""urn:schemas-microsoft-
com
ffice:spreadsheet"">")
' Create the styles for the worksheet
fs.WriteLine(" <ss:Styles>")
' Style for the column headers
fs.WriteLine(" <ss:Style ss:ID=""1"">")
fs.WriteLine(" <ss:Font ss:Bold=""1""/>")
fs.WriteLine(" <ss:Alignment ss:Horizontal=""Center""
ss:Vertical=""Center"" ss:WrapText=""1""/>")
fs.WriteLine(" <ss:Interior ss:Color=""#C0C0C0""
ss
attern=""Solid""/>")
fs.WriteLine(" </ss:Style>")
' Style for the column information
fs.WriteLine(" <ss:Style ss:ID=""2"">")
fs.WriteLine(" <ss:Alignment ss:Vertical=""Center""
ss:WrapText=""1""/>")
fs.WriteLine(" </ss:Style>")
fs.WriteLine(" </ss:Styles>")
' Write the worksheet contents
fs.WriteLine("<ss:Worksheet ss:Name=""Sheet1"">")
fs.WriteLine(" <ss:Table>")
For Each col As DataGridViewColumn In grdView.Columns
If col.Visible Then
fs.WriteLine(String.Format(" <ss:Column
ss:Width=""{0}""/>", col.Width))
End If
Next
fs.WriteLine(" <ss:Row>")
For Each col As DataGridViewColumn In grdView.Columns
If col.Visible Then
fs.WriteLine(String.Format(" <ss:Cell
ss:StyleID=""1""><ss
ata ss:Type=""String"">{0}</ss
ata></ss:Cell>",
col.HeaderText))
End If
Next
fs.WriteLine(" </ss:Row>")
' Check for an empty row at the end due to Adding allowed on
the DataGridView
Dim subtractBy As Integer
If grdView.AllowUserToAddRows = True Then subtractBy = 2 Else
subtractBy = 1
' Write contents for each cell
For Each row As DataGridViewRow In grdView.Rows
fs.WriteLine(String.Format(" <ss:Row
ss:Height=""{0}"">", row.Height))
For Each col As DataGridViewColumn In grdView.Columns
If col.Visible Then
fs.WriteLine(String.Format(" <ss:Cell
ss:StyleID=""2""><ss
ata ss:Type=""String"">{0}</ss
ata></ss:Cell>",
row.Cells(col.Name).Value.ToString))
End If
Next
fs.WriteLine(" </ss:Row>")
Next
' Close up the document
fs.WriteLine(" </ss:Table>")
fs.WriteLine("</ss:Worksheet>")
fs.WriteLine("</ss:Workbook>")
fs.Close()
' Open the file in Microsoft Excel
' 10 = SW_SHOWDEFAULT
ShellEx(Me.Handle, "Open", myFile, "", "", 10)
End Sub
Private Sub Button4_Click(ByVal sender As System.Object, ByVal e
As System.EventArgs) Handles Button4.Click
' Call the export sub
exportExcel(DataGridView1, "exportedData", ".xls",
My.Computer.FileSystem.SpecialDirectories.Desktop)
End Sub
======================================================================================================================================
AT THIS POINT IS WHERE I AM HAVING TROUBLES. I want either to import
the xlsx file to a SQL SERVER TABLE created on the fly with the fields
of that file OR create a table on the fly with whatever the user had
selected from the checkbox code above.
I had posted the export code at the begnning of the thread.
If anybody has a better idea on how I can achieve what I need to I'd
really be foever thankful!!!
Thanks a lot guys!!!!!
Regards,
Tammy
