📖 User Manual

SQL2Excel User Manual

Complete guide covering all SQL2Excel features from installation to advanced functionality

Download Document 📦 GitHub

📖 Table of Contents

🎯 Overview

🛠️ Installation & Setup

🚀 Basic Usage

🎨 Advanced Features

📦 Deployment

📋 Examples

🔧 Troubleshooting

🎯 Overview

What is SQL2Excel?

SQL2Excel is a Node.js-based tool for converting SQL query results into Excel files with advanced styling and template support features.

It helps developers and data analysts easily transform database information into Excel format for reports and analysis materials.

Key Features

  • Multi-sheet Support: Store multiple SQL query results in separate sheets within a single Excel file
  • Template Style System: Pre-defined Excel styling templates for consistent design
  • Multiple DB Connections: Use different database connections for each sheet
  • Enhanced Dynamic Variables: Extract values from database in real-time for advanced processing
  • Standalone Executable: Generate .exe files without Node.js dependency
  • DateTime Variables: 20+ automatic datetime functions
  • Multi-language Support: Korean and English release packages

🆕 New in v1.2.7

🔍

Optimized Validation & Encoding

Enhanced query file validation shows only databases actually used. Fixed character encoding issues in release batch files.

🌏

Internationalized Sample Files

All query sample files converted to English for better global accessibility and cross-platform compatibility.

🔓

Removed Filename Restrictions

Removed unnecessary Korean character validation for filenames, allowing more flexible file naming.

🔄 Key Features from v1.2.6

🌍

Interactive Multilingual Menu

User-friendly interactive menu system in sql2db style with complete multilingual support.

✅

Enhanced Validation System

Full sheet list display, individual validation results, and detailed database information output.

🔧

pkg Build Optimization

Improved native module compatibility, file size optimization through compression, and stable operation in pkg environment.

🔄 Key Features from v1.2.5

📋

Improved Batch Interface

Changed to numbered selection system for more intuitive and user-friendly file selection.

✅

Enhanced Input Validation

Added validation for file selection numbers to prevent incorrect inputs.

🔍

Automatic File Type Detection

Automatically detects XML and JSON files and displays them separately in selection menus.

🔄 Key Features from v1.2.4

📦

Standalone Executable

Generate versioned standalone .exe files (sql2excel-v1.2.5.exe) that don't require Node.js installation.

⏰

Enhanced DateTime Variables

20+ automatic datetime variables including UTC, KST, Korean localized formats, and compact formats.

🕒

Creation Timestamp

Automatically display creation timestamp on each Excel sheet with Korean locale formatting.

🌐

Multi-language Packages

Automated Korean and English release package generation with localized interfaces.

📋

SQL Query Formatting

Preserve original SQL formatting with line breaks in Table of Contents for better readability.

📊
SQL Query
→
⚙️
SQL2Excel
→
📋
Excel File

🛠️ Installation & Setup

1

System Requirements

  • Windows 10 or higher (64-bit)
  • SQL Server 2012 or higher
  • Appropriate database permissions
  • No Node.js installation required ✨
2

Download & Extract

Download the latest release package:

📦 sql2excel-v1.2.7-win-x64.zip

Complete standalone package with executable and all required files

Download
steps 
1. Download sql2excel-v1.2.7-win-x64.zip
2. Extract to your desired directory (e.g., C:\sql2excel\)
3. Edit the config/dbinfo.json file to configure your database connection
4. Refer to the sample query files in the queries/ folder to write your own query files.
5. Run run.bat (English) or 실행하기.bat (Korean) to use the interactive menu.
3

Package Contents

sql2excel-v1.2.7-win-x64/
├── sql2excel-v1.2.7.exe    # Standalone executable
├── run.bat                 # Interactive menu (English, --lang=en)
├── 실행하기.bat             # Interactive menu (Korean, --lang=kr)
├── config/
│   └── dbinfo.json         # Database configuration
├── queries/                # Sample query files
│   ├── datetime-variables-example.xml
│   ├── queries-sample.xml
│   └── ...
├── templates/              # Excel style templates
│   └── excel-styles.xml
├── user_manual/            # Documentation
│   ├── README_KR.md
│   ├── README.md
│   ├── CHANGELOG_KR.md
│   └── CHANGELOG.md
└── RELEASE_INFO.txt        # Release information
4

Database Configuration

Edit the config/dbinfo.json file to configure your database connection:

json 
{
  "dbs": {
    "sampleDB": {
      "server": "localhost",
      "port": 1433,
      "database": "SampleDB",
      "user": "sa",
      "password": "yourpassword",
      "options": {
        "encrypt": false,
        "trustServerCertificate": true
      }
    }
  }
}

🚀 Basic Usage

Basic CLI Command Usage

🖱️

Method 1: Interactive Menu System (Recommended)

Double-click run.bat (English) or 실행하기.bat (Korean) for a user-friendly menu interface

batch 
# English menu
run.bat

# Korean menu
실행하기.bat

Interactive Menu Options:

  • 1. Validate Query Definition File - Detailed sheet list and validation results
  • 2. Test Database Connection - Display detailed DB information
  • 3. Generate Excel File (XML) - Numbered file selection
  • 4. Generate Excel File (JSON) - Numbered file selection
  • 5. Show Help
  • 0. Exit
⚡

Method 2: Direct Command Line

Use the standalone executable directly

Basic Excel Generation
sql2excel-v1.2.6.exe export --xml queries/sample-queries.xml
Using Template Styles
sql2excel-v1.2.6.exe export --xml queries/sample-queries.xml --style modern
Validate Configuration (Detailed Output)
sql2excel-v1.2.6.exe validate --xml queries/sample-queries.xml
List Available Styles
sql2excel-v1.2.6.exe list-styles

XML Configuration File Structure

xml 
<?xml version="1.0" encoding="UTF-8"?>
<queries maxRows="10000">
  <!-- Excel file configuration -->
  <excel db="sampleDB" output="output/report.xlsx" style="modern">
  </excel>
  
  <!-- Variable definitions -->
  <vars>
    <var name="year">2024</var>
    <var name="month">12</var>
  </vars>
  
  <!-- Sheet definitions -->
  <sheet name="SalesReport" use="true" aggregateColumn="Region">
    <![CDATA[
      SELECT Region, SUM(Amount) as TotalAmount
      FROM Sales 
      WHERE YEAR(Date) = ${year}
      GROUP BY Region
    ]]>
  </sheet>
</queries>

JSON Configuration File Structure

json 
{
  "excel": {
    "db": "sampleDB",
    "output": "output/report.xlsx",
    "style": "modern"
  },
  "vars": {
    "year": "2024",
    "month": "12"
  },
  "sheets": [
    {
      "name": "SalesReport",
      "use": true,
      "aggregateColumn": "Region",
      "query": "SELECT Region, SUM(Amount) as TotalAmount FROM Sales WHERE YEAR(Date) = ${year} GROUP BY Region"
    }
  ]
}

🎨 Advanced Features

🎨

Template Style System

Provides consistent design with 7 pre-defined styles.

Default Modern Dark Colorful Minimal Business Premium
🔄

Enhanced Dynamic Variables

Supports advanced processing by extracting values from database in real-time.

  • column_identified type
  • key_value_pairs type
  • Pre-execution variable processing
  • Debug mode support
📋

Table of Contents

Generate table of contents Sheet for enhanced report management and sharing

  • Sheet-specific record count display (template available)
  • Sheet-specific applied SQL query display

📋 Practical Examples

📊 Sales Report Generation

Example of generating Excel reports by analyzing monthly sales data.

xml 
<sheet name="MonthlySales_${year}" use="true" style="business">
  <![CDATA[
    SELECT 
      MONTH(OrderDate) as Month,
      DATENAME(MONTH, OrderDate) as MonthName,
      COUNT(*) as OrderCount,
      SUM(TotalAmount) as TotalSales
    FROM Orders 
    WHERE YEAR(OrderDate) = ${year}
    GROUP BY MONTH(OrderDate), DATENAME(MONTH, OrderDate)
    ORDER BY Month
  ]]>
</sheet>

👥 Customer Analysis Report

Example of customer purchase pattern analysis report.

json 
{
  "name": "CustomerAnalysis",
  "use": true,
  "style": "premium",
  "query": "SELECT c.CustomerName, COUNT(o.OrderID) as OrderCount, SUM(o.TotalAmount) as TotalPurchase FROM Customers c LEFT JOIN Orders o ON c.CustomerID = o.CustomerID GROUP BY c.CustomerID, c.CustomerName ORDER BY TotalPurchase DESC"
}

🔄 Dynamic Variable Usage

Example of using values retrieved from database in real-time for queries.

xml 
<dynamicVars>
  <dynamicVar name="activeCustomers">
    <![CDATA[
      SELECT CustomerID FROM Customers WHERE IsActive = 1
    ]]>
  </dynamicVar>
</dynamicVars>

<sheet name="ActiveCustomerOrders">
  <![CDATA[
    SELECT * FROM Orders 
    WHERE CustomerID IN (${activeCustomers.CustomerID})
  ]]>
</sheet>

🔧 Troubleshooting

❌ Connection Error

Issue: Database connection fails.

Solution:

  • Check connection information in config/dbinfo.json file
  • Verify SQL Server service is running
  • Check firewall settings
  • Verify user permissions

⚠️ Korean Filename Warning

Issue: Warning appears when using Korean filenames.

Solution:

  • Use English filenames when possible
  • Avoid special characters in filenames
  • Use underscore(_) instead of spaces

🔍 Variable Substitution Error

Issue: Dynamic variables are not properly substituted.

Solution:

  • Set DEBUG_VARIABLES=true environment variable
  • Check variable query results
  • Verify variable names and usage
  • Check database permissions

📊 Large Data Processing

Issue: Memory shortage error occurs when processing large data.

Solution:

  • Limit row count using maxRows property
  • Optimize queries
  • Utilize indexes
  • Consider batch processing

❓ Frequently Asked Questions

Q: What databases does SQL2Excel support?

+

A: Currently supports SQL Server 2012 and above. Support for other databases like MySQL and PostgreSQL is planned for the future.

Q: Can I customize template styles?

+

A: Yes, you can modify the templates/excel-styles.xml file to add new styles or modify existing ones.

Q: How is performance with large data processing?

+

A: You can limit the number of rows to process using the maxRows property, and memory efficiency is improved through streaming processing.

Q: Is automated report generation possible?

+

A: Yes, you can automate regular report generation using Windows batch files or schedulers.

⏰ DateTime Variables

SQL2Excel v1.2.4 introduces 20+ automatic datetime variables for real-time timestamp generation in various formats.

Basic DateTime Functions

Variable Description Example Output
${CURRENT_TIMESTAMP} Current UTC timestamp 2024-10-05 15:30:45
${KST_NOW} Korean Standard Time 2024-10-06 00:30:45
${KOREAN_DATE} Korean date format 2024년 10월 6일
${DATE_YYYYMMDD} Compact date format 20241006

Usage Examples

-- In XML queries
<vars>
  <var name="reportDate">${KOREAN_DATE}</var>
  <var name="currentTime">${KST_NOW}</var>
</vars>

-- In file naming
<excel output="report_${DATE_YYYYMMDD}_${DATETIME_YYYYMMDD_HHMMSS}.xlsx">

🕒 Creation Timestamp

SQL2Excel automatically adds creation timestamps to each generated Excel sheet, providing clear audit trails.

Automatic Display

Each Excel sheet includes database source information and creation timestamp

Korean Locale

Timestamps use Korean locale formatting (2024년 10월 5일 토요일 오후 11:30:25)

Visual Styling

Blue background with white bold text for consistent formatting

No Configuration

Works automatically without any setup required

📦 Standalone Executable

Generate versioned standalone .exe files for distribution without Node.js dependency.

Building Executable

# Build versioned executable
npm run build

# Creates: dist/sql2excel-v1.2.6.exe

Features

  • Self-contained: All Node.js dependencies bundled
  • Versioned filename: sql2excel-v1.2.6.exe format
  • Asset bundling: Templates and styles included
  • No installation required: Run directly on any Windows system
  • Optimized: File size optimization through GZip compression
  • Native modules: Proper operation of mssql, tedious, and other native modules

🌐 Multi-language Support

Automated Korean and English release package generation with localized interfaces.

Unified Release Package

📦 sql2excel-v1.2.6-win-x64

  • run.bat (English) / 실행하기.bat (Korean) interactive menus
  • Language-specific batch files automatically apply --lang option
  • Both Korean and English documentation included
  • RELEASE_INFO.txt (release information)

Creating Release Package

# Generate release package
npm run release

# Creates:
# release/sql2excel-v1.2.6-win-x64/
# release/sql2excel-v1.2.6-win-x64.zip

Multi-language Support Features

  • Interactive menu: app.js-based multilingual menu system
  • Command-line option: --lang=en (English), --lang=kr (Korean)
  • Auto-selection: Language automatically set by run.bat and 실행하기.bat
  • Full localization: All menus, messages, and errors display in selected language

📞 Support & Contact

📧

Email Support

For technical issues or inquiries, please contact us via email.

Send Email
🐛

Issue Reporting

Report bugs or request features through GitHub issues.

Report Issue
📚

Documentation & Examples

Find more detailed information and examples in the GitHub repository.

Visit GitHub