Java tutorial
/** * Copyright (c) 2012-2014 http://www.eryansky.com * * Licensed under the Apache License, Version 2.0 (the "License"); */ package com.eryansky.core.excelTools; import org.apache.commons.beanutils.PropertyUtilsBean; import org.apache.commons.lang.StringUtils; import org.apache.poi.hssf.usermodel.*; import org.apache.poi.hssf.util.HSSFColor; import org.apache.poi.ss.util.CellRangeAddress; import java.beans.PropertyDescriptor; import java.util.HashMap; import java.util.List; import java.util.Map; /** *@author : &Eryan eryanwcp@gmail.com * @date : 2014-07-31 20:36m */ public class ExcelUtils { /** * JavaBeanMap * @param obj * @return */ public static Map<String, Object> beanToMap(Object obj) { Map<String, Object> params = new HashMap<String, Object>(0); try { PropertyUtilsBean propertyUtilsBean = new PropertyUtilsBean(); PropertyDescriptor[] descriptors = propertyUtilsBean.getPropertyDescriptors(obj); for (int i = 0; i < descriptors.length; i++) { String name = descriptors[i].getName(); if (!StringUtils.equals(name, "class")) { params.put(name, propertyUtilsBean.getNestedProperty(obj, name)); } } } catch (Exception e) { e.printStackTrace(); } return params; } /** * * @param list ?? * @return */ public static TableHeaderMetaData createTableHeader(List<String> list) { TableHeaderMetaData headMeta = new TableHeaderMetaData(); for (String title : list) { TableColumn tc = new TableColumn(); tc.setDisplay(title); headMeta.addColumn(tc); } return headMeta; } /** * * @param titls ?? * @return */ public static TableHeaderMetaData createTableHeader(String[] titls) { TableHeaderMetaData headMeta = new TableHeaderMetaData(); for (String title : titls) { TableColumn tc = new TableColumn(); tc.setDisplay(title); headMeta.addColumn(tc); } return headMeta; } /** * ? * @param parents * @param children ? * @return */ public static TableHeaderMetaData createTableHeader(String[] parents, String[][] children) { TableHeaderMetaData headMeta = new TableHeaderMetaData(); TableColumn parentColumn = null; TableColumn sonColumn = null; for (int i = 0; i < parents.length; i++) { parentColumn = new TableColumn(); parentColumn.setDisplay(parents[i]); if (children != null && children[i] != null) { for (int j = 0; j < children[i].length; j++) { sonColumn = new TableColumn(); sonColumn.setDisplay(children[i][j]); parentColumn.addChild(sonColumn); } } headMeta.addColumn(parentColumn); } return headMeta; } /** * ? * * @param list ? * @param headMeta * @param fields Map?????List<Object[]>??null * @return TableData */ @SuppressWarnings("unchecked") public static TableData createTableData(List list, TableHeaderMetaData headMeta, String[] fields) { TableData td = new TableData(headMeta); TableDataRow row = null; if (list != null && list.size() > 0) { if (list.get(0).getClass().isArray()) {// for (Object obj : list) { row = new TableDataRow(td); for (Object o : (Object[]) obj) { row.addCell(o); } td.addRow(row); } } else {//JavaBeanMap for (Object obj : list) { row = new TableDataRow(td); Map<String, Object> map = (obj instanceof Map) ? (Map<String, Object>) obj : beanToMap(obj); for (String key : fields) { row.addCell(map.get(key)); } td.addRow(row); } } } return td; } public static void copySheetStyle(HSSFWorkbook destwb, HSSFSheet dest, HSSFWorkbook srcwb, HSSFSheet src) { if (src == null || dest == null) return; dest.setAlternativeExpression(src.getAlternateExpression()); dest.setAlternativeFormula(src.getAlternateFormula()); dest.setAutobreaks(src.getAutobreaks()); dest.setDialog(src.getDialog()); if (src.getColumnBreaks() != null) { for (int col : src.getColumnBreaks()) { dest.setColumnBreak(col); } } dest.setDefaultColumnWidth(src.getDefaultColumnWidth()); dest.setDefaultRowHeight(src.getDefaultRowHeight()); dest.setDefaultRowHeightInPoints(src.getDefaultRowHeightInPoints()); dest.setDisplayGuts(src.getDisplayGuts()); dest.setFitToPage(src.getFitToPage()); dest.setHorizontallyCenter(src.getHorizontallyCenter()); dest.setDisplayFormulas(src.isDisplayFormulas()); dest.setDisplayGridlines(src.isDisplayGridlines()); dest.setDisplayRowColHeadings(src.isDisplayRowColHeadings()); dest.setGridsPrinted(src.isGridsPrinted()); dest.setPrintGridlines(src.isPrintGridlines()); for (int i = 0; i < src.getNumMergedRegions(); i++) { CellRangeAddress r = src.getMergedRegion(i); dest.addMergedRegion(r); } if (src.getRowBreaks() != null) { for (int row : src.getRowBreaks()) { dest.setRowBreak(row); } } dest.setRowSumsBelow(src.getRowSumsBelow()); dest.setRowSumsRight(src.getRowSumsRight()); int maxcol = 0; for (int i = 0; i <= src.getLastRowNum(); i++) { HSSFRow row = src.getRow(i); if (row != null) { if (maxcol < row.getLastCellNum()) maxcol = row.getLastCellNum(); } } for (int col = 0; col <= maxcol; col++) { if (src.getColumnWidth(col) != src.getDefaultColumnWidth()) dest.setColumnWidth(col, src.getColumnWidth(col)); dest.setColumnHidden(col, src.isColumnHidden(col)); } } public static String dumpCellStyle(HSSFCellStyle style) { StringBuffer sb = new StringBuffer(); sb.append(style.getHidden()).append(","); sb.append(style.getLocked()).append(","); sb.append(style.getWrapText()).append(","); sb.append(style.getAlignment()).append(","); sb.append(style.getBorderBottom()).append(","); sb.append(style.getBorderLeft()).append(","); sb.append(style.getBorderRight()).append(","); sb.append(style.getBorderTop()).append(","); sb.append(style.getBottomBorderColor()).append(","); sb.append(style.getDataFormat()).append(","); sb.append(style.getFillBackgroundColor()).append(","); sb.append(style.getFillForegroundColor()).append(","); sb.append(style.getFillPattern()).append(","); sb.append(style.getIndention()).append(","); sb.append(style.getLeftBorderColor()).append(","); sb.append(style.getRightBorderColor()).append(","); sb.append(style.getRotation()).append(","); sb.append(style.getTopBorderColor()).append(","); sb.append(style.getVerticalAlignment()); return sb.toString(); } public static String dumpFont(HSSFFont font) { StringBuffer sb = new StringBuffer(); sb.append(font.getItalic()).append(",").append(font.getStrikeout()).append(",").append(font.getBoldweight()) .append(",").append(font.getCharSet()).append(",").append(font.getColor()).append(",") .append(font.getFontHeight()).append(",").append(font.getFontName()).append(",") .append(font.getTypeOffset()).append(",").append(font.getUnderline()); return sb.toString(); } public static void copyCellStyle(HSSFWorkbook destwb, HSSFCell dest, HSSFWorkbook srcwb, HSSFCell src) { if (src == null || dest == null) return; HSSFCellStyle nstyle = findStyle(src.getCellStyle(), srcwb, destwb); if (nstyle == null) { nstyle = destwb.createCellStyle(); copyCellStyle(destwb, nstyle, srcwb, src.getCellStyle()); } dest.setCellStyle(nstyle); } private static boolean isSameColor(int a, int b, HSSFPalette apalette, HSSFPalette bpalette) { if (a == b) return true; HSSFColor acolor = apalette.getColor(a); HSSFColor bcolor = bpalette.getColor(b); if (acolor == null) return true; if (bcolor == null) return false; return acolor.getHexString().equals(bcolor.getHexString()); } private static short findColor(short index, HSSFWorkbook srcwb, HSSFWorkbook destwb) { Integer id = new Integer(index); if (HSSFColor.getIndexHash().containsKey(id)) return index; if (index == HSSFColor.AUTOMATIC.index) return index; HSSFColor color = srcwb.getCustomPalette().getColor(index); if (color == null) { return index; } HSSFColor ncolor = destwb.getCustomPalette().findColor((byte) color.getTriplet()[0], (byte) color.getTriplet()[1], (byte) color.getTriplet()[2]); if (ncolor != null) return ncolor.getIndex(); destwb.getCustomPalette().setColorAtIndex(index, (byte) color.getTriplet()[0], (byte) color.getTriplet()[1], (byte) color.getTriplet()[2]); return index; } public static HSSFCellStyle findStyle(HSSFCellStyle style, HSSFWorkbook srcwb, HSSFWorkbook destwb) { HSSFPalette srcpalette = srcwb.getCustomPalette(); HSSFPalette destpalette = destwb.getCustomPalette(); for (short i = 0; i < destwb.getNumCellStyles(); i++) { HSSFCellStyle old = destwb.getCellStyleAt(i); if (old == null) continue; if (style.getAlignment() == old.getAlignment() && style.getBorderBottom() == old.getBorderBottom() && style.getBorderLeft() == old.getBorderLeft() && style.getBorderRight() == old.getBorderRight() && style.getBorderTop() == old.getBorderTop() && isSameColor(style.getBottomBorderColor(), old.getBottomBorderColor(), srcpalette, destpalette) && style.getDataFormat() == old.getDataFormat() && isSameColor(style.getFillBackgroundColor(), old.getFillBackgroundColor(), srcpalette, destpalette) && isSameColor(style.getFillForegroundColor(), old.getFillForegroundColor(), srcpalette, destpalette) && style.getFillPattern() == old.getFillPattern() && style.getHidden() == old.getHidden() && style.getIndention() == old.getIndention() && isSameColor(style.getLeftBorderColor(), old.getLeftBorderColor(), srcpalette, destpalette) && style.getLocked() == old.getLocked() && isSameColor(style.getRightBorderColor(), old.getRightBorderColor(), srcpalette, destpalette) && style.getRotation() == old.getRotation() && isSameColor(style.getTopBorderColor(), old.getTopBorderColor(), srcpalette, destpalette) && style.getVerticalAlignment() == old.getVerticalAlignment() && style.getWrapText() == old.getWrapText()) { HSSFFont oldfont = destwb.getFontAt(old.getFontIndex()); HSSFFont font = srcwb.getFontAt(style.getFontIndex()); if (oldfont.getBoldweight() == font.getBoldweight() && oldfont.getItalic() == font.getItalic() && oldfont.getStrikeout() == font.getStrikeout() && oldfont.getCharSet() == font.getCharSet() && isSameColor(oldfont.getColor(), font.getColor(), srcpalette, destpalette) && oldfont.getFontHeight() == font.getFontHeight() && oldfont.getFontName().equals(font.getFontName()) && oldfont.getTypeOffset() == font.getTypeOffset() && oldfont.getUnderline() == font.getUnderline()) { return old; } } } return null; } public static void copyCellStyle(HSSFWorkbook destwb, HSSFCellStyle dest, HSSFWorkbook srcwb, HSSFCellStyle src) { if (src == null || dest == null) return; dest.setAlignment(src.getAlignment()); dest.setBorderBottom(src.getBorderBottom()); dest.setBorderLeft(src.getBorderLeft()); dest.setBorderRight(src.getBorderRight()); dest.setBorderTop(src.getBorderTop()); dest.setBottomBorderColor(findColor(src.getBottomBorderColor(), srcwb, destwb)); dest.setDataFormat( destwb.createDataFormat().getFormat(srcwb.createDataFormat().getFormat(src.getDataFormat()))); dest.setFillPattern(src.getFillPattern()); dest.setFillForegroundColor(findColor(src.getFillForegroundColor(), srcwb, destwb)); dest.setFillBackgroundColor(findColor(src.getFillBackgroundColor(), srcwb, destwb)); dest.setHidden(src.getHidden()); dest.setIndention(src.getIndention()); dest.setLeftBorderColor(findColor(src.getLeftBorderColor(), srcwb, destwb)); dest.setLocked(src.getLocked()); dest.setRightBorderColor(findColor(src.getRightBorderColor(), srcwb, destwb)); dest.setRotation(src.getRotation()); dest.setTopBorderColor(findColor(src.getTopBorderColor(), srcwb, destwb)); dest.setVerticalAlignment(src.getVerticalAlignment()); dest.setWrapText(src.getWrapText()); HSSFFont f = srcwb.getFontAt(src.getFontIndex()); HSSFFont nf = findFont(f, srcwb, destwb); if (nf == null) { nf = destwb.createFont(); nf.setBoldweight(f.getBoldweight()); nf.setCharSet(f.getCharSet()); nf.setColor(findColor(f.getColor(), srcwb, destwb)); nf.setFontHeight(f.getFontHeight()); nf.setFontHeightInPoints(f.getFontHeightInPoints()); nf.setFontName(f.getFontName()); nf.setItalic(f.getItalic()); nf.setStrikeout(f.getStrikeout()); nf.setTypeOffset(f.getTypeOffset()); nf.setUnderline(f.getUnderline()); } dest.setFont(nf); } private static HSSFFont findFont(HSSFFont font, HSSFWorkbook src, HSSFWorkbook dest) { for (short i = 0; i < dest.getNumberOfFonts(); i++) { HSSFFont oldfont = dest.getFontAt(i); if (font.getBoldweight() == oldfont.getBoldweight() && font.getItalic() == oldfont.getItalic() && font.getStrikeout() == oldfont.getStrikeout() && font.getCharSet() == oldfont.getCharSet() && font.getColor() == oldfont.getColor() && font.getFontHeight() == oldfont.getFontHeight() && font.getFontName().equals(oldfont.getFontName()) && font.getTypeOffset() == oldfont.getTypeOffset() && font.getUnderline() == oldfont.getUnderline()) { return oldfont; } } return null; } public static void copySheet(HSSFWorkbook destwb, HSSFSheet dest, HSSFWorkbook srcwb, HSSFSheet src) { if (src == null || dest == null) return; copySheetStyle(destwb, dest, srcwb, src); for (int i = 0; i <= src.getLastRowNum(); i++) { HSSFRow row = src.getRow(i); copyRow(destwb, dest.createRow(i), srcwb, row); } } public static void copyRow(HSSFWorkbook destwb, HSSFRow dest, HSSFWorkbook srcwb, HSSFRow src) { if (src == null || dest == null) return; for (int i = 0; i <= src.getLastCellNum(); i++) { if (src.getCell(i) != null) { HSSFCell cell = dest.createCell(i); copyCell(destwb, cell, srcwb, src.getCell(i)); } } } public static void copyCell(HSSFWorkbook destwb, HSSFCell dest, HSSFWorkbook srcwb, HSSFCell src) { if (src == null) { dest.setCellType(HSSFCell.CELL_TYPE_BLANK); return; } if (src.getCellComment() != null) dest.setCellComment(src.getCellComment()); if (src.getCellStyle() != null) { HSSFCellStyle nstyle = findStyle(src.getCellStyle(), srcwb, destwb); if (nstyle == null) { nstyle = destwb.createCellStyle(); copyCellStyle(destwb, nstyle, srcwb, src.getCellStyle()); } dest.setCellStyle(nstyle); } dest.setCellType(src.getCellType()); switch (src.getCellType()) { case HSSFCell.CELL_TYPE_BLANK: break; case HSSFCell.CELL_TYPE_BOOLEAN: dest.setCellValue(src.getBooleanCellValue()); break; case HSSFCell.CELL_TYPE_FORMULA: dest.setCellFormula(src.getCellFormula()); break; case HSSFCell.CELL_TYPE_ERROR: dest.setCellErrorValue(src.getErrorCellValue()); break; case HSSFCell.CELL_TYPE_NUMERIC: dest.setCellValue(src.getNumericCellValue()); break; default: dest.setCellValue(new HSSFRichTextString(src.getRichStringCellValue().getString())); break; } } }