Friday, 22 March 2013

google.visualization.DataTable php helper class

Montana_bgIn this article I would like to present a helper class that I use in my php project HomeBook. I am very happy using it and I would like to share it with You. Everybody is free to use and modify it.


I like "Google Chart Tools" and I use them often in my web applications. To achieve good performance of my work and readable code I often have to create helpers classes and methods  that automate request and page generation. This is I suppose a common practice for many developers.

google.visualization.DataTable

All  "Google Chart" are populated with data using a common JavaScript DataTable class. Having a common data structure makes it easy to switch between chart types. This class exposes methods for sorting, modifying, and filtering data. There is a very good description of this class in Google Visualization API Reference.

GoogleVisualizationDataTable

This is the helper class that I use in my php project HomeBook to create and populate a google.visualization.DataTable using php. It provides a JSON string of the class instance, that can be used in all "Google Chart Tools". The source code of the class can be found in the resource link at the end of this article. It is very simple and there is no need of many explanation. If you have questions, I will be happy to respond to them in the comments. Here I would like to present a sample usage af this class.

Example usage of GoogleVisualizationDataTable.class.php

Define a class that extend GoogleVisualizationDataTable (in the example GVDTUsers class).


In the class constructor setup:
  • columns
  • SQL query
  • query parameters

class GVDTUsers extends GoogleVisualizationDataTable {

    public function __construct($user_bank_id) {

        $this->cols = array();

        $this->cols[] = new GVColumn("id", "ID", GVColumnType::STRING);
        $this->cols[] = new GVColumn("name", "Name", GVColumnType::STRING);
        $this->cols[] = new GVColumn("birthday", "Birthday", GVColumnType::DATE);
        $this->cols[] = new GVColumn("is_admin", "Admin", GVColumnType::BOOL);
        $this->cols[] = new GVColumn("loan", "Loan", GVColumnType::NUMBER);

        $this->setSQL("SELECT 
                        id, 
                        name, 
                        ".GoogleVisualizationDataTable::GTDateSelectStr("birthday").", 
                        is_admin, 
                        loan
                    FROM user
                    WHERE loan>0 AND  user_bank_id=?
                    ORDER BY loan DESC"); 

        $this->setBoundParamsArr(array('i', $user_bank_id));

        $this->rows = array();
    }

}


In the page where you would like to use data create class instance width suitable parameters, call GetData() metod to retrieve data and finally call toJSON() method that returns a JSON representation of the DataTable that can be passed to the DataTable constructor

For example:
$dt = new GVDTUsers($current_bank->getId());
$dt->GetData();
$dataJson = $dt->toJSON();

<script type="text/javascript">
....
var userDataTableJS = new google.visualization.DataTable(< php echo $dataJson >);
var chart = new google.visualization.SteppedAreaChart(document.getElementById('chart_user_div'));
var chartOptionsPH = {...};
chart.draw(userDataTableJS, chartOptionsPH); 
....
</script>
Important! 
If you have date/datetime fileld, as in the example, 2 additional rows of code are needed to remove " in JSON date field value.
$dataJson = preg_replace('/\"new/', 'new', $dataJson);
$dataJson = preg_replace('/\)\"/', ')', $dataJson);
That's it!

Source file GoogleVisualizationDataTable.class.php.zip can be downloaded from blog.iordanov.info.

Monday, 1 August 2011

Read data from Microsoft Excel using Microsoft Visual C# .NET

There is many ways to read excel file. This article describes several methods for transferring data from Microsoft Excel using a Microsoft Visual C# .NET program. It also presents the advantages and disadvantages of each method.

State of the Art

Read data via COM Interop.

To use the Excel object you must first set a COM reference to the Microsoft Excel Object Library which is an ActiveX component. Behind the scenes .NET uses the Interop.Excel DLL to talk to Excel. This DLL must be distributed with your application.[1]
You can format your data as a TAB delimited string, use the Excel object to start an instance of Microsoft Excel, select the worksheet and range to populate, and paste in your data.
An issue exists with the Excel object when the user closes your application before closing the instance of Excel that you started. This instance becomes an orphaned process - i.e. when you look at the Processes tab in Windows Task Manager you will see a process exists for Excel.Exe yet there will be no visible Excel applications or windows present.[2]
This method of reading Excel is very slow, especially in case when input files have more than 100,000 rows and required Excel to be installed on the target machine.[3]

'Excel's OleDb Driver

Another way of reading an Excel file and loading it to a DataTable is by using OleDbConnection, OleDbCommand and OleDbDataReaders. Basically you will treat the Excel file like a database where each work sheet represents a table.[4]
The interop assemblies provide a lot of options for working with Excel data, both reading and writing. Some experimenting with the object model can yield decent performance and expose some very useful options which are not obvious from reading the documentation. OleDB provides fast read access to Excel data, but it didn't meet my specific needs, which included accessing only certain columns and data validation.[5]
When DataReader ran through data rows without reading them (i.e. while (rd.Read());) it was quite fast, but retrieving data was still very slow.[3]

Custom Reader

  • Alex Reader. Reader will produce a DataSet object, with sheets represented as DataTables within it.[3]
  • Dusty Candland Reader. So in looking for a better way to accomplish my goals with reasonable CPU and speed, I experimented with the Office 2003 Interop Assemblies. I found, in my opinion, a decent way to accomplish the needed speed. CPU usage can still be high, but at an acceptable trade off for my needs.The interop assemblies provide a lot of options for working with Excel data, both reading and writing. Some experimenting with the object model can yield decent performance and expose some very useful options which are not obvious from reading the documentation.[5]

Best Practices

  • Turn Off Everything But the Essentials While Your Code is Running[6]
  • Read/Write Large Blocks of Cells in a Single Operation
  • Avoid Selecting / Activating Objects
  • Use Range.SpecialCells() to scope down the number of cells your code needs to work with.
  • For simple functions used by your code in high frequency, implement them yourself in VBA instead of using the WorksheetFunction object.
  • Declare variables with explicit types to avoid the overhead of determining the data type (repetitively if used in a loop) during code execution.

References


RitchTextBox Drag & Drop from RTF and HTML Originators


Drag-and-drop operations with the Windows Forms RichTextBox control are done by handling the DragEnter and DragDrop events. To enable drag operations in a RichTextBox control[1]
1. Set the AllowDrop property of the RichTextBox control to true.
2. Add handlers for both the DragEnter and DragDrop event of RichTextBox.
richTextBox.AllowDrop = true;
  richTextBox.DragEnter += new DragEventHandler(richTextBox_DragEnter);
  richTextBox.DragDrop += new DragEventHandler(richTextBox_DragDrop);
3.DragEnter event: Use a check to ensure for data being dragged is of an acceptable type (in this case - rtf and html). The DragEventArgs.Effect property can be set to any value of the DragDropEffects enumeration. In our case for both we use DragDropEffects.Copy for both and miss this part.
Additionally a check for available RTF format is carried out using IDataObject.GetFormats(Boolean)[2] method. It gets a list of all formats that data stored in this instance is associated with or can be converted to.
private void richTextBox_DragEnter(object sender, DragEventArgs e)
  {
     e.Effect = DragDropEffects.Copy;
     String[] supportedFormats = e.Data.GetFormats(true);

     if (supportedFormats != null)
     {
        List<string> sfList = new List<string>(supportedFormats);
        //--- checked for Rtf
        if (sfList.Contains(DataFormats.Rtf.ToString()))
           richTextBox.EnableAutoDragDrop = true;
        else if (sfList.Contains(DataFormats.Html.ToString()))
           richTextBox.EnableAutoDragDrop = false;
     }
  }
In case when data are associated with DataFormats.Rtf we set RichTextBox property EnableAutoDragDrop to true and do nothing in DragDrop event handler. It works very well as converting text to rtf format and more important it works when merging 2 RTF texts (plays text on a specific position in already filled RichTextBox). This functionality is handled very well from MS and I was happy to discover this. For HTML formatted text the case is not the same. Auto drag and drop insert them as plain text. This is not what we desire. We would like to have links, e-mails, images and tables to be shown in the similar and well formatted way. That's in this case EnableAutoDragDrop property is set to false.
4. Drop event: Simply do nothing if data has RTF format association. Leave the Auto Drag Drop functionality to work. For HTML format associated data I found a sample solution for HTML 2 RTF conversion[3], that is not perfect but works in my case. You have to manually delete some part of the imported text.
private void richTextBox_DragDrop(object sender, DragEventArgs e)
   {
       String[] supportedFormats = e.Data.GetFormats(true);
       string DataStr = string.Empty;
       if (supportedFormats != null)
       {
           List<string> sfList = new List<string>(supportedFormats);
          if (sfList.Contains(DataFormats.Html.ToString()))
          {
               WebBrowser webBrowser = new WebBrowser();
               webBrowser.CreateControl(); // only if needed
               DataStr = (string)e.Data.GetData(DataFormats.Html, true);
               webBrowser.DocumentText = DataStr;
               while (webBrowser.DocumentText != DataStr)
                   Application.DoEvents();
               webBrowser.Document.ExecCommand("SelectAll", false, null);
               webBrowser.Document.ExecCommand("Copy", false, null);
               richTextBox.Paste();
          }
       }
   }
5. Final step is to create an event handler for LinkClicked event to process a link that has been clicked within the control.
private void richTextBox_LinkClicked(object sender, LinkClickedEventArgs e)
 {
     System.Diagnostics.Process.Start(e.LinkText);
 }

Phraseology

  • DragDrop Event - The DragDrop event occurs when the user completes a drag-and-drop operation by dragging an object over the control and then dropping it onto the control by releasing the mouse button.
  • DragEventArgs - A DragEventArgs object specifies any data associated with this event; the current state of the SHIFT, CTRL, and ALT keys; the location of the mouse pointer; and the drag-and-drop effects allowed by the source and target of the drag event.
  • Originator - source of the drag event.

Excample

An example C# solution and more detail you can find on my personal "WordPress" installation at blog.iordanov.info. Very interesting post you can find on this site


References