Victor Charles
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
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
Will saving the result to a CSV file which can be opened in Excel an excepted solution?
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?
ASKER
Hi,
They are excel files.
Victor
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.
What version of Excel are you using?
Can you post Excel files for File1 and File2 from your original question please.
ASKER
Hi Fernando,
I am using execl 2013, will send you the excel files later today.
Thanks,
V
I am using execl 2013, will send you the excel files later today.
Thanks,
V
ASKER
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
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.
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.
ASKER
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
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.
Please write out an algorithm to follow and verify that it is correct before posting.
ASKER
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
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.
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.
ASKER
Hi Fernando,
I'm away this week, will send you more information when back in the office next week.
Thank You.
Victor
I'm away this week, will send you more information when back in the office next week.
Thank You.
Victor
ASKER
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
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.
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
ASKER
Hi Fernando
Thank you, will try it and get back to you.
Victor
Thank you, will try it and get back to you.
Victor
ASKER
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,
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
ASKER
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.
Example of code in actual project to load Grid1 and Grid2
Victor
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.
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
Victor
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank You.