List of usage examples for org.apache.poi.ss.usermodel Sheet createFreezePane
void createFreezePane(int colSplit, int rowSplit);
From source file:balony.tableWriter.java
public static void writeTable(JFrame parent, Object[][] tableData, String[] colNames) { String opts[] = { "Tab-delimited text (raw)", "Excel .xls", "Excel 2007+ .xlsx" }; int i = JOptionPane.showOptionDialog(parent, "Choose output format:", "Export Table", JOptionPane.DEFAULT_OPTION, JOptionPane.QUESTION_MESSAGE, null, opts, opts[0]); if (i == JOptionPane.CLOSED_OPTION) { return;/* w w w.jav a2s .c o m*/ } String[] chars = { ":", "[", "]", "/", "\\", "?", "*" }; String fname = parent.getTitle(); for (String c : chars) { fname = fname.replace(c, ""); } JFileChooser jfc = new JFileChooser(); if (i == 0) { jfc.setFileFilter(new FileNameExtensionFilter("Tab-delimited text files", "txt")); jfc.setSelectedFile(new File(fname.concat(".txt"))); } if (i == 1) { jfc.setFileFilter(new FileNameExtensionFilter("Excel .xls files", "xls")); jfc.setSelectedFile(new File(fname.concat(".xls"))); } if (i == 2) { jfc.setFileFilter(new FileNameExtensionFilter("Excel .xlsx files", "xlsx")); jfc.setSelectedFile(new File(fname.concat(".xlsx"))); } int rv = jfc.showSaveDialog(parent); if (rv == JFileChooser.APPROVE_OPTION) { File f = jfc.getSelectedFile(); try { if (i == 0) { BufferedWriter out = new BufferedWriter(new FileWriter(f)); for (String columnName : colNames) { out.write(columnName); out.write("\t"); } out.newLine(); for (Object[] tableData1 : tableData) { for (int k = 0; k < colNames.length; k++) { if (tableData1[k] != null) { out.write(tableData1[k].toString()); } out.write("\t"); } out.newLine(); } out.close(); } if (i > 0) { Workbook wb; if (i == 1) { wb = new HSSFWorkbook(); } else { wb = new XSSFWorkbook(); } Sheet sheet = wb.createSheet(fname); Row r; CellStyle style; sheet.createFreezePane(0, 1); for (int j = 0; j < tableData.length; j++) { r = sheet.createRow(j + 1); for (int k = 0; k < colNames.length; k++) { if (tableData[j][k] != null) { Cell c = r.createCell(k); if (tableData[j][k] instanceof Integer) { c.setCellType(Cell.CELL_TYPE_NUMERIC); int v = ((Integer) tableData[j][k]); c.setCellValue(v); } else { if (tableData[j][k] instanceof Double) { c.setCellType(Cell.CELL_TYPE_NUMERIC); double v = ((Double) tableData[j][k]); c.setCellValue(v); } else { c.setCellType(Cell.CELL_TYPE_STRING); c.setCellValue(tableData[j][k].toString()); } } } } } r = sheet.createRow(0); style = wb.createCellStyle(); Font font = wb.createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); style.setFont(font); for (int k = 0; k < colNames.length; k++) { Cell c = r.createCell(k); c.setCellType(Cell.CELL_TYPE_STRING); c.setCellStyle(style); c.setCellValue(colNames[k]); } FileOutputStream fos = new FileOutputStream(f); wb.write(fos); fos.close(); } } catch (Exception ex) { Logger.getLogger(dataTable.class.getName()).log(Level.SEVERE, null, ex); JOptionPane.showMessageDialog(parent, "File error: ".concat(ex.getLocalizedMessage()), "File Error", JOptionPane.ERROR_MESSAGE); return; } int n = JOptionPane.showOptionDialog(parent, "File saved. Open in default application?", "Message", JOptionPane.YES_NO_OPTION, JOptionPane.QUESTION_MESSAGE, null, null, null); if (n == JOptionPane.YES_OPTION) { try { Desktop d = Desktop.getDesktop(); d.open(f); } catch (IOException e) { System.out.println(e.getLocalizedMessage()); } } } }
From source file:br.com.techne.gluonsoft.eowexport.builder.ExcelBuilder.java
License:Apache License
/** * mtodo cria bytes de documento Excel//from w w w.ja v a2 s.c o m * @param titles * @param columnIndex * @param dataRows * @param locale * @return * @throws Exception */ public static byte[] createExcelBytes(String[] titles, String[] columnIndex, List<HashMap<String, Object>> dataRows, Locale locale) throws Exception { //Workbook wb = new HSSFWorkbook(); XSSFWorkbook wb = new XSSFWorkbook(); byte[] outBytes; try { HashMap<String, CellStyle> styles = createStyles(wb); Sheet sheet = wb.createSheet("Tab 1"); //turn off gridlines sheet.setDisplayGridlines(false); sheet.setPrintGridlines(false); sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); PrintSetup 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 Row headerRow = sheet.createRow(0); headerRow.setHeightInPoints(12.75f); for (int indexColumn = 0; indexColumn < titles.length; indexColumn++) { Cell cell = headerRow.createCell(indexColumn); cell.setCellValue(titles[indexColumn]); if ((titles.length - 1) < indexColumn) { cell.setCellValue(""); } else cell.setCellValue(titles[indexColumn]); cell.setCellStyle(styles.get("header")); } //freeze the first row sheet.createFreezePane(0, 1); Row row; Cell cell; int rownum = 1;//devido constar titulo, comea do indice 1 ValueCellUtil vcutil = new ValueCellUtil(locale); for (int indexRow = 0; indexRow < dataRows.size(); indexRow++, rownum++) { row = sheet.createRow(rownum); HashMap<String, Object> dataRow = dataRows.get(indexRow); if (dataRow == null) continue; List<String> keysAttribs = null; if (columnIndex.length == 0) { keysAttribs = Arrays.asList(dataRow.keySet().toArray(new String[0])); Collections.reverse(keysAttribs); } else { keysAttribs = Arrays.asList(columnIndex); } int colCt = 0; for (String keyAttrib : keysAttribs) { cell = row.createCell(colCt); String styleName; cell.setCellValue(vcutil.parseValue(dataRow.get(keyAttrib)).toString()); //zebrando tabela if (indexRow % 2 == 0) { // even row styleName = "cell_normal_even"; } else { // odd row styleName = "cell_normal_odd"; } if (indexRow == 0) { //setando auto ajuste sheet.autoSizeColumn(colCt); } cell.setCellStyle(styles.get(styleName)); colCt++; } } sheet.setZoom(75); //75% scale // Write the output to a file // write for return byte[] ByteArrayOutputStream out = new ByteArrayOutputStream(); try { wb.write(out); outBytes = out.toByteArray(); } finally { out.close(); } } finally { wb.close(); } return outBytes; }
From source file:com.b510.excel.client.BusinessPlan.java
License:Apache License
public static void main(String[] args) throws Exception { Workbook wb;//w w w .ja va 2s . c o m if (args.length > 0 && args[0].equals("-xls")) wb = new HSSFWorkbook(); else wb = new XSSFWorkbook(); Map<String, CellStyle> styles = createStyles(wb); Sheet sheet = wb.createSheet("Business Plan"); //turn off gridlines sheet.setDisplayGridlines(false); sheet.setPrintGridlines(false); sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); PrintSetup 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 Row headerRow = sheet.createRow(0); headerRow.setHeightInPoints(12.75f); for (int i = 0; i < titles.length; i++) { Cell cell = headerRow.createCell(i); cell.setCellValue(titles[i]); cell.setCellStyle(styles.get("header")); } //columns for 11 weeks starting from 9-Jul Calendar calendar = Calendar.getInstance(); int year = calendar.get(Calendar.YEAR); calendar.setTime(fmt.parse("9-Jul")); calendar.set(Calendar.YEAR, year); for (int i = 0; i < 11; i++) { Cell cell = headerRow.createCell(titles.length + i); cell.setCellValue(calendar); cell.setCellStyle(styles.get("header_date")); calendar.roll(Calendar.WEEK_OF_YEAR, true); } //freeze the first row sheet.createFreezePane(0, 1); Row row; Cell 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); String styleName; boolean isHeader = i == 0 || data[i - 1] == null; switch (j) { case 0: if (isHeader) { styleName = "cell_b"; cell.setCellValue(Double.parseDouble(data[i][j])); } else { styleName = "cell_normal"; cell.setCellValue(data[i][j]); } break; case 1: if (isHeader) { styleName = i == 0 ? "cell_h" : "cell_bb"; } else { styleName = "cell_indented"; } cell.setCellValue(data[i][j]); break; case 2: styleName = isHeader ? "cell_b" : "cell_normal"; cell.setCellValue(data[i][j]); break; case 3: styleName = isHeader ? "cell_b_centered" : "cell_normal_centered"; cell.setCellValue(Integer.parseInt(data[i][j])); break; case 4: { calendar.setTime(fmt.parse(data[i][j])); calendar.set(Calendar.YEAR, year); cell.setCellValue(calendar); styleName = isHeader ? "cell_b_date" : "cell_normal_date"; break; } case 5: { int r = rownum + 1; String fmla = "IF(AND(D" + r + ",E" + r + "),E" + r + "+D" + r + ",\"\")"; cell.setCellFormula(fmla); styleName = isHeader ? "cell_bg" : "cell_g"; break; } default: styleName = data[i][j] != null ? "cell_blue" : "cell_normal"; } cell.setCellStyle(styles.get(styleName)); } } //group rows for each phase, row numbers are 0-based sheet.groupRow(4, 6); sheet.groupRow(9, 13); sheet.groupRow(16, 18); //set column widths, the width is measured in units of 1/256th of a character width sheet.setColumnWidth(0, 256 * 6); sheet.setColumnWidth(1, 256 * 33); sheet.setColumnWidth(2, 256 * 20); sheet.setZoom(3, 4); // Write the output to a file String file = "businessplan.xls"; if (wb instanceof XSSFWorkbook) file += "x"; FileOutputStream out = new FileOutputStream(file); wb.write(out); out.close(); }
From source file:com.dufeng.core.BusinessPlan.java
License:Apache License
public static void main(String[] args) throws Exception { Workbook wb;/* ww w. java 2 s . c o m*/ if (args.length > 0 && args[0].equals("-xls")) wb = new HSSFWorkbook(); else wb = new XSSFWorkbook(); Map<String, CellStyle> styles = createStyles(wb); Sheet sheet = wb.createSheet("Business Plan"); //turn off gridlines sheet.setDisplayGridlines(false); sheet.setPrintGridlines(false); sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); PrintSetup 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 Row headerRow = sheet.createRow(0); headerRow.setHeightInPoints(12.75f); for (int i = 0; i < titles.length; i++) { Cell cell = headerRow.createCell(i); cell.setCellValue(titles[i]); cell.setCellStyle(styles.get("header")); } //columns for 11 weeks starting from 9-Jul Calendar calendar = Calendar.getInstance(); int year = calendar.get(Calendar.YEAR); //calendar.setTime(fmt.parse("9-Jul")); calendar.setTime(new Date()); calendar.set(Calendar.YEAR, year); for (int i = 0; i < 11; i++) { Cell cell = headerRow.createCell(titles.length + i); cell.setCellValue(calendar); cell.setCellStyle(styles.get("header_date")); calendar.roll(Calendar.WEEK_OF_YEAR, true); } //freeze the first row sheet.createFreezePane(0, 1); Row row; Cell 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); String styleName; boolean isHeader = i == 0 || data[i - 1] == null; switch (j) { case 0: if (isHeader) { styleName = "cell_b"; cell.setCellValue(Double.parseDouble(data[i][j])); } else { styleName = "cell_normal"; cell.setCellValue(data[i][j]); } break; case 1: if (isHeader) { styleName = i == 0 ? "cell_h" : "cell_bb"; } else { styleName = "cell_indented"; } cell.setCellValue(data[i][j]); break; case 2: styleName = isHeader ? "cell_b" : "cell_normal"; cell.setCellValue(data[i][j]); break; case 3: styleName = isHeader ? "cell_b_centered" : "cell_normal_centered"; cell.setCellValue(Integer.parseInt(data[i][j])); break; case 4: { //calendar.setTime(fmt.parse(data[i][j])); calendar.setTime(new Date()); calendar.set(Calendar.YEAR, year); cell.setCellValue(calendar); styleName = isHeader ? "cell_b_date" : "cell_normal_date"; break; } case 5: { int r = rownum + 1; String fmla = "IF(AND(D" + r + ",E" + r + "),E" + r + "+D" + r + ",\"\")"; cell.setCellFormula(fmla); styleName = isHeader ? "cell_bg" : "cell_g"; break; } default: styleName = data[i][j] != null ? "cell_blue" : "cell_normal"; } cell.setCellStyle(styles.get(styleName)); } } //group rows for each phase, row numbers are 0-based sheet.groupRow(4, 6); sheet.groupRow(9, 13); sheet.groupRow(16, 18); //set column widths, the width is measured in units of 1/256th of a character width sheet.setColumnWidth(0, 256 * 6); sheet.setColumnWidth(1, 256 * 33); sheet.setColumnWidth(2, 256 * 20); sheet.setZoom(3, 4); // Write the output to a file String file = "E:/businessplan.xls"; if (wb instanceof XSSFWorkbook) file += "x"; FileOutputStream out = new FileOutputStream(file); wb.write(out); out.close(); }
From source file:com.github.igor_kudryashov.utils.excel.ExcelWriter.java
License:Apache License
/** * Format a table of worksheet/*from w w w .j a v a 2s . c om*/ * * @param sheet * Name of sheet * @param withHeader * <code>true</code> for create auto filter and freeze pane in * first row, otherwise <code>false</code> */ public void setAutoSizeColumns(Sheet sheet, boolean withHeader) { if (sheet.getLastRowNum() > 0) { if (withHeader) { int x = sheet.getRow(sheet.getLastRowNum()).getLastCellNum(); CellRangeAddress range = new CellRangeAddress(0, 0, 0, x - 1); sheet.setAutoFilter(range); sheet.createFreezePane(0, 1); } // auto-sizing columns if (columnWidth.containsKey(sheet.getSheetName())) { Map<Integer, Integer> width = columnWidth.get(sheet.getSheetName()); for (Map.Entry<Integer, Integer> entry : width.entrySet()) { sheet.setColumnWidth(entry.getKey(), entry.getValue()); } } } }
From source file:com.kafeidev.test.BusinessPlan.java
License:Apache License
@Test public static void main(String[] args) throws Exception { Workbook wb;/*from w w w. java2s . co m*/ if (args.length > 0 && args[0].equals("-xls")) wb = new HSSFWorkbook(); else wb = new XSSFWorkbook(); Map<String, CellStyle> styles = createStyles(wb); Sheet sheet = wb.createSheet("Business Plan"); //turn off gridlines sheet.setDisplayGridlines(false); sheet.setPrintGridlines(false); sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); PrintSetup 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 Row headerRow = sheet.createRow(0); headerRow.setHeightInPoints(12.75f); for (int i = 0; i < titles.length; i++) { Cell cell = headerRow.createCell(i); cell.setCellValue(titles[i]); cell.setCellStyle(styles.get("header")); } //columns for 11 weeks starting from 9-Nov Calendar calendar = Calendar.getInstance(); int year = calendar.get(Calendar.YEAR); // { // String inputDate = "2010-Nov-04 01:32:27"; // Date date = new SimpleDateFormat("yyyy-MMM-dd HH:mm:ss", new Locale("en,EN")).parse(inputDate); // String str= new SimpleDateFormat("dd.MMM.yyyy HH:mm:ss").format(date); // System.out.println("data:"+str); // // } calendar.setTime(fmt.parse("19-Nov")); calendar.set(Calendar.YEAR, year); for (int i = 0; i < 11; i++) { Cell cell = headerRow.createCell(titles.length + i); cell.setCellValue(calendar); cell.setCellStyle(styles.get("header_date")); calendar.roll(Calendar.WEEK_OF_YEAR, true); } //freeze the first row sheet.createFreezePane(0, 1); Row row; Cell 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); String styleName; boolean isHeader = i == 0 || data[i - 1] == null; switch (j) { case 0: if (isHeader) { styleName = "cell_b"; cell.setCellValue(Double.parseDouble(data[i][j])); } else { styleName = "cell_normal"; cell.setCellValue(data[i][j]); } break; case 1: if (isHeader) { styleName = i == 0 ? "cell_h" : "cell_bb"; } else { styleName = "cell_indented"; } cell.setCellValue(data[i][j]); break; case 2: styleName = isHeader ? "cell_b" : "cell_normal"; cell.setCellValue(data[i][j]); break; case 3: styleName = isHeader ? "cell_b_centered" : "cell_normal_centered"; cell.setCellValue(Integer.parseInt(data[i][j])); break; case 4: { calendar.setTime(fmt.parse(data[i][j])); calendar.set(Calendar.YEAR, year); cell.setCellValue(calendar); styleName = isHeader ? "cell_b_date" : "cell_normal_date"; break; } case 5: { int r = rownum + 1; String fmla = "IF(AND(D" + r + ",E" + r + "),E" + r + "+D" + r + ",\"\")"; cell.setCellFormula(fmla); styleName = isHeader ? "cell_bg" : "cell_g"; break; } default: styleName = data[i][j] != null ? "cell_blue" : "cell_normal"; } cell.setCellStyle(styles.get(styleName)); } } //group rows for each phase, row numbers are 0-based sheet.groupRow(4, 6); sheet.groupRow(9, 13); sheet.groupRow(16, 18); //set column widths, the width is measured in units of 1/256th of a character width sheet.setColumnWidth(0, 256 * 6); sheet.setColumnWidth(1, 256 * 33); sheet.setColumnWidth(2, 256 * 20); sheet.setZoom(3, 4); // Write the output to a file String file = "businessplan.xls"; if (wb instanceof XSSFWorkbook) file += "x"; FileOutputStream out = new FileOutputStream(file); wb.write(out); out.close(); }
From source file:com.springapp.mvc.BusinessPlan.java
License:Apache License
public static void main(String[] args) throws Exception { Workbook wb;//from w ww. j av a 2s. com if (args.length > 0 && args[0].equals("-xls")) wb = new HSSFWorkbook(); else wb = new XSSFWorkbook(); Map<String, CellStyle> styles = createStyles(wb); Sheet sheet = wb.createSheet("Business Plan"); //turn off gridlines sheet.setDisplayGridlines(false); sheet.setPrintGridlines(false); sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); PrintSetup 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 Row headerRow = sheet.createRow(0); headerRow.setHeightInPoints(12.75f); for (int i = 0; i < titles.length; i++) { Cell cell = headerRow.createCell(i); cell.setCellValue(titles[i]); cell.setCellStyle(styles.get("header")); } //columns for 11 weeks starting from 9-Jul Calendar calendar = Calendar.getInstance(); int year = calendar.get(Calendar.YEAR); //calendar.setTime(fmt.parse("9-11")); calendar.set(Calendar.YEAR, year); for (int i = 0; i < 11; i++) { Cell cell = headerRow.createCell(titles.length + i); cell.setCellValue(calendar); cell.setCellStyle(styles.get("header_date")); calendar.roll(Calendar.WEEK_OF_YEAR, true); } //freeze the first row sheet.createFreezePane(0, 1); Row row; Cell 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); String styleName; boolean isHeader = i == 0 || data[i - 1] == null; switch (j) { case 0: if (isHeader) { styleName = "cell_b"; cell.setCellValue(Double.parseDouble(data[i][j])); } else { styleName = "cell_normal"; cell.setCellValue(data[i][j]); } break; case 1: if (isHeader) { styleName = i == 0 ? "cell_h" : "cell_bb"; } else { styleName = "cell_indented"; } cell.setCellValue(data[i][j]); break; case 2: styleName = isHeader ? "cell_b" : "cell_normal"; cell.setCellValue(data[i][j]); break; case 3: styleName = isHeader ? "cell_b_centered" : "cell_normal_centered"; cell.setCellValue(Integer.parseInt(data[i][j])); break; case 4: { //calendar.setTime(fmt.parse(data[i][j])); calendar.set(Calendar.YEAR, year); cell.setCellValue(calendar); styleName = isHeader ? "cell_b_date" : "cell_normal_date"; break; } case 5: { int r = rownum + 1; String fmla = "IF(AND(D" + r + ",E" + r + "),E" + r + "+D" + r + ",\"\")"; cell.setCellFormula(fmla); styleName = isHeader ? "cell_bg" : "cell_g"; break; } default: styleName = data[i][j] != null ? "cell_blue" : "cell_normal"; } cell.setCellStyle(styles.get(styleName)); } } //group rows for each phase, row numbers are 0-based sheet.groupRow(4, 6); sheet.groupRow(9, 13); sheet.groupRow(16, 18); //set column widths, the width is measured in units of 1/256th of a character width sheet.setColumnWidth(0, 256 * 6); sheet.setColumnWidth(1, 256 * 33); sheet.setColumnWidth(2, 256 * 20); sheet.setZoom(3, 4); // Write the output to a file String file = "businessplan.xls"; if (wb instanceof XSSFWorkbook) file += "x"; FileOutputStream out = new FileOutputStream(file); wb.write(out); out.close(); }
From source file:de.fme.alfresco.repo.web.scripts.DeclarativeSpreadsheetWebScript.java
License:Open Source License
/** * Generates the spreadsheet, based on the properties in the header * and a callback for the body./* w w w .j av a 2 s . co m*/ */ public void generateSpreadsheet(Object resource, String format, WebScriptRequest req, Status status, Map<String, Object> model) throws IOException { Pattern qnameMunger = Pattern.compile("([A-Z][a-z]+)([A-Z].*)"); // Build up the details of the header List<Pair<QName, Boolean>> propertyDetails = buildPropertiesForHeader(resource, format, req); String[] headings = new String[propertyDetails.size()]; String[] descriptions = new String[propertyDetails.size()]; boolean[] required = new boolean[propertyDetails.size()]; for (int i = 0; i < headings.length; i++) { Pair<QName, Boolean> property = propertyDetails.get(i); if (property == null || property.getFirst() == null) { headings[i] = ""; required[i] = false; } else { QName column = property.getFirst(); required[i] = property.getSecond(); // Ask the dictionary service nicely for the details PropertyDefinition pd = dictionaryService.getProperty(column); if (pd != null && pd.getTitle() != null) { // Use the friendly titles, which may even be localised! headings[i] = pd.getTitle(); descriptions[i] = pd.getDescription(); } else { // Nothing friendly found, try to munge the raw qname into // something we can show to a user... String raw = column.getLocalName(); raw = raw.substring(0, 1).toUpperCase() + raw.substring(1); Matcher m = qnameMunger.matcher(raw); if (m.matches()) { headings[i] = m.group(1) + " " + m.group(2); } else { headings[i] = raw; } } } } // Build a list of just the properties List<QName> properties = new ArrayList<QName>(propertyDetails.size()); for (Pair<QName, Boolean> p : propertyDetails) { QName qn = null; if (p != null) { qn = p.getFirst(); } properties.add(qn); } // Output if ("csv".equals(format)) { StringWriter sw = new StringWriter(); CSVPrinter csv = new CSVPrinter(sw, CSVStrategy.EXCEL_STRATEGY); csv.println(headings); populateBody(resource, csv, properties); model.put(MODEL_CSV, sw.toString()); } else { Workbook wb; if ("xlsx".equals(format)) { wb = new XSSFWorkbook(); // TODO Properties } else { wb = new HSSFWorkbook(); // TODO Properties } // Add our header row Sheet sheet = wb.createSheet("Export"); Row hr = sheet.createRow(0); try { sheet.createFreezePane(0, 1); } catch (IndexOutOfBoundsException e) { //https://issues.apache.org/bugzilla/show_bug.cgi?id=51431 & http://stackoverflow.com/questions/6469693/apache-poi-clearing-freeze-split-panes } Font fb = wb.createFont(); fb.setBoldweight(Font.BOLDWEIGHT_BOLD); Font fi = wb.createFont(); fi.setBoldweight(Font.BOLDWEIGHT_BOLD); fi.setItalic(true); CellStyle csReq = wb.createCellStyle(); csReq.setFont(fb); CellStyle csOpt = wb.createCellStyle(); csOpt.setFont(fi); // Populate the header Drawing draw = null; for (int i = 0; i < headings.length; i++) { Cell c = hr.createCell(i); c.setCellValue(headings[i]); if (required[i]) { c.setCellStyle(csReq); } else { c.setCellStyle(csOpt); } if (headings[i].length() == 0) { sheet.setColumnWidth(i, 3 * 250); } else { sheet.setColumnWidth(i, 18 * 250); } if (descriptions[i] != null && descriptions[i].length() > 0) { // Add a description for it too if (draw == null) { draw = sheet.createDrawingPatriarch(); } ClientAnchor ca = wb.getCreationHelper().createClientAnchor(); ca.setCol1(c.getColumnIndex()); ca.setCol2(c.getColumnIndex() + 1); ca.setRow1(hr.getRowNum()); ca.setRow2(hr.getRowNum() + 2); Comment cmt = draw.createCellComment(ca); cmt.setAuthor(""); cmt.setString(wb.getCreationHelper().createRichTextString(descriptions[i])); cmt.setVisible(false); c.setCellComment(cmt); } } // Have the contents populated populateBody(resource, wb, sheet, properties); // Save it for the template ByteArrayOutputStream baos = new ByteArrayOutputStream(); wb.write(baos); model.put(MODEL_EXCEL, baos.toByteArray()); } }
From source file:de.fraunhofer.sciencedataamanager.datamanager.SearchDefinitonExecutionDataManager.java
/** * * @param searchDefinitonExecutionList//w ww . jav a 2 s . c om * @param outputStream * @throws Exception */ public void exportToExcel(LinkedList<SearchDefinitonExecution> searchDefinitonExecutionList, OutputStream outputStream) throws Exception { Workbook currentWorkBook = new HSSFWorkbook(); int currenSheetCount = 0; for (SearchDefinitonExecution searchDefinitonExecution : searchDefinitonExecutionList) { Sheet currentSheet = currentWorkBook.createSheet(); currentSheet.setFitToPage(true); currentSheet.setHorizontallyCenter(true); currentSheet.createFreezePane(0, 1); currentWorkBook.setSheetName(currenSheetCount, searchDefinitonExecution.getSystemInstance().getName()); Row headerRow = currentSheet.createRow(0); headerRow.setHeightInPoints(12.75f); headerRow.createCell(0).setCellValue("ID"); headerRow.createCell(1).setCellValue("Title"); headerRow.createCell(2).setCellValue("Identifier 1"); headerRow.createCell(3).setCellValue("Identifier 2"); headerRow.createCell(4).setCellValue("Identifier 3"); headerRow.createCell(5).setCellValue("Identifier 4"); headerRow.createCell(6).setCellValue("Url 1"); headerRow.createCell(7).setCellValue("Url 2"); headerRow.createCell(8).setCellValue("Text 1"); headerRow.createCell(9).setCellValue("Publication Name"); headerRow.createCell(10).setCellValue("Issue Name"); headerRow.createCell(11).setCellValue("Publish Date"); headerRow.createCell(12).setCellValue("Volume"); headerRow.createCell(13).setCellValue("Start Page"); headerRow.createCell(14).setCellValue("Issue Identifier"); CellStyle style = currentWorkBook.createCellStyle(); Font headerFont = currentWorkBook.createFont(); headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD); style.setBorderRight(CellStyle.BORDER_THIN); style.setRightBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderBottom(CellStyle.BORDER_THIN); style.setBottomBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderLeft(CellStyle.BORDER_THIN); style.setLeftBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderTop(CellStyle.BORDER_THIN); style.setTopBorderColor(IndexedColors.BLACK.getIndex()); style.setAlignment(CellStyle.ALIGN_CENTER); style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setFont(headerFont); headerRow.setRowStyle(style); Row currentRow = null; int rowNum = 1; for (ScientificPaperMetaInformation scientificPaperMetaInformation : searchDefinitonExecution .getScientificPaperMetaInformation()) { currentRow = currentSheet.createRow(rowNum); currentRow.createCell(0).setCellValue(scientificPaperMetaInformation.getID()); currentRow.createCell(1).setCellValue(scientificPaperMetaInformation.getTitle()); currentRow.createCell(2).setCellValue(scientificPaperMetaInformation.getIdentifier_1()); currentRow.createCell(3).setCellValue(scientificPaperMetaInformation.getIdentifier_2()); currentRow.createCell(4).setCellValue(scientificPaperMetaInformation.getIdentifier_3()); currentRow.createCell(5).setCellValue(scientificPaperMetaInformation.getIdentifier_4()); currentRow.createCell(6).setCellValue(scientificPaperMetaInformation.getUrl_1()); currentRow.createCell(7).setCellValue(scientificPaperMetaInformation.getUrl_2()); currentRow.createCell(8).setCellValue(scientificPaperMetaInformation.getText_1()); currentRow.createCell(9).setCellValue(scientificPaperMetaInformation.getSrcTitle()); currentRow.createCell(10).setCellValue(scientificPaperMetaInformation.getScrPublisherName()); currentRow.createCell(11).setCellValue(scientificPaperMetaInformation.getSrcPublicationDate()); currentRow.createCell(12).setCellValue(scientificPaperMetaInformation.getSrcVolume()); currentRow.createCell(13).setCellValue(scientificPaperMetaInformation.getSrcStartPage()); currentRow.createCell(14).setCellValue(scientificPaperMetaInformation.getScrIdentifier_1()); rowNum++; } currenSheetCount++; } currentWorkBook.write(outputStream); outputStream.close(); }
From source file:de.fraunhofer.sciencedataamanager.exampes.export.ExcelDataExport.java
/** * * @param dataToExport The objects gets all the values, which should * exported.//from w w w .j a v a 2s . c o m * @param outputStream * @throws Exception */ @Override public void export(Map<String, Map<String, List<Object>>> allConnectorsToExport, OutputStream outputStream) throws Exception { Workbook currentWorkBook = new HSSFWorkbook(); int currenSheetCount = 0; for (String currentKey : allConnectorsToExport.keySet()) { Map<String, List<Object>> dataToExport = allConnectorsToExport.get(currentKey); List<String> columns = new ArrayList<String>(dataToExport.keySet()); List<Map<String, Object>> rows = new ArrayList<Map<String, Object>>(); int size = dataToExport.values().iterator().next().size(); for (int i = 0; i < size; i++) { Map<String, Object> row = new HashMap<String, Object>(); for (String column : columns) { row.put(column, dataToExport.get(column).get(i)); } rows.add(row); } //for (SearchDefinitonExecution searchDefinitonExecution : searchDefinitonExecutionList) { Sheet currentSheet = currentWorkBook.createSheet(); currentSheet.setFitToPage(true); currentSheet.setHorizontallyCenter(true); currentSheet.createFreezePane(0, 1); currentWorkBook.setSheetName(currenSheetCount, currentKey); Row headerRow = currentSheet.createRow(0); headerRow.setHeightInPoints(12.75f); int headerColumnIndex = 0; for (String currentColumn : columns) { headerRow.createCell(headerColumnIndex).setCellValue(currentColumn); headerColumnIndex++; } CellStyle style = currentWorkBook.createCellStyle(); Font headerFont = currentWorkBook.createFont(); headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD); style.setBorderRight(CellStyle.BORDER_THIN); style.setRightBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderBottom(CellStyle.BORDER_THIN); style.setBottomBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderLeft(CellStyle.BORDER_THIN); style.setLeftBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderTop(CellStyle.BORDER_THIN); style.setTopBorderColor(IndexedColors.BLACK.getIndex()); style.setAlignment(CellStyle.ALIGN_CENTER); style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); style.setFont(headerFont); headerRow.setRowStyle(style); Row currentRow = null; int rowNum = 1; int currentColum = 0; for (Map<String, Object> currentRow2 : rows) { currentRow = currentSheet.createRow(rowNum); for (String column : columns) { if (currentRow2.get(column) != null) { currentRow.createCell(currentColum).setCellValue(currentRow2.get(column).toString()); } currentColum++; } currentColum = 0; rowNum++; } currenSheetCount++; } currentWorkBook.write(outputStream); outputStream.close(); }