/*
编写：围城(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();
}
}
}
