这几天一直在倒腾Excel导入这一块,开始还挺顺利的,使用老办法给用户制定法一个模板,制定好表头,让用户依次添加数据,我们需要做的事就是将此Excel转换成DataTable,这样就很容易写到数据库中了。到后边可就没这么顺利了,表头固定不了,需要动态下载表头,本想只要将表头下载下来,然后再照原方法去做就好了,后边才知道往数据库中录数据的时候也不像之前那样了,将excel中的每一行数据都作为一条数据保存到数据库中,现在需要将每一条数据拆开成多条数据保存到数据库中年,且拆开的条数也不固定。开始想着挺难得,觉得做判断的时候也不好做,错误列表也不好固定,保存数据的时候又这么多条件......,不想动手, 想不出来该怎么下手,到最后实在拖不下去的时候才塌下心来仔细的想了想逻辑,结果没费多长时间也研究出来了,做出来才发现其实也只是用了个嵌套循环就出来了,比以往费事的地方也就多了个获取excel表头内容,根据它去查询,判断等。
下面我把整个过程写在这,分享一下我成功的喜悦。
这是下载下来的excel模板:
根据这个模板,我需要将一行 拆成5条数据保存到数据库中,也就是说"干部名称,干部职位,单位名称,单位类型,较差"为一条记录,"干部名称,干部职位,单位名称,单位类型,良好"为一条记录,以此类推。
js代码:
<form id="form1" runat="server" style="width: 100%">
<div class="container">
<asp:TextBox ID="TxtYear" name="HidYear" runat="server" hidden="1"></asp:TextBox>
<asp:TextBox ID="TxtTarget" name="HidTarget" runat="server" hidden="1"></asp:TextBox><%--指标名称--%>
<asp:TextBox ID="TxtTargetId" name="HidTargetId" runat="server" hidden="1"></asp:TextBox>
<asp:TextBox ID="TxtUnitName" name="HidUnitName" runat="server" hidden="1"></asp:TextBox><%--所属单位名称--%>
<asp:TextBox ID="TxtUnitNameId" name="HidUnitNameId" runat="server" hidden="1"></asp:TextBox>
<asp:TextBox ID="TxtUnitType" name="HidUnitType" runat="server" hidden="1"></asp:TextBox><%--所属单位类别--%>
<asp:TextBox ID="TxtUnitTypeId" name="HidUnitTypeId" runat="server" hidden="1"></asp:TextBox>
<div class="cnt form">
<fieldset style="width: 1020px; height: 45px;">
<table class="data-form" cellspacing="0" cellpadding="0">
<tr>
<td>
<span class="tip">提示:导入数据前请选择指标下载相应的EXCEL模板</span>
<a id="downloadExcel" class="btn-lit"><span>点下载EXCEL模板</span></a>
<br />
<br />
</td>
</tr>
<tr>
<td>
<asp:FileUpload ID="fupImport" runat="server" /><%--上传文件地址--%>
<asp:LinkButton ID="btnImport" CssClass="btn-lit" runat="server" OnClick="btnImport_Click"><span>批量导入</span></asp:LinkButton>
</td>
</tr>
</table>
</fieldset>
</div>
</div>
</form>
后台代码:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using EvaluationSystem.BLL;
using System.Data;
using System.Text;
using EvaluationSystem.Model;
namespace EvaluationSystem.Web.Assessment.InputComputer
{
public partial class CadresQuality : System.Web.UI.Page
{
//年份
string strYear = "";
//考核指标
string strTargetName = "";
//考核指标id
string strTargetId = "";
//所属单位名称
string strUnitName = "";
//所属单位id
string strUnitNameId = "";
//处级干部名称
string strCadres = "";
//处级干部id
string strCadresId = "";
//单位类型名称
string strUnitType = "";
//单位类型id
string strUnitTypeId = "";
//处级干部职位
string strPosition = "";
//票数
string strVotes = "";
//级别id
string strLevelId = "";
//列名
string strheadfield = "";
//级别
string strLevel = "";
//错误列
string strVoteField = "";
/// <summary>
/// 向错误列表中添加当前行
/// </summary>
/// <param name="dtErrorRow">代表错误列表的datatable</param>
private void AddErrorRow(DataTable dtErrorRow, string reason)
{
//新建数据行
DataRow drAddErrorRow = dtErrorRow.NewRow();
//填充数据行
drAddErrorRow["干部名称"] = "\'" + strCadres;
drAddErrorRow["干部职位"] = "\'" + strVoteField;
drAddErrorRow["单位名称"] = "\'" + strUnitName;
drAddErrorRow["票数"] = "\'" + strVoteField;
drAddErrorRow["错误原因"] = reason;
dtErrorRow.Rows.Add(drAddErrorRow);
}
protected void btnImport_Click(object sender, EventArgs e)
{
try
{
//考核时间
strYear = TxtYear.Text;
//考核指标名称
strTargetName = TxtTarget.Text;
//考核指标id
strTargetId = TxtTargetId.Text;
//处级干部单位定性指标类
CharacterizationTargetBLL targetBll = new CharacterizationTargetBLL();
//处级干部定性指标级别类
CharacterizationLevelBLL levelBll = new CharacterizationLevelBLL();
//处级干部单位基础信息类
CadresBaseInfoBLL cadresBasicBll = new CadresBaseInfoBLL();
//处级干部定性指标信息录入类
CadresCharacterizationVotesBLL votesBll = new CadresCharacterizationVotesBLL();
//BLL层把Excel转化为datatable类
CreateExcelDataBLL createExcelData = new CreateExcelDataBLL();
//将datatable类转换成excel
DataTableToExcel dataTableToExcel = new DataTableToExcel();
//获取上传文件地址
string url = fupImport.PostedFile.FileName.ToString();
if (url == "")
{
//数据源为空,弹出提示:请选择Excel文件!
Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript' defer>alert('请选择Excel文件!');</script>");
return;
}
//如果文件扩展名不是xls则提示文件格式不正确
if (url.Substring(url.LastIndexOf("."), url.Length - url.LastIndexOf(".")) != ".xls")
{
Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript' defer>alert('文件类型不正确,请选择扩展名为.xls的文件!');</script>");
return;
}
string urlLocation = url.Substring(url.LastIndexOf("\\") + 1);//获取文件名
DataTable dtCadresData;
//在系统中建文件夹up,并将excel文件另存
this.fupImport.SaveAs(Server.MapPath("~\\UploadFile") + "\\" + urlLocation);//记录文件名到服务器相对应的文件夹中
//获得文件路径
string strpath = Server.MapPath("~\\UploadFile") + "\\" + urlLocation;
//把上传的Excel转换为datatable
dtCadresData = createExcelData.CreateExcelDataSource(strpath);
/*-------------------------------------判断数据源是否合法--------------------------------------*/
//判断时间是否为服务器的时间
string strServerTime = votesBll.GetYear();
if (strYear != strServerTime)
{
Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript' defer>alert('时间已过期,不能录入数据!');</script>");
//退出方法
return;
}
//判断dtCadresData中是否包含全部要求的字段
for (int i = 0; i < dtCadresData.Columns.Count; i++)
{
//只要有一个字段不被包含,则提示"数据源缺少必要的字段",并退出循环和整个方法
if (!dtCadresData.Columns.Contains(dtCadresData.Columns[i].ToString().Trim()))
{
Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript' defer>alert('数据源缺少必要的字段,请检查Excel数据源!');</script>");
//退出方法
return;
}
}
//判断数据源中是否有数据
if (dtCadresData.Rows.Count == 0)
{
Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript' defer>alert('Excel文件中没有任何数据,请填充数据!');</script>");
//退出方法
return;
}
//判断是否有相同的行
DataView dvCadresData = new DataView(dtCadresData);
if (dvCadresData.Count != dvCadresData.ToTable(true, "单位名称", "干部名称", "干部职位","单位类别").Rows.Count)
{
Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript' defer>alert('" + "第" + (dvCadresData.ToTable(true, "单位名称", "干部名称", "干部职位","单位类别").Rows.Count + 2) + "条数据已存在,请删除此条数据!" + "');</script>");
//退出方法
return;
}
DataSet dsCadresData = new DataSet("ds_CadresData"); //创建一个名为ds_CadresData的DataSet
//手动创建的新数据表-处级干部定性信息录入表
DataTable dtAddCadresData = new DataTable("dt_AddCadresData"); //创建一个名为dt_AddCadresData的DataTalbe
//为dt_AddCadresData表内建立Column(表头),添加数据列:
dtAddCadresData.Columns.Add(new DataColumn("Targetid", typeof(string)));//指标id
dtAddCadresData.Columns.Add(new DataColumn("CadresID", typeof(string)));//考核对象id
dtAddCadresData.Columns.Add(new DataColumn("Name", typeof(string)));//所属单位名称
dtAddCadresData.Columns.Add(new DataColumn("CharacterizationLevelId", typeof(string)));//级别
dtAddCadresData.Columns.Add(new DataColumn("other2", typeof(string)));//级别id
dtAddCadresData.Columns.Add(new DataColumn("other3", typeof(string)));//所属单位类别id
dtAddCadresData.Columns.Add(new DataColumn("YearTime", typeof(string)));
dtAddCadresData.Columns.Add(new DataColumn("Votes", 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("票数", typeof(string)));
dtErrorRow.Columns.Add(new DataColumn("错误原因", typeof(string)));
//遍历整个dt ,将为空的单元格写入默认数据“0”;
for (int i = 0; i < dtCadresData.Rows.Count; i++)
{
for (int j = 4; j < dtCadresData.Columns.Count; j++)
{
if (dtCadresData.Rows[i][j].ToString().Trim() == "" || dtCadresData.Rows[i][j].ToString().Trim() == null)
{
dtCadresData.Rows[i][j] = "0";
}
}
}
//从上传的Excel转换为的datatable表中取出数据,放入处级干部定性指标信息录入表中
for (int intRow = 0; intRow < dtCadresData.Rows.Count; intRow++)
{
//处级干部名称
strCadres = dtCadresData.Rows[intRow][0].ToString();
//干部职位
strPosition = dtCadresData.Rows[intRow][1].ToString();
//单位名称
strUnitName = dtCadresData.Rows[intRow][2].ToString();
//单位类别
strUnitType = dtCadresData.Rows[intRow][3].ToString();
//根据单位类别查找类别id
//根据处级干部名称和职位还有所属单位和checked=yes去T_CadresBaseInfo表中查找处级干部单位id
StringBuilder strWhere = new StringBuilder();
strWhere.Append("other1='单位类别' and Name='" + strUnitType + "'");
DataSet ds = cadresBasicBll.GetUnitTypeId(strWhere.ToString());
//级别id
strUnitTypeId = ds.Tables[0].Rows[0][0].ToString().Trim();
//根据处级干部名称和职位还有所属单位和checked=yes去T_CadresBaseInfo表中查找处级干部单位id
StringBuilder strWhere1 = new StringBuilder();
strWhere1.Append("CadresName='" + strCadres + "'and Checked='Yes'and Position='" + strPosition + "'and Unit='" + strUnitName + "'");
//处级干部单位id
strCadresId = cadresBasicBll.GetIdByName(strWhere1.ToString());
//判断处级干部单位id知否存在,如果不存在说明此处级干部单位今年不是考核对象
if (strCadresId == "" || strCadresId == null)
{
// 向错误列表中添加当前行
AddErrorRow(dtErrorRow, "此处级干部今年未被列入考核对象,请核对信息!");
// 跳出当前循环
continue;
}
for (int intColumns = 4; intColumns < dtCadresData.Columns.Count; intColumns++)
{
//票数
strVotes = dtCadresData.Rows[intRow][intColumns].ToString();
//级别
strLevel = dtCadresData.Columns[intColumns].ColumnName;
//根基时间,级别,指标id去级别表中查找级别id
StringBuilder strWhere2 = new StringBuilder();
strWhere2.Append("TargetId='" + strTargetId + "'and Timestamp='" + strYear + "'and LevelId='" + strLevel + "'");
DataSet ds2 = levelBll.GetLevel(strWhere2.ToString());
//级别id
strLevelId = ds2.Tables[0].Rows[0][0].ToString().Trim();
//处级干部单位定性指标录入实体
CadresCharacterizationVotesEntity votesEntity = new CadresCharacterizationVotesEntity();
//将判断条件传到实体中
votesEntity.YearTime = strYear;//考核时间
votesEntity.Targetid = strTargetId;//指标id
votesEntity.CharacterizationLevelId = strLevel;//级别
votesEntity.other2 = strLevelId;//级别id
votesEntity.CadresID= strCadresId;//处级干部id
votesEntity.Name = strUnitName;//所属单位名称
votesEntity.other3 = strUnitTypeId;//所属单位类别id
votesEntity.Votes = strVotes;//票数
//如果指定的记录已存在则将当前行添加到错误列表,并跳出当前循环
if (votesBll.ExistProperty(votesEntity) == true)
{
// 向错误列表中添加当前行
strVoteField = strLevel + ":" + strVotes;//票数
AddErrorRow(dtErrorRow, "指定的数据已存在!");
// 跳出当前循环
continue;
}
//添加处级干部原始数据表的新行
DataRow drAddCadresData = dtAddCadresData.NewRow();//注意这边创建dt的新行的方法。指定类型是DataRow而不是TableRow,然后不用new直接的用创建的DataTable下面的NewRow方法。
//虚拟学生对应的表
drAddCadresData["Targetid"] = strTargetId;
drAddCadresData["CadresID"] = strCadresId;
drAddCadresData["YearTime"] = strYear;
drAddCadresData["CharacterizationLevelId"] = strLevel;
drAddCadresData["other2"] = strLevelId;
drAddCadresData["other3"] = strUnitTypeId;
drAddCadresData["Name"] = strUnitName;
drAddCadresData["Votes"] = strVotes;
dtAddCadresData.Rows.Add(drAddCadresData); //将一整条数据写入表中
}
}
//将表加入DataSet中
dsCadresData.Tables.Add(dtAddCadresData);
string tablename;
tablename = "T_CadresCharacterizationVotes";
Boolean flagImportRecord = votesBll.ImportRecord(dsCadresData, tablename);
if (true == flagImportRecord && dtErrorRow.Rows.Count == 0)
{
//导入成功,弹出提示
Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript' defer>alert('导入成功!');</script>");
}
else if (flagImportRecord == false)
{
//导入失败,弹出提示
Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript' defer>alert('导入失败');</script>");
}
else if (true == flagImportRecord && dtErrorRow.Rows.Count != 0)
{
//只有部分数据导入成功,弹出提示
Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript' defer>alert('只有部分导入成功,有错误的部分已导出为EXCEL,请查看新导出的EXCEL;新导出的Excel修改错误后可作为数据源重新导入');</script>");
//将错误数据导出到Excel并打开显示
dataTableToExcel.ToExcel(dtErrorRow);
}
}
catch (Exception ex)
{
MessageBox.Show(this.Page, ex.Message);
}
}
}
}
B层:这里只介绍excel转换成datatable那一部分代码,其它查询的的方法就不一一介绍了
/// <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 dt;
}
catch (Exception ex)
{
throw ex;
}
finally
{
oleAdapter.Dispose();
oleConn.Close();
oleConn.Dispose();
//删除上传的Excel文件(因为该文件的存在会占用多余的网站空间)
if (File.Exists(url))
{
File.Delete(url);
}
}
}
/// <summary>
/// excel导入开发区单位原始数据录入表
/// 2-25日陈金荣添加
/// </summary>
/// <param name="ds"></param>
/// <param name="tablename"></param>
/// <returns></returns>
public bool ImportRecord(DataSet ds, string tablename)
{
bool bloimport;
bloimport = dal.ImportRecordAll(ds);
return bloimport;
}
D层:这里只介绍Excel导入这一部分
/// <summary>
///excel导入开发区单位原始数据
///2-25陈金荣添加
/// </summary>
/// <returns></returns>
public bool ImportRecordAll(DataSet ds)
{
TestHelper testhelp = new TestHelper(); ;
bool test;
test = testhelp.InsertTable(ds.Tables[0], "T_CadresCharacterizationVotes", ds.Tables[0].Columns);
return test;
}
Help中:
/// <summary>批量导入DataTable
/// 批量导入DataTable
/// </summary>
/// <param name="dt">DataTable数据表</param>
/// <param name="tableName">表名</param>
/// <param name="dtColum">数据列集合</param>
/// <return>Boolean值:true成功,false失败</return>
public Boolean InsertTable(DataTable dt, string tableName, DataColumnCollection dtColum)
{
//打开数据库
GetConn();
try
{
//声明SqlBulkCopy ,using释放非托管资源
using (SqlBulkCopy sqlBC = new SqlBulkCopy(sqlConn))
{
//一次批量的插入的数据量
sqlBC.BatchSize = 1000;
//超时之前操作完成所允许的秒数,如果超时则事务不会提交 ,数据将回滚,所有已复制的行都会从目标表中移除
sqlBC.BulkCopyTimeout = 60;
//设置要批量写入的表
sqlBC.DestinationTableName = tableName;
//自定义的datatable和数据库的字段进行对应
for (int i = 0; i < dtColum.Count; i++)
{
sqlBC.ColumnMappings.Add(dtColum[i].ColumnName.ToString(), dtColum[i].ColumnName.ToString());
}
//批量写入
sqlBC.WriteToServer(dt);
}
return true;
}
catch
{
return false;
}
finally
{
//关闭数据库
sqlConn.Close();
}
}
整个实现过程就是这样,没有想象的那么费劲,现在唯一担心的事就怕数据量大的时候时间长的出乎意料,这还须进行测试。
通过这一阶段的学习,我想说,认认真真的想,也就那样;踏踏实实的做,会成功的!加油!