Inserting A Picture To Excel Document Using C#

May 27, 2010

The following code can be used to insert an image into Excel document using C#.


        using System;
        using System.Windows.Forms;
        using Microsoft.Office.Interop.Excel;
        using System.Reflection;
        using System.Runtime.InteropServices;

        void button1_Click(object sender, EventArgs e)
        {

            ApplicationClass objExcel = null;
            Workbooks objBooks = null;
            _Workbook objBook = null;
            Sheets objSheets = null;
            _Worksheet objSheet = null;
            Range objRange = null;
            int row = 1, col = 1;
            try
            {

                objExcel = new ApplicationClass();
                objBooks = objExcel.Workbooks;
                objBook = objBooks.Add(XlWBATemplate.xlWBATWorksheet);
                objSheets = objBook.Worksheets;
                objSheet = (_Worksheet)objSheets.get_Item(1);
                objRange = (Range)objSheet.Cells[row, col];
                //Insert picture into Excel Shee
                Picture picture = ((Pictures)objSheet.Pictures(Type.Missing)).Insert(@"C:\Documents and Settings\All Users\Documents\My Pictures\Sample Pictures\Sunset.jpg", Type.Missing);

                //Save Excel document
                objSheet.Name = "Sample Sheet";
                object objOpt = Missing.Value;
                objBook.SaveAs(@"C:\TestSheet1.xls", objOpt, objOpt, objOpt, objOpt, objOpt, XlSaveAsAccessMode.xlNoChange, objOpt, objOpt, objOpt, objOpt, objOpt);
                objBook.Close(false, objOpt, objOpt);

            }
            catch
            {
                MessageBox.Show("Error Inserting Picture in Excel Document");
            }
            finally
            {
                objExcel = null;
                objBooks = null;
                objBook = null;
                objSheets = null;
                objSheet = null;
                objRange = null;
                ReleaseComObject(objExcel);
                ReleaseComObject(objBooks);
                ReleaseComObject(objBook);
                ReleaseComObject(objSheets);
                ReleaseComObject(objSheet);
                ReleaseComObject(objRange);
            }
        }
        //Release COM objects from memory
        public void ReleaseComObject(object reference)
        {
            try
            {
                while (System.Runtime.InteropServices.Marshal.ReleaseComObject(reference) <= 0)
                {
                }
            }
            catch
            {
            }
        }

Advertisements

Populating TreeView Using XElement in C#

May 27, 2010

You can parse an XML document using XElement class available in System.Xml.Linq namespace. The following code can be used to populate a Windows Forms TreeView control using XElement .

XML Content

<?xml version="1.0" encoding="utf-8" ?>
<States>
  <State name="Tamilnadu">
    <Regions>
      <Region name="North">
        <City name="Chennai" />
        <City name="Vellore" />
      </Region>
      <Region name="South">
        <City name="Madurai"/>
        <City name="Trichy"/>
      </Region>
      <Region name="West">
        <City name="Coimbatore"/>
        <City name="Erode"/>
      </Region>
    </Regions>
  </State>
  <State name="Andhra">
    <Regions>
      <Region name="Rayalaseema">
        <City name ="Chittoor"/>
        <City name="Kurnool"/>
      </Region>
      <Region name="Telangana">
        <City name="Hydrabad" />
        <City name="Warangal" />
        <City name="Nizamabad" />
      </Region>
    </Regions>
  </State>
</States>

C# Code to Populate TreeView

private void button1_Click(object sender, EventArgs e)
        {
           var xmlData = XElement.Load(@"C:\Work\States.xml");
            TreeNode treeNode = treeView1.Nodes.Add("India");
            LoadXmlElements(xmlData, treeNode);
        }

        private void LoadXmlElements(XElement xElem, TreeNode treeNode)
        {
            foreach (XElement element in xElem.Elements())
            {
                if (element.HasElements)
                {
                    if (element.FirstAttribute != null)
                    {
                        TreeNode tempNode = treeNode.Nodes.Add(element.FirstAttribute.Value);
                        LoadXmlElements(element, tempNode);
                    }
                    else
                        LoadXmlElements(element, treeNode);                    
                }
                else
                    treeNode.Nodes.Add(element.FirstAttribute.Value);
            }
        }

Column Filtering in a DataTable

April 7, 2010

We can filter specific columns of a DataTable and create another DataTable for those filtered columns using the following code snippet.


DataTable dtEmp = new DataTable();
dtEmp.Columns.Add("EmpNo", System.Type.GetType("System.Int32"));
dtEmp.Columns.Add("EmpName", System.Type.GetType("System.String"));
dtEmp.Columns.Add("Salary", System.Type.GetType("System.String"));
dtEmp.Columns.Add("Department", System.Type.GetType("System.String"));

dtEmp.Rows.Add(new object[] { 1001, "John", 45000, "Operations" });
dtEmp.Rows.Add(new object[] { 1002, "Harry", 65000, "Accounting" });
dtEmp.Rows.Add(new object[] { 1003, "Sujith", 75000, "Purchase" });
dtEmp.Rows.Add(new object[] { 1003, "Sujith", 51000, "Operations" });
dtEmp.Rows.Add(new object[] { 1003, "Sujith", 61000, "Purchase" });

DataTable dtFiltered = dtEmp.DefaultView.ToTable(false, new string[] { "EmpNo", "Salary" });

In the above code snippet, the DataTable object dtFiltered will have rows from dtEmp only for EmpNo and Salary columns.

The DataTable.DefaultView property returns a DataView object which can be used for sorting, filtering, searching, editing, and navigation operations . The DataView.ToTable() method creates and returns a new DataTable object based on the arguments passed to it. The first argument in this method is used to determine whether the output DataTable should have distinct values for all its columns. The next argument is an array of column names for which the output DataTable will have rows.

Copy Clipboard Data To Textbox Using JavaScript

April 3, 2010

The object.execCommand() javascript method can be to paste the text from clipboard to the current object such as textbox control. It executes a command on the current document, current selection, or the given range.

Here is the code.


 function PasteTextFromClipboard()
        {
            document.getElementById('textArea1').focus();
            var strText = document.getElementById('textArea1').createTextRange();
            strText.execCommand("Paste");
        }

Changing the origin of the drawing coordinate system in System.Drawing in C#

April 3, 2010

The origin in System.Drawing.Graphics class is typically the upper-left-hand corner of the drawing surface (0,0) . If you want to change default coordinates(0,0) where rendering begins, use TranslateTransform method() of Graphics class.

It helps to change the coordinates from point (0, 0) to some point (m, n). For example, The following code will draw a line from point (0, 0) to point (120, 80) with origin (50, 40) .


Graphics g = e.Graphics;
g.TranslateTransform(50, 40);
Point A = new Point(0, 0);
Point B = new Point(120, 80);
g.DrawLine(Pens.Black, A, B);

I posted this code to answer someone’s question in MSDN forums.

Detecting Form Field Changes in Windows Forms using C#

April 3, 2010

Sometimes we may need to have a windows form that has lot of textboxes, dropdown lists, radio buttons, checkboxes etc. If is difficult to determine whether the form fields are changed by checking each and every fields with their respective initial values.

In this case, you can try to implement a MessageFilter to the windows form that detect key down and left mouse up events and check if current focused control is changed if user generate these two events. It means that the value of the current focused control assumed to be changed if user pressed a key or clicked left mouse on it.

IMessageFilter in System.Windows.Forms namespace is an interface that allows an application to capture a message before it is dispatched to a control or form.

A class that implements the IMessageFilter interface can be added to the application’s message pump to filter out a message or perform other operations before the message is dispatched to a form or control.

The class associated with a Windows Form control should be inherited from both System.Windows.Forms.Form and System.Windows.Forms.IMessageFilter in order to implement this functionality.

Here is the code.


using System;
using System.Windows.Forms;

namespace WinFormTest1
{
    public partial class MessageFilterDemoForm : Form, IMessageFilter
    {
        const int WM_KEYUP = 0x101;
        bool isFormFieldChanged = false;

        public MessageFilterDemoForm()
        {
            Application.AddMessageFilter(this);
            InitializeComponent();
        }

        public bool PreFilterMessage(ref Message m)
        {
            bool ret = true;
            if (m.Msg == WM_KEYUP)
            {
                if (this.ActiveControl is TextBox)
                {
                    isFormFieldChanged = true;
                    ret = true;
                }
            }
            else
            {
                ret = false;
            }
            return ret;
        }

        private void button1_Click(object sender, EventArgs e)
        {
            MessageBox.Show("isFormFieldChanged : " + isFormFieldChanged.ToString());
        }
    }
}


Concatenating two string columns in a DataTable

April 3, 2010

The Expression property in DataColumn object of DataTable can be used to create calculated columns.

Assume that a DataTable contain two columns First Name and Last Name of string type. Suppose that you need to create a column in a DataTable by concatenating these two existing columns, you can simply use the ‘+’ operator in the Expression property of the newly created column. It will concatenate the First Name and Last Name without any space. If you want to add a ‘-‘ operator between the First Name and Last Name, the following code will help you.


DataTable dtTest = new DataTable();
dtTest.Columns.Add("FirstName", System.Type.GetType("System.String"));
dtTest.Columns.Add("LastName", System.Type.GetType("System.String"));
DataColumn dcFullName = new DataColumn("FullName");
dcFullName.Expression = string.Format("{0}+'-'+{1}", "FirstName", "LastName");
dtTest.Columns.Add(dcFullName);

Moving a file from one folder to another folder on a ftp site using C#

April 3, 2010

The following code snippet can be used to move a file from one folder to another folder on an ftp site. I have written this code to answer a question in MSDN forum. It is marked as answered.

I have used System.Net.WebClient object to perform this operation. This class provides common methods for sending data to and receiving data from a resource identified by a URI. The following steps are performed to move a file.

  • Download the source file from ftp location to a folder in local PC.
  • Once the download is completed, uploading the file from local PC to another location on the ftp site.
  • Delete the file from local PC

using System;
using System.ComponentModel;
using System.Windows.Forms;

namespace WinFormTest1
{
  public partial class FtpFileMoveSample : Form
  {
    public FtpFileMoveSample()
    {
       InitializeComponent();
     }

   private void button1_Click(object sender, EventArgs e)
  {
     System.Net.WebClient wc1 = new System.Net.WebClient();
     wc1.DownloadFileCompleted += new AsyncCompletedEventHandler(wc_DownloadFileCompleted);
     System.Uri uri1 = new Uri("ftp://ftpUserId:Password@ftpServerIP/abc.txt");
     wc1.DownloadFileAsync(uri1, @"D:\Temp\abc.txt");
  }

  static void wc_DownloadFileCompleted(object sender, AsyncCompletedEventArgs e)
  {
     System.Net.WebClient wc2 = new System.Net.WebClient();
     wc2.UploadFileCompleted += new System.Net.UploadFileCompletedEventHandler(wc_UploadFileCompleted);
     System.Uri url2 = new Uri("ftp://ftpUserId:Password@ftpServerIP/Test/xyz.txt");
     wc2.Proxy = null;
     wc2.UploadFileAsync(url2, @"D:\Temp\abc.txt");
  }

  static void wc_UploadFileCompleted(object sender, System.Net.UploadFileCompletedEventArgs e)
  {
     System.IO.File.Delete(@"D:\Temp\abc.txt");
     MessageBox.Show("File has been moved");
   }
 }
}

Problem connecting VS 2005 to SQL Server 2008

January 21, 2010

Sometimes it is not possible to connect Visual Studio 2005 to SQL Server 2008.

It will throw the following error message when you try to use VS 2005 Service Pack 1 design tools to open a database connection to Microsoft SQL Server 2008:

“This Server version is not supported. You must have Microsoft SQL Server 2005 Beta 2 or later”

You need to install the VS 2005 SP1 update for SQL Server 2008. You can download the update from the following link:

http://www.microsoft.com/downloads/details.aspx?FamilyID=e1109aef-1aa2-408d-aa0f-9df094f993bf&DisplayLang=en

Refer my post in dotnetspider.com

DataTable to Excel With Bulk Insert

January 21, 2010

Sometimes you may need to generate report as Excel document from your ASP.NET application. I have written this article in dotnetspider.com that describes about exporting .NET DataTable into Excel document. Click DataTable to Excel With Bulk Insert.

The Excel report can be generated in different ways.
(i) Using XML Spreadsheet
(ii) Using Excel COM objects.

In XML spreadsheet, the content of the Excel report is written using XML tags. When the file created using XML spreadsheet tags is opened, the Excel application renders the content in Excel format. The Excel Spreadsheet XML format supports element tags and attributes for Excel functionality such as multi-sheet workbooks, formulas, and cell formatting.

The other way is generating Excel report using Excel object model. The followings objects are important to generate an Excel document.
1. Application
2. Workbook
3. Worksheet
4. Range

To make the above objects available to your project, you need to add reference to the assembly “Microsoft.Office.Interop.Excel” version 12.0. This assembly uses Excel COM objects to generate Excel reports. To generate report using Excel object model, Excel application has to be installed and configured in the server where the ASP.NET application is deployed.

The following code snippet can be used to write the contents of a DataTable into Excel document using the Excel object model.


using System;
using System.Data;
using System.Collections.Generic;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Microsoft.Office.Interop.Excel;
using System.Reflection;

public partial class TestPage1 : System.Web.UI.Page
{
   
    protected void Button1_Click(object sender, EventArgs e)
    {       
        ApplicationClass objExcel = null;
        Workbooks objBooks = null;
        _Workbook objBook = null;
        Sheets objSheets = null;
        _Worksheet objSheet = null;
        Range objRange = null;
        int row = 1, col = 1;
        try
        {

            //Get your database records into a DataTable
            System.Data.DataTable dtCustmer = GetAllCustomers();
            objExcel = new ApplicationClass();
            objBooks = objExcel.Workbooks;
            objBook = objBooks.Add(XlWBATemplate.xlWBATWorksheet);

            //Print column heading in the excel sheet
            int j = col;
            foreach (DataColumn column in dtCustmer.Columns)
            {

                objSheets = objBook.Worksheets;
                objSheet = (_Worksheet)objSheets.get_Item(1);
                objRange = (Range)objSheet.Cells[row, j];
                objRange.Value2 = column.ColumnName;
                j++;
            }
            row++;

            //Write the content of each row into Excel cells
            int count = dtCustmer.Columns.Count;
            foreach (DataRow dataRow in dtCustmer.Rows)
            {
                int k = col;
                for (int i = 0; i < count; i++)
                {
                    objRange = (Range)objSheet.Cells[row, k];
                    objRange.Value2 = dataRow[i].ToString();
                    k++;
                }
                row++;
            }

            //Save Excel document
            objSheet.Name = “Sample Sheet”;
            object objOpt = Missing.Value;
            objBook.SaveAs(@”C:\TestSheet1.xls”, objOpt, objOpt, objOpt, objOpt, objOpt, XlSaveAsAccessMode.xlNoChange, objOpt, objOpt, objOpt, objOpt, objOpt);
            objBook.Close(false, objOpt, objOpt);

        }
        catch
        {
        }
        finally
        {
            objExcel = null;
            objBooks = null;
            objBook = null;
            objSheets = null;
            objSheet = null;
            objRange = null;
            ReleaseComObject(objExcel);
            ReleaseComObject(objBooks);
            ReleaseComObject(objBook);
            ReleaseComObject(objSheets);
            ReleaseComObject(objSheet);
            ReleaseComObject(objRange);
        }
    }   

    //Release COM objects from memory
    public void ReleaseComObject(object reference)
    {
        try
        {
            while (System.Runtime.InteropServices.Marshal.ReleaseComObject(reference) <= 0)
            {
            }
        }
        catch
        {
        }
    }

    //Returns a DataTable. Modify as you needed
    public System.Data.DataTable GetAllCustomers()
    {
        System.Data.DataTable dt = new System.Data.DataTable();
        dt.Columns.Add(new DataColumn(“Customer_Id”, typeof(int)));
        dt.Columns.Add(new DataColumn(“Name”, typeof(string)));
        dt.Columns.Add(new DataColumn(“Email_Id”, typeof(string)));
        dt.Rows.Add(new object[] { 1001, “Customer 1”, “customer1@TestSite.com” });
        dt.Rows.Add(new object[] { 1002, “Customer 2”, “customer2@TestSite.com” });
        dt.Rows.Add(new object[] { 1003, “Customer 3”, “customer3@TestSite.com” });
        dt.Rows.Add(new object[] { 1004, “Customer 4”, “customer4@TestSite.com” });
        dt.Rows.Add(new object[] { 1005, “Customer 5”, “customer5@TestSite.com” });
        return dt;
    }
}

In the above code snippet, each row of DataTable is read using a ‘for’ loop and then written to particular row and column of the Excel datasheet. The ‘for’ loop is executed by .NET code and then pass the DataRow value to Excel COM object. Then the Excel object writes the content to Spreadsheet.
If the DataTable contains thousands of records, then it may take considerable amount of time to generate the Excel report. In web applications, the users will not like to have long time to generate a report.

A work around solution is available to address this problem. We need to convert the DataTable into a two dimensional array and the pass the array to the Excel COM object. The Excel object modal accepts arrays and writes the content into Excel worksheet. The Excel object itself automatically loops through the array and then writes the content into worksheet. Since the looping through the array is executed by Excel COM object, this approach will take less time to generate the report as comparing with the above approach. The following code snippet will help to generate the Excel report from a DataTable using this work around solution.


protected void Button2_Click(object sender, EventArgs e)
{
ApplicationClass objExcel = null;
Workbooks objBooks = null;
_Workbook objBook = null;
Sheets objSheets = null;
_Worksheet objSheet = null;
Range objRange = null;
int row = 1, col = 1;

try
{
System.Data.DataTable dtCustmer = GetAllCustomers();

objExcel = new ApplicationClass();
objBooks = objExcel.Workbooks;
objBook = objBooks.Add(XlWBATemplate.xlWBATWorksheet);

objSheets = objBook.Worksheets;
objSheet = (_Worksheet)objSheets.get_Item(1);
object objOpt = Missing.Value;

//Print column heading in the excel sheet
int j = 1;
foreach (DataColumn column in dtCustmer.Columns)
{
objRange = (Range)objSheet.Cells[row, j];
objRange.Value2 = column.ColumnName;
objRange.Interior.ColorIndex = 5;
j++;
}
row++;

//declare an object array
object[,] objData = new Object[dtCustmer.Rows.Count, dtCustmer.Columns.Count];

//Copy datatabe into array
int r = 0, c = 0;
foreach (DataRow dataRow in dtCustmer.Rows)
{
objData[r, c] = dataRow[“Customer_Id”];
objData[r, c + 1] = dataRow[“Name”];
objData[r, c + 2] = dataRow[“Email_Id”];
r++;
}

objRange = (Range)objSheet.Cells[row, col];
if (objData.GetUpperBound(0) > 0)
objRange = objRange.get_Resize(objData.GetUpperBound(0) + 1, objData.GetUpperBound(1) + 1);
objRange.Value2 = objData;

objSheet.Name = “Sample Sheet”;
objBook.SaveAs(@”C:\TestSheet2.xls”, objOpt, objOpt, objOpt, objOpt, objOpt, XlSaveAsAccessMode.xlNoChange, objOpt, objOpt, objOpt, objOpt, objOpt);
objBook.Close(false, objOpt, objOpt);

}
catch
{
}
finally
{
objExcel = null;
objBooks = null;
objBook = null;
objSheets = null;
objSheet = null;
objRange = null;
ReleaseComObject(objExcel);
ReleaseComObject(objBooks);
ReleaseComObject(objBook);
ReleaseComObject(objSheets);
ReleaseComObject(objSheet);
ReleaseComObject(objRange);
}
}