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); } } }