SQLExporter.cs

这里只显示每个文件的开头 60 行。登录后可以解锁完整代码。

微信扫码关注公众号，代码整理好后第一时间通知你。

using System;
using System.IO;
using System.Data;
using System.Text;
using System.Collections.Generic;
using Newtonsoft.Json;

namespace excel2json {
    class SQLExporter {
        string mStructSQL;
        string mContentSQL;

        public string structSQL {
            get {
                return mStructSQL;
            }
        }

        public string contentSQL {
            get {
                return mContentSQL;
            }
        }

        /// <summary>
        /// 初始化内部数据
        /// </summary>
        /// <param name="sheet">Excel读取的一个表单</param>
        /// <param name="headerRows">表头有几行</param>
        public SQLExporter(string excelName, DataTable sheet, int headerRows) {
            string tableName = sheet.TableName;

            //-- 转换成SQL语句
            mStructSQL = GetTabelStructSQL(sheet, tableName);
            mContentSQL = GetTableContentSQL(sheet, tableName, headerRows);
        }

        /// <summary>
        /// 转换成SQL字符串，并保存到指定的文件
        /// </summary>
        /// <param name="filePath">存盘文件</param>
        /// <param name="encoding">编码格式</param>
        public void SaveToFile(string filePath, Encoding encoding) {
            //-- 保存文件
            using (FileStream file = new FileStream(filePath, FileMode.Create, FileAccess.Write)) {
                using (TextWriter writer = new StreamWriter(file, encoding)) {
                    writer.Write(mStructSQL);
                    writer.WriteLine();
                    writer.Write(mContentSQL);
                }
            }
        }

        /// <summary>
        /// 将表单内容转换成INSERT语句
        /// </summary>
        private string GetTableContentSQL(DataTable sheet, string tabelName, int headerRows) {
            StringBuilder sbContent = new StringBuilder();
            StringBuilder sbNames = new StringBuilder();
            StringBuilder sbValues = new StringBuilder();