3rd party components are a boon in today’s development environment- the can reduce considerable development costs, but they pose a considerable risk to the overall project if some important factors are not taken into account. I will like to put some of such steps which a developer/development team should ensure before making a choice of such component. These practices have become a part of our working at iSense India

- Performance of 3rd party components – It is always a good practice to try out a new 3rd party component by doing a spike/proof of concept before actually working on a concrete user story which would use these component. Usually developers do this spike in the development environment and make conclusions over the performance. The catch is that software code performance varies on different inputs and environments. To make a decision on using the component, a good idea is to test the 3rd party component with production or production alike environment. Components which would perform in the range of milliseconds in a development environment (with non-real and small data) will may become unusable/perform poorly when tested in production alike environments.
We at iSense India have a rule corresponding to this and can be helpful to others – Before making a decision to use a 3rd party component, always performance test the 3rd party components on a production alike test environment to avoid last minute surprises.
- Deployment issues with the 3rd party components – 3rd party components are built and field-tested for specific platforms and by external users. There is a good chance what works on one machine does not work on another. In case the production environment is not well defined before the start of the project, teams can make wrong choices on choosing such a component.
So in short: Always ensure that you have verified or know that the software you are developing will work on the final production environment
- Support : Most of the times there are options in choosing a control – Commercial/Open Source. Usually when you buy the commercial option choose the one which comes with the source and binaries. This would ensure that even in the future you can change or adapt the code to your needs. In case of commercial software you would also like to check what level of support the vendor can provide and if they have an active forum list. With Open source solutions this is not a problem as the source is always available. Also it’s a good idea to check the bugs/issues related to one’s required functionality
- Code Quality – At iSense we ensure that all code developed passes code metrics and analysis parameters. Usually, the codes in these 3rd party components do not score high on code quality metrics and practices and this can affect your complete application code quality.

- Additional training – Sometimes using a 3rd party component takes more time than developing it yourself, this happens when either the 3rd party control is a complex application, or one which has no documentation/information available and hence becomes complex to understand and execute within a set time. In such cases it’s better to put some effort and getting an available training from a fellow organization member/commercial vendor or partner who has experience with the tool. This training can save a considerable time while executing the project. This should also be factored in estimating the time to develop the software project.
After all nobody wants to re-invent the wheel, using 3rd party components can help you reduce the time ,effort and the cost but be careful with the fine prints when choosing one.

Due to my busy schedule, I haven’t been able to write / update any new articles on this blog. And for this month, I chose a relatively simple topic that does not require me to write any code. I chose to talk about some of the tools that are off immense use to every .NET programmer.

.NET Reflector - If you haven’t heard of Reflector you’re probably not a .NET developer. This is a tool that should have been built in to Visual Studio 2002! Look out for the Add-Ins and check out the amazing static analysis you can do with things like the Diff and Graph.

LINQPad - Interactively query your databases with LINQ with this tool from Joseph Albahari. A fantastic learning tool for those who are just getting into LINQ or for those who want a code snippet IDE to execute any C# or VB expression.

CodeRush (and DxCore) €€œ DevExpress is a company that I admire from my €œDelphi days€© and even today when I have completely switched to .NET my enthusiasm for DevExpress and their components have remained the same.

CodeRush is a tool that has matured completely from it’s first vision of a tool for Delphi. It just keeps getting better. Look out for a free Express edition and the free Extensibility Engine called DxCore that brings VS.NET plugins to the masses. Don’t miss out on free add-ins like CR_Documentor and ElectricEditing.

MemProfiler – The amount of information this tool offers is obscene. We used this at my last job to track down a number of funky memory leak

WinMerge or BeyondCompare – I’m a BeyondCompare person and have purchased it, but WinMerge is getting better and better. It’s free, it’s open source and it’ll compare files and folders and help you merge your conflicted source code files like a champ.

KDiff3 is another free option with very configurable color schemas, multi-paned view, and it’s cross platform on Linux, Windows and Mac.

WinCheat – Not a tool to cheat Windows or in games, WinCheat is like Spy++ in that it lets you dig deep into the internals of the PE format and the Win32 Windowing subsystems. I’m consistently surprised how often I need an app like this.

Kaxaml – The original and still the most awesome notepad for XAML, a must for WPF or Silverlight developers.

MSBuildShellExtension – Really ought to be built in. Right-click on any .NET project and build it directly from Explorer.

WebDeveloper for FireFox – If you’re the last developer to download FireFox, or you’re holding off, WebDeveloper is a solid reason to switch to FireFox NOW. It’s amazing and has to be used to be believed. It consolidates at least 2 dozens useful functions for those who sling ASP.NET or HTML. And if you’re a CSS person, the realtime CSS editing is pretty hot.

TestDriven.NET (integrated with NCoverExplorer) – The perfect combination of Unit Testing with Visual Studio.NET. Right click and “Run Test.” The output window says “Build” then switches to “Test.” The best part, though, is “Test With…Debugger” as a right click that automatically starts up an external process runner, loads and starts your test. Compatible with NUnit, MBUnit and Team System. TD.NET also works with Silverlight.

Query Express – Wow, a Query Analyzer look-alike that doesn’t suck, doesn’t need an install, is wicked fast, is free and is only 100k. Pinch me, I’m dreaming.

TreeTrim or Jeff Atwood’s CleanSourcesPlus – Jeff extends on Omar‘s idea of a quick Explorer utility that lets you right click on any folder with code in it and get your bin,obj,debug,release directories blown away. Jeff’s includes configuration options for deleting things like Resharper folders and Source Control bindings. TreeTrim is a similar command-line tool for cleaning up, but on steroids, including a plugin model.

Visual Studio Gallery – All the world’s extensions to Visual Studio in one place, and ranked by the public. Easy to search and sort.

Telerik Code Converter – Website that converts C# to VB and VB to C#.

CarlosAg’s CodeTranslator – One of the first, and many say, the best. An AJAXy Code Converter that’ll do to and from C# and VB.NET.

DeveloperFusion Code Converter – This online utility will also convert .NET 3.5 Syntax and LINQ between C# and VB.

Anyone who has tried their hand at OpenXml programming would concur that its a bit daunting at start with. Anyone tried their hands at making charts in OpenXml would agree even more. There are, of course, several code snippets on the internet to help you out. However, I didn’t find any that would help me with simple charts in PowerPoint. And the one I found for Excel, for instance, was too complicated to be used and involved creating a chart from the scratch.

All I wanted was a simple way to put my data into in existing bar chart in a powerpoint slide. That way I can give the user the freedom to have any formatting options that he wishes for the chart and only the data will be filled in by my program.

You’ll see the code snippet below. Basically I delete the existing “BarChartSeries” and insert my own with my data. I don’t touch any other part of the Chart leaving the formatting intact.

It may not work for all your scenarios but its a simple thing to start with. Note that I’m using Microsoft’s OpenXml SDK.

private void button1_Click(object sender, EventArgs e)
{
Dictionary<string, int> values = new Dictionary<string, int>();
values.Add("hello", 20);
values.Add("world", 30);
values.Add("test", 40);
values.Add("data", 50);

PresentationDocument presentationDoc = PresentationDocument.Open("c:\\p2.pptx", true);
SlidePart slidePart = presentationDoc.PresentationPart.SlideParts.First();
ChartPart chartPart = slidePart.ChartParts.First();

ReplaceValuesInChartInSlide(chartPart, values, "my category");
presentationDoc.PresentationPart.Presentation.Save();
presentationDoc.Close();
}

public static void ReplaceValuesInChartInSlide(ChartPart chartPart, Dictionary<string, int> data, string categoryTitle)
{
ChartSpace chartSpace = chartPart.ChartSpace;
List<int> indexOfUsedItems = new List<int>();

BarChart barChart = chartSpace.Descendants<BarChart>().FirstOrDefault();

barChart.RemoveAllChildren<BarChartSeries>();
uint i = 0;
foreach (string key in data.Keys)
{
BarChartSeries barChartSeries = barChart.AppendChild<BarChartSeries>(new BarChartSeries(new Index() { Val = new UInt32Value(i) },
new Order() { Val = new UInt32Value(i) },
new SeriesText(new NumericValue() { Text = key })));

StringLiteral strLit = barChartSeries.AppendChild<CategoryAxisData>(new CategoryAxisData()).AppendChild<StringLiteral>(new StringLiteral());
strLit.Append(new PointCount() { Val = new UInt32Value(1U) });
strLit.AppendChild<StringPoint>(new StringPoint() { Index = new UInt32Value(0U) }).Append(new NumericValue(categoryTitle));

NumberLiteral numLit = barChartSeries.AppendChild<DocumentFormat.OpenXml.Drawing.Charts.Values>(
new DocumentFormat.OpenXml.Drawing.Charts.Values()).AppendChild<NumberLiteral>(new NumberLiteral());
numLit.Append(new FormatCode("General"));
numLit.Append(new PointCount() { Val = new UInt32Value(1U) });
numLit.AppendChild<NumericPoint>(new NumericPoint() { Index = new UInt32Value(0u) }).Append(new NumericValue(data[key].ToString()));

i++;
}

chartSpace.Save();
}

The p2.pptx in the code simply contained a slide with a barchart.

Note that a chart in powerpoint has its associated data in an embedded excel file. In this sample we haven’t touched that excel file and hence haven’t changed that data. Hence, although your chart will render new data correctly, it may not show the new data in that excel (it appears when click “Edit Data” for the chart in PowerPoint). Anyways, this simple code worked for us cos our primary aim was to render the graph.

This document explains, as to how one can write data into an excel file using openxml.

Here we are considering that we already have an excel file and we are only writing data into the file and also assuming that the data which has to be written into excel is stored in a datatable.

To make use of openxml we first need to add reference to the openxml dll and use the following namespaces.

using DocumentFormat.OpenXml.Packaging;

using DocumentFormat.OpenXml.Spreadsheet;

using DocumentFormat.OpenXml;

 

To write the data, we first need to open the excel file. The below mentioned code is used to perform the same.

    public static void OpenExcelDocument(DataSet ds, string xlsxFilePath)

    {

        using (SpreadsheetDocument myWorkbook = SpreadsheetDocument.Open(xlsxFilePath, true))

        {

 

            //Access the main Workbook part, which contains all references

            WorkbookPart workbookPart = myWorkbook.WorkbookPart;

 

            foreach (DataTable dt  in ds.Tables)

            {

                InsertValuesInSheets(dt.TableName, workbookPart, dt);

            }

 

            workbookPart.Workbook.Save();

          

        }

    }

This method accepts two parameters, one is the dataset and the other is the complete path of the excel file. We are using a dataset here as an excel file can contain n number  of worksheets inside it, so the data that has to be added in each sheet can be stored in a datatable, which is intern added to the dataset. In the above method we are calling the InsertvaluesInsheets method for each table in the dataset.

Now let’s see what this InsertValuesInSheet method does. It accepts three parameters, the sheetname in excel, the workbookpart and the datatable which contains the data to be written. We first need to get the worksheetpart where the data has to be inserted, we use the sheetname as show below to get that particular worksheet part.

Sheet ss = workbookPart.Workbook.Descendants<Sheet>().Where(s=>s.Name== sheetName).SingleOrDefault<Sheet>();

 WorksheetPart           worksheetPart = (WorksheetPart)workbookPart.GetPartById(ss.Id);

 

If incase we have single sheet in the excel file then we do not need the sheetname and instead we can use the below line to get the first worksheet part from excel.

WorksheetPart worksheetPart = workbookPart.WorksheetParts.FirstOrDefault();

Next we get the sheet data and get the rows in the sheet by using the following line of code.

var excelRows = sheetData.Descendants<Row>().ToList();

 

Then we loop through each row in the datatable and check if the row exists in the excel sheet and if it exists, then we get the cells in that row and if cell exists, we update the cell value with the corresponding values from the datatable else we create a new cell, update the cell value and then add the cell to the excel row. Incase the row does not exists then we create a new row and add the corresponding values from the datatable and then added this to the excel sheet. 

Once all the data is written into the excel sheet then we save the changes that we made by calling the following line of code.

worksheetPart.Worksheet.Save();

public static void InsertValuesInSheets(string sheetName,WorkbookPart workbookPart,DataTable dt)

    {

        WorksheetPart worksheetPart = null;

        if (!string.IsNullOrEmpty(sheetName))

        {

            Sheet ss = workbookPart.Workbook.Descendants<Sheet>().Where(s => s.Name == sheetName).SingleOrDefault<Sheet>();

            worksheetPart = (WorksheetPart)workbookPart.GetPartById(ss.Id);

        }

        else

        {

            worksheetPart = workbookPart.WorksheetParts.FirstOrDefault();

        }

     

        if (worksheetPart != null)

        {

            SheetData sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();

            var excelRows = sheetData.Descendants<Row>().ToList();

    int excelRowIndex = 1;

                for (int rowIndexInTable = 0; rowIndexInTable < dt.Rows.Count; rowIndexInTable++)

                {

                    if (excelRowIndex >= excelRows.Count)

                    {

                        Row contentRow = CreateContentRow(excelRowIndex+1, dt.Rows[rowIndexInTable], dt.Columns.Count);

                        sheetData.AppendChild(contentRow);

                    }

                    else

                    {

                        var cells = excelRows[excelRowIndex].Descendants<Cell>().ToList();

                        for (int colIndexInTable = 0; colIndexInTable < dt.Columns.Count; colIndexInTable++)

                        {

                            Cell cell;

                            if (colIndexInTable >= cells.Count)

                            {

                               cell = new Cell();

                                    excelRows[excelRowIndex].AppendChild(AddValueToCell(cell, dt.Rows[rowIndexInTable][colIndexInTable].ToString()));

                            }

                            else

                            {

                                if (cells.Count == 1)

                                {

                                    cell = new Cell();

                                    excelRows[excelRowIndex].InsertAt(AddValueToCell(cell, dt.Rows[rowIndexInTable][colIndexInTable].ToString()), 0);

                                }

                                else

                                {

                                    cell = cells[colIndexInTable];

                                    AddValueToCell(cell, dt.Rows[rowIndexInTable][colIndexInTable].ToString());

                                }

                            }

 

                        }

                    }

                    excelRowIndex++;

                }

            worksheetPart.Worksheet.Save();

        }

    }

To create a new row and to add the same to the excel sheet we make use of another method called, CreateContentRow as shown below.

In this method we create a new row assign the index of the row and loop through the no.of columns in datatable and create new cell and added it to the row. To create a new cell we use the CreateTextCell method.

public static Row CreateContentRow(int index, DataRow dr, int headerColumnsCount)

    {

 

        //Create new row

 

        Row r = new Row();

 

        r.RowIndex = (UInt32)index;

 

        for (int i = 0; i < headerColumnsCount; i++)

        {

 

            Cell c = CreateTextCell(headerColumns[i], dr[i].ToString(), index);

            r.AppendChild(c);

 

        }

        return r;

    }

 

public static Cell CreateTextCell(string header, string text, int index)

    {

 

        //Create new inline string cell

 

        Cell c = new Cell();

 

        c.DataType = CellValues.InlineString;

 

        //Add text to text cell

 

        InlineString inlineString = new InlineString();

 

        Text t = new Text();

 

        t.Text = text;

 

        inlineString.AppendChild(t);

 

        c.AppendChild(inlineString);

 

        return c;

 

    }

}

Incase the cell is already existing in the excelsheet and we just have to update the data then we use the following method.

public static Cell AddValueToCell(Cell cell, string text)

    {

        cell.DataType = CellValues.InlineString;

        cell.RemoveAllChildren();

        InlineString inlineString = new InlineString();

        Text t = new Text();

        t.Text = text;

        inlineString.AppendChild(t);

        cell.AppendChild(inlineString);

        return cell;

    }

By using all the above methods we can write data into excel file using openxml.