public void Create(string filePath)
{
string tempFile1 = ExampleHelper.GetTempFilePath(filePath);
string tempFile2 = ExampleHelper.GetTempFilePath(filePath);
try
{
new UsingTables().Create(tempFile1);
var wb = new XLWorkbook(tempFile1);
var wsSource = wb.Worksheet(1);
// Copy the worksheet to a new sheet in this workbook
wsSource.CopyTo("Copy");
// We're going to open another workbook to show that you can
// copy a sheet from one workbook to another:
new BasicTable().Create(tempFile2);
var wbSource = new XLWorkbook(tempFile2);
wbSource.Worksheet(1).CopyTo(wb, "Copy From Other");
// Save the workbook with the 2 copies
wb.SaveAs(filePath);
}
finally
{
if (File.Exists(tempFile1))
{
File.Delete(tempFile1);
}
if (File.Exists(tempFile2))
{
File.Delete(tempFile2);
}
}
}
public void Create(string filePath)
{
string tempFile = ExampleHelper.GetTempFilePath(filePath);
try
{
//Note: Prepare
{
var workbook = new XLWorkbook();
workbook.Worksheets.Add("1");
workbook.Worksheets.Add("2");
workbook.Worksheets.Add("3");
workbook.Worksheets.Add("4");
workbook.SaveAs(tempFile);
}
//Note: Delate few worksheet
{
var workbook = new XLWorkbook(tempFile);
workbook.Worksheets.Delete("1");
workbook.Worksheets.Delete("2");
workbook.SaveAs(filePath);
}
}
finally
{
if (File.Exists(tempFile))
{
File.Delete(tempFile);
}
}
}
public void CheckNonEqual()
{
string left = ExampleHelper.GetTempFilePath("left.xlsx");
string right = ExampleHelper.GetTempFilePath("right.xlsx");
try
{
new BasicTable().Create(left);
new HelloWorld().Create(right);
string message;
Assert.IsFalse(ExcelDocsComparer.Compare(left, right, out message));
}
finally
{
if (File.Exists(left))
{
File.Delete(left);
}
if (File.Exists(right))
{
File.Delete(right);
}
}
}
public void Create(string filePath)
{
string tempFile = ExampleHelper.GetTempFilePath(filePath);
try
{
new MergeCells().Create(tempFile);
var workbook = new XLWorkbook(tempFile);
var ws = workbook.Worksheet(1);
ws.Range("B1:F1").InsertRowsBelow(1);
ws.Range("A3:A9").InsertColumnsAfter(1);
ws.Row(1).Delete();
ws.Column(1).Delete();
ws.Range("E8:E9").InsertColumnsAfter(1);
ws.Range("F2:F8").Merge();
ws.Range("E3:E4").InsertColumnsAfter(1);
ws.Range("F2:F8").Merge();
ws.Range("E1:E2").InsertColumnsAfter(1);
ws.Range("G2:G8").Merge();
ws.Range("E1:E2").Delete(XLShiftDeletedCells.ShiftCellsLeft);
ws.Range("D3:E3").InsertRowsBelow(1);
ws.Range("A1:B1").InsertRowsBelow(1);
ws.Range("B3:D3").Merge();
ws.Range("A1:B1").Delete(XLShiftDeletedCells.ShiftCellsUp);
ws.Range("B8:D8").Merge();
ws.Range("D8:D9").Clear();
workbook.SaveAs(filePath);
}
finally
{
if (File.Exists(tempFile))
{
File.Delete(tempFile);
}
}
}
// Public
public void Create(String filePath)
{
string tempFile = ExampleHelper.GetTempFilePath(filePath);
try
{
new BasicTable().Create(tempFile);
var wb = new XLWorkbook(tempFile);
var ws = wb.Worksheets.First();
var firstCell = ws.FirstCellUsed();
var lastCell = ws.LastCellUsed();
var range = ws.Range(firstCell.Address, lastCell.Address);
range.FirstRow().Delete(); // Deleting the "Contacts" header (we don't need it for our purposes)
// We want to use a theme for table, not the hard coded format of the BasicTable
range.Clear(XLClearOptions.Formats);
// Put back the date and number formats
range.Column(4).Style.NumberFormat.NumberFormatId = 15;
range.Column(5).Style.NumberFormat.Format = "$ #,##0";
var table = range.CreateTable(); // You can also use range.AsTable() if you want to
ws.Cell("Q6000").Value = "dummy value";
var row = table.DataRange.InsertRowsBelow(1).First();
wb.SaveAs(filePath);
}
finally
{
if (File.Exists(tempFile))
{
File.Delete(tempFile);
}
}
}
public void Create(string filePath)
{
var backupCulture = Thread.CurrentThread.CurrentCulture;
// Set thread culture to French, which should format numbers using decimal COMMA
Thread.CurrentThread.CurrentCulture = CultureInfo.CreateSpecificCulture("fr-FR");
string tempFile = ExampleHelper.GetTempFilePath(filePath);
try
{
new DataTypes().Create(tempFile);
var workbook = new XLWorkbook(tempFile);
workbook.SaveAs(filePath);
}
finally
{
Thread.CurrentThread.CurrentCulture = backupCulture;
if (File.Exists(tempFile))
{
File.Delete(tempFile);
}
}
}
// Public
public void Create(String filePath)
{
string tempFile = ExampleHelper.GetTempFilePath(filePath);
try
{
new BasicTable().Create(tempFile);
using (var wb = new XLWorkbook(tempFile))
{
var ws = wb.Worksheet(1);
ws.Name = "Contacts Table";
var firstCell = ws.FirstCellUsed();
var lastCell = ws.LastCellUsed();
var range = ws.Range(firstCell.Address, lastCell.Address);
range.FirstRow().Delete(); // Deleting the "Contacts" header (we don't need it for our purposes)
// We want to use a theme for table, not the hard coded format of the BasicTable
range.Clear(XLClearOptions.Formats);
// Put back the date and number formats
range.Column(4).Style.NumberFormat.NumberFormatId = 15;
range.Column(5).Style.NumberFormat.Format = "$ #,##0";
var table = range.CreateTable(); // You can also use range.AsTable() if you want to
// manipulate the range as a table but don't want
// to create the table in the worksheet.
// Let's activate the Totals row and add the sum of Income
table.ShowTotalsRow = true;
table.Field("Income").TotalsRowFunction = XLTotalsRowFunction.Sum;
// Just for fun let's add the text "Sum Of Income" to the totals row
table.Field(0).TotalsRowLabel = "Sum Of Income";
// Copy all the headers
Int32 columnWithHeaders = lastCell.Address.ColumnNumber + 2;
Int32 currentRow = table.RangeAddress.FirstAddress.RowNumber;
ws.Cell(currentRow, columnWithHeaders).Value = "Table Headers";
foreach (var cell in table.HeadersRow().Cells())
{
currentRow++;
ws.Cell(currentRow, columnWithHeaders).Value = cell.Value;
}
// Format the headers as a table with a different style and no autofilters
var htFirstCell = ws.Cell(table.RangeAddress.FirstAddress.RowNumber, columnWithHeaders);
var htLastCell = ws.Cell(currentRow, columnWithHeaders);
var headersTable = ws.Range(htFirstCell, htLastCell).CreateTable("Headers");
headersTable.Theme = XLTableTheme.TableStyleLight10;
headersTable.ShowAutoFilter = false;
// Add a custom formula to the headersTable
headersTable.ShowTotalsRow = true;
headersTable.Field(0).TotalsRowFormulaA1 = "CONCATENATE(\"Count: \", CountA(Headers[Table Headers]))";
// Copy the names
Int32 columnWithNames = columnWithHeaders + 2;
currentRow = table.RangeAddress.FirstAddress.RowNumber; // reset the currentRow
ws.Cell(currentRow, columnWithNames).Value = "Names";
foreach (var row in table.DataRange.Rows())
{
currentRow++;
var fName = row.Field("FName").GetString(); // Notice how we're calling the cell by field name
var lName = row.Field("LName").GetString(); // Notice how we're calling the cell by field name
var name = String.Format("{0} {1}", fName, lName);
ws.Cell(currentRow, columnWithNames).Value = name;
}
// Format the names as a table with a different style and no autofilters
var ntFirstCell = ws.Cell(table.RangeAddress.FirstAddress.RowNumber, columnWithNames);
var ntLastCell = ws.Cell(currentRow, columnWithNames);
var namesTable = ws.Range(ntFirstCell, ntLastCell).CreateTable();
namesTable.Theme = XLTableTheme.TableStyleLight12;
namesTable.ShowAutoFilter = false;
ws.Columns().AdjustToContents();
ws.Columns("A,G,I").Width = 3;
wb.SaveAs(filePath);
}
}
finally
{
if (File.Exists(tempFile))
{
File.Delete(tempFile);
}
}
}