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 CreateCalInProcessExcel(IExcelUtil excelUtil) { _crExcel = excelUtil; }
public CreateCalInProcessExcel() { _crExcel = new ExcelUtility(); }
/// <summary> /// デフォールトのコンストラクタ /// </summary> /* * public CreateToolInventoryExcel() { * _crExcel = new ExcelUtility(); * } */ public CreateToolInventoryExcel(IExcelUtil crExcel) { _crExcel = crExcel; }