static void JLH_ExcelBordersAndFormat(Args _args)
{
SysExcelApplication excelApplication;
SysExcelWorkBook workBook;
SysExcelWorkSheet workSheet;
SysExcelWorksheets worksheets;
SysExcelCells cells;
// Borders
COM range;
COM borders, border;
SysExcelRange sysExcelRange;
// Style
SysExcelInterior interior;
SysExcelStyles styles;
SysExcelStyle styleColumnHeaders;
SysExcelFont fontColumnHeaders;
Object languageSettings;
int languageId;
Hwnd hwnd;
#Excel
;
startLengthyOperation();
excelApplication = SysExcelApplication::construct();
// It seems fun to see the Excelsheet being build up, but clicking the sheet when being built up, will occasionally result in an error, so hide the application
// until the sheet is completely finished.
excelApplication.visible(false);
workBook = excelApplication.workbooks().add();
workSheet = workBook.worksheets().itemFromNum(1);
languageSettings = excelApplication.comObject().LanguageSettings();
// Use the language from excel the enter language specific formulas
languageId = languageSettings.LanguageId(2); //2 -> UI Language Id
// Start filling some cells
workSheet.cells().item(2, 2).value("Cell 1");
workSheet.cells().item(3, 2).value("Cell 2");
workSheet.cells().item(2, 4).value("Cell 3");
workSheet.cells().item(3, 4).value("Cell 4");
// Now we start to create some borders
/* LEFT BORDER FOR ALL CELLS in the RANGE- Item (1)*/
sysExcelRange = worksheet.range('B6:C8');
range = sysExcelRange.comObject();
// then we define the range for the borders
borders = range.Borders();
// Item(1) is the left border:
border = borders.Item(1);
// LineStyle can be 1 to 13, play around to find the style you like. 1 is the default line
border.lineStyle(1);
// Weight can be 1 to 4, this defines the thickness of the borderline, 2 is the default thickness
border.Weight(2);
workSheet.cells().item(6, 2).value("Item 1");
workSheet.cells().item(7, 2).value("Item 1");
workSheet.cells().item(8, 2).value("Item 1");
workSheet.cells().item(6, 3).value("Item 1");
workSheet.cells().item(7, 3).value("Item 1");
workSheet.cells().item(8, 3).value("Item 1");
/* RIGHT BORDER FOR ALL CELLS in the RANGE - Item(2)*/
sysExcelRange = worksheet.range('E6:F8');
range = sysExcelRange.comObject();
borders = range.Borders();
border = borders.Item(2);
border.lineStyle(1);
border.Weight(2);
workSheet.cells().item(6, 5).value("Item 2");
workSheet.cells().item(7, 5).value("Item 2");
workSheet.cells().item(8, 5).value("Item 2");
workSheet.cells().item(6, 6).value("Item 2");
workSheet.cells().item(7, 6).value("Item 2");
workSheet.cells().item(8, 6).value("Item 2");
/* TOP BORDER FOR ALL CELLS in the RANGE - Item(3) */
sysExcelRange = worksheet.range('H6:I8');
range = sysExcelRange.comObject();
borders = range.Borders();
border = borders.Item(3);
border.lineStyle(1);
border.Weight(2);
workSheet.cells().item(6, 8).value("Item 3");
workSheet.cells().item(7, 8).value("Item 3");
workSheet.cells().item(8, 8).value("Item 3");
workSheet.cells().item(6, 9).value("Item 3");
workSheet.cells().item(7, 9).value("Item 3");
workSheet.cells().item(8, 9).value("Item 3");
/* BOTTOM BORDER FOR ALL CELLS in the RANGE - Item(4)*/
sysExcelRange = worksheet.range('K6:L8');
range = sysExcelRange.comObject();
borders = range.Borders();
border = borders.Item(4);
border.lineStyle(1);
border.Weight(2);
workSheet.cells().item(6, 11).value("Item 4");
workSheet.cells().item(7, 11).value("Item 4");
workSheet.cells().item(8, 11).value("Item 4");
workSheet.cells().item(6, 12).value("Item 4");
workSheet.cells().item(7, 12).value("Item 4");
workSheet.cells().item(8, 12).value("Item 4");
/* CROSS T-B INNER - Item(5)*/
sysExcelRange = worksheet.range('N6:O8');
range = sysExcelRange.comObject();
borders = range.Borders();
border = borders.Item(5);
border.lineStyle(1);
border.Weight(2);
workSheet.cells().item(6, 14).value("Item 5");
workSheet.cells().item(7, 14).value("Item 5");
workSheet.cells().item(8, 14).value("Item 5");
workSheet.cells().item(6, 15).value("Item 5");
workSheet.cells().item(7, 15).value("Item 5");
workSheet.cells().item(8, 15).value("Item 5");
/* CROSS B-T INNER - Item (6)*/
sysExcelRange = worksheet.range('B10:C12');
range = sysExcelRange.comObject();
borders = range.Borders();
border = borders.Item(6);
border.lineStyle(1);
border.Weight(2);
workSheet.cells().item(10, 2).value("Item 6");
workSheet.cells().item(11, 2).value("Item 6");
workSheet.cells().item(12, 2).value("Item 6");
workSheet.cells().item(10, 3).value("Item 6");
workSheet.cells().item(11, 3).value("Item 6");
workSheet.cells().item(12, 3).value("Item 6");
/* LEFT BORDER FOR RANGE - Item(7)*/
sysExcelRange = worksheet.range('E10:F12');
range = sysExcelRange.comObject();
borders = range.Borders();
border = borders.Item(7);
border.lineStyle(1);
border.Weight(2);
workSheet.cells().item(10, 5).value("Item 7");
workSheet.cells().item(11, 5).value("Item 7");
workSheet.cells().item(12, 5).value("Item 7");
workSheet.cells().item(10, 6).value("Item 7");
workSheet.cells().item(11, 6).value("Item 7");
workSheet.cells().item(12, 6).value("Item 7");
/* TOP BORDER FOR RANGE - Item(8) */
sysExcelRange = worksheet.range('H10:I12');
range = sysExcelRange.comObject();
borders = range.Borders();
border = borders.Item(8);
border.lineStyle(1);
border.Weight(2);
workSheet.cells().item(10, 8).value("Item 8");
workSheet.cells().item(11, 8).value("Item 8");
workSheet.cells().item(12, 8).value("Item 8");
workSheet.cells().item(10, 9).value("Item 8");
workSheet.cells().item(11, 9).value("Item 8");
workSheet.cells().item(12, 9).value("Item 8");
/* BOTTOM BORDER FOR RANGE - Item(9)*/
sysExcelRange = worksheet.range('K10:L12');
range = sysExcelRange.comObject();
borders = range.Borders();
border = borders.Item(9);
border.lineStyle(1);
border.Weight(2);
workSheet.cells().item(10, 11).value("Item 9");
workSheet.cells().item(11, 11).value("Item 9");
workSheet.cells().item(12, 11).value("Item 9");
workSheet.cells().item(10, 12).value("Item 9");
workSheet.cells().item(11, 12).value("Item 9");
workSheet.cells().item(12, 12).value("Item 9");
/* RIGHT BORDER FOR RANGE - Item(10)*/
sysExcelRange = worksheet.range('N10:O12');
range = sysExcelRange.comObject();
borders = range.Borders();
border = borders.Item(10);
border.lineStyle(1);
border.Weight(2);
workSheet.cells().item(10, 14).value("Item 10");
workSheet.cells().item(11, 14).value("Item 10");
workSheet.cells().item(12, 14).value("Item 10");
workSheet.cells().item(10, 15).value("Item 10");
workSheet.cells().item(11, 15).value("Item 10");
workSheet.cells().item(12, 15).value("Item 10");
/* INNER LEFT BORDER FOR RANGE - Item(11)*/
sysExcelRange = worksheet.range('B14:C16');
range = sysExcelRange.comObject();
borders = range.Borders();
border = borders.Item(11);
border.lineStyle(1);
border.Weight(2);
workSheet.cells().item(14, 2).value("Item 11");
workSheet.cells().item(15, 2).value("Item 11");
workSheet.cells().item(16, 2).value("Item 11");
workSheet.cells().item(14, 3).value("Item 11");
workSheet.cells().item(15, 3).value("Item 11");
workSheet.cells().item(16, 3).value("Item 11");
/* INNER TOP BORDER FOR RANGE - Item(12) */
sysExcelRange = worksheet.range('E14:F16');
range = sysExcelRange.comObject();
borders = range.Borders();
border = borders.Item(12);
border.lineStyle(1);
border.Weight(2);
workSheet.cells().item(14, 5).value("Item 12");
workSheet.cells().item(15, 5).value("Item 12");
workSheet.cells().item(16, 5).value("Item 12");
workSheet.cells().item(14, 6).value("Item 12");
workSheet.cells().item(15, 6).value("Item 12");
workSheet.cells().item(16, 6).value("Item 12");
// PUTTING IT ALL TOGETHER:
/* COLORED ALL-BORDERS FOR RANGE*/
sysExcelRange = worksheet.range('H14:I16');
range = sysExcelRange.comObject();
borders = range.Borders();
border = borders.Item(1);
border.lineStyle(1);
border.color(WinApi::rgb2int(83, 141, 213));
border.Weight(2);
border = borders.Item(3);
border.lineStyle(1);
border.color(WinApi::rgb2int(83, 141, 213));
border.Weight(2);
border = borders.Item(9);
border.lineStyle(1);
border.color(WinApi::rgb2int(83, 141, 213));
border.Weight(2);
border = borders.Item(10);
border.lineStyle(1);
border.color(WinApi::rgb2int(83, 141, 213));
border.Weight(2);
workSheet.cells().item(14, 8).value("allborder");
workSheet.cells().item(15, 8).value("allborder");
workSheet.cells().item(16, 8).value("allborder");
workSheet.cells().item(14, 9).value("allborder");
workSheet.cells().item(15, 9).value("allborder");
workSheet.cells().item(16, 9).value("allborder");
// Add style
styles = workbook.styles();
styleColumnHeaders = styles.add('HeaderColumn');
interior = styleColumnHeaders.interior();
interior.color(WinApi::rgb2int(54, 96, 146));
fontColumnHeaders = styleColumnHeaders.font();
fontColumnHeaders.bold(true);
fontColumnHeaders.color(WinApI::rgb2int(255, 255, 255));
workSheet.cells().range('B18:C18').style('HeaderColumn');
workSheet.cells().item(18, 2).value("Format");
workSheet.cells().item(18, 3).value("Format");
// Merge two fields
workSheet.cells().item(18, 5).value("Merged");
sysExcelRange = workSheet.cells().range('E18:F18');
sysExcelRange.comObject().MergeCells(1);
SysExcelRange.horizontalAlignment(#xlCenter);
// Hide column Q
workSheet.cells().item(18, 16).value("Column Q is hidden");
worksheet.columns().item(17).hidden(true);
// Resize all columns:
worksheet.columns().autoFit();
endLengthyOperation();
// Show the excelsheet
excelApplication.visible(true);
// And bring sheet to the front
hwnd = WinAPI::findWindow(#XLMAIN, '');
WinAPI::bringWindowToTop(hwnd);
WinAPI::setForegroundWindow(hwnd);
}
