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