List of usage examples for org.apache.poi.ss.usermodel Workbook createSheet
Sheet createSheet(String sheetname);
From source file:com.jslsolucoes.tagria.lib.grid.exporter.impl.ExcelExporter.java
License:Apache License
public void doExport(OutputStream out) throws IOException { Workbook workbook = new HSSFWorkbook(); Sheet sheet = workbook.createSheet("data"); header(sheet, workbook);// w w w . j a va2s.c o m body(sheet, workbook); workbook.write(out); }
From source file:com.kafeidev.test.BusinessPlan.java
License:Apache License
@Test public static void main(String[] args) throws Exception { Workbook wb; if (args.length > 0 && args[0].equals("-xls")) wb = new HSSFWorkbook(); else/*from w w w . j av a2 s.c om*/ 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.linus.excel.poi.MergingCells.java
License:Apache License
public static void main(String[] args) throws Exception { Workbook wb = new XSSFWorkbook(); //or new HSSFWorkbook(); Sheet sheet = wb.createSheet("new sheet"); Row row = sheet.createRow((short) 1); Cell cell = row.createCell((short) 1); cell.setCellValue(new XSSFRichTextString("This is a test of merging")); sheet.addMergedRegion(new CellRangeAddress(1, 1, 1, 2)); // Write the output to a file FileOutputStream fileOut = new FileOutputStream("excel/merging_cells.xlsx"); wb.write(fileOut);//from ww w .java 2 s. c o m fileOut.close(); }
From source file:com.lushapp.common.excel.ExportExcel.java
License:Apache License
private void exportExcelInUserModel(String title, Class<T> pojoClass, Collection<T> dataSet, OutputStream out) { try {//from www . ja v a 2 s . co m // ?? if (dataSet == null || dataSet.size() == 0) { throw new Exception("??"); } if (title == null || out == null || pojoClass == null) { throw new Exception("???"); } // Workbook workbook = new HSSFWorkbook(); // ? Sheet sheet = workbook.createSheet(title); // List<String> exportFieldTitle = new ArrayList<String>(); List<Integer> exportFieldWidth = new ArrayList<Integer>(); // ???get List<Method> methodObj = new ArrayList<Method>(); Map<String, Method> convertMethod = new HashMap<String, Method>(); // Field fileds[] = pojoClass.getDeclaredFields(); // ??filed for (int i = 0; i < fileds.length; i++) { Field field = fileds[i]; Excel excel = field.getAnnotation(Excel.class); // annottion if (excel != null) { // exportFieldTitle.add(excel.exportName()); // exportFieldWidth.add(excel.exportFieldWidth()); // ? String fieldname = field.getName(); // System.out.println(i+""+excel.exportName()+" "+excel.exportFieldWidth()); StringBuffer getMethodName = new StringBuffer("get"); getMethodName.append(fieldname.substring(0, 1).toUpperCase()); getMethodName.append(fieldname.substring(1)); Method getMethod = pojoClass.getMethod(getMethodName.toString(), new Class[] {}); methodObj.add(getMethod); if (excel.exportConvert() == true) { StringBuffer getConvertMethodName = new StringBuffer("get"); getConvertMethodName.append(fieldname.substring(0, 1).toUpperCase()); getConvertMethodName.append(fieldname.substring(1)); getConvertMethodName.append("Convert"); Method getConvertMethod = pojoClass.getMethod(getConvertMethodName.toString(), new Class[] {}); convertMethod.put(getMethodName.toString(), getConvertMethod); } } } int index = 0; // Row row = sheet.createRow(index); for (int i = 0, exportFieldTitleSize = exportFieldTitle.size(); i < exportFieldTitleSize; i++) { Cell cell = row.createCell(i); // cell.setCellStyle(style); RichTextString text = new HSSFRichTextString(exportFieldTitle.get(i)); cell.setCellValue(text); } // ? for (int i = 0; i < exportFieldWidth.size(); i++) { // 256=65280/255 sheet.setColumnWidth(i, 256 * exportFieldWidth.get(i)); } Iterator its = dataSet.iterator(); // ?? while (its.hasNext()) { // index++; row = sheet.createRow(index); Object t = its.next(); for (int k = 0, methodObjSize = methodObj.size(); k < methodObjSize; k++) { Cell cell = row.createCell(k); Method getMethod = methodObj.get(k); Object value = null; if (convertMethod.containsKey(getMethod.getName())) { Method cm = convertMethod.get(getMethod.getName()); value = cm.invoke(t, new Object[] {}); } else { value = getMethod.invoke(t, new Object[] {}); } cell.setCellValue(value == null ? "" : value.toString()); } } workbook.write(out); } catch (Exception e) { e.printStackTrace(); } }
From source file:com.lw.common.utils.ExcelUtil.java
/** * excel// w w w.jav a 2s . c o m * @param list ? * @param keys listmapkey? * @param columnNames excel?? * */ public static Workbook createWorkBook(List<Map<String, Object>> list, String[] keys, String columnNames[]) { // excel Workbook wb = new HSSFWorkbook(); // sheet?? Sheet sheet = wb.createSheet(list.get(0).get("sheetName").toString()); // ???n? for (int i = 0; i < keys.length; i++) { sheet.setColumnWidth((short) i, (short) (35.7 * 150)); } // Row row = sheet.createRow((short) 0); // ??? CellStyle cs = wb.createCellStyle(); CellStyle cs2 = wb.createCellStyle(); // ? Font f = wb.createFont(); Font f2 = wb.createFont(); // ???? f.setFontHeightInPoints((short) 10); f.setColor(IndexedColors.BLACK.getIndex()); f.setBoldweight(Font.BOLDWEIGHT_BOLD); // ?? f2.setFontHeightInPoints((short) 10); f2.setColor(IndexedColors.BLACK.getIndex()); // Font f3=wb.createFont(); // f3.setFontHeightInPoints((short) 10); // f3.setColor(IndexedColors.RED.getIndex()); // ????? cs.setFont(f); cs.setBorderLeft(CellStyle.BORDER_THIN); cs.setBorderRight(CellStyle.BORDER_THIN); cs.setBorderTop(CellStyle.BORDER_THIN); cs.setBorderBottom(CellStyle.BORDER_THIN); cs.setAlignment(CellStyle.ALIGN_CENTER); // ??? cs2.setFont(f2); cs2.setBorderLeft(CellStyle.BORDER_THIN); cs2.setBorderRight(CellStyle.BORDER_THIN); cs2.setBorderTop(CellStyle.BORDER_THIN); cs2.setBorderBottom(CellStyle.BORDER_THIN); cs2.setAlignment(CellStyle.ALIGN_CENTER); //?? for (int i = 0; i < columnNames.length; i++) { Cell cell = row.createCell(i); cell.setCellValue(columnNames[i]); cell.setCellStyle(cs); } //?? for (short i = 1; i < list.size(); i++) { // Row ,Cell , Row Cell 0 // sheet Row row1 = sheet.createRow((short) i); // row for (short j = 0; j < keys.length; j++) { Cell cell = row1.createCell(j); cell.setCellValue(list.get(i).get(keys[j]) == null ? " " : list.get(i).get(keys[j]).toString()); cell.setCellStyle(cs2); } } return wb; }
From source file:com.lwr.software.reporter.restservices.ReportExportService.java
License:Open Source License
public Response exportExcel(Report toExport, Set<ReportParameter> reportParams) { Workbook wb = new XSSFWorkbook(); Font boldFont = wb.createFont(); boldFont.setBold(true);// ww w . j av a 2 s .c o m CellStyle headerStyle = wb.createCellStyle(); headerStyle.setBorderBottom(BorderStyle.THIN); headerStyle.setBorderTop(BorderStyle.THIN); headerStyle.setBorderRight(BorderStyle.THIN); headerStyle.setBorderLeft(BorderStyle.THIN); headerStyle.setFillBackgroundColor(IndexedColors.BLUE.getIndex()); headerStyle.setFont(boldFont); CellStyle cellStyle = wb.createCellStyle(); cellStyle.setBorderBottom(BorderStyle.THIN); cellStyle.setBorderTop(BorderStyle.THIN); cellStyle.setBorderRight(BorderStyle.THIN); cellStyle.setBorderLeft(BorderStyle.THIN); CellStyle titleStyle = wb.createCellStyle(); titleStyle.setBorderBottom(BorderStyle.THIN); titleStyle.setBorderTop(BorderStyle.THIN); titleStyle.setBorderRight(BorderStyle.THIN); titleStyle.setBorderLeft(BorderStyle.THIN); List<RowElement> rows = toExport.getRows(); int sheetIndex = 0; for (RowElement rowElement : rows) { List<Element> elements = rowElement.getElements(); for (Element element : elements) { try { element.setParams(reportParams); element.init(); } catch (Exception e) { logger.error("Unable to init '" + element.getTitle() + "' element of report '" + toExport.getTitle() + "' Error " + e.getMessage(), e); return Response.serverError().entity("Unable to init '" + element.getTitle() + "' element of report '" + toExport.getTitle() + "' Error " + e.getMessage()).build(); } String sheetName = element.getTitle().substring(0, element.getTitle().length() > 30 ? 30 : element.getTitle().length()) + (sheetIndex++); Sheet sheet = wb.createSheet(sheetName); Row reportTitleRow = sheet.createRow(0); Cell reportTitleHeader = reportTitleRow.createCell(0); reportTitleHeader.setCellStyle(headerStyle); reportTitleHeader.setCellValue("Report Title:"); Cell reportTitleCell = reportTitleRow.createCell(1); reportTitleCell.setCellStyle(titleStyle); reportTitleCell.setCellValue(toExport.getTitle()); Row elementTitleRow = sheet.createRow(1); Cell elementTitleHeader = elementTitleRow.createCell(0); elementTitleHeader.setCellStyle(headerStyle); elementTitleHeader.setCellValue("Element Title:"); Cell elementTitleCell = elementTitleRow.createCell(1); elementTitleCell.setCellStyle(titleStyle); elementTitleCell.setCellValue(element.getTitle()); List<List<Object>> dataToExport = element.getData(); int rowIndex = 3; Row headerRow = sheet.createRow(rowIndex++); List<Object> unifiedHeaderRow = element.getHeader(); for (int i = 0; i < unifiedHeaderRow.size(); i++) { Cell headerCell = headerRow.createCell(i); String headerCellValue = unifiedHeaderRow.get(i).toString(); headerCell.setCellValue(headerCellValue); headerCell.setCellStyle(headerStyle); } for (int i = 0; i < dataToExport.size(); i++) { Row row = sheet.createRow(rowIndex++); List<Object> unifiedRow = dataToExport.get(i); int cellIndex = 0; for (Object cellValue : unifiedRow) { Cell cell = row.createCell(cellIndex); cell.setCellStyle(cellStyle); try { double val = Double.parseDouble(cellValue.toString()); cell.setCellValue(val); } catch (NumberFormatException e) { cell.setCellValue(cellValue.toString()); } cellIndex++; } } } } try { File file = new File(DashboardConstants.APPLN_TEMP_DIR + System.nanoTime()); logger.info("Export CSV temp file path is " + file.getAbsoluteFile()); wb.write(new FileOutputStream(file)); wb.close(); ResponseBuilder responseBuilder = Response.ok((Object) file); responseBuilder.header("Content-Type", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); responseBuilder.header("Content-Transfer-Encoding", "binary"); responseBuilder.header("Content-Disposition", "attachment;filename=" + file.getName()); responseBuilder.header("Content-Length", file.length()); Response responseToSend = responseBuilder.build(); file.deleteOnExit(); return responseToSend; } catch (Exception e1) { return Response.serverError() .entity("Unable to export " + toExport.getTitle() + " report " + e1.getMessage()).build(); } }
From source file:com.md.mdcms.xlsx.CSVtoXLSX.java
License:Open Source License
/** * @param args// ww w . j a v a 2 s .c om */ public static void main(String[] args) { // check if correct number of arguments were passed if (args.length != 23) { System.out.println("Invalid number of Parameters passed"); System.out.println("Parameters expected = 23"); System.out.println("Parameters passed = " + args.length); System.out.println("Expected parameters: "); System.out.println("1) csv file path"); System.out.println("2) excel file path"); System.out.println("3) Header1"); System.out.println("4) Header2"); System.out.println("5) Header3"); System.out.println("6) Header4"); System.out.println("7) Header5"); System.out.println("8) Header6"); System.out.println("9) Header7"); System.out.println("10) Header8"); System.out.println("11) Header9"); System.out.println("12) Footer1"); System.out.println("13) Footer2"); System.out.println("14) Footer3"); System.out.println("15) Footer4"); System.out.println("16) Footer5"); System.out.println("17) Footer6"); System.out.println("18) Field Types1"); System.out.println("19) Field Types2"); System.out.println("20) Field Types3"); System.out.println("21) Field Types4"); System.out.println("22) Date order"); System.out.println("23) Date Separator"); System.exit(1); } try { System.setProperty("java.awt.headless", "true"); File csvFile = new File(args[0]); File xlsFile = new File(args[1]); String dateOrder = args[21]; String dateSep = args[22]; // prep CSV String lineIn; BufferedReader br = new BufferedReader(new FileReader(csvFile)); // Workbook Settings Workbook wb = new XSSFWorkbook(); Sheet sheet = wb.createSheet("Sheet1"); // wb.setLocale(new Locale("en", "EN")); // WritableWorkbook workbook = Workbook.createWorkbook(xlsFile, ws); // WritableSheet sheet = workbook.createSheet("Table1", 0); // SheetSettings settings = new SheetSettings(sheet); // settings.setFitToPages(true); // settings.setPaperSize(PaperSize.A4); // settings.setOrientation(PageOrientation.LANDSCAPE); // set cell formats // arial9Format.setShrinkToFit(true); // arial9Format.setWrap(true); // arial9TotalIntegerFormat.setShrinkToFit(true); // arial9TotalIntegerFormat.setBorder(Border.TOP, BorderLineStyle.DOUBLE); // arial9TotalFloatFormat.setShrinkToFit(true); // arial9TotalFloatFormat.setBorder(Border.TOP, BorderLineStyle.DOUBLE); // colHeaderFormat.setBackground(Colour.GREY_25_PERCENT); // colHeaderFormat.setWrap(true); // colHeaderFormat.setShrinkToFit(true); // colHeaderFormat.setVerticalAlignment(VerticalAlignment.TOP); // floatFormat.setShrinkToFit(true); // floatFormat.setWrap(true); // integerFormat.setShrinkToFit(true); // integerFormat.setWrap(true); // headerFormat.setBackground(Colour.GREY_25_PERCENT); // headerFormat.setShrinkToFit(true); // footerFormat.setBackground(Colour.GREY_25_PERCENT); // footerFormat.setShrinkToFit(true); // cell(column, row) int colnr = 0; int rownr = 0; int firstHeaderRow = 0; // Headers String[] header = { args[2], args[3], args[4], args[5], args[6], args[7], args[8], args[9], args[10] }; int lastHeaderRow = firstHeaderRow; boolean headerFound = false; for (int j = 8; j > -1; j--) { if (!"".equals(header[j].trim()) || (headerFound)) { Label label = new Label(0, j + firstHeaderRow, header[j].replaceAll("\\s+$", ""), headerFormat); sheet.addCell(label); if (!headerFound) { headerFound = true; lastHeaderRow = j + firstHeaderRow; } } } // fill field type list String fieldTypes = args[17].trim() + args[18].trim() + args[19].trim() + args[20].trim(); String[] fieldType = fieldTypes.split(","); // table data int columnHeadingRow = lastHeaderRow; if (headerFound) { columnHeadingRow += 2; } rownr = columnHeadingRow; int firstDataRow = 0; int lastDataRow = 0; String[] char13 = { "m", "w", "A", "B", "C", "D", "E", "G", "H", "K", "M", "N", "O", "P", "Q", "R", "S", "U", "V", "W" }; double factor = 1.0; int width; double w; double charWidth; String value; // loop through CSV lines lineIn = br.readLine(); while (lineIn != null && !"".equals(lineIn)) { String[] fields = lineIn.split("\t"); // loop through columns in line for (int i = 0; (i < fields.length && i < fieldType.length); i++) { if (!fieldType[i].substring(0, 1).equals("E")) { value = fields[i]; value = value.replaceAll("\"", "").trim(); // column heading if (rownr == columnHeadingRow) { factor = 1.3; Label label = new Label(colnr, rownr, value, colHeaderFormat); sheet.addCell(label); } // column data else { factor = 1; if (firstDataRow == 0) { firstDataRow = rownr; } lastDataRow = rownr; // date field if (fieldType[i].equals("D")) { if (value.length() == 6) { if (dateOrder.equals("DMY")) { value = value.substring(4, 6) + dateSep + value.substring(2, 4) + dateSep + value.substring(0, 2); } else { if (dateOrder.equals("MDY")) { value = value.substring(2, 4) + dateSep + value.substring(4, 6) + dateSep + value.substring(0, 2); } else { value = value.substring(0, 2) + dateSep + value.substring(2, 4) + dateSep + value.substring(4, 6); } } } if (value.length() == 8) { if (dateOrder.equals("DMY")) { value = value.substring(6, 8) + dateSep + value.substring(4, 6) + dateSep + value.substring(0, 4); } else { if (dateOrder.equals("MDY")) { value = value.substring(2, 4) + dateSep + value.substring(4, 6) + dateSep + value.substring(0, 4); } else { value = value.substring(0, 4) + dateSep + value.substring(4, 6) + dateSep + value.substring(6, 8); } } } Label label = new Label(colnr, rownr, value, arial9Format); sheet.addCell(label); } // floating point field if (fieldType[i].substring(0, 1).equals("F")) { try { double doubleValue = Double.valueOf(value).doubleValue(); Number number = new Number(colnr, rownr, doubleValue, floatFormat); sheet.addCell(number); } catch (Exception e) { Label label = new Label(colnr, rownr, value, arial9Format); sheet.addCell(label); } } // integer field if (fieldType[i].substring(0, 1).equals("I")) { try { int integerValue = Integer.valueOf(value).intValue(); Number number = new Number(colnr, rownr, integerValue, integerFormat); sheet.addCell(number); } catch (Exception e) { Label label = new Label(colnr, rownr, value, arial9Format); sheet.addCell(label); } } // string field if (fieldType[i].equals("S")) { Label label = new Label(colnr, rownr, value, arial9Format); sheet.addCell(label); } } // calculate cell width and add column number w = 1; for (int j = 0; j < value.length(); j++) { charWidth = 1; for (int k = 0; k < char13.length; k++) { if (char13[k].equals(value.substring(j, j + 1))) { charWidth = 1.3; k = char13.length; } } w = w + (charWidth * factor); } width = Double.valueOf(String.valueOf(w)).intValue(); if (width > 80) { width = 80; } concludeColumnWidth(colnr, width); colnr++; } } lineIn = br.readLine(); colnr = 0; rownr++; } for (Iterator iterator = columnWidth.keySet().iterator(); iterator.hasNext();) { Integer col = (Integer) iterator.next(); sheet.setColumnView(col.intValue(), ((Integer) columnWidth.get(col)).intValue()); } // total row colnr = 0; int columnCount = 5; for (int i = 0; i < fieldType.length; i++) { if (!fieldType[i].substring(0, 1).equals("E")) { if (fieldType[i].length() > 1) { if (fieldType[i].substring(1, 2).equals("T")) { String firstCell; String lastCell; firstCell = CellReferenceHelper.getCellReference(colnr, firstDataRow); lastCell = CellReferenceHelper.getCellReference(colnr, lastDataRow); value = "SUM(" + firstCell + ":" + lastCell + ")"; if (fieldType[i].substring(0, 1).equals("F")) { Formula formula = new Formula(colnr, rownr, value, arial9TotalFloatFormat); sheet.addCell(formula); } else { Formula formula = new Formula(colnr, rownr, value, arial9TotalIntegerFormat); sheet.addCell(formula); } } } colnr++; if (colnr > columnCount) { columnCount = colnr; } } } // merge the header cells if (headerFound) { for (int i = firstHeaderRow; i <= lastHeaderRow; i++) { sheet.mergeCells(0, i, columnCount - 1, i); } } // Footers String[] footer = { args[11], args[12], args[13], args[14], args[15], args[16] }; boolean footerFound = false; rownr++; for (int j = 5; j > -1; j--) { if (!"".equals(footer[j].trim()) || (footerFound)) { Label label = new Label(0, rownr + j, footer[j].replaceAll("\\s+$", ""), footerFormat); sheet.addCell(label); sheet.mergeCells(0, rownr + j, columnCount - 1, rownr + j); footerFound = true; } } // write workbook to file if (xlsFile.exists()) { xlsFile.delete(); } FileOutputStream fileOut = new FileOutputStream(xlsFile); wb.write(fileOut); fileOut.close(); } catch (UnsupportedEncodingException e) { System.out.println(e.toString()); System.exit(1); } catch (IOException e) { System.out.println(e.toString()); System.exit(1); } catch (Exception e) { System.out.println(e.toString()); System.exit(1); } }
From source file:com.miraisolutions.xlconnect.App.java
License:Open Source License
public static void main(String[] args) throws Exception { String file = "/home/mstuder/test.xlsx"; File f = new File(file); if (f.exists()) f.delete();/*from www. j a v a 2 s . com*/ Workbook wb = Workbook.getWorkbook(f, true); wb.setStyleAction(StyleAction.DATATYPE); CellStyle cs = wb.createCellStyle(); cs.setDataFormat("d/m/yy"); wb.setCellStyleForDataType(DataType.DateTime, cs); DataFrame df = new DataFrame(); boolean[] missing = new boolean[] { false, false, false, false, false }; Date date = new Date(); df.addColumn("A", new Column(new double[] { 1.0, 2.0, 3.0, 4.0, 5.0 }, missing, DataType.Numeric)); df.addColumn("B", new Column(new Date[] { date, date, date, date, date }, missing, DataType.DateTime)); wb.createSheet("data"); wb.writeWorksheet(df, "data", true); wb.save(); printDataFrame(df); }
From source file:com.mto.excel.model.MergeWorkbook.java
License:Open Source License
public MergeWorkbook(Workbook workbook, int rowOffset) { this.workbook = workbook; this.sheet = workbook.createSheet("Merge"); this.helper = workbook.getCreationHelper(); this.rowOffset = rowOffset; }
From source file:com.mycompany.excelreadandwrite.WritetoExcel.java
public void writeSongsListToExcel(List<Song> songList) { /*//ww w. ja v a 2s . c o m Use XSSF for xlsx format and for xls use HSSF */ Workbook workbook = new XSSFWorkbook(); /* create new sheet */ Sheet songsSheet = workbook.createSheet("Albums"); XSSFCellStyle my_style = (XSSFCellStyle) workbook.createCellStyle(); /* Create XSSFFont object from the workbook */ XSSFFont my_font = (XSSFFont) workbook.createFont(); /* setting cell color */ CellStyle style = workbook.createCellStyle(); style.setFillForegroundColor(IndexedColors.LEMON_CHIFFON.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); /* setting Header color */ CellStyle style2 = workbook.createCellStyle(); style2.setFillForegroundColor(IndexedColors.DARK_RED.getIndex()); style2.setFillPattern(CellStyle.SOLID_FOREGROUND); Row rowName = songsSheet.createRow(1); /* Merging the cells */ songsSheet.addMergedRegion(new CellRangeAddress(1, 1, 2, 3)); /* Applying style to attribute name */ int nameCellIndex = 1; Cell namecell = rowName.createCell(nameCellIndex++); namecell.setCellValue("Name"); namecell.setCellStyle(style); Cell cel = rowName.createCell(nameCellIndex++); cel.setCellValue("Lastname, Firstname"); /* Applying underline to Name */ my_font.setUnderline(XSSFFont.U_SINGLE); my_style.setFont(my_font); /* Attaching the style to the cell */ CellStyle combined = workbook.createCellStyle(); combined.cloneStyleFrom(my_style); combined.cloneStyleFrom(style); cel.setCellStyle(combined); /* Applying colors to header */ Row rowMain = songsSheet.createRow(3); SheetConditionalFormatting sheetCF = songsSheet.getSheetConditionalFormatting(); ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("3"); PatternFormatting fill1 = rule1.createPatternFormatting(); fill1.setFillBackgroundColor(IndexedColors.CORNFLOWER_BLUE.index); fill1.setFillPattern(PatternFormatting.SOLID_FOREGROUND); CellRangeAddress[] regions = { CellRangeAddress.valueOf("A4:G4") }; sheetCF.addConditionalFormatting(regions, rule1); /* setting new rule to apply alternate colors to cells having same Genre */ ConditionalFormattingRule rule2 = sheetCF.createConditionalFormattingRule("4"); PatternFormatting fill2 = rule2.createPatternFormatting(); fill2.setFillBackgroundColor(IndexedColors.LEMON_CHIFFON.index); fill2.setFillPattern(PatternFormatting.SOLID_FOREGROUND); CellRangeAddress[] regionsAction = { CellRangeAddress.valueOf("A5:G5"), CellRangeAddress.valueOf("A6:G6"), CellRangeAddress.valueOf("A7:G7"), CellRangeAddress.valueOf("A8:G8"), CellRangeAddress.valueOf("A13:G13"), CellRangeAddress.valueOf("A14:G14"), CellRangeAddress.valueOf("A15:G15"), CellRangeAddress.valueOf("A16:G16"), CellRangeAddress.valueOf("A23:G23"), CellRangeAddress.valueOf("A24:G24"), CellRangeAddress.valueOf("A25:G25"), CellRangeAddress.valueOf("A26:G26") }; /* setting new rule to apply alternate colors to cells having same Genre */ ConditionalFormattingRule rule3 = sheetCF.createConditionalFormattingRule("4"); PatternFormatting fill3 = rule3.createPatternFormatting(); fill3.setFillBackgroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.index); fill3.setFillPattern(PatternFormatting.SOLID_FOREGROUND); CellRangeAddress[] regionsAdv = { CellRangeAddress.valueOf("A9:G9"), CellRangeAddress.valueOf("A10:G10"), CellRangeAddress.valueOf("A11:G11"), CellRangeAddress.valueOf("A12:G12"), CellRangeAddress.valueOf("A17:G17"), CellRangeAddress.valueOf("A18:G18"), CellRangeAddress.valueOf("A19:G19"), CellRangeAddress.valueOf("A20:G20"), CellRangeAddress.valueOf("A21:G21"), CellRangeAddress.valueOf("A22:G22"), CellRangeAddress.valueOf("A27:G27"), CellRangeAddress.valueOf("A28:G28"), CellRangeAddress.valueOf("A29:G29") }; /* Applying above created rule formatting to cells */ sheetCF.addConditionalFormatting(regionsAction, rule2); sheetCF.addConditionalFormatting(regionsAdv, rule3); /* Setting coloumn header values */ int mainCellIndex = 0; rowMain.createCell(mainCellIndex++).setCellValue("SNO"); rowMain.createCell(mainCellIndex++).setCellValue("Genre"); rowMain.createCell(mainCellIndex++).setCellValue("Rating"); rowMain.createCell(mainCellIndex++).setCellValue("Movie Name"); rowMain.createCell(mainCellIndex++).setCellValue("Director"); rowMain.createCell(mainCellIndex++).setCellValue("Release Date"); rowMain.createCell(mainCellIndex++).setCellValue("Budget"); /* populating cell values */ int rowIndex = 4; int sno = 1; for (Song song : songList) { if (song.getSno() != 0) { Row row = songsSheet.createRow(rowIndex++); int cellIndex = 0; /* first place in row is Sno */ row.createCell(cellIndex++).setCellValue(sno++); /* second place in row is Genre */ row.createCell(cellIndex++).setCellValue(song.getGenre()); /* third place in row is Critic score */ row.createCell(cellIndex++).setCellValue(song.getCriticscore()); /* fourth place in row is Album name */ row.createCell(cellIndex++).setCellValue(song.getAlbumname()); /* fifth place in row is Artist */ row.createCell(cellIndex++).setCellValue(song.getArtist()); /* sixth place in row is marks in date */ if (song.getReleasedate() != null) { Cell date = row.createCell(cellIndex++); DataFormat format = workbook.createDataFormat(); CellStyle dateStyle = workbook.createCellStyle(); dateStyle.setDataFormat(format.getFormat("dd-MMM-yyyy")); date.setCellStyle(dateStyle); date.setCellValue(song.getReleasedate()); /* auto-resizing columns */ songsSheet.autoSizeColumn(6); songsSheet.autoSizeColumn(5); songsSheet.autoSizeColumn(4); songsSheet.autoSizeColumn(3); songsSheet.autoSizeColumn(2); } } } /* writing this workbook to excel file. */ try { FileOutputStream fos = new FileOutputStream(FILE_PATH); workbook.write(fos); fos.close(); System.out.println(FILE_PATH + " is successfully written"); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } }