//tree.xml读写
public static List <Node> ReadTableTreeXML()
{
ConfigUtils.CreateTableTreeXML();
XmlDocument doc = new XmlDocument();
List <Node> list = new List <Node>();
try
{
doc.Load(Setting.TREE_FILE);
XmlNode root = doc.SelectSingleNode("root");
foreach (XmlNode node in root.ChildNodes)
{
Node nd = new Node();
nd.sheetname = new List <string>();
XmlElement ele = (XmlElement)node;
nd.bookname = ele.GetAttribute("name");
foreach (XmlNode subNode in ele.ChildNodes)
{
nd.sheetname.Add(subNode.InnerText);
}
list.Add(nd);
}
}
catch (System.Exception ex)
{
ConfigUtils.Error(ex.Message, ex.StackTrace);
}
return(list);
}
private void btn_Search_Click(object sender, EventArgs e)
{
try
{
int start, end;
if (!int.TryParse(tb_StartId.Text, out start))
{
start = 0;
}
if (!int.TryParse(tb_EndId.Text, out end))
{
end = 100000000;
}
filter = string.Format("{0} >= {1} AND {0} <= {2} AND ({0} LIKE '%{4}%' OR {3} LIKE '%{4}%')",
args.column_id, start, end, args.column_name, tb_Keyword.Text);
cur_dataView.RowFilter = filter;
args.start_id = tb_StartId.Text;
args.end_id = tb_EndId.Text;
XMLController.WriteTableSearchXML();
InitTable();
}
catch (Exception ex)
{
ConfigUtils.Error(ex.Message, ex.StackTrace);
if (ex.InnerException != null)
{
ConfigUtils.Error(ex.InnerException.Message, ex.InnerException.StackTrace);
}
}
}
//更新TreeView
private void refreshTreeView()
{
try
{
tv_table.BeginUpdate();
tv_table.Nodes.Clear();
tableNodeList = XMLController.ReadTableTreeXML();
foreach (Node node in tableNodeList)
{
TreeNode tnode = new TreeNode(node.bookname);
tnode.ContextMenuStrip = cms_TableNode;
if (modifyNodeList.Contains(node.bookname))
{
tnode.ForeColor = System.Drawing.Color.Red;
}
foreach (string name in node.sheetname)
{
TreeNode sNode = new TreeNode(name);
sNode.ContextMenuStrip = cms_TableNode;
tnode.Nodes.Add(sNode);
}
tv_table.Nodes.Add(tnode);
}
tv_table.EndUpdate();
}
catch (Exception ex)
{
ConfigUtils.Error(ex.Message, ex.StackTrace);
MessageBox.Show("Error: " + ex.Message);
}
}
//关闭事件
private void BeforeBookClose(Excel.Workbook Wb, ref bool Cancel)
{
//This is called when you choose to close the workbook in Excel.
//The event handlers are removed, and then the workbook is closed
// 是否保存
//Wb.Saved = true;
//事件移除
try
{
if (!Wb.Saved)
{
//switch (MessageBox.Show("你还没保存,是否要保存?", "提醒", MessageBoxButtons.YesNoCancel))
//{
// case DialogResult.Yes:
//foreach (Excel.Worksheet sheet in wb.Worksheets)
//{
// sheet.SelectionChange -= EventDel_SelectionChange;
// sheet.Change -= EventDel_CellsChange;
//}
//app.WorkbookBeforeClose -= EventDel_BeforeBookClose;
//Wb.SaveAs();
//Wb.Close();
////关闭进程
//// Close();
//Close(app);
// break;
// case DialogResult.No:
// foreach (Excel.Worksheet sheet in wb.Worksheets)
// {
// sheet.SelectionChange -= EventDel_SelectionChange;
// sheet.Change -= EventDel_CellsChange;
// }
// app.WorkbookBeforeClose -= EventDel_BeforeBookClose;
// Wb.Close();
// //关闭进程
// // Close();
// Close(app);
// break;
// case DialogResult.Cancel:
// Cancel = true;
// break;
// default: break;
//}
}
else
{
ExcelUtils.Close(app);
}
}
catch (Exception ex)
{
ConfigUtils.Error("Error: " + ex.Message);
ExcelUtils.Close(app);
}
//xlSheet1.Change -= EventDel_CellsChange;
//xlSheet2.Change -= EventDel_CellsChange;
//xlSheet3.Change -= EventDel_CellsChange;
}
//配置文件config的读写
public static void WriteConfigXML()
{
ConfigUtils.CreateConfigXML();
XmlDocument doc = new XmlDocument();
try
{
doc.Load(Setting.CONFIG_FILE);
XmlNode root = doc.SelectSingleNode("root");
XmlNode node = root.SelectSingleNode("tablePath");
if (node == null)
{
node = doc.CreateElement("tablePath");
root.AppendChild(node);
}
node.InnerText = Setting.tablePath;
node = root.SelectSingleNode("tortoiseSvnPath");
if (node == null)
{
node = doc.CreateElement("tortoiseSvnPath");
root.AppendChild(node);
}
node.InnerText = Setting.tortoiseSvnPath;
node = root.SelectSingleNode("svnPath");
if (node == null)
{
node = doc.CreateElement("svnPath");
root.AppendChild(node);
}
node.InnerText = Setting.svnPath;
node = root.SelectSingleNode("pythonPath");
if (node == null)
{
node = doc.CreateElement("pythonPath");
root.AppendChild(node);
}
node.InnerText = Setting.pythonPath;
node = root.SelectSingleNode("siteUrl");
if (node == null)
{
node = doc.CreateElement("siteUrl");
root.AppendChild(node);
}
node.InnerText = Setting.siteUrl;
doc.Save(Setting.CONFIG_FILE);
}
catch (System.Exception ex)
{
ConfigUtils.Error(ex.Message, ex.StackTrace);
}
}
//关闭进程
public void Close()
{
if (app != null)
{
try
{
if (wb.Saved)
{
wb.Close();
app.Quit();
ExcelUtils.Close(app);
}
else
{
switch (MessageBox.Show("你还没保存,是否要保存?", "提醒", MessageBoxButtons.YesNo))
{
case DialogResult.Yes:
try
{
wb.SaveAs();
}
catch (System.Exception ex)
{
ConfigUtils.Error(ex.Message, ex.StackTrace);
if (ex.InnerException != null)
{
ConfigUtils.Error(ex.InnerException.Message, ex.InnerException.StackTrace);
}
// MessageBox.Show("Error: " + ex.Message);
}
finally
{
wb.Close();
app.Quit();
ExcelUtils.Close(app);
}
break;
case DialogResult.No:
wb.Close();
app.Quit();
ExcelUtils.Close(app);
break;
}
}
}
catch (Exception ex)
{
ConfigUtils.Error(ex.Message, ex.StackTrace);
MessageBox.Show("Error: " + ex.Message);
}
}
}
private void bw_LoadData_RunWorkerCompleted(object sender, System.ComponentModel.RunWorkerCompletedEventArgs e)
{
if (e.Error != null)
{
ConfigUtils.Error(e.Error.Message, e.Error.StackTrace);
}
else
{
btn_Search.PerformClick();
}
}
//直接打开Excel文件
private void OpenExcel2()
{
try
{
System.Diagnostics.Process.Start(cur_filename);
}
catch (Exception ex)
{
ConfigUtils.Error(ex.Message, ex.StackTrace);
MessageBox.Show("Error: " + ex.Message + "\n请刷新下列表");
}
}
private void tnmi_OpenDirectory_Click(object sender, EventArgs e)
{
try
{
System.Diagnostics.Process.Start("Explorer.exe", "/select," + cur_filename);
}
catch (Exception ex)
{
ConfigUtils.Error(ex.Message, ex.StackTrace);
MessageBox.Show("Error: " + ex.Message + "\n请刷新下列表");
}
}
public static bool WriteTableTreeXML(string excelDirectory, BackgroundWorker bw)
{
ConfigUtils.CreateTableTreeXML();
XmlDocument doc = new XmlDocument();
try
{
doc.Load(Setting.TREE_FILE);
XmlNode root = doc.SelectSingleNode("root");
root.InnerText = null;
DirectoryInfo baseDir = new DirectoryInfo(excelDirectory);
FileInfo[] files = baseDir.GetFiles("*.xls*");
int size = files.GetLength(0);
int cur = 0;
foreach (FileInfo file in files)
{
if (bw.CancellationPending)
{
return(false);
}
if (file.Name.StartsWith("~$"))
{
continue;
}
bw.ReportProgress((int)(cur * 100.0f / size), "正在搜索: " + file.Name);
XmlElement xmlElm = doc.CreateElement("excel");
xmlElm.SetAttribute("name", file.Name);
List <string> sheetNames = ExcelUtils.ImportSheetName(file.FullName);
foreach (string sheetName in sheetNames)
{
XmlElement xmlSubElm = doc.CreateElement("sheet");
xmlSubElm.InnerText = sheetName;
xmlElm.AppendChild(xmlSubElm);
}
root.AppendChild(xmlElm);
++cur;
}
doc.Save(Setting.TREE_FILE);
bw.ReportProgress(100, string.Format("共搜索到{0}个文件", size));
}
catch (System.Exception ex)
{
ConfigUtils.Error(ex.Message, ex.StackTrace);
return(false);
}
return(true);
}
//打开一个Excel[监听事件]
private void OpenExcel()
{
//打开一个新的Excel
try
{
ExcelEdit EEdit = new ExcelEdit();
EEdit.Open(cur_filename, cur_sheetname);
}
catch (Exception ex)
{
ConfigUtils.Error(ex.Message, ex.StackTrace);
MessageBox.Show("Error: " + ex.Message + "\n请刷新下列表");
}
}
private void bw_ReloadTableXML_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
{
if (e.Cancelled == true)
{
MessageBox.Show("您取消了刷新, 数据未更新!");
}
else if (e.Error != null)
{
ConfigUtils.Error(e.Error.Message, e.Error.StackTrace);
MessageBox.Show("Error: " + e.Error.Message);
}
else
{
logDialog.SetButtonState(ButtonState.OK);
SvnDiffExcel();
}
}
private void bw_SaveCsv_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
{
if (e.Cancelled == true)
{
MessageBox.Show("您取消了操作,数据未完全保存");
}
else if (e.Error != null)
{
ConfigUtils.Error(e.Error.Message, e.Error.StackTrace);
MessageBox.Show("Error: " + e.Error.Message);
}
else
{
logDialog.AppendText("操作完成！");
logDialog.SetButtonState(ButtonState.OK);
}
}
private void bw_Csv2Code_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
{
if (e.Error != null)
{
ConfigUtils.Error(e.Error.Message, e.Error.StackTrace);
logDialog.AppendText(e.Error.Message + Environment.NewLine);
}
else if (e.Cancelled)
{
MessageBox.Show("您取消了导表, 操作未完成");
}
else
{
ConfigUtils.Log(logDialog.GetText().Replace("\n", Environment.NewLine));
logDialog.AppendText(string.Format("日志已被保存到 {0}{1}",
Setting.LOG_PATH, Environment.NewLine));
}
logDialog.SetButtonState(ButtonState.OK);
}
private void bw_refresh_RunWorkerCompleted(object sender, System.ComponentModel.RunWorkerCompletedEventArgs e)
{
if (e.Error != null)
{
ConfigUtils.Error(e.Error.Message, e.Error.StackTrace);
}
else
{
try
{
int curIndex = dgv_table.FirstDisplayedScrollingRowIndex;
this.cur_row = pageSize * (cur_page - 1);
LoadPageData();
dgv_table.FirstDisplayedScrollingRowIndex = curIndex;
}
catch (Exception ex)
{
ConfigUtils.Error(ex.Message, ex.StackTrace);
}
}
}
//加载数据表
private void TableLoad()
{
try
{
DataTable table = ExcelUtils.ImportExcel(this.filename, args.sheetname, args.columns, null, null, false);
if (table == null)
{
return;
}
for (int i = 0; i < table.Rows.Count; ++i)
{
if (table.Rows[i].RowState == DataRowState.Detached ||
table.Rows[i].RowState == DataRowState.Deleted)
{
continue;
}
string cell = table.Rows[i][0].ToString().Trim();
if ("表头".Equals(cell))
{
int j = 0;
foreach (DataColumn col in table.Columns)
{
col.ColumnName = table.Rows[i][j++].ToString();
}
}
table.Rows[i].Delete();
if ("BEGIN".Equals(cell))
{
break;
}
}
cur_dataView = table.DefaultView;
}
catch (Exception ex)
{
ConfigUtils.Error(ex.Message, ex.StackTrace);
MessageBox.Show("Error: " + ex.Message);
}
}
public static void WriteTableSearchXML()
{
ConfigUtils.CreateTableSearchXML();
XmlDocument doc = new XmlDocument();
try
{
doc.Load(Setting.SEARCH_FILE);
XmlNode root = doc.SelectSingleNode("root");
XmlNodeList nodes = root.SelectNodes("node");
if (nodes.Count < Setting.searchTables.Count)
{
for (int i = nodes.Count; i < Setting.searchTables.Count; ++i)
{
XmlNode node = doc.CreateElement("node");
root.AppendChild(node);
}
}
else
{
for (int i = Setting.searchTables.Count; i < nodes.Count; ++i)
{
root.RemoveChild(nodes[i]);
}
}
nodes = root.SelectNodes("node");
for (int i = 0; i < nodes.Count; ++i)
{
Setting.searchTables[i].ToXmlNode(doc, nodes[i]);
}
doc.Save(Setting.SEARCH_FILE);
}
catch (Exception ex)
{
ConfigUtils.Error(ex.Message, ex.StackTrace);
}
}
//搜索定制表格tableSearch.xml的读写
public static void ReadTableSearchXML()
{
ConfigUtils.CreateTableSearchXML();
XmlDocument doc = new XmlDocument();
try
{
doc.Load(Setting.SEARCH_FILE);
XmlNode root = doc.SelectSingleNode("root");
XmlNodeList nodes = root.SelectNodes("node");
Setting.searchTables.Clear();
foreach (XmlNode node in nodes)
{
SearchTableArgs args = new SearchTableArgs();
args.FromXmlNode(node);
Setting.searchTables.Add(args);
}
}
catch (Exception ex)
{
ConfigUtils.Error(ex.Message, ex.StackTrace);
}
}
//xlsx to csv 批量导出
public static bool XlsxToCsv(BackgroundWorker bw, List <string> modify = null)
{
Excel.Application elapp = new Excel.Application();
Excel.Workbooks elwbs;
Excel.Workbook elwb;
int sheetcount = 0;
try
{
elwbs = elapp.Workbooks;
DirectoryInfo baseDir = new DirectoryInfo(Setting.tablePath + Setting.EXCEL);
FileInfo[] files = baseDir.GetFiles("*.xls*");
double size = files.Length;
double cnt = 0;
elapp.DisplayAlerts = false;
foreach (FileInfo file in files)
{
if (bw.CancellationPending)
{
return(false);
}
if (file.Name.StartsWith("~$"))
{
continue;
}
if (modify != null && !modify.Contains(file.Name))
{
continue;
}
elwb = (Excel.Workbook)elwbs.Open(file.FullName);
foreach (Excel.Worksheet elsheet in elwb.Worksheets)
{
Excel.Range range = elsheet.Cells.Find("BEGIN", Type.Missing, Type.Missing, Excel.XlLookAt.xlWhole, Excel.XlSearchOrder.xlByColumns);
if (range == null)
{
continue;
}
int rowid = range.Row;
string name = elsheet.Cells[rowid, 2].Value as string;
if (name == null || name == "")
{
continue;
}
bw.ReportProgress((int)(cnt / size * 100), "正在保存: " + name + "(" + file.Name + ")");
elsheet.SaveAs(Setting.tablePath + Setting.CSV + name, Excel.XlFileFormat.xlCSV);
++sheetcount;
}
elwb.Close();
++cnt;
}
elapp.DisplayAlerts = true;
}
catch (Exception ex)
{
ConfigUtils.Error(ex.Message, ex.StackTrace);
return(false);
}
finally
{
bw.ReportProgress(100, string.Format("共保存{0}个文件", sheetcount));
Close(elapp);
}
return(true);
}
//单个Excel或SheetToCsv
public static bool XlsxToCsv(BackgroundWorker bw, string filename, string sheetName = null)
{
Excel.Application elapp = new Excel.Application();
Excel.Workbook elwb;
double cnt = 0;
try
{
elwb = (Excel.Workbook)elapp.Workbooks.Open(filename);
elapp.DisplayAlerts = false;
if (sheetName == null)
{
double size = elwb.Worksheets.Count;
foreach (Excel.Worksheet elsheet in elwb.Worksheets)
{
if (bw.CancellationPending)
{
return(false);
}
Excel.Range range = elsheet.Cells.Find("BEGIN", Type.Missing, Type.Missing, Excel.XlLookAt.xlWhole, Excel.XlSearchOrder.xlByColumns);
if (range == null)
{
continue;
}
int rowid = range.Row;
string name = elsheet.Cells[rowid, 2].Value as string;
if (name == null || name == "")
{
continue;
}
elsheet.SaveAs(Setting.tablePath + Setting.CSV + name, Excel.XlFileFormat.xlCSV);
bw.ReportProgress((int)(cnt / size * 100), "已保存: " + elsheet.Name);
++cnt;
}
}
else
{
Excel.Worksheet sheet = elwb.Worksheets[sheetName];
Excel.Range range = sheet.Cells.Find("BEGIN", Type.Missing, Type.Missing, Excel.XlLookAt.xlWhole, Excel.XlSearchOrder.xlByColumns);
if (range != null)
{
int rowid = range.Row;
string name = sheet.Cells[rowid, 2].Value as string;
if (name != null && name != "")
{
sheet.SaveAs(Setting.tablePath + Setting.CSV + name, Excel.XlFileFormat.xlCSV);
++cnt;
}
}
}
elapp.DisplayAlerts = true;
}
catch (Exception ex)
{
ConfigUtils.Error(ex.Message, ex.StackTrace);
return(false);
}
finally
{
bw.ReportProgress(100, string.Format("共保存{0}个文件", cnt));
Close(elapp);
}
return(true);
}
