/// <summary> /// make the range of rows bold /// </summary> /// <param name="row1"></param> /// <param name="row2"></param> private void BoldRow(string row1, string row2) { _excelRange = _excelSheet.get_Range(row1, row2); _excelFont = _excelRange.Font; _excelFont.Bold = true; }
/// <summary> /// Extension method to write list data to Microsoft.Office.Interop.Excel File. /// </summary> /// <typeparam name="T">Ganeric list</typeparam> /// <param name="list"></param> /// <param name="PathToSave">Path to save file.</param> public static void ToExcel <T>(this List <T> list, string PathToSave) { #region Declarations if (string.IsNullOrEmpty(PathToSave)) { throw new Exception("Invalid file path."); } else if (PathToSave.ToLower().Contains("") == false) { throw new Exception("Invalid file path."); } if (list == null) { throw new Exception("No data to export."); } Microsoft.Office.Interop.Excel.Application excelApp = null; Microsoft.Office.Interop.Excel.Workbooks books = null; Microsoft.Office.Interop.Excel._Workbook book = null; Microsoft.Office.Interop.Excel.Sheets sheets = null; Microsoft.Office.Interop.Excel._Worksheet sheet = null; Microsoft.Office.Interop.Excel.Range range = null; Microsoft.Office.Interop.Excel.Font font = null; // Optional argument variable object optionalValue = Missing.Value; string strHeaderStart = "A2"; string strDataStart = "A3"; #endregion #region Processing try { #region Init Microsoft.Office.Interop.Excel app. excelApp = new Microsoft.Office.Interop.Excel.Application(); books = (Microsoft.Office.Interop.Excel.Workbooks)excelApp.Workbooks; book = (Microsoft.Office.Interop.Excel._Workbook)(books.Add(optionalValue)); sheets = (Microsoft.Office.Interop.Excel.Sheets)book.Worksheets; sheet = (Microsoft.Office.Interop.Excel._Worksheet)(sheets.get_Item(1)); #endregion #region Creating Header Dictionary <string, string> objHeaders = new Dictionary <string, string>(); PropertyInfo[] headerInfo = typeof(T).GetProperties(); foreach (var property in headerInfo) { var attribute = property.GetCustomAttributes(typeof(DisplayNameAttribute), false) .Cast <DisplayNameAttribute>().FirstOrDefault(); objHeaders.Add(property.Name, attribute == null ? property.Name : attribute.DisplayName); } range = sheet.get_Range(strHeaderStart, optionalValue); range = range.get_Resize(1, objHeaders.Count); range.set_Value(optionalValue, objHeaders.Values.ToArray()); range.BorderAround(Type.Missing, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, Type.Missing); font = range.Font; font.Bold = true; range.Interior.Color = Color.LightGray.ToArgb(); #endregion #region Writing data to cell int count = list.Count; object[,] objData = new object[count, objHeaders.Count]; for (int j = 0; j < count; j++) { var item = list[j]; int i = 0; foreach (KeyValuePair <string, string> entry in objHeaders) { var y = typeof(T).InvokeMember(entry.Key.ToString(), BindingFlags.GetProperty, null, item, null); objData[j, i++] = (y == null) ? "" : y.ToString(); } } range = sheet.get_Range(strDataStart, optionalValue); range = range.get_Resize(count, objHeaders.Count); range.set_Value(optionalValue, objData); range.BorderAround(Type.Missing, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, Type.Missing); range = sheet.get_Range(strHeaderStart, optionalValue); range = range.get_Resize(count + 1, objHeaders.Count); range.Columns.AutoFit(); #endregion #region Saving data and Opening Microsoft.Office.Interop.Excel file. if (string.IsNullOrEmpty(PathToSave) == false) { book.SaveCopyAs(PathToSave); } excelApp.Visible = true; #endregion #region Release objects try { if (sheet != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet); } sheet = null; if (sheets != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(sheets); } sheets = null; if (book != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(book); } book = null; if (books != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(books); } books = null; if (excelApp != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp); } excelApp = null; } catch (Exception ex) { sheet = null; sheets = null; book = null; books = null; excelApp = null; } finally { GC.Collect(); } #endregion } catch (Exception ex) { throw ex; } #endregion }
/// <summary> /// Set Header style as bold /// </summary> private void SetHeaderStyle() { _font = _range.Font; _font.Bold = true; }
static int Main(string[] args) { if (args.Length < 2) { return(0); } // EXCELファイルパス string filePath = args[0]; // EXCELファイルフルパス string fullPath = System.IO.Path.GetFullPath(filePath); // ファイルが無い場合終了 if (!System.IO.File.Exists(fullPath)) { return(0); } // シート名 string strWorksheetName = ""; // フォント名 string fontName = args[1]; //末端の改行文字を取り除く fontName.Trim(); Microsoft.Office.Interop.Excel.Application xlApp = null; Microsoft.Office.Interop.Excel.Workbooks xlBooks = null; Microsoft.Office.Interop.Excel.Workbook xlBook = null; Microsoft.Office.Interop.Excel.Sheets xlSheets = null; Microsoft.Office.Interop.Excel.Worksheet xlSheet = null; Microsoft.Office.Interop.Excel.Range xlRange = null; Microsoft.Office.Interop.Excel.Font xlFont = null; Microsoft.Office.Interop.Excel.Shapes xlShapes = null; Microsoft.Office.Interop.Excel.Shape xlShape = null; Microsoft.Office.Interop.Excel.TextFrame2 xlTextFrame2 = null; Microsoft.Office.Core.TextRange2 xlTextRange2 = null; Microsoft.Office.Core.Font2 xlFont2 = null; try { // EXCEL起動 xlApp = new Microsoft.Office.Interop.Excel.Application(); // EXCELは非表示にする xlApp.Visible = false; // 保存確認なしにする xlApp.DisplayAlerts = false; // Workbook開く xlBooks = xlApp.Workbooks; xlBook = xlBooks.Open(fullPath); // Worksheet開く xlSheets = xlBook.Sheets; for (int ii = 1; ii <= xlSheets.Count; ++ii) { xlSheet = xlSheets.Item[ii]; strWorksheetName = xlSheet.Name; // セルのフォントを変更する xlRange = xlSheet.UsedRange; xlFont = xlRange.Font; xlFont.Name = fontName; if (xlFont != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(xlFont); xlFont = null; } if (xlRange != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(xlRange); xlRange = null; } // 図形のフォントを変更する xlShapes = xlSheet.Shapes; for (int jj = 1; jj <= xlShapes.Count; ++jj) { xlShape = xlShapes.Item(jj); xlTextFrame2 = xlShape.TextFrame2; if (xlTextFrame2.HasText == Microsoft.Office.Core.MsoTriState.msoTrue) { xlTextRange2 = xlTextFrame2.TextRange; xlFont2 = xlTextRange2.Font; // 右から左へ記述するフォントの設定(例:アラビア語) xlFont2.NameComplexScript = fontName; // 全角フォントの設定(例:日本語) xlFont2.NameFarEast = fontName; // 半角フォントの設定(例:英語) xlFont2.Name = fontName; xlRange = xlShape.TopLeftCell; System.Console.WriteLine(xlRange.Address + " [" + xlShape.Name + "]" + "のフォントを変更しました。"); if (xlRange != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(xlRange); xlRange = null; } if (xlFont2 != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(xlFont2); xlFont2 = null; } if (xlTextRange2 != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(xlTextRange2); xlTextRange2 = null; } } if (xlTextFrame2 != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(xlTextFrame2); xlTextFrame2 = null; } if (xlShape != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(xlShape); xlShape = null; } } if (xlShapes != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(xlShapes); xlShapes = null; } if (xlSheet != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(xlSheet); xlSheet = null; } System.Console.WriteLine("処理が終了しました。シート名:" + strWorksheetName); } if (xlSheets != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(xlSheets); xlSheets = null; } // Workbook保存 xlBook.Save(); System.Console.WriteLine("処理が終了しました。ファイル名:" + xlBook.Name); } catch { System.Console.WriteLine("ランタイムエラーが発生しました。シート名:" + strWorksheetName); } finally { if (xlFont2 != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(xlFont2); xlFont2 = null; } if (xlTextRange2 != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(xlTextRange2); xlTextRange2 = null; } if (xlTextFrame2 != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(xlTextFrame2); xlTextFrame2 = null; } if (xlShape != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(xlShape); xlShape = null; } if (xlShapes != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(xlShapes); xlShapes = null; } if (xlFont != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(xlFont); xlFont = null; } if (xlRange != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(xlRange); xlRange = null; } if (xlSheet != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(xlSheet); xlSheet = null; } if (xlSheets != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(xlSheets); xlSheets = null; } if (xlBook != null) { xlBook.Close(false); System.Runtime.InteropServices.Marshal.ReleaseComObject(xlBook); xlBook = null; } if (xlBooks != null) { System.Runtime.InteropServices.Marshal.ReleaseComObject(xlBooks); xlBooks = null; } if (xlApp != null) { xlApp.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp); xlApp = null; } } return(0); }
public BuildDueContractFiles(DataTable table, string currentPath) { Console.WriteLine("Building Excel files, please wait..."); BranchData branchData = new BranchData(); Dictionary <string, string> branchManagers = branchData.getBranchManagers(); fileEmailCollection = new Dictionary <string, string>(); Utils utils = new Utils(); for (int count = 0; count < branchManagers.Count; count++) { //get branch manager details for branch manager collection var element = branchManagers.ElementAt(count); var branchCodeKey = (element.Key).ToUpper().ToString(); var branchManagerEmail = element.Value; var fileName = "bm_" + utils.createFilename(); //create temp branch data collection tempBranchData = new DataTable(); tempBranchData.Columns.Add("Branch", typeof(string)); tempBranchData.Columns.Add("Product", typeof(string)); tempBranchData.Columns.Add("Contract No", typeof(string)); tempBranchData.Columns.Add("Rental Amount", typeof(string)); tempBranchData.Columns.Add("Rental No", typeof(string)); tempBranchData.Columns.Add("Contract peroid", typeof(string)); tempBranchData.Columns.Add("Debtor balance", typeof(string)); tempBranchData.Columns.Add("Default Interest", typeof(string)); tempBranchData.Columns.Add("No of days Over in arrears", typeof(string)); tempBranchData.Columns.Add("Customer name", typeof(string)); tempBranchData.Columns.Add("Address", typeof(string)); tempBranchData.Columns.Add("Marketing officer", typeof(string)); tempBranchData.Columns.Add("Collector name", typeof(string)); excel = new Microsoft.Office.Interop.Excel.Application(); excel.Visible = false; excel.DisplayAlerts = false; workBook = excel.Workbooks.Add(Type.Missing); //collect excel object processes and add to a list int pid = -1; HandleRef hwnd = new HandleRef(excel, (IntPtr)excel.Hwnd); GetWindowThreadProcessId(hwnd, out pid); GlobalObjects.addPidsToList(pid); workSheet = (Microsoft.Office.Interop.Excel.Worksheet)workBook.ActiveSheet; workSheet.Name = "Contracts"; workSheet.Range[workSheet.Cells[1, 1], workSheet.Cells[1, table.Columns.Count]].Merge(); workSheet.Cells[1, 1] = "Due Contracts"; workSheet.Cells.Font.Size = 15; foreach (DataRow datarow in table.Rows) { string currentBranchCode = (datarow.Field <string>("Branch")).ToUpper().ToString(); if (branchCodeKey.Equals(currentBranchCode)) { tempBranchData.ImportRow(datarow); } } //create the branch specific excel file if data exists if (tempBranchData.Rows.Count > 0) { try { int rowcount = 2; //sort the data by product name DataView dv = tempBranchData.DefaultView; dv.Sort = "Product ASC"; DataTable sortedDT = dv.ToTable(); foreach (DataRow dr in sortedDT.Rows) { rowcount += 1; for (int i = 1; i <= table.Columns.Count; i++) { if (rowcount == 3) { workSheet.Cells[2, i] = table.Columns[i - 1].ColumnName; workSheet.Cells.Font.Color = System.Drawing.Color.Black; } workSheet.Cells[rowcount, i] = dr[i - 1].ToString(); workSheet.Cells[rowcount, i].HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft; workSheet.Cells.Font.Size = 12; if (rowcount > 3) { if (i == table.Columns.Count) { if (rowcount % 2 == 0) { celLrangE = workSheet.Range[workSheet.Cells[rowcount, 1], workSheet.Cells[rowcount, table.Columns.Count]]; } } } } } celLrangE = workSheet.Range[workSheet.Cells[1, 1], workSheet.Cells[rowcount, table.Columns.Count]]; celLrangE.EntireColumn.AutoFit(); Microsoft.Office.Interop.Excel.Borders border = celLrangE.Borders; border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous; border.Weight = 2d; celLrangE = workSheet.Range[workSheet.Cells[1, 1], workSheet.Cells[2, table.Columns.Count]]; //bold and center title celLrangE = workSheet.Range["A1", "M1"]; celLrangE.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter; fontObj = celLrangE.Font; fontObj.Color = ConsoleColor.Red; //bold header titles celLrangE = workSheet.Range["A2", "M2"]; fontObj = celLrangE.Font; fontObj.Bold = true; //cell number format - Rentals celLrangE = workSheet.Range["D3", "D" + rowcount]; celLrangE.NumberFormat = "0#.00"; //cell number format - Deter balance celLrangE = workSheet.Range["G3", "G" + rowcount]; celLrangE.NumberFormat = "0#.00"; //cell number format - default interest celLrangE = workSheet.Range["H3", "H" + rowcount]; celLrangE.NumberFormat = "0#.00"; //cell number format - No. of days over arrears celLrangE = workSheet.Range["I3", "I" + rowcount]; celLrangE.NumberFormat = "0#.00"; workBook.SaveAs(currentPath + fileName, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt); workBook.Close(false, m_objOpt, m_objOpt); excel.Quit(); fileEmailCollection.Add(fileName, branchManagerEmail); tempBranchData.Dispose(); } catch (Exception e) { Console.WriteLine(e.ToString()); } finally { utils.releaseObject(workBook); utils.releaseObject(workSheet); utils.releaseObject(excel); utils.releaseObject(celLrangE); utils.releaseObject(fontObj); } } } Console.WriteLine("Creating excel files completed"); //clear excel objects in a new thread using the pids in the pid list /* * new Thread(() => * { * Thread.CurrentThread.IsBackground = true; * Console.WriteLine("Clear excel files BM thread"); * utils.killProcess(pidList, "EXCEL"); * }).Start(); */ //sending the emails if (fileEmailCollection.Count > 0) { SendEmail sendEmail = new SendEmail(fileEmailCollection, currentPath); } }