import java.awt.image.BufferedImage; import java.io.ByteArrayOutputStream; import java.io.File; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.FileOutputStream; import java.io.IOException; import java.util.HashMap; import java.util.Map; import javax.imageio.ImageIO; 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.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.hssf.util.CellRangeAddress; import org.apache.poi.hssf.util.Region; import org.apache.poi.poifs.filesystem.POIFSFileSystem; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.DataFormat; import org.apache.poi.ss.usermodel.Font; import org.apache.poi.ss.usermodel.IndexedColors; import org.apache.poi.ss.usermodel.Workbook; public class ExportXLS { private static final String[][] data = { {"1", "Marketing Research Tactical Plan", "2009-09-18 16:51:25", "普通群发", "26228", null, "3234", "0.014", "346.4"}, {"2", "Marketing Research Tactical Plan", "2009-09-18 16:51:25", "普通群发", "26228", null, "3234", "0.014", "346.4"}, {"3", "Marketing Research Tactical Plan", "2009-09-18 16:51:25", "普通群发", "26228", null, "3234", "0.014", "346.4"}, {"4", "Marketing Research Tactical Plan", "2009-09-18 16:51:25", "普通群发", "26228", "43534", "3234", "0.014", "346.4"}, {"5", "Marketing Research Tactical Plan", "2009-09-18 16:51:25", "普通群发", "26228", null, "3234", "0.014", "346.4"} }; private static final String[][] sumData = { {"1", "Marketing Research Tactical Plan", "2009-09-18 16:51:25", "普通群发", "26228", null, "3234", "0.014", "346.4"}, }; /** * @param args */ //真正有意义的是下面这句话,不管你插入多少图片,都只要生成一个HSSFPatriarch 的对象 //HSSFPatriarch patriarch = sheet1.createDrawingPatriarch(); public static void main(String[] args) { // TODO Auto-generated method stub try { FileInputStream fis = new FileInputStream("xxxx.xls"); // 根据excel文件路径创建文件流 POIFSFileSystem fs = new POIFSFileSystem(fis); // 利用poi读取excel文件流 HSSFWorkbook wb = new HSSFWorkbook(fs); // 读取excel工作簿 HSSFSheet hs = wb.getSheetAt(0); ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream(); BufferedImage bufferImg = ImageIO.read(new File("loginLogo.gif")); ImageIO.write(bufferImg,"gif",byteArrayOut); int x = bufferImg.getWidth(); //hs.getRow(0).getCell(0);(int dx1, int dy1, int dx2, int dy2, short col1, int row1, short col2, int row2) HSSFClientAnchor anchor = new HSSFClientAnchor(0,0,550,210,(short) 0,0,(short)1,0); HSSFPatriarch patriarch = hs.createDrawingPatriarch(); patriarch.createPicture(anchor , wb.addPicture(byteArrayOut.toByteArray(),HSSFWorkbook.PICTURE_TYPE_JPEG)).resize(); hs.getRow(0).getCell(7).setCellValue("2010-03-01 ~ 2010-03-31"); HSSFCell comCell = hs.getRow(1).getCell(0); comCell.setCellStyle(createStyles(wb).get("cell_company")); comCell.setCellValue("读取excel工作簿"); for(int i = 0;i < data.length;i++){ HSSFRow row = hs.createRow(4 + i); for(int j = 0;j < data[i].length;j++){ HSSFCell cell = row.createCell(j); if(i == data.length - 1){ cell.setCellStyle(createStyles(wb).get("cell_sum")); if(j > 3){ cell.setCellValue(data[i][j]); } }else{ cell.setCellStyle(createStyles(wb).get("cell_value")); cell.setCellValue(data[i][j]); } } } int column = 4 + data.length; hs.addMergedRegion(new CellRangeAddress(column - 1, column - 1, 0, 3)); HSSFRow zr = hs.createRow(column); HSSFCell cell = zr.createCell(0); cell.setCellStyle(createStyles(wb).get("cell_total")); cell.setCellValue("总合计:"); HSSFCell cell1 = zr.createCell(1); cell1.setCellStyle(createStyles(wb).get("cell_total")); cell1.setCellValue(234234); FileOutputStream out = new FileOutputStream("workbook.xls"); wb.write(out); out.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } } /** * create a library of cell styles */ private static Map<String, CellStyle> createStyles(Workbook wb){ Map<String, CellStyle> styles = new HashMap<String, CellStyle>(); DataFormat df = wb.createDataFormat(); CellStyle style; Font comFont = wb.createFont(); comFont.setFontName("宋体"); comFont.setFontHeightInPoints((short)10); style = createBorderedStyle(wb, false, false, false, false); style.setFont(comFont); styles.put("cell_company", style); style = createBorderedStyle(wb, false, false, true, true); styles.put("cell_value", style); Font sumFont = wb.createFont(); sumFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); sumFont.setColor(IndexedColors.BLUE.getIndex()); style = createBorderedStyle(wb, true, true, true, true); style.setFont(sumFont); styles.put("cell_sum", style); Font totalFont = wb.createFont(); totalFont.setFontName("宋体"); totalFont.setFontHeightInPoints((short)11); totalFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); totalFont.setColor(IndexedColors.RED.getIndex()); style = createBorderedStyle(wb, false, false, false, false); style.setFont(totalFont); styles.put("cell_total", style); return styles; } private static CellStyle createBorderedStyle(Workbook wb, boolean top, boolean bottom, boolean left, boolean right){ CellStyle style = wb.createCellStyle(); if(right){ style.setBorderRight(CellStyle.BORDER_THICK); style.setRightBorderColor(IndexedColors.BLACK.getIndex()); } if(bottom){ style.setBorderBottom(CellStyle.BORDER_THICK); style.setBottomBorderColor(IndexedColors.BLACK.getIndex()); } if(left){ style.setBorderLeft(CellStyle.BORDER_THICK); style.setLeftBorderColor(IndexedColors.BLACK.getIndex()); } if(top){ style.setBorderTop(CellStyle.BORDER_THICK); style.setTopBorderColor(IndexedColors.BLACK.getIndex()); } return style; } }