public static bool AnalyzeExcel(ExcelXMLLayout layout)
{
System.Data.OleDb.OleDbConnection conn = null;
try
{
conn = new System.Data.OleDb.OleDbConnection(MakeConnectionString(layout.solution.path));
conn.Open();
System.Data.DataTable table = conn.GetOleDbSchemaTable(
System.Data.OleDb.OleDbSchemaGuid.Columns,
new object[] { null, null, layout.sheet + "$", null });
layout.Clear();
System.Diagnostics.Debug.WriteLine("Start Analyze [" + table.Rows.Count + "]");
foreach (System.Data.DataRow row in table.Rows)
{
string name = row["Column_Name"].ToString();
System.Diagnostics.Debug.WriteLine(name);
// 测试数据类型
ExcelXMLLayout.KeyType testType = ExcelXMLLayout.KeyType.Unknown;
{
System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(
string.Format("select [{0}] from [{1}$]", name, layout.sheet), conn
);
System.Data.OleDb.OleDbDataReader r = cmd.ExecuteReader();
while (r.Read())
{
System.Diagnostics.Debug.WriteLine(r[0].GetType());
if (r[0].GetType() == typeof(System.Double))
{
testType = ExcelXMLLayout.KeyType.Integer;
break;
}
if (testType == ExcelXMLLayout.KeyType.String)
{
break;
}
testType = ExcelXMLLayout.KeyType.String;
}
r.Close();
cmd.Dispose();
}
layout.Add(name, testType);
}
table.Dispose();
conn.Close();
return true;
}
catch (Exception outErr)
{
lastError = string.Format("无法分析，Excel 无法打开\r\n{0}", outErr.Message);
}
return false;
}
public bool Load(string file = "config.xml")
{
try
{
System.Xml.XmlDocument doc = new System.Xml.XmlDocument();
doc.Load(file);
System.Xml.XmlNode root = doc.SelectSingleNode("root");
m_solutions.Clear();
foreach (System.Xml.XmlNode xls in root.SelectNodes("excel"))
{
ExcelSolution solu = new ExcelSolution(this);
solu.name = xls.Attributes["name"].Value;
solu.path = xls.Attributes["path"].Value;
foreach (System.Xml.XmlNode layoutNode in xls.SelectNodes("layout"))
{
ExcelXMLLayout layout = new ExcelXMLLayout(solu);
layout.path = layoutNode.Attributes["path"].Value;
layout.sheet = layoutNode.Attributes["sheet"].Value;
layout.name = layoutNode.Attributes["name"].Value;
layout.summary = layoutNode.Attributes["summary"].Value;
layout.typed = bool.Parse(layoutNode.Attributes["typed"].Value);
try { layout.primary = layoutNode.Attributes["primary"].Value; }
catch (Exception) { }
try { layout.primaryComment = layoutNode.Attributes["primary-comment"].Value; }
catch (Exception) { }
foreach (System.Xml.XmlNode column in layoutNode.SelectNodes("column"))
{
layout.Add(
column.InnerText,
(ExcelXMLLayout.KeyType)Enum.Parse(typeof(ExcelXMLLayout.KeyType), column.Attributes["type"].Value)
);
}
solu.AddLayout(layout);
}
AddSolution(solu);
}
return true;
}
catch (Exception err)
{
System.Diagnostics.Debug.WriteLine(err.Message);
}
return false;
}
