首页 > 代码库 > SqlExcel使用文档及源码

SqlExcel使用文档及源码

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

使用文档

一、增、改、查、删

1、增(向sheet中插入数据):

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

执行Sql前:

image

执行Sql后:

image

2、改(更新sheet中的数据):

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

执行Sql前:

image

执行Sql后:

image

3、查(在sheet中查询数据):

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

image

4、删(从sheet中删除数据):

image

显然支持!

二、WHERE:(WHERE在修改、查询中的应用)

1、修改:

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

执行Sql前:

image

执行Sql后:

image

2、查询:

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

image

三、LIKE与通配符

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

image

1、*:所有列的名称;

2、%:通配n个字符;

3、_:通配1个字符。

四、排序(ORDER BY)

1、升序:(ASC)可省略;

2、降序:(DSEC)。

示例1:(升序排序)

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

image

示例2:(降序排序)

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

image

示例3:(升序排序简写)

SELECT * FROM [Employee$] ORDER BY EmployeeId;

image

五、多sheet连接

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

Characters:

IDCharacter
1内向
2外向
3中性性格

Colors:

IDColor
1绿色
2红色
4蓝色

1、内连接:

内连接(JOIN  或 INNER JOIN):内连接取交集

示意图:

image

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

image

2、外连接:

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

(1)左连接(LEFT JOIN):

示意图:

image

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

结果:

image

(2)右连接(RIGHT JOIN):

示意图:

image

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

image

(3)完全外连接:

示意图:

image

SELECT * FROM [characters$] LEFT JOIN [colors$] ON [characters$].ID = [colors$].ID
UNION
SELECT * FROM [characters$] RIGHT JOIN [colors$] ON [characters$].ID = [colors$].ID;
结果:

image

3、交叉连接( CROSS JOIN ):

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

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

结果:

image


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 = http://www.mamicode.com/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

SqlExcel使用文档及源码