C# XLWorkbook.Worksheet方法代码示例

        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 static void Execute(DownloadServiceOptions options)
        {
            using (var workbook = new XLWorkbook(options.File))
            using (var worksheet = workbook.Worksheet(1))
            {
                var fullRange = worksheet.RangeUsed();
                var rangeUsed = worksheet.Range(2, 1, fullRange.RangeAddress.LastAddress.RowNumber, fullRange.RangeAddress.LastAddress.ColumnNumber);
                const int movieNameCell = 1;
                const int yearCell = 2;
                const int processedCell = 3;
                const int downloadedCell = 4;

                var fullCount = 0;

                foreach (var row in rangeUsed.RowsUsed())
                {
                    if (fullCount > 100) break;
                    // must have 4 cells, and the processed cell must not contain an x
                    if (row.CellCount() == 4 && !row.Cell(processedCell).GetValue<string>().Equals("x"))
                    {
                        var urls = CreateUrl(options.BaseUrl, row.Cell(movieNameCell).GetValue<string>(), row.Cell(yearCell).GetValue<string>());
                        var downloaded = false;
                        foreach (var url in urls)
                        {
                            Thread.Sleep(1000 * 30); // wait 30 seconds

                            var hash = GetTorrentMagnetHash(PullSource(url));
                            if (string.IsNullOrEmpty(hash)) continue;

                            var torrent = string.Format("http://torcache.net/torrent/{0}.torrent", hash);
                            var result = DownloadTorrent(torrent, options.DownloadPath);

                            if (!result) continue;

                            downloaded = true;
                            break;
                        }
                        row.Cell(processedCell).SetValue("x");
                        if (downloaded)
                            row.Cell(downloadedCell).SetValue("x");
                        fullCount++;
                    }
                }

                workbook.Save();
            }
        }

 //public static void Create()
 //{
 //    ////create new xls file
 //    //string file = "C:\\newdoc.xls";
 //    ////Workbook workbook = new Workbook();
 //    ////Worksheet worksheet = new Worksheet("First Sheet");
 //    ////worksheet.Cells[0, 1] = new Cell((short)1);
 //    ////worksheet.Cells[2, 0] = new Cell(9999999);
 //    ////worksheet.Cells[3, 3] = new Cell((decimal)3.45);
 //    ////worksheet.Cells[2, 2] = new Cell("Text string");
 //    ////worksheet.Cells[2, 4] = new Cell("Second string");
 //    ////worksheet.Cells[4, 0] = new Cell(32764.5, "#,##0.00");
 //    ////worksheet.Cells[5, 1] = new Cell(DateTime.Now, @"YYYY\-MM\-DD");
 //    ////worksheet.Cells.ColumnWidth[0, 1] = 3000;
 //    ////workbook.Worksheets.Add(worksheet);
 //    ////workbook.Save(file);
 //    ////// open xls file
 //    ////Workbook book = Workbook.Load(file);
 //    ////Worksheet sheet = book.Worksheets[0];
 //    //var wb = new XLWorkbook();
 //    //var ws = wb.Worksheets.Add("Contacts");
 //    //// Title
 //    //ws.Cell("B2").Value = "Contacts";
 //    //// First Names
 //    //ws.Cell("B3").Value = "FName";
 //    //ws.Cell("B4").Value = "John";
 //    //ws.Cell("B5").Value = "Hank";
 //    //ws.Cell("B6").Value = "Dagny";
 //    //// Last Names
 //    //ws.Cell("C3").Value = "LName";
 //    //ws.Cell("C4").Value = "Galt";
 //    //ws.Cell("C5").Value = "Rearden";
 //    //ws.Cell("C6").Value = "Taggart";
 //    //// Boolean
 //    //ws.Cell("D3").Value = "Outcast";
 //    //ws.Cell("D4").Value = true;
 //    //ws.Cell("D5").Value = false;
 //    //ws.Cell("D6").Value = false;
 //    //// DateTime
 //    //ws.Cell("E3").Value = "DOB";
 //    //ws.Cell("E4").Value = new DateTime(1919, 1, 21);
 //    //ws.Cell("E5").Value = new DateTime(1907, 3, 4);
 //    //ws.Cell("E6").Value = new DateTime(1921, 12, 15);
 //    //// Numeric
 //    //ws.Cell("F3").Value = "Income";
 //    //ws.Cell("F4").Value = 2000;
 //    //ws.Cell("F5").Value = 40000;
 //    //ws.Cell("F6").Value = 10000;
 //    //// From worksheet
 //    //var rngTable = ws.Range("B2:F6");
 //    //// From another range
 //    //var rngDates = rngTable.Range("D3:D5"); // The address is relative to rngTable (NOT the worksheet)
 //    //var rngNumbers = rngTable.Range("E3:E5"); // The address is relative to rngTable (NOT the worksheet)
 //    //// Using OpenXML's predefined formats
 //    //rngDates.Style.NumberFormat.NumberFormatId = 15;
 //    //// Using a custom format
 //    //rngNumbers.Style.NumberFormat.Format = "$ #,##0";
 //    //var rngHeaders = rngTable.Range("A2:E2"); // The address is relative to rngTable (NOT the worksheet)
 //    //rngHeaders.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
 //    //rngHeaders.Style.Font.Bold = true;
 //    //rngHeaders.Style.Fill.BackgroundColor = XLColor.Aqua;
 //    //rngTable.Style.Border.BottomBorder = XLBorderStyleValues.Thin;
 //    //rngTable.Cell(1, 1).Style.Font.Bold = true;
 //    //rngTable.Cell(1, 1).Style.Fill.BackgroundColor = XLColor.CornflowerBlue;
 //    //rngTable.Cell(1, 1).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
 //    //rngTable.Row(1).Merge(); // We could've also used: rngTable.Range("A1:E1").Merge()
 //    ////Add a thick outside border
 //    //rngTable.Style.Border.OutsideBorder = XLBorderStyleValues.Thick;
 //    //// You can also specify the border for each side with:
 //    //// rngTable.FirstColumn().Style.Border.LeftBorder = XLBorderStyleValues.Thick;
 //    //// rngTable.LastColumn().Style.Border.RightBorder = XLBorderStyleValues.Thick;
 //    //// rngTable.FirstRow().Style.Border.TopBorder = XLBorderStyleValues.Thick;
 //    //// rngTable.LastRow().Style.Border.BottomBorder = XLBorderStyleValues.Thick;
 //    //ws.Columns(2, 6).AdjustToContents();
 //    //wb.SaveAs("BasicTable.xlsx");
 //    //CreateResult();
 //}
 public static void CreateResult(ExcelModel model, bool needToSave = true)
 {
     model.SetMeltsOxides();
     Model = model;
     var disk = AppDomain.CurrentDomain.BaseDirectory.ToCharArray().First();
     string fileName = string.Format(@"{1}:\result{0}.xlsx", DateTime.Now.ToString("dd-MM-yy_hhmm"), disk);
     string template = "template.xlsx";
     var wb = new XLWorkbook(template);
     var ws = wb.Worksheet("result");
     CreateHeader(ws);
     SetRows(ws);
     SetData(ws);
     wb.SaveAs(fileName);
     if (needToSave)
     {
         Sqlite.SaveProject(Model);
     }
     Model = null;
 }

 public void Test()
 {
     XLWorkbook wb = new XLWorkbook("D:\\aaa.xlsx");
     wb.Worksheet("Sheet1").Cell(1, 5).Value = 3;
     wb.Worksheet("Sheet1").Cell(2, 5).Value = "BBB";
     wb.Worksheet("Sheet1").Cell(3, 5).SetValue("ASDASDASDASD");
     //wb.AddWorksheet("CCC");
     wb.SaveAs("D:\\bbb.xlsx");
     //wb.Save();
 }

        public void InsertingSheets3()
        {
            var wb = new XLWorkbook();
            wb.Worksheets.Add("Sheet3");
            wb.Worksheets.Add("Sheet2", 1);
            wb.Worksheets.Add("Sheet1", 1);

            Assert.AreEqual("Sheet1", wb.Worksheet(1).Name);
            Assert.AreEqual("Sheet2", wb.Worksheet(2).Name);
            Assert.AreEqual("Sheet3", wb.Worksheet(3).Name);
        }

        public void DeletingSheets1()
        {
            var wb = new XLWorkbook();
            wb.Worksheets.Add("Sheet3");
            wb.Worksheets.Add("Sheet2");
            wb.Worksheets.Add("Sheet1", 1);

            wb.Worksheet("Sheet3").Delete();

            Assert.AreEqual("Sheet1", wb.Worksheet(1).Name);
            Assert.AreEqual("Sheet2", wb.Worksheet(2).Name);
            Assert.AreEqual(2, wb.Worksheets.Count);
        }

        public TranslationData Read(string srcPath)
        {
            // ワークブックの作成
            using (var workbook = new XLWorkbook(srcPath))
            {
                // ワークブック指定の名前のシートを取得
                var worksheet = workbook.Worksheet(ExcelConverter.SheetName);


                var dataRange = worksheet.Range(LanguageTitleRow,
                                                DataColumn,
                                                worksheet.LastRowUsed().RowNumber(),
                                                worksheet.LastColumnUsed().ColumnNumber());

                var dataTable = dataRange.AsTable();
                var langList = dataTable.DataRange.Columns()
                                                  .Select(o => new
                                                  {
                                                      Locale = o.Cell(0).GetString(),
                                                      Values = o.Cells().Select(i => new LocalizationItem(worksheet.Cell(i.Address.RowNumber, OffsetColumn).GetString(),
                                                                                                          worksheet.Cell(i.Address.RowNumber, OffsetColumn + 1).GetString(),
                                                                                                          i.GetString()))
                                                  });


                var result = langList.Select(o => new LanguageData(o.Locale, o.Values));

                return new TranslationData(result);
            }
        }

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

        private void findProvderName()
        {
            Point providerLocation = new Point(0, 0);
            //int fileNumber = 0;
            for (int fileNumber = 0; fileNumber < workbooks.Count; fileNumber++)
            {
                workbook = workbooks[fileNumber];
                var sheet = workbook.Worksheet(1);
                var colRange = sheet.Range("A:A");
                foreach (var cell in colRange.CellsUsed())
                {
                    if (cell.Value != null)
                    {
                        String value = (String)cell.Value;
                        int cellRow = cell.Address.RowNumber;
                        if (value.Contains(provider))
                        {
                            providerLocation = new Point(1, cellRow);
                            setMetricDataLocations(providerLocation, fileNumber);
                            break;
                        }
                    }

                }

            }

            //metrics.Insert(0, now.Month +"-" + now.Year);
        }

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

 //var wb = new XLWorkbook(northwinddataXlsx);
 public ExcelHelper(string filePath)
 {
     this.filePath = filePath;
     workbook = new XLWorkbook(filePath);
     if(workbook.Worksheets.Count>0)
         worksheet = workbook.Worksheet(1);
 }

        public void GivenCreateCalled_AndDateDataRows_WhenFillData_ThenDatePrecisionToSecondsPreserved()
        {
            var nowWithSecondPrecision = DateTime.Now.Truncate(TimeSpan.FromSeconds(1));
            var data = new List<List<object>>
            {
                new List<object> { nowWithSecondPrecision },
                new List<object> { nowWithSecondPrecision.AddDays(2) },
                new List<object> { nowWithSecondPrecision.TimeOfDay }
            };
            string outputPath = Path.ChangeExtension(Path.Combine("TestData", MethodBase.GetCurrentMethod().Name), ".xlsx");
            byte[] templateData = File.ReadAllBytes(@"TestData\StudentProfileExportTemplate.xltx");
            using (MemoryStream stream = new MemoryStream())
            {
                stream.Write(templateData, 0, (int)templateData.Length);
                using (var target = new StudentProfileExportFile())
                {
                    target.Create(stream);

                    target.FillData(data);
                }

                File.WriteAllBytes(outputPath, stream.ToArray());
            }
            using (XLWorkbook workbook = new XLWorkbook(outputPath))
            {
                IXLWorksheet worksheet = workbook.Worksheet(1);
                var date1 = worksheet.Cell("A3").GetDateTime();
                var date2 = worksheet.Cell("A4").GetDateTime();
                var timeSpan = worksheet.Cell("A5").GetTimeSpan();
                Assert.AreEqual(data[0][0], date1);
                Assert.AreEqual(data[1][0], date2);
                Assert.AreEqual(data[2][0], timeSpan);
            }
        }

        private void findProvderName()
        {
            Point providerLocation = new Point(0, 0);

             workbook = workbooks[0];
             var sheet = workbook.Worksheet(1);
             var colRange = sheet.Range("A:A");
             foreach (var cell in colRange.CellsUsed())
             {
                 if (cell.Value != null)
                 {
                     String value = (String)cell.Value;
                     int cellRow = cell.Address.RowNumber;
                     if (value.Contains(provider))
                     {
                         providerLocation = new Point(1, cellRow);
                         for (int metricNumber = 0; metricNumber < 8; metricNumber++)
                         {
                             setMetricDataLocations(providerLocation, metricNumber);
                         }
                         break;
                     }
                 }
             }
        }

        public void Create(string filePath)
        {
            var wb = new XLWorkbook();
            foreach (var wsNum in Enumerable.Range(1, 5))
            {
                wb.Worksheets.Add("Original Pos. is " + wsNum.ToString());
            }

            // Move first worksheet to the last position
            wb.Worksheet(1).Position = wb.Worksheets.Count() + 1;

            // Delete worksheet on position 4 (in this case it's where original position = 5)
            wb.Worksheet(4).Delete();

            // Swap sheets in positions 1 and 2
            wb.Worksheet(2).Position = 1;

            wb.SaveAs(filePath);
        }