Export excel file format
1. Fomat 1.
1 SysExcelStyle style; 2 ; 3 4 ... 5 // Set orientation 6 style.comObject().orientation(90); 7 // distribute text 8 style.horizontalalignment(-4117); 9 // center text 10 style.horizontalalignment(-4108); 11 // right align text 12 style.horizontalalignment(-4152); 13 // set indent level 14 style.comObject().indentlevel(5); 15 // wrap text 16 style.comObject().wraptext(true); 17 ... 18 19 sheet.cells().range("A1:B1").comObject().merge(); //合并 20 sheet.columns().autoFit(); //自动宽度 21 22 static void ExportToExcel(Args _args) 23 { 24 #AviFiles 25 SysOperationProgress progress = new SysOperationProgress(); 26 SysExcelApplication sysExcelApplication; 27 SysExcelWorkbooks sysExcelWorkBooks; 28 // Filename to which you will be writing your data 29 FileName fileName = "C:\\Windows\\Temp\\ExportToExcel.xlsx"; 30 SysExcelWorkbook sysExcelWorkBook; 31 SysExcelWorkSheets sysExcelWorkSheets; 32 SysExcelWorkSheet sysExcelWorkSheet; 33 SysExcelWorkSheet sysExcelWorksheetBackOrder; 34 SysExcelWorksheet sysExcelWorkSheetToBeDeleted; 35 SysExcelStyles styles; 36 SysExcelStyle style; 37 SysExcelFont font; 38 SysExcelCells cells; 39 int row = 1; 40 int rowBackOrder; 41 CustTable custTable; 42 SalesTable salesTable; 43 SalesLine salesLine; 44 boolean workSheetAdded = false; 45 int nbrOfCustomers; 46 ; 47 48 // Initialising progress bar 49 progress.setCaption("Export To Excel in progress..."); 50 progress.setAnimation(#AviTransfer); 51 // Initialisation of some objects 52 sysExcelApplication = SysExcelApplication::construct(); 53 // Create new workbook 54 sysExcelWorkBooks = sysExcelApplication.workbooks(); 55 sysExcelWorkBook = sysExcelWorkBooks.add(); 56 // Create new style 57 styles = sysExcelWorkBook.styles(); 58 style = styles.add("Header"); 59 // Set font for this style to bold and color to red 60 font = style.font(); 61 font.bold(true); 62 font.color(255); 63 // Get worksheets collection 64 sysExcelWorkSheets = sysExcelWorkbook.worksheets(); 65 // Excel visible on desktop running the job or not? 66 sysExcelApplication.visible(false); 67 // Newly created Excel files have by default some worksheets 68 // Delete those worksheets created by default 69 while(sysExcelWorkSheets.count() > 1) 70 { 71 sysExcelWorkSheetToBeDeleted = sysExcelWorkSheets.itemFromNum(2); 72 sysExcelWorkSheetToBeDeleted.delete(); 73 } 74 // Add as many worksheets as there are customers 75 select count(RecId) from CustTable; 76 sysExcelWorkSheet = sysExcelWorkSheets.add(null,null,CustTable.RecId); 77 // Add another worksheet 78 sysExcelWorkSheet = sysExcelWorkSheets.add(); 79 //Rename the first worksheet 80 sysExcelWorkSheet.name("Customers"); 81 // Make a title row 82 // set a value in cell on row 1 column 1 83 sysExcelWorkSheet.cells().item(1,1).value("Customer account"); 84 // set a value in cell on row 1 column 2 85 sysExcelWorksheet.cells().item(1,2).value("Name"); 86 // set the title row in the Header style 87 sysExcelWorksheet.rows().item(1).style("Header"); 88 while select custTable 89 { 90 progress.setText(strfmt("Customer %1", custTable.Name)); 91 row++; 92 rowBackOrder = 1; 93 sysExcelWorksheet.cells().item(row,1).value(custTable.AccountNum); 94 sysExcelWorksheet.cells().item(row,2).value(custTable.Name); 95 while select salesLine 96 where salesLine.SalesStatus == salesStatus::Backorder 97 && salesLine.ConfirmedDlv < Today() 98 && salesLine.RemainSalesPhysical > 0 99 join salesTable 100 where salesTable.SalesId == salesLine.SalesId && 101 salesTable.CustAccount == custTable.AccountNum 102 { 103 if(!workSheetAdded) 104 { 105 // Use the next Excel worksheet and rename it 106 sysExcelWorksheetBackOrder = sysExcelWorkSheets.itemFromNum( 107 row); 108 //Name of worksheet can have maximum 31 characters 109 sysExcelWorksheetBackOrder.name(substr(custTable.Name,1,31)); 110 workSheetAdded = true; 111 // Make a title row 112 // set a value in cell on row 1 column 1 113 sysExcelWorksheetBackOrder.cells().item(1,1).value( 114 "Ship Date"); 115 // set a value in cell on row 1 column 2 116 sysExcelWorksheetBackOrder.cells().item(1,2).value( 117 "Item Number"); 118 // set a value in cell on row 1 column 3 119 sysExcelWorksheetBackOrder.cells().item(1,3).value( 120 "Item Name"); 121 // set a value in cell on row 1 column 4 122 sysExcelWorksheetBackOrder.cells().item(1,4).value( 123 "Deliver Remainder"); 124 // set the title row in the Header style 125 sysExcelWorksheetBackOrder.rows().item(1).style("Header"); 126 } 127 rowBackOrder++; 128 sysExcelWorksheetBackOrder.cells().item(rowBackOrder,1).value( 129 salesLine.ConfirmedDlv); 130 sysExcelWorksheetBackOrder.cells().item(rowBackOrder,2).value( 131 salesLine.ItemId); 132 sysExcelWorksheetBackOrder.cells().item(rowBackOrder,3).value( 133 InventTable::find(salesLine.ItemId).ItemName); 134 sysExcelWorksheetBackOrder.cells().item(rowBackOrder,4).value( 135 salesLine.RemainSalesPhysical); 136 } 137 // Set the column width to autofit 138 sysExcelWorksheetBackOrder.columns().autoFit(); 139 // Format the 4th column as Number with 2 decimal places 140 cells = sysExcelWorksheetBackOrder.cells(); 141 cells.range("D2:D99").numberFormat("0,00"); 142 workSheetAdded = false; 143 } 144 // Set the column width to autoFit 145 sysExcelWorksheet.columns().autoFit(); 146 // Suppress the pop-up window: 147 // A file named foo already exists in this location. Do you want to replace it? 148 sysExcelApplication.displayAlerts(false); 149 // Save the Excel file 150 sysExcelWorkbook.saveAs(fileName); 151 sysExcelWorkBook.comObject().save(); 152 sysExcelWorkBook.saved(true); 153 // Make sure you close the Excel application 154 // Especially if you run the job without showing Excel on the desktop 155 // (sysExcelApplication.visible(false)) 156 sysExcelApplication.quit(); 157 158 }
2. My example.
1 public void wiiSetStyle_backup(str strFilePath) 2 { 3 SysExcelApplication excel; 4 SysExcelWorkSheets sheets; 5 SysExcelWorkSheet sheet; 6 SysExcelRange sysExcelRange; 7 SysExcelCells excelCells; 8 SysExcelStyles styles; 9 SysExcelStyle Style; 10 str strSetIndex; 11 12 ; 13 excel = SysExcelApplication::construct(); 14 excel.workbooks().open(strFilePath); 15 sheets = excel.worksheets(); 16 sheet = sheets.itemFromNum(1); 17 excelCells = sheet.cells(); 18 styles = excel.workbooks().item(1).styles(); 19 style = styles.add("mystyles"); 20 style.font().bold(true); 21 style.font().color(000100); 22 style.font().comObject().Size(10); 23 style.font().comObject().Name("Arial"); 24 sysExcelRange = sheet.range("A1:F1"); 25 sysExcelRange.style("mystyles"); 26 style = styles.add("mystyles1"); 27 style.font().bold(true); 28 style.font().color(000100); 29 style.font().comObject().Size(10); 30 style.font().comObject().Name("Arial"); 31 sysExcelRange = sheet.range("G2:N2"); 32 sysExcelRange.style("mystyles1"); 33 style = styles.add("mystyles2"); 34 strSetIndex = "A3:N" + int2str(lastindex+2); 35 style.font().comObject().Size(8); 36 style.font().comObject().Name("Arial"); 37 sysExcelRange = sheet.range(strSetIndex); 38 sysExcelRange.style("mystyles2"); 39 style = styles.add("mystyles3"); 40 strSetIndex = "A3:N" + int2str(lastindex+2); 41 style.font().comObject().Size(8); 42 style.font().comObject().Name("Arial"); 43 sysExcelRange = sheet.range(strSetIndex); 44 sysExcelRange.style("mystyles3"); 45 excel.save(); 46 excel.workbooks().close(); 47 excel.quit(); 48 49 }
