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); }