List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet autoSizeColumn
@Override public void autoSizeColumn(int column, boolean useMergedCells)
This process can be relatively slow on large sheets, so this should normally only be called once per column, at the end of your processing.
You can specify whether the content of merged cells should be considered or ignored.
From source file:com.dawg6.web.dhcalc.server.ExportExcel.java
License:Open Source License
private void addSkillSummary(HSSFSheet skillSummary) { createTableHeader(skillSummary, 0, "Skill"); createTableHeader(skillSummary, 1, "# Attacks"); createTableHeader(skillSummary, 2, "Per Attack"); createTableHeader(skillSummary, 3, "Total"); createTableHeader(skillSummary, 4, "DPS"); createTableHeader(skillSummary, 5, "% of Total"); double total = 0; for (Damage d : data.output.damages) { total += d.actualDamage;// ww w . j a va 2 s . com } int i = 1; for (Map.Entry<DamageSource, DamageHolder> e : data.skillDamages.entrySet()) { Row row = skillSummary.createRow(i++); ActiveSkill skill = e.getKey().skill; GemSkill gem = e.getKey().gem; DamageHolder d = e.getValue(); addTableCell(row, 0, (skill != null) ? skill.getLongName() : gem.getDisplayName()); addTableCell(row, 1, d.attacks); addTableCell(row, 2, Math.round((d.damage) / d.attacks)); addTableCell(row, 3, d.damage); addTableCell(row, 4, Math.round((d.damage) / data.output.duration)); addTableCell(row, 5, Math.round(10000.0 * d.damage / total) / 10000.0, pctStyle); } for (i = 0; i < 6; i++) { skillSummary.autoSizeColumn(i, true); } }
From source file:com.dawg6.web.dhcalc.server.ExportExcel.java
License:Open Source License
private void addShooterSummary(HSSFSheet shooterSummary) { createTableHeader(shooterSummary, 0, "Shooter"); createTableHeader(shooterSummary, 1, "# Attacks"); createTableHeader(shooterSummary, 2, "Per Attack"); createTableHeader(shooterSummary, 3, "Total"); createTableHeader(shooterSummary, 4, "DPS"); createTableHeader(shooterSummary, 5, "% of Total"); double total = 0; for (Damage d : data.output.damages) { total += d.actualDamage;/*from w w w . j a v a 2 s. c o m*/ } int i = 1; for (Map.Entry<String, DamageHolder> e : data.shooterDamages.entrySet()) { Row row = shooterSummary.createRow(i++); DamageHolder d = e.getValue(); addTableCell(row, 0, e.getKey()); addTableCell(row, 1, d.attacks); addTableCell(row, 2, Math.round((d.damage) / d.attacks)); addTableCell(row, 3, d.damage); addTableCell(row, 4, Math.round((d.damage) / data.output.duration)); addTableCell(row, 5, Math.round(10000.0 * d.damage / total) / 10000.0, pctStyle); } for (i = 0; i < 6; i++) { shooterSummary.autoSizeColumn(i, true); } }
From source file:com.dawg6.web.dhcalc.server.ExportExcel.java
License:Open Source License
private void addTypeSummary(HSSFSheet typeSummary) { createTableHeader(typeSummary, 0, "Type"); createTableHeader(typeSummary, 1, "# Attacks"); createTableHeader(typeSummary, 2, "Per Attack"); createTableHeader(typeSummary, 3, "Total"); createTableHeader(typeSummary, 4, "DPS"); createTableHeader(typeSummary, 5, "% of Total"); double total = 0; for (Damage d : data.output.damages) { total += d.actualDamage;// w w w. j a v a 2s . c om } int i = 1; for (Map.Entry<DamageType, DamageHolder> e : data.types.entrySet()) { Row row = typeSummary.createRow(i++); DamageHolder d = e.getValue(); addTableCell(row, 0, e.getKey().name()); addTableCell(row, 1, d.attacks); addTableCell(row, 2, Math.round((d.damage) / d.attacks)); addTableCell(row, 3, d.damage); addTableCell(row, 4, Math.round((d.damage) / data.output.duration)); addTableCell(row, 5, Math.round(10000.0 * d.damage / total) / 10000.0, pctStyle); } for (i = 0; i < 6; i++) { typeSummary.autoSizeColumn(i, true); } }
From source file:com.dawg6.web.dhcalc.server.ExportExcel.java
License:Open Source License
private void addDamageLog(HSSFSheet damageLog) { int col = 0;//ww w .ja v a 2 s . c o m createTableHeader(damageLog, col++, "Time"); createTableHeader(damageLog, col++, "Shooter"); createTableHeader(damageLog, col++, "Skill"); createTableHeader(damageLog, col++, "Rune"); createTableHeader(damageLog, col++, "Type"); createTableHeader(damageLog, col++, "+/- Hatred"); createTableHeader(damageLog, col++, "Hatred"); createTableHeader(damageLog, col++, "+/- Disc"); createTableHeader(damageLog, col++, "Disc"); createTableHeader(damageLog, col++, "Damage"); createTableHeader(damageLog, col++, "Target HP"); createTableHeader(damageLog, col++, "% HP"); createTableHeader(damageLog, col++, "Target"); createTableHeader(damageLog, col++, "Notes"); createTableHeader(damageLog, col++, "Calculations"); double total = 0; for (Damage d : data.output.damages) { total += d.actualDamage; } for (int i = 0; i < data.output.damages.length; i++) { Damage d = data.output.damages[i]; Row row = damageLog.createRow(i + 1); col = 0; addTableCellD(row, col++, Math.round(d.time * 100.0) / 100.0); addTableCell(row, col++, d.shooter); if (d.source != null) { ActiveSkill skill = d.source.skill; GemSkill gem = d.source.gem; Rune rune = d.source.rune; addTableCell(row, col++, (skill != null) ? skill.getLongName() : gem.getDisplayName()); addTableCell(row, col++, (rune != null) ? rune.getLongName() : "N/A"); } else { col += 2; } if (d.type != null) { addTableCell(row, col++, d.type.name()); } else { col++; } if (d.hatred != 0) { addTableCellD(row, col++, Math.round(d.hatred * 10.0) / 10.0); } else { col++; } addTableCellD(row, col++, Math.round(d.currentHatred * 10.0) / 10.0); if (d.disc != 0) { addTableCellD(row, col++, Math.round(d.disc * 10.0) / 10.0); } else { col++; } addTableCellD(row, col++, Math.round(d.currentDisc * 10.0) / 10.0); if (d.damage > 0) { addTableCell(row, col++, Math.round(d.damage)); addTableCell(row, col++, (double) Math.round(d.targetHp)); addTableCell(row, col++, Math.round(d.targetHpPercent * 1000.0) / 10.0 + "%"); } else { col += 3; } if (d.target != null) { addTableCell(row, col++, d.target.toString()); } else { col += 2; } if (d.note != null) { addTableCell(row, col++, d.note); } else { col++; } if (d.log != null) { addTableCell(row, col++, d.log); } else { col++; } } for (int i = 0; i < 12; i++) { damageLog.autoSizeColumn(i, true); } }
From source file:com.eryansky.core.excelTools.JsGridReportBase.java
License:Apache License
/** * //w w w . j a v a 2s. c o m * @param * @return void */ private void generateColumn(HSSFSheet sheet, TableColumn tc, int maxlevel, int rownum, int colnum, HSSFCellStyle headerstyle) { HSSFRow row = sheet.getRow(rownum); if (row == null) row = sheet.createRow(rownum); HSSFCell cell = row.createCell(colnum); cell.setCellValue(tc.getDisplay()); if (headerstyle != null) cell.setCellStyle(headerstyle); if (tc.isComplex()) { CellRangeAddress address = new CellRangeAddress(rownum, rownum, colnum, colnum + tc.getLength() - 1); sheet.addMergedRegion(address); fillMergedRegion(sheet, address, headerstyle); int cn = colnum; for (int i = 0; i < tc.getChildren().size(); i++) { if (i != 0) { cn = cn + tc.getChildren().get(i - 1).getLength(); } generateColumn(sheet, tc.getChildren().get(i), maxlevel, rownum + 1, cn, headerstyle); } } else { CellRangeAddress address = new CellRangeAddress(rownum, rownum + maxlevel - tc.level, colnum, colnum); sheet.addMergedRegion(address); fillMergedRegion(sheet, address, headerstyle); } sheet.autoSizeColumn(colnum, true); }
From source file:com.eryansky.core.excelTools.JsGridReportBase.java
License:Apache License
/** * /* ww w . jav a 2 s .com*/ * @param wb Excel * @param title Sheet?? * @param styles ? * @param creator * @param tableData ? * @throws Exception */ public HSSFWorkbook writeSheet(HSSFWorkbook wb, String title, HashMap<String, HSSFCellStyle> styles, String creator, TableData tableData) throws Exception { SimpleDateFormat formater = new SimpleDateFormat("yyyyMMdd HHmm"); String create_time = formater.format(new Date()); HSSFSheet sheet = wb.createSheet(title);// Excel sheet.setDisplayGridlines(false);// ? HSSFRow row = sheet.createRow(0);// HSSFCell cell = row.createCell(0);// int rownum = 0; cell.setCellValue(new HSSFRichTextString(title)); HSSFCellStyle style = styles.get("TITLE"); if (style != null) cell.setCellStyle(style); TableHeaderMetaData headerMetaData = tableData.getTableHeader();// HTML sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, headerMetaData.getColumnCount() - 1)); row = sheet.createRow(1); cell = row.createCell(0); cell.setCellValue(new HSSFRichTextString(":")); style = styles.get("SUB_TITLE"); if (style != null) cell.setCellStyle(style); cell = row.createCell(1); cell.setCellValue(new HSSFRichTextString(creator)); style = styles.get("SUB_TITLE2"); if (style != null) cell.setCellStyle(style); cell = row.createCell(2); cell.setCellValue(new HSSFRichTextString(":")); style = styles.get("SUB_TITLE"); if (style != null) cell.setCellStyle(style); cell = row.createCell(3); style = styles.get("SUB_TITLE2"); cell.setCellValue(new HSSFRichTextString(create_time)); if (style != null) cell.setCellStyle(style); rownum = 3;// rownum = 1? HSSFCellStyle headerstyle = styles.get("TABLE_HEADER"); System.out.println(JsonMapper.getInstance().toJson(headerMetaData)); int colnum = 0; for (int i = 0; i < headerMetaData.getOriginColumns().size(); i++) { TableColumn tc = headerMetaData.getOriginColumns().get(i); if (i != 0) { colnum += headerMetaData.getOriginColumns().get(i - 1).getLength(); } generateColumn(sheet, tc, headerMetaData.maxlevel, rownum, colnum, headerstyle); } rownum += headerMetaData.maxlevel; List<TableDataRow> dataRows = tableData.getRows(); HashMap<Integer, Integer> counter = new HashMap<Integer, Integer>(); HashMap<Integer, String> word = new HashMap<Integer, String>(); int index = 0; for (TableDataRow dataRow : dataRows) { row = sheet.createRow(rownum); List<TableDataCell> dataCells = dataRow.getCells(); int size = headerMetaData.getColumns().size(); index = -1; for (int i = 0; i < size; i++) { TableColumn tc = headerMetaData.getColumns().get(i); if (!tc.isVisible()) continue; index++; String value = dataCells.get(i).getValue(); if (tc.isGrouped()) { String w = word.get(index); if (w == null) { word.put(index, value); counter.put(index, 1); createCell(row, tc, dataCells, i, index, styles); } else { if (w.equals(value)) { counter.put(index, counter.get(index) + 1); } else { stopGrouping(sheet, word, counter, index, size, rownum, styles.get("STRING")); word.put(index, value); counter.put(index, 1); createCell(row, tc, dataCells, i, index, styles); } } } else { createCell(row, tc, dataCells, i, index, styles); } } rownum++; } stopGrouping(sheet, word, counter, 0, index, rownum, styles.get("STRING")); // ??? for (int c = 0; c < headerMetaData.getColumns().size(); c++) { sheet.autoSizeColumn((short) c, true); } sheet.setGridsPrinted(true); return wb; }
From source file:demo.admin.controller.UserController.java
@RequestMapping(value = "/user/downloadData") @VerifyAuthentication(Trader = true, Admin = true, Operation = true) public HttpEntity<byte[]> downloadUserData(String status, String securephone, @RequestParam(value = "startDate", required = false) @DateTimeFormat(iso = DateTimeFormat.ISO.DATE) LocalDate startDate, @RequestParam(value = "endDate", required = false) @DateTimeFormat(iso = DateTimeFormat.ISO.DATE) LocalDate endDate) throws IOException, DocumentException { if (securephone != null && securephone != "") { securephone = Where.$like$(securephone); }/* w w w. java2s .c om*/ List<Map<String, Object>> users = userMapper.userExport(status, securephone, startDate, endDate); String type = status + "?"; HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet(type); HSSFRow row = sheet.createRow(0); CellStyle cellStyle = wb.createCellStyle(); cellStyle.setAlignment(CellStyle.ALIGN_CENTER); cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); sheet.setVerticallyCenter(true); sheet.setHorizontallyCenter(true); String[] excelHeader = { "??", "???", "??", "", "??" }; for (int i = 0; i < excelHeader.length; i++) { HSSFCell cell = row.createCell(i); cell.setCellValue(excelHeader[i]); cell.setCellStyle(cellStyle); sheet.autoSizeColumn(i, true); } for (int i = 0; i < users.size(); i++) { Map<String, Object> resultSet = users.get(i); sheet.autoSizeColumn(i, true); row = sheet.createRow(i + 1); row.setRowStyle(cellStyle); row.createCell(0).setCellValue(i + 1); row.createCell(1).setCellValue(String.valueOf(resultSet.get("name"))); row.createCell(2).setCellValue(String.valueOf(resultSet.get("address"))); row.createCell(3).setCellValue(String.valueOf(resultSet.get("registertime"))); row.createCell(4).setCellValue(String.valueOf(resultSet.get("securephone"))); } File file = File.createTempFile(".xls", ".xls"); OutputStream out = new FileOutputStream(file); wb.write(out); HttpHeaders headers = new HttpHeaders(); headers.setContentType(MediaType.APPLICATION_OCTET_STREAM); headers.setContentDispositionFormData("attachment", URLEncoder.encode(type, "UTF-8") + LocalDate.now() + ".xls"); return new HttpEntity<byte[]>(FileUtils.readFileToByteArray(file), headers); }
From source file:kitt.admin.controller.UserController.java
@RequestMapping(value = "/downloadData") @Authority(role = AuthenticationRole.Service) @Authority(role = AuthenticationRole.TraderAssistant) @Authority(role = AuthenticationRole.LegalPersonnel) @Authority(role = AuthenticationRole.Admin) @Authority(role = AuthenticationRole.Operation) public void downloadUserData(String status, @RequestParam(value = "securephone", required = false, defaultValue = "") String securephone, @RequestParam(value = "clienttype", required = false, defaultValue = "0") int clienttype, @RequestParam(value = "startDate", required = false) @DateTimeFormat(iso = DateTimeFormat.ISO.DATE) LocalDate startDate, @RequestParam(value = "endDate", required = false) @DateTimeFormat(iso = DateTimeFormat.ISO.DATE) LocalDate endDate, HttpServletRequest request, HttpServletResponse response) throws IOException, DocumentException { List<Map<String, Object>> users = userMapper.userExport(status, Where.$like$(securephone), clienttype, startDate, endDate);//from ww w .j av a 2 s. c o m String filename = status + "?"; HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet(filename); HSSFRow row = sheet.createRow(0); CellStyle cellStyle = wb.createCellStyle(); cellStyle.setAlignment(CellStyle.ALIGN_CENTER); cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); sheet.setVerticallyCenter(true); sheet.setHorizontallyCenter(true); sheet.setColumnWidth(0, 1200); sheet.setColumnWidth(1, 3600); sheet.setColumnWidth(2, 8000); sheet.setColumnWidth(3, 4500); sheet.setColumnWidth(4, 4500); String[] excelHeader = { "??", "", "??", "??", "" }; for (int i = 0; i < excelHeader.length; i++) { sheet.autoSizeColumn(i, true); HSSFCell cell = row.createCell(i); cell.setCellValue(excelHeader[i]); cell.setCellStyle(cellStyle); } for (int i = 0; i < users.size(); i++) { Map<String, Object> resultSet = users.get(i); sheet.autoSizeColumn(i, true); row = sheet.createRow(i + 1); row.setRowStyle(cellStyle); row.createCell(0).setCellValue(i + 1); row.createCell(1).setCellValue(String.valueOf(resultSet.get("tradername")).equals("null") ? "" : String.valueOf(resultSet.get("tradername"))); row.createCell(2).setCellValue(String.valueOf(resultSet.get("companyname"))); row.createCell(3).setCellValue(String.valueOf(resultSet.get("securephone"))); row.createCell(4).setCellValue(String.valueOf(resultSet.get("verifytime"))); } response.setCharacterEncoding("UTF-8"); response.setContentType("application/x-download"); filename += LocalDate.now() + ".xls"; if (request.getHeader("user-agent").toLowerCase().contains("firefox")) { filename = new String(filename.getBytes("UTF-8"), "ISO-8859-1"); } else { filename = URLEncoder.encode(filename, "UTF-8"); } response.addHeader("Content-Disposition", "attachment; filename=" + filename); OutputStream out = response.getOutputStream(); wb.write(out); out.close(); }
From source file:kr.co.blackducksoftware.rg.displayexcel.Ex.java
License:Open Source License
/** *///from www.ja v a2s . co m public static void setOneLineHeader(HSSFSheet sheet, int iRowNum, ArrayList<String> al) { LogMaker.makelog("Creating Header in Excel"); try { HSSFRow row = sheet.createRow(iRowNum); row.setHeight((short) 600); int i = 0; for (String str : al) { row.createCell(i, HSSFCell.CELL_TYPE_STRING).setCellValue(str); if (i == 1) { row.getCell(i).setCellStyle(Style.finalHeaderCellStyle); CellRangeAddressList addressList = new CellRangeAddressList(0, 0, 1, 1); DVConstraint dvConstraint = DVConstraint .createExplicitListConstraint(new String[] { "A", "B" }); DataValidation dataValidation = new HSSFDataValidation(addressList, dvConstraint); dataValidation.setSuppressDropDownArrow(false); sheet.addValidationData(dataValidation); } else if (i == 2) { row.getCell(i).setCellStyle(Style.finalHeaderCellStyle); CellRangeAddressList addressList = new CellRangeAddressList(0, 0, i, i); DVConstraint dvConstraint = DVConstraint.createExplicitListConstraint( new String[] { "1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12" }); DataValidation dataValidation = new HSSFDataValidation(addressList, dvConstraint); dataValidation.setSuppressDropDownArrow(false); sheet.addValidationData(dataValidation); } else if (i == 3) { row.getCell(i).setCellStyle(Style.finalHeaderCellStyle); CellRangeAddressList addressList = new CellRangeAddressList(0, 0, i, i); DVConstraint dvConstraint = DVConstraint .createExplicitListConstraint(new String[] { "A", "B", "C", "D", "E" }); DataValidation dataValidation = new HSSFDataValidation(addressList, dvConstraint); dataValidation.setSuppressDropDownArrow(false); sheet.addValidationData(dataValidation); } else { try { row.getCell(i).setCellStyle(Style.finalHeaderCellStyle); } catch (NullPointerException e) { e.printStackTrace(); } } sheet.autoSizeColumn((short) i, true); i++; } } catch (Exception e) { e.printStackTrace(); } }
From source file:kr.co.blackducksoftware.rg.displayexcel.Ex.java
License:Open Source License
public static void src2ndrow(HSSFSheet sheet, ArrayList<String> al, int i, HSSFRow row, int a, int b) { try {/*from w ww . j a va2s . c o m*/ //HSSFRow row = sheet.createRow(iRowNum); row.setHeight((short) 500); for (String str : al) { row.createCell(i, HSSFCell.CELL_TYPE_STRING).setCellValue(str); //row.getCell(20).setCellStyle(Style.src2ndrowFinalCellStyle); if (i < a && i >= 0) { row.getCell(i).setCellStyle(Style.src2ndrowCellStyle); } else if (i < b && i >= a) { row.getCell(i).setCellStyle(Style.src2ndrowMidCellStyle); } else if (i >= b) { row.getCell(i).setCellStyle(Style.src2ndrowFinalCellStyle); } //if(!al.equals(" ")){ //" " //sheet.autoSizeColumn((short)i, true);//bia autosize //} if (al.equals("Person In Charge")) { sheet.autoSizeColumn((short) i, true); } i++; } //row.getCell(20).setCellStyle(Style.src2ndrowFinalCellStyle); } catch (Exception e) { e.printStackTrace(); } }