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;
}
public static bool DoExport(ExcelXMLLayout layout)
{
try
{
System.Data.OleDb.OleDbConnection conn =
new System.Data.OleDb.OleDbConnection(DB.ExcelFile.MakeConnectionString(layout.solution.path));
StringBuilder sb = new StringBuilder(1024 * 1024);
try
{
conn.Open();
System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(
DB.ExcelFile.MakeSelectString(layout), conn);
try
{
System.Data.OleDb.OleDbDataReader r = cmd.ExecuteReader();
sb.Append("<?xml version='1.0' encoding='utf-8'?>");
sb.Append("<dataroot>");
while (r.Read())
{
if (IsHeadRow(r, layout))
{
continue;
}
sb.AppendFormat("<{0}>", layout.name);
for (int i = 0; i < r.FieldCount; ++i)
{
if (layout.typed)
{
sb.AppendFormat("<{0} type='{1}'>{2}</{0}>",
layout[i].name, ExcelXMLLayout.Type2Int(layout[i].type), r[i].ToString());
}
else
{
sb.AppendFormat("<{0}>{1}</{0}>", layout[i].name, r[i].ToString());
}
}
sb.AppendFormat("</{0}>", layout.name);
}
sb.Append("</dataroot>");
r.Dispose();
}
catch (Exception rErr)
{
lastError = string.Format("表格查询命令\r\n{0}", rErr.ToString());
cmd.Dispose();
conn.Close();
conn.Dispose();
return false;
}
cmd.Dispose();
conn.Close();
conn.Dispose();
}
catch (Exception connOpenErr)
{
lastError = string.Format("表格打开失败\r\n{0}", connOpenErr.ToString());
conn.Dispose();
return false;
}
//System.Diagnostics.Debug.WriteLine(sb.ToString());
try
{
System.IO.TextWriter w = System.IO.File.CreateText(layout.path);
w.Write(sb.ToString());
w.Close();
}
catch (Exception wErr)
{
lastError = string.Format("目标XML文件无法写入\r\n{0}", wErr.ToString());
return false;
}
return true;
}
catch (Exception outErr)
{
lastError = string.Format("Excel 无法打开\r\n{0}", outErr.ToString());
System.Diagnostics.Debug.WriteLine(outErr.ToString());
}
return false;
}
public static string MakeSelectString(ExcelXMLLayout layout)
{
StringBuilder sb = new StringBuilder();
sb.Append("SELECT ");
int index = 1;
foreach (ExcelXMLLayout.KeyPair k in layout.EachKeyPair())
{
sb.Append("[");
sb.Append(k.name);
sb.Append("]");
if (index != layout.count)
{
sb.Append(",");
}
index++;
}
sb.Append(" FROM [");
sb.Append(layout.sheet);
sb.Append("$]");
return sb.ToString();
}
public static bool IsHeadRow(System.Data.OleDb.OleDbDataReader r, ExcelXMLLayout layout)
{
if (layout[0].name.ToLower() == layout.primary)
{
string val = r[0].ToString();
return (val == "" ||
(val.Length >= 2 && val.Substring(0, 2) == "__") ||
(val.Length >= 2 && val.Substring(0, 2) == "表头") ||
(val.Length >= 2 && val.Substring(0, 2) == "编号")
);
}
for (int i = 0; i < layout.count; ++i)
{
if (layout[i].name.ToLower()==layout.primary)
{
return r[i].ToString() == layout.primaryComment;
}
}
if (layout[0].name.ToLower() == "id")
{
string val = r[0].ToString();
return (val == "" ||
(val.Length >= 2 && val.Substring(0, 2) == "__") ||
(val.Length >= 2 && val.Substring(0, 2) == "表头") ||
(val.Length >= 2 && val.Substring(0, 2) == "编号")
);
}
return false;
}
public void RemoveLayout(ExcelXMLLayout layout)
{
m_layouts.Remove(layout);
}
public void AddLayout(ExcelXMLLayout layout)
{
m_layouts.Add(layout);
}
private void 添加LayoutToolStripMenuItem_Click(object sender, EventArgs e)
{
DB.ExcelSolution solu = tvExcel.SelectedNode.Tag as DB.ExcelSolution;
DB.ExcelXMLLayout layout = new DB.ExcelXMLLayout(solu);
solu.AddLayout(layout);
Util_AddLayout(tvExcel.SelectedNode, layout);
}
