Name	Name	Last commit message	Last commit date
Latest commit History 165 Commits
config	config
queries	queries
resources	resources
src	src
templates	templates
test	test
.gitignore	.gitignore
CHANGELOG.md	CHANGELOG.md
CHANGELOG_KR.md	CHANGELOG_KR.md
LICENSE	LICENSE
README.md	README.md
README_KR.md	README_KR.md
USER_MANUAL.md	USER_MANUAL.md
USER_MANUAL_KR.md	USER_MANUAL_KR.md
app.js	app.js
create-release.js	create-release.js
package-lock.json	package-lock.json
package.json	package.json
run.bat	run.bat
test-final-datetime.json	test-final-datetime.json
실행하기.bat	실행하기.bat

SQL2Excel - Generate Excel Files from SQL Query Results

A Node.js-based tool for generating Excel files from SQL query results.

Key Features

📊 Multi-Sheet Support: Save multiple SQL query results in separate sheets within one Excel file
🎨 Template Style System: Pre-defined Excel styling templates for consistent design with 7 built-in styles
🔗 Multiple DB Connections: Use different database connections for each sheet
📝 Variable System: Use variables in queries for dynamic query generation
🔄 Enhanced Dynamic Variables: Extract values from database in real-time with advanced processing
🔄 Query Reuse: Define common queries and reuse them across multiple sheets
⚙️ Parameter Override: Override query definition parameters for each sheet with different values
📋 Auto Table of Contents: Automatically generate table of contents sheet with hyperlinks
📊 Aggregation Features: Automatic aggregation and display of counts by specified column values
🚦 Query Limits: Row count limiting for large data processing
🖥️ CLI Interface: Simple command-line tool execution
🪟 Windows Batch Files: Interactive batch files for Windows users
📄 XML/JSON Support: Flexible configuration file format support
🎯 Sheet-specific Styling: Apply different styles to individual sheets
📦 Standalone Executable: Generate standalone .exe files for distribution without Node.js dependency
🌐 Multi-language Support: Korean and English release packages
🔧 Release Automation: Automated release package generation with proper documentation
🕒 Creation Timestamp: Display creation timestamp on each Excel sheet
⏰ Custom DateTime Variables: Support for 22 timezones worldwide with custom format (${DATE.UTC:YYYY-MM-DD}, ${DATE.KST:YYYY년 MM월 DD일}, ${DATE.EST:YYYY-MM-DD HH:mm}, etc.) or use local time (${DATE:YYYY-MM-DD})
📋 SQL Query Formatting: Preserve original SQL formatting with line breaks in Table of Contents
🔧 Input Validation: Automatic whitespace trimming for file path inputs
🗂️ Filename Variables: Use ${DATE:...}, ${DATE.TZ:...}, and ${DB_NAME} in excel.output (also supports custom $(DB_NAME})

v1.3.1 Highlights

Filename variables in output path
- Support ${DB_NAME} (current DB key), custom syntax $(DB_NAME} normalized automatically
- Support ${DATE:...} (local time) and ${DATE.TZ:...} (explicit timezone) in filenames
- Lowercase date tokens supported: yyyy, yy, dd, d, hh, h, sss
- Removed auto _yyyymmddhhmmss suffix; control naming via DATE variables

v1.3.0 Highlights

Per-sheet export routing by extension
- .xlsx / .xls → Generate a single Excel workbook (existing behavior)
- .csv → Generate per-sheet CSV files
- All other extensions (e.g., .txt, .log, .data, .sql, etc.) → Generate per-sheet TXT files (tab-delimited)
Directory and filename rules for per-sheet export
- Output directory: <output_basename>_<ext> (no dot). Example: output="d:/temp/report.csv" → d:/temp/report_csv/
- Each sheet becomes a separate file named after the sheet's originalName
- No 31-character truncation for CSV/TXT (Excel-only limit). Filenames sanitized and capped at 100 chars
Format defaults
- CSV: comma, UTF-8 with BOM, headers, CRLF
- TXT: tab, UTF-8 with BOM, headers, CRLF

Previously in v1.2.11

Validation warning for sheet names > 31 chars; note about Excel truncation
TOC: Added "Original Name" column; removed note tooltip

Previously in v1.2.10

Non-interactive CLI: Run tasks directly with app.js --mode (no menu)
- Modes: validate, test, export, help
- Works in both Node and packaged EXE

Non-interactive CLI (New)

Node.js

# Validate query definition
node app.js --mode=validate --xml=./queries/sample-queries.xml
# or JSON
node app.js --mode=validate --query=./queries/sample-queries.json
# Test DB connections
node app.js --mode=test
# Export Excel
node app.js --mode=export --xml=./queries/sample-queries.xml
# or JSON
node app.js --mode=export --query=./queries/sample-queries.json
# Help
node app.js --mode=help

Standalone EXE

sql2excel.exe --mode=validate --xml=./queries/sample-queries.xml
sql2excel.exe --mode=test
sql2excel.exe --mode=export --xml=./queries/sample-queries.xml
sql2excel.exe --mode=help

🚀 Quick Start

🛠️ Installation and Setup

1. System Requirements

For Development/Source Code Usage

Node.js 16.0 or higher
SQL Server 2012 or higher
Appropriate database permissions

For Standalone Executable Usage

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

2. Installation Options

Option A: Development Installation

# Clone or download the source code
npm install
# Build standalone executable (optional)
npm run build

Option B: Standalone Executable

Download the release package from the releases section
Extract to your desired directory
Run interactive menu:
- English: Run run.bat
- Korean: Run 실행하기.bat
Or use sql2excel-v{version}.exe directly

3. Database Connection Setup

Create config/dbinfo.json file:

{
 "dbs": {
 "sampleDB": {
 "server": "localhost",
 "port": 1433,
 "database": "SampleDB",
 "user": "sa",
 "password": "yourpassword",
 "options": {
 "encrypt": false,
 "trustServerCertificate": true
 }
 },
 "erpDB": {
 "server": "erp-server.com",
 "port": 1433,
 "database": "ERP_Database",
 "user": "erp_user",
 "password": "erp_password",
 "options": {
 "encrypt": true,
 "trustServerCertificate": false
 }
 }
 }
}

🚀 Basic Usage

Language Settings

The tool supports Korean and English through environment variables:

Development Environment

English: Run run.bat (automatically sets LANGUAGE=en)
Korean: Run 실행하기.bat (automatically sets LANGUAGE=kr)

Release Package

English: Run run.bat
Korean: Run 실행하기.bat

💡 Note: Language is controlled via the LANGUAGE environment variable. Default is English (en).

Method 1: Interactive Batch File (Recommended for Windows Users)

Development Environment

# English version
run.bat
# Korean version
실행하기.bat

Release Package

# English version
run.bat
# Korean version
실행하기.bat

The interactive menu provides:

Validate Query Definition File - Check your XML/JSON files for errors
Test Database Connection - Verify database connectivity
Generate Excel File (XML File) - Export using XML query definitions
Generate Excel File (JSON File) - Export using JSON query definitions
Show Help - Display detailed help information

Method 2: Direct CLI Command Execution

For Development (Node.js)

# Using XML query file
node src/excel-cli.js export --xml ./queries/sample-queries.xml
# Using JSON query file
node src/excel-cli.js export --query ./queries/sample-queries.json
# Execute with variables
node src/excel-cli.js export --xml ./queries/sample-queries.xml --var "year=2024" --var "dept=IT"
# Using template style
node src/excel-cli.js export --xml ./queries/sample-queries.xml --style modern

For Standalone Executable

# Using XML query file
sql2excel.exe export --xml ./queries/sample-queries.xml
# Using JSON query file
sql2excel.exe export --query ./queries/sample-queries.json
# Execute with variables
sql2excel.exe export --xml ./queries/sample-queries.xml --var "year=2024" --var "dept=IT"
# Using template style
sql2excel.exe export --xml ./queries/sample-queries.xml --style modern

Method 3: NPM Scripts (Development Only)

# Export to Excel
npm run export -- --xml ./queries/sample-queries.xml
# Validate configuration
npm run validate -- --xml ./queries/sample-queries.xml
# Test database connection
npm run list-dbs
# Build standalone executable
npm run build
# Create release package
npm run release

Common Commands

Validate Query File

# Development
node src/excel-cli.js validate --xml ./queries/sample-queries.xml
# Standalone
sql2excel.exe validate --xml ./queries/sample-queries.xml

Test Database Connection

# Development
node src/excel-cli.js list-dbs
# Standalone
sql2excel.exe list-dbs

List Available Template Styles

# Development
node src/excel-cli.js list-styles
# Standalone
sql2excel.exe list-styles

📚 Documentation

For detailed usage and advanced features, refer to the following documents:

📖 User Manual - Complete usage guide
📋 Version History - Version-specific changes

💡 Usage Examples

XML Configuration File Example (with Dynamic Variables)

<queries>
 <excel db="sampleDB" output="output/SalesReport.xlsx">
 <header>
 <font name="Arial" size="12" color="FFFFFF" bold="true"/>
 <fill color="4F81BD"/>
 </header>
 </excel>
 
 <!-- Regular variables -->
 <vars>
 <var name="startDate">2024年01月01日</var>
 <var name="endDate">2024年12月31日</var>
 </vars>
 
 <!-- Dynamic variables -->
 <dynamicVars>
 <dynamicVar name="activeCustomers" description="Active customer list">
 <![CDATA[
 SELECT CustomerID, CustomerName, Region
 FROM Customers WHERE IsActive = 1
 ]]>
 </dynamicVar>
 </dynamicVars>
 
 <sheet name="MonthlySales" use="true" aggregateColumn="Month">
 <![CDATA[
 SELECT MONTH(OrderDate) as Month, SUM(TotalAmount) as Sales
 FROM Orders 
 WHERE YEAR(OrderDate) = 2024
 AND CustomerID IN (${activeCustomers.CustomerID})
 GROUP BY MONTH(OrderDate)
 ]]>
 </sheet>
</queries>

Variable Usage Example

node src/excel-cli.js export --xml ./queries/sales-report.xml \
 --var "startDate=2024年01月01日" \
 --var "endDate=2024年06月30日"

Dynamic Variables

The tool supports dynamic variables that can extract data at runtime and use it in queries:

Variable Types

Type	Description	Access Pattern	Default
`column_identified`	Extract all columns as arrays keyed by column name	`${varName.columnName}`	✅ Yes
`key_value_pairs`	Extract first two columns as key-value pairs	`${varName.key}`	No

Usage Examples

<!-- Using column_identified (default) -->
<dynamicVar name="customerData" description="Customer information">
 <![CDATA[
 SELECT CustomerID, CustomerName, Region FROM Customers
 ]]>
 <!-- type omitted - defaults to column_identified -->
</dynamicVar>
<!-- Using key_value_pairs -->
<dynamicVar name="statusMapping" description="Status mapping">
 <![CDATA[
 SELECT StatusCode, StatusName FROM StatusCodes
 ]]>
 <type>key_value_pairs</type>
</dynamicVar>

-- In your sheet queries
SELECT * FROM Orders 
WHERE CustomerID IN (${customerData.CustomerID})
 AND Status IN (${statusMapping.StatusCode})

🔧 Environment Requirements

Node.js 16.0 or higher
SQL Server 2012 or higher
Appropriate database permissions

📞 Support

Website: www.sql2excel.com
Email: sql2excel.nodejs@gmail.com

License

mrjung72/sql2excel-nodejs

Folders and files

Latest commit

History

Repository files navigation

SQL2Excel - Generate Excel Files from SQL Query Results

Key Features

v1.3.1 Highlights

v1.3.0 Highlights

Previously in v1.2.11

Previously in v1.2.10

Non-interactive CLI (New)

Node.js

Standalone EXE

🚀 Quick Start

🛠️ Installation and Setup

1. System Requirements

For Development/Source Code Usage

For Standalone Executable Usage

2. Installation Options

Option A: Development Installation

Option B: Standalone Executable

3. Database Connection Setup

🚀 Basic Usage

Language Settings

Development Environment

Release Package

Method 1: Interactive Batch File (Recommended for Windows Users)

Development Environment

Release Package

Method 2: Direct CLI Command Execution

For Development (Node.js)

For Standalone Executable

Method 3: NPM Scripts (Development Only)

Common Commands

Validate Query File

Test Database Connection

List Available Template Styles

📚 Documentation

💡 Usage Examples

XML Configuration File Example (with Dynamic Variables)

Variable Usage Example

Dynamic Variables

Variable Types

Usage Examples

🔧 Environment Requirements

📞 Support

About

Resources

License

Uh oh!

Stars

Watchers

Forks

Releases 7

Packages 0

Contributors 2

Uh oh!

Languages

Packages