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);
}
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);
}
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);
}
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);
}
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];
// some kind of numerics
string Pattern1 = string.Format("0{0}00", XlLateBindingApiSettings.XlThreadCulture.NumberFormat.CurrencyDecimalSeparator);
string Pattern2 = string.Format("#{1}##0{0}00", XlLateBindingApiSettings.XlThreadCulture.NumberFormat.CurrencyDecimalSeparator, XlLateBindingApiSettings.XlThreadCulture.NumberFormat.CurrencyGroupSeparator);
workSheet.Range("A1").Value = "Type";
workSheet.Range("B1").Value = "Value";
workSheet.Range("C1").Value = "Formatted " + Pattern1;
workSheet.Range("D1").Value = "Formatted " + Pattern2;
int integerValue = 532234;
workSheet.Range("A3").Value = "Integer";
workSheet.Range("B3").Value = integerValue;
workSheet.Range("C3").Value = integerValue;
workSheet.Range("C3").NumberFormat = Pattern1;
workSheet.Range("D3").Value = integerValue;
workSheet.Range("D3").NumberFormat = Pattern2;
double doubleValue = 23172.64;
workSheet.Range("A4").Value = "double";
workSheet.Range("B4").Value = doubleValue;
workSheet.Range("C4").Value = doubleValue;
workSheet.Range("C4").NumberFormat = Pattern1;
workSheet.Range("D4").Value = doubleValue;
workSheet.Range("D4").NumberFormat = Pattern2;
float floatValue = 84345.9132f;
workSheet.Range("A5").Value = "float";
workSheet.Range("B5").Value = floatValue;
workSheet.Range("C5").Value = floatValue;
workSheet.Range("C5").NumberFormat = Pattern1;
workSheet.Range("D5").Value = floatValue;
workSheet.Range("D5").NumberFormat = Pattern2;
Decimal decimalValue = 7251231.313367m;
workSheet.Range("A6").Value = "Decimal";
workSheet.Range("B6").Value = decimalValue;
workSheet.Range("C6").Value = decimalValue;
workSheet.Range("C6").NumberFormat = Pattern1;
workSheet.Range("D6").Value = decimalValue;
workSheet.Range("D6").NumberFormat = Pattern2;
workSheet.Range("A9").Value = "DateTime";
workSheet.Range("B10").Value = XlLateBindingApiSettings.XlThreadCulture.DateTimeFormat.FullDateTimePattern;
workSheet.Range("C10").Value = XlLateBindingApiSettings.XlThreadCulture.DateTimeFormat.LongDatePattern;
workSheet.Range("D10").Value = XlLateBindingApiSettings.XlThreadCulture.DateTimeFormat.ShortDatePattern;
workSheet.Range("E10").Value = XlLateBindingApiSettings.XlThreadCulture.DateTimeFormat.LongTimePattern;
workSheet.Range("F10").Value = XlLateBindingApiSettings.XlThreadCulture.DateTimeFormat.ShortTimePattern;
// DateTime
DateTime dateTimeValue = DateTime.Now;
workSheet.Range("B11").Value = dateTimeValue;
workSheet.Range("B11").NumberFormat = XlLateBindingApiSettings.XlThreadCulture.DateTimeFormat.FullDateTimePattern;
workSheet.Range("C11").Value = dateTimeValue;
workSheet.Range("C11").NumberFormat = XlLateBindingApiSettings.XlThreadCulture.DateTimeFormat.LongDatePattern;
workSheet.Range("D11").Value = dateTimeValue;
workSheet.Range("D11").NumberFormat = XlLateBindingApiSettings.XlThreadCulture.DateTimeFormat.ShortDatePattern;
workSheet.Range("E11").Value = dateTimeValue;
workSheet.Range("E11").NumberFormat = XlLateBindingApiSettings.XlThreadCulture.DateTimeFormat.LongTimePattern;
workSheet.Range("F11").Value = dateTimeValue;
workSheet.Range("F11").NumberFormat = XlLateBindingApiSettings.XlThreadCulture.DateTimeFormat.ShortTimePattern;
// string
workSheet.Range("A14").Value = "String";
workSheet.Range("B14").Value = "This is a sample String";
workSheet.Range("B14").NumberFormat = "@";
// number as string
workSheet.Range("B15").Value = "513";
workSheet.Range("B15").NumberFormat = "@";
// set colums
workSheet.Columns[1].AutoFit();
workSheet.Columns[2].AutoFit();
workSheet.Columns[3].AutoFit();
workSheet.Columns[4].AutoFit();
// save the book
string fileExtension = XlConverter.GetDefaultExtension(excelApplication);
string workbookFile = string.Format("{0}\\Example3{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];
// font action
workSheet.Range("A1").Value = "Arial Size:8 Bold Italic Underline";
workSheet.Range("A1").Font.Name = "Arial";
workSheet.Range("A1").Font.Size = 8;
workSheet.Range("A1").Font.Bold = true;
workSheet.Range("A1").Font.Italic = true;
workSheet.Range("A1").Font.Underline = true;
workSheet.Range("A1").Font.Color = Color.Violet.ToArgb();
workSheet.Range("A3").Value = "Times New Roman Size:10";
workSheet.Range("A3").Font.Name = "Times New Roman";
workSheet.Range("A3").Font.Size = 10;
workSheet.Range("A3").Font.Color = Color.Orange.ToArgb();
workSheet.Range("A5").Value = "Comic Sans MS Size:12 WrapText";
workSheet.Range("A5").Font.Name = "Comic Sans MS";
workSheet.Range("A5").Font.Size = 12;
workSheet.Range("A5").WrapText = true;
workSheet.Range("A5").Font.Color = Color.Navy.ToArgb();
// HorizontalAlignment
workSheet.Range("A7").Value = "xlHAlignLeft";
workSheet.Range("A7").HorizontalAlignment = LateBindingApi.Excel.Enums.XlHAlign.xlHAlignLeft;
workSheet.Range("B7").Value = "xlHAlignCenter";
workSheet.Range("B7").HorizontalAlignment = LateBindingApi.Excel.Enums.XlHAlign.xlHAlignCenter;
workSheet.Range("C7").Value = "xlHAlignRight";
workSheet.Range("C7").HorizontalAlignment = LateBindingApi.Excel.Enums.XlHAlign.xlHAlignRight;
workSheet.Range("D7").Value = "xlHAlignJustify";
workSheet.Range("D7").HorizontalAlignment = LateBindingApi.Excel.Enums.XlHAlign.xlHAlignJustify;
workSheet.Range("E7").Value = "xlHAlignDistributed";
workSheet.Range("E7").HorizontalAlignment = LateBindingApi.Excel.Enums.XlHAlign.xlHAlignDistributed;
workSheet.Range("A9").Value = "xlVAlignTop";
workSheet.Range("A9").VerticalAlignment = LateBindingApi.Excel.Enums.XlVAlign.xlVAlignTop;
workSheet.Range("B9").Value = "xlVAlignCenter";
workSheet.Range("B9").VerticalAlignment = LateBindingApi.Excel.Enums.XlVAlign.xlVAlignCenter;
workSheet.Range("C9").Value = "xlVAlignBottom";
workSheet.Range("C9").VerticalAlignment = LateBindingApi.Excel.Enums.XlVAlign.xlVAlignBottom;
workSheet.Range("D9").Value = "xlVAlignDistributed";
workSheet.Range("D9").VerticalAlignment = LateBindingApi.Excel.Enums.XlVAlign.xlVAlignDistributed;
workSheet.Range("E9").Value = "xlVAlignJustify";
workSheet.Range("E9").VerticalAlignment = LateBindingApi.Excel.Enums.XlVAlign.xlVAlignJustify;
// setup rows and columns
workSheet.Columns[1].AutoFit();
workSheet.Columns[2].ColumnWidth = 25;
workSheet.Columns[3].ColumnWidth = 25;
workSheet.Columns[4].ColumnWidth = 25;
workSheet.Columns[5].ColumnWidth = 25;
workSheet.Rows[9].RowHeight = 35;
// save the book
string fileExtension = XlConverter.GetDefaultExtension(excelApplication);
string workbookFile = string.Format("{0}\\Example2{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);
}