Exemplo n.º 1
0
        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);
            }
        }
Exemplo n.º 2
0
        /// <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);
        }
Exemplo n.º 3
0
        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;
            }
        }
Exemplo n.º 4
0
 /// <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);
        }
Exemplo n.º 6
0
 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);
     }
 }
Exemplo n.º 7
0
        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);
        }
Exemplo n.º 8
0
        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);
        }
Exemplo n.º 9
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);
        }
Exemplo n.º 10
0
        /// <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();
        }
Exemplo n.º 11
0
        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);
            }
        }
Exemplo n.º 12
0
        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);
        }
Exemplo n.º 13
0
        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);
        }
Exemplo n.º 14
0
        ///<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);
        }
Exemplo n.º 15
0
        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;
                    }
                }
            }
        }
Exemplo n.º 16
0
        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);
            }
        }
Exemplo n.º 17
0
        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);
            }
        }
Exemplo n.º 18
0
        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());
            }
        }
Exemplo n.º 19
0
 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);
     }
 }
Exemplo n.º 20
0
        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();
        }
Exemplo n.º 21
0
        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();
        }
Exemplo n.º 22
0
        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);
            }
        }
Exemplo n.º 23
0
    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);
    }
Exemplo n.º 24
0
        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;
        }
Exemplo n.º 25
0
        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);
        }
Exemplo n.º 26
0
 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];
     }
 }
Exemplo n.º 27
0
        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;
        }
Exemplo n.º 28
0
        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);
            }
        }
Exemplo n.º 30
0
        /// <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);
        }
Exemplo n.º 31
0
        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();
            }
        }
Exemplo n.º 32
0
        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;
        }
Exemplo n.º 36
0
 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;
 }
Exemplo n.º 37
0
            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();
 }
Exemplo n.º 39
0
		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;
			}
Exemplo n.º 40
0
        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();
            }
        }
Exemplo n.º 41
0
        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;
            }
        }