List of usage examples for org.apache.poi.hssf.usermodel HSSFCell setCellValue
@SuppressWarnings("fallthrough") public void setCellValue(boolean value)
From source file:de.bund.bfr.knime.openkrise.db.exports.ExcelExport.java
License:Open Source License
public void doExport(final String filename, final MyDBTable myDB, final JProgressBar progress, final boolean exportFulltext, final String zeilen2Do) { //filename = "C:/Users/Armin/Documents/private/freelance/BfR/Data/100716/Matrices_BLS-Liste.xls"; Runnable runnable = new Runnable() { public void run() { try (HSSFWorkbook wb = new HSSFWorkbook()) { if (progress != null) { progress.setVisible(true); progress.setStringPainted(true); progress.setString("Exporting Excel File..."); progress.setMinimum(0); progress.setMaximum(myDB.getRowCount()); progress.setValue(0); }/*from w w w. ja va2 s .co m*/ HSSFSheet sheet = wb.createSheet(myDB.getActualTable().getTablename()); // Create Titel cs = wb.createCellStyle(); cs.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFFont font = wb.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); cs.setFont(font); HSSFRow row0 = sheet.createRow(0); //row0.setRowStyle(cs); colLfd = 0; for (int j = 0; j < myDB.getColumnCount(); j++) { if (myDB.getColumn(j).isVisible()) { HSSFCell cell = row0.createCell(colLfd); colLfd++; cell.setCellValue(myDB.getColumn(j).getColumnName()); cell.setCellStyle(cs); } } //String[] mnTable = myDB.getActualTable().getMNTable(); MyTable[] myFs = myDB.getActualTable().getForeignFields(); for (int i = 1; i <= myDB.getRowCount(); i++) { if (progress != null) progress.setValue(i); //System.out.println(myDB.getValueAt(i, 0) + "_" + myDB.isVisible()); HSSFRow rowi = sheet.createRow(i); for (int j = 0; j < myDB.getColumnCount(); j++) { Object res = null; if (j > 0 && myFs != null && myFs.length > j - 1 && myFs[j - 1] != null && myFs[j - 1].getTablename().equals("DoubleKennzahlen")) { //if (j > 0 && mnTable != null && j-1 < mnTable.length && mnTable[j - 1] != null && mnTable[j - 1].equals("DBL")) { getDblVal(myDB, i - 1, j, row0, rowi); /* getDblVal(myDB, i-1, j, "Einzelwert", row0, rowi); getDblVal(myDB, i-1, j, "Wiederholungen", row0, rowi); getDblVal(myDB, i-1, j, "Mittelwert", row0, rowi); getDblVal(myDB, i-1, j, "Median", row0, rowi); getDblVal(myDB, i-1, j, "Minimum", row0, rowi); getDblVal(myDB, i-1, j, "Maximum", row0, rowi); getDblVal(myDB, i-1, j, "Standardabweichung", row0, rowi); getDblVal(myDB, i-1, j, "LCL95", row0, rowi); getDblVal(myDB, i-1, j, "UCL95", row0, rowi); getDblVal(myDB, i-1, j, "Verteilung", row0, rowi); getDblVal(myDB, i-1, j, "Funktion (Zeit)", row0, rowi); getDblVal(myDB, i-1, j, "Funktion (?)", row0, rowi); getDblVal(myDB, i-1, j, "Undefiniert (n.d.)", row0, rowi); */ } else { if (exportFulltext) { res = myDB.getVisibleCellContent(i - 1, j); } else { res = myDB.getValueAt(i - 1, j); } //MyLogger.handleMessage(res); if (res != null) rowi.createCell(j).setCellValue(res.toString()); else rowi.createCell(j); } } } try { FileOutputStream fileOut = new FileOutputStream(filename); wb.write(fileOut); fileOut.close(); } catch (Exception e) { JOptionPane.showMessageDialog(progress, e.getMessage(), "Export Problem", JOptionPane.OK_OPTION); } if (progress != null) { progress.setValue(myDB.getRowCount()); progress.setVisible(false); } } catch (Exception e) { MyLogger.handleException(e); } } }; Thread thread = new Thread(runnable); thread.start(); }
From source file:de.bund.bfr.knime.openkrise.db.exports.ExcelExport.java
License:Open Source License
private void getDblVal(MyDBTable myDBTable, int row, int col, HSSFRow row0, HSSFRow rowi) { Object key = myDBTable.getValueAt(row, col); if (key != null) { try {//from w w w. j av a 2 s.c o m ResultSet rs = DBKernel.getResultSet("SELECT * FROM " + DBKernel.delimitL("DoubleKennzahlen") + " WHERE " + DBKernel.delimitL("ID") + "=" + key, false); if (rs != null && rs.first()) { String columnName = myDBTable.getActualTable().getFieldNames()[col - 1]; for (int i = 2; i <= rs.getMetaData().getColumnCount(); i++) { if (rs.getObject(i) != null) { if (row0 != null) { String kennzahl = rs.getMetaData().getColumnName(i); String colStr = columnName + "-" + kennzahl; int theCol; if (kennzahl.equals("Wert")) {//if (kennzahl.equals("Einzelwert")) { theCol = col; } else if (kzS.containsKey(colStr)) { theCol = kzS.get(colStr); } else { theCol = colLfd; kzS.put(colStr, theCol); HSSFCell cell = row0.createCell(theCol); colLfd++; cell.setCellValue(colStr); cell.setCellStyle(cs); } boolean is = DBKernel.kzIsString(kennzahl); boolean ib = DBKernel.kzIsBoolean(kennzahl); if (is) { rowi.createCell(theCol).setCellValue(rs.getString(i)); } else if (ib) { rowi.createCell(theCol).setCellValue(rs.getBoolean(i)); } else { rowi.createCell(theCol).setCellValue(DBKernel.getDoubleStr(rs.getObject(i))); } } } } } } catch (Exception e) { MyLogger.handleException(e); } } }
From source file:de.bund.bfr.knime.openkrise.db.imports.custom.LieferkettenImporterEFSA.java
License:Open Source License
private void transformFormat(HSSFWorkbook wb, HSSFWorkbook wbNew) { HSSFSheet transactionSheet = wb.getSheet("Transactions"); HSSFSheet businessSheet = wb.getSheet("Business_List"); int numRows = transactionSheet.getLastRowNum() + 1; HSSFSheet transactionSheetNew = wbNew.getSheet("NewTransactions"); HSSFRow newRow;//w w w . ja va 2 s .co m HSSFCell newCell; HSSFSheet lookupNew = wbNew.getSheet("NewLookUp"); HashMap<Long, HSSFRow> storedRows = new HashMap<Long, HSSFRow>(); HashMap<Long, String> storedSerials = new HashMap<Long, String>(); CRC32 crc32 = new CRC32(); CellStyle cs = wbNew.createCellStyle(); cs.setWrapText(true); int newRowLfd = 0; int i = 1; for (; i < numRows; i++) { HSSFRow row = transactionSheet.getRow(i); if (row != null) { String serial = getStrVal(row.getCell(0)); // Serial_number String contactPerson = getStrVal(row.getCell(2)); // person String adressRec = getStrVal(row.getCell(4)); // Address if ((serial == null || serial.trim().isEmpty())) { if (contactPerson != null && !contactPerson.isEmpty() || adressRec != null && !adressRec.isEmpty()) { System.err.println("serial is seriously null... " + (i + 1)); } } else { /* int index = serial.lastIndexOf("_"); if (index <= 0) { System.err.println("index error ... no '_' there... " + (i + 1)); } serial = serial.substring(0, index) + "_" + (i + 1); */ HSSFRow busRow = getRow(businessSheet, adressRec, 9); if (busRow == null) { System.err.println("Id issue on recs...Row: " + (i + 1) + "\t" + adressRec); } else { String s1 = getStrVal(busRow.getCell(1)); if (s1 == null) s1 = ""; String s2 = getStrVal(busRow.getCell(2)); if (s2 == null) s2 = ""; String s3 = getStrVal(busRow.getCell(3), 10); if (s3 == null) s3 = ""; String s4 = getStrVal(busRow.getCell(5)); if (s4 == null) s4 = ""; String s5 = getStrVal(busRow.getCell(7)); if (s5 == null) s5 = ""; adressRec = s1 + ", " + s2 + " " + s3 + ", " + s4 + ", " + s5; // =B3&", "&C3&" "&D3&", "&F3&", "&H3 } String prodNameOut = getStrVal(row.getCell(6)); // ProductName String prodNumOut = getStrVal(row.getCell(7)); // ProductNo String dayOut = getStrVal(row.getCell(8)); // Day String monthOut = getStrVal(row.getCell(9)); // Month String yearOut = getStrVal(row.getCell(10)); // Year String amountKG_Out = getStrVal(row.getCell(11)); // amountKG String typePUOut = getStrVal(row.getCell(12)); // typePU String numPUOut = getStrVal(row.getCell(13)); // numPU String lotNo_Out = getStrVal(row.getCell(14)); // String dayMHDOut = getStrVal(row.getCell(15)); String monthMHDOut = getStrVal(row.getCell(16)); String yearMHDOut = getStrVal(row.getCell(17)); // String dayPDOut = getStrVal(row.getCell(18)); String monthPDOut = getStrVal(row.getCell(19)); String yearPDOut = getStrVal(row.getCell(20)); String adressInsp = getStrVal(row.getCell(22)); // Address String activityInsp = getStrVal(row.getCell(23)); // Activity busRow = getRow(businessSheet, adressInsp, 9); if (busRow == null) { System.err.println("Id issue on insps...Row: " + (i + 1) + "\t" + adressInsp); } else { String s1 = getStrVal(busRow.getCell(1)); if (s1 == null) s1 = ""; String s2 = getStrVal(busRow.getCell(2)); if (s2 == null) s2 = ""; String s3 = getStrVal(busRow.getCell(3), 10); if (s3 == null) s3 = ""; String s4 = getStrVal(busRow.getCell(5)); if (s4 == null) s4 = ""; String s5 = getStrVal(busRow.getCell(7)); if (s5 == null) s5 = ""; adressInsp = s1 + ", " + s2 + " " + s3 + ", " + s4 + ", " + s5; // =B3&", "&C3&" "&D3&", "&F3&", "&H3 } String prodNameIn = getStrVal(row.getCell(24)); // ProductName String prodNumIn = getStrVal(row.getCell(25)); // ProductNo String dayIn = getStrVal(row.getCell(26)); // Day String monthIn = getStrVal(row.getCell(27)); // Month String yearIn = getStrVal(row.getCell(28)); // Year String amountKG_In = getStrVal(row.getCell(29)); // amountKG String typePUIn = getStrVal(row.getCell(30)); // typePU String numPUIn = getStrVal(row.getCell(31)); // numPU String lotNo_In = getStrVal(row.getCell(32)); // String dayMHDIn = getStrVal(row.getCell(33)); String monthMHDIn = getStrVal(row.getCell(34)); String yearMHDIn = getStrVal(row.getCell(35)); // String dayPDIn = getStrVal(row.getCell(36)); String monthPDIn = getStrVal(row.getCell(37)); String yearPDIn = getStrVal(row.getCell(38)); String adressSup = getStrVal(row.getCell(40)); // Address String activitySup = getStrVal(row.getCell(41)); // Activity busRow = getRow(businessSheet, adressSup, 9); if (busRow == null) { System.err.println("Id issue on susps...Row: " + (i + 1) + "\t" + adressSup); } else { String s1 = getStrVal(busRow.getCell(1)); if (s1 == null) s1 = ""; String s2 = getStrVal(busRow.getCell(2)); if (s2 == null) s2 = ""; String s3 = getStrVal(busRow.getCell(3), 10); if (s3 == null) s3 = ""; String s4 = getStrVal(busRow.getCell(5)); if (s4 == null) s4 = ""; String s5 = getStrVal(busRow.getCell(7)); if (s5 == null) s5 = ""; adressSup = s1 + ", " + s2 + " " + s3 + ", " + s4 + ", " + s5; // =B3&", "&C3&" "&D3&", "&F3&", "&H3 } String ec = getStrVal(row.getCell(42)); // EndChain String ece = getStrVal(row.getCell(43)); // Explanation_EndChain String oc = getStrVal(row.getCell(44)); // OriginCountry String cqr = getStrVal(row.getCell(45)); // Contact_Questions_Remarks String ft = getStrVal(row.getCell(46)); // Further_Traceback String ms = getStrVal(row.getCell(47)); // MicrobiologicalSample busRow = getRow(lookupNew, activityInsp, 9); String treatmentIn = null, treatmentOut = null; if (busRow != null) treatmentOut = busRow.getCell(13).getStringCellValue(); busRow = getRow(lookupNew, activitySup, 9); if (busRow != null) treatmentIn = busRow.getCell(13).getStringCellValue(); String sOut = adressInsp + "_" + prodNameOut + "_" + prodNumOut + "_" + lotNo_Out + "_" + dayPDOut + "_" + monthPDOut + "_" + yearPDOut + "_" + dayMHDOut + "_" + monthMHDOut + "_" + yearMHDOut + "_" + dayOut + "_" + monthOut + "_" + yearOut + "_" + amountKG_Out + "_" + numPUOut + "_" + typePUOut + "_" + adressRec; crc32.reset(); crc32.update(sOut.getBytes()); long crc32Out = crc32.getValue(); //System.err.println(crc32Out + " -> " + sOut); String sIn = adressSup + "_" + prodNameIn + "_" + prodNumIn + "_" + lotNo_In + "_" + dayPDIn + "_" + monthPDIn + "_" + yearPDIn + "_" + dayMHDIn + "_" + monthMHDIn + "_" + yearMHDIn + "_" + dayIn + "_" + monthIn + "_" + yearIn + "_" + amountKG_In + "_" + numPUIn + "_" + typePUIn + "_" + adressInsp; crc32.reset(); crc32.update(sIn.getBytes()); long crc32In = crc32.getValue(); //System.err.println(crc32In + " -> " + sIn); String backSerial = serial + ".1"; if (storedRows.containsKey(crc32In)) { //HSSFRow r = storedRows.get(crc32In); backSerial = r.getCell(1).getStringCellValue(); backSerial = storedSerials.get(crc32In); } if (storedRows.containsKey(crc32Out)) { HSSFRow r = storedRows.get(crc32Out); HSSFCell c = r.getCell(0); if (c == null) { c = r.createCell(0); c.setCellStyle(cs); c.setCellValue(backSerial); } else add2Cell(c, backSerial); add2Cell(r.getCell(20), contactPerson); add2Cell(r.getCell(23), oc); add2Cell(r.getCell(24), cqr); add2Cell(r.getCell(26), ms); } else { newRowLfd++; newRow = transactionSheetNew.createRow(newRowLfd); newCell = newRow.createCell(0, HSSFCell.CELL_TYPE_STRING); newCell.setCellStyle(cs); newCell.setCellValue(backSerial); newCell = newRow.createCell(1, HSSFCell.CELL_TYPE_STRING); newCell.setCellStyle(cs); newCell.setCellValue(serial + ".0"); newCell = newRow.createCell(2, HSSFCell.CELL_TYPE_STRING); newCell.setCellValue(adressInsp); newCell = newRow.createCell(3, HSSFCell.CELL_TYPE_STRING); newCell.setCellValue(prodNameOut); newCell = newRow.createCell(4, HSSFCell.CELL_TYPE_STRING); newCell.setCellValue(prodNumOut); if (treatmentOut != null) { newCell = newRow.createCell(5, HSSFCell.CELL_TYPE_STRING); newCell.setCellValue(treatmentOut); } newCell = newRow.createCell(6, HSSFCell.CELL_TYPE_STRING); newCell.setCellValue(lotNo_Out); newCell = newRow.createCell(7, HSSFCell.CELL_TYPE_STRING); newCell.setCellValue(dayPDOut); newCell = newRow.createCell(8, HSSFCell.CELL_TYPE_STRING); newCell.setCellValue(monthPDOut); newCell = newRow.createCell(9, HSSFCell.CELL_TYPE_STRING); newCell.setCellValue(yearPDOut); newCell = newRow.createCell(10, HSSFCell.CELL_TYPE_STRING); newCell.setCellValue(dayMHDOut); newCell = newRow.createCell(11, HSSFCell.CELL_TYPE_STRING); newCell.setCellValue(monthMHDOut); newCell = newRow.createCell(12, HSSFCell.CELL_TYPE_STRING); newCell.setCellValue(yearMHDOut); newCell = newRow.createCell(13, HSSFCell.CELL_TYPE_STRING); newCell.setCellValue(dayOut); newCell = newRow.createCell(14, HSSFCell.CELL_TYPE_STRING); newCell.setCellValue(monthOut); newCell = newRow.createCell(15, HSSFCell.CELL_TYPE_STRING); newCell.setCellValue(yearOut); newCell = newRow.createCell(16, HSSFCell.CELL_TYPE_STRING); newCell.setCellValue(amountKG_Out); newCell = newRow.createCell(17, HSSFCell.CELL_TYPE_STRING); newCell.setCellValue(numPUOut); newCell = newRow.createCell(18, HSSFCell.CELL_TYPE_STRING); newCell.setCellValue(typePUOut); newCell = newRow.createCell(19, HSSFCell.CELL_TYPE_STRING); newCell.setCellValue(adressRec); newCell = newRow.createCell(20, HSSFCell.CELL_TYPE_STRING); newCell.setCellStyle(cs); newCell.setCellValue(contactPerson); newCell = newRow.createCell(23, HSSFCell.CELL_TYPE_STRING); newCell.setCellStyle(cs); newCell.setCellValue(oc); newCell = newRow.createCell(24, HSSFCell.CELL_TYPE_STRING); newCell.setCellStyle(cs); newCell.setCellValue(cqr); newCell = newRow.createCell(26, HSSFCell.CELL_TYPE_STRING); newCell.setCellStyle(cs); newCell.setCellValue(ms); storedRows.put(crc32Out, newRow); storedSerials.put(crc32Out, serial + ".0"); } if (storedRows.containsKey(crc32In)) { HSSFRow r = storedRows.get(crc32In); add2Cell(r.getCell(20), contactPerson); add2Cell(r.getCell(21), ec); add2Cell(r.getCell(22), ece); add2Cell(r.getCell(23), oc); add2Cell(r.getCell(24), cqr); add2Cell(r.getCell(25), ft); add2Cell(r.getCell(26), ms); } else { newRowLfd++; newRow = transactionSheetNew.createRow(newRowLfd); newCell = newRow.createCell(1, HSSFCell.CELL_TYPE_STRING); newCell.setCellStyle(cs); newCell.setCellValue(serial + ".1"); newCell = newRow.createCell(2, HSSFCell.CELL_TYPE_STRING); newCell.setCellValue(adressSup); newCell = newRow.createCell(3, HSSFCell.CELL_TYPE_STRING); newCell.setCellValue(prodNameIn); newCell = newRow.createCell(4, HSSFCell.CELL_TYPE_STRING); newCell.setCellValue(prodNumIn); if (treatmentIn != null) { newCell = newRow.createCell(5, HSSFCell.CELL_TYPE_STRING); newCell.setCellValue(treatmentIn); } newCell = newRow.createCell(6, HSSFCell.CELL_TYPE_STRING); newCell.setCellValue(lotNo_In); newCell = newRow.createCell(7, HSSFCell.CELL_TYPE_STRING); newCell.setCellValue(dayPDIn); newCell = newRow.createCell(8, HSSFCell.CELL_TYPE_STRING); newCell.setCellValue(monthPDIn); newCell = newRow.createCell(9, HSSFCell.CELL_TYPE_STRING); newCell.setCellValue(yearPDIn); newCell = newRow.createCell(10, HSSFCell.CELL_TYPE_STRING); newCell.setCellValue(dayMHDIn); newCell = newRow.createCell(11, HSSFCell.CELL_TYPE_STRING); newCell.setCellValue(monthMHDIn); newCell = newRow.createCell(12, HSSFCell.CELL_TYPE_STRING); newCell.setCellValue(yearMHDIn); newCell = newRow.createCell(13, HSSFCell.CELL_TYPE_STRING); newCell.setCellValue(dayIn); newCell = newRow.createCell(14, HSSFCell.CELL_TYPE_STRING); newCell.setCellValue(monthIn); newCell = newRow.createCell(15, HSSFCell.CELL_TYPE_STRING); newCell.setCellValue(yearIn); newCell = newRow.createCell(16, HSSFCell.CELL_TYPE_STRING); newCell.setCellValue(amountKG_In); newCell = newRow.createCell(17, HSSFCell.CELL_TYPE_STRING); newCell.setCellValue(numPUIn); newCell = newRow.createCell(18, HSSFCell.CELL_TYPE_STRING); newCell.setCellValue(typePUIn); newCell = newRow.createCell(19, HSSFCell.CELL_TYPE_STRING); newCell.setCellValue(adressInsp); newCell = newRow.createCell(20, HSSFCell.CELL_TYPE_STRING); newCell.setCellStyle(cs); newCell.setCellValue(contactPerson); newCell = newRow.createCell(21, HSSFCell.CELL_TYPE_STRING); newCell.setCellStyle(cs); newCell.setCellValue(ec); newCell = newRow.createCell(22, HSSFCell.CELL_TYPE_STRING); newCell.setCellStyle(cs); newCell.setCellValue(ece); newCell = newRow.createCell(23, HSSFCell.CELL_TYPE_STRING); newCell.setCellStyle(cs); newCell.setCellValue(oc); newCell = newRow.createCell(24, HSSFCell.CELL_TYPE_STRING); newCell.setCellStyle(cs); newCell.setCellValue(cqr); newCell = newRow.createCell(25, HSSFCell.CELL_TYPE_STRING); newCell.setCellStyle(cs); newCell.setCellValue(ft); newCell = newRow.createCell(26, HSSFCell.CELL_TYPE_STRING); newCell.setCellStyle(cs); newCell.setCellValue(ms); storedRows.put(crc32In, newRow); storedSerials.put(crc32In, serial + ".1"); } } } } System.err.println("last row: " + i); }
From source file:de.bund.bfr.knime.openkrise.db.imports.custom.LieferkettenImporterEFSA.java
License:Open Source License
private void add2Cell(HSSFCell c, String value) { if (c != null) { String ts = c.getStringCellValue(); if (ts != null && value != null && ts.indexOf(value) < 0) c.setCellValue(ts + "\n" + value); //if (ts.length() > 5000) System.err.println(ts.length()); }// ww w . j a v a2s. co m }
From source file:de.jwic.ecolib.tableviewer.export.ExcelExportControl.java
License:Apache License
private HSSFWorkbook createWorkBook() { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet("Sheet"); HSSFRow row = sheet.createRow(0);//from w w w. j a v a 2 s . co m // Style for title cells HSSFFont font = wb.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); font.setColor(HSSFColor.BLUE.index); HSSFCellStyle styleTitle = wb.createCellStyle(); styleTitle.setFont(font); // Style for data date cells font = wb.createFont(); HSSFCellStyle styleDate = wb.createCellStyle(); styleDate.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy")); short col = 0; TableModel model = tableViewer.getModel(); Iterator<TableColumn> it = model.getColumnIterator(); // create title in the sheet while (it.hasNext()) { TableColumn column = it.next(); if (!isColumnVisible(column)) { continue; } sheet.setColumnWidth(col, (short) (column.getWidth() * 40)); HSSFCell cell = row.createCell(col++); cell.setCellValue(column.getTitle()); cell.setCellStyle(styleTitle); } // add the datas from the table viewer IContentProvider<?> contentProvider = model.getContentProvider(); Iterator<?> iter = contentProvider.getContentIterator(new Range()); try { renderRows(iter, 0, model, sheet, styleDate); } catch (Throwable t) { log.error("Error rendering rows", t); } return wb; }
From source file:de.jwic.ecolib.tableviewer.export.ExcelExportControl.java
License:Apache License
@SuppressWarnings({ "unchecked", "rawtypes" })
protected void renderRows(Iterator<?> iter, int level, TableModel model, HSSFSheet sheet,
HSSFCellStyle styleDate) {/*from w w w . j ava 2s. com*/
while (iter.hasNext()) {
short col = 0;
Object inputObj = iter.next();
HSSFRow row = sheet.createRow(sheet.getLastRowNum() + 1);
IContentProvider contentProvider = model.getContentProvider();
for (Iterator<TableColumn> it = model.getColumnIterator(); it.hasNext();) {
;
TableColumn column = it.next();
if (!isColumnVisible(column)) {
// skip column, it's not visible!
continue;
}
// call the label provider's getCellLabel method to get the
// CellLabel object
CellLabel label = null;
String rowKey = contentProvider.getUniqueKey(inputObj);
boolean expanded = model.isExpanded(rowKey);
HSSFCell cell = row.createCell(col++);
try {
label = tableViewer.getTableLabelProvider().getCellLabel(inputObj, column,
new RowContext(expanded, level));
Object obj = label.object;
// set cell text and style
if (obj != null) {
// identify special style for Date and Number
if (obj instanceof Number) {
cell.setCellValue(((Number) obj).doubleValue());
continue;
} else if (obj instanceof Date) {
cell.setCellValue((Date) obj);
cell.setCellStyle(styleDate);
continue;
} else if (obj instanceof Boolean) {
cell.setCellValue((Boolean) obj ? "Y" : "N");
continue;
}
}
String columnText = label.text;
cell.setCellValue(columnText);
} catch (Throwable t) {
cell.setCellValue(t.getMessage());
log.error("Error rendering column " + column.getTitle(), t);
}
}
// render children
if (contentProvider.hasChildren(inputObj)) {
Iterator children = contentProvider.getChildren(inputObj);
renderRows(children, level + 1, model, sheet, styleDate);
}
}
}
From source file:de.maklerpoint.office.Schnittstellen.Excel.ExportExcelXLS.java
License:Open Source License
/** * /*from w w w .java 2s . c om*/ * @throws FileNotFoundException * @throws IOException */ public void write() throws FileNotFoundException, IOException { FileOutputStream out = new FileOutputStream(new File(filename)); HSSFWorkbook wb; wb = new HSSFWorkbook(); Map<String, HSSFCellStyle> styles = createStyles(wb); HSSFSheet sheet = wb.createSheet(sheetName); //turn off gridlines sheet.setDisplayGridlines(false); sheet.setPrintGridlines(false); sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); HSSFPrintSetup printSetup = sheet.getPrintSetup(); printSetup.setLandscape(true); sheet.setAutobreaks(true); printSetup.setFitHeight((short) 1); printSetup.setFitWidth((short) 1); HSSFRow headerRow = sheet.createRow(0); headerRow.setHeightInPoints(12.75f); int[][] width = new int[titles.length][titles.length]; for (int i = 0; i < titles.length; i++) { HSSFCell cell = headerRow.createCell(i); cell.setCellValue(titles[i]); cell.setCellStyle(styles.get("header")); width[i][0] = titles[i].length(); } HSSFRow row; HSSFCell cell; int rownum = 1; for (int i = 0; i < data.length; i++, rownum++) { row = sheet.createRow(rownum); if (data[i] == null) continue; for (int j = 0; j < data[i].length; j++) { cell = row.createCell(j); if (data[i][j] == null) data[i][j] = ""; cell.setCellValue(data[i][j].toString()); if (data[i][j].toString().length() > width[j][0]) width[j][0] = data[i][j].toString().length(); } } for (int i = 0; i < titles.length; i++) { int widthShort = (256 * (width[i][0] + 3)); sheet.setColumnWidth(i, widthShort); } int position = (titles.length / 2) - 1; row = sheet.createRow(rownum + 3); cell = row.createCell(position); if (footName == null) { SimpleDateFormat df = new SimpleDateFormat("dd.MM.yyyy HH:mm"); cell.setCellValue("Export MaklerPoint vom " + df.format(new Date(System.currentTimeMillis())) + " - www.maklerpoint.de"); } else { cell.setCellValue(footName); } sheet.setZoom(3, 4); wb.write(out); out.close(); }
From source file:de.maklerpoint.office.Schnittstellen.Excel.ExportKalenderExcel.java
License:Open Source License
/** * //from www.j a v a 2 s . co m * @throws FileNotFoundException * @throws IOException */ public void write() throws FileNotFoundException, IOException { Calendar calendar = Calendar.getInstance(); int year = calendar.get(Calendar.YEAR); HSSFWorkbook wb = new HSSFWorkbook(); Map<String, HSSFCellStyle> styles = createStyles(wb); for (int month = 0; month < 12; month++) { calendar.set(Calendar.MONTH, month); calendar.set(Calendar.DAY_OF_MONTH, 1); //create a sheet for each month HSSFSheet sheet = wb.createSheet(months[month]); //turn off gridlines sheet.setDisplayGridlines(false); sheet.setPrintGridlines(false); sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); HSSFPrintSetup printSetup = sheet.getPrintSetup(); printSetup.setLandscape(true); //the following three statements are required only for HSSF sheet.setAutobreaks(true); printSetup.setFitHeight((short) 1); printSetup.setFitWidth((short) 1); //the header row: centered text in 48pt font HSSFRow headerRow = sheet.createRow(0); headerRow.setHeightInPoints(80); HSSFCell titleCell = headerRow.createCell(0); titleCell.setCellValue(months[month] + " " + year); titleCell.setCellStyle(styles.get("title")); // sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$N$1")); //header with month titles HSSFRow monthRow = sheet.createRow(1); for (int i = 0; i < days.length; i++) { //set column widths, the width is measured in units of 1/256th of a character width sheet.setColumnWidth((i * 2), (5 * 256)); //the column is 5 characters wide sheet.setColumnWidth((i * 2 + 1), (13 * 256)); //the column is 13 characters wide //sheet.addMergedRegion(new Region(1, (short) 1, i*2, (short) (i * 2 + 1))); sheet.addMergedRegion(new CellRangeAddress(1, i * 2, 1, (i * 2 + 1))); // TODO Test HSSFCell monthCell = monthRow.createCell((i * 2)); monthCell.setCellValue(days[i]); monthCell.setCellStyle(styles.get("month")); } int cnt = 1, day = 1; int rownum = 2; for (int j = 0; j < 6; j++) { HSSFRow row = sheet.createRow(rownum++); row.setHeightInPoints(100); for (int i = 0; i < days.length; i++) { HSSFCell dayCell_1 = row.createCell((i * 2)); HSSFCell dayCell_2 = row.createCell((i * 2 + 1)); int day_of_week = calendar.get(Calendar.DAY_OF_WEEK); if (cnt >= day_of_week && calendar.get(Calendar.MONTH) == month) { dayCell_1.setCellValue(day); calendar.set(Calendar.DAY_OF_MONTH, ++day); if (i == 0 || i == days.length - 1) { dayCell_1.setCellStyle(styles.get("weekend_left")); dayCell_2.setCellStyle(styles.get("weekend_right")); } else { dayCell_1.setCellStyle(styles.get("workday_left")); dayCell_2.setCellStyle(styles.get("workday_right")); } } else { dayCell_1.setCellStyle(styles.get("grey_left")); dayCell_2.setCellStyle(styles.get("grey_right")); } cnt++; } if (calendar.get(Calendar.MONTH) > month) break; } } // Write the output to a file FileOutputStream out = new FileOutputStream(this.filename); wb.write(out); out.close(); }
From source file:de.thorstenberger.taskmodel.impl.ReportBuilderImpl.java
License:Open Source License
private short createUserInfoColumns(Tasklet tasklet, short c, HSSFWorkbook wb, HSSFRow row) { UserInfo userInfo = taskManager.getUserInfo(tasklet.getUserId()); List<UserAttribute> uas = taskManager.availableUserAttributes(); String login;// www .j a v a 2 s.c o m String firstName; String name; List<String> userAttributeValues = new LinkedList<String>(); ; boolean notfound; if (userInfo != null) { login = userInfo.getLogin(); firstName = userInfo.getFirstName(); name = userInfo.getName(); for (UserAttribute ua : uas) { String s = userInfo.getUserAttributeValue(ua.getKey()); if (s == null || s.trim().length() == 0) { s = "-"; } userAttributeValues.add(s); } notfound = false; } else { login = tasklet.getUserId(); firstName = "?"; name = "?"; for (UserAttribute ua : uas) { userAttributeValues.add("?"); } notfound = true; } if (notfound) { HSSFCellStyle cs2 = wb.createCellStyle(); HSSFFont font2 = wb.createFont(); font2.setColor(HSSFColor.RED.index); cs2.setFont(font2); HSSFCell cell2 = row.createCell(c++); cell2.setCellStyle(cs2); cell2.setCellValue(login); } else { row.createCell(c++).setCellValue(login); } row.createCell(c++).setCellValue(firstName); row.createCell(c++).setCellValue(name); for (String uav : userAttributeValues) { row.createCell(c++).setCellValue(uav); } return c; }
From source file:de.viaboxx.nlstools.formats.MBExcelPersistencer.java
License:Apache License
private int writeHeaders(MBBundle bundle) throws IOException { HSSFRow headerRow = createRow();// www .j a v a 2 s . c om HSSFCell cell = headerRow.createCell(0); cell.setCellStyle(styles.get(STYLE_BOLD)); cell.setCellValue("Bundle:"); cell = headerRow.createCell(1); cell.setCellStyle(styles.get(STYLE_BOLD)); cell.setCellValue(bundle.getBaseName()); cell = headerRow.createCell(3); cell.setCellValue("Created: "); cell = headerRow.createCell(4); cell.setCellValue(new Date()); cell.setCellStyle(styles.get(STYLE_DATETIME)); headerRow = createRow(); if (null != bundle.getInterfaceName()) { cell = headerRow.createCell(0); cell.setCellStyle(styles.get(STYLE_ITALIC)); cell.setCellValue("Interface:"); cell = headerRow.createCell(1); cell.setCellStyle(styles.get(STYLE_ITALIC)); cell.setCellValue(bundle.getInterfaceName()); } if (null != bundle.getSqldomain()) { cell = headerRow.createCell(2); cell.setCellStyle(styles.get(STYLE_ITALIC)); cell.setCellValue("SQLDomain:"); cell = headerRow.createCell(3); cell.setCellStyle(styles.get(STYLE_ITALIC)); cell.setCellValue(bundle.getSqldomain()); } rowNum++; // empty row headerRow = createRow(); String[] headerCols = { "Key", "Aliases", "Description" }; for (int i = 0; i < headerCols.length; i++) { HSSFCell headerCell = headerRow.createCell(i); HSSFRichTextString text = new HSSFRichTextString(headerCols[i]); headerCell.setCellStyle(styles.get(STYLE_BOLD)); headerCell.setCellValue(text); } int colNum = headerCols.length; int firstCol = colNum; List<String> locales = bundleWriter.getLocalesUsed(); for (String each : locales) { HSSFCell headerCell = headerRow.createCell(colNum++); HSSFRichTextString text = new HSSFRichTextString(each); headerCell.setCellStyle(styles.get(STYLE_BOLD)); headerCell.setCellValue(text); } return firstCol; }