private void button1_Click(object sender, EventArgs e)
{
// start excel and turn off msg boxes
excelApplication = new XlApplication();
excelApplication.DisplayAlerts = false;
// add a new workbook
XlWorkbook workBook = excelApplication.Workbooks.Add();
XlWorksheet workSheet = workBook.Worksheets[1];
// we need some data to display
XlRange dataRange = PutSampleData(workSheet);
// create a nice diagram
XlChartObject chart = workSheet.ChartObjects.Add(70, 100, 375, 225);
chart.Chart.SetSourceData(dataRange);
// save the book
string fileExtension = XlConverter.GetDefaultExtension(excelApplication);
string workbookFile = string.Format("{0}\\Example5{1}", Environment.CurrentDirectory, fileExtension);
workBook.SaveAs(workbookFile);
// close excel and dispose reference
excelApplication.Quit();
excelApplication.Dispose();
FinishDialog fDialog = new FinishDialog("Workbook saved.", workbookFile);
fDialog.ShowDialog(this);
}
public XlWorkbook Open(string fileName,
object updateLinks, bool readOnly, object format, object passWord, object writeResPassword,
object ignoreReadOnlyRecommended, object origin, object delimiter, object editable, object notify, object converter, object addToMru, object local, object corruptLoad)
{
object[] paramArray = new object[15];
paramArray[0] = fileName;
paramArray[1] = updateLinks;
paramArray[2] = readOnly;
paramArray[3] = format;
paramArray[4] = passWord;
paramArray[5] = writeResPassword;
paramArray[6] = ignoreReadOnlyRecommended;
paramArray[7] = origin;
paramArray[8] = delimiter;
paramArray[9] = editable;
paramArray[10] = notify;
paramArray[11] = converter;
paramArray[12] = addToMru;
paramArray[13] = local;
paramArray[14] = corruptLoad;
object returnValue = InstanceType.InvokeMember("Open", BindingFlags.InvokeMethod, null, ComReference, paramArray, XlLateBindingApiSettings.XlThreadCulture);
if (null == returnValue)
{
return(null);
}
XlWorkbook newClass = new XlWorkbook(this, returnValue);
ListChildReferences.Add(newClass);
return(newClass);
}
private static void WriteWorkbook(string fName)
{
Console.WriteLine("Creating workbook named [{0}]", fName);
XlWorkbook book = new XlWorkbook();
XlWorksheets sheets = book.Worksheets;
XlSchema schema = GetDeploymentSchema();
XlWorksheet sheet = sheets.AddWorksheet("DET_Deployments", XlColor.White, schema);
GetDeploymentRows(sheet);
schema = GetMeasurementSchema();
sheet = sheets.AddWorksheet("DET_Measurements", XlColor.White, schema);
GetMeasurementRows(sheet);
schema = GetSiteSchema();
sheet = sheets.AddWorksheet("LIST_Sites", XlColor.Orange, schema);
GetSiteRows(sheet);
schema = GetSensorSchema();
sheet = sheets.AddWorksheet("LIST_Sensors", XlColor.Orange, schema);
GetSensorRows(sheet);
XlCustomProperties properties = book.Properties;
properties.AddCustomProperty("oncorId", Guid.NewGuid().ToString());
properties.AddCustomProperty("oncorUser", "Dr. Frank N. Furter, ESQ");
book.Save(fName);
Console.WriteLine("Closing workbook named [{0}]", fName);
}
private XlStyle CreateYearTotalStyle(XlWorkbook workBook, string styleName)
{
/*
* borders in styles doesnt realy working, very simple using is possible with the index trick. thats all
*/
XlStyle newStyle = workBook.Styles.Add(styleName);
newStyle.Font.Size = 12;
newStyle.Borders[(XlBordersIndex)Constants.xlTop].LineStyle = XlLineStyle.xlContinuous;
newStyle.Borders[(XlBordersIndex)Constants.xlTop].Color = 0;
newStyle.Borders[(XlBordersIndex)Constants.xlTop].Weight = 2;
newStyle.Borders[(XlBordersIndex)Constants.xlBottom].LineStyle = LateBindingApi.Excel.Enums.XlLineStyle.xlContinuous;
newStyle.Borders[(XlBordersIndex)Constants.xlBottom].Color = 0;
newStyle.Borders[(XlBordersIndex)Constants.xlBottom].Weight = 2;
newStyle.Borders[(XlBordersIndex)Constants.xlLeft].LineStyle = LateBindingApi.Excel.Enums.XlLineStyle.xlDouble;
newStyle.Borders[(XlBordersIndex)Constants.xlLeft].Color = 0;
newStyle.Borders[(XlBordersIndex)Constants.xlLeft].Weight = 2;
newStyle.Borders[(XlBordersIndex)Constants.xlRight].LineStyle = LateBindingApi.Excel.Enums.XlLineStyle.xlLineStyleNone;
newStyle.Borders[(XlBordersIndex)Constants.xlRight].Color = 0;
newStyle.Borders[(XlBordersIndex)Constants.xlRight].Weight = 2;
newStyle.NumberFormat = "#,##0.00 €";
return(newStyle);
}
private void button1_Click(object sender, EventArgs e)
{
// we enable the event support
XlLateBindingApiSettings.EventsEnabled = true;
// start excel and turn off msg boxes
excelApplication = new XlApplication();
excelApplication.DisplayAlerts = false;
excelApplication.Visible = true;
/*
* we register some events. note: the event trigger was called from excel, means an other Thread
* remove the Quit() call below and check out more events if you want
* you can get event notifys from various objects: XlApplication or XlWorkbook or XlWorksheet for example
*/
excelApplication.NewWorkbook += new AppEvents_NewWorkbookEventHandler(excelApplication_NewWorkbook);
excelApplication.WorkbookBeforeClose += new AppEvents_WorkbookBeforeCloseEventHandler(excelApplication_WorkbookBeforeClose);
excelApplication.WorkbookActivate += new AppEvents_WorkbookActivateEventHandler(excelApplication_WorkbookActivate);
excelApplication.WorkbookDeactivate += new AppEvents_WorkbookDeactivateEventHandler(excelApplication_WorkbookDeactivate);
excelApplication.SheetActivate += new AppEvents_SheetActivateEventHandler(excelApplication_SheetActivate);
excelApplication.SheetDeactivate += new AppEvents_SheetDeactivateEventHandler(excelApplication_SheetDeactivate);
// add a new workbook add a sheet and close
XlWorkbook workBook = excelApplication.Workbooks.Add();
workBook.Worksheets.Add();
workBook.Close();
excelApplication.Quit();
excelApplication.Dispose();
}
private void LoadSheets(XlWorkbook book, AddSheetMethod addSheet, LoadRow loadRow)
{
foreach (XlWorksheet worksheet in book.Worksheets)
{
addSheet(worksheet.Name);
LoadRows(worksheet, loadRow);
}
}
private void button1_Click(object sender, EventArgs e)
{
// start excel and turn off msg boxes
excelApplication = new XlApplication();
excelApplication.DisplayAlerts = false;
// add a new workbook
XlWorkbook workBook = excelApplication.Workbooks.Add();
XlWorksheet workSheet = workBook.Worksheets[1];
/*do background color for cells*/
string listSeperator = System.Globalization.CultureInfo.CurrentCulture.TextInfo.ListSeparator;
// draw the face
string rangeAdressFace = string.Format("$C10:$M10{0}$C30:$M30{0}$C11:$C30{0}$M11:$M30", listSeperator);
workSheet.Range(rangeAdressFace).Interior.Color = XlConverter.ToDouble(Color.DarkGreen);
string rangeAdressEyes = string.Format("$F14{0}$J14", listSeperator);
workSheet.Range(rangeAdressEyes).Interior.Color = XlConverter.ToDouble(Color.Black);
string rangeAdressNoise = string.Format("$G18:$I19", listSeperator);
workSheet.Range(rangeAdressNoise).Interior.Color = XlConverter.ToDouble(Color.DarkGreen);
string rangeAdressMouth = string.Format("$F26{0}$J26{0}$G27:$I27", listSeperator);
workSheet.Range(rangeAdressMouth).Interior.Color = XlConverter.ToDouble(Color.DarkGreen);
/*do borderlines for cells*/
// border the face with the border arround method
workSheet.Range(rangeAdressFace).BorderAround(LateBindingApi.Excel.Enums.XlLineStyle.xlDashDot, LateBindingApi.Excel.Enums.XlBorderWeight.xlThin, Color.BlueViolet.ToArgb());
workSheet.Range(rangeAdressEyes).BorderAround(LateBindingApi.Excel.Enums.XlLineStyle.xlDashDot, LateBindingApi.Excel.Enums.XlBorderWeight.xlThin, Color.BlueViolet.ToArgb());
workSheet.Range(rangeAdressNoise).BorderAround(LateBindingApi.Excel.Enums.XlLineStyle.xlDouble, LateBindingApi.Excel.Enums.XlBorderWeight.xlThin, Color.BlueViolet.ToArgb());
// border explicitly
workSheet.Range(rangeAdressMouth).Borders[LateBindingApi.Excel.Enums.XlBordersIndex.xlEdgeBottom].LineStyle = LateBindingApi.Excel.Enums.XlLineStyle.xlDouble;
workSheet.Range(rangeAdressMouth).Borders[LateBindingApi.Excel.Enums.XlBordersIndex.xlEdgeBottom].Weight = 4;
workSheet.Range(rangeAdressMouth).Borders[LateBindingApi.Excel.Enums.XlBordersIndex.xlEdgeBottom].Color = 400;
// save the book
string fileExtension = XlConverter.GetDefaultExtension(excelApplication);
string workbookFile = string.Format("{0}\\Example1{1}", Environment.CurrentDirectory, fileExtension);
workBook.SaveAs(workbookFile);
// close excel and dispose reference
excelApplication.Quit();
excelApplication.Dispose();
FinishDialog fDialog = new FinishDialog("Workbook saved.", workbookFile);
fDialog.ShowDialog(this);
}
public void SetupEventBinding(XlWorkbook workBook)
{
if (true == XlLateBindingApiSettings.EventsEnabled)
{
_workBook = workBook;
IConnectionPointContainer connectionPointContainer = (IConnectionPointContainer)workBook.COMReference;
Guid guid = new Guid("{00024412-0000-0000-C000-000000000046}");
connectionPointContainer.FindConnectionPoint(ref guid, out _connectionPoint);
_connectionPoint.Advise(this, out _connectionCookie);
}
}
private void button1_Click(object sender, EventArgs e)
{
// start excel and turn off msg boxes
_excelApplication = new XlApplication();
_excelApplication.DisplayAlerts = false;
_excelApplication.ScreenUpdating = false;
// add a new workbook
XlWorkbook workBook = _excelApplication.Workbooks.Add();
// we use the first sheet as summary sheet and remove the 2 last sheets
XlWorksheet summarySheet = workBook.Worksheets[1];
workBook.Worksheets[3].Delete();
workBook.Worksheets[2].Delete();
// we get the data & perform the report
_report = new SalesReport(_yearToReport, _monthToReport);
_report.Proceed();
// we create named styles for the range.Style property
CreateStorageAndRankingStyle(workBook, "StorageAndRanking");
CreateMonthStyle(workBook, "MonthInfos");
CreateMonthStyle(workBook, "YearTotalInfos");
// write product sheets
XlWorksheet productSheet = null;
foreach (SalesReportProduct itemProduct in _report.Products)
{
productSheet = workBook.Worksheets.Add();
ProceedProductWorksheet(productSheet, itemProduct);
productSheet.Move(null, workBook.Worksheets[workBook.Worksheets.Count]);
}
// write summary sheet
ProceedSummaryWorksheet(_report, workBook, summarySheet, productSheet);
summarySheet.Range("$A2").Select();
// save the book
string fileExtension = XlConverter.GetDefaultExtension(_excelApplication);
string workbookFile = string.Format("{0}\\Example10{1}", Environment.CurrentDirectory, fileExtension);
workBook.SaveAs(workbookFile);
// close excel and dispose reference
_excelApplication.Quit();
_excelApplication.Dispose();
FinishDialog fDialog = new FinishDialog("Workbook saved.", workbookFile);
fDialog.ShowDialog(this);
}
public XlWorkbook Open()
{
object returnValue = InstanceType.InvokeMember("Open", BindingFlags.InvokeMethod, null, ComReference, null, XlLateBindingApiSettings.XlThreadCulture);
XlWorkbook newClass = new XlWorkbook(this, returnValue);
if (null == returnValue)
{
return(null);
}
ListChildReferences.Add(newClass);
return(newClass);
}
private void AddRows(XlWorkbook book, DataTab data)
{
XlWorksheets sheets = book.Worksheets;
List <BaseDTO> list = new List <BaseDTO>(data);
XlWorksheet sheet = sheets.AddWorksheet(data.Name, data.Color, data.Schema);
int numberRows = list.Count;
for (int rowIndex = 0; rowIndex < numberRows; rowIndex++)
{
Dictionary <string, string> values = list[rowIndex].Values();
sheet.AddRow(rowIndex, values);
}
}
private XlWorkbook CreateWorkBook(IEnumerable <DataTab> tables)
{
XlWorkbook book = new XlWorkbook();
foreach (DataTab tab in tables)
{
AddRows(book, tab);
}
XlCustomProperties properties = book.Properties;
properties.AddCustomProperty("oncorId", Id.ToString());
properties.AddCustomProperty("oncorUser", Owner);
return(book);
}
private void ProceedSummaryWorksheet(SalesReport report, XlWorkbook workBook, XlWorksheet summarySheet, XlWorksheet afterSheet)
{
summarySheet.Name = "Summary";
XlStyle matrixStyle = CreateSummaryStyle(workBook, "MatrixStyle");
ProceedSummaryMatrix(report, summarySheet, matrixStyle);
ProceedSummaryWorksheetCharts(summarySheet, report.Products.Length + 1);
ProceedSummaryPrintSettings(summarySheet);
summarySheet.Columns.AutoFit();// proceed AutoFit before header
ProceedSummaryWorksheetHeader(summarySheet);
summarySheet.Select();
}
/// <summary>
/// Loads an Excelfile on local Filesystem
/// </summary>
/// <param name="Filename"></param>
/// <returns></returns>
public XlWorkbook Open(string fileName)
{
object[] paramArray = new object[1];
paramArray[0] = fileName;
object returnValue = InstanceType.InvokeMember("Open", BindingFlags.InvokeMethod, null, ComReference, paramArray, XlLateBindingApiSettings.XlThreadCulture);
if (null == returnValue)
{
return(null);
}
XlWorkbook newClass = new XlWorkbook(this, returnValue);
ListChildReferences.Add(newClass);
return(newClass);
}
public void OpenWorkbook(string filename, LoadRow loadRow, CheckSheetCount checkSheetCount, CheckHeaders checkHeaders)
{
XlWorkbook book = new XlWorkbook();
book.Load(filename);
Load(book, AddSheet, loadRow);
checkSheetCount();
if (checkHeaders != null)
{
foreach (XlWorksheet worksheet in book.Worksheets)
{
checkHeaders(worksheet);
}
}
}
private void LoadProperties(XlWorkbook book)
{
foreach (XlCustomProperty property in book.Properties)
{
if (property.Name == "oncorId")
{
string guidValue = property.Value;
Id = Guid.Parse(guidValue);
}
if (property.Name == "oncorUser")
{
Owner = property.Value;
}
}
}
private static void ReadWorkbook(string fName)
{
Console.WriteLine("Reading workbook named [{0}]", fName);
XlWorkbook book = new XlWorkbook();
book.Load(fName);
foreach (XlWorksheet worksheet in book.Worksheets)
{
PrintWorksheet(worksheet);
}
foreach (XlCustomProperty property in book.Properties)
{
PrintCustomProperty(property);
}
Console.WriteLine("Closing workbook named [{0}]", fName);
}
/// <summary>
/// returns an Workbook by Index, not 0 based
/// </summary>
/// <param name="i"></param>
/// <returns></returns>
public XlWorkbook this[int index]
{
get
{
object[] paramArray = new object[1];
paramArray[0] = index;
object returnValue = InstanceType.InvokeMember("Item", BindingFlags.GetProperty, null, ComReference, paramArray, XlLateBindingApiSettings.XlThreadCulture);
if (null == returnValue)
{
return(null);
}
XlWorkbook newClass = new XlWorkbook(this, returnValue);
ListChildReferences.Add(newClass);
return(newClass);
}
}
/// <summary>
/// Foreach Enumerator
/// </summary>
/// <returns></returns>
public IEnumerator GetEnumerator()
{
int iCount = Count;
XlWorkbook[] res_books = new XlWorkbook[iCount];
for (int i = 1; i <= iCount; i++)
{
res_books[i - 1] = this[i];
}
for (int i = 0; i < res_books.Length; i++)
{
yield return(res_books[i]);
}
}
public XlWorkbook OpenDatabase(string fileName, object commandText, object commandType, object backgroundQuery, object importDataAs)
{
object[] paramArray = new object[5];
paramArray[0] = fileName;
paramArray[1] = commandText;
paramArray[2] = commandType;
paramArray[3] = backgroundQuery;
paramArray[4] = importDataAs;
object returnValue = InstanceType.InvokeMember("OpenDatabase", BindingFlags.InvokeMethod, null, ComReference, paramArray, XlLateBindingApiSettings.XlThreadCulture);
if (null == returnValue)
{
return(null);
}
XlWorkbook newClass = new XlWorkbook(this, returnValue);
ListChildReferences.Add(newClass);
return(newClass);
}
private void button1_Click(object sender, EventArgs e)
{
// start excel and turn off msg boxes
excelApplication = new XlApplication();
excelApplication.DisplayAlerts = false;
// add a new workbook
XlWorkbook workBook = excelApplication.Workbooks.Add();
// add new global Code Module
XlVBComponent globalModule = workBook.VBProject.VBComponents.Add(LateBindingApi.Excel.Enums.vbext_ComponentType.vbext_ct_StdModule);
globalModule.Name = "MyNewCodeModule";
// add a new procedure to the modul
globalModule.CodeModule.InsertLines(1, "Public Sub HelloWorld(Param as string)\r\n MsgBox \"Hello World!\" & vbnewline & Param\r\nEnd Sub");
// create a click event trigger for the first worksheet
int linePosition = workBook.VBProject.VBComponents[2].CodeModule.CreateEventProc("BeforeDoubleClick", "Worksheet");
workBook.VBProject.VBComponents[2].CodeModule.InsertLines(linePosition + 1, "HelloWorld \"BeforeDoubleClick\"");
// display info in the worksheet
workBook.Worksheets[1].Cells(2, 2).Value = "This workbook contains dynamic created VBA Moduls and Event Code";
workBook.Worksheets[1].Cells(5, 2).Value = "Open the VBA Editor to see the code";
workBook.Worksheets[1].Cells(8, 2).Value = "Do a double click to catch the BeforeDoubleClick Event from this Worksheet.";
// save the book
string fileExtension = XlConverter.GetDefaultExtension(excelApplication);
string workbookFile = string.Format("{0}\\Example7{1}", Environment.CurrentDirectory, fileExtension);
workBook.SaveAs(workbookFile);
// close excel and dispose reference
excelApplication.Quit();
excelApplication.Dispose();
FinishDialog fDialog = new FinishDialog("Workbook saved.", workbookFile);
fDialog.ShowDialog(this);
}
private void button1_Click(object sender, EventArgs e)
{
// start excel and turn off msg boxes
excelApplication = new XlApplication();
excelApplication.DisplayAlerts = false;
// add a new workbook
XlWorkbook workBook = excelApplication.Workbooks.Add();
XlWorksheet workSheet = workBook.Worksheets[1];
workSheet.Cells(1, 1).Value = "these sample shapes was dynamicly created by code.";
// create a star
XlShape starShape = workSheet.Shapes.AddShape(LateBindingApi.Excel.Enums.MsoAutoShapeType.msoShape32pointStar, 10, 50, 200, 20);
// create a simple textbox
XlShape textBox = workSheet.Shapes.AddTextbox(LateBindingApi.Excel.Enums.MsoTextOrientation.msoTextOrientationHorizontal, 10, 150, 200, 50);
textBox.TextFrame.Characters().Text = "text";
textBox.TextFrame.Characters().Font.Size = 14;
// create a wordart
XlShape textEffect = workSheet.Shapes.AddTextEffect(LateBindingApi.Excel.Enums.MsoPresetTextEffect.msoTextEffect14, "WordArt", "Arial", 12, LateBindingApi.Excel.Enums.MsoTriState.msoTrue, LateBindingApi.Excel.Enums.MsoTriState.msoFalse, 10, 250);
// create text effect
XlShape textDiagram = workSheet.Shapes.AddTextEffect(LateBindingApi.Excel.Enums.MsoPresetTextEffect.msoTextEffect11, "Effect", "Arial", 14, LateBindingApi.Excel.Enums.MsoTriState.msoFalse, LateBindingApi.Excel.Enums.MsoTriState.msoFalse, 10, 350);
// save the book
string fileExtension = XlConverter.GetDefaultExtension(excelApplication);
string workbookFile = string.Format("{0}\\Example4{1}", Environment.CurrentDirectory, fileExtension);
workBook.SaveAs(workbookFile);
// close excel and dispose reference
excelApplication.Quit();
excelApplication.Dispose();
FinishDialog fDialog = new FinishDialog("Workbook saved.", workbookFile);
fDialog.ShowDialog(this);
}
public static Guid GetFileId(FilestoreFile excelDet)
{
if (excelDet != null)
{
try
{
XlCustomProperties props = XlWorkbook.LoadProperties(excelDet);
foreach (XlCustomProperty property in props)
{
if (property.Name == "oncorId")
{
string guidValue = property.Value;
if (!string.IsNullOrEmpty(guidValue))
{
return(Guid.Parse(guidValue));
}
}
}
}
catch { }
}
return(Guid.Empty);
}
internal static XlNonCreatable CreateDynamicType(IXlObject parent, object comProxy)
{
string className = TypeDescriptor.GetClassName(comProxy);
switch (className)
{
case "Workbooks":
XlWorkbooks newBooks = new XlWorkbooks(parent, comProxy);
return(newBooks);
case "Workbook":
XlWorkbook newBook = new XlWorkbook(parent, comProxy);
return(newBook);
case "Worksheets":
XlWorksheets newSheets = new XlWorksheets(parent, comProxy);
return(newSheets);
case "Worksheet":
XlWorksheet newSheet = new XlWorksheet(parent, comProxy);
return(newSheet);
case "Range":
XlRange newRange = new XlRange(parent, comProxy);
return(newRange);
default:
throw (new ProxyTypeException("Unhandled ComProxyType: " + className));
}
}
private Guid CreateExcelFile(Dictionary <CompoundIdentity, Tuple <int, Site> > siteDict, Dictionary <CompoundIdentity, Tuple <int, SamplingEvent> > eventDict,
Dictionary <CompoundIdentity, Tuple <int, WaterQualityDeployment> > deploymentDict, List <Tuple <int, WaterQualityMeasurement> > measurementList,
Dictionary <CompoundIdentity, Organization> orgDict, Dictionary <CompoundIdentity, FieldTrip> fieldTripDict,
Dictionary <CompoundIdentity, FieldActivity> fieldActivityDict, Dictionary <CompoundIdentity, Project> projectDict)
{
IFileStoreProvider provider = FileStoreManager.Instance.GetProvider();
//Setting up file and Excel Workbook
FilestoreFile deployFile = provider.MakeTemp(DateTime.UtcNow.AddHours(4));
XlWorkbook book = new XlWorkbook();
XlWorksheets sheets = book.Worksheets;
//Generating Sampling Event Sheet
XlSchema eventSchema = GetSampleEventSchema();
XlWorksheet eventSheet = sheets.AddWorksheet("SamplingEvents", XlColor.White, eventSchema);
XlRows eventRows = eventSheet.Rows;
var orderedEvents = eventDict.OrderBy(x => x.Value.Item1);
foreach (var evt in orderedEvents)
{
string orgName = na;
if (orgDict.ContainsKey(evt.Value.Item2.PrincipalOrgId))
{
orgName = orgDict[evt.Value.Item2.PrincipalOrgId].Name;
}
string ftripName = na;
string factivityName = na;
string projName = na;
if (fieldTripDict.ContainsKey(evt.Value.Item2.FieldTripId))
{
FieldTrip ftrip = fieldTripDict[evt.Value.Item2.FieldTripId];
ftripName = ftrip.Name;
if (fieldActivityDict.ContainsKey(ftrip.FieldActivityId))
{
FieldActivity factivity = fieldActivityDict[ftrip.FieldActivityId];
factivityName = factivity.Name;
if (projectDict.ContainsKey(factivity.ProjectId))
{
projName = projectDict[factivity.ProjectId].Name;
}
}
}
List <string> evtItems = new List <string>();
evtItems.Add(orgName);
evtItems.Add(projName);
evtItems.Add(factivityName);
evtItems.Add(ftripName);
evtItems.Add(evt.Value.Item2.Name);
evtItems.Add(evt.Value.Item1.ToString());
evtItems.Add(evt.Value.Item2.Description);
//evtItems.Add(evt.Value.Item2.DateRange.Min.ToString());
//evtItems.Add(evt.Value.Item2.DateRange.Max.ToString());
SchemaRowData row = new SchemaRowData(eventSchema, evtItems);
eventRows.AddRow(row);
}
//Generating Deployment/Measurement Sheet
XlSchema measSchema = GetDeployMeasurementSchema();
XlWorksheet measSheet = sheets.AddWorksheet("WaterQualityMeasurements", XlColor.White, measSchema);
XlRows measRows = measSheet.Rows;
var orderedMeasurements = measurementList.OrderBy(x => x.Item1);
foreach (var meas in orderedMeasurements)
{
WaterQualityDeployment deploy = deploymentDict[meas.Item2.DeploymentId].Item2;
WaterQualityMeasurement measurement = meas.Item2;
int eventIndex = eventDict[deploy.SampleEventId].Item1;
//deploy.SiteId could be a dangling reference
string siteFK = na;
if (siteDict.ContainsKey(deploy.SiteId))
{
siteFK = siteDict[deploy.SiteId].Item1.ToString();
}
List <string> measItems = new List <string>();
measItems.Add(meas.Item1.ToString());
measItems.Add(deploy.Name);
measItems.Add(deploy.Description);
measItems.Add(eventIndex.ToString());
measItems.Add(siteFK);
measItems.Add(deploy.Range.StartDate.ToString());
measItems.Add(deploy.Range.EndDate.ToString());
measItems.Add(measurement.SampleDate.ToString());
measItems.Add(measurement.SurfaceElevation.ToString());
measItems.Add(measurement.Temperature.ToString());
measItems.Add(measurement.pH.ToString());
measItems.Add(measurement.DissolvedOxygen.ToString());
measItems.Add(measurement.Conductivity.ToString());
measItems.Add(measurement.Salinity.ToString());
measItems.Add(measurement.Velocity.ToString());
SchemaRowData row = new SchemaRowData(measSchema, measItems);
measRows.AddRow(row);
}
//Generating Site Sheet
XlSchema siteSchema = GetSiteSchema();
XlWorksheet siteSheet = sheets.AddWorksheet("Sites", XlColor.White, siteSchema);
XlRows siteRows = siteSheet.Rows;
var orderedSites = siteDict.OrderBy(x => x.Value.Item1);
foreach (var site in orderedSites)
{
Site s = site.Value.Item2;
List <string> siteItems = new List <string>();
siteItems.Add(site.Value.Item1.ToString());
siteItems.Add(s.Name);
siteItems.Add(s.Description);
IGeometry2 <double> geom = s.Location;
if (geom != null)
{
if (geom is PolygonBag2 <double> )
{
siteItems.Add(WktUtils.ToWkt(geom as PolygonBag2 <double>).ToString());
}
else if (geom is Polygon2 <double> )
{
siteItems.Add(WktUtils.ToWkt(geom as Polygon2 <double>).ToString());
}
else if (geom is Polyline2 <double> )
{
siteItems.Add(WktUtils.ToWkt(geom as Polyline2 <double>).ToString());
}
else if (geom is PolylineBag2 <double> )
{
siteItems.Add(WktUtils.ToWkt(geom as PolylineBag2 <double>).ToString());
}
else if (geom is Point2 <double> )
{
siteItems.Add(WktUtils.ToWkt(geom as Point2 <double>).ToString());
}
}
else
{
siteItems.Add("");
}
Point2 <double> geom2 = s.LocationMark;
if (geom2 != null)
{
siteItems.Add(WktUtils.ToWkt(geom2 as Point2 <double>).ToString());
}
else
{
siteItems.Add("");
}
SchemaRowData row = new SchemaRowData(siteSchema, siteItems);
siteRows.AddRow(row);
}
book.Save(deployFile);
deployFile.Flush();
deployFile.Close();
deployFile.Dispose();
return(deployFile.FileId);
}
void excelApplication_WorkbookDeactivate(XlWorkbook Wb)
{
textBoxEvents.BeginInvoke(_updateDelegate, new object[] { "Event WorkbookDeactivate called." });
}
void excelApplication_WorkbookBeforeClose(XlWorkbook Wb, ref bool Cancel)
{
textBoxEvents.BeginInvoke(_updateDelegate, new object[] { "Event WorkbookBeforeClose called." });
}
void excelApplication_NewWorkbook(XlWorkbook Wb)
{
textBoxEvents.BeginInvoke(_updateDelegate, new object[] { "Event NewWorkbook called." });
}
private void button1_Click(object sender, EventArgs e)
{
// start excel and turn off msg boxes
excelApplication = new XlApplication();
excelApplication.DisplayAlerts = false;
// dont show dialogs with an invisible excel
excelApplication.Visible = true;
// add a new workbook
XlWorkbook workBook = excelApplication.Workbooks.Add();
XlWorksheet workSheet = workBook.Worksheets[1];
// show selected window and display user clicks ok or cancel
bool returnValue = false;
RadioButton radioSelectButton = GetSelectedRadioButton();
switch (radioSelectButton.Text)
{
case "xlDialogAddinManager":
returnValue = excelApplication.Dialogs[LateBindingApi.Excel.Enums.XlBuiltInDialog.xlDialogAddinManager].Show();
break;
case "xlDialogFont":
returnValue = excelApplication.Dialogs[LateBindingApi.Excel.Enums.XlBuiltInDialog.xlDialogFont].Show();
break;
case "xlDialogEditColor":
returnValue = excelApplication.Dialogs[LateBindingApi.Excel.Enums.XlBuiltInDialog.xlDialogEditColor].Show();
break;
case "xlDialogGallery3dBar":
returnValue = excelApplication.Dialogs[LateBindingApi.Excel.Enums.XlBuiltInDialog.xlDialogGallery3dBar].Show();
break;
case "xlDialogSearch":
returnValue = excelApplication.Dialogs[LateBindingApi.Excel.Enums.XlBuiltInDialog.xlDialogSearch].Show();
break;
case "xlDialogPrinterSetup":
returnValue = excelApplication.Dialogs[LateBindingApi.Excel.Enums.XlBuiltInDialog.xlDialogPrinterSetup].Show();
break;
case "xlDialogFormatNumber":
returnValue = excelApplication.Dialogs[LateBindingApi.Excel.Enums.XlBuiltInDialog.xlDialogFormatNumber].Show();
break;
case "xlDialogApplyStyle":
returnValue = excelApplication.Dialogs[LateBindingApi.Excel.Enums.XlBuiltInDialog.xlDialogApplyStyle].Show();
break;
default:
throw (new Exception("Unkown dialog selected."));
}
string message = string.Format("The dialog returns {0}.", returnValue);
MessageBox.Show(this, message, this.Text, MessageBoxButtons.OK, MessageBoxIcon.Information);
// close excel and dispose reference
excelApplication.Quit();
excelApplication.Dispose();
}
private void button1_Click(object sender, EventArgs e)
{
XlCommandBar commandBar;
XlCommandBarPopup commandBarPop;
XlCommandBarButton commandBarBtn;
// first we enable the event support
XlLateBindingApiSettings.EventsEnabled = true;
// start excel and turn off msg boxes
excelApplication = new XlApplication();
excelApplication.DisplayAlerts = false;
// add a new workbook
XlWorkbook workBook = excelApplication.Workbooks.Add();
#region Create a new menu
// add a commandbar popup
commandBarPop = (XlCommandBarPopup)excelApplication.CommandBars["Worksheet Menu Bar"].Controls.Add(
MsoControlType.msoControlPopup, Missing.Value, Missing.Value, Missing.Value, true);
commandBarPop.Caption = "commandBarPopup";
// add a button to the popup
#region a lot of words, how to access the picture
/*
* you can see we use an own icon via .PasteFace()
* is not possible from outside process boundaries to use the PictureProperty directly
* the reason for is IPictureDisp: http://support.microsoft.com/kb/286460/de
* is not important is early or late binding or managed or unmanaged, the behaviour is always the same
* For example, a COMAddin running as InProcServer and can access the Picture Property
* Use the IconConverter.cs class from this project to convert a image to IPictureDisp
*/
#endregion
commandBarBtn = (XlCommandBarButton)commandBarPop.Controls.Add(MsoControlType.msoControlButton);
commandBarBtn.Style = MsoButtonStyle.msoButtonIconAndCaption;
commandBarBtn.Caption = "commandBarButton";
Clipboard.SetDataObject(this.Icon.ToBitmap());
commandBarBtn.PasteFace();
commandBarBtn.Click += new CommandBarButtonEvents_ClickEventHandler(commandBarBtn_Click);
#endregion
#region Create a new toolbar
// add a new toolbar
commandBar = excelApplication.CommandBars.Add("MyCommandBar", MsoBarPosition.msoBarTop, false, true);
commandBar.Visible = true;
// add a button to the toolbar
commandBarBtn = (XlCommandBarButton)commandBar.Controls.Add(MsoControlType.msoControlButton);
commandBarBtn.Style = MsoButtonStyle.msoButtonIconAndCaption;
commandBarBtn.Caption = "commandBarButton";
commandBarBtn.FaceId = 3;
commandBarBtn.Click += new CommandBarButtonEvents_ClickEventHandler(commandBarBtn_Click);
// add a dropdown box to the toolbar
commandBarPop = (XlCommandBarPopup)commandBar.Controls.Add(MsoControlType.msoControlPopup);
commandBarPop.Caption = "commandBarPopup";
// add a button to the popup, we use an own icon for the button
commandBarBtn = (XlCommandBarButton)commandBarPop.Controls.Add(MsoControlType.msoControlButton);
commandBarBtn.Style = MsoButtonStyle.msoButtonIconAndCaption;
commandBarBtn.Caption = "commandBarButton";
Clipboard.SetDataObject(this.Icon.ToBitmap());
commandBarBtn.PasteFace();
commandBarBtn.Click += new CommandBarButtonEvents_ClickEventHandler(commandBarBtn_Click);
#endregion
#region Create a new ContextMenu
// add a commandbar popup
commandBarPop = (XlCommandBarPopup)excelApplication.CommandBars["Cell"].Controls.Add(
MsoControlType.msoControlPopup, Missing.Value, Missing.Value, Missing.Value, true);
commandBarPop.Caption = "commandBarPopup";
// add a button to the popup
commandBarBtn = (XlCommandBarButton)commandBarPop.Controls.Add(MsoControlType.msoControlButton);
commandBarBtn.Style = MsoButtonStyle.msoButtonIconAndCaption;
commandBarBtn.Caption = "commandBarButton";
commandBarBtn.FaceId = 9;
commandBarBtn.Click += new CommandBarButtonEvents_ClickEventHandler(commandBarBtn_Click);
#endregion
#region Display info
workBook.Worksheets[1].Cells(2, 2).Value = "this excel instance contains 3 custom menus";
workBook.Worksheets[1].Cells(3, 2).Value = "the main menu, the toolbar menu and the cell context menu";
workBook.Worksheets[1].Cells(4, 2).Value = "in this case the menus are temporaily created";
workBook.Worksheets[1].Cells(5, 2).Value = "they are not persistant and needs no unload event or something like this";
workBook.Worksheets[1].Cells(6, 2).Value = "you can also create persistant menus if you want";
#endregion
excelApplication.Visible = true;
button1.Enabled = false;
button2.Enabled = true;
}
