List of usage examples for org.apache.poi.xssf.usermodel XSSFCell setCellStyle
@Override public void setCellStyle(CellStyle style)
Set the style for the cell.
From source file:com.solidmaps.webapp.report.utils.ExcelMapCivilGenerator.java
private void createCellSell(XSSFSheet sheetBuy, XSSFRow row, String value) { XSSFCell cell = row.createCell(9); sheetBuy.addMergedRegion(new CellRangeAddress(row.getRowNum(), row.getRowNum(), 9, 11)); cell.setCellStyle(styleProductList); cell.setCellValue(value);/* w w w .j a va2 s.c om*/ }
From source file:com.solidmaps.webapp.report.utils.ExcelMapCivilGenerator.java
private void createCellNextTrimester(XSSFSheet sheetBuy, XSSFRow row, String value) { XSSFCell cell = row.createCell(12); sheetBuy.addMergedRegion(new CellRangeAddress(row.getRowNum(), row.getRowNum(), 12, 13)); cell.setCellStyle(styleProductList); cell.setCellValue(value);/*from ww w . ja va 2 s. co m*/ }
From source file:com.yyl.common.utils.excel.ExcelTools.java
/** * ?xlsxExcel// w w w . java 2s . c om * @param heads * @param data ? * @param sheetName Excel? * @param out ? * @return ByteArrayOutputStream * @throws IOException */ public static void writeToXLSX(ExcelRow heads, ExcelData data, String sheetName, ByteArrayOutputStream out) throws IOException { XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet sheet1 = wb.createSheet(sheetName); XSSFRow row = sheet1.createRow(0); XSSFCellStyle style = wb.createCellStyle(); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); XSSFCell cell; for (int i = 0; i < heads.size(); i++) { cell = row.createCell(i); cell.setCellValue(heads.get(i)); cell.setCellStyle(style); } if (data != null && data.size() != 0) { for (int n = 0; n < data.size(); n++) { row = sheet1.createRow(n + 1); ExcelRow datarow = data.get(n); for (int m = 0; m < datarow.size(); m++) { cell = row.createCell(m); cell.setCellValue(datarow.get(m)); cell.setCellStyle(style); } } } wb.write(out); }
From source file:Controller.Sonstiges.ExcelController.java
private void makeTitleOfPage() throws IOException { this.sheet.addMergedRegion(new CellRangeAddress(0, 3, 0, 13)); this.row = this.sheet.createRow(0); XSSFCell cell2 = this.row.createCell(0); String title = "Inprotuc Datenbank | Informationen zur Personen \nSuchkriterien: "; ArrayList<String> array2 = this.model.getQueryInfo(); ArrayList<String> array = this.deleteEmptyValueOArray(array2); String info = ""; if (array.size() == 2) { info = array.get(0) + " / " + array.get(1) + "."; }/*ww w .j a v a 2 s . co m*/ if (array.size() == 4) { info = array.get(0) + "/" + array.get(1) + ", "; info = info + array.get(2) + "/" + array.get(3) + "."; } if (array.size() == 6) { info = array.get(0) + "/" + array.get(1) + ", "; info = info + array.get(2) + "/" + array.get(3) + ", "; info = info + array.get(4) + "/" + array.get(5) + "."; } cell2.setCellValue(title + info); CellStyle cellStyle = this.wb.createCellStyle(); cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); cellStyle.setAlignment(HorizontalAlignment.LEFT); // font Font font = this.wb.createFont(); font.setFontHeightInPoints((short) 14); font.setFontName(HSSFFont.FONT_ARIAL); font.setBold(true); font.setColor(HSSFColor.BLACK.index); cellStyle.setFont(font); cell2.setCellStyle(cellStyle); }
From source file:Dao.XlsBillDao.java
public void GenFullXLS(String pono, String relpath) { try {// w ww. j av a 2s . c om //0.Declare Variables for Sheet //DB Variable // pono = "WO/2015/2005"; //XLS Variable XSSFSheet spreadsheet; XSSFWorkbook workbook; XSSFRow row; XSSFCell cell; XSSFFont xfont = null; XSSFCellStyle xstyle = null; //1.Get Connection and Fetch Data ArrayList<WorkItemBean> wi1 = new ArrayList<WorkItemBean>(); WorkDao wdao1 = new WorkDao(); wi1 = wdao1.getWOItem(pono); //2.Create WorkBook and Sheet workbook = new XSSFWorkbook(); spreadsheet = workbook.createSheet("WorkOrder Detail"); // spreadsheet.protectSheet("kandarpCBA"); // spreadsheet.setColumnWidth(0, 255); //set header style xfont = workbook.createFont(); xfont.setFontHeight(11); xfont.setFontName("Calibri"); xfont.setBold(true); //Set font into style CellStyle borderStyle = workbook.createCellStyle(); borderStyle.setAlignment(CellStyle.ALIGN_CENTER); borderStyle.setFont(xfont); xstyle = workbook.createCellStyle(); xstyle.setFont(xfont); //header row = spreadsheet.createRow(0); cell = row.createCell(0); cell.setCellValue("WORK ORDER NO : " + pono); cell.setCellStyle(borderStyle); spreadsheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 5)); //3.Get First Row and Set Headers row = spreadsheet.createRow(1); cell = row.createCell(0); cell.setCellValue("LINE_NO"); cell.setCellStyle(xstyle); cell = row.createCell(1); cell.setCellValue("ITEM_ID"); cell.setCellStyle(xstyle); cell = row.createCell(2); cell.setCellValue("DESCRIPTION"); cell.setCellStyle(xstyle); cell = row.createCell(3); cell.setCellValue("UOM"); cell.setCellStyle(xstyle); cell = row.createCell(4); cell.setCellValue("QTY"); cell.setCellStyle(xstyle); cell = row.createCell(5); cell.setCellValue("RATE"); cell.setCellStyle(xstyle); cell = row.createCell(6); cell.setCellValue("NOTE"); cell.setCellStyle(xstyle); int i = 2; for (WorkItemBean w : wi1) { row = spreadsheet.createRow(i); cell = row.createCell(0); cell.setCellValue(w.getLINE_NO()); cell = row.createCell(1); cell.setCellValue(w.getITEM_ID()); cell = row.createCell(2); cell.setCellValue(w.getITEM_DESC()); cell = row.createCell(3); cell.setCellValue(w.getUOM()); cell = row.createCell(4); cell.setCellValue(w.getQTY()); cell = row.createCell(5); cell.setCellValue(w.getRATE()); cell = row.createCell(6); cell.setCellValue(w.getCMT()); i++; } //Export to Excel // FileOutputStream out = new FileOutputStream(new File("D://" + pono.replace("/", "-") + "_Items" + ".xlsx")); FileOutputStream out = new FileOutputStream( new File(relpath + "uxls//" + pono.replace("/", "-") + "_Items" + ".xlsx")); workbook.write(out); out.close(); Logger.getLogger(XlsBillDao.class.getName()).log(Level.SEVERE, "DONE|!"); Logger.getLogger(XlsBillDao.class.getName()).log(Level.SEVERE, "{0}uxls//{1}" + "_Items" + ".xlsx", new Object[] { relpath, pono.replace("/", "-") }); } catch (FileNotFoundException ex) { Logger.getLogger(XlsBillDao.class.getName()).log(Level.SEVERE, "Exception : {0}", ex); } catch (IOException ex) { Logger.getLogger(XlsBillDao.class.getName()).log(Level.SEVERE, "Exception : {0}", ex); } }
From source file:Dao.XlsBillDao.java
public void GenXLS(String pono, String relpath) { try {//from w w w .j av a 2s . c o m //0.Declare Variables for Sheet //DB Variable // pono = "WO/2015/2005"; String sql; Connection con; PreparedStatement ps; ResultSet rs; //XLS Variable XSSFSheet spreadsheet; XSSFWorkbook workbook; XSSFRow row; XSSFCell cell; XSSFFont xfont = null; XSSFCellStyle xstyle = null; //1.Get Connection and Fetch Data con = DBmanager.GetConnection(); sql = "SELECT DISTINCT cba_wo_item.line_no, cba_wo_item.item_id,\n" + " mtl_system_items.description, cba_wo_item.uom,\n" + " cba_wo_item.qty, cba_wo_item.rate, cba_wo_item.cmt,\n" + " cba_wo_item.plant, cba_wo_item.proj, cba_wo_item.task," + " cba_wo_item.po_no\n" + " FROM cba_wo_item, mtl_system_items\n" + " WHERE ( (cba_wo_item.item_id = mtl_system_items.segment1)\n" + " AND (mtl_system_items.organization_id = 0)\n" + " AND (cba_wo_item.po_no = '" + pono + "')\n" + " )\n" + " ORDER BY cba_wo_item.line_no"; ps = con.prepareStatement(sql); rs = ps.executeQuery(); //2.Create WorkBook and Sheet workbook = new XSSFWorkbook(); spreadsheet = workbook.createSheet("WorkOrder Detail"); //spreadsheet.protectSheet("kandarpCBA"); //spreadsheet.setColumnWidth(0, 255); //set header style xfont = workbook.createFont(); xfont.setFontHeight(11); xfont.setFontName("Calibri"); xfont.setBold(true); //Set font into style CellStyle borderStyle = workbook.createCellStyle(); borderStyle.setAlignment(CellStyle.ALIGN_CENTER); borderStyle.setFont(xfont); // borderStyle.setFillBackgroundColor(IndexedColors.GREEN.getIndex()); // borderStyle.setFillPattern(CellStyle.ALIGN_FILL); xstyle = workbook.createCellStyle(); xstyle.setFont(xfont); //header row = spreadsheet.createRow(0); cell = row.createCell(0); cell.setCellValue("WORK ORDER NO : " + pono + " Note : If WO is with project information,each bill item should have project and task"); cell.setCellStyle(borderStyle); spreadsheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 9)); //3.Get First Row and Set Headers row = spreadsheet.createRow(1); cell = row.createCell(0); cell.setCellValue("LINE_NO"); cell.setCellStyle(xstyle); cell = row.createCell(1); cell.setCellValue("ITEM_ID"); cell.setCellStyle(xstyle); cell = row.createCell(2); cell.setCellValue("DESCRIPTION"); cell.setCellStyle(xstyle); cell = row.createCell(3); cell.setCellValue("UOM"); cell.setCellStyle(xstyle); cell = row.createCell(4); cell.setCellValue("QTY"); cell.setCellStyle(xstyle); cell = row.createCell(5); cell.setCellValue("RATE"); cell.setCellStyle(xstyle); cell = row.createCell(6); cell.setCellValue("WO NOTE"); cell.setCellStyle(xstyle); cell = row.createCell(7); cell.setCellValue("PLANT"); cell.setCellStyle(xstyle); cell = row.createCell(8); cell.setCellValue("COST CENTER"); cell.setCellStyle(xstyle); cell = row.createCell(9); cell.setCellValue("PROJECT"); cell.setCellStyle(xstyle); cell = row.createCell(10); cell.setCellValue("TASK"); cell.setCellStyle(xstyle); cell = row.createCell(11); cell.setCellValue("HERE ADD NOTE"); cell.setCellStyle(xstyle); //Itrate or Database data and write int i = 2; while (rs.next()) { row = spreadsheet.createRow(i); cell = row.createCell(0); cell.setCellValue(rs.getString(1)); cell = row.createCell(1); cell.setCellValue(rs.getString(2)); cell = row.createCell(2); cell.setCellValue(rs.getString(3)); cell = row.createCell(3); cell.setCellValue(rs.getString(4)); cell = row.createCell(4); cell.setCellValue(rs.getString(6)); cell = row.createCell(5); cell.setCellValue(rs.getString(5)); cell = row.createCell(6); cell.setCellValue(""); cell = row.createCell(7); cell.setCellValue(rs.getString(7)); cell = row.createCell(8); cell.setCellValue(rs.getString(8)); cell = row.createCell(9); cell.setCellValue(rs.getString(9)); cell = row.createCell(10); cell.setCellValue(rs.getString(10)); cell = row.createCell(11); cell.setCellValue(""); i++; } //SECOND WORKSHEET FOR COST CENTER AND PLANT DETAIL XSSFRow row2; XSSFCell cell2; XSSFSheet ccsheet = workbook.createSheet("Cost Center"); row2 = ccsheet.createRow(0); cell2 = row2.createCell(0); cell2.setCellValue("Cost Center name and code. Please enter only code in excel"); cell2.setCellStyle(borderStyle); ccsheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 9)); row2 = ccsheet.createRow(1); cell2 = row2.createCell(0); cell2.setCellValue("CODE"); cell2.setCellStyle(xstyle); cell2 = row2.createCell(1); cell2.setCellValue("NAME"); cell2.setCellStyle(xstyle); con = DBmanager.GetConnection(); sql = "select cc,plant from cba_cc_mst"; ps = con.prepareStatement(sql); rs = ps.executeQuery(); int i2 = 2; while (rs.next()) { row2 = ccsheet.createRow(i2); cell2 = row2.createCell(0); cell2.setCellValue(rs.getString(1)); cell2 = row2.createCell(1); cell2.setCellValue(rs.getString(2)); i2++; } //THIRD SHEET //SECOND WORKSHEET FOR COST CENTER AND PLANT DETAIL XSSFRow row3; XSSFCell cell3; XSSFSheet plantsheet = workbook.createSheet("Plant Center"); row3 = plantsheet.createRow(0); cell3 = row3.createCell(0); cell3.setCellValue("Plant Center name and code. Please enter only code in excel"); cell3.setCellStyle(borderStyle); plantsheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 9)); row3 = plantsheet.createRow(1); cell3 = row3.createCell(0); cell3.setCellValue("CODE"); cell3.setCellStyle(xstyle); cell3 = row3.createCell(1); cell3.setCellValue("NAME"); cell3.setCellStyle(xstyle); con = DBmanager.GetConnection(); sql = "select cc,plant from cba_plant_mst"; ps = con.prepareStatement(sql); rs = ps.executeQuery(); int i3 = 2; while (rs.next()) { row3 = plantsheet.createRow(i3); cell3 = row3.createCell(0); cell3.setCellValue(rs.getString(1)); cell3 = row3.createCell(1); cell3.setCellValue(rs.getString(2)); i3++; } //SHEET 3 HEADER //row1 XSSFSheet spreadsheet4 = workbook.createSheet("Project And Task"); XSSFRow row4 = spreadsheet4.createRow(0); XSSFCell cell4 = row4.createCell(0); cell4.setCellValue("Note : Please copy project,task code and paste into 1 sheet"); spreadsheet4.addMergedRegion(new CellRangeAddress(0, 0, 0, 5)); //row2 row4 = spreadsheet4.createRow(1); cell4 = row4.createCell(0); cell4.setCellValue("PROJECT CODE"); cell4.setCellStyle(xstyle); cell4 = row4.createCell(1); cell4.setCellValue("PROJECT NAME"); cell4.setCellStyle(xstyle); cell4 = row4.createCell(2); cell4.setCellValue("TASK CODE"); cell4.setCellStyle(xstyle); //SHEET 3 DATA int j = 2; ArrayList<WorkItemBean> wi1 = Dropdown.LoadProjTaskMst("123"); for (WorkItemBean w : wi1) { row4 = spreadsheet4.createRow(j); cell4 = row4.createCell(0); cell4.setCellValue(w.getPROJ()); cell4 = row4.createCell(1); cell4.setCellValue(w.getPROJ_NAME()); cell4 = row4.createCell(2); cell4.setCellValue(w.getTASK()); j++; } //Export to Excel // FileOutputStream out = new FileOutputStream(new File("D://" + pono.replace("/", "-") + ".xlsx")); FileOutputStream out = new FileOutputStream( new File(relpath + "xls//" + pono.replace("/", "-") + ".xlsx")); workbook.write(out); out.close(); Logger.getLogger(XlsBillDao.class.getName()).log(Level.SEVERE, "DONE|!"); Logger.getLogger(XlsBillDao.class.getName()).log(Level.SEVERE, "{0}xls//{1}.xlsx", new Object[] { relpath, pono.replace("/", "-") }); } catch (SQLException ex) { Logger.getLogger(XlsBillDao.class.getName()).log(Level.SEVERE, "Exception : {0}", ex); } catch (IOException ex) { Logger.getLogger(XlsBillDao.class.getName()).log(Level.SEVERE, "Exception : {0}", ex); } }
From source file:Dao.XlsWoDao.java
public void GenXLS(String orgId, String relpath) { try {//from w ww . j av a2s .c o m //2.Create WorkBook and Sheet XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet spreadsheet1 = workbook.createSheet("WorkOrder Detail"); XSSFSheet spreadsheet2 = workbook.createSheet("Plant Master"); XSSFSheet spreadsheet3 = workbook.createSheet("Project and Task Master"); //style XSSFFont xfont = workbook.createFont(); xfont.setFontHeight(11); xfont.setFontName("Calibri"); xfont.setBold(true); //Set font into style CellStyle borderStyle = workbook.createCellStyle(); borderStyle.setAlignment(CellStyle.ALIGN_CENTER); borderStyle.setFont(xfont); XSSFCellStyle xstyle = workbook.createCellStyle(); xstyle.setFont(xfont); //SHEET 1 HEADER //1row XSSFRow row1 = spreadsheet1.createRow(0); XSSFCell cell1 = row1.createCell(0); cell1.setCellValue("Note : If you are not sure " + "about plant,project,task please " + "leave it blank." + "It can be add when release bill"); spreadsheet1.addMergedRegion(new CellRangeAddress(0, 0, 0, 15)); //2row row1 = spreadsheet1.createRow(1); cell1 = row1.createCell(0); cell1.setCellValue("Create Work Order Template"); cell1.setCellStyle(borderStyle); spreadsheet1.addMergedRegion(new CellRangeAddress(1, 1, 0, 4)); //3row row1 = spreadsheet1.createRow(2); cell1 = row1.createCell(0); cell1.setCellValue("ITEM_NO"); cell1.setCellStyle(xstyle); cell1 = row1.createCell(1); cell1.setCellValue("RATE"); cell1.setCellStyle(xstyle); cell1 = row1.createCell(2); cell1.setCellValue("PLANT"); cell1.setCellStyle(xstyle); cell1 = row1.createCell(3); cell1.setCellValue("PROJECT"); cell1.setCellStyle(xstyle); cell1 = row1.createCell(4); cell1.setCellValue("TASK"); cell1.setCellStyle(xstyle); cell1 = row1.createCell(5); cell1.setCellValue("ADDITIONAL NOTE"); cell1.setCellStyle(xstyle); //SHEET 2 HEADER //row1 XSSFRow row2 = spreadsheet2.createRow(0); XSSFCell cell2 = row2.createCell(0); cell2.setCellValue("Note : Please copy plant code and paste into 1 sheet"); spreadsheet2.addMergedRegion(new CellRangeAddress(0, 0, 0, 5)); //row2 row2 = spreadsheet2.createRow(1); cell2 = row2.createCell(0); cell2.setCellValue("PLANT_CODE"); cell2.setCellStyle(xstyle); cell2 = row2.createCell(1); cell2.setCellValue("PLANT NAME"); cell2.setCellStyle(xstyle); //SHEET 2 DATA int i = 2; Map<String, String> plant = Dropdown.LoadPlantMst(); for (Map.Entry<String, String> entry : plant.entrySet()) { row2 = spreadsheet2.createRow(i); cell2 = row2.createCell(0); cell2.setCellValue(entry.getKey()); cell2 = row2.createCell(1); cell2.setCellValue(entry.getValue()); i++; } //SHEET 3 HEADER //row1 XSSFRow row3 = spreadsheet3.createRow(0); XSSFCell cell3 = row3.createCell(0); cell3.setCellValue("Note : Please copy project,task code and paste into 1 sheet"); spreadsheet3.addMergedRegion(new CellRangeAddress(0, 0, 0, 5)); //row2 row3 = spreadsheet3.createRow(1); cell3 = row3.createCell(0); cell3.setCellValue("PROJECT CODE"); cell3.setCellStyle(xstyle); cell3 = row3.createCell(1); cell3.setCellValue("PROJECT NAME"); cell3.setCellStyle(xstyle); cell3 = row3.createCell(2); cell3.setCellValue("TASK CODE"); cell3.setCellStyle(xstyle); //SHEET 3 DATA int j = 2; ArrayList<WorkItemBean> wi1 = Dropdown.LoadProjTaskMst(orgId); for (WorkItemBean w : wi1) { row3 = spreadsheet3.createRow(j); cell3 = row3.createCell(0); cell3.setCellValue(w.getPROJ()); cell3 = row3.createCell(1); cell3.setCellValue(w.getPROJ_NAME()); cell3 = row3.createCell(2); cell3.setCellValue(w.getTASK()); j++; } //Export to Excel // FileOutputStream out = new FileOutputStream(new File("D://" + pono.replace("/", "-") + "_Items" + ".xlsx")); // FileOutputStream out = new FileOutputStream(new File(relpath + "uxls//" + "WO_Creation_Template" + ".xlsx")); FileOutputStream out = new FileOutputStream(new File(relpath)); workbook.write(out); out.close(); Logger.getLogger(XlsBillDao.class.getName()).log(Level.SEVERE, "DONE|!"); } catch (FileNotFoundException ex) { Logger.getLogger(XlsBillDao.class.getName()).log(Level.SEVERE, "Exception : {0}", ex); } catch (IOException ex) { Logger.getLogger(XlsBillDao.class.getName()).log(Level.SEVERE, "Exception : {0}", ex); } }
From source file:data.control.dataSheet.java
private void newPatient(Patient newPatient) { XSSFRow newRow;/* w w w. ja va 2 s. c o m*/ // creating a new row if (firstSheet.getRow(newPatient.getId()) == null) { newRow = firstSheet.createRow(newPatient.getId()); } else { newRow = firstSheet.getRow(newPatient.getId()); } // creating a new cell XSSFCell cell = newRow.getCell(0); if (cell == null) { cell = newRow.createCell(0); } // adding the patient ID cell.setCellValue(newPatient.getId()); // adding the patient Name // creating a new cell cell = newRow.getCell(1); if (cell == null) { cell = newRow.createCell(1); } cell.setCellValue(newPatient.getName()); // adding BPM if (!newPatient.getBPMArray().isEmpty()) { int cellID = 2; for (Iterator itr = newPatient.getBPMArray().iterator(); itr.hasNext();) { cell = newRow.getCell(cellID); if (cell == null) { cell = newRow.createCell(cellID); } cell.setCellValue((Double) itr.next()); cellID++; } } // adding Tempreature if (!newPatient.getTempArray().isEmpty()) { int cellID = 7; for (Iterator itr = newPatient.getTempArray().iterator(); itr.hasNext();) { cell = newRow.getCell(cellID); if (cell == null) { cell = newRow.createCell(cellID); } cell.setCellValue((Double) itr.next()); cellID++; } } // adding the patient blood type cell = newRow.getCell(12); if (cell == null) { cell = newRow.createCell(12); } cell.setCellValue(newPatient.getBloodType()); // adding the patient sex cell = newRow.getCell(13); if (cell == null) { cell = newRow.createCell(13); } cell.setCellValue(newPatient.getSex()); // adding the patient age cell = newRow.getCell(14); if (cell == null) { cell = newRow.createCell(14); } cell.setCellValue(newPatient.getAge()); // setting up the date format XSSFCellStyle cellStyle = theWorkbook.createCellStyle(); CreationHelper createHelper = theWorkbook.getCreationHelper(); cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("d/m/yy hh:mm:ss")); // adding the patient dateAdded cell = newRow.getCell(15); if (cell == null) { cell = newRow.createCell(15); } if (newPatient.getDateCreated() != null) { cell.setCellValue(newPatient.getDateCreated()); } else { cell.setCellValue(new Date()); } cell.setCellStyle(cellStyle); // adding the date modified if (newPatient.getLastModified() != null) { cell = newRow.getCell(16); if (cell == null) { cell = newRow.createCell(16); } cell.setCellValue(newPatient.getLastModified()); cell.setCellStyle(cellStyle); } // adding the lastAlarm if (newPatient.getLastAlarmed() != null) { cell = newRow.getCell(17); if (cell == null) { cell = newRow.createCell(17); } cell.setCellValue(newPatient.getLastAlarmed()); cell.setCellStyle(cellStyle); } }
From source file:de.bund.bfr.knime.openkrise.db.imports.custom.bfrnewformat.TraceGenerator.java
License:Open Source License
private XSSFRow copyRow(XSSFWorkbook workbook, XSSFSheet worksheet, int sourceRowNum, int destinationRowNum) { XSSFRow sourceRow = worksheet.getRow(sourceRowNum); worksheet.shiftRows(destinationRowNum, worksheet.getLastRowNum(), 1, true, false); XSSFRow newRow = worksheet.createRow(destinationRowNum); // Loop through source columns to add to new row for (int i = 0; i < sourceRow.getLastCellNum(); i++) { // Grab a copy of the old/new cell XSSFCell oldCell = sourceRow.getCell(i); XSSFCell newCell = newRow.createCell(i); // If the old cell is null jump to next cell if (oldCell == null) { newCell = null;// www . j a va 2s . c o m continue; } // Copy style from old cell and apply to new cell XSSFCellStyle newCellStyle = workbook.createCellStyle(); newCellStyle.cloneStyleFrom(oldCell.getCellStyle()); newCell.setCellStyle(newCellStyle); // Set the cell data type newCell.setCellType(oldCell.getCellType()); } // If there are are any merged regions in the source row, copy to new row for (int i = 0; i < worksheet.getNumMergedRegions(); i++) { CellRangeAddress cellRangeAddress = worksheet.getMergedRegion(i); if (cellRangeAddress.getFirstRow() == sourceRow.getRowNum()) { CellRangeAddress newCellRangeAddress = new CellRangeAddress(newRow.getRowNum(), (newRow.getRowNum() + (cellRangeAddress.getLastRow() - cellRangeAddress.getFirstRow())), cellRangeAddress.getFirstColumn(), cellRangeAddress.getLastColumn()); worksheet.addMergedRegion(newCellRangeAddress); } } newRow.setHeight(sourceRow.getHeight()); return newRow; }
From source file:de.symeda.sormas.api.doc.DataDictionaryGenerator.java
License:Open Source License
@SuppressWarnings("unchecked") private XSSFSheet createEntitySheet(XSSFWorkbook workbook, Class<? extends EntityDto> entityClass, String i18nPrefix) {/*from w w w . j a v a2s . c o m*/ String name = I18nProperties.getCaption(i18nPrefix); String safeName = WorkbookUtil.createSafeSheetName(name); XSSFSheet sheet = workbook.createSheet(safeName); // Create XSSFTable table = sheet.createTable(); String safeTableName = safeName.replaceAll("\\s", "_"); table.setName(safeTableName); table.setDisplayName(safeTableName); XssfHelper.styleTable(table, 1); int columnCount = EntityColumn.values().length; int rowNumber = 0; // header XSSFRow headerRow = sheet.createRow(rowNumber++); for (EntityColumn column : EntityColumn.values()) { table.addColumn(); String columnCaption = column.toString(); columnCaption = columnCaption.substring(0, 1) + columnCaption.substring(1).toLowerCase(); headerRow.createCell(column.ordinal()).setCellValue(columnCaption); } // column width sheet.setColumnWidth(EntityColumn.FIELD.ordinal(), 256 * 30); sheet.setColumnWidth(EntityColumn.TYPE.ordinal(), 256 * 30); sheet.setColumnWidth(EntityColumn.CAPTION.ordinal(), 256 * 30); sheet.setColumnWidth(EntityColumn.DESCRIPTION.ordinal(), 256 * 60); sheet.setColumnWidth(EntityColumn.REQUIRED.ordinal(), 256 * 10); sheet.setColumnWidth(EntityColumn.DISEASES.ordinal(), 256 * 45); sheet.setColumnWidth(EntityColumn.OUTBREAKS.ordinal(), 256 * 10); CellStyle defaultCellStyle = workbook.createCellStyle(); defaultCellStyle.setWrapText(true); List<Class<Enum<?>>> usedEnums = new ArrayList<Class<Enum<?>>>(); for (Field field : entityClass.getDeclaredFields()) { if (java.lang.reflect.Modifier.isStatic(field.getModifiers())) continue; XSSFRow row = sheet.createRow(rowNumber++); // field name XSSFCell fieldNameCell = row.createCell(EntityColumn.FIELD.ordinal()); fieldNameCell.setCellValue(field.getName()); // value range XSSFCell fieldValueCell = row.createCell(EntityColumn.TYPE.ordinal()); fieldValueCell.setCellStyle(defaultCellStyle); Class<?> fieldType = field.getType(); if (fieldType.isEnum()) { // use enum type name - values are added below // Object[] enumValues = fieldType.getEnumConstants(); // StringBuilder valuesString = new StringBuilder(); // for (Object enumValue : enumValues) { // if (valuesString.length() > 0) // valuesString.append(", "); // valuesString.append(((Enum) enumValue).name()); // } // fieldValueCell.setCellValue(valuesString.toString()); fieldValueCell.setCellValue(fieldType.getSimpleName()); if (!usedEnums.contains(fieldType)) { usedEnums.add((Class<Enum<?>>) fieldType); } } else if (EntityDto.class.isAssignableFrom(fieldType)) { fieldValueCell.setCellValue(fieldType.getSimpleName().replaceAll("Dto", "")); } else if (ReferenceDto.class.isAssignableFrom(fieldType)) { fieldValueCell.setCellValue(fieldType.getSimpleName().replaceAll("Dto", "")); } else if (String.class.isAssignableFrom(fieldType)) { fieldValueCell.setCellValue(I18nProperties.getCaption("text")); } else if (Date.class.isAssignableFrom(fieldType)) { fieldValueCell.setCellValue(I18nProperties.getCaption("date")); } else if (Number.class.isAssignableFrom(fieldType)) { fieldValueCell.setCellValue(I18nProperties.getCaption("number")); } else if (Boolean.class.isAssignableFrom(fieldType) || boolean.class.isAssignableFrom(fieldType)) { fieldValueCell.setCellValue(Boolean.TRUE.toString() + ", " + Boolean.FALSE.toString()); } // caption XSSFCell captionCell = row.createCell(EntityColumn.CAPTION.ordinal()); captionCell.setCellValue(I18nProperties.getPrefixCaption(i18nPrefix, field.getName(), "")); // description XSSFCell descriptionCell = row.createCell(EntityColumn.DESCRIPTION.ordinal()); descriptionCell.setCellStyle(defaultCellStyle); descriptionCell.setCellValue(I18nProperties.getPrefixDescription(i18nPrefix, field.getName(), "")); // required XSSFCell requiredCell = row.createCell(EntityColumn.REQUIRED.ordinal()); if (field.getAnnotation(Required.class) != null) requiredCell.setCellValue(true); // diseases XSSFCell diseasesCell = row.createCell(EntityColumn.DISEASES.ordinal()); diseasesCell.setCellStyle(defaultCellStyle); Diseases diseases = field.getAnnotation(Diseases.class); if (diseases != null) { StringBuilder diseasesString = new StringBuilder(); for (Disease disease : diseases.value()) { if (diseasesString.length() > 0) diseasesString.append(", "); diseasesString.append(disease.toShortString()); } diseasesCell.setCellValue(diseasesString.toString()); } else { diseasesCell.setCellValue("All"); } // outbreak XSSFCell outbreakCell = row.createCell(EntityColumn.OUTBREAKS.ordinal()); if (field.getAnnotation(Outbreaks.class) != null) outbreakCell.setCellValue(true); } AreaReference reference = workbook.getCreationHelper().createAreaReference(new CellReference(0, 0), new CellReference(rowNumber - 1, columnCount - 1)); table.setCellReferences(reference); table.getCTTable().addNewAutoFilter(); for (Class<Enum<?>> usedEnum : usedEnums) { rowNumber = createEnumTable(sheet, rowNumber + 1, usedEnum); } return sheet; }