public ReportDataSet ReadDataSet(string file, int headerRowNumber) { ReportDataSet dataSet = new ReportDataSet(); try { excel = new Excel.Application(); Excel.Workbook tempBook = excel.Workbooks.Open(file); foreach (var item in tempBook.Sheets) { Excel.Worksheet sheet = item as Excel.Worksheet; ReportDataTable table = this.GetDataTable(sheet, headerRowNumber); dataSet.Tables.Add(table.Name, table); } tempBook.Save(); tempBook.Close(); tempBook = null; excel.Application.Quit(); } catch (Exception ex) { excel.Application.Quit(); } return(dataSet); }
private ReportDataTable GetDataTable(Excel.Worksheet sheet, int headerRowNumber) { int columnCount = 0; int rowCount = 0; //calculate the column number while (true) { string columnName = ExcelColumnIndexToName(columnCount + 1); var range = sheet.get_Range(columnName + headerRowNumber); if ((range != null && !string.IsNullOrEmpty(((string)range.Value2))) || columnCount < 3) { columnCount++; } else { break; } } //calculate the row number while (true) { var range = sheet.get_Range("A" + (headerRowNumber + rowCount)); if (range != null && !string.IsNullOrEmpty(((string)range.Value2))) { rowCount++; } else { break; } } string[,] data = new string[rowCount, columnCount]; for (int i = 0; i < rowCount; i++) { for (int j = 0; j < columnCount; j++) { string columnName = ExcelColumnIndexToName(j + 1); var range = sheet.get_Range(columnName + (headerRowNumber + i)); if (i == 0 && columnName == "B" && (range == null || range.Value2 == null)) { data[i, j] = "FormulaColumn"; } else { data[i, j] = (range == null || range.Value2 == null) ? string.Empty : range.Value2.ToString(); } } } ReportDataTable dataset = new ReportDataTable(data, sheet.Name); return(dataset); }
private ReportDataTable GetDataTable(Excel.Worksheet sheet, int headerRowNumber) { int columnCount = 0; int rowCount = 0; //calculate the column number while (true) { string columnName = ExcelColumnIndexToName(columnCount + 1); var range = sheet.get_Range(columnName + headerRowNumber); if ((range != null && !string.IsNullOrEmpty(((string)range.Value2))) || columnCount < 3) { columnCount++; } else { break; } } //calculate the row number while (true) { var range = sheet.get_Range("A" + (headerRowNumber + rowCount)); if (range != null && !string.IsNullOrEmpty(((string)range.Value2))) { rowCount++; } else { break; } } string[,] data = new string[rowCount, columnCount]; for (int i = 0; i < rowCount; i++) { for (int j = 0; j < columnCount; j++) { string columnName = ExcelColumnIndexToName(j + 1); var range = sheet.get_Range(columnName + (headerRowNumber + i)); if (i == 0 && columnName == "B" && (range == null || range.Value2 == null)) { data[i, j] = "FormulaColumn"; } else { data[i, j] = (range == null || range.Value2 == null) ? string.Empty : range.Value2.ToString(); } } } ReportDataTable dataset = new ReportDataTable(data,sheet.Name); return dataset; }
private void ExecuteVerification(ReportVerificationModel reportVerificationModel) { reportVerificationModel.IsIdentity = true; foreach (var item in reportVerificationModel.DestinationDataSet.Tables.Keys) { ReportDataTable tableDes = reportVerificationModel.DestinationDataSet.Tables[item]; string[,] resultData = new string[tableDes.Rows.Count, tableDes.Columns.Count]; StringBuilder sbMsg = new StringBuilder(); if (reportVerificationModel.SourceDataSet.Tables.ContainsKey(item)) { ReportDataTable tableSource = reportVerificationModel.SourceDataSet.Tables[item]; int countNew = 0; int countN = 0; for (int i = 0; i < tableDes.Rows.Count; i++) { for (int j = 0; j < tableDes.Columns.Count; j++) { string rowName = tableDes.Rows[i]; string columnName = tableDes.Columns[j]; if (j == 0 || i == 0) { resultData[i, j] = tableDes[rowName, columnName]; } else { string valueDes = tableDes[rowName, columnName]; string valueSource = tableSource[rowName, columnName]; if (valueSource == null) { reportVerificationModel.IsIdentity = false; resultData[i, j] = "New " + valueDes; countNew++; } else { if (valueDes == valueSource) { resultData[i, j] = "Y"; } else { reportVerificationModel.IsIdentity = false; resultData[i, j] = string.Format("N({0}/{1})", valueDes, valueSource); countN++; } } } } } if (countN > 0) { sbMsg.Append(countN + " cells are not identity."); } else if (countNew > 0) { sbMsg.Append(countNew + " cells are new."); } else { sbMsg.Append("All cells are identity."); } } else { reportVerificationModel.IsIdentity = false; for (int i = 0; i < tableDes.Rows.Count; i++) { for (int j = 0; j < tableDes.Columns.Count; j++) { string rowName = tableDes.Rows[i]; string columnName = tableDes.Columns[j]; string valueDes = tableDes[rowName, columnName]; if (j == 0 || i == 0) { resultData[i, j] = tableDes[rowName, columnName]; } else { resultData[i, j] = "New"; } } } sbMsg.Append("The sheet is new, it it not existing on the source file."); } reportVerificationModel.VerifiedResultMsg.Add(item, sbMsg.ToString()); reportVerificationModel.VerifiedResultSet.Tables.Add(item, new ReportDataTable(resultData, item)); } }