代码
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 GemBox.ExcelLite;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Collections;
using System.IO;
namespace Encryption
{
public partial class ExcelDemo : Form
{
private static string _filePath = string.Empty;
public ExcelDemo()
{
InitializeComponent();
BindUser();
}
/// <summary>
/// 绑定数据
/// </summary>
private void BindUser()
{
string sql = "select * from Users";
DataTable dt = DbHelperSQL.QueryTb(sql);
dataGridView1.DataSource = dt;
}
/// <summary>
/// 将 Users表中的数据导入Excel中
/// </summary>
private void btnExcelin_Click(object sender, EventArgs e)
{
ExcelFile excelFile = new ExcelFile();
ExcelWorksheet sheet = excelFile.Worksheets.Add("Users");
int columns = dataGridView1.Columns.Count;
int rows = dataGridView1.Rows.Count;
for (int j = 0; j < columns; j++)
{
sheet.Cells[0, j].Value = dataGridView1.Columns[j].HeaderText;
}
for (int i = 1; i < rows; i++)
{
for (int j = 0; j < columns; j++)
{
sheet.Cells[i, j].Value = dataGridView1[j, i - 1].Value.ToString().Trim();
}
}
excelFile.SaveXls("./Users.xls");
MessageBox.Show("生成成功");
}
/// <summary>
/// 选择要向SQL数据库中导入数据的Excel文件
/// </summary>
private void btnChoose_Click(object sender, EventArgs e)
{
using (OpenFileDialog dialog = new OpenFileDialog())
{
dialog.Multiselect = true;
if (dialog.ShowDialog() == DialogResult.OK)
{
try
{
txtPath.Text = dialog.FileName;
}
catch { }
}
}
}
/// <summary>
/// 将 Excel中的数据导入到SQL数据库中
/// </summary>
private void btnExcelout_Click(object sender, EventArgs e)
{
DataSet ds=ImportFromExcel(txtPath.Text.Trim());
DataTable dt = ds.Tables[0];
try
{
string strInsertComm;
for (int i = 0; i < dt.Rows.Count; i++)
{
strInsertComm = "";
strInsertComm = "Insert INTO NewUsers(LoginNum,UserName,Password,Email,UserHead,Sex,BloodType,BirthPlace,UserLevel,Education,Occupational,Contact,[Profile])";
strInsertComm += " values(";
for (int j = 1; j < dt.Columns.Count; j++)
{
if (j > 1)
{
strInsertComm += ",'" + dt.Rows[i][j].ToString().Trim() + "'";
}
else
{
strInsertComm += "'" + dt.Rows[i][j].ToString().Trim() + "'";
}
}
strInsertComm += ")";
DbHelperSQL.ExecuteSql(strInsertComm);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
#region Excel导入SQL数据库
/// <summary>
/// 获取 Excel数据表列表
/// </summary>
/// <returns></returns>
public static ArrayList GetExcelTables(string FilePath)
{
//将Excel架构存入数据里
System.Data.DataTable dt = new System.Data.DataTable();
ArrayList TablesList = new ArrayList();
if (File.Exists(FilePath))
{
using (OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet." +
"OLEDB.4.0;Extended Properties=\"Excel 8.0\";Data Source=" + FilePath))
{
try
{
conn.Open();
dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
}
catch (Exception exp)
{
MessageBox.Show(exp.Message);
}
//获取数据表个数
int tablecount = dt.Rows.Count;
for (int i = 0; i < tablecount; i = i + 2)
{
string tablename = dt.Rows[i][2].ToString().Trim().TrimEnd('$');
if (TablesList.IndexOf(tablename) < 0)
{
TablesList.Add(tablename);
}
}
}
}
return TablesList;
}
/// <summary>
/// 导入 Excel数据表至DataTable（第一行作为表头）
/// </summary>
/// <returns></returns>
public static System.Data.DataSet FillDataSet(string FilePath)
{
if (!File.Exists(FilePath))
{
throw new Exception("Excel文件不存在！");
}
ArrayList TableList = new ArrayList();
TableList = GetExcelTables(FilePath);
if (TableList.Count <= 0)
{
return null;
}
System.Data.DataTable table;
System.Data.DataSet ds = new DataSet();
OleDbConnection dbcon = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FilePath + ";Extended Properties=Excel 8.0");
try
{
if (dbcon.State == ConnectionState.Closed)
{
dbcon.Open();
}
for (int i = 0; i < TableList.Count; i++)
{
string dtname = TableList[i].ToString();
try
{
OleDbCommand cmd = new OleDbCommand("select * from [" + dtname + "$]", dbcon);
OleDbDataAdapter adapter = new OleDbDataAdapter(cmd);
table = new DataTable(dtname);
adapter.Fill(table);
ds.Tables.Add(table);
}
catch (Exception exp)
{
MessageBox.Show(exp.Message);
}
}
}
finally
{
if (dbcon.State == ConnectionState.Open)
{
dbcon.Close();
}
}
return ds;
}
/// <summary>
/// Excel 导入数据库
/// </summary>
/// <returns></returns>
public static DataSet ImportFromExcel(string FilePath)
{
return FillDataSet(FilePath);
}
#endregion
}
}
以上代码来自园中《C#将SQL数据库中数据导入Excel中，并将Excel中反导入SQL数据库中》写的很详细。
假如我们不使用第三方控件，在对gridview中数据导入Excel中时，发现在加入母版页后，会出现报错。报错：类型“GridView”的控件“ctl00_ContentPlaceHolder1_GridV..
在网上搜一下发现解决方法有很多。现在把一个个人认为很简单的方法，在此分享一下：
1。加入<%Page EnableEventValidation="false" %>
2。加入using System.IO;
3。加入
代码
public override void VerifyRenderingInServerForm(Control control)
{
//重写这个方法，就不会报错了
}
protected void Button2_Click(object sender, EventArgs e)
{
toExecl(GridView1);
}
// 将GridView数据导出到EXECL
private void toExecl(System.Web.UI.WebControls.GridView gv)
{
Response.Clear();
Response.Buffer = true;
Response.Charset = "GB2312";
Response.AppendHeader("Content-Disposition", "attachment;filename=Name.xls"); //Name为保存的文件名
Response.ContentEncoding = System.Text.Encoding.UTF7;//解决乱码问题
Response.ContentType = "application/ms-excel";//设置输出文件类型
System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
this.GridView1.RenderControl(oHtmlTextWriter);
Response.Output.Write(oStringWriter.ToString());
Response.Flush();
Response.End();
}
这些小技巧，网上有很多，在此分享一下，同时方便自己以后用到。