Back in the 90’s when I first started working with computers professionally, I remember finding software for $49.95, and often much more, to do on Microsoft Windows what is now free.<\/p>\n
Apache POI (“Poor Obfuscation Implementation” \ud83d\ude42 ) is a perfect example of this. Always take the opportunity to send $20 via PayPal if given the opportunity. Even better, volunteer to either write code or document the software.<\/p>\n
\r\nimport org.apache.poi.xssf.usermodel.*;\r\nimport org.apache.poi.ss.usermodel.*;\r\nimport org.apache.poi.ss.util.*;\r\nimport org.apache.poi.hssf.usermodel.*;\r\n\r\nimport java.util.*;\r\nimport java.io.*;\r\nimport java.sql.*;\r\nimport java.text.*;\r\n\r\nimport javax.mail.*;\r\nimport javax.mail.internet.*;\r\nimport javax.mail.internet.MimeMessage.*;\r\nimport javax.activation.*;\r\n\r\npublic class dumpQueryAsExcel {\r\n public static void main(String[] args) {\r\n dumpQueryAsExcel dqae = new dumpQueryAsExcel();\r\n }\r\n\r\n public dumpQueryAsExcel() {\r\n try {\r\n\r\n SimpleDateFormat sdf = new SimpleDateFormat(\"yyyy_MM_dd\");\r\n Calendar c = Calendar.getInstance();\r\n c.setTime(new java.util.Date());\r\n c.add(Calendar.DATE, -1);\r\n String output = sdf.format(c.getTime());\r\n\r\n Class.forName(\"oracle.jdbc.driver.OracleDriver\");\r\n Connection conn = DriverManager.getConnection(\"jdbc:oracle:thin:user\/password@database_host:1521\/service_name\/my-service.my_domain\");\r\n ResultSet rst = conn.createStatement().executeQuery(\"select col1,col2,col3 from my_table\");\r\n XSSFWorkbook workbook = new XSSFWorkbook();\r\n XSSFSheet sheet = workbook.createSheet(output);\r\n XSSFCellStyle my_style = workbook.createCellStyle();\r\n XSSFFont my_font=workbook.createFont();\r\n my_font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);\r\n my_style.setFont(my_font);\r\n Map data = new TreeMap();\r\n int rownum = 0;\r\n int cellnum = 0;\r\n ResultSetMetaData rsmd = rst.getMetaData();\r\n Row row = sheet.createRow(rownum++);\r\n for (int col = 1; col <= rsmd.getColumnCount(); col++) {\r\n Cell cell = row.createCell(cellnum++);\r\n cell.setCellValue((String)rsmd.getColumnName(col));\r\n cell.setCellStyle(my_style);\r\n }\r\n while (rst.next()) {\r\n row = sheet.createRow(rownum++);\r\n cellnum = 0;\r\n for (int col = 1; col <= rsmd.getColumnCount(); col++) {\r\n Cell cell = row.createCell(cellnum++);\r\n if (rsmd.getColumnTypeName(col).equals(\"VARCHAR2\"))\r\n cell.setCellValue(rst.getString(col));\r\n else\r\n cell.setCellValue(rst.getDouble(col));\r\n }\r\n }\r\n\r\n try {\r\n FileOutputStream out = new FileOutputStream(new File(output + \"_fandango_tickets.xlsx\"));\r\n workbook.write(out);\r\n out.close();\r\n emailExcelFile(output);\r\n }\r\n catch (Exception e) {\r\n e.printStackTrace();\r\n }\r\n\r\n }\r\n catch (Exception e) {\r\n e.printStackTrace();\r\n }\r\n }\r\n\r\n private void emailExcelFile(String subject) {\r\n try {\r\n String from = \"me@myself.com\";\r\n String[] to = {\"joe@blow.com\",\"foo@bar.com\"};\r\n String[] cc = {\"you@yourself.com\"};\r\n String host = \"your_smtp_host\";\r\n\r\n Properties props = new Properties();\r\n\r\n props.put(\"mail.smtp.host\", host);\r\n \/\/props.put(\"mail.debug\", \"true\");\r\n\r\n Session session = Session.getInstance(props);\r\n\r\n MimeMessage message = new MimeMessage(session);\r\n message.setFrom(new InternetAddress(\"foo@bar.com\"));\r\n InternetAddress[] addressTo = new InternetAddress[to.length];\r\n for (int i = 0; i < to.length; i++) {\r\n message.addRecipient(Message.RecipientType.TO, new InternetAddress(to[i]));\r\n }\r\n for (int i = 0; i < cc.length; i++) {\r\n message.addRecipient(Message.RecipientType.CC, new InternetAddress(cc[i]));\r\n }\r\n message.setSubject(\"Orders to receive Fandango tickets for \" + subject);\r\n MimeBodyPart messageBodyPart = new MimeBodyPart();\r\n messageBodyPart.setText(\"Attached please find the list of orders for yesterday that qualify for the Fandango ticket promotion.\");\r\n Multipart multipart = new MimeMultipart();\r\n multipart.addBodyPart(messageBodyPart);\r\n messageBodyPart = new MimeBodyPart();\r\n DataSource source = new FileDataSource(\"\/tmp\/orders_to_receive_fandango_tickets.xlsx\");\r\n messageBodyPart.setDataHandler(new DataHandler(source));\r\n messageBodyPart.setFileName(subject + \"_fandango_tickets.xlsx\");\r\n multipart.addBodyPart(messageBodyPart);\r\n message.setContent(multipart);\r\n Transport.send(message);\r\n }\r\n catch (Exception e2) {\r\n e2.printStackTrace();\r\n }\r\n }\r\n}\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"Back in the 90’s when I first started working with computers professionally, I remember finding software for $49.95, and often much more, to do on Microsoft Windows what is now free. Apache POI (“Poor Obfuscation Implementation” \ud83d\ude42 ) is a…<\/p>\n