using Microsoft.Office.Interop.Excel;
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.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;
namespace ExportWindows
{
public partial class Form1 : Form
{
private string ExcleName = ("Excel" + DateTime.Now.Year + (DateTime.Now.Month < 10 ? "0" + DateTime.Now.Month : DateTime.Now.Month.ToString())
+ (DateTime.Now.Day < 10 ? "0" + DateTime.Now.Day : DateTime.Now.Day.ToString())) + DateTime.Now.Hour + DateTime.Now.Minute + DateTime.Now.Second
+ DateTime.Now.Millisecond.ToString().Replace("/", "").Replace(":", "").Replace(" ", "").Trim();
public Form1()
{
InitializeComponent();
}
/// <summary>
/// 读取数据库数据
/// </summary>
/// <returns></returns>
private DataView BindUser()
{
String connStr = "Data Source =.;DataBase = AdventureWorks;uid = sa;pwd = sa";
string sql = "select ContactID,FirstName,LastName,EmailAddress from person where ContactId <= 1";
SqlConnection conn;
DataSet ds = null;
SqlDataAdapter adapter;
conn = new SqlConnection(connStr);
try
{
conn.Open();
adapter = new SqlDataAdapter(sql, conn);
ds = new DataSet();
adapter.Fill(ds);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
conn.Dispose();
}
}
return ds == null ? null : (ds.Tables.Count == 0 ? null : ds.Tables[0].DefaultView);
}
/// <summary>
/// (DataGridView)导出EXCEL
/// </summary>
/// <returns></returns>
private bool ExportFile()
{
try
{
if (this.dataGridView1.Rows.Count < 1)
MessageBox.Show("没有数据可以导出!");
else
{
//建立Excel对象
Excel.Application excel = new Excel.Application();
if (excel == null)
{
MessageBox.Show("创建Excel失败!");
}
else
{
excel.Application.Workbooks.Add(true).SaveAs(ExcleName);
excel.Visible = true;
//生成字段
for (int i = 1; i <= this.dataGridView1.ColumnCount; i++)
{
excel.Cells[1, i] = this.dataGridView1.Columns[i - 1].HeaderText;
}
//填充数据
for (int i = 0; i < this.dataGridView1.RowCount; i++)
{
for (int j = 0; j < this.dataGridView1.ColumnCount; j++)
{
if (this.dataGridView1[j, i].ValueType == typeof(string))
excel.Cells[i + 2, j + 1] = "" + this.dataGridView1[j, i].Value.ToString();
else
excel.Cells[i + 2, j + 1] = this.dataGridView1[j, i].Value.ToString();
}
}
}
}
}
catch (Exception ee)
{
MessageBox.Show(ee.Message);
}
return false;
}
/// <summary>
/// 获取Excel数据
/// </summary>
/// <param name="pathName">Excel文件路径</param>
/// <param name="sheetName">选择Sheet名称</param>
/// <returns></returns>
private System.Data.DataTable ExcelToDataTable(string pathName, string sheetName)
{
System.Data.DataTable tbContainer = new System.Data.DataTable();
string strConn = string.Empty;
if (string.IsNullOrEmpty(sheetName))
sheetName = "Sheet1";
FileInfo file = new FileInfo(pathName);
if (!file.Exists)
throw new Exception("文件不存在!");
string extension = file.Extension;
switch (extension)
{
case ".xls":
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathName
+ ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";
break;
case ".xlsx":
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + pathName
+ ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1;'";
break;
default:
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathName
+ ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";
break;
}
OleDbConnection connxls = null;
try
{
//链接Excel
connxls = new OleDbConnection(strConn);
connxls.Open();
//读取Excel里面有表Sheet1
OleDbDataAdapter oda = new OleDbDataAdapter(string.Format("select * from[{0}$]", sheetName), connxls);
DataSet ds = new DataSet();
oda.Fill(tbContainer);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
if (connxls != null)
if (connxls.State == ConnectionState.Open)
{
connxls.Close();
connxls.Dispose();
}
}
return tbContainer;
}
/// <summary>
/// 获取Excel中所有Sheet名称
/// </summary>
/// <param name="excelFile"></param>
/// <returns></returns>
private string[] GetExcelSheetNames(string excelFile)
{
OleDbConnection odConn = null;
System.Data.DataTable dt = null;
try
{
string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelFile
+ ";Extended Properties ='Excel 12.0;HDR=Yes;IMEX=1;'";
odConn = new OleDbConnection(strConn);
odConn.Open();
dt = odConn.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[2].ToString();
return excelSheets;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
if (odConn.State == ConnectionState.Open)
{
odConn.Close();
odConn.Dispose();
}
}
return null;
}
/// <summary>
/// 数据库查询
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button1_Click(object sender, EventArgs e)
{
this.dataGridView1.DataSource = this.BindUser();
this.dataGridView1.AllowUserToAddRows = false;
}
/// <summary>
/// 导出
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button2_Click(object sender, EventArgs e)
{
this.ExportFile();
}
/// <summary>
/// 导入
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button3_Click(object sender, EventArgs e)
{
var win = new OpenFileDialog();
win.ShowDialog();
if (win.FileName != null)
{
this.dataGridView1.AllowUserToAddRows = false;
this.dataGridView1.DataSource = this.ExcelToDataTable(win.FileName, null);
}
}
private void button4_Click(object sender, EventArgs e)
{
var win = new OpenFileDialog();
win.ShowDialog();
var sheets = this.GetExcelSheetNames(win.FileName);
if (sheets != null)
for (int i = 0; i < sheets.Length; i++)
this.listBox1.Items.Add(sheets[i].ToString());
}
}
}