SQL2Excel User Manual

🎯 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-Database Support: Supports ORACLE, PostgreSQL, MSSQL, MySQL, MariaDB, SQLite
📊 Multi-sheet Support: Save multiple SQL query results in separate sheets within one Excel file
🎨 Template Style System: Pre-defined Excel styling templates with 7 built-in styles
🔗 Multiple DB Connections: Use different database connections for each sheet
📝 Variable System: Use variables for dynamic query generation
🔄 Enhanced Dynamic Variables: Fetch values from DB in real-time for advanced processing
🔄 Query Reuse: Define common queries and reuse across multiple sheets
⚙️ Parameter Overrides: Override query definition parameters per sheet
📋 Auto Table of Contents: Auto-generate TOC sheet with hyperlinks
📊 Aggregation: Auto count by specified column values
🚦 Query Limits: Limit row count for large datasets
🖥️ CLI Interface: Simple command-line usage
🪟 Windows Batch: Interactive batch files for Windows users
📄 XML/JSON Support: Flexible configuration file formats
🎯 Per-sheet Styling: Apply different styles per sheet
📦 Standalone Executable: Build distributable .exe without Node.js dependency
🌐 Multi-language Support: Korean and English release packages
🔧 Release Automation: Auto-generate release packages with proper docs
🕒 Creation Timestamp: Show creation timestamp on each Excel sheet
⏰ Enhanced DateTime Variables: 22 timezones supported with custom formats
📋 SQL Query Formatting: Preserve original SQL with line breaks in TOC
🔧 Input Validation: Auto trim whitespace in file path inputs

🛠️ Installation & Setup

1. System Requirements

Windows 10 or higher (64-bit)
Database server (MSSQL/MySQL/MariaDB/PostgreSQL/SQLite/Oracle as applicable)
Appropriate database permissions
No Node.js installation required ✨

2. Download & Extract

Download the latest release package:

📦 sql2excel-v2.1.5-win-x64.zip

steps

1. Download sql2excel-v2.1.5-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-v2.1.5-win-x64/
├── sql2excel-v2.1.5.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-v2.1.5.exe export --xml queries/sample-queries.xml

Using Template Styles

sql2excel-v2.1.5.exe export --xml queries/sample-queries.xml --style modern

Validate Configuration (Detailed Output)

sql2excel-v2.1.5.exe validate --xml queries/sample-queries.xml

List Available Styles

sql2excel-v2.1.5.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"
    }
  ]
}

📋 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: v2.1.5 supports multiple databases: ORACLE, PostgreSQL, MSSQL, MySQL, MariaDB, and SQLite. You can select DB at runtime per workbook, per sheet, or per dynamic variable. Use list-dbs to verify configured connections.

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.9 supports 22 timezones worldwide with flexible custom date/time formatting.

With Timezone (Recommended): ${DATE.<TIMEZONE>:format}

Without Timezone (Local Time): ${DATE:format}

💡 When timezone is omitted, server's local time is used. Timezone specification is recommended for global consistency.

Supported Timezones (22)

Timezone Code	Description	UTC Offset	Region
UTC	Coordinated Universal Time	UTC+0	Global Standard
GMT	Greenwich Mean Time	UTC+0	United Kingdom
KST	Korea Standard Time	UTC+9	South Korea
JST	Japan Standard Time	UTC+9	Japan
CST	China Standard Time	UTC+8	China
PHT	Philippine Time	UTC+8	Philippines
ICT	Indochina Time	UTC+7	Thailand, Vietnam
CET	Central European Time	UTC+1	Germany, France, Italy, Poland
EST	Eastern Standard Time	UTC-5	US East Coast
AST	Atlantic Standard Time	UTC-4	Eastern Canada
CST_US	Central Standard Time	UTC-6	US, Canada, Mexico Central
PST	Pacific Standard Time	UTC-8	US West Coast

Others: SGT, AEST, IST, GST, EET, MST, AKST, HST, BRT, ART

Common Format Examples

Format	Output Example	Use Case
`${DATE.UTC:YYYY-MM-DD}`	2024-10-21	Standard date format
`${DATE.KST:YYYY년 MM월 DD일}`	2024년 10월 22일	Korean date
`${DATE.EST:YYYY-MM-DD HH:mm:ss}`	2024-10-21 10:30:45	US East Coast time
`${DATE.UTC:YYYYMMDD_HHmmss}`	20241021_153045	Filename-friendly format

Usage Examples

-- In XML queries
<vars>
  <var name="reportDate">${DATE.KST:YYYY년 MM월 DD일}</var>
  <var name="currentTime">${DATE.KST:YYYY-MM-DD HH:mm:ss}</var>
</vars>

-- In file naming (with timezone)
<excel output="report_${DATE.UTC:YYYYMMDD}_${DATE.UTC:HHmmss}.xlsx">

-- In file naming (local time)
<excel output="report_${DATE:YYYYMMDD}_${DATE:HHmmss}.xlsx">

-- Global report (multi-timezone)
SELECT 
  'Seoul: ${DATE.KST:YYYY-MM-DD HH:mm:ss}' as Seoul_Time,
  'New York: ${DATE.EST:YYYY-MM-DD HH:mm:ss}' as NewYork_Time,
  'Tokyo: ${DATE.JST:YYYY-MM-DD HH:mm:ss}' as Tokyo_Time

📞 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

📖 Table of Contents

🎯 Overview

🛠️ Installation & Setup

🚀 Basic Usage

🎨 Advanced Features

📦 Deployment

📋 Examples

🔧 Troubleshooting

🎯 Overview

What is SQL2Excel?

Key Features

🛠️ Installation & Setup

1. System Requirements

2. Download & Extract

3. Package Contents

4. Database Configuration

🚀 Basic Usage

Basic CLI Command Usage

Method 1: Interactive Menu System (Recommended)

Method 2: Direct Command Line

Basic Excel Generation

Using Template Styles

Validate Configuration (Detailed Output)

List Available Styles

XML Configuration File Structure

JSON Configuration File Structure

📋 Practical Examples

📊 Sales Report Generation

👥 Customer Analysis Report

🔄 Dynamic Variable Usage

🔧 Troubleshooting

❌ Connection Error

⚠️ Korean Filename Warning

🔍 Variable Substitution Error

📊 Large Data Processing

❓ Frequently Asked Questions

Q: What databases does SQL2Excel support?

Q: Can I customize template styles?

Q: How is performance with large data processing?

Q: Is automated report generation possible?

⏰ DateTime Variables

Supported Timezones (22)

Common Format Examples

Usage Examples

📞 Support & Contact

Email Support

Issue Reporting

Documentation & Examples