Excel fast generation

Question

I think I work too much to help, help me ...

I am creating a categorized todo list (and not as a function or something else, just quick, out of the cuff).
The list should be organized as follows:

Category1 -----------Item1 -----------Item2 Category2 -----------Item1 (always the same items in each category) -----------Item2 

So, I have 2 text files, 1 with categories and one with elements.

If this were a database problem, I would make a Cartesian connection, and the result would be very close to what I need.

What I want to do is take these 2 files and pop out the Excel file, where the first column is the category and the second is the elements.

I started writing a small C # program to overdo it, but it seems to me that something is missing - is there a macro or maybe even a powerful powershell script that I could put together to do this? It seems to be so simple ...

Answer 1

If you are really looking for a quick and dirty solution, you can quickly write VB / C # code to combine the two files into one CSV and open them in Excel to continue your task. If you need more file manipulation on the fly, I recommend that you read the link posted here.

To read / write from an Excel spreadsheet using C #

Regards, Andy.

Answer 2

Try the following:

  $(foreach($c in cat .\categories.txt) { foreach ($i in cat .\items.txt) { New-Object PSObject -Property @{ Category = $c Item = $i } } }) | Format-Table -GroupBy Category -Property Item Category: category1 Item ---- item1 item2 item3 item4 Category: category2 Item ---- item1 item2 item3 item4 

Answer 3

I don't know about C #, but I did something like VB.

All I did was use the libraries that are in .NET to create an Excel workbook as well, I started to record a macro of how I want it, then it's just a matter of applying this macro to my VB program.

This answer may not help all of you, I just hope that it will point you in the right direction, just let me find an example, and I will share it with pleasure.

EDTI I found a bunch of links, hope they work

MSDN Forums

CodeProject.com

Answer 4

Here is a way to get it in Excel. I think you want a dash, but instead of repeating the name of the category. Right?

 $(foreach($c in cat .\categories.txt) { foreach ($i in cat .\items.txt) { New-Object PSObject -Property @{ Category = $c Item = $i } } }) | select Category, Item | Export-Csv -NoTypeInformation $pwd\test.csv; Invoke-Item $pwd\test.csv 

Answer 5

This is self-sufficient and shows several methods that I found useful. You will be very disappointed with the speed of Excel, but my users like to see beautiful things on the screen, not CSV.

 Param ( [parameter(Mandatory=$false)] [string]$targetFile = 'D:\cafp1\middleware\restricted\middleware\IIS\PowerShell\Demos\excel\test.xls' ) begin { Set-StrictMode -version Latest function configureSheet { param ( [parameter(Mandatory=$true)] $appExcel, [parameter(Mandatory=$true)] $appWorkbook, [parameter(Mandatory=$true)] $worksheetName ) # Get the appropriate sheet $sheetNumber = 1 $headerSheet = $appWorkbook.WorkSheets.Item($sheetNumber) $headerSheet.Activate() $headerSheet.Name = $worksheetName # Place the intro text $introRowNumber = 1 $introColumnNumber = 1 $headerSheet.Cells.Item($introRowNumber,$introColumnNumber) = "Intro Text" # Freeze panes for easy navigation $freezeCell = "a2" [void]$headerSheet.Range($freezeCell).Select() $headerSheet.application.activewindow.FreezePanes = $true # Configure headers $headerSheet.Cells.Item(2,1) = "Header 1" $headerSheet.Columns.Item(1).ColumnWidth = 15 $headerSheet.Cells.Item(2,2) = "Header 2" $headerSheet.Columns.Item(2).ColumnWidth = 25 $headerTitles = $headerSheet.UsedRange $headerTitles.Interior.ColorIndex = 40 $headerTitles.Font.ColorIndex = 9 $headerTitles.Font.Bold = $True $firstDataRow = 3 [void]$headerSheet.Cells.Item($firstDataRow,1).Activate() $headerSheet } function reportObject { param ( [parameter(Mandatory=$true)] $sheet, [parameter(Mandatory=$true)] $activeRowNumber, [parameter(Mandatory=$true)] $variant ) # I took out a lot of logic to allow complex objects to be reported. This is easy to extend, though. $sheet.Cells.Item($activeRowNumber,1) = $variant.Category $sheet.Cells.Item($activeRowNumber,2) = $variant.Item } $appExcel = New-Object -comObject Excel.Application $appExcel.visible = $true $appExcel.ScreenUpdating = $true $appWorkbook = $appExcel.Workbooks.Add() $originalCalculationState = $appExcel.Calculation $appExcel.Calculation = -4135 # Magic number to disable calculation $appWorkbook.Title = "Title in Properties" $appWorkbook.Subject = "Subject in Properties" $currentSheet = "WorkSheet Name" $sheet = $false } process { $sheet = configureSheet ` -appExcel $appExcel ` -appWorkbook $appWorkbook ` -worksheetName $currentSheet $activeRow = 3 $record = New-Object PSObject add-member -InputObject $record Noteproperty 'Category' "Category1" add-member -InputObject $record Noteproperty 'Item' "" reportObject ` -sheet $sheet ` -activeRowNumber $activeRow ` -variant $record } end { # Save and close this workbook $headerSheet = $appWorkbook.WorkSheets.Item(1) $row = 3 $column = 1 [void]$headerSheet.Cells.Item($row,$column).Select() [void]$appExcel.Selection.AutoFilter() [void]$headerSheet.Cells.Item($row,$column).Activate() $appExcel.Calculation = $originalCalculationState # Excel defaults to My Documents as the home folder $appExcel.DisplayAlerts = $false $appWorkbook.SaveAs($targetFile,1) $appWorkbook.Close() # Do this to get PowerShell Console to really kill the Excel object. # PowerShell ISE will only really kill Excel once it itself is killed. [void][System.Runtime.Interopservices.Marshal]::ReleaseComObject($appExcel) }