Python Django create report HTML, PDF and EXCEL, Point Of Sale (POS) Part 02

Показать описание

Python Django How to develop Report from QUERY to HTML, PDF and EXCEL

All source code are available in comments, if you want to get the complete in 1 zip file, just leave messgae ini comments where i have to send the email

Рекомендации по теме

Комментарии

CREATE TABLE TABEL_BARANG
(
ID NUMBER(38) NOT NULL,
KATEGORI NUMBER,
SUB_KATEGORI NUMBER,
KODE_BARANG VARCHAR2(25 BYTE) NOT NULL,
NAMA_BARANG VARCHAR2(100 BYTE) NOT NULL,
QTY NUMBER,
SATUAN VARCHAR2(25 BYTE) NOT NULL,
HARGA_SATUAN NUMBER,
JUMLAH NUMBER,
NO NUMBER,
KODE_BARANG_BARU VARCHAR2(25 BYTE),
ID_LAMA NUMBER,
KATEG NUMBER,
SUB_CATEG NUMBER
)
TABLESPACE ASSET_DATA;

ALTER TABLE TABEL_BARANG ADD (
CONSTRAINT PK_BARANG
PRIMARY KEY
(ID));

pythondjangowebapplication

urls.py

from django.urls import path
from . import views

urlpatterns = [
path('goods-report/', views.goods_list_report, name='goods_report'),
path('export-excel/', views.export_goods_excel, name='export_goods_excel'),
path('export-pdf/', views.export_goods_pdf, name='export_goods_pdf'),
]

pythondjangowebapplication

CREATE TABLE TBL_SUB_KATEGORI_BARANGS
(
ID NUMBER NOT NULL,
ID_KATEGORI NUMBER NOT NULL,
SUB_KATEGORI VARCHAR2(50 BYTE) NOT NULL,
PINDAH NUMBER,
MEDIS NUMBER,
KODE_KATEGORI NUMBER NOT NULL,
KODE_SUB_KATEGORI NUMBER NOT NULL,
CREATED_BY NUMBER,
CREATED_AT VARCHAR2(25 BYTE),
UPDATED_BY NUMBER,
UPDATED_AT VARCHAR2(25 BYTE),
ID_KATEGORI_LAMA NUMBER
)
TABLESPACE ASSET_DATA;

ALTER TABLE ADD (
CONSTRAINT PK_TBL_SUB_KATEG_BARANGS
PRIMARY KEY
(ID) );

pythondjangowebapplication

views.py

from django.db import connection
from django.shortcuts import render
from django.contrib.staticfiles import finders
from django.templatetags.static import static

@login_required
def goods_list_report(request):
with connection.cursor() as cursor:
cursor.execute("""
select B.SUB_KATEGORI, A.KODE_BARANG, A.NAMA_BARANG, A.QTY, A.SATUAN, A.HARGA_SATUAN, A.JUMLAH
from tabel_barang a, tbl_sub_kategori_barangs b
where A.KATEGORI=B.KODE_KATEGORI AND

order by kode_barang
""")
rows = cursor.fetchall()

# Group by SUB_KATEGORI
grouped_data = {}
grand_total = 0

for row in rows:
sub_kategori = row[0]
item = {
'kode_barang': row[1],
'nama_barang': row[2],
'qty': row[3],
'satuan': row[4],
'harga_satuan': row[5],
'jumlah': row[6],
}

if sub_kategori not in grouped_data:
grouped_data[sub_kategori] = {
'items': [],
'subtotal': 0
}

+= item['jumlah']
grand_total += item['jumlah']
logo_path = os.path.join(settings.BASE_DIR,

return render(request, 'goods/goods_list_pdf.html', {
'grouped_data': grouped_data,
'grand_total': grand_total,
'logo_path': logo_path,
# 'logo_path': static('images/logo.png'),
'now': datetime.now(),
'title': 'Daftar Barang',

})

pythondjangowebapplication

goods_list_pdf.html



<!DOCTYPE html>
<html>
<head>
{% load humanize %}
{% load static %}

<title>{{ title }}</title>

<style>
.logo-title {
display: flex;
align-items: center;
margin-bottom: 20px;
}
.logo-title img {
height: 50px;
margin-right: 15px;
}

@media print {
.no-print {
display: none !important;
}
}
</style>

</head>

<body class="p-4">
<div class="mb-3 no-print">
<a href="{% url 'export_goods_excel' %}" class="btn btn-success">Download Excel</a>
<a href="{% url 'export_goods_pdf' %}" class="btn btn-danger">Download PDF</a>
</div>

<div class="container">
<div class="logo-title">

{% if for_pdf %}
<img src="{{ logo_path }}" alt="Logo" style="height: 80px;">
{% else %}
<img src="{% static 'images/logo.png' %}" alt="Logo" style="height: 80px;" />
{% endif %}




<div>
<h5 style="margin: 0;">Oracle Corporation Singapore Pte Ltd</h5>
<p style="margin: 0; font-size: 14px;">
1 Fusionopolis Place, Level 12, Galaxis, <br>
Singapore 138522<br>

</p>
</div>
</div>
<h2 class="mb-4">{{ title }}</h2>

{% for sub_kategori, data in grouped_data.items %}
<div class="card mb-4">
<div class="card-header bg-primary text-white">
{{ sub_kategori }}
</div>
<div class="card-body p-0">
<table class="table table-bordered table-striped mb-0">
<thead class="table-light">
<tr>
<th>Kode Barang</th>
<th>Nama Barang</th>
<th style="text-align: right;">Qty</th>
<th style="text-align: center;">Satuan</th>
<th style="text-align: right;">Harga Satuan</th>
<th style="text-align: right;">Jumlah</th>
</tr>
</thead>
<tbody>
{% for item in data.items %}
<tr>
<td>{{ item.kode_barang }}</td>
<td>{{ item.nama_barang }}</td>
<td style="text-align: right;">{{ }}</td>
<td style="text-align: center;">{{ item.satuan }}</td>
<td style="text-align: right;">{{ }}</td>
<td style="text-align: right;">{{ }}</td>
</tr>
{% endfor %}
<tr>
<td colspan="5" style="text-align: right; font-weight: bold;">Subtotal</td>
<td style="text-align: right; font-weight: bold;">{{ }}</td>
</tr>
</tbody>
</table>
</div>
</div>
{% endfor %}

<table style="width: 100%; border-collapse: collapse;" border="1" cellpadding="5">
<tr>
<td colspan="5" style="text-align: right; font-weight: bold;">Grand Total</td>
<td style="text-align: right; font-weight: bold;">{{ }}</td>
</tr>
</table>
</div>

</body>
</html>

pythondjangowebapplication

views.py

from django.template.loader import get_template
from django.conf import settings
from xhtml2pdf import pisa
from io import BytesIO
from django.http import FileResponse
import os
from datetime import datetime

@login_required
def export_goods_pdf(request):
with connection.cursor() as cursor:
cursor.execute("""
SELECT B.SUB_KATEGORI, A.KODE_BARANG, A.NAMA_BARANG, A.QTY, A.SATUAN,
A.HARGA_SATUAN, A.JUMLAH
FROM TABEL_BARANG A, TBL_SUB_KATEGORI_BARANGS B
WHERE A.KATEGORI = B.KODE_KATEGORI
AND A.SUB_KATEGORI = B.KODE_SUB_KATEGORI
ORDER BY A.KODE_BARANG
""")
rows = cursor.fetchall()

logo_path =
# Group by SUB_KATEGORI
grouped_data = {}
grand_total = 0

for row in rows:
sub_kategori = row[0]
item = {
'kode_barang': row[1],
'nama_barang': row[2],
'qty': row[3],
'satuan': row[4],
'harga_satuan': row[5],
'jumlah': row[6],
}

if sub_kategori not in grouped_data:
grouped_data[sub_kategori] = {
'items': [],
'subtotal': 0
}

+= item['jumlah']
grand_total += item['jumlah']

# Logo file path (make sure it exists)
# logo_path = os.path.join(settings.BASE_DIR,

#logo_path = or settings.STATICFILES_DIRS[0], 'images', 'logo.png')
# logo_path = os.path.join(settings.BASE_DIR, 'orderappp', 'static', 'images', 'logo.jpg')

print(' Path', logo_path)

# Render template to HTML
template =
html = template.render({
'grouped_data': grouped_data,
'grand_total': grand_total,
'logo_path': logo_path,
# 'logo_path': logo_path.replace('\\', '/'),
# 'logo_path': static('images/logo.png'),
'now': datetime.now(),
'title': 'Daftar Barang',
'for_pdf' :True,
})

# Generate PDF
output = BytesIO()
pisa_status = pisa.CreatePDF(html, dest=output)
if pisa_status.err:
return HttpResponse('PDF generation failed', status=500)

output.seek(0)
filename =
return FileResponse(output, as_attachment=True, filename=filename)
# return FileResponse(output, as_attachment=True,

pythondjangowebapplication

views.py

import openpyxl
from openpyxl.styles import Font, Alignment, Border, Side
from django.http import FileResponse
from django.db import connection
from datetime import datetime
from io import BytesIO
from openpyxl.utils import get_column_letter

@login_required
def export_goods_excel(request):
with connection.cursor() as cursor:
cursor.execute("""
SELECT B.SUB_KATEGORI, A.KODE_BARANG, A.NAMA_BARANG, A.QTY, A.SATUAN, A.HARGA_SATUAN, A.JUMLAH
FROM tabel_barang a, tbl_sub_kategori_barangs b
WHERE A.KATEGORI = B.KODE_KATEGORI
AND A.SUB_KATEGORI = B.KODE_SUB_KATEGORI
ORDER BY A.KODE_BARANG
""")
rows = cursor.fetchall()

# Group the data
grouped_data = {}
grand_total = 0

for row in rows:
sub_kategori = row[0]
item = {
'kode_barang': row[1],
'nama_barang': row[2],
'qty': row[3],
'satuan': row[4],
'harga_satuan': row[5],
'jumlah': row[6],
}

if sub_kategori not in grouped_data:
grouped_data[sub_kategori] = {
'items': [],
'subtotal': 0
}

+= item['jumlah']
grand_total += item['jumlah']

# Create Excel workbook
wb = openpyxl.Workbook()
ws = wb.active
ws.title = "Goods List"

# Styles
bold = Font(bold=True)
right =
center =
thin_border = Border(left=Side(style='thin'), right=Side(style='thin'),
top=Side(style='thin'), bottom=Side(style='thin'))

row_num = 1
ws.merge_cells(start_row=row_num, start_column=1, end_row=row_num, end_column=6)
ws.cell(row=row_num, column=1).value = "DAFTAR BARANG"
ws.cell(row=row_num, column=1).font = Font(size=14, bold=True)
ws.cell(row=row_num, column=1).alignment = center
row_num += 2

for sub_kategori, data in grouped_data.items():
ws.cell(row=row_num, column=1).value = f"Sub Kategori: {sub_kategori}"
ws.cell(row=row_num, column=1).font = bold
row_num += 1

headers = ["Kode Barang", "Nama Barang", "Qty", "Satuan", "Harga Satuan", "Jumlah"]
for col_num, header in enumerate(headers, start=1):
cell = ws.cell(row=row_num, column=col_num, value=header)
cell.font = bold
cell.border = thin_border
cell.alignment = center
row_num += 1

for item in data['items']:
ws.cell(row=row_num, column=1, = thin_border
ws.cell(row=row_num, column=2, = thin_border
ws.cell(row=row_num, column=3, value=item['qty']).border = thin_border
ws.cell(row=row_num, column=3).alignment = right
ws.cell(row=row_num, column=4, value=item['satuan']).border = thin_border
ws.cell(row=row_num, column=5, = '#, ##0.00'
ws.cell(row=row_num, column=5).border = thin_border
ws.cell(row=row_num, column=5).alignment = right
ws.cell(row=row_num, column=6, = '#, ##0.00'
ws.cell(row=row_num, column=6).border = thin_border
ws.cell(row=row_num, column=6).alignment = right
row_num += 1

ws.cell(row=row_num, column=5, value="Subtotal").font = bold
ws.cell(row=row_num, column=6, value=data['subtotal']).font = bold
ws.cell(row=row_num, column=6).number_format = '#, ##0.00'
ws.cell(row=row_num, column=6).alignment = right
row_num += 2

# Grand Total
ws.cell(row=row_num, column=5, value="Grand Total").font = bold
ws.cell(row=row_num, column=6, value=grand_total).font = bold
ws.cell(row=row_num, column=6).number_format = '#, ##0.00'
ws.cell(row=row_num, column=6).alignment = right

# Adjust column widths
# for col in ws.columns:
# max_length = max(len(str(cell.value)) if cell.value else 0 for cell in col)
# = max_length + 2

for i, col in enumerate(ws.columns, start=1):
max_length = max(len(str(cell.value)) if cell.value else 0 for cell in col)
= max_length + 2

# Return as response
output = BytesIO()
wb.save(output)
output.seek(0)

filename =
return FileResponse(output, as_attachment=True, filename=filename)

pythondjangowebapplication

Python Django create report HTML, PDF and EXCEL, Point Of Sale (POS) Part 02

How to generate Reports with Python automatically - 3: HTML (with template) & HTML to PDF

Django Render HTML to PDF | Introduction

PYTHON : Render HTML to PDF in Django site

How to generate Reports with Python automatically - 1: Overview

Python Django create report HTML, PDF and EXCEL, Point Of Sale (POS) Part 02

How To Generate PDF Files Dynamically With Django - Django Wednesdays #17

Generate Bill or Any Report on Web App Using Django | Python [ Reportlab ] | Js

How to generate Reports with Python automatically - 4: PDF directly

PROJECT GRAND FINALE - Reports | Python & Django Project-Based Tutorial | Ep. 10

Reporting Dashboard built with Django

Django Report (CSV, PDF, EXCEL) App

Django Course Part - 11 || Create Html To PDF With Dynamic Data Using Xhtml2Pdf Package

Best Programming Languages #programming #coding #javascript

How to Create PDF Files with Python [New Method 2024]

Python in Excel‼️ #excel #python

How To Create a Login and Register Page using HTML, CSS, and JavaScript #css #html #javascript

Generate dynamic pdf Django | Django generate pdf from HTML | Pdf generator for Django |

Power of CSS & JAVASCRIPT #htmlfullcourse #htmltutorial #javascript #coding #python #programming...

PYTHON DJANGO TUTORIAL - Meeting Link Organizer | Creating the HTML & URLS [Frontend] (4/5)

Introduction to Jinja Templating

Fixed Live Server Problem In Visual Studio Code!#vscode #liveserver

🛑Django PDF Generator | Django HTML to PDF converter | 2024

Python Basics: Your FIRST Program in Under a Minute! 🚀

SQL Injection 101: Exploiting Vulnerabilities