הערה
הגישה לדף זה מחייבת הרשאה. באפשרותך לנסות להיכנס או לשנות מדריכי כתובות.
הגישה לדף זה מחייבת הרשאה. באפשרותך לנסות לשנות מדריכי כתובות.
Question
Wednesday, May 7, 2014 6:08 AM
Hi All
How to import specific columns and specific rows from excel sheet to Sql datatable in vb.net
Excel Sheet Have Columns A to M
but I want to import only columns A, C, E and H
Have Rows more than 1000
Only import Rows 15 to 75
how can I do?
Best Regards
All replies (6)
Wednesday, May 7, 2014 8:09 AM ✅Answered | 2 votes
Hi,
If you had already the codes of how to import excel try to use this;
xlWorkSheet.UsedRange.Cells([Row Index],[Column Index])
or you can the idea from here: Import Excel
Wednesday, May 7, 2014 10:57 AM ✅Answered | 1 vote
Hello,
Using Office automation you would use code similar to what is shown below. Now in this example I get a simple range and explain about expanding in these in the remarks.
In short you set a range and iterate thru the range and ignore the columns you don't want and populate a DataTable then (right now it's a procedure) return the DataTable to the caller by changing the procedure to a function.
Option Strict On
Option Infer On
Imports Excel = Microsoft.Office.Interop.Excel
Imports Microsoft.Office
Imports System.Runtime.InteropServices
Module ExcelDemoIteratingData_1
''' <summary>
''' </summary>
''' <param name="FileName"></param>
''' <param name="SheetName"></param>
''' <remarks>
''' This is a simple example that is hard-coded to a range of cells A1-A10
''' but we can change this to say C5-K20 etc. There is a for-next that cycles
''' thru the rows and a inner for-next to cycle thru the cells, here you would
''' have a pre-defined DataTable that you populate in these for-next statements
''' then change this procedure to a function and return the DataTable.
''' </remarks>
Public Sub OpenExcelIterate(ByVal FileName As String, ByVal SheetName As String)
If IO.File.Exists(FileName) Then
Dim Proceed As Boolean = False
Dim xlApp As Excel.Application = Nothing
Dim xlWorkBooks As Excel.Workbooks = Nothing
Dim xlWorkBook As Excel.Workbook = Nothing
Dim xlWorkSheet As Excel.Worksheet = Nothing
Dim xlWorkSheets As Excel.Sheets = Nothing
Dim xlCells As Excel.Range = Nothing
xlApp = New Excel.Application
xlApp.DisplayAlerts = False
xlWorkBooks = xlApp.Workbooks
xlWorkBook = xlWorkBooks.Open(FileName)
xlApp.Visible = False
xlWorkSheets = xlWorkBook.Sheets
For x As Integer = 1 To xlWorkSheets.Count
xlWorkSheet = CType(xlWorkSheets(x), Excel.Worksheet)
If xlWorkSheet.Name = SheetName Then
Proceed = True
Exit For
End If
Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkSheet)
xlWorkSheet = Nothing
Next
If Proceed Then
Dim xlUsedRange = xlWorkSheet.Range("A1", "A10") ' xlWorkSheet.UsedRange
Dim DecimalTest As Decimal = 0
Try
Dim ExcelArray(,) As Object = CType(xlUsedRange.Value(Excel.XlRangeValueDataType.xlRangeValueDefault), Object(,))
If ExcelArray IsNot Nothing Then
Dim RowsCount As Integer = ExcelArray.GetUpperBound(0)
Dim ColumnsCount As Integer = ExcelArray.GetUpperBound(1)
For j As Integer = 1 To RowsCount
Console.Write("Row " & j.ToString & " ")
For x As Integer = 1 To ColumnsCount
Dim s1 As String = CStr(ExcelArray(j, x))
If Decimal.TryParse(s1, DecimalTest) Then
Console.Write(s1 & " is decimal")
Else
Console.Write(s1)
End If
Console.Write(" "c)
Next
Console.WriteLine()
Next
End If
Finally
ReleaseComObject(xlUsedRange)
End Try
Else
MessageBox.Show(SheetName & " not found.")
End If
xlWorkBook.Close()
xlApp.UserControl = True
xlApp.Quit()
ReleaseComObject(xlCells)
ReleaseComObject(xlWorkSheets)
ReleaseComObject(xlWorkSheet)
ReleaseComObject(xlWorkBook)
ReleaseComObject(xlWorkBooks)
ReleaseComObject(xlApp)
Else
MessageBox.Show("'" & FileName & "' not located. Try one of the write examples first.")
End If
End Sub
Private Sub ReleaseComObject(ByVal obj As Object)
Try
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
obj = Nothing
Catch ex As Exception
obj = Nothing
End Try
End Sub
End Module
We can do the same thing with OleDb i.e. (code is taken from this article)
Imports System.Data.OleDb
Module OleDbDemos
Private ConnectionNoHeader As String = "provider= Microsoft.ACE.OLEDB.12.0; data source='{0}';Extended Properties=""Excel 12.0; HDR=No;"""
Public Sub OpenSheetInDataGridView(ByVal FileName As String, ByVal SheetName As String)
Dim f As New frmViewSheetData
Try
Dim dt As New DataTable
Using cn As New OleDbConnection With {.ConnectionString = String.Format(ConnectionNoHeader, FileName)}
cn.Open()
Dim cmd As OleDbCommand = New OleDbCommand(
<Text>
SELECT F1 As TheMonth, F2 as Spent FROM [<%= SheetName %>$A2:B5]
</Text>.Value,
cn
)
dt.Load(cmd.ExecuteReader)
End Using
f.Text = "Sheet name: " & SheetName
f.DataGridView1.DataSource = dt
f.DataGridView1.Columns("TheMonth").HeaderText = "Month"
f.ShowDialog()
Finally
f.Dispose()
End Try
End Sub
End Module
Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.
Friday, May 9, 2014 2:00 AM ✅Answered | 2 votes
Hi,
The link already provide click here : Import Excel
Imports Microsoft.Office.Interop
Public Class Form1
Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
End Sub
Private Sub KillExcelProcess()
Try
Dim Xcel() As Process = Process.GetProcessesByName("EXCEL")
For Each Process As Process In Xcel
Process.Kill()
Next
Catch ex As Exception
End Try
End Sub
Private Sub getXlFile()
Dim xlApp As Excel.Application
Dim xlWorkbook As Excel.Workbook
Dim xlWorkSheet As Excel.Worksheet
Dim xlRange As Excel.Range
Dim xlCol As Integer
Dim xlRow As Integer
Dim strDestination As String
Dim Data(0 To 100) As String
With OpenFileDialog1
.Filter = "Excel Office|*.xls;*.xlsx"
.FileName = ""
.ShowDialog()
strDestination = .FileName
TextBox1.Text = .FileName
End With
With ListView1
.View = View.Details
.FullRowSelect = True
.GridLines = True
.Columns.Clear()
.Items.Clear()
If strDestination <> "" And TextBox2.Text <> "" Then
xlApp = New Excel.Application
xlWorkbook = xlApp.Workbooks.Open(strDestination)
xlWorkSheet = xlWorkbook.Worksheets(TextBox2.Text)
xlRange = xlWorkSheet.UsedRange
If xlRange.Columns.Count > 0 Then
If xlRange.Rows.Count > 0 Then
'Header
For xlCol = 1 To xlRange.Columns.Count
.Columns.Add("Column" & xlCol)
Next
'Detail
For xlRow = 1 To xlRange.Rows.Count
For xlCol = 1 To xlRange.Columns.Count
Data(xlCol) = xlRange.Cells(xlRow, xlCol).text
If xlCol = 1 Then
.Items.Add(Data(xlCol).ToString)
Else
.Items(xlRow - 1).SubItems.Add(Data(xlCol).ToString)
End If
Next
Next
xlWorkbook.Close()
xlApp.Quit()
KillExcelProcess()
End If
End If
Else
MessageBox.Show("Pls. input correct attributes", Me.Text, MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
End If
End With
End Sub
Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
getXlFile()
End Sub
End Class
Monday, May 12, 2014 6:16 AM ✅Answered | 1 vote
Hi,
Because the number of data acquired only 100 rows, try to change the data(0 to 10000)
Dim Data(0 to 10000) as String
Wednesday, May 7, 2014 9:53 AM
Hi Har Das
thanks for your fast response
I don't have codes
can provide detail codes snippet
best regrads
Friday, May 9, 2014 6:45 AM
Hi Har Das
I am Tried That Code
it's not comfortable more than 1000 rows
can you check..?
Regards