question

RashmiGupta avatar image
0 Votes"
RashmiGupta asked RashmiGupta commented

How to start exporting data to an excel file from a particular row of excel sheet?

Hi everyone,
I have exported access file's data to a datagridview and I am trying to export the datagridview's datatable to an excel file which has a template like this:

81317-given-template.png


I want the data to be inserted from the 3rd row without affecting first two rows same as here

81354-desired-output.png


But in the actual output first row is being overwritten by column names and data is inserted from where the template ends :

81329-actual-output.png


Here is the code by which I was inserting the data :

''' <summary>
''' Export datagridview's data contained in an data table to excel file
''' </summary>
''' <param name="dataTable">DataGridView's datatable</param>
''' <param name="XLPath"> Excel File Path with xlsx extension</param>

  Private Shared Sub ExportToExcel(ByVal dataTable As DataTable, ByVal XLPath As String)
  Dim connStr = "Provider = Microsoft.ACE.OLEDB.12.0;Data Source=" + XLPath + ";Extended Properties='Excel 8.0;HDR = YES';"
   Using connection As OleDbConnection = New OleDbConnection(connStr)
      connection.Open()
        Using command As OleDbCommand = New OleDbCommand()
             command.Connection = connection
             Dim columnNames As New List(Of String)
             Dim tableName As String = dataTable.TableName
             If dataTable.Columns.Count <> 0 Then
                 For Each dataColumn As DataColumn In dataTable.Columns
                      columnNames.Add(dataColumn.ColumnName)
                  Next
             Else 
                tableName = If(Not String.IsNullOrWhiteSpace(dataTable.TableName), dataTable.TableName, Guid.NewGuid().ToString()) + "$"
                command.CommandText = $"CREATE TABLE [{tableName}] ({String.Join(",", columnNames.[Select](Function(c) $"[{c}]                                                                
                      VARCHAR").ToArray())});"
                      command.ExecuteNonQuery()
                End If
                 If dataTable.Rows.Count <> 0 Then
                    For Each row As DataRow In dataTable.Rows
                           Dim rowValues As List(Of String) = New List(Of String)()
                           For Each column As DataColumn In dataTable.Columns
                                  rowValues.Add(If((row(column) IsNot Nothing AndAlso Not row(column).Equals(DBNull.Value)), 
                                  row(column).ToString(), String.Empty))
                           Next
                           command.CommandText = $"INSERT INTO [{tableName}]({String.Join(",", columnNames.[Select](Function(c) $"[{c}]"))})                                                             
                           VALUES ({String.Join(",", rowValues.[Select](Function(r) $"'{r}'").ToArray())});"
                           command.ExecuteNonQuery()
                      Next
                 End If
          End Using            
        End Using
    End Sub

I wanna ask two question:
1. Why the data is being inseted from the end of the template?
2. Is there any way I can insert the data from 3rd row without overwriting the first two rows?
I tried to do it with OPENROWSET but I did not understand how to apply it in my situation so I was getting syntax error.
I would really appreciate if any one can help me.

Note: I cannot use interop or any third party library.


dotnet-csharpwindows-formsoffice-access-dev
actual-output.png (67.4 KiB)
given-template.png (45.1 KiB)
desired-output.png (60.7 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@RashmiGupta
Based on your description, your issue seems to be more related to code, which is out of the scope of tag "office-excel-itpro" and "office-itpro", I will remove them. Thanks for your understanding.

0 Votes 0 ·
karenpayneoregon avatar image
1 Vote"
karenpayneoregon answered RashmiGupta edited

Hello,

If you are open to an alternative using a free library which does not require Excel to be installed. In short you have a fresh copy of the Excel file which gets copied to (in this case) to the application folder which of course you can change.

Otherwise back to your current code, it appears the last row is off and needs to be set as per this Microsoft page.

  • Install from NuGet package manager SpreadSheetLight and DocumentFormat.OpenXml (may not need this one).

  • Create a class named ExcelOperations1 as per below.

  • Add form code as per below.

  • Place a copy of the Excel file say in a folder beneath the application folder then in code below that file is copied to the application folder and data is placed into the copied Excel file.

Full source


Full source is located in the following GitHub repository project. To run the code sample, perform a NuGet Package restore, run the following script to create the database. Or simply view the source.

ExcelOperations1.vb



Notes

  • There is actually more code here than needed. This is because you might benefit from the additional methods as all SpreadSheetLight documentation is in C#.

  • An event is available for when a runtime exception is thrown e.g. if the Excel file does not exists, the sheet does not exists or if you run the code, open the file, keep it open while running the code again which would cause an exception because you can't write to an open file.

.

 Imports System.IO
 Imports SpreadsheetLight
    
 Public Class ExcelOperations1
     Public Delegate Sub OnErrorDelegate(exception As Exception)
     Public Shared Event OnErrorEvent As OnErrorDelegate
     ''' <summary>
     ''' Insert DataTable into an existing Excel WorkSheet
     ''' </summary>
     ''' <param name="pFileName">Existing Excel file</param>
     ''' <param name="pSheetName">Existing WorkSheet in pFileName</param>
     ''' <param name="pDataTable">DataTable</param>
     ''' <param name="pColumnHeaders">Use column name from DataTable as headers</param>
     ''' <param name="pStartRow">Row to start the import</param>
     Public Shared Function Import(
           pFileName As String, pSheetName As String,
           pDataTable As DataTable, pColumnHeaders As Boolean,
           Optional pStartRow As Integer = 3) As Boolean
    
         Try
    
             CopyFile(pFileName)
    
             Using doc As New SLDocument(pFileName)
    
                 doc.SelectWorksheet(pSheetName)
                 doc.ImportDataTable(pStartRow, SLConvert.ToColumnIndex("A"), pDataTable, pColumnHeaders)
                 doc.Save()
    
                 Return True
    
             End Using
    
         Catch ex As Exception
             RaiseEvent OnErrorEvent(ex)
             Return False
         End Try
    
     End Function
    
     Private Shared Sub CopyFile(pFileName As String)
    
         Dim originalFile = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "ExcelFiles", pFileName)
         Dim targetFile = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, pFileName)
    
         File.Copy(originalFile, targetFile, True)
    
     End Sub
     Public Shared Function SheetExists(doc As SLDocument, pSheetName As String) As Boolean
         Return doc.GetSheetNames(False).Any(Function(sheetName) sheetName.ToLower() = pSheetName.ToLower())
     End Function
     Public Function SheetNames(pFileName As String) As List(Of String)
         Using doc = New SLDocument(pFileName)
             Return doc.GetSheetNames(False)
         End Using
     End Function
     ''' <summary>
     ''' Remove a sheet if it exists.
     ''' </summary>
     ''' <param name="pFileName">Existing Excel file</param>
     ''' <param name="pSheetName"></param>
     ''' <returns></returns>
     Public Function RemoveWorkSheet(pFileName As String, pSheetName As String) As Boolean
    
         Using doc As New SLDocument(pFileName)
    
             Dim workSheets = doc.GetSheetNames(False)
    
             If workSheets.Any(Function(sheetName) sheetName.ToLower() = pSheetName.ToLower()) Then
    
                 If workSheets.Count > 1 Then
                     doc.SelectWorksheet(doc.GetSheetNames().FirstOrDefault(Function(sName) sName.ToLower() <> pSheetName.ToLower()))
                 ElseIf workSheets.Count = 1 Then
                     RaiseEvent OnErrorEvent(New Exception("Can not delete the sole worksheet"))
                 End If
    
                 doc.DeleteWorksheet(pSheetName)
                 doc.Save()
    
                 Return True
    
             Else
                 Return False
             End If
    
         End Using
    
     End Function
     ''' <summary>
     ''' Add a new sheet if it does not currently exists.
     ''' </summary>
     ''' <param name="pFileName"></param>
     ''' <param name="pSheetName"></param>
     ''' <returns></returns>
     Public Function AddNewSheet(pFileName As String, pSheetName As String) As Boolean
    
         Using doc = New SLDocument(pFileName)
    
             If Not (SheetExists(doc, pSheetName)) Then
    
                 doc.AddWorksheet(pSheetName)
                 doc.Save()
    
                 Return True
    
             Else
                 Return False
             End If
    
         End Using
    
     End Function
 End Class

Form code

In this case I populate a DataTable from a database, assigned to a DataGridView. Where the data comes from is irrelevant and if using a DataSet rather than a DataTable simple pass the Table e.g. SomeDataSet.Tables("SomeTableName").

 Public Class Form1
     Private Sub Form1_Shown(sender As Object, e As EventArgs) Handles Me.Shown
         DataGridView1.DataSource = DataOperationsSqlServer.LoadCustomerRecordsUsingDataTable()
         AddHandler ExcelOperations1.OnErrorEvent, AddressOf OnExcelExportError
     End Sub
     Private Sub ExportSimpleButton_Click(sender As Object, e As EventArgs) Handles ExportSimpleButton.Click
         Dim dt = CType(DataGridView1.DataSource, DataTable)
         If ExcelOperations1.Import("DemoExport.xlsx", "Info_Table", dt, False) Then
             MessageBox.Show("Done")
         End If
     End Sub
     Private Sub OnExcelExportError(exception As Exception)
         MessageBox.Show(exception.Message)
     End Sub
 End Class


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @karenpayneoregon Mam!
I really appreciate your efforts and I know that spreadsheetlight works but I have already stated in question that I cannot use any third party library and interop. It is a restriction
which is making harder to find a solution but I am compulsive to follow this.
I have MS Office 365 installed in my system and my project uses that.
I noticed you have suggested OPENROWSET [here][1] for a question kind of mine.
Can I use OPENROWSET for desired results, if so, how? I tried to implement it but was not successful.
If not, please suggest another way if you can.

Thankyou.
[1]: https://social.msdn.microsoft.com/Forums/en-US/99c7d06f-6c5d-4ab3-96e4-e07698394a89/import-data-from-sql-to-excel-sheets-named-region?forum=sqlintegrationservices

0 Votes 0 ·
karenpayneoregon avatar image
1 Vote"
karenpayneoregon answered RashmiGupta commented

In regards to

Can I use OPENROWSET for desired results, if so, how? I tried to implement it but was not successful.

  1. In your base Excel file set the last used row so that when pushing data to Excel to the correct row

  2. Follow steps in my Microsoft article to setup SQL-Server, there are several steps which if not done will cause the process to fail on SQL-Server. Note when I wrote the article I never tried to export to a specific row so you will need to do so. Note full source code is provided in this GitHub repository using VS2017 and with minor mods will work with .NET Core. Lastly on this bullet the source code is C# yet if you simply read the code is easy for even a child to understand and for a coder/developer should be easy enough to follow along and translate to VB.

Notes
Not sure if I've mentioned this before but I have not used OleDb or Excel automation since around 2005 as there are just way too many things to be concerned about ranging from proper connection string (which in some cases IMEX must be set correctly), formatting of dates and numbers to versions of Excel libraries.

All my work is done with Aspose cells or GemBox SpreadSheet (which of course you are not permitted to use). I provide SpreadSheetLight code samples because it's reliable and free for forum questions.

What you really need to do is convince those who are denying usage of libraries such as SpreadSheetLight to change this ruling. A good developer needs to provide the facts to those denying usage of external libraries so they are better informed. Using Excel automation a last option while OleDb is next to last option.

In closing the above is all which I have, nothing left out.






· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@karenpayneoregon Mam!
I am sorry but I totally forgot to mention in the question that I am using MS Access database. An access file's data is exported to datagridview and then I am trying to export the datagridview's datatable to excel sheet. Sorry for that !!!
In this situation,
if possible, can OPENROWSET help me?
OR
is there any other way to get the results....

When talking about the third party library, my boss told me that one of our company's important product was using a third party library from past years. Now, the authors of the library has introduced such changes ( I don't know about them, may be start charging money or something else) that the whole product is being rebuilt now which is very
expensive. Learning from this incident, my boss denied to deploy any kind of dependency to third party libraries in our project. I think he has right thoughts.


Anyway, thanks for replying.


0 Votes 0 ·

I'm getting access denied when posting a reply as a question and is too long for a comment so see the following

https://gist.github.com/karenpayneoregon/c2a40eeba2ea6cd29cd0311caa4948d3

0 Votes 0 ·
RashmiGupta avatar image RashmiGupta karenpayneoregon ·

Thankyou ! I will try it.

0 Votes 0 ·