public void Create(string filePath)
{
string tempFile = ExampleHelper.GetTempFilePath(filePath);
try
{
new BasicTable().Create(tempFile);
var workbook = new XLWorkbook(tempFile);
var ws = workbook.Worksheet(1);
// Get a range object
var rngHeaders = ws.Range("B3:F3");
// Insert some rows/columns before the range
ws.Row(1).InsertRowsAbove(2);
ws.Column(1).InsertColumnsBefore(2);
// Change the background color of the headers
rngHeaders.Style.Fill.BackgroundColor = XLColor.LightSalmon;
ws.Columns().AdjustToContents();
workbook.SaveAs(filePath);
}
finally
{
if (File.Exists(tempFile))
{
File.Delete(tempFile);
}
}
}
public void Create(string filePath)
{
var tempFile = ExampleHelper.GetTempFilePath(filePath);
try
{
new BasicTable().Create(tempFile);
var workbook = new XLWorkbook(tempFile);
var ws = workbook.Worksheet(1);
// Define a range with the data
var firstTableCell = ws.FirstCellUsed();
var lastTableCell = ws.LastCellUsed();
var rngData = ws.Range(firstTableCell.Address, lastTableCell.Address);
// Copy the table to another worksheet
var wsCopy = workbook.Worksheets.Add("Contacts Copy");
wsCopy.Cell(1, 1).Value = rngData;
workbook.SaveAs(filePath);
}
finally
{
if (File.Exists(tempFile))
{
File.Delete(tempFile);
}
}
}
public void Create(string filePath)
{
string tempFile = ExampleHelper.GetTempFilePath(filePath);
try
{
new BasicTable().Create(tempFile);
var workbook = new XLWorkbook(tempFile);
var ws = workbook.Worksheet(1);
var rngTable = ws.Range("B2:F6");
rngTable.Transpose(XLTransposeOptions.MoveCells);
ws.Columns().AdjustToContents();
workbook.SaveAs(filePath);
}
finally
{
if (File.Exists(tempFile))
{
File.Delete(tempFile);
}
}
}
public void Create(string filePath)
{
string tempFile = ExampleHelper.GetTempFilePath(filePath);
try
{
new BasicTable().Create(tempFile);
var workbook = new XLWorkbook(tempFile);
var ws = workbook.Worksheet(1);
// Define a range with the data
var firstDataCell = ws.Cell("B4");
var lastDataCell = ws.LastCellUsed();
var rngData = ws.Range(firstDataCell.Address, lastDataCell.Address);
// Delete all rows where Outcast = false (the 3rd column)
rngData.Rows() // From all rows
.Where(r => !r.Cell(3).GetBoolean()) // where the 3rd cell of each row is false
.ForEach(r => r.Delete()); // delete the row and shift the cells up (the default for rows in a range)
//// Put a light gray background to all text cells
//rngData.Cells() // From all cells
// .Where(c => c.DataType == XLCellValues.Text) // where the data type is Text
// .ForEach(c => c.Style.Fill.BackgroundColor = XLColor.LightGray); // Fill with a light gray
var cells = rngData.Cells();
var filtered = cells.Where(c => c.DataType == XLDataType.Text);
var list = filtered.ToList();
foreach (var c in list)
{
c.Style.Fill.BackgroundColor = XLColor.LightGray;
}
// Put a thick border to the bottom of the table (we may have deleted the bottom cells with the border)
rngData.LastRow().Style.Border.BottomBorder = XLBorderStyleValues.Thick;
workbook.SaveAs(filePath);
}
finally
{
if (File.Exists(tempFile))
{
File.Delete(tempFile);
}
}
}
public void Create(string filePath)
{
string tempFile = ExampleHelper.GetTempFilePath(filePath);
try
{
new BasicTable().Create(tempFile);
var workbook = new XLWorkbook(tempFile);
var ws = workbook.Worksheet(1);
// Change the background color of the headers
var rngHeaders = ws.Range("B3:F3");
rngHeaders.Style.Fill.BackgroundColor = XLColor.LightSalmon;
// Change the date formats
var rngDates = ws.Range("E4:E6");
rngDates.Style.DateFormat.Format = "MM/dd/yyyy";
// Change the income values to text
var rngNumbers = ws.Range("F4:F6");
foreach (var cell in rngNumbers.Cells())
{
string formattedString = cell.GetFormattedString();
cell.DataType = XLCellValues.Text;
cell.Value = formattedString + " Dollars";
}
ws.Columns().AdjustToContents();
workbook.SaveAs(filePath);
}
finally
{
if (File.Exists(tempFile))
{
File.Delete(tempFile);
}
}
}
public void Create(string filePath)
{
string tempFile = ExampleHelper.GetTempFilePath(filePath);
try
{
new BasicTable().Create(tempFile);
var workbook = new XLWorkbook(tempFile);
var ws = workbook.Worksheet(1);
var rngTable = ws.Range("B2:F6");
rngTable.Row(rngTable.RowCount() - 1).Delete(XLShiftDeletedCells.ShiftCellsUp);
// Place some markers
var cellNextRow = ws.Cell(rngTable.RangeAddress.LastAddress.RowNumber + 1, rngTable.RangeAddress.LastAddress.ColumnNumber);
cellNextRow.Value = "ColumnRight Row";
var cellNextColumn = ws.Cell(rngTable.RangeAddress.LastAddress.RowNumber, rngTable.RangeAddress.LastAddress.ColumnNumber + 1);
cellNextColumn.Value = "ColumnRight Column";
rngTable.Transpose(XLTransposeOptions.MoveCells);
rngTable.Transpose(XLTransposeOptions.MoveCells);
rngTable.Transpose(XLTransposeOptions.ReplaceCells);
rngTable.Transpose(XLTransposeOptions.ReplaceCells);
ws.Columns().AdjustToContents();
workbook.SaveAs(filePath);
}
finally
{
if (File.Exists(tempFile))
{
File.Delete(tempFile);
}
}
}