Adding or updating tables represents a common task when working on Wiki articles. Often, the source data for a table already exists in form of a, for example, Microsoft Excel spreadsheet.

Getting the source date from an Excel spreadsheet can become a tedious task; especially, if "copy & paste" is the only method you have to get your Excel data into your Wiki article. You can significantly simplify this task by using a script.

The objective of this article is to show you how easy it is to write a script that extracts the source data from an Excel spreadsheet and turns it into a HTML table fragment you can include into your Wiki article.

 

 


Reading Excel Data Using a Script

If you have Excel installed on your computer, you also have access to an interface called "Excel.Application" that represents the entire Microsoft Excel application.
This interface enables you to automate Excel on your computer by using scripts.

The following PowerShell code snippet outlines, how you can read an Excel spreadsheet and create a HTML string that contains the spreadsheet data:

001
002
003
004
005
006
007
008
009
010
011
012
013
014
015
016
017
018
019
020
021
022
023
024
025
026
027
028
029
030
031
032
033
034
035
036
037
038
039
040
041
042
043
044
045
046
047
048
049
050
051
$fileName = "c:\temp\1.xlsx"
$excel = New-Object -com "Excel.Application"
$excel.Visible = $false

$workbook = $excel.workbooks.open($fileName)
$sheet = $workbook.ActiveSheet
$sheetName = $sheet.Name
$maxRows = $sheet.UsedRange.Rows.Count 
$maxCols = $sheet.UsedRange.Columns.Count

$sHtml = "<table>"
For($i = 1; $i -le $maxRows; $i++)
{
   $sHtml += "<tr>"
   For($j = 1; $j -le $maxCols; $j++)
   {
      Write-Progress `
         -Activity        "Processing rows" `
         -Status          "Processing row $i of $maxRows" `
         -PercentComplete (($i / $maxRows) * 100)
   

      If($i -eq 1)
      {
         $sHtml += "<th>" + $sheet.Cells.Item.Invoke($i, $j).Value2 + "</th>"
      }
      Else
      {
         $sHtml += "<td>" + $sheet.Cells.Item.Invoke($i, $j).Value2 + "</td>"
      }
   }
   $sHtml += "</tr>"
}
$sHtml += "</table>"

$workbook.Close()
$excel.Quit()

$null = [Reflection.Assembly]::LoadWithPartialName("System.Windows.Forms") 
$dataObject = New-Object windows.forms.dataobject 
$dataObject.SetData([Windows.Forms.DataFormats]::UnicodeText, $true, $sHtml) 
[Windows.Forms.Clipboard]::SetDataObject($dataObject, $true)

Write-Host "Command completed successfully"

Trap 
 { 
    Write-Host "$($_.Exception.Message)
`r`nn"
 -foregroundcolor white -backgroundcolor darkred
    Exit 1
 }

 

The gist of this script is very simple. A table consists of rows and columns. If you can determine the boundaries of a spreadsheet, you can use the information to read the data that is stored in it. In the code snippet above, the boundaries of the spreadsheet are defined by "$sheet.UsedRange.Rows.Count" and "$sheet.UsedRange.Columns.Count".

Using the boundary information, you can read the data in your spreadsheet using two loops:

001
002
003
004
005
006
007
008
009
010
011
012
013
014
015
016
017
018
$sHtml = "<table>"
For($i = 1; $i -le $maxRows; $i++)
{
   $sHtml += "<tr>"
   For($j = 1; $j -le $maxCols; $j++)
   {
      If($i -eq 1)
      {
         $sHtml += "<th>" + $sheet.Cells.Item.Invoke($i, $j).Value2 + "</th>"
      }
      Else
      {
         $sHtml += "<td>" + $sheet.Cells.Item.Invoke($i, $j).Value2 + "</td>"
      }
   }
   $sHtml += "</tr>"
}
$sHtml += "</table>"

 

When you are done reading the table data, you can make the resulting HTML code snippet accessible by placing it into your clipboard. The following code snippet shows an example for this:

001
002
003
004
$null = [Reflection.Assembly]::LoadWithPartialName("System.Windows.Forms") 
$dataObject = New-Object windows.forms.dataobject 
$dataObject.SetData([Windows.Forms.DataFormats]::UnicodeText, $true, $sHtml) 
[Windows.Forms.Clipboard]::SetDataObject($dataObject, $true)

 

Running the PowerShell script code creates an HTML representation of your Excel table. You can paste it into your Wiki article in the HTML mode of the Wiki editor. 

 Note
By using the dataobject, you also create a HTML representation of your table in your clipboard.
However, this method requires additional script code, which is outside of the scope of this article.

As an alternative to PowerShell, you can also use JavaScript as script language for this task. The following code snippet shows an example for this:

001
002
003
004
005
006
007
008
009
010
011
012
013
014
015
016
017
018
019
020
021
022
023
024
025
026
027
028
029
030
031
032
033
var oExcel = new ActiveXObject("Excel.Application");
oExcel.Visible = false;
var oWorkBook  = oExcel.Workbooks.Open("c:\temp\1.xlsx");
var oSheet     = oWorkBook.ActiveSheet;
     
gColCount = oSheet.UsedRange.Columns.Count;
gRowCount = oSheet.UsedRange.Rows.Count;

var sHtml = "<table id=\"oTable\">";
for(var i = 1; i < gRowCount + 1; i++)
{
   sHtml += "<tr>"
   for(var j = 1; j < gColCount + 1; j++)
   {
      document.getElementById("oStatus").innerText = "Processing row " + i + " of " + gRowCount;
      if(i == 1)
      {
         sHtml += "<th>" + oSheet.Cells(i, j).Value + "</th>";
      }
      else
      {
         sHtml += "<td>" + oSheet.Cells(i, j).Value + "</td>";
      }
   }
   sHtml += "</tr>";
}

sHtml += "</table>";
oSheet = null;
oWorkBook.Close();
oWorkBook = null;
oExcel.Quit();
oExcel = null;

↑ Back to top

 


Formatting Your Table Data

Scripting the creation of a table does not only provide you with a fast method to turn an Excel spreadsheet into a HTML table, it also enables you to further format the table outline. For example, if your table consists of a large number of rows, you can improve the readability of the table data by alternating the table row colors using inline styles. The following screenshot shows an example for this:




In your script code, you can implement this feature using the following code snippet:

001
002
003
004
005
006
007
008
009
010
011
012
013
014
015
016
017
018
019
020
021
022
023
024
025
026
var sHtml = "<table id=\"oTable\">";
for(var i = 1; i < gRowCount + 1; i++)
{
   sHtml += "<tr>"
   for(var j = 1; j < gColCount + 1; j++)
   {
      document.getElementById("oStatus").innerText = "Processing row " + i + " of " + gRowCount;
      if(i == 1)
      {
         sHtml += "<th>" + oSheet.Cells(i, j).Value + "</th>";
      }
      else
      {
         if(i % 2 == 0)
         {
            sHtml += "<td style=\"padding-left:10px;padding-right:10px;\">" + oSheet.Cells(i, j).Value + "</td>";
         }
         else
         {
            sHtml += "<td style=\"background: beige; padding-right: 10px; padding-left: 10px;\" >" + oSheet.Cells(i, j).Value + "</td>";
         }
      }
   }
   sHtml += "</tr>";
}
sHtml += "</table>";

 
In addition to alternating the table row colors, you might also want to modify the following styles of table elements:

  • Text alignment
  • Cell padding
  • Cell spacing
  • Border styles  

One benefit of using a script to apply inline styles to HTML tags is that you can quickly modify a style in your script code if you want to.

↑ Back to top

 


Adding a User Interface to Your Script

Before you paste your transformed HTML table into your Wiki article, you might want to test the effect of certain style settings in form of a preview. In this case, it might be a good idea to extend your script with a user interface.

How to Add a Graphical User Interface to a PowerShell Script by Using HTML Applications shows a method to add a graphical user interface to a script. The Wiki Table Creator is an example for a script that has user interface wrapped around a script.

This script enables you to turn an Excel spreadsheet into a HTML table, to test offline certain formatting settings, to copy the table into your clipboard, and to paste your table in the Design mode of the Wiki editor into your article.

↑ Back to top

 


See Also

↑ Back to top