ADG Product Ideas
Add a new idea Subscribe
Categories General Ledger
Created by Guest
Created on Dec 4, 2025

RELATED IDEAS

Format G/L Detail Date/DB/CR Total Report

Currently, the G/L Detail Date/DB/CR Total report downloads in a raw format, lacking basic formatting such as column widths, bold headers, and borders. This makes it harder to read and always requires post-formatting by the user to be usable in Excel.

The proposal is to enhance the XLSX export by applying a clean, user-friendly design.

Additionally, I suggest giving users the option to toggle G/L Account block grouping based on their needs. This improvement would allow users to more easily apply custom calculations and analysis directly in Excel, making the ADG system more adaptable to the diverse needs of different organizations.

To demonstrate the concept, below is a JS/HTML-based tool that allows users to drop the raw CSV file and automatically generate a formatted Excel file with a separate tab for the styled report. (Note: I was unable to attach the HTML file directly in this submission.)


<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8" />
<title>GL Detail — Edit Sheet Formatter</title>
<meta name="viewport" content="width=device-width, initial-scale=1" />
<style>
:root { --bg:#ffffff; --text:#111827; --muted:#6b7280; --drop:#e5e7eb; --border:#d1d5db; --gray:#f3f4f6; }
html, body { margin:0; padding:0; height:100%; background:var(--bg); color:var(--text);
font-family: Inter, ui-sans-serif, system-ui, Segoe UI, Roboto, Arial, sans-serif; }
.wrap { max-width: 960px; margin: 40px auto; padding: 0 16px; }
h1 { font-size: 28px; margin: 0 0 6px; font-weight: 700; }
.sub { color: var(--muted); margin-bottom: 20px; line-height:1.5; }
.card { border:1px solid var(--border); border-radius:14px; padding:18px; box-shadow:0 6px 20px rgba(0,0,0,.06); background:#fff; }
.drop { border:2px dashed #9ca3af; background: var(--drop); border-radius:14px; padding:26px; text-align:center; transition:.18s ease; cursor:pointer; user-select:none; }
.drop:hover, .drop.hover { border-color:#6b7280; transform: translateY(-1px); }
.drop h2 { margin:0 0 6px; font-size:18px; }
.hint { color: var(--muted); font-size:14px; }
.browse { display:inline-block; margin-top:12px; background:#111827; color:#fff; border:none;
padding:10px 14px; border-radius:10px; font-weight:600; cursor:pointer; }
.status { margin-top:14px; font-size:14px; color:#065f46; min-height:20px; white-space:pre-line; }
input[type="file"] { display:none; }
.footer { margin-top:10px; font-size:12px; color:var(--muted); }
.mono { font-family: ui-monospace, SFMono-Regular, Menlo, Consolas, "Liberation Mono", monospace; }
</style>
</head>
<body>
<div class="wrap">
<h1>GL Detail — Edit Sheet Formatter By G/L Accounts Blocks</h1>
<div class="sub">
Drop your GL Detail Report bellow.
</div>

<div class="card">
  <div id="drop" class="drop" tabindex="0">
    <h2>Drop your <span class="mono">.xlsx</span> file here</h2>
    <div class="hint">…or click to choose a file</div>
    <button id="browseBtn" class="browse" type="button">Browse...</button>
    <input id="fileInput" type="file" accept=".xlsx,application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" />
  </div>
  <div id="status" class="status"></div>
  <div class="footer">Developed by Daniel Barbosa - VSO #9952.</div>
</div>

</div>


<script src="https://cdn.jsdelivr.net/npm/exceljs@4.4.0/dist/exceljs.min.js"></script>

<script>
const drop = document.getElementById('drop');
const fileInput = document.getElementById('fileInput');
const browseBtn = document.getElementById('browseBtn');
const statusEl = document.getElementById('status');

const setStatus = (m) => statusEl.textContent = m || '';
const clearStatus = () => setStatus('');

// Drag & drop
['dragenter','dragover'].forEach(ev =>
drop.addEventListener(ev, e => { e.preventDefault(); e.stopPropagation(); drop.classList.add('hover'); })
);
['dragleave','drop'].forEach(ev =>
drop.addEventListener(ev, e => { e.preventDefault(); e.stopPropagation(); if (ev==='dragleave') drop.classList.remove('hover'); })
);
drop.addEventListener('click', () => fileInput.click());
browseBtn.addEventListener('click', () => fileInput.click());

drop.addEventListener('drop', (e) => {
drop.classList.remove('hover');
const file = e.dataTransfer && e.dataTransfer.files[0];
if (file) handleFile(file);
});
fileInput.addEventListener('change', () => {
const file = fileInput.files && fileInput.files[0];
if (file) handleFile(file);
});

// Helpers
const glRegex = /^\d{3}-\d{3}-\d{3}-\d{4}$/;
const accHeaderFill = { type: 'pattern', pattern: 'solid', fgColor: { argb: 'FFD1D5DB' } }; // light gray
const borderAll = { top:{style:'thin'}, left:{style:'thin'}, bottom:{style:'thin'}, right:{style:'thin'} };
const accountingFmt = '($* #,##0.00);($* (#,##0.00);($* "-"??);(@_)';

function toPlain(v){
if (v === undefined || v === null) return null;
if (typeof v === 'object') {
if ('result' in v && v.result !== undefined) return v.result;
if ('text' in v && v.text !== undefined) return v.text;
if ('richText' in v && Array.isArray(v.richText)) return v.richText.map(rt=>rt.text).join('');
}
return v;
}
function yyyyMmDdToDate(s){
const m = /^([0-9]{4})-([0-9]{2})-([0-9]{2})$/.exec(String(s).trim());
if (!m) return null;
return new Date(+m[1], +m[2]-1, +m[3]);
}
function lastRowWithValue(ws, col){
let last = ws.rowCount || 0;
while (last > 0) {
const v = toPlain(ws.getRow(last).getCell(col).value);
if (v !== null && String(v).trim() !== '') return last;
last--;
}
return 0;
}
function makeOutName(name){
const dot = name.lastIndexOf('.');
const base = dot > 0 ? name.slice(0, dot) : name;
return GL_Formatted_${base}.xlsx;
}
function triggerDownload(blob, filename){
const a = document.createElement('a');
a.href = URL.createObjectURL(blob);
a.download = filename;
document.body.appendChild(a);
a.click();
setTimeout(()=>{ URL.revokeObjectURL(a.href); a.remove(); }, 0);
}
const isDash = (v) => typeof v === 'string' && /^-+$/.test(v.trim());

async function handleFile(file){
clearStatus();
setStatus(Reading "${file.name}"…);

try {
  const arrayBuffer = await file.arrayBuffer();

  // Load regardless of extension; error if not valid xlsx
  const srcWb = new ExcelJS.Workbook();
  await srcWb.xlsx.load(arrayBuffer).catch(()=>{ throw new Error("This file doesn't appear to be an .xlsx (Excel Open XML) workbook."); });

  const srcWs = srcWb.worksheets.find(s => !s.state || s.state === 'visible') || srcWb.worksheets[0];
  if (!srcWs) return setStatus('No worksheet found in this file.');

  // Copy to array for "Raw data"
  const srcVals = srcWs.getSheetValues();
  const rawVals = [];
  for (let r=1; r<srcVals.length; r++){
    const row = srcVals[r] || [];
    const arr = [];
    for (let c=1; c<row.length; c++) arr.push(toPlain(row[c]));
    rawVals.push(arr);
  }

  // Build output
  const outWb = new ExcelJS.Workbook();

  // Raw data
  const rawWs = outWb.addWorksheet('Raw data');
  for (const r of rawVals) rawWs.addRow(r);

  // Edit (start as copy)
  const editWs = outWb.addWorksheet('Edit');
  for (const r of rawVals) editWs.addRow(r);

    // --- Step 2: header rows + B1/B2 and dynamic meta line -> B3 ---
    editWs.spliceRows(1, 0, [], [], [], []); // add 4 rows at top
    editWs.getCell('B1').value = 'Volusia Sheriffs Office';
    editWs.getCell('B1').font = { bold: true, size: 12 };
    editWs.getCell('B2').value = 'GL Detail Formatted';
    editWs.getCell('B2').font = { bold: true };

    /* Find the “meta” line (e.g., "Fiscal Year: ...") that can appear in A5..A25 or B5..B25 */
    function pickMetaCell(ws){
    let fallback = null;
    for (let r = 5; r <= 25; r++) {
        for (let c of [1, 2]) { // A or B
        const v = toPlain(ws.getRow(r).getCell(c).value);
        if (typeof v === 'string') {
            const s = v.trim();
            if (!s) continue;
            if (/fiscal\s*year/i.test(s)) return { r, c, text: s };
            if (!fallback && s.length > 10) fallback = { r, c, text: s }; // remember first long-ish line
        }
        }
    }
    return fallback; // may be null
    }

    const meta = pickMetaCell(editWs);
    if (meta) {
    editWs.getCell('B3').value = meta.text;
    editWs.getCell('B3').font = { italic: true };
    editWs.getRow(meta.r).getCell(meta.c).value = null; // clear the original
    }


  // --- Step 3: remove Description/Account Totals/Grand Totals by Column B ---
  const lastB = lastRowWithValue(editWs, 2);
  const removeSet = new Set(['Description','** ACCOUNT TOTALS **','*** GRAND TOTALS ***']);
  for (let r = lastB; r >= 5; r--) {
    const v = toPlain(editWs.getRow(r).getCell(2).value);
    if (typeof v === 'string' && removeSet.has(v.trim())) {
      editWs.spliceRows(r, 1);
    }
  }

  // --- Step 4: move Column A -> B where B empty; then delete A ---
  let lastRow = lastRowWithValue(editWs, 2);
  for (let r = 5; r <= lastRow; r++) {
    const a = editWs.getRow(r).getCell(1);
    const av = toPlain(a.value);
    if (av !== null && String(av).trim() !== '') {
      const b = editWs.getRow(r).getCell(2);
      if (toPlain(b.value) === null || String(toPlain(b.value)).trim()==='') {
        b.value = av; a.value = null;
      }
    }
  }
  editWs.spliceColumns(1, 1);       // drop old col A
  lastRow = lastRowWithValue(editWs, 1); // new A is previous B

  // --- Step 5: G/L headers and YYYY-MM-DD → real date ---
  let r = 1;
  while (r <= lastRow) {
    const cellA = editWs.getRow(r).getCell(1);
    const s = (toPlain(cellA.value) ?? '').toString().trim();

    if (glRegex.test(s)) {
      // Insert blank at row start (shift row right by one)
      const rowObj = editWs.getRow(r);
      const rowVals = [];
      for (let c=1; c<=rowObj.cellCount; c++) rowVals.push(toPlain(rowObj.getCell(c).value));
      rowVals.unshift('');
      editWs.spliceRows(r, 1, rowVals);

      // Style A..C and set title
      const rNow = editWs.getRow(r);
      rNow.getCell(1).value = 'G/L Account';
      rNow.getCell(1).font = { bold: true };
      rNow.getCell(2).font = { bold: true };
      rNow.getCell(3).font = { bold: true };
      [1,2,3].forEach(c=>{
        const cell = rNow.getCell(c);
        cell.fill = accHeaderFill;
        cell.border = borderAll;
      });

      // Sub-header
      const hdr = ['Batch_Date','Batch_#','V/C#_Reference','Debits','Credits'];
      editWs.spliceRows(r+1, 0, hdr);
      const hdrRow = editWs.getRow(r+1);
      for (let c=1; c<=hdr.length; c++) {
        const cell = hdrRow.getCell(c);
        cell.font = { bold: true };
        cell.fill = accHeaderFill;
        cell.border = borderAll;
      }

      r += 2;        // below header
      lastRow += 1;  // we inserted one row
      continue;
    }

    if (/^\d{4}-\d{2}-\d{2}$/.test(s)) {
      const dt = yyyyMmDdToDate(s);
      if (dt) { cellA.value = dt; cellA.numFmt = 'mm/dd/yyyy'; }
    }
    r++;
  }

  // --- Step 6: clear F; widths; D/E accounting ---
  const maxLast = Math.max(
    lastRowWithValue(editWs,1), lastRowWithValue(editWs,2),
    lastRowWithValue(editWs,3), lastRowWithValue(editWs,4),
    lastRowWithValue(editWs,5), lastRowWithValue(editWs,6)
  );
  for (let rr=1; rr<=maxLast; rr++) editWs.getRow(rr).getCell(6).value = null;

  editWs.getColumn(1).width = 15; // A
  editWs.getColumn(2).width = 16; // B
  editWs.getColumn(3).width = 65; // C
  editWs.getColumn(4).width = 15; // D
  editWs.getColumn(5).width = 15; // E

  for (let rr=1; rr<=maxLast; rr++) {
    const d = editWs.getRow(rr).getCell(4);
    const e = editWs.getRow(rr).getCell(5);
    if (d.value !== null && d.value !== '' && typeof d.value !== 'string') d.numFmt = accountingFmt;
    if (e.value !== null && e.value !== '' && typeof e.value !== 'string') e.numFmt = accountingFmt;
  }

  // --- Step 7: zero-fill D/E if only one side present ---
  for (let rr=1; rr<=maxLast; rr++) {
    const d = editWs.getRow(rr).getCell(4);
    const e = editWs.getRow(rr).getCell(5);
    const dHas = d.value !== null && d.value !== '' && !Number.isNaN(Number(d.value));
    const eHas = e.value !== null && e.value !== '' && !Number.isNaN(Number(e.value));
    if (!dHas && eHas) { d.value = 0; d.numFmt = accountingFmt; }
    if (!eHas && dHas) { e.value = 0; e.numFmt = accountingFmt; }
  }

  // --- NEW: Step 8 - Column alignment & date style you requested ---
  // Column A short date & centered; Column B centered
  editWs.getColumn(1).numFmt = 'mm/dd/yyyy';
  editWs.getColumn(1).alignment = { horizontal: 'center' };
  editWs.getColumn(2).alignment = { horizontal: 'center' };
  // A1..A4 left-aligned
  for (let i=1; i<=4; i++){
    editWs.getCell(`A${i}`).alignment = { horizontal: 'left' };
  }

  // --- NEW: Step 9 - Auto-SUM at dashed rows in D/E with borders ---
  // For any row where D (or E) is "---------------", place SUM from the first data row
  // under the "Debits/Credits" header down to the row above the dashes.
  const topThinBottomDouble = { top:{style:'thin'}, bottom:{style:'double'} };
  for (let rr=1; rr<=maxLast; rr++) {
    const dCell = editWs.getRow(rr).getCell(4);
    const eCell = editWs.getRow(rr).getCell(5);

    // Helper to find the header row (cell text equals "Debits"/"Credits") above rr
    const findHeaderRowUp = (colIndex, headerText) => {
      let i = rr - 1;
      while (i > 0) {
        const v = toPlain(editWs.getRow(i).getCell(colIndex).value);
        if (typeof v === 'string' && v.trim().toLowerCase() === headerText) return i;
        // stop if we hit another GL header row (its A cell is "G/L Account")
        const aVal = toPlain(editWs.getRow(i).getCell(1).value);
        if (typeof aVal === 'string' && aVal.trim() === 'G/L Account') break;
        i--;
      }
      return null;
    };

    // Debits (Column D)
    if (isDash(toPlain(dCell.value))) {
      const hdr = findHeaderRowUp(4, 'debits');
      if (hdr && hdr + 1 <= rr - 1) {
        dCell.value = { formula: `SUM(D${hdr+1}:D${rr-1})` };
        dCell.numFmt = accountingFmt;
        dCell.border = topThinBottomDouble;
      }
    }
    // Credits (Column E)
    if (isDash(toPlain(eCell.value))) {
      const hdr = findHeaderRowUp(5, 'credits');
      if (hdr && hdr + 1 <= rr - 1) {
        eCell.value = { formula: `SUM(E${hdr+1}:E${rr-1})` };
        eCell.numFmt = accountingFmt;
        eCell.border = topThinBottomDouble;
      }
    }
  }

  // Freeze top header area
  editWs.views = [{ state:'frozen', xSplit:0, ySplit:4 }];

  // Save
  setStatus('Formatting…');
  const outName = makeOutName(file.name);
  const outBuffer = await outWb.xlsx.writeBuffer();
  const blob = new Blob([outBuffer], { type: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" });
  triggerDownload(blob, outName);
  setStatus(`Done. Downloaded "${outName}".`);
} catch (err) {
  console.error(err);
  setStatus(err && err.message ? err.message : 'Error processing file.');
}

}
</script>
</body>
</html>

  • Attach files