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