List of usage examples for org.apache.poi.xssf.usermodel XSSFWorkbook write
@SuppressWarnings("resource") public final void write(OutputStream stream) throws IOException
From source file:com.bfa.controller.ExcelWriter.java
public void writeTimeTable() { FileOutputStream fOutStream = null; try {/*from w w w .jav 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/* w w w . j a v a2 s . 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.consensus.qa.framework.ExcelOperations.java
private void WriteAndCloseFile(String filePath, FileInputStream fileInput, XSSFWorkbook workBook) throws IOException { fileInput.close();/*from ww w . j a v a 2 s.c o m*/ FileOutputStream output_file = new FileOutputStream(new File(filePath)); workBook.write(output_file); output_file.close(); }
From source file:com.dao.DatabaseDao.java
public static void exportDB(String destpath) { try {/* w w w . ja v a 2 s . com*/ 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/*from w w w. j a v a 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(); }
From source file:com.docdoku.server.export.ExcelGenerator.java
License:Open Source License
public File generateXLSResponse(QueryResult queryResult, Locale locale, String baseURL) { File excelFile = new File("export_parts.xls"); //Blank workbook XSSFWorkbook workbook = new XSSFWorkbook(); //Create a blank sheet XSSFSheet sheet = workbook.createSheet("Parts Data"); String header = StringUtils.join(queryResult.getQuery().getSelects(), ";"); String[] columns = header.split(";"); Map<Integer, String[]> data = new HashMap<>(); String[] headerFormatted = createXLSHeaderRow(header, columns, locale); data.put(1, headerFormatted);//from w w w. ja v a2 s. com Map<Integer, String[]> commentsData = new HashMap<>(); String[] headerComments = createXLSHeaderRowComments(header, columns); commentsData.put(1, headerComments); List<String> selects = queryResult.getQuery().getSelects(); int i = 1; for (QueryResultRow row : queryResult.getRows()) { i++; data.put(i, createXLSRow(selects, row, baseURL)); commentsData.put(i, createXLSRowComments(selects, row)); } //Iterate over data and write to sheet Set<Integer> keyset = data.keySet(); int rownum = 0; for (Integer key : keyset) { Row row = sheet.createRow(rownum++); String[] objArr = data.get(key); int cellnum = 0; for (String obj : objArr) { Cell cell = row.createCell(cellnum++); cell.setCellValue(obj); } CreationHelper factory = workbook.getCreationHelper(); Drawing drawing = sheet.createDrawingPatriarch(); String[] commentsObjArr = commentsData.get(key); cellnum = 0; for (String commentsObj : commentsObjArr) { if (commentsObj.length() > 0) { Cell cell = row.getCell(cellnum) != null ? row.getCell(cellnum) : row.createCell(cellnum); // When the comment box is visible, have it show in a 1x3 space ClientAnchor anchor = factory.createClientAnchor(); anchor.setCol1(cell.getColumnIndex()); anchor.setCol2(cell.getColumnIndex() + 1); anchor.setRow1(row.getRowNum()); anchor.setRow2(row.getRowNum() + 1); Comment comment = drawing.createCellComment(anchor); RichTextString str = factory.createRichTextString(commentsObj); comment.setString(str); // Assign the comment to the cell cell.setCellComment(comment); } cellnum++; } } // Define header style Font headerFont = workbook.createFont(); headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD); headerFont.setFontHeightInPoints((short) 10); headerFont.setFontName("Courier New"); headerFont.setItalic(true); headerFont.setColor(IndexedColors.WHITE.getIndex()); CellStyle headerStyle = workbook.createCellStyle(); headerStyle.setFont(headerFont); headerStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); headerStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); // Set header style for (int j = 0; j < columns.length; j++) { Cell cell = sheet.getRow(0).getCell(j); cell.setCellStyle(headerStyle); if (cell.getCellComment() != null) { String comment = cell.getCellComment().getString().toString(); if (comment.equals(QueryField.CTX_PRODUCT_ID) || comment.equals(QueryField.CTX_SERIAL_NUMBER) || comment.equals(QueryField.PART_MASTER_NUMBER)) { for (int k = 0; k < queryResult.getRows().size(); k++) { Cell grayCell = sheet.getRow(k + 1).getCell(j) != null ? sheet.getRow(k + 1).getCell(j) : sheet.getRow(k + 1).createCell(j); grayCell.setCellStyle(headerStyle); } } } } try { //Write the workbook in file system FileOutputStream out = new FileOutputStream(excelFile); workbook.write(out); out.close(); } catch (Exception e) { LOGGER.log(Level.FINEST, null, e); } return excelFile; }
From source file:com.dotosoft.dotoquiz.tools.OldApp.java
License:Apache License
private void processPicasa() { if (DATA_TYPE.EXCEL.toString().equals(settings.getDataType())) { log.info("process data from excel!"); } else if (DATA_TYPE.GOOGLESHEET.toString().equals(settings.getDataType())) { log.info("process data from googlesheet!"); }/*from w w w . jav a 2 s .c o m*/ // variable for googlesheet GooglesheetClient googlesheetClient = null; WorksheetEntry fullSheet = null; // variable for excel FileInputStream file = null; XSSFWorkbook workbook = null; XSSFSheet sheet = null; // variable for DB Session session = null; Transaction trx = null; APPLICATION_TYPE type = APPLICATION_TYPE.valueOf(settings.getApplicationType()); try { if (APPLICATION_TYPE.DB.toString().equals(settings.getApplicationType())) { session = HibernateUtil.getSessionFactory().openSession(); trx = session.beginTransaction(); } if (DATA_TYPE.EXCEL.toString().equals(settings.getDataType())) { file = new FileInputStream(settings.getSyncDataFile()); workbook = new XSSFWorkbook(file); } else if (DATA_TYPE.GOOGLESHEET.toString().equals(settings.getDataType())) { googlesheetClient = new GooglesheetClient(auth, settings.getSyncDataFile()); } int index = 0; // -------------------------------------------------------------------------- // Extract Achievement ------------------------------------------------------ // -------------------------------------------------------------------------- // parent folder if (!APPLICATION_TYPE.SHOW_COLUMN_HEADER.toString().equals(settings.getApplicationType())) { DataTopicsParser topicAchievement = new DataTopicsParser(QuizParserConstant.ACHIEVEMENT_NAME, QuizParserConstant.EMPTY_STRING, QuizParserConstant.EMPTY_STRING, QuizParserConstant.EMPTY_STRING, QuizParserConstant.ACHIEVEMENT_NAME, QuizParserConstant.ACHIEVEMENT_DESCRIPTION, QuizParserConstant.ACHIEVEMENT_IMAGE_URL, QuizConstant.NO, new java.util.Date(), QuizConstant.SYSTEM_USER, QuizConstant.NO, type); topicAchievement = syncTopicToPicasa(topicAchievement); topicMapByTopicId.put(topicAchievement.getId(), topicAchievement); } List listRow = null; for (String achievementTab : settings.getStructure().getTabAchievements().split(";")) { String sheetName = ""; if (DATA_TYPE.EXCEL.toString().equals(settings.getDataType())) { sheet = workbook.getSheetAt(Integer.parseInt(achievementTab)); listRow = Lists.newArrayList(sheet.iterator()); sheetName = sheet.getSheetName(); } else if (DATA_TYPE.GOOGLESHEET.toString().equals(settings.getDataType())) { fullSheet = (WorksheetEntry) googlesheetClient.getWorksheet(Integer.parseInt(achievementTab)); listRow = googlesheetClient.getListRows(fullSheet); sheetName = fullSheet.getTitle().getPlainText(); } for (Object row : listRow) { if (showColumnHeader(row, sheetName)) break; ParameterAchievementParser achievement = DotoQuizStructure.convertDataToAchievement(row, settings); if (achievement != null) { if (type == APPLICATION_TYPE.DB) { session.saveOrUpdate(achievement.toParameterAchievements()); log.info("Save or update achievement: " + achievement); } else if (type == APPLICATION_TYPE.SYNC) { achievement = syncAchievementToPicasa(achievement); if (!QuizConstant.YES.equals(achievement.getIsProcessed())) { GooglesheetClient.updateSyncPicasa(settings, QuizParserConstant.PARSE_ACHIEVEMENT, row, achievement.getPicasaId(), achievement.getImagePicasaUrl(), QuizConstant.YES); } } } } } trx = CommitDB(trx, session, true); for (String dataTab : settings.getStructure().getTabTopics().split(";")) { // -------------------------------------------------------------------------- // Extract Topic // -------------------------------------------------------------------------- index = 0; String sheetName = ""; if (DATA_TYPE.EXCEL.toString().equals(settings.getDataType())) { sheet = workbook.getSheetAt(Integer.parseInt(dataTab)); listRow = Lists.newArrayList(sheet.iterator()); sheetName = sheet.getSheetName(); } else if (DATA_TYPE.GOOGLESHEET.toString().equals(settings.getDataType())) { fullSheet = (WorksheetEntry) googlesheetClient.getWorksheet(Integer.parseInt(dataTab)); listRow = googlesheetClient.getListRows(fullSheet); sheetName = fullSheet.getTitle().getPlainText(); } for (Object row : listRow) { if (showColumnHeader(row, sheetName)) break; DataTopicsParser topic = DotoQuizStructure.convertDataToTopics(row, settings); if (topic != null) { if (type == APPLICATION_TYPE.DB) { if (StringUtils.hasValue(topic.getTopicParentId())) { topic.setDatTopics(topicMapByTopicId.get(topic.getTopicParentId()).toDataTopics()); } session.saveOrUpdate(topic.toDataTopics()); log.info("Save or update topic: " + topic); } else if (type == APPLICATION_TYPE.SYNC) { topic = syncTopicToPicasa(topic); if (!QuizConstant.YES.equals(topic.getIsProcessed())) { GooglesheetClient.updateSyncPicasa(settings, QuizParserConstant.PARSE_TOPIC, row, topic.getPicasaId(), topic.getImagePicasaUrl(), QuizConstant.YES); } } topicMapByTopicId.put(topic.getId(), topic); } } } trx = CommitDB(trx, session, true); for (String dataTab : settings.getStructure().getTabQuestions().split(";")) { // -------------------------------------------------------------------------- // Extract QuestionAnswers // -------------------------------------------------------------------------- index = 0; String sheetName = ""; if (DATA_TYPE.EXCEL.toString().equals(settings.getDataType())) { sheet = workbook.getSheetAt(Integer.parseInt(dataTab)); listRow = Lists.newArrayList(sheet.iterator()); sheetName = sheet.getSheetName(); } else if (DATA_TYPE.GOOGLESHEET.toString().equals(settings.getDataType())) { fullSheet = (WorksheetEntry) googlesheetClient.getWorksheet(Integer.parseInt(dataTab)); listRow = googlesheetClient.getListRows(fullSheet); sheetName = fullSheet.getTitle().getPlainText(); } for (Object row : listRow) { if (showColumnHeader(row, sheetName)) break; DataQuestionsParser questionAnswer = DotoQuizStructure.convertDataToAnswerQuestion(row, settings); if (questionAnswer != null) { if (type == APPLICATION_TYPE.DB) { questionAnswer.setMtQuestionType(HibernateUtil.getQuestionTypeByName(session, questionAnswer.getQuestionTypeData())); session.saveOrUpdate(questionAnswer.toDataQuestion()); log.info("Save or update QuestionAnswers: " + questionAnswer); for (String topicId : questionAnswer.getTopics()) { DataTopicsParser datTopic = topicMapByTopicId.get(topicId); HibernateUtil.saveOrUpdateTopicQuestionData(session, datTopic.toDataTopics(), questionAnswer.toDataQuestion()); } } else if (type == APPLICATION_TYPE.SYNC) { questionAnswer = syncQuestionAnswersToPicasa(questionAnswer); if (!QuizConstant.YES.equals(questionAnswer.getIsProcessed())) { GooglesheetClient.updateSyncPicasa(settings, QuizParserConstant.PARSE_QUESTION_ANSWER, row, questionAnswer.getPicasaId(), questionAnswer.getImagePicasaUrl(), QuizConstant.YES); } } } } } trx = CommitDB(trx, session, false); if (DATA_TYPE.EXCEL.toString().equals(settings.getDataType())) { file.close(); log.info("Save data to file..."); FileOutputStream fos = new FileOutputStream(settings.getSyncDataFile()); workbook.write(fos); fos.close(); } log.info("Done"); } catch (Exception e) { trx.rollback(); session.close(); e.printStackTrace(); } System.exit(0); }
From source file:com.dtec.validationgen.service.IoService.java
public void writeExcel(XSSFWorkbook workBook, String fileName) throws IOException { try (FileOutputStream outPut = new FileOutputStream(new File(fileName));) { workBook.write(outPut); }//from w ww . ja va 2s .co m }
From source file:com.endro.belajar.controller.InvoiceProdukController.java
private void clickedbuttonExportDialog() { dialogExport.getButtonExport().addActionListener(new ActionListener() { @Override/* w ww. j av a 2 s.c o m*/ public void actionPerformed(ActionEvent e) { try { LocalDate tanggalAwal = dialogExport.getTanggalAwalChooser().getDate().toInstant() .atZone(ZoneId.systemDefault()).toLocalDate(); LocalDate tanggalAkhir = dialogExport.getTanggalAkhirChooser().getDate().toInstant() .atZone(ZoneId.systemDefault()).toLocalDate(); List<InvoiceOrder> daftar = invoiceDao.findAllByTanggal(tanggalAwal, tanggalAkhir); processConvertExcel(daftar); } catch (SQLException | IOException ex) { Logger.getLogger(InvoiceProdukController.class.getName()).log(Level.SEVERE, null, ex); } catch (NullPointerException ex) { JOptionPane.showMessageDialog(dialogExport, "Form tanggal diisi dengan lengkap!"); } finally { dialogExport.dispose(); dialogExport = null; } } private void processConvertExcel(List<InvoiceOrder> daftarInvoice) throws FileNotFoundException, IOException { Integer returnVal = dialogExport.getChooserSaveFile().showOpenDialog(dialogExport); if (returnVal == dialogExport.getChooserSaveFile().APPROVE_OPTION) { XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet sheet = workbook.createSheet("Just Example"); List<InvoiceOrder> list = daftarInvoice; Integer rowTable = 0; Integer cellTable = 0; CellStyle cellStyleTanggal = workbook.createCellStyle(); CellStyle cellStyleHeader = workbook.createCellStyle(); CellStyle cellStyleDouble = workbook.createCellStyle(); CreationHelper createHelper = workbook.getCreationHelper(); XSSFFont font = workbook.createFont(); cellStyleTanggal.setDataFormat(createHelper.createDataFormat().getFormat("dd/mm/yyyy")); cellStyleDouble.setDataFormat( createHelper.createDataFormat().getFormat("[$Rp-421]#,##0.0000;-[$Rp-421]#,##0.0000")); font.setBold(true); cellStyleHeader.setFont(font); cellStyleHeader.setWrapText(true); //cellStyleHeader.setFillBackgroundColor(IndexedColors.YELLOW.getIndex()); cellStyleHeader.setFillPattern(FillPatternType.DIAMONDS); for (InvoiceOrder order : list) { Row row = sheet.createRow(rowTable); if (rowTable == 0) { sheet.setColumnWidth(0, 2000); Cell cellHeader = row.createCell(0); cellHeader.setCellValue("ID"); cellHeader.setCellStyle(cellStyleHeader); sheet.setColumnWidth(1, 5000); cellHeader = row.createCell(1); cellHeader.setCellValue("Nomor Transaksi"); cellHeader.setCellStyle(cellStyleHeader); sheet.setColumnWidth(2, 4000); cellHeader = row.createCell(2); cellHeader.setCellValue("Tanggal"); cellHeader.setCellStyle(cellStyleHeader); sheet.setColumnWidth(3, 6000 * 3); cellHeader = row.createCell(3); cellHeader.setCellValue("Informasi Posting"); cellHeader.setCellStyle(cellStyleHeader); sheet.setColumnWidth(4, 4850); cellHeader = row.createCell(4); cellHeader.setCellValue("Total Sebelum Diskon"); cellHeader.setCellStyle(cellStyleHeader); sheet.setColumnWidth(5, 5000); cellHeader = row.createCell(5); cellHeader.setCellValue("Diskon"); cellHeader.setCellStyle(cellStyleHeader); sheet.setColumnWidth(6, 4500); cellHeader = row.createCell(6); cellHeader.setCellValue("Total Setelah Diskon"); cellHeader.setCellStyle(cellStyleHeader); sheet.setColumnWidth(7, 5000 * 2); cellHeader = row.createCell(7); cellHeader.setCellValue("Alamat Pengiriman"); cellHeader.setCellStyle(cellStyleHeader); } else { row.createCell(0).setCellValue((Integer) order.getPk()); row.createCell(1).setCellValue((String) order.getNomortransaksi()); Cell cellTanggal = row.createCell(2); cellTanggal.setCellValue((Date) order.getTanggal()); cellTanggal.setCellStyle(cellStyleTanggal); row.createCell(3).setCellValue((String) order.getInformasiposting()); Cell cellDouble = row.createCell(4); cellDouble.setCellValue(order.getTotalbelumdiskon()); cellDouble.setCellStyle(cellStyleDouble); cellDouble = row.createCell(5); cellDouble.setCellValue(order.getDiskonfaktur()); cellDouble.setCellStyle(cellStyleDouble); cellDouble = row.createCell(6); cellDouble.setCellValue(order.getTotalsetelahdiskon()); cellDouble.setCellStyle(cellStyleDouble); row.createCell(7).setCellValue((String) order.getAlamatpengiriman() == null ? "Null" : order.getAlamatpengiriman()); } rowTable++; } File file = dialogExport.getChooserSaveFile().getSelectedFile(); FileOutputStream outputStream = new FileOutputStream(file + File.separator + "Penjualan.xlsx"); workbook.write(outputStream); int pesan = JOptionPane.showConfirmDialog(dialogExport, "Telah tersimpan di " + file + File.separator + "Penjualan.xlsx \n Apakah anda ingin membuka file tersebut?", "Notification", JOptionPane.OK_CANCEL_OPTION); if (pesan == JOptionPane.YES_OPTION) { if ("Linux".equals(System.getProperty("os.name"))) { String runPenjualan = "xdg-open " + file + File.separator + "Penjualan.xlsx"; Runtime.getRuntime().exec(runPenjualan); } else if ("Windows".equals(System.getProperty("os.name"))) { String runPenjualan = "excel.exe /r" + file + File.separator + "Penjualan.xlsx"; Runtime.getRuntime().exec(runPenjualan); } } } else { dialogExport.getChooserSaveFile().cancelSelection(); } } }); }
From source file:com.endro.belajar.controller.MainController.java
private void clickedExport() { exportPenjualan.getButtonExport().addActionListener(new ActionListener() { @Override/*w w w . j a v a2 s.c o m*/ public void actionPerformed(ActionEvent e) { try { LocalDate tanggalAwal = exportPenjualan.getTanggalAwalChooser().getDate().toInstant() .atZone(ZoneId.systemDefault()).toLocalDate(); LocalDate tanggalAkhir = exportPenjualan.getTanggalAkhirChooser().getDate().toInstant() .atZone(ZoneId.systemDefault()).toLocalDate(); List<InvoiceOrder> daftar = invoiceDao.findAllByTanggal(tanggalAwal, tanggalAkhir); processConvertExcel(daftar); } catch (SQLException ex) { Logger.getLogger(MainController.class.getName()).log(Level.SEVERE, null, ex); } catch (NullPointerException ex) { JOptionPane.showMessageDialog(exportPenjualan, "Form tanggal diisi dengan lengkap!"); } catch (IOException ex) { Logger.getLogger(MainController.class.getName()).log(Level.SEVERE, null, ex); } finally { exportPenjualan.dispose(); exportPenjualan = null; } } private void processConvertExcel(List<InvoiceOrder> daftarInvoice) throws FileNotFoundException, IOException { Integer returnVal = exportPenjualan.getChooserSaveFile().showOpenDialog(exportPenjualan); if (returnVal == exportPenjualan.getChooserSaveFile().APPROVE_OPTION) { XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet sheet = workbook.createSheet("Just Example"); List<InvoiceOrder> list = daftarInvoice; Integer rowTable = 0; Integer cellTable = 0; CellStyle cellStyleTanggal = workbook.createCellStyle(); CellStyle cellStyleHeader = workbook.createCellStyle(); CellStyle cellStyleDouble = workbook.createCellStyle(); CreationHelper createHelper = workbook.getCreationHelper(); XSSFFont font = workbook.createFont(); cellStyleTanggal.setDataFormat(createHelper.createDataFormat().getFormat("dd/mm/yyyy")); cellStyleDouble.setDataFormat( createHelper.createDataFormat().getFormat("[$Rp-421]#,##0.0000;-[$Rp-421]#,##0.0000")); font.setBold(true); cellStyleHeader.setFont(font); cellStyleHeader.setWrapText(true); //cellStyleHeader.setFillBackgroundColor(IndexedColors.YELLOW.getIndex()); cellStyleHeader.setFillPattern(FillPatternType.DIAMONDS); for (InvoiceOrder order : list) { Row row = sheet.createRow(rowTable); if (rowTable == 0) { sheet.setColumnWidth(0, 2000); Cell cellHeader = row.createCell(0); cellHeader.setCellValue("ID"); cellHeader.setCellStyle(cellStyleHeader); sheet.setColumnWidth(1, 5000); cellHeader = row.createCell(1); cellHeader.setCellValue("Nomor Transaksi"); cellHeader.setCellStyle(cellStyleHeader); sheet.setColumnWidth(2, 4000); cellHeader = row.createCell(2); cellHeader.setCellValue("Tanggal"); cellHeader.setCellStyle(cellStyleHeader); sheet.setColumnWidth(3, 6000 * 3); cellHeader = row.createCell(3); cellHeader.setCellValue("Informasi Posting"); cellHeader.setCellStyle(cellStyleHeader); sheet.setColumnWidth(4, 4850); cellHeader = row.createCell(4); cellHeader.setCellValue("Total Sebelum Diskon"); cellHeader.setCellStyle(cellStyleHeader); sheet.setColumnWidth(5, 5000); cellHeader = row.createCell(5); cellHeader.setCellValue("Diskon"); cellHeader.setCellStyle(cellStyleHeader); sheet.setColumnWidth(6, 4500); cellHeader = row.createCell(6); cellHeader.setCellValue("Total Setelah Diskon"); cellHeader.setCellStyle(cellStyleHeader); sheet.setColumnWidth(7, 5000 * 2); cellHeader = row.createCell(7); cellHeader.setCellValue("Alamat Pengiriman"); cellHeader.setCellStyle(cellStyleHeader); } else { row.createCell(0).setCellValue((Integer) order.getPk()); row.createCell(1).setCellValue((String) order.getNomortransaksi()); Cell cellTanggal = row.createCell(2); cellTanggal.setCellValue((Date) order.getTanggal()); cellTanggal.setCellStyle(cellStyleTanggal); row.createCell(3).setCellValue((String) order.getInformasiposting()); Cell cellDouble = row.createCell(4); cellDouble.setCellValue(order.getTotalbelumdiskon()); cellDouble.setCellStyle(cellStyleDouble); cellDouble = row.createCell(5); cellDouble.setCellValue(order.getDiskonfaktur()); cellDouble.setCellStyle(cellStyleDouble); cellDouble = row.createCell(6); cellDouble.setCellValue(order.getTotalsetelahdiskon()); cellDouble.setCellStyle(cellStyleDouble); row.createCell(7).setCellValue((String) order.getAlamatpengiriman() == null ? "Null" : order.getAlamatpengiriman()); } rowTable++; } File file = exportPenjualan.getChooserSaveFile().getSelectedFile(); FileOutputStream outputStream = new FileOutputStream(file + File.separator + "Penjualan.xlsx"); workbook.write(outputStream); int pesan = JOptionPane.showConfirmDialog(exportPenjualan, "Telah tersimpan di " + file + File.separator + "Penjualan.xlsx \n Apakah anda ingin membuka file tersebut?", "Notification", JOptionPane.OK_CANCEL_OPTION); if (pesan == JOptionPane.YES_OPTION) { if ("Linux".equals(System.getProperty("os.name"))) { String runPenjualan = "xdg-open " + file + File.separator + "Penjualan.xlsx"; Runtime.getRuntime().exec(runPenjualan); } else if ("Windows".equals(System.getProperty("os.name"))) { String runPenjualan = "excel.exe /r" + file + File.separator + "Penjualan.xlsx"; Runtime.getRuntime().exec(runPenjualan); } } } else { exportPenjualan.getChooserSaveFile().cancelSelection(); } } }); }