winfrom中comboBox的模糊查询和datagridview的导入、导出

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using Microsoft.Office.Interop.Excel;

namespace 模糊显示
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}

//初始化绑定默认关键词（此数据源可以从数据库取）
List<string> listOnit = new List<string>();
//输入key之后，返回的关键词
List<string> listNew = new List<string>();

private void Form1_Load(object sender, EventArgs e)
{
//调用绑定
BindComboBox();
GetData();

}

private void GetData()
{
string con = "server=.;database=Text;uid=sa;pwd=123456";
string str = "select ID,UserName,Age,Sex from UserInfo";
DataSet ds = new DataSet();
SqlDataAdapter dap = new SqlDataAdapter(str, con);
dap.Fill(ds);
dataGridView1.DataSource=ds.Tables[0];
}

/// <summary>
/// 绑定ComboBox
/// </summary>
private void BindComboBox()
{
//listOnit.Add("张三");
//listOnit.Add("张思");
//listOnit.Add("张五");
//listOnit.Add("王五");
//listOnit.Add("刘宇");
//listOnit.Add("马六");
//listOnit.Add("孙楠");
//listOnit.Add("那英");
//listOnit.Add("刘欢");

string con = "server=.;database=Text;uid=sa;pwd=123456";
string str = "select*from UserInfo";
DataSet ds = new DataSet();
SqlDataAdapter dap = new SqlDataAdapter(str ,con);
dap.Fill(ds);
foreach (DataRow item in ds.Tables[0].Rows)
{
listOnit.Add(item["UserName"].ToString());
}
/*
* 1.注意用Item.Add(obj)或者Item.AddRange(obj)方式添加
* 2.如果用DataSource绑定，后面再进行绑定是不行的，即便是Add或者Clear也不行
*/
this.comboBox1.Items.AddRange(listOnit.ToArray());
}

private void comboBox1_TextUpdate(object sender, EventArgs e)
{
//清空combobox
this.comboBox1.Items.Clear();
//清空listNew
listNew.Clear();
//遍历全部备查数据
foreach (var item in listOnit)
{
if (item.Contains(this.comboBox1.Text))
{
//符合，插入ListNew
listNew.Add(item);
}
}
//combobox添加已经查到的关键词
this.comboBox1.Items.AddRange(listNew.ToArray());
//设置光标位置，否则光标位置始终保持在第一列，造成输入关键词的倒序排列
this.comboBox1.SelectionStart = this.comboBox1.Text.Length;
//保持鼠标指针原来状态，有时候鼠标指针会被下拉框覆盖，所以要进行一次设置。
Cursor = Cursors.Default;
//自动弹出下拉框
this.comboBox1.DroppedDown = true;

}

private void EXECL_OUT_Click(object sender, EventArgs e)
{
OutToExcelFromDataGridView("", dataGridView1,true);
}

public static bool OutToExcelFromDataGridView(string title, DataGridView dgv, bool isShowExcel)
{
int titleColumnSpan = 0;//标题的跨列数
string fileName = "";//保存的excel文件名
int columnIndex = 1;//列索引
if (dgv.Rows.Count == 0)
return false;
/*保存对话框*/
SaveFileDialog sfd = new SaveFileDialog();
sfd.FileName = title + DateTime.Now.ToString("yyyyMMddhhmmss");

if (sfd.ShowDialog() == DialogResult.OK)
{
fileName = sfd.FileName;
sfd.Filter = "Excel(*.xls)|*.xls";
/*建立Excel对象*/
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
if (excel == null)
{
MessageBox.Show("无法创建Excel对象,可能您的计算机未安装Excel!");
return false;
}
try
{
excel.Application.Workbooks.Add(true);
excel.Visible = isShowExcel;
/*分析标题的跨列数*/
foreach (DataGridViewColumn column in dgv.Columns)
{
if (column.Visible == true)
titleColumnSpan++;
}
/*合并标题单元格*/
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)excel.ActiveSheet;
//worksheet.get_Range("A1", "C10").Merge();
// worksheet.get_Range(worksheet.Cells[1, 1] as Range, worksheet.Cells[1, titleColumnSpan] as Range).Merge();
/*生成标题*/
// excel.Cells[1, 1] = title;
// (excel.Cells[1, 1] as Range).HorizontalAlignment = XlHAlign.xlHAlignCenter;//标题居中
//生成字段名称
// columnIndex = 1;
for (int i = 0; i < dgv.ColumnCount; i++)
{
if (dgv.Columns[i].Visible == true)
{
excel.Cells[1, columnIndex] = dgv.Columns[i].HeaderText;
(excel.Cells[1, columnIndex] as Range).HorizontalAlignment = XlHAlign.xlHAlignCenter;//字段居中
columnIndex++;
}
}
//填充数据
for (int i = 0; i < dgv.RowCount; i++)
{
columnIndex = 1;
for (int j = 0; j < dgv.ColumnCount; j++)
{
if (dgv.Columns[j].Visible == true)
{
excel.Cells[i + 2, columnIndex] = dgv[j, i].Value.ToString();
(excel.Cells[i + 2, columnIndex] as Range).HorizontalAlignment = XlHAlign.xlHAlignLeft;//字段居中
columnIndex++;
}
}
}
worksheet.SaveAs(fileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

}
catch { }
finally
{
excel.Quit();
excel = null;
GC.Collect();
}
// KillProcess("Excel");
return true;
}
else
{
return false;
}
}
private static void KillProcess(string processName)//杀死与Excel相关的进程
{
System.Diagnostics.Process myproc = new System.Diagnostics.Process();//得到所有打开的进程
try
{
foreach (System.Diagnostics.Process thisproc in System.Diagnostics.Process.GetProcessesByName(processName))
{
if (!thisproc.CloseMainWindow())
{
thisproc.Kill();
}
}
}
catch (Exception Exc)
{
throw new Exception("", Exc);
}
}

/// <summary>
/// 获取sheet
/// </summary>
/// <param name="excelFile"></param>
/// <returns></returns>
public static String[] GetExcelSheetNames(string excelFile)
{
OleDbConnection objConn = null;
System.Data.DataTable dt = null;

try
{
objConn = new OleDbConnection(ConnectionString(excelFile));
objConn.Open();
dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
if (dt == null)
{
return null;
}
String[] excelSheets = new String[dt.Rows.Count];
int i = 0;
foreach (DataRow row in dt.Rows)
{
excelSheets[i] = row["TABLE_NAME"].ToString();
i++;
}

return excelSheets;
}
catch
{
return null;
}
finally
{
if (objConn != null)
{
objConn.Close();
objConn.Dispose();
}
if (dt != null)
{
dt.Dispose();
}
}
}

/// <summary>
/// 导入
/// </summary>
/// <param name="filePath"></param>
/// <returns></returns>
public static bool Import(string filePath)
{
try
{

//Excel就好比一个数据源一般使用

OleDbConnection con = new OleDbConnection(ConnectionString(filePath));
con.Open();
string[] names = GetExcelSheetNames(filePath);
if (names.Length > 0)
{
foreach (string name in names)
{
OleDbCommand cmd = con.CreateCommand();
cmd.CommandText = string.Format(" select * from [{0}]", name);//[sheetName$]要如此格式
OleDbDataReader odr = cmd.ExecuteReader();

while (odr.Read())
{

if (odr[0].ToString() == "ID")//过滤列头 按你的实际Excel文件
continue;
string conn = "server=.;database=Text;uid=sa;pwd=123456";
SqlConnection st = new SqlConnection(conn);
st.Open();
string str = string.Format("insert UserInfo(UserName,Age,Sex) values('{0}','{1}','{2}')", odr[1].ToString(), odr[2].ToString(),odr[3].ToString());
SqlCommand scmd = new SqlCommand(str,st);
scmd.ExecuteNonQuery();
// Add(odr[1].ToString(), odr[2].ToString(), odr[3].ToString());//数据库添加操作
st.Close();
}
odr.Close();

}
}
return true;
}
catch (Exception)
{
return false;
}

}

/// <summary>
/// 导入
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void EXECL_IN_Click(object sender, EventArgs e)
{
OpenFileDialog ofd = new OpenFileDialog();
if (ofd.ShowDialog() == DialogResult.OK)
{
if (Import(ofd.FileName) == true)
{
MessageBox.Show("导入成功！");
GetData();
}
else
{
MessageBox.Show("导入失败！");
}
}
}

private static string ConnectionString(string fileName)
{
bool isExcel2003 = fileName.EndsWith(".xls");
string connectionString = string.Format(
isExcel2003
? "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Excel 8.0;"
:"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'",
fileName);
return connectionString;
}
}
}