package base.poi; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.IOException; import java.io.InputStream; import java.io.OutputStream; import java.util.Date; 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.HSSFDataFormat; import org.apache.poi.hssf.usermodel.HSSFFont; import org.apache.poi.hssf.usermodel.HSSFPatriarch; import org.apache.poi.hssf.usermodel.HSSFPicture; 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.HSSFWorkbook; import org.apache.poi.hssf.util.Region; import org.apache.poi.poifs.filesystem.POIFSFileSystem; import org.apache.poi.util.IOUtils; public class ExcelOpt{ public HSSFWorkbook theworkbook = null; public HSSFSheet[] thesheets = null; public HSSFCellStyle[] thestyles = null; public HSSFRow currow = null; public HSSFCell curcell = null; public static short COLOR_AQUA = 49; public static short COLOR_AUTOMATIC = 64; public static short COLOR_BLACK = 8; public static short COLOR_BLUE = 12; public static short COLOR_BLUE_GREY = 54; public static short COLOR_BRIGHT_GREEN = 11; public static short COLOR_BROWN = 60; public static short COLOR_CORAL = 29; public static short COLOR_CORNFLOWER_BLUE = 24; public static short COLOR_DARK_BLUE = 18; public static short COLOR_DARK_GREEN = 58; public static short COLOR_DARK_RED = 16; public static short COLOR_DARK_TEAL = 56; public static short COLOR_DARK_YELLOW = 19; public static short COLOR_GOLD = 51; public static short COLOR_GREEN = 17; public static short COLOR_GREY_25_PERCENT = 22; public static short COLOR_GREY_40_PERCENT = 55; public static short COLOR_GREY_50_PERCENT = 23; public static short COLOR_GREY_80_PERCENT = 63; public static short COLOR_INDIGO = 62; public static short COLOR_LAVENDER = 46; public static short COLOR_LEMON_CHIFFON = 26; public static short COLOR_LIGHT_BLUE = 48; public static short COLOR_LIGHT_CORNFLOWER_BLUE = 31; public static short COLOR_LIGHT_GREEN = 42; public static short COLOR_LIGHT_ORANGE = 52; public static short COLOR_LIGHT_TURQUOISE = 41; public static short COLOR_LIGHT_YELLOW = 43; public static short COLOR_LIME = 50; public static short COLOR_MAROON = 25; public static short COLOR_OLIVE_GREEN = 59; public static short COLOR_ORANGE = 53; public static short COLOR_ORCHID = 28; public static short COLOR_PALE_BLUE = 44; public static short COLOR_PINK = 14; public static short COLOR_PLUM = 61; public static short COLOR_RED = 10; public static short COLOR_ROSE = 45; public static short COLOR_ROYAL_BLUE = 30; public static short COLOR_SEA_GREEN = 57; public static short COLOR_SKY_BLUE = 40; public static short COLOR_TAN = 47; public static short COLOR_TEAL = 21; public static short COLOR_TURQUOISE = 15; public static short COLOR_VIOLET = 20; public static short COLOR_WHITE = 9; public static short COLOR_YELLOW = 13; public static short BORDER_DASH_DOT = 9; public static short BORDER_DASH_DOT_DOT = 11; public static short BORDER_DASHED = 3; public static short BORDER_DOTTED = 7; public static short BORDER_DOUBLE = 6; public static short BORDER_HAIR = 4; public static short BORDER_MEDIUM = 2; public static short BORDER_MEDIUM_DASH_DOT = 10; public static short BORDER_MEDIUM_DASH_DOT_DOT = 12; public static short BORDER_MEDIUM_DASHED = 8; public static short BORDER_NONE = 0; public static short BORDER_SLANTED_DASH_DOT = 13; public static short BORDER_THICK = 5; public static short BORDER_THIN = 1; public boolean InitSheet(int paramInt) { if (this.theworkbook == null) return false; if ((this.thesheets == null) || (paramInt >= this.thesheets.length)) return false; this.thesheets[paramInt] = this.theworkbook.getSheetAt(paramInt); return true; } public boolean InitSheet(int paramInt, String paramString) { if (this.theworkbook == null) return false; if ((this.thesheets == null) || (paramInt >= this.thesheets.length)) return false; this.thesheets[paramInt] = this.theworkbook.createSheet(paramString); return true; } public boolean InitSheets(int paramInt) { if ((paramInt < 1) || (paramInt > 255)) return false; this.thesheets = new HSSFSheet[paramInt]; for (int i = 0; i < paramInt; ++i) this.thesheets[i] = null; return true; } public boolean InitSheets(int paramInt, String[] paramArrayOfString) { if ((paramInt > 255) || (paramInt != paramArrayOfString.length)) return false; if (this.theworkbook == null) return false; this.thesheets = new HSSFSheet[paramInt]; for (int i = 0; i < paramInt; ++i) this.thesheets[i] = this.theworkbook.createSheet(paramArrayOfString[i]); return true; } public boolean InitSheets(String paramString) { if (this.theworkbook == null) return false; this.thesheets = new HSSFSheet[1]; this.thesheets[0] = this.theworkbook.createSheet(paramString); return true; } public boolean InitStyles(int paramInt) { if ((paramInt < 1) || (paramInt > 255)) return false; this.thestyles = new HSSFCellStyle[paramInt]; for (int i = 0; i < paramInt; ++i) this.thestyles[i] = null; return true; } public void InitWorkbook() { this.theworkbook = new HSSFWorkbook(); } public void InitWorkbook(InputStream paramInputStream) throws IOException { POIFSFileSystem localPOIFSFileSystem = new POIFSFileSystem(paramInputStream); this.theworkbook = new HSSFWorkbook(localPOIFSFileSystem); if (this.theworkbook == null) return; int i = this.theworkbook.getNumberOfSheets(); if ((i > 255) || (i < 1)) return; this.thesheets = new HSSFSheet[i]; for (int j = 0; j < i; ++j) this.thesheets[j] = this.theworkbook.getSheetAt(j); } public boolean MergeCells(int paramInt1, int paramInt2, int paramInt3, int paramInt4, int paramInt5) { if ((this.thesheets == null) || (this.thesheets[paramInt1] == null)) return false; this.thesheets[paramInt1].addMergedRegion(new Region(paramInt2, (short)paramInt3, paramInt4, (short)paramInt5)); return true; } public void WriteWorkbook(OutputStream paramOutputStream) throws IOException { this.theworkbook.write(paramOutputStream); } public boolean ZoomSheet(int paramInt1, int paramInt2, int paramInt3) { if ((this.thesheets == null) || (this.thesheets[paramInt1] == null)) return false; this.thesheets[paramInt1].setZoom(paramInt2, paramInt3); return true; } public boolean createCell(int paramInt) { if (this.currow == null) return false; this.curcell = this.currow.createCell((short)paramInt); return true; } public boolean createRow(int paramInt1, int paramInt2) { if ((this.thesheets == null) || (paramInt1 >= this.thesheets.length)) return false; this.currow = this.thesheets[paramInt1].createRow(paramInt2); return true; } public boolean getCell(int paramInt) { if (this.currow == null) return false; this.curcell = this.currow.getCell((short)paramInt); if (this.curcell == null) this.curcell = this.currow.createCell((short)paramInt); return true; } public boolean getCell(int paramInt1, int paramInt2, int paramInt3) { if (!(getRow(paramInt1, paramInt2))) return false; this.curcell = this.currow.getCell((short)paramInt3); if (this.curcell == null) this.curcell = this.currow.createCell((short)paramInt3); return true; } public Date getCellDate() { if (this.curcell == null) return null; return this.curcell.getDateCellValue(); } public double getCellDouble() { if (this.curcell == null) return -1.0D; return this.curcell.getNumericCellValue(); } public String getCellValue() { if (this.curcell == null) return ""; int i = this.curcell.getCellType(); if (i == 1) { return this.curcell.getRichStringCellValue().getString(); } if (i == 0) { return String.valueOf(this.curcell.getNumericCellValue()); } return ""; } public String getCellValue(int paramInt) { if (!(getCell(paramInt))) return ""; int i = this.curcell.getCellType(); if (i == 1) { return this.curcell.getRichStringCellValue().getString(); } if (i == 0) { return String.valueOf(this.curcell.getNumericCellValue()); } return ""; } public String getCellValue(int paramInt1, int paramInt2, int paramInt3) { if (!(getCell(paramInt1, paramInt2, paramInt3))) return ""; int i = this.curcell.getCellType(); if (i == 1) { return this.curcell.getRichStringCellValue().getString(); } if (i == 0) { return String.valueOf(this.curcell.getNumericCellValue()); } return ""; } public boolean getRow(int paramInt1, int paramInt2) { if ((this.thesheets == null) || (paramInt1 >= this.thesheets.length)) return false; this.currow = this.thesheets[paramInt1].getRow(paramInt2); if (this.currow == null) this.currow = this.thesheets[paramInt1].createRow(paramInt2); return true; } public int getRowCount(int paramInt) { if ((this.thesheets == null) || (paramInt >= this.thesheets.length)) return 0; return this.thesheets[paramInt].getPhysicalNumberOfRows(); } public boolean setCellStyle(int paramInt) { if ((this.thestyles == null) || (paramInt >= this.thestyles.length)) return false; if (this.thestyles[paramInt] == null) return false; if (this.curcell == null) return false; this.curcell.setCellStyle(this.thestyles[paramInt]); return true; } public boolean setCellType(int paramInt) { if (this.curcell == null) return false; switch (paramInt) { case 0: this.curcell.setCellType(0); break; case 1: this.curcell.setCellType(1); break; default: return false; } return true; } public boolean setCellValue(double paramDouble) { if (this.curcell == null) return false; this.curcell.setCellValue(paramDouble); return true; } public boolean setCellValue(int paramInt) { if (this.curcell == null) return false; this.curcell.setCellValue(paramInt); return true; } public boolean setCellValue(String paramString) { if (this.curcell == null) return false; this.curcell.setCellValue(new HSSFRichTextString(paramString)); return true; } public boolean setCellValue(Date paramDate) { if (this.curcell == null) return false; this.curcell.setCellValue(paramDate); return true; } public boolean setCellValue(boolean paramBoolean) { if (this.curcell == null) return false; this.curcell.setCellValue(paramBoolean); return true; } public boolean setColWidth(int paramInt1, int paramInt2, int paramInt3) { if ((this.thesheets == null) || (paramInt1 >= this.thesheets.length)) return false; if (this.thesheets[paramInt1] == null) return false; this.thesheets[paramInt1].setColumnWidth((short)paramInt2, (short)paramInt3); return true; } public boolean setRowHeight(int paramInt) { if (this.currow == null) return false; this.currow.setHeight((short)paramInt); return true; } public boolean setStyleAlign(int paramInt1, int paramInt2) { if ((this.thestyles == null) || (paramInt1 >= this.thestyles.length)) return false; if (this.thestyles[paramInt1] == null) { if (this.theworkbook == null) return false; this.thestyles[paramInt1] = this.theworkbook.createCellStyle(); } switch (paramInt2) { case 1: this.thestyles[paramInt1].setAlignment((short) 1); break; case 2: this.thestyles[paramInt1].setAlignment((short) 2); break; case 3: this.thestyles[paramInt1].setAlignment((short) 3); break; default: this.thestyles[paramInt1].setAlignment((short) 0); } return true; } public boolean setStyleBorderBottom(int paramInt, short paramShort1, short paramShort2) { if ((this.thestyles == null) || (paramInt >= this.thestyles.length)) return false; if (this.thestyles[paramInt] == null) { if (this.theworkbook == null) return false; this.thestyles[paramInt] = this.theworkbook.createCellStyle(); } if (paramShort1 > 0) this.thestyles[paramInt].setBorderBottom(paramShort1); if (paramShort2 > 0) this.thestyles[paramInt].setBottomBorderColor(paramShort2); return true; } public boolean setStyleBorderLeft(int paramInt, short paramShort1, short paramShort2) { if ((this.thestyles == null) || (paramInt >= this.thestyles.length)) return false; if (this.thestyles[paramInt] == null) { if (this.theworkbook == null) return false; this.thestyles[paramInt] = this.theworkbook.createCellStyle(); } if (paramShort1 > 0) this.thestyles[paramInt].setBorderLeft(paramShort1); if (paramShort2 > 0) this.thestyles[paramInt].setLeftBorderColor(paramShort2); return true; } public boolean setStyleBorderRight(int paramInt, short paramShort1, short paramShort2) { if ((this.thestyles == null) || (paramInt >= this.thestyles.length)) return false; if (this.thestyles[paramInt] == null) { if (this.theworkbook == null) return false; this.thestyles[paramInt] = this.theworkbook.createCellStyle(); } if (paramShort1 > 0) this.thestyles[paramInt].setBorderRight(paramShort1); if (paramShort2 > 0) this.thestyles[paramInt].setRightBorderColor(paramShort2); return true; } public boolean setStyleBorderTop(int paramInt, short paramShort1, short paramShort2) { if ((this.thestyles == null) || (paramInt >= this.thestyles.length)) return false; if (this.thestyles[paramInt] == null) { if (this.theworkbook == null) return false; this.thestyles[paramInt] = this.theworkbook.createCellStyle(); } if (paramShort1 > 0) this.thestyles[paramInt].setBorderTop(paramShort1); if (paramShort2 > 0) this.thestyles[paramInt].setTopBorderColor(paramShort2); return true; } public boolean setStyleDate(int paramInt, boolean paramBoolean) { if ((this.thestyles == null) || (paramInt >= this.thestyles.length)) return false; if (this.thestyles[paramInt] == null) { if (this.theworkbook == null) return false; this.thestyles[paramInt] = this.theworkbook.createCellStyle(); } if (paramBoolean) this.thestyles[paramInt].setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm")); else this.thestyles[paramInt].setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy")); return true; } public boolean setStyleFont(int paramInt1, String paramString, int paramInt2, boolean paramBoolean1, boolean paramBoolean2, boolean paramBoolean3, int paramInt3, int paramInt4, short paramShort) { if (this.theworkbook == null) return false; if ((this.thestyles == null) || (paramInt1 >= this.thestyles.length)) return false; if (this.thestyles[paramInt1] == null) { this.thestyles[paramInt1] = this.theworkbook.createCellStyle(); } HSSFFont localHSSFFont = this.theworkbook.createFont(); if (!(paramString.equals(""))) localHSSFFont.setFontName(paramString); if (paramInt2 > 0) localHSSFFont.setFontHeightInPoints((short)paramInt2); if (paramBoolean1) localHSSFFont.setBoldweight((short) 700); else localHSSFFont.setBoldweight((short) 400); localHSSFFont.setItalic(paramBoolean2); localHSSFFont.setStrikeout(paramBoolean3); switch (paramInt3) { case 0: localHSSFFont.setUnderline((byte) 0); break; case 1: localHSSFFont.setUnderline((byte) 1); break; case 2: localHSSFFont.setUnderline((byte) 2); break; case 11: localHSSFFont.setUnderline((byte) 33); break; case 12: localHSSFFont.setUnderline((byte) 34); case 3: case 4: case 5: case 6: case 7: case 8: case 9: case 10: } switch (paramInt4) { case 0: localHSSFFont.setTypeOffset((short) 0); break; case 1: localHSSFFont.setTypeOffset((short) 1); break; case 2: localHSSFFont.setTypeOffset((short) 2); } if (paramShort > 0) localHSSFFont.setColor(paramShort); this.thestyles[paramInt1].setFont(localHSSFFont); return true; } public boolean setStyleVAlign(int paramInt1, int paramInt2) { if ((this.thestyles == null) || (paramInt1 >= this.thestyles.length)) return false; if (this.thestyles[paramInt1] == null) { if (this.theworkbook == null) return false; this.thestyles[paramInt1] = this.theworkbook.createCellStyle(); } switch (paramInt2) { case 1: this.thestyles[paramInt1].setVerticalAlignment((short) 0); break; case 2: this.thestyles[paramInt1].setVerticalAlignment((short) 1); break; case 3: this.thestyles[paramInt1].setVerticalAlignment((short) 2); break; case 4: this.thestyles[paramInt1].setVerticalAlignment((short) 3); break; default: this.thestyles[paramInt1].setVerticalAlignment((short) 1); } return true; } public boolean setStyleWrapText(int paramInt, boolean paramBoolean) { if ((this.thestyles == null) || (paramInt >= this.thestyles.length)) return false; if (this.thestyles[paramInt] == null) { if (this.theworkbook == null) return false; this.thestyles[paramInt] = this.theworkbook.createCellStyle(); } this.thestyles[paramInt].setWrapText(paramBoolean); return true; } public boolean insertPicture(int sheet,String imgsrc,int resize,short col1,int row1,short col2,int row2){ if ((this.curcell == null)||(this.thesheets[sheet] == null)||this.theworkbook==null) return false; InputStream is; int pictureIdx; try { is = new FileInputStream(imgsrc); byte[] bytes; bytes = IOUtils.toByteArray(is); pictureIdx = theworkbook.addPicture(bytes, HSSFWorkbook.PICTURE_TYPE_JPEG); is.close(); } catch (Exception e) { return false; } HSSFPatriarch patriarch = this.thesheets[sheet].createDrawingPatriarch(); HSSFClientAnchor anchor = new HSSFClientAnchor(0,0,0,0,col1,row1,col2,row2); anchor.setAnchorType(2); HSSFPicture picture = patriarch.createPicture(anchor, pictureIdx); if(resize==1){ picture.resize(); } return true; } } 封装了的POI3.2,导出带图片,支持JPG格式。图片要在本地磁盘