private static void sendEmailWithExcelFile(string salesOrg, List <MissingCMIRProperty> missingCMIRList, IMailUtil mu, string email)
{
IDBConnector dbxl = Create.dbXl();
Workbook wb;
string path = $"{Environment.GetFolderPath(Environment.SpecialFolder.Desktop)}\\{salesOrg} {DateTime.Now.Day}.{DateTime.Now.Month}.{DateTime.Now.Year} {DateTime.Now.Hour}hour {DateTime.Now.Minute}minute Missing CMIR Report.xlsx";
dbxl.listToExcel(list: missingCMIRList, path: path);
using (IExcelUtil xl = Create.xlUtil()) {
try {
wb = xl.getWorkbook(path: path);
} catch (Exception) {
System.Threading.Thread.Sleep(5000);
wb = xl.getWorkbook(path: path);
}
Worksheet ws = wb.Worksheets[1] as Worksheet;
try {
IdaExcelService.prettifyExcel(worksheet: ws, excelUtilObj: xl);
} catch (Exception) {
System.Threading.Thread.Sleep(2000);
IdaExcelService.prettifyExcel(worksheet: ws, excelUtilObj: xl);
}
wb.Save();
mu.mailOneAttachment(emailTo: email, cc: "", subject: $"{salesOrg} Missing CMIR Report", body: $"{mu.listToHTMLtable(missingCMIRList)}", path: path);
dbxl.closeConnection();
xl.closeWBAndAllInstances(wbName: wb.Name);
}
}
public static void prettifyExcel(Worksheet worksheet, IExcelUtil excelUtilObj)
{
var range = worksheet.UsedRange;
long lastColumn = excelUtilObj.getColumnCount(row: 1, ws: worksheet);
#region gottaBuildThatWall
range.Borders[XlBordersIndex.xlEdgeLeft].LineStyle = XlLineStyle.xlContinuous;
range.Borders[XlBordersIndex.xlEdgeLeft].Weight = XlBorderWeight.xlThin;
range.Borders[XlBordersIndex.xlEdgeRight].LineStyle = XlLineStyle.xlContinuous;
range.Borders[XlBordersIndex.xlEdgeRight].Weight = XlBorderWeight.xlThin;
range.Borders[XlBordersIndex.xlEdgeBottom].LineStyle = XlLineStyle.xlContinuous;
range.Borders[XlBordersIndex.xlEdgeBottom].Weight = XlBorderWeight.xlThin;
range.Borders[XlBordersIndex.xlEdgeTop].LineStyle = XlLineStyle.xlContinuous;
range.Borders[XlBordersIndex.xlEdgeTop].Weight = XlBorderWeight.xlThin;
range.Borders[XlBordersIndex.xlInsideVertical].LineStyle = XlLineStyle.xlContinuous;
range.Borders[XlBordersIndex.xlInsideVertical].Weight = XlBorderWeight.xlThin;
range.Borders[XlBordersIndex.xlInsideHorizontal].LineStyle = XlLineStyle.xlContinuous;
range.Borders[XlBordersIndex.xlInsideHorizontal].Weight = XlBorderWeight.xlThin;
#endregion
//this is full of workarounds due to excel interop bugs like object not set
Range firstRow = worksheet.Range[worksheet.Cells[1, 1], worksheet.Cells[1, lastColumn]];
if (firstRow is null)
{
firstRow = worksheet.Range[worksheet.Cells[1, 1], worksheet.Cells[1, lastColumn]];
}
Interior firstRowInterior = firstRow.Interior;
object firstRowColorIndex = firstRowInterior.ColorIndex;
firstRowColorIndex = 34;
range.Columns.AutoFit();
}
public void SetUp()
{
_excelUtil = new ExcelUtil();
}
public void SetUp()
{
_excelUtil = new ExcelUtil();
}
public CreateCalInProcessExcel(IExcelUtil excelUtil)
{
_crExcel = excelUtil;
}
public CreateCalInProcessExcel()
{
_crExcel = new ExcelUtility();
}
/// <summary>
/// デフォールトのコンストラクタ
/// </summary>
/*
* public CreateToolInventoryExcel() {
* _crExcel = new ExcelUtility();
* }
*/
public CreateToolInventoryExcel(IExcelUtil crExcel)
{
_crExcel = crExcel;
}
