Esempio n. 1
0
        public void AssignStringTest()
        {
            string testString = "Hello";

            DataValidations.AssignStringIfNotNull(out testString, "World!");
            Assert.AreEqual("World!", testString);
        }
Esempio n. 2
0
        public static bool ValidateGreaterThanZero(string aNumber, Label msgLabel, string errorMsg)
        {
            bool valid = DataValidations.IsNumberGreaterThanZero(aNumber);

            if (!valid)
            {
                ErrorMessage(msgLabel, errorMsg);
            }
            return(valid);
        }
        private void WriteWorksheet(WorkbookPart workbookPart, Sheets sheets, DotForm form)
        {
            //Add data to first sheet
            WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>();
            Worksheet     worksheet     = new Worksheet();

            worksheetPart.Worksheet = worksheet;
            Columns columns = new Columns();

            columns.Append(new Column {
                Min = 1, Max = 2, Width = 4, CustomWidth = true
            });
            columns.Append(new Column {
                Min = 3, Max = 7, Width = 12, CustomWidth = true
            });
            columns.Append(new Column {
                Min = 8, Max = 8, Width = 40, CustomWidth = true
            });
            columns.Append(new Column {
                Min = 9, Max = 9, Width = 20, CustomWidth = true
            });
            columns.Append(new Column {
                Min = 10, Max = 11, Width = 4, CustomWidth = true
            });

            var sheetData       = new SheetData();
            var dataValidations = new DataValidations();

            worksheet.Append(columns);
            worksheet.Append(sheetData);
            var mergeCells = CreateOrGetMergeCells(worksheet);

            worksheet.Append(dataValidations);

            Sheet sheet = new Sheet
            {
                Name    = form.EventTreeName,
                SheetId = (uint)sheets.Count() + 1,
                Id      = workbookPart.GetIdOfPart(worksheetPart),
            };

            sheets.Append(sheet);

            WriteHeader(sheetData, mergeCells);
            WriteEventHeader(form, sheetData, mergeCells);
            WriteExpertInformation(form, sheetData);
            WriteElicitationCodeInformation(sheetData, mergeCells);
            AddImage(form.GetFileStream, worksheetPart);
            var rowNumber = WriteNodes(form, sheetData, dataValidations);

            WriteSheetBottom(sheetData, rowNumber);


            worksheetPart.Worksheet.Save();
        }
Esempio n. 4
0
 /// <summary>
 /// Email address validation
 /// </summary>
 /// <returns></returns>
 private string EmailValidation()
 {
     if (string.IsNullOrEmpty(Email))
     {
         return("Email address field is empty");
     }
     else if (!DataValidations.IsValidEmailId(Email))
     {
         return("Please enter valid email address");
     }
     return(string.Empty);
 }
Esempio n. 5
0
        public static bool ValidateID(string anID, Label aMsgLabel)
        {
            bool valid = DataValidations.IsValidID(anID);

            if (valid)
            {
                OkMessage(aMsgLabel, "OK");
            }
            else
            {
                ErrorMessage(aMsgLabel, "Invalid ID number");
            }
            return(valid);
        }
Esempio n. 6
0
        public static bool ValidatePhoneNumber(string aPhoneNumber, Label aMsgLabel)
        {
            bool valid = DataValidations.IsValidPhoneNumber(aPhoneNumber);

            if (valid)
            {
                OkMessage(aMsgLabel, "OK");
            }
            else
            {
                ErrorMessage(aMsgLabel, "Invalid phone number");
            }
            return(valid);
        }
        public ISheetBuilder Decorate(ISheetBuilder target)
        {
            var originalFunc = target.BuildSheet;
            target.BuildSheet = (models, sheetDefinition, doc) =>
            {
                originalFunc(models, sheetDefinition, doc);

                var validations = AddDataValidations(sheetDefinition.ColumnDefinitions);

                var worksheet = doc.GetWorksheetByName(sheetDefinition.Name);
                var node = worksheet.GetFirstChild<DataValidations>();
                if (!validations.Any()){ return; }
                    
                if (node == null)
                {
                    node = new DataValidations();
                    worksheet.Append(node);
                }
                node.Append(validations);
            };
            return target;
        }
Esempio n. 8
0
        public void IsNumberTest()
        {
            string testNumber = "99";

            Assert.IsTrue(DataValidations.IsNumberGreaterThanZero(testNumber));
        }
Esempio n. 9
0
        public void IsValidNegativeNumberTest()
        {
            string testNumber = "-5";

            Assert.IsFalse(DataValidations.IsNumberGreaterThanZero(testNumber));
        }
Esempio n. 10
0
        public void ValidIDTest()
        {
            string testID = "4.972.519-2";

            Assert.IsTrue(DataValidations.IsValidID(testID));
        }
Esempio n. 11
0
        public void InvalidIDTest()
        {
            string testID = "1-555-5555";

            Assert.IsFalse(DataValidations.IsValidID(testID));
        }
Esempio n. 12
0
        public void ValidPhoneNumberTest()
        {
            string testPhone = "2400-38-85";

            Assert.IsTrue(DataValidations.IsValidPhoneNumber(testPhone));
        }
Esempio n. 13
0
        public void InvalidPhoneNumberTest()
        {
            string testPhone = "1-555-5555";

            Assert.IsFalse(DataValidations.IsValidPhoneNumber(testPhone));
        }
Esempio n. 14
0
        public void AssignNullStringTest()
        {
            string testString = "Hello";

            DataValidations.AssignStringIfNotNull(out testString, null);
        }
Esempio n. 15
0
        public void AssignEmptyStringTest()
        {
            string testString = "Hello";

            DataValidations.AssignStringIfNotNull(out testString, "");
        }
Esempio n. 16
0
 private DataValidations DataValidation(int column = 0, ImportDataDTO importDataDTO = null, DataValidations dataValidations = null)
 {
     if (string.IsNullOrEmpty(importDataDTO.NameAndCompendium) ||
         string.IsNullOrEmpty(importDataDTO.Date) ||
         importDataDTO.Order == 0 ||
         string.IsNullOrEmpty(importDataDTO.Symbol))
     {
         dataValidations.IsCorrect = false;
         if (string.IsNullOrEmpty(importDataDTO.NameAndCompendium))
         {
             AddDataValidations(column, importDataDTO, dataValidations, (int)Type.NameAndCompendium, importDataDTO.NameAndCompendium);
         }
         if (string.IsNullOrEmpty(importDataDTO.Date))
         {
             AddDataValidations(column, importDataDTO, dataValidations, (int)Type.Date, importDataDTO.Date);
         }
         if (importDataDTO.Order == 0)
         {
             AddDataValidations(column, importDataDTO, dataValidations, (int)Type.Order, importDataDTO.Order.ToString());
         }
         if (string.IsNullOrEmpty(importDataDTO.Symbol))
         {
             AddDataValidations(column, importDataDTO, dataValidations, (int)Type.Symbol, importDataDTO.Symbol);
         }
     }
     return(dataValidations);
 }
Esempio n. 17
0
        private void AddDataValidations(int column, ImportDataDTO importDataDTO = null, DataValidations dataValidations = null, int dataType = 0, string value = "")
        {
            var dataValidation = new DataValidationDTO();

            dataValidation.RowNumber = column;
            dataValidation.IsCorrect = false;
            dataValidation.DataType  = dataType;
            dataValidation.Value     = value;
            dataValidation.Message   = NOT_EMPTY;
            dataValidations.dataValidationDTOs.Add(dataValidation);
        }
Esempio n. 18
0
        public void IsNaNTest()
        {
            string testNumber = "0";

            Assert.IsFalse(DataValidations.IsNumberGreaterThanZero(testNumber));
        }
Esempio n. 19
0
        public static void UpdateCell(string docName, string text,
                                      uint rowIndex, string columnName)
        {
            //Worksheet worksheet1 = new Worksheet() { MCAttributes = new MarkupCompatibilityAttributes() { Ignorable = "x14ac" } };
            //worksheet1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
            //worksheet1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
            //worksheet1.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");

            //Worksheet worksheet2 = new Worksheet() { MCAttributes = new MarkupCompatibilityAttributes() { Ignorable = "x14ac" } };
            //worksheet2.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
            //worksheet2.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
            //worksheet2.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");

            Worksheet worksheet1 = new Worksheet();

            ExcelOperations ex = new ExcelOperations();

            // Open the document for editing.
            using (SpreadsheetDocument spreadSheet = SpreadsheetDocument.Open(docName, true))
            {
                WorksheetPart worksheetPart = GetWorksheetPartByName(spreadSheet, "DropDownContainingSheet");

                if (worksheetPart != null)
                {
                    worksheet1 = worksheetPart.Worksheet;
                    SheetData sheetData  = new SheetData();
                    SheetData sheetData1 = new SheetData();
                    int       Counter1   = 1;

                    foreach (var value in DataInSheet.GetDataOfSheet1())
                    {
                        Row contentRow = ExcelOperations.CreateRowValues(Counter1, value);
                        Counter1++;
                        sheetData.AppendChild(contentRow);
                    }

                    worksheet1.Append(sheetData);
                    int Counter2 = 1;
                    //foreach (var value in DataInSheet.GetDataOfSheet2())
                    //{

                    //    Row contentRow = ExcelOprations.CreateRowValues(Counter2, value);
                    //    Counter2++;
                    //    sheetData1.AppendChild(contentRow);
                    //}
                    //worksheet2.Append(sheetData1);


                    DataValidation dataValidation = new DataValidation
                    {
                        Type                 = DataValidationValues.List,
                        AllowBlank           = true,
                        SequenceOfReferences = new ListValue <StringValue>()
                        {
                            InnerText = "A1"
                        },
                        Formula1 = new Formula1("'DropDownDataContainingSheet'!$B$1:$B$5")
                    };

                    DataValidations dataValidations = worksheet1.GetFirstChild <DataValidations>();
                    if (dataValidations != null)
                    {
                        dataValidations.Count = dataValidations.Count + 1;
                        dataValidations.Append(dataValidation);
                    }
                    else
                    {
                        DataValidations newdataValidations = new DataValidations();
                        newdataValidations.Append(dataValidation);
                        newdataValidations.Count = 1;
                        worksheet1.Append(newdataValidations);
                    }



                    Cell cell = GetCell(worksheetPart.Worksheet,
                                        columnName, rowIndex);

                    cell.CellValue = new CellValue(text);
                    cell.DataType  =
                        new EnumValue <CellValues>(CellValues.Number);

                    // Save the worksheet.
                    worksheetPart.Worksheet.Append(worksheet1);
                    // worksheetPart.Worksheet=(worksheet2);

                    worksheetPart.Worksheet.Save();
                }
            }
        }
Esempio n. 20
0
        public static void CreatingExcelAndDrowownInExcel()
        {
            var filepath          = @"C:\Test.xlsx";
            OpenXMLWindowsApp app = new OpenXMLWindowsApp();
            //app.UpdateSheet(filepath);
            SpreadsheetDocument myWorkbook = SpreadsheetDocument.Create(filepath, SpreadsheetDocumentType.Workbook);
            //SpreadsheetDocument myWorkbook = SpreadsheetDocument.Open(filepath,true);
            WorkbookPart workbookpart = myWorkbook.AddWorkbookPart();

            workbookpart.Workbook = new Workbook();
            WorksheetPart worksheetPart = workbookpart.AddNewPart <WorksheetPart>();

            worksheetPart.Worksheet = new Worksheet(new SheetData());

            WorksheetPart worksheetPart2 = workbookpart.AddNewPart <WorksheetPart>();

            worksheetPart2.Worksheet = new Worksheet(new SheetData());

            Sheets    sheets     = myWorkbook.WorkbookPart.Workbook.AppendChild <Sheets>(new Sheets());
            Worksheet worksheet1 = new Worksheet()
            {
                MCAttributes = new MarkupCompatibilityAttributes()
                {
                    Ignorable = "x14ac"
                }
            };

            worksheet1.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
            worksheet1.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
            worksheet1.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");

            Worksheet worksheet2 = new Worksheet()
            {
                MCAttributes = new MarkupCompatibilityAttributes()
                {
                    Ignorable = "x14ac"
                }
            };

            worksheet2.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
            worksheet2.AddNamespaceDeclaration("mc", "http://schemas.openxmlformats.org/markup-compatibility/2006");
            worksheet2.AddNamespaceDeclaration("x14ac", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/ac");

            SheetDimension sheetDimension1 = new SheetDimension()
            {
                Reference = "A1"
            };
            Sheet sheet = new Sheet()
            {
                Id      = myWorkbook.WorkbookPart.GetIdOfPart(worksheetPart),
                SheetId = 1,
                Name    = "DropDownContainingSheet"
            };

            Sheet sheet1 = new Sheet()
            {
                Id      = myWorkbook.WorkbookPart.GetIdOfPart(worksheetPart2),
                SheetId = 2,
                Name    = "DropDownDataContainingSheet"
            };

            sheets.Append(sheet);
            sheets.Append(sheet1);
            SheetData sheetData  = new SheetData();
            SheetData sheetData1 = new SheetData();
            int       Counter1   = 1;

            foreach (var value in DataInSheet.GetDataOfSheet1())
            {
                Row contentRow = CreateRowValues(Counter1, value);
                Counter1++;
                sheetData.AppendChild(contentRow);
            }

            worksheet1.Append(sheetData);
            int Counter2 = 1;

            foreach (var value in DataInSheet.GetDataOfSheet2())
            {
                Row contentRow = CreateRowValues(Counter2, value);
                Counter2++;
                sheetData1.AppendChild(contentRow);
            }
            worksheet2.Append(sheetData1);


            DataValidation dataValidation = new DataValidation
            {
                Type                 = DataValidationValues.List,
                AllowBlank           = true,
                SequenceOfReferences = new ListValue <StringValue>()
                {
                    InnerText = "A1"
                },
                Formula1 = new Formula1("'DropDownDataContainingSheet'!$A$1:$A$8")
            };

            DataValidations dataValidations = worksheet1.GetFirstChild <DataValidations>();

            if (dataValidations != null)
            {
                dataValidations.Count = dataValidations.Count + 1;
                dataValidations.Append(dataValidation);
            }
            else
            {
                DataValidations newdataValidations = new DataValidations();
                newdataValidations.Append(dataValidation);
                newdataValidations.Count = 1;
                worksheet1.Append(newdataValidations);
            }


            worksheetPart.Worksheet  = worksheet1;;
            worksheetPart2.Worksheet = worksheet2;
            workbookpart.Workbook.Save();
            myWorkbook.Close();
        }
Esempio n. 21
0
        public async Task <IActionResult> ValidateBulkInsert()
        {
            IFormCollection form;

            form = await Request.ReadFormAsync();

            object          obj  = Request.Form["validData"]; // object
            ValidaImportDto data = Libs.DeserializeObject <ValidaImportDto>(obj.ToString());
            ReturnResult <ValidaImportDto> result = new ReturnResult <ValidaImportDto>();
            IFormFile file = Request.Form.Files.FirstOrDefault();

            result.Item.ImportDataDTOs = new List <ImportDataDTO>();
            string directoryPathFileUpload = Path.Combine(Const.FILE_UPLOAD_DIR, Const.FILE_IMPORT);

            if (!_fileService.FileExist(directoryPathFileUpload))
            {
                Directory.CreateDirectory(directoryPathFileUpload);
                _fileService.Delete(directoryPathFileUpload, file.FileName);
            }
            var dataValidations = new DataValidations();

            dataValidations.IsCorrect          = true;
            dataValidations.dataValidationDTOs = new List <DataValidationDTO>();
            var fileExtension = file.FileName.Split('.');

            if (fileExtension.Length != 0)
            {
                if (fileExtension[1].Equals("xls") || fileExtension[1].Equals("xlsx") || fileExtension[1].Equals("csv"))
                {
                    string path = Path.Combine(directoryPathFileUpload, file.FileName);
                    FilesUtillities.CopyFileToPhysicalDiskSync(file, path);
                    Encoding.RegisterProvider(CodePagesEncodingProvider.Instance);
                    using (var stream = new FileStream(path, FileMode.Open, FileAccess.ReadWrite, FileShare.ReadWrite))
                    {
                        using (var reader = ExcelReaderFactory.CreateReader(stream))
                        {
                            int column = 1;
                            do
                            {
                                while (reader.Read())
                                {
                                    if (column == 1 || column == 2)
                                    {
                                        UpdateDataCustom(reader, column, result.Item);
                                    }
                                    else if (column == 4 || column == 5 || column == 6)
                                    {
                                        result.Item.ProfileTitle = ReadDataProfileTitle(reader, column);
                                    }
                                    else if (column == 3)
                                    {
                                        column++;
                                        continue;
                                    }
                                    else if (column != reader.RowCount)
                                    {
                                        var importData = GetImportDataDTO(reader, column);
                                        if (importData != null)
                                        {
                                            DataValidation(column, importData, dataValidations);
                                            result.Item.ImportDataDTOs.Add(importData);
                                        }
                                    }
                                    column++;
                                }
                            } while (reader.NextResult());
                        }
                    }
                    _fileService.Delete(directoryPathFileUpload, file.FileName);
                }
            }
            return(Ok(dataValidations));
        }
        private uint WriteNodes(DotForm form, SheetData sheetData, DataValidations dataValidations)
        {
            uint rowNumber = 21;

            // Write table parts
            foreach (DotNode node in form.Nodes)
            {
                var estimates = node.Estimates.OrderBy(n => n.WaterLevel).ToArray();

                AddRow(sheetData, StyleSheetLibrary.DefaultStyleIndex, rowNumber++);
                // TODO: merge all cells above table
                AddRow(sheetData, StyleSheetLibrary.DefaultStyleIndex, rowNumber++,
                       ConstructCell(node.NodeName, CellValues.String, StyleSheetLibrary.TableHeaderStyleIndex));
                AddRow(sheetData, StyleSheetLibrary.DefaultStyleIndex, rowNumber++,
                       ConstructCell("Waterstand", CellValues.String, StyleSheetLibrary.TableHeaderStyleIndex),
                       ConstructCell("Frequentie", CellValues.String, StyleSheetLibrary.TableHeaderStyleIndex),
                       ConstructCell("Onder", CellValues.String, StyleSheetLibrary.TableHeaderStyleIndex),
                       ConstructCell("Gemiddeld", CellValues.String, StyleSheetLibrary.TableHeaderStyleIndex),
                       ConstructCell("Boven", CellValues.String, StyleSheetLibrary.TableHeaderStyleIndex),
                       ConstructCell("Weergave", CellValues.String, StyleSheetLibrary.TableHeaderStyleIndex),
                       ConstructCell("Toelichting", CellValues.String, StyleSheetLibrary.TableHeaderStyleIndex));

                var styleIndex = StyleSheetLibrary.TableBodyStyleNormalIndex;
                foreach (var estimate in estimates)
                {
                    AddRow(sheetData, StyleSheetLibrary.DefaultStyleIndex, rowNumber++,
                           ConstructCell(estimate.WaterLevel, CellValues.Number, styleIndex),
                           ConstructCell(estimate.Frequency, CellValues.Number, styleIndex),
                           ConstructCell(estimate.LowerEstimate == 0 ? double.NaN : estimate.LowerEstimate, CellValues.Number,
                                         styleIndex),
                           ConstructCell(estimate.BestEstimate == 0 ? double.NaN : estimate.BestEstimate, CellValues.Number,
                                         styleIndex),
                           ConstructCell(estimate.UpperEstimate == 0 ? double.NaN : estimate.UpperEstimate, CellValues.Number,
                                         styleIndex),
                           ConstructCell(double.NaN, CellValues.Number, styleIndex),
                           ConstructCell("", CellValues.String, styleIndex));

                    dataValidations.Append(new DataValidation
                    {
                        AllowBlank           = true,
                        Type                 = DataValidationValues.List,
                        Formula1             = new Formula1(elicitationCodeCellRange),
                        SequenceOfReferences = new ListValue <StringValue>
                        {
                            InnerText = "E" + (rowNumber - 1)
                        }
                    });
                    dataValidations.Append(new DataValidation
                    {
                        AllowBlank           = true,
                        Type                 = DataValidationValues.List,
                        Formula1             = new Formula1(elicitationCodeCellRange),
                        SequenceOfReferences = new ListValue <StringValue>
                        {
                            InnerText = "F" + (rowNumber - 1)
                        }
                    });
                    dataValidations.Append(new DataValidation
                    {
                        AllowBlank           = true,
                        Type                 = DataValidationValues.List,
                        Formula1             = new Formula1(elicitationCodeCellRange),
                        SequenceOfReferences = new ListValue <StringValue>
                        {
                            InnerText = "G" + (rowNumber - 1)
                        }
                    });

                    styleIndex = styleIndex == StyleSheetLibrary.TableBodyStyleNormalIndex
                        ? StyleSheetLibrary.TableAlternateBodyStyleIndex
                        : StyleSheetLibrary.TableBodyStyleNormalIndex;
                }
            }

            return(rowNumber);
        }
Esempio n. 23
0
        public byte[] ExcelTemplate(List <Columns_Excel> columns)
        {
            var stream   = new MemoryStream();
            var document = SpreadsheetDocument.Create(stream, SpreadsheetDocumentType.Workbook);

            var workbookpart = document.AddWorkbookPart();

            workbookpart.Workbook = new Workbook();

            var worksheetPart = workbookpart.AddNewPart <WorksheetPart>();


            var sheetData = new SheetData();

            worksheetPart.Worksheet = new Worksheet();

            //----------------------custom width cols-----------------------------------
            Columns     cols = new Columns();
            UInt32Value i    = 1;

            foreach (var header in columns)
            {
                cols.Append(new Column()
                {
                    Min = i, Max = i, Width = header.Width, CustomWidth = true
                });
                i++;
            }
            worksheetPart.Worksheet.Append(cols);
            worksheetPart.Worksheet.Append(sheetData);

            var sheets = document.WorkbookPart.Workbook.
                         AppendChild <Sheets>(new Sheets());

            var sheet = new Sheet()
            {
                Id      = document.WorkbookPart.GetIdOfPart(worksheetPart),
                SheetId = 1,
                Name    = "Template"
            };

            sheets.AppendChild(sheet);


            var stylesPart = workbookpart.AddNewPart <WorkbookStylesPart>();

            stylesPart.Stylesheet = CreateStylesheet(); //new Stylesheet();



            // Add header
            UInt32 rowIdex = 0;
            var    row     = new Row {
                RowIndex = ++rowIdex
            };

            sheetData.AppendChild(row);
            var cellIdex           = 0;
            var dropdownIdex_start = columns.Count + 40;



            //----------------------create text for headers-----------------------------------
            foreach (var header in columns)
            {
                row.AppendChild(CreateTextCell(ColumnLetter(cellIdex++), rowIdex, header.Colum_name));
            }

            int drops_index   = 2;
            int general_index = 1;

            DataValidations dataValidations = new DataValidations();
            bool            put_validations = false;

            //----------------------generate Dropdowns-----------------------------------
            foreach (var drop in columns)
            {
                if (drop.DropDown_List != null)
                {
                    put_validations = true;

                    var dropdownIdex = dropdownIdex_start;
                    row = new Row {
                        RowIndex = ++rowIdex
                    };
                    sheetData.AppendChild(row);

                    // Add sheet data
                    foreach (var rowData in drop.DropDown_List)
                    {
                        var cell = CreateTextCell(ColumnLetter(dropdownIdex++), rowIdex, rowData);
                        row.AppendChild(cell);
                    }

                    string start_letter = "$" + ColumnLetter(dropdownIdex_start) + "$" + drops_index;
                    string end_letter   = "$" + ColumnLetter(dropdownIdex - 1) + "$" + drops_index;
                    string formual      = "Template!" + start_letter + ":" + end_letter;

                    //DefinedNames definedNamesCol = new DefinedNames();    //Create the collection
                    //DefinedName definedName = new DefinedName()
                    //{ Name = drop.Colum_name, Text = formual };       // Create a new range
                    //definedNamesCol.Append(definedName);                  // Add it to the collection
                    //workbookpart.Workbook.Append(definedNamesCol);                     // Add collection to the workbook

                    string col_name = ColumnLetter(general_index - 1);

                    DataValidation dataValidation =
                        new DataValidation()
                    {
                        Formula1             = new Formula1(formual),
                        Type                 = DataValidationValues.List,
                        AllowBlank           = true, ShowInputMessage = true, ShowErrorMessage = true,
                        SequenceOfReferences = new ListValue <StringValue>()
                        {
                            InnerText = col_name + ":" + col_name
                        }
                    };
                    dataValidations.Append(dataValidation);

                    drops_index++;
                }
                general_index++;
            }
            if (put_validations)
            {
                worksheetPart.Worksheet.Append(dataValidations);
            }

            workbookpart.Workbook.Save();
            document.Close();

            return(stream.ToArray());
        }
Esempio n. 24
0
        public JsonResult jsonImportFileCommit(HttpPostedFileBase file, string columns, bool copyDayBefore, bool sourceListFinal, string hospital)
        {
            file.SaveAs("C:\\Data\\PatientAssignment\\" + file.FileName);
            FileInfo     savedFile = new FileInfo("C:\\Data\\PatientAssignment\\" + file.FileName);
            DataTable    result    = new DataTable();
            ExcelPackage pack      = new ExcelPackage(savedFile);

            result = ToDataTable(pack);
            DateTime srvDate = DateTime.Now.Date + new TimeSpan(00, 00, 00);

            string[]      splitColumns = columns.Split(new char[] { ',' });
            List <string> listMRNs     = new List <string>(); //List of imported MRN's used to evaluate whether the record should be deleted if sourceListFinal is true

            string[] dcs = new string[] { "DC - STD", "DC - EXT" };

            //Copies from day before if selected to do so
            if (copyDayBefore)
            {
                DateTime dayBefore = srvDate.AddDays(-1);
                var      query     = from p in db.PatientLogs
                                     where p.ServiceDate == dayBefore && p.Hospital == hospital && !dcs.Contains(p.ServiceType)
                                     select p;

                foreach (PatientLog pat in query)
                {
                    pat.ServiceDate = srvDate;
                    pat.ServiceType = "Assigned";
                    db.PatientLogs.Add(pat);
                }
                db.SaveChanges();
            }
            DataValidations validate = new DataValidations();

            //Parses the Excel spreadsheet to create a Patient object
            for (int i = 0; i < result.Rows.Count; i++)
            {
                PatientLog patient = new PatientLog();

                for (int j = 0; j < splitColumns.Length; j++)
                {
                    switch (splitColumns[j])
                    {
                    case "Comments":
                        patient.Comments = result.Rows[i][j].ToString();
                        break;

                    case "DOB":
                        try
                        {
                            patient.DOB = DateTime.Parse(result.Rows[i][j].ToString());
                        }
                        catch
                        {
                            patient.DOB = DateTime.Parse("1/1/1900");
                        }
                        break;

                    case "Gender":
                        patient.Gender = validate.checkGender(db, result.Rows[i][j].ToString());
                        break;

                    case "Hospital":
                        patient.Hospital = result.Rows[i][j].ToString();
                        break;

                    case "MRN_FIN":
                        patient.MRN_FIN = result.Rows[i][j].ToString();
                        listMRNs.Add(patient.MRN_FIN);
                        break;

                    case "PatientClass":
                        patient.PatientClass = result.Rows[i][j].ToString();
                        break;

                    case "PatientName":
                        patient.PatientName = result.Rows[i][j].ToString();
                        break;

                    case "PCP_Practice":
                        patient.PCP_Practice = validate.checkPCPandHosp(db, result.Rows[i][j].ToString(), hospital);
                        break;

                    case "Physician":
                        patient.Physician = validate.checkAIMSPhyAndHosp(db, result.Rows[i][j].ToString(), hospital);
                        break;

                    case "RoomNo":
                        patient.RoomNo = result.Rows[i][j].ToString();
                        break;

                    case "ServiceDate":
                        try
                        {
                            patient.ServiceDate = DateTime.Parse(result.Rows[i][j].ToString());
                        }
                        catch
                        {
                            patient.ServiceDate = DateTime.Parse(DateTime.Now.ToShortDateString());
                        }
                        break;

                    case "ServiceType":
                        patient.ServiceType = result.Rows[i][j].ToString();
                        break;
                    }
                }
                patient.Hospital = hospital;
                if (patient.ServiceDate == null)
                {
                    patient.ServiceDate = srvDate;
                }


                //Set default service type if none exists
                if (patient.ServiceType == null || patient.ServiceType == "")
                {
                    patient.ServiceType = "Assigned";
                }

                //Detect if patient already exists
                bool alreadyExists = (from p in db.PatientLogs
                                      where p.PatientName == patient.PatientName && p.MRN_FIN == patient.MRN_FIN && p.ServiceDate == patient.ServiceDate
                                      select p).Any();

                //If does not exist, add, otherwise check if sourceFinalList is true and evaluate eligibility
                if (!alreadyExists)
                {
                    //Set physician to unassigned if none exists
                    if (patient.Physician == null || patient.Physician == "")
                    {
                        patient.Physician = "Unassigned";
                    }
                    patient.DateCreated = DateTime.Now;
                    db.PatientLogs.Add(patient);
                }
                else
                {
                    PatientLog oldData = (from p in db.PatientLogs
                                          where p.PatientName == patient.PatientName && p.MRN_FIN == patient.MRN_FIN && p.ServiceDate == patient.ServiceDate
                                          select p).First();

                    if (patient.Comments != null && patient.Comments != "")
                    {
                        oldData.Comments = patient.Comments;
                    }

                    if (patient.DOB != null)
                    {
                        oldData.DOB = patient.DOB;
                    }

                    if (patient.Gender != null && patient.Gender != "")
                    {
                        oldData.Gender = patient.Gender;
                    }

                    if (patient.Hospital != null && patient.Hospital != "")
                    {
                        oldData.Hospital = patient.Hospital;
                    }

                    if (patient.PatientClass != null && patient.PatientClass != "")
                    {
                        oldData.PatientClass = patient.PatientClass;
                    }

                    if (patient.PCP_Practice != null && patient.PCP_Practice != "")
                    {
                        oldData.PCP_Practice = patient.PCP_Practice;
                    }

                    if (patient.Physician != null && patient.Physician != "")
                    {
                        oldData.Physician = patient.Physician;
                    }

                    if (patient.RoomNo != null && patient.RoomNo != "")
                    {
                        oldData.RoomNo = patient.RoomNo;
                    }

                    if (patient.ServiceType != null && patient.ServiceType != "")
                    {
                        oldData.ServiceType = patient.ServiceType;
                    }

                    db.Entry(oldData).State = EntityState.Modified;
                    //oldData = MergePatient(oldData, patient);
                    //db.SaveChanges();
                }

                //patient.Hospital = hospital;
                //importList.Add(patient);
            }
            db.SaveChanges();


            //If sourceListFinal is true and this MRN is not found, it is deleted
            if (sourceListFinal)
            {
                var todaysPatients = from p in db.PatientLogs
                                     where p.ServiceDate == srvDate && p.Hospital == hospital && !dcs.Contains(p.ServiceType)
                                     select p;
                foreach (PatientLog pat in todaysPatients)
                {
                    if (!listMRNs.Contains(pat.MRN_FIN))
                    {
                        db.PatientLogs.Remove(pat);
                    }
                }
            }

            db.SaveChanges();
            return(Json("Success", JsonRequestBehavior.AllowGet));
        }