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” 🙂 ) 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.
import org.apache.poi.xssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.*;
import org.apache.poi.hssf.usermodel.*;
import java.util.*;
import java.io.*;
import java.sql.*;
import java.text.*;
import javax.mail.*;
import javax.mail.internet.*;
import javax.mail.internet.MimeMessage.*;
import javax.activation.*;
public class dumpQueryAsExcel {
public static void main(String[] args) {
dumpQueryAsExcel dqae = new dumpQueryAsExcel();
}
public dumpQueryAsExcel() {
try {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy_MM_dd");
Calendar c = Calendar.getInstance();
c.setTime(new java.util.Date());
c.add(Calendar.DATE, -1);
String output = sdf.format(c.getTime());
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManager.getConnection("jdbc:oracle:thin:user/password@database_host:1521/service_name/my-service.my_domain");
ResultSet rst = conn.createStatement().executeQuery("select col1,col2,col3 from my_table");
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.createSheet(output);
XSSFCellStyle my_style = workbook.createCellStyle();
XSSFFont my_font=workbook.createFont();
my_font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
my_style.setFont(my_font);
Map data = new TreeMap();
int rownum = 0;
int cellnum = 0;
ResultSetMetaData rsmd = rst.getMetaData();
Row row = sheet.createRow(rownum++);
for (int col = 1; col <= rsmd.getColumnCount(); col++) {
Cell cell = row.createCell(cellnum++);
cell.setCellValue((String)rsmd.getColumnName(col));
cell.setCellStyle(my_style);
}
while (rst.next()) {
row = sheet.createRow(rownum++);
cellnum = 0;
for (int col = 1; col <= rsmd.getColumnCount(); col++) {
Cell cell = row.createCell(cellnum++);
if (rsmd.getColumnTypeName(col).equals("VARCHAR2"))
cell.setCellValue(rst.getString(col));
else
cell.setCellValue(rst.getDouble(col));
}
}
try {
FileOutputStream out = new FileOutputStream(new File(output + "_fandango_tickets.xlsx"));
workbook.write(out);
out.close();
emailExcelFile(output);
}
catch (Exception e) {
e.printStackTrace();
}
}
catch (Exception e) {
e.printStackTrace();
}
}
private void emailExcelFile(String subject) {
try {
String from = "[email protected]";
String[] to = {"[email protected]","[email protected]"};
String[] cc = {"[email protected]"};
String host = "your_smtp_host";
Properties props = new Properties();
props.put("mail.smtp.host", host);
//props.put("mail.debug", "true");
Session session = Session.getInstance(props);
MimeMessage message = new MimeMessage(session);
message.setFrom(new InternetAddress("[email protected]"));
InternetAddress[] addressTo = new InternetAddress[to.length];
for (int i = 0; i < to.length; i++) {
message.addRecipient(Message.RecipientType.TO, new InternetAddress(to[i]));
}
for (int i = 0; i < cc.length; i++) {
message.addRecipient(Message.RecipientType.CC, new InternetAddress(cc[i]));
}
message.setSubject("Orders to receive Fandango tickets for " + subject);
MimeBodyPart messageBodyPart = new MimeBodyPart();
messageBodyPart.setText("Attached please find the list of orders for yesterday that qualify for the Fandango ticket promotion.");
Multipart multipart = new MimeMultipart();
multipart.addBodyPart(messageBodyPart);
messageBodyPart = new MimeBodyPart();
DataSource source = new FileDataSource("/tmp/orders_to_receive_fandango_tickets.xlsx");
messageBodyPart.setDataHandler(new DataHandler(source));
messageBodyPart.setFileName(subject + "_fandango_tickets.xlsx");
multipart.addBodyPart(messageBodyPart);
message.setContent(multipart);
Transport.send(message);
}
catch (Exception e2) {
e2.printStackTrace();
}
}
}