public static void Run() { //Source directory string sourceDir = RunExamples.Get_SourceDirectory(); //Output directory string outputDir = RunExamples.Get_OutputDirectory(); // Open a template Excel file Workbook workbook = new Workbook(sourceDir + "sampleGetIconSetsDataBars.xlsx"); // Get the first worksheet in the workbook Worksheet sheet = workbook.Worksheets[0]; // Get the A1 cell Cell cell = sheet.Cells["A1"]; // Get the conditional formatting result object ConditionalFormattingResult cfr = cell.GetConditionalFormattingResult(); // Get the icon set ConditionalFormattingIcon icon = cfr.ConditionalFormattingIcon; // Create the image file based on the icon's image data File.WriteAllBytes(outputDir + "outputGetIconSetsDataBars.jpg", icon.ImageData); Console.WriteLine("GetIconSetsDataBars executed successfully.\r\n"); }
public static void Run() { //Output directory string outputDir = RunExamples.Get_OutputDirectory(); // Instantiate a new Workbook Workbook workbook = new Workbook(); // Get the first worksheet's cells collection Cells cells = workbook.Worksheets[0].Cells; Style st = workbook.CreateStyle(); st.Pattern = BackgroundType.Solid; st.ForegroundColor = System.Drawing.Color.Yellow; // Add string values to the cells cells["A1"].PutValue("A1"); cells["A1"].SetStyle(st, true); st.ForegroundColor = System.Drawing.Color.Red; cells["C10"].PutValue("C10"); cells["C10"].SetStyle(st, true); // Get the conditional icon's image data byte[] imagedata = ConditionalFormattingIcon.GetIconImageData(IconSetType.TrafficLights31, 0); // Create a stream based on the image data MemoryStream stream = new MemoryStream(imagedata); // Add a blank picture to the D1 cell Picture pic = workbook.Worksheets[0].Shapes.AddPicture(5, 5, stream, 600, 600); // Specify the formula that refers to the source range of cells pic.Formula = "A1:C10"; // Update the shapes selected value in the worksheet workbook.Worksheets[0].Shapes.UpdateSelectedValue(); //Reset the picture to original height and width pic.HeightScale = 100; pic.WidthScale = 100; // Save the Excel file. workbook.Save(outputDir + "outputInsertPictureCellReference.xlsx"); Console.WriteLine("InsertPictureCellReference executed successfully."); }
public static void Main() { // The path to the documents directory. string dataDir = Path.GetFullPath("../../../Data/"); //Open a template Excel file Workbook workbook = new Workbook(dataDir + "book1.xlsx"); //Get the first worksheet in the workbook Worksheet sheet = workbook.Worksheets[0]; //Get the A1 cell Cell cell = sheet.Cells["A1"]; //Get the conditional formatting result object ConditionalFormattingResult cfr = cell.GetConditionalFormattingResult(); //Get the icon set ConditionalFormattingIcon icon = cfr.ConditionalFormattingIcon; //Create the image file based on the icon's image data File.WriteAllBytes(dataDir + "imgIcon.jpg", icon.ImageData); }
public static void Run() { try { // ExStart:1 // The path to the documents directory. string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType); // Instantiate a new Workbook Workbook workbook = new Workbook(); // Get the first worksheet's cells collection Cells cells = workbook.Worksheets[0].Cells; // Add string values to the cells cells["A1"].PutValue("A1"); cells["C10"].PutValue("C10"); // Get the conditional icon's image data byte[] imagedata = ConditionalFormattingIcon.GetIconImageData(IconSetType.TrafficLights31, 0); // Create a stream based on the image data MemoryStream stream = new MemoryStream(imagedata); // Add a blank picture to the D1 cell Picture pic = (Picture)workbook.Worksheets[0].Shapes.AddPicture(0, 3, stream, 10, 10); // Specify the formula that refers to the source range of cells pic.Formula = "A1:C10"; // Update the shapes selected value in the worksheet workbook.Worksheets[0].Shapes.UpdateSelectedValue(); // Save the Excel file. workbook.Save(dataDir + "referencedpicture.out.xlsx"); // ExEnd:1 } catch (Exception ex) { Console.WriteLine(ex.Message); Console.ReadLine(); } }
public static void Run() { // ExStart:1 // The path to the documents directory. string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType); // Open a template Excel file Workbook workbook = new Workbook(dataDir + "book1.xlsx"); // Get the first worksheet in the workbook Worksheet sheet = workbook.Worksheets[0]; // Get the A1 cell Cell cell = sheet.Cells["A1"]; // Get the conditional formatting result object ConditionalFormattingResult cfr = cell.GetConditionalFormattingResult(); // Get the icon set ConditionalFormattingIcon icon = cfr.ConditionalFormattingIcon; // Create the image file based on the icon's image data File.WriteAllBytes(dataDir + "imgIcon.out.jpg", icon.ImageData); // ExEnd:1 }
public static void Run() { //Output directory string outputDir = RunExamples.Get_OutputDirectory(); // Instantiate a new Workbook Workbook workbook = new Workbook(); // Get the first worksheet (default worksheet) in the workbook Worksheet worksheet = workbook.Worksheets[0]; // Get the cells Cells cells = worksheet.Cells; // Set the columns widths (A, B and C) worksheet.Cells.SetColumnWidth(0, 24); worksheet.Cells.SetColumnWidth(1, 24); worksheet.Cells.SetColumnWidth(2, 24); // Input date into the cells cells["A1"].PutValue("KPIs"); cells["A2"].PutValue("Total Turnover (Sales at List)"); cells["A3"].PutValue("Total Gross Margin %"); cells["A4"].PutValue("Total Net Margin %"); cells["B1"].PutValue("UA Contract Size Group 4"); cells["B2"].PutValue(19551794); cells["B3"].PutValue(11.8070745566204); cells["B4"].PutValue(11.858589818569); cells["C1"].PutValue("UA Contract Size Group 3"); cells["C2"].PutValue(8150131.66666667); cells["C3"].PutValue(10.3168384396244); cells["C4"].PutValue(11.3326931937091); // Get the conditional icon's image data byte[] imagedata = ConditionalFormattingIcon.GetIconImageData(IconSetType.TrafficLights31, 0); // Create a stream based on the image data MemoryStream stream = new MemoryStream(imagedata); // Add the picture to the cell based on the stream worksheet.Pictures.Add(1, 1, stream); // Get the conditional icon's image data byte[] imagedata1 = ConditionalFormattingIcon.GetIconImageData(IconSetType.Arrows3, 2); // Create a stream based on the image data MemoryStream stream1 = new MemoryStream(imagedata1); // Add the picture to the cell based on the stream worksheet.Pictures.Add(1, 2, stream1); // Get the conditional icon's image data byte[] imagedata2 = ConditionalFormattingIcon.GetIconImageData(IconSetType.Symbols3, 0); // Create a stream based on the image data MemoryStream stream2 = new MemoryStream(imagedata2); // Add the picture to the cell based on the stream worksheet.Pictures.Add(2, 1, stream2); // Get the conditional icon's image data byte[] imagedata3 = ConditionalFormattingIcon.GetIconImageData(IconSetType.Stars3, 0); // Create a stream based on the image data MemoryStream stream3 = new MemoryStream(imagedata3); // Add the picture to the cell based on the stream worksheet.Pictures.Add(2, 2, stream3); // Get the conditional icon's image data byte[] imagedata4 = ConditionalFormattingIcon.GetIconImageData(IconSetType.Boxes5, 1); // Create a stream based on the image data MemoryStream stream4 = new MemoryStream(imagedata4); // Add the picture to the cell based on the stream worksheet.Pictures.Add(3, 1, stream4); // Get the conditional icon's image data byte[] imagedata5 = ConditionalFormattingIcon.GetIconImageData(IconSetType.Flags3, 1); // Create a stream based on the image data MemoryStream stream5 = new MemoryStream(imagedata5); // Add the picture to the cell based on the stream worksheet.Pictures.Add(3, 2, stream5); // Save the Excel file workbook.Save(outputDir + "outputAddConditionalIconsSet.xlsx"); Console.WriteLine("AddConditionalIconsSet executed successfully.\r\n"); }
public static void Run() { // ExStart:1 // The path to the documents directory. string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType); // Create directory if it is not already present. bool IsExists = System.IO.Directory.Exists(dataDir); if (!IsExists) { System.IO.Directory.CreateDirectory(dataDir); } // Instantiate a new Workbook Workbook workbook = new Workbook(); // Get the first worksheet (default worksheet) in the workbook Worksheet worksheet = workbook.Worksheets[0]; // Get the cells Cells cells = worksheet.Cells; // Set the columns widths (A, B and C) worksheet.Cells.SetColumnWidth(0, 24); worksheet.Cells.SetColumnWidth(1, 24); worksheet.Cells.SetColumnWidth(2, 24); // Input date into the cells cells["A1"].PutValue("KPIs"); cells["A2"].PutValue("Total Turnover (Sales at List)"); cells["A3"].PutValue("Total Gross Margin %"); cells["A4"].PutValue("Total Net Margin %"); cells["B1"].PutValue("UA Contract Size Group 4"); cells["B2"].PutValue(19551794); cells["B3"].PutValue(11.8070745566204); cells["B4"].PutValue(11.858589818569); cells["C1"].PutValue("UA Contract Size Group 3"); cells["C2"].PutValue(8150131.66666667); cells["C3"].PutValue(10.3168384396244); cells["C4"].PutValue(11.3326931937091); // Get the conditional icon's image data byte[] imagedata = ConditionalFormattingIcon.GetIconImageData(IconSetType.TrafficLights31, 0); // Create a stream based on the image data MemoryStream stream = new MemoryStream(imagedata); // Add the picture to the cell based on the stream worksheet.Pictures.Add(1, 1, stream); // Get the conditional icon's image data byte[] imagedata1 = ConditionalFormattingIcon.GetIconImageData(IconSetType.Arrows3, 2); // Create a stream based on the image data MemoryStream stream1 = new MemoryStream(imagedata1); // Add the picture to the cell based on the stream worksheet.Pictures.Add(1, 2, stream1); // Get the conditional icon's image data byte[] imagedata2 = ConditionalFormattingIcon.GetIconImageData(IconSetType.Symbols3, 0); // Create a stream based on the image data MemoryStream stream2 = new MemoryStream(imagedata2); // Add the picture to the cell based on the stream worksheet.Pictures.Add(2, 1, stream2); // Get the conditional icon's image data byte[] imagedata3 = ConditionalFormattingIcon.GetIconImageData(IconSetType.Stars3, 0); // Create a stream based on the image data MemoryStream stream3 = new MemoryStream(imagedata3); // Add the picture to the cell based on the stream worksheet.Pictures.Add(2, 2, stream3); // Get the conditional icon's image data byte[] imagedata4 = ConditionalFormattingIcon.GetIconImageData(IconSetType.Boxes5, 1); // Create a stream based on the image data MemoryStream stream4 = new MemoryStream(imagedata4); // Add the picture to the cell based on the stream worksheet.Pictures.Add(3, 1, stream4); // Get the conditional icon's image data byte[] imagedata5 = ConditionalFormattingIcon.GetIconImageData(IconSetType.Flags3, 1); // Create a stream based on the image data MemoryStream stream5 = new MemoryStream(imagedata5); // Add the picture to the cell based on the stream worksheet.Pictures.Add(3, 2, stream5); dataDir = dataDir + "outfile_cond_icons1.out.xlsx"; // Save the Excel file workbook.Save(dataDir); // ExEnd:1 Console.WriteLine("\nProcess completed successfully.\nFile saved at " + dataDir); }