Convert xlsx into datatable in winfroms

Convert xlsx into datatable in winfroms

Posted by: skm.mas on 19 December 2022, 4:21 pm EST

• 

  skm.mas

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  Posted 19 December 2022, 4:21 pm EST

  Hi,
  
  Please let me know how we can convert xls and xlsx into data table in vb.net

• 

  prabhat.sharma

  

  • Post Options:
  • Link
    Copy

  Posted 19 December 2022, 5:36 pm EST

  Hello,

  As of now, there is no direct way to convert excel to a data table but you can use the Documents for Excel API to get the values from excel cells to data table by using the code snippet given below:

  https://www.grapecity.com/documents-api-excel/docs/online/overview.html

  Dim workbook As Workbook = New Workbook()
  workbook.Open("demo.xlsx")
  Dim dt As DataTable = New DataTable()
  Dim worksheet As IWorksheet = workbook.Worksheets(0)
  
  For i As Integer = 0 To worksheet.UsedRange.ColumnCount - 1
      dt.Columns.Add(worksheet.UsedRange.Cells(0, i).Value.ToString(), GetType(Object))
  Next
  
  For i As Integer = 1 To worksheet.UsedRange.RowCount - 1
      Dim dr As DataRow = dt.NewRow()
  
      For j As Integer = 0 To worksheet.UsedRange.ColumnCount - 1
          dr(j) = worksheet.UsedRange.Cells(i, j).Value.ToString()
      Next
      dt.Rows.Add(dr)
  Next
  

  Regards,
  
  Prabhat Sharma.

• 

  skm.mas

  • Post Options:
  • Link
    Copy

  Posted 19 December 2022, 9:13 pm EST

  Ok,
  
  Can it possible using C1Excel4.8

  Thanks,
  
  SKM

• 

  prabhat.sharma

  

  • Post Options:
  • Link
    Copy

  Posted 19 December 2022, 9:45 pm EST

  Hello SKM,

  Yes, it can be done also using the C1Excel API as implemented in the attached sample.

  Regards,
  
  Prabhat Sharma.

  ExceltoDT_Demo_VB.zip

• 

  skm.mas

  • Post Options:
  • Link
    Copy

  Posted 21 December 2022, 11:20 pm EST

  Thanks, It’s working

• 

  skm.mas

  • Post Options:
  • Link
    Copy

  Posted 5 January 2023, 4:25 am EST

  Hi Prabhat,
  
  Can you please let me know, how to handle the empty cell because it’s throw exception if any empty cell

  Thanks

• 

  prabhat.sharma

  

  • Post Options:
  • Link
    Copy

  Posted 5 January 2023, 4:31 pm EST

  Hello SKM,

  To handle this scenario, you can use the Null-Conditional operator (?) with the GetCell method of Worksheet to check if it contains a null value.
  
  Please find the stripped-down code and modified sample attached.

  For i As Integer = 1 To sheet.Rows.Count - 1
       Dim dr As DataRow = dt.NewRow()
      For j As Integer = 0 To sheet.Columns.Count - 1
            dr(j) = sheet.GetCell(i, j)?.Value.ToString()
      Next
      dt.Rows.Add(dr)
  Next

  If you need any other help, please feel free to ask.

  Regards,
  
  Prabhat Sharma.
  
  ExceltoDT_Demo_VB_mod.zip

• 

  skm.mas

  • Post Options:
  • Link
    Copy

  Posted 6 January 2023, 2:15 am EST - Updated 6 January 2023, 2:49 am EST

  Thanks Prabhat. Can you please have a look why error through in my demo.xlsx fileExceltoDT_Demo_VB.zip

• 

  prabhat.sharma

  

  • Post Options:
  • Link
    Copy

  Posted 8 January 2023, 5:21 pm EST

  Hello,

  For your specific excel file, you first need to evaluate the row count and column count first and then fill in the data accordingly.
  
  We evaluated the first null row in the current sample to get the row count. Please find the attached modified sample for the same.

  Regards,
  
  Prabhat Sharma.
  
  ExceltoDT_Demo_VB_Mod.zip