Archive for the ‘.NET’ Category

XML Data Schema(XSD) To Excel Format

March 12, 2013

This article will help you to parse the XSD file to MS-excel format in easy way (in 6 steps)

1 .Create Web application project from VS.IDE

CreateWebproject

2  Paste the following code to Default.aspx.cs  file

 public partial class _Default : System.Web.UI.Page
    {
        System.Text.StringBuilder sb = new System.Text.StringBuilder();
        protected void Page_Load(object sender, EventArgs e)
        {
            sb.Append("</pre>
<table border="1">"); sb.Append("
<tbody>
<tr>
<td colspan="6" align="middle">"); sb.Append("
<h3>Schema Design for: - 10.6</h3>
"); sb.Append("</td>
</tr>
"); sb.Append("
<tr>
<td colspan="6" align="middle"> </td>
</tr>
"); TraverseSOM(Server.MapPath("CustomersOrders.xsd")); sb.Append("</tbody>
</table>
<pre>
");
            Response.Clear();
            Response.ClearContent();
            Response.ClearHeaders();
            Response.ContentType = "application/vnd.ms-excel";
            Response.AddHeader("Content-Disposition", "attachment; filename=Reports.xls");
            Response.Write(sb); Response.End();
        }
        ///
    /// Reads the and compile schema.    ///

        /// ///Name of the file.
        ///
        private static XmlSchema ReadAndCompileSchema(string fileName)
        {
            XmlTextReader tr = new XmlTextReader(fileName, new NameTable());         // The Read method will throw errors encountered
            // on parsing the schema
            XmlSchema schema = XmlSchema.Read(tr, new ValidationEventHandler(ValidationCallbackOne));
            tr.Close(); XmlSchemaSet xset = new XmlSchemaSet();
            xset.Add(schema);
            xset.ValidationEventHandler += new ValidationEventHandler(ValidationCallbackOne);         // The Compile method will throw errors         // encountered on compiling the schema
            xset.Compile();
            return schema;
        }
        public static void ValidationCallbackOne(object sender, ValidationEventArgs args)
        {
            Console.WriteLine("Exception Severity: " + args.Severity);
            Console.WriteLine(args.Message);
        }
        private void TraverseSOM(string xsfFilename)
        {
            XmlSchema custSchema = ReadAndCompileSchema(xsfFilename);
            foreach (XmlSchemaElement elem in custSchema.Elements.Values)
            {           // XmlSchemaComplexType ct = elem.ElementSchemaType as XmlSchemaComplexType;
                ProcessElement(elem);               // ProcessElement(elem);
                sb.Append("
&nbsp

");
            }
        }
        private void ProcessElement(XmlSchemaElement elem)
        {
            if (elem.ElementSchemaType is XmlSchemaComplexType)
            {
                sb.Append("
&nbsp");
                sb.Append("<b>Root Element Name : " + elem.Name + "</b>
");                sb.Append("");
                sb.Append("<b> Element Type  </b>

");
                sb.Append("
&nbspMaxOccurs: " + elem.MaxOccurs + " MinOccurs: " + elem.MinOccurs + "" + elem.SchemaTypeName.Name + "

"); sb.Append("
<b> SL No</b><b> Element Name</b><b> Data Type</b><b> Mandatory?</b><b> Constraint </b><b> Explanation </b>

");
                XmlSchemaComplexType ct = elem.ElementSchemaType as XmlSchemaComplexType;
                int i = 1;
                if (ct.Attributes.Count != 0)
                {
                    foreach (XmlSchemaAttribute obj in ct.Attributes)
                    {
                       string strAnno = "";
                        if (obj.Annotation != null)
                        {
                            var ann = obj.Annotation.Items;
                            if (ann is XmlSchemaObjectCollection)
                            {
                                foreach (XmlSchemaDocumentation document in ann)
                                {
                                    strAnno = strAnno + document.Markup[0].Value;
                                }
                            }
                        }
                        sb.Append("
" + i + "" + obj.Name + "" + (obj.AttributeSchemaType as XmlSchemaType).Name + "" + obj.Use + "" + strAnno + " 

");
                        i++;
                    }
                }
                var sequence = (elem.ElementSchemaType as XmlSchemaComplexType).ContentTypeParticle as XmlSchemaSequence;
                if (sequence != null)
                {
                    foreach (XmlSchemaParticle childParticle in sequence.Items)
                    {
                        XmlSchemaElement elementSequ = (childParticle as XmlSchemaElement);
                        decimal minOccurs = childParticle.MinOccurs;
                        string strMan;
                        if (minOccurs == 0)
                        {
                            strMan = "No";
                        }
                        else
                        {
                            strMan = "Yes";
                        }
                        if (elementSequ != null)
                            sb.Append("
" + i + "" + elementSequ.QualifiedName.Name + "" + elementSequ.SchemaTypeName.Name + "" + strMan + "  

");
                        i++;
                    }
                }
                var Choice = (elem.ElementSchemaType as XmlSchemaComplexType).ContentTypeParticle as XmlSchemaChoice;
                if (Choice != null)
                {
                    foreach (XmlSchemaParticle childParticle in Choice.Items)
                    {
                        XmlSchemaElement elementChoice = (childParticle as XmlSchemaElement);

                        if (elementChoice != null)
                            sb.Append("
" + i + "" + elementChoice.QualifiedName.Name + "" + elementChoice.SchemaTypeName.Name + "" + "Mandatory for the instance" + "  

");
                        i++;
                    }
                }

                ProcessSchemaObject(ct.ContentTypeParticle);
            }
        }
        private void ProcessSequence(XmlSchemaSequence sequence)
        {
            ProcessItemCollection(sequence.Items);
        }
        private void ProcessChoice(XmlSchemaChoice choice)
        {
            ProcessItemCollection(choice.Items);
        }
        private void ProcessItemCollection(XmlSchemaObjectCollection objs)
        {
            foreach (XmlSchemaObject obj in objs)
            {
                ProcessSchemaObject(obj);
            }
        }
        private void ProcessSchemaObject(XmlSchemaObject obj)
        {
            if (obj is XmlSchemaElement)
            {
                ProcessElement(obj as XmlSchemaElement);
            }
            if (obj is XmlSchemaChoice)
            {
                ProcessChoice(obj as XmlSchemaChoice);
            }
            if (obj is XmlSchemaSequence)
            {
                ProcessSequence(obj as XmlSchemaSequence);
            }
        }
    }

3 Add XSD file to the solution folder (Same folder)

4 Change the file name to code file to your XSD file “TraverseSOM(Server.MapPath(“Your.xsd”));”

 5. Press F5 to run the application

 6 Save or View your Excel file with formatted XSD details ,  Please see the below sample out put .

excelOutput

I think this post is helpful for large schema analysis  and development  activities 

Advertisements

Excel Repot From Data Table

February 23, 2010
Code for coverting Data table Execl Report

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Globalization;
using System.Reflection;

namespace Common
{
 /// <summary>
 /// Download Excel
 /// </summary>
 public class DownloadExcel : System.Web.UI.Page
 {

 #region Methods (4)

 // Public Methods (3)

 /// <summary>
 /// Generics the list to data table.
 /// </summary>
 /// <param name="list">The list.</param>
 /// <returns></returns>
 public static DataTable GenericListToDataTable(object list)
 {
 DataTable dt = null;
 Type listType = list.GetType();
 if (listType.IsGenericType)
 {
 //determine the underlying type the List<> contains
 Type elementType = listType.GetGenericArguments()[0];

 //create empty table -- give it a name in case
 //it needs to be serialized
 dt = new DataTable(elementType.Name + "List");

 //define the table -- add a column for each public
 //property or field
 MemberInfo[] miArray = elementType.GetMembers(
 BindingFlags.Public | BindingFlags.Instance);
 foreach (MemberInfo mi in miArray)
 {
 if (mi.MemberType == MemberTypes.Property)
 {
 PropertyInfo pi = mi as PropertyInfo;
 dt.Columns.Add(pi.Name);
 }
 else if (mi.MemberType == MemberTypes.Field)
 {
 FieldInfo fi = mi as FieldInfo;
 dt.Columns.Add(fi.Name, fi.FieldType);
 }
 }

 //populate the table
 IList il = list as IList;
 foreach (object record in il)
 {
 int i = 0;
 object[] fieldValues = new object[dt.Columns.Count];
 foreach (DataColumn c in dt.Columns)
 {
 MemberInfo mi = elementType.GetMember(c.ColumnName)[0];
 if (mi.MemberType == MemberTypes.Property)
 {
 PropertyInfo pi = mi as PropertyInfo;
 fieldValues[i] = pi.GetValue(record, null);
 }
 else if (mi.MemberType == MemberTypes.Field)
 {
 FieldInfo fi = mi as FieldInfo;
 fieldValues[i] = fi.GetValue(record);
 }
 i++;
 }
 dt.Rows.Add(fieldValues);
 }
 }
 return dt;
 }

 /// <summary>
 /// Gets the date from string.
 /// </summary>
 /// <param name="strDate">The STR date.</param>
 /// <returns></returns>
 public static DateTime GetDateFromString(string strDate)
 {
 DateTime dt;
 try
 {
 Int32 intOADate = Int32.Parse(strDate);
 dt = DateTime.FromOADate(intOADate);
 }
 catch
 {
 try //try with dd/MM/yyyy
 {
 IFormatProvider culture = new CultureInfo("fr-FR", true);
 dt = DateTime.Parse(strDate, culture);
 }
 catch //try with MM/dd/yyyy
 {
 IFormatProvider culture = new CultureInfo("en-US", false);
 dt = DateTime.Parse(strDate, culture);
 }
 }
 return dt;
 }

 /// <summary>
 /// Function to export grid data to excel
 /// </summary>
 /// <typeparam name="T"></typeparam>
 /// <param name="entityList">The entity list.</param>
 /// <param name="hashTable">The hash table.</param>
 public void GetExcel<T>(List<T> entityList, Dictionary<int, string> hashTable) where T : class
 {

 ExportExcel(entityList, hashTable);

 }

 // Protected Methods (1)

 /// <summary>
 /// Exports the excel.
 /// </summary>
 /// <param name="entityList">The entity list.</param>
 /// <param name="hashTable">The hash table.</param>
 protected void ExportExcel(object entityList, Dictionary<int, string> hashTable)
 {

 DataTable dt = GenericListToDataTable(entityList);
 Response.Clear();
 Response.ClearContent();
 Response.ClearHeaders();
 Response.ContentType = "application/vnd.ms-excel";
 Response.AddHeader("Content-Disposition", "attachment; filename=Reports.xls");
 //Response.Clear();
 //Response.ContentType = "application/vnd.ms-excel";
 string sep = "";
 ICollection keyCollection = hashTable.Keys;
 int[] keys = new int[keyCollection.Count];
 keyCollection.CopyTo(keys, 0);
 for (int i = 0; i < dt.Columns.Count; i++)
 {

 Response.Write(sep + dt.Columns[i].ColumnName);
 sep = "\t";
 //excelTableColumn = excelTableColumn + "[" + hashTable[keys[i]] + "] text ,";
 }
 //foreach (DataColumn dc in dt.Columns)
 //{
 //    Response.Write(sep + dc.ColumnName);
 //    sep = "\t";
 //}
 Response.Write("\n");

 // int i;
 foreach (DataRow dr in dt.Rows)
 {
 sep = "";
 for (int i = 0; i < dt.Columns.Count; i++)
 {
 Response.Write(sep + dr[i].ToString());
 sep = "\t";
 }
 Response.Write("\n");
 }
 Response.End();

 }

 #endregion Methods

 }
}

The Above code you can be placed in common folder or App_code(freamwork 2.0) when you are creating report page
inherit this class to Report page like following code

/// <summary>
 ///
 /// </summary>
 public partial class DelayedVechiclesBodyTypeReport :  DownloadExcel
 {
 #region Private variables
//your code
 #endregion
 }

in Generate Report button Click write the following code :

#region Web Generated Events
 /// <summary>
 ///
 /// </summary>
 /// <param name="sender"></param>
 /// <param name="e"></param>
 protected void btnGenerateReport_Click(object sender, EventArgs e)
 {
 List<ReportDelayedVehicleEntity> delayedVehicleDetailsList;
 delayedVehicleDetailsList = reportService.GetDelayVechiclesBodyTypeReport(Convert.ToInt32( rdoBodyType.SelectedValue));
 Dictionary<int, string> excelHashTable = new Dictionary<int, string>();
 excelHashTable.Add(0, "BodyNo");
 excelHashTable.Add(1, "ModelCode");
 excelHashTable.Add(2, "SuffixCode");
 excelHashTable.Add(3, "ColorCode");
 excelHashTable.Add(4, "JigInDate");
 excelHashTable.Add(5, "FrameNo");
 excelHashTable.Add(6, "ASSPlanned_LO");
 excelHashTable.Add(7, "EngineNo");
 excelHashTable.Add(8, "CurrentStageID");
 excelHashTable.Add(9, "CarFamily");
 excelHashTable.Add(10, "LotCode");
 excelHashTable.Add(11, "SpecSheetNumber");
 excelHashTable.Add(12, "EngineModelcode");
 excelHashTable.Add(13, "StageName");
 excelHashTable.Add(14, "StandardDefectName");
 excelHashTable.Add(15, "Remark");
 excelHashTable.Add(16, "DelayedDays");
 excelHashTable.Add(17, "RootResponsibilityName");
 excelHashTable.Add(18, "RepairResponsibilityName");
 excelHashTable.Add(19, "TargetDate");

 GetExcel(delayedVehicleDetailsList, excelHashTable);
 }

 #endregion

Getting Started Tutorial for WCF

October 24, 2009

Hi all

I found following links are useful for learning  WCF

  1. http://msdn.microsoft.com/en-us/library/ms734712.aspx
  2. http://www.wcf-training-guide.com/
  3. http://bloggingabout.net/blogs/dennis/archive/2007/04/20/wcf-simple-example.aspx