/// <summary> /// 执行条件窗格中的查询 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void tbQuery_Click(object sender, EventArgs e) { try { txtSQL.Focus(); //用来确认使“查询设计器dgvConditions”结束编辑状态并激活事件dgvConditions_CellEndEdit()生成SQL语句 dgvResults.DataSource = null; Application.DoEvents(); if (g_stQuery.strTable == null || g_stQuery.QueryCol == null) { MessageBox.Show("请选择数据表或查询快照!", "查询", MessageBoxButtons.OK, MessageBoxIcon.Information); return; } if (txtSQL.Text.Trim().Equals("")) { MessageBox.Show("请设置查询条件!", "查询", MessageBoxButtons.OK, MessageBoxIcon.Information); return; } if (!DB.ExecuteDataSet(txtSQL.Text)) { MessageBox.Show("查询执行失败或结果为空!", "查询", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); return; } dgvResults.DataSource = DB.dataSet.Tables[0]; //格式化显示日期时间 for (int i = 0; i < DB.dataSet.Tables[0].Columns.Count; i++) { if (DB.dataSet.Tables[0].Columns[i].DataType.Name.ToLower() == "datetime") { dgvResults.Columns[i].DefaultCellStyle.Format = "yyyy-MM-dd HH:mm:ss"; } } CreateChart(zgcChart, DB.dataSet.Tables[0]); } catch (System.Exception ex) { MessageBox.Show(ex.ToString(), "tbQuery_Click()"); } }
/// <summary> /// 使用COM组件,向当前工作表中的单元格区域写入数据 /// </summary> /// <param name="sRangeString"></param> /// <param name="sValueString"></param> /// <returns></returns> public static bool SetRange(string sRangeString, string sValueString, MyDB.Database DB) { try { if (xlSheet == null) { return(false); } sRangeString = sRangeString.Trim().ToUpper(); string[] sA1Cell = sRangeString.Split('-'); //Excel单元格区域引用使用A1样式,冒号用中划线代替 xlRange = xlSheet.get_Range(sA1Cell[0], sA1Cell[1]); sValueString = sValueString.Trim(); if (sValueString.Length >= 4 && sValueString.Substring(0, 4).Equals("SQL=", StringComparison.OrdinalIgnoreCase)) { string strSQL = sValueString.Substring(4); #if (MYSQL) if (MySQLDB.MySQLDB.ExecuteDBDataSet(strSQL)) { int row = MySQLDB.MySQLDB.dataSet.Tables[0].Rows.Count; int col = MySQLDB.MySQLDB.dataSet.Tables[0].Columns.Count; #else if (DB.ExecuteDataSet(strSQL)) { int row = DB.dataSet.Tables[0].Rows.Count; int col = DB.dataSet.Tables[0].Columns.Count; #endif row = (row < xlRange.Rows.Count) ? row : xlRange.Rows.Count; col = (col < xlRange.Columns.Count) ? col : xlRange.Columns.Count; object[,] objValue = new object[row, col]; //二维数组 for (int i = 0; i < row; i++) { for (int j = 0; j < col; j++) { #if (MYSQL) objValue[i, j] = MySQLDB.MySQLDB.dataSet.Tables[0].Rows[i][j]; #else objValue[i, j] = DB.dataSet.Tables[0].Rows[i][j]; #endif } } xlRange.Value2 = objValue; } } else if (sValueString.Length >= 4 && sValueString.Substring(0, 4).Equals("TXT=", StringComparison.OrdinalIgnoreCase)) { string strValue = sValueString.Substring(4); xlRange.NumberFormatLocal = "@"; //强制文本类型 xlRange.Value2 = strValue; } else { xlRange.Value2 = sValueString; //EXCEL默认类型 } xlBook.Save(); return(true); } catch (Exception ex) { MessageBox.Show(Form.ActiveForm, ex.Message + "\r\n" + "SetRange(" + sRangeString + "," + sValueString + ")", "SetRange()"); return(false); } }