SqlExcel使用文檔及源碼，sqlexcel文檔源碼

SqlExcel使用文檔及源碼，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