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