Excel Formating

Business Central/NAV Developers Forum
1

Now, that I have many nice reports being generated in excel my boss would like me to make certain reports landscape by default. Also change the margins and set the view to 90%. I told him that I didn’t know if any or all of this was possible. I searched the forum and didn’t see that these issues have been asked before. So, I come to you my fellow deveopers in search of these mysteries. Below is the code sample of a project. OnPreReport CREATE(xlApp); xlBook := xlApp.Workbooks.Add(-4167); xlSheet := xlApp.ActiveSheet; OnPreDataItem xlSheet.Name := ‘Open AR’; xlSheet.Range(‘A1’).Value := “Cust. Ledger Entry”.FIELDNAME(“Customer No.”); xlSheet.Range(‘B1’).Value := ‘Customer Name’; xlSheet.Range(‘C1’).Value := ‘Customer Address’; xlSheet.Range(‘D1’).Value := ‘Customer City’; xlSheet.Range(‘E1’).Value := ‘Customer State’; xlSheet.Range(‘F1’).Value := ‘Customer Zip Code’; xlSheet.Range(‘G1’).Value := “Cust. Ledger Entry”.FIELDNAME(“Document Type”); xlSheet.Range(‘H1’).Value := “Cust. Ledger Entry”.FIELDNAME(“Document No.”); xlSheet.Range(‘I1’).Value := “Cust. Ledger Entry”.FIELDNAME(“Posting Date”); xlSheet.Range(‘J1’).Value := ‘Original Amount’; xlSheet.Range(‘K1’).Value := ‘Amount Due’; xlSheet.Range(‘L1’).Value := “Cust. Ledger Entry”.FIELDNAME(“Your Reference”); xlSheet.Range(‘M1’).Value := ‘Retainage’; i := 1; OnAfterGetRecord i += 1; Row := FORMAT(i); xlSheet.Range(‘A’+Row).Value := “Customer No.”; xlSheet.Range(‘B’+Row).Value := Cust.Name; xlSheet.Range(‘C’+Row).Value := Cust.Address; xlSheet.Range(‘D’+Row).Value := Cust.City; xlSheet.Range(‘E’+Row).Value := Cust.State; xlSheet.Range(‘F’+Row).Value := Cust.“ZIP Code”; xlSheet.Range(‘G’+Row).Value := FORMAT(“Document Type”); xlSheet.Range(‘H’+Row).Value := “Document No.”; xlSheet.Range(‘I’+Row).Value := “Posting Date”; xlSheet.Range(‘J’+Row).Value := Amount; xlSheet.Range(‘K’+Row).Value := "Remaining Amount ($); xlSheet.Range(‘L’+Row).Value := “Your Reference”; xlSheet.Range(‘M’+Row).Value := Retainage;

2

Hi William, xlSheet.PageSetup.Orientation := 2; //Landscape xlSheet.PageSetup.Zoom := 90; // Zoom 90 % xlSheet.PageSetup.PrintTitleRows := ‘$1:$3’; xlSheet.PageSetup.LeftMargin := 0.5; xlSheet.PageSetup.TopMargin := 0.5; xlSheet.PageSetup.BottomMargin := 0.5; xlSheet.PageSetup.RightMargin := 0.5;

3

Thanks, it worked great!