Splitting up an Excel file but preserve formatting

[jdenicola-pa]

I have an Excel file (which gets generated by a reporting system).

It looks something like this:

Customer Information
rows for customer 1
totals

Customer Information
rows for customer 2
totals

Customer Information
rows for customer 3
totals

and so on

The "Customer Information" is a literal string that begins each section for a new customer.

I need to split this file into multiple files so that each customer is in its own file.

I wrote some code to do this, to just iterate over the rows and find the start row of "Customer Information" and the row of the next "Customer Information" and pipe the range of $startingRow and $endingRow | Export-Excel, each to a different file.

But of course what gets lost is any formatting. While I can try to reproduce the formatting manually, it's relatively complex. Font size changes, font weight changes, column width and row height changes.

1. Is there a way to read these attributes from the Excel file? I know I can set them, but I didn't see a way to read them.

2. Another way I had thought of was to get the start/end of each customer, then for each customer, copy the Excel file, load it using Open-ExcelPackage and delete the rows before and after the rows that I want, then close it to save it. But can rows be deleted after loading with Open-ExcelPackage?

Any other ideas?

[dfinke]

Lot's of work to go after those details.

I didn't tackle it.

Here is just the font info for a single cell.

Does not include alignment, cell color, borders, etc, etc, etc.

$data = ConvertFrom-Csv @"
Region,State,Units,Price
West,Texas,927,923.71
North,Tennessee,466,770.67
East,Florida,520,458.68
East,Maine,828,661.24
West,Virginia,465,053.58
North,Missouri,436,235.67
South,Kansas,214,992.47
North,North Dakota,789,640.72
South,Delaware,712,508.55
"@

$xlfile = ".\spike.xlsx"

Remove-Item $xlfile -ErrorAction SilentlyContinue

$excel = $data | Export-Excel $xlfile -PassThru

Set-ExcelRange -Worksheet $excel.Sheet1 -Range "A1" -Bold -FontSize 18
Close-ExcelPackage $excel 

$excel = Open-ExcelPackage $xlfile

$excel.Sheet1.Cells["A1"].Style.Font

Close-ExcelPackage $excel

Name          : Calibri
Size          : 18
Family        : 0
Color         : OfficeOpenXml.Style.ExcelColor
Scheme        :
Bold          : True
Italic        : False
Strike        : False
UnderLine     : False
UnderLineType : None
VerticalAlign : None

[jdenicola-pa]

Thanks, that should get me close to what I need. As long as I can find the column width like that I should be good. I may have missed it, but I looked through the docs and examples for that and didn't catch it.

The sheet itself is relatively simple, but it has a lot of column width changes to get things to align.

[ninmonkey]

You can autosize columns with Export-Excel -AutoSize

If it's the same styles, you could save your xlsx as the base. Modify the data, Then copy it to a new path every time you export it.

[jdenicola-pa]

Unfortunately, AutoSize won't work. If you've ever seen an Excel export of a SQL Server Reporting Services report, it attempts to export it as nearly "pixel perfect" so the Excel sheet looks like the report in the viewer from a format perspective. To do this, it creates a series of columns of varying widths and merges cells across those columns to accomplish this. If it AutoSize'ed it, it would look like a jumbled mess.

(It is possible to design reports to avoid this to an extent, but these are reports from an ERP system and I can't change the format.)

What I was thinking of doing - querying and storing the formatting and cell merges - was getting very complex. In particular, the cell merges, which are ranges, and since I was trying to pull a subset of the rows out, I'd need to write code to figure out which merges applied to the rows I extracted, then adjust the ranges to the new row numbers in the new sheet.

I ended up going with my alternate approach, which is a bit brute force - I make a copy of the file, open it as a package, figure out the rows I want, and delete all the rows above and below it, then save it. I just do this in a loop until I get all the repeating sections I want out of the file. The files aren't that big so it goes pretty quick.

[dfinke]

Brute force FTW!

[dfinke]

I don't have docs for the Excel Object Model itself. You can search for VBA | EPPlus | set a breakpoint in your script and inspect the methods/properties by printing them $excel.Sheet1.Column(1) or using PowerShell's Get-Memeber

$excel.Sheet1.Column(1).Width