Thursday, May 19, 2011

Different ways to create excel files

I Just analyzed the methodologies used to read/write excel files and listed the pros and cons below.

OpenXML (Office 2007 .XLSX)

Pros:

  • Reliable as this is a Microsoft’s product.
  • Native Excel format
  • Supports all Excel features
  • Do not require an install copy of Excel
  • Open XML SDK 2.o Productivity tool to generate code.

Cons:

  • Limited compatibility outside Excel 2007
  • Complicated unless you're using an expensive third party component

NPOI

Pros:

  • Easy to use and similar to existing SyncFusion code.
  • Do not require an install copy of Excel
  • Excel Chart, Pivot Table, Auto filter on columns, Insert images in header or footer are supported in NPOI 1.2.3 and later versions.
  • Supports Office Open Xml (OOXML) format also.(using ExcelPackage)

Cons:    

COM Interop

Pros:

  • Uses native Microsoft libraries
  • Read support for native documents

Cons:

  • Very slow
  • Dependency/version matching issues
  • Concurrency/data integrity issues for web use when reading
  • Scaling issues for web use (different from concurrency): need to create many instances of heavy Excel app on the server
  • Requires Windows

SpreadSheetML (open format XML)

Pros:

  • Supports most Excel features: formatting, styles, formulas’, multiple sheets per workbook
  • Excel does not need to be installed to use it
  • No third party libraries needed - just write out your xml
  • Documents can be opened by Excel XP/2003/2007

Cons:

  • Lack of good documentation
  • Not supported in older versions of Excel (pre-2000)
  • Write-only, in that once you open it and make changes from Excel it's converted to native Excel.
  • Apart from these, there are some more third party tools available.
  1. Spreadsheetgear http://www.spreadsheetgear.com (License required)

They are claiming that it is faster than Open XMl SDK 2.0.  We can find a testimonial from MSN project manager saying MSN is using spreadsheetgear in the home page.

  1. Aspose (http://www.aspose.com/categories/.net-components/aspose.total-for-.net/default.aspx)  (License required)
  2. MyXlshttp://sourceforge.net/projects/myxls/ (Freeware)

Monday, May 2, 2011

Nullable Types in C#

Nullable types are instances of the System.Nullable<T> struct.

In C# 2.0 and above, you can store null value in any of your datatypes. But the datatype is little bit different.

We know that bool type will hold either true or false. But nullable bool will allow you to assign null also.

Characteristics:

Value Type

Nullable types represent value-type variables that can be assigned the value of null. You cannot create a nullable type based on a reference type. (Reference types already support the null value.)

Assign a value to a nullable type in the same way as for an ordinary value type, for example

         int? i = 10;

         double? d1 = 3.14;

         bool? flag = null;

         char? letter = 'a';

         int?[] arr = new int?[10];

 

GetValueOrDefault

Use the System.Nullable.GetValueOrDefault property to return either the assigned value, or the default value for the underlying type if the value is null, for example

int x? = 10;

int j = x.GetValueOrDefault();

HasValue & Value

Use the HasValue and Value read-only properties to test for null and retrieve the value, for example

if (x.HasValue) j = x.Value;

The HasValue property returns true if the variable contains a value, or false if it is null.

The Value property returns a value if one is assigned, otherwise a System.InvalidOperationException is thrown.

The default value for a nullable type variable sets HasValue to false. The Value is undefined.

Conversions

Explicit Conversions:

A nullable type can be cast to a regular type, either explicitly with a cast, or by using the Value property. For example:

         int? n = null;

 

         //int m1 = n;      // Will not compile.

         int m2 = (int)n;   // Compiles, but will create an exception if x is null.

         int m3 = n.Value;  // Compiles, but will create an exception if x is null.

 

Implicit Conversions:

The conversion from an ordinary type to a nullable type, is implicit.

         int? n2;

         n2 = 10;  // Implicit conversion.

Operators

The predefined unary and binary operators and any user-defined operators that exist for value types may also be used by nullable types.

         int? a = 10;

         int? b = null;

 

         a++;         // Increment by 1, now a is 11.

         a = a * 10;  // Multiply by 10, now a is 110.

         a = a + b;   // Add b, now a is null.

 

 

?? operator

Use the ?? operator to assign a default value that will be applied when a nullable type whose current value is null is assigned to a non-nullable type.

Example 1

         int? c = null;

         // d = c, unless c is null, in which case d = -1.

         int d = c ?? -1;

 

   Example 2

         int? e = null;

         int? f = null;

 

         // g = e or f, unless e and f are both null, in which case g = -1.

         int g = e ?? f ?? -1;

 

 

 Boxing Nullable Types

                Keep in mind that nullable types are value types and can hold null values whereas Reference types hold null value by default.

Objects based on nullable types are only boxed if the object is non-null. If HasValue is false, the object reference is assigned to null instead of boxing. For example:

bool? b = null;

      object o = b;

// Now o is null.

If the object is non-null -- if HasValue is true -- then boxing occurs, but only the underlying type that the nullable object is based on is boxed. Boxing a non-null nullable value type boxes the value type itself, not the System.Nullable<T> that wraps the value type. For example:

bool? b = false;

      int? i = 44;

      object bBoxed = b; // bBoxed contains a boxed bool.

      object iBoxed = i; // iBoxed contains a boxed int.

The two boxed objects are identical to those created by boxing non-nullable types. And, just like non-nullable boxed types, they can be unboxed into nullable types, as in the following example:

bool? b2 = (bool?)bBoxed;    

      int? i2 = (int?)iBoxed;

Identifying Nullable Types

We can identify the nullable types using the following methods.

         1. System.Type

         2. System.Reflection

        3. is operator

Using System. Type

We can use the C# typeof operator to create a Type object that represents a Nullable type:

         System.Type type = typeof(int?);

Using System.Reflection

You can also use the classes and methods of the System.Reflection namespace to generate Type objects that represent Nullable types.

 

int? i = 5;

Type t = i.GetType();

      Console.WriteLine(t.FullName); //"System.Int32"

The C# is operator also operates on a Nullable's underlying type. Therefore you cannot use is to determine whether a variable is a Nullable type.

  static void Main(string[] args)

        {

            int? i = 5;

            if (i is int) // true

            {

                //

            }

        }