Create a Grocery List Template in Excel

Posted

in

by

admin

Learn How to Create a Grocery List Template in Excel with PHP using PHPSpreadSheet.

List down and organize all your grocery item needs before doing a shopping to save time. Group the things-to-buy according to their classification and put both the estimated price for each item and the total estimated price of all the items. The quantity and unit of each items to purchase is also important to include in the list. Brand items are also a good choice to be included.

There are various PHPSpreadSheet methods you can use to format and apply styles to your Excel Grocery List Template for your shopping needs. And here are a few of them that we will be using in this tutorial.

To make font size bigger, use setSize(), for setting font weights bolder, use setBold. Use setFillType() and setARGB to apply a cell background. AutoFit column width using setAutoSize(). A header logo will also be added with the use of addImage() and setOddHeader(), this is just incase you would like to add an image to the header.

This article will discuss the process of creating an excel grocery list template in PHP using PHPSpreadSheet.

Requirements:

  • Composer
  • PHP 7.2 or newer

Step 1.

Setup dependencies.

{
    "require": {
        "phpoffice/phpspreadsheet": "^1.3"
    }
}
composer.json

Step 2.

Install phpspreadsheet.

$ composer install
command line

Step 3.

Create a new PHP file (create-a-grocery-list-template-in-excel.php), and start coding.

Step 4.

Prepare the sheet.

// Autoload dependencies
require 'vendor/autoload.php';

// Import the core class of PhpSpreadsheet
use PhpOffice\PhpSpreadsheet\Spreadsheet;

// Import the Xlsx writer class
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

// Create a new Spreadsheet object
$spreadsheet = new Spreadsheet();

// Retrieve the current active worksheet
$sheet = $spreadsheet->getActiveSheet();

Step 5.

Prepare the data.

This includes all the grocery items, each specific units and quantities, and estimated prices. Brands were also added and all items are grouped into their classification.

/**
 * Prepare the column headers.
 */
$headers = ['Items', 'Quantity', 'Unit', 'Brand', 'Estimated Price'];

/**
 * Prepare the grocery items and descriptions.
 */
$contents = [
    'Fruits'   => [
        'Apples'   => ['1', 'kg', 'Brand A', '4.87'],
        'Oranges'   => ['1', 'kg', 'Brand O', '4.16'],
        'Banana'   => ['1', 'kg', 'Brand B', '1.61'],
    ],
    'Dairies'   => [
        'Milk'   => ['1', 'liter', 'Brand M', '1.03'],
        'Eggs'   => ['1', 'dozen', 'Brand E', '4.60'],
        'Cheese'   => ['1', 'dozen', 'Brand C', '11.80'],
    ],
    'Vegetables'   => [
        'Potato'   => ['1', 'kg', 'Brand P', '2.17'],
        'Onion'   => ['1', 'kg', 'Brand O', '2.77'],
        'Lettuce'   => ['1', 'head', 'Brand L', '2.14'],
        'Tomato'   => ['1', 'kg', 'Brand T', '4.57'],
    ],
];

Step 6.

Prepare the columns and rows data.

/**
 * Prepare column letter representations.
 */
$alphabet = range('A', 'Z');

/**
 * Start at row 4 to give
 * space for the header logo.
 */
$rowCounter = 4;

Step 7.

Write all the data.

This is where the list items and descriptions will be populated in the spreadsheet.

/**
 * Write all the grocery list.
 */
foreach ($contents AS $content => $contentValues) { 

    // write the product type
    $sheet->setCellValue('A' . $rowCounter, $content);

    // set product type font weight to bold and font size to 14
    $sheet->getStyle('A' . $rowCounter)->getFont()->setBold(true)->setSize(14);

    // set product type font weight to bold
    $sheet->getStyle('A' . $rowCounter)->getFont()->setBold(true);

    // write the headers
    $rowCounter++;
    foreach ($headers AS $headerKey => $headerValue) {
        $sheet->setCellValue($alphabet[$headerKey] . $rowCounter, $headerValue);

        // Apply a solid type background to the header
        $sheet->getStyle($alphabet[$headerKey] . $rowCounter)->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID);

        // Fill up the background of the header with color blue
        $sheet->getStyle($alphabet[$headerKey] . $rowCounter)->getFill()->getStartColor()->setARGB('F1F1F1FF');

        // set header font weight to bold
        $sheet->getStyle($alphabet[$headerKey] . $rowCounter)->getFont()->setBold(true);

        // set column to 'auto width'
        $sheet->getColumnDimension($alphabet[$headerKey])->setAutoSize(true);
    }

    // write the item descriptions
    foreach ($contentValues AS $product => $descriptions) {
        $rowCounter++;

        $sheet->setCellValue('A' . $rowCounter, $product);

        $columnCounter = 1;
        foreach ($descriptions AS $description) {
            $sheet->setCellValue($alphabet[$columnCounter] . $rowCounter, $description);

            $columnCounter++;
        }
    }

    $rowCounter+=2; // allot one extra count spacing
}

Step 8.

Set the total estimated price data and computation.

// Merge cells where to put the 'Total Estimated Price' label
$sheet->mergeCells('B' . $rowCounter . ':' . $alphabet[(count($headers)-2)] . $rowCounter);

// set 'Total Estimated Price' label
$sheet->setCellValue('B' . $rowCounter, 'Total Estimated Price');

// set 'Total Estimated Price' label font weight to bold and font size to 14
$sheet->getStyle('B' . $rowCounter)->getFont()->setBold(true)->setSize(14);

// set 'Total Estimated Price' value
$sheet->setCellValue($alphabet[(count($headers)-1)] . $rowCounter, '=SUM(' . $alphabet[(count($headers)-1)] . '1:' . $alphabet[(count($headers)-1)] . $rowCounter . ')');

// set 'Total Estimated Price' value font weight to bold and font size to 14
$sheet->getStyle($alphabet[(count($headers)-1)] . $rowCounter)->getFont()->setBold(true)->setSize(14);

// set 'Total Estimated Price' value font color to red
$sheet->getStyle($alphabet[(count($headers)-1)] . $rowCounter)->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_RED);

Step 9.

Add a header logo.

This is just an add-on data, you can skip this if you don’t want to put a header logo to your list.

// Initiate new HeaderFooterDrawing instance
$drawing = new \PhpOffice\PhpSpreadsheet\Worksheet\HeaderFooterDrawing();

// Set the name of the logo
$drawing->setName('Spreadsheet-Coding.com Logo');

// Set the path of the logo
$drawing->setPath('./spreadsheet-coding-com-logo-white-bg.png');

// Add the image to the left side header of the sheet
$sheet->getHeaderFooter()->addImage($drawing, \PhpOffice\PhpSpreadsheet\Worksheet\HeaderFooter::IMAGE_HEADER_LEFT);

// Set the print header
$sheet->getHeaderFooter()->setOddHeader('&L&G');

Step 10.

Last step.

Create and save the excel file.

// Write a new .xlsx file
$writer = new Xlsx($spreadsheet);

// Save the new .xlsx file
$writer->save('create-a-grocery-list-template-in-excel.xlsx');

Complete code.

<?php

// Autoload dependencies
require 'vendor/autoload.php';

// Import the core class of PhpSpreadsheet
use PhpOffice\PhpSpreadsheet\Spreadsheet;

// Import the Xlsx writer class
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

// Create a new Spreadsheet object
$spreadsheet = new Spreadsheet();

// Retrieve the current active worksheet
$sheet = $spreadsheet->getActiveSheet();

/**
 * Prepare the column headers.
 */
$headers = ['Items', 'Quantity', 'Unit', 'Brand', 'Estimated Price'];

/**
 * Prepare the grocery items and descriptions.
 */
$contents = [
    'Fruits'   => [
        'Apples'   => ['1', 'kg', 'Brand A', '4.87'],
        'Oranges'   => ['1', 'kg', 'Brand O', '4.16'],
        'Banana'   => ['1', 'kg', 'Brand B', '1.61'],
    ],
    'Dairies'   => [
        'Milk'   => ['1', 'liter', 'Brand M', '1.03'],
        'Eggs'   => ['1', 'dozen', 'Brand E', '4.60'],
        'Cheese'   => ['1', 'dozen', 'Brand C', '11.80'],
    ],
    'Vegetables'   => [
        'Potato'   => ['1', 'kg', 'Brand P', '2.17'],
        'Onion'   => ['1', 'kg', 'Brand O', '2.77'],
        'Lettuce'   => ['1', 'head', 'Brand L', '2.14'],
        'Tomato'   => ['1', 'kg', 'Brand T', '4.57'],
    ],
];

/**
 * Prepare column letter representations.
 */
$alphabet = range('A', 'Z');

/**
 * Start at row 4 to give
 * space for the header logo.
 */
$rowCounter = 4;

/**
 * Write all the grocery list.
 */
foreach ($contents AS $content => $contentValues) { 

    // write the product type
    $sheet->setCellValue('A' . $rowCounter, $content);

    // set product type font weight to bold and font size to 14
    $sheet->getStyle('A' . $rowCounter)->getFont()->setBold(true)->setSize(14);

    // set product type font weight to bold
    $sheet->getStyle('A' . $rowCounter)->getFont()->setBold(true);

    // write the headers
    $rowCounter++;
    foreach ($headers AS $headerKey => $headerValue) {
        $sheet->setCellValue($alphabet[$headerKey] . $rowCounter, $headerValue);

        // Apply a solid type background to the header
        $sheet->getStyle($alphabet[$headerKey] . $rowCounter)->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID);

        // Fill up the background of the header with color blue
        $sheet->getStyle($alphabet[$headerKey] . $rowCounter)->getFill()->getStartColor()->setARGB('F1F1F1FF');

        // set header font weight to bold
        $sheet->getStyle($alphabet[$headerKey] . $rowCounter)->getFont()->setBold(true);

        // set column to 'auto width'
        $sheet->getColumnDimension($alphabet[$headerKey])->setAutoSize(true);
    }

    // write the item descriptions
    foreach ($contentValues AS $product => $descriptions) {
        $rowCounter++;

        $sheet->setCellValue('A' . $rowCounter, $product);

        $columnCounter = 1;
        foreach ($descriptions AS $description) {
            $sheet->setCellValue($alphabet[$columnCounter] . $rowCounter, $description);

            $columnCounter++;
        }
    }

    $rowCounter+=2; // allot one extra count spacing
}

// Merge cells where to put the 'Total Estimated Price' label
$sheet->mergeCells('B' . $rowCounter . ':' . $alphabet[(count($headers)-2)] . $rowCounter);

// set 'Total Estimated Price' label
$sheet->setCellValue('B' . $rowCounter, 'Total Estimated Price');

// set 'Total Estimated Price' label font weight to bold and font size to 14
$sheet->getStyle('B' . $rowCounter)->getFont()->setBold(true)->setSize(14);

// set 'Total Estimated Price' value
$sheet->setCellValue($alphabet[(count($headers)-1)] . $rowCounter, '=SUM(' . $alphabet[(count($headers)-1)] . '1:' . $alphabet[(count($headers)-1)] . $rowCounter . ')');

// set 'Total Estimated Price' value font weight to bold and font size to 14
$sheet->getStyle($alphabet[(count($headers)-1)] . $rowCounter)->getFont()->setBold(true)->setSize(14);

// set 'Total Estimated Price' value font color to red
$sheet->getStyle($alphabet[(count($headers)-1)] . $rowCounter)->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_RED);

// Initiate new HeaderFooterDrawing instance
$drawing = new \PhpOffice\PhpSpreadsheet\Worksheet\HeaderFooterDrawing();

// Set the name of the logo
$drawing->setName('Spreadsheet-Coding.com Logo');

// Set the path of the logo
$drawing->setPath('./spreadsheet-coding-com-logo-white-bg.png');

// Add the image to the left side header of the sheet
$sheet->getHeaderFooter()->addImage($drawing, \PhpOffice\PhpSpreadsheet\Worksheet\HeaderFooter::IMAGE_HEADER_LEFT);

// Set the print header
$sheet->getHeaderFooter()->setOddHeader('&L&G');

// Write a new .xlsx file
$writer = new Xlsx($spreadsheet);

// Save the new .xlsx file
$writer->save('create-a-grocery-list-template-in-excel.xlsx');

create-a-grocery-list-template-in-excel.php

Test.

Run the following codes.

$ php create-a-grocery-list-template-in-excel.php
command line

Result.

Open the generated file create-a-grocery-list-template-in-excel.xlsx.

Excel file view.

Print preview.

References:

Posted

in

by

admin

Tags:

Leave a Reply

Your email address will not be published. Required fields are marked *

Latest Tutorials

Web Dev Tutorials