Hey, Scripting Guy! How Can I Write to Excel Without Using Excel?

ScriptingGuy1

Hey, Scripting Guy! Question

Hey, Scripting Guy! I need to be able to write information to an Excel file. I do not want to install Microsoft Office Excel on my server, but the spreadsheet format is a nice way to store information. I can then give it to my pointy-headed boss, and he can create pivot tables, charts, or whatever exactly it is he does all day. Basically, I think if I give him lots of data, he will leave me alone and let me do my job. Can you help?

– TW

SpacerHey, Scripting Guy! Answer

Hi PW,

You know the old saying about bosses? If you cannot dazzle them with brilliance, baffle them with, uh, data. Lots and lots and lots of data. So you want to write to Excel without installing Excel. This is similar to a question we got last week from a different scripter. He wanted to read from Excel. Well, I have good news and better news. The good news is we can use the same technique with ADO.NET to write to an Excel spreadsheet. The better news is I will show you how to perform this feat.

If we do not have Excel installed on the machine, we can use either classic COM ADO or we can use ADO.NET. We are going to use ADO.NET. Basically, we will need to create two objects. The first is the System. Data.OleDb.OleDbConnection object and the second one is the System.Data.OleDb.OleDbCommand object. When we have the two objects created, we will need to create a query. The query will use the Insert into syntax. We will need to specify parameters for each of the properties we wish to store, use theExecuteNonQuery method from the OleDbCommand object, and close our connection. All this takes place in the script seen here:

$strFileName = "C:\test\shares.xls"
$strProvider = "Provider=Microsoft.Jet.OLEDB.4.0"
$strDataSource = "Data Source ="+$strfilename
$strExtend = "Extended Properties=Excel 8.0"
$strQuery = "Insert into [Shares$] (Name,Path,Description,Type) Values (?,?,?,?)"
$objConn = new-object System.Data.OleDb.OleDbConnection( ` "$strProvider;$strDataSource;$strExtend")
$sqlCommand = new-object System.Data.OleDb.OleDbCommand($strQuery)
$sqlCommand.Connection = $objConn
$NameParam = $sqlCommand.parameters.add("Name","VarChar",80)
$PathParam = $sqlCommand.parameters.add("Path","VarChar",80)
$DescriptionParam = $sqlCommand.parameters.add("Description","VarChar",80)
$TypeParam = $sqlCommand.parameters.add("Type","UnsignedInt",16)
$objConn.open()

$shares = Get-WmiObject -Class Win32_share
ForEach($share in $Shares)
{
  $aReturn = $NameParam.value = $share.name
  $breturn = $PathParam.value = $share.path
  $cReturn = $DescriptionParam.value = $share.Description
  $dReturn = $TypeParam.value = $share.Type
  $returnValue = $sqlCommand.ExecuteNonQuery()
}
$objConn.close()

The first thing we need to do is to specify the path to our Excel spreadsheet. In this script we are not going to create an empty spreadsheet; we are going to rely upon one that has been previously created. The Excel spreadsheet will be used to store information about shares that are defined on a server. This figure illustrates the simple nature of the empty spreadsheet:

Empty spreadsheet graphic

 

The next thing we need to do is to specify the provider name. To talk to Excel, we need to use the Microsoft.Jet.OLEDB.4.0 provider. We will also need to specify the data source as the path to the spreadsheet and explicitly use the Extended Properties = Excel 8.0syntax for the extended properties of our connection string. This section of the script is seen here:

$strFileName = "C:\test\shares.xls"
$strProvider = "Provider=Microsoft.Jet.OLEDB.4.0"
$strDataSource = "Data Source ="+$strfilename
$strExtend = "Extended Properties=Excel 8.0"

Perhaps one of the more difficult things to figure out about this script was the Insert into syntax for the query. The Insert into itself was obvious enough, and based upon the script we wrote last week, putting the name of the spreadsheet into square brackets was no surprise. It was the rest of the silly thing that caused hours of trial and error on my part. After we specify the spreadsheet name, we need to list the columns we will be writing to-these go into parentheses. We will be supplying the values later, and we use question marks-one for each column we will be writing to. When we supply values for the question marks later, we will do so by position. This query syntax is peculiar to the OleDb providers. The Insert into query is proudly shown here:

$strQuery = "Insert into [Shares$] (Name,Path,Description,Type) Values (?,?,?,?)"

Once we have our query worked out, we need to create the connection object and the command object. When we create theOleDbConnection object, we specify the name of the provider, the data source, and the extended parameters. The command object receives the string that holds our Insert into query. After the two objects are created, we supply the connection object to the connection property of the command object. This section of the script is seen here:

$objConn = new-object System.Data.OleDb.OleDbConnection( ` "$strProvider;$strDataSource;$strExtend")
$sqlCommand = new-object System.Data.OleDb.OleDbCommand($strQuery)
$sqlCommand.Connection = $objConn

Now that we have created our two objects and associated them with one each other, we need to create the parameters. The parameters are used to supply the data to the Excel spreadsheet. We will feed the data via the value property of the parameters later. Remember that you want to keep these parameters in order as they will be taking the place of the question marks we used earlier in our query. There are three values that need to be specified when we create a parameter object: the name of the parameter, the data type of the parameter, and the size of the data you intend to store in it. Here is the code that creates the parameters:

$NameParam = $sqlCommand.parameters.add("Name","VarChar",80)
$PathParam = $sqlCommand.parameters.add("Path","VarChar",80)
$DescriptionParam = $sqlCommand.parameters.add("Description","VarChar",80)
$TypeParam = $sqlCommand.parameters.add("Type","UnsignedInt",16)

We now need to open the connection, perform our WMI query, loop through the WMI data, and assign the data to the parameters. At the end of each sequence, we need to use the ExecuteNonQuery to write the information to the spreadsheet. We continue until we have worked our way through all the data. When we are done, we close the connection. Here is this section of the script:

$objConn.open()

$shares = Get-WmiObject -Class Win32_share
ForEach($share in $Shares)
{
  $aReturn = $NameParam.value = $share.name
  $breturn = $PathParam.value = $share.path
  $cReturn = $DescriptionParam.value = $share.Description
  $dReturn = $TypeParam.value = $share.Type
  $returnValue = $sqlCommand.ExecuteNonQuery()
}

$objConn.close()

When we run the script, it writes the information about our shares to the spreadsheet as seen in this figure:

Empty spreadsheet graphic

 

So, PW, we have examined the mysteries of writing to an Excel spreadsheet when we do not even have Excel available. I hope this Excel-lent script will arm you with the tools you need to create prodigious amounts of data that can keep your boss entertained for hours on end. You could also try a ball of tin foil on a string. Until next time…

Ed Wilson and Craig Liebendorfer, Scripting Guys

0 comments

Discussion is closed.

Feedback usabilla icon