My technical experience

Monday, June 7, 2010

Generating MS Word 2007 file from html content

Hello everyone,

My teammate was given a task to edit html markup content stored in a database and to generate a word document from the edited html content. I used "DocumentFormat.OpenXml" to generate a word document from the html content. Happy to solve my teammate's problem :).

The below code use a template word document. Using the template, a new document is generated on server. The file can be deleted from the server once client has downloaded.

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

Add references "DocumentFormat.OpenXml.Packaging" and "DocumentFormat.OpenXml.Wordprocessing" in your code.

Code:


//It will generate Word 2007 document for the html content.
public static void GenerateWordDocFromHTMLContent(string templateFilePath, string documentFilePath, DataTable data, string fileName)
{
  File.Copy(templateFilePath, documentFilePath, true);

  using (WordprocessingDocument wordDoc = WordprocessingDocument.Open(documentFilePath, true))
  {
      MainDocumentPart mainPart = wordDoc.MainDocumentPart;
      int altChunkIdCounter = 1;
      int blockLevelCounter = 1;

      //Fetch the html content from DataTable. Let us take first record.
      string htmlContent = data.Rows[0]["HTMLMarkUp"].ToString();
      string html = "<html><body>" + htmlContent + "</html></body>";
      string altChunkId = String.Format("AltChunkId{0}", altChunkIdCounter++);

      //Import data as html content using Altchunk
      AlternativeFormatImportPart chunk = mainPart.AddAlternativeFormatImportPart(AlternativeFormatImportPartType.Xhtml, altChunkId);

      using (Stream chunkStream = chunk.GetStream(FileMode.Create, FileAccess.Write))
      using (StreamWriter stringStream = new StreamWriter(chunkStream, Encoding.UTF8)) //Encoding.UTF8 is important to remove special characters
          stringStream.Write(html);

      AltChunk altChunk = new AltChunk();
      altChunk.Id = altChunkId;

      mainPart.Document.Body.InsertAt(altChunk, blockLevelCounter++);
      mainPart.Document.Save();
  }
}

Note: The content type or Mime type for word 2007 file is "application/vnd.openxmlformats-officedocument.wordprocessingml.document"

References:
http://msdn.microsoft.com/en-us/library/ee956524%28office.14%29.aspx
http://msdn.microsoft.com/en-us/library/dd469465.aspx

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