public void xlsx_reader(string file_location, string file_name)
{
string xlsx_path = file_location + file_name;
FileInfo xlsx_file = new FileInfo(xlsx_path);
ExcelPackage package = new ExcelPackage(xlsx_file);
ExcelWorksheet sheet = package.Workbook.Worksheets[1];
var start = sheet.Dimension.Start;
var end = sheet.Dimension.End;
string row_str = ""; //Row of data
string cell = ""; //Data within cell
string header_str = ExcelAddress.GetAddressCol(1);
for (int row = start.Row; row <= end.Row; row++)
{
for (int col = start.Column; col <= end.Column; col++)
{
cell = sheet.Cells[row, col].Text;//Get cell data according to row,collumn number
if (cell != "" && !string.IsNullOrEmpty(cell))
{
row_str = row_str + cell + ",";
}
}
row_str = row_str.TrimEnd(',');
to_csv(file_location, file_name, row_str);//Sends all info to CSV file format
row_str = "";
}
csv_reader(file_location + file_name + ".csv");//Reads created CSV
}
private void CreateExcelSheet(ExcelPackage excel, int year, int month)
{
var sheet = excel.Workbook.Worksheets.Add($"{new DateTime(year, month, 1).ToString("MMM")} {year}");
// write header texts:
sheet.Cells[1, 1].Value = "Tag";
sheet.Cells[1, 2].Value = "Projektst.";
int column = 3;
foreach (var activity in m_settings.TrackedActivities)
{
sheet.Cells[1, column++].Value = activity;
}
int endColumn = column - 1;
// gray background:
sheet.Cells[1, 1, 1, endColumn].Style.Fill.PatternType = ExcelFillStyle.Solid;
sheet.Cells[1, 1, 1, endColumn].Style.Fill.BackgroundColor.SetColor(Color.LightGray);
// properly sized:
sheet.Row(1).Height = 30;
for (int i = 1; i <= endColumn; i++)
{
sheet.Column(i).Width = 24;
// border:
sheet.Cells[1, i].Style.Border.BorderAround(ExcelBorderStyle.Medium, Color.Black);
// allow line breaks:
sheet.Cells[1, i].Style.WrapText = true;
// alignment:
sheet.Cells[1, i].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
sheet.Cells[1, i].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
}
// write table body:
int row = 2;
var inclusiveMin = new DateTime(year, month, 1);
var exclusiveMax = new DateTime(month == 12 ? year + 1 : year, month == 12 ? 1 : month + 1, 1);
foreach (var stamp in m_manager.StampList.Where(s => s.Day >= inclusiveMin && s.Day < exclusiveMax).OrderBy(s => s.Day).ToArray())
{
sheet.Cells[row, 1].Value = stamp.Day.ToString("dd.MM.yyyy");
sheet.Cells[row, 2].Formula = $"=SUM(C{row}:H{row})"; //GetTimeForExcelCell(stamp);
column = 3;
foreach (var activity in m_settings.TrackedActivities)
{
sheet.Cells[row, column++].Value = GetTimeForExcelCell(stamp, activity);
}
// formatting:
sheet.Cells[row, 2, row, endColumn].Style.Numberformat.Format = "0.00";
// border:
sheet.Cells[row, 2].Style.Border.Right.Style = ExcelBorderStyle.Medium;
sheet.Cells[row, 2].Style.Border.Right.Color.SetColor(Color.Black);
row++;
}
int summaryRow = 24;
// keep drawing border down to summary row:
for (int i = row; i < summaryRow; i++)
{
sheet.Cells[i, 2].Style.Border.Right.Style = ExcelBorderStyle.Medium;
sheet.Cells[i, 2].Style.Border.Right.Color.SetColor(Color.Black);
}
// write footer summary line formulas:
row = summaryRow;
sheet.Cells[row, 1].Formula = "=COUNTA(A2:A21)";
for (int i = 2; i <= endColumn; i++)
{
sheet.Cells[row, i].Formula = $"=SUM({ExcelAddress.GetAddressCol(i)}2:{ExcelAddress.GetAddressCol(i)}21)";
}
// formatting:
sheet.Cells[row, 2, row, endColumn].Style.Numberformat.Format = "0.00";
// gray background:
sheet.Cells[row, 1, row, endColumn].Style.Fill.PatternType = ExcelFillStyle.Solid;
sheet.Cells[row, 1, row, endColumn].Style.Fill.BackgroundColor.SetColor(Color.LightGray);
// border:
for (int i = 1; i <= endColumn; i++)
{
sheet.Cells[row, i].Style.Border.BorderAround(ExcelBorderStyle.Medium, Color.Black);
}
}
