Link to home
Start Free TrialLog in
Avatar of Victor  Charles
Victor CharlesFlag for United States of America

asked on

Help with creating one excel fle from multiple excel files based on identical IDs using VB.NET

Hi,

 I'm exporting data from multiple C1TrueDBGrid controls to multiple excel files. How do I create 1 excel file from multiple excel files based on identical IDs using vb.net?  For example if first excel file1 contains:

 id   nsn   add
 1    888   hnm
 2    999   ujk

 and excel file 2 contains:

 id   NAS  NMN
 1    777   ccc
 2    666   ddd

 I would like to have the following excel file:

 id   nsn   add     NAS   NMN
 1    888   hnm    777    ccc
 2    999   ujk      666    ddd


 Thanks,

 Victor
Avatar of Fernando Soto
Fernando Soto
Flag of United States of America image

Will saving the result to a CSV file which can be opened in Excel an excepted solution?
Avatar of Victor  Charles

ASKER

Yes.
Another question. Are the file1 and file2 exported to  XML from C1TrueDBGrid or are they actually Excel files or are they CSV files?
Hi,

They are excel files.

Victor
I have not worked much with Excel Files except using the CSV format.
What version of Excel are you using?
Can you post Excel files for File1 and File2 from your original question please.
Hi Fernando,

I am using execl 2013, will send you the excel files later today.
Thanks,
V
Fernando,

Enclosed is an example, I would like to create File3 by  inserting data from File1 to File2 based on matching NSN instead of ID. Please note same values from File1 can be copied to File2 if the NSN values in both file match to create File3.

Thanks,

Victor
FILE1.xlsx
FILE2.xlsx
FILE3.xlsx
Hi Victor;

I am not understanding your requirements on this. For example in file1 you have "1305 BEL" but In file2 there is no NSN value "1305 BEL", so where are you getting the ADD and OPP values from you have in file3? From my understanding of the question there should only be three records in file3 where you have  6.

Please give a description in words of the logic you need to get to the results you need.

User generated image
Fernando,

The logic is correct, 1305 BEL should be in file2.

The fields in file1 should copy to file2 to create file3 only when NSN value  in file1 match with file2.

Thanks,

Victor
In file1 you have a record 1305 BEL with values for ADD and OPP columns. In file2 there is no record that match 1305 BEL. In file3 you have a record for 1305 BEL with values for ADD and OPP columns but it also has values for the columns NASE, FIF and HUN. Where did you pull those values from??

Please write out an algorithm to follow and verify that it is correct before posting.
Fernando,

I meant file 2 should include.

NSN.            NASC   FIF  HUN

1305 BEL     JKJ         UJI    UNH3

and combined with file1 should give record 4 shown in file3.

Thanjs,

Victor
Hi Victor,

To your statement, "I meant file 2 should include.", and the reason why if I am to continue to help with this question I need you to go over what you would do to accomplish this manually and write down those steps in a post as well as repost the excel files with the corrections. So that I can understand what needs done. Also think about, will file1 always have a matching item/s in file2? and anything else that will help.
Hi Fernando,

I'm away this week, will send you more information when back in the office next week.

Thank You.

Victor
Hi Fernando,

I'm trying to create File3 by copying data from File1 to File2 based on matching NSN values. Enclosed are the new excel files. The fields in both files (file1,file2) will always be the same.

Thanks,

Victor
FILE1.xlsx
FILE2.xlsx
FILE3.xlsx
Hi Victor;

Here is a program that will read the Excel files, file1.xlsx, file2.xlsx and create the Excel file3.xlsx. Please note the Imports statements at the top of the program. You will need to add a NuGet package to the project called EPPlus. You will want to download the documentation at, EPPlus 4.0 Documenation (Chm) and the Samples at, EPPlus 4.0.4 with Samples so that you will be able to understand the code below and be able to modify the code as needed.
Imports System.IO
Imports OfficeOpenXml

Public Class Form1

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        '' Used to read the excel files file1.xlsx and file2.xlsx, Change Path as needed
        Dim file1Path As String = "C:\Working Directory\File1.xlsx"
        Dim file2Path As String = "C:\Working Directory\File2.xlsx"
        Dim file3PathNew As String = "C:\Working Directory\File3New.xlsx"

        '' Holds the parsed data from the excel files
        Dim excelData1 As New List(Of File1Info)
        Dim excelData2 As New List(Of File2Info)
        '' Combined the two Excel files to be used to create the third file, file3.xlsx
        Dim joinedData As List(Of File3Info)

        '' Parse the file1.xlsx skipping row 1, header row
        excelData1 = (From col In GetExcelData(file1Path).Skip(1)
                      Select New File1Info With {
                          .NSN = col(0),
                          .ADD = col(1),
                          .OPP = col(2)
                     }).ToList()

        '' Parse the file2.xlsx skipping row 1, header row
        excelData2 = (From col In GetExcelData(file2Path).Skip(1)
                      Select New File2Info With {
                          .NSN = col(0),
                          .NASC = col(1),
                          .FIF = col(2),
                          .HUN = col(3)
                     }).ToList()

        '' Join the data from the two files that have matching NSN fields
        joinedData = (From e2 In excelData2
                      From e1 In excelData1
                      Where e1.NSN = e2.NSN
                      Select New File3Info With {
                           .NSN = e2.NSN,
                           .NASC = e2.NASC,
                           .FIF = e2.FIF,
                           .HUN = e2.HUN,
                           .ADD = e1.ADD,
                           .OPP = e1.OPP
                     }).ToList()

        '' Get a List of NSN fields from file1.xlsx that do not have a matching NSN in file2.xlsx
        Dim file1NSN As List(Of String) = excelData1.Select(Function(n) n.NSN).ToList()
        '' Create a File3Info record for those records in file2.xlsx not in file1.xlsx
        Dim notInFile1 = (From f2 In excelData2.Where(Function(n) Not file1NSN.Contains(n.NSN))
                          Select New File3Info With {
                              .NSN = f2.NSN,
                              .NASC = f2.NASC,
                              .FIF = f2.FIF,
                              .HUN = f2.HUN,
                              .ADD = "",
                              .OPP = ""
                         }).ToList()

        '' Add the records without matching records to the list
        joinedData.AddRange(notInFile1)

        '' Create the combined data from joinedData list
        Dim newFile As FileInfo = New FileInfo(file3PathNew)
        If newFile.Exists Then
            newFile.Delete()
            newFile = New FileInfo(file3PathNew)
        End If

        Using package As New ExcelPackage(newFile)
            '' Add a new worksheet to the empty workbook
            Dim worksheet As ExcelWorksheet = package.Workbook.Worksheets.Add("Sheet1")
            '' Add the headers to the worksheet
            worksheet.Cells(1, 1).Value = "NSN"
            worksheet.Cells(1, 2).Value = "NASC"
            worksheet.Cells(1, 3).Value = "FIF"
            worksheet.Cells(1, 4).Value = "HUN"
            worksheet.Cells(1, 5).Value = "ADD"
            worksheet.Cells(1, 6).Value = "OPP"

            Dim rowNum As Integer = 2

            '' Add data cells
            For Each row As File3Info In joinedData
                worksheet.Cells("A" & rowNum.ToString()).Value = row.NSN
                worksheet.Cells("B" & rowNum.ToString()).Value = row.NASC
                worksheet.Cells("C" & rowNum.ToString()).Value = row.FIF
                worksheet.Cells("D" & rowNum.ToString()).Value = row.HUN
                worksheet.Cells("E" & rowNum.ToString()).Value = row.ADD
                worksheet.Cells("F" & rowNum.ToString()).Value = row.OPP
                rowNum += 1
            Next

            ''Autofit columns for all cells
            worksheet.Cells.AutoFitColumns()

            package.Save()

        End Using

    End Sub

    '' Function to Parse the Excel files
    Private Function GetExcelData(ByVal filePath) As List(Of List(Of Object))
        Dim file As FileInfo = New FileInfo(filePath)
        Dim excelData As New List(Of List(Of Object))

        Using package1 As New ExcelPackage(file)
            '' Get the first worksheet in the workbook
            Dim worksheet = package1.Workbook.Worksheets(1)
            '' Get the range of cells of data
            Dim xml = worksheet.WorksheetXml.DocumentElement.FirstChild.Attributes(0).Value
            '' Get the data from the range of cells
            Using cellRange As ExcelRange = worksheet.Cells(xml)
                '' A row of data
                Dim row As New List(Of Object)
                '' Index of Rows
                For idxr As Integer = 0 To (cellRange.Rows - 1)
                    '' Index of Columns
                    For idxc As Integer = 0 To (cellRange.Columns - 1)
                        '' Get the data at Cell(Row, Column)
                        row.Add(cellRange.Value(idxr, idxc))
                    Next
                    '' Add the Row of data to the excelData list
                    excelData.Add(row)
                    '' Create the next Row to fill
                    row = New List(Of Object)
                Next
            End Using
        End Using

        Return excelData

    End Function

End Class

'' Data structures to parse and create Excel files
Public Class File1Info
    Public Property NSN As String
    Public Property ADD As String
    Public Property OPP As String
End Class

Public Class File2Info
    Public Property NSN As String
    Public Property NASC As String
    Public Property FIF As String
    Public Property HUN As String
End Class

Public Class File3Info
    Public Property NSN As String
    Public Property NASC As String
    Public Property FIF As String
    Public Property HUN As String
    Public Property ADD As String
    Public Property OPP As String
End Class

Open in new window

Hi Fernando

Thank you, will try it and get back to you.

Victor
Fernando,

Is there a way to avoid a third party control, If I'm able to combine file1 and file2 in one xml file as separate sheets? My office would like me to avoid using third party controls if at all possible.

Thanks,

        Try
            C1TrueDBGrid1.ExportToExcel("..\..\file1.xls", True)
            C1TrueDBGrid2.ExportToExcel("..\..\file2.xls", True)
    ' Export Multiple TDBGrids to multiple sheets of one Excel file
            Dim path As String = Application.StartupPath
       
            path = Directory.GetParent(Directory.GetParent(path).FullName).FullName
            Dim tempdir As String = Application.ExecutablePath.Substring(0, Application.ExecutablePath.LastIndexOf("\") + 1)
            Dim xlsFileName As String = tempdir + "file3" & ".xls"
            If System.IO.File.Exists(xlsFileName) Then
                System.IO.File.Delete(xlsFileName)
            End If
            ' clear the book
            C1XLBook1.Clear()
            C1XLBook1.Sheets.Clear()
           
 	     Dim fileName As String
            For Each fileName In System.IO.Directory.GetFiles(path, "*.xls")

                ' load Excel file
                Dim book As New C1.C1Excel.C1XLBook()
                book.Load(fileName)

                ' clone and rename first sheet (sheet names must be unique)
                Dim clone As C1.C1Excel.XLSheet = book.Sheets(0).Clone()
                clone.Name = System.IO.Path.GetFileNameWithoutExtension(fileName)

                ' add cloned sheet to main book
                C1XLBook1.Sheets.Add(clone)
            Next
            C1XLBook1.Save(xlsFileName)
            System.Diagnostics.Process.Start(xlsFileName)
        Catch
            MsgBox(Err.Description)
            Exit Sub
        End Try

                ' load Excel file
                Dim book As New C1.C1Excel.C1XLBook()
                book.Load(fileName)

                ' clone and rename first sheet (sheet names must be unique)
                Dim clone As C1.C1Excel.XLSheet = book.Sheets(0).Clone()
                clone.Name = System.IO.Path.GetFileNameWithoutExtension(fileName)

                ' add cloned sheet to main book
                C1XLBook1.Sheets.Add(clone)
            Next
            C1XLBook1.Save(xlsFileName)
            System.Diagnostics.Process.Start(xlsFileName)
        Catch
            MsgBox(Err.Description)
            Exit Sub
        End Try

Open in new window

Fernando,

Another solution to avoid using a third party control may be to
a)export the data from a datatable to and  xml file for each Grid, instead of using the datagrid to export to multiple excel files,

b) Use another code to merge xml files based on identical NSN to one xml file

c) Load the xml file to another Grid (makeGrid invisible)

d) Export the data from the Grid to excel.

Sounds like a lengthy process and would use lots or resources, hopefully there is another solution without using a third party control.

Open in new window


Example of code in actual project to load Grid1 and Grid2

 Dim SearchCriteria1 As String = "SN IN (" & countriesz & ") " '" & ")"
            Dim FilteredDTA As DataTable
            Dim DVA As New DataView(dtsetLinkAOP6A.Tables(0), SearchCriteria1, Nothing, DataViewRowState.CurrentRows)
            DVA.Sort = "SN"
            FilteredDTA = DVA.ToTable
            Me.C1Screen1.DataSource = FilteredDTA

' In separate method

 Dim SearchCriteria2 As String = yx
        Dim FilteredDTA As DataTable
        Dim DVAa As New DataView(dtsetLinkAOP6B.Tables(0), SearchCriteria2, Nothing, DataViewRowState.CurrentRows)
             FilteredDTA = DVAa.ToTable
             If FilteredDTA.Rows.Count > 0 Then
            Me.C1Screen2.DataSource = FilteredDTA

Open in new window


Victor
ASKER CERTIFIED SOLUTION
Avatar of Fernando Soto
Fernando Soto
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thank You.