XML To TREE VIEW

Use the Following Function to construct Treeview with the help of xml file.

public void XMLToTreeview(TreeView treeView, string fileName)
{

XmlTextReader reader = null;
try
{
// disabling re-drawing of treeview till all nodes are added
treeView.BeginUpdate();

reader =
new XmlTextReader(fileName);

TreeNode parentNode = null;

while (reader.Read())
{
if (reader.NodeType == XmlNodeType.Element)
{


TreeNode newNode = new TreeNode();
bool isEmptyElement = reader.IsEmptyElement;

newNode.Text = reader.Name;

// add new node to Parent Node or TreeView
if (parentNode != null)
parentNode.Nodes.Add(newNode);
else
treeView.Nodes.Add(newNode);

// making current node 'ParentNode' if its not empty
if (!isEmptyElement)
{
parentNode = newNode;
}
}
// moving up to in TreeView if end tag is encountered
else if (reader.NodeType == XmlNodeType.EndElement)
{


parentNode = parentNode.Parent;

}
else if (reader.NodeType == XmlNodeType.XmlDeclaration)
{ //Ignore Xml Declaration
}
else if (reader.NodeType == XmlNodeType.None)
{
return;
}
else if (reader.NodeType == XmlNodeType.Text)
{
parentNode.Nodes.Add(reader.Value);

}

}
}
finally
{

treeView.EndUpdate();
reader.Close();
}

}

Tool to Import and Export data from (and to) Excel File

Excel Tool to Import and Export data from (and to) Excel File

This small Application is really help to those who were in the need to get data from excel file and export to excel file

This is the screen shot of the application.





It have 3 buttons

Browse
Generate
Export to Excel

Browse

When user clicks on browse button one popup window will open it gets the filename along with path.



This is the code for browse button click

private void Browse_Click(object sender, EventArgs e)
{
OpenFileDialog ofd = new OpenFileDialog();
ofd.Title = "Select Excel file only";
ofd.CheckFileExists = false;
ofd.CheckPathExists = true;
ofd.AddExtension = true;
ofd.DefaultExt = "txt";
ofd.ShowReadOnly = true;
ofd.ShowHelp = true;
if (ofd.ShowDialog() == DialogResult.Cancel)
Application.Exit();
strfile = ofd.FileName.ToString();
}
Name and path of the file will be saved in strfile.

Generate Button

Generate Button will connect to the specified file and fill the grid with the excel file content.

coding for Generate Button click

private void button1_Click(object sender, EventArgs e)
{
DataSet objDataset1 = new DataSet();
string ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strfile + ";Extended Properties=Excel 5.0";
OleDbConnection objConn = new OleDbConnection(ConnectionString);
objConn.Open();
String strConString = "SELECT * from [Sheet1$]";
OleDbCommand objCmdSelect = new OleDbCommand(strConString, objConn);
OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();
objAdapter1.SelectCommand = objCmdSelect;
DataTable dt = new DataTable();
objAdapter1.Fill(objDataset1, "ExcelData");
dataGridView1.DataSource = objDataset1.Tables[0];
objConn.Close();
}
( Note check with query if your excel file does have sheet1 means just replace sheet1 to your sheet name.)

Export To Excel

Export to Excel button will export the content of grid to excel.



private void button2_Click(object sender, EventArgs e)
{
DataTable dt = (DataTable)dataGridView1.DataSource;
exportToExcel(dt, "c:\\Exportedtoexcel.xls");
MessageBox.Show("Exported to Excel successfully");
}


>Download the tool

Export to Excel with image.

Export to Excel with Image in VB.NET

This article clearly shows how to export image to excel file in VB.NET. First we look the full code then Description for each and ever statement.

[code]
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
AddImageIntoExcel()
End Sub
Dim Img As Bitmap = Image.FromFile("c:\test1.gif")
'place u r image file name with path in above double quoted area
Private Sub AddImageIntoExcel()
'Create an Excel App
Dim excelApp As New Microsoft.Office.Interop.Excel.Application()
excelApp.Visible = False
'The Excel doc paths
Dim oMissing As Object = System.Reflection.Missing.Value
Dim destFile As String = "C:\test_image.xlsx"
Dim excelFile As String = "C:\Book1.xlsx"
'Open the worksheet file
Dim excelBook As Microsoft.Office.Interop.Excel.Workbook
excelBook = excelApp.Workbooks.Open(excelFile)
Dim excelSheet As Microsoft.Office.Interop.Excel.Worksheet
excelSheet = CType(excelBook.Sheets.Item(1), Microsoft.Office.Interop.Excel.Worksheet)
Dim imgCell As Object = "G1"
Dim range As Microsoft.Office.Interop.Excel.Range
range = excelSheet.Range(imgCell)
range.Select()
Dim newImg As Bitmap = New Bitmap(80, 40)
Me.BackgroundImage = newImg
Using g As Graphics = Graphics.FromImage(newImg)
g.DrawImage(Img, New Rectangle(0, 0, newImg.Width, newImg.Height), _
0, 0, Img.Width, Img.Height, GraphicsUnit.Pixel)
End Using
Clipboard.SetDataObject(newImg)
excelSheet.Paste()
excelSheet.SaveAs(destFile)
'Quit
excelApp.Quit()
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp)
MessageBox.Show("image inserted!")
End Sub

End Class
[/code]

Image

Before going to draw the image in excel file we must load it in our application. we can achieve this by using the following statement.
[code]
Dim Img As Bitmap = Image.FromFile("c:\test1.gif")
[/code]
Or in other words we can say The above statement loads the image in img variable.

create the excel application by using below statement.
[code]
Dim excelApp As New Microsoft.Office.Interop.Excel.Application()
[/code]
create the excel book by the help of below statement
[code]
Dim destFile As String = "C:\test_image.xlsx"
Dim excelFile As String = "C:\Book1.xlsx"
'Open the worksheet file
Dim excelBook As Microsoft.Office.Interop.Excel.Workbook
excelBook = excelApp.Workbooks.Open(excelFile)
[/code]

Set the image displaying area by giving values to imgCell ( In our example its value is G1 so I will starts displaying at G1)

[code]
Dim excelSheet As Microsoft.Office.Interop.Excel.Worksheet
excelSheet = CType(excelBook.Sheets.Item(1), Microsoft.Office.Interop.Excel.Worksheet)
Dim imgCell As Object = "G1"
Dim range As Microsoft.Office.Interop.Excel.Range
range = excelSheet.Range(imgCell)
range.Select()
[/code]

Below is the key statement g.DrawImage function will draw the image in uppropriate place
[code]
Using g As Graphics = Graphics.FromImage(newImg)
g.DrawImage(Img, New Rectangle(0, 0, newImg.Width, newImg.Height), _
0, 0, Img.Width, Img.Height, GraphicsUnit.Pixel)
End Using
[/code]




Then the image will be copied into clipboard and the same will be paste in excel file the file will be saved in destination file path Finally the excel application will be quit. By using following statements.

[code]
Clipboard.SetDataObject(newImg)
excelSheet.Paste()
excelSheet.SaveAs(destFile)
'Quit
excelApp.Quit()
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp)
MessageBox.Show("image inserted!")
[/code]