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