List of usage examples for org.apache.poi.xssf.streaming SXSSFWorkbook createSheet
@Override
public SXSSFSheet createSheet(String sheetname)
From source file:cfdi.clases.db.DerbyUtilities.java
License:Open Source License
/** * Exporta los registros de de CFDI datos generales o su detalle, con el filtro que se * haya utilizado en la interface grfica * //from w w w. j a v a 2 s. co m * @param query es el query filtradn para la tabla de CFDI y CFDI_DETALLE * @param nombre nombre del archivo * @param path directorio donde se va a crear el archivo de excel * @return the boolean */ public boolean exportarExcel(String query, String nombre, String path) { Connection connection = null; Statement st = null; ResultSet rs = null; boolean respuesta = false; BoneCP connectionPool = null; try { Class.forName(propiedades.getProperty("DB_DRIVER")); // setup the connection pool BoneCPConfig config = new BoneCPConfig(); config.setJdbcUrl(propiedades.getProperty("DB_SERVER")); // jdbc url specific to your database, eg jdbc:mysql://127.0.0.1/yourdb config.setUsername(propiedades.getProperty("DB_USER")); config.setPassword(propiedades.getProperty("DB_PASSWORD")); config.setMinConnectionsPerPartition(5); config.setMaxConnectionsPerPartition(10); config.setPartitionCount(1); connectionPool = new BoneCP(config); // setup the connection pool FileOutputStream fileOut = new FileOutputStream(path + nombre + ".xlsx"); connection = connectionPool.getConnection(); // fetch a connection if (connection != null) { st = connection.createStatement(); rs = st.executeQuery(query); ResultSetMetaData metaData = rs.getMetaData(); int count = metaData.getColumnCount(); SXSSFWorkbook workbook = new SXSSFWorkbook(10000); Sheet sheet = workbook.createSheet(nombre); int rownum = 0; Row row = sheet.createRow(rownum++); CellStyle stylec = workbook.createCellStyle(); stylec.setBorderBottom(CellStyle.BORDER_THIN); stylec.setBottomBorderColor(IndexedColors.BLACK.getIndex()); Font fontc = workbook.createFont(); fontc.setBoldweight(Font.BOLDWEIGHT_BOLD); stylec.setFont(fontc); for (int i = 1; i <= count; i++) { row.createCell(i).setCellValue(metaData.getColumnName(i)); row.getCell(i).setCellStyle(stylec); } while (rs.next()) { Row rowh = sheet.createRow(rownum++); for (int i = 1; i <= count; i++) { if (metaData.getColumnTypeName(i).equalsIgnoreCase("INT") || metaData.getColumnTypeName(i).equalsIgnoreCase("INT UNSIGNED")) rowh.createCell(i).setCellValue(rs.getInt(i)); else if (metaData.getColumnTypeName(i).equalsIgnoreCase("DOUBLE")) rowh.createCell(i).setCellValue(rs.getDouble(i)); else rowh.createCell(i).setCellValue(rs.getString(i)); } } /*if(rownum<5000){ for (int i = 1; i <= count; i++) sheet.autoSizeColumn(i); }*/ try { workbook.write(fileOut); fileOut.flush(); fileOut.close(); } catch (FileNotFoundException e) { System.out.println("Error: export 1"); } catch (IOException e) { System.out.println("Error: export 2"); } respuesta = true; connectionPool.shutdown(); } } catch (SQLException e) { System.out.println("Error: insertDatos 3"); logger.log(Level.SEVERE, null, e); } catch (ClassNotFoundException ex) { logger.log(Level.SEVERE, null, ex); } catch (Exception ex) { System.out.println("Error: insertDatos 5"); logger.log(Level.SEVERE, null, ex); } finally { if (connection != null) { try { connection.close(); } catch (SQLException e) { System.out.println("Error: insertDatos 4"); logger.log(Level.SEVERE, null, e); } } } return respuesta; }
From source file:com.jshuabo.reportcenter.server.service.automoblie.impl.DefaultAutoRecordServiceImpl.java
License:Open Source License
@Override public void importToExcel(Map<String, Object> exprotMap, HttpServletResponse response, HttpServletRequest request, String[] title, String excelName) { Map<String, Object> pageData = autoRecordDataMapper.exprotPageData(exprotMap); SXSSFWorkbook workbook = new SXSSFWorkbook(1000); // keep 100 rows in memory, exceeding rows int flag = 1; // int rowCount = 150000; // ?sheet long count = (Long) pageData.get("COUNT"); long pageSize = rowCount; // ?? long page = 1; if (rowCount < count) { if (count % pageSize == 0) { } else {/* w w w . jav a2 s . com*/ page = count / pageSize + 1; } // } Sheet sh = workbook.createSheet(excelName + System.currentTimeMillis()); Row row = sh.createRow((short) 0); // Cell cell = null; CellStyle[] cs = POIUtils.cellStyle(workbook); POIUtils.createHeard(sh, row, cell, cs, title); for (int i = 1; i <= page; i++) { long offset = (i - 1) * pageSize; long rows = pageSize; exprotMap.put("offset", Long.valueOf(offset)); exprotMap.put("rows", Long.valueOf(rows)); // ?? List<AutoRecordData> autoRecordDataList = autoRecordDataMapper.exprot2Excel(exprotMap); if (autoRecordDataList.isEmpty()) continue; for (int rownum = 0; rownum < autoRecordDataList.size(); rownum++) { AutoRecordData autoRecord = autoRecordDataList.get(rownum); String subStation = SubStationConstant.getSubStationByKey(autoRecord.getSubStation()); String licenseDate = DateFormatUtils.format(autoRecord.getLicenseDate(), DateFormatUtils.ymd); String inspectionDate = DateFormatUtils.format(autoRecord.getInspectionDate(), DateFormatUtils.ymd); String insuranceDate = DateFormatUtils.format(autoRecord.gettLInsuranceDate(), DateFormatUtils.ymd); String changeDate = DateFormatUtils.format(autoRecord.getChangeDate(), DateFormatUtils.ymd); String strongInsDate = DateFormatUtils.format(autoRecord.getStrongInsDate(), DateFormatUtils.ymd); // ? ?sheet if (flag > pageSize) { flag = 1; sh = workbook.createSheet(excelName + System.currentTimeMillis()); row = sh.createRow((short) 0); POIUtils.createHeard(sh, row, cell, cs, title); } row = sh.createRow(flag); for (int cellnum = 0; cellnum < title.length; cellnum++) { cell = row.createCell(0); cell.setCellValue(subStation); cell.setCellStyle(cs[1]); cell = row.createCell(1); cell.setCellValue(autoRecord.getDeputyCard()); cell.setCellStyle(cs[1]); cell = row.createCell(2); cell.setCellValue(autoRecord.getCarKind()); cell.setCellStyle(cs[1]); cell = row.createCell(3); cell.setCellValue(autoRecord.getLicenseNo()); cell.setCellStyle(cs[1]); cell = row.createCell(4); cell.setCellValue(autoRecord.getLicense()); cell.setCellStyle(cs[1]); cell = row.createCell(5); cell.setCellValue(licenseDate); cell.setCellStyle(cs[1]); cell = row.createCell(6); cell.setCellValue(autoRecord.getLicenseName()); cell.setCellStyle(cs[1]); cell = row.createCell(7); cell.setCellValue(inspectionDate); cell.setCellStyle(cs[1]); cell = row.createCell(8); cell.setCellValue(autoRecord.getName()); cell.setCellStyle(cs[1]); cell = row.createCell(9); cell.setCellValue(autoRecord.getIdCard()); cell.setCellStyle(cs[1]); cell = row.createCell(10); cell.setCellValue(String.valueOf(autoRecord.getFtReceive())); cell.setCellStyle(cs[1]); cell = row.createCell(11); cell.setCellValue(changeDate); cell.setCellStyle(cs[1]); cell = row.createCell(12); cell.setCellValue(autoRecord.getTelephone()); cell.setCellStyle(cs[1]); cell = row.createCell(13); cell.setCellValue(strongInsDate); cell.setCellStyle(cs[1]); cell = row.createCell(14); cell.setCellValue(String.valueOf(autoRecord.gettLInsurance())); cell.setCellStyle(cs[1]); cell = row.createCell(15); cell.setCellValue(insuranceDate); cell.setCellStyle(cs[1]); cell = row.createCell(16); cell.setCellValue(autoRecord.getPoliceProve()); cell.setCellStyle(cs[1]); cell = row.createCell(17); cell.setCellValue(autoRecord.getHouseholdCopy()); cell.setCellStyle(cs[1]); cell = row.createCell(18); cell.setCellValue(autoRecord.getIdCardCopy()); cell.setCellStyle(cs[1]); cell = row.createCell(19); cell.setCellValue(autoRecord.getLicenseCopy()); cell.setCellStyle(cs[1]); cell = row.createCell(20); cell.setCellValue(autoRecord.getGuaranRespon()); cell.setCellStyle(cs[1]); cell = row.createCell(21); cell.setCellValue(autoRecord.getGuaranIncome()); cell.setCellStyle(cs[1]); cell = row.createCell(22); cell.setCellValue(autoRecord.getGuaranHouseCopy()); cell.setCellStyle(cs[1]); cell = row.createCell(23); cell.setCellValue(autoRecord.getGuaranIDCopy()); cell.setCellStyle(cs[1]); cell = row.createCell(24); cell.setCellValue(autoRecord.getDriLicenseCopy()); cell.setCellStyle(cs[1]); cell = row.createCell(25); cell.setCellValue(autoRecord.getStrongInsCopy()); cell.setCellStyle(cs[1]); cell = row.createCell(26); cell.setCellValue(autoRecord.getCommerInsuCopy()); cell.setCellStyle(cs[1]); cell = row.createCell(27); cell.setCellValue(autoRecord.getCertificate()); cell.setCellStyle(cs[1]); cell = row.createCell(28); cell.setCellValue(String.valueOf(autoRecord.getAgreeDate())); cell.setCellStyle(cs[1]); cell = row.createCell(29); cell.setCellValue(autoRecord.getRentalAgreement()); cell.setCellStyle(cs[1]); cell = row.createCell(30); cell.setCellValue(autoRecord.getStrongInsPrompt()); cell.setCellStyle(cs[1]); cell = row.createCell(31); cell.setCellValue(autoRecord.gettLInsurancePrompt()); cell.setCellStyle(cs[1]); cell = row.createCell(32); cell.setCellValue(autoRecord.getInspectionPrompt()); cell.setCellStyle(cs[1]); cell = row.createCell(33); cell.setCellValue(autoRecord.getChangePrompt()); cell.setCellStyle(cs[1]); cell = row.createCell(34); cell.setCellValue(autoRecord.getStatus()); cell.setCellStyle(cs[1]); } flag++; } } POIUtils.exprot(workbook, response, excelName); }
From source file:com.jshuabo.reportcenter.server.service.automoblie.impl.DefaultBranchLineCarInfoServiceImpl.java
License:Open Source License
@Override public void importToExcel(Map<String, Object> exprotMap, HttpServletResponse response, HttpServletRequest request, String[] title, String excelName) { User user = SecurityContextHolder.getCurrentUser(); if (StringUtils.isEmpty(exprotMap.get("subStation"))) { exprotMap.put("subStation", user.getId()); }// w w w . j a va 2s. c om Map<String, Object> pageData = carInfoMapper.exprotPageData(exprotMap); SXSSFWorkbook workbook = new SXSSFWorkbook(1000); // keep 100 rows in memory, exceeding rows int flag = 1; // int rowCount = 150000; // ?sheet long count = (Long) pageData.get("COUNT"); long pageSize = rowCount; // ?? long page = 1; if (rowCount < count) { if (count % pageSize == 0) { } else { page = count / pageSize + 1; } // } Sheet sh = workbook.createSheet(excelName + System.currentTimeMillis()); Row row = sh.createRow((short) 0); // Cell cell = null; CellStyle[] cs = POIUtils.cellStyle(workbook); POIUtils.createHeard(sh, row, cell, cs, title); for (int i = 1; i <= page; i++) { long offset = (i - 1) * pageSize; long rows = pageSize; exprotMap.put("offset", Long.valueOf(offset)); exprotMap.put("rows", Long.valueOf(rows)); // ?? List<BranchLineCarInfo> autoRecordDataList = carInfoMapper.exprot2Excel(exprotMap); if (autoRecordDataList.isEmpty()) continue; for (int rownum = 0; rownum < autoRecordDataList.size(); rownum++) { BranchLineCarInfo branchLineCarInfo = autoRecordDataList.get(rownum); String subStation = branchLineCarInfo.getExtendProp3(); String licenseDate = DateFormatUtils.format(branchLineCarInfo.getLicenseDate(), DateFormatUtils.ymd); String inspectionDate = DateFormatUtils.format(branchLineCarInfo.getInspectionDate(), DateFormatUtils.ymd); String insuranceDate = DateFormatUtils.format(branchLineCarInfo.getTlInsuranceDate(), DateFormatUtils.ymd); String changeDate = DateFormatUtils.format(branchLineCarInfo.getChangeDate(), DateFormatUtils.ymd); String strongInsDate = DateFormatUtils.format(branchLineCarInfo.getStrongInsdate(), DateFormatUtils.ymd); String agreeDate = DateFormatUtils.format(branchLineCarInfo.getAgreeDate(), DateFormatUtils.ymd); // ? ?sheet if (flag > pageSize) { flag = 1; sh = workbook.createSheet(excelName + System.currentTimeMillis()); row = sh.createRow((short) 0); POIUtils.createHeard(sh, row, cell, cs, title); } row = sh.createRow(flag); for (int cellnum = 0; cellnum < title.length; cellnum++) { cell = row.createCell(0); cell.setCellValue(subStation); cell.setCellStyle(cs[1]); cell = row.createCell(1); cell.setCellValue(branchLineCarInfo.getDeputyCard()); cell.setCellStyle(cs[1]); cell = row.createCell(2); cell.setCellValue(branchLineCarInfo.getCarKind()); cell.setCellStyle(cs[1]); cell = row.createCell(3); cell.setCellValue(branchLineCarInfo.getLicenseNo()); cell.setCellStyle(cs[1]); cell = row.createCell(4); cell.setCellValue(branchLineCarInfo.getLicense()); cell.setCellStyle(cs[1]); cell = row.createCell(5); cell.setCellValue(licenseDate); cell.setCellStyle(cs[1]); cell = row.createCell(6); cell.setCellValue(branchLineCarInfo.getLicenseName()); cell.setCellStyle(cs[1]); cell = row.createCell(7); cell.setCellValue(inspectionDate); cell.setCellStyle(cs[1]); cell = row.createCell(8); cell.setCellValue(branchLineCarInfo.getName()); cell.setCellStyle(cs[1]); cell = row.createCell(9); cell.setCellValue(branchLineCarInfo.getIdCard()); cell.setCellStyle(cs[1]); cell = row.createCell(10); cell.setCellValue(String.valueOf(branchLineCarInfo.getFtReceive())); cell.setCellStyle(cs[1]); cell = row.createCell(11); cell.setCellValue(changeDate); cell.setCellStyle(cs[1]); cell = row.createCell(12); cell.setCellValue(branchLineCarInfo.getTelephone()); cell.setCellStyle(cs[1]); cell = row.createCell(13); cell.setCellValue(strongInsDate); cell.setCellStyle(cs[1]); cell = row.createCell(14); cell.setCellValue(StringUtils.isEmpty(branchLineCarInfo.getTlInsurance()) ? 0D : branchLineCarInfo.getTlInsurance()); cell.setCellStyle(cs[1]); cell = row.createCell(15); cell.setCellValue(insuranceDate); cell.setCellStyle(cs[1]); cell = row.createCell(16); cell.setCellValue(branchLineCarInfo.getPoliceProve()); cell.setCellStyle(cs[1]); cell = row.createCell(17); cell.setCellValue(branchLineCarInfo.getHouseholdCopy()); cell.setCellStyle(cs[1]); cell = row.createCell(18); cell.setCellValue(branchLineCarInfo.getIdcardCopy()); cell.setCellStyle(cs[1]); cell = row.createCell(19); cell.setCellValue(branchLineCarInfo.getLicenseCopy()); cell.setCellStyle(cs[1]); cell = row.createCell(20); cell.setCellValue(branchLineCarInfo.getGuaranRespon()); cell.setCellStyle(cs[1]); cell = row.createCell(21); cell.setCellValue(branchLineCarInfo.getGuaranIncome()); cell.setCellStyle(cs[1]); cell = row.createCell(22); cell.setCellValue(branchLineCarInfo.getGuaranhouseCopy()); cell.setCellStyle(cs[1]); cell = row.createCell(23); cell.setCellValue(branchLineCarInfo.getGuaranidCopy()); cell.setCellStyle(cs[1]); cell = row.createCell(24); cell.setCellValue(branchLineCarInfo.getDrilicenseCopy()); cell.setCellStyle(cs[1]); cell = row.createCell(25); cell.setCellValue(branchLineCarInfo.getStronginsCopy()); cell.setCellStyle(cs[1]); cell = row.createCell(26); cell.setCellValue(branchLineCarInfo.getCommerinsuCopy()); cell.setCellStyle(cs[1]); cell = row.createCell(27); cell.setCellValue(branchLineCarInfo.getCertificate()); cell.setCellStyle(cs[1]); cell = row.createCell(28); cell.setCellValue(agreeDate); cell.setCellStyle(cs[1]); cell = row.createCell(29); cell.setCellValue(branchLineCarInfo.getRentalAgreement()); cell.setCellStyle(cs[1]); cell = row.createCell(30); cell.setCellValue(branchLineCarInfo.getStronginsPrompt()); cell.setCellStyle(cs[1]); cell = row.createCell(31); cell.setCellValue(branchLineCarInfo.getTlinsurancePrompt()); cell.setCellStyle(cs[1]); cell = row.createCell(32); cell.setCellValue(branchLineCarInfo.getInspectionPrompt()); cell.setCellStyle(cs[1]); cell = row.createCell(33); cell.setCellValue(branchLineCarInfo.getChangePrompt()); cell.setCellStyle(cs[1]); cell = row.createCell(34); cell.setCellValue(branchLineCarInfo.getStatus()); cell.setCellStyle(cs[1]); } flag++; } } POIUtils.exprot(workbook, response, excelName); }
From source file:com.jshuabo.reportcenter.server.service.automoblie.impl.DefaultBranchLineDrawoutRecordServiceImpl.java
@Override public void importToExcel(Map<String, Object> exprotMap, HttpServletResponse response, HttpServletRequest request, String[] title, String excelName) { User user = SecurityContextHolder.getCurrentUser(); if (StringUtils.isEmpty(exprotMap.get("subStation"))) { exprotMap.put("subStation", user.getId()); }// www . j av a2 s . c o m Map<String, Object> pageData = drawoutRecordDataMapper.exprotPageData(exprotMap); SXSSFWorkbook workbook = new SXSSFWorkbook(1000); // keep 100 rows in memory, exceeding rows int flag = 1; // int rowCount = 150000; // ?sheet long count = (Long) pageData.get("COUNT"); long pageSize = rowCount; // ?? long page = 1; if (rowCount < count) { if (count % pageSize == 0) { } else { page = count / pageSize + 1; } // } Sheet sh = workbook.createSheet(excelName + System.currentTimeMillis()); Row row = sh.createRow((short) 0); // Cell cell = null; CellStyle[] cs = POIUtils.cellStyle(workbook); POIUtils.createHeard(sh, row, cell, cs, title); for (int i = 1; i <= page; i++) { long offset = (i - 1) * pageSize; long rows = pageSize; exprotMap.put("offset", Long.valueOf(offset)); exprotMap.put("rows", Long.valueOf(rows)); // ?? List<BranchLineDrawoutRecord> drawoutRecordDataList = drawoutRecordDataMapper.exprot2Excel(exprotMap); if (drawoutRecordDataList.isEmpty()) continue; for (int rownum = 0; rownum < drawoutRecordDataList.size(); rownum++) { BranchLineDrawoutRecord bdr = drawoutRecordDataList.get(rownum); String data = DateFormatUtils.format(bdr.getDate(), DateFormatUtils.ymd); String subStation = bdr.getExtendProp3(); // ? ?sheet if (flag > pageSize) { flag = 1; sh = workbook.createSheet(excelName + System.currentTimeMillis()); row = sh.createRow((short) 0); POIUtils.createHeard(sh, row, cell, cs, title); } row = sh.createRow(flag); for (int cellnum = 0; cellnum < title.length; cellnum++) { cell = row.createCell(0); cell.setCellValue(subStation); cell.setCellStyle(cs[1]); cell = row.createCell(1); cell.setCellValue(data); cell.setCellStyle(cs[1]); cell = row.createCell(2); cell.setCellValue(bdr.getLicenseNo()); cell.setCellStyle(cs[1]); cell = row.createCell(3); cell.setCellValue(bdr.getDriver()); cell.setCellStyle(cs[1]); cell = row.createCell(4); cell.setCellValue(bdr.getLine()); cell.setCellStyle(cs[1]); cell = row.createCell(5); cell.setCellValue(bdr.getGpsNo()); cell.setCellStyle(cs[1]); cell = row.createCell(6); cell.setCellValue(bdr.getStartTime()); cell.setCellStyle(cs[1]); cell = row.createCell(7); cell.setCellValue(bdr.getStartMileage()); cell.setCellStyle(cs[1]); cell = row.createCell(8); cell.setCellValue(bdr.getStopTime()); cell.setCellStyle(cs[1]); cell = row.createCell(9); cell.setCellValue(bdr.getStopMileage()); cell.setCellStyle(cs[1]); cell = row.createCell(10); cell.setCellValue(bdr.getMileage()); cell.setCellStyle(cs[1]); cell = row.createCell(11); String temp = bdr.getExtendProp2(); cell.setCellValue(StringUtils.isEmpty(temp) ? 0D : Double.valueOf(temp)); cell.setCellStyle(cs[1]); cell = row.createCell(12); cell.setCellValue(bdr.getTicketQuantity()); cell.setCellStyle(cs[1]); cell = row.createCell(13); cell.setCellValue(bdr.getHallQuantity()); cell.setCellStyle(cs[1]); cell = row.createCell(14); cell.setCellValue(bdr.getPieQuantity()); cell.setCellStyle(cs[1]); cell = row.createCell(15); cell.setCellValue(bdr.getPcsQuantity()); cell.setCellStyle(cs[1]); cell = row.createCell(16); cell.setCellValue(bdr.getFuelPrice()); cell.setCellStyle(cs[1]); cell = row.createCell(17); cell.setCellValue(bdr.getFuelCosts()); cell.setCellStyle(cs[1]); cell = row.createCell(18); cell.setCellValue(bdr.getDay()); cell.setCellStyle(cs[1]); cell = row.createCell(19); cell.setCellValue(bdr.getRentalFee()); cell.setCellStyle(cs[1]); cell = row.createCell(20); cell.setCellValue(bdr.getParkingFee()); cell.setCellStyle(cs[1]); cell = row.createCell(21); cell.setCellValue(bdr.getAward()); cell.setCellStyle(cs[1]); cell = row.createCell(22); cell.setCellValue(bdr.getIsReim()); cell.setCellStyle(cs[1]); cell = row.createCell(23); cell.setCellValue(bdr.getNoDelivery()); cell.setCellStyle(cs[1]); cell = row.createCell(24); cell.setCellValue(bdr.getRefusal()); cell.setCellStyle(cs[1]); cell = row.createCell(25); cell.setCellValue(bdr.getVoteSign()); cell.setCellStyle(cs[1]); cell = row.createCell(26); cell.setCellValue(bdr.getRemarks()); cell.setCellStyle(cs[1]); cell = row.createCell(27); cell.setCellValue(bdr.getExtendProp1()); cell.setCellStyle(cs[1]); } flag++; } } POIUtils.exprot(workbook, response, excelName); }
From source file:com.plugin.excel.util.ExcelFileHelper.java
License:Apache License
public static void writeFile(String directory, String fileName, Map<String, List<List<ExcelCell>>> sheets, int headerRowHeight, int commentRowHeight) { if (StringUtils.isNotBlank(directory) && StringUtils.isNotBlank(fileName) && sheets != null && !sheets.isEmpty()) { SXSSFWorkbook workbook = new SXSSFWorkbook(); Font invisibleFont = workbook.createFont(); for (Entry<String, List<List<ExcelCell>>> entry : sheets.entrySet()) { // TODO: remove and logging // log.info("writeFile","Started writing sheet: "+entry.getKey()); SXSSFSheet sheet = (SXSSFSheet) workbook.createSheet(entry.getKey()); int totalColumn = 0; if (entry.getValue() != null && !entry.getValue().isEmpty()) { int rowNumber = 0; Font dataFont = null; for (List<ExcelCell> rows : entry.getValue()) { // Row row = sheet.getRow(rowNumber)!=null ? sheet.getRow(rowNumber) : rowMap.get(rowNumber); Row row = sheet.createRow(rowNumber); int rowHeight = rowNumber == 0 ? headerRowHeight : commentRowHeight; if (rowNumber == 0 || rowNumber == 1) { if (rowHeight > 0) { row.setHeight((short) rowHeight); }//from ww w.jav a 2 s . co m addDataValidation(rowNumber, sheet); } rowNumber++; if (rows != null && !rows.isEmpty()) { int cellNum = 0; Font font = null; if (rowNumber > 3 && dataFont != null) { font = dataFont; } else { font = workbook.createFont(); dataFont = font; } // as each row requires different syle with separate font Map<IndexedColors, CellStyle> s_cellStyle = new HashMap<IndexedColors, CellStyle>(); for (ExcelCell cellValue : rows) { Cell cell = row.createCell(cellNum); updateCell(cell, cellValue, s_cellStyle, workbook, font, invisibleFont); ++cellNum; } totalColumn = cellNum; } if (rowNumber == 2) {/* * auto size after DOCUMENTATION-ROW (row=2) so, we don't have to do * multiple times */ autoSize(sheet, totalColumn, false); // rowMap = createRows(workbook, sheet, rowNumber+1, excelConfig.getMaxInputRows()); } } } autoSize(sheet, totalColumn, true); } // addMetaSheet(workbook); writeWorkBook(directory, fileName, workbook); } }
From source file:com.plugin.excel.util.ExcelFileHelper.java
License:Apache License
private static void addMetaSheet(SXSSFWorkbook workbook) { SXSSFSheet instructionSheet = (SXSSFSheet) workbook.createSheet("Instrunction"); SXSSFSheet sheet = (SXSSFSheet) workbook.getSheet("Other"); ExcelUtil.copySheets(instructionSheet, sheet); }
From source file:com.romeikat.datamessie.core.base.util.ExcelSheet.java
License:Open Source License
public ExcelSheet(final SXSSFWorkbook workbook, String sheetname) { // Sheet/* ww w. j a v a 2s . c om*/ sheetname = normalizeSheetname(sheetname); sheet = workbook.createSheet(sheetname); // Date format final short dateFormat = workbook.createDataFormat().getFormat("dd.MM.yyyy"); dateFormatCellStyle = workbook.createCellStyle(); dateFormatCellStyle.setDataFormat(dateFormat); // Double number format final short doubleFormat = workbook.createDataFormat().getFormat("0.00"); doubleFormatCellStyle = workbook.createCellStyle(); doubleFormatCellStyle.setDataFormat(doubleFormat); // Indices currentRowIndex = 0; columnCursorIndex = 0; // Create first row currentRow = sheet.createRow(currentRowIndex); }
From source file:edu.harvard.hms.dbmi.bd2k.irct.ws.rs.resultconverter.XSLXTabularDataConverter.java
License:Mozilla Public License
@Override public StreamingOutput createStream(final Result result) { StreamingOutput stream = new StreamingOutput() { @Override//from ww w .ja v a2 s . c o m public void write(OutputStream outputStream) throws IOException, WebApplicationException { ResultSet rs = null; SXSSFWorkbook wb = null; try { rs = (ResultSet) result.getData(); rs.load(result.getResultSetLocation()); wb = new SXSSFWorkbook(100); // Create Sheet Sheet sh = wb.createSheet("Results"); // Create Header CellStyle headerStyle = wb.createCellStyle(); Font font = wb.createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); headerStyle.setFont(font); Row headerRow = sh.createRow(0); for (int i = 0; i < rs.getColumnSize(); i++) { Cell cell = headerRow.createCell(i); cell.setCellStyle(headerStyle); cell.setCellValue(rs.getColumn(i).getName()); } // Add data rs.beforeFirst(); int rowNum = 1; while (rs.next()) { Row row = sh.createRow(rowNum); for (int i = 0; i < rs.getColumnSize(); i++) { String value = rs.getString(i); Cell cell = row.createCell(i); if (value != null) { cell.setCellValue(rs.getString(i)); } } rowNum++; } wb.write(outputStream); } catch (ResultSetException | PersistableException e) { log.info("Error creating XSLX Stream: " + e.getMessage()); } finally { if (wb != null) { wb.close(); } if (rs != null && !rs.isClosed()) { try { rs.close(); } catch (ResultSetException e) { e.printStackTrace(); } } if (outputStream != null) { outputStream.close(); } } } }; return stream; }
From source file:egovframework.rte.fdl.excel.EgovExcelSXSSFServiceTest.java
License:Apache License
/** * [Flow #-1] ? ? : ?? ?//from w ww .jav a2s . c om */ @Test public void testWriteExcelFile() throws Exception { try { log.debug("testWriteExcelFile start...."); String sheetName1 = "first sheet"; String sheetName2 = "second sheet"; StringBuffer sb = new StringBuffer(); sb.append(fileLocation).append("/").append("testWriteExcelFile.xlsx"); // delete file if (EgovFileUtil.isExistsFile(sb.toString())) { EgovFileUtil.delete(new File(sb.toString())); log.debug("Delete file...." + sb.toString()); } SXSSFWorkbook wb = new SXSSFWorkbook(); wb.createSheet(sheetName1); wb.createSheet(sheetName2); wb.createSheet(); // ? ? SXSSFWorkbook tmp = excelService.createSXSSFWorkbook(wb, sb.toString()); // ? ? assertTrue(EgovFileUtil.isExistsFile(sb.toString())); // ? Sheet ? ? assertEquals(sheetName1, tmp.getSheetName(0)); assertEquals(sheetName2, tmp.getSheetName(1)); } catch (Exception e) { log.error(e.toString()); throw new Exception(e); } finally { log.debug("testWriteExcelFile end...."); } }
From source file:egovframework.rte.fdl.excel.EgovExcelSXSSFServiceTest.java
License:Apache License
/** * [Flow #-3] ? ? : ?? ?(? ?, Border? ?, ? ?, )? *//*from w ww. j a va 2 s. c o m*/ @Test public void testWriteExcelFileAttribute() throws Exception { try { log.debug("testWriteExcelFileAttribute start...."); short rowheight = 40; int columnwidth = 30; StringBuffer sb = new StringBuffer(); sb.append(fileLocation).append("/").append("testWriteExcelFileAttribute.xlsx"); // delete file if (EgovFileUtil.isExistsFile(sb.toString())) { EgovFileUtil.delete(new File(sb.toString())); log.debug("Delete file...." + sb.toString()); } SXSSFWorkbook wb = new SXSSFWorkbook(); Sheet sheet1 = wb.createSheet("new sheet"); wb.createSheet("second sheet"); // ? ? sheet1.setDefaultRowHeight(rowheight); sheet1.setDefaultColumnWidth(columnwidth); Font f2 = wb.createFont(); CellStyle cs = wb.createCellStyle(); cs = wb.createCellStyle(); cs.setFont(f2); cs.setWrapText(true); // cs.setAlignment(HSSFCellStyle.ALIGN_RIGHT); cs.setFillPattern(HSSFCellStyle.DIAMONDS); // ? // ? ? cs.setFillForegroundColor(new HSSFColor.BLUE().getIndex()); // cs.setFillBackgroundColor(new HSSFColor.RED().getIndex()); // sheet1.setDefaultColumnStyle((short) 0, cs); Workbook tmp = excelService.createSXSSFWorkbook(wb, sb.toString()); Sheet sheetTmp1 = tmp.getSheetAt(0); assertEquals(rowheight, sheetTmp1.getDefaultRowHeight()); assertEquals(columnwidth, sheetTmp1.getDefaultColumnWidth()); CellStyle cs1 = tmp.getCellStyleAt((short) (tmp.getNumCellStyles() - 1)); log.debug("getAlignment : " + cs1.getAlignment()); assertEquals(HSSFCellStyle.ALIGN_RIGHT, cs1.getAlignment()); log.debug("getFillPattern : " + cs1.getFillPattern()); assertEquals(HSSFCellStyle.DIAMONDS, cs1.getFillPattern()); log.debug("getFillForegroundColor : " + cs1.getFillForegroundColor()); log.debug("getFillBackgroundColor : " + cs1.getFillBackgroundColor()); assertEquals(new HSSFColor.BLUE().getIndex(), cs1.getFillForegroundColor()); assertEquals(new HSSFColor.RED().getIndex(), cs1.getFillBackgroundColor()); } catch (Exception e) { log.error(e.toString()); throw new Exception(e); } finally { log.debug("testWriteExcelFileAttribute end...."); } }