private static void CopyRowAsRange(IXLWorksheet originalSheet, int originalRowNumber, IXLWorksheet destSheet, int destRowNumber) { { var destinationRow = destSheet.Row(destRowNumber); destinationRow.Clear(); var originalRow = originalSheet.Row(originalRowNumber); int columnNumber = originalRow.LastCellUsed(true).Address.ColumnNumber; var originalRange = originalSheet.Range(originalRowNumber, 1, originalRowNumber, columnNumber); var destRange = destSheet.Range(destRowNumber, 1, destRowNumber, columnNumber); originalRange.CopyTo(destRange); } }
/// <summary> /// Checks the records in the sheet /// </summary> /// <param name="sheet">Sheet to check</param> /// <param name="date">Current date</param> /// <param name="yesterday">Yesterdays date</param> private static void CheckRecords( IXLWorksheet sheet, DateTime date, DateTime yesterday) { // Check the header is bold Assert.AreEqual(true, sheet.Row(1).Style.Font.Bold); // Check the header row Assert.AreEqual("FirstColumn", sheet.Cell(1, 1).Value); Assert.AreEqual("Int Column", sheet.Cell(1, 2).Value); Assert.AreEqual("StringColumn", sheet.Cell(1, 3).Value); Assert.AreEqual("TypeConvertedColumn", sheet.Cell(1, 4).Value); Assert.AreEqual("BoolColumn", sheet.Cell(1, 5).Value); Assert.AreEqual("DoubleColumn", sheet.Cell(1, 6).Value); Assert.AreEqual("DateTimeColumn", sheet.Cell(1, 7).Value); Assert.AreEqual("NullStringColumn", sheet.Cell(1, 8).Value); Assert.AreEqual("FormulaColumn", sheet.Cell(1, 9).Value); // Check the first record Assert.AreEqual("first column", sheet.Cell(2, 1).Value); Assert.AreEqual((double)1, sheet.Cell(2, 2).Value); Assert.AreEqual("string column", sheet.Cell(2, 3).Value); Assert.AreEqual("test", sheet.Cell(2, 4).Value); Assert.AreEqual("true", sheet.Cell(2, 5).Value); Assert.AreEqual(12.34, sheet.Cell(2, 6).Value); Assert.AreEqual(date, sheet.Cell(2, 7).Value); Assert.AreEqual("", sheet.Cell(2, 8).Style.DateFormat.Format); // TODO: Do we need a different default here for dates? Assert.AreEqual("", sheet.Cell(2, 8).Value); Assert.AreEqual("1+2", sheet.Cell(2, 9).FormulaA1); Assert.AreEqual((double)3, sheet.Cell(2, 9).Value); // Check the second record Assert.AreEqual("first column 2", sheet.Cell(3, 1).Value); Assert.AreEqual((double)2, sheet.Cell(3, 2).Value); Assert.AreEqual("string column 2", sheet.Cell(3, 3).Value); Assert.AreEqual("test", sheet.Cell(3, 4).Value); Assert.AreEqual("false", sheet.Cell(3, 5).Value); Assert.AreEqual(43.21, sheet.Cell(3, 6).Value); Assert.AreEqual(yesterday, sheet.Cell(3, 7).Value); Assert.AreEqual("", sheet.Cell(3, 7).Style.DateFormat.Format); // TODO: Do we need a different default here for dates? Assert.AreEqual("", sheet.Cell(3, 8).Value); Assert.AreEqual("not a formula", sheet.Cell(3, 9).Value); }
private static void WriteEmployeeRows(ReportDTO <QuickJobTimeReportDTO> report, IXLWorksheet excelSheet) { var columns = new string[] { "A", "B", "C", "D", "E", "F", "G", "H", "I" }; var row = EMPLOYEE_ROW_START; if (report.Data.Employees.Any()) { excelSheet.Row(7).InsertRowsAbove(report.Data.Employees.Count()); } foreach (var employeeRow in report.Data.Employees.OrderBy(x => x.TaskCategory).ThenBy(x => x.TaskName).ThenBy(x => x.EmployeeName)) { SetCellContentWithBorder(excelSheet, row, 1, employeeRow.EmployeeName, alignLeft: true, addLeftBorder: true); excelSheet.Cell(row, 2).Style.Border.BottomBorder = XLBorderStyleValues.Thin; SetCellContentWithBorder(excelSheet, row, 3, employeeRow.TaskCategory, alignLeft: true); SetCellContentWithBorder(excelSheet, row, 4, employeeRow.TaskName, alignLeft: true); excelSheet.Cell(row, 5).Style.Border.BottomBorder = XLBorderStyleValues.Thin; SetCellContentWithBorder(excelSheet, row, 6, (double)employeeRow.Regular); SetCellContentWithBorder(excelSheet, row, 7, (double)employeeRow.Overtime); excelSheet.Cell(row, 8).SetFormulaA1($"SUM({columns[5]}{row },{columns[6]}{row})"); excelSheet.Cell(row, 8).Style.Border.RightBorder = XLBorderStyleValues.Thin; excelSheet.Cell(row, 8).Style.Border.BottomBorder = XLBorderStyleValues.Thin; row++; } if (report.Data.Employees.Any()) { excelSheet.Cell(row, 6).SetFormulaA1($"SUM({columns[5]}{EMPLOYEE_ROW_START }:{columns[5]}{row - 1})"); excelSheet.Cell(row, 6).Style.Border.LeftBorder = XLBorderStyleValues.Thin; excelSheet.Cell(row, 6).Style.Border.BottomBorder = XLBorderStyleValues.Thin; excelSheet.Cell(row, 6).Style.Font.Bold = true; excelSheet.Cell(row, 7).SetFormulaA1($"SUM({columns[6]}{EMPLOYEE_ROW_START }:{columns[6]}{row - 1})"); excelSheet.Cell(row, 7).Style.Border.BottomBorder = XLBorderStyleValues.Thin; excelSheet.Cell(row, 7).Style.Font.Bold = true; excelSheet.Cell(row, 8).SetFormulaA1($"SUM({columns[5]}{row }:{columns[6]}{row})"); excelSheet.Cell(row, 8).Style.Border.RightBorder = XLBorderStyleValues.Thin; excelSheet.Cell(row, 8).Style.Border.BottomBorder = XLBorderStyleValues.Thin; excelSheet.Cell(row, 8).Style.Font.Bold = true; } }
/// <summary> /// Retrieve column position by name in first row. /// </summary> /// <param name="sheet">Page to find column</param> /// <param name="columnName">Name to find column in first row</param> /// <returns>Position of column name or 0 when not found</returns> /// <exception cref="ArgumentNullException">When <paramref name="columnName"/> is null o whitespace</exception> public static int ColumnByName(this IXLWorksheet sheet, string columnName) { if (string.IsNullOrWhiteSpace(columnName)) { throw new ArgumentNullException(nameof(columnName), "Enter the column name to get position."); } if (sheet.RowCount() <= 0) { return(0); } for (var i = 1; i <= sheet.ColumnUsedCount(); i++) { if (sheet.Row(1).Cell(i).Value.ToString()?.ToUpper() == columnName.ToUpper()) { return(i); } } return(0); }
private static int insertHardwareHeaders(this IXLWorksheet worksheet, int row) { IXLRow headerRow = worksheet.Row(row); headerRow.Style.Font.SetBold(); headerRow.Cell("A").Value = "Name"; headerRow.Cell("B").Value = "Model Number"; headerRow.Cell("C").Value = "Manufacturer"; headerRow.Cell("D").Value = "Quantity"; headerRow.Cell("E").Value = "List Price"; headerRow.Cell("F").Value = "Unit Cost"; headerRow.Cell("G").Value = "Total Cost"; headerRow.Cell("H").Value = "Unit Labor"; headerRow.Cell("I").Value = "Total Labor"; row++; return(row); }
public bool DeleteEmployee(int id) { try { string filePath = Path.Combine(_hostingEnvironment.WebRootPath, "Database", "Employees.xlsx"); using (XLWorkbook workBook = new XLWorkbook(filePath)) { //Read the first Sheet from Excel file. IXLWorksheet workSheet = workBook.Worksheet(1); workSheet.Row(id).Delete(); } return(true); } catch (Exception e) { Console.WriteLine(e); return(false); } }
private int GetColumnNumber(String sheetName, String colName) { worksheet = workbook.Worksheet(sheetName); IXLRow r = worksheet.Row(1); while (r != worksheet.LastRowUsed()) { foreach (IXLCell c in r.CellsUsed()) { if (c.Value.Equals(colName)) { return(c.Address.ColumnNumber); // Starts with 0 } } r = r.RowBelow(); } return(-1); }
private async Task DrawStockOutTable(IXLWorksheet ws) { //string fontName = "Arial"; //double fontSize = 10; ws.Row(8).Height = 30; ws.Cell("A8").Value = "Order No"; ws.Range("A8").Style.Fill.BackgroundColor = XLColor.FromArgb(0, 32, 96); ws.Range("A8").Style.Font.FontColor = XLColor.White; ws.Cell("B8").Value = "Order Date"; ws.Range("B8:C8").Style.Fill.BackgroundColor = XLColor.FromArgb(0, 32, 96); ws.Range("B8:C8").Style.Font.FontColor = XLColor.White; ws.Range("B8:C8").Merge(); ws.Cell("D8").Value = "Item Name"; ws.Range("D8:G8").Style.Fill.BackgroundColor = XLColor.FromArgb(0, 32, 96); ws.Range("D8:G8").Style.Font.FontColor = XLColor.White; ws.Range("D8:G8").Merge(); ws.Cell("H8").Value = "Qty"; ws.Range("H8").Style.Fill.BackgroundColor = XLColor.FromArgb(0, 32, 96); ws.Range("H8").Style.Font.FontColor = XLColor.White; ws.Cell("I8").Value = "Price"; ws.Range("I8:J8").Style.Fill.BackgroundColor = XLColor.FromArgb(0, 32, 96); ws.Range("I8:J8").Style.Font.FontColor = XLColor.White; ws.Range("I8:J8").Merge(); ws.Cell("K8").Value = "Discount"; ws.Range("K8:L8").Style.Fill.BackgroundColor = XLColor.FromArgb(0, 32, 96); ws.Range("K8:L8").Style.Font.FontColor = XLColor.White; ws.Range("K8:L8").Merge(); ws.Cell("M8").Value = "Amount Paid"; ws.Range("M8:N8").Style.Fill.BackgroundColor = XLColor.FromArgb(0, 32, 96); ws.Range("M8:N8").Style.Font.FontColor = XLColor.White; ws.Range("M8:N8").Merge(); await Task.Delay(0); }
public bool ReadLine() { if (CurrentRowNumber == 0) { CurrentRowNumber = _firstRowNumber - 1; } while (CurrentRowNumber < _lastRowNumber) { CurrentRowNumber++; _currentRow = _worksheet.Row(CurrentRowNumber); if (!_currentRow.IsEmpty()) { return(true); } } return(false); }
/// <summary> /// parse fixed type workbook ; if worksheet_name specific it will locate the matching one /// </summary> public XlsxParser(XLWorkbook wb, IEnumerable <XlsxColumn> required_columns, bool column_name_match_case = false, string worksheet_name = null, bool worksheet_name_matchcase = false) { Columns = required_columns; if (worksheet_name != null) { ws = wb.Worksheets.First(w => string.Equals(w.Name, worksheet_name, worksheet_name_matchcase ? StringComparison.CurrentCulture : StringComparison.CurrentCultureIgnoreCase)); } else { ws = wb.Worksheets.First(); } var header = ws.Row(1); foreach (var cell in header.CellsUsed()) { if (cell.Value == null || !(cell.Value is string)) { continue; } var val = cell.Value as string; foreach (var r in required_columns.Where(t => t.ColumnIndex == -1)) { if (string.Equals(r.Name, val, column_name_match_case ? StringComparison.CurrentCulture : StringComparison.CurrentCultureIgnoreCase)) { r.ColumnIndex = cell.WorksheetColumn().ColumnNumber(); continue; } } } var q = required_columns.FirstOrDefault(w => w.ColumnIndex == -1); if (q != null) { throw new Exception($"can't find required column [{q.Name}]"); } RowCount = ws.RangeUsed().RowCount(); }
private void FillWorksheet(IXLWorksheet worksheet, List <CategorizedWord> catWords) { worksheet.Cell(WORD_CELL).Value = WORD; worksheet.Cell(LANG_CELL).Value = LANG; worksheet.Cell(CAT_CELL).Value = CAT; worksheet.Cell(TRAN_CELL).Value = TRAN; worksheet.Row(1).Style.Font.Bold = true; for (int i = 0; i < catWords.Count; i++) { var word = _context.Words.Find(catWords[i].WordId); var language = _context.Languages.Find(word.LanguageId); var category = _context.Categories.Find(catWords[i].CategoryId); var translations = _context.Translations.Where(t => t.CategorizedWordId == catWords[i].Id).ToList(); worksheet.Cell(i + 2, WORD_IND).Value = word.ThisWord; worksheet.Cell(i + 2, LANG_IND).Value = language.Name; worksheet.Cell(i + 2, CAT_IND).Value = category.Name; worksheet.Cell(i + 2, TRAN_IND).Value = _helper.TranslationsToString(translations); } }
public Employee GetEmployeeById(int id) { string filePath = Path.Combine(_hostingEnvironment.WebRootPath, "Database", "Employees.xlsx"); var emp = new Employee(); using (XLWorkbook workBook = new XLWorkbook(filePath)) { //Read the first Sheet from Excel file. IXLWorksheet workSheet = workBook.Worksheet(1); var empRow = workSheet.Row(id); if (empRow.CellsUsed() != null) { emp.Id = empRow.Cell(1).GetValue <int>(); emp.FirstName = empRow.Cell(2).GetValue <string>(); emp.LastName = empRow.Cell(3).GetValue <string>(); emp.Team = empRow.Cell(4).GetValue <string>().ToTeam(); emp.Email = empRow.Cell(5).GetValue <string>(); } } return(emp); }
public async Task <string> GetGroupByNameAsync(string excelPath, ExcelCell groupByNameCell) { if (_log.IsDebugEnabled) { _log.Debug($"ExcelReader.GetGroupByNameAsync - Creating a new file stream to extract names from source Excel at { excelPath }"); } string groupName; using (FileStream fs = new FileStream(excelPath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite)) { XLWorkbook excel = new XLWorkbook(fs); int workSheetCount = excel.Worksheets.Count; if (_log.IsDebugEnabled) { _log.Debug($"ExcelReader.GetGroupByNameAsync - workSheetCount = { workSheetCount }"); } IXLWorksheet worksheet = excel.Worksheet(workSheetCount); groupName = worksheet.Row(groupByNameCell.Row).Cell(XLHelper.GetColumnNumberFromLetter(groupByNameCell.Column)).Value.ToString(); } return(groupName); }
///<summary> ///returns next spare row number ///</summary> public static int AddLinesSummaryStatisticToSheet(IXLWorksheet toSheet, int startRow, List <LinesTotalStatisticDto> data) { //Table Header toSheet.Cell(startRow, 1).Value = DEBS.Translate("Line.SummaryReport"); RowStyle_H2(toSheet.Row(startRow)); startRow++; int colIndex = 1; toSheet.Cell(startRow, colIndex).Value = DEBS.Translate("Report.Month"); toSheet.Cell(startRow + 1, colIndex).Value = DEBS.Translate("Report.linesCount"); toSheet.Cell(startRow + 2, colIndex).Value = DEBS.Translate("Report.totalStudents"); toSheet.Cell(startRow + 3, colIndex).Value = DEBS.Translate("Report.totalPrice"); colIndex++; for (int i = 0; i < data.Count; i++) { toSheet.Cell(startRow, colIndex + i).Value = i + 1; toSheet.Cell(startRow + 1, colIndex + i).Value = data[i].linesCount; toSheet.Cell(startRow + 2, colIndex + i).Value = data[i].totalStudents; toSheet.Cell(startRow + 3, colIndex + i).Value = data[i].totalPrice; } return(startRow + 4); }
public void ExportToExcel(List <DataTable> datatables, string sheet_prefix) { if (datatables == null) { return; } foreach (DataTable dt in datatables) { if (dt.Rows.Count == 0) { continue; } IXLWorksheet worksheet = null; lock (workbook) { worksheet = workbook.Worksheets.Add(sheet_name(sheet_prefix, dt.TableName)); } // speedup by doing this in parallel worksheet.Cell(1, 1).InsertTable(dt); foreach (var col in worksheet.Columns()) { try { col.Cells(2, worksheet.LastRowUsed().RowNumber()).DataType = Double.TryParse(worksheet.Row(2).Cell(col.ColumnNumber()).Value.ToString(), out double is_number) ? XLCellValues.Number : XLCellValues.Text; } catch { col.Cells(2, worksheet.LastRowUsed().RowNumber()).DataType = XLCellValues.Text; } } } }
private void AssertHistorySheet(IXLWorksheet worksheet, ExportTagDto expectedTagData) { Assert.IsNotNull(worksheet); AssertHeadingsInHistorySheet(worksheet); var expectedData = expectedTagData.History; Assert.AreEqual(expectedData.Count + 1, worksheet.RowsUsed().Count()); var rowIdx = 2; // Start at 2 because Row(1) is the header foreach (var historyDto in expectedData) { var row = worksheet.Row(rowIdx++); Assert.AreEqual(expectedTagData.TagNo, row.Cell(ExcelConverter.HistorySheetColumns.TagNo).Value); Assert.AreEqual(historyDto.Description, row.Cell(ExcelConverter.HistorySheetColumns.Description).Value); AssertDateTime(historyDto.CreatedAtUtc, row.Cell(ExcelConverter.HistorySheetColumns.Date)); Assert.AreEqual(historyDto.CreatedBy, row.Cell(ExcelConverter.HistorySheetColumns.User).Value); AssertInt(historyDto.DueInWeeks, row.Cell(ExcelConverter.HistorySheetColumns.DueInWeeks).Value); } }
private HttpResponseMessage writeToExcel(DataTable dt) { XLWorkbook Workbook = new XLWorkbook(@"c:\temp\Sample.xlsx"); HttpResponseMessage result = new HttpResponseMessage(HttpStatusCode.OK); IXLWorksheet Worksheet = Workbook.Worksheets.First(); Debug.WriteLine(Worksheet); IXLCell cellfornewdata = Worksheet.Cell(8, 1); cellfornewdata.InsertTable(dt.AsEnumerable()); Worksheet.Row(8).Delete(); Workbook.SaveAs(@"c:\temp\Sample.xlsx"); try { Workbook workbook = new Workbook(); workbook.LoadFromFile(@"c:\temp\Sample.xlsx"); workbook.ConverterSetting.SheetFitToPage = true; workbook.CustomDocumentProperties.Add("_MarkAsFinal", true); workbook.CustomDocumentProperties.Add("The Editor", "E-iceblue"); workbook.CustomDocumentProperties.Add("Phone number1", 81705109); workbook.CustomDocumentProperties.Add("Revision number", 7.12); workbook.CustomDocumentProperties.Add("Revision date", DateTime.Now); workbook.SaveToFile(@"c:\temp\Sample.pdf", FileFormat.PDF); Process.Start(@"c:\temp\Sample.pdf"); var pdf = @"c:\temp\Sample.pdf"; var stream = new FileStream(pdf, FileMode.Open, FileAccess.Read); result.Content = new StreamContent(stream); result.Content.Headers.ContentType = new MediaTypeHeaderValue("application/pdf"); return(result); } catch (Exception ex) { Debug.WriteLine("Ëxception is thrown"); Debug.WriteLine(ex.Message); return(result); } }
private static void TryParseRow(Config config, IXLWorksheet ws, JsonSerializerSettings settings, string json) { try { var data = JsonConvert.DeserializeObject <Dictionary <string, object> >(json, settings); var row = ws.Row(RowI); for (var i = 0; i < config.Columns.Count; ++i) { var columnI = i + 1; var column = config.Columns[i]; var value = data.ContainsKey(column.Name) ? data[column.Name] : null; column.CreateCell(row.Cell(columnI), value, config.SubHeader && RowI == 2 ? "string" : null); } RowI += 1; } catch (Exception x) { Console.Error.WriteLine(x.ToString()); } }
public bool EditEmployee(Employee emp) { try { string filePath = Path.Combine(_hostingEnvironment.WebRootPath, "Database", "Employees.xlsx"); using (XLWorkbook workBook = new XLWorkbook(filePath)) { //Read the first Sheet from Excel file. IXLWorksheet workSheet = workBook.Worksheet(1); var empRow = workSheet.Row(emp.Id); empRow.Cell(2).SetValue(emp.FirstName); empRow.Cell(3).SetValue(emp.LastName); empRow.Cell(4).SetValue(emp.Team.ToString()); empRow.Cell(5).SetValue(emp.Email); } return(true); } catch (Exception e) { Console.WriteLine(e); return(false); } }
public void zCreateEvaluationPlansReport(DataTable dt) { ClosedXML.Excel.XLWorkbook workbook = new XLWorkbook(ClosedXML.Excel.XLEventTracking.Disabled); // workbook.Worksheets.Add(dt.TableName.ToString()); // var worksheet = workbook.Worksheet(dt.TableName.ToString()); IXLWorksheet worksheet = workbook.Worksheets.Add(dt.TableName.ToString()); int dtcolumncount = dt.Columns.Count; for (int i = 0; i < dtcolumncount; i++) { worksheet.Cell(1, (i + 1)).Value = dt.Columns[i].Caption; worksheet.Range(2, i + 1, dt.Rows.Count + 1, i + 1).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left; } for (int i = 0; i < dt.Rows.Count; i++) { for (int j = 0; j < dtcolumncount; j++) { worksheet.Cell(i + 2, j + 1).Value = dt.Rows[i][j]; } } //Format top row var rangeheader = worksheet.Range(1, 1, 1, dtcolumncount); rangeheader.Style.Font.Bold = true; worksheet.Row(1).Height = 30; worksheet.Columns(1, dtcolumncount).AdjustToContents(1, dt.Rows.Count + 1); SaveWorkbook(ref workbook); workbook.Dispose(); }
static void Main(string[] args) { IXLWorkbook workbook = new XLWorkbook(Environment.GetFolderPath(Environment.SpecialFolder.Desktop) + "\\Excel Örnek\\Verilerim.xlsx"); IXLWorksheet worksheet = workbook.Worksheet("Veriler"); IXLRange range = worksheet.RangeUsed(); IXLRangeRow rangeRow = range.FirstRow(); IXLRow row = worksheet.Row(5); IXLColumn column = worksheet.Column("C"); double width = column.ColumnRight(5).Width; Console.WriteLine("Sütun Genişliği:" + width); Console.WriteLine("Value : " + row.FirstCell().Value); Console.WriteLine("C5'in Değeri: " + worksheet.Cell("C5").Value); IXLAddress address = worksheet.Cell("C5").Address; IXLCell cell = worksheet.Cell("C5"); for (int i = 1; i <= range.RowCount(); i++) { if (range.Row(i).Cell(2).Value.ToString() == "Yılmaz") { IXLAddress address1 = range.Row(i).Cell(2).Address; Console.WriteLine(address1.RowNumber + " / " + address1.ColumnLetter); break; } } Console.Read(); }
private void AssertTagSheet(IXLWorksheet worksheet, IList <ExportTagDto> expectedTagData) { Assert.IsNotNull(worksheet); AssertHeadingsInTagSheet(worksheet); Assert.AreEqual(expectedTagData.Count + 1, worksheet.RowsUsed().Count()); for (var i = 0; i < expectedTagData.Count; i++) { var tag = expectedTagData.ElementAt(i); var row = worksheet.Row(i + 2); // + 2 because Row(1) is the header Assert.AreEqual(tag.TagNo, row.Cell(ExcelConverter.TagSheetColumns.TagNo).Value); Assert.AreEqual(tag.Description, row.Cell(ExcelConverter.TagSheetColumns.Description).Value); Assert.AreEqual(tag.NextDueAsYearAndWeek, row.Cell(ExcelConverter.TagSheetColumns.NextInYearAndWeek).Value); AssertInt(tag.NextDueWeeks, row.Cell(ExcelConverter.TagSheetColumns.NextDueWeeks).Value); Assert.AreEqual(tag.Journey, row.Cell(ExcelConverter.TagSheetColumns.Journey).Value); Assert.AreEqual(tag.Step, row.Cell(ExcelConverter.TagSheetColumns.Step).Value); Assert.AreEqual(tag.Mode, row.Cell(ExcelConverter.TagSheetColumns.Mode).Value); Assert.AreEqual(tag.PurchaseOrderTitle, row.Cell(ExcelConverter.TagSheetColumns.Po).Value); Assert.AreEqual(tag.AreaCode, row.Cell(ExcelConverter.TagSheetColumns.Area).Value); Assert.AreEqual(tag.ResponsibleCode, row.Cell(ExcelConverter.TagSheetColumns.Resp).Value); Assert.AreEqual(tag.DisciplineCode, row.Cell(ExcelConverter.TagSheetColumns.Disc).Value); Assert.AreEqual(tag.Status, row.Cell(ExcelConverter.TagSheetColumns.PresStatus).Value); Assert.AreEqual(tag.RequirementTitles, row.Cell(ExcelConverter.TagSheetColumns.Req).Value); Assert.AreEqual(tag.Remark, row.Cell(ExcelConverter.TagSheetColumns.Remark).Value); Assert.AreEqual(tag.StorageArea, row.Cell(ExcelConverter.TagSheetColumns.StorageArea).Value); Assert.AreEqual(tag.ActionStatus, row.Cell(ExcelConverter.TagSheetColumns.ActionStatus).Value); Assert.AreEqual(tag.CommPkgNo, row.Cell(ExcelConverter.TagSheetColumns.CommPkg).Value); Assert.AreEqual(tag.McPkgNo, row.Cell(ExcelConverter.TagSheetColumns.McPkg).Value); AssertInt(tag.ActionsCount, row.Cell(ExcelConverter.TagSheetColumns.Actions).Value); AssertInt(tag.OpenActionsCount, row.Cell(ExcelConverter.TagSheetColumns.OpenActions).Value); AssertInt(tag.OverdueActionsCount, row.Cell(ExcelConverter.TagSheetColumns.OverdueActions).Value); AssertInt(tag.AttachmentsCount, row.Cell(ExcelConverter.TagSheetColumns.Attachments).Value); AssertBool(tag.IsVoided, row.Cell(ExcelConverter.TagSheetColumns.Voided).Value); } }
private void CreateColumns(IXLWorksheet worksheet) { var rowAdress = 5; foreach (Columns column in Enum.GetValues(typeof(Columns))) { worksheet.Cell(rowAdress, (int)column).Value = column.ToString(); if (column == Columns.勤務時間 || column == Columns.労働時間 || column == Columns.休憩時間) { worksheet.Column((int)column).Width = 12; } if (column == Columns.詳細) { worksheet.Column((int)column).Width = 6; } } var columncells = worksheet.Row(rowAdress).CellsUsed(); columncells.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; columncells.Style.Fill.BackgroundColor = XLColor.FromHtml("#f0ffff"); SetBorder(columncells); }
private static void AddSummaryWorksheet(IXLWorksheet sheet, List <string> list) { sheet.Row(1).Cell(1).Value = "Compte"; sheet.Row(1).Cell(2).Value = "Nombre"; sheet.Row(1).Cell(3).Value = "Prix"; var index = 2; foreach (var item in list) { sheet.Row(index).Cell(1).Value = item; sheet.Row(index).Cell(2).FormulaA1 = $"={item}!_{item}_COUNT"; sheet.Row(index).Cell(3).FormulaA1 = $"={item}!_{item}_PRICE"; sheet.Row(index).Cell(3).Style.NumberFormat.Format = "#,##0.00"; index++; } var used = sheet.RangeUsed().Rows(1, index - 1); used.Style.Border.OutsideBorder = XLBorderStyleValues.Thin; used.Style.Border.InsideBorder = XLBorderStyleValues.Thin; }
private static void AddSummaryTable(IXLWorksheet sheet, int index, int lastDataRow) { index += 2; sheet.Row(index).Cell(3).FormulaR1C1 = string.Format("=COUNTIF(H:H,\"CMU\")+COUNTIF(H:H,\"CMU_AZ\")+COUNTIF(H:H,\"CMU_UNIGE\")+COUNTIF(H:H,\"CMU_DBU\")+COUNTIF(H:H,\"CMU_HUG\")+COUNTIF(H:H,\"CMU_IEH2\")+COUNTIF(H:H,\"ARVE-UNIGE\")+COUNTIF(H:H,\"ARVE-BELS\")+COUNTIF(H:H,\"ARVE-CUI\")+COUNTIF(H:H,\"ARVE-DBU\")+COUNTIF(H:H,\"ARVE-ISE\")+COUNTIF(H:H,\"ARVE_AZ\")+COUNTIF(H:H,\"ARVE-MATH\")+COUNTIF(H:H,\"ARVE-OBS\")+COUNTIF(H:H,\"ARVE-TERRE\")+COUNTIF(H:H,\"ARVE\")", lastDataRow); sheet.Row(index).Cell(4).Value = "commandes UNIGE"; sheet.Row(index).Cell(7).Style.NumberFormat.Format = "#,##0.00"; sheet.Row(index).Cell(7).FormulaR1C1 = string.Format("=SUMIF(H:H,\"CMU\",G:G)+SUMIF(H:H,\"CMU_AZ\",G:G)+SUMIF(H:H,\"CMU_UNIGE\",G:G)+SUMIF(H:H,\"CMU_DBU\",G:G)+SUMIF(H:H,\"CMU_HUG\",G:G)+SUMIF(H:H,\"CMU_IEH2\",G:G)+SUMIF(H:H,\"ARVE-UNIGE\",G:G)+SUMIF(H:H,\"ARVE-BELS\",G:G)+SUMIF(H:H,\"ARVE-CUI\",G:G)+SUMIF(H:H,\"ARVE-DBU\",G:G)+SUMIF(H:H,\"ARVE-ISE\",G:G)+SUMIF(H:H,\"ARVE_AZ\",G:G)+SUMIF(H:H,\"ARVE-MATH\",G:G)+SUMIF(H:H,\"ARVE-OBS\",G:G)+SUMIF(H:H,\"ARVE-TERRE\",G:G)+SUMIF(H:H,\"ARVE\",G:G)", lastDataRow); index++; sheet.Row(index).Cell(3).FormulaR1C1 = string.Format("=COUNTIF(H:H,\"CMU_GE\")+COUNTIF(H:H,\"CMU_AUTRESUISSE\")+COUNTIF(H:H,\"CMU_IDS\")+COUNTIF(H:H,\"CMU_ILLRERO\")+COUNTIF(H:H,\"CMU_NEBIS\")+COUNTIF(H:H,\"CMU_RENOUVAUD\")+COUNTIF(H:H,\"CMU_NEBIS\")+COUNTIF(H:H,\"ARVE-GE\")+COUNTIF(H:H,\"ARVE_AUTRESUISSE\")+COUNTIF(H:H,\"ARVE-IDS\")+COUNTIF(H:H,\"ARVE-ILLRERO\")+COUNTIF(H:H,\"ARVE-NEBIS\")+COUNTIF(H:H,\"ARVE-RENOUVAUD\")", lastDataRow); sheet.Row(index).Cell(4).Value = "commandes Suisse"; sheet.Row(index).Cell(7).Style.NumberFormat.Format = "#,##0.00"; sheet.Row(index).Cell(7).FormulaR1C1 = string.Format("=SUMIF(H:H,\"CMU_GE\",G:G)+SUMIF(H:H,\"CMU_AUTRESUISSE\",G:G)+SUMIF(H:H,\"CMU_IDS\",G:G)+SUMIF(H:H,\"CMU_ILLRERO\",G:G)+SUMIF(H:H,\"CMU_NEBIS\",G:G)+SUMIF(H:H,\"CMU_RENOUVAUD\",G:G)+SUMIF(H:H,\"CMU_NEBIS\",G:G)+SUMIF(H:H,\"ARVE-GE\",G:G)+SUMIF(H:H,\"ARVE_AUTRESUISSE\",G:G)+SUMIF(H:H,\"ARVE-IDS\",G:G)+SUMIF(H:H,\"ARVE-ILLRERO\",G:G)+SUMIF(H:H,\"ARVE-NEBIS\",G:G)+SUMIF(H:H,\"ARVE-RENOUVAUD\",G:G)", lastDataRow); index++; // sheet.Row(index).Cell(3).FormulaR1C1 = string.Format("=COUNTIF(H:H,\"SUBITO\")", lastDataRow); // sheet.Row(index).Cell(4).Value = "commandes Subito (All.)"; // sheet.Row(index).Cell(7).Style.NumberFormat.Format = "#,##0.00"; // sheet.Row(index).Cell(7).FormulaR1C1 = string.Format("=SUMIF(H:H,\"SUBITO\",G:G)", lastDataRow); // // index++; sheet.Row(index).Cell(3).FormulaR1C1 = string.Format("=COUNTIF(H:H,\"CMU_ETRANGER\")+COUNTIF(H:H,\"CMU_BL\")+COUNTIF(H:H,\"CMU_NLM\")+COUNTIF(H:H,\"CMU_SUBITO\")+COUNTIF(H:H,\"CMU_SUDOC\")+COUNTIF(H:H,\"ARVE-ETRANGER\")+COUNTIF(H:H,\"ARVE-NLM\")+COUNTIF(H:H,\"ARVE-SUBITO\")+COUNTIF(H:H,\"ARVE-SUDOC\")", lastDataRow); sheet.Row(index).Cell(4).Value = "commandes Etranger"; sheet.Row(index).Cell(7).Style.NumberFormat.Format = "#,##0.00"; sheet.Row(index).Cell(7).FormulaR1C1 = string.Format("=SUMIF(H:H,\"CMU_ETRANGER\",G:G)+SUMIF(H:H,\"CMU_BL\",G:G)+SUMIF(H:H,\"CMU_NLM\",G:G)+SUMIF(H:H,\"CMU_SUBITO\",G:G)+SUMIF(H:H,\"CMU_SUDOC\",G:G)+SUMIF(H:H,\"ARVE-ETRANGER\",G:G)+SUMIF(H:H,\"ARVE-NLM\",G:G)+SUMIF(H:H,\"ARVE-SUBITO\",G:G)+SUMIF(H:H,\"ARVE-SUDOC\",G:G)", lastDataRow); index++; sheet.Row(index).Cell(3).FormulaR1C1 = string.Format("=COUNTIF(H:H,\"CMU_OA\")+COUNTIF(H:H,\"ARVE_OA\")", lastDataRow); sheet.Row(index).Cell(4).Value = "Open Access online"; sheet.Row(index).Cell(7).Style.NumberFormat.Format = "#,##0.00"; sheet.Row(index).Cell(7).FormulaR1C1 = string.Format("=SUMIF(H:H,\"CMU_OA\",G:G)+SUMIF(H:H,\"ARVE_OA\",G:G)", lastDataRow); sheet.Row(index).Cells(3, 7).Style.Border.BottomBorder = XLBorderStyleValues.Thick; index++; sheet.Row(index).Cells(3, 7).Style.Font.Bold = true; sheet.Row(index).Cell(3).FormulaA1 = string.Format("=SUM(C{0}:C{1})", index - 5, index - 1); sheet.Row(index).Cell(7).Style.NumberFormat.Format = "#,##0.00"; sheet.Row(index).Cell(7).FormulaA1 = string.Format("=SUM(G{0}:G{1})", index - 5, index - 1); }
private static void AppendArray(IXLWorksheet worksheet, string[] headers) { for (int index = 0; index < headers.Length; index++) { worksheet.Row(1).Cell(index + 1).Value = headers[index]; } }
private static void AddOrdersWorksheet(IXLWorksheet sheet, IList <Order> orders) { var cell = 1; sheet.Row(1).Cell(cell++).Value = "Compte"; sheet.Row(1).Cell(cell++).Value = "Nom"; sheet.Row(1).Cell(cell++).Value = "Prénom"; sheet.Row(1).Cell(cell++).Value = "Email"; sheet.Row(1).Cell(cell++).Value = "N° commande"; sheet.Row(1).Cell(cell++).Value = "Date commande"; sheet.Row(1).Cell(cell++).Value = "Date envoi"; sheet.Row(1).Cell(cell++).Value = "Prix"; sheet.Row(1).Cell(cell++).Value = "Localisation"; sheet.Row(1).Cell(cell++).Value = "Type Doc"; sheet.Row(1).Cell(cell++).Value = "Périodique"; sheet.Row(1).Cell(cell++).Value = "Année"; sheet.Row(1).Cell(cell++).Value = "Vol"; sheet.Row(1).Cell(cell++).Value = "pp."; var index = 2; sheet.ColumnWidth = 11; foreach (var order in orders) { sheet.Row(index).Cell(1).Value = order.InvoiceAccount; sheet.Row(index).Cell(2).Value = order.Name; sheet.Row(index).Cell(3).Value = order.FirstName; sheet.Row(index).Cell(4).Value = order.EMail; sheet.Row(index).Cell(5).Value = order.IlLinkId; sheet.Row(index).Cell(6).Style.DateFormat.Format = "dd.MM.yyyy"; sheet.Row(index).Cell(6).Value = order.OrderDate; sheet.Row(index).Cell(7).Style.DateFormat.Format = "dd.MM.yyyy"; sheet.Row(index).Cell(7).Value = order.SendDate; sheet.Row(index).Cell(8).Style.NumberFormat.Format = "#,##0.00"; sheet.Row(index).Cell(8).Value = order.Price; sheet.Row(index).Cell(9).Value = order.Localisation; sheet.Row(index).Cell(10).Value = order.DocumentType; sheet.Row(index).Cell(11).Value = order.Title; sheet.Row(index).Cell(12).Value = order.Year; sheet.Row(index).Cell(13).Value = order.Volume; sheet.Row(index).Cell(14).Value = order.Pages; index++; } var tablename = $"Table_{sheet.Name}"; var table = sheet.Range(sheet.FirstColumn().FirstCell(), sheet.LastRowUsed().LastCellUsed()).CreateTable(tablename); table.ShowAutoFilter = true; table.Theme = XLTableTheme.TableStyleMedium2; }
private static void AddDetailsWorksheet(IXLWorksheet sheet, IList <Order> orders, DateTime startDate, DateTime endDate) { sheet.PageSetup.PageOrientation = XLPageOrientation.Landscape; sheet.PageSetup.PaperSize = XLPaperSize.A4Paper; var cell = 1; sheet.Row(1).Cell(cell++).Value = "Compte"; sheet.Row(1).Cell(cell++).Value = "Nom"; sheet.Row(1).Cell(cell++).Value = "Prénom"; sheet.Row(1).Cell(cell++).Value = "Email"; sheet.Row(1).Cell(cell++).Value = "N° commande"; sheet.Row(1).Cell(cell++).Value = "Date envoi"; sheet.Row(1).Cell(cell++).Value = "Prix"; sheet.Row(1).Cell(cell++).Value = "Localisation"; sheet.Row(1).Cell(cell++).Value = "Type Doc"; sheet.Row(1).Cell(cell++).Value = "Périodique"; sheet.Row(1).Cell(cell++).Value = "Année"; sheet.Row(1).Cell(cell++).Value = "Vol"; sheet.Row(1).Cell(cell++).Value = "pp."; var index = 2; sheet.ColumnWidth = 11; foreach (var order in orders) { sheet.Row(index).Cell(1).Value = order.InvoiceAccount; sheet.Row(index).Cell(2).Value = order.Name; sheet.Row(index).Cell(3).Value = order.FirstName; sheet.Row(index).Cell(4).Value = order.EMail; sheet.Row(index).Cell(5).Value = order.IlLinkId; sheet.Row(index).Cell(6).Style.DateFormat.Format = "dd.MM.yyyy"; sheet.Row(index).Cell(6).Value = order.SendDate; sheet.Row(index).Cell(7).Style.NumberFormat.Format = "#,##0.00"; sheet.Row(index).Cell(7).Value = order.Price; sheet.Row(index).Cell(8).Value = order.Localisation; sheet.Row(index).Cell(9).Value = order.DocumentType; sheet.Row(index).Cell(10).Value = order.Title; sheet.Row(index).Cell(11).Value = order.Year; sheet.Row(index).Cell(12).Value = order.Volume; sheet.Row(index).Cell(13).Value = order.Pages; index++; } var lastDataRow = index - 1; var tablename = $"Table_{sheet.Name}"; var table = sheet.Range(1, 1, index - 1, 13).CreateTable(tablename); table.ShowAutoFilter = false; table.Theme = XLTableTheme.TableStyleMedium2; sheet.Row(index).Cell(1).FormulaA1 = $"=COUNT(G2:G{lastDataRow})"; sheet.Row(index).Cell(1).AddToNamed("Count"); sheet.Row(index).Cell(2).Value = string.Format("commandes du {0:dd MMMM} au {1:dd MMMM yyyy}", startDate, endDate); sheet.Row(index).Cell(6).Value = "Total:"; sheet.Row(index).Cell(7).Style.NumberFormat.Format = "#,##0.00"; sheet.Row(index).Cell(7).FormulaA1 = $"=SUM(G2:G{lastDataRow})"; sheet.Range(index, 1, index, 1).AddToNamed($"_{sheet.Name}_COUNT", XLScope.Worksheet); sheet.Range(index, 7, index, 7).AddToNamed($"_{sheet.Name}_PRICE", XLScope.Worksheet); sheet.LastRowUsed().Cells(1, 13).Style.Border.BottomBorder = XLBorderStyleValues.Thick; sheet.LastRowUsed().Cells(1, 13).Style.Font.Bold = true; // Add table if more than 10 order or more than 1 location if (orders.Count >= 10 || orders.Select(o => o.Localisation).Distinct().Count() != 1) { AddSummaryTable(sheet, index, lastDataRow); } }
public ActionResult ExportDataSetToExcel(DataSet dsExcelData, string sheetName1, string sheetName2, string fileNameWithoutExtension, bool totalRequired = true, bool passwordRequired = true) { using (XLWorkbook wb = new XLWorkbook()) { //sheetName1 = sheetName1.Replace(" ", "_"); //sheetName2 = sheetName2.Replace(" ", "_"); fileNameWithoutExtension = fileNameWithoutExtension.Replace(" ", "_"); //if (dsExcelData.Tables[0].Rows.Count > 0) //{ dsExcelData.Tables[0].TableName = sheetName1; IXLWorksheet workSheet = wb.Worksheets.Add(dsExcelData.Tables[0]); IXLRow row = workSheet.Row(dsExcelData.Tables[0].Rows.Count + 1); IXLRange range = workSheet.RangeUsed(); IXLTable xlTable = range.AsTable(); if (totalRequired) { string colLetterForSNo = GetExcelColumnLetter(xlTable, "#"); if (!string.IsNullOrEmpty(colLetterForSNo)) { row.Cell(colLetterForSNo).Value = string.Empty; } string colLetterForDaysPresent = GetExcelColumnLetter(xlTable, "Days Present"); if (!string.IsNullOrEmpty(colLetterForDaysPresent)) { row.Cell(colLetterForDaysPresent).Value = string.Empty; } } if (passwordRequired) { wb.Worksheet(sheetName1).Protect("123456"); } //} //if (dsExcelData.Tables[1].Rows.Count > 0) //{ dsExcelData.Tables[1].TableName = sheetName2; IXLWorksheet workSheet2 = wb.Worksheets.Add(dsExcelData.Tables[1]); IXLRow row2 = workSheet2.Row(dsExcelData.Tables[1].Rows.Count + 1); IXLRange range2 = workSheet2.RangeUsed(); IXLTable xlTable2 = range2.AsTable(); if (totalRequired) { string colLetterForSNo = GetExcelColumnLetter(xlTable2, "#"); if (!string.IsNullOrEmpty(colLetterForSNo)) { row2.Cell(colLetterForSNo).Value = string.Empty; } string colLetterForDaysPresent = GetExcelColumnLetter(xlTable2, "Days Present"); if (!string.IsNullOrEmpty(colLetterForDaysPresent)) { row2.Cell(colLetterForDaysPresent).Value = string.Empty; } } if (passwordRequired) { wb.Worksheet(sheetName2).Protect("123456"); } //} wb.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; wb.Style.Font.Bold = true; Response.Clear(); Response.Buffer = true; Response.Charset = ""; Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; Response.AddHeader("content-disposition", "attachment;filename= " + fileNameWithoutExtension + ".xlsx"); using (MemoryStream MyMemoryStream = new MemoryStream()) { wb.SaveAs(MyMemoryStream); MyMemoryStream.WriteTo(Response.OutputStream); Response.Flush(); Response.End(); } return(null); } }
/// <summary> /// In bảng thống kê toàn trường /// </summary> /// <param name="sheet">sheet</param> /// <param name="indexs">danh sách index hàng chứa tổng hợp các khối</param> /// <param name="rowStart">chỉ số hàng bắt đầu in</param> /// <param name="columns">danh sách cột</param> /// <returns>chỉ số hàng tiếp theo có thể được In tiếp dữ liệu</returns> private List <int> SetToanTruong(IXLWorksheet sheet, List <int> indexs, ref int rowStart, List <ColumnDetail> columns) { List <int> range = new List <int>(); sheet.Row(rowStart).InsertRowsBelow(indexs.Count + 1); int rowBegin = Convert.ToInt32(rowStart.ToString()); string[] khoi = new string[] { "6", "7", "8", "9" }; for (int j = 0; j < indexs.Count; j++) { for (int i = 1; i <= columns.Count; i++) { var cell = sheet.Cell(rowStart, i); cell.Style.Font.Bold = false; cell.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; #region Thiết lập cột switch (columns[i - 1].FieldName) { case "TEN_TRUONG": cell.Value = khoi[j]; break; case "SO_LUONGHS": cell.FormulaA1 = string.Format("=SUM({0},{1},{2},{3},{4})", sheet.Cell(rowStart, 3).Address.ToString(), sheet.Cell(rowStart, 5).Address.ToString(), sheet.Cell(rowStart, 7).Address.ToString(), sheet.Cell(rowStart, 9).Address.ToString(), sheet.Cell(rowStart, 11).Address.ToString()); break; case "GIOI_TS": cell.FormulaA1 = string.Format("={0}", sheet.Cell(indexs[j], 3).Address.ToString()); break; case "GIOI_TL": cell.FormulaA1 = string.Format("=IF({0},{0}/${1},0)", sheet.Cell(rowStart, 3).Address.ToString(), sheet.Cell(rowStart, 2).Address.ToString()); break; case "KHA_TS": cell.FormulaA1 = string.Format("={0}", sheet.Cell(indexs[j], 5).Address.ToString()); break; case "KHA_TL": cell.FormulaA1 = string.Format("=IF({0},{0}/${1},0)", sheet.Cell(rowStart, 3).Address.ToString(), sheet.Cell(rowStart, 2).Address.ToString()); break; case "TRUNGBINH_TS": cell.FormulaA1 = string.Format("={0}", sheet.Cell(indexs[j], 7).Address.ToString()); break; case "TRUNGBINH_TL": cell.FormulaA1 = string.Format("=IF({0},{0}/${1},0)", sheet.Cell(rowStart, 3).Address.ToString(), sheet.Cell(rowStart, 2).Address.ToString()); break; case "YEU_TS": cell.FormulaA1 = string.Format("={0}", sheet.Cell(indexs[j], 9).Address.ToString()); break; case "YEU_TL": cell.FormulaA1 = string.Format("=IF({0},{0}/${1},0)", sheet.Cell(rowStart, 3).Address.ToString(), sheet.Cell(rowStart, 2).Address.ToString()); break; case "KEM_TS": cell.FormulaA1 = string.Format("={0}", sheet.Cell(indexs[j], 11).Address.ToString()); break; case "KEM_TL": cell.FormulaA1 = string.Format("=IF({0},{0}/${1},0)", sheet.Cell(rowStart, 3).Address.ToString(), sheet.Cell(rowStart, 2).Address.ToString()); break; case "TBTROLEN_TS": cell.FormulaA1 = string.Format("=SUM({0},{1},{2})", sheet.Cell(rowStart, 3).Address.ToString(), sheet.Cell(rowStart, 5).Address.ToString(), sheet.Cell(rowStart, 7).Address.ToString()); break; case "TBTROLEN_TL": cell.FormulaA1 = string.Format("=IF({0},{0}/${1},0)", sheet.Cell(rowStart, 13).Address.ToString(), sheet.Cell(rowStart, 2).Address.ToString()); break; case "TBTROLEN_XEP_HANG": cell.FormulaA1 = cell.FormulaA1 = string.Format("=RANK({0},{1}:{2},0)", sheet.Cell(rowStart, 14).Address.ToString(), this.GetAddressString(sheet.Cell(rowBegin, 14), true), this.GetAddressString(sheet.Cell(rowBegin + khoi.Length - 1, 14), true)); break; default: break; } #endregion //set cell type if (columns[i - 1].DataType == XLCellValues.Number) { cell.DataType = columns[i - 1].DataType; cell.Style.NumberFormat.NumberFormatId = 10; } } range.Add(rowStart); rowStart++; } return(range); }
private static void ImportTache(IXLWorksheet sheet, ref int line, Tache t, JourEvenement jour, Planning p, FestivArtsContext ctx) { IXLRow timeRow = sheet.Row(line); line++; int maxB = t.GetMaxBenevoleByDay(jour.Id); Regex regex = new Regex("^([0-9]+)"); var aAjouter = new Dictionary <int, HashSet <int> >(); for (int l = 0; l < maxB; l++) { IXLRow r = sheet.Row(line); int i = ExcelUtils.FIRST_PLAN_COLUMN; foreach (CreneauDef d in jour.CreneauDefs.OrderBy(s => s.NoCreneau)) { var cell = r.Cell(i); string benStr = cell.Value.ToString(); if (!string.IsNullOrWhiteSpace(benStr)) { var m = regex.Match(benStr.Trim()); if (m.Success) { int id = int.Parse(m.Groups[0].Captures[0].Value); var b = ctx.Benevoles.FirstOrDefault(s => s.Id == id); if (b == null) { throw new ImportException(string.Format("Cell ({0}) Tache {1} : n° de bénévole introuvable : {2}'", cell.Address.ToStringRelative(true), t.Nom, benStr)); } var c = t.Creneaux.FirstOrDefault(s => s.CreneauDefId == d.Id); if (c == null) { throw new ImportException(string.Format("Cell ({0}) Tache {1} : creneau introuvable. Creneau def {2}'", cell.Address.ToStringRelative(true), t.Nom, d.Id)); } if (!aAjouter.ContainsKey(b.Id)) { aAjouter.Add(b.Id, new HashSet <int>()); } if (!aAjouter[b.Id].Contains(c.Id)) { aAjouter[b.Id].Add(c.Id); } } else { throw new ImportException(string.Format("Cell ({0}) ne correspond pas a un n° de bénévole : {1}'", cell.Address.ToStringRelative(true), benStr)); } } i++; } line++; } if (aAjouter.Count > 0) { var anciensA = ctx.Affectations.Where(s => s.Creneau.CreneauDef.JourId == jour.Id && s.PlanningId == p.Id && s.Creneau.TacheId == t.Id).ToList(); foreach (Affectation a in anciensA) { if (aAjouter.ContainsKey(a.BenevoleId) && aAjouter[a.BenevoleId].Contains(a.CreneauId)) { //L'affectation existait déjà, on ne fait rien aAjouter[a.BenevoleId].Remove(a.CreneauId); } else { //N'existe plus, on la supprime; ctx.Affectations.Remove(a); } } foreach (var kv in aAjouter.Where(s => s.Value.Count > 0).ToList()) { foreach (var v in kv.Value) { Affectation a = new Affectation() { BenevoleId = kv.Key, CreneauId = v, PlanningId = p.Id }; ctx.Affectations.Add(a); } } ctx.SaveChanges(); } }
public static XLWorkbook GetExcel(TableModel[] models) { XLWorkbook wb = new XLWorkbook(); IXLWorksheet sheet = wb.Worksheets.Add("Sheet1"); // ЗАГОЛОВКИ IXLRange range = sheet.Range(sheet.Cell(1, "A"), sheet.Cell(2, "I")); // заливка range.Style.Fill.SetBackgroundColor(XLColor.FromHtml("#fcecdc")); // жирный шрифт range.Style.Font.SetBold(); // выравнивание range.Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center); range.Style.Alignment.SetVertical(XLAlignmentVerticalValues.Center); // ширина заголовков double factor = 1.145; // множитель (чтобы ширина была как у оригинального excel) sheet.Column("A").Width = 24.13 * factor; sheet.Column("B").Width = 33.63 * factor; sheet.Column("C").Width = 11.88 * factor; sheet.Column("D").Width = 9.25 * factor; sheet.Column("E").Width = 10.5 * factor; sheet.Column("F").Width = 52.75 * factor; sheet.Column("G").Width = 8.75 * factor; sheet.Column("H").Width = 8.75 * factor; sheet.Column("I").Width = 33.25 * factor; // подписи у заголовков и объединение ячеек для заголовков sheet.Range(sheet.Cell(1, "A"), sheet.Cell(2, "A")) .Merge().Value = "Component type"; sheet.Range(sheet.Cell(1, "B"), sheet.Cell(2, "B")) .Merge().Value = "Name"; sheet.Range(sheet.Cell(1, "C"), sheet.Cell(2, "C")) .Merge().Value = "Nominal"; sheet.Range(sheet.Cell(1, "D"), sheet.Cell(2, "D")) .Merge().Value = "Deviation"; sheet.Range(sheet.Cell(1, "E"), sheet.Cell(2, "E")) .Merge().Value = "Case type"; sheet.Range(sheet.Cell(1, "F"), sheet.Cell(2, "F")) .Merge().Value = "Comment"; sheet.Range(sheet.Cell(1, "G"), sheet.Cell(1, "H")) .Merge().Value = "quantity"; sheet.Cell(2, "G").Value = "00"; sheet.Cell(2, "H").Value = "01"; sheet.Range(sheet.Cell(1, "I"), sheet.Cell(2, "I")) .Merge().Value = "Remark:"; // границы у заголовков XLBorderStyleValues styleBorder = XLBorderStyleValues.Medium; range.Style.Border.SetOutsideBorder(styleBorder); range.Style.Border.SetInsideBorder(styleBorder); #region ЛОГИКА ВЫВОДА ДАННЫХ int currentRow = 3; IXLCells cellRange; for (int i = 0; i < models.Length; i++) { TableModel tm = models[i]; #region ЛОГИКА ОТДЕЛЕНИЯ ГРУПП КОМПОНЕНТОВ // чтобы не выйти за пределы массива if (i > 0 && tm.ComponentType != models[i - 1].ComponentType) { range = sheet.Range(sheet.Cell(currentRow, "A"), sheet.Cell(currentRow, "i")).Merge(); // границы range.Style.Border.SetOutsideBorder(XLBorderStyleValues.Medium); // заливка range.Style.Fill.SetBackgroundColor(XLColor.FromHtml("#f8f4f4")); currentRow++; } #endregion cellRange = sheet.Row(currentRow).Cells("A", "I"); // выравнивание текста cellRange.Style.Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center); cellRange.Style.Alignment.SetVertical(XLAlignmentVerticalValues.Center); // границы styleBorder = XLBorderStyleValues.Thin; cellRange.Style.Border.SetInsideBorder(styleBorder); cellRange.Style.Border.SetOutsideBorder(styleBorder); sheet.Cell(currentRow, "A").Value = tm.ComponentType; sheet.Cell(currentRow, "B").Value = tm.Name; sheet.Cell(currentRow, "C").Value = tm.Nominal; sheet.Cell(currentRow, "D").Value = tm.Deviation; sheet.Cell(currentRow, "E").SetValue(tm.CaseType); sheet.Cell(currentRow, "F").Value = tm.Comment; sheet.Cell(currentRow, "G").Value = tm.Quanity.ZeroZero; sheet.Cell(currentRow, "H").Value = tm.Quanity.ZeroOne; currentRow++; } #endregion // СТИЛИ ДЛЯ ВСЕХ ЯЧЕЕК cellRange = sheet.CellsUsed(); // шрифт cellRange.Style.Font.SetFontName("Arial Cyr"); cellRange.Style.Font.SetFontSize(9); return(wb); }
private static IXLRow FindRowById(string id, IXLWorksheet worksheet) { var lastRowUsed = worksheet.LastRowUsed().RowNumber(); for (int rowIndex = 0; rowIndex <= lastRowUsed; rowIndex++) { if (worksheet.Row(rowIndex).Cell(1).GetValue<string>().ToLower() == id.ToLower()) { return worksheet.Row(rowIndex); } } return null; }
private int GetColumnNumber(String sheetName, String colName) { worksheet = workbook.Worksheet(sheetName); IXLRow r = worksheet.Row(1); while (r != worksheet.LastRowUsed()) { foreach(IXLCell c in r.CellsUsed()) { if (c.Value.Equals(colName)) return c.Address.ColumnNumber; // Starts with 0 } r = r.RowBelow(); } return -1; }
public void AddContainerInfo(IXLWorksheet ws, Container container) { ws.Cell("A1").SetValue(container.ExporterName).Style.Font.FontSize = 20; ws.Range("A1:E1").Merge(); ws.Cell("A2").SetValue(container.ExporterAddress).Style.Alignment.WrapText = true; ws.Range("A2:B3").Merge(); ws.Cell("A5").SetValue("Shipped Per"); ws.Cell("B5").SetValue(container.ShippedPer); ws.Range("B5:C5").Merge(); ws.Cell("A6").SetValue("On/About"); ws.Cell("B6").SetValue(container.OnAbout); ws.Range("B6:C6").Merge(); ws.Cell("A7").SetValue("From"); ws.Cell("B7").SetValue(container.From); // ws.Row(7) // .Style // .Alignment.SetVertical(XLAlignmentVerticalValues.Top) // .Alignment.SetWrapText(true); ws.Range("B7:C7").Merge(); ws.Row(7).Height = 70; ws.Cell("A8").SetValue("Airway Bill No. \nor B/L No."); ws.Cell("B8").SetValue(container.AirwayBillNumber); ws.Range("B8:C8").Merge(); ws.Row(8).Height = 30; // ws.Row(8).Style.Alignment.SetWrapText(true); ws.Cell("A9").SetValue("Letter of\nCredit No."); ws.Cell("B9").SetValue(container.LetterOfCreditNumber); ws.Range("B9:C9").Merge(); ws.Row(9).Height = 30; ws.Cell("A10").SetValue("Drawn Under"); ws.Cell("B10").SetValue(container.DrawnUnder) .Style .Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center); // .Alignment.SetWrapText(true); ws.Range("B10:C10").Merge(); ws.Row(10).Height = 70; // ws.Row(10).Style.Alignment.SetVertical(XLAlignmentVerticalValues.Top); ws.Range("A1:A10").Style.Font.Bold = true; ws.Range("B5:C10").Style.Border.BottomBorder = XLBorderStyleValues.Thin; // ws.Range("B5:C10").Style.Alignment.WrapText = true; ws.Rows("5:10").Style.Alignment.SetWrapText(true) .Alignment.SetVertical(XLAlignmentVerticalValues.Top); //Importer ws.Cell("E5").SetValue(container.ImporterName + "\n" + container.ImporterAddress + "\n" + "(TAX CERTIFICATE NO. " + container.ImporterTaxCertificateNumber + ")") .Style .Alignment.SetVertical(XLAlignmentVerticalValues.Top) .Alignment.SetWrapText(); ws.Range("E5:H10").Merge().Style.Border.OutsideBorder = XLBorderStyleValues.Medium; //Container Number + Container Date ws.Cell("F2").SetValue("INVOICE NO:"); ws.Cell("G2").SetValue(container.CustomsInvoiceNumber); ws.Cell("F3").SetValue("DATE:"); ws.Cell("G3").SetValue(container.Date); ws.Range("F2:F3").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Right; ws.Range("G2:H2").Merge(); ws.Range("G3:H3").Merge(); ws.Range("G2:G3").Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left; ws.Range("F2:H3").Style.Font.Bold = true; }
private void AddPerformanceTitles(IXLWorksheet performanceSheet) { //global styles performanceSheet.Style.Font.FontName = "verdana"; performanceSheet.Style.Font.FontSize = 10d; //headings performanceSheet.Cell(1, 1).Value = "Date"; performanceSheet.Cell(1, 2).Value = "Comment"; performanceSheet.Cell(1, 3).Value = "DAU"; performanceSheet.Cell(1, 4).Value = "Q Users"; performanceSheet.Cell(1, 5).Value = "% Users"; performanceSheet.Cell(1, 6).Value = "MAU"; performanceSheet.Cell(1, 7).Value = "Q users"; performanceSheet.Cell(1, 8).Value = "% users"; performanceSheet.Cell(1, 9).Value = "DAU/MAU"; performanceSheet.Cell(1, 10).Value = "DAU INDEX"; performanceSheet.Cell(1, 11).Value = "Instalaciones"; performanceSheet.Cell(1, 12).Value = "K Factor (overall)"; performanceSheet.Cell(1, 13).Value = "K Factor invite"; performanceSheet.Cell(1, 14).Value = "K Notification"; performanceSheet.Cell(1, 15).Value = "Viral Installs"; performanceSheet.Cell(1, 16).Value = "MKT Installs"; performanceSheet.Cell(1, 17).Value = "Visits"; performanceSheet.Cell(1, 18).Value = "Daily Sessions"; performanceSheet.Cell(1, 19).Value = "Time on site"; performanceSheet.Cell(1, 20).Value = "Daily Revenue"; performanceSheet.Cell(1, 21).Value = "ARP DAU"; performanceSheet.Cell(1, 22).Value = "ARP MAU"; performanceSheet.Cell(1, 23).Value = "ARP PU"; performanceSheet.Cell(1, 24).Value = "Pay Users"; performanceSheet.Cell(1, 25).Value = "% Pay. Users"; performanceSheet.Cell(1, 26).Value = "LTV"; performanceSheet.Cell(1, 27).Value = "CPA"; performanceSheet.Cell(1, 28).Value = "LVL 1"; performanceSheet.Cell(1, 29).Value = "%DAU"; performanceSheet.Cell(1, 30).Value = "LVL 2"; performanceSheet.Cell(1, 31).Value = "%DAU"; performanceSheet.Cell(1, 32).Value = "% Pasaje"; performanceSheet.Cell(1, 33).Value = "LVL 3"; performanceSheet.Cell(1, 34).Value = "%DAU"; performanceSheet.Cell(1, 35).Value = "% Pasaje"; performanceSheet.Cell(1, 36).Value = "LVL 4"; performanceSheet.Cell(1, 37).Value = "%DAU"; performanceSheet.Cell(1, 38).Value = "% Pasaje"; performanceSheet.Cell(1, 39).Value = "LVL5"; performanceSheet.Cell(1, 40).Value = "%DAU"; performanceSheet.Cell(1, 41).Value = "% Pasaje"; performanceSheet.Cell(1, 42).Value = "Weekly Average"; performanceSheet.Cell(1, 43).Value = "Monthly Average"; //row one styles performanceSheet.Row(1).Style.Font.Bold = true; }
public static IEnumerable<TranslationColumn> ReadCultures(IXLWorksheet worksheet) { var textValues = worksheet.Row(1).Cells().Where(cell => cell.Value != null).Select(cell => cell.Value.ToString()).ToList<String>(); var cols = textValues.Skip(2).Where(s => !s.Contains("[Comments]")).ToList<String>(); var list = new List<TranslationColumn>(); foreach (var s in cols) { try { var textColumn = new TranslationColumn(new CultureInfo(s)); textColumn.TextColumnIndex = textValues.IndexOf(s); string commentsKey = s; if (s != "") commentsKey += " [Comments]"; else commentsKey += "[Comments]"; var commentColumn = textValues.Skip(2).Where(t => t.Equals(commentsKey)).FirstOrDefault(); if (commentColumn != null) textColumn.CommentColumnIndex = textValues.IndexOf(commentColumn); list.Add(textColumn); } catch (CultureNotFoundException) { } } return list; }
public int GetColumnCount(string sheetName, int rowNumber) { worksheet = workbook.Worksheet(sheetName); return worksheet.Row(rowNumber).CellsUsed().Count(); }
private void CreateVerticalNumberHeader(IXLWorksheet workSheet, string HeadLineText, int LineNumber, int StartColumnIndex) { workSheet.Cell(LineNumber, StartColumnIndex).Style .Font.SetBold() .Font.SetFontSize(9) .Alignment.SetHorizontal(XLAlignmentHorizontalValues.Left) .Alignment.SetTextRotation(90); workSheet.Cell(LineNumber, StartColumnIndex).Style .Alignment.WrapText = true; workSheet.Cell(LineNumber, StartColumnIndex).Value = HeadLineText; workSheet.Row(LineNumber).Height = 75; workSheet.Column(StartColumnIndex).Width = 4; }
private static void SetRows(IXLWorksheet ws) { ws.Cell(10, 1).Value = Model.NameOfMaterial; ws.Cell(20, 43).Value = Model.NameOfMaterial; var rowCount = Model.ResultTemperaturesModels.Count; if (rowCount > 1) { ws.Row(20).InsertRowsBelow(rowCount - 1); ws.Range(20, 43, 19 + rowCount, 44).Merge(); ws.Row(10).InsertRowsBelow(rowCount - 1); ws.Range(10, 1, 9 + rowCount, 1).Merge(); } }
private void AddFacebookInsight(IXLWorksheet performanceSheet, int n, FacebookInsights insight) { performanceSheet.Cell(n, 1).Value = insight.Date; //Date performanceSheet.Cell(n, 3).Value = insight.DailyActiveUsers; //DAU performanceSheet.Cell(n, 4).FormulaA1 = string.Format("C{0}-C{1}", n, n + 1); //Q Users performanceSheet.Cell(n, 5).FormulaA1 = string.Format("IF(C{0}=0,0, D{1}/C{2})", n + 1, n, n + 1); //% Users performanceSheet.Cell(n, 6).Value = insight.MonthlyActiveUsers; //MAU performanceSheet.Cell(n, 7).FormulaA1 = string.Format("F{0}-F{1}", n, n + 1); //Q users performanceSheet.Cell(n, 8).FormulaA1 = string.Format("IF(F{0}=0,0,G{1}/F{2})", n + 1, n, n + 1); //% Users performanceSheet.Cell(n, 9).FormulaA1 = string.Format("IF(F{0}=0,0,C{1}/F{2})", n, n, n); //DAU/MAU performanceSheet.Cell(n, 10).FormulaA1 = string.Format("IF(G{0}=0,0,D{1}/G{2})", n, n, n); //DAU INDEX performanceSheet.Cell(n, 11).Value = insight.DailyAppInstalls; //Instalaciones var virality = repository.Get<Virality, DateTime>(insight.Date); if (virality == null) virality = new Virality(); performanceSheet.Cell(n, 12).Value = virality.Total; //K Factor (overall) performanceSheet.Cell(n, 13).Value = virality.Invite; //K Factor invite performanceSheet.Cell(n, 14).Value = virality.Notification; //K Notification performanceSheet.Cell(n, 15).Value = virality.TotalInstallations; //Viral Installs performanceSheet.Cell(n, 16).FormulaA1 = string.Format("K{0}-O{0}", n, n); //MKT Installs var analytics = repository.Get<Analytics, DateTime>(insight.Date); if (analytics == null) analytics = new Analytics(); performanceSheet.Cell(n, 17).Value = analytics.Visits; //Visits performanceSheet.Cell(n, 18).FormulaA1 = string.Format("IF(C{0}=0,0,Q{1}/C{2})", n, n, n); //Daily Sessions TimeSpan timeOnSite = new TimeSpan(0, 0, analytics.TimeOnSite); performanceSheet.Cell(n, 19).Value = timeOnSite.ToString(); //Time on site var revenue = repository.Get<Revenue, DateTime>(insight.Date); if (revenue == null) revenue = new Revenue(); performanceSheet.Cell(n, 20).Value = revenue.TotalRevenue; //Daily Revenue performanceSheet.Cell(n, 21).FormulaA1 = string.Format("IF(C{0}=0,0,T{1}/C{2})", n, n, n); //ARP DAU performanceSheet.Cell(n, 22).FormulaA1 = string.Format("IF(F{0}=0,0,T{1}/F{2})", n, n, n); //ARP MAU performanceSheet.Cell(n, 23).FormulaA1 = string.Format("IF(X{0}=0,0, T{1}/X{2})", n, n, n); //ARP PU performanceSheet.Cell(n, 24).Value = revenue.TransactionCount; ///Pay Users performanceSheet.Cell(n, 25).FormulaA1 = string.Format("X{0}/C{0}", n, n); //% Pay. Users performanceSheet.Cell(n, 26).FormulaA1 = string.Format("L{0}*T{0}*I{0}", n, n); //LTV performanceSheet.Cell(n, 27).Value = string.Empty; //CPA performanceSheet.Cell(n, 28).FormulaA1 = "K" + n.ToString(); // Level 1 performanceSheet.Cell(n, 29).FormulaA1 = string.Format("IF(C{0}=0,0,AB{1}/C{2})", n, n, n); //%DAU performanceSheet.Cell(n, 30).Value = analytics.Level2; //Level 2 performanceSheet.Cell(n, 31).FormulaA1 = string.Format("IF(C{0}=0,0,AD{1}/C{2})", n, n, n); //%DAU performanceSheet.Cell(n, 32).FormulaA1 = string.Format("IF(AB{0}=0,0,AD{1}/AB{2})", n, n, n); //% Pasaje performanceSheet.Cell(n, 33).Value = analytics.Level3; //Level 3 performanceSheet.Cell(n, 34).FormulaA1 = string.Format("IF(C{0}=0,0,AG{1}/C{2})", n, n, n); //%DAU performanceSheet.Cell(n, 35).FormulaA1 = string.Format("IF(AD{0}=0,0,AG{1}/AD{2})", n, n, n); //% Pasaje performanceSheet.Cell(n, 36).Value = analytics.Level4; //Level 4 performanceSheet.Cell(n, 37).FormulaA1 = string.Format("IF(C{0}=0,0,AJ{1}/C{2})", n, n, n); //%DAU performanceSheet.Cell(n, 38).FormulaA1 = string.Format("IF(AG{0}=0,0,AJ{1}/AG{2})", n, n, n); //% Pasaje performanceSheet.Cell(n, 39).Value = analytics.Level5; //Level 5 performanceSheet.Cell(n, 40).FormulaA1 = string.Format("IF(C{0}=0,0,AM{1}/C{2})", n, n, n); //%DAU performanceSheet.Cell(n, 41).FormulaA1 = string.Format("IF(AJ{0}=0,0,AM{1}/AJ{2})", n, n, n); //% Pasaje performanceSheet.Cell(n, 42).Value = string.Empty; //Weekly Average performanceSheet.Cell(n, 43).Value = string.Empty; //Monthly Average if (insight.Date.DayOfWeek == DayOfWeek.Saturday || insight.Date.DayOfWeek == DayOfWeek.Sunday) { performanceSheet.Row(n).Style.Fill.BackgroundColor = XLColor.Yellow; } }