0

I have an Excel file with 3 worksheets, the third is called "DataArea". This worksheet has a empty table called "Table1" with 2 columns "Column1" and "Column2". I already retrieve the data from my SQL server database, so i have 2 columns in a dataset. My scope is to insert the information contained in my dataset to that table in excel, in need to do this because because the table is the source of a graphic in the second sheet.

Is there a way to do that?

Thanks in advance.

Giuseppe Lolli
  • 167
  • 4
  • 15
  • The *easy* way is to create a query in Excel and just refresh the data. – Panagiotis Kanavos Feb 07 '20 at 10:40
  • I know that, but i need to do that using powershell.. i'm asking here because of that. I was trying but is like i cannot retrieve the table names in a sheet... and i don't know how to fill the table. – Giuseppe Lolli Feb 07 '20 at 10:44
  • *"but i need to do that using powershel"* Why? you don't even mention the powershell requirement in your question. All you ask is *"Is there a way to do that?"*, and @PanagiotisKanavos responds to that question. if you need to use specific tools/languages to perform the task, tell us that (in the question), explain why, and show us the code you've written and explain why it isn't working. – Thom A Feb 07 '20 at 10:48
  • In the title of my question there is "using powershell" – Giuseppe Lolli Feb 07 '20 at 10:51
  • @GiuseppeLolli **why**? Excel can already do this, in fact it ... excels in data loading and transformation. That's how Pivot tables and pivot charts work since the 90s. The data is loaded from a query directly into a sheet, a Pivot table loads the data from that sheet and a Pivot chart displays what the pivot tables shows. There's even a wizard for this. When the users wants to load new data, they hit F9 to reload everything – Panagiotis Kanavos Feb 07 '20 at 11:00
  • A proper answer to `why` would be eg `the users don't have access to the database`. Since you use SQL Server, you could use the `Export` wizard to create an SSIS package that exports data from a query to an Excel destination. In C#, you could use a library like Epplus to generate a rich Excel file. – Panagiotis Kanavos Feb 07 '20 at 11:08
  • The [ImportExcel](https://github.com/dfinke/ImportExcel) Powershell module uses Epplus behind the scenes to import/export Excel data, generate pivot tables and charts. One of the commands, [Send-SQLDataToExcel.md](https://github.com/dfinke/ImportExcel/blob/master/mdHelp/en/Send-SQLDataToExcel.md), loads data from a database and writes it out to Excel. Check [Introducing the PowerShell Excel Module](https://devblogs.microsoft.com/scripting/introducing-the-powershell-excel-module-2/) for a short intro – Panagiotis Kanavos Feb 07 '20 at 11:12

1 Answers1

1

Take this one, see comments:

Add-Type -AssemblyName Microsoft.Office.Interop.Excel


# create dummy datatable with 2 colums
$dt = New-Object System.Data.DataTable
[void]$dt.Columns.Add('Column1',[string]::empty.GetType() )
[void]$dt.Columns.Add('Column2',[string]::empty.GetType() )

#fill datatable
for( $i = 0; $i -lt 10; $i++ ) {

    $row          = $dt.NewRow()
    $row.Column1  = 'Hello' + $i.ToString()
    $row.Column2  = 'World' + $i.ToString()
    [void]$dt.Rows.Add( $row )
}

# to move a datatable to excel, it must be an n-dimensional array of objects
# move datatable to object-array:

$excelArray = New-Object 'object[,]' $dt.Rows.Count, $dt.Columns.Count

for( $i = 0; $i -lt $dt.Rows.Count; $i++ ) {
    $excelArray[$i, 0] = $dt.Rows[$i].Column1
    $excelArray[$i, 1] = $dt.Rows[$i].Column2
}

# open excel file
$excelFile = 'D:\test\emptySheet.xlsx' 

$excel                = New-Object -ComObject Excel.Application
$excel.Visible        = $true
$excel.ScreenUpdating = $true

$workbook  = $excel.Workbooks.Open( $excelFile ,$null, $false )

$ws        = $workbook.WorkSheets.item(1) 

[void]$ws.Activate()

# select range
$range = $ws.Range('A1', ([char](64 + $dt.Columns.Count)).ToString() + ($dt.Rows.Count).ToString() ) 

# copy aray to excel range
$range.Value2 = $excelArray

# save and cleanup
[void]$workbook.Save()
[void]$workbook.Close()
[void]$excel.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) | Out-Null
f6a4
  • 1,684
  • 1
  • 10
  • 13
  • This is holy nice! I just realize that powershell cannot retrieve the name of the Tables created in a worksheet for some reason. However this helps me a lot! Thanks! – Giuseppe Lolli Feb 07 '20 at 13:12