Office Ribbons

Excel 2007 Development
Excel 2013 Development

Office Ribbons are the results of a Usability re-engineering.

… but it is not so obvious that you have to enable the develpment view from

Excel > Options > Customise Ribbons

Are you there? So let’s come back to the image with Excel CheckBoxes and assume we have received a xlsx with many of them.

Ribbons

How could we set them either checked or unchecked with a lovely c# library? Find my code is below.

SustainablePrint.cs

using System;
using System.Collections.Generic;
using System.Reflection;
using System.Linq;

using Microsoft.Office.Interop.Excel;

namespace PrintCert
{
	public class SustainblePrint
	{
		public bool Build(SustainableInfo info, out string log) {

			try {
				Application excelApplication = new Application();
				excelApplication.ScreenUpdating = false;
				excelApplication.DisplayAlerts = false;
				excelApplication.Visible = false;

				Workbook excelWorkbook =
					excelApplication.Workbooks.
					Open(info.input_xlsx_model_path,
					     Missing.Value, false, Missing.Value, Missing.Value, Missing.Value,
					     true, Missing.Value, Missing.Value, true, Missing.Value, Missing.Value, Missing.Value,
					     Missing.Value, Missing.Value);

				if (excelWorkbook == null)
				{
					excelApplication.Quit();
					excelApplication = null;
					excelWorkbook = null;
					log = "Cannot create new excel workbook.";
					return false;
				}

				List<string> shapeList = new List<string>();
				Worksheet sheet = (Worksheet) excelWorkbook.ActiveSheet;
				if (sheet.Shapes != null && sheet.Shapes.Count>0) {
					foreach (Shape shape in sheet.Shapes) {
						CheckBox cb = shape.OLEFormat.Object as CheckBox;
						System.Diagnostics.Debug.WriteLine(shape.Name );
						shapeList.Add(shape.Name+" - id " + shape.ID.ToString() +" ("+shape.AlternativeText+"): "
						              + (cb == null ? "no checkbox" :
						                 (cb.Value == null? "null value":
						                  ( cb.Value.ToString().Equals("1") ? "yes" : "no")))
						             );
						if (cb != null && cb.Value != null) {
							if (info.CheckBoxes != null && info.CheckBoxes.Length>0) {
								CheckSetter checkSetter = info.CheckBoxes.FirstOrDefault(cs => cs.Name.Equals(shape.Name));
								if (checkSetter != null ) {
									cb.Value = checkSetter.IntValue;
									excelWorkbook.Save();
								}
							}

							}

						}
					}
				}

				try
				{
					excelWorkbook.
						ExportAsFixedFormat(XlFixedFormatType.xlTypePDF,info.output_pdf_cert_path);

				}
				catch (Exception e)
				{
					log = e.Message;
					return false;
				}
				finally
				{

					try
					{
						if (excelWorkbook != null)
							excelWorkbook.Close(false, Type.Missing, Type.Missing);
						System.Runtime.InteropServices.Marshal.ReleaseComObject(excelWorkbook);
					}

					try
					{
						if (excelApplication != null) {
							excelApplication.ScreenUpdating = true;
							excelApplication.DisplayAlerts = true;
							excelApplication.Visible = true;
							excelApplication.Quit();
						}
						System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApplication);
					}
					catch
					{ }
					finally
					{

					}

				}

				log = "";
				return true;

			} catch (Exception exc) {
				log = exc.Message;
				return false;
			}

		}
	}
}

SustainableInfo.cs

using System;
namespace PrintCert
{
public class SustainableInfo
{
public string input_xlsx_model_path;
public string output_pdf_cert_path;

public CheckSetter[] CheckBoxes;
}
public class CheckSetter
{
public string Name;
public int IntValue;
}
}

LOL 🙂 Have you noticed that I’m developing a new Bio Fuel tool?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s