private void UpdateExcelData(List <TemplateStruct> list, Dictionary <string, List <TemplateValue> > dic, MSExcel.Workbook wbook)
{
for (int i = 0; i < list.Count; i++)
{
TemplateStruct ts = list[i];
List <TemplateValue> temp = dic[list[i].NodeName];
int start = ExcelDataStart;
MSExcel.Worksheet ws = (MSExcel.Worksheet)wbook.Sheets[i + 1];
for (int j = 0; j < temp.Count; j++)
{
TemplateValue tv = temp[j];
for (int k = 0; k < ts.Attributes.Count; k++)
{
MSExcel.Range r = ws.get_Range(getCell(start, k + StructCol));
r.Value2 = tv.Props[ts.Attributes[k]];
}
start++;
}
}
}
private void CreateExcelStruct(List <TemplateStruct> list, MSExcel.Application excel, MSExcel.Workbook wbook)
{
for (int i = 0; i < list.Count; i++)
{
if (i > 0)
{
wbook.Sheets.Add(Type.Missing, wbook.Sheets[i], 1, Type.Missing);
}
MSExcel.Worksheet ws = (MSExcel.Worksheet)wbook.Sheets[i + 1];
ws.Select();
TemplateStruct ts = list[i];
ws.Name = ts.NodeName;
MSExcel.Range r = ws.get_Range(getCell(FreezeRow, FreezeCol), Type.Missing);
r.Select();
excel.ActiveWindow.FreezePanes = true;
for (int j = 0; j < ts.Attributes.Count; j++)
{
r = ws.get_Range(getCell(StructRow, j + StructCol));
r.ColumnWidth = 15;
r.Value2 = ts.Attributes[j];
}
}
}
private List <TemplateStruct> GetTemplateStructList(XmlNode root)
{
List <TemplateStruct> list = new List <TemplateStruct>();
foreach (XmlNode node in root.ChildNodes)
{
if (node.ChildNodes.Count != 0)
{
XmlNode item = node.ChildNodes[0];
if (item.NodeType != XmlNodeType.Element)
{
continue;
}
TemplateStruct t = new TemplateStruct();
t.NodeName = item.LocalName;
t.Attributes = new List <string>();
foreach (XmlAttribute att in item.Attributes)
{
t.Attributes.Add(att.LocalName);
}
list.Add(t);
}
}
return(list);
}
private Dictionary <string, TemplateStruct> GetExcelStructList(MSExcel.Workbook wbook)
{
Dictionary <string, TemplateStruct> list = new Dictionary <string, TemplateStruct>();
int count = wbook.Worksheets.Count;
for (int i = 0; i < count; i++)
{
MSExcel.Worksheet sheet = wbook.Worksheets[i + 1];
TemplateStruct ts = new TemplateStruct();
ts.NodeName = sheet.Name;
ts.Attributes = new List <string>();
ts.Types = new List <string>();
list[ts.NodeName] = ts;
for (int j = 0; j < ExcelMaxCol; j++)
{
MSExcel.Range r = sheet.get_Range(getCell(StructRow, j + StructCol));
if (string.IsNullOrEmpty(r.Value2))
{
break;
}
ts.Attributes.Add(r.Value2);
MSExcel.Range r2 = sheet.get_Range(getCell(TypeRow, j + StructCol));
if (string.IsNullOrEmpty(r.Value2))
{
ts.Types.Add("System.String");
}
else
{
ts.Types.Add(r2.Value2);
}
}
}
return(list);
}
private Dictionary <string, List <TemplateValue> > GetExcelList(MSExcel.Workbook wbook, Dictionary <string, TemplateStruct> list)
{
Dictionary <string, List <TemplateValue> > dic = new Dictionary <string, List <TemplateValue> >();
int count = wbook.Worksheets.Count;
for (int i = 0; i < count; i++)
{
MSExcel.Worksheet sheet = wbook.Worksheets[i + 1];
TemplateStruct ts = list[sheet.Name];
if (!dic.ContainsKey(ts.NodeName))
{
dic[ts.NodeName] = new List <TemplateValue>();
}
int start = ExcelDataStart;
bool endRow = false;
for (int j = 0; j < ExcelMaxRow; j++)
{
TemplateValue tv = new TemplateValue();
tv.NodeName = ts.NodeName;
tv.Props = new Dictionary <string, string>();
for (int k = 0; k < ts.Attributes.Count; k++)
{
string attribute = ts.Attributes[k];
MSExcel.Range r = sheet.get_Range(getCell(start, k + StructCol));
if (k == 0 && string.IsNullOrEmpty(r.FormulaLocal))
{
endRow = true;
break;
}
if (r.FormulaLocal.StartsWith("="))
{
int temp = Convert.ToInt32(r.Value2);
if (r.Value2 > temp)
{
tv.Props[attribute] = Convert.ToString(r.Value2);
}
else
{
tv.Props[attribute] = temp.ToString();
}
}
else
{
tv.Props[attribute] = r.FormulaLocal;
}
}
start++;
if (endRow)
{
break;
}
dic[ts.NodeName].Add(tv);
}
}
return(dic);
}
private void button2_Click(object sender, EventArgs e)
{
OpenFileDialog ofd = new OpenFileDialog();
ofd.Title = "选择文件";
ofd.Filter = "Excel 文件(*.xlsx,*.xls)|*.xlsx;*.xls";
ofd.FilterIndex = 1;
List <string> temp, temp2;
Dictionary <string, TemplateStruct> list;
Dictionary <string, List <TemplateValue> > dic;
if (ofd.ShowDialog(this) == System.Windows.Forms.DialogResult.OK)
{
string str = ofd.FileName;
MSExcel.Application excel = new MSExcel.Application();
MSExcel.Workbook wbook = excel.Workbooks.Open(str);
list = GetExcelStructList(wbook);
dic = GetExcelList(wbook, list);
temp = list.Keys.ToList();
XmlDocument doc = new XmlDocument();
doc.LoadXml("<root></root>");
XmlNode root = doc.DocumentElement;
for (int i = 0; i < temp.Count; i++)
{
TemplateStruct ts = list[temp[i]];
List <TemplateValue> tvList = dic[ts.NodeName];
XmlNode node = doc.CreateNode(XmlNodeType.Element, "ArrayOf" + ts.NodeName, "");
root.AppendChild(node);
for (int j = 0; j < tvList.Count; j++)
{
TemplateValue tv = tvList[j];
temp2 = tv.Props.Keys.ToList();
XmlNode item = doc.CreateNode(XmlNodeType.Element, tv.NodeName, "");
node.AppendChild(item);
for (int k = 0; k < temp2.Count; k++)
{
XmlAttribute xa = doc.CreateAttribute(temp2[k]);
xa.Value = tv.Props[temp2[k]];
item.Attributes.Append(xa);
}
}
}
wbook.Close(false);
excel.Quit();
string path = str.Substring(0, str.LastIndexOf("\\"));
string filename = str.Substring(str.LastIndexOf("\\") + 1).Replace(".xlsx", "").Replace(".xls", "") + ".xml";
path = Path.Combine(path, filename);
try
{
File.Delete(path);
doc.Save(path);
MessageBox.Show("转换成功");
}
catch //(Exception ex)
{
MessageBox.Show("转换失败");
return;
}
}
}
