/// <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);
}
}
