xls到xml

  protected void btn_ok_Click(object sender, EventArgs e)
    {
        string x = txtpath.Text;
        DataSet ds = GetConnect(x);         DataTable xDataTable = ds.Tables[0];         string xFile = Server.MapPath("~/") + "action.xml";         if (File.Exists(xFile))             File.Delete(xFile);         //建立Xml的定义声明           XmlDocument xmlDoc = new XmlDocument();         try         {             XmlDeclaration dec = xmlDoc.CreateXmlDeclaration("1.0", "GB2312", null);             xmlDoc.AppendChild(dec);   //创建根节点                XmlElement root = xmlDoc.CreateElement("data");             xmlDoc.AppendChild(root);             for (int i = 0; i < xDataTable.Rows.Count; i++)             {                 var str=xDataTable.Rows[i][1].ToString();                 if(str=="")                 {                     break;                 }                 if (i < 5)                 {                     var sdate = "2015年" + (xDataTable.Rows[i][3] + "").Substring(0, 5);                     var edate = "2015年8月" + ((xDataTable.Rows[i][3] + "").Substring(6, 2));                     //TimeSpan days=Convert.ToDateTime(edate) - Convert.ToDateTime(sdate);                     //var n = int.Parse(days.ToString());                     for (int r = 0; r <= 6; r++)                     {                         XmlNode item = xmlDoc.CreateElement("item");                         XmlElement title = xmlDoc.CreateElement("Title");                         title.InnerText = xDataTable.Rows[i][1] + "";                         item.AppendChild(title);                         XmlAttribute xmldate = xmlDoc.CreateAttribute("date");                         xmldate.Value = "8月"+(Convert.ToDateTime(sdate).AddDays(r).ToString()).Substring(7,3)+"日";                         title.Attributes.Append(xmldate);                         XmlAttribute xmlchangguan = xmlDoc.CreateAttribute("changguan");                         xmlchangguan.Value = xDataTable.Rows[i][4] + "";                         title.Attributes.Append(xmlchangguan);                         XmlAttribute xmlisImportion = xmlDoc.CreateAttribute("isImportion");                         xmlisImportion.Value = "1";                         title.Attributes.Append(xmlisImportion);                         XmlElement jiaBin = xmlDoc.CreateElement("jiaBin");                         jiaBin.InnerText = xDataTable.Rows[i][2] + "";                         item.AppendChild(jiaBin);                         XmlElement time = xmlDoc.CreateElement("time");                         time.InnerText = "9:00-21:00";                         item.AppendChild(time);                         XmlElement address = xmlDoc.CreateElement("address");                         address.InnerText = xDataTable.Rows[i][4] + "";                         item.AppendChild(address);                         XmlElement zhuBanDanWei = xmlDoc.CreateElement("zhuBanDanWei");                         zhuBanDanWei.InnerText = xDataTable.Rows[i][5] + "";                         item.AppendChild(zhuBanDanWei);                         root.AppendChild(item);                     }                 }                 else                 {                     XmlNode item = xmlDoc.CreateElement("item");                     XmlElement title = xmlDoc.CreateElement("Title");                     title.InnerText = xDataTable.Rows[i][1] + "";                     item.AppendChild(title);                     XmlAttribute xmldate = xmlDoc.CreateAttribute("date");                     xmldate.Value = (xDataTable.Rows[i][3] + "").Substring(0,5);                     title.Attributes.Append(xmldate);                     XmlAttribute xmlchangguan = xmlDoc.CreateAttribute("changguan");                     xmlchangguan.Value = xDataTable.Rows[i][4] + "";                     title.Attributes.Append(xmlchangguan);                     XmlAttribute xmlisImportion = xmlDoc.CreateAttribute("isImportion");                     xmlisImportion.Value = "1";                     title.Attributes.Append(xmlisImportion);                     XmlElement jiaBin = xmlDoc.CreateElement("jiaBin");                     jiaBin.InnerText = xDataTable.Rows[i][2] + "";                     item.AppendChild(jiaBin);                     XmlElement time = xmlDoc.CreateElement("time");                     int index = (xDataTable.Rows[i][3] + "").IndexOf("日");                     time.InnerText = (xDataTable.Rows[i][3] + "").Substring(index + 1);                     item.AppendChild(time);                     XmlElement address = xmlDoc.CreateElement("address");                     address.InnerText = xDataTable.Rows[i][4] + "";                     item.AppendChild(address);                     XmlElement zhuBanDanWei = xmlDoc.CreateElement("zhuBanDanWei");                     zhuBanDanWei.InnerText = xDataTable.Rows[i][5] + "";                     item.AppendChild(zhuBanDanWei);                     root.AppendChild(item);                 }             }             xmlDoc.Save(xFile);             lblshow.InnerText = "成功";         }         catch (Exception ex)         {             lblshow.InnerText = "失败" + ex.Message + ex.TargetSite;         }     }     /// <summary>     /// 导出EXCEL表中的数据到 myDataSet     /// </summary>     public static DataSet GetConnect(string FilePath)     {         DataSet myDataSet = new DataSet();         string strCon = "Provider=Microsoft.Jet.Oledb.4.0; Data Source=" + FilePath + "; Extended Properties=\"Excel 8.0; HDR=YES; IMEX=1;\"";         if (Path.GetExtension(FilePath).ToLower() == ".xlsx")             strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FilePath + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1'";         OleDbConnection myConn = new OleDbConnection(strCon);         //条件查询EXCEL表         string strCom = " SELECT * FROM [Sheet1$] ";         myConn.Open();         //打开数据链接，得到一个数据集         OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, myConn);         //创建一个 DataSet对象         //得到自己的DataSet对象         myCommand.Fill(myDataSet, "[Sheet1$]");         //关闭此数据链接         myConn.Close();         return myDataSet;     }

转载于:https://www.cnblogs.com/ft-Pavilion/p/4737590.html