using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;
using Office = Microsoft.Office.Core;
using Microsoft.Office.Tools.Excel;
using System.Data.SQLite;
using System.Diagnostics;
namespace zizhuTools
{
public partial class Statistics : Form
{
public Excel.Application Eapp;
SQLiteConnection m_dbConnection;
Stopwatch sw = new Stopwatch();
public Statistics()
{
InitializeComponent();
}
Excel.Workbook wb ;
Excel.Worksheet sht ;
int iT ;
int iCols;
int iRows ;
Excel.Range rng;
int shtindex = 1;
private void Statistics_Load(object sender, EventArgs e)
{
Eapp = Globals.ThisAddIn.Application;
wb = Eapp.ActiveWorkbook;
sht = wb.ActiveSheet;
for (int i = 1; i <= wb.Worksheets.Count;i++ )
{
if (sht.Name == wb.Worksheets[i].Name)
{
shtindex = i;
break;
}
}
sht = wb.Worksheets[shtindex];
iT = int.Parse(nUDtitle.Value.ToString());
iCols = sht.Range["B" + iT].CurrentRegion.Columns.Count;
iRows = sht.Range["B"+iT].CurrentRegion.Rows.Count;
rng = sht.Range["A" + iT].Resize[1, iCols];
object[,] title = rng.Value;
for(int i=1;i<=title.GetLength(1);i++)
{
string values =title[1,i] != null ?title[1,i].ToString(): "Column" + i;
G1_ListBkexuan.Items.Add(values);
G2Cb1.Items.Add(values);
G3cB1.Items.Add(values);
}
}
private void btn_Confirm_Click(object sender, EventArgs e)
{
sw.Start();
this.Text = "发放统计(程序运行中)";
sht = wb.Worksheets[shtindex];
iT = int.Parse(nUDtitle.Value.ToString());
string sOrder = G1rBUp.Checked ? " ASC" : " DESC";
iRows = sht.Range["A" + iT].CurrentRegion.Rows.Count; //数据源总行数
iCols = G1_ListBkexuan.Items.Count;
int iDrow = int.Parse(nUDd.Value.ToString());//标题
Excel.Range rng = sht.Range["A" + iT].Resize[1, iCols]; //标题单元格
////创建一个空的数据库
//SQLiteConnection.CreateFile("NewDataBase.sqlite");
//创建一个连接到指定数据库
//连接到数据库
string sFolderPath = System.AppDomain.CurrentDomain.SetupInformation.ApplicationBase;
sFolderPath = sFolderPath.Replace("\\", "/") + "DataAsStatics/NewDataBase.sqlite";
// MessageBox.Show(sFolderPath);
m_dbConnection = new SQLiteConnection("Data Source=" + sFolderPath);
//m_dbConnection = new SQLiteConnection("Data Source=D:/VS程序保存/新建文件夹/WFA/WFA/bin/Debug/");
m_dbConnection.Open();
//删除该表
string sql = "DROP TABLE IF EXISTS \"" + sht.Name + "\"";
SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
command.ExecuteNonQuery();
// System.Text.RegularExpressions.Regex rg = new System.Text.RegularExpressions.Regex(@"'");
// System.Text.RegularExpressions.Match mc = rg.Match("");
//创建表
string sqlTitle = "";
sql = "create table \"" + sht.Name + "\"(";
//object[,] arrTitle = rng.Value;
foreach (string s in G1_ListBkexuan.Items)
{
sql += "'" + s + "'" + " text ,";
sqlTitle += "'" + s + "',";
}
sqlTitle = sqlTitle.Substring(0, sqlTitle.Length - 1) + ") values (";
sql = sql.Substring(0, sql.Length - 1) + ");";
try
{
command = new SQLiteCommand(sql, m_dbConnection);
command.ExecuteNonQuery();
}
catch(Exception ex)
{
MessageBox.Show("程序运行异常(请勿有同名列)，原因:" + ex.Message, "程序运行异常");
return;
}
//完成创建
textBox1.AppendText("\r\n开始插入数据:" + sw.Elapsed);
//插入数据
rng = sht.Range["A" + iDrow].Resize[iRows-1, iCols];
object[,] arr = rng.Value;
SQLiteTransaction tx = m_dbConnection.BeginTransaction();
command.Transaction = tx;
sqlTitle = "insert into \"" + sht.Name + "\" (" + sqlTitle;
for (int i = 1; i <= arr.GetLength(0); i++)
{
try
{
sql = "";
for (int j = 1; j <= iCols; j++)
{
var ax = arr[i, j] != null ? arr[i, j].ToString().Replace("'", "").ToUpper().Trim() : "";
//ax = rg.Match(ax.ToString()).Success ? ax.ToString().Replace("'", "''") : ax;
Type a = ax.GetType();
sql += a.Name == "String" ? "'" + ax + "'" + "," : ax + ",";
}
sql = sqlTitle + sql.Substring(0, sql.Length - 1) + ")";
command.CommandText = sql;
command.ExecuteNonQuery();
}
catch (Exception ex)
{
//MessageBox.Show("语句【" + sql + "】产生异常:" + ex.Message, "异常");
textBox1.AppendText("\r\n语句【" + sql + "】产生异常，已忽略:" + ex.Message + "\r\n");
}
}
tx.Commit();
textBox1.AppendText("\r\n插入完成:" + sw.Elapsed );
//完成插入数据
//生成表后，根据要求编写sql语句
//sql = "select * from \"" + sht.Name + "\"";
//首先是查询范围，包括统计字段、分组字段（根本不会写┭┮﹏┭┮）
/*
*SELECT "就读学校（幼儿园）" , "县市" , "贫困类别" , sum("金额") as '金额', count("学生身份证号") as '人数' ,
*count(DISTINCT "学生身份证号") as '去重人数'
FROM "资金发放名册 (2)"
WHERE "就读学校
（幼儿园）" like '%中学%'
GROUP BY "就读学校
（幼儿园）" ,"县市","贫困类别"
ORDER BY "就读学校
（幼儿园）"
*/
textBox1.AppendText("\r\n开始编写查询语句:" + sw.Elapsed );
sql = "";
string sql_group = "";
//查询分组字段
foreach (string s in G1_ListBfenzu.Items)
{
sql += " \"" + s + "\",";
sql_group += " \"" + s + "\",";
}
foreach (string s in G1_ListBCol.Items)
{
sql += " \"" + s + "\",";
sql_group += " \"" + s + "\",";
}
//统计字段
foreach (string s in G2_ListBtongji.Items)
{
sql += s + ",";
}
sql = sql != "" ? "SELECT " + sql.Substring(0, sql.Length - 1) : "SELECT *";
sql_group = sql_group != "" ? " GROUP BY " + sql_group.Substring(0, sql_group.Length - 1) : "";
//排序字段
string strOrder = "";
foreach (string s in G1_ListBpaixu.Items)
{
strOrder += " \"" + s + "\" " + sOrder + ",";
}
strOrder = strOrder != "" ? " ORDER BY " + strOrder.Substring(0, strOrder.Length - 1) : "";
sql += " From \"" + sht.Name + "\" ";
string strGl = "";
//过滤
foreach (string s in G3_ListBguolv.Items)
{
strGl += s;
}
strGl = strGl != "" ? " WHERE " + strGl : "";
//分组
sql += strGl + sql_group + strOrder;
textBox1.AppendText("\r\n编写完成:" + sw.Elapsed );
//完成查询后，将数据插入新的工作表
command = new SQLiteCommand(sql, m_dbConnection);
SQLiteDataReader rdr = command.ExecuteReader();
// textBox1.AppendText("读取查询结果完成1:" + sw.Elapsed + "\r\n");
DataTable dt = new DataTable();
dt.Load(rdr);
textBox1.AppendText("\r\n读取查询结果完成:" + sw.Elapsed );
if (dt.Rows.Count == 0)
{
return;
}
wb.Worksheets.Add(Type.Missing, wb.Worksheets[shtindex]);
sht = wb.Worksheets[shtindex + 1];
int dtR = dt.Rows.Count;
int dtC = dt.Columns.Count;
// int k1 = 1;
//生成字段名称
textBox1.AppendText("\r\n将查询结果复制至Excel:" + sw.Elapsed );
object[,] result = new object[dtR+1, dtC];
int c = 0;
foreach (DataColumn col in dt.Columns)
{
result[0,c] = col.ColumnName;
c += 1;
}
// k1 = 2;
// rng = sht.Range["A2"].Resize[dtR - 1, dtC];
c = 1;
int k=0;
foreach (DataRow r in dt.Rows)
{
foreach (var s in r.ItemArray)
{
result[c, k] = s;
k += 1;
}
//rng = sht.Range["A"+k1].Resize[1, dtC];
//rng.Value2 = r.ItemArray;
c += 1;
k=0;
}
rng = sht.Range["A1"].Resize[result.GetLength(0), dtC];
rng.Value = result;
textBox1.AppendText("\r\n完成复制:" + sw.Elapsed );
textBox1.AppendText("\r\n开始转换透视表:" + sw.Elapsed);
int rowsc2 = sht.UsedRange.Rows.Count;
//完成查询后，将数据插入新的工作表
//数据透视表
//行标题 G1_ListBfenzu
//列标题 G1_ListBCol
//排序 G1_ListBpaixu
//统计字段 G2_ListBtongji sum("金额") as '金额', count("学生身份证号") as '人数'
int iCol = rng.Columns.Count;
iRows = rng.Rows.Count;
Excel.Range new_rng = sht.Cells[1, iCol + 2];
wb.PivotCaches().Create(Excel.XlPivotTableSourceType.xlDatabase, rng, Excel.XlPivotTableVersionList.xlPivotTableVersion12).CreatePivotTable
(new_rng, "PT_new", Type.Missing, Excel.XlPivotTableVersionList.xlPivotTableVersion12);
Excel.PivotTable pvtTable = sht.PivotTables("PT_new");
// pvtTable.ShowValuesRow = false;
pvtTable.RowAxisLayout(Excel.XlLayoutRowType.xlTabularRow);
foreach (string s in G1_ListBfenzu.Items)
{
Excel.PivotField pvF = pvtTable.PivotFields(s);
pvF.Subtotals[1] = false;
pvF.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
pvF.Position = G1_ListBfenzu.Items.IndexOf(s) + 1;
}
foreach (string s in G1_ListBCol.Items)
{
Excel.PivotField pvF = pvtTable.PivotFields(s);
pvF.Subtotals[1] = false;
pvF.Orientation = Excel.XlPivotFieldOrientation.xlColumnField;
pvF.Position = G1_ListBCol.Items.IndexOf(s) + 1;
}
// ActiveSheet.PivotTables("PT_new").AddDataField ActiveSheet.PivotTables("PT_new" _
//).PivotFields("人数"), "求和项:人数", xlSum
foreach (string s in Gslist)
{
Excel.PivotField pvF = pvtTable.PivotFields(s);
pvF.Subtotals[1] = false;
pvtTable.AddDataField(pvF, Type.Missing, Excel.XlConsolidationFunction.xlSum);
// pvtTable.AddDataField(pvtTable.PivotFields(s), s, Excel.XlConsolidationFunction.xlSum);
}
int orNum = sOrder.Contains("ASC") ? 1 : 2;
foreach (string s in G1_ListBpaixu.Items)
{
Excel.PivotField pvF = pvtTable.PivotFields(s);
pvF.AutoSortEx(orNum, s);
}
new_rng = pvtTable.TableRange2; //数据透视表的范围
rng = sht.Cells[sht.UsedRange.Rows.Count + 3, 1];
new_rng.Copy();
rng.PasteSpecial(Excel.XlPasteType.xlPasteValues, Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, Type.Missing, Type.Missing);
pvtTable.TableRange2.Delete(Excel.XlDeleteShiftDirection.xlShiftToLeft);
rng.Replace("求和项:", "", Excel.XlLookAt.xlPart, Excel.XlSearchOrder.xlByRows, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
int ht = G1_ListBfenzu.Items.Count; //行标题的数量
int ctt = G1_ListBCol.Items.Count;//列标题的数量
//表格格式处理
if (ctt >= 1)
{
rng.Resize[1, 1].EntireRow.Clear();
}
iCols = sht.UsedRange.Columns.Count; //表格总列数
iRows = sht.UsedRange.Rows.Count; //表格总行数
rng = sht.Range["A" + iRows].CurrentRegion;
string ad = rng.Address;
//从右往左遍历，获得空白单元格
int rR = rng.Row;
int ct = Gslist.Count;
rng.Columns.EntireColumn.AutoFit();//自动调整列宽
rng.Rows.EntireRow.AutoFit();//自动调整行高
//标题行、统计列的范围取决于 标题列和统计列的和
//如果标题列小于等于1和统计列为1，则范围是当前行
//只要标题列不为0，都会多1行
// 没有列，1
//1列，1
//2列1值，2
//1列2值，2
// 2列2值，3
int fw = 1;
if ((ct + ctt) == 3 && ctt >= 1)
{
fw = 2;
}
else if ((ct + ctt) >= 4)
{
fw = ctt + 1;
}
//Excel.Range shtcel2 = shtcell.End[Excel.XlDirection.xlDown];
// int xk = shtcell.Row;
//只有1行值，没有标题行
//1行值，一行标题行
//只要值等于1行，标题行等于标题列数量；否则等于标题列数量+1
//首先根据这个逻辑把标题行涂灰
Excel.Range shtcell = sht.Cells[rR, 1].Resize[fw, iCols];
shtcell.Interior.Color = Color.Silver;//标题灰色
shtcell.Font.Bold = true;
rng.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;//加边框
sht.Range[sht.Cells[iRows, 1], sht.Cells[iRows, iCols]].Interior.Color = Color.Yellow;
sht.Range[sht.Cells[iRows, 1], sht.Cells[iRows, iCols]].Font.Bold = true;
rng.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
rng.HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
Excel.Range shtcel2 = shtcell.Resize[fw, Type.Missing];//行标题的范围
rR = shtcel2.Row;
iT = shtcel2.Rows.Count;
if (iT > 1) //如果不是只有一行
{
//合并行标题的第一行
for (int j = 1; j <= ht; j++)
{
sht.Cells[rR, j].Resize[iT, 1].Merge(false);
}
//合并统计列
for (int j = (rR + fw - 1); j > rR; j--)
{
for (int i = iCols; i > (iCols - ct); i--)
{
if (string.IsNullOrEmpty(sht.Cells[j, i].Value))
{
shtcell = sht.Cells[j, i];
shtcel2 = shtcell.End[Excel.XlDirection.xlUp];
sht.Range[shtcell, shtcel2].Merge(false);
}
}
}
}
//Excel.Range shtcell;
//Excel.Range shtcel2;
//合并列标题
for (int j = rR; j < (rR + fw - 1); j++)
{
for (int i = (iCols - ct); i > ht; i--)
{
if (string.IsNullOrEmpty(sht.Cells[j, i].Value))
{
shtcell = sht.Cells[j, i];
shtcel2 = shtcell.End[Excel.XlDirection.xlToLeft];
sht.Range[shtcell, shtcel2].Merge(false);
i = shtcel2.Column;
}
}
}
//合并行标题,从下往上
for (int i = iRows - 1; i > (rR + ht); i--) //多少行
{
for (int j = 1; j <= ht; j++) //多少列
{
if (string.IsNullOrEmpty(sht.Cells[i, j].Value))
{
shtcell = sht.Cells[i, j];
shtcel2 = shtcell.End[Excel.XlDirection.xlUp];
sht.Range[shtcell, shtcel2].Merge(false);
i = shtcel2.Row;
}
}
}
if (ht >= 1)
{
shtcell = sht.Cells[iRows, ht];
shtcel2 = shtcell.End[Excel.XlDirection.xlToLeft];
sht.Range[shtcell, shtcel2].Merge(false);
}
textBox1.AppendText("\r\n转换透视表结束:" + sw.Elapsed);
this.Text = "发放统计";
sw.Stop();
MessageBox.Show("程序运行结束:"+sw.Elapsed, "完成");
}
List<string> listFenzu = new List<string>();
private void G1btnKx2_Click(object sender, EventArgs e)
{
G1_ListBCol.Items.Remove(G1_ListBCol.SelectedItem.ToString());
}
private void G1btnKx3_Click(object sender, EventArgs e)
{
G1_ListBCol.Items.Add(G1_ListBkexuan.SelectedItem.ToString());
}
private void G1btnKx1_Click(object sender, EventArgs e)
{
G1_ListBCol.Items.Clear();
}
private void G1btnKx4_Click(object sender, EventArgs e)
{
G1_ListBCol.Items.Clear();
foreach (string s in G1_ListBkexuan.Items)
{
G1_ListBCol.Items.Add(s);
}
}
private void G1btnFzUp_Click(object sender, EventArgs e)
{
int iIndex = G1_ListBfenzu.SelectedIndex;
string a = G1_ListBfenzu.Items[iIndex - 1].ToString();
string b = G1_ListBfenzu.SelectedItem.ToString() ;
G1_ListBfenzu.Items[iIndex] = a;
G1_ListBfenzu.SetSelected(iIndex - 1, true);
G1_ListBfenzu.Items[iIndex-1] = b;
}
private void G1btnFzDn_Click(object sender, EventArgs e)
{
int iIndex = G1_ListBfenzu.SelectedIndex;
string a = G1_ListBfenzu.Items[iIndex + 1].ToString();
string b = G1_ListBfenzu.SelectedItem.ToString();
G1_ListBfenzu.Items[iIndex] = a;
G1_ListBfenzu.SetSelected(iIndex + 1, true);
G1_ListBfenzu.Items[iIndex + 1] = b;
}
private void G1btnFz1_Click(object sender, EventArgs e)
{
G1_ListBfenzu.Items.Clear();
foreach (string s in G1_ListBkexuan.Items)
{
G1_ListBfenzu.Items.Add(s);
}
}
private void G1btnFz2_Click(object sender, EventArgs e)
{
G1_ListBfenzu.Items.Add(G1_ListBkexuan.SelectedItem.ToString());
}
private void G1btnFz3_Click(object sender, EventArgs e)
{
G1_ListBfenzu.Items.Remove(G1_ListBfenzu.SelectedItem);
}
private void G1btnFz4_Click(object sender, EventArgs e)
{
G1_ListBfenzu.Items.Clear();
}
private void G1btnPxUp_Click(object sender, EventArgs e)
{
int iIndex = G1_ListBpaixu.SelectedIndex;
string a = G1_ListBpaixu.Items[iIndex - 1].ToString();
string b = G1_ListBpaixu.SelectedItem.ToString();
G1_ListBpaixu.Items[iIndex] = a;
G1_ListBpaixu.SetSelected(iIndex - 1, true);
G1_ListBpaixu.Items[iIndex - 1] = b;
}
private void G1btnPxDn_Click(object sender, EventArgs e)
{
int iIndex = G1_ListBpaixu.SelectedIndex;
string a = G1_ListBpaixu.Items[iIndex + 1].ToString();
string b = G1_ListBpaixu.SelectedItem.ToString();
G1_ListBpaixu.Items[iIndex] = a;
G1_ListBpaixu.SetSelected(iIndex + 1, true);
G1_ListBpaixu.Items[iIndex + 1] = b;
}
List<string> Gslist = new List<string>();
List<string> Gslist2 = new List<string>();
private void G2btn1_Click(object sender, EventArgs e)
{
//G2Cb1 计算字段
//G2Cb2 计算方法
/*
求和 Sum xlSum
统计计数 Count xlCount
去重计数 CountIF
平均值 AVERAGE xlAverage
最大值 Max
最小值 Min
*/
//G2tB1 别名
string Gs = "";
switch (G2Cb2.SelectedItem.ToString())
{
case "求和":
Gs = "sum(";
break;
case "计数":
Gs = "Count(";
break;
case "计数(去重)":
Gs = "Count(DISTINCT ";
break;
case "平均值":
Gs = "AVG(";
break;
case "最大值":
Gs = "Max(";
break;
case "最小值":
Gs = "Min(";
break;
}
string str="";
// sum("金额") as '金额'
if (G2tB1.Text != "")
{
Gslist.Add(G2tB1.Text.ToString());
str = string.Format("{0}\"{1}\") as '{2}'", Gs, G2Cb1.SelectedItem.ToString(), G2tB1.Text);
}
else
{
Gslist.Add(G2Cb1.SelectedItem.ToString());
str = string.Format("{0}\"{1}\")as '{1}'", Gs, G2Cb1.SelectedItem.ToString());
}
G2_ListBtongji.Items.Add(str);
Gslist2.Add(str);
}
private void G2btn3_Click(object sender, EventArgs e)
{
G2_ListBtongji.Items.Clear();
Gslist.Clear();
Gslist2.Clear();
}
private void G2btnUp_Click(object sender, EventArgs e)
{
int iIndex = G2_ListBtongji.SelectedIndex;
string a = G2_ListBtongji.Items[iIndex - 1].ToString();
string b = G2_ListBtongji.SelectedItem.ToString();
G2_ListBtongji.Items[iIndex] = a;
G2_ListBtongji.SetSelected(iIndex - 1, true);
G2_ListBtongji.Items[iIndex - 1] = b;
}
private void G2btnDn_Click(object sender, EventArgs e)
{
int iIndex = G2_ListBtongji.SelectedIndex;
string a = G2_ListBtongji.Items[iIndex + 1].ToString();
string b = G2_ListBtongji.SelectedItem.ToString();
G2_ListBtongji.Items[iIndex] = a;
G2_ListBtongji.SetSelected(iIndex + 1, true);
G2_ListBtongji.Items[iIndex + 1] = b;
}
private void G3cB2_SelectedIndexChanged(object sender, EventArgs e)
{
if (G3cB2.SelectedItem.ToString() == "模糊")
{
G3cB3.Enabled = false;
G3cB3.Items.Add("Like");
G3cB3.SelectedItem = "Like";
}
else
{
G3cB3.Enabled = true;
G3cB3.Items.Remove("Like");
G3cB3.SelectedItem = "=";
}
}
private void G3btn1_Click(object sender, EventArgs e)
{
//WHERE "就读学校（幼儿园）" like '%中学%'
string str="";
if (G3_ListBguolv.Items.Count == 0)
{
if (!G3cB3.Enabled)
{
str = string.Format("(\"{0}\" {1} '%{2}%')", G3cB1.SelectedItem.ToString(), G3cB3.SelectedItem.ToString(), G3tb1.Text.ToString());
}
else
{
str = string.Format("(\"{0}\" {1} '{2}')", G3cB1.SelectedItem.ToString(), G3cB3.SelectedItem.ToString(), G3tb1.Text.ToString());
}
}
else
{
if (!G3cB3.Enabled)
{
str = string.Format("{0} (\"{1}\" {2} '%{3}%')", G3cB4.SelectedItem.ToString(), G3cB1.SelectedItem.ToString(), G3cB3.SelectedItem.ToString(), G3tb1.Text.ToString());
}
else
{
str = string.Format("{0} (\"{1}\" {2} '{3}')", G3cB4.SelectedItem.ToString(), G3cB1.SelectedItem.ToString(), G3cB3.SelectedItem.ToString(), G3tb1.Text.ToString());
}
}
G3_ListBguolv.Items.Add(str);
}
private void G2btn2_Click(object sender, EventArgs e)
{
int yc = Gslist2.IndexOf(G2_ListBtongji.SelectedItem.ToString());
Gslist.Remove(Gslist[yc].ToString());
Gslist2.Remove(G2_ListBtongji.SelectedItem.ToString());
G2_ListBtongji.Items.Remove(G2_ListBtongji.SelectedItem);
}
private void G3btn2_Click(object sender, EventArgs e)
{
G3_ListBguolv.Items.Remove(G3_ListBguolv.SelectedItem);
}
private void G3btn3_Click(object sender, EventArgs e)
{
G3_ListBguolv.Items.Clear();
}
private void G3btnDelete_Click(object sender, EventArgs e)
{
G3_ListBguolv.Items.Remove(G3_ListBguolv.SelectedItem);
}
private void G3btnClear_Click(object sender, EventArgs e)
{
G3_ListBguolv.Items.Clear();
}
private void G3btnUp_Click(object sender, EventArgs e)
{
int iIndex = G3_ListBguolv.SelectedIndex;
string a = G3_ListBguolv.Items[iIndex - 1].ToString();
string b = G3_ListBguolv.SelectedItem.ToString();
G3_ListBguolv.Items[iIndex] = a;
G3_ListBguolv.SetSelected(iIndex - 1, true);
G3_ListBguolv.Items[iIndex - 1] = b;
}
private void G3btnDn_Click(object sender, EventArgs e)
{
int iIndex = G3_ListBguolv.SelectedIndex;
string a = G3_ListBguolv.Items[iIndex + 1].ToString();
string b = G3_ListBguolv.SelectedItem.ToString();
G3_ListBguolv.Items[iIndex] = a;
G3_ListBguolv.SetSelected(iIndex + 1, true);
G3_ListBguolv.Items[iIndex + 1] = b;
}
private void button23_Click(object sender, EventArgs e)
{
//WHERE "就读学校（幼儿园）" like '%中学%'
string str = "";
if (G3_ListBguolv.Items.Count == 0)
{
if (!G3cB3.Enabled)
{
str = string.Format("(\"{0}\" {1} '%{2}%')", G3cB1.SelectedItem.ToString(), G3cB3.SelectedItem.ToString(), G3tb1.Text.ToString());
}
else
{
str = string.Format("(\"{0}\" {1} '{2}')", G3cB1.SelectedItem.ToString(), G3cB3.SelectedItem.ToString(), G3tb1.Text.ToString());
}
}
else
{
if (!G3cB3.Enabled)
{
str = string.Format("{0} (\"{1}\" {2} '%{3}%')", G3cB4.SelectedItem.ToString(), G3cB1.SelectedItem.ToString(), G3cB3.SelectedItem.ToString(), G3tb1.Text.ToString());
}
else
{
str = string.Format("{0} (\"{1}\" {2} '{3}')", G3cB4.SelectedItem.ToString(), G3cB1.SelectedItem.ToString(), G3cB3.SelectedItem.ToString(), G3tb1.Text.ToString());
}
}
G3_ListBguolv.Items.Add(str);
}
private void nUDtitle_ValueChanged(object sender, EventArgs e)
{
G1_ListBkexuan.Items.Clear();
G2Cb1.Items.Clear();
G3cB1.Items.Clear();
iT = int.Parse(nUDtitle.Value.ToString());
nUDd.Value = iT + 1;
//iCols = sht.Range["A" + iT].CurrentRegion.Columns.Count;
//iRows = sht.Range["A" + iT].CurrentRegion.Rows.Count;
iCols = sht.Range["B"+iT].CurrentRegion.Columns.Count;
iRows = sht.Range["B" + iT].CurrentRegion.Rows.Count;
rng = sht.Range["A" + iT].Resize[1, iCols];
object[,] title = rng.Value;
for (int i = 1; i <= title.GetLength(1); i++)
{
string values = title[1, i] != null ? title[1, i].ToString() : "Column" + i;
G1_ListBkexuan.Items.Add(values);
G2Cb1.Items.Add(values);
G3cB1.Items.Add(values);
//listTitle.Add(r.Value);
//listJszd.Add(r.Value);
//listGlv.Add(r.Value);
}
//foreach (Excel.Range r in rng)
//{
// //listTitle.Add(r.Value);
// //listJszd.Add(r.Value);
// //listGlv.Add(r.Value);
// if (r.Value == null)
// {
// break;
// }
// G1_ListBkexuan.Items.Add(r.Value);
// G2Cb1.Items.Add(r.Value);
// G3cB1.Items.Add(r.Value );
//}
//G1_ListBkexuan.DataSource = listTitle;
//G2Cb1.DataSource = listJszd;
//G3cB1.DataSource = listGlv;
}
private void button2_Click(object sender, EventArgs e)
{
int iIndex = G1_ListBCol.SelectedIndex;
string a = G1_ListBCol.Items[iIndex - 1].ToString();
string b = G1_ListBCol.SelectedItem.ToString();
G1_ListBCol.Items[iIndex] = a;
G1_ListBCol.SetSelected(iIndex - 1, true);
G1_ListBCol.Items[iIndex - 1] = b;
}
private void button1_Click(object sender, EventArgs e)
{
int iIndex = G1_ListBCol.SelectedIndex;
string a = G1_ListBCol.Items[iIndex + 1].ToString();
string b = G1_ListBCol.SelectedItem.ToString();
G1_ListBCol.Items[iIndex] = a;
G1_ListBCol.SetSelected(iIndex + 1, true);
G1_ListBCol.Items[iIndex + 1] = b;
}
private void Gbtn_pX1_Click(object sender, EventArgs e)
{
G1_ListBpaixu.Items.Clear();
foreach (string s in G1_ListBfenzu.Items)
{
G1_ListBpaixu.Items.Add(s);
}
}
private void Gbtn_pX2_Click(object sender, EventArgs e)
{
G1_ListBpaixu.Items.Add(G1_ListBfenzu.SelectedItem.ToString());
}
private void Gbtn_pX3_Click(object sender, EventArgs e)
{
G1_ListBpaixu.Items.Remove(G1_ListBpaixu.SelectedItem);
}
private void Gbtn_pX_Click(object sender, EventArgs e)
{
G1_ListBpaixu.Items.Clear();
}
private void button3_Click(object sender, EventArgs e)
{
string fileName = System.AppDomain.CurrentDomain.SetupInformation.ApplicationBase + "Image\\原图\\数据高级统计.png";
System.Diagnostics.Process.Start(fileName);
}
}
}
