Solved

Powershell with excel.

Posted on 2013-06-10
9
536 Views
Last Modified: 2013-06-26
Looking to find a way to have powershell  take everything in a Excel sheet and make it a table, with a table style of green. Any idea?
0
Comment
Question by:Xytras1
  • 3
  • 3
  • 2
9 Comments
 
LVL 29

Expert Comment

by:Randy Downs
Comment Utility
Maybe this will help. It imports Excel into a SQL table

http://poshcode.org/1098

#Change these settings as needed
$filepath = 'C:\Users\u00\Documents\backupset.xlsx'
#Comment/Uncomment connection string based on version
#Connection String for Excel 2007:
$connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=`"$filepath`";Extended Properties=`"Excel 12.0 Xml;HDR=YES`";"
#Connection String for Excel 2003:
#$connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=`"$filepath`";Extended Properties=`"Excel 8.0;HDR=Yes;IMEX=1`";"
$qry = 'select * from [backupset$]'
$sqlserver = 'Z002\SQLEXPRESS'
$dbname = 'SQLPSX'
#Create a table in destination database with the with referenced columns and table name.
$tblname = 'ExcelData_fill'
 
#######################
function Get-ExcelData
{
 
    param($connString, $qry='select * from [sheet1$]')
 
    $conn = new-object System.Data.OleDb.OleDbConnection($connString)
    $conn.open()
    $cmd = new-object System.Data.OleDb.OleDbCommand($qry,$conn) 
    $da = new-object System.Data.OleDb.OleDbDataAdapter($cmd) 
    $dt = new-object System.Data.dataTable 
    [void]$da.fill($dt)
    $conn.close()
    $dt
 
} #Get-ExcelData
 
#######################
function Write-DataTableToDatabase
{ 
    param($dt,$destServer,$destDb,$destTbl)

    $connectionString = "Data Source=$destServer;Integrated Security=true;Initial Catalog=$destdb;"
    $bulkCopy = new-object ("Data.SqlClient.SqlBulkCopy") $connectionString
    $bulkCopy.DestinationTableName = "$destTbl"
    $bulkCopy.WriteToServer($dt)
 
}# Write-DataTableToDatabase

#######################
$dt = Get-ExcelData $connString $qry
Write-DataTableToDatabase $dt $sqlserver $dbname $tblname

Open in new window

0
 
LVL 68

Expert Comment

by:Qlemo
Comment Utility
Your wording is ambiguous - do you want to
1. take some data from somewhere, and put it into Excel
2. take some Excel data, and want to format it as e.g. a HTML table?
0
 

Author Comment

by:Xytras1
Comment Utility
I have data coming into Excel and I would like that data to be in a Table with a green style. Not sure if its possible .
0
 
LVL 29

Accepted Solution

by:
Randy Downs earned 250 total points
Comment Utility
Maybe this will work for you -
http://learn-powershell.net/2012/12/20/powershell-and-excel-adding-some-formatting-to-your-report/

I will now take the report that I created and make more pleasant to the eyes and really show why this is different from just piping the output into Export-CSV to a CSV file.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 68

Assisted Solution

by:Qlemo
Qlemo earned 250 total points
Comment Utility
Can you show us a screenshot of the Excel sheet, or the Excel sheet itself, as it should look alike?
BTW, using PS to just color your Excel table is somewhat oversized.
0
 

Author Comment

by:Xytras1
Comment Utility
OK where is what i would like the table to look like.
Capture.JPG
0
 

Author Comment

by:Xytras1
Comment Utility
Found it. Thanks for all the help.

Puts everything in WS to a Table.

#Create Table
$TableRange = $ws.Range("A1","H300")
$ws.ListObjects.Add([Microsoft.Office.Interop.Excel.XlListObjectSourceType]::xlSrcRange, $xl.ActiveCell.CurrentRegion, $null, [Microsoft.Office.Interop.Excel.XlYesNoGuess]::xlYes).Name = "Table2"
$ws.ListObjects.Item("Table2").TableStyle = "TableStyleMedium2"
$TableRange.EntireColumn.Autofit()
0
 
LVL 68

Expert Comment

by:Qlemo
Comment Utility
I think you should just do that in Excel VBA (as a macro).
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

The article will show you how you can maintain a simple logfile of all Startup and Shutdown events on Windows servers and desktops with PowerShell. The script can be easily adapted into doing more like gracefully silencing/updating your monitoring s…
This script checks a path to see if a folder exists. If the folder does exist you will get output "The folder has previously been created. No action taken" If not it will create the folder. Then adds one user modify permission to the folder. It …
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now