工作需要研究POI技术导出Excel格式,目前实验版本支持Excel 2003,其他版本有待验证确认。记录工作需要,其实整体来说了解POI开源技术,学习apache(http://poi.apache.org/)提供的源码和官方文档之后,感觉难度不大,主要内容包括常规的导出(比如:单元格合并、数据迭代、单元格图片操作、字体颜色、单元格背景、单元格格式、单元格条件格式、单元格函数控制等),权当工作记录,以资后用。参考源码如下:
package com.poi; /** * @author zhaoxiaobo * @email zxbxiaobo@sina.com * @createdTime:2011-9-11 * JDK: 1.6.0_19 * POI: 3.8 (20120326) * JavaBean basic data object class */ public class MeterReadings { private String carNo; private Long departureKm; private Long currentReadings; private Long arrivalKm; private Long actualKm; private byte[] picture; public MeterReadings(String carNo, Long departureKm, Long currentReadings, Long rrivalKm, Long actualKm, byte[] picture) { this.carNo = carNo; this.departureKm = departureKm; this.currentReadings = currentReadings; this.arrivalKm = actualKm; this.actualKm = actualKm; this.picture = picture; } public String getCarNo() { return carNo; } public void setCarNo(String carNo) { this.carNo = carNo; } public Long getDepartureKm() { return departureKm; } public void setDepartureKm(Long departureKm) { this.departureKm = departureKm; } public Long getCurrentReadings() { return currentReadings; } public void setCurrentReadings(Long currentReadings) { this.currentReadings = currentReadings; } public void setArrivalKm(Long arrivalKm) { this.arrivalKm = arrivalKm; } public Long getArrivalKm() { return arrivalKm; } public void setActualKm(Long actualKm) { this.actualKm = actualKm; } public Long getActualKm() { return actualKm; } public void setPicture(byte[] picture) { this.picture = picture; } public byte[] getPicture() { return picture; } }
package com.poi; /** * @author zhaoxiaobo * @email zxbxiaobo@sina.com * @createdTime:2011-9-11 * JDK: 1.6.0_19 * POI: 3.8 (20120326) * * Generate excel data class */ import java.io.BufferedInputStream; import java.io.FileInputStream; import java.io.FileOutputStream; import java.io.IOException; import java.io.OutputStream; import java.lang.reflect.Field; import java.lang.reflect.Method; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Collection; import java.util.Date; import java.util.Iterator; import java.util.List; import java.util.regex.Matcher; import java.util.regex.Pattern; import javax.swing.JOptionPane; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFClientAnchor; import org.apache.poi.hssf.usermodel.HSSFComment; import org.apache.poi.hssf.usermodel.HSSFConditionalFormattingRule; import org.apache.poi.hssf.usermodel.HSSFFont; import org.apache.poi.hssf.usermodel.HSSFPatriarch; import org.apache.poi.hssf.usermodel.HSSFPatternFormatting; import org.apache.poi.hssf.usermodel.HSSFRichTextString; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFSheetConditionalFormatting; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.hssf.util.HSSFColor; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.FontFormatting; import org.apache.poi.ss.usermodel.IndexedColors; import org.apache.poi.ss.util.CellRangeAddress; public class ExportExcelTest<T> { /** * This is generic method, using Java reflection (invoke) mechanism ,can be placed some Java Collections data * and certain conditions of the data symbols in the form of Excel output to the specified IO device. * refer to the official website * @param sheetName sheet name * @param headers output table header name array * @param dataset Javabean data collections (get set method) * @param out output object stream(specify output path) * @param dateFormat datetime format dd/MM/yyyy (d/MM/yyyy HH:mm:ss) * */ public void exportExcel(String sheetName, String[] headers, Collection<T> dataset, OutputStream out, String dateFormat) { // declare a workbook HSSFWorkbook workbook = new HSSFWorkbook(); // create one sheet HSSFSheet sheet = workbook.createSheet(sheetName); //Fixed cell width : 15 character sheet.setDefaultColumnWidth(15); //specify width sheet.setColumnWidth(2, 5400); sheet.createFreezePane(0, 3); // create one style HSSFCellStyle style = workbook.createCellStyle(); // setup style style.setFillForegroundColor(HSSFColor.SKY_BLUE.index); style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); style.setBorderBottom(HSSFCellStyle.BORDER_THIN); style.setBorderLeft(HSSFCellStyle.BORDER_THIN); style.setBorderRight(HSSFCellStyle.BORDER_THIN); style.setBorderTop(HSSFCellStyle.BORDER_THIN); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // create one font HSSFFont font = workbook.createFont(); font.setColor(HSSFColor.VIOLET.index); font.setFontHeightInPoints((short) 12); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // the font is applied to the current style style.setFont(font); // create another style HSSFCellStyle style2 = workbook.createCellStyle(); style2.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index); style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); style2.setBorderBottom(HSSFCellStyle.BORDER_THIN); style2.setBorderLeft(HSSFCellStyle.BORDER_THIN); style2.setBorderRight(HSSFCellStyle.BORDER_THIN); style2.setBorderTop(HSSFCellStyle.BORDER_THIN); style2.setAlignment(HSSFCellStyle.ALIGN_CENTER); style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //define cell width HSSFCellStyle styleWidth = workbook.createCellStyle(); //styleWidth.set styleWidth.setFillForegroundColor(HSSFColor.BLUE_GREY.index); // create another font HSSFFont font2 = workbook.createFont(); font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL); font2.setFontHeight((short) 250); // System.out.println(font2.getFontHeight()); // System.out.println(font2.getFontHeightInPoints()); // the font is applied to the current style style2.setFont(font2); // declare one top manager for painting HSSFPatriarch patriarch = sheet.createDrawingPatriarch(); // define one comment content HSSFComment comment = patriarch.createComment(new HSSFClientAnchor(0, 0, 0, 0, (short) 4, 2, (short) 6, 5)); // setup comment content author , invalidate comment.setAuthor("Zxb Dennis"); // setup comment content comment.setString(new HSSFRichTextString("Insert into comment for pic!")); //comment.setAuthor(author) comment.setVisible(false); comment.setFillColor(255, 155, 255); comment.setLineStyle(1); HSSFCellStyle styleDate = workbook.createCellStyle(); styleDate.setAlignment(CellStyle.ALIGN_RIGHT); HSSFFont fontDate = workbook.createFont(); fontDate.setColor(HSSFColor.BLACK.index); fontDate.setFontHeightInPoints((short) 12); fontDate.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); styleDate.setFont(fontDate); HSSFRow row0 = sheet.createRow(0); row0.setHeightInPoints(20); HSSFCell cell0 = row0.createCell(0); HSSFCell cell1 = row0.createCell(1); cell0.setCellValue("Date:"); cell0.setCellStyle(styleDate); //cell0.setCellStyle(styleMerge); //Date date = (Date) value; SimpleDateFormat sdf0 = new SimpleDateFormat(dateFormat); String dateValue = sdf0.format(new Date()); cell1.setCellValue(dateValue); //merge column HSSFRow row1 = sheet.createRow(1); row1.setHeightInPoints(12); sheet.addMergedRegion(CellRangeAddress.valueOf("$A$2:$I$2")); //create new row HSSFRow row = sheet.createRow(2); row.setHeightInPoints(15); //table header column for (int i = 0, len = headers.length; i < len; i++) { HSSFCell cell = row.createCell(i); cell.setCellStyle(style); HSSFRichTextString text = new HSSFRichTextString(headers[i]); cell.setCellValue(text); cell.setCellComment(comment); } //iterator data , generate data Iterator<T> it = dataset.iterator(); HSSFSheetConditionalFormatting formating = sheet.getSheetConditionalFormatting(); int index = 2; while (it.hasNext()) { index++; row = sheet.createRow(index); T t = (T) it.next(); //Java reflection technology Javabean Field[] fields = t.getClass().getDeclaredFields(); for (int i = 0, len = fields.length; i < len; i++) { HSSFCell cell = row.createCell(i); cell.setCellStyle(style2); Field field = fields[i]; String fieldName = field.getName(); String getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1); try { Class tCls = t.getClass(); Method getMethod = tCls.getMethod(getMethodName, new Class[] { }); Object value = getMethod.invoke(t, new Object[] { }); //cast data type String textValue = null; if (value instanceof Integer) { int intValue = (Integer) value; cell.setCellValue(intValue); } else if (value instanceof Float) { float fValue = (Float) value; textValue = new HSSFRichTextString(String.valueOf(fValue)).toString(); cell.setCellValue(textValue); } else if (value instanceof Double) { double dValue = (Double) value; textValue = new HSSFRichTextString(String.valueOf(dValue)).toString(); cell.setCellValue(textValue); } else if (value instanceof Long) { long longValue = (Long) value; cell.setCellValue(longValue); } if (value instanceof Boolean) { boolean bValue = (Boolean) value; textValue = "是"; if (!bValue) { textValue = "否"; } } else if (value instanceof Date) { Date date = (Date) value; SimpleDateFormat sdf = new SimpleDateFormat(dateFormat); textValue = sdf.format(date); } else if (value instanceof byte[]) { // setup row height row.setHeightInPoints(25); // sheet.setColumnWidth(i, (short) (35.7 * 80)); // sheet.autoSizeColumn(i); byte[] bsValue = (byte[]) value; HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 1023, 255, (short) 5, index, (short) 5, index); anchor.setAnchorType(2); patriarch.createPicture(anchor, workbook.addPicture( bsValue, HSSFWorkbook.PICTURE_TYPE_JPEG)); } else { //toString textValue = value.toString(); } //validate data type if (textValue != null) { //Pattern p = Pattern.compile("^//d+(//.//d+)?$"); Pattern p = Pattern.compile("^\\d+$"); Matcher matcher = p.matcher(textValue); if (matcher.matches()) { //cell.getCellStyle(); if (!"carNo".equals(fieldName)) { setCellStyle(workbook, cell); } if ("arrivalKm".equals(fieldName)) { cell.setCellFormula("(LEFTB(B" + (index + 1)+",3)) * 10000 + C" + (index + 1)); //digit data type cell.setCellValue(Double.parseDouble(textValue)); } else if ("actualKm".equals(fieldName)) { String conditionFormat = "D" + (index + 1) + "-" + "B" + (index + 1); cell.setCellFormula(conditionFormat); //cell.setCellValue(richString); //HSSFConditionalFormattingRule rule1 = sheet.createConditionalFormattingRule("$A$1<96"); conditionFormat = "OR(E" + (index + 1) + " < 0,E" + (index + 1) + " > 500)"; //System.out.println("=== " + conditionFormat); CellRangeAddress[] range = { new CellRangeAddress(index, index ,4 ,4) }; //cell.setCellComment(comment); //cell.setCellStyle(styleWidth); addConditionFormat(formating, range, conditionFormat, cell, workbook); } else { //digit cell.setCellValue(Double.parseDouble(textValue)); } } else { HSSFRichTextString richString = new HSSFRichTextString(textValue); HSSFFont font3 = workbook.createFont(); font3.setColor(HSSFColor.BLUE.index); richString.applyFont(font3); if ("arrivalKm".equals(fieldName)) { cell.setCellFormula("(LEFTB(B" + (index + 0)+",3)) * 10000 + C" + (index + 0)); cell.setCellValue(richString); //HSSFConditionalFormattingRule rule1 = formating.createConditionalFormattingRule("$A$1<96"); } else { cell.setCellValue(richString); } } } } catch (Exception e) { e.printStackTrace(); } finally { } } } try { writeSummary(sheet, workbook); workbook.write(out); } catch (IOException e) { e.printStackTrace(); } } private void addConditionFormat(HSSFSheetConditionalFormatting formating, CellRangeAddress[] range, String formatRule, HSSFCell cell, HSSFWorkbook workbook) { try{ HSSFConditionalFormattingRule rule1 = formating.createConditionalFormattingRule(formatRule); HSSFConditionalFormattingRule[] rule = {rule1}; //cellstyle background color setup HSSFPatternFormatting patternFmt1 = rule1.createPatternFormatting(); patternFmt1.setFillBackgroundColor(HSSFColor.ROYAL_BLUE.index); //cellstyle font color setup FontFormatting font = rule1.createFontFormatting(); font.setFontStyle(false, true); font.setFontColorIndex(IndexedColors.RED.index); //patternFmt1.setFillPattern(fp); formating.addConditionalFormatting(range, rule); }catch(Exception ex) { ex.printStackTrace(); } } private void writeSummary(final HSSFSheet sheet, final HSSFWorkbook wb) { HSSFCellStyle style = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_RIGHT); HSSFFont font = wb.createFont(); font.setColor(HSSFColor.BLACK.index); font.setFontHeightInPoints((short) 12); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); style.setFont(font); //another HSSFCellStyle style2 = wb.createCellStyle(); style2.setAlignment(CellStyle.ALIGN_CENTER); HSSFFont font2 = wb.createFont(); font2.setColor(HSSFColor.GREEN.index); font2.setFontHeightInPoints((short) 12); font2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); style2.setFont(font2); HSSFRow row1 = sheet.getRow(3); HSSFCell cell = row1.createCell(7); HSSFCell cell_ = row1.createCell(8); cell.setCellValue("Max:"); cell_.setCellFormula("MAX(E4:E200)"); cell.setCellStyle(style); cell_.setCellStyle(style2); HSSFRow row2 = sheet.getRow(4); HSSFCell cel2 = row2.createCell(7); HSSFCell cel2_ = row2.createCell(8); cel2.setCellValue("Mix:"); cel2_.setCellFormula("MIN(E4:E200)"); cel2.setCellStyle(style); cel2_.setCellStyle(style2); HSSFRow row3 = sheet.getRow(5); HSSFCell cel3 = row3.createCell(7); HSSFCell cel3_ = row3.createCell(8); cel3.setCellValue("Average:"); cel3_.setCellFormula("AVERAGE(E4:E200)"); cel3.setCellStyle(style); cel3_.setCellStyle(style2); //specify width sheet.setColumnWidth(6, 1500); sheet.setColumnWidth(7, 3000); sheet.setColumnWidth(8, 2500); } private void setCellStyle(final HSSFWorkbook wb, final HSSFCell cell) { HSSFCellStyle styleThousands = wb.createCellStyle(); styleThousands.cloneStyleFrom(cell.getCellStyle()); styleThousands.setAlignment(CellStyle.ALIGN_CENTER); //styleThousands.setDataFormat(workbook.createDataFormat().getFormat("_($* #,##0.00_);_($* (#,##0.00);_($* \"-\"??_);_(@_)")); //styleThousands.setDataFormat(wb.createDataFormat().getFormat("#,##0.00")); styleThousands.setDataFormat(wb.createDataFormat().getFormat("#,##0")); cell.setCellStyle(styleThousands); } public static void main(String[] args) { // Meter readings ExportExcelTest<MeterReadings> readings = new ExportExcelTest<MeterReadings>(); String[] headers = {"Car Number", "Depart Km", "Current Readings", "Arrival Km", "Actual Km" ,"Picture"}; List<MeterReadings> dataset = new ArrayList<MeterReadings>(); //basic data try { BufferedInputStream bis = new BufferedInputStream( new FileInputStream("C:\\TEMP\\default\\xz.jpg")); byte[] buf = new byte[bis.available()]; while ((bis.read(buf)) != -1) { } dataset.add(new MeterReadings("1001", 2115827l, 6063l, 2116063l, 236l,buf)); dataset.add(new MeterReadings("1002", 2040982l, 1301l, 2041301l, 319l,buf)); dataset.add(new MeterReadings("1003", 2072589l, 2782l, 2072782l, 193l,buf)); dataset.add(new MeterReadings("1004", 1910111l, 0303l, 1910303l, 192l,buf)); dataset.add(new MeterReadings("1005", 2005604l, 5875l, 2005875l, 271l,buf)); dataset.add(new MeterReadings("1006", 2086034l, 6270l, 2086270l, 236l,buf)); dataset.add(new MeterReadings("1007", 1986280l, 6600l, 1986600l, 320l,buf)); dataset.add(new MeterReadings("1008", 2037798l, 8131l, 2038131l, 333l,buf)); dataset.add(new MeterReadings("1009", 2122344l, 2565l, 2122565l, 221l,buf)); dataset.add(new MeterReadings("1010", 2081331l, 1400l, 2081400l, 69l,buf)); OutputStream out = new FileOutputStream("D:\\tmp\\b\\MeterReadings.xls"); //readings.exportExcel("Car km data", headers, dataset, out, "dd/MM/yyyy HH:mm:ss"); readings.exportExcel("Car km data", headers, dataset, out, "dd/MM/yyyy"); out.close(); JOptionPane.showMessageDialog(null, "Export successfully!!!"); } catch (Exception e) { System.out.println("Exception:you should close file(MeterReadings.xls)!!!\n"); e.printStackTrace(); } } }
最后效果图: