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