public new Task <DataTable> GetTable(int tbl, string _filename)
{
return(Task.Run(() => {
GetTableArrayProgress(0, 100);
var wrkBook = new XLWorkbook(_filename);
var table = new DataTable();
var wrkSheet = wrkBook.Worksheets.ToArray()[tbl];
IXLTable xlTable = wrkSheet.Range(wrkSheet.Cell(1, 1), wrkSheet.LastCellUsed()).AsTable();
foreach (IXLTableField col in xlTable.Fields)
{
table.Columns.Add(col.Name);
}
int rowsCount = xlTable.DataRange.RowCount();
int currentRow = 0;
xlTable.DataRange.Rows(c => true)
.ForEach(xrow => {
var row = table.NewRow();
int cellsCount = xrow.Cells().Count();
for (int i = 1; i <= cellsCount; i++)
{
row[i - 1] = xrow.Cell(i).Value;
}
table.Rows.Add(row);
GetTableArrayProgress(currentRow++, rowsCount);
});
wrkBook.Dispose();
return table;
}));
}
public void TableInsertBelowFromRows()
{
var wb = new XLWorkbook();
IXLWorksheet ws = wb.AddWorksheet("Sheet1");
ws.FirstCell().SetValue("Value");
IXLTable table = ws.Range("A1:A2").CreateTable();
table.SetShowTotalsRow()
.Field(0).TotalsRowFunction = XLTotalsRowFunction.Sum;
IXLTableRow row = table.DataRange.FirstRow();
row.Field("Value").Value = 1;
row = row.InsertRowsBelow(1).First();
row.Field("Value").Value = 2;
row = row.InsertRowsBelow(1).First();
row.Field("Value").Value = 3;
Assert.AreEqual(1, ws.Cell(2, 1).GetDouble());
Assert.AreEqual(2, ws.Cell(3, 1).GetDouble());
Assert.AreEqual(3, ws.Cell(4, 1).GetDouble());
//wb.SaveAs(@"D:\Excel Files\ForTesting\Sandbox.xlsx");
}
public DelegateTableRow(IXLTable table, IXLRangeRow row)
{
string?FindFieldValue(string name)
{
var colNumber = table.FindColumn(col => col.FirstCell().Value.ToString() == name).ColumnNumber();
return(row.Cell(colNumber).GetValue <string?>());
}
string?FindNullableFieldValue(string name)
{
var cellValue = FindFieldValue(name);
return(!string.IsNullOrWhiteSpace(cellValue) ? cellValue : null);
}
RowNumber = row.RowNumber();
CandidateNumber = FindFieldValue("DelegateID");
LastName = FindFieldValue("LastName");
FirstName = FindFieldValue("FirstName");
JobGroupId = int.TryParse(FindFieldValue("JobGroupID"), out var jobGroupId) ? jobGroupId : (int?)null;
Active = bool.TryParse(FindFieldValue("Active"), out var active) ? active : (bool?)null;
Answer1 = FindFieldValue("Answer1");
Answer2 = FindFieldValue("Answer2");
Answer3 = FindFieldValue("Answer3");
Answer4 = FindFieldValue("Answer4");
Answer5 = FindFieldValue("Answer5");
Answer6 = FindFieldValue("Answer6");
AliasId = FindFieldValue("AliasID");
Email = FindFieldValue("EmailAddress")?.Trim();
HasPrnRawValue = FindFieldValue("HasPRN");
HasPrn = bool.TryParse(HasPrnRawValue, out var hasPrn) ? hasPrn : (bool?)null;
Prn = FindNullableFieldValue("PRN");
RowStatus = RowStatus.NotYetProcessed;
}
/// <summary>
/// Добавить лист со сводной таблицей
/// </summary>
/// <param name="wb">книга для добавления листа</param>
/// <param name="table">данные для сводной таблицы</param>
private void AddPivotTable(XLWorkbook wb, IXLTable table)
{
using (var ws = wb.Worksheets.Add("Сводная таблица"))
{
// После добавления фильтров таблицы pt.ReportFilters.Add сгенерированный файл при открытии выдавал ошибку
// Решение: https://closedxml.codeplex.com/discussions/640419
//var pt = ws.PivotTables.AddNew("Сводная_таблица", ws.Cell(1, 1), table);
var pt = ws.PivotTables.AddNew("Сводная_таблица", ws.Cell(4, 1), table);
pt.ReportFilters.Add("Страна");
pt.ReportFilters.Add("Регион");
pt.ReportFilters.Add("Продукция");
pt.RowLabels.Add("Компания");
pt.ColumnLabels.Add("Месяц");
pt.Values.Add("Сумма").NumberFormat.NumberFormatId = 3;
// TODO: автоподбор ширины колонок в сводной таблице не работает
//ws.Columns().AdjustToContents();
ws.Column(1).Width = 50;
ws.Columns(2, 5).Width = 15;
}
}
private void AssertTablesAreEqual(IXLTable table1, IXLTable table2)
{
Assert.AreEqual(table1.RangeAddress.ToString(XLReferenceStyle.A1, false), table2.RangeAddress.ToString(XLReferenceStyle.A1, false));
Assert.AreEqual(table1.Fields.Count(), table2.Fields.Count());
for (int j = 0; j < table1.Fields.Count(); j++)
{
var originalField = table1.Fields.ElementAt(j);
var copyField = table2.Fields.ElementAt(j);
Assert.AreEqual(originalField.Name, copyField.Name);
if (table1.ShowTotalsRow)
{
Assert.AreEqual(originalField.TotalsRowFormulaA1, copyField.TotalsRowFormulaA1);
Assert.AreEqual(originalField.TotalsRowFunction, copyField.TotalsRowFunction);
}
}
Assert.AreEqual(table1.Name, table2.Name);
Assert.AreEqual(table1.ShowAutoFilter, table2.ShowAutoFilter);
Assert.AreEqual(table1.ShowColumnStripes, table2.ShowColumnStripes);
Assert.AreEqual(table1.ShowHeaderRow, table2.ShowHeaderRow);
Assert.AreEqual(table1.ShowRowStripes, table2.ShowRowStripes);
Assert.AreEqual(table1.ShowTotalsRow, table2.ShowTotalsRow);
Assert.AreEqual((table1.Style as XLStyle).Value, (table2.Style as XLStyle).Value);
Assert.AreEqual(table1.Theme, table2.Theme);
}
public void AutoFilterExpandsWithTable()
{
using (var wb = new XLWorkbook())
{
using (IXLWorksheet ws = wb.Worksheets.Add("Sheet1"))
{
ws.FirstCell().SetValue("Categories")
.CellBelow().SetValue("1")
.CellBelow().SetValue("2");
IXLTable table = ws.RangeUsed().CreateTable();
var listOfArr = new List <Int32>();
listOfArr.Add(3);
listOfArr.Add(4);
listOfArr.Add(5);
listOfArr.Add(6);
table.DataRange.InsertRowsBelow(listOfArr.Count - table.DataRange.RowCount());
table.DataRange.FirstCell().InsertData(listOfArr);
Assert.AreEqual("A1:A5", table.AutoFilter.Range.RangeAddress.ToStringRelative());
}
}
}
public void SavingLoadingTableWithNewLineInHeader2()
{
using (var wb = new XLWorkbook())
{
IXLWorksheet ws = wb.Worksheets.Add("Test");
var dt = new DataTable();
string columnName = "Line1" + Environment.NewLine + "Line2";
dt.Columns.Add(columnName);
DataRow dr = dt.NewRow();
dr[columnName] = "some text";
dt.Rows.Add(dr);
ws.Cell(1, 1).InsertTable(dt);
IXLTable table1 = ws.Table(0);
string fieldName1 = table1.Field(0).Name;
Assert.AreEqual(columnName, fieldName1);
using (var ms = new MemoryStream())
{
wb.SaveAs(ms, true);
var wb2 = new XLWorkbook(ms);
IXLWorksheet ws2 = wb2.Worksheet(1);
IXLTable table2 = ws2.Table(0);
string fieldName2 = table2.Field(0).Name;
Assert.AreEqual("Line1\nLine2", fieldName2);
}
}
}
private void CreateTableTotalSum(IXLTable table)
{
table.ShowTotalsRow = true;
foreach (var field in table.Fields)
{
field.TotalsRowFunction = XLTotalsRowFunction.Sum;
}
}
private string getHeaderTratado(IXLTable tabela, string campoPesq)
{
var campoRetorno = tabela.Fields.Select(campo => campo.HeaderCell.GetString())
.ToList()
.Find(header => string.Equals(header.Trim().ToLower(), campoPesq.Trim().ToLower()));
return(campoRetorno);
}
public IDictionary <ExcelDataType, object> ReadExcelNamedRange(IXLWorkbook workbook, string namedRange)
{
IXLNamedRange range = workbook.NamedRange(namedRange);
if (range == null)
{
throw new ClosedXMLReadException($"There is no named range called {namedRange} in this workbook");
}
int count = range.Ranges.First().Cells().Count();
//if solo then don't return collection.
if (count.Equals(1))
{
IXLCell cell = range.Ranges.First().Cells().First();
switch (cell.DataType)
{
case XLDataType.DateTime:
return(new Dictionary <ExcelDataType, object>
{
{ ExcelDataType.Numeric, cell.GetDateTime().ToOADate() }
});
case XLDataType.Number:
return(new Dictionary <ExcelDataType, object>
{
{ ExcelDataType.Numeric, cell.GetDouble() }
});
case XLDataType.Text:
string textValue = GetSingleCellTextValue(cell);
KeyValuePair <ExcelDataType, object> parsed = ParseString(textValue);
switch (parsed.Key)
{
case ExcelDataType.Numeric:
return(new Dictionary <ExcelDataType, object>
{
{ ExcelDataType.Numeric, (double)parsed.Value }
});
case ExcelDataType.Text:
string[,] array = new string[1, 1];
array[0, 0] = textValue;
return(new Dictionary <ExcelDataType, object>
{
{ ExcelDataType.Text, array }
});
default:
throw new NotImplementedException("I haven't implemented formulas yet");
}
}
}
IXLTable table = range.Ranges.First().AsTable();
return(ReadTable(table));
}
private int getHeaderIndex(IXLTable tabela, string campoPesq)
{
var index = tabela.Fields.Select(campo => campo)
.ToList()
.Find(header => string.Equals(header.Name.Trim().ToLower(), campoPesq.Trim().ToLower()))
?.Index;
return(index.HasValue ? index.Value : -1);
}
public ActionResult ExportDataToExcel(DataTable dtExcelData, string sheetName, string fileNameWithoutExtension, bool totalRequired = true, bool passwordRequired = true)
{
using (XLWorkbook wb = new XLWorkbook())
{
sheetName = sheetName.Replace(" ", "_");
fileNameWithoutExtension = fileNameWithoutExtension.Replace(" ", "_");
dtExcelData.TableName = sheetName;
IXLWorksheet workSheet = wb.Worksheets.Add(dtExcelData);
IXLRow row = workSheet.Row(dtExcelData.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;
}
}
wb.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
wb.Style.Font.Bold = true;
if (passwordRequired)
{
wb.Worksheet(sheetName).Protect("123456");
}
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);
}
}
public static void ReadFromXml(string filePath)
{
XLWorkbook wb = new XLWorkbook(filePath);
IXLWorksheet ws = wb.Worksheets.First();
IXLTable table = ws.Range(ws.FirstCellUsed(), ws.LastCellUsed()).AsTable();
/*string s = "";
* table.DataRange.CellsUsed().Count();
* table.DataRange.Rows().ForEach(row =>
* {
* s += "\n";
* row.Cells().ToList().ForEach(cell =>
* {
* s += cell.GetString()+"\t|||\t";
* });
* });
* if (true)
* {
*
* }*/
TimeTable orar = new TimeTable();
IXLCell grupaCell = null;
#region grupa
//cauta coloana care contine grupa
IXLRangeColumn grupaColoana = table.DataRange.FindColumn(column =>
{
grupaCell = column.Cells()?.FirstOrDefault(cell => cell.GetString().Equals("Grupa"));
return(grupaCell != null);
//return column.Cells().Any(cell => cell.GetString().Equals("Grupa"));
});
int id = 1;
//insereaza grupele care apar in excel in orar
grupaColoana.CellsUsed().Where(cell => !cell.GetString().Equals("Grupa")).ForEach(cell =>
{
orar.Groups.Add(new GroupDto(id++, cell.GetString()));
});
#endregion
table.Range(grupaCell.Address.RowNumber + 1, grupaCell.Address.ColumnNumber + 2,
table.LastCellUsed().Address.RowNumber, table.LastCellUsed().Address.ColumnNumber)
.CellsUsed().Where(cell => !table.Cell(cell.Address.RowNumber, 3).IsEmpty()).ForEach(cell =>
{
string[] split = cell.GetString().Split(",".ToCharArray());
//Programare prog = new Programare();
string materie = split[0];
string tipMaterie = split[1];
string sala = split[2];
string profesor = split[3];
});
}
public bool TryGetTable(string tableName, out IXLTable table)
{
if (_tables.ContainsKey(tableName))
{
table = _tables[tableName];
return(true);
}
table = null;
return(false);
}
public string GetExcelColumnLetter(IXLTable table, string columnHeader)
{
var cell = table.HeadersRow().CellsUsed(c => c.Value.ToString() == columnHeader).FirstOrDefault();
if (cell != null)
{
return(cell.WorksheetColumn().ColumnLetter());
}
return(null);
}
internal ImportCompetenciesResult ProcessCompetenciesTable(IXLTable table, int adminUserId, int frameworkId, int maxFrameworkCompetencyId, int maxFrameworkCompetencyGroupId)
{
var competenciesRows = table.Rows().Skip(1).Select(row => new CompetencyTableRow(table, row)).ToList();
foreach (var competencyRow in competenciesRows)
{
maxFrameworkCompetencyGroupId = ProcessCompetencyRow(adminUserId, frameworkId, maxFrameworkCompetencyId, maxFrameworkCompetencyGroupId, competencyRow);
}
return(new ImportCompetenciesResult(competenciesRows));
}
public IDictionary <ExcelDataType, object> ReadTable(IXLTable table)
{
int rows = table.RowCount();
int columns = table.ColumnCount();
var numericData = new double?[rows, columns];
var textData = new string[rows, columns];
var formula = new string[rows, columns];
for (int i = 1; i <= rows; i++)
{
for (int j = 1; j <= columns; j++)
{
IXLCell cell = table.Cell(i, j);
switch (cell.DataType)
{
case XLDataType.Number:
numericData[i - 1, j - 1] = cell.GetDouble();
break;
case XLDataType.Text:
{
string textValue = GetSingleCellTextValue(cell);
KeyValuePair <ExcelDataType, object> parsed = ParseString(textValue);
switch (parsed.Key)
{
case ExcelDataType.Numeric:
numericData[i - 1, j - 1] = (double)parsed.Value;
break;
case ExcelDataType.Text:
textData[i - 1, j - 1] = textValue;
break;
default:
throw new NotImplementedException("I haven't implemented formulas yet");
}
break;
}
}
if (cell.HasFormula)
{
formula[i - 1, j - 1] = cell.FormulaA1;
}
}
}
return(new Dictionary <ExcelDataType, object>
{
{ ExcelDataType.Numeric, numericData },
{ ExcelDataType.Formulae, formula },
{ ExcelDataType.Text, textData }
});
}
private bool FileHasHeaders(List <string> headerShortcutList, IXLTable table)
{
foreach (var headerShortcut in headerShortcutList)
{
if (GetFullHeaderName(headerShortcut, table) is null)
{
return(false);
}
}
return(true);
}
internal BulkUploadResult ProcessDelegatesTable(IXLTable table, int centreId, DateTime?welcomeEmailDate = null)
{
var jobGroupIds = jobGroupsDataService.GetJobGroupsAlphabetical().Select(item => item.id).ToList();
var delegateRows = table.Rows().Skip(1).Select(row => new DelegateTableRow(table, row)).ToList();
foreach (var delegateRow in delegateRows)
{
ProcessDelegateRow(centreId, welcomeEmailDate, delegateRow, jobGroupIds);
}
return(new BulkUploadResult(delegateRows));
}
public IXLPivotTable Add(string name, IXLCell targetCell, IXLTable table)
{
var pivotTable = new XLPivotTable(this.Worksheet)
{
Name = name,
TargetCell = targetCell,
SourceTable = table
};
_pivotTables.Add(name, pivotTable);
return(pivotTable);
}
private static bool ValidateHeaders(IXLTable table)
{
var expectedHeaders = new List <string>
{
"competency group",
"competency name",
"competency description"
}.OrderBy(x => x);
var actualHeaders = table.Fields.Select(x => x.Name.ToLower()).OrderBy(x => x);
return(actualHeaders.SequenceEqual(expectedHeaders));
}
public static void testLinq(IXLTable table)
{
Int32 currentRow = table.RangeAddress.FirstAddress.RowNumber; // reset the currentRow
var Linq1 = table.DataRange.Rows().Where(row => row.Field("columnA").GetString() == "A1").Select(r => r);
var Linq2 = from row in table.DataRange.Rows()
where row.Field("columnC").GetString() == "C1"
select row;
foreach (var row2 in Linq1)
{
Console.WriteLine(row2.Field("columnC").GetString());
}
}
/// <summary>
/// ''' can't touch already created pivot-table
/// ''' https://github.com/ClosedXML/ClosedXML/pull/124
/// ''' </summary>
/// ''' <param name="sheetSource"></param>
/// ''' <param name="sheetDest"></param>
/// ''' <param name="rows"></param>
/// ''' <param name="cols"></param>
/// ''' <returns></returns>
public ExcelHelper DoPivotTable(string sheetSource, string sheetDest, string[] rows = null, string[] cols = null, string[] datafields = null)
{
try
{
IXLWorksheet worksheetSource = null;
IXLTable sourceTable = null;
if (_workbook.Worksheets.Count < 1)
{
_workbook = new XLWorkbook(this._inputFile.FullName);
}
worksheetSource = _workbook.Worksheets.Worksheet(sheetSource);
sourceTable = worksheetSource.Table(0);
// TODO PRIMA CONTROLLARE SE ESISTE E nel caso cancella
this.RemoveSheet(sheetDest); // <<TODO invece di remove che in questo caso crea un errore cercare se è possibile chiamare REFRESH
IXLWorksheet pivotTableSheet = _workbook.Worksheets.Add(sheetDest);
IXLPivotTable pivoTable = pivotTableSheet.PivotTables.Add("PivotTable", pivotTableSheet.Cell(1, 1), sourceTable.AsRange());
foreach (string r in rows)
{
if (r.Trim() != "")
{
pivoTable.RowLabels.Add(r);
}
}
foreach (string c in cols)
{
if (c.Trim() != "")
{
pivoTable.ColumnLabels.Add(c);
}
}
foreach (string d in datafields)
{
if (d.Trim() != "")
{
pivoTable.Values.Add(d);
}
}
}
// i filtri non sono al momento supportati https://github.com/ClosedXML/ClosedXML/issues/218
catch (Exception ex)
{
//SD.Log(ex.Message, SD.LogLevel.Error, ex.StackTrace);
return(this);
}
return(this);
}
private string GetFullHeaderName(string fragmentNazwyNaglowka, IXLTable table)
{
var headers = new List <string>();
foreach (var cell in table.HeadersRow().Cells())
{
headers.Add(cell.GetString());
}
var fullHeaderName = headers.Where(s => s.ToLower().Contains(fragmentNazwyNaglowka.ToLower()))
.FirstOrDefault();
return(fullHeaderName);
}
public void Inserting_Column_Sets_Header()
{
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();
table.InsertColumnsAfter(1);
Assert.AreEqual("Column2", table.HeadersRow().LastCell().GetString());
}
}
public CompetencyTableRow(IXLTable table, IXLRangeRow row)
{
string?FindFieldValue(string name)
{
var colNumber = table.FindColumn(col => col.FirstCell().Value.ToString().ToLower() == name).ColumnNumber();
return(row.Cell(colNumber).GetValue <string?>());
}
RowNumber = row.RowNumber();
CompetencyGroupName = FindFieldValue("competency group");
CompetencyName = FindFieldValue("competency name");
CompetencyDescription = FindFieldValue("competency description");
RowStatus = RowStatus.NotYetProcessed;
}
private ImportacaoAmbiente ImportacaoExcelCamposDinamicos(IXLTable tabela, ImportacaoAmbienteInput inputs)
{
try
{
var importacaoDinamica = new ImportacaoAmbiente();
idImportacao = importacaoDinamica.IdImportacao;
var linhasAgrupadasDinamicamente = tabela.DataRange.Rows().GroupBy(q => new DynamicDataRowGroup(q, inputs.Agrupadores)).ToList();
linhasAgrupadasDinamicamente.ForEach(group =>
{
string campoControle = group.ToList()?.First()?.Field(0)?.GetString();
if (!string.IsNullOrEmpty(campoControle))
{
group.ToList().ForEach(linha =>
{
var agr = importacaoDinamica.Filhos;
Guid?idPaiAux = null;
inputs.Agrupadores.ToList().ForEach(colunaAgrupadora =>
{
var descricaoAmbiente = linha.Field(colunaAgrupadora).GetString();
var descricaoTipoAmbiente = linha.Field(inputs.CampoTipoAmbiente).GetString();
var result = CriarArvore(agr, descricaoAmbiente, idPaiAux, descricaoTipoAmbiente);
agr = result.Item2;
idPaiAux = result.Item1;
});
var ambiente = new Ambiente(linha.Field(inputs.CampoAmbiente).GetString(), idPaiAux);
ambiente.TipoAmbiente = _tpAmbienteServ.BuscarOuCriar(linha.Field(inputs.CampoTipoAmbiente).GetString());
ambiente.IdTipoAmbiente = ambiente.TipoAmbiente.Id;
ambiente.IdImportacao = idImportacao;
agr.Add(ambiente);
_ambienteServ.CriarAmbiente(ambiente);
});
}
});
return(importacaoDinamica);
}
catch (Exception)
{
throw;
}
}
public void Validation_persists_on_Worksheet_DataValidations()
{
var wb = new XLWorkbook();
IXLWorksheet ws = wb.Worksheets.Add("People");
ws.FirstCell().SetValue("Categories")
.CellBelow().SetValue("A");
IXLTable table = ws.RangeUsed().CreateTable();
IXLDataValidation dv = table.DataRange.SetDataValidation();
dv.ErrorTitle = "Error";
Assert.AreEqual("Error", ws.DataValidations.Single().ErrorTitle);
}
public void TableShowHeader()
{
var wb = new XLWorkbook();
IXLWorksheet ws = wb.AddWorksheet("Sheet1");
ws.FirstCell().SetValue("Categories")
.CellBelow().SetValue("A")
.CellBelow().SetValue("B")
.CellBelow().SetValue("C");
ws.RangeUsed().CreateTable().SetShowHeaderRow(false);
IXLTable table = ws.Tables.First();
//wb.SaveAs(@"D:\Excel Files\ForTesting\Sandbox1.xlsx");
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();
//wb.SaveAs(@"D:\Excel Files\ForTesting\Sandbox2.xlsx");
//wb.SaveAs(@"D:\Excel Files\ForTesting\Sandbox3.xlsx");
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 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);
}
}
public void Add(IXLTable table)
{
_tables.Add(table.Name, table);
}
