wordpress hit counter
Exporting html data into SpreadsheetML - OpenXML Developer - Blog - OpenXML Developer
Goodbye and Hello

OpenXmlDeveloper.org is Shutting Down

There is a time for all good things to come to an end, and the time has come to shut down OpenXmlDeveloper.org.

Screen-casts and blog posts: Content on OpenXmlDeveloper.org will be moving to EricWhite.com.

Forums: We are moving the forums to EricWhite.com and StackOverflow.com. Please do not post in the forums on OpenXmlDeveloper.org. Instead, please post in the forums at EricWhite.com or at StackOverflow.com.

Please see this blog post for more information about my plans moving forward.  Cheers, Eric

Exporting html data into SpreadsheetML

Exporting html data into SpreadsheetML

  • Comments 4

                               By Muthu Kumar Arjunan of Sonata Software Ltd.

 

The previous articles in this series (article1, article 2 on XSL transformation of Spreadsheet ML to HTML – Part 1 & 2) explained an XSLT-based approach for displaying the data from a SpreadsheetML document in a web page (HTML).  In continuation of the series, this article focuses on exporting data from the HTML table into a SpreadsheetML document.

 

This exporting of data from HTML table to an Open XML spreadsheetML document is achieved using JavaScript, XmlHttp and Excel object model library.  Source code  for the sample application is downloadable.

 

If the reader has .net 3.0 installed in his computer, then the exporting of data from HTML table to spreadsheet ML document can be achieved using JavaScript, XmlHttp and

Excel Package by Dr.John Tunnicliffe.  (See http://www.codeplex.com/ExcelPackage  and http://openxmldeveloper.org/articles/Creating_Spreadsheets_Server.aspx.)

 

We will also throw some directives on integrating Excel Package into the sample application (downloadable).

 

In real time applications, the server side code for creation of excel documents can be exposed as a web service, which will serve heterogeneous client applications in the creation of excel documents.

 

The sample application (downloadable) demonstrates how:

 JavaScript in any webpage can be used to create an xml file from the data stored in HTML table.

This xml file is then sent to the server using XMLHTTP.

On the server; we’ve used the Excel object Model library to populate the Excel sheet with the data stored in the XML file.

A link is provided in the web page to download this excel file (populated with the data in the HTML table).

 

The GUI to Upload Document:

Fig: UploadExcelFile_GUI.PNG

 

 Note the port used by Cassini web server from the URL

 (The URL in the image (above) is http://localhost:3830/AsynchronousProcessing/SubmitLongProcess.aspx, Hence the port used by Cassini web server, in this case, is 3830)

 

Select the excel file to upload using the “Browse” button.

 

Click on “Convert2XML and Display as table (Using XSLT)” button.

 

 

After the “processing” time, the HTML table holding the data (from Excel sheet uploaded) is displayed in the web page.

 

 

 

Fig: ExportData_GUI.PNG

 

 

In the Submit URL: Change the Cassini port (highlighted in the image) to the one used in your system.  

On Click on “Export Data to Excel”, a JavaScript function creates an xml file from the data stored in HTML table. This xml file is then sent to the server using XMLHTTP.

On the server; we’ve used the Excel object Model library to populate the Excel sheet with the data stored in the XML file.

A link(“Download EXcel File”) is provided in the web page to download this excel file (populated with the data in the HTML table), as shown in the image below.

 

Fig: DownLoadExcelFile_GUI.PNG

 

Thus the downloaded excel file holds the whole table (data) displayed in the web page.

 

 

This excel file (.xls) is not a Spreadsheet ML document, in other words, this files does not contain the OpenXml constituents.

 

If you have .net 3.0 installed on your machine, then the Excel Package created by

Dr. John Tunnicliffe comes in aid to create a Spreadsheet ML document on the server using the XML data sent from the HTML/web page.

 

To create a SpreadsheetML document using the Excel Package:

 

Do the following to the sample project (downloadable).

 

Step1: download the runtime binary from   http://www.codeplex.com/ExcelPackage/Release/ProjectReleases.aspx

 

And refer it from ur project.

 

Step2: Recode the server-side function(“CreateExcelUsingXML”) in Default.aspx.cs

 

Refer article : http://openxmldeveloper.org/articles/Creating_Spreadsheets_Server.aspx

 

The following code is a directive:

 

private void CreateExcelUsingXML(XmlDataDocument objXmlDataDocument)

    {

         try

         {

             string strGUID = System.Guid.NewGuid().ToString();

            // string strGUID = "MyNewFile";

        string strFilePath = @"C:\Inetpub\wwwroot\AsynchronousProcessing\DownLoadFile_location\" + strGUID.ToString() + ".xlsx";

 

        FileInfo newFile = new FileInfo(strFilePath);

        if (newFile.Exists)

        {

            newFile.Delete();  // ensures we create a new workbook

            newFile = new FileInfo(strFilePath);

        }

        System.Data.DataSet DATASETNAME = new DataSet();

        StringReader strReader = new StringReader(objXmlDataDocument.OuterXml);

        DATASETNAME.ReadXml(strReader);

        using (ExcelPackage xlPackage = new ExcelPackage(newFile))

        {

            // this will cause the assembly to output the raw XML files in the outputDir

            // for debug purposes.  You will see to sub-folders called 'xl' and 'docProps'.

            xlPackage.DebugMode = true;

 

            // add a new worksheet to the empty workbook

            ExcelWorksheet worksheet = xlPackage.Workbook.Worksheets.Add("Tinned Goods");

            // write some strings into column 1

            System.Data.DataTable table = DATASETNAME.Tables[0];

            int ColumnIndex = 0;

            foreach (DataColumn col in table.Columns)

            {

                ColumnIndex++;

                worksheet.Cell(1, ColumnIndex).Value = col.ColumnName;

                //Range objRange = (Range)excel.Cells[1, ColumnIndex];

                //objRange.Font.Bold = true;

            }

 

            int rowIndex = 0;

            foreach (DataRow row in table.Rows)

            {

                rowIndex++;

                ColumnIndex = 0;

 

                foreach (DataColumn col in table.Columns)

                {

                    ColumnIndex++;

                    worksheet.Cell(rowIndex + 1, ColumnIndex).Value = row[col.ColumnName].ToString();

                }

            }

 

          

            // set some core property values

            xlPackage.Workbook.Properties.Title = "Import XML data";

            xlPackage.Workbook.Properties.Author = "Sonata";

            xlPackage.Workbook.Properties.Subject = "ExcelPackage Porting XML into XLSX";

            xlPackage.Workbook.Properties.Keywords = "Office Open XML";

            xlPackage.Workbook.Properties.Category = "SpreadSheetML articles";

            xlPackage.Workbook.Properties.Comments = "This sample demonstrates how to populate an xml file into Excel 2007 file from scratch using the Packaging API and Office Open XML";

 

            // set some extended property values

      

            xlPackage.Save();

 

            string strLink = "?FileName=" + strGUID.ToString() + ".xlsx";

 

            Response.Write("<root><a href='" + strLink + "'>Download Excel File</a></root>");

 

        }

     }

          catch (Exception ex)

        {

            Response.Write(ex.Message);

        }

 

 

     }

 

For further information on Excel Package ref: http://www.codeplex.com/ExcelPackage

 

That's all guys.  Happy sharing this with you.

 

Attachment: Downloads.zip
Page 1 of 1 (4 items)