Prasun:
To be honest, there are many issues here and it's difficult to nail down
what specifically is your problem. I'm including some code below that
creates three datasets and uses merge to add them together. Each one has 10
rows. After the first merge, the merged dataset has 20 rows. After the
second merge, the merged dataset's table has 30 rows.
Now I'd mention a few things. If you are just trying to set every instance
of "Tech Allowed" for instance to "Allowed Amount", I'd simply exclude the
column altogther and just create an expression column for "Tech Allowed"
that had an equation equal to "Allowed Amount". This way, whenever Allowed
Amount is changed (and the same logic holds for the other columns), then the
change is immediately reflected in Tech Allowed. This also gets you out of
having to Loop through the values.
Two other things come to mind. 1 is that you can use the DataAdapter to
submit your changes to the Worksheets without having to dig into the com
interop layer. Second, depending on the version of excel you're using, you
can use the WriteXml method of the Dataset and then just import this file
into excel which is a bit cleaner than what you are doing here.
In order to use my code, just substitute the 'fill'ed datasets with the
datasets that I'm manually populating in the loops. As long as you have a
primary key stipulated and the the rowname/type of the pk column throughout
the datasets is the same, it will work and it's quite straightforward.
Here's the logic that if you replicate, will work:
DataSet BaseData = new DataSet("BaseData");
DataTable SampleTable = new DataTable("TestTable");
BaseData.Tables.Add(SampleTable);
DataColumn ExpressionColumn = new DataColumn("Indexer",
typeof(System.Int32));
DataColumn[] KeyColumn = new DataColumn[] {ExpressionColumn};
SampleTable.Columns.Add(ExpressionColumn);
SampleTable.PrimaryKey = KeyColumn;
for(int i = 0; i < 10; i++){
DataRow dro = SampleTable.NewRow();
dro[0] = i;
SampleTable.Rows.Add(dro);
}
DataSet SecondaryData = new DataSet("SecondaryData");
DataTable SecondarySampleTable = new DataTable("TestTable");
SecondaryData.Tables.Add(SecondarySampleTable);
DataColumn SecondaryExpressionColumn = new DataColumn("Indexer",
typeof(System.Int32));
DataColumn[] SecondaryKeyColumn = new DataColumn[]
{SecondaryExpressionColumn};
SecondarySampleTable.Columns.Add(SecondaryExpressionColumn);
SecondarySampleTable.PrimaryKey = SecondaryKeyColumn;
for(int i = 10; i < 20; i++){
DataRow dro = SecondarySampleTable.NewRow();
dro[0] = i;
SecondarySampleTable.Rows.Add(dro);
}
DataSet ThirdData = new DataSet("ThirdData");
DataTable ThirdSampleTable = new DataTable("TestTable");
ThirdData.Tables.Add(ThirdSampleTable);
DataColumn ThirdExpressionColumn = new DataColumn("Indexer",
typeof(System.Int32));
DataColumn[] ThirdKeyColumn = new DataColumn[] {ThirdExpressionColumn};
ThirdSampleTable.Columns.Add(ThirdExpressionColumn);
ThirdSampleTable.PrimaryKey = ThirdKeyColumn;
for(int i = 20; i < 30; i++){
DataRow dro = ThirdSampleTable.NewRow();
dro[0] = i;
ThirdSampleTable.Rows.Add(dro);
}
Console.WriteLine("Base Data Rows: " +
BaseData.Tables[0].Rows.Count.ToString()); //10
BaseData.Merge(SecondaryData);
Console.WriteLine("1st Merge Rows: " +
BaseData.Tables[0].Rows.Count.ToString()); //20
BaseData.Merge(ThirdData);
Console.WriteLine("2nd Rows: " + BaseData.Tables[0].Rows.Count.ToString());
//30
Console.ReadLine();
--
W.G. Ryan, MVP
www.tibasolutions.com |
www.devbuzz.com |
www.knowdotnet.com
Prasun said:
Here is my code. It does not seem to work. The output does not change
ANy Ideas?
Prasun
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
Try
Dim XLFileConn As New OleDbConnection 'New OleDbConnection object
XLFileConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\Test_001.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""
XLFileConn.Open()
Dim XLFileCmd1 As New OleDbCommand 'New OleDbCommand object
XLFileCmd1.Connection = XLFileConn 'Setting the command object to use the
current connection to the excel file
Dim XLFileDA As OleDbDataAdapter = New OleDbDataAdapter 'OleDataAdapter
Object
Dim XLFileMainDS = New DataSet("MainDataSet") 'DataSet Object
Dim TestDS = New DataSet("TestSet")
Dim TestDS1 = New DataSet("TestSet1")
Dim TableMain As DataTable = XLFileMainDS.Tables.Add("TableWithMain") 'New
DataTable Object created and added to the DataSet
XLFileCmd1.CommandText = "SELECT * FROM [Sheet1$] WHERE Modify is NULL"
'Select Statement to choose the correct rows from the excel file
XLFileDA.SelectCommand = XLFileCmd1 'Set DataAdapter's Select command to the
command stored by the Oledbcommand object
XLFileDA.Fill(XLFileMainDS, "TableWithMain") 'Fill the table in the DataSet
with appropriate columns
Dim cnt As Integer = XLFileMainDS.Tables(0).Rows.Count() 'Variable to count
the number of rows selecteds
Dim TableTC As DataTable = TestDS.Tables.Add("TableWithModTC") 'New
DataTable Object created and added to the DataSet
XLFileCmd1.CommandText = "SELECT DISTINCT ProcCode, Modify FROM [Sheet1$]"
XLFileDA.SelectCommand = XLFileCmd1 'Set DataAdapter's Select command to the
command stored by the Oledbcommand object"
XLFileDA.Fill(TestDS, "TableWithModTC") 'Fill the table in the DataSet with
appropriate columns
Dim cnt1 As Integer = TestDS.Tables(0).Rows.Count() 'Variable to count the
number of rows selecteds
Dim Table26 As DataTable = TestDS1.Tables.Add("TableWithMod26") 'New
DataTable Object created and added to the DataSet
XLFileCmd1.CommandText = "SELECT * FROM [Sheet1$] WHERE trim (Modify) =
'26'" 'Select Statement to choose the correct rows from the excel file
XLFileDA.SelectCommand = XLFileCmd1 'Set DataAdapter's Select command to the
command stored by the Oledbcommand object
XLFileDA.Fill(TestDS1, "TableWithMod26") 'Fill the table in the DataSet with
appropriate columns
Dim cnt2 As Integer = TestDS1.Tables(0).Rows.Count() 'Variable to count the
number of rows selecteds
Dim DataRowTC As DataRow
For Each DataRowTC In TestDS.Tables("TableWithModTC").Rows
DataRowTC("Tech Amt") = DataRowTC("Amt") 'to 'Tech Amt', 'Tech Allowed' and
'Prof Amt' and 'Prof Allowed'
DataRowTC("Amt") = "0"
DataRowTC("Tech Allowed") = DataRowTC("Allowed Amount")
DataRowTC("Allowed Amount") = "0"
Next
Dim DataRow26 As DataRow
For Each DataRow26 In TestDS1.Tables("TableWithMod26").Rows
DataRow26("Prof Amt") = DataRow26("Amt")
DataRow26("Amt") = "0"
DataRow26("Prof Allowed") = DataRow26("Allowed Amount")
DataRow26("Allowed Amount") = "0"
Next
XLFileMainDS.Merge(TestDS)
XLFileMainDS.Merge(TestDS1)
Dim oXL As Excel.Application 'Excel Application Object
Dim oWB As Excel.Workbook 'Excel Workbook Object
Dim oSheet As Excel.Worksheet 'Excel Worksheet Object
Dim oRng As Excel.Range 'Excel Range Object
Try
oXL = CreateObject("Excel.Application") 'Start Excel and get Application
object.
oXL.Visible = False 'Turn off the Excel file's visibility
oXL.ScreenUpdating = False 'Turn off Screen updating till all the cells are
filled
oWB = oXL.Workbooks.Add 'Get a new workbook.
oSheet = oWB.ActiveSheet 'Set the Sheet Object to the current (active) Sheet
'oWB.Worksheets.Add()
Dim XLOutDataColumn As DataColumn 'DataColumn Object to read the Columns
from the Main Table in the Dataset
Dim NumOfCols As Double = 0 'Variable to hold the number of columns in the
table
For Each XLOutDataColumn In XLFileMainDS.Tables("TableWithMain").Columns
'Code to set the headers for the column
oSheet.Range("A1").Offset(0, NumOfCols).Value = XLOutDataColumn.ColumnName
NumOfCols += 1
Next
Dim NumOfRows As Int32
For NumOfRows = 0 To XLFileMainDS.Tables("TableWithMain").Rows.Count - 1
'Code to fill up the data in the output excel file
oSheet.Range("A2").Offset(NumOfRows).Resize(1, NumOfCols).Value = _
XLFileMainDS.Tables("TableWithMain").Rows(NumOfRows).ItemArray()
Next
Finally
oXL.ScreenUpdating = True 'Turn on Screen Updating
oXL.Visible = True 'Show / Make Visible the Result Excel File
End Try
With oXL.ActiveSheet.Range("A1") 'Code to format the Output - Auto filter
and Simple Border
.AutoFilter()
.AutoFormat(Excel.XlRangeAutoFormat.xlRangeAutoFormatSimple)
End With
oWB = Nothing 'Code to set object to nothing - Release memory
oSheet = Nothing
oRng = Nothing
oXL = Nothing
XLFileConn.Close()
Finally
'Close the connection to the excel file
GC.Collect()
End Try
End Sub
