/** * 初始化函数 * * @param title 表格标题,传“空值”,表示无标题 * @param headerList 表头列表 */ private void initialize(String title, List<String> headerList) { this.wb = new SXSSFWorkbook(500); this.sheet = wb.createSheet("Export"); this.styles = createStyles(wb); // Create title if (StringUtils.isNotBlank(title)) { Row titleRow = sheet.createRow(rownum++); titleRow.setHeightInPoints(30); Cell titleCell = titleRow.createCell(0); titleCell.setCellStyle(styles.get("title")); titleCell.setCellValue(title); sheet.addMergedRegion( new CellRangeAddress( titleRow.getRowNum(), titleRow.getRowNum(), titleRow.getRowNum(), headerList.size() - 1)); } // Create header if (headerList == null) { throw new RuntimeException("headerList not null!"); } Row headerRow = sheet.createRow(rownum++); headerRow.setHeightInPoints(16); for (int i = 0; i < headerList.size(); i++) { Cell cell = headerRow.createCell(i); cell.setCellStyle(styles.get("header")); String[] ss = StringUtils.split(headerList.get(i), "**", 2); if (ss.length == 2) { cell.setCellValue(ss[0]); Comment comment = this.sheet .createDrawingPatriarch() .createCellComment(new XSSFClientAnchor(0, 0, 0, 0, (short) 3, 3, (short) 5, 6)); comment.setString(new XSSFRichTextString(ss[1])); cell.setCellComment(comment); } else { cell.setCellValue(headerList.get(i)); } sheet.autoSizeColumn(i); } for (int i = 0; i < headerList.size(); i++) { int colWidth = sheet.getColumnWidth(i) * 2; sheet.setColumnWidth(i, colWidth < 3000 ? 3000 : colWidth); } log.debug("Initialize success."); }
public void addSheet( String sheetName, List<Object[]> bodyData, int[] entryTypes, String[] header) { Sheet addedSheet = workbook.createSheet(sheetName); createHeaderRow(addedSheet.createRow(0), header); // goes through each entry in the list creating the rows for (int index = 0; index < bodyData.size(); index++) { createBodyRow( addedSheet.createRow(index + 1), bodyData.get(index), entryTypes); // the 1 accounts for the header } // it now auto-sizes the columns for (int column = 0; column <= header.length; column++) { addedSheet.autoSizeColumn(column); } }
public void writeDataToExcel(Sheet sheet) { LOGGER.debug("[{}] Exporting data to excel", getText()); List<ExcelColumnRenderer> columnRendererList = new ArrayList<>(); columnRendererList.add(new TaskStatusExcelColumn()); columnRendererList.add(new TaskDescriptionExcelColumn()); TimerangeProvider timerangeProvider = fetchTimereportContext.get().getTimerangeProvider(); LocalDate startDate = timerangeProvider.getStartDate(); LocalDate endDate = timerangeProvider.getEndDate(); long amountOfDaysToDisplay = ChronoUnit.DAYS.between(startDate, endDate); for (int days = 0; days <= amountOfDaysToDisplay; days++) { LocalDate currentColumnDate = timerangeProvider.getStartDate().plus(days, ChronoUnit.DAYS); String displayDate = FormattingUtil.formatDate(currentColumnDate); columnRendererList.add(new WorklogExcelColumn(displayDate, currentColumnDate)); } columnRendererList.add(new TaskWorklogSummaryExcelColumn()); TreeItem<DisplayRow> root = taskTableView.getRoot(); ObservableList<TreeItem<DisplayRow>> children = root.getChildren(); for (int columnIndex = 0; columnIndex < columnRendererList.size(); columnIndex++) { ExcelColumnRenderer excelColumnRenderer = columnRendererList.get(columnIndex); excelColumnRenderer.renderCells( columnIndex, sheet, children, fetchTimereportContext.get().getGroupByCategory().isPresent()); } // autosize column widths for (int i = 0; i < columnRendererList.size(); i++) { sheet.autoSizeColumn(i); } }
@Test public void generateExcelPoiReport() throws IOException { Workbook wb = new HSSFWorkbook(); Sheet sheet = wb.createSheet("Accounts"); int rowNum = 1; Row row = sheet.createRow(rowNum++); Font font = wb.createFont(); font.setFontHeightInPoints((short) 24); font.setFontName(FONT_TYPE); font.setColor(FONT_COLOR_TITLE); /** * Header **** */ CellStyle style = wb.createCellStyle(); style.setFont(font); style.setAlignment(CellStyle.ALIGN_CENTER); style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); style.setFillForegroundColor(BACKGROUND_COLOR); Cell cell = row.createCell((short) 1); cell.setCellValue("Account Report per Beneficiary"); cell.setCellStyle(style); sheet.addMergedRegion( new CellRangeAddress( 1, // first row (0-based) 2, // last row (0-based) 1, // first column (0-based) 16 // last column (0-based) )); /** * Body **** */ font = wb.createFont(); font.setFontHeightInPoints((short) 12); font.setFontName(FONT_TYPE); font.setColor(FONT_COLOR); style = wb.createCellStyle(); style.setFont(font); rowNum = rowNum + 3; List<Account> accounts = accountManager.getAllAccounts(); for (Account account : accounts) { row = sheet.createRow(rowNum++); cell = row.createCell((short) 1); cell.setCellStyle(style); cell.setCellValue(account.getName()); cell = row.createCell((short) 2); cell.setCellValue(account.getNumber()); cell.setCellStyle(style); cell = row.createCell((short) 3); cell.setCellValue(account.getEntityId()); cell.setCellStyle(style); } sheet.autoSizeColumn(1); sheet.autoSizeColumn(2); sheet.autoSizeColumn(3); // Write the output to a file FileOutputStream fileOut = new FileOutputStream("C:/Temp/workbook.xls"); wb.write(fileOut); fileOut.close(); }
// Define A method for Creating Excel File public String createExcel( String titleString, String sheetString, String subTitleString, List data, String[] headerTitles, String titleKey, String filePath) { String file = null; Workbook wb; int col = 0; try { // check Header Title if (headerTitles != null && headerTitles.length > 0) col = headerTitles.length; wb = (Workbook) new HSSFWorkbook(); // Hear we are getting whole property List<ConfigurationUtilBean> titleMap = new CustomerCommonPropertyMap().getTitles(titleKey); Map<String, CellStyle> styles = createStyles(wb); Sheet sheet = wb.createSheet(sheetString); PrintSetup printSetup = sheet.getPrintSetup(); printSetup.setLandscape(true); sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); Header header = sheet.getHeader(); header.setCenter("Center Header"); header.setLeft("Left Header"); header.setRight("Right Footer"); Footer footer = sheet.getFooter(); footer.setCenter("center footer"); footer.setLeft("left footer"); footer.setRight("right footer"); // Title Row.... Row titleRow = sheet.createRow(0); titleRow.setHeightInPoints(20); Cell titleCell = titleRow.createCell(0); titleCell.setCellValue(titleString); titleCell.setCellStyle(styles.get("title")); sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, col - 1)); // Sub Title Row..... // System.out.println("Sub Title String >>>>>>"+subTitleString); Row headerRow = null; if (subTitleString != "") { Row subTitleRow = sheet.createRow(1); subTitleRow.setHeightInPoints(18); Cell subTitleCell = subTitleRow.createCell(0); subTitleCell.setCellValue(subTitleString); subTitleCell.setCellStyle(styles.get("subTitle")); sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, col - 1)); headerRow = sheet.createRow(2); headerRow.setHeightInPoints(15); // } else { headerRow = sheet.createRow(1); headerRow.setHeightInPoints(15); } Cell headerCell = null; if (headerTitles != null) { for (ConfigurationUtilBean cell : titleMap) { int titleIndex = 0; for (int i = 0; i < headerTitles.length; i++) { if (cell.getKey().equalsIgnoreCase(headerTitles[titleIndex].trim())) { headerCell = headerRow.createCell(titleIndex); headerCell.setCellValue(cell.getValue()); headerCell.setCellStyle(styles.get("header")); } titleIndex++; } } } Row dataRow = null; Cell dataCell = null; int rowIndex = 2; /* List Iteration text */ try { if (data != null && data.size() > 0) { for (Iterator it = data.iterator(); it.hasNext(); ) { Object[] obdata = (Object[]) it.next(); dataRow = sheet.createRow(rowIndex); for (int cellIndex = 0; cellIndex < headerTitles.length; cellIndex++) { dataCell = dataRow.createCell(cellIndex); if (obdata[cellIndex] != null && !obdata[cellIndex].toString().equalsIgnoreCase("")) { dataCell.setCellValue(obdata[cellIndex].toString()); } else { dataCell.setCellValue("NA"); } } rowIndex++; } } } catch (Exception e) { // TODO: handle exception } for (int titleIndex = 0; titleIndex < headerTitles.length; titleIndex++) sheet.autoSizeColumn(titleIndex); // adjust width of the column file = filePath + File.separator + "OpportunityReportDetail_" + DateUtil.getCurrentDateIndianFormat() + (DateUtil.getCurrentTimeHourMin()).replaceAll(":", "-") + ".xls"; if (wb instanceof XSSFWorkbook) file += "x"; FileOutputStream out = new FileOutputStream(file); wb.write(out); out.close(); } catch (Exception e) { e.printStackTrace(); } finally { } return file; }
// Define A method for Creating Excel File public String createExcelformate( String titleString, String sheetString, String[] headerTitles, String titleKey, String filePath) { String file = null; Workbook wb; int col = 0; try { // check Header Title if (headerTitles != null && headerTitles.length > 0) col = headerTitles.length; wb = (Workbook) new HSSFWorkbook(); // Hear we are getting whole property List<ConfigurationUtilBean> titleMap = new CustomerCommonPropertyMap().getTitles(titleKey); Map<String, CellStyle> styles = createStyles(wb); Sheet sheet = wb.createSheet(sheetString); PrintSetup printSetup = sheet.getPrintSetup(); printSetup.setLandscape(true); sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); Header header = sheet.getHeader(); header.setCenter("Center Header"); header.setLeft("Left Header"); header.setRight("Right Footer"); Footer footer = sheet.getFooter(); footer.setCenter("center footer"); footer.setLeft("left footer"); footer.setRight("right footer"); // Title Row.... Row titleRow = sheet.createRow(0); titleRow.setHeightInPoints(20); Cell titleCell = titleRow.createCell(0); titleCell.setCellValue(titleString); titleCell.setCellStyle(styles.get("title")); sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, col - 1)); // Row headerRow = sheet.createRow(1); headerRow.setHeightInPoints(15); Cell headerCell = null; if (headerTitles != null) { for (ConfigurationUtilBean cell : titleMap) { int titleIndex = 0; for (int i = 0; i < headerTitles.length; i++) { if (cell.getKey().equalsIgnoreCase(headerTitles[titleIndex].trim())) { headerCell = headerRow.createCell(titleIndex); headerCell.setCellValue(cell.getValue()); headerCell.setCellStyle(styles.get("header")); } titleIndex++; } } } for (int titleIndex = 0; titleIndex < headerTitles.length; titleIndex++) sheet.autoSizeColumn(titleIndex); // adjust width of the column file = filePath + File.separator + "ContactReport" + DateUtil.getCurrentDateIndianFormat() + (DateUtil.getCurrentTime()).replaceAll(":", "-") + ".xls"; if (wb instanceof XSSFWorkbook) file += "x"; FileOutputStream out = new FileOutputStream(file); wb.write(out); out.close(); } catch (Exception e) { e.printStackTrace(); } finally { } return file; }
/** * Creates an Excel worksheet containing the metric's data (timestamps and values) for the * specified time range. This worksheet is titled with the trhe metric's name and added to the * specified Workbook. * * @param wb the workbook to add this worksheet to * @param metricName the name of the metric whose data is being rendered in this worksheet * @param rrdFilename the name of the RRD file to retrieve the metric's data from * @param startTime start time, in seconds since Unix epoch, to fetch metric's data * @param endTime end time, in seconds since Unix epoch, to fetch metric's data * @throws IOException * @throws MetricsGraphException */ private void createSheet( Workbook wb, String metricName, String rrdFilename, long startTime, long endTime) throws IOException, MetricsGraphException { LOGGER.trace("ENTERING: createSheet"); MetricData metricData = getMetricData(rrdFilename, startTime, endTime); String displayableMetricName = convertCamelCase(metricName); String title = displayableMetricName + " for " + getCalendarTime(startTime) + " to " + getCalendarTime(endTime); Sheet sheet = wb.createSheet(displayableMetricName); sheet.autoSizeColumn(0); sheet.autoSizeColumn(1); Font headerFont = wb.createFont(); headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD); CellStyle columnHeadingsStyle = wb.createCellStyle(); columnHeadingsStyle.setFont(headerFont); CellStyle bannerStyle = wb.createCellStyle(); bannerStyle.setFont(headerFont); bannerStyle.setFillForegroundColor(HSSFColor.PALE_BLUE.index); bannerStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); int rowCount = 0; Row row = sheet.createRow((short) rowCount); Cell cell = row.createCell(0); cell.setCellValue(title); cell.setCellStyle(bannerStyle); rowCount++; // Blank row for spacing/readability row = sheet.createRow((short) rowCount); cell = row.createCell(0); cell.setCellValue(""); rowCount++; row = sheet.createRow((short) rowCount); cell = row.createCell(0); cell.setCellValue("Timestamp"); cell.setCellStyle(columnHeadingsStyle); cell = row.createCell(1); cell.setCellValue("Value"); cell.setCellStyle(columnHeadingsStyle); rowCount++; List<Long> timestamps = metricData.getTimestamps(); List<Double> values = metricData.getValues(); for (int i = 0; i < timestamps.size(); i++) { String timestamp = getCalendarTime(timestamps.get(i)); row = sheet.createRow((short) rowCount); row.createCell(0).setCellValue(timestamp); // convert value to a long to prevent fractional values row.createCell(1).setCellValue(new Double(values.get(i)).longValue()); rowCount++; } if (metricData.hasTotalCount()) { // Blank row for spacing/readability row = sheet.createRow((short) rowCount); cell = row.createCell(0); cell.setCellValue(""); rowCount++; row = sheet.createRow((short) rowCount); cell = row.createCell(0); cell.setCellValue("Total Count: "); cell.setCellStyle(columnHeadingsStyle); row.createCell(1).setCellValue(metricData.getTotalCount()); } LOGGER.trace("EXITING: createSheet"); }
@Test public void createex() { Workbook wb = new HSSFWorkbook(); CreationHelper createHelper = wb.getCreationHelper(); CellStyle cellStyle = wb.createCellStyle(); cellStyle.setFillPattern(CellStyle.NO_FILL); Sheet sheet = wb.createSheet("数据日志"); Sheet sheet2 = wb.createSheet("second sheet"); String safeName = WorkbookUtil.createSafeSheetName("[O'Brien's sales*?]"); // returns " O'Brien's sales " Sheet sheet3 = wb.createSheet(safeName); Header header = sheet.getHeader(); header.setCenter("Center Header"); header.setLeft("Left Header"); header.setRight( HSSFHeader.font("Stencil-Normal", "Italic") + HSSFHeader.fontSize((short) 16) + "Right w/ Stencil-Normal Italic font and size 16"); sheet.setColumnWidth(0, 15 * 256); // .autoSizeColumn(0 ); // 调整第一列宽度 sheet.setColumnWidth(1, 17 * 256); // 调整第二列宽度 sheet.setColumnWidth(3, 17 * 256); // 调整第三列宽度 sheet.autoSizeColumn(2); // 调整第四列宽度 // Create a row and put some cells in it. Rows are 0 based. Row row = sheet.createRow((short) 1); // Create a cell and put a value in it. Cell cell = row.createCell(0); cell.setCellValue("ID"); cell.setCellStyle(cellStyle); // Or do it on one line. row.createCell(1).setCellValue("表名"); row.createCell(2).setCellValue(createHelper.createRichTextString("操作类型")); row.createCell(3).setCellValue("操作日期"); row.createCell(4).setCellValue("操作者"); List<Log> list = this.logList(); Row rw; for (int i = 2; i < list.size(); i++) { Log log = list.get(i); rw = sheet.createRow((short) i); rw.createCell(0).setCellValue(createHelper.createRichTextString(log.getId())); rw.createCell(1).setCellValue(log.getDataTable()); rw.createCell(2).setCellValue(this.convertActionType(log.getActionType())); rw.createCell(3) .setCellValue(DateUtil.convertDateToString(log.getLogDate(), DateUtil.pattern2)); rw.createCell(4).setCellValue(log.getAdminId()); } String bp = this.getClass().getResource("/").toString(); try { System.out.println(bp); File f = new File("workbook.xls"); FileOutputStream fileOut = new FileOutputStream(f); wb.write(fileOut); fileOut.close(); } catch (FileNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } }
public void process2HTTP( ExportModel expModel, UISimpleViewExport uis, HttpServletResponse hsr, DateTimeHelper dth) { try { String strFileName = uis.getDownloadFileName(); Workbook wbCurrent = null; if (strFileName.toLowerCase().endsWith(".xlsx")) { wbCurrent = new XSSFWorkbook(); } else { wbCurrent = new HSSFWorkbook(); } HashMap<String, CellStyle> hsCS = new HashMap<String, CellStyle>(); CreationHelper cr = wbCurrent.getCreationHelper(); CellStyle csDate = wbCurrent.createCellStyle(); csDate.setDataFormat(cr.createDataFormat().getFormat(dth.getDFDate().toPattern())); CellStyle csDateTime = wbCurrent.createCellStyle(); csDateTime.setDataFormat(cr.createDataFormat().getFormat(dth.getDFDateTime().toPattern())); CellStyle csTime = wbCurrent.createCellStyle(); csTime.setDataFormat(cr.createDataFormat().getFormat(dth.getDFTime().toPattern())); hsCS.put("DATE", csDate); hsCS.put("TIME", csTime); hsCS.put("DATETIME", csDateTime); Sheet sh = wbCurrent.createSheet("SVE Export"); int nRowCount = 0; // BUILDING HEADER if (uis.isIncludeHeader()) { Row rw = sh.createRow(nRowCount); int nCol = 0; for (ExportColumn expColumn : expModel.getColumns()) { rw.createCell(nCol).setCellValue(expColumn.getColumnName()); nCol++; } nRowCount++; } // Processing Values for (ExportDataRow expRow : expModel.getRows()) { Row rw = sh.createRow(nRowCount); int nCol = 0; for (ExportColumn expColumn : expModel.getColumns()) { Cell clCurrent = rw.createCell(nCol); setCellValue(expRow.getValue(expColumn.getPosition()), clCurrent, expColumn, hsCS); nCol++; } nRowCount++; } for (int nCol = 0; nCol < expModel.getColumns().size(); nCol++) { sh.autoSizeColumn(nCol); } if (strFileName.toLowerCase().endsWith(".xlsx")) { hsr.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); } else if (strFileName.toLowerCase().endsWith("xls")) { hsr.setContentType("application/vnd.ms-excel"); } else { hsr.setContentType("application/octet-stream"); } hsr.addHeader("Content-disposition", "inline; filename=\"" + strFileName + "\""); OutputStream os = hsr.getOutputStream(); ByteArrayOutputStream bos = new ByteArrayOutputStream(); wbCurrent.write(bos); bos.writeTo(os); os.close(); } catch (Exception e) { ErrorPageBuilder.getInstance() .processError(hsr, "Error during SVE-Generation (Workbook Export)", e); } }
public void exportarPlanilha(File arquivoDeSaida) throws FileNotFoundException, IOException { logger.info("Gerando relatório"); workbook = new HSSFWorkbook(); dataCellStyle = buildCellStyle(); headerCellStyle = buildCellStyle(); totalHeaderStyle = buildCellStyle(); totalHeaderStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex()); totalHeaderStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); totalCellStyle = buildCellStyle(); totalCellStyle.setAlignment(CellStyle.ALIGN_CENTER); totalCellStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex()); totalCellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); totalRegistrosGravar = categorias.size() * medicos.size() * planos.size(); logger.debug("{} registros para gravar", totalRegistrosGravar); registrosGravados = 0; for (String categoria : categorias.keySet()) { Sheet sheet = workbook.createSheet(WorkbookUtil.createSafeSheetName(categoria)); Row rowPlanos = sheet.createRow(0); buildHeaderCell(rowPlanos, (short) 0); Map<String, AtomicInteger> totaisPlanos = new HashMap<String, AtomicInteger>(); for (String plano : planos.keySet()) { buildHeaderCell(rowPlanos, rowPlanos.getLastCellNum()).setCellValue(plano); totaisPlanos.put(plano, new AtomicInteger()); } Cell cellTotalHeader = buildTotalHeaderCell(rowPlanos, rowPlanos.getLastCellNum()); cellTotalHeader.setCellValue("TOTAL"); AtomicInteger totalGeral = new AtomicInteger(); for (String medico : medicos.keySet()) { Row row = sheet.createRow(sheet.getLastRowNum() + 1); Cell cellMedico = buildHeaderCell(row, (short) 0); cellMedico.setCellValue(medico); AtomicInteger totalMedico = new AtomicInteger(); for (String plano : planos.keySet()) { Chave chave = new Chave(plano, medico, categoria); AtomicInteger contador = contadores.get(chave); Cell cell = buildDataCell(row, row.getLastCellNum()); if (contador != null) { cell.setCellValue(contador.get()); totalMedico.addAndGet(contador.get()); totaisPlanos.get(plano).addAndGet(contador.get()); } else { cell.setCellValue("*"); } EventQueue.invokeLater( new Runnable() { @Override public void run() { int porcentagemConcluido = 50 + (int) ((++registrosGravados / (float) totalRegistrosGravar) * 50); progressBar.setValue(porcentagemConcluido); } }); } Cell cellTotal = buildTotalCell(row, row.getLastCellNum()); cellTotal.setCellValue(totalMedico.get()); totalGeral.addAndGet(totalMedico.get()); } Row rowTotais = sheet.createRow(sheet.getLastRowNum() + 1); Cell cellTotalPlanosHeader = buildTotalHeaderCell(rowTotais, (short) 0); cellTotalPlanosHeader.setCellValue("TOTAL"); for (String plano : planos.keySet()) { Cell cellTotal = buildTotalCell(rowTotais, rowTotais.getLastCellNum()); cellTotal.setCellValue(totaisPlanos.get(plano).get()); } Cell cellTotalGeral = buildTotalCell(rowTotais, rowTotais.getLastCellNum()); cellTotalGeral.setCellValue(totalGeral.get()); for (int i = 0; i < sheet.getRow(0).getLastCellNum(); i++) { sheet.autoSizeColumn(i); } } FileOutputStream fileOut = new FileOutputStream(arquivoDeSaida); workbook.write(fileOut); fileOut.close(); logger.info("Relatório concluido"); }