Reading Excel using OpenXML

public DataTable ReadDataFromExcel()
       {
        string filePath = @"c:/temp/temp.xlsx";
           using (SpreadsheetDocument LobjDocument = SpreadsheetDocument.Open(filePath, false))
           {
           
               WorkbookPart LobjWorkbookPart = LobjDocument.WorkbookPart;
               Sheet LobjSheetToImport = LobjWorkbookPart.Workbook.Descendants<Sheet>().First<Sheet>();
               WorksheetPart LobjWorksheetPart = (WorksheetPart)(LobjWorkbookPart.GetPartById(LobjSheetToImport.Id));
               SheetData LobjSheetData = LobjWorksheetPart.Worksheet.Elements<SheetData>().First();

               //Read only the data rows and skip all the header rows.
               int LiRowIterator = 1;

               //  for progress bar
               int LiTotal = LobjSheetData.Elements<Row>().Count() - MobjImportMapper.HeaderRowIndex;
               // =================

               foreach (Row LobjRowItem in LobjSheetData.Elements<Row>().Skip(6))
               {

                   DataRow LdrDataRow = LdtExcelData.NewRow();
                   int LiColumnIndex = 0;
                   int LiHasData = 0;
                   LdrDataRow[LiColumnIndex] = LobjRowItem.RowIndex; //LiRowIterator;
                   LiColumnIndex++;

                   //TODO: handle restriction of column range.
                   foreach (Cell LobjCellItem in LobjRowItem.Elements<Cell>().Where(PobjCell
                       => ImportHelper.GetColumnIndexFromExcelColumnName(ImportHelper.GetColumnName(PobjCell.CellReference))
                       <= MobjImportMapper.LastColumnIndex))
                   {
                    
                       // Gets the column index of the cell with data
                       int LiCellColumnIndex = 10;
                       if (LiColumnIndex < LiCellColumnIndex)
                       {
                           do
                           {
                               LdrDataRow[LiColumnIndex] = string.Empty;
                               LiColumnIndex++;
                           }
                           while (LiColumnIndex < LiCellColumnIndex);
                       }

                       string LstrCellValue = LobjCellItem.InnerText;

                       if (LobjCellItem.DataType != null)
                       {
                           switch (LobjCellItem.DataType.Value)
                           {
                               case CellValues.SharedString:
                                   var LobjStringTable = LobjWorkbookPart.GetPartsOfType<SharedStringTablePart>().FirstOrDefault();
                                   DocumentFormat.OpenXml.OpenXmlElement LXMLElment = null;
                                   string LstrXMLString = String.Empty;
                                   if (LobjStringTable != null)
                                   {
                                       LstrXMLString =
                                           LobjStringTable.SharedStringTable.ElementAt(int.Parse(LstrCellValue, CultureInfo.InvariantCulture)).InnerXml;

                                       if (LstrXMLString.IndexOf("<x:rPh", StringComparison.CurrentCulture) != -1)
                                       {
                                           LXMLElment = LobjStringTable.SharedStringTable.ElementAt(int.Parse(LstrCellValue, CultureInfo.InvariantCulture)).FirstChild;
                                           LstrCellValue = LXMLElment.InnerText;
                                       }
                                       else
                                       {
                                           LstrCellValue = LobjStringTable.SharedStringTable.ElementAt(int.Parse(LstrCellValue, CultureInfo.InvariantCulture)).InnerText;

                                       }
                                   }
                                   break;
                               default:
                                   break;
                           }
                       }
                       LdrDataRow[LiColumnIndex] = LstrCellValue.Trim();
                       if (!string.IsNullOrEmpty(LstrCellValue))
                           LiHasData++;
                      LiColumnIndex++;
                   }

                   if (LiHasData > 0)
                   {
                       LiRowIterator++;
                       LdtExcelData.Rows.Add(LdrDataRow);
                   }
               }
           }
          

           return LdtExcelData;
       }

Continue reading »

Reading XML Content

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml.Linq;
using System.Diagnostics;
using System.Threading;
using System.Xml;
using System.Reflection;

namespace XMLReading

{

class Program
    {
static void Main(string[] args)
        {

string fileName = @"C:\temp\t.xml";
List<EmergencyContactXMLDTO> emergencyContacts = new XmlReader<EmergencyContactXMLDTO, EmergencyContactXMLDTOMapper>().Read(fileName);

foreach (var item in emergencyContacts)
            {
Console.WriteLine(item.FileNb);
            }
         }
    }

public class XmlReader<TDTO, TMAPPER> where TDTO : BaseDTO, new() where TMAPPER : PCPWXMLDTOMapper, new()
    {
public List<TDTO> Read(String fileName)
        {
XmlTextReader reader = new XmlTextReader(fileName);
List<TDTO> emergencyContacts = new List<TDTO>();
while (true)
            {
                TMAPPER mapper = new TMAPPER();
bool isFound = SeekElement(reader, mapper.GetMainXMLTagName());
if (!isFound) break;
                TDTO dto = new TDTO();
foreach (var propertyKey in mapper.GetPropertyXMLMap())
                {
String dtoPropertyName = propertyKey.Key;
String xmlPropertyName = propertyKey.Value;
                    SeekElement(reader, xmlPropertyName);
                    SetValue(dto, dtoPropertyName, reader.ReadElementString());
                }
                emergencyContacts.Add(dto);
            }
return emergencyContacts;
        }

private void SetValue(Object dto, String propertyName, String value)
        {
PropertyInfo prop = dto.GetType().GetProperty(propertyName, BindingFlags.Public | BindingFlags.Instance);
            prop.SetValue(dto, value, null);
        }

private bool SeekElement(XmlTextReader reader, String elementName)
        {
while (reader.Read())
            {
XmlNodeType nodeType = reader.MoveToContent();
if (nodeType != XmlNodeType.Element)
                {
continue;
                }

if (reader.Name == elementName)
                {
return true;
                }
            }
return false;
        }
    }

public class BaseDTO

    {

    }

public class EmergencyContactXMLDTO : BaseDTO
    {

public string FileNb { get; set; }

public string ContactName { get; set; }

public string ContactPhoneNumber { get; set; }

public string Relationship { get; set; }

public string DoctorName { get; set; }

public string DoctorPhoneNumber { get; set; }

public string HospitalName { get; set; }

    }

public interface PCPWXMLDTOMapper
    {
Dictionary<string, string> GetPropertyXMLMap();
String GetMainXMLTagName();
    }

public class EmergencyContactXMLDTOMapper : PCPWXMLDTOMapper
    {
public Dictionary<string, string> GetPropertyXMLMap()
        {
return new Dictionary<string, string>
            {
                { "FileNb", "XFileNb" },
                { "ContactName", "XContactName"},
                { "ContactPhoneNumber", "XContactPhoneNumber" },
                { "Relationship", "XRelationship" },
                { "DoctorName", "XDoctorName" },
                { "DoctorPhoneNumber", "XDoctorPhoneNumber" },
                { "HospitalName", "XHospitalName" },
            };
        }

public String GetMainXMLTagName()
        {
return "EmergencyContact";
        }
    }
}

Continue reading »

Reading Excel using ClosedXML

I have used closedXML api to read the excel. Here is how you do it. Statistically, this performs better than OpenXML.

public DataTable ReadDataFromExcelUsingClosedXML()
        {
string filePath ="@c:/temp/example.xlsx";

            var LobjWorkbook = new XLWorkbook(filePath);
            var LobjWorksheet = LobjWorkbook.Worksheets.First();

            var LobjFullRange = LobjWorksheet.RangeUsed();
            var LobjUsedRange = LobjWorksheet.Range(MobjImportMapper.HeaderRowIndex + 1, 1, LobjFullRange.RangeAddress.LastAddress.RowNumber,
                                                    LobjFullRange.RangeAddress.LastAddress.ColumnNumber);

            var LiNumberOfcolumnsInTheExcel = LobjUsedRange.ColumnCount();

            //  for progress bar
            int LiAggregateRowCounter = MobjImportMapper.HeaderRowIndex;
            int LiTotalNumberOfRows = LobjWorksheet.RowCount() - LiAggregateRowCounter;
            int LiPercentage = 0;

            foreach (var LobjRow in LobjUsedRange.RowsUsed())
            {
                int LiTemp = 0;
                object[] LobjrowData = new object[LiNumberOfcolumnsInTheExcel + 1];
                LobjrowData[LiTemp] = LobjRow.RangeAddress.FirstAddress.RowNumber;
                LiTemp++;

                LobjRow.Cells().ForEach(PobjCell => LobjrowData[LiTemp++] = PobjCell.Value);
                LdtExcelData.Rows.Add(LobjrowData);

                //  for progress bar
                LiPercentage = ((100 * LiAggregateRowCounter / LiTotalNumberOfRows) / 4) * 3;
                if (LiPercentage > 5)
                    PobjBackgoundWorker.ReportProgress(LiPercentage);
                LiAggregateRowCounter++;
                // =====================
            }

            return LdtExcelData;
        }

Continue reading »

In JavaScript, curly brace placement matters: An example by David

I used to follow Kernighan and Ritchie style of code formatting, but lost that habit. Not sure how may hours spent on fixing JS issues due to Allman format. Every time I feel bad whilst Visual Studio gives K&R style. Just realized the impotence of K&R style for JS. My Big thanks to David for pointing the curly brace placement issue with JS and posting such a nice article. In JavaScript, curly brace placement matters: An example Continue reading »

ASP.NET Event Handling using ASPX and Code behind

This video shows how to register UI events in ASP.NET. Registration can be done in two ways first one is using APSX file and second one is using Code behind. This Video explains both the approaches, also explains you how easily events can be created in Visual Studio.

Continue reading »

State Design Pattern .NET Code Sample

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

class Program
{
    static void Main(string[] args)
    {
        Person p1 = new Person("P1");
        Person p2 = new Person("P2");
        p1.EatFood();
        p2.EatFood();
        p1.Vomit();
        p2.Vomit();
    }
}

interface StomachState
{
    void Eat(Person p);
    void Vomit(Person p);
}

class StomachFull : StomachState
{
    public void Eat(Person p)
    {
        Console.WriteLine("Can't eat more.");
    }

    public void Vomit(Person p)
    {
        Console.WriteLine("I've just Vomited.");
        p.StomachState = new StomachEmpty();
    }
}

class StomachEmpty : StomachState
{
    public void Eat(Person p)
    {
        Console.WriteLine("I've just had food.");
        p.StomachState = new StomachFull();
    }

    public void Vomit(Person p)
    {
        Console.WriteLine("Nothing to Vomit.");
    }
}

class Person
{
    private StomachState stomachState;
    private String personName;
    public Person(String personName)
    {
        this.personName = personName;
        StomachState = new StomachEmpty();
    }

    public StomachState StomachState 
    { 
        get 
        {
            return stomachState;
        } 
        set 
        {
            stomachState = value;
            Console.WriteLine(personName + " Stomach State Changed to " + StomachState.GetType().Name);
            Console.WriteLine("***********************************************\n");
        } 
    }

    public Person(StomachState StomachState)
    {
        this.StomachState = StomachState;
    }

    public void EatFood()
    {
        StomachState.Eat(this);
    }

    public void Vomit()
    {
        StomachState.Vomit(this);
    }
}

Continue reading »

Export from asp.net page to Excel, word & pdf

 

Here is an excellent articles that talks about exporting data from asp.net page to Excel, word and pdf

http://www.codeproject.com/KB/TipsnTricks/AllInOneExportData.aspx

Continue reading »

DataTable to Generic List Conversion

using System;
using System.Collections.Generic;
using System.Linq;
using System.Data;

namespace ConsoleApplication1
{
    class Program
    {

        static void Main(string[] args)
        {
            DataTable table = new DataTable
            {
                Columns = {
                            {"Number", typeof(int)},
                            {"Name", typeof(string)}
                         }
            };

            //Just adding few test rows to datatable.
            for (int i = 1; i <= 5; i++)
            {
                table.Rows.Add(i, "Name" + i);
            }

            var returnList = from row in table.AsEnumerable()
                              select new MyObject
                              {
                                  Number = row.Field<int>("Number"),
                                  Name = row.Field<String>("Name")
                              };

            //Displaying converted collection
            foreach (MyObject item in returnList)
            {
                Console.WriteLine("{0}\t{1}", item.Number, item.Name);
            }
        }
    }

    class MyObject
    {
        public int Number { get; set; }
        public String Name { get; set; }
    }
}

Continue reading »

Microsoft offers Professional Tools for small companies with support at no cost

 

  

Continue reading »

How to Hibernate from .NET Apps and How to enable Hibernate in Windows XP

The usage of Computer desktop or laptop is increased all around the world phenomenally. This link gives you the picture on how power consumption is for various devices we use daily. to reduce the power consumption Hibernate is one of the best way provided by default in Windows Vista or Windows 7. Hibernate feature enables you to close the machine without closing your applications, that means the applications will be restored as they were once we restart the machine. Hibernate feature is not enabled in Windows XP by default. I’ve seen many people that they run (do not switch off) the machines months and months as they do not want to close the windows or applications running in Windows XP. below are the steps to enable Hibernate in Windows XP.

1. Right click on Desktop.
2. Click on properties.
3. Go to screen save tab.
4. Click on power button
5. Select Hibernate tab
6. Check the checkbox “Enabled Hibernate”
7. Apply the settings.

Now when you try to shutdown, “Shut down windows” dialog shows “Hibernate” options. Now you can safely close the machine without closing your applications or windows as they will be restored once you on the machine.

</SPAN?

Some time you might want to provide this future programmatically for the applications you develop for windows. Generally you might want to provide this option in windows applications where process needs huge time. Download managers are the one of the best example. below is the code to do a Hibernate from the .NET code.

using System.Windows.Forms;

namespace CodeKicks.WinApp.Machine
{
    public static class MyMachineHelper
    {
        public static void DoHibernate()
        {
            //Application.SetSuspendState(PowerState.Suspend, true, false);
            Application.SetSuspendState(PowerState.Hibernate, true, false);
        }
    }
}

Continue reading »

Configure SQL Express 2005 for remote access

Please follow the below steps as shown in pictures to configure SQL Server Express 2005 for remote access.

Fig1: Open SQL Serve Configuration Manager

Fig2: Navigate to SQL Serve 2005 N/W configuration and click on Protocols node

Fig3: Enable TCP/IP Protocol

Fig4: Enable Named Pipes Protocol

Fig5: After enabling TCP/IP and Named Pipes protocols

Fig6: Finally click on TCP/IP to configure the port number to listen N/W requests to SQL Express 2005.

Continue reading »

Visual Studio 2010 Installation Screenshots, links to installation Guides, Forum

Today Installed Visual Studio 2010 in my new Sony Vaio laptop. I’ve habit of taking screen shots while setups are running. It helps me if I want to find the items what I installed earlier for that software. but taking screen shots is not required for the software's like Visual Studio as it provides add/remove items at anytime. Below are the screen shorts for the members are you new to Visual Studio installation, it’s pretty much easy and self understandable if you follow the instructions mentioned in installation wizard. I thought it does several system restarts as earlier versions, but VS2010 did not restart the machine. Just it said successfully installed. You might want to refer this link for further assistance. You can also ask your queries in this forum. You can also find installation guide. Happy coding with Visual Studio 2010 :-)

You might also want to other articles
27 New Features of .NET Framework 4.0
New features of IIS 7.0
22 New Features of Visual Studio 2008 for .NET Professionals

 

 

 

 

 

 

Continue reading »

An observation on .NET loops – foreach, for, while, do-while

It’s very common that .NET programmers use “foreach” loop for iterating through collections. Following is my observation whilst I was testing simple scenario on loops. “for” loop is 30% faster than “foreach” and “while” loop is 50% faster than “foreach”. “do-while” is bit faster than “while”. Someone may feel that how does it make difference if I’m iterating only 1000 times in a loop.

This test case is only for simple iteration. According to the "Data structure" concepts, best and worst cases are completely based on the data we provide to the algorithm. so we can not conclude that a "foreach" algorithm is not good. All I want to tell that we need to be little cautious even choosing the loops.

Example:- You might want to chose quick sort when you want to sort more numbers. At the same time bubble sort may be effective than quick sort when you want to sort less numbers.

Take a simple scenario, a request of a simple web application fetches the data of 10000 (10K) rows and iterating them for some business logic. Think, this application is being accessed by 1000 (1K) people simultaneously. In this simple scenario you are ending up with 10000000 (10Million or 1 Crore) iterations.

below is the test scenario with simple console application to test 100 Million records.

using System;
using System.Collections.Generic;
using System.Diagnostics;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            var sw = new Stopwatch();
            var numbers = GetSomeNumbers();

            sw.Start();
            foreach (var item in numbers)
            {

            }
            sw.Stop();

            Console.WriteLine(
            String.Format("\"foreach\" took {0} milliseconds",
            sw.ElapsedMilliseconds));

            sw.Reset();
            sw.Start();
            for (int i = 0; i < numbers.Count; i++)
            {

            }
            sw.Stop();

            Console.WriteLine(
            String.Format("\"for\" loop took {0} milliseconds",
            sw.ElapsedMilliseconds));

            sw.Reset();
            sw.Start();
            var it = 0;
            while (it++ < numbers.Count)
            {

            }
            sw.Stop();

            Console.WriteLine(
            String.Format("\"while\" loop took {0} milliseconds",
            sw.ElapsedMilliseconds));

            sw.Reset();
            sw.Start();
            var it2 = 0;
            do
            {

            } while (it2++ < numbers.Count);

            sw.Stop();

            Console.WriteLine(
            String.Format("\"do-while\" loop took {0} milliseconds",
            sw.ElapsedMilliseconds));
        }

        #region Get me 10Crore (100 Million) numbers
        private static List<int> GetSomeNumbers()
        {
            var lstNumbers = new List<int>();
            var count = 100000000;
            for (var i = 1; i <= count; i++)
            {
                lstNumbers.Add(i);
            }
            return lstNumbers;
        }
        #endregion Get me some numbers
    }
}

In above example, I was just iterating through 100 Million numbers. You can see the time to execute various  loops provided in .NET

Output
"foreach" took 1108 milliseconds

"for" loop took 727 milliseconds

"while" loop took 596 milliseconds

"do-while" loop took 594 milliseconds

  Press any key to continue . . .



So I feel we need to be careful while choosing the looping strategy. Please comment your thoughts.
Continue reading »

Control HelpButton, HelpRequested, HelpButtonClicked - Instant help for windows Dialog Form components

Instant help for windows dialog components is a great feature and very much known since windows 98. but I saw many many people are not aware it and query on Google to get help, but “help button” for dialogs helps you or your customers to get the help instantly. Every dialog window has help icon if that dialog was coded to enable it. it really helps to know the functionality of the components quickly. For example I was trying to pint a document from acrobat reader and opened printer properties to print the content front and back of the paper.

If you observe there is a help button before close button. To get help on options of “Print on Both Sides” you would need to click on help button first and then click on the area on which you want to see the help. above picture shows help text for the options of “Print on Both Sides”. If you would like to get the help using keyboard you can use F1 key.

Help button can be displayed only if minimize button and maximize button both are not shown unless you want go with custom buttons. below is the way if you want to get Help button for windows forms.

 
In this sample demo I want to have a checkbox and need to show help when I click on F1 on check box. So I created a form which country check box and help label as show in adjacent picture.

Below is the code for your code bind file.

using System;
using System.Windows.Forms;

namespace WindowsFormsApplication1
{
    public partial classForm1: Form
   {
        publicForm1()
        {
            InitializeComponent();
        }

        private void Form1_Load(objectsender, EventArgs e)
        {
            this.Text = "Help Button Demo Form";
            lblHelp.Text = "Press F1 on any component to get Instant Help";

            this.HelpButton = true;
            this.MaximizeBox = false;
            this.MinimizeBox = false;

            chkCountry.Tag = "Check or Uncheck Coutry Check Box";
            chkCountry.HelpRequested += newHelpEventHandler(chkCountry_HelpRequested);
            chkCountry.MouseLeave += newEventHandler(chkCountry_MouseLeave);
        }

        void chkCountry_HelpRequested(objectsender, HelpEventArgs hlpevent)
        {
            ControlrequestingControl = (Control)sender;
            lblHelp.Text = (string)requestingControl.Tag;
            hlpevent.Handled = true;
        }

        void chkCountry_MouseLeave(objectsender, EventArgs e)
        {
            lblHelp.Text = "Press F1 on any component to get Instant Help";
        }
    }
}

In above code  “HelpRequested” is an event will be fired when you click on F1 on Country checkbox. I stored the help information in the checkbox property called “Tag”. You might also maintain a property file to keep help text for each component differently. If you click on F1 when focus is on main form instead on individual component then generally separate help window opens. This can be done using the event “Form.HelpRequested” to open help windows as in below code.

this.HelpRequested += newHelpEventHandler(Form1_HelpRequested);

voidForm1_HelpRequested(objectsender, HelpEventArgs hlpevent)
{
    frmHelp.Show();
}

Continue reading »