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);
}
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");
}
}
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);
}
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);
}
}
/// <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);
}
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);
}
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);
}
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);
}
}
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);
}
}
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("");
}
}
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);
}
}
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();
}
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);
}
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"));
}
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());
}
}
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);
}
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);
}
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);
}
}
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);
}
