Example #1
0
        Transaction GetTransaction(int rowNumber)
        {
            IExcelSheet    sheet = _app.GetActiveWorkbook().GetActiveSheet();
            ExcelSheetData data  = sheet.GetSheetData(1, rowNumber);


            if (data.Rows.Count < 2)
            {
                return(null);
            }


            ExcelRowData headerRowData = data.Rows[0];

            ExcelRowData valueRowData = data.Rows[1];

            string separator = ";";
            string header    = headerRowData.GetValuesInLine(separator);
            string values    = valueRowData.GetValuesInLine(separator);

            TransactionParser parser      = new TransactionParser(header, separator.FirstOrDefault());
            Transaction       transaction = parser.ParseFromLine(values);

            return(transaction);
        }
        public ScheduleDetailsDTO ParseDataRow(ExcelRowData excelRowData)
        {
            ScheduleDetailsDTO schedule = null;

            try
            {
                schedule = new ScheduleDetailsDTO()
                {
                    ID               = new Guid(excelRowData.DataRow[0].CellValue),
                    FlightID         = new Guid(excelRowData.DataRow[1].CellValue),
                    FlightStateID    = new Guid(excelRowData.DataRow[2].CellValue),
                    CityDeparture    = excelRowData.DataRow[3].CellValue.Substring(0, excelRowData.DataRow[3].CellValue.IndexOf(" (") + 1),
                    CountryDeparture = Regex.Match(excelRowData.DataRow[3].CellValue, @"\(([^)]*)\)").Groups[1].Value,
                    CityArrival      = excelRowData.DataRow[4].CellValue.Substring(0, excelRowData.DataRow[4].CellValue.IndexOf(" (") + 1),
                    CountryArrival   = Regex.Match(excelRowData.DataRow[4].CellValue, @"\(([^)]*)\)").Groups[1].Value,
                    DepartureDT      = DateTime.Parse(excelRowData.DataRow[5].CellValue),
                    ArrivalDT        = DateTime.Parse(excelRowData.DataRow[6].CellValue),
                    Company          = excelRowData.DataRow[7].CellValue,
                    Comment          = excelRowData.DataRow[8].CellValue,
                };
            }
            catch (ArgumentOutOfRangeException ex)
            {
                throw new AirportServiceException("Couldn't parse the data.");
            }
            catch (FormatException ex)
            {
                throw new AirportServiceException("Couldn't parse the data.");
            }

            return(schedule);
        }
        public ExcelRowData GenerateDataRow(ScheduleDetailsDTO schedule)
        {
            ExcelRowData excelRowData = new ExcelRowData()
            {
                DataRow = new List <ExcelCellData>()
                {
                    new ExcelCellData()
                    {
                        CellValue    = schedule.ID.ToString(),
                        CellDataType = schedule.ID.GetType()
                    },
                    new ExcelCellData()
                    {
                        CellValue    = schedule.FlightID.ToString(),
                        CellDataType = schedule.FlightID.GetType()
                    },
                    new ExcelCellData()
                    {
                        CellValue    = schedule.FlightStateID.ToString(),
                        CellDataType = schedule.FlightStateID.GetType()
                    },
                    new ExcelCellData()
                    {
                        CellValue    = schedule.CityDeparture.ToString() + " (" + schedule.CountryDeparture.ToString() + ")",
                        CellDataType = schedule.CityDeparture.GetType()
                    },
                    new ExcelCellData()
                    {
                        CellValue    = schedule.CityArrival.ToString() + " (" + schedule.CountryArrival.ToString() + ")",
                        CellDataType = schedule.CityArrival.GetType()
                    },
                    new ExcelCellData()
                    {
                        CellValue    = schedule.DepartureDT.Value.ToOADate().ToString(),
                        CellDataType = schedule.DepartureDT.GetType()
                    },
                    new ExcelCellData()
                    {
                        CellValue    = schedule.ArrivalDT.Value.ToOADate().ToString(),
                        CellDataType = schedule.ArrivalDT.GetType()
                    },
                    new ExcelCellData()
                    {
                        CellValue    = schedule.Company.ToString(),
                        CellDataType = schedule.Company.GetType()
                    },
                    new ExcelCellData()
                    {
                        CellValue    = schedule.Comment.ToString(),
                        CellDataType = schedule.Comment.GetType()
                    }
                }
            };

            return(excelRowData);
        }
        public ExcelRowData GenerateHeadingRow()
        {
            ExcelRowData excelHearingData = new ExcelRowData(new List <ExcelCellData>()
            {
                new ExcelCellData()
                {
                    CellValue    = Constants.ScheduleID,
                    CellDataType = Constants.ScheduleID.GetType()
                },
                new ExcelCellData()
                {
                    CellValue    = Constants.FlightID,
                    CellDataType = Constants.FlightID.GetType()
                },
                new ExcelCellData()
                {
                    CellValue    = Constants.FlightStateID,
                    CellDataType = Constants.FlightStateID.GetType()
                },
                new ExcelCellData()
                {
                    CellValue    = Constants.From,
                    CellDataType = Constants.From.GetType()
                },
                new ExcelCellData()
                {
                    CellValue    = Constants.To,
                    CellDataType = Constants.To.GetType()
                },
                new ExcelCellData()
                {
                    CellValue    = Constants.Departure,
                    CellDataType = Constants.Departure.GetType()
                },
                new ExcelCellData()
                {
                    CellValue    = Constants.Arrival,
                    CellDataType = Constants.Arrival.GetType()
                },
                new ExcelCellData()
                {
                    CellValue    = Constants.Company,
                    CellDataType = Constants.Company.GetType()
                },
                new ExcelCellData()
                {
                    CellValue    = Constants.Comment,
                    CellDataType = Constants.Comment.GetType()
                }
            }
                                                             );

            return(excelHearingData);
        }
Example #5
0
        public void GenerateSheets()
        {
            int YEAR = 2017;

            object[] headers =
            {
                @"Project/Activity", "Feature",  "Employee", "Comment", "Business Line (BL)",
                "Time(date)",        "Location", "Work Units"
            };

            string PATH = @"c:\Maciek\Timesheets\2017";

            int COL_FIRST      = 1;
            int COL_LAST       = 8;
            int ROW_FIRST      = 1;
            int ROW_DATA_FIRST = 2;

            ExcelApp excel = new ExcelApp();

            excel.CreateNewInstance();

            for (int m = 12; m >= 1; m--)
            {
                IExcelWorkbook workbook     = excel.CreateAndActivateNewWorkbook();
                string         workBookName = $"Protokol_MSzczudlo_{YEAR}_{m:D2}.xlsx";
                string         workBookPath = Path.Combine(PATH, workBookName);

                int daysInMonth = DateTime.DaysInMonth(YEAR, m);
                int ROW_LAST    = daysInMonth + 1;
                int ROW_SUM     = ROW_LAST + 1;

                ExcelSheetData sheetData     = new ExcelSheetData();
                ExcelRowData   headerRowData = new ExcelRowData(COL_FIRST, ROW_FIRST, COL_LAST, ROW_FIRST);
                headerRowData.Values = headers.ToList();
                sheetData.Rows.Add(headerRowData);
                ExcelFormatData headerRowFormat = new ExcelFormatData(COL_FIRST, ROW_FIRST, COL_LAST, ROW_FIRST);
                headerRowFormat.Background          = Color.LightGray;
                headerRowFormat.IsFontBold          = true;
                headerRowFormat.HorizontalAlignment = ExcelHorizontalAlignmentType.Center;
                sheetData.Formats.Add(headerRowFormat);

                for (int d = 1; d <= daysInMonth; d++)
                {
                    object[] values = new object[8];


                    int      row  = d + 1;
                    DateTime iDay = new DateTime(YEAR, m, d);

                    values[5] = iDay.ToShortDateString();

                    bool isFreeDay = iDay.DayOfWeek == DayOfWeek.Saturday || iDay.DayOfWeek == DayOfWeek.Sunday;
                    if (isFreeDay)
                    {
                        ExcelFormatData iFormatData = new ExcelFormatData(COL_FIRST, row, COL_LAST, row);
                        iFormatData.Background = Color.Salmon;
                        sheetData.Formats.Add(iFormatData);
                        ExcelRowData iRow = new ExcelRowData(COL_FIRST, row, COL_LAST, row);
                        iRow.Values = values.ToList();
                        sheetData.Rows.Add(iRow);
                    }
                    else
                    {
                        values[0] = "IS Treasury";
                        values[2] = "Maciej Szczudło";
                        values[7] = 8;

                        ExcelRowData iRow = new ExcelRowData(COL_FIRST, row, COL_LAST, row);
                        iRow.Values = values.ToList();
                        sheetData.Rows.Add(iRow);
                    }


                    ExcelBorderData borderData = new ExcelBorderData(COL_FIRST, ROW_FIRST, COL_LAST, ROW_LAST);
                    borderData.Borders.AddBorder(ExcelBordersIndex.xlAround, ExcelBorderWeight.xlThin, ExcelLineStyle.xlContinuous);
                    borderData.Borders.AddBorder(ExcelBordersIndex.xlInside, ExcelBorderWeight.xlThin, ExcelLineStyle.xlContinuous);
                    sheetData.Borders.Add(borderData);

                    ExcelFormatData fontFormatData = new ExcelFormatData(borderData.Range);
                    fontFormatData.FontSize = 10;
                    sheetData.Formats.Add(fontFormatData);
                }


                string monthName = DateTimeTools.GetMonthName(m);
                string sheetName = $"{monthName} {YEAR}";

                IExcelSheet newSheet = workbook.CreateSheet(sheetName);

                ExcelRangeInfo sumRange = new ExcelRangeInfo(ROW_DATA_FIRST, COL_LAST, ROW_LAST, COL_LAST);
                ExcelFormula   formula  = ExcelFormulaHelper.GetSum(ROW_SUM, COL_LAST, sumRange);
                sheetData.Formulas.Add(formula);
                ExcelBorderData borderSumData = new ExcelBorderData(formula.Range);
                borderSumData.Borders.AddBorder(ExcelBordersIndex.xlAround, ExcelBorderWeight.xlThin, ExcelLineStyle.xlContinuous);
                sheetData.Borders.Add(borderSumData);

                newSheet.InsertSheetData(sheetData);
                newSheet.SetColumnsAutoFit(COL_FIRST, COL_LAST);
                newSheet.SetCellName(ROW_SUM, COL_LAST, "godziny");

                excel.SetDisplayGridlines(false);

                workbook.Save(workBookPath);
            }
        }