//打开一个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请刷新下列表");
}
}
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);
}
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_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();
}
}
public static void ReadConfigXML()
{
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)
{
Setting.tablePath = node.InnerText;
}
node = root.SelectSingleNode("tortoiseSvnPath");
if (node != null)
{
Setting.tortoiseSvnPath = node.InnerText;
}
node = root.SelectSingleNode("svnPath");
if (node != null)
{
Setting.svnPath = node.InnerText;
}
node = root.SelectSingleNode("pythonPath");
if (node != null)
{
Setting.pythonPath = node.InnerText;
}
node = root.SelectSingleNode("siteUrl");
if (node != null)
{
Setting.siteUrl = node.InnerText;
}
}
catch (System.Exception ex)
{
ConfigUtils.Error(ex.Message, ex.StackTrace);
}
}
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);
}
}
//单个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);
}
//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);
}