protected void drawImage(File reportPNG, HSSFSheet sheet, int columnStart, int columnEnd) throws IOException { HSSFPatriarch patriarch = sheet.createDrawingPatriarch(); HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 240, 0, (short) columnStart, 5, (short) columnEnd, 23); anchor.setAnchorType(3); patriarch.createPicture(anchor, loadPicture(workbook, reportPNG)); }
/** * 输出图片到指定的单元格,参考POI例子中的ReportImageUtil类。 * * @param cell -- 单元格 * @param bytes -- 图片内容 */ public static void addImageToSheet(HSSFCell cell, byte[] bytes) { if (cell == null) { _log.showError("-----insertImageToSheet: cell is null!"); return; } if (bytes == null || bytes.length == 0) { _log.showError("-----insertImageToSheet: bytes is null!"); return; } // 取所在表单对象 HSSFSheet sheet = cell.getSheet(); // 取图片输出行与列 int firstRow = cell.getRowIndex(); int lastRow = cell.getRowIndex(); int firstCol = cell.getColumnIndex(); int lastCol = cell.getColumnIndex(); // 取单元格所在的区域 CellRangeAddress range = getMergedRegion(cell); if (range != null) { firstRow = range.getFirstRow(); lastRow = range.getLastRow(); firstCol = range.getFirstColumn(); lastCol = range.getLastColumn(); } _log.showDebug( "---------image cells=[" + firstRow + "," + firstCol + "," + lastRow + "," + lastCol + "]"); // 图片输出要比单元格的高与宽偏5个值,保留单元的边框,宽度1023表示填充满,高度255表示填充满 HSSFClientAnchor anchor = new HSSFClientAnchor(5, 5, 1023, 255, (short) firstCol, firstRow, (short) lastCol, lastRow); anchor.setAnchorType(HSSFClientAnchor.MOVE_AND_RESIZE); // 取图片管理器,如果没有则创建 HSSFPatriarch draw = sheet.getDrawingPatriarch(); if (draw == null) { draw = sheet.createDrawingPatriarch(); } // 插入新图片,返回的新图片序号无效 sheet.getWorkbook().addPicture(bytes, HSSFWorkbook.PICTURE_TYPE_JPEG); // 上面代码中新建图片的序号没有考虑原有图片数量,所以取原图片数量+1作为新图片的序号 List<HSSFPicture> lsPicture = getAllPicture(sheet); int index = lsPicture.size() + 1; _log.showDebug("---------new image index=" + index); draw.createPicture(anchor, index); }
/** * 这是一个通用的方法,利用了JAVA的反射机制,可以将放置在JAVA集合中并且符号一定条件的数据以EXCEL 的形式输出到指定IO设备上 * * @param headers 表格属性列名数组 * @param dataset 需要显示的数据集合,集合中一定要放置符合javabean风格的类的对象。此方法支持的 * javabean属性的数据类型有基本数据类型及String,Date,byte[](图片数据) */ @SuppressWarnings("unchecked") public Workbook exportExcel(String[] headers, Collection<T> dataset) { // 生成一个表格 HSSFSheet sheet = workbook.createSheet(sheetName); // 设置表格默认列宽度为15个字节 if (useStyle) sheet.setDefaultColumnWidth(defaultColumnWidth); // 生成一个样式 // 声明一个画图的顶级管理器 HSSFPatriarch patriarch = sheet.createDrawingPatriarch(); // 定义注释的大小和位置,详见文档 HSSFComment comment = patriarch.createComment(new HSSFClientAnchor(0, 0, 0, 0, (short) 4, 2, (short) 6, 5)); // 设置注释内容 comment.setString(new HSSFRichTextString(docPrse)); // 设置注释作者,当鼠标移动到单元格上是可以在状态栏中看到该内容. comment.setAuthor(author); // 产生表格标题行 HSSFRow row = sheet.createRow(startRow); int colNum1 = startCol; for (short i = 0; i < headers.length; i++) { HSSFCell cell = row.createCell(colNum1); if (useStyle) cell.setCellStyle(style); HSSFRichTextString text = new HSSFRichTextString(headers[i]); cell.setCellValue(text); colNum1++; } // 遍历集合数据,产生数据行 Iterator<T> it = dataset.iterator(); int index = startRow; while (it.hasNext()) { index++; row = sheet.createRow(index); T t = (T) it.next(); // 利用反射,根据javabean属性的先后顺序,动态调用getXxx()方法得到属性值 Field[] fields = t.getClass().getDeclaredFields(); int colRow2 = startCol; for (short i = 0; i < fields.length; i++) { HSSFCell cell = row.createCell(colRow2); if (useStyle) 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[] {}); // 判断值的类型后进行强制类型转换 String textValue = null; 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[]) { // 有图片时,设置行高为60px; row.setHeightInPoints(picCellHeight); // 设置图片所在列宽度为80px,注意这里单位的一个换算 sheet.setColumnWidth(i, picCellWidth); // sheet.autoSizeColumn(i); byte[] bsValue = (byte[]) value; HSSFClientAnchor anchor = new HSSFClientAnchor( 0, 0, 1023, 255, (short) colRow2, index, (short) colRow2, index); anchor.setAnchorType(2); patriarch.createPicture( anchor, workbook.addPicture(bsValue, HSSFWorkbook.PICTURE_TYPE_JPEG)); } else { // 其它数据类型都当作字符串简单处理 textValue = value.toString(); } // 如果不是图片数据,就利用正则表达式判断textValue是否全部由数字组成 if (textValue != null) { Pattern p = Pattern.compile("^//d+(//.//d+)?$"); Matcher matcher = p.matcher(textValue); if (matcher.matches()) { // 是数字当作double处理 cell.setCellValue(Double.parseDouble(textValue)); } else { HSSFRichTextString richString = new HSSFRichTextString(textValue); HSSFFont font3 = workbook.createFont(); font3.setColor(HSSFColor.BLUE.index); richString.applyFont(font3); cell.setCellValue(richString); } } } catch (SecurityException e) { e.printStackTrace(); } catch (NoSuchMethodException e) { e.printStackTrace(); } catch (IllegalArgumentException e) { e.printStackTrace(); } catch (IllegalAccessException e) { e.printStackTrace(); } catch (InvocationTargetException e) { e.printStackTrace(); } finally { // 清理资源 } colRow2++; } } return workbook; }
@SuppressWarnings("deprecation") public Reporteconcepto( Rgenerador objeto, Rgenerador objeto2, Boolean siinicial, String partida, String ruta, String ruta2, String path) throws IOException { this.FILE = path; // creacion del libro que contedra nuestro reporte libro = new HSSFWorkbook(); // cracion de la hoja que estara contenida en nuestro libro hoja = libro.createSheet("new sheet"); // Definicion de estilo que contendra nuestro encabezado // ***************************************************************************************************************************************************** // definicion estilos de celdas, establecimineto del tipo de fuente fuenteen = libro.createFont(); fuenteen.setFontHeightInPoints((short) 12); fuenteen.setFontName(fuenteen.FONT_ARIAL); fuenteen.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // Creacion del objeto que se encargara de aplicar el estilo a la celda esceldaen = libro.createCellStyle(); esceldaen.setWrapText(true); esceldaen.setAlignment(HSSFCellStyle.ALIGN_LEFT); esceldaen.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP); esceldaen.setFont(fuenteen); // establecimiento de sombreado de nuestra celda esceldaen.setFillForegroundColor((short) 44); esceldaen.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // Definicion de estilo que contendra los tiulos // ***************************************************************************************************************************************************** // definicion estilos de celdas, establecimineto del tipo de fuente fuentet = libro.createFont(); fuentet.setFontHeightInPoints((short) 11); fuentet.setFontName(fuentet.FONT_ARIAL); fuentet.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // Creacion del objeto que se encargara de aplicar el estilo a la celda esceldat = libro.createCellStyle(); esceldat.setWrapText(true); esceldat.setAlignment(HSSFCellStyle.ALIGN_CENTER); esceldat.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP); esceldat.setFont(fuentet); // establecimiento de bordes esceldat.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); esceldat.setBottomBorderColor((short) 8); esceldat.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM); esceldat.setLeftBorderColor((short) 8); esceldat.setBorderRight(HSSFCellStyle.BORDER_MEDIUM); esceldat.setRightBorderColor((short) 8); esceldat.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); esceldat.setRightBorderColor((short) 8); // establecimiento de sombreado de nuestra celda esceldat.setFillForegroundColor((short) 22); esceldat.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // ***************************************************************************************************************************************************** // Definicion del estilo de la celda de nuestros datos que contendra el // reporte // definicion estilos de celdas, establecimineto del tipo de fuente fuentein = libro.createFont(); fuentein.setFontHeightInPoints((short) 10); fuentein.setFontName(fuentein.FONT_ARIAL); fuentein.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // Creacion del objeto que se encargara de aplicar el estilo a la celda esceldain = libro.createCellStyle(); esceldain.setWrapText(true); esceldain.setAlignment(HSSFCellStyle.ALIGN_CENTER); esceldain.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP); esceldain.setFont(fuentet); // establecimiento de bordes esceldain.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); esceldain.setBottomBorderColor((short) 8); esceldain.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM); esceldain.setLeftBorderColor((short) 8); esceldain.setBorderRight(HSSFCellStyle.BORDER_MEDIUM); esceldain.setRightBorderColor((short) 8); esceldain.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); esceldain.setRightBorderColor((short) 8); // definimos el numero de filas que contedra nuestro decumento. encabezado = hoja.createRow((short) 5); Cencabezado = encabezado.createCell((short) 2); Cencabezado.setCellValue("Datos Verificados: Partida(" + partida + ")"); hoja.addMergedRegion(new Region(5, (short) 2, 6, (short) 4)); Cencabezado.setCellStyle(esceldaen); // ******************************************************************************************************************** HSSFRow fila1 = hoja.createRow((short) 2); HSSFCell ccontrato = fila1.createCell((short) 8); ccontrato.setCellValue("contrato:"); ccontrato.setCellStyle(esceldain); HSSFCell rcontrato = fila1.createCell((short) 9); rcontrato.setCellValue(" "); rcontrato.setCellStyle(esceldain); HSSFCell cgenrencia = fila1.createCell((short) 10); cgenrencia.setCellValue("Gerencia:"); cgenrencia.setCellStyle(esceldain); HSSFCell rgerencia = fila1.createCell((short) 11); rgerencia.setCellValue(" "); rgerencia.setCellStyle(esceldain); HSSFCell choja = fila1.createCell((short) 12); choja.setCellValue("Hoja:"); choja.setCellStyle(esceldain); HSSFCell rhoja = fila1.createCell((short) 13); rhoja.setCellValue(" "); rhoja.setCellStyle(esceldain); // ******************************************************************************************************************************************************* HSSFRow fila2 = hoja.createRow((short) 3); HSSFCell cnc = fila2.createCell((short) 8); cnc.setCellValue("N.C. :"); cnc.setCellStyle(esceldain); HSSFCell rcnc = fila2.createCell((short) 9); rcnc.setCellValue(" "); rcnc.setCellStyle(esceldain); HSSFCell ctipo = fila2.createCell((short) 10); ctipo.setCellValue("Tipo de obra:"); ctipo.setCellStyle(esceldain); HSSFCell rtipo = fila2.createCell((short) 11); rtipo.setCellValue(" "); rtipo.setCellStyle(esceldain); HSSFCell cunidad = fila2.createCell((short) 12); cunidad.setCellValue("Unidad:"); cunidad.setCellStyle(esceldain); HSSFCell runidad = fila2.createCell((short) 13); runidad.setCellValue(" "); runidad.setCellStyle(esceldain); // ******************************************************************************************************************************************************* HSSFRow fila3 = hoja.createRow((short) 4); HSSFCell clocalidad = fila3.createCell((short) 8); clocalidad.setCellValue("localidad:"); clocalidad.setCellStyle(esceldain); HSSFCell rlocalidad = fila3.createCell((short) 9); rlocalidad.setCellValue(" "); rlocalidad.setCellStyle(esceldain); hoja.addMergedRegion(new Region(4, (short) 9, 4, (short) 11)); rlocalidad.setCellStyle(esceldain); HSSFCell runo = fila3.createCell((short) 10); runo.setCellValue(" "); runo.setCellStyle(esceldain); HSSFCell rdos = fila3.createCell((short) 11); rdos.setCellValue(" "); rdos.setCellStyle(esceldain); HSSFCell cfecha = fila3.createCell((short) 12); cfecha.setCellValue("Fecha:"); cfecha.setCellStyle(esceldain); HSSFCell rfecha = fila3.createCell((short) 13); rfecha.setCellValue(" "); rfecha.setCellStyle(esceldain); // *************************************************************************************************************************************************************** HSSFRow fila4 = hoja.createRow((short) 5); HSSFCell ccontratista = fila4.createCell((short) 8); ccontratista.setCellValue("Contratista:"); ccontratista.setCellStyle(esceldain); HSSFCell rcontratista = fila4.createCell((short) 9); rcontratista.setCellValue(" "); rcontratista.setCellStyle(esceldain); hoja.addMergedRegion(new Region(5, (short) 9, 5, (short) 13)); rcontratista.setCellStyle(esceldain); HSSFCell runoc = fila4.createCell((short) 10); runoc.setCellValue(" "); runoc.setCellStyle(esceldain); HSSFCell rdosc = fila4.createCell((short) 11); rdosc.setCellValue(" "); rdosc.setCellStyle(esceldain); HSSFCell rtres = fila4.createCell((short) 12); rtres.setCellValue(" "); rtres.setCellStyle(esceldain); HSSFCell rcuatro = fila4.createCell((short) 13); rcuatro.setCellValue(" "); rcuatro.setCellStyle(esceldain); // *************************************************************************************************************************************************************** HSSFRow fila5 = hoja.createRow((short) 6); HSSFCell cperiodo = fila5.createCell((short) 8); cperiodo.setCellValue("Consultor:"); cperiodo.setCellStyle(esceldain); HSSFCell rperiodo = fila5.createCell((short) 9); rperiodo.setCellValue(" "); rperiodo.setCellStyle(esceldain); hoja.addMergedRegion(new Region(6, (short) 9, 6, (short) 13)); rperiodo.setCellStyle(esceldain); HSSFCell runop = fila5.createCell((short) 10); runop.setCellValue(" "); runop.setCellStyle(esceldain); HSSFCell rdosp = fila5.createCell((short) 11); rdosp.setCellValue(" "); rdosp.setCellStyle(esceldain); HSSFCell rtresp = fila5.createCell((short) 12); rtresp.setCellValue(" "); rtresp.setCellStyle(esceldain); HSSFCell rcuatrop = fila5.createCell((short) 13); rcuatrop.setCellValue(" "); rcuatrop.setCellStyle(esceldain); // crear un una columna HSSFRow row1 = hoja.createRow((short) 7); // create de las celdas HSSFCell cc = row1.createCell((short) 2); HSSFCell cd = row1.createCell((short) 3); HSSFCell cu = row1.createCell((short) 4); HSSFCell cx = row1.createCell((short) 5); HSSFCell cy = row1.createCell((short) 6); HSSFCell cz = row1.createCell((short) 7); HSSFCell ca = row1.createCell((short) 8); HSSFCell cl = row1.createCell((short) 9); HSSFCell cal = row1.createCell((short) 10); HSSFCell cca = row1.createCell((short) 11); HSSFCell cpz = row1.createCell((short) 12); HSSFCell cim = row1.createCell((short) 13); // writing data to the cells cc.setCellValue("Clave"); cc.setCellStyle(esceldat); cd.setCellValue("Descripción"); cd.setCellStyle(esceldat); cu.setCellValue("Unidad"); cu.setCellStyle(esceldat); cx.setCellValue("X"); cx.setCellStyle(esceldat); cy.setCellValue("Y"); cy.setCellStyle(esceldat); cz.setCellValue("Z"); cz.setCellStyle(esceldat); ca.setCellValue("Alto"); ca.setCellStyle(esceldat); cl.setCellValue("Largo"); cl.setCellStyle(esceldat); cal.setCellValue("ancho"); cal.setCellStyle(esceldat); cca.setCellValue("Cantidad"); cca.setCellStyle(esceldat); cpz.setCellValue("Piezas"); cpz.setCellStyle(esceldat); cim.setCellValue("Importe"); cim.setCellStyle(esceldat); // crear un una columna HSSFRow row = hoja.createRow((short) 8); // create de las celdas HSSFCell cc1 = row.createCell((short) 2); HSSFCell cd1 = row.createCell((short) 3); HSSFCell cu1 = row.createCell((short) 4); HSSFCell cx1 = row.createCell((short) 5); HSSFCell cy1 = row.createCell((short) 6); HSSFCell cz1 = row.createCell((short) 7); HSSFCell ca1 = row.createCell((short) 8); HSSFCell cl1 = row.createCell((short) 9); HSSFCell cal1 = row.createCell((short) 10); HSSFCell cca1 = row.createCell((short) 11); HSSFCell cpz1 = row.createCell((short) 12); HSSFCell cim1 = row.createCell((short) 13); cc1.setCellValue(objeto.getClave()); cc1.setCellStyle(esceldain); cd1.setCellValue(objeto.getDescripcion()); cd1.setCellStyle(esceldain); cu1.setCellValue(objeto.getUnidad()); cu1.setCellStyle(esceldain); cx1.setCellValue(objeto.getX()); cx1.setCellStyle(esceldain); cy1.setCellValue(objeto.getY()); cy1.setCellStyle(esceldain); cz1.setCellValue(objeto.getZ()); cz1.setCellStyle(esceldain); ca1.setCellValue(objeto.getAlto()); ca1.setCellStyle(esceldain); cl1.setCellValue(objeto.getLargo()); cl1.setCellStyle(esceldain); cal1.setCellValue(objeto.getAncho()); cal1.setCellStyle(esceldain); cca1.setCellValue(objeto.getCantidad()); cca1.setCellStyle(esceldain); cpz1.setCellValue(objeto.getPiezas()); cpz1.setCellStyle(esceldain); cim1.setCellValue(objeto.getImporte()); cim1.setCellStyle(esceldain); HSSFSheet sheet = libro.getSheetAt(0); HSSFPatriarch patriarch = sheet.createDrawingPatriarch(); // contendor que contiene las imagenes HSSFClientAnchor anchor; if (ruta != null) { File fis = new File(ruta); anchor = new HSSFClientAnchor(0, 0, 0, 0, (short) 3, 11, (short) 5, 24); anchor.setAnchorType(2); HSSFPicture imagen = patriarch.createPicture(anchor, Cargarimagen(fis, libro)); } if (ruta2 != null) { File fis2 = new File(ruta2); anchor = new HSSFClientAnchor(0, 0, 0, 0, (short) 9, 11, (short) 12, 24); anchor.setAnchorType(2); HSSFPicture imagen2 = patriarch.createPicture(anchor, Cargarimagen(fis2, libro)); } sheet.autoSizeColumn((short) 2); sheet.autoSizeColumn((short) 3); sheet.autoSizeColumn((short) 4); sheet.autoSizeColumn((short) 5); sheet.autoSizeColumn((short) 6); sheet.autoSizeColumn((short) 7); sheet.autoSizeColumn((short) 8); sheet.autoSizeColumn((short) 9); sheet.autoSizeColumn((short) 10); sheet.autoSizeColumn((short) 11); sheet.autoSizeColumn((short) 12); sheet.autoSizeColumn((short) 13); if (siinicial == true) { // ************************************************************************************ HSSFRow encabezado2 = hoja.createRow((short) 26); HSSFCell Cencabezado2 = encabezado2.createCell((short) 2); Cencabezado2.setCellValue("Datos: Partida(" + partida + ")"); hoja.addMergedRegion(new Region(26, (short) 2, 27, (short) 4)); Cencabezado2.setCellStyle(esceldaen); // crear un una columna HSSFRow row27 = hoja.createRow((short) 28); // create de las celdas HSSFCell cc2 = row27.createCell((short) 2); HSSFCell cd2 = row27.createCell((short) 3); HSSFCell cu2 = row27.createCell((short) 4); HSSFCell cx2 = row27.createCell((short) 5); HSSFCell cy2 = row27.createCell((short) 6); HSSFCell cz2 = row27.createCell((short) 7); HSSFCell ca2 = row27.createCell((short) 8); HSSFCell cl2 = row27.createCell((short) 9); HSSFCell cal2 = row27.createCell((short) 10); HSSFCell cca2 = row27.createCell((short) 11); HSSFCell cpz2 = row27.createCell((short) 12); HSSFCell cim2 = row27.createCell((short) 13); // writing data to the cells cc2.setCellValue("Clave"); cc2.setCellStyle(esceldat); cd2.setCellValue("Descripción"); cd2.setCellStyle(esceldat); cu2.setCellValue("Unidad"); cu2.setCellStyle(esceldat); cx2.setCellValue("X"); cx2.setCellStyle(esceldat); cy2.setCellValue("Y"); cy2.setCellStyle(esceldat); cz2.setCellValue("Z"); cz2.setCellStyle(esceldat); ca2.setCellValue("Alto"); ca2.setCellStyle(esceldat); cl2.setCellValue("Largo"); cl2.setCellStyle(esceldat); cal2.setCellValue("ancho"); cal2.setCellStyle(esceldat); cca2.setCellValue("Cantidad"); cca2.setCellStyle(esceldat); cpz2.setCellValue("Piezas"); cpz2.setCellStyle(esceldat); cim2.setCellValue("Importe"); cim2.setCellStyle(esceldat); // crear un una columna HSSFRow row28 = hoja.createRow((short) 29); // create de las celdas HSSFCell cc12 = row28.createCell((short) 2); HSSFCell cd12 = row28.createCell((short) 3); HSSFCell cu12 = row28.createCell((short) 4); HSSFCell cx12 = row28.createCell((short) 5); HSSFCell cy12 = row28.createCell((short) 6); HSSFCell cz12 = row28.createCell((short) 7); HSSFCell ca12 = row28.createCell((short) 8); HSSFCell cl12 = row28.createCell((short) 9); HSSFCell cal12 = row28.createCell((short) 10); HSSFCell cca12 = row28.createCell((short) 11); HSSFCell cpz12 = row28.createCell((short) 12); HSSFCell cim12 = row28.createCell((short) 13); cc12.setCellValue(objeto2.getClave()); cc12.setCellStyle(esceldain); cd12.setCellValue(objeto2.getDescripcion()); cd12.setCellStyle(esceldain); cu12.setCellValue(objeto2.getUnidad()); cu12.setCellStyle(esceldain); cx12.setCellValue(objeto2.getX()); cx12.setCellStyle(esceldain); cy12.setCellValue(objeto2.getY()); cy12.setCellStyle(esceldain); cz12.setCellValue(objeto2.getZ()); cz12.setCellStyle(esceldain); ca12.setCellValue(objeto2.getAlto()); ca12.setCellStyle(esceldain); cl12.setCellValue(objeto2.getLargo()); cl12.setCellStyle(esceldain); cal12.setCellValue(objeto2.getAncho()); cal12.setCellStyle(esceldain); cca12.setCellValue(objeto2.getCantidad()); cca12.setCellStyle(esceldain); cpz12.setCellValue(objeto2.getPiezas()); cpz12.setCellStyle(esceldain); cim12.setCellValue(objeto2.getImporte()); cim12.setCellStyle(esceldain); float uno = 0, dos = 0, resultado = 0; uno = Float.parseFloat(objeto.getImporte()); dos = Float.parseFloat(objeto2.getImporte()); if (uno > dos) { HSSFRow row31 = hoja.createRow((short) 33); // create de las celdas HSSFCell cc131 = row31.createCell((short) 3); resultado = uno - dos; cc131.setCellValue("Execedente: " + String.valueOf(resultado)); cc131.setCellStyle(esceldain); } else { if (dos > uno) { HSSFRow row31 = hoja.createRow((short) 33); // create de las celdas HSSFCell cc131 = row31.createCell((short) 3); resultado = dos - uno; cc131.setCellValue("Restante: " + String.valueOf(resultado)); cc131.setCellStyle(esceldain); } } // **************************************************************************************** } else { HSSFRow encabezado2 = hoja.createRow((short) 26); HSSFCell Cencabezado2 = encabezado2.createCell((short) 2); Cencabezado2.setCellValue( "El aspecto:" + objeto.getDescripcion() + " no esta comtemplado en la estimacion inicial"); hoja.addMergedRegion(new Region(26, (short) 2, 27, (short) 4)); Cencabezado2.setCellStyle(esceldaen); HSSFRow row28 = hoja.createRow((short) 29); // create de las celdas HSSFCell cc12 = row28.createCell((short) 3); cc12.setCellValue("Execedente: " + objeto.getImporte()); cc12.setCellStyle(esceldain); } try { FileOutputStream elFichero = new FileOutputStream(FILE); libro.write(elFichero); elFichero.close(); } catch (Exception e) { e.printStackTrace(); } }
/** * Discription:[写一个单元格] * * @param cell 单元格 * @param value 写入的值 * @param valueType 写入的值的类型 * @param dateFormat 日期格式,默认yyyy-MM-dd * @author:[代超] * @update:[日期YYYY-MM-DD] [更改人姓名][变更描述] */ public void writeCell(Cell cell, Object value, String valueType, String dateFormat) { if (cell == null || value == null) { return; } if (dateFormat == null || "".equals(dateFormat.trim())) { dateFormat = "yyyy-MM-dd"; } String cellValue = ""; if ("String".equals(valueType)) { cellValue = value.toString(); } else if ("int".equals(valueType)) { cellValue = String.valueOf(value); } else if ("float".equals(valueType)) { cellValue = String.valueOf(value); } else if ("double".equals(valueType)) { cellValue = String.valueOf(value); } else if ("Number".equals(valueType)) { cellValue = String.valueOf(value); } else if ("BigDecimal".equals(valueType)) { cellValue = String.valueOf(value); } else if ("byte[]".equals(valueType)) { // 有图片时,设置行高为60px; cell.getRow().setHeightInPoints(60); // 设置图片所在列宽度为80px,注意这里单位的一个换算 cell.getSheet().setColumnWidth(cell.getColumnIndex(), (short) (35.7 * 80)); // sheet.autoSizeColumn(i); byte[] bsValue = (byte[]) value; HSSFClientAnchor anchor = new HSSFClientAnchor( 0, 0, 1023, 255, (short) 6, cell.getRowIndex(), (short) 6, cell.getRowIndex()); anchor.setAnchorType(2); // 声明一个画图的顶级管理器 cell.getSheet() .createDrawingPatriarch() .createPicture( anchor, cell.getSheet().getWorkbook().addPicture(bsValue, HSSFWorkbook.PICTURE_TYPE_JPEG)); return; } else if ("Date".equals(valueType)) { SimpleDateFormat sdf = new SimpleDateFormat(dateFormat); cellValue = sdf.format(value); } else if ("boolean".equals(valueType)) { boolean bool = (Boolean) value; if (bool) { cellValue = "是"; } else { cellValue = "否"; } } else if ("Boolean".equals(valueType)) { boolean bool = (Boolean) value; if (bool) { cellValue = "是"; } else { cellValue = "否"; } } else { cellValue = String.valueOf(value); } cell.setCellValue(cellValue); return; }
/** * create a rectangle, save the workbook, read back and verify that all shape properties are there */ public void testReadWriteRectangle() throws IOException { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet(); HSSFPatriarch drawing = sheet.createDrawingPatriarch(); HSSFClientAnchor anchor = new HSSFClientAnchor(10, 10, 50, 50, (short) 2, 2, (short) 4, 4); anchor.setAnchorType(2); assertEquals(anchor.getAnchorType(), 2); HSSFSimpleShape rectangle = drawing.createSimpleShape(anchor); rectangle.setShapeType(HSSFSimpleShape.OBJECT_TYPE_RECTANGLE); rectangle.setLineWidth(10000); rectangle.setFillColor(777); assertEquals(rectangle.getFillColor(), 777); assertEquals(10000, rectangle.getLineWidth()); rectangle.setLineStyle(10); assertEquals(10, rectangle.getLineStyle()); assertEquals(rectangle.getWrapText(), HSSFSimpleShape.WRAP_SQUARE); rectangle.setLineStyleColor(1111); rectangle.setNoFill(true); rectangle.setWrapText(HSSFSimpleShape.WRAP_NONE); rectangle.setString(new HSSFRichTextString("teeeest")); assertEquals(rectangle.getLineStyleColor(), 1111); assertEquals( ((EscherSimpleProperty) ((EscherOptRecord) HSSFTestHelper.getEscherContainer(rectangle) .getChildById(EscherOptRecord.RECORD_ID)) .lookup(EscherProperties.TEXT__TEXTID)) .getPropertyValue(), "teeeest".hashCode()); assertEquals(rectangle.isNoFill(), true); assertEquals(rectangle.getWrapText(), HSSFSimpleShape.WRAP_NONE); assertEquals(rectangle.getString().getString(), "teeeest"); wb = HSSFTestDataSamples.writeOutAndReadBack(wb); sheet = wb.getSheetAt(0); drawing = sheet.getDrawingPatriarch(); assertEquals(1, drawing.getChildren().size()); HSSFSimpleShape rectangle2 = (HSSFSimpleShape) drawing.getChildren().get(0); assertEquals(HSSFSimpleShape.OBJECT_TYPE_RECTANGLE, rectangle2.getShapeType()); assertEquals(10000, rectangle2.getLineWidth()); assertEquals(10, rectangle2.getLineStyle()); assertEquals(anchor, rectangle2.getAnchor()); assertEquals(rectangle2.getLineStyleColor(), 1111); assertEquals(rectangle2.getFillColor(), 777); assertEquals(rectangle2.isNoFill(), true); assertEquals(rectangle2.getString().getString(), "teeeest"); assertEquals(rectangle.getWrapText(), HSSFSimpleShape.WRAP_NONE); rectangle2.setFillColor(3333); rectangle2.setLineStyle(9); rectangle2.setLineStyleColor(4444); rectangle2.setNoFill(false); rectangle2.setLineWidth(77); rectangle2.getAnchor().setDx1(2); rectangle2.getAnchor().setDx2(3); rectangle2.getAnchor().setDy1(4); rectangle2.getAnchor().setDy2(5); rectangle.setWrapText(HSSFSimpleShape.WRAP_BY_POINTS); rectangle2.setString(new HSSFRichTextString("test22")); wb = HSSFTestDataSamples.writeOutAndReadBack(wb); sheet = wb.getSheetAt(0); drawing = sheet.getDrawingPatriarch(); assertEquals(1, drawing.getChildren().size()); rectangle2 = (HSSFSimpleShape) drawing.getChildren().get(0); assertEquals(HSSFSimpleShape.OBJECT_TYPE_RECTANGLE, rectangle2.getShapeType()); assertEquals(rectangle.getWrapText(), HSSFSimpleShape.WRAP_BY_POINTS); assertEquals(77, rectangle2.getLineWidth()); assertEquals(9, rectangle2.getLineStyle()); assertEquals(rectangle2.getLineStyleColor(), 4444); assertEquals(rectangle2.getFillColor(), 3333); assertEquals(rectangle2.getAnchor().getDx1(), 2); assertEquals(rectangle2.getAnchor().getDx2(), 3); assertEquals(rectangle2.getAnchor().getDy1(), 4); assertEquals(rectangle2.getAnchor().getDy2(), 5); assertEquals(rectangle2.isNoFill(), false); assertEquals(rectangle2.getString().getString(), "test22"); HSSFSimpleShape rect3 = drawing.createSimpleShape(new HSSFClientAnchor()); rect3.setShapeType(HSSFSimpleShape.OBJECT_TYPE_RECTANGLE); wb = HSSFTestDataSamples.writeOutAndReadBack(wb); drawing = wb.getSheetAt(0).getDrawingPatriarch(); assertEquals(drawing.getChildren().size(), 2); }