private void InitExcuteTable() { cbbExcuteTable.Items.Clear(); if (string.IsNullOrEmpty(txtSource.Text)) { return; } string[] listAllExcel = DirFileHelper.GetFileNames(txtSource.Text, "*.xls", true); Hashtable ht = new Hashtable(); foreach (var item in listAllExcel) { Workbook wb = new Workbook(item); foreach (var sheet in wb.Worksheets) { if (!ht.ContainsKey(sheet.Name)) { ht.Add(sheet.Name, null); } } } foreach (var key in ht.Keys) { cbbExcuteTable.Items.Add(key); } }
void workCheck_DoWork(object sender, DoWorkEventArgs e) { try { workCheck.ReportProgress(0, "开始处理..."); string[] listAllExcel = DirFileHelper.GetFileNames(txtSource.Text, "*.xls", true); Workbook template = new Workbook(listAllExcel[0]); foreach (var item in listAllExcel) { if (item == listAllExcel[0]) { continue; } Workbook wb = new Workbook(item); template.Combine(wb); } for (int i = 0; i < template.Worksheets.Count; i++) { if (!IsExcuteSheet(template.Worksheets[i].Name)) { template.Worksheets.RemoveAt(i); i--; } } template.Save(txtResult.Text); workCheck.ReportProgress(0, "处理完成"); } catch (Exception ex) { workCheck.ReportProgress(0, ex.Message); } }
void workCheck_DoWork(object sender, DoWorkEventArgs e) { try { bool isUseTemplate = false; workCheck.ReportProgress(0, "开始处理..."); List <ModelStore> list = new List <ModelStore>(); string[] listAllExcel = DirFileHelper.GetFileNames(txtSource.Text, "*.xls", true); Workbook workbookTemplate; workbookTemplate = new Workbook(listAllExcel[0]); Cells cellsTemplate = workbookTemplate.Worksheets[0].Cells; string str = string.Empty; // workCheck.ReportProgress(0, "模版处理..."); for (int i = 0; i < cellsTemplate.MaxDataRow + 1; i++) { for (int j = 0; j < cellsTemplate.MaxDataColumn + 1; j++) { if (cellsTemplate[i, j].IsFormula) { continue; } if (isUseTemplate) { if (cellsTemplate[i, j].Value != null && cellsTemplate[i, j].StringValue.ToLower() == TemplateTag) { list.Add(new ModelStore { row = i, column = j }); } } else { list.Add(new ModelStore { row = i, column = j }); } } } if (e.Argument.ToString() == "按行排列") { SumByRow(listAllExcel, list); } else if (e.Argument.ToString() == "按列排列") { SumByColumn(listAllExcel, list); } workCheck.ReportProgress(0, "处理完成"); } catch (Exception ex) { workCheck.ReportProgress(0, ex.Message); } }
void workCheck_DoWork(object sender, DoWorkEventArgs e) { try { ExportTableOptions opts = new ExportTableOptions(); opts.ExportAsString = true; workCheck.ReportProgress(0, "开始处理..."); string[] listAllExcel = DirFileHelper.GetFileNames(txtSource.Text, "*.xls", true); Workbook template = new Workbook(); Cells TemplateCells = template.Worksheets[0].Cells; foreach (var item in listAllExcel) { Workbook workbook = new Workbook(item); foreach (var sheet in workbook.Worksheets) { workCheck.ReportProgress(0, "开始处理:" + item + "/" + sheet.Name); if (!IsExcuteSheet(sheet.Name)) { workCheck.ReportProgress(0, "此sheet名称不在待处理表中,将忽略...---" + sheet.Name); continue; } Cells cells = sheet.Cells; DataTable table = cells.ExportDataTable(StartRow - 1, 0, cells.MaxDataRow - StartRow - EndRow + 3, cells.MaxDataColumn + 1, opts); // DataTable table = cells.ExportDataTable(StartRow - 1, 0, 5, cells.MaxDataColumn + 1, opts); //TemplateCells.ImportDataTable(table, false, 0, 0, table.Rows.Count, table.Columns.Count, true, "yyyy/MM/dd"); TemplateCells.ImportDataTable(table, false, 0, 0, table.Rows.Count, table.Columns.Count, true, "mm/dd/yyyy", false); } } TemplateCells.DeleteBlankRows(); DataTable tableSource = TemplateCells.ExportDataTable(0, 0, TemplateCells.MaxDataRow + 1, TemplateCells.MaxDataColumn + 1, opts); tableSource = ReturnMergeData(tableSource); TemplateCells.Clear(); TemplateCells.ImportDataTable(tableSource, false, 0, 0, tableSource.Rows.Count, tableSource.Columns.Count, true, "mm/dd/yyyy", false); template.Save(txtResult.Text); workCheck.ReportProgress(0, "处理完成"); } catch (Exception ex) { workCheck.ReportProgress(0, ex.Message); } }
void workCheck_DoWork(object sender, DoWorkEventArgs e) { try { workCheck.ReportProgress(0, "开始处理..."); string[] listAllExcel = DirFileHelper.GetFileNames(txtSource.Text, "*.xls", true); Workbook template = new Workbook(); Cells TemplateCells = template.Worksheets[0].Cells; int k = 0; // DataTable TemplateTable = TemplateCells.ExportDataTable(StartRow - 1, 0, TemplateCells.MaxDataRow - EndRow + 1, TemplateCells.MaxDataColumn + 1); foreach (var item in listAllExcel) { Workbook workbook = new Workbook(item); ExportTableOptions opts = new ExportTableOptions(); opts.ExportAsString = true; foreach (var sheet in workbook.Worksheets) { if (!IsExcuteSheet(sheet.Name)) { workCheck.ReportProgress(0, "此sheet名称不在待处理表中,将忽略...---" + sheet.Name); continue; } workCheck.ReportProgress(0, "开始处理:" + item + "/" + sheet.Name); Cells cells = sheet.Cells; DataTable table; k++; if (k == listAllExcel.Length) { //最后一个表保留表头 table = cells.ExportDataTable(0, 0, cells.MaxDataRow + 1 - EndRow + 1, cells.MaxDataColumn + 1, opts); } else { table = cells.ExportDataTable(StartRow - 1, 0, cells.MaxDataRow - EndRow + 1, cells.MaxDataColumn + 1, opts); } for (int i = 0; i < table.Rows.Count; i++) { //去除无效数据行 if (table.Rows[i][pdKeyColumn - 1] == null || string.IsNullOrEmpty(table.Rows[i][pdKeyColumn - 1].ToString())) { table.Rows.RemoveAt(i); //调整索引减1 i--; continue; } } //TemplateCells.ImportDataTable(table, false, 0, 0, table.Rows.Count, table.Columns.Count, true, "yyyy/MM/dd"); TemplateCells.ImportDataTable(table, false, 0, 0, table.Rows.Count, table.Columns.Count, true, "yyyy/MM/dd"); } } template.Save(txtResult.Text); workCheck.ReportProgress(0, "处理完成"); } catch (Exception ex) { workCheck.ReportProgress(0, ex.Message); } }
void workCheck_DoWork(object sender, DoWorkEventArgs e) { try { workCheck.ReportProgress(0, "开始处理..."); List <ModelStore> list = new List <ModelStore>(); string[] listAllExcel = DirFileHelper.GetFileNames(txtSource.Text, "*.xls", true); Workbook workbookTemplate; workbookTemplate = new Workbook(txtTemplate.Text); Cells cellsTemplate = workbookTemplate.Worksheets[TemplateSheetName].Cells; string str = string.Empty; workCheck.ReportProgress(0, "模版处理..."); for (int i = 0; i < cellsTemplate.MaxDataRow + 1; i++) { for (int j = 0; j < cellsTemplate.MaxDataColumn + 1; j++) { if (cellsTemplate[i, j] == null || cellsTemplate[i, j].Value == null) { continue; } if (cellsTemplate[i, j].IsFormula) { list.Add(new ModelStore { row = i, column = j, IsFormula = true, cellType = cellsTemplate[i, j].Type, Formula = cellsTemplate[i, j].Formula }); } else { list.Add(new ModelStore { row = i, column = j, IsFormula = false, cellType = cellsTemplate[i, j].Type, Formula = cellsTemplate[i, j].Formula }); } } } foreach (var item in listAllExcel) { Workbook workbookOld = new Workbook(item); Workbook workbook = new Workbook(item); foreach (var sheet in workbook.Worksheets) { bool isPassGongshi = true; bool isPassGeshi = true; if (!IsExcuteSheet(sheet.Name)) { workCheck.ReportProgress(0, "此sheet名称不在待处理表中,将忽略...---" + sheet.Name); continue; } Cells cells = sheet.Cells; Cells cellsOld = workbookOld.Worksheets[sheet.Name].Cells; workCheck.ReportProgress(0, "开始处理数据...---" + workbook.FileName + "sheetName:" + sheet.Name); for (int i = 0; i < cells.MaxDataRow + 1; i++) { for (int j = 0; j < cells.MaxDataColumn + 1; j++) { ModelStore ms = list.Find(x => x.row == i && x.column == j); if (ms != null && ms.IsFormula) { cells[i, j].Formula = ms.Formula; } } } workbook.CalculateFormula(); for (int i = 0; i < cells.MaxDataRow + 1; i++) { for (int j = 0; j < cells.MaxDataColumn + 1; j++) { ModelStore ms = list.Find(x => x.row == i && x.column == j); if (ms == null) { continue; } if (ms != null && ms.IsFormula) { if (cells[i, j].StringValue != cellsOld[i, j].StringValue) { isPassGongshi = false; AddError(workbook.FileName + ",sheetName:" + sheet.Name, CellsHelper.CellIndexToName(i, j) + "公式有误"); } } else { if (cells[i, j].Type != ms.cellType) { isPassGeshi = false; AddError(workbook.FileName + ",sheetName:" + sheet.Name, CellsHelper.CellIndexToName(i, j) + "格式有误"); } } } } if (isPassGeshi) { AddError(workbook.FileName + ",sheetName:" + sheet.Name, "格式审核通过"); } if (isPassGongshi) { AddError(workbook.FileName + ",sheetName:" + sheet.Name, "公式审核通过"); } } } cellsTemplate.Clear(); cellsTemplate.ImportDataTable(tableResult, true, 0, 0, tableResult.Rows.Count, tableResult.Columns.Count); workbookTemplate.Save(txtResult.Text); workCheck.ReportProgress(0, "处理完成"); } catch (Exception ex) { workCheck.ReportProgress(0, ex.Message); } }
void workCheck_DoWork(object sender, DoWorkEventArgs e) { try { bool isUseTemplate = false; workCheck.ReportProgress(0, "开始处理..."); List <ModelStore> list = new List <ModelStore>(); string[] listAllExcel = DirFileHelper.GetFileNames(txtSource.Text, "*.xls", true); Workbook workbookTemplate; if (!string.IsNullOrEmpty(txtTemplate.Text)) { workbookTemplate = new Workbook(txtTemplate.Text); isUseTemplate = true; } else { workbookTemplate = new Workbook(listAllExcel[0]); } Cells cellsTemplate = workbookTemplate.Worksheets[0].Cells; string str = string.Empty; workCheck.ReportProgress(0, "模版处理..."); for (int i = 0; i < cellsTemplate.MaxDataRow + 1; i++) { for (int j = 0; j < cellsTemplate.MaxDataColumn + 1; j++) { if (cellsTemplate[i, j].IsFormula) { continue; } if (isUseTemplate) { if (cellsTemplate[i, j].Value != null && cellsTemplate[i, j].StringValue.ToLower() == TemplateTag) { list.Add(new ModelStore { row = i, column = j }); } } else { if (cellsTemplate[i, j].Type == CellValueType.IsNumeric) { list.Add(new ModelStore { row = i, column = j }); } } } } foreach (var item in listAllExcel) { if (!isUseTemplate && item == listAllExcel[0]) { continue; } Workbook workbook = new Workbook(item); Cells cells = workbook.Worksheets[0].Cells; workCheck.ReportProgress(0, "开始处理数据...---" + workbook.FileName); for (int i = 0; i < cells.MaxDataRow + 1; i++) { for (int j = 0; j < cells.MaxDataColumn + 1; j++) { if (list.FindIndex(x => x.row == i && x.column == j) >= 0 && cells[i, j].Type == CellValueType.IsNumeric) { if (isUseTemplate && cellsTemplate[i, j].Type != CellValueType.IsNumeric) { cellsTemplate[i, j].PutValue(cells[i, j].IntValue); } else { cellsTemplate[i, j].PutValue(cellsTemplate[i, j].IntValue + cells[i, j].IntValue); } } } } } workbookTemplate.Save(txtResult.Text); workCheck.ReportProgress(0, "处理完成"); } catch (Exception ex) { txtLog.AppendText(ex.Message); } }