Solved

Powershell with excel.

Posted on 2013-06-10
9
546 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
ID: 39235854
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
ID: 39235954
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
ID: 39236064
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
ID: 39236229
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
Does Powershell have you tied up in knots?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

 
LVL 68

Assisted Solution

by:Qlemo
Qlemo earned 250 total points
ID: 39236299
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
ID: 39237989
OK where is what i would like the table to look like.
Capture.JPG
0
 

Author Comment

by:Xytras1
ID: 39238265
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
ID: 39238266
I think you should just do that in Excel VBA (as a macro).
0

Featured Post

Does Powershell have you tied up in knots?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This script can help you clean up your user profile database by comparing profiles to Active Directory users in a particular OU, and removing the profiles that don't match.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

864 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

25 Experts available now in Live!

Get 1:1 Help Now