Download Excel File using AJAX in jQuery

The Excel file can be downloaded as BLOB using jQuery AJAX and XmlHttpRequest (XHR) request. Once the file is successfully sent by the server, it could be downloaded using the Response object inside the Success event handler of jQuery AJAX function.

In this post I will explain the sample code to download the Excel file using AJAX in jQuery.

Lets say the following javascript function DownloadFile is called when user clicked the Download button on the web page. It accepts an arbitrary parameter fileId, you can use any other parameter like filename etc.

Inside the DownloadFile function, you can pass the url(or an accessible file path on server) in the URL parameter of the jQuery AJAX call.

Inside the success callback of AJAX function, we can read and download the target file as byte array from the xhr object. We read the response object as Blob.

function DownloadFile(fileId) {
        $.ajax({
            type: "POST",
            url: "MyController/MyAction",
            data: JSON.stringify({ fileId: fileId }),
            contentType: "application/json; charset=utf-8",
            xhrFields: {
                responseType: 'blob'
            },
            success: function (response, status, xhr) {

                var filename = "";
                var disposition = xhr.getResponseHeader('Content-Disposition');

                if (disposition) {
                    var filenameRegex = /filename[^;=\n]*=((['"]).*?\2|[^;\n]*)/;
                    var matches = filenameRegex.exec(disposition);
                    if (matches !== null && matches[1]) filename = matches[1].replace(/['"]/g, '');
                }
                var linkelem = document.createElement('a');
                try {
                    var blob = new Blob([response], { type: 'application/octet-stream' });

                    if (typeof window.navigator.msSaveBlob !== 'undefined') {
                        //   IE workaround for "HTML7007: One or more blob URLs were revoked by closing the blob for which they were created. These URLs will no longer resolve as the data backing the URL has been freed."
                        window.navigator.msSaveBlob(blob, filename);
                    } else {
                        var URL = window.URL || window.webkitURL;
                        var downloadUrl = URL.createObjectURL(blob);

                        if (filename) {
                            // use HTML5 a[download] attribute to specify filename
                            var a = document.createElement("a");

                            // safari doesn't support this yet
                            if (typeof a.download === 'undefined') {
                                window.location = downloadUrl;
                            } else {
                                a.href = downloadUrl;
                                a.download = filename;
                                document.body.appendChild(a);
                                a.target = "_blank";
                                a.click();
                            }
                        } else {
                            window.location = downloadUrl;
                        }
                    }

                } catch (ex) {
                    console.log(ex);
                }
            },
            failure: function (response) {
                console.log(response.d);
            },
            error: function (response) {
                console.log(response.d);
            }
        });         
    }

You can use the same code to download any binary file, like PDF etc.

Since we are creating html anchor element and simulate click to download file at client. Its good idea to also remove the link once you finished working.

In the following jquery code snippet we are creating anchor element, appending to the body tag and at the end we also removed it.

var a = $("<a />");
a.attr("download", fileName);
a.attr("href", link);
$("body").append(a);
a[0].click();
$("body").remove(a); 

Use ClosedXML to write excel file

Office Open XML (also informally known as OOXML) is a zipped, XML-based file format developed by Microsoft for representing spreadsheets, charts, presentations and word processing documents. Starting with the 2007 Microsoft Office system, Microsoft Office uses the XML-based file formats, such as .docx, .xlsx, and .pptx representing Microsoft Word, Microsoft Excel, and Microsoft PowerPoint repectively.

The OpenXML specification is a large and complicated model to implement. Trying to implement this model by your own involves a lot of time and effort. Another option is to use ClosedXML (an open source library created based on OpenXmlSdk) for manipulate OpenXML format files.

ClosedXML is a .NET library for reading, manipulating and writing Excel 2007+ (.xlsx, .xlsm) files. Having user-friendly model, it makes it easier to deal with OpenXML API.

To install ClosedXML, run the following command in the Package Manager Console:

Install-Package ClosedXML

The most important point about ClosedXML is:

ClosedXML allows you to create Excel files without the Excel application. The typical example is creating Excel reports on a web server.

First you have to import the library for Excel classes:

using ClosedXML.Excel;

In the following code snippet, we are creating a new worksheet Sheet1 and writing the text Hello World! on the cell A1, then save the file as HelloWorld.xlsx:

using (var workbook = new XLWorkbook())
{
    var worksheet = workbook.Worksheets.Add("Sheet1");
    worksheet.Cell("A1").Value = "Hello World!";
    workbook.SaveAs("HelloWorld.xlsx");
}

Change the font for the whole sheet:

worksheet.Style.Font.FontName = "Calibri";

Change the font style and color for a particular cell:

worksheet.Cells("A1").Style.Font.FontSize = 14;
worksheet.Cells("A1").Style.Font.Bold = true;
worksheet.Cells("A1").Style.Font.FontColor = XLColor.White;

Select the range and fill background color:

var range1 = worksheet.Ranges("A1:A25,B1:B25");
range1.Style.Fill.BackgroundColor = XLColor.FromHtml("#556B2F");

Adjusts the width of all columns based on its contents.

worksheet.Columns().AdjustToContents();

These are few examples which demonstrates the basic functionality ClosedXML supports for Excel files.

For official project site and Nuget Package, please visit the github and nuget sites:

References(s):

• https://github.com/ClosedXML/ClosedXML
• https://www.nuget.org/packages/ClosedXML/

User-defined exceptions in PL/SQL

If you are required to standardize the exception handling, not just to control the abnormal program flow but also to alter the program execution logic, you need to use user-defined exceptions. The user-defined exceptions are raised in the BEGIN..END section of the block using the RAISE statement.

With custom exceptions, Oracle allows users to specify names, associate error codes, and raise statements in line with the implementation logic.

There are three ways to declare user-defined exceptions.

• EXCEPTION type variable:

  • Declare the EXCEPTION type variable in the declaration section.
  • Raise it explicitly in the program body using the RAISE statement.
  • Handle it in the EXCEPTION section. No error code will be involved here.

• EXCEPTION variable:

  Declare the EXCEPTION variable and associate it with a standard error number using PRAGMA EXCEPTION_INIT.

• RAISE_APPLICATION_ERROR:

  Use the RAISE_APPLICATION_ERROR to declare a dedicated error number and error message.

Related Post(s):

• Exception handling in PL/SQL

Exception handling in PL/SQL

Exception happens when a program terminated abnomrally due to an unexpected flow during runtime. To suppress the abnormal termination with an alternative action we use exception handlers.

Exception handling is one of the important steps of database programming. Unhandled exceptions can result in unplanned application outages, impact business continuity, and frustrate end users.

There are two types of exceptions:

• system-defined:

  Implicitly raised by Oracle Database.

• user-defined:

  A user-defined exception is explicitly declared and raised within the program unit.

Let see an example of System-defined exception.

System-defined exceptions:

• These exceptions are defined and maintained implicitly by the Oracle Database and are defined in the Oracle STANDARD package.
• Whenever an exception occurs inside a program, the database picks up the appropriate one from an already defined list of exceptions.
• All system-defined exceptions are associated with a negative error code (except 1 to 100) and a short name. The short name is what we use while specifying the exception handlers.

For example, we have a table named EMP, with 100 employee records, with empployee ids as 1-100. In the following PL/SQL program, we include a SELECT statement to retreive the details of employee id 101. It raises NO_DATA_FOUND exception because a row with employee id 101 doesn't exist.

SET SERVEROUTPUT ON

DECLARE
	LOCAL_ENAME VARCHAR2 (100);
	LOCAL_SAL NUMBER;
	LOCAL_EMPID NUMBER := 101;

BEGIN
	SELECT ENAME, SAL
	INTO LOCAL_ENAME, LOCAL_SAL
	FROM EMP
	WHERE EMPNO = LOCAL_EMPID;
END;
/

You will get the output error message similar to this:

DECLARE
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 8

Let us rewrite the same PL/SQL block to include an EXCEPTION section and handle the NO_DATA_FOUND exception (note that we are using the short name (NO_DATA_FOUND) of exception we are expecting in our code block and want to handle):

SET SERVEROUTPUT ON

DECLARE
	LOCAL_ENAME VARCHAR2 (100);
	LOCAL_SAL NUMBER;
	LOCAL_EMPID NUMBER := 101;
BEGIN
	SELECT ENAME, SAL
	INTO LOCAL_ENAME, LOCAL_SAL
	FROM EMP
	WHERE EMPNO = LOCAL_EMPID;
EXCEPTION
	WHEN NO_DATA_FOUND THEN
	DBMS_OUTPUT.PUT_LINE ('No record found with the employee id: '||LOCAL_EMPID);
END;
/

This time you will not get the default error message, but the flow will cath-up in the exception block and run our logic which will display a customized message like this:

No record found with the employee id: 101
PL/SQL procedure successfully completed.

Related Post(s):

• User-defined exceptions in PL/SQL

PL/SQL - Cursor FOR loop

In the last post we have seen how to loop through cursor records by using its attributes to manually check for the different stages of cursor OPEN, FETCH and CLOSE. In this post we will write more compact code with CURSOR FOR loop statement. A FOR loop opening a cursor directly is known as a CURSOR FOR loop.

Following program will print the Salary for each Employee in EMP table.

/*Enable the SERVEROUTPUT to display messages in output console*/
SET SERVEROUTPUT ON

DECLARE
/*Declare an explicit cursor to select employee information*/
 CURSOR C_EMP IS
 SELECT EMP_NAME, SAL
 FROM EMP;
BEGIN
/*FOR Loop uses the cursor C_EMP*/
FOR EMP IN C_EMP
LOOP

 /*Display message*/
 DBMS_OUTPUT.PUT_LINE(EMP.EMP_NAME||' Salary = '||EMP.SAL);

END LOOP;
END;
/

You will get the output similar to this:

Nasir Naveed Salary = 30000
Aurangzaib Khan Salary = 40000
Muhammad Anwar Salary = 50000
...
PL/SQL procedure successfully completed.

Note that, with the help of CURSOR FOR loop we have:

• No need to declare the block variables for holding the cursor columns. The CURSOR FOR loop index implicitly acts as a record of the cursor type.
• No need to explicitly open or close the cursor in the PL/SQL program.

Related Post(s):

• PL/SQL Cursor - Overview
• PL/SQL - Cursor Attributes and Loop