以下是导入的文件样板:
因为是xml格式的,所以用记事本打开这个文件,可以看到是这种格式:
<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:html="http://www.w3.org/TR/REC-html40">
<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
<Author>zifar-computer</Author>
<LastAuthor>zifar-computer</LastAuthor>
<Created>2013-07-08T08:23:29Z</Created>
<Company>zifar.com</Company>
<Version>11.5606</Version>
</DocumentProperties>
<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
<WindowHeight>13680</WindowHeight>
<WindowWidth>24795</WindowWidth>
<WindowTopX>480</WindowTopX>
<WindowTopY>60</WindowTopY>
<ProtectStructure>False</ProtectStructure>
<ProtectWindows>False</ProtectWindows>
</ExcelWorkbook>
<Styles>
<Style ss:ID="Default" ss:Name="Normal">
<Alignment ss:Vertical="Center"/>
<Borders/>
<Font ss:FontName="宋体" x:CharSet="134" ss:Size="12"/>
<Interior/>
<NumberFormat/>
<Protection/>
</Style>
<Style ss:ID="s23" ss:Name="超链接">
<Font ss:FontName="宋体" x:CharSet="134" ss:Size="12" ss:Color="#0000FF"
ss:Underline="Single"/>
</Style>
<Style ss:ID="s21">
<NumberFormat ss:Format="@"/>
</Style>
<Style ss:ID="s22">
<NumberFormat ss:Format="Short Date"/>
</Style>
</Styles>
<Worksheet ss:Name="Sheet1">
<Table ss:ExpandedColumnCount="23" ss:ExpandedRowCount="1200" x:FullColumns="1"
x:FullRows="1" ss:DefaultColumnWidth="54" ss:DefaultRowHeight="14.25">
<Column ss:Width="57" ss:Span="4"/>
<Column ss:Index="6" ss:Width="83.25"/>
<Column ss:Width="33"/>
<Column ss:Width="57"/>
<Column ss:Width="33"/>
<Column ss:Width="57"/>
<Column ss:Width="33"/>
<Column ss:Width="57" ss:Span="3"/>
<Column ss:Index="16" ss:Width="83.25"/>
<Column ss:Width="33"/>
<Column ss:Width="57" ss:Span="3"/>
<Column ss:Index="22" ss:Width="33" ss:Span="1"/>
<Row ss:AutoFitHeight="0">
<Cell><Data ss:Type="String">用户姓名</Data></Cell>
<Cell><Data ss:Type="String">人员工号</Data></Cell>
<Cell><Data ss:Type="String">身证证号</Data></Cell>
<Cell><Data ss:Type="String">手机号码</Data></Cell>
<Cell><Data ss:Type="String">是否启用</Data></Cell>
<Cell><Data ss:Type="String">所属部门</Data></Cell>
<Cell><Data ss:Type="String">职务</Data></Cell>
<Cell><Data ss:Type="String">出生日期</Data></Cell>
<Cell><Data ss:Type="String">年龄</Data></Cell>
<Cell><Data ss:Type="String">办公电话</Data></Cell>
<Cell><Data ss:Type="String">职称</Data></Cell>
<Cell><Data ss:Type="String">文化程度</Data></Cell>
<Cell><Data ss:Type="String">婚姻状况</Data></Cell>
<Cell><Data ss:Type="String">政治面貌</Data></Cell>
<Cell><Data ss:Type="String">电子邮箱</Data></Cell>
<Cell><Data ss:Type="String">参加工作时间</Data></Cell>
<Cell><Data ss:Type="String">工龄</Data></Cell>
<Cell><Data ss:Type="String">毕业学校</Data></Cell>
<Cell><Data ss:Type="String">所修专业</Data></Cell>
<Cell><Data ss:Type="String">毕业时间</Data></Cell>
<Cell><Data ss:Type="String">家庭住址</Data></Cell>
<Cell><Data ss:Type="String">民族</Data></Cell>
<Cell><Data ss:Type="String">性别</Data></Cell>
</Row>
<Row ss:AutoFitHeight="0">
<Cell><Data ss:Type="String">梅锦龙</Data></Cell>
<Cell ss:StyleID="s21"><Data ss:Type="Number">120096</Data></Cell>
<Cell><Data ss:Type="String">身份证号</Data></Cell>
<Cell><Data ss:Type="String">手机号码</Data></Cell>
<Cell><Data ss:Type="String">是</Data></Cell>
<Cell><Data ss:Type="String">厂部</Data></Cell>
<Cell><Data ss:Type="String">技术员</Data></Cell>
<Cell ss:StyleID="s22"><Data ss:Type="DateTime">1980-09-09T00:00:00.000</Data></Cell>
<Cell><Data ss:Type="Number">30</Data></Cell>
<Cell><Data ss:Type="String">办公电话</Data></Cell>
<Cell><Data ss:Type="String">工程师</Data></Cell>
<Cell><Data ss:Type="String">大专</Data></Cell>
<Cell><Data ss:Type="String">已婚 </Data></Cell>
<Cell><Data ss:Type="String">团员</Data></Cell>
<Cell ss:StyleID="s23" ss:HRef="mailto:abc@163.com"><Data ss:Type="String">abc@163.com</Data></Cell>
<Cell ss:StyleID="s22"><Data ss:Type="DateTime">2010-08-08T00:00:00.000</Data></Cell>
<Cell><Data ss:Type="Number">3</Data></Cell>
<Cell><Data ss:Type="String">毕业学校</Data></Cell>
<Cell><Data ss:Type="String">所修专业</Data></Cell>
<Cell><Data ss:Type="String">毕业时间</Data></Cell>
<Cell><Data ss:Type="String">家庭住址</Data></Cell>
<Cell><Data ss:Type="String">汉</Data></Cell>
<Cell><Data ss:Type="String">男</Data></Cell>
</Row>
<Row ss:AutoFitHeight="0">
<Cell><Data ss:Type="String">何**</Data></Cell>
<Cell ss:StyleID="s21"><Data ss:Type="Number">120110</Data></Cell>
<Cell ss:Index="6"><Data ss:Type="String">厂部</Data></Cell>
<Cell ss:Index="23"><Data ss:Type="String">男</Data></Cell>
</Row>
<Row ss:AutoFitHeight="0">
<Cell><Data ss:Type="String">高**</Data></Cell>
<Cell ss:StyleID="s21"><Data ss:Type="Number">120603</Data></Cell>
<Cell ss:Index="6"><Data ss:Type="String">厂部</Data></Cell>
<Cell ss:Index="23"><Data ss:Type="String">男</Data></Cell>
</Row>
<Row ss:AutoFitHeight="0">
<Cell><Data ss:Type="String">曾**</Data></Cell>
<Cell ss:StyleID="s21"><Data ss:Type="Number">120153</Data></Cell>
<Cell ss:Index="6"><Data ss:Type="String">厂部</Data></Cell>
<Cell ss:Index="23"><Data ss:Type="String">男</Data></Cell>
</Row>
</Table>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<Unsynced/>
<Selected/>
<Panes>
<Pane>
<Number>3</Number>
<ActiveRow>3</ActiveRow>
<ActiveCol>11</ActiveCol>
</Pane>
</Panes>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
<Worksheet ss:Name="Sheet2">
<Table ss:ExpandedColumnCount="0" ss:ExpandedRowCount="0" x:FullColumns="1"
x:FullRows="1" ss:DefaultColumnWidth="54" ss:DefaultRowHeight="14.25"/>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<Unsynced/>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
<Worksheet ss:Name="Sheet3">
<Table ss:ExpandedColumnCount="0" ss:ExpandedRowCount="0" x:FullColumns="1"
x:FullRows="1" ss:DefaultColumnWidth="54" ss:DefaultRowHeight="14.25"/>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<Unsynced/>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
</Workbook>
导入没什么难度,主要是因为这个xml有N个命名空间,再有就是因为有很多空的单元格,空单元格在这个xml中是看不出来的,跳格是通过cell标记上的ss:Index指向的。下面是解析代码:
/// <summary>
/// 从excel中导入数据到oracel
/// </summary>
/// <param name="filename">要解析的excel文件</param>
/// <param name="errmsg"></param>
/// <param name="nofullsucceed">不完全成功提示:如果只是成功了一部分,状态还是返回为true,但是要提示用户</param>
/// <returns></returns>
public bool InportFromExcelToDb(string filename,ref string errmsg,ref string nofullsucceed)
{
bool r = false;
if (!File.Exists(filename))
{
errmsg = "文件"+filename+"不存在或者没有权限读取";
return false;
}
//附加xml命名空间
Dictionary<string, string> dic = new Dictionary<string, string>();
dic.Add("d", "urn:schemas-microsoft-com:office:spreadsheet");
dic.Add("o", "urn:schemas-microsoft-com:office:office");
dic.Add("x", "urn:schemas-microsoft-com:office:excel");
dic.Add("ss", "urn:schemas-microsoft-com:office:spreadsheet");
dic.Add("html", "http://www.w3.org/TR/REC-html40");
XmlNodeList xnl = ConfigHelper.GetXmlNodeList(filename, "descendant::d:Row", dic);
int max = xnl.Count;//取要导入记录的数目,在最后用的时候,记得要减掉标题 那行
int succedcount = 0;//没写入成功的记录数
if (xnl == null || xnl.Count<2)
{
//等于1表示只有一行标题
errmsg = "没有可导的数据";
return false;
}
//数据从第二行开始
List<string[]> slist = new List<string[]>();//用于保存用户信息
//从1开始,因为第一行是标题
for (int count=1;count<xnl.Count;count++)
{
string[] arr = new string[23];//创建一个长度为23的数组
int index = 0;//表示当前实际在第几个节点
XmlNodeList snl = xnl[count].ChildNodes;//取当前节点的所有子节点,结构不错的话,应该是cell节点
if (snl == null || snl.Count == 0)
continue;
#region 循环取出数据
for (int j = 0; j < snl.Count; j++)
{
XmlNode xn = snl[j];
string at=string.Empty;
bool ishas=IsHasAttr(xn,"ss:Index",ref at);
if (ishas && ValidHelper.IsIntData(at))
{
index = Convert.ToInt32(at) - 1;
}
//总共只有23个列,超过了则进入下一轮
if (index < 23)
{
arr[index] = xn.InnerText.Trim();
}
index++;
if (index >= 23)
{
index = 0;
}
}
#endregion
slist.Add(arr);
}
#region 开始往数据库中写入
bool isSuc = false;
CUM_UserInfoBM bm = null;
CUM_UserInfoService bll = new CUM_UserInfoService();
for (int i = 0; i < slist.Count; i++)
{
string[] sarr = slist[i];
if (TrimOrAddSpace(sarr[0], false) == "" || TrimOrAddSpace(sarr[1], false) == "")
{
continue;//如果员工姓名或者人员工号为空,则下一轮
}
//用户姓名,人员工号,身证证号,手机号码,是否启用,所属部门,职务,出生日期,年龄,办公电话,职称
//文化程度,婚姻状况,政治面貌,电子邮箱,参加工作时间,工龄,毕业学校,所修专业,毕业时间,家庭住址,民族,性别
bm = new CUM_UserInfoBM();
bm.User_Name = TrimOrAddSpace(sarr[0]);
bm.User_No = TrimOrAddSpace(sarr[1]);
bm.User_IDCard = TrimOrAddSpace(sarr[2]);//为避免空值插入不了数据库,需要对值进行处理
bm.User_Mobile = TrimOrAddSpace(sarr[3]);
short valid = 0;
if (TrimOrAddSpace(sarr[4]) == "是")
{
valid = 1;
}
bm.User_ISvalid = valid;
bm.User_Dept = GetOrgIdByName(sarr[5]);
bm.User_Job = TrimOrAddSpace(sarr[6]);
//excel自动加了T和时分秒
if (!string.IsNullOrEmpty(sarr[7]) && sarr[7].Contains("T"))
{
sarr[7] = sarr[7].Split('T')[0];
}
if (ValidHelper.IsDateTime(sarr[7]))
{
bm.User_Born = Convert.ToDateTime(sarr[7]);
}
if (ValidHelper.IsIntData(sarr[8]))
{
bm.User_Age = Convert.ToInt32(sarr[8]);
}
bm.User_DeptTel = TrimOrAddSpace(sarr[9]);
bm.User_Title = TrimOrAddSpace(sarr[10]);
bm.User_Diploma = TrimOrAddSpace(sarr[11]);
bm.User_Mar = TrimOrAddSpace(sarr[12]);
bm.User_Polity = TrimOrAddSpace(sarr[13]);
bm.User_Email = TrimOrAddSpace(sarr[14]);
if (!string.IsNullOrEmpty(sarr[15]) && sarr[15].Contains("T"))
{
sarr[15] = sarr[15].Split('T')[0];
}
if (ValidHelper.IsDateTime(sarr[15]))
{
bm.User_WorkDate = Convert.ToDateTime(sarr[15]);
}
if (ValidHelper.IsIntData(sarr[16]))
{
bm.User_WorkYear = Convert.ToInt32(sarr[16]);
}
bm.User_School = TrimOrAddSpace(sarr[17]);
bm.User_Profession = TrimOrAddSpace(sarr[18]);
if (!string.IsNullOrEmpty(sarr[19]) && sarr[19].Contains("T"))
{
sarr[19] = sarr[19].Split('T')[0];
}
if (ValidHelper.IsDateTime(sarr[19]))
{
bm.User_FishDate = Convert.ToDateTime(sarr[19]);
}
bm.User_FamAddress = TrimOrAddSpace(sarr[20]);
bm.User_Nation = TrimOrAddSpace(sarr[21]);
bm.User_Sex = TrimOrAddSpace(sarr[22],true);
//bm.User_Sex = "男";
bm.User_Pwd = SecurityHelper.getUserEncryptPass("123456");
try
{
if (bll.IsHasUserNo(0, bm.User_No))
{
continue;
}
long userid = bll.InsertUserInfo(bm);
isSuc = true;
}
catch (Exception ex)
{
succedcount++;
IOHelper.WriteLog("插入用户" + sarr[0] + "失败," + ex.Message + ex.StackTrace);
continue;
}
//插入50条后休息30毫秒
if (i > 0 && i % 50 == 0)
{
Thread.Sleep(30);
}
}
//只要成功插入过一次,就算是成功
if (isSuc)
{
r = true;
}
#endregion
if (succedcount > 0 && succedcount < max)
{
nofullsucceed = "共"+succedcount+"条记录导入失败,失败原因请查看根目录下的logs文件夹下的日志";
}
return r;
}
/// <summary>
/// 对原值进行trim,如果为空,则赋值空格,如果不为空,则trim掉多余空格
/// </summary>
/// <param name="s"></param>
/// <param name="isSpace">是否要加一个空格,默认加上</param>
/// <returns></returns>
private string TrimOrAddSpace(string s,bool isSpace=true)
{
string result = " ";
if (!isSpace)
{
result = "";
}
if (string.IsNullOrEmpty(s)) return result;
if (s.Trim() == "")
{
return result;
}
else
{
return s.Trim();
}
}
/// <summary>
/// 通过名称取部门ID
/// </summary>
/// <param name="name"></param>
private long GetOrgIdByName(string name)
{
if (string.IsNullOrEmpty(name)) return 0;
IList<CUM_OrgBM> list = GetOrgList();
CUM_OrgBM bm = list.FirstOrDefault(m => m.CUM_OrgName == name);
if (bm != null)
{
return bm.CUM_OrgID;
}
else
{
return 0;
}
}
/// <summary>
/// 判断xmlnode是否有某个节点,有则返回这个节点的值
/// </summary>
/// <param name="xn">要判断的某个节点</param>
/// <param name="ckattr">要判断的那个属性</param>
/// <param name="index"></param>
/// <returns></returns>
private bool IsHasAttr(XmlNode xn,string ckattr, ref string index)
{
bool ishas = false;
XmlAttributeCollection xattr = xn.Attributes;
if (xattr != null)
{
foreach (XmlAttribute attr in xattr)
{
if (attr.Name.Contains(ckattr))
{
string sy = xn.Attributes[attr.Name].Value;
ishas = true;
index = sy;
break;
}
}
}
return ishas;
}