Quick Excel generation

I am building a categorized todo list (not as a feature or anything, just a quick, off-the cuff list). The list should be organized like this:

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

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

If this were a database problem, I'd do a cartesian join, and the result would be pretty close to what I'm after.

开发者_开发知识库What I want to do is take these 2 files and spew out an Excel file where the first column is the category, and the second is the items.

I started writing a little C# program to brute force this, but it just seems like I must be missing something -- is there a macro or maybe even a pithy PowerShell script that I could throw together to do this? It just seems like it should be so easy.

Try this:

 $(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                                                                                                                       

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

To read/write from excel spreadsheet using C#

Regards, Andy.

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

All i did was use the libraries that .NET has, also to construct the Excel Book, i started recording a macro of how i'll want it, then it was just matter of appliying that macro to my VB program.

This answer may not help you all, i just hope it points you in the right direction, just let me find an example, and i'll gladlly share it

EDTI I found a copule of links, i hope they work

MSDN forums

CodeProject.com

Here is a way to get it into Excel. I think you want dashes though rather than repeating the category name. 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

This is self-contained and shows a couple techniques I've found helpful. You'll be very disappointed with Excel's speed, but my users enjoy seeing pretty things on their screen, instead of .csv's.

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)

}