Beispiel #1
0
        public void Create(String filePath)
        {
            var workbook = new XLWorkbook();
            var ws       = workbook.AddWorksheet("Sheet1");

            ws.Cell(2, 1).SetValue(1)
            .CellRight().SetValue(1)
            .CellRight().SetValue(2)
            .CellRight().SetValue(3);

            var range = ws.RangeUsed();

            range.AddConditionalFormat().WhenEquals("1").Font.SetBold();
            range.InsertRowsAbove(1);

            workbook.SaveAs(filePath);
        }
Beispiel #2
0
        public void DateAgainstStringComparison()
        {
            using (var wb = new XLWorkbook())
            {
                var ws = wb.AddWorksheet("Sheet1");
                ws.Cell("A1").Value    = new DateTime(2016, 1, 1);
                ws.Cell("A1").DataType = XLCellValues.DateTime;

                ws.Cell("A2").FormulaA1 = @"=IF(A1 = """", ""A"", ""B"")";
                var actual = ws.Cell("A2").Value;
                Assert.AreEqual(actual, "B");

                ws.Cell("A3").FormulaA1 = @"=IF("""" = A1, ""A"", ""B"")";
                actual = ws.Cell("A3").Value;
                Assert.AreEqual(actual, "B");
            }
        }
Beispiel #3
0
        public void Create(String filePath)
        {
            var workbook = new XLWorkbook();
            var ws       = workbook.AddWorksheet("Sheet1");

            ws.FirstCell().SetValue(1)
            .CellBelow().SetValue(1)
            .CellBelow().SetValue(2)
            .CellBelow().SetValue(3);

            ws.RangeUsed().AddConditionalFormat().IconSet(XLIconSetStyle.ThreeTrafficLights2, true, true)
            .AddValue(XLCFIconSetOperator.EqualOrGreaterThan, "0", XLCFContentType.Number)
            .AddValue(XLCFIconSetOperator.EqualOrGreaterThan, "2", XLCFContentType.Number)
            .AddValue(XLCFIconSetOperator.EqualOrGreaterThan, "3", XLCFContentType.Number);

            workbook.SaveAs(filePath);
        }
Beispiel #4
0
        public void DataRange_returns_null_if_empty()
        {
            using (var wb = new XLWorkbook())
            {
                IXLWorksheet ws = wb.AddWorksheet("Sheet1");
                ws.FirstCell().SetValue("Categories")
                .CellBelow().SetValue("A")
                .CellBelow().SetValue("B")
                .CellBelow().SetValue("C");

                IXLTable table = ws.RangeUsed().CreateTable();

                ws.Rows("2:4").Delete();

                Assert.IsNull(table.DataRange);
            }
        }
Beispiel #5
0
        /// <summary>
        /// 将 <see cref="DataTable"/> 中的内容以 Excel2007 的格式写入 <see cref="MemoryStream"/>
        /// </summary>
        /// <param name="table">要写入 <see cref="MemoryStream"/> 的 <see cref="DataTable"/> 数据表实例</param>
        /// <returns>一个可读取的 <see cref="MemoryStream"/> 实例</returns>
        /// <exception cref="ArgumentNullException">传入的实例为空</exception>
        static public MemoryStream ToExcel(this DataTable table)
        {
            if (table == null)
            {
                throw new ArgumentNullException(nameof(table));
            }

            var workbook = new XLWorkbook();

            workbook.AddWorksheet(table, "Sheet1");

            MemoryStream excelWrite = new MemoryStream();

            workbook.SaveAs(excelWrite, true);

            return(new MemoryStream(excelWrite.ToArray()));
        }
        //public List<Invoice> ImportShipmentResult(string fromDate, string toDate)
        //{
        //    DataSet dsResult = new DataSet();

        //    using (SqlConnection conn = new SqlConnection("data source=198.1.1.3;uid=sa;password=zy01sy?;database=EDIDATA1"))
        //    {
        //        conn.Open();
        //        var cmd = conn.CreateCommand();
        //        cmd.CommandType = CommandType.Text;
        //        cmd.CommandText = @"select EntryNo As DecNo, InvNo, ImportCD as CompCd, ImportDV as Div,
        //        (select top 1 H5Trad from HD050 where H5INVN=InvNo) as TradCd
        //        from nmb05 where ClearDate >= @fromDate and ClearDate <= @toDate";

        //        cmd.Parameters.AddWithValue("@fromdate", fromDate);
        //        cmd.Parameters.AddWithValue("@todate", toDate);

        //        var da = new SqlDataAdapter(cmd);
        //        da.Fill(dsResult);
        //        conn.Close();
        //    }

        //    var inv = new List<Invoice>();

        //    foreach (DataRow dr in dsResult.Tables[0].Rows)
        //    {
        //        inv.Add(new Invoice
        //        {
        //            InvNO = dr["InvNO"].ToString(),
        //            DecNo = dr["DecNo"].ToString(),
        //            CompCd = dr["CompCd"].ToString(),
        //            DivCd = dr["Div"].ToString(),
        //            TransportType = dr["TradCd"].ToString()
        //        });
        //    }

        //    return inv;
        //}

        public void WriteExcel(List <Invoice> data, string name, string location)
        {
            var workbook  = new XLWorkbook();
            var worksheet = workbook.AddWorksheet(name);

            var range = worksheet.Range(worksheet.Cell(1, 1), worksheet.Cell(1, 4));

            range.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
            range.Style.Font.Bold            = true;
            range.Style.Font.FontName        = "Tahoma";
            range.Style.Font.FontSize        = 10;

            worksheet.Cell(1, 1).Value = "BRANCH";
            worksheet.Cell(1, 2).Value = "DECNO";
            worksheet.Cell(1, 3).Value = "INVOICE";
            worksheet.Cell(1, 4).Value = "STATUS";

            for (int i = 1; i < 4; i++)
            {
                worksheet.Column(i).Width = 15;
                worksheet.Column(i).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Left;
                worksheet.Column(i).Style.Alignment.Vertical   = XLAlignmentVerticalValues.Center;
            }

            int iRow = 2;

            for (int i = 0; i < data.Count; i++)
            {
                var invoice = data.ElementAt(i);
                worksheet.Cell(iRow, 1).Value = invoice.Branch;
                worksheet.Cell(iRow, 2).Value = invoice.DecNo;
                worksheet.Cell(iRow, 3).Value = invoice.InvNO;
                worksheet.Cell(iRow, 4).Value = invoice.Status;

                iRow++;
            }

            var destinationFile = Path.Combine(location, name + ".xlsx");

            if (File.Exists(destinationFile))
            {
                File.Delete(destinationFile);
            }

            workbook.SaveAs(destinationFile);
        }
Beispiel #7
0
        public void Create(String filePath)
        {
            var workbook = new XLWorkbook();
            var ws       = workbook.AddWorksheet("Sheet1");

            ws.FirstCell().SetValue(1)
            .CellBelow().SetValue(1)
            .CellBelow().SetValue(2)
            .CellBelow().SetValue(3)
            .CellBelow().SetValue(4);

            ws.RangeUsed().AddConditionalFormat().DataBar(XLColor.Red, XLColor.Green)
            .LowestValue()
            .HighestValue();

            workbook.SaveAs(filePath);
        }
Beispiel #8
0
        public void Create(String filePath)
        {
            var workbook = new XLWorkbook();
            var ws       = workbook.AddWorksheet("Sheet1");

            using (var range = ws.Range("A1:A10"))
            {
                range.AddConditionalFormat().WhenEquals("3")
                .Fill.SetBackgroundColor(XLColor.Blue);
                range.AddConditionalFormat().WhenEquals("2")
                .Fill.SetBackgroundColor(XLColor.Green);
                range.AddConditionalFormat().WhenEquals("1")
                .Fill.SetBackgroundColor(XLColor.Red);
            }

            workbook.SaveAs(filePath);
        }
Beispiel #9
0
        public void DeleteColumnUsedInNamedRange()
        {
            using (var wb = new XLWorkbook())
            {
                var ws = wb.AddWorksheet("Sheet1");
                ws.FirstCell().SetValue("Column1");
                ws.FirstCell().CellRight().SetValue("Column2").Style.Font.SetBold();
                ws.FirstCell().CellRight(2).SetValue("Column3");
                ws.NamedRanges.Add("MyRange", "A1:C1");

                ws.Column(1).Delete();

                Assert.IsTrue(ws.Cell("A1").Style.Font.Bold);
                Assert.AreEqual("Column3", ws.Cell("B1").GetValue <string>());
                Assert.IsEmpty(ws.Cell("C1").GetValue <string>());
            }
        }
        public void ThenStepAddedSuccessfully()
        {
            var excelStepFormatter = Container.Resolve <ExcelStepFormatter>();
            var step = new Step {
                NativeKeyword = "Given", Name = "I have some precondition"
            };

            using (var workbook = new XLWorkbook())
            {
                IXLWorksheet worksheet = workbook.AddWorksheet("SHEET1");
                int          row       = 5;
                excelStepFormatter.Format(worksheet, step, ref row);

                Check.That(worksheet.Cell("C5").Value).IsEqualTo(step.NativeKeyword);
                Check.That(worksheet.Cell("D5").Value).IsEqualTo(step.Name);
            }
        }
Beispiel #11
0
        public void Create(String filePath)
        {
            var workbook = new XLWorkbook();
            var ws       = workbook.AddWorksheet("Sheet1");

            ws.FirstCell().SetValue(1)
            .CellBelow().SetValue(1)
            .CellBelow().SetValue(2)
            .CellBelow().SetValue(3);

            ws.RangeUsed().AddConditionalFormat().ColorScale()
            .LowestValue(XLColor.Red)
            .Midpoint(XLCFContentType.Percent, "50", XLColor.Yellow)
            .HighestValue(XLColor.Green);

            workbook.SaveAs(filePath);
        }
        public void RemovedRangeExcludedFromIndex()
        {
            using (var wb = new XLWorkbook())
            {
                var ws = wb.AddWorksheet();
                var dv = ws.Range("A1:A3").SetDataValidation();
                dv.MinValue = "100";
                var range = ws.Range("C1:C3");
                dv.AddRange(range);

                dv.RemoveRange(range);

                var actualResult = ws.DataValidations.TryGet(range.RangeAddress, out var foundDv);
                Assert.IsFalse(actualResult);
                Assert.IsNull(foundDv);
            }
        }
        public void ThenSingleScenarioOutlineAddedSuccessfully()
        {
            var excelScenarioFormatter = Container.Resolve <ExcelScenarioOutlineFormatter>();
            var exampleTable           = new Table();

            exampleTable.HeaderRow = new TableRow("Var1", "Var2", "Var3", "Var4");
            exampleTable.DataRows  =
                new List <TableRow>(new[] { new TableRow("1", "2", "3", "4"), new TableRow("5", "6", "7", "8") });
            var example = new Example {
                Name = "Examples", Description = string.Empty, TableArgument = exampleTable
            };
            var examples = new List <Example>();

            examples.Add(example);
            var scenarioOutline = new ScenarioOutline
            {
                Name        = "Test Feature",
                Description =
                    "In order to test this feature,\nAs a developer\nI want to test this feature",
                Examples = examples
            };

            using (var workbook = new XLWorkbook())
            {
                IXLWorksheet worksheet = workbook.AddWorksheet("SHEET1");
                int          row       = 3;
                excelScenarioFormatter.Format(worksheet, scenarioOutline, ref row);

                Check.That(worksheet.Cell("B3").Value).IsEqualTo(scenarioOutline.Name);
                Check.That(worksheet.Cell("C4").Value).IsEqualTo(scenarioOutline.Description);
                Check.That(worksheet.Cell("B6").Value).IsEqualTo("Examples");
                Check.That(worksheet.Cell("D7").Value).IsEqualTo("Var1");
                Check.That(worksheet.Cell("E7").Value).IsEqualTo("Var2");
                Check.That(worksheet.Cell("F7").Value).IsEqualTo("Var3");
                Check.That(worksheet.Cell("G7").Value).IsEqualTo("Var4");
                Check.That(worksheet.Cell("D8").Value).IsEqualTo(1.0);
                Check.That(worksheet.Cell("E8").Value).IsEqualTo(2.0);
                Check.That(worksheet.Cell("F8").Value).IsEqualTo(3.0);
                Check.That(worksheet.Cell("G8").Value).IsEqualTo(4.0);
                Check.That(worksheet.Cell("D9").Value).IsEqualTo(5.0);
                Check.That(worksheet.Cell("E9").Value).IsEqualTo(6.0);
                Check.That(worksheet.Cell("F9").Value).IsEqualTo(7.0);
                Check.That(worksheet.Cell("G9").Value).IsEqualTo(8.0);
                Check.That(row).IsEqualTo(10);
            }
        }
Beispiel #14
0
        public void CanRenderRangeForEmptySet()
        {
            using (var wb = new XLWorkbook())
            {
                var ws = wb.AddWorksheet("Sheet1");
                ws.Range("A2:B3").AddToNamed("List");
                ws.Cell("B2").Value = "{{item}}";
                ws.Cell("B4").Value = "Cell below";

                var template = new XLTemplate(wb);
                template.AddVariable("List", new List <string>());
                template.Generate();

                ws.Cell("B3").Value.Should().Be("Cell below");
                ws.Cell("B4").Value.Should().Be("");
            }
        }
Beispiel #15
0
        public void WorkbookFilterPrivacyIsNotSetByDefault()
        {
            using var ms = new MemoryStream();

            using (var wb = new XLWorkbook())
            {
                wb.AddWorksheet();
                wb.SaveAs(ms);
            }

            ms.Seek(0, SeekOrigin.Begin);

            using (var wb = SpreadsheetDocument.Open(ms, false))
            {
                Assert.IsNull(wb.WorkbookPart.Workbook.WorkbookProperties.FilterPrivacy);
            }
        }
Beispiel #16
0
 private void Init()
 {
     if (_sheet == null)
     {
         if (!_wb.TryGetWorksheet(SheetName, out _sheet))
         {
             _sheet = _wb.AddWorksheet(SheetName);
             _sheet.SetCalcEngineCacheExpressions(false);
         }
         //_sheet.Visibility = XLWorksheetVisibility.VeryHidden;
     }
     _row     = 1;
     _clmn    = 1;
     _maxRow  = _prevrow = 1;
     _maxClmn = _prevclmn = 1;
     Clear();
 }
Beispiel #17
0
        public void TableRenameTests()
        {
            var l = new List <TestObjectWithAttributes>()
            {
                new TestObjectWithAttributes()
                {
                    Column1 = "a", Column2 = "b", MyField = 4, UnOrderedColumn = 999
                },
                new TestObjectWithAttributes()
                {
                    Column1 = "c", Column2 = "d", MyField = 5, UnOrderedColumn = 777
                }
            };

            using (var wb = new XLWorkbook())
            {
                IXLWorksheet ws     = wb.AddWorksheet("Sheet1");
                var          table1 = ws.FirstCell().InsertTable(l);
                var          table2 = ws.Cell("A10").InsertTable(l);

                Assert.AreEqual("Table1", table1.Name);
                Assert.AreEqual("Table2", table2.Name);

                table1.Name = "table1";
                Assert.AreEqual("table1", table1.Name);

                table1.Name = "_table1";
                Assert.AreEqual("_table1", table1.Name);

                table1.Name = "\\table1";
                Assert.AreEqual("\\table1", table1.Name);

                Assert.Throws <ArgumentException>(() => table1.Name = "");
                Assert.Throws <ArgumentException>(() => table1.Name = "R");
                Assert.Throws <ArgumentException>(() => table1.Name = "C");
                Assert.Throws <ArgumentException>(() => table1.Name = "r");
                Assert.Throws <ArgumentException>(() => table1.Name = "c");

                Assert.Throws <ArgumentException>(() => table1.Name = "123");
                Assert.Throws <ArgumentException>(() => table1.Name = new String('A', 256));

                Assert.Throws <ArgumentException>(() => table1.Name = "Table2");
                Assert.Throws <ArgumentException>(() => table1.Name = "TABLE2");
            }
        }
        public override void OutputCSV(string filename)
        {
            var steps      = Steps.OrderBy(step => step.ReflectionCoefficient).ToList();
            var radiusList = (from step in steps from data in step.Datas select data.Radius).Distinct().OrderBy(r => r).ToList();

            var book  = new XLWorkbook();
            var sheet = book.AddWorksheet("Main");

            //説明
            sheet.Cell(1, 1).Value = "説明";
            sheet.Cell(2, 1).Value = Discription;

            //データの記述
            const int dataStartRow = 4;

            sheet.Cell(dataStartRow, 1).Value = "半径/反射率";
            for (var i = 0; i < radiusList.Count; i++)
            {
                var radius = radiusList[i];
                sheet.Cell(dataStartRow + 1 + i, 1).Value = radius;
            }

            for (var i = 0; i < steps.Count; i++)
            {
                var step = steps[i];
                sheet.Cell(dataStartRow, 2 + i).Value = step.ReflectionCoefficient.ToString("0.00");
            }

            for (var i = 0; i < steps.Count; i++)
            {
                var step = steps[i];
                var max  = step.Datas.Max(data => data.Count);

                for (var j = 0; j < radiusList.Count; j++)
                {
                    var radius = radiusList[j];
                    if (step.Datas.Any(data => data.Radius == radius))
                    {
                        sheet.Cell(dataStartRow + 1 + j, 2 + i).Value = step.Datas.First(data => data.Radius == radius).Count / (double)max;
                    }
                }
            }

            book.SaveAs(filename);
        }
Beispiel #19
0
        public void TestFieldCellTypes()
        {
            var l = new List <TestObjectWithAttributes>()
            {
                new TestObjectWithAttributes()
                {
                    Column1 = "a", Column2 = "b", MyField = 4, UnOrderedColumn = 999
                },
                new TestObjectWithAttributes()
                {
                    Column1 = "c", Column2 = "d", MyField = 5, UnOrderedColumn = 777
                }
            };

            using (var wb = new XLWorkbook())
            {
                var ws    = wb.AddWorksheet("Sheet1");
                var table = ws.Cell("B2").InsertTable(l);

                Assert.AreEqual(4, table.Fields.Count());

                Assert.AreEqual("B2", table.Field(0).HeaderCell.Address.ToString());
                Assert.AreEqual("C2", table.Field(1).HeaderCell.Address.ToString());
                Assert.AreEqual("D2", table.Field(2).HeaderCell.Address.ToString());
                Assert.AreEqual("E2", table.Field(3).HeaderCell.Address.ToString());

                Assert.IsNull(table.Field(0).TotalsCell);
                Assert.IsNull(table.Field(1).TotalsCell);
                Assert.IsNull(table.Field(2).TotalsCell);
                Assert.IsNull(table.Field(3).TotalsCell);

                table.SetShowTotalsRow();

                Assert.AreEqual("B5", table.Field(0).TotalsCell.Address.ToString());
                Assert.AreEqual("C5", table.Field(1).TotalsCell.Address.ToString());
                Assert.AreEqual("D5", table.Field(2).TotalsCell.Address.ToString());
                Assert.AreEqual("E5", table.Field(3).TotalsCell.Address.ToString());

                var field = table.Fields.Last();

                Assert.AreEqual("E2:E5", field.Column.RangeAddress.ToString());
                Assert.AreEqual("E3", field.DataCells.First().Address.ToString());
                Assert.AreEqual("E4", field.DataCells.Last().Address.ToString());
            }
        }
        private void ExportExcelEventHandler(ControllerContext context)
        {
            try
            {
                var workbook = new XLWorkbook();

                if (ExportData != null)
                {
                    context.HttpContext.Response.Clear();

                    // Encoding
                    context.HttpContext.Response.ContentEncoding = Encoding.UTF8;

                    // Content-Type
                    context.HttpContext.Response.ContentType =
                        "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

                    // FileName
                    var browser        = context.HttpContext.Request.Browser.Browser;
                    var exportFileName = browser.Equals("FireFox", StringComparison.OrdinalIgnoreCase)
                        ? FileName
                        : HttpUtility.UrlEncode(FileName, Encoding.UTF8);

                    // Header
                    context.HttpContext.Response.Headers.Add("Content-Disposition", $"attachment; filename={exportFileName}");

                    // Add All DataTable
                    workbook.AddWorksheet(ExportData, SheetName);

                    // Write
                    using (MemoryStream stream = new MemoryStream())
                    {
                        workbook.SaveAs(stream);
                        stream.WriteTo(context.HttpContext.Response.OutputStream);
                        stream.Close();
                    }
                }

                workbook.Dispose();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public FileResult Export()
        {
            var workbook = new XLWorkbook();

            workbook.AddWorksheet("Contacts");
            var ws = workbook.Worksheet("Contacts");

            var contacts = db.Contacts.ToList();

            //insert columns
            ws.Cell("A1").Value = "First Name";
            ws.Cell("B1").Value = "Last Name";
            ws.Cell("C1").Value = "Phone Number";
            ws.Cell("D1").Value = "Email";
            ws.Cell("E1").Value = "Address";


            int row = 2;

            foreach (var contact in contacts)
            {
                ws.Cell("A" + row.ToString()).Value = contact.FirstName;
                ws.Cell("B" + row.ToString()).Value = contact.LastName;
                ws.Cell("C" + row.ToString()).Value = contact.PhoneNumbers.FirstOrDefault() != null?contact.PhoneNumbers.FirstOrDefault().Number : "";

                ws.Cell("D" + row.ToString()).Value = contact.Emails.FirstOrDefault() != null?contact.Emails.FirstOrDefault().EmailAddress : "";

                ws.Cell("E" + row.ToString()).Value = contact.Addresses.FirstOrDefault() != null?contact.Addresses.FirstOrDefault().Country
                                                      + " " + contact.Addresses.FirstOrDefault().City + " "
                                                      + contact.Addresses.FirstOrDefault().StreetAddress : "";

                row++;
            }

            //konverton ne byte excelin per ta shkarkuar me metoden File
            var workbookBytes = new byte[0];

            using (var ms = new MemoryStream())
            {
                workbook.SaveAs(ms);
                workbookBytes = ms.ToArray();
            }

            return(File(workbookBytes, "application/vnd.ms-excel", "Grid.xls"));
        }
Beispiel #22
0
        public void TableShowHeader()
        {
            using (var wb = new XLWorkbook())
            {
                IXLWorksheet ws = wb.AddWorksheet("Sheet1");
                ws.FirstCell().SetValue("Categories")
                .CellBelow().SetValue("A")
                .CellBelow().SetValue("B")
                .CellBelow().SetValue("C");

                IXLTable table = ws.RangeUsed().CreateTable();

                Assert.AreEqual("Categories", table.Fields.First().Name);

                table.SetShowHeaderRow(false);

                Assert.AreEqual("Categories", table.Fields.First().Name);

                Assert.IsTrue(ws.Cell(1, 1).IsEmpty(true));
                Assert.AreEqual(null, table.HeadersRow());
                Assert.AreEqual("A", table.DataRange.FirstRow().Field("Categories").GetString());
                Assert.AreEqual("C", table.DataRange.LastRow().Field("Categories").GetString());
                Assert.AreEqual("A", table.DataRange.FirstCell().GetString());
                Assert.AreEqual("C", table.DataRange.LastCell().GetString());

                table.SetShowHeaderRow();
                IXLRangeRow headerRow = table.HeadersRow();
                Assert.AreNotEqual(null, headerRow);
                Assert.AreEqual("Categories", headerRow.Cell(1).GetString());

                table.SetShowHeaderRow(false);

                ws.FirstCell().SetValue("x");

                table.SetShowHeaderRow();

                Assert.AreEqual("x", ws.FirstCell().GetString());
                Assert.AreEqual("Categories", ws.Cell("A2").GetString());
                Assert.AreNotEqual(null, headerRow);
                Assert.AreEqual("A", table.DataRange.FirstRow().Field("Categories").GetString());
                Assert.AreEqual("C", table.DataRange.LastRow().Field("Categories").GetString());
                Assert.AreEqual("A", table.DataRange.FirstCell().GetString());
                Assert.AreEqual("C", table.DataRange.LastCell().GetString());
            }
        }
        public void TestCopyIfDataIsSet()
        {
            var          wb                = new XLWorkbook();
            IXLWorksheet ws                = wb.AddWorksheet("Test");
            var          excelReport       = Substitute.For <object>();
            var          templateProcessor = Substitute.For <ITemplateProcessor>();

            IXLRange range = ws.Range(1, 1, 2, 4);

            range.AddToNamed("DataPanel", XLScope.Worksheet);
            IXLNamedRange namedRange = ws.NamedRange("DataPanel");

            object[] data  = { 1, "One" };
            var      panel = new ExcelDataSourcePanel(data, namedRange, excelReport, templateProcessor)
            {
                RenderPriority                 = 10,
                Type                           = PanelType.Horizontal,
                ShiftType                      = ShiftType.NoShift,
                BeforeRenderMethodName         = "BeforeRenderMethod",
                AfterRenderMethodName          = "AfterRenderMethod",
                BeforeDataItemRenderMethodName = "BeforeDataItemRenderMethodName",
                AfterDataItemRenderMethodName  = "AfterDataItemRenderMethodName",
                GroupBy                        = "2,4",
            };

            ExcelDataSourcePanel copiedPanel = (ExcelDataSourcePanel)panel.Copy(ws.Cell(5, 5));

            Assert.AreSame(excelReport, copiedPanel.GetType().GetField("_report", BindingFlags.Instance | BindingFlags.NonPublic).GetValue(copiedPanel));
            Assert.AreSame(templateProcessor, copiedPanel.GetType().GetField("_templateProcessor", BindingFlags.Instance | BindingFlags.NonPublic).GetValue(copiedPanel));
            Assert.IsNull(copiedPanel.GetType().GetField("_dataSourceTemplate", BindingFlags.Instance | BindingFlags.NonPublic).GetValue(copiedPanel));
            Assert.AreSame(data, copiedPanel.GetType().GetField("_data", BindingFlags.Instance | BindingFlags.NonPublic).GetValue(copiedPanel));
            Assert.AreEqual(ws.Cell(5, 5), copiedPanel.Range.FirstCell());
            Assert.AreEqual(ws.Cell(6, 8), copiedPanel.Range.LastCell());
            Assert.AreEqual(10, copiedPanel.RenderPriority);
            Assert.AreEqual(PanelType.Horizontal, copiedPanel.Type);
            Assert.AreEqual(ShiftType.NoShift, copiedPanel.ShiftType);
            Assert.AreEqual("BeforeRenderMethod", copiedPanel.BeforeRenderMethodName);
            Assert.AreEqual("AfterRenderMethod", copiedPanel.AfterRenderMethodName);
            Assert.AreEqual("BeforeDataItemRenderMethodName", copiedPanel.BeforeDataItemRenderMethodName);
            Assert.AreEqual("AfterDataItemRenderMethodName", copiedPanel.AfterDataItemRenderMethodName);
            Assert.AreEqual("2,4", copiedPanel.GroupBy);
            Assert.IsNull(copiedPanel.Parent);

            //wb.SaveAs("test.xlsx");
        }
        public void TestInvalidXmlCharacters()
        {
            byte[] data;

            using (var stream = new MemoryStream())
            {
                var wb = new XLWorkbook();
                wb.AddWorksheet("Sheet1").FirstCell().SetValue("\u0018");
                wb.SaveAs(stream);
                data = stream.ToArray();
            }

            using (var stream = new MemoryStream(data))
            {
                var wb = new XLWorkbook(stream);
                Assert.AreEqual("\u0018", wb.Worksheets.First().FirstCell().Value);
            }
        }
        public void FirstCellUsedPredicateConsidersMergedRanges()
        {
            using (XLWorkbook wb = new XLWorkbook())
            {
                var ws = wb.AddWorksheet("Sheet1");
                ws.Cell("A1").Style.Fill.BackgroundColor = XLColor.Red;
                ws.Cell("A2").Style.Fill.BackgroundColor = XLColor.Yellow;
                ws.Cell("A3").Style.Fill.BackgroundColor = XLColor.Green;
                ws.Range("A1:C1").Merge();
                ws.Range("A2:C2").Merge();
                ws.Range("A3:C3").Merge();

                var actual = ws.FirstCellUsed(XLCellsUsedOptions.All,
                                              c => c.Style.Fill.BackgroundColor == XLColor.Yellow);

                Assert.AreEqual("A2", actual.Address.ToString());
            }
        }
Beispiel #26
0
        public IXLWorksheet Create(XLWorkbook workbook)
        {
            var returnVar = workbook.Worksheets.FirstOrDefault(ws => ws.Name.Equals(SheetName, StringComparison.OrdinalIgnoreCase)); //.Worksheet(DataType.Name);

            if (returnVar == null)
            {
                returnVar = workbook.AddWorksheet(SheetName);
                var firstRow = returnVar.Row(1);
                int i        = 1;
                foreach (var p in GetProperties())
                {
                    var cell = firstRow.Cell(i++);
                    cell.Value    = p.Name;
                    cell.DataType = XLCellValues.Text;
                }
            }
            return(returnVar);
        }
Beispiel #27
0
        public void Create(String filePath)
        {
            var workbook = new XLWorkbook();
            var ws       = workbook.AddWorksheet("Sheet1");

            ws.FirstCell().SetValue("Hello")
            .CellBelow().SetValue("Hellos")
            .CellBelow().SetValue("Hell")
            .CellBelow().SetValue("Holl");

            ws.RangeUsed().AddConditionalFormat().WhenStartsWith("Hell")
            .Fill.SetBackgroundColor(XLColor.Red)
            .Border.SetOutsideBorder(XLBorderStyleValues.Thick)
            .Border.SetOutsideBorderColor(XLColor.Blue)
            .Font.SetBold();

            workbook.SaveAs(filePath);
        }
        /// <summary>
        /// Create sheet describing Stored Procedures and functions
        /// </summary>
        /// <param name="workbook">The workbook to create the sheet in</param>
        /// <param name="storedProcFuncs">The stored procedures and functions to document</param>
        public void CreateSheetInWorkbook(ref XLWorkbook workbook, IEnumerable <StoredProcFuncInfo> storedProcFuncs)
        {
            if (storedProcFuncs == null)
            {
                throw new ArgumentNullException(nameof(storedProcFuncs));
            }
            if (storedProcFuncs.Count() == 0)
            {
                throw new ArgumentException(nameof(storedProcFuncs));
            }

            _data = storedProcFuncs.ToList();

            IXLWorksheet sheet = workbook.AddWorksheet("DbObjects");

            CreateTableRowsForObjects(ref sheet);
            FormatWorksheet(ref sheet);
        }
Beispiel #29
0
        public void IsOdd_Simple_true()
        {
            using (var wb = new XLWorkbook())
            {
                var ws = wb.AddWorksheet("Sheet");

                ws.Cell("A1").Value = 1;
                ws.Cell("A2").Value = 1.2;
                ws.Cell("A3").Value = 3;

                var actual = ws.Evaluate("=IsOdd(A1)");
                Assert.AreEqual(true, actual);
                actual = ws.Evaluate("=IsOdd(A2)");
                Assert.AreEqual(true, actual);
                actual = ws.Evaluate("=IsOdd(A3)");
                Assert.AreEqual(true, actual);
            }
        }
Beispiel #30
0
        public void SeriesSum()
        {
            object actual = XLWorkbook.EvaluateExpr("SERIESSUM(2,3,4,5)");

            Assert.AreEqual(40.0, actual);

            var          wb = new XLWorkbook();
            IXLWorksheet ws = wb.AddWorksheet("Sheet1");

            ws.Cell("A2").FormulaA1 = "PI()/4";
            ws.Cell("A3").Value     = 1;
            ws.Cell("A4").FormulaA1 = "-1/FACT(2)";
            ws.Cell("A5").FormulaA1 = "1/FACT(4)";
            ws.Cell("A6").FormulaA1 = "-1/FACT(6)";

            actual = ws.Evaluate("SERIESSUM(A2,0,2,A3:A6)");
            Assert.IsTrue(Math.Abs(0.70710321482284566 - (double)actual) < XLHelper.Epsilon);
        }