Solved

Powershell with excel.

Posted on 2013-06-10
9
583 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
9 Comments
 
LVL 30

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 70

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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 30

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
 
LVL 70

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 70

Expert Comment

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

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
A project that enables an administrator to perform actions within a user session context not just at the time of login but any time later on day(s) or week(s) later.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

738 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