Saturday, May 22, 2010

Calling WCF services via Perl environment

Hello everyone,

Let's see the advantages of Service Oriented Architecture (SOA). Recently, I was a part of development team for mobile application. We wanted to create a mobile version of a web application with extra features. It would had been easy for us to create a different client version if we had followed the SOA.

Windows Communication Foundation (WCF) is the service oriented programming which can be consumed by clients. One of the best feature of WCF services is interoperability.

Let's test this functionality of WCF services by consuming them via Perl environment.

1) Create a WCF service project

2) Add an operation contract.
[OperationContract(Name = "GetInputData")]
string GetInputData(int result);

3) Change the binding from default "wsHttpBinding" to "basicHttpBinding" in web.config. BasicHttpBinding is based on SOAP 1.1 specifications whereas WSHttpBinding is based on SOAP 1.2 specifications.

Perl script:

#!/usr/bin/perl -w
print "Content-type: text/html\n\n";

package main;
use SOAP::Lite;

my $ws_url = 'http://1160/WCFServices/SampleWCFService.svc?wsdl';
my $ws_uri = 'http://tempuri.org/';
my $ws_xmlns = 'http://tempuri.org';

my $soap = SOAP::Lite
-> uri( $ws_uri)
-> on_action(sub{sprintf '%sISampleWCFService/%s', @_})
-> proxy($ws_url);
#WCF service's method name and signature should match mapping wsdl file.
my $response = $soap->GetInputData(SOAP::Data->new(name => 'result', value => 5));

if($response->fault)
{
die $response->faultstring;
}
else
{
print $response->result;
}

References:
http://www.soaplite.com/
http://www.cpan.org/
http://www.perlmonks.org/?node_id=649135

Wednesday, May 19, 2010

Downloading Excel 2007 reports in asp.net

Hello everyone,

This is my first blog :). I enjoy working in any .Net technologies. Hence , let me start my blog with my experience in .Net. Most of times, I was told to generate reports in MS Excel files. Excel 2007 have a zip file format with a bundle of xml files. This makes life simple.

Let's say you have a created a asp.net website and in that you have a grid with some css styling. You want to add a feature of downloading grid data with same css styling to an excel 2007 file at client end.

I am using "DocumentFormat.OpenXml.Drawing.Spreadsheet". I felt this as a scalable approach.

Add two dlls DocumentFormat.OpenXml.dll and WindowsBase.dll in your web application/ website.

For time being let's use a template for excel 2007 file which has same styling as we have for data grid.

Code:
// It will generate Office Excel 2007 file based on the input data. The report will be generated at the given documentFilePath.

public static void GenerateExcelReport(string templateFilePath, string documentFilePath, DataTable data)
{
File.Copy(templateFilePath, documentFilePath, true);

using (SpreadsheetDocument myWorkbook = SpreadsheetDocument.Open(documentFilePath, true))
{
// Access the main Workbook part, which contains all references.
WorkbookPart workbookPart = myWorkbook.WorkbookPart;

// Get the first worksheet.
WorksheetPart worksheetPart = (WorksheetPart)workbookPart.GetPartById("rId1");

//WorksheetPart worksheetPart = workbookPart.WorksheetParts.ElementAt(2);

// The SheetData object will contain all the data.
SheetData sheetData = worksheetPart.Worksheet.GetFirstChild();

Stylesheet styleSheet = myWorkbook.WorkbookPart.WorkbookStylesPart.Stylesheet;
UInt32 styleIndex = styleSheet.Fonts.Count;

int rowIndex = 1;
// New Row
Row row = new Row();
row.RowIndex = (UInt32)rowIndex;

int colIndex = 1;
Cell cell = null;
// For each item in the database, add a Row to SheetData.
foreach (DataColumn col in data.Columns)
{

// New Cell
cell = new Cell();
cell.DataType = CellValues.String;
// Column A1, 2, 3 ... and so on
cell.CellReference = colIndex.ToString() + rowIndex;
cell.CellValue = new CellValue(col.ColumnName);

cell.StyleIndex = styleIndex;

// Append Cell to Row
row.AppendChild(cell);

colIndex++;
}

// Append Row to SheetData
sheetData.AppendChild(row);

rowIndex = 2;
foreach (DataRow item in data.Rows)
{
// New Row
row = new Row();
row.RowIndex = (UInt32)rowIndex;

colIndex = 1;

// For each item in the database, add a Row to SheetData.
foreach (DataColumn col in data.Columns)
{

// New Cell
cell = new Cell();
//cell.DataType = CellValues.InlineString;
if (string.Compare(col.DataType.Name, "Double") == 0 || string.Compare(col.DataType.Name,"Int32") == 0)
{
cell.DataType = CellValues.Number;
}
else
{
cell.DataType = CellValues.String;
}

// Column A1, 2, 3 ... and so on
cell.CellReference = colIndex.ToString() + rowIndex;

if (string.Compare(col.DataType.Name, "DateTime") == 0)
{
cell.CellValue = new CellValue(Convert.ToDateTime(item[col].ToString()).ToShortDateString());
}
else
{
cell.CellValue = new CellValue(item[col].ToString());
}
// Append Cell to Row
row.AppendChild(cell);

colIndex++;
}

// Append Row to SheetData
sheetData.AppendChild(row);

// increase row pointer
rowIndex++;
}

// save
worksheetPart.Worksheet.Save();
}
}

Now the file is generated on server and you can allow client to download it.

Note: The content type or Mime type for excel 2007 file is "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"

In this post, we downloaded the data in excel. We can even download the chart images in excel file using "DocumentFormat.OpenXml.Drawing.Spreadsheet".

References: http://openxmldeveloper.org/forums/3675/ShowThread.aspx#3675