Excel Add-in for Exporting data to XML

• Download ExcelExportXML-Setup – 221.11 KB
• Download ExcelExportXML-Source – 328.11 KB

Introduction

ExcelExportXML is a Microsoft Excel 2010 Add-in that generates XML data from excel sheet. It is developed in Visual Studio 2010 using C#.

It is a very simple add-in. To understand how to works, consider an excel sheet having following data.

Country	Capital	Continent
France	Paris	Europe
Germany	Berlin	Europe
India	New Delhi	Asia
Indonesia	Jakarta	Asia

Using the add-in, above can be exported to following Xml.

<sheet1>
    <row>
        <country>France</country>
        <capital>Paris</capital>
        <continent>Europe</continent>
    </row>
    <row>
        <country>Germany</country>
        <capital>Berlin</capital>
        <continent>Europe</continent>
    </row>
    <row>
        <country>India</country>
        <capital>New Delhi</capital>
        <continent>Asia</continent>
    </row>
    <row>
        <country>Indonesia</country>
        <capital>Jakarta</capital>
        <continent>Asia</continent>
    </row>
</sheet1>

Once the Add-in is installed, you will have a ‘Generate XML’ button on Add-ins tab of Excel Ribbon as shown below. Clicking ‘Generate XML’ will pop up the save file dialog which will allow you to save the generated Xml file.

Use Case & Assumptions

ExcelExportXml is useful when you have tabular data in excel and the first row contains the column headings. It works based on following assumptions.

1. First row is considered as column headers and will be converted to Xml tags.
2. After encountering the first empty cell in header row, rest of the columns to the right will be ignored.
3. Supports columns up to ‘Z’ only i.e. maximum of 26 columns.
4. First row for which all values are empty will be considered the end of sheet.
5. Sheet name and column names should not have any spaces.

Why not use standard Save As Xml functionality

Excel provides more than one ways to export xml data. One of them is ‘Save As Xml Data’ available in Save As dialog. This requires Xml Mappings to be defined which I believe requires developer Add-in from Microsoft to be installed.

Another option is ‘Save As Xml Spreedsheet 2003′ which generates following Xml.

<Worksheet ss:Name="Sheet1">
  <Table ss:ExpandedColumnCount="3" ss:ExpandedRowCount="5" x:FullColumns="1"
   x:FullRows="1" ss:DefaultRowHeight="15">
   <Column ss:Width="51"/>
   <Column ss:Width="54"/>
   <Column ss:Width="51.75"/>
   <Row ss:StyleID="s64">
    <Cell><Data ss:Type="String">Country</Data></Cell>
    <Cell><Data ss:Type="String">Capital</Data></Cell>
    <Cell><Data ss:Type="String">Continent</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="String">France</Data></Cell>
    <Cell><Data ss:Type="String">Paris</Data></Cell>
    <Cell><Data ss:Type="String">Europe</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="String">Germany</Data></Cell>
    <Cell><Data ss:Type="String">Berlin</Data></Cell>
    <Cell><Data ss:Type="String">Europe</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="String">India</Data></Cell>
    <Cell><Data ss:Type="String">New Delhi</Data></Cell>
    <Cell><Data ss:Type="String">Asia</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="String">Indonesia</Data></Cell>
    <Cell><Data ss:Type="String">Jakarta</Data></Cell>
    <Cell><Data ss:Type="String">Asia</Data></Cell>
   </Row>
  </Table>
  </Worksheet>

As you can see, the above might not be what you want. It is more a representation of excel sheet than semantics of your data. ExcelExportXML is quite limited but effective in a common scenario where you have tabular data and the first row contains column headers.

Links

• ExcelExportXML – My Article at CodeProject
• Writing Add-in for Microsoft Excel 2010 using Visual Studio 2010

Writing Add-in for Microsoft Excel 2010 using Visual Studio 2010

Using Visual Studio 2010 Professional, creating Excel Add-in is pretty simple. Following are the steps I followed for writing an Add-In for exporting Xml:

Step # 1

Create new project of type ‘Excel 2010 Add-in’. The project will have a class file called ThisAddin.cs.

Step # 2

For creating a button on the Excel ribbon, right click on the project and select Add – > New Item.

Step # 3

Select ‘Ribbon (XML)’ from the list of new items and name the ribbon class, say Ribbon1. This will add two files to the project which are Ribbon1.cs and Ribbon1.xml.

Step # 4

Add the following code in ThisAddin class

protected override Microsoft.Office.Core.IRibbonExtensibility CreateRibbonExtensibilityObject()
{
    return new Ribbon1();
}

Step # 5

In Ribbon1.xml define the properties of the button that will be displayed on the Excel ribbon. The onAction attribute specifies the method which will be called from Ribbon1.cs on clicking the button (onAction="OnTextButton").

 <?xml version="1.0" encoding="UTF-8"?>
	<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui" onLoad="Ribbon_Load">
	  <ribbon>
	    <tabs>
	      <tab idMso="TabAddIns">
	        <group id="MyGroup"
	               label="Export XML">
	          <button id="textButton" label="Generate XML"
	             screentip="Export to XML" onAction="OnTextButton"
	             supertip="Export excel sheet to XML file."/>
	        </group>
	      </tab>
	    </tabs>
	  </ribbon>
	</customUI>

Step # 6

Add following method declaration to Ribbon1.cs and implement your functionality here.

public void OnTextButton(Office.IRibbonControl control)
{
    //TODO:Add your implementation here
} 

Links:

• Writing a simple Excel Add-In for exporting data to Xml
• Working with Excel Workbooks (MSDN)
• How to: Store and Retrieve Date Values in Excel Ranges (MSDN)
• Walkthrough: Creating a Custom Tab by Using Ribbon XML (MSDN)

ASP.Net Web Forms vs ASP.NET MVC

When microsoft released ASP.Net, it was thought that it would relieve the need to master HTML and related technologies for most use cases. It will bring the web development closer to windows forms development model with both having similar controls and tools. In ASP.Net forms, we would be able to drag a data grid, drop it on to a page and bind it to a data source, the same way we used to do for windows apps.

Initially I was excited about the idea. Coming from a windows development background, it made things simpler for me. As I did more work with ASP.Net, something didn’t look right. It was very easy to do things you do most of the time in the most common way. But if you want to do something little bit different, then the complexity of the platform made it ridiculously difficult. ASP.Net forms had some significant improvements over ASP but, in hindsight, I think the power and simplicity of HTML, CSS and JavaScript was underestimated.

With ASP.Net MVC, the focus is back on the key web technologies and it feels so much better.

For a detailed comparison between the two technologies, see:

http://msdn.microsoft.com/en-us/magazine/dd942833.aspx
http://weblogs.asp.net/shijuvarghese/archive/2008/07/09/asp-net-mvc-vs-asp-net-web-form.aspx

For learning ASP.Net MVC, I would recommend the Pluralsight video tutorial at ASP.Net MVC website.

Reached “Design – All Star” in design .toolbox



.toolbox is a really fun environment to learn basic design concepts as well as the fundamentals of creating Silverlight applications using Expression Studio. Today, I finished both the tracks (i.e. principles and scenarios) and achieved all 15 badges. The high quality tutorials and evaluations, and a very nice theme make it really enjoyable. Check it out at http://www.microsoft.com/design/toolbox/

LightSwitch vs Maximo Application Development Tools

IBM Maximo is an enterprise asset management system but it also has a very advanced application development tool set. This toolset is used to customize existing business objects and screens; and also to create custom application to cater for additional user requirements.

Maximo application development tools and Microsoft LightSwitch take a somewhat similar approach to allow rapid development of business applications which are also easier to maintain and enhance. They contain visual application designers which can be used to quickly generate the screens with full functionality based on the data model. But at the same time, the generated code for screens and business objects is highly extensible.

LightSwitch is a nascent product in its first beta version right now while Maximo is pretty mature. In following passages I have listed down some important features I wish to be there in future LightSwitch releases as I compare it with IBM Maximo.

LightSwitch Feature Wishlist:

 

Workflow:

Workflow engine is an important component of any business application. It enables us to visually (and declaratively) design the process flow for business transactions. A simple example of workflow could be the approval process for a purchase order where we define who will review and approve the Purchase Order based on certain conditions like total purchase order value.

Having the business process defined in declarative form with the built in capability of creating assignments and tracking document flow gives us tremendous power. It brings visibility to the processing of each transaction as it moves through the process and also provides valuable stats about the process steps which can be used to create KPIs (Key Performance Indicators).

Another key benefit of having business process in declarative form is the agility factor. With the workflow engines, IT system systems can keep pace with changes in the business process because even business users can modify the workflow using visually designers.

LightSwitch looks like a promising product but to be my platform of choice it must have a built-in workflow engine.

Security Profile:

Currently LightSwitch supports ASP.Net Authentication and Authorization provider. For each privilege, we have to create an entry in configuration file and then write some code to disable the unauthorized operations for the logged-in user.

In Maximo, all default and custom developed operations of a business object are available in security profiles screen to be assigned to users. No coding or changes to configuration file are required, this is supported out-of-the-box. Moreover we can create data restrictions on a business object to expose only a subset of data to a user profile.

Hopefully future releases of LightSwitch will improve on what he have right now in terms of security profile and authorization.

Notification & Escalation:

It is a very common task to generate emails from a business application to notify user about their tasks or simply inform them about some events. Like IBM Maximo, I wish to see the capacity in LightSwitch to design email notifications for desired events.

Report & KPI Designer:

Maximo has a powerful reporting engine along with KPI and Dashboard designers. These designers also available to application user so that they can cater for their reporting and BI (business intelligence) needs without writing any code.

There are some reporting and BI add-ins developed for LightSwitch by third party vendors. Hopefully Microsoft will come up with its own built-in reporting and BI designer also.

Object Relationship:

LightSwitch supports creating relationship between two tables. But there is some room for improvement here. For example:

• relationship between two tables can be too complex to be defined just by equality of two fields. Application Designer should allow for creation of more complex relationships which have multiple conditions.
• when designing screen for a table, screen designer should also allow to pick fields from tables which are joined through the relationship.

Attachments:

Another important feature in business applications is to have the ability to attach documents to the records. I wish in future LightSwitch will provide this functionality out-of-the-box.

Advantages of LightSwitch over IBM Maximo:

Even though LightSwitch is in very early stages of development, still in some areas it scores better than IBM Maximo.

• LightSwitch supports multiple data sources. These data sources can be different RDMBS like Sql Server, Oracle or can even be a sharepoint list. Comparing it with Maximo Database Configuration, there we can work with only one data source.
• In case we write custom code to implement some feature, the develop, build and deploy cycle is relatively time-consuming and tedious in Maximo. While LightSwitch provides a much faster and smoother development experience with a better integrated technology stack.

Microsoft Visual Studio LightSwitch

Visual Studio LightSwitch is a new tool from Microsoft for developing business applications. Currently available development technologies from Microsoft (like VS C#) are general purpose and can be used to develop a wide variety of applications. LightSwitch, on the other hand, is focused on simplifying development of business applications by handling the basic plumbing itself.

I downloaded the currently available Beta1 version of LightSwitch and started playing with it. It took about 5 minutes to get following application ready.


Employee Information Sample App

The above sample application supports addition, updation and deletion of employee records along with employee qualifications and skills. It is an impressive output considering the effort that was put in.

I performed the following steps to develop the sample app.

1. Click on ‘create table’ in App Designer, name the table as Employee and define its attributes.
2. Do the same as step 1, for Qualification and Skill.
3. Create the relationship two relationships using designer i.e. employee-qualification and employee-skills.
4. Click on  ’new screen’ in App Designer and specify the table and desired layout.
5. Hurrah! Application is ready.

 

LightSwitch Application Designer:

Following is the LightSwitch application designer for tables. Additional settings are available for each attribute from the properties window.

LightSwitch Table Designer

 

Below is the screen shot of application designer for screens. I didn’t make any changes here except changing the display names for qualification and skill tabs.

LightSwitch Screen Designer

 

LightSwitch also has a query designer. Queries can also be used as a basis of screen design apart from tables. Following screen shot shows the query designer.

LightSwitch Query Designer

 

Key Features:

• We can extend the functionality of LightSwitch application by writing code in C#.Net or VB.Net. LightSwitch allows the developers to easily hook their business logic in application events. We can even write a custom control or fully custom screen.
   
  I believe this is the single most important feature of LightSwitch, this is where most of the LightSwitch precursors struggled.

Screen Shot shows partial list of methods which can be implemented for a table. Screen designer has a similar 'Write Code' functionality with method like loaded, closed etc.

 

• Another important feature is the ability of LightSwitch to connect to various data sources, its not tied up to a particular back-end technology. It support Sql Server, Azure (Cloud), SharePoint, Entity Framework as data sources.
• The application generated by LightSwitch is a Silverlight application. It runs in out-of-browser mode if application type is desktop. So multiple platforms are supported.
• Support Master details screen (e.g. invoice and invoice lines)
• Support searching, sorting and paging of records without writing any code.
• Support user authentication and role based security using ASP.Net Membership Provider.
• Handle image as one of the basic attributes of an object.

 

LightSwitch Links:

• Introducing LightSwitch at Microsoft.com
• LightSwitch Developer Centre
• Download Link (Beta1)
• Visual Studio LightSwitch Team Blog
• MSDN Library for LightSwitch
• MSDN Sample for LightSwitch

Save and Load RichTextBox content in WPF

The content of RichTextBox can be saved to a file (or any other stream) using TextRange class. It supports following four formats:

1. System.Windows.DataFormats.Text : This saves the plain text in the content of RichTextBox excluding the formatting information and the images.
2. System.Windows.DataFormats.Rtf : This saves the content in RTF format preserving formating information and images.
3. System.Windows.DataFormats.Xaml : This saves the content in Xaml format which is the same format used in WPF designer for specifying content for a RichTextBox. It contains formating tags but doesn’t support image.
   
4. System.Windows.DataFormats.XamlPackage : This is a binary file format which packages Xaml and any images into one file.

Following table is presenting key information about above formats:

Format	Binary/Text	Formating Supported	Images Supported
Text	Text	No	No
Rtf	Binary	Yes	Yes
Xaml	Text	Yes	No
XamlPackage	Binary	Yes	Yes

The code for saving & loading is pretty simple once who know the relevant class. Following is the code for saving content to file.

TextRange t = new TextRange(richTextBox1.Document.ContentStart,
                                    richTextBox1.Document.ContentEnd);
FileStream file = new FileStream("Sample File.xaml", FileMode.Create);
t.Save(file, System.Windows.DataFormats.XamlPackage);
file.Close();

 

 

Following snippet is for loading the content of RichTextBox from a file.

TextRange t = new TextRange(richTextBox1.Document.ContentStart,
                                richTextBox1.Document.ContentEnd);
FileStream file = new FileStream("Good File.xaml", FileMode.Open);
t.Load(file, System.Windows.DataFormats.XamlPackage);
file.Close();

RepMonitor: Oracle Replication Error Monitoring Tool

I had an assignment to rectify the Oracle data replication implementation between multiple Oracle databases connected over a wide area network. The replication was throwing a large number of errors and I needed an efficient tool to monitor the replication process. The standard tool provided by Oracle had certain limitations so I decided to quickly write something which could fill in the gaps. That’s how I got into writing RepMonitor which I am sharing here.

RepMonitor is capable of doing the following:
1- list replication errors
2- analyze them by comparing original and current data values at different sites
3- Re-execute replication transaction
4- Remove replication transaction from pipeline
5- Generate reports and serialize replication transaction data

Following are the tasks which can be done more efficiently in RepMonitor than Replication Management Tool that comes with Oracle Enterprise Manager:
1- Search and filter transaction errors by date and other criterias.
2- Load data on-demand only to provide better performance.
2- Generate transaction error reports.
3- Save/Serialize selected transactions and all related info in xml format.

Just to briefly elaborate on point # 2 i.e. loading data on-demand. Oracle Enterprise Manager loads all the transaction error data at start up so if you have a large number of errors, it would take ages to load. Similarly any refresh reloads everything. RepMonitor overcomes these problems by loading data on-demand.

If you want to know more about Oracle Advanced Replication, go to Oracle.com. Here is an introductory whitepaper from Oracle.

RepMonitor is developed using Visual Studio 2005 and programming language is C#.Net. The source code can be downloaded from here.

Loading and Saving a TreeView control to an XML file using XmlTextWriter and XmlTextReader

Follow the below link to see my article at CodeProject with source code.

http://www.codeproject.com/KB/cpp/TreeView_Serializer.aspx

It demonstrates how to serialize and de-serialize the contents of System.Windows.Forms.TreeView control from an XML file using forward only, non-cached XmlTextReader and XmlTextWriter.