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; }
//---------------------------------------------------------// // 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); } }
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); }
public void IsBlank_false() { var ws = new XLWorkbook().AddWorksheet("Sheet"); ws.Cell("A1").Value = " "; var actual = ws.Evaluate("=IsBlank(A1)"); Assert.AreEqual(false, actual); }
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; // マウスカーソルを戻す }
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); }
// 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); } }
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 })); }
//---------------------------------------------------------// // 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 }
//---------------------------------------------------------------------- // 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"); }
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])
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); }
//作業割り当て表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; } }
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); } } }
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); } } }
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); } } }
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); } } }
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); } } }
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(); } }
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(); }
// 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 } //} }
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(); } }
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); //} } }
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); }
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); }
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); }
//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; } }
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; } } }
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; } }
//---------------------------------------------------------/ // 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); } }
// 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); }
// 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); }
public ExcelWriter(string newFilePath) { if (!string.IsNullOrEmpty(newFilePath)) filePath = newFilePath; workbook = new XLWorkbook(); }
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); } }
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); } }
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); }
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(); }
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); } }
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>()); }
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"); }