List of usage examples for org.apache.poi.xssf.usermodel XSSFWorkbook createSheet
@Override
public XSSFSheet createSheet(String sheetname)
From source file:br.com.techne.gluonsoft.eowexport.builder.ExcelBuilder.java
License:Apache License
/** * mtodo cria bytes de documento Excel//from ww w. j a v a 2 s. c o m * @param titles * @param columnIndex * @param dataRows * @param locale * @return * @throws Exception */ public static byte[] createExcelBytes(String[] titles, String[] columnIndex, List<HashMap<String, Object>> dataRows, Locale locale) throws Exception { //Workbook wb = new HSSFWorkbook(); XSSFWorkbook wb = new XSSFWorkbook(); byte[] outBytes; try { HashMap<String, CellStyle> styles = createStyles(wb); Sheet sheet = wb.createSheet("Tab 1"); //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 indexColumn = 0; indexColumn < titles.length; indexColumn++) { Cell cell = headerRow.createCell(indexColumn); cell.setCellValue(titles[indexColumn]); if ((titles.length - 1) < indexColumn) { cell.setCellValue(""); } else cell.setCellValue(titles[indexColumn]); cell.setCellStyle(styles.get("header")); } //freeze the first row sheet.createFreezePane(0, 1); Row row; Cell cell; int rownum = 1;//devido constar titulo, comea do indice 1 ValueCellUtil vcutil = new ValueCellUtil(locale); for (int indexRow = 0; indexRow < dataRows.size(); indexRow++, rownum++) { row = sheet.createRow(rownum); HashMap<String, Object> dataRow = dataRows.get(indexRow); if (dataRow == null) continue; List<String> keysAttribs = null; if (columnIndex.length == 0) { keysAttribs = Arrays.asList(dataRow.keySet().toArray(new String[0])); Collections.reverse(keysAttribs); } else { keysAttribs = Arrays.asList(columnIndex); } int colCt = 0; for (String keyAttrib : keysAttribs) { cell = row.createCell(colCt); String styleName; cell.setCellValue(vcutil.parseValue(dataRow.get(keyAttrib)).toString()); //zebrando tabela if (indexRow % 2 == 0) { // even row styleName = "cell_normal_even"; } else { // odd row styleName = "cell_normal_odd"; } if (indexRow == 0) { //setando auto ajuste sheet.autoSizeColumn(colCt); } cell.setCellStyle(styles.get(styleName)); colCt++; } } sheet.setZoom(75); //75% scale // Write the output to a file // write for return byte[] ByteArrayOutputStream out = new ByteArrayOutputStream(); try { wb.write(out); outBytes = out.toByteArray(); } finally { out.close(); } } finally { wb.close(); } return outBytes; }
From source file:br.uff.ic.kraken.extractdata.excel.ConflictingChunkData.java
public static void main(String[] args) { String bdName = "automaticAnalysisUpdated"; String outputPath = "/Users/gleiph/Dropbox/doutorado/publication/TSE Manual + Automatic/TSE 2/report1.0.xlsx"; //Excel stuff XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet sheet = wb.createSheet("Data"); int rowNumber = 0; XSSFRow row = sheet.createRow(rowNumber); XSSFCell cell = row.createCell(PROJECT_ID); cell.setCellValue("Project ID"); row = sheet.createRow(rowNumber++);/* w ww . ja v a 2 s. c om*/ cell = row.createCell(PROJECT_NAME); cell.setCellValue("Project name"); cell = row.createCell(REVISION_SHA); cell.setCellValue("Revision SHA"); cell = row.createCell(FILE_NAME); cell.setCellValue("File name"); cell = row.createCell(CONFLICTING_CHUNK_IDENTIFIER); cell.setCellValue("Conflicting chunk identifier"); cell = row.createCell(OUTMOST_KIND_CONFLICT); cell.setCellValue("Kind of conflict"); cell = row.createCell(AMOUNT_LANGUAGE_CONSTRUCTS); cell.setCellValue("#Language constructs"); cell = row.createCell(DEVELOPER_DECISION); cell.setCellValue("Developer decision"); cell = row.createCell(LOC_VERSION_1); cell.setCellValue("#LOC Version 1"); cell = row.createCell(LOC_VERSION_2); cell.setCellValue("#LOC Version 2"); cell = row.createCell(DEVELOPERS); cell.setCellValue("Developers"); try (Connection connection = (new JDBCConnection()).getConnection(bdName)) { ProjectJDBCDAO projectDAO = new ProjectJDBCDAO(connection); RevisionJDBCDAO revisionDAO = new RevisionJDBCDAO(connection); ConflictingFileJDBCDAO conflictingFileDAO = new ConflictingFileJDBCDAO(connection); ConflictingChunkJDBCDAO conflictingChunkDAO = new ConflictingChunkJDBCDAO(connection); List<Project> projects = projectDAO.selectAnalyzedMainProjects(); for (Project project : projects) { System.out.println(project.getName()); List<Revision> revisions = revisionDAO.selectByProjectId(project.getId()); for (Revision revision : revisions) { List<ConflictingFile> conflictingFiles = conflictingFileDAO .selectByRevisionId(revision.getId()); for (ConflictingFile conflictingFile : conflictingFiles) { if (!conflictingFile.getName().toLowerCase().endsWith(".java")) { continue; } List<ConflictingChunk> conflictingChunks = conflictingChunkDAO .selectByConflictingFileId(conflictingFile.getId()); for (ConflictingChunk conflictingChunk : conflictingChunks) { row = sheet.createRow(rowNumber++); cell = row.createCell(PROJECT_ID); cell.setCellValue(project.getId()); // System.out.print(project.getId() + ", "); cell = row.createCell(PROJECT_NAME); cell.setCellValue(project.getName()); // System.out.print(project.getName() + ", "); cell = row.createCell(REVISION_SHA); cell.setCellValue(revision.getSha()); // System.out.print(revision.getSha() + ", "); cell = row.createCell(FILE_NAME); cell.setCellValue(conflictingFile.getName()); // System.out.print(conflictingFile.getName() + ", "); cell = row.createCell(CONFLICTING_CHUNK_IDENTIFIER); cell.setCellValue(conflictingChunk.getId()); // System.out.print(conflictingChunk.getIdentifier() + ", "); String generalKindConflictOutmost = conflictingChunk.getGeneralKindConflictOutmost(); cell = row.createCell(OUTMOST_KIND_CONFLICT); String newKindConflict = replaceAttributeByVariable(generalKindConflictOutmost); cell.setCellValue(newKindConflict); // System.out.print(generalKindConflictOutmost + ", "); String[] languageConstructs = generalKindConflictOutmost.split(", "); cell = row.createCell(AMOUNT_LANGUAGE_CONSTRUCTS); cell.setCellValue(languageConstructs.length); // System.out.print(languageConstructs.length + ", "); String developerDecision = conflictingChunk.getDeveloperDecision().toString(); cell = row.createCell(DEVELOPER_DECISION); cell.setCellValue(developerDecision); // System.out.print(developerDecision + ", "); int locVersion1 = conflictingChunk.getSeparatorLine() - conflictingChunk.getBeginLine() - 1; int locVersion2 = conflictingChunk.getEndLine() - conflictingChunk.getSeparatorLine() - 1; cell = row.createCell(LOC_VERSION_1); cell.setCellValue(locVersion1); cell = row.createCell(LOC_VERSION_2); cell.setCellValue(locVersion2); // System.out.println(locVersion1 + ", " + locVersion2); cell = row.createCell(DEVELOPERS); cell.setCellValue(project.getDevelopers()); if (rowNumber % 10 == 0) { FileOutputStream out; out = new FileOutputStream(outputPath); wb.write(out); out.close(); } } } } } } catch (SQLException ex) { Logger.getLogger(ConflictingChunkData.class.getName()).log(Level.SEVERE, null, ex); } catch (FileNotFoundException ex) { Logger.getLogger(ConflictingChunkData.class.getName()).log(Level.SEVERE, null, ex); } catch (IOException ex) { Logger.getLogger(ConflictingChunkData.class.getName()).log(Level.SEVERE, null, ex); } try { FileOutputStream out; out = new FileOutputStream(outputPath); wb.write(out); out.close(); } catch (FileNotFoundException ex) { Logger.getLogger(ConflictingChunkData.class.getName()).log(Level.SEVERE, null, ex); } catch (IOException ex) { Logger.getLogger(ConflictingChunkData.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:Business.ExcelReportCreator.java
public static int create(EcoSystem system) { // Steps:- // Create a Workbook. // Create a Sheet. // Repeat the following steps until all data is processed: // Create a Row. // Create Cells in a Row. Apply formatting using CellStyle. // Write to an OutputStream. // Close the output stream. XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet sheet = workbook.createSheet("Customer Details"); //Custom font style for header CellStyle cellStyle = sheet.getWorkbook().createCellStyle(); Font font = sheet.getWorkbook().createFont(); font.setBold(true);// ww w. j a va 2 s . com cellStyle.setFont(font); int rowCount = 0; int columnCount1 = 0; //Creating header row String s[] = { "CUSTOMER ID", "CUSTOMER NAME", "CONTACT NO.", "EMAIL ID", "USAGE(Gallons)", "BILLING DATE", "TOTAL BILL($)" }; Row row1 = sheet.createRow(++rowCount); for (String s1 : s) { Cell header = row1.createCell(++columnCount1); header.setCellValue(s1); header.setCellStyle(cellStyle); } for (Network network : system.getNetworkList()) { for (Enterprise enterprise : network.getEnterpriseDirectory().getEnterpriseList()) { if (enterprise instanceof WaterEnterprise) { for (Organization organization : enterprise.getOrganizationDirectory().getOrganizationList()) { if (organization instanceof CustomerOrganization) { for (Employee employee : organization.getEmployeeDirectory().getEmployeeList()) { Customer customer = (Customer) employee; Row row = sheet.createRow(++rowCount); int columnCount = 0; for (int i = 0; i < 7; i++) { Cell cell = row.createCell(++columnCount); if (i == 0) { cell.setCellValue(customer.getId()); } else if (i == 1) { cell.setCellValue(customer.getName()); } else if (i == 2) { cell.setCellValue(customer.getContactNo()); } else if (i == 3) { cell.setCellValue(customer.getEmailId()); } else if (i == 4) { cell.setCellValue(customer.getTotalUsageVolume()); } else if (i == 5) { if (customer.getBillingDate() != null) cell.setCellValue(String.valueOf(customer.getBillingDate())); else cell.setCellValue("Bill Not yet available"); } else if (i == 6) { if (customer.getTotalBill() != 0) cell.setCellValue(customer.getTotalBill()); else cell.setCellValue("Bill Not yet available"); } } } } } } } } try (FileOutputStream outputStream = new FileOutputStream("Customer_details.xlsx")) { workbook.write(outputStream); } catch (FileNotFoundException ex) { JOptionPane.showMessageDialog(null, "File not found"); return 0; } catch (IOException ex) { JOptionPane.showMessageDialog(null, "IOException"); return 0; } return 1; }
From source file:CE.CyberedgeInterface.java
public void toExcel() { try {/* w w w.j av a2 s .com*/ con = DbConnection.getConnection(); String query = "Select * from Candidates_record"; ps = con.prepareStatement(query); rs = ps.executeQuery(); XSSFWorkbook w = new XSSFWorkbook(); XSSFSheet ws = w.createSheet("Candidates Record"); XSSFRow header = ws.createRow(0); header.createCell(0).setCellValue("ID"); header.createCell(1).setCellValue("Name"); header.createCell(2).setCellValue("Position"); header.createCell(3).setCellValue("Client"); header.createCell(4).setCellValue("Location"); header.createCell(5).setCellValue("Contact"); header.createCell(6).setCellValue("Email"); header.createCell(7).setCellValue("Experience"); header.createCell(8).setCellValue("Remark"); ws.setColumnWidth(1, 256 * 25); ws.setColumnWidth(2, 256 * 25); ws.setColumnWidth(3, 256 * 25); ws.setColumnWidth(4, 256 * 25); ws.setColumnWidth(5, 256 * 25); ws.setColumnWidth(6, 256 * 25); ws.setColumnWidth(7, 256 * 25); ws.setColumnWidth(8, 256 * 25); ws.setColumnWidth(9, 256 * 25); int index = 1; while (rs.next()) { XSSFRow row = ws.createRow(index); row.createCell(0).setCellValue(rs.getInt("Candidate_id")); row.createCell(1).setCellValue(rs.getString("Name")); row.createCell(2).setCellValue(rs.getString("Position")); row.createCell(3).setCellValue(rs.getString("Client")); row.createCell(4).setCellValue(rs.getString("Location")); row.createCell(5).setCellValue(rs.getString("Contact")); row.createCell(6).setCellValue(rs.getString("Email")); row.createCell(7).setCellValue(rs.getInt("Experience")); row.createCell(8).setCellValue(rs.getString("Remark")); index++; } String file = "C:\\..\\..\\..\\..\\..\\Cyberedge\\CandidateDetails.xlsx"; FileOutputStream fileout = new FileOutputStream(file); w.write(fileout); fileout.close(); JOptionPane.showMessageDialog(null, "File Saved"); ps.close(); rs.close(); } catch (Exception e) { JOptionPane.showMessageDialog(null, e); } }
From source file:ch.admin.isb.hermes5.business.userszenario.projektstrukturplan.ProjektstrukturplanGeneratorExcel.java
License:Apache License
public void addProjektstrukturPlan(String root, AnwenderloesungRenderingContainer container, ZipOutputBuilder zipBuilder, LocalizationEngine localizationEngine) { Szenario szenario = container.getSzenario(); SzenarioItem szenarioTree = container.getSzenarioUserData().getSzenarioTree(); try {//from w w w . j a va 2 s. c o m XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet sheet = wb.createSheet("Workbreakdownstructure_" + localizationEngine.getLanguage()); addHeader(localizationEngine, wb, sheet); XSSFCellStyle ergebnisStyle = getErgebnisStyle(wb); XSSFCellStyle modulStyle = getModulStyle(wb); XSSFCellStyle defaultStyle = getDefaultStyle(wb); int currentRow = 1; List<Phase> phasen = szenario.getPhasen(); for (Phase phase : phasen) { currentRow = addPhase(sheet, currentRow, phase, localizationEngine, ergebnisStyle, modulStyle, defaultStyle, szenarioTree) + 1; } autosize(sheet); ByteArrayOutputStream out = new ByteArrayOutputStream(); wb.write(out); zipBuilder.addFile(root + "/" + localizationEngine.getLanguage() + "/" + "Workbreakdownstructure_" + localizationEngine.getLanguage() + ".xlsx", out.toByteArray()); } catch (IOException e) { throw new IllegalStateException(e); } }
From source file:com.bc.util.XlsxExporter.java
public static void WriteInvoiceToFile(File output, CustomerOrder order, ArrayList<CustomerOrderItem> items) { try {/*from w ww .j a va 2 s . co m*/ if (output.exists()) { log.info(output.getName() + " exists. Deleting"); output.delete(); log.info("Deleted " + output.getName()); } log.info("Creating xlsx file..."); FileOutputStream fos = new FileOutputStream(output); XSSFWorkbook workBook = new XSSFWorkbook(); XSSFSheet sheet = workBook.createSheet("Order"); CellStyle style = workBook.createCellStyle(); style.setFillBackgroundColor(IndexedColors.GREY_50_PERCENT.getIndex()); style.setFillPattern(CellStyle.ALIGN_FILL); Font font = workBook.createFont(); font.setColor(IndexedColors.WHITE.getIndex()); style.setFont(font); String[] columnHeaders = { "Invoice", "Salesman", "Customer Name", "Customer Code", "PO", "Ship Date", "Post Date", "ISBN", "ISBN13", "Title", "List Price", "Price", "Quantity", "Shipped", "Discount", "Extended Price" }; log.info("Creating header row & columns"); Row row = sheet.createRow(0); for (int i = 0; i < columnHeaders.length; i++) { Cell cell = row.createCell(i); cell.setCellValue(columnHeaders[i]); cell.setCellStyle(style); sheet.setColumnWidth(i, 4500); } sheet.setColumnWidth(9, 13500); log.info("Writing " + items.size() + " records"); XSSFDataFormat decimalFormat = workBook.createDataFormat(); CellStyle dstyle = workBook.createCellStyle(); dstyle.setDataFormat(decimalFormat.getFormat("0.00")); int i = 1; for (CustomerOrderItem orderItem : items) { Row drow = sheet.createRow(i++); Hibernate.initialize(order.getCustomerOrderItems()); String strValue; Float floatValue; Integer intVal; Cell cInvoice = drow.createCell(0); strValue = order.getInvoiceNumber(); if (strValue == null) strValue = ""; cInvoice.setCellValue(order.getInvoiceNumber()); Cell cSalesman = drow.createCell(1); strValue = order.getSalesman(); if (strValue == null) strValue = ""; cSalesman.setCellValue(strValue); Cell cCustomerName = drow.createCell(2); strValue = order.getCustomer().getCompanyName(); if (strValue == null) strValue = ""; cCustomerName.setCellValue(strValue); Cell cCustomerCode = drow.createCell(3); strValue = order.getCustomerCode(); if (strValue == null) strValue = ""; cCustomerCode.setCellValue(strValue); Cell cPo = drow.createCell(4); strValue = order.getPoNumber(); if (strValue == null) strValue = ""; cPo.setCellValue(strValue); Cell cShipDate = drow.createCell(5); Date d = order.getShipDate(); if (d == null) cShipDate.setCellValue(""); else cShipDate.setCellValue("" + d.getMonth() + "/" + d.getDay() + "/" + (1900 + d.getYear())); Cell cPostDate = drow.createCell(6); d = order.getPostDate(); if (d == null) cPostDate.setCellValue(""); else cPostDate.setCellValue("" + d.getMonth() + "/" + d.getDay() + "/" + (1900 + d.getYear())); Hibernate.initialize(orderItem.getInventoryItem()); InventoryItem item = orderItem.getInventoryItem(); //orderItem.getInventoryItem(); if (item != null) { Cell cIsbn = drow.createCell(7); strValue = item.getIsbn(); if (strValue == null) strValue = ""; cIsbn.setCellValue(strValue); Cell cIsbn13 = drow.createCell(8); strValue = item.getIsbn13(); if (strValue == null) strValue = ""; cIsbn13.setCellValue(strValue); Cell cTitle = drow.createCell(9); strValue = item.getTitle(); if (strValue == null) strValue = ""; cTitle.setCellValue(strValue); Cell cListPrice = drow.createCell(10); floatValue = item.getListPrice(); cListPrice.setCellStyle(dstyle); if (floatValue == null) floatValue = 0.0f; cListPrice.setCellValue(floatValue); Cell cPrice = drow.createCell(11); floatValue = item.getSellingPrice(); cPrice.setCellStyle(dstyle); if (floatValue == null) floatValue = 0.0f; cPrice.setCellValue(floatValue); } Cell cQuantity = drow.createCell(12); intVal = orderItem.getQuantity(); log.info("Quantity : " + intVal); if (intVal == null) intVal = 0; cQuantity.setCellValue(intVal); Cell cShipped = drow.createCell(13); intVal = orderItem.getFilled(); log.info("Shipped QTY : " + intVal); if (intVal == null) intVal = 0; cShipped.setCellValue(intVal); Cell cDiscount = drow.createCell(14); cDiscount.setCellStyle(dstyle); floatValue = orderItem.getDiscount(); if (floatValue == null) floatValue = 0.0f; cDiscount.setCellValue(floatValue); Cell cExtendedPrice = drow.createCell(15); cExtendedPrice.setCellStyle(dstyle); BigDecimal dValue = orderItem.getTotalPrice(); if (dValue == null) dValue = BigDecimal.ZERO; cExtendedPrice.setCellValue(dValue.doubleValue()); } workBook.write(fos); log.info("Finished writing data, closing..."); fos.close(); log.info("Completed exporting data to " + output.getAbsolutePath()); } catch (Exception ex) { Logger.getLogger(XlsxExporter.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:com.bfa.controller.ExcelWriter.java
public void writeTimeTable() { FileOutputStream fOutStream = null; try {/*from w ww. j av a 2 s . c o m*/ File testFile = new File("TimeTable.xlsx"); XSSFWorkbook myWorkBook = new XSSFWorkbook(); String[] days = { "Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun" }; String[] times = { "8:55-9:50", "9:50-10:45", "11:15-12:10", "12:10-13:05", "14:00-14:55", "14:55-15:50" }; Iterator timeTableIterator = timeTableDetails.iterator(); int rowNum = 1, i = 0, cellNum = 0; while (timeTableIterator.hasNext()) { TimeTableBean currTimeTable = (TimeTableBean) timeTableIterator.next(); XSSFSheet mySheet = myWorkBook.createSheet(currTimeTable.getClassName()); Row row = mySheet.createRow(0); Cell cell = row.createCell(0); cell.setCellValue(currTimeTable.getClassName()); int j = 0; TimeTableSlot[][] currSlot = currTimeTable.getTimeTable(); for (TimeTableSlot[] a : currSlot) { row = mySheet.createRow(rowNum++); cell = row.createCell(0); cell.setCellValue(days[j++]); //System.out.println(days[j-1]); Row secondRow = mySheet.createRow(rowNum++); row = mySheet.createRow(rowNum++); for (TimeTableSlot p : a) { if (p != null) { int timeSlot = 0; cell = row.createCell(cellNum); Cell secondCell = secondRow.createCell(cellNum++); secondCell.setCellValue(times[timeSlot++]); cell.setCellValue(p.getSubject()); } //System.out.println(p.subjectName+"----"+p.teacherName); } cellNum = 0; //System.out.println("\n"); } rowNum = 1; } fOutStream = new FileOutputStream(testFile); myWorkBook.write(fOutStream); System.out.println("Success"); } catch (Exception ex) { Logger.getLogger(ExcelWriter.class.getName()).log(Level.SEVERE, null, ex); } finally { try { fOutStream.close(); } catch (IOException ex) { Logger.getLogger(ExcelWriter.class.getName()).log(Level.SEVERE, null, ex); } } }
From source file:com.cms.utils.DataUtil.java
License:Open Source License
/** * * @param file//from www.j av a2s. c o m * @param iSheet * @param iBeginRow * @param iFromCol * @param iToCol * @param rowBack * @param lstValidateCells * @return */ public static List isValidExcells(String mimeType, File file, int iSheet, int iBeginRow, int iFromCol, int iToCol, int rowBack, List<ValidateCells> lstValidateCells) { String fileName = file.getName(); fileName = removeDotInteger(fileName); boolean isCopySheet = true; File fileError = null; Map<String, String> mapsNameError = new HashMap<>(); List lst = null; try { if (mimeType.equalsIgnoreCase(Constants.FORMATFILE.EXCEL_XLSX)) { // xlsx lst = ExcelReaderXLSX.importExcel(file, iSheet, iBeginRow, iFromCol, iToCol, rowBack); } else if (mimeType.equalsIgnoreCase(Constants.FORMATFILE.EXCEL_XLS)) { // xls lst = ExcelReader.importExcel(file, iSheet, iBeginRow, iFromCol, iToCol, rowBack); } //If lst null return if (lst == null) { return lst; } String error = ""; ValidateCells validateCells = null; int index = 0; if (iBeginRow == 0) { index = 1; } else { index = 0; } int rowErr = 0; Object[] temp; List<String> lstReturn = Lists.newArrayList(); for (int i = index, size = lst.size(); i < size; i++) { temp = (Object[]) lst.get(i); if (checkObjectNull(temp)) { // lst.remove(temp); // i--; // continue; if (i == 0) { lst = Lists.newArrayList(); } break; } error = ""; for (int j = 0; j < lstValidateCells.size(); j++) { validateCells = lstValidateCells.get(j); if (validateCells.getPattern() != null) { error += DataUtil.validCell(i, j, (String) temp[j], validateCells.getType(), validateCells.isIsNotNull(), validateCells.getPattern()); } else { lstReturn = DataUtil.validCell(i, j, (String) temp[j], validateCells.getType(), validateCells.isIsNotNull(), validateCells.getLength()); error += lstReturn.get(0); temp[j] = lstReturn.get(1); } } if (!isStringNullOrEmpty(error)) { rowErr = i + iBeginRow; mapsNameError.put(rowErr + "", error); } } if (!mapsNameError.isEmpty()) { // FileInputStream flieInput = new FileInputStream(file); XSSFWorkbook workbookIp = null; String fileCreate = fileName + "_Error.xlsx"; FileOutputStream fileOut = new FileOutputStream(fileCreate); XSSFWorkbook workbookEp = new XSSFWorkbook(); XSSFSheet worksheetEp = workbookEp.createSheet("Thong_Tin_Loi"); XSSFCellStyle cellStyle = null; // if (isCopySheet) { if (mimeType.equalsIgnoreCase(Constants.FORMATFILE.EXCEL_XLSX)) { // xlsx workbookIp = new XSSFWorkbook(flieInput); } else if (mimeType.equalsIgnoreCase(Constants.FORMATFILE.EXCEL_XLS)) { // xls HSSFWorkbook hSSFWorkbook = new HSSFWorkbook(flieInput); workbookIp = ExcelReaderXLSX.convertWorkbookHSSFToXSSF(hSSFWorkbook); } XSSFSheet worksheetIp = workbookIp.getSheetAt(iSheet); ExcelReaderXLSX.copySheets(worksheetEp, worksheetIp, iToCol); // cellStyle = CommonUtils.styleCell(workbookEp); isCopySheet = false; } for (Map.Entry<String, String> entrySet : mapsNameError.entrySet()) { String key = entrySet.getKey(); String value = entrySet.getValue(); int row = Integer.valueOf(key); XSSFRow row5 = worksheetEp.getRow(row); if (row5 != null) { XSSFCell cellB1 = row5.createCell(iToCol + 1); cellB1.setCellValue(value); cellB1.setCellStyle(cellStyle); } } workbookEp.write(fileOut); fileOut.flush(); fileOut.close(); fileError = new File(fileCreate); Resource resource = new FileResource(fileError); Page.getCurrent().open(resource, null, false); lst = null; fileError.deleteOnExit(); } } catch (Exception e) { e.printStackTrace(); ; lst = null; } return lst; }
From source file:com.dao.DatabaseDao.java
public static void exportDB(String destpath) { try {//w w w. ja v a 2 s . c o m DB db = new DB(); Connection c = db.getConnection(); Statement st = c.createStatement(); ResultSet rs = st.executeQuery("SELECT * FROM EMPLOYEE_INFO"); XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet sheet = wb.createSheet("EmployeeInfo"); XSSFRow row = sheet.createRow(0); row.createCell(0, CellType.STRING).setCellValue("ID"); row.createCell(1, CellType.STRING).setCellValue("NAME"); row.createCell(2, CellType.STRING).setCellValue("RECEIPT_NO"); row.createCell(3, CellType.STRING).setCellValue("ENTRY_DATE"); row.createCell(4, CellType.STRING).setCellValue("SUB_RATE"); row.createCell(5, CellType.STRING).setCellValue("JAN"); row.createCell(6, CellType.STRING).setCellValue("FEB"); row.createCell(7, CellType.STRING).setCellValue("MAR"); row.createCell(8, CellType.STRING).setCellValue("APR"); row.createCell(9, CellType.STRING).setCellValue("MAY"); row.createCell(10, CellType.STRING).setCellValue("JUN"); row.createCell(11, CellType.STRING).setCellValue("JUL"); row.createCell(12, CellType.STRING).setCellValue("AUG"); row.createCell(13, CellType.STRING).setCellValue("SEP"); row.createCell(14, CellType.STRING).setCellValue("OCT"); row.createCell(15, CellType.STRING).setCellValue("NOV"); row.createCell(16, CellType.STRING).setCellValue("DECB"); row.createCell(17, CellType.STRING).setCellValue("TOTAL"); row.createCell(18, CellType.STRING).setCellValue("REMARK"); row.createCell(19, CellType.STRING).setCellValue("SECTOR"); row.createCell(20, CellType.STRING).setCellValue("SUB_FROM"); row.createCell(21, CellType.STRING).setCellValue("SUB_TO"); row.createCell(22, CellType.STRING).setCellValue("PLACE"); int i = 1; while (rs.next()) { row = sheet.createRow(i); row.createCell(0, CellType.STRING).setCellValue(rs.getLong("ID")); row.createCell(1, CellType.STRING).setCellValue(rs.getString("NAME")); row.createCell(2, CellType.STRING).setCellValue(rs.getLong("RECEIPT_NO")); row.createCell(3, CellType.STRING).setCellValue(rs.getDate("ENTRY_DATE")); row.createCell(4, CellType.STRING).setCellValue(rs.getInt("SUB_RATE")); row.createCell(5, CellType.STRING).setCellValue(rs.getInt("JAN")); row.createCell(6, CellType.STRING).setCellValue(rs.getInt("FEB")); row.createCell(7, CellType.STRING).setCellValue(rs.getInt("MAR")); row.createCell(8, CellType.STRING).setCellValue(rs.getInt("APR")); row.createCell(9, CellType.STRING).setCellValue(rs.getInt("MAY")); row.createCell(10, CellType.STRING).setCellValue(rs.getInt("JUN")); row.createCell(11, CellType.STRING).setCellValue(rs.getInt("JUL")); row.createCell(12, CellType.STRING).setCellValue(rs.getInt("AUG")); row.createCell(13, CellType.STRING).setCellValue(rs.getInt("SEP")); row.createCell(14, CellType.STRING).setCellValue(rs.getInt("OCT")); row.createCell(15, CellType.STRING).setCellValue(rs.getInt("NOV")); row.createCell(16, CellType.STRING).setCellValue(rs.getInt("DECB")); row.createCell(17, CellType.STRING).setCellValue(rs.getLong("TOTAL")); row.createCell(18, CellType.STRING).setCellValue(rs.getString("REMARK")); row.createCell(19, CellType.STRING).setCellValue(rs.getString("SECTOR")); row.createCell(20, CellType.STRING).setCellValue(rs.getString("SUB_FROM")); row.createCell(21, CellType.STRING).setCellValue(rs.getString("SUB_TO")); row.createCell(22, CellType.STRING).setCellValue(rs.getString("PLACE")); i++; } Calendar cal = Calendar.getInstance(); String filename = "BMSBackup_" + cal.get(Calendar.DAY_OF_MONTH) + "_" + (cal.get(Calendar.MONTH) + 1) + "_" + cal.get(Calendar.YEAR) + ".xlsx"; FileOutputStream fout = new FileOutputStream(destpath + "\\" + filename); wb.write(fout); fout.flush(); fout.close(); } catch (Exception e) { e.printStackTrace(); JOptionPane.showMessageDialog(null, "Database Exporting Error..." + e.getMessage(), "Error Message", JOptionPane.ERROR_MESSAGE); } }
From source file:com.dfpray.formatter.CardModel.java
/** * Exports List of B.C to Excel file, Path should include name and format .xlsx ending * @param path/*w w w.ja va 2 s .c o m*/ * @throws IOException */ public void exportToExcel(String path) throws IOException { System.out.println("Called "); BusinessCard card; Cell cell; String[] info; Double number; String cardInfo; Row row; //Create Blank workbook/sheet @SuppressWarnings("resource") XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet sheet = workbook.createSheet("Business Data"); String[] tmpArray = { "CompanyName", "ContactFirstName", "ContactLastName", "Title", "Street Address", "Suite/PO Box", "City", "State", "ZipCode", "Country", "PhoneNumber", "Extension", "MobilePhone", "FaxNumber", "EmailAddress", "Website", "CsiCodes", "CompanyFunction", "MBEAffiliations", "Labor", "ServiceArea", "CompanyNotes", "ContactLists", "CF_Alternate Email", "CF_Do Not Use", "CF_Supplier/Manuf", "CF_Trade", "CF_Union Value", "CF_Unlicensed States", "CF_Will Not Bid" }; Font headerFont = workbook.createFont(); headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD); XSSFCellStyle cellStyle = workbook.createCellStyle(); cellStyle.setFont(headerFont); XSSFCellStyle cellStyle2 = workbook.createCellStyle(); cellStyle2.setFont(headerFont); cellStyle2.setFillPattern(CellStyle.SOLID_FOREGROUND); cellStyle2.setFillForegroundColor(IndexedColors.YELLOW.getIndex()); //Write Template row = sheet.createRow(0); for (int k = 0; k < 30; k++) { cell = row.createCell(k); cell.setCellStyle(cellStyle); if (k == 0 || k == 13 || k == 14 || k == 16 || k == 17) { cell.setCellStyle(cellStyle2); } cell.setCellValue(tmpArray[k]); } //Row = Business for (int i = 1; i <= amtCards(); i++) { row = sheet.createRow(i); card = cards.get(i - 1); info = card.infoToArray(); //Create Column = Data for each Business for (int k = 0; k < 30; k++) { cardInfo = info[k]; cell = row.createCell(k); if (k == 24) continue; try { number = Double.parseDouble(cardInfo); cell.setCellValue(number); } catch (NumberFormatException e) { cell.setCellValue(cardInfo); } } card.setExported(true); } //Create file system using specific name FileOutputStream out; try { out = new FileOutputStream(new File(path)); } catch (FileNotFoundException e) { //Reset cards to not exported for (BusinessCard cardR : cards) { cardR.setExported(false); } throw new IOException(); } workbook.write(out); out.close(); }