CODEWITHSUNDEEP
powershell
Varghese
Varghese

Reputation: 21

How to Format the csv file so that on opening in excel, data should be displayed in a formatted manner using powershell scripting

we have a Csv file containing details of all logged users. Currently we are displaying the required details as table in the mail body and whole list as attachment and but we have to open the excel and do manual formatting to see the whole details attachment.

Is there anyway to sort this issue so that we can see the formatted excel itself on opening the attachment from mail.

Any help is really appreciated!!!

Thanks gv

Upvotes: 2

Views: 1815

Answers (3)

Deadly-Bagel
Deadly-Bagel

Reputation: 1620

You could check out the ImportExcel module, I use this to generate a report on a remote server where Excel is not installed. It conveniently does most of the formatting for you, there are a few issues but you can return an OpenOfficeXML object which is relatively easy to work with, when you know where everything is and get used to the quirks (like stuff that applies to the top row doesn't account for adding a title, and that indexing starts from 1 instead of 0).

An example of a spreadsheet with two worksheets and the sort of stuff you can do:

$data | Export-Excel -WorkSheetname "MyData" -Title $atitle -TitleSize 20 -Path "$report\Data.xlsx" -PassThru
$xl = $data2 | Export-Excel -WorkSheetname "MyData2" -Title $btitle -TitleSize 20 -Path "$report\Data.xlsx" -PassThru

$ws = $xl.Workbook.Worksheets

 1..($ws.Count) | Foreach-Object {
    Foreach ($col in 2..($ws[$_].Dimension.Columns))
    {
        $ws[$_].Column($col).Style.HorizontalAlignment = "Center"    # Align centre except first column
    }

    $ws[$_].Cells["A2:H2"].AutoFilter = $true    # Set autofilter on headers
    $ws[$_].Cells["A1:H2"].Style.Font.Bold = $true    # Bold title and headers
    $ws[$_].Row(2).Height = 40    # Increase height of header row
    $ws[$_].Row(2).Style.VerticalAlignment = "Center"    # Center header row
    $ws[$_].Row(2).Style.Border.Bottom.Style = "Thin"    # Underline header row
    $ws[$_].Cells["B3:C" + ($ws[$_].Dimension.Rows).ToString()].Style.NumberFormat.Format = "0.0%;[Red]-0.0%"    # Format activity columns as percentages
    $ws[$_].View.FreezePanes(3, ($ws.Dimensions.Columns))    # Freeze top two rows
    $ws[$_].Cells["A1:H1"].Merge = $true
    $ws[$_].Cells["A2:H" + ($ws[$_].Dimension.Rows).ToString()].AutoFitColumns()    # Autofit columns excluding header

    Foreach ($col in 1..($ws[$_].Dimension.Columns))
    {
        $ws[$_].Column($col).Width = $ws[$_].Column($col).Width + 2    # Bump up column width as autosize seems to underestimate
    }
}

$xl.Save()

Upvotes: 1

TechSpud
TechSpud

Reputation: 3528

You can't presereve formatting in a csv - csv is flat file with nothing except data.

If you have Excel installed on the machine thats producing the extract, the code below will open your csv, add some formatting and save as an xlsx.

# create an Excel com object
$Excel = New-Object -ComObject Excel.Application
# set it to visible (not needed, but it means we can see what's happening)
$Excel.Visible = $True

# open our csv file
$Workbook = $Excel.Workbooks.Open('C:\Path\To\File.csv')

# get a handle to the worksheet
$Sheet = $Workbook.ActiveSheet

# set the first row to a bold, size 10 font
$Sheet.Rows.Item(1).Font.Size = 10
$Sheet.Rows.Item(1).Font.Bold = $true

# add an autofilter
$Sheet.UsedRange.AutoFilter()

# make the columns autofit
$Sheet.UsedRange.EntireColumn.AutoFit()

# select the 2nd row
$Sheet.Rows.Item(2).Select()

# freeze the first row
$Excel.ActiveWindow.FreezePanes = $true

# save the csv as an excel doc, so we keep our formatting
$Workbook.SaveAs('C:\Path\To\File.xlsx',51)

# clos the workbook
$Workbook.Close()

Otherwise, you could try PSExcel, or EPPlus.

Upvotes: 0

Anton Krouglov
Anton Krouglov

Reputation: 3399

As indicated here the easiest way to produce formatted output is convert to HTML. Excel in turn can easily read html.

For instance this code creates list of processes and exports it excel/html:

get-process | ConvertTo-Html | out-file c:\result.xls

Upvotes: 0

Related Questions