Office OpenXML Standards

Generally we come across following issues when generating office documents at server :

1.Creating and Reading MS Office XPS (.XLSX, DOCX) file formats by using Interop libraries at server end requires MS office client installed on server.
2.When creates office documents which will invoke corresponding executable process(winword.exe,excel.exe) for every request and executable process hangs on server memory until garbage collected properly.
3.Response time of our request to create files will take long time if more requests made at a time.

The best way to overcome these problems (mentioned in Problem Statement) is using Office Open XML Standards to generate MS office files at server. Microsoft .NET Framework provides set of classes and wrappers around the System.IO.Packaging namespace which is in WindowsBase.dll to process the new Office Open XML file format. It extracts away the complexity of dealing with the individual XML components making it real easy to create sophisticated spreadsheets and documents on the server.
There are so many .NET helper libraries are available to generate Office files at server :
• DocX (http://docx.codeplex.com/)
• ExcelPackage (http://excelpackage.codeplex.com/)
• EPPlus (http://epplus.codeplex.com/)
• Simple OOXML (http://simpleooxml.codeplex.com/)

Among them we are using “EPPlus” which supports setting values and formulas, cell styling, auto filters, add drawings (charts, shapes and pictures) and more in excel (XLSX) format.

How to convert ORACLE XML String - CLOB as Valid XML and fetch values using XPATH

  -- Create the test table with a CLOB column
  CREATE a table with name - "SampleXmlCLOB" along with following columns
  ID INTEGER PRIMARY KEY,
  Subject VARCHAR(256),
  XML_CLOB_STRING CLOB

Table created.

How to read value using XPATH :

SELECT XmlType(SampleXmlCLOB .XML_CLOB_STRING).EXTRACT('//DATAMATRIX/FSDATAMATRIX[1]/FIELD1/text()').GETSTRINGVAL() FROM 
SampleXmlCLOB  WHERE SampleXmlCLOB .ID = 100

Internet Explorer - CSS CONDITIONAL Comments

Microsoft implemented conditional comments in their browser, which allow you to link a stylesheet that will be interpreted by a browser alone.

You can also target only a certain version of IE:

While conditional comments are better, we can also target some versions of Internet Explorer using the following syntax:

.class {
  width:200px; /* All browsers */
  *width:250px; /* IE */
  _width:300px; /* IE6 */
  .width:200px; /* IE7 */
}

Since this technique is not W3C compliant,conditional comments is best option. For more details please refer Microsoft official documentation -
http://msdn.microsoft.com/en-us/library/ms537512%28VS.85%29.aspx

How to RESET Default CSS Styles set by browsers

Web browsers define different default styling for html elements, the first thing to do is to always include a CSS reset in your stylesheet. By using this code, you're already eliminating lots of future headaches.

html,body,div,ul,ol,li,dl,dt,dd,h1,h2,h3,h4,h5,h6,pre,form,p,blockquote,fieldset,input,hr {margin:0; padding:0;}
h1,h2,h3,h4,h5,h6,pre,code,address,caption,cite,code,em,strong,th {font-size:1em; font-weight:normal; font-style:normal;}
ul,ol {list-style:none;}
fieldset,img,hr {border:none;}
caption,th {text-align:left;}
table {border-collapse:collapse; border-spacing:0;}
td {vertical-align:top;}

ADD FILE HEADER OR CHANGE LOG TO FILES IN VISUAL STUDIO BY USING MACRO

Adding the Macro:
1. Click “Tools” menu
2. Click “Macros” menu item
3. Click “Macros IDE…” menu choice
4. Right click on “My Macros”
5. Click “Add” menu item
6. Choose “Add Module…” (a module is a VB term for a static C# class with all static members)
7. Enter the name of “FileHeader” (or whatever you want to name your module)
8. Paste the code below and update any variables to match your organization or name

Imports System
Imports EnvDTE
Imports EnvDTE80
Imports EnvDTE90
Imports System.Diagnostics

Public Module FileHeader

    Sub FileHeader()
        Dim doc As Document
        Dim docName As String
        Dim companyName As String = "My Company"
        Dim authorName As String = "Benedict Alphonse"
        Dim copyrightText As String = "© 2009 My Company. All rights reserved"
        Dim Email As String = "benedictkmu@gmail.com"
        Dim Summary As String

        ' Get the name of this object from the file name
        doc = DTE.ActiveDocument

        ' Get the name of the current document
        docName = doc.Name

        ' Set selection to top of document
        DTE.ActiveDocument.Selection.StartOfDocument()
        DTE.ActiveDocument.Selection.NewLine()

        ' Write first line
        DTE.ActiveDocument.Selection.LineUp()
        DTE.ActiveDocument.Selection.Text = "#region File Header"
        DTE.ActiveDocument.Selection.NewLine()
        DTE.ActiveDocument.Selection.Text = "// ******************************************************************************************************************"
        DTE.ActiveDocument.Selection.NewLine()

        ' Write copyright tag
        DTE.ActiveDocument.Selection.Text = "// "
        DTE.ActiveDocument.Selection.NewLine()
        DTE.ActiveDocument.Selection.Text = "//     " + copyrightText
        DTE.ActiveDocument.Selection.NewLine()
        DTE.ActiveDocument.Selection.Text = "// "

        ' Write author name tag (optional)
        DTE.ActiveDocument.Selection.NewLine()
        DTE.ActiveDocument.Selection.Text = "// " + authorName + ""
        DTE.ActiveDocument.Selection.NewLine()

        ' Write email  tag (optional)
        DTE.ActiveDocument.Selection.NewLine()
        DTE.ActiveDocument.Selection.Text = "// " + Email + ""
        DTE.ActiveDocument.Selection.NewLine()

        ' Write email  tag (optional)
        DTE.ActiveDocument.Selection.NewLine()
        DTE.ActiveDocument.Selection.Text = "// 
" + docName + "
"
        DTE.ActiveDocument.Selection.NewLine()

        ' Write email  tag (optional)
        DTE.ActiveDocument.Selection.NewLine()
        DTE.ActiveDocument.Selection.Text = "//------------------------------Revision History---------------------------------------------------------------------"
        DTE.ActiveDocument.Selection.NewLine()
        DTE.ActiveDocument.Selection.Text = "// Date             Author                  Change Log Comments"
        DTE.ActiveDocument.Selection.NewLine()
        DTE.ActiveDocument.Selection.Text = "//" + DateTime.Now.ToString("MMM/dd/yyyy") + "        " + authorName + "               " + "Newly Created" + "                  "
        DTE.ActiveDocument.Selection.NewLine()
        DTE.ActiveDocument.Selection.Text = "//                                                             "
        DTE.ActiveDocument.Selection.NewLine()
        DTE.ActiveDocument.Selection.Text = "//                                                             "
        DTE.ActiveDocument.Selection.NewLine()
        ' Write last line
        DTE.ActiveDocument.Selection.Text = "// ******************************************************************************************************************"
        DTE.ActiveDocument.Selection.NewLine()
        DTE.ActiveDocument.Selection.Text = "#endregion"
    End Sub

End Module

Macro Results This creates the following text inserted into the top of the source code document:

#region File Header
// ******************************************************************************************************************
// 
//     © 2009 My Company. All rights reserved
// 
// Benedict Alphonse

// benedictkmu@gmail.com

// 
ReportUtils.cs

//------------------------------Revision History---------------------------------------------------------------------
// Date             Author                  Change Log Comments
//Dec/01/2009        Benedict Alphonse               Newly Created                  
//                                                             
//                                                             
// ******************************************************************************************************************
#endregion

Adding Macro to Toolbar: To add this to a toolbar in your IDE:
1. Click “Tools” menu
2. Click “Customize…” menu choice
3. Activate “Commands” tab
4. Choose “Macros” category
5. Select “MyMacros.FileHeader.FileHeader” (or whatever you named it)
6. Drag command to your toolbar.
7. You can customize the icon, text, visibility of text/icon, etc. by right clicking on the toolbar button
8. Close customization dialog

Render Crystal Report in different file formats without CrystalReportViewer

public static void RenderCrystalReport(DataSet ResultSet, String ReportLocation, String RenderType,HttpResponse Response)
        {
            MemoryStream oStream = new MemoryStream();
            ReportDocument crystalReport = new ReportDocument();
            crystalReport.Load(HttpContext.Current.Server.MapPath(ReportLocation));
            crystalReport.SetDataSource(ResultSet);

            switch (RenderType)
            {
                case "PDF":
                    oStream = crystalReport.ExportToStream(ExportFormatType.PortableDocFormat) as MemoryStream;
                    Response.Clear();
                    Response.Buffer = true;
                    Response.ContentType = "application/pdf";
                    break;

                case "DOC":
                    oStream = crystalReport.ExportToStream(ExportFormatType.WordForWindows) as MemoryStream;
                    Response.Clear();
                    Response.Buffer = true;
                    Response.ContentType = "application/doc";
                    break;

                case "XLS":
                    oStream = crystalReport.ExportToStream(ExportFormatType.Excel) as MemoryStream;
                    Response.Clear();
                    Response.Buffer = true;
                    Response.ContentType = "application/vnd.ms-excel";
                    break;

            }


            Response.BinaryWrite(oStream.ToArray());
            Response.End();
        }

ORACLE CUSTOM FUNCTION - SPLIT DELIMITER STRING

-- User Defined Types to Hold string as table Starts --
CREATE OR REPLACE TYPE SPLITTED_TEXT AS OBJECT(SPLITTED_VALUE VARCHAR2(50));
CREATE OR REPLACE TYPE SPLITTED_TEXT_TABLE AS TABLE OF SPLITTED_TEXT;
-- User Defined Types to Hold string as table Ends--

-- Function to split delimiter string Starts --

CREATE OR REPLACE
FUNCTION OBF_SPLIT_DELIMITER_STRING_FN(p_string IN VARCHAR2, p_delimiter IN VARCHAR2)
RETURN SPLITTED_TEXT_TABLE PIPELINED
AS
    v_length   NUMBER := LENGTH(p_string);
    v_start    NUMBER := 1;
    v_index    NUMBER;
    --v_tempstring SPLITTED_TEXT_TABLE.SPLITTED_VALUE%type;
BEGIN
    WHILE(v_start <= v_length)
    LOOP
        v_index    := INSTR(p_string, p_delimiter, v_start);

        IF v_index = 0
        THEN    
           -- v_tempstring := SUBSTR(p_string,v_start);
            PIPE ROW(new SPLITTED_TEXT(SUBSTR(p_string,v_start)));
            v_start    := v_length + 1;
        ELSE
            --v_tempstring := SUBSTR(p_string, v_start, v_index - v_start);
            PIPE ROW(new SPLITTED_TEXT(SUBSTR(p_string, v_start, v_index - v_start)));
            v_start    := v_index + 1;
        END IF;
    END LOOP;

    RETURN;
END OBF_SPLIT_DELIMITER_STRING_FN;

-- Function to split delimiter string Ends--

-- Usage of Function Starts --

select * from table(OBF_SPLIT_DELIMITER_STRING_FN('one#two#three#four','#'))

-- Usage of Function Ends--