DB数据导出⼯具分享⼀个根据数据库链接字符串，sql语句即可将结果集导出到Excel的⼯具分享，⽀持sqlserver,mysql。
前因⼀个⽉前朋友找到我，让我帮忙做⼀个根据sql导出查询结果到Excel的⼯具（之前帮他⼀个导⼊Excel然后按其规则统计数据的⼯具）。
然后扔了我⼀个SQL语句，瞬间懵⽐。
卧槽。
这么多列，我特么得定义这么属性，改了还得重新改程序（⼀直⽤EF）。
于是思考如何忽略列名，进⽽如何做到通⽤，做到于我有益，⽽不是简单的帮个忙。
如何完成这个需求Q:程序中根据SQL查询出数据⽽不需要关注有哪些列？A:将查询结果保存到DataTable中然后遍历Q:如何将DataTable转换Excel？A:⼀搜，⼀试，可⽤之Q:如何保存到本地？A:待我改改写⽇志的⽅法使⽤进⾏数据操作SqlSugar 4.x是⼀款⾼性能（达到最⾼性能⽔平）、轻量级、⽀持多库和⼈性化语法的ORM，语法⽅便，⼊门简单，功能强⼤。
对数据库结构没太多要求，⽀持多主键，多⾃增列SqlSugar⽀持sqlserver,mysql故此⼯具适⽤于此两者数据库0. 创建项⽬-预览1. 到github仓库clone了源码⾄本地⽣成需要的dll，然后在项⽬中添加了引⽤2. 使⽤SqlSugar获取结果到DataTable中(不知道是不是最近帮朋友写的代码写多了，感觉挺好)3. DataTable转Excelpublic class DataTableToExcel{private DataTableToExcel(){ }private static DataTableToExcel _instance = null;public static DataTableToExcel Instance{get{if (_instance == null) _instance = new DataTableToExcel();return _instance;}}/// <summary>/// DataTable通过流导出Excel/// </summary>/// <param name="ds">数据源DataSet</param>/// <param name="columns">DataTable中列对应的列名(可以是中⽂),若为null则取DataTable中的字段名</param>/// <param name="fileName">保存⽂件名(例如：a.xls)</param>/// <returns></returns>public string StreamExport(DataTable dt, string[] columns = null,string savePath=""){//if (dt.Rows.Count > 65535) //总⾏数⼤于Excel的⾏数//{// throw new Exception("预导出的数据总⾏数⼤于excel的⾏数");//}StringBuilder content = new StringBuilder();content.Append("<html xmlns:o='urn:schemas-microsoft-com:office:office' xmlns:x='urn:schemas-microsoft-com:office:excel' xmlns='/TR/REC-html40'>"); content.Append("<head><title></title><meta http-equiv='Content-Type' content=\"text/html; charset=gb2312\">");//注意：[if gte mso 9]到[endif]之间的代码，⽤于显⽰Excel的⽹格线，若不想显⽰Excel的⽹格线，可以去掉此代码content.Append("<!--[if gte mso 9]>");content.Append("<xml>");content.Append(" <x:ExcelWorkbook>");content.Append(" <x:ExcelWorksheets>");content.Append(" <x:ExcelWorksheet>");content.Append(" <x:Name>Sheet1</x:Name>");content.Append(" <x:WorksheetOptions>");content.Append(" <x:Print>");content.Append(" <x:ValidPrinterInfo />");content.Append(" </x:Print>");content.Append(" </x:WorksheetOptions>");content.Append(" </x:ExcelWorksheet>");content.Append(" </x:ExcelWorksheets>");content.Append("</x:ExcelWorkbook>");content.Append("</xml>");content.Append("<![endif]-->");content.Append("</head><body><table style='border-collapse:collapse;table-layout:fixed;'><tr>");if (columns != null){for (int i = 0; i < columns.Length; i++){if (columns[i] != null && columns[i] != ""){content.Append("<td><b>" + columns[i] + "</b></td>");}else{content.Append("<td><b>" + dt.Columns[i].ColumnName + "</b></td>");}}}else{for (int j = 0; j < dt.Columns.Count; j++){content.Append("<td><b>" + dt.Columns[j].ColumnName + "</b></td>");}}content.Append("</tr>\n");for (int j = 0; j < dt.Rows.Count; j++){content.Append("<tr>");for (int k = 0; k < dt.Columns.Count; k++){object obj = dt.Rows[j][k];Type type = obj.GetType();if ( == "Int32" || == "Single" || == "Double" || == "Decimal"){double d = obj == DBNull.Value ? 0.0d : Convert.ToDouble(obj);if ( == "Int32" || (d - Math.Truncate(d) == 0))content.AppendFormat("<td style='vnd.ms-excel.numberformat:#,##0'>{0}</td>", obj);elsecontent.AppendFormat("<td style='vnd.ms-excel.numberformat:#,##0.00'>{0}</td>", obj);}elsecontent.AppendFormat("<td style='vnd.ms-excel.numberformat:@'>{0}</td>", obj);}content.Append("</tr>\n");}content.Append("</table></body></html>");content.Replace(" ", "");using (var w = new StreamWriter(savePath, false, Encoding.UTF8)){w.WriteLine(content);}return savePath;}}4. 使⽤Ini⽂件保存输⼊public class IniHelper{// 声明INI⽂件的写操作函数 WritePrivateProfileString()[System.Runtime.InteropServices.DllImport("kernel32")]private static extern long WritePrivateProfileString(string section, string key, string val, string filePath);// 声明INI⽂件的读操作函数 GetPrivateProfileString()[System.Runtime.InteropServices.DllImport("kernel32")]private static extern int GetPrivateProfileString(string section, string key, string def, System.Text.StringBuilder retVal, int size, string filePath);private int retLength = 500;private string sPath = null;public IniHelper(string path, int rl = 500){this.sPath = path;if (rl > 0){this.retLength = rl;}}public void WriteValue(string key, string value, string section = "Setting"){// section=配置节，key=键名，value=键值，path=路径WritePrivateProfileString(section, key, value, sPath);}public string ReadValue(string key, string section = "Setting"){// 每次从ini中读取多少字节System.Text.StringBuilder temp = new System.Text.StringBuilder(retLength);// section=配置节，key=键名，temp=上⾯，path=路径GetPrivateProfileString(section, key, "", temp, retLength, sPath);return temp.ToString();}}5. ⽂本框全选功能public frmMain(){this.ControlAdded += new System.Windows.Forms.ControlEventHandler(this.Control_ControlAdded); //注册全选功能 InitializeComponent();}#region ⽂本框能够使⽤Ctrl+A 全选功能private void Control_ControlAdded(object sender, ControlEventArgs e){//使“未来”⽣效e.Control.ControlAdded += new System.Windows.Forms.ControlEventHandler(this.Control_ControlAdded);//使“⼦孙”⽣效foreach (Control c in e.Control.Controls){Control_ControlAdded(sender, new ControlEventArgs(c));}//使“过去”⽣效TextBox textBox = e.Control as TextBox;if (textBox != null){textBox.KeyPress += TextBox_KeyPress;}}private void TextBox_KeyPress(object sender, KeyPressEventArgs e){TextBox textBox = sender as TextBox;if (textBox == null)return;if (e.KeyChar == (char)1){textBox.SelectAll();e.Handled = true;}}#endregion6. 打开保存的excelprivate void btnOpenDir_Click(object sender, EventArgs e){var txtFileName = this.txtFileName.Text;var txtExportDir = this.txtExportDir.Text;var openPath = bine(txtExportDir, txtFileName);if (File.Exists(openPath)){System.Diagnostics.Process.Start(openPath, "c:\\windows");}else{AppendTipMsg("⽂件" + openPath + "不存在");}}7. 页⾯主要功能代码winform中使⽤多线程时给ui控件赋值var txtThread = new Thread(() => txtMsg.BeginInvoke(new Action(() => txtMsg.AppendText("向⽂本框中追加内容")))); txtThread.Start();8. ⼀些记录winform中使⽤多线程时给ui控件赋值var txtThread = new Thread(() => txtMsg.BeginInvoke(new Action(() => txtMsg.AppendText("向⽂本框中追加内容")))); txtThread.Start();源码中的NopI组件可移除，此⼯具实际未⽤到开启线程执⾏导出的时候使⽤的是Task.Run(() =>{});若将框架版本改为4.0则需要将此处修改为new Thread(() =>{}).Start();整个过程解决了⼀下问题0. 数据库查询（SqlSugar⽀持sqlserver,mysql）1. datatable转excel⽂本2. ini存取⽂件3. winform⽂本框全选功能4. winform中使⽤多线程时给ui控件赋值源码使⽤说明。
