List of usage examples for org.apache.poi.ss.usermodel Sheet setHorizontallyCenter
void setHorizontallyCenter(boolean value);
From source file:com.guardias.excel.CalendarToExcel.java
License:Apache License
public static void GenerateExcel(String RutaFile, Calendar calendar, String JSONContenidos, Medico AdministratorUser) throws IOException { Guardias[] lGuardias;// w w w .ja v a 2 s .c o m Gson gson = new GsonBuilder().create(); lGuardias = gson.fromJson(JSONContenidos, Guardias[].class); boolean xlsx = true; int year = calendar.get(Calendar.YEAR); int month = calendar.get(Calendar.MONTH); DateFormat _format = new SimpleDateFormat("yyyy-MM-dd"); Workbook wb = xlsx ? new XSSFWorkbook() : new HSSFWorkbook(); Map<String, CellStyle> styles = createStyles(wb); calendar.set(Calendar.MONTH, month); calendar.set(Calendar.DAY_OF_MONTH, 1); calendar.setFirstDayOfWeek(Calendar.MONDAY); //create a sheet for each month Sheet sheet = wb.createSheet(_format.format(calendar.getTime())); CellStyle styleBORDER = wb.createCellStyle(); styleBORDER.setBorderRight(CellStyle.BORDER_THICK); styleBORDER.setBorderBottom(CellStyle.BORDER_THICK); styleBORDER.setBorderTop(CellStyle.BORDER_THICK); styleBORDER.setBorderLeft(CellStyle.BORDER_THICK); styleBORDER.setRightBorderColor(IndexedColors.LIGHT_ORANGE.getIndex()); styleBORDER.setLeftBorderColor(IndexedColors.LIGHT_ORANGE.getIndex()); styleBORDER.setTopBorderColor(IndexedColors.LIGHT_ORANGE.getIndex()); styleBORDER.setBottomBorderColor(IndexedColors.LIGHT_ORANGE.getIndex()); // //turn off gridlines sheet.setDisplayGridlines(true); sheet.autoSizeColumn(0); sheet.setPrintGridlines(true); sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); PrintSetup printSetup = sheet.getPrintSetup(); printSetup.setLandscape(true); //header with month titles Row monthRow = sheet.createRow(1); Font fontH = wb.createFont(); CellStyle CStyleH = wb.createCellStyle(); CStyleH.setBorderRight(CellStyle.BORDER_THICK); CStyleH.setRightBorderColor(IndexedColors.LIGHT_ORANGE.getIndex()); fontH.setBold(true); CStyleH.setFont(fontH); for (int i = 0; i < days.length; i++) { Cell monthCell = monthRow.createCell(i); monthCell.setCellStyle(CStyleH); monthCell.setCellValue(days[i]); sheet.autoSizeColumn(i); } int cnt = 1, day = 1; int rownum = 2; for (int j = 0; j < 6; j++) { Row row = sheet.createRow(rownum++); Row rowGuardias; boolean bRowsCreated = false; // row.setHeightInPoints(100); for (int i = 0; i < days.length; i++) { Cell dayCell_1 = row.createCell(i); // Cell dayCell_2 = row.createCell(i*2 + 1); int currentDayOfWeek = (calendar.get(Calendar.DAY_OF_WEEK) + 7 - calendar.getFirstDayOfWeek()) % 7; //int day_of_week = calendar.get(Calendar.DAY_OF_WEEK); if (cnt > currentDayOfWeek && calendar.get(Calendar.MONTH) == month) { Font font = wb.createFont(); CellStyle CStyle = wb.createCellStyle(); short colorI = HSSFColor.AQUA.index; // presencia //font.set(colorI); CStyle.setFillForegroundColor(colorI); CStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); //CStyle.setBorderBottom( colorBorder); CStyle.setBorderRight(CellStyle.BORDER_THICK); CStyle.setBorderBottom(CellStyle.BORDER_THICK); CStyle.setBorderTop(CellStyle.BORDER_THICK); CStyle.setBorderLeft(CellStyle.BORDER_THICK); CStyle.setRightBorderColor(IndexedColors.LIGHT_ORANGE.getIndex()); CStyle.setLeftBorderColor(IndexedColors.LIGHT_ORANGE.getIndex()); CStyle.setTopBorderColor(IndexedColors.LIGHT_ORANGE.getIndex()); CStyle.setBottomBorderColor(IndexedColors.LIGHT_ORANGE.getIndex()); //CStyle.setFont(font); dayCell_1.setCellValue(day); dayCell_1.setCellStyle(CStyle); sheet.autoSizeColumn(i); String _Dia = _format.format(calendar.getTime()); int DataRowCont = 1; // esto sirve para coger la fila de los datos de cada dia for (int d = 0; d < lGuardias.length; d++) { Guardias oGuardias = lGuardias[d]; if (oGuardias.getDiaGuardia().equals(_Dia)) { if (!bRowsCreated) rowGuardias = sheet.createRow(rownum++); else rowGuardias = sheet.getRow(row.getRowNum() + DataRowCont); Cell dayCell_1_GUARDIAS = rowGuardias.createCell(i); // Cell dayCell_2_GUARDIAS = rowGuardias.createCell(i*2 + 1); List<Medico> _lMedico = MedicoDBImpl.getMedicos(oGuardias.getIdMedico(), AdministratorUser.getServicioId()); Medico _oMedico = _lMedico.get(0); font = wb.createFont(); CStyle = wb.createCellStyle(); // PRESENCIA // LOCALIZADA //XSSFRichTextString richString = new HSSFRichTextString(_oMedico.getApellidos() + " " + _oMedico.getNombre()); colorI = HSSFColor.LIGHT_ORANGE.index; // presencia if (oGuardias.getTipo().equals(Util.eTipoGuardia.LOCALIZADA.toString().toLowerCase())) colorI = HSSFColor.GREEN.index; else if (oGuardias.getTipo() .equals(Util.eTipoGuardia.REFUERZO.toString().toLowerCase())) colorI = HSSFColor.BLUE.index; else if (oGuardias.getTipo().equals("")) // residente colorI = HSSFColor.RED.index; font.setColor(colorI); CStyle.setFont(font); //CStyle.setBorderBottom( colorBorder); CStyle.setBorderRight(CellStyle.BORDER_THICK); CStyle.setRightBorderColor(IndexedColors.LIGHT_ORANGE.getIndex()); dayCell_1_GUARDIAS.setCellValue(_oMedico.getApellidos() + " " + _oMedico.getNombre() + "[" + _oMedico.getIDMEDICO() + "]"); dayCell_1_GUARDIAS.setCellStyle(CStyle); DataRowCont++; } } bRowsCreated = true; // dayCell_1_GUARDIAS.setCellValue(TextoGuardias.toString()); 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 String file = RutaFile; FileOutputStream out = new FileOutputStream(file); wb.write(out); out.close(); wb.close(); }
From source file:com.inet.web.service.mail.utils.ExportUtils.java
License:Open Source License
/** * //from w w w.j av a 2 s. c o m * @param users * @param domain * @return * @throws WebOSException */ public static byte[] exportAccount(List<LdapUser> users, String domain) throws WebOSException { try { ByteArrayOutputStream output = new ByteArrayOutputStream(); Workbook workbook = new HSSFWorkbook(); //Map<String, CellStyle> styles = createStyles(workbook); Sheet sheet = workbook.createSheet("Email list"); PrintSetup printSetup = sheet.getPrintSetup(); printSetup.setLandscape(true); sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); for (int i = 0; i < users.size(); i++) { writeRecordAccount(sheet, users.get(i), i + 1); } workbook.write(output); output.close(); return output.toByteArray(); } catch (Exception e) { e.printStackTrace(); throw new WebOSException(e.getMessage(), e); } }
From source file:com.inet.web.service.mail.utils.ExportUtils.java
License:Open Source License
/** * // w ww . j a va 2 s . com * @param key * @return * @throws WebOSException */ public static byte[] exportErrorAccount(String key) throws WebOSException { AccountImport accountImport = AccountImportCacheService.get(key); if (accountImport == null) { return null; } try { ByteArrayOutputStream output = new ByteArrayOutputStream(); Workbook workbook = new HSSFWorkbook(); Sheet sheet = workbook.createSheet("Email list"); PrintSetup printSetup = sheet.getPrintSetup(); printSetup.setLandscape(true); sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); Row headerRow = sheet.createRow(0); headerRow.setHeightInPoints(30); headerRow.createCell(STT).setCellValue("STT"); headerRow.createCell(FULL_NAME).setCellValue("FULL NAME"); headerRow.createCell(USER).setCellValue("USER"); headerRow.createCell(LAST_NAME).setCellValue("LAST NAME"); headerRow.createCell(MIDDLE_NAME).setCellValue("MIDDLE NAME"); headerRow.createCell(FIRST_NAME).setCellValue("FIRST NAME"); headerRow.createCell(STATUS).setCellValue("STATUS"); headerRow.createCell(DUPLICATE).setCellValue("DUPLICATE"); for (int i = 0; i < accountImport.getError().size(); i++) { AccountImportInfo account = accountImport.getError().get(i); Row row = sheet.createRow(i + 1); row.setHeightInPoints(40); row.createCell(STT).setCellValue(account.getNumber()); row.createCell(FULL_NAME).setCellValue(account.getFullName()); row.createCell(USER).setCellValue(account.getAccount()); row.createCell(LAST_NAME).setCellValue(account.getLastName()); row.createCell(MIDDLE_NAME).setCellValue(account.getMiddleName()); row.createCell(FIRST_NAME).setCellValue(account.getFirstName()); row.createCell(STATUS).setCellValue(getStatus(account.getStatus())); row.createCell(DUPLICATE).setCellValue(account.getExistAccount()); } workbook.write(output); output.close(); return output.toByteArray(); } catch (Exception e) { e.printStackTrace(); throw new WebOSException(e.getMessage(), e); } }
From source file:com.inet.web.service.spi.download.ExportEmailWriterSpiService.java
License:Open Source License
/** * Export email of domain/*from ww w . j a v a2 s .co m*/ * * @param contacts * @return * @throws WebOSException */ private byte[] exportDomain(AccountExport accountExport) throws WebOSException { try { Workbook workbook = new HSSFWorkbook(); // style CellStyle style = workbook.createCellStyle(); style.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index); // sheet config Sheet sheet = workbook.createSheet("Email list"); PrintSetup printSetup = sheet.getPrintSetup(); printSetup.setLandscape(true); sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); writeHeaderEmail(sheet); Map<LdapGroup, List<AccountExportInfo>> map = accountExport.getMap(); for (LdapGroup group : map.keySet()) { writeGroup(sheet, group, accountExport.getDomain(), map.get(group), style); } ByteArrayOutputStream output = new ByteArrayOutputStream(); workbook.write(output); output.close(); return output.toByteArray(); } catch (Exception e) { e.printStackTrace(); throw new WebOSException(e.getMessage(), e); } }
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 .ja v a2s . 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-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.lufs.java.apache.poi.example.CalendarDemo.java
License:Apache License
public static void main(String[] args) throws Exception { Calendar calendar = Calendar.getInstance(); boolean xlsx = true; for (String arg : args) { if (arg.charAt(0) == '-') { xlsx = arg.equals("-xlsx"); } else {//from w w w. ja v a 2s.com calendar.set(Calendar.YEAR, Integer.parseInt(arg)); } } int year = calendar.get(Calendar.YEAR); try (Workbook wb = xlsx ? new XSSFWorkbook() : new HSSFWorkbook()) { Map<String, CellStyle> 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 Sheet sheet = wb.createSheet(months[month]); //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(80); Cell 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 Row 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 CellRangeAddress(1, 1, i * 2, i * 2 + 1)); Cell 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++) { Row row = sheet.createRow(rownum++); row.setHeightInPoints(100); for (int i = 0; i < days.length; i++) { Cell dayCell_1 = row.createCell(i * 2); Cell 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 String file = "calendar.xls"; if (wb instanceof XSSFWorkbook) file += "x"; try (FileOutputStream out = new FileOutputStream(file)) { wb.write(out); } } }
From source file:com.report.template.LoanCalculator.java
License:Apache License
public static void main(String[] args) throws Exception { Workbook wb;/*w ww . jav a2 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("Loan Calculator"); sheet.setPrintGridlines(false); sheet.setDisplayGridlines(false); PrintSetup printSetup = sheet.getPrintSetup(); printSetup.setLandscape(true); sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); sheet.setColumnWidth(0, 3 * 256); sheet.setColumnWidth(1, 3 * 256); sheet.setColumnWidth(2, 11 * 256); sheet.setColumnWidth(3, 14 * 256); sheet.setColumnWidth(4, 14 * 256); sheet.setColumnWidth(5, 14 * 256); sheet.setColumnWidth(6, 14 * 256); createNames(wb); Row titleRow = sheet.createRow(0); titleRow.setHeightInPoints(35); for (int i = 1; i <= 7; i++) { titleRow.createCell(i).setCellStyle(styles.get("title")); } Cell titleCell = titleRow.getCell(2); titleCell.setCellValue("Simple Loan Calculator"); sheet.addMergedRegion(CellRangeAddress.valueOf("$C$1:$H$1")); Row row = sheet.createRow(2); Cell cell = row.createCell(4); cell.setCellValue("Enter values"); cell.setCellStyle(styles.get("item_right")); row = sheet.createRow(3); cell = row.createCell(2); cell.setCellValue("Loan amount"); cell.setCellStyle(styles.get("item_left")); cell = row.createCell(4); cell.setCellValue("123123"); cell.setCellStyle(styles.get("input_$")); cell.setAsActiveCell(); row = sheet.createRow(4); cell = row.createCell(2); cell.setCellValue("Annual interest rate"); cell.setCellStyle(styles.get("item_left")); cell = row.createCell(4); cell.setCellStyle(styles.get("input_%")); row = sheet.createRow(5); cell = row.createCell(2); cell.setCellValue("Loan period in years"); cell.setCellStyle(styles.get("item_left")); cell = row.createCell(4); cell.setCellStyle(styles.get("input_i")); row = sheet.createRow(6); cell = row.createCell(2); cell.setCellValue("Start date of loan"); cell.setCellStyle(styles.get("item_left")); cell = row.createCell(4); cell.setCellStyle(styles.get("input_d")); row = sheet.createRow(8); cell = row.createCell(2); cell.setCellValue("Monthly payment"); cell.setCellStyle(styles.get("item_left")); cell = row.createCell(4); cell.setCellFormula("IF(Values_Entered,Monthly_Payment,\"\")"); cell.setCellStyle(styles.get("formula_$")); row = sheet.createRow(9); cell = row.createCell(2); cell.setCellValue("Number of payments"); cell.setCellStyle(styles.get("item_left")); cell = row.createCell(4); cell.setCellFormula("IF(Values_Entered,Loan_Years*12,\"\")"); cell.setCellStyle(styles.get("formula_i")); row = sheet.createRow(10); cell = row.createCell(2); cell.setCellValue("Total interest"); cell.setCellStyle(styles.get("item_left")); cell = row.createCell(4); cell.setCellFormula("IF(Values_Entered,Total_Cost-Loan_Amount,\"\")"); cell.setCellStyle(styles.get("formula_$")); row = sheet.createRow(11); cell = row.createCell(2); cell.setCellValue("Total cost of loan"); cell.setCellStyle(styles.get("item_left")); cell = row.createCell(4); cell.setCellFormula("IF(Values_Entered,Monthly_Payment*Number_of_Payments,\"\")"); cell.setCellStyle(styles.get("formula_$")); // Write the output to a file String file = "loan-calculator.xls"; if (wb instanceof XSSFWorkbook) file += "x"; FileOutputStream out = new FileOutputStream(file); wb.write(out); out.close(); }
From source file:com.solidmaps.webapp.report.TimesheetDemo.java
License:Apache License
public void main(String[] args) throws Exception { Workbook wb = new HSSFWorkbook(); Map<String, CellStyle> styles = createStyles(wb); Sheet sheet = wb.createSheet("Timesheet"); PrintSetup printSetup = sheet.getPrintSetup(); printSetup.setLandscape(true);/* w w w . j a va 2 s .c o m*/ sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); //title row Row titleRow = sheet.createRow(0); titleRow.setHeightInPoints(45); Cell titleCell = titleRow.createCell(0); titleCell.setCellValue("Weekly Timesheet"); titleCell.setCellStyle(styles.get("title")); sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$L$1")); //header row Row headerRow = sheet.createRow(1); headerRow.setHeightInPoints(40); Cell headerCell; for (int i = 0; i < titles.length; i++) { headerCell = headerRow.createCell(i); headerCell.setCellValue(titles[i]); headerCell.setCellStyle(styles.get("header")); } int rownum = 2; for (int i = 0; i < 10; i++) { Row row = sheet.createRow(rownum++); for (int j = 0; j < titles.length; j++) { Cell cell = row.createCell(j); if (j == 9) { //the 10th cell contains sum over week days, e.g. SUM(C3:I3) String ref = "C" + rownum + ":I" + rownum; cell.setCellFormula("SUM(" + ref + ")"); cell.setCellStyle(styles.get("formula")); } else if (j == 11) { cell.setCellFormula("J" + rownum + "-K" + rownum); cell.setCellStyle(styles.get("formula")); } else { cell.setCellStyle(styles.get("cell")); } } } //row with totals below Row sumRow = sheet.createRow(rownum++); sumRow.setHeightInPoints(35); Cell cell; cell = sumRow.createCell(0); cell.setCellStyle(styles.get("formula")); cell = sumRow.createCell(1); cell.setCellValue("Total Hrs:"); cell.setCellStyle(styles.get("formula")); for (int j = 2; j < 12; j++) { cell = sumRow.createCell(j); String ref = (char) ('A' + j) + "3:" + (char) ('A' + j) + "12"; cell.setCellFormula("SUM(" + ref + ")"); if (j >= 9) cell.setCellStyle(styles.get("formula_2")); else cell.setCellStyle(styles.get("formula")); } rownum++; sumRow = sheet.createRow(rownum++); sumRow.setHeightInPoints(25); cell = sumRow.createCell(0); cell.setCellValue("Total Regular Hours"); cell.setCellStyle(styles.get("formula")); cell = sumRow.createCell(1); cell.setCellFormula("L13"); cell.setCellStyle(styles.get("formula_2")); sumRow = sheet.createRow(rownum++); sumRow.setHeightInPoints(25); cell = sumRow.createCell(0); cell.setCellValue("Total Overtime Hours"); cell.setCellStyle(styles.get("formula")); cell = sumRow.createCell(1); cell.setCellFormula("K13"); cell.setCellStyle(styles.get("formula_2")); //set sample data for (int i = 0; i < sample_data.length; i++) { Row row = sheet.getRow(2 + i); for (int j = 0; j < sample_data[i].length; j++) { if (sample_data[i][j] == null) continue; if (sample_data[i][j] instanceof String) { row.getCell(j).setCellValue((String) sample_data[i][j]); } else { row.getCell(j).setCellValue((Double) sample_data[i][j]); } } } //finally set column widths, the width is measured in units of 1/256th of a character width sheet.setColumnWidth(0, 30 * 256); //30 characters wide for (int i = 2; i < 9; i++) { sheet.setColumnWidth(i, 6 * 256); //6 characters wide } sheet.setColumnWidth(10, 10 * 256); //10 characters wide // Write the output to a file String file = "/home/brunorocca/Desktop/mapa_teste.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;/*ww w. j a va 2s .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-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:com.vincestyling.apkinfoextractor.core.export.ExportToExcel.java
License:Apache License
@Override public void export() throws Exception { Workbook wb = new HSSFWorkbook(); Map<String, CellStyle> styles = createStyles(wb); Sheet sheet = wb.createSheet(Constancts.APP_NAME); sheet.setHorizontallyCenter(true); sheet.setFitToPage(true);/*from ww w .jav a 2 s . c o m*/ Row titleRow = sheet.createRow(0); titleRow.setHeightInPoints(45); Cell titleCell = titleRow.createCell(0); titleCell.setCellValue( " File generated by ApkInfoExtractor (https://github.com/vince-styling/ApkInfoExtractor), Copyright (C) 2014 Vince Styling"); titleCell.setCellStyle(styles.get("title")); Row headerRow = sheet.createRow(1); headerRow.setHeightInPoints(40); int cellNum = 0; String[] fields = solution.getExtractFields().split(","); for (String field : fields) { if (field.equals(Constancts.ICON)) continue; Cell headerCell = headerRow.createCell(cellNum); headerCell.setCellValue(field); headerCell.setCellStyle(styles.get("header")); sheet.setColumnWidth(cellNum, ApkInfo.getFieldCharacterCount(field) * 256); cellNum++; } int rowNum = 2; for (int i = 0; i < solution.getResultCount(); i++) { ResultDataProvider provider = solution.getResultList().get(i); postProgress(i + 1); cellNum = 0; Row row = sheet.createRow(rowNum++); for (String field : fields) { if (field.equals(Constancts.ICON)) continue; Cell cell = row.createCell(cellNum); cell.setCellStyle(styles.get("cell")); String value = getFieldValue(provider.getApkInfo(), field); cell.setCellValue(value); cellNum++; } row.setHeight((short) (5 * 256)); } File outputFile = new File(solution.getWorkingFolder(), solution.generateOutputFileName() + ".xls"); FileOutputStream out = new FileOutputStream(outputFile); wb.write(out); out.close(); callback.onProcessSuccess(outputFile); }