?
Solved

Powershell with excel.

Posted on 2013-06-10
9
Medium Priority
?
629 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
8 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 71

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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 30

Accepted Solution

by:
Randy Downs earned 750 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 71

Assisted Solution

by:Qlemo
Qlemo earned 750 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 71

Expert Comment

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

Featured Post

A Cyber Security RX to Protect Your Organization

Join us on December 13th for a webinar to learn how medical providers can defend against malware with a cyber security "Rx" that supports a healthy technology adoption plan for every healthcare organization.

Question has a verified solution.

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

My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

830 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