コード例 #1
0
        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);
                }
            }
        }
コード例 #2
0
        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);
                }
            }
        }
コード例 #3
0
        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);
                }
            }
        }
コード例 #4
0
ファイル: MergeMoves.cs プロジェクト: zamabraga/ClosedXML
        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);
                }
            }
        }
コード例 #5
0
        // 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);
                }
            }
        }
コード例 #6
0
        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);
                }
            }
        }
コード例 #7
0
        // 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);
                }
            }
        }