コード例 #1
0
        private void exportButton_Click(object sender, EventArgs e)
        {
            if (outputExcelData == null)
            {
                MessageBox.Show("\nPlease validate an excel file first before exporting", "No Excel Validated", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }

            string outputFileName = inputTextBox.Text.Split('\\').Last().Replace(".xlsx", " - VALIDATED.xlsx");
            string outputFilePath = outputTextBox.Text.EndsWith("\\") ? outputTextBox.Text + outputFileName : outputTextBox.Text + "\\" + outputFileName;

            if (String.IsNullOrEmpty(outputTextBox.Text) || !Directory.Exists(outputTextBox.Text))
            {
                MessageBox.Show("Output Directory does not exist.", "Directory Not Found", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }

            if (File.Exists(outputFilePath) && ExcelOperations.FileIsOpened(new FileInfo(outputFilePath)))
            {
                MessageBox.Show(outputFileName + "\nOutput excel file is currently in use. Please close it and try again.", "File In Use", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }

            statusLabel.Text      = "STATUS:  EXPORTING " + inputTextBox.Text.Split('\\').Last();
            statusLabel.ForeColor = System.Drawing.Color.Yellow;
            statusLabel.Refresh();

            CreateExcelFile.CreateExcelDocument(outputExcelData, outputFilePath);

            statusLabel.Text      = "STATUS:  SUCCESSFULLY EXPORTED " + inputTextBox.Text.Split('\\').Last();
            statusLabel.ForeColor = System.Drawing.Color.Green;
            statusLabel.Refresh();
        }
コード例 #2
0
        private void validateButton_Click(object sender, EventArgs e)
        {
            GC.Collect();

            if (!File.Exists(inputTextBox.Text))
            {
                MessageBox.Show("Input File does not exist.", "File Not Found", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }

            if (ExcelOperations.FileIsOpened(new FileInfo(inputTextBox.Text)))
            {
                MessageBox.Show(inputTextBox.Text + "\n\nInput Excel file is currently in use.\nPlease close it and try again.", "File In Use", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }

            statusLabel.Text      = "STATUS:  WORKING. PLEASE WAIT...";
            statusLabel.ForeColor = System.Drawing.Color.Yellow;
            statusLabel.Refresh();

            DataSet excelData;

            using (var c = ExcelOperations.GetConnection(inputTextBox.Text, true))
            {
                excelData = ExcelOperations.GetExcelFileAsOneDataSetTable(c);

                if (excelData == null)
                {
                    MessageBox.Show(inputTextBox.Text + "\n\nFailed to read excel file.", "Empty or Corrupted File", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                }

                outputExcelData = new DataSet(excelData.DataSetName + " VALIDATED");

                Dictionary <string, string> columnMapper   = new Dictionary <string, string>();
                HashSet <string>            requiredFields = new HashSet <string>();

                if (outputFormat.SelectedItem.ToString() == "VIA MEDICA" && inputFormat.SelectedItem.ToString() == "VIA MEDICA")
                {
                    columnMapper.Add("EMP No / UAEID", "EMP No / UAEID");
                    columnMapper.Add("Name", "Name");
                    columnMapper.Add("Date_Of_Birth", "Date_Of_Birth");
                    columnMapper.Add("Emirates_ID", "Emirates_ID");
                    columnMapper.Add("NATIONALITY", "NATIONALITY");
                    columnMapper.Add("Gender", "Gender");
                    columnMapper.Add("Contact", "Contact");
                    columnMapper.Add("Passport_Number", "Passport_Number");
                    columnMapper.Add("address", "address");
                    columnMapper.Add("Emirate", "Emirate");
                    columnMapper.Add("Referring_Facility_MRN", "Referring_Facility_MRN");
                    columnMapper.Add("Referring_Facility_ID", "Referring_Facility_ID");
                    columnMapper.Add("Referring_Facility_Name", "Referring_Facility_Name");
                    columnMapper.Add("Ordering_Physician", "Ordering_Physician");
                    columnMapper.Add("Hasana ID", "Hasana ID");

                    requiredFields.Add("Gender");
                    requiredFields.Add("Name");
                    requiredFields.Add("Contact");
                    requiredFields.Add("NATIONALITY");
                    requiredFields.Add("Date_Of_Birth");
                    requiredFields.Add("Referring_Facility_MRN");
                }
                else if (outputFormat.SelectedItem.ToString() == "LDM" && inputFormat.SelectedItem.ToString() == "VIA MEDICA")
                {
                    columnMapper.Add("PATIENT NAME", "Name");
                    columnMapper.Add("BIRTHDATE", "Date_Of_Birth");
                    columnMapper.Add("EMIRATES ID", "Emirates_ID");
                    columnMapper.Add("NATIONALITY", "NATIONALITY");
                    columnMapper.Add("GENDER", "Gender");
                    columnMapper.Add("Mobile Number", "Contact");
                    columnMapper.Add("Address", "address");
                    columnMapper.Add("MRN", "Referring_Facility_MRN");
                    columnMapper.Add("PASSPORT NUMBER", "Passport_Number");
                    columnMapper.Add("city", "Emirate");

                    requiredFields.Add("GENDER");
                    requiredFields.Add("PATIENT NAME");
                    requiredFields.Add("Mobile Number");
                    requiredFields.Add("NATIONALITY");
                    requiredFields.Add("BIRTHDATE");
                    requiredFields.Add("MRN");
                }


                //Finding the duplicate values in each of the unique fields AND finding the default autocompletions
                for (int i = 0; i < excelData.Tables.Count; i++)
                {
                    //check if all mapped columns exist in source excel file
                    foreach (KeyValuePair <string, string> column in columnMapper)
                    {
                        if (!excelData.Tables[i].Columns.Contains(column.Value.ToLower()))
                        {
                            statusLabel.Text      = "STATUS:  ERROR!";
                            statusLabel.ForeColor = System.Drawing.Color.Red;
                            MessageBox.Show("\"" + column.Value + "\" column should be present in the source excel sheet \"" + excelData.Tables[i].TableName + "\".\nPlease check the format and try again.", "Missing Column", MessageBoxButtons.OK, MessageBoxIcon.Error);
                            outputExcelData = null;
                            return;
                        }
                    }

                    //A Dictionary storing the data that should be autocompleted for each applicable column
                    Dictionary <string, string> defaultAutocompletion = new Dictionary <string, string>();

                    //A set for each field that can't be duplicated (stores the duplicates)
                    Dictionary <string, HashSet <string> > duplicates = new Dictionary <string, HashSet <string> >();

                    //A set that detects the duplicates when keys repeat (only detects duplicates, and sends to above sets)
                    Dictionary <string, HashSet <string> > duplicatesDetector = new Dictionary <string, HashSet <string> >();

                    //A set for each field that can't be combine duplicated (stores the duplicates)
                    Dictionary <string, Dictionary <string, int> > duplicatesCombined = new Dictionary <string, Dictionary <string, int> >();

                    //A set that detects the combined duplicates when keys repeat (only detects duplicates, and sends to above sets)
                    Dictionary <string, HashSet <string> > duplicatesDetectorCombined = new Dictionary <string, HashSet <string> >();

                    //a set of fields common between all formats and having specific checks/modification (unifying their names with a known Key)
                    Dictionary <string, string> fieldNames = new Dictionary <string, string>();

                    //a set containing the required length of some fields like EID
                    Dictionary <string, int> columnLengths = new Dictionary <string, int>();


                    outputExcelData.Tables.Add(excelData.Tables[i].TableName);

                    //Setting up output format for sheet + default autocompletion keys + duplicate detectors
                    switch (outputFormat.SelectedItem.ToString())
                    {
                    case "VIA MEDICA":
                        outputExcelData.Tables[i].Columns.Add("EMP No / UAEID");
                        outputExcelData.Tables[i].Columns.Add("Name");
                        outputExcelData.Tables[i].Columns.Add("Date_Of_Birth");
                        outputExcelData.Tables[i].Columns.Add("Emirates_ID");
                        outputExcelData.Tables[i].Columns.Add("NATIONALITY");
                        outputExcelData.Tables[i].Columns.Add("Gender");
                        outputExcelData.Tables[i].Columns.Add("Contact");
                        outputExcelData.Tables[i].Columns.Add("Passport_Number");
                        outputExcelData.Tables[i].Columns.Add("address");
                        outputExcelData.Tables[i].Columns.Add("Emirate");
                        outputExcelData.Tables[i].Columns.Add("Referring_Facility_MRN");
                        outputExcelData.Tables[i].Columns.Add("Referring_Facility_ID");
                        outputExcelData.Tables[i].Columns.Add("Referring_Facility_Name");
                        outputExcelData.Tables[i].Columns.Add("Ordering_Physician");
                        outputExcelData.Tables[i].Columns.Add("Hasana ID");
                        outputExcelData.Tables[i].Columns.Add("Notes");

                        fieldNames.Add("passport_number", "Passport_Number");
                        fieldNames.Add("EID", "Emirates_ID");
                        fieldNames.Add("gender", "Gender");
                        fieldNames.Add("MRN", "Referring_Facility_MRN");
                        fieldNames.Add("mobile_number", "Contact");
                        fieldNames.Add("birthdate", "Date_Of_Birth");

                        defaultAutocompletion.Add("Emirate", "");

                        duplicates.Add("Passport_Number", new HashSet <string>());
                        duplicates.Add("Emirates_ID", new HashSet <string>());
                        duplicates.Add("Referring_Facility_MRN", new HashSet <string>());

                        duplicatesCombined.Add("Emirates_ID|Referring_Facility_MRN", new Dictionary <string, int>());


                        duplicatesDetector.Add("Passport_Number", new HashSet <string>());
                        duplicatesDetector.Add("Emirates_ID", new HashSet <string>());
                        duplicatesDetector.Add("Referring_Facility_MRN", new HashSet <string>());

                        duplicatesDetectorCombined.Add("Emirates_ID|Referring_Facility_MRN", new HashSet <string>());

                        break;

                    case "LDM":
                        outputExcelData.Tables[i].Columns.Add("SN");
                        outputExcelData.Tables[i].Columns.Add("SAMPLE ID");
                        outputExcelData.Tables[i].Columns.Add("RACK NO.");
                        outputExcelData.Tables[i].Columns.Add("EMIRATES ID");
                        outputExcelData.Tables[i].Columns.Add("MRN");
                        outputExcelData.Tables[i].Columns.Add("PATIENT NAME");
                        outputExcelData.Tables[i].Columns.Add("BIRTHDATE");
                        outputExcelData.Tables[i].Columns.Add("GENDER");
                        outputExcelData.Tables[i].Columns.Add("COLLECTION DATE AND  TIME");
                        outputExcelData.Tables[i].Columns.Add("PASSPORT NUMBER");
                        outputExcelData.Tables[i].Columns.Add("NATIONALITY");
                        outputExcelData.Tables[i].Columns.Add("HASANA ID");
                        outputExcelData.Tables[i].Columns.Add("AI (Additional Identifier)");
                        outputExcelData.Tables[i].Columns.Add("Mobile Number");
                        outputExcelData.Tables[i].Columns.Add("email");
                        outputExcelData.Tables[i].Columns.Add("city");
                        outputExcelData.Tables[i].Columns.Add("Address");
                        outputExcelData.Tables[i].Columns.Add("Notes");

                        fieldNames.Add("passport_number", "PASSPORT NUMBER");
                        fieldNames.Add("EID", "EMIRATES ID");
                        fieldNames.Add("gender", "GENDER");
                        fieldNames.Add("MRN", "MRN");
                        fieldNames.Add("mobile_number", "Mobile Number");
                        fieldNames.Add("birthdate", "BIRTHDATE");

                        defaultAutocompletion.Add("city", "");

                        duplicates.Add("PASSPORT NUMBER", new HashSet <string>());
                        duplicates.Add("EMIRATES ID", new HashSet <string>());
                        duplicates.Add("MRN", new HashSet <string>());

                        duplicatesCombined.Add("EMIRATES ID|MRN", new Dictionary <string, int>());

                        duplicatesDetector.Add("PASSPORT NUMBER", new HashSet <string>());
                        duplicatesDetector.Add("EMIRATES ID", new HashSet <string>());
                        duplicatesDetector.Add("MRN", new HashSet <string>());

                        duplicatesDetectorCombined.Add("EMIRATES ID|MRN", new HashSet <string>());

                        break;
                    }

                    //conditions shared between all formats
                    //govern the length of some fields like EID
                    columnLengths.Add("EID", 15);
                    columnLengths.Add("mobile_number", 16);

                    for (int j = 0; j < excelData.Tables[i].Rows.Count; j++)
                    {
                        if (excelData.Tables[i].Rows[j].ItemArray.All(v => v.ToString() == ""))
                        {
                            excelData.Tables[i].Rows[j].Delete();
                            continue;
                        }

                        foreach (var detector in duplicatesDetector)
                        {
                            string cellData = excelData.Tables[i].Rows[j][columnMapper[detector.Key]].ToString();

                            if (duplicatesDetector[detector.Key].Contains(cellData))
                            {
                                duplicates[detector.Key].Add(cellData);
                            }
                            else
                            {
                                duplicatesDetector[detector.Key].Add(cellData);
                            }
                        }

                        foreach (var detector in duplicatesDetectorCombined)
                        {
                            string[] columns  = detector.Key.Split('|');
                            string   cellData = "";

                            for (int k = 0; k < columns.Length; k++)
                            {
                                cellData += excelData.Tables[i].Rows[j][columnMapper[columns[k]]].ToString();
                            }

                            if (duplicatesDetectorCombined[detector.Key].Contains(cellData))
                            {
                                if (duplicatesCombined[detector.Key].ContainsKey(cellData))
                                {
                                    duplicatesCombined[detector.Key][cellData]++;
                                }
                                else
                                {
                                    duplicatesCombined[detector.Key].Add(cellData, 2);
                                }
                            }
                            else
                            {
                                duplicatesDetectorCombined[detector.Key].Add(cellData);
                            }
                        }

                        for (int k = 0; k < defaultAutocompletion.Count; k++)
                        {
                            string cellData = excelData.Tables[i].Rows[j][columnMapper[defaultAutocompletion.ElementAt(k).Key]].ToString();

                            if (defaultAutocompletion.ContainsKey(defaultAutocompletion.ElementAt(k).Key) && defaultAutocompletion[defaultAutocompletion.ElementAt(k).Key] == "" && cellData != "")
                            {
                                defaultAutocompletion[defaultAutocompletion.ElementAt(k).Key] = cellData;
                            }
                        }
                    }

                    excelData.AcceptChanges();

                    for (int j = 0; j < excelData.Tables[i].Rows.Count; j++)
                    {
                        bool hadDeletedDuplicates = false;

                        string combinedName  = fieldNames["EID"] + "|" + fieldNames["MRN"];
                        string combinedValue = excelData.Tables[i].Rows[j][columnMapper[fieldNames["EID"]]].ToString() + excelData.Tables[i].Rows[j][columnMapper[fieldNames["MRN"]]].ToString();

                        //Before adding the output row, check first if both EID and Ref Number are duplicated
                        if (duplicatesCombined[combinedName].ContainsKey(combinedValue))
                        {
                            if (duplicatesCombined[combinedName][combinedValue] > 1)
                            {
                                duplicatesCombined[combinedName][combinedValue]--;
                                excelData.Tables[i].Rows[j].Delete();
                                excelData.AcceptChanges();
                                j--;
                                continue;
                            }
                            else
                            {
                                hadDeletedDuplicates = true;
                            }
                        }

                        outputExcelData.Tables[i].Rows.Add();

                        //if hadDeletedDuplicates is true, add a note stating so
                        if (hadDeletedDuplicates)
                        {
                            outputExcelData.Tables[i].Rows[j]["Notes"] = "Other row(s) with the same EID & MRN were deleted; ";
                        }

                        //check if that at least NID or Passport No. is available (use input column name here ONLY)
                        if (String.IsNullOrEmpty(excelData.Tables[i].Rows[j][columnMapper[fieldNames["EID"]]].ToString()) && String.IsNullOrEmpty(excelData.Tables[i].Rows[j][columnMapper[fieldNames["passport_number"]]].ToString()))
                        {
                            outputExcelData.Tables[i].Rows[j]["Notes"] = outputExcelData.Tables[i].Rows[j]["Notes"].ToString() + "At least EID or Passport No. is required and neither is entered; ";
                        }

                        for (int k = 0; k < outputExcelData.Tables[i].Columns.Count - 1; k++)
                        {
                            string outputColumnName = outputExcelData.Tables[i].Columns[k].ColumnName;

                            string cellData;

                            //skip with a blank if column only exists in output excel format and is not SN (which is autogenerated)
                            if (!columnMapper.ContainsKey(outputColumnName))
                            {
                                cellData = outputColumnName == "SN" ? (j + 1).ToString() : "";
                                outputExcelData.Tables[i].Rows[j][outputColumnName] = cellData;
                                continue;
                            }

                            cellData = excelData.Tables[i].Rows[j][columnMapper[outputColumnName]].ToString();

                            //check if column is required but left blank
                            if (String.IsNullOrEmpty(cellData) && requiredFields.Contains(outputColumnName))
                            {
                                outputExcelData.Tables[i].Rows[j]["Notes"]          = outputExcelData.Tables[i].Rows[j]["Notes"].ToString() + columnMapper[outputColumnName] + " is required but not entered; ";
                                outputExcelData.Tables[i].Rows[j][outputColumnName] = cellData;
                                continue;
                            }

                            //check if EID is not in correct length
                            if (outputColumnName == fieldNames["EID"] && !String.IsNullOrEmpty(cellData) && cellData.Replace("-", "").Length != columnLengths["EID"])
                            {
                                outputExcelData.Tables[i].Rows[j]["Notes"] = outputExcelData.Tables[i].Rows[j]["Notes"].ToString() + outputColumnName + " is not in correct length; ";
                            }

                            //check if mobile number is not in correct length
                            if (outputColumnName == fieldNames["mobile_number"] && !String.IsNullOrEmpty(cellData) && cellData.Length != columnLengths["mobile_number"])
                            {
                                outputExcelData.Tables[i].Rows[j]["Notes"] = outputExcelData.Tables[i].Rows[j]["Notes"].ToString() + outputColumnName + " is not in correct length; ";
                            }

                            //Check for duplicates in the fields marked before to be unique
                            if (cellData != "" && duplicates.ContainsKey(outputColumnName) && duplicates[outputColumnName].Contains(cellData))
                            {
                                outputExcelData.Tables[i].Rows[j]["Notes"] = outputExcelData.Tables[i].Rows[j]["Notes"].ToString() + columnMapper[outputColumnName] + " is duplicated; ";
                            }

                            //If column is marked to be autocompleted, use the default autocompletion found in first run
                            cellData = defaultAutocompletion.ContainsKey(outputColumnName) && String.IsNullOrEmpty(cellData) ? defaultAutocompletion[outputColumnName] : cellData;

                            //operations that are specific to some formats
                            if (outputFormat.SelectedItem.ToString() == "LDM")
                            {
                                //Only use initials for gender
                                cellData = outputColumnName == fieldNames["gender"] ? cellData.Substring(0, 1) : cellData;

                                //Remove dashes from EID
                                cellData = outputColumnName == fieldNames["EID"] ? cellData.Replace("-", "") : cellData;

                                //Remove country code from mobile number
                                cellData = outputColumnName == fieldNames["mobile_number"] ? cellData.Substring(6).Replace("-", "") : cellData;
                            }

                            outputExcelData.Tables[i].Rows[j][outputColumnName] = cellData;
                        }
                    }

                    outputExcelData.AcceptChanges();
                }
            }
            statusLabel.Text      = "STATUS:   SUCESSFULLY LOADED " + inputTextBox.Text.Split('\\').Last();
            statusLabel.ForeColor = System.Drawing.Color.GreenYellow;
            statusLabel.Refresh();
        }