SqlExcel使用文档及源码

昨天帮朋友做了个小工具，以完成多表连接处理一些数据。今天下班后又做了份使用文档，不知友能看懂否？现将使用文档及源码发布如下，以供有同样需求的朋友下载。

使用文档

一、增、改、查、删

1、增（向sheet中插入数据）：

INSERT INTO [Employee$](EmployeeId,EmployeeName,BranchId) VALUES('YG2014120001','韩兆新','BM20141201');

执行Sql前：

执行Sql后：

2、改（更新sheet中的数据）:

UPDATE [Employee$] SET BranchId = 'BM20141202';

执行Sql前：

执行Sql后：

3、查（在sheet中查询数据）:

SELECT EmployeeId,EmployeeName,BranchId FROM [Employee$];

4、删（从sheet中删除数据）:

显然不支持！

二、WHERE：（WHERE在修改、查询中的应用）

1、修改：

UPDATE [Employee$] SET EmployeeID=null,EmployeeName=null,BranchId=null WHERE EmployeeID='YG2014120003';

执行Sql前：

执行Sql后：

2、查询：

SELECT EmployeeId,EmployeeName,BranchId FROM [Employee$] WHERE EmployeeID = 'YG2014120002';

三、LIKE与通配符

SELECT * FROM [Employee$] WHERE EmployeeID LIKE 'YG201412%';

1、*：所有列的名称；

2、%：通配n个字符；

3、_：通配1个字符。

四、排序（ORDER BY）

1、升序：（ASC）可省略；

2、降序：（DSEC）。

示例1：（升序排序）

SELECT * FROM [Employee$] ORDER BY EmployeeId DESC;

示例2：（降序排序）

SELECT * FROM [Employee$] ORDER BY EmployeeId DESC;

示例3：（升序排序简写）

SELECT * FROM [Employee$] ORDER BY EmployeeId;

五、多sheet连接

先建立两个用于演示的sheet：

Characters：

ID	Character
1	内向
2	外向
3	中性性格

Colors：

ID	Color
1	绿色
2	红色
4	蓝色

1、内连接：

内连接（JOIN  或 INNER JOIN）：内连接取交集

示意图：

SELECT * FROM [characters$] INNER JOIN [colors$] ON [characters$].ID = [colors$].ID;

2、外连接：

外连接可分为：左连接、右连接、完全外连接。

（1）左连接（LEFT JOIN）：

示意图：

SELECT * FROM [characters$] LEFT JOIN [colors$] ON [characters$].ID = [colors$].ID;

结果：

（2）右连接（RIGHT JOIN）：

示意图：

SELECT * FROM [characters$] RIGHT JOIN [colors$] ON [characters$].ID = [colors$].ID;

结果：

（3）完全外连接：

示意图：

SELECT * FROM [characters$] LEFT JOIN [colors$] ON [characters$].ID = [colors$].ID

UNION

SELECT * FROM [characters$] RIGHT JOIN [colors$] ON [characters$].ID = [colors$].ID;

结果：

3、交叉连接（ CROSS JOIN ）：

交叉连接产生连接所涉及的表的笛卡尔积。

SELECT * FROM [characters$],[colors$];

结果：

---

4、补充：

1、如下可获取内连接结果：

SELECT * FROM [characters$],[colors$] WHERE [characters$].ID = [colors$].ID;

2、如下可获取交叉连接结果：

SELECT * FROM [characters$],[colors$];

---

源码：

Program.cs

using System;

using System.IO;

using System.Windows.Forms;

namespace SqlExcel

{

    static class Program

    {

        /// <summary>

        /// 应用程序的主入口点。

        /// </summary>

        [STAThread]

        static void Main()

        {

            Sunisoft.IrisSkin.SkinEngine skinEngine = new Sunisoft.IrisSkin.SkinEngine();

            string skinPath = Application.StartupPath + Path.DirectorySeparatorChar + "skin" + Path.DirectorySeparatorChar + "skin.ssk";

            skinEngine.SkinFile = skinPath;

            Application.EnableVisualStyles();

            Application.SetCompatibleTextRenderingDefault(false);

            Application.Run(new MainForm());

        }

    }

}

MainForm.cs

using System;

using System.Data;

using System.Data.Common;

using System.Drawing;

using System.Windows.Forms;

namespace SqlExcel

{

    public partial class MainForm : Form

    {

        public MainForm()

        {

            InitializeComponent();

        }

        /// <summary>

        /// 输入文件选择

        /// </summary>

        /// <param name="sender"></param>

        /// <param name="e"></param>

        private void btnInFile_Click(object sender, EventArgs e)

        {

            OpenFileDialog openFileDlg = new OpenFileDialog();

            openFileDlg.Filter = "Excel 2003文件|*.xls|Excel 2007文件|*.xlsx";

            if (DialogResult.OK.Equals(openFileDlg.ShowDialog()))

            {

                txtInFile.Text = openFileDlg.FileName;

            }

        }

        /// <summary>

        /// 执行Sql...

        /// </summary>

        /// <param name="sender"></param>

        /// <param name="e"></param>

        private void btnExecute_Click(object sender, EventArgs e)

        {

            if (string.IsNullOrEmpty(txtInFile.Text.Trim()))

            {

                MessageBox.Show("请选择输入文件！", "操作提示", MessageBoxButtons.OK, MessageBoxIcon.Information);

                return;

            }

            if (string.IsNullOrEmpty(txtSql.Text.Trim()))

            {

                MessageBox.Show("请输入Sql语句！", "操作提示", MessageBoxButtons.OK, MessageBoxIcon.Information);

                return;

            }

            int linesNum = 0;

            double executionTime = 0.0;

            string resultInfo = string.Empty;

            DataTable dtResult = null;

            tabResult.SelectedTab = tPageResultInfo;

            try

            {

                if (txtSql.Text.ToLower().StartsWith("select"))

                {

                    executionTime = CodeTimer.ExecuteCode(delegate()

                    {

                        dtResult = SqlHelper.ExecuteDataTable(txtInFile.Text, txtSql.Text);

                    });

                    tabResult.SelectedTab = tPageResult;

                }

                else

                {

                    executionTime = CodeTimer.ExecuteCode(delegate()

                    {

                        linesNum = SqlHelper.ExecuteNonQuery(txtInFile.Text, txtSql.Text);

                    });

                }

                resultInfo = FormatResultInfo(txtSql.Text, linesNum, executionTime);

            }

            catch (Exception ex)

            {

                if (ex.Message.Equals("未在本地计算机上注册“Microsoft.Ace.OLEDB.12.0”提供程序。"))

                {

                    MessageBox.Show("本程序运行需安装：AccessDatabaseEngine，\r\n请安装后重试！", "系统警告", MessageBoxButtons.OK, MessageBoxIcon.Warning);

                }

                else if (ex is DbException)

                {

                    MessageBox.Show(string.Format("Sql语句错误：“{0}”", ex.Message), "操作提示", MessageBoxButtons.OK, MessageBoxIcon.Information);

                }

                else

                {

                    MessageBox.Show(string.Format("发生未处理错误，请联系作者！\r\n错误信息：“{0}”", ex.Message), "系统错误", MessageBoxButtons.OK, MessageBoxIcon.Error);

                }

                resultInfo = FormatResultInfo(txtSql.Text, ex.Message);

            }

            finally

            {

                gvResult.DataSource = dtResult;

                txtResultInfo.Text = resultInfo;

            }

        }

        /// <summary>

        /// 到处结果数据

        /// </summary>

        /// <param name="sender"></param>

        /// <param name="e"></param>

        private void btnExport_Click(object sender, EventArgs e)

        {

            DataTable dt = gvResult.DataSource as DataTable;

            if (null == dt)

            {

                MessageBox.Show("无操作结果！", "操作提示", MessageBoxButtons.OK, MessageBoxIcon.Information);

                return;

            }

            SaveFileDialog saveFileDlg = new SaveFileDialog();

            saveFileDlg.Filter = "Excel 2003文件|*.xls|Excel 2007文件|*.xlsx";

            if (DialogResult.OK.Equals(saveFileDlg.ShowDialog()))

            {

                try

                {

                    ExcelHelper.DataTableToExcel(dt, "result", saveFileDlg.FileName);

                    MessageBox.Show("导出成功", "操作提示", MessageBoxButtons.OK, MessageBoxIcon.None);

                }

                catch (Exception ex)

                {

                    MessageBox.Show(string.Format("导出失败，原因：“{0}”", ex.Message), "操作提示", MessageBoxButtons.OK, MessageBoxIcon.Information);

                }

            }

        }

        //显示行号

        private void gvResult_RowPostPaint(object sender, DataGridViewRowPostPaintEventArgs e)

        {

            Rectangle rectangle = new Rectangle(e.RowBounds.Location.X,

                e.RowBounds.Location.Y,

                gvResult.RowHeadersWidth - 4,

                e.RowBounds.Height);

            TextRenderer.DrawText(e.Graphics, (e.RowIndex + 1).ToString(),

                gvResult.RowHeadersDefaultCellStyle.Font,

                rectangle,

                gvResult.RowHeadersDefaultCellStyle.ForeColor,

                TextFormatFlags.VerticalCenter | TextFormatFlags.Right);

        }

        #region 格式化Sql执行结果信息

        private string FormatResultInfo(string sql, int linesNum, double executionTime)

        {

            return string.Format("[SQL]{0}\r\n受影响的行: {1}\r\n时间: {2}ms\r\n", sql, linesNum, executionTime);

        }

        private string FormatResultInfo(string sql, string errorInfo)

        {

            return string.Format("[SQL]{0}\r\n[Err]{1}", sql, errorInfo);

        }

        #endregion

    }

}

SqlHelper.cs

 

using System;

using System.Configuration;

using System.Data;

using System.Data.OleDb;

namespace SqlExcel

{

    static class SqlHelper

    {

        private static string GetConnectionString(string dataSource)

        {

            if (string.IsNullOrEmpty(dataSource))

            {

                throw new Exception("数据源不能为空！");

            }

            return string.Format(ConfigurationManager.ConnectionStrings["Conn"].ConnectionString, dataSource);

        }

        public static DataTable ExecuteDataTable(string dataSource, string sql, params OleDbParameter[] parameters)

        {

            using (OleDbConnection conn = new OleDbConnection(GetConnectionString(dataSource)))

            {

                using (OleDbCommand cmd = conn.CreateCommand())

                {

                    cmd.CommandText = sql;

                    cmd.Parameters.AddRange(parameters);

                    using (OleDbDataAdapter da = new OleDbDataAdapter(cmd))

                    {

                        DataTable dt = new DataTable();

                        da.Fill(dt);

                        return dt;

                    }

                }

            }

        }

        public static int ExecuteNonQuery(string dataSource, string sql, params OleDbParameter[] parameters)

        {

            using (OleDbConnection conn = new OleDbConnection(GetConnectionString(dataSource)))

            {

                conn.Open();

                using (OleDbCommand cmd = conn.CreateCommand())

                {

                    cmd.CommandText = sql;

                    cmd.Parameters.AddRange(parameters);

                    return cmd.ExecuteNonQuery();

                }

            }

        }

    }

}

ExcelHelper.cs

using System;

using System.Data;

using System.IO;

using NPOI.HSSF.UserModel;

using NPOI.SS.UserModel;

using NPOI.XSSF.UserModel;

namespace SqlExcel

{

    static class ExcelHelper

    {

        #region 导出DataTable到Excel（Author：hanzhaoxin/2014-12-12）

        public static void DataTableToExcel(DataTable dtSource, string sheetName, string fileName)

        {

            string extension = Path.GetExtension(fileName);

            IWorkbook workbook;

            if (extension.Equals(".xls"))

            {

                workbook = new HSSFWorkbook();

            }

            else if (extension.Equals(".xlsx"))

            {

                workbook = new XSSFWorkbook();

            }

            else

            {

                throw new Exception("不是有效的Excel格式！");

            }

            ISheet sheet = workbook.CreateSheet(sheetName);

            IRow headerRow = sheet.CreateRow(0);

            foreach (DataColumn cl in dtSource.Columns)

            {

                headerRow.CreateCell(cl.Ordinal).SetCellValue(cl.ColumnName);

            }

            int rowIndex = 1;

            foreach (DataRow dr in dtSource.Rows)

            {

                IRow dataRow = sheet.CreateRow(rowIndex);

                foreach (DataColumn cl in dtSource.Columns)

                {

                    #region SetCellValue

                    switch (cl.DataType.ToString())

                    {

                        case "System.String":

                            dataRow.CreateCell(cl.Ordinal).SetCellValue(dr[cl].ToString());

                            break;

                        case "System.DateTime":

                            DateTime dtCellValue = new DateTime();

                            DateTime.TryParse(dr[cl].ToString(), out dtCellValue);

                            dataRow.CreateCell(cl.Ordinal).SetCellValue(dtCellValue);

                            break;

                        case "System.Boolean":

                            bool blCellValue;

                            bool.TryParse(dr[cl].ToString(), out blCellValue);

                            dataRow.CreateCell(cl.Ordinal).SetCellValue(blCellValue);

                            break;

                        case "System.Int16":

                        case "System.Int32":

                        case "System.Int64":

                        case "System.Byte":

                            int iCellValue;

                            int.TryParse(dr[cl].ToString(), out iCellValue);

                            dataRow.CreateCell(cl.Ordinal).SetCellValue(iCellValue);

                            break;

                        case "System.Decimal":

                        case "System.Double":

                            double doubCellValue;

                            double.TryParse(dr[cl].ToString(), out doubCellValue);

                            dataRow.CreateCell(cl.Ordinal).SetCellValue(doubCellValue);

                            break;

                        case "System.DBNull":

                            dataRow.CreateCell(cl.Ordinal).SetCellValue("");

                            break;

                        default:

                            dataRow.CreateCell(cl.Ordinal).SetCellValue(dr[cl].ToString());

                            break;

                    }

                    #endregion

                }

                rowIndex++;

            }

            using (FileStream fs = File.OpenWrite(fileName))

            {

                workbook.Write(fs);

                headerRow = null;

                sheet = null;

                workbook = null;

            }

        }

        #endregion

    }

}

CodeTimer.cs

using System.Diagnostics;

namespace SqlExcel

{

    delegate void Action();

    static class CodeTimer

    {

        public static double ExecuteCode(Action dgMethodName)

        { 

            Stopwatch sw = new Stopwatch();

            sw.Start();

            dgMethodName.Invoke();

            sw.Stop();

            return sw.Elapsed.TotalMilliseconds;

        }

    }

}

App.config

<?xml version="1.0" encoding="utf-8" ?>

<configuration>

    <connectionStrings>

        <add name="Conn" connectionString="Provider=Microsoft.Ace.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source={0};"/>

    </connectionStrings>

</configuration>

下载

因该程序运行需注册“Microsoft.Ace.OLEDB.12.0”，考虑到很多朋友没有安装。后面也会给出下载“AccessDatabaseEngine.exe”的链接。

下载地址：

SqlExcel源码：http://files.cnblogs.com/hanzhaoxin/SqlExcel%E6%BA%90%E7%A0%81.zip

AccessDatabaseEngine：http://www.microsoft.com/zh-cn/download/details.aspx?id=13255