/* 编写:围城(solq) 日期:2012-5-8 blog:http://www.cnblogs.com/solq/ 说明:读取 execl2007 注意,要安装驱动,否则不能识别2007,保存xml 时,注意编码问题。。不然会出错 表格格式: 行1:不重要的标题,, 行2:对应行1的英文名,,,程序自动对这个名来生成 xml 节点属性 行3+:。。。。xxxxxxx数据 */ 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 System.Data.OleDb; using System.Xml; namespace readexecl { public partial class Form1 : Form { public Form1() { InitializeComponent(); } private void button1_Click(object sender, EventArgs e) { OpenFileDialog openFileDialog1 = new OpenFileDialog(); openFileDialog1.InitialDirectory = "c:"; openFileDialog1.Filter = "*.xlsx|*.xlsx"; openFileDialog1.FilterIndex = 2; openFileDialog1.RestoreDirectory = true; if (openFileDialog1.ShowDialog() == DialogResult.OK) { this.textBox1.Text = openFileDialog1.FileName; Connection = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0;HDR=yes;IMEX=1\";", this.textBox1.Text); } } private string save_file = ""; private string table = ""; private void button2_Click(object sender, EventArgs e) { if (textBox2.Text == "") { MessageBox.Show("请输入 输出的表格。。。。"); return; } if (textBox1.Text == "") { MessageBox.Show("请选择XML文件。。。。"); return; } SaveFileDialog saveFileDialog1 = new SaveFileDialog(); //设置文件类型 saveFileDialog1.Filter = "All files(*.*)|*.*|xml(*.xml)|*.xml"; //设置默认文件类型显示顺序 saveFileDialog1.FilterIndex = 2; //保存对话框是否记忆上次打开的目录 saveFileDialog1.RestoreDirectory = true; if (saveFileDialog1.ShowDialog() == DialogResult.OK) { save_file = saveFileDialog1.FileName; readExecl(); } } /////////////////////execl//////////////////////// string Connection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=d:/test.xlsx;Extended Properties=\"Excel 12.0;HDR=no;IMEX=1\";"; string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:/test.xls;" + "Extended Properties='Excel 8.0'"; private void readExecl() { OleDbConnection con = new OleDbConnection(Connection); #region /* 获取多少个表。。。。。 */ con.Open(); DataTable sheetsName = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" }); //Console.WriteLine(sheetsName.Rows.Count); bool check = false; for (int t = 0; t < sheetsName.Rows.Count; t++) { if (sheetsName.Rows[t][2].ToString().Equals(this.textBox2.Text + "$")) { check = true; break; } Console.WriteLine(sheetsName.Rows[t][2].ToString()); // get table name } if (!check) { MessageBox.Show("没有表格名!!! " + this.textBox2.Text); return; } #endregion /* //读取方式一 OleDbDataAdapter myCommand = new OleDbDataAdapter("select * from [Sheet1$]", con); DataTable dt = new DataTable(); myCommand.Fill(dt); DataSet set = new DataSet(); myCommand.Fill(set); foreach (DataRow r in dt.Rows) { String str = r[0].ToString(); ////Console.WriteLine(str); }*/ /* 如果只想读取前两列可以用:select * from [Sheet1$A:B] 如果只想读取A1到B2的内容,就用:select * from [Sheet1$A1:B2] */ //读取方式二 string sql = string.Format("SELECT * FROM [{0}$] ", textBox2.Text); OleDbCommand myOleDbCommand = new OleDbCommand(sql, con); OleDbDataReader myDataReader = myOleDbCommand.ExecuteReader(); List<string> fields = new List<string>(); if (myDataReader.Read()) { //读取英文头表,字段 for (int i = 0; i < myDataReader.FieldCount; i++) { string c = Convert.ToString(myDataReader.GetValue(i)).Trim(); //Console.Write(c + "\t"); fields.Add(c); } } ////////////////////////////////////xml//////////////////////////////////////////// XmlDocument doc = new XmlDocument(); XmlDeclaration dec = doc.CreateXmlDeclaration("1.0", "UTF-8", null); doc.AppendChild(dec); //创建一个根节点(一级) XmlElement root = doc.CreateElement("datas"); doc.AppendChild(root); //读取主体 while (myDataReader.Read()) { XmlElement element = doc.CreateElement("data"); for (int i = 0; i < myDataReader.FieldCount; i++) { string value = Convert.ToString(myDataReader.GetValue(i)).Trim(); Console.Write(value + "\t"); element.SetAttribute(fields[i].ToString(), value); } root.AppendChild(element); //Console.WriteLine(""); } try { doc.Save(save_file); } catch (Exception e) { MessageBox.Show(e.Message); } myDataReader.Dispose(); con.Close(); } private void readExecl2() { OleDbConnection con = new OleDbConnection(Connection); con.Open(); DataTable sheetsName = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" }); for (int t = 0; t < sheetsName.Rows.Count; t++) { string sql = string.Format("SELECT * FROM [{0}] ", sheetsName.Rows[t][2].ToString()); OleDbCommand myOleDbCommand = new OleDbCommand(sql, con); OleDbDataReader myDataReader = myOleDbCommand.ExecuteReader(); List<string> fields = new List<string>(); if (myDataReader.Read()) { //读取英文头表,字段 for (int i = 0; i < myDataReader.FieldCount; i++) { string c = Convert.ToString(myDataReader.GetValue(i)).Trim(); fields.Add(c); } } ////////////////////////////////////xml//////////////////////////////////////////// XmlDocument doc = new XmlDocument(); XmlDeclaration dec = doc.CreateXmlDeclaration("1.0", "UTF-8", null); doc.AppendChild(dec); //创建一个根节点(一级) XmlElement root = doc.CreateElement("datas"); doc.AppendChild(root); //读取主体 while (myDataReader.Read()) { XmlElement element = doc.CreateElement("data"); for (int i = 0; i < myDataReader.FieldCount; i++) { string value = Convert.ToString(myDataReader.GetValue(i)).Trim(); Console.Write(value + "\t"); element.SetAttribute(fields[i].ToString(), value); } root.AppendChild(element); //Console.WriteLine(""); } string file=sheetsName.Rows[t][2].ToString().Replace("$",".xml"); file = this.textBox3.Text +"/"+file; try { doc.Save(file); } catch (Exception e) { MessageBox.Show(e.Message); } myDataReader.Dispose(); } con.Close(); } private void button3_Click(object sender, EventArgs e) { if (textBox1.Text == "") { MessageBox.Show("请选择XML文件。。。。"); return; } if (textBox3.Text == "") { MessageBox.Show("请输入保存目录。。。。"); return; } readExecl2(); } } }