1,导入到excel:
Public Function ToExcel(ByVal da As DataGridView)
Dim MyExcel As New Microsoft.Office.Interop.Excel.Application()
MyExcel.Application.Workbooks.Add(True)
MyExcel.Visible = True
'去除dataGridView1的编号列(这里也可以不要)
Dim m As Integer
For m = 0 To da.ColumnCount - 1
MyExcel.Cells(1, m + 1) = da.Columns(m).HeaderText
Next m
'往excel表里添加数据
Dim i As Integer
For i = 0 To da.RowCount - 1
Dim j As Integer
For j = 0 To da.ColumnCount - 1
If da(j, i).Value Is System.DBNull.Value Then
MyExcel.Cells(i + 2, j + 1) = ""
Else
MyExcel.Cells(i + 2, j + 1) = da(j, i).Value.ToString
End If
Next j
Next i
Return True
End Function
2,从excel中导入数据到数据库
B层
/*******************************************************************************
*文 件:CreateExcelDataBLL.cs
*文件说明:基础系统-把excel转化为datatable
*创建日期:2013年1月23日9:43:16
*修改作者:
*修改日期:
*修改描述:
*版 本 号:V1.0
*版本号变更记录:
********************************************************************************/
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
//引用各命名空间
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.IO;
namespace BLL
{
public class CreateExcelDataBLL
{
//构造方法
public CreateExcelDataBLL()
{
}
/// <summary>
/// 传入excel路径,转换为datatable
/// </summary>
/// <param name="url"></param>
/// <returns></returns>
public DataTable CreateExcelDataSource(string url)
{
//定义一个DataTable数据表
DataTable dt = null;
//获得excel数据
string connetionStr = "Provider=Microsoft.Jet.OleDb.4.0;" + "data source=" + url + ";Extended Properties='Excel 8.0; HDR=YES; IMEX=1'";
//从Excel表的Sheet1单元格获取数据
string strSql = "select * from [Sheet1$]";
OleDbConnection oleConn = new OleDbConnection(connetionStr);
OleDbDataAdapter oleAdapter = new OleDbDataAdapter(strSql, connetionStr);
try
{
//把Excel数据填充给DataTable
dt = new DataTable();
oleAdapter.Fill(dt);
//返回数据表
return removeEmpty(dt);
}
catch (Exception ex)
{
throw ex;
}
finally
{
oleAdapter.Dispose();
oleConn.Close();
oleConn.Dispose();
//删除上传的Excel文件(因为该文件的存在会占用多余的网站空间)
if (File.Exists(url))
{
File.Delete(url);
}
}
}
/// <summary>
/// 循环去除datatable中的空行
/// </summary>
/// <param name="dt"></param>
protected DataTable removeEmpty(DataTable dt)
{
List<DataRow> removelist = new List<DataRow>();
for (int i = 0; i < dt.Rows.Count; i++)
{
bool rowdataisnull = true;
for (int j = 0; j < dt.Columns.Count; j++)
{
if (!string.IsNullOrEmpty(dt.Rows[i][j].ToString().Trim()))
{
rowdataisnull = false;
}
}
if (rowdataisnull)
{
removelist.Add(dt.Rows[i]);
}
}
for (int i = 0; i < removelist.Count; i++)
{
dt.Rows.Remove(removelist[i]);
}
return dt;
}
}
}
D层
/// <summary>
/// 批量导入教师
/// </summary>
/// <param name="ds">Dataset</param>
/// <returns>布尔值</returns>
public Boolean ImportMember(DataSet ds)
{
//添加教师信息
Boolean flagAddMember;
//调用sqlHelper的"批量导入datatable表"的方法
try
{
flagAddMember = DbHelperSQL.InsertTable(ds.Tables["dt_AddDutyTeacher"], "T_DutyTeacherInfo", ds.Tables["dt_AddDutyTeacher"].Columns);
}
catch (Exception e)
{
return false;
}
return true;
}
界面:
#region 批量信息
protected void btnImportTeacher_Click(object sender, EventArgs e)
{
//成员业务逻辑层
BLL.DutyTeacherInfoBLL teacherInfo = new BLL.DutyTeacherInfoBLL ();
//BLL层把Excel转化为datatable类
BLL.CreateExcelDataBLL createExcelData = new BLL.CreateExcelDataBLL();
//将错误信息导出到Excel
BLL.DataTableToExcel dataTableToExcel = new BLL.DataTableToExcel();
//获取上传文件地址
string url = fupImportTeacher.PostedFile.FileName.ToString();
if (url == "")
{
//数据源为空,弹出提示:请选择Excel文件!
Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript' defer>alert('请选择Excel文件!');</script>");
return;
}
string urlLocation = url.Substring(url.LastIndexOf("\\") + 1);//获取文件名
DataTable dtAllMember;
//在系统中建文件夹up,并将excel文件另存
this.fupImportTeacher.SaveAs(Server.MapPath("~\\UploadFile") + "\\" + urlLocation);//记录文件名到服务器相对应的文件夹中
//获得文件路径
string strpath = Server.MapPath("~\\UploadFile") + "\\" + urlLocation;
//把上传的Excel转换为datatable
dtAllMember = createExcelData.CreateExcelDataSource(strpath);
/*-------------------------------------判断数据源是否合法--------------------------------------*/
//定义要求的字段数据
string[] headfields = { "星期", "教师姓名", "教师手机号", "教师QQ号", "备注" };
//判断dtAllTeacher中是否包含全部要求的字段
for (int i = 0; i < headfields.Length; i++)
{
//只要有一个字段不被包含,则提示"数据源缺少必要的字段",并退出循环和整个方法
if (!dtAllMember.Columns.Contains(headfields[i]))
{
Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript' defer>alert('数据源缺少必要的字段,请检查Excel数据源!');</script>");
//退出方法
return;
}
}
//判断数据源中是否有数据
if (dtAllMember.Rows.Count == 0)
{
Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript' defer>alert('Excel文件中没有任何数据,请填充数据!');</script>");
//退出方法
return;
}
//判断是否有相同手机号的行
DataView dvTeacher = new DataView(dtAllMember);
if (dvTeacher.Count != dvTeacher.ToTable(true, "教师手机号").Rows.Count)
{
Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript' defer>alert('Excel中有相同的教师手机号,教师手机号不能相同!');</script>");
//退出方法
return;
}
DataSet dsMember = new DataSet("dt_DutyTeacher"); //创建一个名为dt_DutyTeacher的DataSet
//手动创建的新数据表-学生数据表
DataTable dtAddDutyTeacher = new DataTable("dt_AddDutyTeacher"); //创建一个名为dt_AddDutyTeacher的DataTalbe
//为dt_AddDutyTeacher表内建立Column(表头),添加数据列:
dtAddDutyTeacher.Columns.Add(new DataColumn("week", typeof(string)));
dtAddDutyTeacher.Columns.Add(new DataColumn("teacherName", typeof(string)));
dtAddDutyTeacher.Columns.Add(new DataColumn("teacherPhone", typeof(string)));
dtAddDutyTeacher.Columns.Add(new DataColumn("teacherQQ", typeof(string)));
dtAddDutyTeacher.Columns.Add(new DataColumn("explian", typeof(string)));
//新建数据表用于存放错误数据
DataTable dtErrorRow = new DataTable();
//为dtErrorRow创建列
dtErrorRow.Columns.Add(new DataColumn("星期", typeof(string)));
dtErrorRow.Columns.Add(new DataColumn("教师姓名", typeof(string)));
dtErrorRow.Columns.Add(new DataColumn("教师手机号", typeof(string)));
dtErrorRow.Columns.Add(new DataColumn("教师QQ号", typeof(string)));
dtErrorRow.Columns.Add(new DataColumn("备注", typeof(string)));
dtErrorRow.Columns.Add(new DataColumn("错误原因", typeof(string)));
//从上传的Excel转换为的datatable表中取出数据,放入值班教师信息。
for (int intRow = 0; intRow < dtAllMember.Rows.Count; intRow++)
{
//成员姓名
strweek = dtAllMember.Rows[intRow]["星期"].ToString();
//成员姓名
strteacherName = dtAllMember.Rows[intRow]["教师姓名"].ToString();
strteacherPhone = dtAllMember.Rows[intRow]["教师手机号"].ToString();
strQQ = dtAllMember.Rows[intRow]["教师QQ号"].ToString();
strExplian = dtAllMember.Rows[intRow]["备注"].ToString();
//判断星期格式是否正确
if(strweek !="星期一" && strweek !="星期二" && strweek !="星期三" && strweek !="星期四" &&strweek !="星期五" &&strweek !="星期六" &&strweek !="星期日" )
{
//向错误列表中添加当前行
AddErrorRow(dtErrorRow, "星期格式错误,只能为大写格式如:星期一");
//跳出当前循环
continue;
}
if (strweek == "")
{
//向错误列表中添加当前行
AddErrorRow(dtErrorRow, "星期不能为空");
//跳出当前循环
continue;
}
判断是否包含教师姓名
if (strteacherName == "")
{
//向错误列表中添加当前行
AddErrorRow(dtErrorRow, "教师姓名不能为空");
//跳出当前循环
continue;
}
//判断是否包含教师手机号
if (strteacherPhone == "")
{
//向错误列表中添加当前行
AddErrorRow(dtErrorRow, "教师手机号不能为空");
//跳出当前循环
continue;
}
判断是否包含教师QQ号
//if (strQQ == "")
//{
// //向错误列表中添加当前行
// AddErrorRow(dtErrorRow, "教师QQ号不能为空");
// //跳出当前循环
// continue;
//}
//添加成员信息表的新行
DataRow drAddDutyTeacher = dtAddDutyTeacher.NewRow();//注意这边创建dt的新行的方法。指定类型是DataRow而不是TableRow,然后不用new直接的用创建的DataTable下面的NewRow方法。
//学生信息表对应的各列值
drAddDutyTeacher["week"] = strweek;
drAddDutyTeacher["teacherName"] = strteacherName;
drAddDutyTeacher["teacherPhone"] = strteacherPhone;
drAddDutyTeacher["teacherQQ"] = strQQ;
drAddDutyTeacher["explian"] = strExplian ;
dtAddDutyTeacher.Rows.Add(drAddDutyTeacher); //将一整条数据写入表中
}
//将表加入DataSet中:成员信息
dsMember.Tables.Add(dtAddDutyTeacher);
//将DataSet中数据表导入数据库
Boolean flagImportMember = teacherInfo.ImportMember(dsMember);
//判断成员是否导入成功
if (true == flagImportMember && dtErrorRow.Rows.Count == 0)
{
//导入成功,弹出提示
Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript' defer>alert('成员信息导入成功!');</script>");
}
else if (false == flagImportMember)
{
//导入失败,弹出提示
Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript' defer>alert('成员信息导入失败!');</script>");
}
else if (true == flagImportMember && dtErrorRow.Rows.Count != 0)
{
//只有部分数据导入成功,弹出提示
Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript' defer>alert('成员信息只有部分导入成功,有错误的部分已导出为EXCEL,请查看新导出的EXCEL;新导出的Excel修改错误后可作为数据源重新导入');</script>");
//将错误数据导出到Excel并打开显示
dataTableToExcel.ToExcel(dtErrorRow);
}
}
#endregion
#region 向错误列表中添加当前行
/// <summary>
/// 向错误列表中添加当前行
/// </summary>
/// <param name="dtErrorRow">代表错误列表的datatable</param>
private void AddErrorRow(DataTable dtErrorRow, string reason)
{
//新建数据行
DataRow drAddErrorRow = dtErrorRow.NewRow();
//填充数据行
drAddErrorRow["星期"] = "\'" + strweek;
drAddErrorRow["教师姓名"] = "\'" + strteacherName ;
drAddErrorRow["教师手机号"] = strteacherPhone;
drAddErrorRow["教师QQ号"] = "\'" + strQQ;
drAddErrorRow["备注"] = strExplian;
drAddErrorRow["错误原因"] = reason;
dtErrorRow.Rows.Add(drAddErrorRow);
}
#endregion