Exemple #1
13
        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);
                }
            }
        }
        /// Unpacks and writes out the FullNetwork file, returns the filepath 
        /// </summary>
        /// <param name="Network"></param>
        /// <param name="Path"></param>
        public string WriteOutExcelDocument(FullNetwork Network, string FileName)
        {
            // Build data table from the Network
            DataTable Table = new DataTable();
            Table.Columns.Add("Network ID");
            Table.Columns.Add("First Usable");
            Table.Columns.Add("Last Usable");
            Table.Columns.Add("Broadcast Address");

            foreach (var subnet in Network.Subnets)
            {
                // Add the data to each collumn
                DataRow Row = Table.NewRow();

                Row[0] = subnet.NetworkId;
                Row[1] = subnet.FirstUsable;
                Row[2] = subnet.LastUsable;
                Row[3] = subnet.BroadcastAddress;
                Table.Rows.Add(Row);
            }

            string FilePath = FileName + ".xlsx";

            XLWorkbook wb = new XLWorkbook();
            wb.Worksheets.Add(Table, "Subnet Data");
            wb.SaveAs(FilePath);

            return FilePath;
        }
Exemple #3
2
        //---------------------------------------------------------//
        // SubRoutine
        //---------------------------------------------------------//
        private void editExcelSheet(string sheetName, PublishData pd, DataGridView dgv)
        {
            string tempFile = Folder.DefaultLocation() + @"\.~temp.xlsx";

            Cursor.Current = Cursors.WaitCursor;   // マウスカーソルを砂時計(Wait)

            try
            {
                using (oWBook = new XLWorkbook(fileName))
                {
                    oWSheet = oWBook.Worksheet(sheetName);
                    switch (sheetName)
                    {
                    case "ContractWorks":
                        editContractWorks(pd, dgv);
                        break;

                    case "ContractSummary":
                        editContractSummary(pd, dgv);
                        break;

                    default:
                        break;
                    }
                    oWBook.SaveAs(tempFile);      // Excel保存
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                Cursor.Current = Cursors.Default;  // マウスカーソルを戻す
                return;
            }

            Cursor.Current = Cursors.Default;                        // マウスカーソルを戻す
            System.Diagnostics.Process.Start("Excel.exe", tempFile); // 表示用Excel
        }
 //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);
 }
 /// <summary>
 /// Creates a new parser using the given <see cref="IXLWorksheet"/> and <see cref="CsvConfiguration"/>.
 /// </summary>
 /// <param name="worksheet">The <see cref="IXLWorksheet"/> with the data.</param>
 /// <param name="configuration">The configuration.</param>
 public ExcelParser(IXLWorksheet worksheet, CsvConfiguration configuration)
 {
     workbook = worksheet.Workbook;
     this.worksheet = worksheet;
     this.configuration = configuration;
     FieldCount = worksheet.RowsUsed().CellsUsed().Max(cell => cell.Address.ColumnNumber);
 }
        private static void ExportToExcelFile(DataGridView dGV, string filename, string tabName)
        {
            //Creating DataTable
            DataTable dt = new DataTable();

            //Adding the Columns
            foreach (DataGridViewColumn column in dGV.Columns)
            {
                dt.Columns.Add(column.HeaderText, column.ValueType);
            }

            //Adding the Rows
            foreach (DataGridViewRow row in dGV.Rows)
            {
                dt.Rows.Add();
                foreach (DataGridViewCell cell in row.Cells)
                {
                    dt.Rows[dt.Rows.Count - 1][cell.ColumnIndex] = cell.Value.ToString();
                }
            }

            using (XLWorkbook wb = new XLWorkbook())
            {
                wb.Worksheets.Add(dt, tabName);
                wb.SaveAs(filename);
            }
        }  
Exemple #7
0
        public void Create(String filePath)
        {
            var workbook = new XLWorkbook();
            var ws = workbook.Worksheets.Add("Style Worksheet");

            ws.Style.Font.Bold = true;
            ws.Style.Font.FontColor = XLColor.Red;
            ws.Style.Fill.BackgroundColor = XLColor.Cyan;

            // The following cells will be bold and red
            // because we've specified those attributes to the entire worksheet
            ws.Cell(1, 1).Value = "Test";
            ws.Cell(1, 2).Value = "Case";

            // Here we'll change the style of a single cell
            ws.Cell(2, 1).Value = "Default";
            ws.Cell(2, 1).Style = XLWorkbook.DefaultStyle;

            // Let's play with some rows
            ws.Row(4).Style = XLWorkbook.DefaultStyle;
            ws.Row(4).Height = 20;
            ws.Rows(5, 6).Style = XLWorkbook.DefaultStyle;
            ws.Rows(5, 6).Height = 20;

            // Let's play with some columns
            ws.Column(4).Style = XLWorkbook.DefaultStyle;
            ws.Column(4).Width = 5;
            ws.Columns(5, 6).Style = XLWorkbook.DefaultStyle;
            ws.Columns(5, 6).Width = 5;

            workbook.SaveAs(filePath);
        }
Exemple #8
0
 public void IsBlank_false()
 {
     var ws = new XLWorkbook().AddWorksheet("Sheet");
     ws.Cell("A1").Value = " ";
     var actual = ws.Evaluate("=IsBlank(A1)");
     Assert.AreEqual(false, actual);
 }
Exemple #9
0
        public void CreateExcelForPdf(string sheetName, PublishData pd, DataGridView dgv)
        {
            //string tempFile = Folder.DefaultLocation() + @"\.~temp.xlsx";
            string tempFile = "";

            Cursor.Current = Cursors.WaitCursor;   // マウスカーソルを砂時計(Wait)
            try
            {
                using (oWBook = new XLWorkbook(fileName))
                {
                    oWSheet = oWBook.Worksheet(sheetName);
                    switch (sheetName)
                    {
                    case "Volume":
                        tempFile = Folder.DefaultLocation() + @"\" + pd.vTaskCode + ".xlsx";
                        editContractWorks(pd, dgv);
                        break;

                    default:
                        break;
                    }
                    oWBook.SaveAs(tempFile);        // Excel保存
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                Cursor.Current = Cursors.Default;   // マウスカーソルを戻す
                return;
            }
            Cursor.Current = Cursors.Default;       // マウスカーソルを戻す
        }
Exemple #10
0
        private static void CreateExcelFile(ChromeDriver driver)
        {
            var upcomingEvents = driver.FindElementsByCssSelector(".event.active");

            var wb = new XLWorkbook();
            var ws = wb.Worksheets.Add("Games");

            ws.Cell("A1").Value = "Games";
            ws.Cell("B1").Value = "Home";
            ws.Cell("C1").Value = "Draw";
            ws.Cell("D1").Value = "Away";

            for (int i = 0; i < upcomingEvents.Count; i++)
            {
                string eventName = upcomingEvents[i].FindElement(By.ClassName("eventName")).Text;
                string homeOdd = upcomingEvents[i].FindElement(By.ClassName("home")).Text;
                string drawOdd = upcomingEvents[i].FindElement(By.ClassName("draw")).Text;
                string awayOdd = upcomingEvents[i].FindElement(By.ClassName("away")).Text;

                ws.Cell("A" + (i + 2)).Value = eventName;
                ws.Cell("B" + (i + 2)).Value = homeOdd;
                ws.Cell("C" + (i + 2)).Value = drawOdd;
                ws.Cell("D" + (i + 2)).Value = awayOdd;
            }

            // Beautify
            ws.Range("A1:D1").Style.Font.Bold = true;
            ws.Columns().AdjustToContents();

            wb.SaveAs("../../../../Events.xlsx");
        }
        private void WriteCategoriesByMonth(XLWorkbook workbook)
        {
            var ws = workbook.AddWorksheet("Categorization (by month)");

            var all_posts = profile.AggregatedPosts().OrderBy(p => p.Date);
            var first_date = all_posts.First().Date;
            var last_date = all_posts.Last().Date;

            var all_categories = Category.Flatten(profile.RootCategory);
            var posts_by_category = all_categories.ToDictionary(category => category, category => category.AggregatePosts().Distinct().ToList());

            ws.Row(1).Cell(1).SetValue("Date");
            for (int i = 0; i < all_categories.Count; i++)
            {
                ws.Row(1).Cell(i + 2).SetValue(all_categories[i].Name);
            }

            var current_date = new DateTime(first_date.Year, first_date.Month, 1);
            last_date = new DateTime(last_date.Year, last_date.Month, 1).AddMonths(1);
            var current_row = 2;
            while (current_date != last_date)
            {
                ws.Row(current_row).Cell(1).SetValue(current_date.ToShortDateString());

                for (int j = 0; j < all_categories.Count; j++)
                {
                    var total = posts_by_category[all_categories[j]].Where(p => p.Date.Year == current_date.Year && p.Date.Month == current_date.Month).Sum(p => p.Value);
                    ws.Row(current_row).Cell(j + 2).SetValue(total);
                }

                current_row++;
                current_date = current_date.AddMonths(1);
            }
        }
        public void Then_feature_with_background_is_added_successfully()
        {
            var excelFeatureFormatter = Container.Resolve<ExcelFeatureFormatter>();

            var feature = new Feature
            {
                Name = "Test Feature",
                Description =
                    "In order to test this feature,\nAs a developer\nI want to test this feature",
            };
            var background = new Scenario
            {
                Name = "Test Background Scenario",
                Description =
                    "In order to test this background,\nAs a developer\nI want to test this background"
            };
            var given = new Step { NativeKeyword = "Given", Name = "a precondition" };
            background.Steps = new List<Step>(new[] { given });
            feature.AddBackground(background);

            using (var workbook = new XLWorkbook())
            {
                IXLWorksheet worksheet = workbook.AddWorksheet("SHEET1");
                excelFeatureFormatter.Format(worksheet, feature);

                Check.That(worksheet.Cell("B4").Value).IsEqualTo(background.Name);
                Check.That(worksheet.Cell("C5").Value).IsEqualTo(background.Description);
                Check.That(worksheet.Cell("D6").Value).IsEqualTo(given.Name);
            }
        }
        public void InsertingColumnsPreservesFormatting()
        {
            var wb = new XLWorkbook();
            IXLWorksheet ws = wb.Worksheets.Add("Sheet");
            IXLColumn column1 = ws.Column(1);
            column1.Style.Fill.SetBackgroundColor(XLColor.FrenchLilac);
            column1.Cell(2).Style.Fill.SetBackgroundColor(XLColor.Fulvous);
            IXLColumn column2 = ws.Column(2);
            column2.Style.Fill.SetBackgroundColor(XLColor.Xanadu);
            column2.Cell(2).Style.Fill.SetBackgroundColor(XLColor.MacaroniAndCheese);

            column1.InsertColumnsAfter(1);
            column1.InsertColumnsBefore(1);
            column2.InsertColumnsBefore(1);

            Assert.AreEqual(ws.Style.Fill.BackgroundColor, ws.Column(1).Style.Fill.BackgroundColor);
            Assert.AreEqual(XLColor.FrenchLilac, ws.Column(2).Style.Fill.BackgroundColor);
            Assert.AreEqual(XLColor.FrenchLilac, ws.Column(3).Style.Fill.BackgroundColor);
            Assert.AreEqual(XLColor.FrenchLilac, ws.Column(4).Style.Fill.BackgroundColor);
            Assert.AreEqual(XLColor.Xanadu, ws.Column(5).Style.Fill.BackgroundColor);

            Assert.AreEqual(ws.Style.Fill.BackgroundColor, ws.Cell(2, 1).Style.Fill.BackgroundColor);
            Assert.AreEqual(XLColor.Fulvous, ws.Cell(2, 2).Style.Fill.BackgroundColor);
            Assert.AreEqual(XLColor.Fulvous, ws.Cell(2, 3).Style.Fill.BackgroundColor);
            Assert.AreEqual(XLColor.Fulvous, ws.Cell(2, 4).Style.Fill.BackgroundColor);
            Assert.AreEqual(XLColor.MacaroniAndCheese, ws.Cell(2, 5).Style.Fill.BackgroundColor);
        }
Exemple #14
0
        // Public
        public void Create(String filePath)
        {
            #region Create case
            {
                var workbook = new XLWorkbook();
                var ws = workbook.Worksheets.Add("Delete red rows");

                // Put a value in a few cells
                foreach (var r in Enumerable.Range(1, 5))
                    foreach (var c in Enumerable.Range(1, 5))
                        ws.Cell(r, c).Value = string.Format("R{0}C{1}", r, c);

                var blueRow = ws.Rows(1, 2);
                var redRow = ws.Row(5);

                blueRow.Style.Fill.BackgroundColor = XLColor.Blue;

                redRow.Style.Fill.BackgroundColor = XLColor.Red;
                workbook.SaveAs(filePath);
            }
            #endregion

            #region Remove rows
            {
                var workbook = new XLWorkbook(filePath);
                var ws = workbook.Worksheets.Worksheet("Delete red rows");

                ws.Rows(1, 2).Delete();
                workbook.Save();
            }
            #endregion
        }
        public void InsertingRowsPreservesFormatting()
        {
            var wb = new XLWorkbook();
            IXLWorksheet ws = wb.Worksheets.Add("Sheet");
            IXLRow row1 = ws.Row(1);
            row1.Style.Fill.SetBackgroundColor(XLColor.FrenchLilac);
            row1.Cell(2).Style.Fill.SetBackgroundColor(XLColor.Fulvous);
            IXLRow row2 = ws.Row(2);
            row2.Style.Fill.SetBackgroundColor(XLColor.Xanadu);
            row2.Cell(2).Style.Fill.SetBackgroundColor(XLColor.MacaroniAndCheese);

            row1.InsertRowsBelow(1);
            row1.InsertRowsAbove(1);
            row2.InsertRowsAbove(1);

            Assert.AreEqual(ws.Style.Fill.BackgroundColor, ws.Row(1).Style.Fill.BackgroundColor);
            Assert.AreEqual(XLColor.FrenchLilac, ws.Row(2).Style.Fill.BackgroundColor);
            Assert.AreEqual(XLColor.FrenchLilac, ws.Row(3).Style.Fill.BackgroundColor);
            Assert.AreEqual(XLColor.FrenchLilac, ws.Row(4).Style.Fill.BackgroundColor);
            Assert.AreEqual(XLColor.Xanadu, ws.Row(5).Style.Fill.BackgroundColor);

            Assert.AreEqual(ws.Style.Fill.BackgroundColor, ws.Cell(1, 2).Style.Fill.BackgroundColor);
            Assert.AreEqual(XLColor.Fulvous, ws.Cell(2, 2).Style.Fill.BackgroundColor);
            Assert.AreEqual(XLColor.Fulvous, ws.Cell(3, 2).Style.Fill.BackgroundColor);
            Assert.AreEqual(XLColor.Fulvous, ws.Cell(4, 2).Style.Fill.BackgroundColor);
            Assert.AreEqual(XLColor.MacaroniAndCheese, ws.Cell(5, 2).Style.Fill.BackgroundColor);
        }
        public void Build(GeneralTree<INode> features)
        {
            if (Log.IsInfoEnabled)
            {
                Log.Info("Writing Excel workbook to {0}", this.configuration.OutputFolder.FullName);
            }

            string spreadsheetPath = this.fileSystem.Path.Combine(this.configuration.OutputFolder.FullName, "features.xlsx");
            using (var workbook = new XLWorkbook())
            {
                var actionVisitor = new ActionVisitor<INode>(node =>
                {
                    var featureDirectoryTreeNode =
                        node as FeatureNode;
                    if (featureDirectoryTreeNode != null)
                    {
                        IXLWorksheet worksheet =
                            workbook.AddWorksheet(
                                this.excelSheetNameGenerator.GenerateSheetName(
                                    workbook,
                                    featureDirectoryTreeNode
                                        .Feature));
                        this.excelFeatureFormatter.Format(
                            worksheet,
                            featureDirectoryTreeNode.Feature);
                    }
                });

                features.AcceptVisitor(actionVisitor);

                this.excelTableOfContentsFormatter.Format(workbook, features);

                workbook.SaveAs(spreadsheetPath);
            }
        }
Exemple #17
0
        public ActionResult Import(int?IDNamHoc, HttpPostedFileBase excel, string LHDT, int sheet = 1)
        {
            if (Path.GetExtension(excel.FileName) != ".xlsx")
            {
                ViewBag.IDNamHocs = db.NamHocs.Where(q => q.Active != false).CreateSelectList(q => q.IDNamHoc, q => q.mNamHoc, IDNamHoc);
                ModelState.AddModelError("", "Vui lòng chọn tệp tin định dạng .xlsx");
                return(View());
            }
            var workbook = new ClosedXML.Excel.XLWorkbook(excel.InputStream);
            var ws       = workbook.Worksheet(sheet);
            var lst      = (from row in ws.Rows(2, ws.RowsUsed().Count())
                            select GetLopHP(IDNamHoc, row)).ToList();

            lst.ForEach(q =>
            {
                if (q != null)
                {
                    q.LHDT = LHDT;
                    _fillHeSo(q);
                    db.LopHPs.Add(q);
                }
            });
            db.SaveChanges();
            return(RedirectToAction("Index", new { IDNamHoc }));
        }
Exemple #18
0
        //---------------------------------------------------------//
        //      Method
        //---------------------------------------------------------//
        public void ExcelFile()
        {
            string tempFile = Folder.DefaultLocation() + @"\.~temp.xlsx";

            Cursor.Current = Cursors.WaitCursor;                // マウスカーソルを砂時計(Wait)

            try
            {
                using (oWBook = new XLWorkbook(fileName))
                {
                    // 編集
                    oWSheet = oWBook.Worksheet(1);      // シートを開く
                    editReviewData(pubDat);

                    // 保存
                    oWBook.SaveAs(tempFile);                    // Excel保存
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                Cursor.Current = Cursors.Default;               // マウスカーソルを戻す
                return;
            }

            Cursor.Current = Cursors.Default;                        // マウスカーソルを戻す
            System.Diagnostics.Process.Start("Excel.exe", tempFile); // 表示用Excel
        }
Exemple #19
0
        //----------------------------------------------------------------------
        // SubRoutine
        //----------------------------------------------------------------------
        private void editExcelSheet(string sheetName, TaskData td, TaskNoteData tnd, TaskIndData[] tid, PartnersData pd, TaskOp tod)
        {
            string tempFile = Folder.DefaultLocation() + @"\.~temp.xlsx";

            Cursor.Current = Cursors.WaitCursor;   // マウスカーソルを砂時計(Wait)

            using (oWBook = new XLWorkbook(FileName))
            {
                // シートを開く
                //oWSheet = oWBook.Worksheet(sheetName);
                oWSheet = oWBook.Worksheet(1);
                // 編集
                //MessageBox.Show("Excel書込み開始");
                editTaskDataPart(td);
                editTaskNoteDataPart(tnd);
                editTaskIndDataPart(tid);
                editPartnersDataPart(pd);
                editPersonsDataPart(tod);
                // 保存
                oWBook.SaveAs(tempFile);      // Excel保存
            }

            Cursor.Current = Cursors.Default;                        // マウスカーソルを戻す

            System.Diagnostics.Process.Start("Excel.exe", tempFile); // 表示用Excel

            //if (File.Exists(tempFile)) File.Delete(tempFile);
        }
        public void ThenTableAddedSuccessfully()
        {
            var excelTableFormatter = Container.Resolve<ExcelTableFormatter>();
            var table = new Table();
            table.HeaderRow = new TableRow("Var1", "Var2", "Var3", "Var4");
            table.DataRows =
                new List<TableRow>(new[] {new TableRow("1", "2", "3", "4"), new TableRow("5", "6", "7", "8")});

            using (var workbook = new XLWorkbook())
            {
                IXLWorksheet worksheet = workbook.AddWorksheet("SHEET1");
                int row = 6;
                excelTableFormatter.Format(worksheet, table, ref row);

                Check.That(worksheet.Cell("D6").Value).IsEqualTo("Var1");
                Check.That(worksheet.Cell("E6").Value).IsEqualTo("Var2");
                Check.That(worksheet.Cell("F6").Value).IsEqualTo("Var3");
                Check.That(worksheet.Cell("G6").Value).IsEqualTo("Var4");
                Check.That(worksheet.Cell("D7").Value).IsEqualTo(1.0);
                Check.That(worksheet.Cell("E7").Value).IsEqualTo(2.0);
                Check.That(worksheet.Cell("F7").Value).IsEqualTo(3.0);
                Check.That(worksheet.Cell("G7").Value).IsEqualTo(4.0);
                Check.That(worksheet.Cell("D8").Value).IsEqualTo(5.0);
                Check.That(worksheet.Cell("E8").Value).IsEqualTo(6.0);
                Check.That(worksheet.Cell("F8").Value).IsEqualTo(7.0);
                Check.That(worksheet.Cell("G8").Value).IsEqualTo(8.0);
                Check.That(row).IsEqualTo(9);
            }
        }
 public static void Basic()
 {
     var wb = new XLWorkbook();
     var ws = wb.AddWorksheet("Worksheet Name");
     ws.Cell("A1").Value = Data;
     wb.SaveAs(MethodBase.GetCurrentMethod().Name + ".xlsx");
 }
Exemple #22
0
 public void Dispose()
 {
     if (_wb != null)
     {
         _wb.Dispose();
     }
     _wb = null;
 }
        static void Main(string[] args)
        {
            //Console.WriteLine("Hello World!");

            var s     = GetExcelPath();
            var bytes = GetExcel(s);
            var wb    = new ClosedXML.Excel.XLWorkbook(new MemoryStream(bytes));

            byte[] file = System.IO.File.ReadAllBytes([ExcelPath])
Exemple #24
0
        internal static void VerifyExcel(MemoryStream stream,
                                         [CallerMemberName] string?caller = null)
        {
            stream.Seek(0, SeekOrigin.Begin);
            //File.WriteAllBytes(caller + ".xlsx", stream.ToArray());
            var book = new ClosedXML.Excel.XLWorkbook(stream);

            VerifyExcel(book.Worksheet(1), caller);
        }
        public static XLS.XLWorkbook getClosedXMLWorkbook()
        {
            XLS.XLWorkbook workbook  = new XLS.XLWorkbook(@"C:\Users\fleet\Documents\cruises.xlsx");
            var            worksheet = workbook.Worksheet(1);
            var            usedRange = worksheet.RangeUsed();

            Console.WriteLine("USED: {0}", usedRange.RowCount());

            return(workbook);
        }
Exemple #26
0
        //作業割り当て表Excelファイル出力
        public void save_asignlist_xlsx_as(List <List <string> > data, List <string> categories, string filename)
        {
            _write_log __write_log = write_log;

            try
            {
                using (var wb = new ClosedXML.Excel.XLWorkbook())
                {
                    var ws = wb.Worksheets.Add("Sheet1");

                    //行のループ
                    for (int i = 0; i < data.Count; i++)
                    {
                        List <string> row = (List <string>)data[i];

                        //列のループ
                        for (int j = 0; j < row.Count; j++)
                        {
                            string col = (string)row[j];
                            ws.Cell(i + 1, j + 1).SetValue <string>(fetch_overflow_characters(col));
                            ws.Cell(i + 1, j + 1).Style.Border.TopBorder    = XLBorderStyleValues.Thin;
                            ws.Cell(i + 1, j + 1).Style.Border.BottomBorder = XLBorderStyleValues.Thin;
                            ws.Cell(i + 1, j + 1).Style.Border.LeftBorder   = XLBorderStyleValues.Thin;
                            ws.Cell(i + 1, j + 1).Style.Border.RightBorder  = XLBorderStyleValues.Thin;
                            ws.Cell(i + 1, j + 1).Style.Font.FontName       = "MS Pゴシック";
                            ws.Cell(i + 1, j + 1).Style.Alignment.SetVertical(XLAlignmentVerticalValues.Top);
                        }

                        if (i == 0)
                        {
                            int maxcol = row.Count;
                            for (int z = 0; z < categories.Count; z++)
                            {
                                ws.Cell(i + 1, maxcol + (z + 1)).SetValue <string>((string)categories[z]);
                                ws.Cell(i + 1, maxcol + (z + 1)).Style.Border.TopBorder    = XLBorderStyleValues.Thin;
                                ws.Cell(i + 1, maxcol + (z + 1)).Style.Border.BottomBorder = XLBorderStyleValues.Thin;
                                ws.Cell(i + 1, maxcol + (z + 1)).Style.Border.LeftBorder   = XLBorderStyleValues.Thin;
                                ws.Cell(i + 1, maxcol + (z + 1)).Style.Border.RightBorder  = XLBorderStyleValues.Thin;
                                ws.Cell(i + 1, maxcol + (z + 1)).Style.Font.FontName       = "MS Pゴシック";
                                ws.Cell(i + 1, maxcol + (z + 1)).Style.Alignment.SetVertical(XLAlignmentVerticalValues.Top);
                                ws.Cell(i + 1, maxcol + (z + 1)).Style.Alignment.TopToBottom = true;
                            }
                        }
                    }

                    wb.SaveAs(filename);
                    main_form.Invoke(__write_log, "保存に成功しました。(" + filename + ")");
                }
            }
            catch (Exception ex)
            {
                main_form.Invoke(__write_log, "【エラー】" + ex.Message);
                return;
            }
        }
Exemple #27
0
        public static void ExcelWorkingReader(string strPath)
        {
            Excel.XLWorkbook xFile = new Excel.XLWorkbook(strPath);

            foreach (Excel.IXLWorksheet Isheet in xFile.Worksheets)
            {
                if (Isheet.Name == "T1科技")
                {
                    ReadT1Item(Isheet);
                }
            }
        }
Exemple #28
0
        public static void ExcelP4Break(string strPath)
        {
            Excel.XLWorkbook xFile = new Excel.XLWorkbook(strPath);

            foreach (Excel.IXLWorksheet Isheet in xFile.Worksheets)
            {
                if (Isheet.Name == "P4碎铁")
                {
                    ReadP4Break(Isheet);
                }
            }
        }
Exemple #29
0
        public static void ExcelMoonChart(string strPath)
        {
            Excel.XLWorkbook xFile = new Excel.XLWorkbook(strPath);

            foreach (Excel.IXLWorksheet Isheet in xFile.Worksheets)
            {
                if (Isheet.Name == "卫星矿")
                {
                    ReadIceChart(Isheet);
                }
            }
        }
Exemple #30
0
        public static void ExcelOreRecycle(string strPath)
        {
            Excel.XLWorkbook xFile = new Excel.XLWorkbook(strPath);

            foreach (Excel.IXLWorksheet Isheet in xFile.Worksheets)
            {
                if (Isheet.Name == "基础矿石")
                {
                    ReadOreRecycle(Isheet);
                }
            }
        }
Exemple #31
0
        public static void ExcelT2High(string strPath)
        {
            Excel.XLWorkbook xFile = new Excel.XLWorkbook(strPath);

            foreach (Excel.IXLWorksheet Isheet in xFile.Worksheets)
            {
                if (Isheet.Name == "T2高级反应")
                {
                    ReadT2High(Isheet);
                }
            }
        }
Exemple #32
0
 public void Export()
 {
     using (this.workbook = new ClosedXML.Excel.XLWorkbook())
     {
         foreach (var item in sheetTemplate)
         {
             var sheet = this.workbook.AddWorksheet(item.Name);
             sheet = item.GetTemplate(sheet);
         }
         this.Response();
     }
 }
Exemple #33
0
        public void ExportExcel(DataSet ds, string destination)
        {
            var workbook = new ClosedXML.Excel.XLWorkbook();

            foreach (DataTable dt in ds.Tables)
            {
                var worksheet = workbook.Worksheets.Add(dt.TableName);
                worksheet.Cell(1, 1).InsertTable(dt);
                worksheet.Columns().AdjustToContents();
            }
            workbook.SaveAs(destination);
            workbook.Dispose();
        }
Exemple #34
0
        // Wakamatsu
        // Wakamatsu 20170301
        //private string editExcelSheet(string sheetName, DataTable dt, FormatSet[] FormatSet)
        private string editExcelSheet(string FileName, string sheetName, DataTable dt, FormatSet[] FormatSet)
        {
            // Wakamatsu 20170301
            //string tempFile = Folder.MyDocuments() + @"\" + sheetName + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx";
            string tempFile = Folder.MyDocuments() + @"\" + FileName + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx";

            // Wakamatsu 20170301
            Cursor.Current = Cursors.WaitCursor;   // マウスカーソルを砂時計(Wait)

            if (System.IO.File.Exists(fileName) == false)
            {
                Cursor.Current = Cursors.Default;  // マウスカーソルを戻す
                return("× テンプレートファイルが存在しません。\r\n");
            }

            // Wakamatsu 20170322
            //using(oWBook = new XLWorkbook(fileName))
            //{
            try
            {
                // Wakamatsu 20170322
                using (oWBook = new XLWorkbook(fileName))
                {
                    oWSheet = oWBook.Worksheet(sheetName);

                    // Excelファイル出力
                    if (MasterExport(dt, FormatSet) == true)
                    {
                        oWBook.SaveAs(tempFile);          // Excel保存
                        Cursor.Current = Cursors.Default; // マウスカーソルを戻す
                        return("○ Excel出力が正常に終了しました。\r\n" +
                               " " + dt.Rows.Count + "件のデータを出力しました。\r\n");
                    }
                    else
                    {
                        Cursor.Current = Cursors.Default;  // マウスカーソルを戻す
                        return("× Excel出力ができませんでした。\r\n");
                    }
                }
            }
            catch (Exception ex)
            {
                Cursor.Current = Cursors.Default;  // マウスカーソルを戻す
                // Wakamatsu 20170322
                //MessageBox.Show(ex.Message);
                //return "× Excel出力ができませんでした。\r\n";
                return(ex.Message + "\r\n× Excel出力ができませんでした。\r\n");
                // Wakamatsu 20170322
            }
            //}
        }
Exemple #35
0
 public void Export()
 {
     using (this.workbook = new ClosedXML.Excel.XLWorkbook())
     {
         foreach (var item in sheets)
         {
             var sheet           = this.workbook.AddWorksheet(item.SheetName);
             WorksheetManager sh = new WorksheetManager(sheet);
             sh.Setting = item;
             sheet      = sh.RenderSheet();
         }
         this.Response();
     }
 }
Exemple #36
0
        public static void ExcelReader(string strPath)
        {
            Excel.XLWorkbook xFile = new Excel.XLWorkbook(strPath);

            foreach (Excel.IXLWorksheet Isheet in xFile.Worksheets)
            {
                if (Isheet.Name == "物品列表")
                {
                    ReadItem(Isheet);
                }
                //if (Isheet.Name == "星系列表")
                //{
                //    ReadSolarSystem(Isheet);
                //}
            }
        }
Exemple #37
0
        private static void getReport()
        {
            DAL       objDAL         = new DAL();
            DataTable dt             = new DataTable();
            DataTable dtConfigReport = getConfig("Report");
            string    filePath       = ConfigurationManager.AppSettings["folderPath"].ToString() + "WeeklyReport.xlsx";
            string    Query          = "usp_get_WeeklyReportData";

            objDAL.CommandText = Query;
            dt = objDAL.ExecuteDataSet().Tables[0];
            XLWorkbook wb = new ClosedXML.Excel.XLWorkbook();

            wb.Worksheets.Add(dt);
            wb.SaveAs(filePath);
            sendEmail("", dtConfigReport, "", filePath);
        }
Exemple #38
0
        private void btnFullMetal_Click(object sender, EventArgs e)
        {
            string strKeyWord = "毒蜥级";
            string strPath    = Application.StartupPath + "\\EXCEL\\" + strKeyWord + "制造方案";

            Excel.XLWorkbook   xFile   = new Excel.XLWorkbook();
            Excel.IXLWorksheet xLSheet = xFile.AddWorksheet();

            int nRow = 1;
            int nCol = 1;

            OutputBluePrintResult(xLSheet, strKeyWord, ref nRow, ref nCol);

            xFile.SaveAs(strPath + ".xlsx");
            MessageBox.Show("计算完成,路径:" + strPath);
        }
Exemple #39
0
        private void btnLoadExcel_Click(object sender, EventArgs e)
        {
            var s     = GetExcelPath();
            var bytes = GetExcel(s);
            var wb    = new ClosedXML.Excel.XLWorkbook(new MemoryStream(bytes));



            var l = wb.ReadTable(1,
                                 new ReadOptions()
            {
                TitlesInFirstRow = true
            });

            dataGridView1.DataSource = l;
        }
        static void Main(string[] args)
        {
            //Console.WriteLine("Hello World!");

            var s     = GetExcelPath();
            var bytes = GetExcel(s);
            var wb    = new ClosedXML.Excel.XLWorkbook(new MemoryStream(bytes));

            IEnumerable <SimpleTable> data = wb.ReadTable <SimpleTable>(1);


            var l = wb.ReadTable <Models.TableWithHeaders>(1,
                                                           new ReadOptions()
            {
                TitlesInFirstRow = true
            });

            Console.WriteLine(l.ToList().Count);


            //Example with trasnformations
            Expression <Func <string, DateTime> > fConvertData  = _ => DateTime.Now.AddDays(10);
            Expression <Func <string, bool> >     fSelectToBool = c => c == "x";

            var ls = wb.ReadTable <Models.TableWithHeadersAndBoleanConversion>(1,
                                                                               new ReadOptions()
            {
                TitlesInFirstRow = true,
                Converters       = new Dictionary <string, LambdaExpression>()
                {
                    { nameof(TableWithHeadersAndBoleanConversion.Birthday), fConvertData },
                    { nameof(TableWithHeadersAndBoleanConversion.Selected), fSelectToBool },
                }
            });

            wb.Dispose();



            Console.WriteLine(ls.ToList().Count);

            Console.ReadLine();
        }
        public static IList <CruiseData> getCruisedataFromExcel()
        {
            IList <CruiseData> cruisedataList = new List <CruiseData>();

            XLS.XLWorkbook workbook  = new XLS.XLWorkbook(@"C:\Users\fleet\Documents\cruises.xlsx");
            var            worksheet = workbook.Worksheet(1);
            var            usedRange = worksheet.RangeUsed();

            for (int i = 1; i <= usedRange.RowCount(); i++)
            {
                CruiseData cs = new CruiseData();
                cs.cruise = worksheet.Row(i).Cell(1).GetValue <String>();
                cs.ship   = worksheet.Row(i).Cell(2).GetValue <String>();
                cs.price  = worksheet.Row(i).Cell(3).GetValue <String>();
                cruisedataList.Add(cs);
            }
            workbook.Dispose();
            return(cruisedataList);
        }
        private static async Task <XLWorkbook> BuildWorkbook(List <CommonLookup> headerfields, SqlDataReader reader, SqlConnection connection, string filename)
        {
            Console.WriteLine("BuildWorkbook");
            //now let's build a spreadsheet
            XLWorkbook workbook = null;

            var t = Task.Run(() =>
            {
                workbook      = new ClosedXML.Excel.XLWorkbook();
                var worksheet = workbook.Worksheets.Add("Sheet1");

                int row = 1;
                int idx = 1;
                foreach (CommonLookup cl in headerfields)
                {
                    worksheet.Row(row).Cell(idx).SetDataType(XLCellValues.Text);
                    worksheet.Row(row).Cell(idx).SetValue(cl.Code);
                    idx++;
                }

                row = row + 1;
                idx = 1;
                while (reader.Read() && row < 100000)
                {
                    foreach (CommonLookup cl in headerfields)
                    {
                        worksheet.Row(row).Cell(idx).SetDataType(XLCellValues.Text);
                        worksheet.Row(row).Cell(idx).SetValue(reader[cl.CodeDesc].ToString());
                        idx++;
                    }
                    row++;
                    idx = 1;
                }

                workbook.SaveAs(filename);
            });

            t.Wait();

            Console.WriteLine("Finished - BuildingWorkbook");
            return(workbook);
        }
Exemple #43
0
        //Excelファイルに出力
        public void save_xlsx_as(List <List <string> > data, string filename)
        {
            d_messenger message = new d_messenger(w_messenger);

            try
            {
                using (var wb = new ClosedXML.Excel.XLWorkbook())
                {
                    var ws = wb.Worksheets.Add("Sheet1");

                    //行のループ
                    for (int i = 0; i < data.Count; i++)
                    {
                        List <string> row = (List <string>)data[i];

                        //列のループ
                        for (int j = 0; j < row.Count; j++)
                        {
                            string col = (string)row[j];
                            ws.Cell(i + 1, j + 1).SetValue <string>(fetch_overflow_characters(col));
                            ws.Cell(i + 1, j + 1).Style.Border.TopBorder    = XLBorderStyleValues.Thin;
                            ws.Cell(i + 1, j + 1).Style.Border.BottomBorder = XLBorderStyleValues.Thin;
                            ws.Cell(i + 1, j + 1).Style.Border.LeftBorder   = XLBorderStyleValues.Thin;
                            ws.Cell(i + 1, j + 1).Style.Border.RightBorder  = XLBorderStyleValues.Thin;
                            ws.Cell(i + 1, j + 1).Style.Font.FontName       = "MS Pゴシック";
                            ws.Cell(i + 1, j + 1).Style.Alignment.SetVertical(XLAlignmentVerticalValues.Top);
                        }
                    }

                    wb.SaveAs(filename);
                    main_form.Invoke(message, "保存に成功しました。(" + filename + ")");
                }
            }
            catch (Exception ex)
            {
                main_form.Invoke(message, "【エラー】" + ex.Message);
                return;
            }
        }
Exemple #44
0
        private void toolStripButton1_Click(object sender, EventArgs e)
        {
            if (gridVista.SelectedRows.Count != 0)
            {
                try
                {
                    string             carpeta = System.Configuration.ConfigurationManager.AppSettings[companySelected() + "_directorio"].ToString();
                    DcemVwContabilidad item    = (DcemVwContabilidad)gridVista.SelectedRows[0].DataBoundItem;
                    if (item != null)
                    {
                        var    iTipoDoc      = lParametros.Where(x => x.Tipo == item.tipodoc);
                        string nombreArchivo = iTipoDoc.First().Archivo;
                        nombreArchivo = Path.GetFileNameWithoutExtension(nombreArchivo) + "_" + DateTime.Now.ToString("yyyyMMdd HHmmss");
                        string archivo = Path.Combine(carpeta, nombreArchivo + ".xlsx");

                        object    items   = mostrarContenido(item.year1, item.periodid, item.tipodoc);
                        DataTable dtItems = ContabilidadElectronicaPresentacion.ConvierteLinqQueryADataTable((IEnumerable <dynamic>)items);
                        var       wb      = new ClosedXML.Excel.XLWorkbook();

                        dtItems.TableName = "test";
                        wb.Worksheets.Add(dtItems);
                        //wb.Worksheet(1).Cell("B1").Value = "0";

                        //wb.Worksheet(1).Column(2).CellsUsed().SetDataType(XLDataType.Number);

                        //wb.Worksheet(1).Cell("B1").Value = "Saldo Inicial";

                        wb.SaveAs(archivo);
                        UtilitarioArchivos.AbrirArchivo(archivo);
                        lblProcesos.Text = "Archivo guardado en: " + archivo;
                    }
                }
                catch (Exception exl)
                {
                    grid.DataSource = null;
                    lblError.Text   = exl.Message;
                }
            }
        }
Exemple #45
0
        private void button_Click(object sender, EventArgs e)
        {
            if (initProc)
            {
                return;
            }

            Button btn = (Button)sender;

            switch (btn.Name)
            {
            case "buttonOpen":
                //fileName = Files.Open( "M_Calendar.xlsx", Folder.MyDocuments(), "xlsx" );
                fileName = Files.Open(BookName, Folder.MyDocuments(), "xlsx");
                if (fileName == null)
                {
                    textBoxMsg.AppendText("× " + fileName + "は不適切なファイルです。処理続行不可能です。\r\n");
                }
                else
                {
                    textBoxMsg.AppendText("☆ " + fileName + "の内容でカレンダマスタを再作成します。\r\n");
                }
                break;

            case "buttonCancel":
                // Wakamatsu 20170323
                fileName        = null;
                textBoxMsg.Text = "";
                break;

            case "buttonStart":
                if (fileName == null)
                {
                    // Wakamatsu 20170323
                    textBoxMsg.AppendText("× 取り込むファイルを指定してください。\r\n");
                    return;
                }

                MasterMaintOp mmo = new MasterMaintOp();
                // Wakamatsu 20170227
                //if (!mmo.MCalendar_Delete())
                //{
                //    textBoxMsg.AppendText("× " + "旧データの削除に失敗しました処理を中断します。\r\n");
                //    return;
                //}
                //mmo = new MasterMaintOp();
                // Wakamatsu 20170227
                int procCount = 0;
                switch (System.IO.Path.GetExtension(fileName))
                {
                case ".xlsx":
                    // Wakamatsu 20170227
                    try
                    {
                        oWBook    = new XLWorkbook(fileName);
                        procCount = mmo.MaintCalendarByExcelData(oWBook.Worksheet(1));

                        // Wakamatsu 20170227
                        if (procCount < 0)
                        {
                            if (procCount == -2)
                            {
                                textBoxMsg.AppendText("× 旧データの削除に失敗しました処理を中断します。\r\n");
                            }

                            textBoxMsg.AppendText("× " + fileName + "を処理できませんでした。\r\n");
                            return;
                        }
                        // Wakamatsu 20170227
                    }
                    // Wakamatsu 20170227
                    catch (Exception ex)
                    {
                        textBoxMsg.AppendText(ex.Message + "\r\n");
                        textBoxMsg.AppendText("× " + fileName + "を処理できませんでした。\r\n");
                        return;
                    }
                    // Wakamatsu 20170227
                    break;

                default:
                    procCount = -1;
                    textBoxMsg.AppendText("× " + fileName + "は処理できないファイルです。\r\n");
                    break;
                }

                if (procCount < 0)
                {
                    return;
                }
                textBoxMsg.AppendText("〇 " + fileName + "を処理しました。\r\n");
                textBoxMsg.AppendText(procCount + "件のデータを登録しました。\r\n");
                break;

            // Wakamatsu
            case "buttonExport":
                textBoxMsg.AppendText("☆ 処理を開始しました。\r\n");
                string SetSQL = "";

                SetSQL += "MDate, DType ";
                SetSQL += "FROM M_Calendar ";
                SetSQL += "ORDER BY MDate";

                SqlHandling sqlh = new SqlHandling();                   // SQL実行クラス
                // レコードを取得する
                DataTable dt = sqlh.SelectFullDescription(SetSQL);
                if (dt == null)
                {
                    textBoxMsg.AppendText("× Excel出力ができませんでした。\r\n");
                    return;
                }

                // フォーマット設定用構造体
                PrintOut.Publish.FormatSet[] FormatSet = new PrintOut.Publish.FormatSet[dt.Columns.Count];
                // フォーマット設定
                FormatSetting(ref FormatSet);

                // Excel出力クラス
                //PrintOut.Publish publ = new PrintOut.Publish( Folder.DefaultExcelTemplate( "M_Calendar.xlsx" ) );
                PrintOut.Publish publ = new PrintOut.Publish(Folder.DefaultExcelTemplate(BookName));
                // Excelファイル出力
                //textBoxMsg.AppendText( publ.ExcelFile( "M_Calendar", dt, FormatSet ) );
                // Wakamatsu 20170301
                //textBoxMsg.AppendText(publ.ExcelFile(SheetName, dt, FormatSet));
                textBoxMsg.AppendText(publ.ExcelFile(masterName, SheetName, dt, FormatSet));
                // Wakamatsu 20170301
                break;

            // Wakamatsu
            case "buttonEnd":
                this.Close();
                break;

            default:
                break;
            }
        }
Exemple #46
0
        //---------------------------------------------------------/
        //      Method
        //---------------------------------------------------------/
        public void ExcelFile()
        {
            string tempFile = Folder.DefaultLocation() + @"\.~temp.xlsx";

            Cursor.Current = Cursors.WaitCursor;                // マウスカーソルを砂時計(Wait)

            try
            {
                using (oWBook = new XLWorkbook(fileName))
                {
                    if (tncA == null || tncA.Length == 0)
                    {
                        DMessage.DataNotExistence("中断します!");
                        return;
                    }

                    // 編集
                    oWSheet = oWBook.Worksheet(1);        // シートを開く
                    readyExcelRows(tncA.Length, 4);
                    int sNo = 5;
                    for (int i = 0; i < tncA.Length; i++)
                    {
                        using (IXLRange SetRange = oWSheet.Range("A5:M5"))
                            // テンプレートデータ行コピー/ペースト
                            SetRange.CopyTo(oWSheet.Cell(sNo + i, 1));

                        if (i == 0)
                        {
                            officeName = tncA[i].OfficeName;
                            oWSheet.Cell(2, 3).Value = DateTime.Today;
                            oWSheet.Cell(3, 3).Value = officeName;
                        }

                        oWSheet.Cell(sNo + i, 1).Value  = i + 1;
                        oWSheet.Cell(sNo + i, 2).Value  = tncA[i].TaskCode;
                        oWSheet.Cell(sNo + i, 3).Value  = tncA[i].TaskName;
                        oWSheet.Cell(sNo + i, 4).Value  = tncA[i].VersionNo;
                        oWSheet.Cell(sNo + i, 5).Value  = tncA[i].IssueDate;
                        oWSheet.Cell(sNo + i, 6).Value  = tncA[i].SalesMName;
                        oWSheet.Cell(sNo + i, 7).Value  = tncA[i].SalesMInputDate;
                        oWSheet.Cell(sNo + i, 8).Value  = tncA[i].Approval;
                        oWSheet.Cell(sNo + i, 9).Value  = tncA[i].ApprovalDate;
                        oWSheet.Cell(sNo + i, 10).Value = tncA[i].MakeOrder;
                        oWSheet.Cell(sNo + i, 11).Value = tncA[i].MakeOrderDate;
                        oWSheet.Cell(sNo + i, 12).Value = tncA[i].ConfirmAdm;
                        oWSheet.Cell(sNo + i, 13).Value = tncA[i].ConfirmDate;

                        if (i != 0)
                        {
                            oWSheet.Range(sNo + i, 1, sNo + i, 13).Style.Border.TopBorder = XLBorderStyleValues.Hair;
                        }
                        if (i == tncA.Length - 1)
                        {
                            oWSheet.Range(sNo + i, 1, sNo + i, 13).Style.Border.BottomBorder = XLBorderStyleValues.Thin;
                        }
                        else
                        {
                            oWSheet.Range(sNo + i, 1, sNo + i, 13).Style.Border.BottomBorder = XLBorderStyleValues.Hair;
                        }
                    }
                }

                // 保存
                oWBook.SaveAs(tempFile);                      // Excel保存
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                Cursor.Current = Cursors.Default;               // マウスカーソルを戻す
                return;
            }

            Cursor.Current = Cursors.Default;                        // マウスカーソルを戻す

            System.Diagnostics.Process.Start("Excel.exe", tempFile); // 表示用Excel
            // pdf出力にする場合は、上記 System.DiafnosticsのLineをコメントアウトし、下記DateTime以下のコメントを外す。
            // pdf file 出力
            //DateTime now = DateTime.Now;
            //outputFile = System.IO.Path.GetDirectoryName( tempFile ) + @"\業務引継書承認未完了一覧表_" + officeName + "_" + "_" + now.ToString( "yyMMddHHmmss" );
            //PublishExcelToPdf etp = new PublishExcelToPdf();
            //etp.ExcelToPDF( tempFile, outputFile );

            //if( File.Exists( tempFile ) ) File.Delete( tempFile );
        }
        public void ThenSingleScenarioWithStepsAddedSuccessfully()
        {
            var excelScenarioFormatter = Container.Resolve<ExcelScenarioFormatter>();
            var scenario = new Scenario
                               {
                                   Name = "Test Feature",
                                   Description =
                                       "In order to test this feature,\nAs a developer\nI want to test this feature"
                               };
            var given = new Step {NativeKeyword = "Given", Name = "a precondition"};
            var when = new Step {NativeKeyword = "When", Name = "an event occurs"};
            var then = new Step {NativeKeyword = "Then", Name = "a postcondition"};
            scenario.Steps = new List<Step>(new[] {given, when, then});

            using (var workbook = new XLWorkbook())
            {
                IXLWorksheet worksheet = workbook.AddWorksheet("SHEET1");
                int row = 3;
                excelScenarioFormatter.Format(worksheet, scenario, ref row);

                worksheet.Cell("B3").Value.ShouldEqual(scenario.Name);
                worksheet.Cell("C4").Value.ShouldEqual(scenario.Description);
                row.ShouldEqual(8);
            }
        }
Exemple #48
-1
        // Public
        public void Create(String filePath)
        {
            var workbook = new XLWorkbook();
            var ws = workbook.Worksheets.Add("Defining a Range");

            // With a string
            var range1 = ws.Range("A1:B1");
            range1.Cell(1, 1).Value = "ws.Range(\"A1:B1\").Merge()";
            range1.Merge();

            // With two XLAddresses
            var range2 = ws.Range(ws.Cell(2, 1).Address, ws.Cell(2, 2).Address);
            range2.Cell(1, 1).Value = "ws.Range(ws.Cell(2, 1).Address, ws.Cell(2, 2).Address).Merge()";
            range2.Merge();

            // With two strings
            var range4 = ws.Range("A3", "B3");
            range4.Cell(1, 1).Value = "ws.Range(\"A3\", \"B3\").Merge()";
            range4.Merge();

            // With 4 points
            var range5 = ws.Range(4, 1, 4, 2);
            range5.Cell(1, 1).Value = "ws.Range(4, 1, 4, 2).Merge()";
            range5.Merge();

            ws.Column("A").AdjustToContents();

            workbook.SaveAs(filePath);
        }
Exemple #49
-1
        // Public
        public void Create(String filePath)
        {
            var wb = new XLWorkbook();
            var ws = wb.Worksheets.Add("Outline");

            ws.Outline.SummaryHLocation = XLOutlineSummaryHLocation.Right;
            ws.Columns(2, 6).Group(); // Create an outline (level 1) for columns 2-6
            ws.Columns(2, 4).Group(); // Create an outline (level 2) for columns 2-4
            ws.Column(2).Ungroup(true); // Remove column 2 from all outlines

            ws.Outline.SummaryVLocation = XLOutlineSummaryVLocation.Bottom;
            ws.Rows(1, 5).Group(); // Create an outline (level 1) for rows 1-5
            ws.Rows(1, 4).Group(); // Create an outline (level 2) for rows 1-4
            ws.Rows(1, 4).Collapse(); // Collapse rows 1-4
            ws.Rows(1, 2).Group(); // Create an outline (level 3) for rows 1-2
            ws.Rows(1, 2).Ungroup(); // Ungroup rows 1-2 from their last outline

            // You can also Collapse/Expand specific outline levels
            //
            // ws.CollapseRows(Int32 outlineLevel)
            // ws.CollapseColumns(Int32 outlineLevel)
            //
            // ws.ExpandRows(Int32 outlineLevel)
            // ws.ExpandColumns(Int32 outlineLevel)

            // And you can also Collapse/Expand ALL outline levels in one shot
            //
            // ws.CollapseRows()
            // ws.CollapseColumns()
            //
            // ws.ExpandRows()
            // ws.ExpandColumns()

            wb.SaveAs(filePath);
        }
Exemple #50
-1
        public ExcelWriter(string newFilePath)
        {
            if (!string.IsNullOrEmpty(newFilePath))
                filePath = newFilePath;

            workbook = new XLWorkbook();
        }
Exemple #51
-1
        private void findProvderName()
        {
            Point providerLocation = new Point(0, 0);
            for (int metricNumber = 0; metricNumber < 16; metricNumber++)//total numebr of metrics = 15
            {
                if (metricNumber < 10)
                    workbook = workbooks[0];
                else if (metricNumber == 10)
                    workbook = workbooks[1];
                else if (metricNumber > 10 && metricNumber < 14)
                    workbook = workbooks[2];
                else if (metricNumber == 14)
                    workbook = workbooks[3];
                else if (metricNumber == 15)
                    workbook = workbooks[4];

                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, metricNumber);
                            break;
                        }
                    }
                }
            }
        }
        public void ThenTableAddedSuccessfully()
        {
            var excelTableFormatter = Kernel.Get<ExcelTableFormatter>();
            var table = new Table();
            table.HeaderRow = new TableRow("Var1", "Var2", "Var3", "Var4");
            table.DataRows =
                new List<TableRow>(new[] {new TableRow("1", "2", "3", "4"), new TableRow("5", "6", "7", "8")});

            using (var workbook = new XLWorkbook())
            {
                IXLWorksheet worksheet = workbook.AddWorksheet("SHEET1");
                int row = 6;
                excelTableFormatter.Format(worksheet, table, ref row);

                worksheet.Cell("D6").Value.ShouldEqual("Var1");
                worksheet.Cell("E6").Value.ShouldEqual("Var2");
                worksheet.Cell("F6").Value.ShouldEqual("Var3");
                worksheet.Cell("G6").Value.ShouldEqual("Var4");
                worksheet.Cell("D7").Value.ShouldEqual(1.0);
                worksheet.Cell("E7").Value.ShouldEqual(2.0);
                worksheet.Cell("F7").Value.ShouldEqual(3.0);
                worksheet.Cell("G7").Value.ShouldEqual(4.0);
                worksheet.Cell("D8").Value.ShouldEqual(5.0);
                worksheet.Cell("E8").Value.ShouldEqual(6.0);
                worksheet.Cell("F8").Value.ShouldEqual(7.0);
                worksheet.Cell("G8").Value.ShouldEqual(8.0);
                row.ShouldEqual(9);
            }
        }
Exemple #53
-1
        public ActionResult CreateStandardReport(AutoModel model)
        {
            string filename = "testje.xlsx";

            XLWorkbook workbook = new XLWorkbook();
            IXLWorksheet worksheet = workbook.Worksheets.Add("Scheet");

            worksheet.ActiveCell = worksheet.Cell(string.Format("A1"));
            worksheet.ActiveCell.Value = model.Naam;
            worksheet.ActiveCell = worksheet.Cell(string.Format("B1"));
            worksheet.ActiveCell.Value = model.AantalWielen;

            //worksheet.Columns().AdjustToContents();
            worksheet.ActiveCell = worksheet.Cell("A1");

            try
            {
                DownloadExcel(workbook, filename);
                model.Message = "Succes!";
                return View(model);
            }
            catch (Exception ex)
            {
                model.Message = ex.Message;
                return View("About", model);
            }
        }
Exemple #54
-1
        public void TestRowCopyContents()
        {
            var workbook = new XLWorkbook();
            IXLWorksheet originalSheet = workbook.Worksheets.Add("original");
            IXLWorksheet copyRowSheet = workbook.Worksheets.Add("copy row");
            IXLWorksheet copyRowAsRangeSheet = workbook.Worksheets.Add("copy row as range");
            IXLWorksheet copyRangeSheet = workbook.Worksheets.Add("copy range");

            originalSheet.Cell("A2").SetValue("test value");
            originalSheet.Range("A2:E2").Merge();

            {
                IXLRange originalRange = originalSheet.Range("A2:E2");
                IXLRange destinationRange = copyRangeSheet.Range("A2:E2");

                originalRange.CopyTo(destinationRange);
            }
            CopyRowAsRange(originalSheet, 2, copyRowAsRangeSheet, 3);
            {
                IXLRow originalRow = originalSheet.Row(2);
                IXLRow destinationRow = copyRowSheet.Row(2);
                copyRowSheet.Cell("G2").Value = "must be removed after copy";
                originalRow.CopyTo(destinationRow);
            }
            TestHelper.SaveWorkbook(workbook, @"Misc\CopyRowContents.xlsx");
        }
        public void Format(XLWorkbook workbook, GeneralTree<IDirectoryTreeNode> features)
        {
            IXLWorksheet tocWorksheet = workbook.AddWorksheet("TOC", 0);

            int startRow = 1;
            BuildTableOfContents(workbook, tocWorksheet, ref startRow, 1, features);
        }
Exemple #56
-1
        public void CopyingRows()
        {
            var wb = new XLWorkbook();
            IXLWorksheet ws = wb.Worksheets.Add("Sheet");

            IXLRow row1 = ws.Row(1);
            row1.Cell(1).Style.Fill.SetBackgroundColor(XLColor.Red);
            row1.Cell(2).Style.Fill.SetBackgroundColor(XLColor.FromArgb(1, 1, 1));
            row1.Cell(3).Style.Fill.SetBackgroundColor(XLColor.FromHtml("#CCCCCC"));
            row1.Cell(4).Style.Fill.SetBackgroundColor(XLColor.FromIndex(26));
            row1.Cell(5).Style.Fill.SetBackgroundColor(XLColor.FromKnownColor(KnownColor.MediumSeaGreen));
            row1.Cell(6).Style.Fill.SetBackgroundColor(XLColor.FromName("Blue"));
            row1.Cell(7).Style.Fill.SetBackgroundColor(XLColor.FromTheme(XLThemeColor.Accent3));

            ws.Cell(2, 1).Value = row1;
            ws.Cell(3, 1).Value = row1.Row(1, 7);

            IXLRow row2 = ws.Row(2);
            Assert.AreEqual(XLColor.Red, row2.Cell(1).Style.Fill.BackgroundColor);
            Assert.AreEqual(XLColor.FromArgb(1, 1, 1), row2.Cell(2).Style.Fill.BackgroundColor);
            Assert.AreEqual(XLColor.FromHtml("#CCCCCC"), row2.Cell(3).Style.Fill.BackgroundColor);
            Assert.AreEqual(XLColor.FromIndex(26), row2.Cell(4).Style.Fill.BackgroundColor);
            Assert.AreEqual(XLColor.FromKnownColor(KnownColor.MediumSeaGreen), row2.Cell(5).Style.Fill.BackgroundColor);
            Assert.AreEqual(XLColor.FromName("Blue"), row2.Cell(6).Style.Fill.BackgroundColor);
            Assert.AreEqual(XLColor.FromTheme(XLThemeColor.Accent3), row2.Cell(7).Style.Fill.BackgroundColor);

            IXLRow row3 = ws.Row(3);
            Assert.AreEqual(XLColor.Red, row3.Cell(1).Style.Fill.BackgroundColor);
            Assert.AreEqual(XLColor.FromArgb(1, 1, 1), row3.Cell(2).Style.Fill.BackgroundColor);
            Assert.AreEqual(XLColor.FromHtml("#CCCCCC"), row3.Cell(3).Style.Fill.BackgroundColor);
            Assert.AreEqual(XLColor.FromIndex(26), row3.Cell(4).Style.Fill.BackgroundColor);
            Assert.AreEqual(XLColor.FromKnownColor(KnownColor.MediumSeaGreen), row3.Cell(5).Style.Fill.BackgroundColor);
            Assert.AreEqual(XLColor.FromName("Blue"), row3.Cell(6).Style.Fill.BackgroundColor);
            Assert.AreEqual(XLColor.FromTheme(XLThemeColor.Accent3), row3.Cell(7).Style.Fill.BackgroundColor);
        }
        public static void exportToExcel(DataTable table, string tableName, string workSheetName, string fileName)
        {
            // Create the excel file and add worksheet
            XLWorkbook workBook = new XLWorkbook();
            IXLWorksheet workSheet = workBook.Worksheets.Add(workSheetName);

            // Hardcode title and contents locations
            IXLCell titleCell = workSheet.Cell(2, 2);
            IXLCell contentsCell = workSheet.Cell(3, 2);

            //Pretty-up the title
            titleCell.Value = tableName;
            titleCell.Style.Font.Bold = true;
            titleCell.Style.Fill.BackgroundColor = XLColor.CornflowerBlue;
            titleCell.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;

            // Merge cells for title
            workSheet.Range(titleCell, workSheet.Cell(2, table.Columns.Count + 1)).Merge();

            // Insert table contents, and adjust for content width
            contentsCell.InsertTable(table);
            workSheet.Columns().AdjustToContents(1, 75);

            // Create a new response and flush it to a memory stream
            System.Web.HttpResponse response = System.Web.HttpContext.Current.Response;
            response.Clear();
            response.AddHeader("Content-Disposition", "attachment; filename=" + fileName + ".xlsx;");
            using (MemoryStream stream = new MemoryStream())
            {
                workBook.SaveAs(stream);
                stream.WriteTo(response.OutputStream);
                stream.Close();
            }
            response.End();
        }
Exemple #58
-1
        public void _openExcel(String dashboardFile, String metricsFolder)
        {
            //grab all the files in the metrics folder
            metrics_files = Directory.GetFiles(metricsFolder);

            if (File.Exists("Copy.xlsx"))
            {
                File.Delete("Copy.xlsx");
            }
            File.Copy(dashboardFile, "Copy.xlsx");

            //try openeing the excel sheets listed in metricsFile
            try
            {
                _dashboard = new XLWorkbook(dashboardFile);

                //iterate thorugh all the dashboard sheets, prep them for new daata by adding new rows, then  extrract data from
                //metrics reports and insert into those sheets
                int numSheets = _dashboard.Worksheets.Count;
                for (int sheetNum = 1; sheetNum < numSheets + 1; sheetNum++)
                    {
                        var sheet = _dashboard.Worksheet(sheetNum);
                        prepDashboardSheets(sheet);
                     }
                _dashboard.Save();

            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
        }
Exemple #59
-6
        public void CanGetNamedFromAnother()
        {
            var wb = new XLWorkbook();
            var ws1 = wb.Worksheets.Add("Sheet1");
            ws1.Cell("A1").SetValue(1).AddToNamed("value1");

            Assert.AreEqual(1, wb.Cell("value1").GetValue<int>());
            Assert.AreEqual(1, wb.Range("value1").FirstCell().GetValue<int>());

            Assert.AreEqual(1, ws1.Cell("value1").GetValue<int>());
            Assert.AreEqual(1, ws1.Range("value1").FirstCell().GetValue<int>());

            var ws2 = wb.Worksheets.Add("Sheet2");

            ws2.Cell("A1").SetFormulaA1("=value1").AddToNamed("value2");

            Assert.AreEqual(1, wb.Cell("value2").GetValue<int>());
            Assert.AreEqual(1, wb.Range("value2").FirstCell().GetValue<int>());

            Assert.AreEqual(1, ws2.Cell("value1").GetValue<int>());
            Assert.AreEqual(1, ws2.Range("value1").FirstCell().GetValue<int>());

            Assert.AreEqual(1, ws2.Cell("value2").GetValue<int>());
            Assert.AreEqual(1, ws2.Range("value2").FirstCell().GetValue<int>());
        }
Exemple #60
-10
        private static void CreateExcelFile(ChromeDriver driver)
        {
            var homeTeams = driver.FindElements(By.ClassName("team-home"));
            var awayTeams = driver.FindElements(By.ClassName("team-away"));
            var scores = driver.FindElements(By.ClassName("score"));

            var wb = new XLWorkbook();
            var ws = wb.Worksheets.Add("Scores");

            ws.Cell("A1").Value = "Home Team";
            ws.Cell("B1").Value = "Score";
            ws.Cell("C1").Value = "Away Team";

            for (int i = 0; i < homeTeams.Count; i++)
            {
                string homeTeam = homeTeams[i].Text;
                string score = scores[i].Text;
                string awayTeam = awayTeams[i].Text;

                ws.Cell("A" + (i + 2)).Value = homeTeam;
                ws.Cell("B" + (i + 2)).Value = score;
                ws.Cell("C" + (i + 2)).Value = awayTeam;
            }

            // Beautify
            ws.Range("A1:C1").Style.Font.Bold = true;
            ws.Columns().AdjustToContents();

            wb.SaveAs("../../../../FlashScore.xlsx");
        }