public ExcelReader(string fileToOpen, bool header, bool imex)
{
if (string.IsNullOrEmpty(fileToOpen))
{
throw new ArgumentNullException("filename", "filename cannot be null or empty");
}
FileInfo info = new FileInfo(fileToOpen);
if (!info.Exists)
{
throw new FileNotFoundException("fileName must exist", fileToOpen);
}
if (!info.Extension.EndsWith(".xls", StringComparison.OrdinalIgnoreCase) && !info.Extension.EndsWith(".xlsx"))
{
throw new InvalidOperationException("The excel file must be an xls or xlsx");
}
if (info.Extension.EndsWith(".xls"))
{
this.excelType = WorkbookType.XLS;
}
else
{
this.excelType = WorkbookType.XLSX;
}
this.fileName = fileToOpen;
this.header = header;
this.imex = imex;
}
public ExcelReader(string fileToOpen, bool header, bool imex)
{
if (string.IsNullOrEmpty(fileToOpen)) throw new ArgumentNullException("filename", "filename cannot be null or empty");
FileInfo info = new FileInfo(fileToOpen);
if (!info.Exists) throw new FileNotFoundException("fileName must exist", fileToOpen);
if (!info.Extension.EndsWith(".xls", StringComparison.OrdinalIgnoreCase) && !info.Extension.EndsWith(".xlsx")) throw new InvalidOperationException("The excel file must be an xls or xlsx");
if (info.Extension.EndsWith(".xls")) this.excelType = WorkbookType.XLS;
else this.excelType = WorkbookType.XLSX;
this.fileName = fileToOpen;
this.header = header;
this.imex = imex;
}
private string GetCellValueFromSheet(string filePath, WorkbookType sheetName, string cellReference)
{
using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
var workbook = WorkbookFactory.Create(file);
var sheet = workbook.GetSheet(sheetName.ToString());
var cellRef = new CellReference(cellReference);
return(GetCellValueAsString(sheet.GetRow(cellRef.Row).GetCell(cellRef.Col)));
}
}
private void ProcessSingleSheet(string directoryPath, ISheet targetSheet, WorkbookType workbookType)
{
var filePath = Path.Combine(directoryPath, workbooksInfo[workbookType].FileName);
targetSheet.ForceFormulaRecalculation = false;
using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
var wb = WorkbookFactory.Create(file);
var sourceSheet = wb.GetSheetAt(0);
//get data from source sheet
var sourceRange = CellRangeAddress.ValueOf(sourceWorksheetsRanges[workbookType]);
var sourceValues = Enumerable.Range(sourceRange.FirstRow, sourceRange.LastRow - sourceRange.FirstRow + 1)
.Select(i => new
{
Aop = sourceSheet.GetRow(i).GetCell(sourceRange.FirstColumn)?.StringCellValue,
Value = GetCellValueAsString(sourceSheet.GetRow(i).GetCell(sourceRange.LastColumn))
})
.Where(m => !string.IsNullOrEmpty(m.Aop))
.Where(m => int.TryParse(m.Aop, out int _))
.ToDictionary(c => c.Aop, c => c.Value);
//set data on target sheet (of final GFI)
var targetRange = CellRangeAddress.ValueOf(workbooksInfo[workbookType].Range);
for (int rowIndex = targetRange.FirstRow; rowIndex <= targetRange.LastRow; rowIndex++)
{
if (targetSheet.GetRow(rowIndex).GetCell(targetRange.LastColumn).CellStyle.IsLocked)
{
continue;
}
var aopDouble = targetSheet.GetRow(rowIndex).GetCell(targetRange.FirstColumn).NumericCellValue;
var aop = Convert.ToInt32(aopDouble).ToString("D3");
var aopValue = sourceValues.TryGetValue(aop, out string value) ? value : null;
var newValue = string.IsNullOrEmpty(aopValue) ? 0 : Convert.ToInt32(aopValue);
targetSheet.GetRow(rowIndex).GetCell(targetRange.LastColumn).SetCellValue(newValue);
}
}
targetSheet.ForceFormulaRecalculation = true;
}
/// <remarks/>
public void GetWorkbookAsync(string sessionId, WorkbookType workbookType, object userState) {
if ((this.GetWorkbookOperationCompleted == null)) {
this.GetWorkbookOperationCompleted = new System.Threading.SendOrPostCallback(this.OnGetWorkbookOperationCompleted);
}
this.InvokeAsync("GetWorkbook", new object[] {
sessionId,
workbookType}, this.GetWorkbookOperationCompleted, userState);
}
/// <remarks/>
public void GetWorkbookAsync(string sessionId, WorkbookType workbookType) {
this.GetWorkbookAsync(sessionId, workbookType, null);
}
public byte[] GetWorkbook(string sessionId, WorkbookType workbookType, out Status[] status) {
object[] results = this.Invoke("GetWorkbook", new object[] {
sessionId,
workbookType});
status = ((Status[])(results[1]));
return ((byte[])(results[0]));
}
/// <summary>
/// It extracts blank DES workbook from resource file and saves it as specified file name.
/// </summary>
/// <param name="fileNameWpath">Desired file name of workbook.</param>
internal static void GetWorkbookFromResourceFile(WorkbookType workbookType, string fileNameWpath)
{
byte[] workbookBytes = null;
try
{
if (File.Exists(fileNameWpath))
{
File.Delete(fileNameWpath);
}
//Load the resource file
ResourceManager rm = new ResourceManager("DevInfo.Lib.DI_LibBAL.Export.ExportR", Assembly.GetExecutingAssembly());
switch (workbookType)
{
case WorkbookType.DataEntrySpreadsheet:
workbookBytes = (byte[])rm.GetObject("DataEntrySpreadsheet");
break;
case WorkbookType.IndicatorEntrySpreadsheet:
workbookBytes = (byte[])rm.GetObject("Indicator");
break;
case WorkbookType.AreaEntrySpreadsheet:
workbookBytes = (byte[])rm.GetObject("Area");
break;
case WorkbookType.General:
workbookBytes = (byte[])rm.GetObject("General");
break;
}
FileStream fileStream = File.Create(fileNameWpath);
fileStream.Write(workbookBytes, 0, workbookBytes.Length);
fileStream.Close();
}
catch (Exception ex)
{
//TODO: exception message?
ExceptionHandler.ExceptionFacade.ThrowException(ex);
}
}
private List <string> ProccessSingleSheet(List <string> targetCells, string filePath, WorkbookType sheetName)
{
using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
var workbook = WorkbookFactory.Create(file);
var sheet = workbook.GetSheet(sheetName.ToString());
return(targetCells
.Select(c => new CellReference(c))
.Select(c => GetCellValueAsString(sheet.GetRow(c.Row).GetCell(c.Col)))
.ToList());
//var noteStart = new CellReference(noteStartCell);
//var valueStart = new CellReference(valueStartCell);
//var noteColumnIndex = noteStart.Col;
//var valueColumnIndex = valueStart.Col;
//var startRow = noteStart.Row;
//var valuesList = new List<string>();
//HSSFFormulaEvaluator formula = new HSSFFormulaEvaluator(workbook);
//for (int i = 0; i < sheet.LastRowNum - startRow; i++)
//{
// var currentRow = sheet.GetRow(startRow + i);
// var currentCellValue = currentRow.GetCell(noteColumnIndex)?.StringCellValue;
// if (string.IsNullOrWhiteSpace(currentCellValue)) continue;
// var cell = currentRow.GetCell(valueColumnIndex);
// formula.EvaluateInCell(cell);
// var value = cell.NumericCellValue.ToString();
// valuesList.Add(value);
//}
//return valuesList;
}
}
private void ViewCode(WorkbookType type, string codes)
{
switch(type)
{
case WorkbookType.Source:
txtSourceCode.Text = codes;
rTxtSelectedModuleCode.Text = codes;
break;
case WorkbookType.Destination:
txtDestinationCode.Text = codes;
break;
}
}
private void DesableMenu(WorkbookType type)
{
switch (type)
{
case WorkbookType.Source:
ListeSourceCodeMenuItem.Enabled = false;
ExportSourceCodesMenuItem.Enabled = false;
closeSourceMenuItem.Enabled = false;
break;
case WorkbookType.Destination:
ListDestinationCodeMenuItem.Enabled = false;
ExportDestinationCodesMenuItem.Enabled = false;
closeDestinationMenuItem.Enabled = false;
break;
}
}
private void ClearViewCode(WorkbookType type)
{
switch (type)
{
case WorkbookType.Source:
txtSourceCode.Text = string.Empty;
rTxtSelectedModuleCode.Text = string.Empty;
tvSourceListCodes.Nodes.Clear();
tvVBASolution.Nodes.Clear();
break;
case WorkbookType.Destination:
txtDestinationCode.Text = string.Empty;
tvDestinationListCodes.Nodes.Clear();
break;
}
}
