MOOWR Annexure B Excel Template Creator¶

Python/OpenPyXL utility for programmatically generating a standardized, formatted MOOWR Annexure B Excel workbook.

1. Project Overview¶

The MOOWR Annexure B Excel Template Creator automates the creation of the MOOWR Annexure B workbook from a Python script.

The project was developed using the supplied MOOWR - Annexure B - New.xlsx workbook as the visual and structural reference and create_moowr_annexure_b (2).py as the implementation.

The important design decision is that the final workbook is built completely from scratch using OpenPyXL. The source Excel file is not embedded, copied, or required at runtime. The Python script contains the workbook structure, sheet definitions, labels, merged cells, dimensions, formatting, number formats, and output logic.

This converts a manually maintained Excel template into a reproducible and maintainable workbook-generation process.

---

2. Business Requirement¶

The requirement was to generate a standardized MOOWR Annexure B workbook consistently without depending on manual copying of an Excel template.

Manual approach¶

Open Existing Template
        ↓
Copy / Rename Workbook
        ↓
Maintain 10 Sheets
        ↓
Preserve Formatting
        ↓
Avoid Accidental Changes

Automated approach¶

Run Python Script
        ↓
Create Workbook
        ↓
Create 10 Sheets
        ↓
Build Layout
        ↓
Apply Formatting
        ↓
Save Standard XLSX

This reduces dependency on a manually maintained template and provides a repeatable way to generate the workbook.

---

3. Project Objectives¶

• Generate the complete MOOWR Annexure B workbook programmatically.
• Reproduce the supplied Excel template's structure and visual layout.
• Create all required worksheets in the correct order.
• Recreate merged-cell sections.
• Recreate column widths and row heights.
• Recreate fonts, fills, borders, alignment, wrapping, and number formats.
• Preserve formatted blank data-entry areas.
• Maintain sheet-specific zoom levels.
• Save the workbook automatically to the user's Downloads folder.
• Support an optional custom output path.
• Avoid runtime dependency on the source Excel template.
• Keep the implementation maintainable through reusable styles and sheet builders.

The source code explicitly states that the workbook is built from scratch using OpenPyXL and does not copy an existing workbook. fileciteturn13file1L21-L39

---

4. Source Artifacts¶

File	Purpose
MOOWR - Annexure B - New.xlsx	Reference Excel template
create_moowr_annexure_b (2).py	Complete Python workbook generator

The uploaded source files are the reference artifacts for this implementation. fileciteturn12file0L1-L6 fileciteturn12file1L1-L8

---

5. Workbook Structure¶

The generated workbook contains 10 worksheets, in the following order:

MOOWR - Annexure B - New.xlsx
│
├── Imports
├── DTA
├── Issued for MFR
├── Rmv for job work
├── Receive from Job work
├── Clearance_E
├── Clearance_HC
├── As such
├── Waste_E
└── Waste_HC

The workbook assembly function creates these sheets explicitly in this order. fileciteturn14file4L422-L447

---

6. High-Level Architecture¶

                  Python Generator
                         │
                         ▼
                Determine Output Path
                         │
                         ▼
                 Create Workbook
                         │
                         ▼
             Remove Default Worksheet
                         │
                         ▼
        ┌────────────────┴────────────────┐
        │                                 │
        ▼                                 ▼
   Style Registry                   Sheet Builders
        │                                 │
        │                    ┌────────────┼────────────┐
        │                    ▼            ▼            ▼
        │                 Headers      Merges     Data Areas
        │                    │            │            │
        └────────────────────┴────────────┴────────────┘
                                     │
                                     ▼
                              Workbook Assembly
                                     │
                                     ▼
                                Save XLSX

---

7. Technology Stack¶

Technology	Purpose
Python	Workbook-generation logic
OpenPyXL	XLSX creation and formatting
pathlib	Dynamic and cross-platform file paths
sys	Optional command-line output path
Excel XLSX	Generated template

The implementation imports OpenPyXL workbook, styling, color, and utility classes for this purpose. fileciteturn13file1L41-L47

---

8. Workbook Generation Flow¶

Start
  │
  ▼
Determine Output Location
  │
  ▼
Create New Workbook
  │
  ▼
Remove Default Blank Sheet
  │
  ▼
Create Imports
  │
  ▼
Create DTA
  │
  ▼
Create Issued for MFR
  │
  ▼
Create Rmv for Job work
  │
  ▼
Create Receive from Job work
  │
  ▼
Create Clearance_E
  │
  ▼
Create Clearance_HC
  │
  ▼
Create As such
  │
  ▼
Create Waste_E
  │
  ▼
Create Waste_HC
  │
  ▼
Save Workbook

The implementation removes OpenPyXL's default worksheet and then invokes each sheet builder explicitly. fileciteturn14file4L422-L447

---

9. Dynamic Downloads Path¶

A practical issue addressed during development was the output being saved relative to the directory from which the script was executed.

The final implementation uses:

downloads_dir = Path.home() / "Downloads"

and creates the directory if necessary.

The default output therefore becomes:

<USER_HOME>/Downloads/MOOWR - Annexure B - New.xlsx

The implementation is independent of the current VS Code, PyCharm, terminal, or project directory. fileciteturn13file1L49-L61

This was an important usability improvement because the workbook should be delivered to the user's Downloads folder rather than an application installation directory.

---

10. Custom Output Path¶

The script also supports an explicit output path.

Default¶

python create_moowr_annexure_b.py

Custom¶

python create_moowr_annexure_b.py "D:\Reports\MOOWR.xlsx"

The command-line entry point reads the first argument when supplied and passes it to the workbook generator. fileciteturn14file4L450-L452

---

11. Workbook Filename¶

The standard output filename is centralized as:

WORKBOOK_FILENAME = "MOOWR - Annexure B - New.xlsx"

This prevents the filename from being duplicated throughout the code. fileciteturn13file1L49-L49

---

12. Formatting Architecture¶

A major part of the project is reproducing the Excel template's visual formatting.

Instead of individually constructing formatting objects for every cell, the implementation uses a centralized STYLES registry.

                       STYLES
                          │
        ┌─────────────────┼─────────────────┐
        ▼                 ▼                 ▼
      Font               Fill             Border
        │                 │                 │
        └─────────────────┼─────────────────┘
                          ▼
                      Alignment
                          │
                          ▼
                    Number Format
                          │
                          ▼
                       Style ID
                          │
                          ▼
                      Excel Cell

The source code documents this approach as a registry of distinct combinations of font, fill, border, alignment, and number format. fileciteturn13file1L64-L72

---

13. Style Registry¶

The style registry supports:

• Font name and size.
• Bold/italic/underline.
• Font color.
• Cell fill.
• Border configuration.
• Horizontal and vertical alignment.
• Text wrapping.
• Text rotation.
• Shrink-to-fit.
• Indentation.
• Date formats.
• Time formats.
• Decimal formats.
• Accounting-style formats.
• Text formats.

This allows the workbook to preserve the visual behavior of the reference template rather than only reproducing its text.

---

14. Formatting Caches¶

The implementation maintains reusable caches:

_font_cache = {}
_fill_cache = {}
_border_cache = {}
_alignment_cache = {}

The formatting helper functions reuse previously created OpenPyXL objects where possible.

This improves consistency and avoids repeatedly constructing identical formatting definitions. fileciteturn14file3L314-L365

---

15. Color Handling¶

The _get_color() helper supports:

RGB colors
Theme colors
Indexed colors

This allows the generator to reproduce different Excel color representations used by the reference template. fileciteturn14file1L120-L130

---

16. Font Handling¶

The _get_font() helper converts the registered font definition into an OpenPyXL Font.

Supported properties include:

Font name
Font size
Bold
Italic
Underline
Font color

The created font object is cached for reuse. fileciteturn14file1L133-L147

---

17. Border Handling¶

The template contains extensive table and section borders.

The _get_border() helper constructs:

Left
Right
Top
Bottom

border definitions and caches them.

This allows the generated workbook to reproduce:

• Thin table borders.
• Medium section borders.
• Header boundaries.
• Data-entry boundaries.
• Outer form boundaries.

fileciteturn14file0L11-L30

---

18. Alignment Handling¶

The _get_alignment() helper supports:

• Horizontal alignment.
• Vertical alignment.
• Text wrapping.
• Rotation.
• Shrink-to-fit.
• Indentation.

This is particularly important for long Annexure B labels and multi-level headings. fileciteturn14file0L33-L47

---

19. Common Formatting Helpers¶

The project centralizes repeated Excel operations.

apply_style()¶

Applies the registered font, fill, border, alignment, and number format to a cell. fileciteturn14file0L50-L69

set_cell()¶

Writes a value and optionally applies a style. fileciteturn14file0L81-L86

merge_cells()¶

Provides a wrapper around OpenPyXL's merge functionality. fileciteturn14file0L89-L91

set_column_widths()¶

Sets worksheet column widths from a dictionary. fileciteturn14file0L94-L97

set_row_heights()¶

Sets worksheet row heights from a dictionary. fileciteturn14file0L100-L103

These helpers keep individual sheet builders readable.

---

20. Sheet Builder Architecture¶

Each worksheet has an independent builder function:

create_imports_sheet()
create_dta_sheet()
create_issued_for_mfr_sheet()
create_rmv_for_job_work_sheet()
create_receive_from_job_work_sheet()
create_clearance_e_sheet()
create_clearance_hc_sheet()
create_as_such_sheet()
create_waste_e_sheet()
create_waste_hc_sheet()

This means each sheet can be changed independently without rewriting the entire workbook-generation process.

---

21. Imports Sheet¶

The Imports worksheet contains the import-related Annexure B structure.

The builder defines:

• Sheet name.
• Zoom level.
• Column widths.
• Row heights.
• Headers.
• Merged sections.
• Data-entry areas.
• Styles and number formats.

The implementation creates the sheet as Imports and uses a 67% zoom level. fileciteturn14file6L586-L614

The sheet includes an extended multi-column layout with dedicated widths for descriptions, quantities, values, and related fields.

---

22. DTA Sheet¶

The DTA worksheet represents the Domestic Tariff Area section.

It contains document/header information such as:

Name and address of the Unit
IEC
DATE
Commissionerate
GSTIN

and a DTA receipt section including:

RECEIPTS (DTA)
GST Invoice No.

fileciteturn14file2L278-L287

The builder also defines its merged header ranges, row heights, column widths, and formatting. fileciteturn14file2L211-L276

---

23. Issued for MFR Sheet¶

The Issued for MFR worksheet represents goods issued for manufacturing or other operations.

Its structure includes:

PROCESSING

Goods issued for manufacturing or other operations

Date of Issue
Description of goods
Qty. with UQC
Value

The builder explicitly defines the sheet layout, merged ranges, labels, and formatted entry cells. fileciteturn14file5L483-L527

---

24. Rmv for Job Work Sheet¶

The Rmv for job work worksheet represents goods removed for job work.

The main table contains:

Date & Time of removal
Description of goods
Description as per BE
Qty. with UQC
Value
DC No.
Details of Job worker

The job-worker section is further divided into:

Name
Address
GSTIN (if applicable)

fileciteturn10file9L527-L547

The layout uses merged headers and dedicated column widths to preserve the original form structure. fileciteturn10file9L481-L525

---

25. Receive from Job Work Sheet¶

The Receive from Job work worksheet represents goods received from job work.

It is maintained as a separate builder to preserve its own:

• Header structure.
• Receipt fields.
• Goods details.
• Quantity/UQC fields.
• Value fields.
• Job-work information.
• Data-entry formatting.

Keeping this as an independent builder avoids coupling the receipt layout with the removal layout.

---

26. Clearance_E Sheet¶

The Clearance_E worksheet represents resultant products cleared for export.

The major section is:

RESULTANT PRODUCTS (CLEARANCE FOR EXPORT)

with sub-sections such as:

Resultant products exported
Quantity of warehoused goods contained in
so much of the resultant products exported

The builder contains a wide multi-column layout and multiple merged header ranges to reproduce the original multi-level table. fileciteturn14file8L726-L803

---

27. Clearance_HC Sheet¶

The Clearance_HC worksheet represents the corresponding home-consumption clearance structure.

It has its own:

• Header layout.
• Clearance section.
• Product information.
• Quantity fields.
• Value fields.
• Duty-related fields.
• Merged cells.
• Formatting.

It is maintained independently because its structure is different from the export-clearance sheet.

---

28. As such Sheet¶

The As such worksheet represents imported goods cleared as such.

The builder defines its own layout and uses a 55% zoom level. fileciteturn14file9L864-L886

The form provides dedicated areas for document references, goods descriptions, quantities, values, and duty-related information.

---

29. Waste_E Sheet¶

The Waste_E worksheet represents the export-related waste section.

It contains:

• Multi-column table structures.
• Multiple merged header sections.
• Quantity/value areas.
• Waste-related fields.
• Formatted data-entry areas.

The builder uses a 70% zoom level and defines a number of merged ranges to reproduce the multi-section layout. fileciteturn9file6L543-L593

---

30. Waste_HC Sheet¶

The Waste_HC worksheet represents the home-consumption waste section.

The builder defines dedicated:

• Column widths.
• Row heights.
• Merged headers.
• Annexure B labels.
• Data-entry cells.
• Borders and number formats.

The sheet uses a 94% zoom level and contains multi-level merged sections. fileciteturn9file2L219-L267

---

31. Merged Cell Strategy¶

Merged cells are essential to the visual structure of the workbook.

Examples from the Job Work sheet include:

B2:J2
B11:J11
B12:J12
B13:B14
C13:C14
D13:D14
E13:E14
F13:F14
H13:J13

fileciteturn10file9L513-L525

The Clearance_E sheet contains another extensive set of merged ranges for multi-level headers. fileciteturn14file8L767-L790

Why merging is important¶

Without the merged ranges:

• Section headings would not span the intended columns.
• Multi-level headers would break.
• Long labels would not align correctly.
• The generated workbook would lose the visual structure of the source template.

---

32. Column Widths¶

Column widths are explicitly defined instead of relying on Excel's default sizing.

For example, the DTA sheet contains different widths for description, value, identifier, and duty-related columns. fileciteturn14file2L216-L232

The Imports sheet also defines a large number of individual column widths for its extended table structure. fileciteturn14file6L591-L614

This ensures consistent readability when the generated workbook is opened.

---

33. Row Heights¶

Row heights are explicitly controlled to support:

• Multi-line headers.
• Long descriptions.
• Merged headings.
• Data-entry rows.
• Section titles.

For example, the DTA sheet assigns a larger height to row 15 for its multi-line table header. fileciteturn14file2L234-L262

---

34. Number Formats¶

The style registry includes multiple Excel number formats, including:

General
0.00
#,##0.00
Date formats
Time formats
Accounting-style formats
Text (@)

These formats are important for fields such as:

• Dates.
• Quantities.
• Values.
• Duties.
• Monetary amounts.
• Document identifiers.

The style registry explicitly stores these number-format definitions. fileciteturn13file1L348-L381

---

35. Data-Entry Areas¶

The generated workbook is intended to be a usable form, not just a static visual copy.

Blank cells that are intended for data entry are still formatted with:

• Borders.
• Alignment.
• Number formats.
• Date formats.
• Decimal formats.
• Appropriate font settings.

For example, the Issued for MFR builder applies styles to blank data-entry cells after defining the table headings. fileciteturn14file5L527-L535

This means users can open the generated workbook and immediately use it as a structured working template.

---

36. Template Fidelity¶

The generator reproduces more than just the text.

The implementation captures:

✓ Worksheet names
✓ Worksheet order
✓ Headings
✓ Labels
✓ Merged cells
✓ Column widths
✓ Row heights
✓ Zoom levels
✓ Fonts
✓ Font properties
✓ Fills
✓ Borders
✓ Alignment
✓ Text wrapping
✓ Number formats
✓ Data-entry regions

The sheet builders are explicitly described as building each sheet to match the original template. fileciteturn14file2L211-L214

---

37. Why Build From Scratch?¶

The project intentionally avoids:

Load source XLSX
      ↓
Copy source workbook
      ↓
Save copy

Instead:

Python Definitions
      ↓
OpenPyXL Workbook
      ↓
Build Every Sheet
      ↓
Apply Formatting
      ↓
Save New Workbook

Benefits¶

• No runtime dependency on the original workbook.
• Reproducible output.
• Version-controlled template logic.
• Easier maintenance.
• Easier automation.
• Consistent formatting.
• Easy modification of individual sheets.
• Suitable for future data-population workflows.

The source script explicitly confirms that there is no embedded or copied source workbook. fileciteturn13file1L21-L27

---

38. Workbook Assembly Function¶

create_moowr_annexure_b() is the main orchestration function.

Its responsibilities are:

1. Determine the output path.
2. Create a new workbook.
3. Remove the default blank worksheet.
4. Create all ten required worksheets.
5. Save the workbook.
6. Return the generated path.

The complete assembly sequence is implemented in the source code. fileciteturn14file4L422-L447

---

39. Execution¶

Standard generation¶

python create_moowr_annexure_b.py

Custom location¶

python create_moowr_annexure_b.py "D:\Reports\MOOWR.xlsx"

Execution flow¶

Start Script
     │
     ▼
Resolve Output Path
     │
     ▼
Create Workbook
     │
     ▼
Create 10 Sheets
     │
     ▼
Apply Layout
     │
     ▼
Apply Formatting
     │
     ▼
Save XLSX
     │
     ▼
Print Final Path

The script prints the resolved output path after generation. fileciteturn14file4L450-L452

---

40. Error Reduction¶

The automated approach reduces common manual-template errors:

Manual Risk	Generator Control
Missing sheet	Fixed sheet builder list
Incorrect sheet order	Controlled assembly
Incorrect merge	Explicit merge definitions
Incorrect column width	Explicit width definitions
Incorrect row height	Explicit height definitions
Missing border	Centralized styles
Wrong number format	Style-level formats
Wrong save directory	Dynamic Downloads path
Accidental template modification	New workbook each run

---

41. Maintainability¶

The implementation is divided into three logical layers.

Layer 1 — Global Styling¶

STYLES

Controls reusable formatting.

Layer 2 — Sheet Builders¶

create_<sheet>_sheet()

Controls individual worksheet structure.

Layer 3 — Workbook Assembly¶

create_moowr_annexure_b()

Controls workbook creation, sheet order, and saving.

This structure makes future changes localized and easier to test.

---

42. Project Deliverables¶

MOOWR Excel Template Creator
│
├── create_moowr_annexure_b (2).py
│       └── Workbook-generation source
│
└── MOOWR - Annexure B - New.xlsx
        └── Generated/reference workbook

The uploaded source files are the project's primary implementation and reference artifacts. fileciteturn12file0L1-L6 fileciteturn12file1L1-L8

---

43. Key Technical Achievements¶

Complete Workbook Automation¶

The entire ten-sheet workbook is generated through Python.

High-Fidelity Template Recreation¶

The implementation captures layout and formatting rather than only cell text.

Centralized Style System¶

Repeated formatting is stored once and reused.

Formatting Caching¶

Font, fill, border, and alignment objects are cached.

Dynamic Output Location¶

The workbook is saved to the user's Downloads folder by default.

Custom Output Support¶

A user can override the default output path.

Runtime Independence¶

The original Excel file is not required when generating the workbook.

Modular Sheet Design¶

Every worksheet has an independent builder.

---

44. Future Enhancements¶

Possible future improvements include:

Automated Workbook Comparison¶

Compare the generated workbook with the reference workbook for:

Sheet names
Sheet order
Merged ranges
Dimensions
Cell values
Styles
Number formats

Regression Testing¶

Automatically generate the workbook and verify critical structural and formatting properties.

Template Versioning¶

Support versions such as:

MOOWR Annexure B v1
MOOWR Annexure B v2

when the official template changes.

Data Population¶

Add a separate layer that populates the generated workbook from application data such as:

Frappe DocTypes
Bill of Entry
GRV
Inventory
MOOWR transactions

The current implementation focuses on template generation, while data population can remain a separate concern.

---

45. End-to-End Visual Representation¶

              ┌────────────────────────────┐
              │ Reference Excel Workbook   │
              │ MOOWR Annexure B           │
              └─────────────┬──────────────┘
                            │
                            ▼
              ┌────────────────────────────┐
              │ Analyze Structure          │
              │ Layout + Formatting        │
              └─────────────┬──────────────┘
                            │
                            ▼
              ┌────────────────────────────┐
              │ Python / OpenPyXL          │
              │ Workbook Generator         │
              └─────────────┬──────────────┘
                            │
             ┌──────────────┼──────────────┐
             ▼              ▼              ▼
       Style Registry   Sheet Builders   Output Logic
             │              │              │
             │       ┌──────┴──────┐       │
             │       ▼             ▼       │
             │   10 Sheets      Layout     │
             │       │             │       │
             └───────┴─────────────┘       │
                         │                 │
                         ▼                 │
                Complete Workbook         │
                         │                 │
                         ▼                 ▼
                 Save XLSX          Downloads Folder
                         │
                         ▼
             MOOWR - Annexure B - New.xlsx

---

46. Project Outcome¶

The project transforms the MOOWR Annexure B template from a manually maintained Excel artifact into a programmatically generated and reproducible workbook.

The final process is:

Reference Template
        ↓
Analyze Layout
        ↓
Encode Structure in Python
        ↓
Centralize Styles
        ↓
Create Individual Sheet Builders
        ↓
Assemble Workbook
        ↓
Save to Dynamic Downloads Path
        ↓
Standardized MOOWR Annexure B Workbook

The implementation therefore provides both visual fidelity and technical maintainability.

---

47. Conclusion¶

The MOOWR Annexure B Excel Template Creator provides a reliable method for generating the complete MOOWR Annexure B workbook using Python and OpenPyXL.

The solution covers:

• Complete workbook creation.
• Ten standardized worksheets.
• Fixed worksheet order.
• Merged-cell layouts.
• Column widths.
• Row heights.
• Zoom levels.
• Fonts and font properties.
• Fills and borders.
• Alignment and text wrapping.
• Date, time, decimal, and accounting formats.
• Formatted blank data-entry areas.
• Centralized reusable styles.
• Formatting-object caching.
• Dynamic Downloads output path.
• Optional custom output paths.
• Runtime independence from the source Excel template.

The key achievement is converting a manually maintained Excel template into a repeatable, maintainable, and automation-ready workbook generator.

MANUAL EXCEL TEMPLATE
        ↓
PROGRAMMATIC TEMPLATE DEFINITION
        ↓
OPENPYXL WORKBOOK GENERATION
        ↓
STANDARDIZED 10-SHEET ANNEXURE B
        ↓
READY FOR DATA ENTRY / FUTURE AUTOMATION