public void ProcStyles(EXCEL.Style style)
{
try
{
style.Font.Name = LocalLang.GetLocFont(style.Font.Name);
}
catch (Exception)
{
Common.WriteLine("[Failed] Style Font: " + style.Name + " " + style.Font.Name);
}
if (style.NumberFormat.Contains("$") && style.NumberFormat.Contains("#"))
{
var numberFormat = style.NumberFormat;
var styleName = style.Name;
try
{
style.NumberFormat = ProcCurrency(numberFormat, styleName);
}
catch (Exception)
{
Common.WriteLine("[Failed] Style Currency: " + style.Name + " " + style.NumberFormat);
}
}
}
public Excel.Style GetHeadingStyle()
{
if (HeadingStyle == null)
{
Excel.Style style = Globals.ThisAddIn.Application.ActiveWorkbook.Styles.Add("Moodle Title Style");
style.Font.Size = 15;
style.Font.Bold = true;
style.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.DodgerBlue);
style.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White);
style.Interior.Pattern = Excel.XlPattern.xlPatternSolid;
style.Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlContinuous;
style.Borders[XlBordersIndex.xlEdgeBottom].Weight = XlBorderWeight.xlMedium;
style.Borders[XlBordersIndex.xlEdgeBottom].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.DodgerBlue);
HeadingStyle = style;
return(style);
}
else
{
return(HeadingStyle);
}
}
public static Excel.Style SetDefaultStyle(this Excel.Style style, ThemeType themeType)
{
var ignoreList = new List <Excel.XlBordersIndex>
{
Excel.XlBordersIndex.xlDiagonalDown,
Excel.XlBordersIndex.xlDiagonalUp,
};
switch (themeType)
{
case ThemeType.White:
style.Interior.ColorIndex = XlColorIndex.xlColorIndexNone;
style.Font.Color = Color.Black;
style.Borders.LineStyle = Excel.XlLineStyle.xlLineStyleNone;
break;
case ThemeType.Dark:
style.Interior.Color = Color.FromArgb(30, 30, 30);
style.Font.Color = Color.FromArgb(220, 220, 220);
style.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
style.Borders.Color = Color.FromArgb(80, 80, 80);
style.Borders.Weight = Excel.XlBorderWeight.xlThin;
ignoreList.ForEach(index =>
{
style.Borders[index].LineStyle = Excel.XlLineStyle.xlLineStyleNone;
});
break;
}
return(style);
}
/// <summary>
/// Imposta tutti i bordi allo stile da applicare al range.
/// </summary>
/// <param name="s">Stile.</param>
/// <param name="colorIndex">Colore del bordo.</param>
/// <param name="weight">Spessore del bordo.</param>
public static void SetAllBorders(Excel.Style s, int colorIndex, Excel.XlBorderWeight weight)
{
s.Borders.ColorIndex = colorIndex;
s.Borders.Weight = weight;
s.Borders[Excel.XlBordersIndex.xlDiagonalDown].LineStyle = Excel.XlLineStyle.xlLineStyleNone;
s.Borders[Excel.XlBordersIndex.xlDiagonalUp].LineStyle = Excel.XlLineStyle.xlLineStyleNone;
}
public static void FormatCells(Excel.Worksheet sheet)
{
Excel.Style style = StyleHelper.getDocGenMainStyle();
sheet.Cells.Style = style;
sheet.Cells.ColumnWidth = 30;
sheet.Cells.Borders[Excel.XlBordersIndex.xlInsideVertical].
Weight = Excel.XlBorderWeight.xlThin;
((Excel.Range)sheet.Rows[(int)Names.Name]).RowHeight = 60;
((Excel.Range)sheet.Rows[(int)Names.Name]).WrapText = true;
}
public Excel.Style FormatCells()
{
Excel.Style style = excel.ActiveWorkbook.Styles.Add("myStyle");
//Creation of an style to format the cells
style.Font.Name = "Segoe UI";
style.Font.Size = 14;
style.Font.Color = ColorTranslator.ToOle(Color.White);
style.Interior.Color = ColorTranslator.ToOle(Color.Silver);
style.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
return(style);
}
public static Excel.Style SetStringStyle(this Excel.Style style, ThemeType themeType)
{
style = style.SetDefaultStyle(themeType);
style.NumberFormat = "@";
switch (themeType)
{
case ThemeType.White:
break;
case ThemeType.Dark:
break;
}
return(style);
}
private void InitializeStyle(Excel.Workbook workbook)
{
//Style of head
headStyle = workbook.Styles.Add(Guid.NewGuid().ToString(), missing);
headStyle.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
headStyle.Font.Name = "Verdana";
headStyle.Font.Size = 10;
headStyle.Font.Bold = true;
headStyle.Font.ColorIndex = 5;
//Style of data
dataStyle = workbook.Styles.Add(Guid.NewGuid().ToString(), missing);
dataStyle.Font.Name = "Verdana";
dataStyle.Font.Size = 10.0f;
dataStyle.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
}
private void InitializeStyle( )
{
//Style of head
headStyle = this.Styles.Add("Result Head Style", missing);
headStyle.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
headStyle.Font.Name = "Verdana";
headStyle.Font.Size = 10;
headStyle.Font.Bold = true;
headStyle.Font.ColorIndex = 5;
//Style of data
dataStyle = this.Styles.Add("Data Style", missing);
dataStyle.Font.Name = "Verdana";
dataStyle.Font.Size = 10.0f;
dataStyle.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
}
private void ThisAddIn_Startup(object sender, System.EventArgs e)
{
Excel.Workbook myWorkbook = this.Application.Workbooks.Open(fileName);
Excel.Worksheet mySheet = myWorkbook.ActiveSheet;
Excel.Style style = this.Application.ActiveWorkbook.Styles.Add("NewStyle");
style.Font.Name = "Verdana";
style.Font.Size = 12;
style.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
style.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Gray);
style.Interior.Pattern = Excel.XlPattern.xlPatternSolid;
Excel.Range FormatingRange = mySheet.get_Range("A1", "A10");
FormatingRange.Style = "NewStyle";
}
public static Excel.Style SetNumberStyle(this Excel.Style style, ThemeType themeType)
{
style = style.SetDefaultStyle(themeType);
style.Font.Name = "Consolas";
switch (themeType)
{
case ThemeType.White:
break;
case ThemeType.Dark:
style.Font.Color = Color.FromArgb(181, 206, 168);
break;
}
return(style);
}
private void DoMyExcelStuff()
{
Excel.Application excelApplication = new Excel.Application();
Excel.Workbooks books = excelApplication.Workbooks;
Excel.Workbook wBook = books.Add("");
Excel.Worksheet wSheet = (Excel.Worksheet)wBook.ActiveSheet;
Excel.Styles styles = wBook.Styles;
Excel.Style columnHeader = styles.Add("ColumnHeader");
columnHeader.Font.Size = 12;
columnHeader.Font.Bold = true;
excelApplication.Range["A1"].Value = "Name";
excelApplication.Range["A1"].Style = columnHeader;
wBook.SaveAs(@"c:\Temp\tst" + DateTime.Now.ToString("mmss") + ".xlsx");
// No need for Marshal.ReleaseComObject(...)
// No need for ... = null
excelApplication.Quit();
}
private void ApplyStylesToRanges()
{
//<Snippet28>
Excel.Style style = this.Application.ActiveWorkbook.Styles.Add("NewStyle");
style.Font.Name = "Verdana";
style.Font.Size = 12;
style.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
style.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Gray);
style.Interior.Pattern = Excel.XlPattern.xlPatternSolid;
//</Snippet28>
//<Snippet29>
Excel.Range rangeStyles = this.Application.get_Range("A1");
rangeStyles.Value2 = "'Style Test";
rangeStyles.Style = "NewStyle";
rangeStyles.Columns.AutoFit();
//</Snippet29>
}
public static Excel.Style getDocGenMainStyle()
{
string styleName = "DocGenMainStyle";
Excel.Workbook workbook = (Excel.Workbook)Globals.ThisAddIn.Application.ActiveWorkbook;
Excel.Style style = null;
if (isStyleExists(styleName))
{
style = workbook.Styles[styleName];
}
else
{
style = workbook.Styles.Add(styleName);
}
style.Font.Name = "Isocpeur";
style.Font.Size = 14;
style.Font.Italic = true;
return(style);
}
public Excel.Style GetDescriptionStyle()
{
if (DescStyle == null)
{
Excel.Style style = Globals.ThisAddIn.Application.ActiveWorkbook.Styles.Add("Moodle Desc Style");
style.Font.Size = 12;
style.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
style.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightYellow);
style.Interior.Pattern = Excel.XlPattern.xlPatternSolid;
DescStyle = style;
return(style);
}
else
{
return(DescStyle);
}
}
private void applyStyle(string style, string format)
{
Excel.Range xRange = Globals.ExcelCustomRibbon.Application.Selection;
try
{
xRange.Style = style;
}
catch (Exception e) when(e.Message.Contains("Style '" + style + "' not found."))
{
Excel.Style dateStyle = Globals.ExcelCustomRibbon.Application.ActiveWorkbook.Styles.Add(style);
dateStyle.IncludeAlignment = false;
dateStyle.IncludeBorder = false;
dateStyle.IncludeFont = false;
dateStyle.IncludeNumber = true;
dateStyle.NumberFormat = format;
dateStyle.IncludePatterns = false;
dateStyle.IncludeProtection = false;
xRange.Style = style;
}
}
/// <summary>
/// Events
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
#region
private void StockRibbon_Load(object sender, RibbonUIEventArgs e)
{
//Worksheet CurrenntSheet = Globals.ThisAddIn.GetActiveWorkSheet();
//Header Style - Start
Excel.Style HeaderStyle = ActiveSheet().Application.ActiveWorkbook.Styles.Add("HdrStyle");
HeaderStyle.Font.Name = "Calibri";
HeaderStyle.Font.Size = 12;
HeaderStyle.Font.Bold = true;
HeaderStyle.Font.Color = System.Drawing.ColorTranslator.ToOle(Color.Black);
HeaderStyle.Interior.Color = System.Drawing.ColorTranslator.ToOle(Color.Orange);
Excel.Range HeaderRange = ActiveSheet().Range["A1", "B1"];
ActiveSheet().Range["A1"].Value = "Stock Ticker";
ActiveSheet().Range["B1"].Value = "Current Stock Value";
//CurrenntSheet.Columns.AutoFit();
HeaderRange.Style = "HdrStyle";
//Header Style - End
//Info Style - Start
Excel.Style InfoStyle = ActiveSheet().Application.ActiveWorkbook.Styles.Add("InfoStyle");
InfoStyle.Font.Name = "Calibri";
InfoStyle.Font.Size = 11;
InfoStyle.Font.Bold = true;
InfoStyle.Font.Color = System.Drawing.ColorTranslator.ToOle(Color.Black);
InfoStyle.Interior.Color = System.Drawing.ColorTranslator.ToOle(Color.Orange);
Excel.Range InfoRange = ActiveSheet().Range["A2", "B10"];
fillStockItems();
//CurrenntSheet.Columns.AutoFit();
HeaderRange.Style = "InfoStyle";
// Info Style - End
ActiveSheet().Columns.AutoFit();
}
public static void Export1(DataTable dt, string filepath)
{
String strFileName = "";
strFileName = filepath;
// Server File Path Where you want to save excel file.
ExcelApp.Application myExcel = new ExcelApp.Application();
//Create a New file
ExcelApp._Workbook mybook = myExcel.Workbooks.Add(System.Reflection.Missing.Value);
//Open the exist file
//ExcelApp._Workbook mybook = myExcel.Workbooks.Open(filepath,
// Type.Missing, Type.Missing, Type.Missing,
// Type.Missing,Type.Missing, Type.Missing, Type.Missing,
// Type.Missing, Type.Missing, Type.Missing,
// Type.Missing, Type.Missing,Type.Missing, Type.Missing);
//ExcelApp._Workbook mybook = myExcel.Workbooks.Open(Filename: filepath);
myExcel.Visible = false;
try
{
mybook.Activate();
ExcelApp._Worksheet mysheet = (ExcelApp._Worksheet)mybook.ActiveSheet;
int colIndex = 0;///********////
int rowIndex = 0;
//foreach (DataColumn dcol in dt.Columns)
//{
// colIndex = colIndex + 1;
// myExcel.Cells[1, colIndex] = dcol.ColumnName;
//}
foreach (DataColumn dcol in dt.Columns)
{
colIndex = colIndex + 1;
myExcel.Cells[rowIndex + 1, colIndex] = dcol.ColumnName;
mysheet.get_Range(myExcel.Cells[rowIndex + 1, colIndex], myExcel.Cells[rowIndex + 1, colIndex]).WrapText = true;
mysheet.get_Range(myExcel.Cells[rowIndex + 1, colIndex], myExcel.Cells[rowIndex + 1, colIndex]).Font.Bold = true;
mysheet.get_Range(myExcel.Cells[rowIndex + 1, colIndex], myExcel.Cells[rowIndex + 1, colIndex]).Font.Size = 10;
mysheet.get_Range(myExcel.Cells[rowIndex + 1, colIndex], myExcel.Cells[rowIndex + 1, colIndex]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow);
ExcelApp.Borders borders = mysheet.get_Range(myExcel.Cells[rowIndex + 1, colIndex], myExcel.Cells[rowIndex + 1, colIndex]).Borders;
borders[ExcelApp.XlBordersIndex.xlEdgeLeft].LineStyle = ExcelApp.XlLineStyle.xlContinuous;
borders[ExcelApp.XlBordersIndex.xlEdgeTop].LineStyle = ExcelApp.XlLineStyle.xlContinuous;
borders[ExcelApp.XlBordersIndex.xlEdgeBottom].LineStyle = ExcelApp.XlLineStyle.xlContinuous;
borders[ExcelApp.XlBordersIndex.xlEdgeRight].LineStyle = ExcelApp.XlLineStyle.xlContinuous;
borders.Color = 0;
borders[ExcelApp.XlBordersIndex.xlInsideVertical].LineStyle = ExcelApp.XlLineStyle.xlLineStyleNone;
borders[ExcelApp.XlBordersIndex.xlInsideHorizontal].LineStyle = ExcelApp.XlLineStyle.xlLineStyleNone;
borders[ExcelApp.XlBordersIndex.xlDiagonalUp].LineStyle = ExcelApp.XlLineStyle.xlLineStyleNone;
borders[ExcelApp.XlBordersIndex.xlDiagonalDown].LineStyle = ExcelApp.XlLineStyle.xlLineStyleNone;
borders = null;
// mysheet.Columns.WrapText = mysheet.get_Range(myExcel.Cells[rowIndex + 1, colIndex], myExcel.Cells[rowIndex + 1, colIndex]);
//mysheet.get_Range(myExcel.Cells[1, colIndex], myExcel.Cells[1, colIndex]).
}
ExcelApp.Style style1 = myExcel.ActiveWorkbook.Styles.Add("Content", Type.Missing);
//style1.Borders.Color = Color.Black;
style1.Font.Name = "Verdana";
// style1.WrapText = true;
style1.Font.Size = 10;
style1.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
style1.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Pink);
foreach (DataRow drow in dt.Rows)
{
rowIndex = rowIndex + 1;
colIndex = 0;
//string str = drow[3].ToString();
//if ((!string.IsNullOrEmpty(drow[3].ToString())))
//{
// decimal sum = Convert.ToDecimal(str);
// if (sum <= 0)
// {
// break;
// }
//}
foreach (DataColumn dcol in dt.Columns)
{
colIndex = colIndex + 1;
myExcel.Cells[rowIndex + 1, colIndex] = drow[dcol.ColumnName];
}
// //if (sum != 0)
// //{
// int col = colIndex;
// for (int i = 0; i <= col; i++)
// {
// ExcelApp.Range rng = (ExcelApp.Range)mysheet.Cells[rowIndex + 1, col];
// // mysheet.Columns.WrapText = rng;
// // rng.WrapText = true;
// rng.Style = style1;
// ExcelApp.Borders borders = mysheet.get_Range(myExcel.Cells[rowIndex + 1, col], myExcel.Cells[rowIndex + 1, col]).Borders;
// borders[ExcelApp.XlBordersIndex.xlEdgeBottom].LineStyle = ExcelApp.XlLineStyle.xlContinuous;
// }
// //}
//}
}
mysheet.Columns.AutoFit();
//For Saving excel file on Server
mybook.SaveCopyAs(strFileName);
}
catch (Exception wzx)
{
MessageBox.Show(wzx.Message);
}
finally
{
mybook.Close(false, false, System.Reflection.Missing.Value);
myExcel.Quit();
GC.Collect();
}
}
//запись в файл Excel
void ExcelWrite()
{
// Создаём экземпляр нашего приложения
Excel.Application excelApp = new Excel.Application();
// Создаём экземпляр рабочий книги Excel
//Excel.Name="MyFile";
Excel.Workbook workBook;
// Создаём экземпляр листа Excel
Excel.Worksheet workSheet;
//создаём лист и рабочую книгу
workBook = excelApp.Workbooks.Add();
workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(1);
//Заполнение таблицы
// Заполняем первую строку числами от 1 до 10
//вывод заголовков
workSheet.Cells[1, 1] = "Заголовок 1";
workSheet.Cells[1, 2] = "Заголовок 2";
workSheet.Cells[1, 3] = "Заголовок 3";
workSheet.Cells[1, 4] = "Заголовок 4";
workSheet.Cells[1, 5] = "Заголовок 5";
/*
* //вариант 2 вывода заголовков
* int what_doyouwant=5;
* string[] cnames=new string{ "Заголовок1","Заголовок2","Заголовок3","Заголовок4","Заголовок5"}
* for (int k=0;k<what_doyouwant;k++)
* workSheet.Cells[1, k+1] = cnames[k];
*
*/
//вывод всей информации
Random rnd = new Random();
for (int k = 0; k < 50; k++)
{
//запись данных
for (int j = 0; j < 5; j++)
{
//workSheet.Cells[k+2, j+1]=Convert.ToString(rnd.Next(1,1000000));
//workSheet.Cells.form
workSheet.Cells[k + 2, j + 1] = "Перенеси меня 300 раз на новую строку 20 раз, попробуй, рас рас рас";
}
}
//стиль для заголовка
Excel.Style style = excelApp.ActiveWorkbook.Styles.Add("NewStyle");
style.Font.Size = 12; //размер шрифта
style.Font.Bold = true;
style.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray); //цвет
style.Interior.Pattern = Excel.XlPattern.xlPatternSolid; //тип заливки
//выравнивание
style.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
style.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
// style.Borders.LineStyle=Excel.XlLineStyle.xlContinuous;
//Excel.ta
//границы ячееек и установка ширины по самой длинной ячейке
Excel.Range rng = (Excel.Range)workSheet.Range[workSheet.Cells[1, 1], workSheet.Cells[51, 5]];
rng.EntireColumn.AutoFit(); //автоподбор длины по содержимому (не работает тк. после указан размер колонки)
rng.EntireRow.WrapText = true; //автоперенос слов
rng.Rows.ColumnWidth = 25; //ширина
rng.Columns.RowHeight = 50; //высота
// rng.Height=100;
Excel.Borders border = rng.Borders; //границы
border.LineStyle = Excel.XlLineStyle.xlContinuous;
//вставка таблицы для rng
workSheet.ListObjects.Add(Excel.XlListObjectSourceType.xlSrcRange, rng, null, Excel.XlYesNoGuess.xlYes, null);
//стиль и границы заголовка
rng = (Excel.Range)workSheet.Range[workSheet.Cells[1, 1], workSheet.Cells[1, 5]];
rng.Style = "NewStyle";
border = rng.Borders;
border.LineStyle = Excel.XlLineStyle.xlContinuous;
//border.LineStyle = Excel.XlLineStyle.xlContinuous;
// Открываем созданный excel-файл
excelApp.Visible = true; //делаем его видимым
excelApp.UserControl = true; //можно контролировать работу с файлом
//пробуем закрыть файл и если надо записываем его
try
{
//excelApp.ActiveWorkbook.SaveCopyAs(@"flist.xlsx"); //сохранение с определённым именем
//workBook.SaveCopyAs("flist.xlsx"); //сохранение с определённым именем
excelApp.Workbooks.Close();
excelApp.Quit();
}
catch {}
}
//---------------------------------------------------------------------
void Test3()
{
//<Snippet45>
Globals.Sheet1.CheckSpelling();
//</Snippet45>
//<Snippet32>
this.data2001.Group();
//</Snippet32>
//<Snippet33>
this.Application.get_Range("data2001");
this.Application.get_Range("data2002")
.Group();
this.Application.get_Range("dataAll")
.Group();
//</Snippet33>
//<Snippet34>
this.data2001.Ungroup();
this.data2002.Ungroup();
this.dataAll.Ungroup();
//</Snippet34>
//<Snippet35>
this.Application.get_Range("data2001").Ungroup();
this.Application.get_Range("data2002").Ungroup();
this.Application.get_Range("dataAll").Ungroup();
//</Snippet35>
//<Snippet48>
Microsoft.Office.Tools.Excel.NamedRange nr =
this.Controls.AddNamedRange(this.Range["A1"], "NamedRange1");
nr.Value2 = "Hello World";
//</Snippet48>
//<Snippet82>
Globals.Sheet1.PrintPreview();
//</Snippet82>
//<Snippet81>
this.Application.Workbooks.OpenXML(@"C:\Test.xml");
//</Snippet81>
//<Snippet80>
this.Application.Workbooks.OpenText(@"C:\Test.txt",
missing, 3,
Excel.XlTextParsingType.xlDelimited,
Excel.XlTextQualifier.xlTextQualifierNone,
missing, missing, missing, true, missing, missing, missing,
missing, missing, missing, missing, missing, missing);
//</Snippet80>
//<Snippet79>
this.fruitList.Range.Sort(
this.fruitList.ListColumns[1].Range, Excel.XlSortOrder.xlAscending,
this.fruitList.ListColumns[2].Range, missing, Excel.XlSortOrder.xlAscending,
missing, Excel.XlSortOrder.xlAscending,
Excel.XlYesNoGuess.xlYes, missing, missing,
Excel.XlSortOrientation.xlSortColumns,
Excel.XlSortMethod.xlPinYin,
Excel.XlSortDataOption.xlSortNormal,
Excel.XlSortDataOption.xlSortNormal,
Excel.XlSortDataOption.xlSortNormal);
//</Snippet79>
//<Snippet78>
this.Fruits.Sort(
this.Fruits.Columns[1, missing], Excel.XlSortOrder.xlAscending,
this.Fruits.Columns[2, missing], missing, Excel.XlSortOrder.xlAscending,
missing, Excel.XlSortOrder.xlAscending,
Excel.XlYesNoGuess.xlNo, missing, missing,
Excel.XlSortOrientation.xlSortColumns,
Excel.XlSortMethod.xlPinYin,
Excel.XlSortDataOption.xlSortNormal,
Excel.XlSortDataOption.xlSortNormal,
Excel.XlSortDataOption.xlSortNormal);
//</Snippet78>
//<Snippet77>
this.Application.Calculate();
//</Snippet77>
//<Snippet75>
Microsoft.Office.Tools.Excel.NamedRange NamedRange1 =
this.Controls.AddNamedRange(this.get_Range("A1"), "NamedRange1");
//</Snippet75>
//<Snippet76>
NamedRange1.Calculate();
//</Snippet76>
//<Snippet74>
Globals.ThisWorkbook.SendMail("*****@*****.**", "July Sales Figures");
//</Snippet74>
//<Snippet73>
Microsoft.Office.Core.FileDialog fd =
this.Application.get_FileDialog(Microsoft.Office.Core.MsoFileDialogType.msoFileDialogOpen);
fd.AllowMultiSelect = true;
fd.Filters.Clear();
fd.Filters.Add("Excel Files", "*.xlsx;*.xlw");
fd.Filters.Add("All Files", "*.*");
if (fd.Show() != 0)
{
fd.Execute();
}
//</Snippet73>
//<Snippet70>
System.Security.Principal.WindowsIdentity user;
user = System.Security.Principal.WindowsIdentity.GetCurrent();
//</Snippet70>
//<Snippet71>
Microsoft.Office.Tools.Excel.NamedRange userID;
userID = this.Controls.AddNamedRange(this.Range["A1"], "userID");
userID.Value2 = user.Name;
//</Snippet71>
//<Snippet67>
Excel.Range rng2 = this.Application.get_Range("A1");
rng2.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
//</Snippet67>
//<Snippet65>
Microsoft.Office.Tools.Excel.NamedRange rng =
this.Controls.AddNamedRange(this.Range["A1"], "NamedRange1");
//</Snippet65>
//<Snippet66>
rng.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
//</Snippet66>
//<Snippet54>
Microsoft.Office.Tools.Excel.NamedRange rangeStyles =
this.Controls.AddNamedRange(this.Range["A1"], "rangeStyles");
rangeStyles.Value2 = "'Style Test";
rangeStyles.Style = "NewStyle";
rangeStyles.Columns.AutoFit();
//</Snippet54>
//<Snippet53>
Excel.Style style = Globals.ThisWorkbook.Styles.Add("NewStyle");
style.Font.Name = "Verdana";
style.Font.Size = 12;
style.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
style.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Gray);
style.Interior.Pattern = Excel.XlPattern.xlPatternSolid;
//</Snippet53>
}
private void WriteToExcel(string expath)
{
try
{
Cursor.Current = Cursors.AppStarting;
xlApp = new Microsoft.Office.Interop.Excel.Application();
object misValue = System.Reflection.Missing.Value;
if (xlApp == null)
{
MessageBox.Show("Excel is not properly installed!!");
return;
}
if (File.Exists(expath))
File.Delete(expath);
xlWorkBook = xlApp.Workbooks.Add();
string[] shtnames = new string[5] { "LOG", "Testplan", "Testorder", "BOM", "Comparison" };
//xlWorkSheet = (Excel.Worksheet)xlWorkBook.Sheets.Add(misValue);
//xlWorkSheet.Name = "Sheet1";
//for (int s = 0; s < xlWorkBook.Sheets.Count; ++s)
//{
// xlWorkSheet = (Excel.Worksheet)xlWorkBook.Sheets.get_Item(s + 1);
// for (int h=0;h<shtnames.Length;++h)
// if (xlWorkSheet.Name == shtnames[h])
// xlWorkBook.Sheets[shtnames[h]].Delete();
//}
StatusL.Text = "Writing to Excel File";
for (int s = 0; s <= shtnames.Length - xlWorkBook.Sheets.Count; ++s)
{
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Sheets.Add(misValue);
}
for (int s = 0; s < shtnames.Length; ++s)
{
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Sheets.get_Item(s+1);
xlWorkSheet.Name = shtnames[s];
}
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Sheets["LOG"];
for (int i = 0; i < LOGtests.Count; i++)
{
StatusL.Text = "Extracting Log File to Excel";
for (int j = 0; j < LOGtests[i].Split('\t').Count(); ++j)
xlWorkSheet.Cells[i + 1, j + 1].Value2 = LOGtests[i].Split('\t').ElementAt(j).ToString();
// DONT CHANGE THE ORDER OF THE BELOW CONDITIONS ---------- Always Check for NOT OK First and then OK second as "OK" is also present in "NOT OK"
//if (CheckStatusArray[j].Contains("NOT OK"))
// xlWorkSheet.get_Range(ExcelAddress[i], ExcelAddress[i]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
//else if (CheckStatusArray[j].Contains("OK"))
// xlWorkSheet.get_Range(ExcelAddress[i], ExcelAddress[i]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LimeGreen);
}
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Sheets["Testplan"];
for (int i = 0; i < TPtests.Count; i++)
{
StatusL.Text = "Extracting Testplan to Excel";
for (int j = 0; j < TPtests[i].Split('\t').Count(); ++j)
xlWorkSheet.Cells[i + 1, j + 1].Value2 = TPtests[i].Split('\t').ElementAt(j).ToString();
// DONT CHANGE THE ORDER OF THE BELOW CONDITIONS ---------- Always Check for NOT OK First and then OK second as "OK" is also present in "NOT OK"
//if (CheckStatusArray[j].Contains("NOT OK"))
// xlWorkSheet.get_Range(ExcelAddress[i], ExcelAddress[i]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
//else if (CheckStatusArray[j].Contains("OK"))
// xlWorkSheet.get_Range(ExcelAddress[i], ExcelAddress[i]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LimeGreen);
}
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Sheets["Testorder"];
for (int i = 0; i < TOtests.Count; i++)
{
StatusL.Text = "Extracting Testorder to Excel";
for (int j = 0; j < TOtests[i].Split('\t').Count(); ++j)
xlWorkSheet.Cells[i + 1, j + 1].Value2 = TOtests[i].Split('\t').ElementAt(j).ToString();
// DONT CHANGE THE ORDER OF THE BELOW CONDITIONS ---------- Always Check for NOT OK First and then OK second as "OK" is also present in "NOT OK"
//if (CheckStatusArray[j].Contains("NOT OK"))
// xlWorkSheet.get_Range(ExcelAddress[i], ExcelAddress[i]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
//else if (CheckStatusArray[j].Contains("OK"))
// xlWorkSheet.get_Range(ExcelAddress[i], ExcelAddress[i]).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LimeGreen);
}
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Sheets["Comparison"];
int maxlength = Math.Max(TOtests.Count, Math.Max(TPtests.Count, Math.Max(BOMtests.Count, LOGtests.Count)));
List<string> comptests = new List<string>();
try
{
//bool cond1 = (i >= TOtests.Count) || (TOtests.Count == 0);
//bool cond2 = (i >= TPtests.Count) || (TPtests.Count == 0);
//bool cond3 = (i >= BOMtests.Count) || (BOMtests.Count == 0);
//bool cond4 = (i >= LOGtests.Count) || (LOGtests.Count == 0);
//if (cond1 && !cond2 && !cond3 && !cond4)
// comptests.Add("\t" + "\t" + TPtests[i].ElementAt(0) + "\t" + BOMtests[i].ElementAt(0) + "\t" + LOGtests[i].ElementAt(0));
//else if (!cond1 && cond2 && !cond3 && !cond4)
// comptests.Add(TOtests[i].ElementAt(2) + "\t" + "\t" + "\t" + BOMtests[i].ElementAt(0) + "\t" + LOGtests[i].ElementAt(0));
//else if (!cond1 && !cond2 && cond3 && !cond4)
// comptests.Add(TOtests[i].ElementAt(2) + "\t" + TPtests[i].ElementAt(0) + "\t" + "\t" + "\t" + LOGtests[i].ElementAt(0));
//else if (!cond1 && !cond2 && !cond3 && cond4)
// comptests.Add(TOtests[i].ElementAt(2) + "\t" + TPtests[i].ElementAt(0) + "\t" + BOMtests[i].ElementAt(0) + "\t" + "\t");
//else
// comptests.Add(TOtests[i].ElementAt(2) + "\t" + TPtests[i].ElementAt(0) + "\t" + BOMtests[i].ElementAt(0) + "\t" + LOGtests[i].ElementAt(0));
List<string> BOMt = new List<string>();
List<string> TOt = new List<string>();
List<string> TPt = new List<string>();
List<string> LOGt = new List<string>();
List<string> TOCOM1 = new List<string>();
List<string> TOCOM2 = new List<string>();
List<string> TPCOM1 = new List<string>();
List<string> TPCOM2 = new List<string>();
StatusL.Text = "Preparing data to be compared";
for (int i = 0; i < TOtests.Count; i++)
{
//for (int j = 0; j < comptests[i].Split('\t').Count(); ++j)
TOt.Add(TOtests[i].Split('\t').ElementAt(2).ToString());
TOt.Sort();
TOt = TOt.Distinct().ToList();
TOCOM1.Add(TOtests[i].Split('\t').ElementAt(4).ToString());
TOCOM1.Sort();
TOCOM1 = TOCOM1.Distinct().ToList();
TOCOM2.Add(TOtests[i].Split('\t').ElementAt(5).ToString());
TOCOM2.Sort();
TOCOM2 = TOCOM2.Distinct().ToList();
}
for (int i = 0; i < TPtests.Count; i++)
{
// for (int j = 0; j < comptests[i].Split('\t').Count(); ++j)
TPt.Add(TPtests[i].Split('\t').ElementAt(0).ToString());
TPt.Sort();
TPt = TPt.Distinct().ToList();
TPCOM1.Add(TPtests[i].Split('\t').ElementAt(1).ToString());
TPCOM1.Sort();
TPCOM1 = TPCOM1.Distinct().ToList();
TPCOM2.Add(TPtests[i].Split('\t').ElementAt(2).ToString());
TPCOM2.Sort();
TPCOM2 = TPCOM2.Distinct().ToList();
}
for (int i = 0; i < BOMtests.Count; i++)
{
//for (int j = 0; j < comptests[i].Split('\t').Count(); ++j)
BOMt.Add(BOMtests[i]);
BOMt.Sort();
BOMt = BOMt.Distinct().ToList();
}
for (int i = 0; i < LOGtests.Count; i++)
{
//for (int j = 0; j < comptests[i].Split('\t').Count(); ++j)
LOGt.Add(LOGtests[i].Split('\t').ElementAt(0).ToString());
LOGt.Sort();
LOGt = LOGt.Distinct().ToList();
}
//for (int i = 0; i < TOtests.Count; i++)
//{
// // for (int j = 0; j < comptests[i].Split('\t').Count(); ++j)
// TOCOM1.Add(TOtests[i].Split('\t').ElementAt(4).ToString());
// TOCOM1.Sort();
// TOCOM1 = TOCOM1.Distinct().ToList();
//}
//for (int i = 0; i < TOtests.Count; i++)
//{
// // for (int j = 0; j < comptests[i].Split('\t').Count(); ++j)
// TOCOM2.Add(TOtests[i].Split('\t').ElementAt(5).ToString());
// TOCOM2.Sort();
// TOCOM2 = TOCOM2.Distinct().ToList();
//}
//for (int i = 0; i < TPtests.Count; i++)
//{
// //for (int j = 0; j < comptests[i].Split('\t').Count(); ++j)
// TPCOM1.Add(TPt[i].Split('\t').ElementAt(1).ToString());
// TPCOM1.Sort();
// TPCOM1 = TPCOM1.Distinct().ToList();
//}
//for (int i = 0; i < TPtests.Count; i++)
//{
// //for (int j = 0; j < comptests[i].Split('\t').Count(); ++j)
// TPCOM2.Add(TPtests[i].Split('\t').ElementAt(2).ToString());
// TPCOM2.Sort();
// TPCOM2 = TPCOM2.Distinct().ToList();
//}
for (int f = 0; f < maxlength; ++f)
{
StatusL.Text = "Comparing data";
maxlength = Math.Max(TOt.Count, Math.Max(TPt.Count, Math.Max(BOMt.Count, LOGt.Count)));
string TO = "", TP = "", BOM = "", LOG = "", resstr = "";
if (f >= TOt.Count)
TO = "";
else
TO = TOt[f];
if (f >= TPt.Count)
TP = "";
else
TP = TPt[f];
if (f >= BOMt.Count)
BOM = "";
else
BOM = BOMt[f];
if (f >= LOGt.Count)
LOG = "";
else
LOG = LOGt[f];
resstr = loweststring(new string[4] { TO, TP, BOM, LOG });
if (f >= TOt.Count)
{
TOt.Insert(f, "");
TOCOM1.Insert(f, "");
TOCOM2.Insert(f, "");
}
else if (string.Compare(resstr, TOt[f], true) == -1)
{
TOt.Insert(f, "");
TOCOM1.Insert(f, "");
TOCOM2.Insert(f, "");
}
else if (string.Compare(resstr, TOt[f], true) == 1)
{
TOt.Insert(f, "");
TPt.Insert(f, "");
BOMt.Insert(f, "");
LOGt.Insert(f, "");
TOCOM1.Insert(f, "");
TOCOM2.Insert(f, "");
TPCOM1.Insert(f, "");
TPCOM2.Insert(f, "");
}
if (f >= TPt.Count)
{
TPt.Insert(f, "");
TPCOM1.Insert(f, "");
TPCOM2.Insert(f, "");
}
else if (string.Compare(resstr, TPt[f], true) == -1)
{
TPt.Insert(f, "");
TPCOM1.Insert(f, "");
TPCOM2.Insert(f, "");
}
else if (string.Compare(resstr, TPt[f], true) == 1)
{
TOt.Insert(f, "");
TPt.Insert(f, "");
BOMt.Insert(f, "");
LOGt.Insert(f, "");
TOCOM1.Insert(f, "");
TOCOM2.Insert(f, "");
TPCOM1.Insert(f, "");
TPCOM2.Insert(f, "");
}
if (f >= BOMt.Count)
BOMt.Insert(f, "");
else if (string.Compare(resstr, BOMt[f], true) == -1)
{
BOMt.Insert(f, "");
}
else if (string.Compare(resstr, BOMt[f], true) == 1)
{
TOt.Insert(f, "");
TPt.Insert(f, "");
BOMt.Insert(f, "");
LOGt.Insert(f, "");
TOCOM1.Insert(f, "");
TOCOM2.Insert(f, "");
TPCOM1.Insert(f, "");
TPCOM2.Insert(f, "");
}
if (f >= LOGt.Count)
LOGt.Insert(f, "");
else if (string.Compare(resstr, LOGt[f], true) == -1)
{
LOGt.Insert(f, "");
}
else if (string.Compare(resstr, LOGt[f], true) == 1)
{
TOt.Insert(f, "");
TPt.Insert(f, "");
BOMt.Insert(f, "");
LOGt.Insert(f, "");
TOCOM1.Insert(f, "");
TOCOM2.Insert(f, "");
TPCOM1.Insert(f, "");
TPCOM2.Insert(f, "");
}
}
StatusL.Text = "Writing Compared data to excel";
xlWorkSheet.Cells[1, 1].Value2 = "Testorder";
for (int i = 0; i < TOt.Count; i++)
{
//for (int j = 0; j < comptests[i].Split('\t').Count(); ++j)
xlWorkSheet.Cells[i + 2, 1].Value2 = TOt[i];
}
xlWorkSheet.Cells[1, 2].Value2 = "Testplan";
for (int i = 0; i < TPt.Count; i++)
{
// for (int j = 0; j < comptests[i].Split('\t').Count(); ++j)
xlWorkSheet.Cells[i + 2, 2].Value2 = TPt[i];
}
xlWorkSheet.Cells[1, 3].Value2 = "BOM";
for (int i = 0; i < BOMt.Count; i++)
{
//for (int j = 0; j < comptests[i].Split('\t').Count(); ++j)
xlWorkSheet.Cells[i + 2, 3].Value2 = BOMt[i];
}
xlWorkSheet.Cells[1, 4].Value2 = "LOG";
for (int i = 0; i < LOGt.Count; i++)
{
//for (int j = 0; j < comptests[i].Split('\t').Count(); ++j)
xlWorkSheet.Cells[i + 2, 4].Value2 = LOGt[i];
}
xlWorkSheet.Cells[1, 5].Value2 = "Testorder Comments";
for (int i = 0; i < TOCOM1.Count; i++)
{
// for (int j = 0; j < comptests[i].Split('\t').Count(); ++j)
xlWorkSheet.Cells[i + 2, 5].Value2 = TOCOM1[i] + " <|> " + TOCOM2[i];
}
xlWorkSheet.Cells[1, 6].Value2 = "Testplan Comments";
for (int i = 0; i < TPCOM1.Count; i++)
{
//for (int j = 0; j < comptests[i].Split('\t').Count(); ++j)
xlWorkSheet.Cells[i + 2, 6].Value2 = TPCOM1[i] + " <|> " + TPCOM2[i];
}
StatusL.Text = "Comparison finished and results written to Excel";
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
Excel.Style style = xlWorkBook.Styles.Add("myStyle");
style.Font.Name = "Arial";
style.Font.Bold = true;
style.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
style.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
style.Font.Size = 12;
style.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);
style.Interior.Pattern = Excel.XlPattern.xlPatternSolid;
////xlWorkBook = xlApp.Workbooks.Add(misValue);
////foreach(string addSTR in ExcelAddress)
//for (int i = 0, j = 0; i < ExcelAddress.Count; i++, j++)
//{
// if (CheckStatusArray[j].Contains("NOT OK"))
// {
// style.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
// xlWorkSheet.Range[ExcelAddress[i]].Style = "myStyle";
// }
// else if (CheckStatusArray[j] == "OK")
// {
// style.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LimeGreen);
// xlWorkSheet.Range[ExcelAddress[i]].Style = "myStyle";
// }
// else
// {
// style.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow);
// xlWorkSheet.Range[ExcelAddress[i]].Style = "myStyle";
// }
//}
if (File.Exists(expath))
xlWorkBook.Save();
else
xlWorkBook.SaveAs(expath);
//StatusCB.Items.Add(DateTime.Now.ToLongTimeString() + " - Excel Report Updated Successfully!");
//StatusCB.BackColor = Color.LimeGreen;
Cursor.Current = Cursors.Default;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
//StatusCB.Items.Add(DateTime.Now.ToLongTimeString() +" - ERROR. Click \"Show Detail\" to view error.");
//StatusCB.BackColor = Color.Red;
//MessageBox.Show(ex.Message + Environment.NewLine + Environment.NewLine + ex.StackTrace, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
xlWorkBook.Close(false);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
}
}
/// <summary>
/// Adds the complete Lineup to the current sheet.
/// </summary>
/// <param name="starterHost">Starter hosts</param>
/// <param name="starterGuest1">Guest 1 to the starters hosts</param>
/// <param name="starterGuest2">Guest 2 to the starters hosts</param>
/// <param name="mainHost">Main Course Hosts</param>
/// <param name="mainGuest1">Guest 1 to the main course hosts</param>
/// <param name="mainGuest2">Guest 2 to the main course hosts</param>
/// <param name="desertHost">Desert hosts</param>
/// <param name="desertGuest1">Guest 1 to the desert hosts</param>
/// <param name="desertGuest2">Guest 2 to the desert hosts</param>
public void AddFoodRelayLineUp(
List <Participant> starterHost,
List <Participant> starterGuest1,
List <Participant> starterGuest2,
List <Participant> mainHost,
List <Participant> mainGuest1,
List <Participant> mainGuest2,
List <Participant> desertHost,
List <Participant> desertGuest1,
List <Participant> desertGuest2)
{
void addParticipantRange(List <Participant> participants, Excel.Range range)
{
int index = 0;
foreach (Participant participant in participants)
{
index++;
range.Cells[index, 1] = participant.Name;
}
}
void addSummaryRange(
Excel.Style style,
List <Participant> hosts,
List <Participant> guests1,
List <Participant> guests2,
Excel.Range range)
{
range.Cells[1, 1] = "Värd";
range.Cells[1, 2] = "Gäst 1";
range.Cells[1, 3] = "Gäst 2";
range.Range["A1", "C1"].Style = style;
int index = 1;
foreach (Participant host in hosts)
{
index++;
range.Cells[index, 1] = host.Name;
range.Cells[index, 2] = guests1[index - 2].Name;
range.Cells[index, 3] = guests2[index - 2].Name;
}
}
// Set up styles.
Excel.Style h1 = WorkBook.Styles.Add("h1");
h1.Font.Size = 15;
h1.Font.Bold = true;
h1.Font.ColorIndex = 5; // https://msdn.microsoft.com/en-us/library/cc296089(v=office.12).aspx
Excel.Style h1Center = WorkBook.Styles.Add("h1Center");
h1Center.Font.Size = 15;
h1Center.Font.Bold = true;
h1Center.Font.ColorIndex = 5;
h1Center.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
Excel.Style h2 = WorkBook.Styles.Add("h2");
h2.Font.Size = 13;
h2.Font.Bold = true;
h2.Font.ColorIndex = 32;
Excel.Style h2center = WorkBook.Styles.Add("h2Center");
h2center.Font.Size = 13;
h2center.Font.Bold = true;
h2center.Font.ColorIndex = 32;
h2center.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
// Add summary to column 1
// Add detailed content to column 3-5
// ==================================
// Headers
Excel.Range heading = WorkSheet.Cells[1, 1];
heading.Cells[1, 1] = "Sammanfattning";
heading.Style = h1;
// Starters
Excel.Range starterHeader = WorkSheet.Cells[2, 1];
starterHeader.Cells[1, 1] = "Värd Förrätt:";
starterHeader.Style = h2;
addParticipantRange(
starterHost, WorkSheet.Cells.Range[
string.Format("A3"),
string.Format("A{0}", starterHost.Count + 3)
]
);
Excel.Range headingStarterMerged = WorkSheet.Cells.Range["C1", "E1"];
headingStarterMerged.Cells[1, 1] = "Förrätt";
headingStarterMerged.Style = h1Center;
headingStarterMerged.MergeCells = true;
addSummaryRange(
h2center,
starterHost,
starterGuest1,
starterGuest2,
WorkSheet.Cells.Range[
string.Format("C2"),
string.Format("E{0}", starterHost.Count + 3)
]
);
// Main Course
Excel.Range mainHeader = WorkSheet.Cells[mainHost.Count + 5, 1];
mainHeader.Cells[1, 1] = "Värd Huvudrätt:";
mainHeader.Style = h2;
addParticipantRange(mainHost, WorkSheet.Cells.Range[
string.Format("A{0}", mainHost.Count + 6),
string.Format("A{0}", mainHost.Count * 2 + 6)
]);
Excel.Range headingMainMerged = WorkSheet.Cells.Range[
string.Format("C{0}", mainHost.Count + 4),
string.Format("E{0}", mainHost.Count + 4)
];
headingMainMerged.Cells[1, 1] = "Huvudrätt";
headingMainMerged.Style = h1Center;
headingMainMerged.MergeCells = true;
addSummaryRange(
h2center,
mainHost,
mainGuest1,
mainGuest2,
WorkSheet.Cells.Range[
string.Format("C{0}", mainHost.Count + 5),
string.Format("E{0}", mainHost.Count * 2 + 5)
]
);
// Desert
Excel.Range desertHeader = WorkSheet.Cells[desertHost.Count * 2 + 8, 1];
desertHeader.Cells[1, 1] = "Värd Efterrätt:";
desertHeader.Style = h2;
addParticipantRange(desertHost, WorkSheet.Cells.Range[
string.Format("A{0}", desertHost.Count * 2 + 9),
string.Format("A{0}", desertHost.Count * 3 + 9)
]);
Excel.Range headingDesertMerged = WorkSheet.Cells.Range[
string.Format("C{0}", desertHost.Count * 2 + 7),
string.Format("E{0}", desertHost.Count * 2 + 7)
];
headingDesertMerged.Cells[1, 1] = "Huvudrätt";
headingDesertMerged.Style = h1Center;
headingDesertMerged.MergeCells = true;
addSummaryRange(
h2center,
desertHost,
desertGuest1,
desertGuest2,
WorkSheet.Cells.Range[
string.Format("C{0}", desertHost.Count * 2 + 8),
string.Format("E{0}", desertHost.Count * 3 + 8)
]
);
WorkSheet.Columns.AutoFit();
}
public virtual void InitFormatCells()
{
Excel.Style style = StyleHelper.getDocGenMainStyle();
sheet.Cells.Style = style;
}
private void bunifuImageButton1_Click(object sender, EventArgs e)
{
Excel.Application xlApp = new Excel.Application();
if (xlApp == null)
{
MessageBox.Show("Excel no se encuentra instalado en tu sistema. No se puede continuar", "Por favor instala Excel para continuar");
return;
}
Excel.Workbook wb = xlApp.Workbooks.Add();
Excel.Worksheet ws1 = (Excel.Worksheet)wb.Worksheets[1];
ws1.Name = "Reporte de toma de refrigerios";
Excel.Worksheet ws2 = (Excel.Worksheet)wb.Worksheets.Add(ws1);
ws2.Name = "Reporte de asistencia";
Excel.Worksheet ws3 = (Excel.Worksheet)wb.Worksheets.Add(ws2);
ws3.Name = "Reporte de asistentes";
#region Creacion de la hoja de asistentes
ws3.Cells[1][1] = "ID";
ws3.Cells[2][1] = "Nombre";
ws3.Cells[3][1] = "Documento";
DataTable dt1 = selectQuery("SELECT * FROM " + tabla_asistentes);
int i = 1;
foreach (DataRow row in dt1.Rows)
{
i++;
ws3.Cells[1][i] = row[0];
ws3.Cells[2][i] = row[1];
ws3.Cells[3][i] = row[2];
}
#endregion
#region Creacion de la hoja de asistencia al evento
ws2.Cells[1][1] = "Nombre del Asistente";
ws2.Cells[2][1] = "Documento";
i = 2;
DataTable dt2 = selectQuery("SELECT * FROM " + tabla_asistencia_nombre);
DataTable dt3 = selectQuery("SELECT * FROM " + tabla_asistencia);
foreach (DataRow row in dt2.Rows)
{
i++;
ws2.Cells[i][1] = row[0];
}
i = 1;
foreach (DataRow row1 in dt1.Rows)
{
i++;
int id = int.Parse(row1[0].ToString());
ws2.Cells[1][i] = row1[1];
ws2.Cells[2][i] = row1[2];
foreach (DataRow row2 in dt3.Rows)
{
string idasistencia = row2[0].ToString();
if (int.Parse(row2[1].ToString()) == id)
{
for (int k = 0; k <= dt2.Rows.Count; k++)
{
string idcelda = ws2.Cells[k + 2][1].Text;
if (idcelda == idasistencia)
{
ws2.Cells[k + 2][i] = "X";
}
}
}
}
}
i = 2;
foreach (DataRow row in dt2.Rows)
{
i++;
ws2.Cells[i][1] = row[1];
}
#endregion
#region Creacion de la hoja de refrigerios recibidos
ws1.Cells[1][1] = "Nombre del Asistente";
ws1.Cells[2][1] = "Documento";
i = 2;
DataTable dt4 = selectQuery("SELECT * FROM " + tabla_horas_refrigerio);
DataTable dt5 = selectQuery("SELECT * FROM " + tabla_refrigerio_toma);
foreach (DataRow row in dt4.Rows)
{
i++;
ws1.Cells[i][1] = row[0];
}
i = 1;
foreach (DataRow row1 in dt1.Rows)
{
i++;
int id = int.Parse(row1[0].ToString());
ws1.Cells[1][i] = row1[1];
ws1.Cells[2][i] = row1[2];
foreach (DataRow row2 in dt5.Rows)
{
string idasistencia = row2[0].ToString();
if (int.Parse(row2[1].ToString()) == id)
{
for (int k = 0; k <= dt4.Rows.Count; k++)
{
string idcelda = ws1.Cells[k + 2][1].Text;
if (idcelda == idasistencia)
{
ws1.Cells[k + 2][i] = "X";
}
}
}
}
}
i = 2;
foreach (DataRow row in dt4.Rows)
{
i++;
ws1.Cells[i][1] = row[1];
}
#endregion
#region Adicion de estilo a las hojas de Excel
Excel.Range rangeWs1 = ws1.UsedRange;
Excel.Range rangeWs2 = ws2.UsedRange;
Excel.Range rangeWs3 = ws3.UsedRange;
Excel.Style textStyle = wb.Styles.Add("NewStyle");
textStyle.Font.Name = "Segoe UI";
textStyle.Font.Size = 14;
textStyle.Font.Color = System.Drawing.ColorTranslator.FromHtml("#0D6076");
textStyle.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
textStyle.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
rangeWs1.Style = textStyle;
rangeWs2.Style = textStyle;
rangeWs3.Style = textStyle;
ws1.Columns.AutoFit();
ws2.Columns.AutoFit();
ws3.Columns.AutoFit();
rangeWs1.Cells.Style.Font.Size = 12;
rangeWs2.Cells.Style.Font.Size = 12;
rangeWs3.Cells.Style.Font.Size = 12;
ws1.Rows.RowHeight = 22;
ws2.Rows.RowHeight = 22;
ws3.Rows.RowHeight = 22;
#endregion
xlApp.Visible = true;
}
}//two argument constructor
public void SaveExcelDocument(Dictionary <string, Dictionary <string, Stock> > map, bool secondScreener)
{
ChangeProgress(0, "Initializing...", (map.Count + map.Values.Count));
Excel.Application app = new Excel.Application();
if (app == null)
{
throw new Exception("Excel is not properly installed!");
}
ChangeProgress(1, "Creating Workbook...");
Excel.Workbook workbook = app.Workbooks.Add();
ChangeProgress(1, "Creating Worksheet...");
Excel.Worksheet worksheet = worksheet = (Excel.Worksheet)workbook.Worksheets[1];
Excel.Style style = workbook.Styles.Add("style");
style.Font.Name = "Arial";
style.Font.Size = 10;
//Sets the column width of each column and the header text for each column
ChangeProgress(1, "Writing header rows...");
var headerText = GetHeaderText(secondScreener);
Excel.Range formatRange = GetRange(worksheet, 1, 1, 1, 10);
formatRange.EntireRow.RowHeight = 51.75;
SetColumnWidth(ref worksheet, 1, 1, 1, 1, 6.14);
SetColumnWidth(ref worksheet, 1, 2, 1, 2, 36.71);
MergeCells(ref worksheet, 1, 1, 1, 2);
worksheet.Cells[1, 3] = headerText[0];
SetColumnWidth(ref worksheet, 1, 3, 1, 3, 11.29);
worksheet.Cells[1, 4] = headerText[1];
SetColumnWidth(ref worksheet, 1, 4, 1, 4, 11.29);
worksheet.Cells[1, 5] = headerText[2];
SetColumnWidth(ref worksheet, 1, 5, 1, 5, 12.14);
worksheet.Cells[1, 6] = headerText[3];
SetColumnWidth(ref worksheet, 1, 6, 1, 6, 16.57);
worksheet.Cells[1, 7] = headerText[4];
SetColumnWidth(ref worksheet, 1, 7, 1, 7, 14.14);
worksheet.Cells[1, 8] = headerText[5];
SetColumnWidth(ref worksheet, 1, 8, 1, 8, 11.14);
worksheet.Cells[1, 9] = headerText[6];
SetColumnWidth(ref worksheet, 1, 9, 1, 9, 12.71);
worksheet.Cells[1, 10] = headerText[7];
SetColumnWidth(ref worksheet, 1, 10, 1, 10, 11.71);
worksheet.Cells[1, 11] = headerText[8];
SetColumnWidth(ref worksheet, 1, 11, 1, 11, 12.86);
if (secondScreener)
{
worksheet.Cells[1, 12] = headerText[9];
SetColumnWidth(ref worksheet, 1, 12, 1, 12, 12.86);
worksheet.Cells[1, 13] = headerText[9];
SetColumnWidth(ref worksheet, 1, 12, 1, 12, 12.86);
}
//formatRange = GetRange(worksheet, 2, 7, 2, 8);
//formatRange.EntireColumn.NumberFormat = "#.#";
formatRange = GetRange(worksheet, 1, 2, 1, 11);
formatRange.EntireColumn.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
int i = 2;//indicates the current row to begin on. Excel starts indexing at 1
foreach (var sector in frmScreener.SortSectorKeys(map.Keys))
{
if (!secondScreener)
{
ChangeProgress(1, String.Format("Writing and formatting {0} header", sector));
/*Sets the background color and text alignment of the Sector header row. Merges the first two columns
* together and the last 8 together. Sets the header text for the Sector and increments the current row*/
formatRange = GetRange(worksheet, i, 1, i, 11);
formatRange.EntireRow.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Silver);
formatRange.Cells.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
MergeCells(ref worksheet, i, 1, i, 2);
MergeCells(ref worksheet, i, 3, i, 11);
worksheet.Cells[i, 1] = String.Format("{0} {1}", DateTime.Today.ToShortDateString(), sector);
i++;
}//end if
/*Gets the ordered Stock objects for the Sector and loops through the objects. Gets the attributes for the
* current object to loop through. Loops through each attribute and sets the cell text to the attribute. If
* the current row is odd, changes the background color for easier reading. Increases the current row*/
IOrderedEnumerable <Stock> stocks = frmScreener.SortSectorDictionary(map[sector], secondScreener);
int k = 0;
foreach (var s in stocks)
{
ChangeProgress(1, String.Format("Writing {0} information", s.SymbolValue));
if (k % 2 != 0)
{
formatRange = GetRange(worksheet, i, 1, i, 11);
formatRange.EntireRow.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Gainsboro);
}
var colors = (s.TotalScoreValue >= 18 ? s.GetFormattingColors(secondScreener) : null);
var attributes = s.GetAttributesEnumerable(secondScreener);
for (int j = 1; j <= attributes.Count(); j++)
{
if (colors != null && 3 <= j && j <= (secondScreener ? 11 : 10))
{
formatRange = GetRange(worksheet, i, j, i, j);
formatRange.Interior.Color = System.Drawing.ColorTranslator.ToOle(colors[j - 3]);
}
var val = attributes.ElementAt(j - 1).ToString();
if (val == Double.MinValue.ToString())
{
val = "NA";
}
worksheet.Cells[i, j] = val;
}//end for
if (secondScreener)
{
worksheet.Cells[i, 13] = sector;
}
k++;
i++;
} //end nested foreach
} //end foreach
ChangeProgress(1, "Formatting borders...");
//Add a border around every cell on in the table
formatRange = worksheet.UsedRange;
formatRange.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
formatRange.Borders.Weight = 2d;
ChangeProgress(1, "Adding Earnings Date, Zacks score explaination...");
//Merge rows at the botom of the document and add the scoring explanation
for (int j = 1; j < (!secondScreener ? 6 : 5); j++)
{
MergeCells(ref worksheet, i, 1, i, 11);
worksheet.Cells[i, 1] = GetScoreExplanation(j, secondScreener);
i++;
}
ChangeProgress(1, "Finializing...");
//Save the document and close the workbook and application
File.Delete(Path.Combine(filePath, fileName));
workbook.SaveAs(Path.Combine(filePath, fileName));
workbook.Close();
app.Quit();
}//end