Confused about creating files and inserting data

[gdbarber]

In the version I have on my PC I create a worksheet and add data like the example below. I am struggling to create this properly with the newest ImportExcel module on a different PC. The samples I am seeing do not show how to add data to specific cells and add header rows. Am I overlooking some instructions somewhere. Thanks.

$xl = new-object -comobject excel.application $xl.DisplayAlerts = $False

Add an Excel file $wb = $xl.Workbooks.Add() Add a worksheet $ws = $wb.Worksheets.Item(1) Set the row counter $row = 1

Add column headers $ws.Cells.Item($row, 1) = "STORE" $ws.Cells.Item($row, 2) = "ITEM" $ws.Cells.Item($row, 3) = "ORDERED" $ws.Cells.Item($row, 4) = "DESCR" $ws.Cells.Item($row, 5) = "PO #" $ws.Cells.Item($row, 6) = "PICKUP" $ws.Cells.Item($row, 7) = "COUNT" $ws.Cells.Item($row, 8) = "TOTAL" $row++

Add line items foreach($LineItem in $ProcessData) { $ws.Cells.Item($row, 1) = $LineItem.Store $ws.Cells.Item($row, 2) = $LineItem.ProdNum $ws.Cells.Item($row, 3) = $LineItem.Qty $ws.Cells.Item($row, 4) = $LineItem.Descr $ws.Cells.Item($row, 5) = $PO $ws.Cells.Item($row, 6) = $PickupDate $row++ }

[dfinke]

$ProcessData=@"
Store,ProdNum,Qty,Descr
002,013,100,'Item 1'
002,014,200,'Item 2'
"@ | ConvertFrom-Csv

$PO='0001'
$PickupDate=Get-Date

$(foreach($LineItem in $ProcessData) {
    [PSCustomObject]@{
        STORE=$LineItem.Store
        ITEM=$LineItem.ProdNum
        ORDERED=$LineItem.Qty
        DESCR=$LineItem.Descr
        'PO #'=$PO
        PICKUP=$PickupDate
    }
}) | Export-Excel -Now

[gdbarber]

Thank you. Can this do subtotals? This was the way I did it with the comobject. $ws.UsedRange.Subtotal(1,-4157,(3),$true,$False,$true)

[dfinke]

You can use the -PassThru parameter to get the underlying Excel object and then do what you need. You'll need to save the workbook in your script thought.

[gdbarber]

Thanks!

[dfinke]

Doesn't look like the object returned by -PassThru has the UsedRange. Not sure if it supports the SubTotal. You can inject excel formulas though. You probably need to calc the range yourself.

   TypeName: OfficeOpenXml.ExcelWorksheet

Name                  MemberType Definition                                                                            
----                  ---------- ----------                                                                            
Column                Method     OfficeOpenXml.ExcelColumn Column(int col)                                             
DeleteColumn          Method     void DeleteColumn(int column), void DeleteColumn(int columnFrom, int columns)         
DeleteRow             Method     void DeleteRow(int row), void DeleteRow(int rowFrom, int rows), void DeleteRow(int ...
Dispose               Method     void Dispose(), void IDisposable.Dispose()                                            
Equals                Method     bool Equals(OfficeOpenXml.ExcelWorksheet x, OfficeOpenXml.ExcelWorksheet y), bool E...
GetHashCode           Method     int GetHashCode(OfficeOpenXml.ExcelWorksheet obj), int GetHashCode(), int IEquality...
GetMergeCellId        Method     int GetMergeCellId(int row, int column)                                               
GetType               Method     type GetType()                                                                        
GetValue              Method     System.Object GetValue(int Row, int Column), T GetValue[T](int Row, int Column)       
InsertColumn          Method     void InsertColumn(int columnFrom, int columns), void InsertColumn(int columnFrom, i...
InsertRow             Method     void InsertRow(int rowFrom, int rows), void InsertRow(int rowFrom, int rows, int co...
Row                   Method     OfficeOpenXml.ExcelRow Row(int row)                                                   
Select                Method     void Select(), void Select(string Address), void Select(string Address, bool Select...
SetValue              Method     void SetValue(int Row, int Column, System.Object Value), void SetValue(string Addre...
ToString              Method     string ToString()                                                                     
AutoFilterAddress     Property   OfficeOpenXml.ExcelAddressBase AutoFilterAddress {get;}                               
BackgroundImage       Property   OfficeOpenXml.ExcelBackgroundImage BackgroundImage {get;}                             
Cells                 Property   OfficeOpenXml.ExcelRange Cells {get;}                                                 
CodeModule            Property   OfficeOpenXml.VBA.ExcelVBAModule CodeModule {get;}                                    
Comments              Property   OfficeOpenXml.ExcelCommentCollection Comments {get;}                                  
ConditionalFormatting Property   OfficeOpenXml.ConditionalFormatting.ExcelConditionalFormattingCollection Conditiona...
DataValidations       Property   OfficeOpenXml.DataValidation.ExcelDataValidationCollection DataValidations {get;}     
DefaultColWidth       Property   double DefaultColWidth {get;set;}                                                     
DefaultRowHeight      Property   double DefaultRowHeight {get;set;}                                                    
Dimension             Property   OfficeOpenXml.ExcelAddressBase Dimension {get;}                                       
Drawings              Property   OfficeOpenXml.Drawing.ExcelDrawings Drawings {get;}                                   
HeaderFooter          Property   OfficeOpenXml.ExcelHeaderFooter HeaderFooter {get;}                                   
Hidden                Property   OfficeOpenXml.eWorkSheetHidden Hidden {get;set;}                                      
Index                 Property   int Index {get;}                                                                      
MergedCells           Property   OfficeOpenXml.ExcelWorksheet+MergeCellsCollection MergedCells {get;}                  
Name                  Property   string Name {get;set;}                                                                
Names                 Property   OfficeOpenXml.ExcelNamedRangeCollection Names {get;}                                  
OutLineApplyStyle     Property   bool OutLineApplyStyle {get;set;}                                                     
OutLineSummaryBelow   Property   bool OutLineSummaryBelow {get;set;}                                                   
OutLineSummaryRight   Property   bool OutLineSummaryRight {get;set;}                                                   
PivotTables           Property   OfficeOpenXml.Table.PivotTable.ExcelPivotTableCollection PivotTables {get;}           
PrinterSettings       Property   OfficeOpenXml.ExcelPrinterSettings PrinterSettings {get;}                             
ProtectedRanges       Property   OfficeOpenXml.ExcelProtectedRangeCollection ProtectedRanges {get;}                    
Protection            Property   OfficeOpenXml.ExcelSheetProtection Protection {get;}                                  
SelectedRange         Property   OfficeOpenXml.ExcelRange SelectedRange {get;}                                         
TabColor              Property   System.Drawing.Color TabColor {get;set;}                                              
Tables                Property   OfficeOpenXml.Table.ExcelTableCollection Tables {get;}                                
View                  Property   OfficeOpenXml.ExcelWorksheetView View {get;}                                          
Workbook              Property   OfficeOpenXml.ExcelWorkbook Workbook {get;}                                           
WorksheetXml          Property   xml WorksheetXml {get;}                                                               

[DarkLite1]

It is possible to add a custom Formula to any cell you want. See the example coming from Get-Help Export-Excel:

$ExcelParams = @{

$Path = $env:TEMP + '\Excel.xlsx'
    Show    = $true
    Verbose = $true
}
Remove-Item -Path $ExcelParams.Path -Force -EA Ignore
[PSCustOmobject][Ordered]@{
    Date      = Get-Date
    Formula1  = '=SUM(F2:G2)'
    String1   = 'My String'
    String2   = 'a'
    IPAddress = '10.10.25.5'
    Number1   = '07670'
    Number2   = '0,26'
    Number3   = '1.555,83'
    Number4   = '1.2'
    Number5   = '-31'
    PhoneNr1  = '+32 44' 
    PhoneNr2  = '+32 4 4444 444'
    PhoneNr3  =  '+3244444444'
} | Export-Excel @ExcelParams -NoNumberConversion *

#Exports all data to the Excel file 'Excel.xslx' as is, no number conversion will take place. This means that Excel will show the exact same data that you handed over to the 'Export-Excel' function.

In theory this would allow you to set specific cells to do calculations of totals, subtotals and anything else you want. As mentioned above by @dfinke .

[GordonDelgado]

Thanks @dfinke ... I'm having trouble customising my document. You indicted "You can use the -PassThru parameter to get the underlying Excel object and then do what you need." I get an object with the type worksheet, but have very few methods compared to your example above:

$XL=( get-process | export-Excel -path C:\temp\junk.xlsx -PassThru)
$XL | gm
TypeName: OfficeOpenXml.ExcelPackage
Name             MemberType Definition
----             ---------- ----------
Dispose          Method     void Dispose(), void IDisposable.Dispose()
Equals           Method     bool Equals(System.Object obj)
GetAsByteArray   Method     byte[] GetAsByteArray(), byte[] GetAsByteArray(string password)
GetHashCode      Method     int GetHashCode()
GetType          Method     type GetType()
Load             Method     void Load(System.IO.Stream input), void Load(System.IO.Stream input, string Password)
Save             Method     void Save(), void Save(string password)
SaveAs           Method     void SaveAs(System.IO.FileInfo file), void SaveAs(System.IO.FileInfo file, string password), void SaveAs(System.IO.Stream OutputStream), void SaveAs...
ToString         Method     string ToString()
Compression      Property   OfficeOpenXml.CompressionLevel Compression {get;set;}
DoAdjustDrawings Property   bool DoAdjustDrawings {get;set;}
Encryption       Property   OfficeOpenXml.ExcelEncryption Encryption {get;}
File             Property   System.IO.FileInfo File {get;set;}
Package          Property   OfficeOpenXml.Packaging.ZipPackage Package {get;}
Stream           Property   System.IO.Stream Stream {get;}
Workbook         Property   OfficeOpenXml.ExcelWorkbook Workbook {get;}

What am I doing wrong? Ideally I'd like to add data a row at a time, and format each cell based on logic in the script... TIA

[dfinke]

If you want to do that, you may want to consider using the COM way

https://social.technet.microsoft.com/Forums/office/en-US/33dbc1c4-40ee-4ef8-b25b-f29440ab3194/using-com-objects-excel-from-powershell?forum=ITCG

then you have full control. This module works more from a bulk export approach.

[GordonDelgado]

Thanks, I wasn't expecting that advice (Lots of warnings received about unreliability!) I'm slowly figuring things out using Workshet.Cells() One more question: Is there an enumeration somewhere for OfficeOpenXml.ExcelRangeBase.StyleName

[dfinke]

Yup, not recommending it. Just saying this module is not designed for a row by row process :)

[GordonDelgado]

Found it!

You can retrieve an object of type: OfficeOpenXml.ExcelRangeBase which can refer to a single cell as follows: (sorry if my style is too unique ...)

$XL=( {script to output data} | export-Excel -path C:\temp\junk.xlsx -PassThru)
$WB=$XL.Workbook
$WS=$XL.Workbook.Worksheets[1]
$CELLS=$XL.Workbook.Worksheets[1].Cells

# Row, Column
$CELL=$CELLS[1,1]
# text data
$CELL.Value="one x one"
# alternate way to address a cell
$CELLS[1,5].Value=2
$CELLS[2,5].Value=3
# entering a formula; remember in Excel it is Column, Row
$CELLS[3,5].Formula="=e1+e2"

# assign a Pattern type first; error otherwise
# You can find these at http://www.nudoq.org/#!/Packages/EPPlus/EPPlus/ExcelFillStyle
$CELL.Style.Fill.PatternType="DarkHorizontal"

# Intensity of the pattern colour; between -1 and +1
$CELL.Style.Fill.PatternColor.Tint=.3
# Colour of the fill pattern
$CELL.Style.Fill.PatternColor.SetColor([System.Drawing.Color]::Red)

# Intensity of the background colour; between -1 and +1
$CELL.Style.Fill.BackGroundColor.Tint=.3
# Colour of the background
$CELL.Style.Fill.BackGroundColor.SetColor([System.Drawing.Color]::Green)
$XL.SaveAs( "C:\Temp\Test.xlsx")

Hope this helps someone!

[dfinke]

Very cool. It be great if you think about doing a pull request and add that to the examples folder.

Doug

Get Outlook for Androidhttps://aka.ms/ghei36

---

From: GordonDelgado notifications@github.com Sent: Thursday, October 12, 2017 12:31:11 PM To: dfinke/ImportExcel Cc: Doug Finke; Mention Subject: Re: [dfinke/ImportExcel] Confused about creating files and inserting data (#223)

Found it!

You can retrieve an object of type: OfficeOpenXml.ExcelRangeBase which can refer to a single cell as follows: (sorry if my style is too unique ...)

$XL=( {script to output data} | export-Excel -path C:\temp\junk.xlsx -PassThru) $WB=$XL.Workbook $WS=$XL.Workbook.Worksheets[1] $CELLS=$XL.Workbook.Worksheets[1].Cells

Row, Column

$CELL=$CELLS[1,1]

text data

$CELL.Value="one x one"

alternate way to address a cell

$CELLS[1,5].Value=2 $CELLS[2,5].Value=3

entering a formula; remember in Excel it is Column, Row

$CELLS[3,5].Formula="=e1+e2"

assign a Pattern type first; error otherwise

You can find these at http://www.nudoq.org/#!/Packages/EPPlus/EPPlus/ExcelFillStyle

$CELL.Style.Fill.PatternType="DarkHorizontal"

Intensity of the pattern colour; between -1 and +1

$CELL.Style.Fill.PatternColor.Tint=.3

Colour of the fill pattern

$CELL.Style.Fill.PatternColor.SetColor([System.Drawing.Color]::Red)

Intensity of the background colour; between -1 and +1

$CELL.Style.Fill.BackGroundColor.Tint=.3

Colour of the background

$CELL.Style.Fill.BackGroundColor.SetColor([System.Drawing.Color]::Green) $XL.SaveAs( "C:\Temp\Test.xlsx")

Hope this helps someone!

— You are receiving this because you were mentioned. Reply to this email directly, view it on GitHubhttps://github.com/dfinke/ImportExcel/issues/223#issuecomment-336192164, or mute the threadhttps://github.com/notifications/unsubscribe-auth/AAEGuqCAZYiGlgsR2U-XynMjqnXceAv3ks5srj7PgaJpZM4PflLg.

[GordonDelgado]

I'd be willing, but I have no idea what a "pull request" even is :-) BTW I'm getting ugly errors if I try to do more than one $XL.SaveAs()