private void createSummerySheet() { sheet0 = workbook.createSheet("Summary"); PrintSetup printSetup = sheet0.getPrintSetup(); printSetup.setLandscape(true); sheet0.setFitToPage(true); sheet0.setHorizontallyCenter(true); // title row Row titleRow = sheet0.createRow(0); titleRow.setHeightInPoints(45); Cell titleCell = titleRow.createCell(0); titleCell.setCellValue("File Health Report"); titleCell.setCellStyle(styles.get("title")); sheet0.addMergedRegion(CellRangeAddress.valueOf("$A$1:$L$1")); for (int i = 0; i < titles.length; i++) { Row _row = sheet0.createRow(i + 1); Cell headerCell = _row.createCell(0); headerCell.setCellValue(titles[i]); headerCell.setCellStyle(styles.get("header")); _row.setHeightInPoints(20); } // finally set column widths, the width is measured in units of 1/256th // of a character width sheet0.setColumnWidth(0, 50 * 256); // 30 characters wide }
/** * @param * @return void */ private void generateColumn( HSSFSheet sheet, TableColumn tc, int maxlevel, int rownum, int colnum, HSSFCellStyle headerstyle) { HSSFRow row = sheet.getRow(rownum); if (row == null) row = sheet.createRow(rownum); HSSFCell cell = row.createCell(colnum); cell.setCellValue(tc.getDisplay()); if (headerstyle != null) cell.setCellStyle(headerstyle); if (tc.isComplex()) { CellRangeAddress address = new CellRangeAddress(rownum, rownum, colnum, colnum + tc.getLength() - 1); sheet.addMergedRegion(address); fillMergedRegion(sheet, address, headerstyle); int cn = colnum; for (int i = 0; i < tc.getChildren().size(); i++) { if (i != 0) { cn = cn + tc.getChildren().get(i - 1).getLength(); } generateColumn(sheet, tc.getChildren().get(i), maxlevel, rownum + 1, cn, headerstyle); } } else { CellRangeAddress address = new CellRangeAddress(rownum, rownum + maxlevel - tc.level, colnum, colnum); sheet.addMergedRegion(address); fillMergedRegion(sheet, address, headerstyle); } sheet.autoSizeColumn(colnum, true); }
/** * Builds the report title and the date header * * @param worksheet * @param startRowIndex starting row offset * @param startColIndex starting column offset */ public static void buildTitle(HSSFSheet worksheet, int startRowIndex, int startColIndex) { // Create font style for the report title Font fontTitle = worksheet.getWorkbook().createFont(); fontTitle.setBoldweight(Font.BOLDWEIGHT_BOLD); fontTitle.setFontHeight((short) 280); // Create cell style for the report title HSSFCellStyle cellStyleTitle = worksheet.getWorkbook().createCellStyle(); cellStyleTitle.setAlignment(CellStyle.ALIGN_CENTER); cellStyleTitle.setWrapText(true); cellStyleTitle.setFont(fontTitle); // Create report title HSSFRow rowTitle = worksheet.createRow((short) startRowIndex); rowTitle.setHeight((short) 500); HSSFCell cellTitle = rowTitle.createCell(startColIndex); cellTitle.setCellValue("Compensation Report"); cellTitle.setCellStyle(cellStyleTitle); // Create merged region for the report title worksheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 14)); // Create date header HSSFRow dateTitle = worksheet.createRow((short) startRowIndex + 1); HSSFCell cellDate = dateTitle.createCell(startColIndex); cellDate.setCellValue("This report was generated at " + new Date()); }
private static void buildTitle(HSSFSheet worksheet, int startRowIndex, int startColIndex) { // 报表标题字体 Font fontTitle = worksheet.getWorkbook().createFont(); fontTitle.setBoldweight((short) Font.BOLDWEIGHT_BOLD); fontTitle.setFontHeight((short) 280); // 标题单元格格式 HSSFCellStyle cellStyleTitle = worksheet.getWorkbook().createCellStyle(); cellStyleTitle.setAlignment(CellStyle.ALIGN_CENTER); cellStyleTitle.setWrapText(true); cellStyleTitle.setFont(fontTitle); HSSFRow rowTitle = worksheet.createRow((short) startRowIndex); rowTitle.setHeight((short) 500); HSSFCell cellTitle = rowTitle.createCell(startColIndex); cellTitle.setCellValue("学生列表"); cellTitle.setCellStyle(cellStyleTitle); worksheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 2)); // 标题合并列 Date date = new Date(); SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd"); HSSFRow dateTitle = worksheet.createRow((short) startRowIndex + 1); HSSFCell cellDate = dateTitle.createCell(startColIndex); cellDate.setCellValue("这个报表创建于: " + dateFormat.format(date)); }
private void putGroupCell(HSSFSheet sheet, int rowIndex, String name) { cell = sheet.getRow(rowIndex).createCell(1); cell.setCellValue(name); CellRangeAddress region = new CellRangeAddress(rowIndex, rowIndex, 1, data.getNumbOfCols()); sheet.addMergedRegion(utils.getBorderedRegion(region, sheet, wb)); cell.setCellStyle(utils.getGroupStyle(wb)); }
public void end() { // 处理组合单元格 if (!mergeCells.isEmpty()) { Region[] regs = new Region[mergeCells.size()]; mergeCells.values().toArray(regs); int len = regs.length; for (int i = 0; i < len; i++) m_sheet.addMergedRegion(regs[i]); } try { flushToFile(); } catch (Exception e) { m_spool.setErrorMsg(e.getMessage()); } finally { if (out != null) try { out.close(); } catch (IOException e) { Logger.error(e.getMessage(), e); } } StringBuffer msg = new StringBuffer(); // 最后给出完整的提示 if (sheetCounter > 1) { String filename = m_filepath.substring(m_filepath.lastIndexOf(System.getProperty("file.separator")) + 1); msg.append( nc.ui.ml.NCLangRes.getInstance() .getStrByID( "10100108", "UPP10100108-000856") /*@res "\n您要导出的内容太多,为减小内存的使用量,它们被存储到目录 \n"*/); msg.append( m_filepath.substring(0, m_filepath.lastIndexOf(System.getProperty("file.separator")))); msg.append( nc.ui.ml.NCLangRes.getInstance() .getStrByID("10100108", "UPP10100108-000857") /*@res " 下的多个Excel文件中,分别是:\n"*/); msg.append(filename).append(","); for (int i = 2; i <= sheetCounter; i++) { if (i != sheetCounter) { msg.append(filename.substring(0, filename.indexOf('.')) + "-" + i + ".xls").append(","); } else { msg.append(filename.substring(0, filename.indexOf('.')) + "-" + i + ".xls"); } } } else { msg.append( nc.ui.ml.NCLangRes.getInstance() .getStrByID("10100108", "UPP10100108-000858") /*@res "导出内容被存放在文件 \n"*/ + m_filepath + nc.ui.ml.NCLangRes.getInstance() .getStrByID("10100108", "UPP10100108-000859") /*@res " 中."*/); } m_spool.setMsg(msg.toString()); mergeCells = new Hashtable(); // 存放所有组合单元格 styles = new Hashtable(); }
public void run() { int pagesize = PAGESIZE; try { while (!m_spool.isHasError() && !m_spool.isEmpty()) { if (linecounter >= pagesize) { // 处理组合单元格 if (!mergeCells.isEmpty()) { Region[] regs = new Region[mergeCells.size()]; mergeCells.values().toArray(regs); int len = regs.length; for (int i = 0; i < len; i++) m_sheet.addMergedRegion(regs[i]); } try { flushToFile(); } catch (Exception e) { Logger.error(e.getMessage(), e); m_spool.setErrorMsg(e.getMessage()); } finally { if (out != null) try { out.close(); } catch (IOException e) { Logger.error(e.getMessage(), e); } } m_workbook = new HSSFWorkbook(); // 创建工作薄 m_sheet = m_workbook.createSheet(); // 创建表单 mergeCells = new Hashtable(); // 存放所有组合单元格 styles = new Hashtable(); linecounter = 0; sheetCounter++; } PrintCellData[] data = null; data = (PrintCellData[]) m_spool.retrieve(); linecounter = linecounter + data.length; // 其实为单元格数目,非行数 fileUtil.exportSheet2(m_templateData, data, m_sheet, m_workbook, styles, mergeCells); /* 每往Excel写入一行,进度条+1,已导出的行数+1 */ m_spool.setExportedLineCount(m_spool.getExportedLineCount() + 1); stepProgressBar(1); setProgressBarHintMsg(); } } catch (Exception e) { Logger.error(e.getMessage(), e); m_spool.setErrorMsg(e.getMessage()); } catch (OutOfMemoryError e) { Logger.error(e.getMessage(), e); m_spool.setErrorMsg( nc.ui.ml.NCLangRes.getInstance() .getStrByID( "10100108", "UPP10100108-000860") /*@res "内存不足,请修改Java Plugin参数,将内存调整大一些"*/); } }
private static void populateWorkbook(NodeList sheetList, Map cellStyles, HSSFWorkbook workbook) throws ExcelTransformerException { if (LOG.isLoggable(Level.FINE)) { LOG.entering( SimpleExcelRenderer.class.getName(), "populateWorkbook", String.valueOf(sheetList.getLength())); } for (int k = 0; k < sheetList.getLength(); k++) { Element sheet = (Element) sheetList.item(k); NodeList rowList = sheet.getElementsByTagName("row"); if (rowList.getLength() > 0) { HSSFSheet hSheet = workbook.createSheet(sheet.getAttribute("name")); for (int i = 0; i < rowList.getLength(); i++) { Element row = (Element) rowList.item(i); HSSFRow hRow = hSheet.createRow(i); short cellCounter = 0; NodeList cells = row.getChildNodes(); for (short j = 0; j < cells.getLength(); j++) { Node cell = cells.item(j); if (cell.getNodeType() == Node.ELEMENT_NODE && cell.getNodeName().equals("cell")) { Element cellE = (Element) cell; String value = XmlUtils.getElementText(cellE); String style = cellE.getAttribute("style"); String colSpan = cellE.getAttribute("colspan"); int colSpanI = 0; if (Utils.hasContent(colSpan)) { colSpanI = Integer.parseInt(colSpan); hSheet.addMergedRegion( new Region(i, cellCounter, i, (short) (cellCounter + colSpanI - 1))); } HSSFCell hCell = hRow.createCell(cellCounter); if (Utils.hasContent(style)) hCell.setCellStyle((HSSFCellStyle) cellStyles.get(style)); hCell.setCellValue(value); cellCounter++; } } } } } if (LOG.isLoggable(Level.FINE)) { LOG.exiting(SimpleExcelRenderer.class.getName(), "populateWorkbook"); } }
private void putBasicInfo( HSSFSheet sheet, int rowIndex, String key, Object value, int numbOfCols) { row = sheet.createRow(rowIndex); row.setHeightInPoints(defHeight); // key cell = utils.putHeader(row, 1, key); cell.getCellStyle().setAlignment(CellStyle.ALIGN_RIGHT); // value utils.putBorderedBasicCell(sheet, rowIndex, 2, value); region = new CellRangeAddress(rowIndex, rowIndex, 2, numbOfCols); sheet.addMergedRegion(utils.getBorderedRegion(region, sheet, wb)); }
public void merge( int startRowIndex, int endRowIndex, int startColumnIndex, int endColumnIndex, boolean center) { if (startRowIndex <= endRowIndex && startColumnIndex <= endColumnIndex) { sheet.addMergedRegion( new CellRangeAddress(startRowIndex, endRowIndex, startColumnIndex, endColumnIndex)); if (center) { CellStyle cellStyle = createCellStyle(); HSSFCell cell = getCell(startRowIndex, startColumnIndex); cellStyle.cloneStyleFrom(cell.getCellStyle()); cellStyle.setAlignment(CellStyle.ALIGN_CENTER); cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); cell.setCellStyle(cellStyle); } } }
/** * @param * @return void */ private void stopGrouping( HSSFSheet sheet, HashMap<Integer, String> word, HashMap<Integer, Integer> counter, int i, int size, int rownum, HSSFCellStyle style) { String w = word.get(i); if (w != null) { int len = counter.get(i); CellRangeAddress address = new CellRangeAddress(rownum - len, rownum - 1, i, i); sheet.addMergedRegion(address); fillMergedRegion(sheet, address, style); word.remove(i); counter.remove(i); } if (i + 1 < size) { stopGrouping(sheet, word, counter, i + 1, size, rownum, style); } }
/** * 添加临时表格内容 * * @param mainSheet -- 原表单内容 * @param subSheet -- 临时表单内容 * @return */ public static HSSFSheet appendSheet(HSSFSheet mainSheet, HSSFSheet subSheet) { if (mainSheet == null || subSheet == null) return null; // 判断报表是否允许输出 if (!isAllowOut(mainSheet)) return mainSheet; // 原报表的最后一行 int endRowNum = mainSheet.getPhysicalNumberOfRows(); HSSFRow sourow = null, descrow = null; HSSFCell sourcell = null, descell = null, orgcell = null; int i = 0, offsetcnt = 0; // 复制表格中的图片 copySheetImage(mainSheet.getWorkbook(), subSheet.getWorkbook()); // 设置以合并的单元格 CellRangeAddress range = null; int mergedNum = subSheet.getNumMergedRegions(); for (i = 0; i < mergedNum; i++) { range = subSheet.getMergedRegion(i); range.setFirstRow(range.getFirstRow() + endRowNum); range.setLastRow(range.getLastRow() + endRowNum); mainSheet.addMergedRegion(range); } range = null; // int k = 0; // 设置相关参数 mainSheet.setAlternativeExpression(subSheet.getAlternateExpression()); mainSheet.setAlternativeFormula(subSheet.getAlternateFormula()); mainSheet.setAutobreaks(subSheet.getAutobreaks()); mainSheet.setDialog(subSheet.getDialog()); mainSheet.setDisplayGuts(subSheet.getDisplayGuts()); mainSheet.setFitToPage(subSheet.getFitToPage()); for (java.util.Iterator<Row> iterow = subSheet.rowIterator(); iterow.hasNext(); ) { sourow = (HSSFRow) iterow.next(); offsetcnt = sourow.getRowNum() + endRowNum; descrow = mainSheet.createRow(offsetcnt); descrow.setHeight(sourow.getHeight()); descrow.setHeightInPoints(sourow.getHeightInPoints()); java.util.Iterator<Cell> iter = sourow.cellIterator(); while (iter.hasNext()) { sourcell = (HSSFCell) iter.next(); int column = sourcell.getColumnIndex(); descell = descrow.createCell(column); // 取模板中的单元格,与来源表单位置相同 int row = sourcell.getRowIndex(); orgcell = mainSheet.getRow(row).getCell(column); if (orgcell != null) { // 取模板中的类型赋值 descell.setCellType(orgcell.getCellType()); // 取模板中的样式赋值 descell.setCellStyle(orgcell.getCellStyle()); } else { _log.showWarn("module xls [{0}, {1}] cell is null!", row, column); } if (sourcell.getCellType() == HSSFCell.CELL_TYPE_STRING) descell.setCellValue(sourcell.getStringCellValue()); else if (sourcell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) descell.setCellValue(sourcell.getNumericCellValue()); else if (sourcell.getCellType() == HSSFCell.CELL_TYPE_BLANK) ; } sourow = null; sourcell = null; descrow = null; orgcell = null; } return mainSheet; }
@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(); } }
// 导出报表到 excel public void toExcel() throws Exception { String date = Common.getSystemDate(); // 定义excel以及sheet HSSFWorkbook workBook = new HSSFWorkbook(); HSSFSheet sheet = workBook.createSheet(date + "学工部设备表"); // header sheet.setColumnWidth((short) 0, (short) (8 * 256)); sheet.setColumnWidth((short) 1, (short) (20 * 256)); sheet.setColumnWidth((short) 2, (short) (20 * 256)); sheet.setColumnWidth((short) 3, (short) (16 * 256)); sheet.setColumnWidth((short) 4, (short) (20 * 256)); sheet.setColumnWidth((short) 5, (short) (20 * 256)); sheet.setColumnWidth((short) 6, (short) (20 * 256)); sheet.setColumnWidth((short) 7, (short) (16 * 256)); sheet.setColumnWidth((short) 8, (short) (20 * 256)); sheet.setColumnWidth((short) 9, (short) (20 * 256)); sheet.setColumnWidth((short) 10, (short) (20 * 256)); sheet.setColumnWidth((short) 11, (short) (20 * 256)); HSSFRow titleRow = sheet.createRow((short) 0); HSSFCell titleCell = titleRow.createCell((short) 0); titleCell.setEncoding(HSSFCell.ENCODING_UTF_16); titleCell.setCellValue("重庆邮电大学学工部设备表"); // 设置表头样式 HSSFCellStyle titleStyle = workBook.createCellStyle(); titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 表头字体 HSSFFont titleFont = workBook.createFont(); titleFont.setFontHeightInPoints((short) 16); titleFont.setFontName("黑体"); titleStyle.setFont(titleFont); // 表头边框 titleStyle.setBorderBottom(CellStyle.BORDER_THIN); // titleStyle.setBorderLeft(CellStyle.BORDER_THIN); titleStyle.setBorderRight(CellStyle.BORDER_THIN); // titleStyle.setBorderTop(CellStyle.BORDER_THIN); titleCell.setCellStyle(titleStyle); titleCell = titleRow.createCell((short) 9); titleCell.setCellStyle(titleStyle); // 设置单元格边框 titleRow = sheet.createRow((short) 1); titleCell = titleRow.createCell((short) 0); titleCell.setCellStyle(titleStyle); titleCell = titleRow.createCell((short) 1); titleCell.setCellStyle(titleStyle); titleCell = titleRow.createCell((short) 2); titleCell.setCellStyle(titleStyle); titleCell = titleRow.createCell((short) 3); titleCell.setCellStyle(titleStyle); titleCell = titleRow.createCell((short) 4); titleCell.setCellStyle(titleStyle); titleCell = titleRow.createCell((short) 5); titleCell.setCellStyle(titleStyle); titleCell = titleRow.createCell((short) 6); titleCell.setCellStyle(titleStyle); titleCell = titleRow.createCell((short) 7); titleCell.setCellStyle(titleStyle); titleCell = titleRow.createCell((short) 8); titleCell.setCellStyle(titleStyle); titleCell = titleRow.createCell((short) 9); titleCell.setCellStyle(titleStyle); titleCell = titleRow.createCell((short) 10); titleCell.setCellStyle(titleStyle); titleCell = titleRow.createCell((short) 11); titleCell.setCellStyle(titleStyle); // 合并单元格 0行0列 1行9列 sheet.addMergedRegion(new Region(0, (short) 0, 1, (short) 11)); // 内容样式 HSSFCellStyle contentStyle = workBook.createCellStyle(); contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 内容字体 HSSFFont contentFont = workBook.createFont(); contentFont.setFontHeightInPoints((short) 11); contentFont.setFontName("宋体"); contentStyle.setFont(contentFont); contentStyle.setBorderBottom(CellStyle.BORDER_THIN); contentStyle.setBorderRight(CellStyle.BORDER_THIN); // contentStyle.setBorderLeft(CellStyle.BORDER_THIN); // contentStyle.setBorderTop(CellStyle.BORDER_THIN); // 第二行表头 titleRow = sheet.createRow((short) 2); titleCell = titleRow.createCell((short) 0); titleCell.setEncoding(HSSFCell.ENCODING_UTF_16); titleCell.setCellValue("编号"); titleCell.setCellStyle(contentStyle); titleCell = titleRow.createCell((short) 1); titleCell.setEncoding(HSSFCell.ENCODING_UTF_16); titleCell.setCellValue("名称"); titleCell.setCellStyle(contentStyle); titleCell = titleRow.createCell((short) 2); titleCell.setEncoding(HSSFCell.ENCODING_UTF_16); titleCell.setCellValue("设备价值(元)"); titleCell.setCellStyle(contentStyle); titleCell = titleRow.createCell((short) 3); titleCell.setEncoding(HSSFCell.ENCODING_UTF_16); titleCell.setCellValue("申请部门"); titleCell.setCellStyle(contentStyle); titleCell = titleRow.createCell((short) 4); titleCell.setEncoding(HSSFCell.ENCODING_UTF_16); titleCell.setCellValue("申请人"); titleCell.setCellStyle(contentStyle); titleCell = titleRow.createCell((short) 5); titleCell.setEncoding(HSSFCell.ENCODING_UTF_16); titleCell.setCellValue("购买时间"); titleCell.setCellStyle(contentStyle); titleCell = titleRow.createCell((short) 6); titleCell.setEncoding(HSSFCell.ENCODING_UTF_16); titleCell.setCellValue("购买地点"); titleCell.setCellStyle(contentStyle); titleCell = titleRow.createCell((short) 7); titleCell.setEncoding(HSSFCell.ENCODING_UTF_16); titleCell.setCellValue("设备状态"); titleCell.setCellStyle(contentStyle); titleCell = titleRow.createCell((short) 8); titleCell.setEncoding(HSSFCell.ENCODING_UTF_16); titleCell.setCellValue("生产厂商"); titleCell.setCellStyle(contentStyle); titleCell = titleRow.createCell((short) 9); titleCell.setEncoding(HSSFCell.ENCODING_UTF_16); titleCell.setCellValue("使用部门"); titleCell.setCellStyle(contentStyle); titleCell = titleRow.createCell((short) 10); titleCell.setEncoding(HSSFCell.ENCODING_UTF_16); titleCell.setCellValue("使用人"); titleCell.setCellStyle(contentStyle); List<Equipment> equipmentListtemp = new ArrayList<Equipment>(); equipmentListtemp = this.equipmentService.getEquipmentBycondtion("where 1=1"); int i = 0; Double totalMoney = 0d; String totalString = ""; for (i = 0; i < equipmentListtemp.size(); ++i) { titleRow = sheet.createRow((short) (i + 3)); // 第一列 编号 titleCell = titleRow.createCell((short) 0); titleCell.setEncoding(HSSFCell.ENCODING_UTF_16); if (equipmentListtemp.get(i).getEquipmentcode() != null) titleCell.setCellValue(equipmentListtemp.get(i).getEquipmentcode()); else titleCell.setCellValue(i + 1); titleCell.setCellStyle(contentStyle); // 第二列 名称 titleCell = titleRow.createCell((short) 1); titleCell.setEncoding(HSSFCell.ENCODING_UTF_16); if (equipmentListtemp.get(i).getName() != null) titleCell.setCellValue(equipmentListtemp.get(i).getName()); else titleCell.setCellValue(""); titleCell.setCellStyle(contentStyle); // 第三列 设备价值 titleCell = titleRow.createCell((short) 2); titleCell.setEncoding(HSSFCell.ENCODING_UTF_16); if (equipmentListtemp.get(i).getValueof().toString() != null) { titleCell.setCellValue(equipmentListtemp.get(i).getValueof().toString()); totalMoney += equipmentListtemp.get(i).getValueof(); } else { titleCell.setCellValue("0"); totalMoney += 0; } titleCell.setCellStyle(contentStyle); // 第四列 申请部门 titleCell = titleRow.createCell((short) 3); titleCell.setEncoding(HSSFCell.ENCODING_UTF_16); String officeIdTemp = equipmentListtemp.get(i).getDepartment(); if (officeIdTemp != null) { Studentoffice studentofficetemp = this.studentofficeService.findById(officeIdTemp); if (studentofficetemp != null) { titleCell.setCellValue(studentofficetemp.getName()); } } else { titleCell.setCellValue("请检查办公室"); } titleCell.setCellStyle(contentStyle); // 第五列 申请人 titleCell = titleRow.createCell((short) 4); titleCell.setEncoding(HSSFCell.ENCODING_UTF_16); if (equipmentListtemp.get(i).getBuyers() != null) titleCell.setCellValue(equipmentListtemp.get(i).getBuyers()); else titleCell.setCellValue(""); titleCell.setCellStyle(contentStyle); // 第六列 购买时间 titleCell = titleRow.createCell((short) 5); titleCell.setEncoding(HSSFCell.ENCODING_UTF_16); if (equipmentListtemp.get(i).getPurchasetime() != null) titleCell.setCellValue(equipmentListtemp.get(i).getPurchasetime().trim()); else titleCell.setCellValue(""); titleCell.setCellStyle(contentStyle); // 第七列 购买地点 titleCell = titleRow.createCell((short) 6); titleCell.setEncoding(HSSFCell.ENCODING_UTF_16); if (equipmentListtemp.get(i).getAddress() != null) titleCell.setCellValue(equipmentListtemp.get(i).getAddress()); else titleCell.setCellValue("请检查岗位名称"); titleCell.setCellStyle(contentStyle); // 第八列 设备状态 titleCell = titleRow.createCell((short) 7); titleCell.setEncoding(HSSFCell.ENCODING_UTF_16); if (equipmentListtemp.get(i).getStates() == 1) { titleCell.setCellValue("正常"); } else titleCell.setCellValue("报废"); titleCell.setCellStyle(contentStyle); // 第九列 生产厂商 titleCell = titleRow.createCell((short) 8); titleCell.setEncoding(HSSFCell.ENCODING_UTF_16); if (equipmentListtemp.get(i).getFactory() != null) { titleCell.setCellValue(equipmentListtemp.get(i).getFactory()); } else titleCell.setCellValue(""); titleCell.setCellStyle(contentStyle); // 第十列 使用部门 titleCell = titleRow.createCell((short) 9); titleCell.setEncoding(HSSFCell.ENCODING_UTF_16); Studentoffice Studentofficenow = new Studentoffice(); List<Equipmentdepartment> equipmentdepartmentlisttemp = this.equipmentDepartmentService.findByEquipmentId(equipmentListtemp.get(i).getId()); Equipmentdepartment Equipmentdepartmentnow = new Equipmentdepartment(); for (int j = 0; j < equipmentdepartmentlisttemp.size(); j++) { if (equipmentdepartmentlisttemp.get(j).getFlowouttime() == null || equipmentdepartmentlisttemp.get(j).getFlowouttime().length() == 0) { Equipmentdepartmentnow = equipmentdepartmentlisttemp.get(j); Studentofficenow = this.studentofficeService.findById( equipmentdepartmentlisttemp.get(j).getDepartment()); break; } } if (Studentofficenow.getName() != null) { titleCell.setCellValue(Studentofficenow.getName()); } else titleCell.setCellValue(""); titleCell.setCellStyle(contentStyle); // 第十一列 使用人 titleCell = titleRow.createCell((short) 10); titleCell.setEncoding(HSSFCell.ENCODING_UTF_16); Officeteacher officeteachernow = new Officeteacher(); if (Equipmentdepartmentnow != null) { if (Equipmentdepartmentnow != null) { List<Equipmentuser> equipmentUserNowList = this.equipmentuserService.findByEquipmentdepId(Equipmentdepartmentnow.getId()); for (int j = 0; j < equipmentUserNowList.size(); j++) { if (equipmentUserNowList.get(j).getFlowouttime2() == null || equipmentUserNowList.get(j).getFlowouttime2().length() == 0) { officeteachernow = this.officeteacherService.findById( equipmentUserNowList.get(j).getEquipmentuser()); break; } } } } if (officeteachernow != null) { if (officeteachernow.getTeacher() != null) { if (officeteachernow.getTeacher().getName() != null) { titleCell.setCellValue(officeteachernow.getTeacher().getName()); } } } else titleCell.setCellValue(""); titleCell.setCellStyle(contentStyle); } titleRow = sheet.createRow((short) (i + 3)); titleCell = titleRow.createCell((short) 0); titleCell.setEncoding(HSSFCell.ENCODING_UTF_16); titleCell.setCellValue("合计"); titleCell.setCellStyle(contentStyle); titleCell = titleRow.createCell((short) 1); titleCell.setCellStyle(contentStyle); titleCell = titleRow.createCell((short) 2); titleCell.setEncoding(HSSFCell.ENCODING_UTF_16); titleCell.setCellStyle(contentStyle); titleCell.setCellValue("¥" + totalMoney + "元"); titleCell = titleRow.createCell((short) 11); titleCell.setCellStyle(contentStyle); // 设置单元格边框 titleRow = sheet.createRow((short) (i + 4)); titleCell = titleRow.createCell((short) 0); titleCell.setCellStyle(contentStyle); titleCell = titleRow.createCell((short) 1); titleCell.setCellStyle(contentStyle); titleCell = titleRow.createCell((short) 2); titleCell.setCellStyle(contentStyle); titleCell = titleRow.createCell((short) 3); titleCell.setCellStyle(contentStyle); titleCell = titleRow.createCell((short) 4); titleCell.setCellStyle(contentStyle); titleCell = titleRow.createCell((short) 5); titleCell.setCellStyle(contentStyle); titleCell = titleRow.createCell((short) 6); titleCell.setCellStyle(contentStyle); titleCell = titleRow.createCell((short) 7); titleCell.setCellStyle(contentStyle); titleCell = titleRow.createCell((short) 8); titleCell.setCellStyle(contentStyle); titleCell = titleRow.createCell((short) 9); titleCell.setCellStyle(contentStyle); titleCell = titleRow.createCell((short) 10); titleCell.setCellStyle(contentStyle); titleCell = titleRow.createCell((short) 11); titleCell.setCellStyle(contentStyle); sheet.addMergedRegion(new Region((i + 3), (short) 0, (i + 4), (short) 1)); sheet.addMergedRegion(new Region((i + 3), (short) 2, (i + 4), (short) 11)); // 设置单元格边框 titleRow = sheet.createRow((short) (i + 5)); titleCell = titleRow.createCell((short) 0); titleCell.setEncoding(HSSFCell.ENCODING_UTF_16); titleCell.setCellValue("大写"); titleCell.setCellStyle(contentStyle); titleCell = titleRow.createCell((short) 1); titleCell.setCellStyle(contentStyle); titleCell.setCellStyle(contentStyle); titleCell = titleRow.createCell((short) 2); titleCell.setEncoding(HSSFCell.ENCODING_UTF_16); // 转换大写方式 totalString = szTzf(totalMoney); titleCell.setCellValue(totalString); titleCell.setCellStyle(contentStyle); titleCell = titleRow.createCell((short) 11); titleCell.setCellStyle(contentStyle); // 设置单元格边框 titleRow = sheet.createRow((short) (i + 6)); titleCell = titleRow.createCell((short) 0); titleCell.setCellStyle(contentStyle); titleCell = titleRow.createCell((short) 1); titleCell.setCellStyle(contentStyle); titleCell = titleRow.createCell((short) 2); titleCell.setCellStyle(contentStyle); titleCell = titleRow.createCell((short) 3); titleCell.setCellStyle(contentStyle); titleCell = titleRow.createCell((short) 4); titleCell.setCellStyle(contentStyle); titleCell = titleRow.createCell((short) 5); titleCell.setCellStyle(contentStyle); titleCell = titleRow.createCell((short) 6); titleCell.setCellStyle(contentStyle); titleCell = titleRow.createCell((short) 7); titleCell.setCellStyle(contentStyle); titleCell = titleRow.createCell((short) 8); titleCell.setCellStyle(contentStyle); titleCell = titleRow.createCell((short) 9); titleCell.setCellStyle(contentStyle); titleCell = titleRow.createCell((short) 10); titleCell.setCellStyle(contentStyle); titleCell = titleRow.createCell((short) 11); titleCell.setCellStyle(contentStyle); sheet.addMergedRegion(new Region((i + 5), (short) 0, (i + 6), (short) 1)); sheet.addMergedRegion(new Region((i + 5), (short) 2, (i + 6), (short) 11)); // 表尾样式 HSSFCellStyle endStyle = workBook.createCellStyle(); endStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT); endStyle.setBorderBottom(CellStyle.BORDER_THIN); endStyle.setBorderRight(CellStyle.BORDER_THIN); endStyle.setFont(contentFont); // sheet.getRow(0).getCell((short)0).setCellStyle(titleStyle); // sheet.getRow(1).getCell((short)0).setCellStyle(contentStyle); // sheet.getRow(1).getCell((short)1).setCellStyle(contentStyle); // sheet.getRow(2).getCell((short)1).setCellStyle(contentStyle); // sheet.getRow(2).getCell((short)1).setCellStyle(contentStyle); // 导出 HttpServletResponse resp = ServletActionContext.getResponse(); resp.setContentType("application/vnd.ms-excel"); resp.setCharacterEncoding("UFT-8"); String fileName = date + "学工部设备表.xls"; fileName = new String(fileName.getBytes("UTF-8"), "ISO8859-1"); resp.setHeader("Content-Disposition", "attachment; filename=\"" + fileName + "\""); ServletOutputStream op = resp.getOutputStream(); op.flush(); workBook.write(op); op.close(); // return "toExcel"; /* * String fileName = date.toLocaleString().substring(0, 10)+ * "勤工助学酬劳发放表.xls"; FileOutputStream fileOut = new * FileOutputStream(fileName); workBook.write(fileOut); fileOut.close(); */ }
public void exportJgjExcel( String title, List<String[]> header, List<Object[]> dataset, OutputStream out, short[] width, List<int[]> merge) { // 声明一个工作薄 HSSFWorkbook workbook = new HSSFWorkbook(); // 生成一个表格 HSSFSheet sheet = workbook.createSheet(title); // 设置表格默认列宽度为15个字节 sheet.setDefaultColumnWidth((short) 15); // 生成一个样式 HSSFCellStyle style = workbook.createCellStyle(); // 设置这些样式 style.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.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); // 生成一个字体 HSSFFont font = workbook.createFont(); font.setColor(HSSFColor.VIOLET.index); font.setFontHeightInPoints((short) 12); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 把字体应用到当前的样式 style.setFont(font); // 生成并设置另一个样式 HSSFCellStyle style2 = workbook.createCellStyle(); style2.setFillForegroundColor(HSSFColor.WHITE.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); // 生成另一个字体 HSSFFont font2 = workbook.createFont(); font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL); // 把字体应用到当前的样式 style2.setFont(font2); // 产生表格标题行 HSSFRow row = sheet.createRow(0); for (int i = 0; i < header.size(); i++) { row = sheet.createRow(i); for (int m = 0; m < header.get(i).length; m++) { HSSFCell cell = row.createCell(m); cell.setCellStyle(style); String headValue = header.get(i)[m]; // HSSFRichTextString text = new HSSFRichTextString(header.get(i)[m]); cell.setCellValue(headValue); } } if (merge != null && merge.size() > 0) { for (int m = 0; m < merge.size(); m++) { sheet.addMergedRegion( new CellRangeAddress( merge.get(m)[0], merge.get(m)[1], merge.get(m)[2], merge.get(m)[3])); // 合并行 } } // sheet.addMergedRegion(new CellRangeAddress(0,0,9,10)); // sheet.addMergedRegion(new CellRangeAddress(0,0,11,12)); for (int j = 0; j < dataset.size(); j++) { row = sheet.createRow(j + header.size()); for (short i = 0; i < dataset.get(j).length; i++) { HSSFCell cell = row.createCell(i); cell.setCellStyle(style2); HSSFRichTextString richString = new HSSFRichTextString(String.valueOf(dataset.get(j)[i]).replace("null", "")); // HSSFFont font3 = workbook.createFont(); // font3.setColor(HSSFColor.BLACK.index); // richString.applyFont(font3); cell.setCellValue(richString); } } if (width != null) { for (short i = 0; i < width.length; i++) { sheet.setColumnWidth(i, width[i]); } } try { workbook.write(out); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } }
public void exportExcel( String headline, String title, String[] headers, List<Object[]> dataset, OutputStream out, short[] width) { // 声明一个工作薄 HSSFWorkbook workbook = new HSSFWorkbook(); // 生成一个表格 HSSFSheet sheet = workbook.createSheet(title); // 设置表格默认列宽度为15个字节 sheet.setDefaultColumnWidth((short) 15); // 生成一个样式 HSSFCellStyle style = workbook.createCellStyle(); // 设置这些样式 style.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.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); // 生成一个字体 HSSFFont font = workbook.createFont(); font.setColor(HSSFColor.VIOLET.index); font.setFontHeightInPoints((short) 12); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 把字体应用到当前的样式 style.setFont(font); // 生成并设置另一个样式 HSSFCellStyle style2 = workbook.createCellStyle(); style2.setFillForegroundColor(HSSFColor.WHITE.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); // 生成另一个字体 HSSFFont font2 = workbook.createFont(); font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL); // 把字体应用到当前的样式 style2.setFont(font2); // 产生表格标题行 HSSFRow row = sheet.createRow(0); if (row != null) { HSSFCell cell = row.createCell(0); HSSFFont f = workbook.createFont(); f.setColor(HSSFColor.DARK_BLUE.index); f.setFontHeightInPoints((short) 24); f.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 把字体应用到当前的样式 HSSFCellStyle s = workbook.createCellStyle(); s.setAlignment(HSSFCellStyle.ALIGN_CENTER); s.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); s.setFont(f); cell.setCellStyle(s); cell.setCellValue(headline); } sheet.addMergedRegion(new Region(0, (short) 0, 0, (short) (headers.length - 1))); row = sheet.createRow(1); for (short i = 0; i < headers.length; i++) { HSSFCell cell = row.createCell(i); cell.setCellStyle(style); HSSFRichTextString text = new HSSFRichTextString(headers[i]); cell.setCellValue(text); } for (int j = 0; j < dataset.size(); j++) { row = sheet.createRow(j + 2); for (short i = 0; i < dataset.get(j).length; i++) { HSSFCell cell = row.createCell(i); cell.setCellStyle(style2); HSSFRichTextString richString = new HSSFRichTextString(String.valueOf(dataset.get(j)[i]).replace("null", "")); // HSSFFont font3 = workbook.createFont(); // font3.setColor(HSSFColor.BLACK.index); // richString.applyFont(font3); cell.setCellValue(richString); } } if (width != null) { for (short i = 0; i < width.length; i++) { sheet.setColumnWidth(i, width[i]); } } try { workbook.write(out); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } }
public void merge(int startRowIndex, int endRowIndex, int startColumnIndex, int endColumnIndex) { if (startRowIndex <= endRowIndex && startColumnIndex <= endColumnIndex) { sheet.addMergedRegion( new CellRangeAddress(startRowIndex, endRowIndex, startColumnIndex, endColumnIndex)); } }
/** * 写入工作表 * * @param wb Excel工作簿 * @param title Sheet工作表名称 * @param styles 表头样式 * @param creator 创建人 * @param tableData 表格数据 * @throws Exception */ public HSSFWorkbook writeSheet( HSSFWorkbook wb, String title, HashMap<String, HSSFCellStyle> styles, String creator, TableData tableData, String subTitleName) throws Exception { TableHeaderMetaData headerMetaData = tableData.getTableHeader(); // 获得HTML的表头元素 SimpleDateFormat formater = new SimpleDateFormat("yyyy年MM月dd日 HH时mm分"); String create_time = formater.format(new Date()); HSSFSheet sheet = wb.createSheet(title); // 在Excel工作簿中建一工作表 sheet.setDisplayGridlines(false); // 设置表标题是否有表格边框 // 创建标题 HSSFRow row = sheet.createRow(0); // 创建新行 HSSFCell cell = row.createCell(0); // 创建新列 int rownum = 0; cell.setCellValue(new HSSFRichTextString(title)); HSSFCellStyle style = styles.get("TITLE"); // 设置标题样式 if (style != null) cell.setCellStyle(style); sheet.addMergedRegion( new CellRangeAddress( 0, 0, 0, headerMetaData.getColumnCount() - 1)); // 合并标题行:起始行号,终止行号, 起始列号,终止列号 // 创建副标题 row = sheet.createRow(1); cell = row.createCell(0); cell.setCellValue(new HSSFRichTextString(subTitleName)); // updated by zdwang 2014-02-21 style = styles.get("SUB_TITLE"); if (style != null) cell.setCellStyle(style); cell = row.createCell(1); cell.setCellValue(new HSSFRichTextString(creator)); style = styles.get("SUB_TITLE2"); if (style != null) cell.setCellStyle(style); cell = row.createCell(2); cell.setCellValue(new HSSFRichTextString("创建时间:")); style = styles.get("SUB_TITLE"); if (style != null) cell.setCellStyle(style); cell = row.createCell(3); style = styles.get("SUB_TITLE2"); cell.setCellValue(new HSSFRichTextString(create_time)); if (style != null) cell.setCellStyle(style); rownum = 3; // 如果rownum = 1,则去掉创建人、创建时间等副标题;如果rownum = 0, 则把标题也去掉 HSSFCellStyle headerstyle = styles.get("TABLE_HEADER"); int colnum = 0; for (int i = 0; i < headerMetaData.getOriginColumns().size(); i++) { TableColumn tc = headerMetaData.getOriginColumns().get(i); if (i != 0) { colnum += headerMetaData.getOriginColumns().get(i - 1).getLength(); } generateColumn(sheet, tc, headerMetaData.maxlevel, rownum, colnum, headerstyle); } rownum += headerMetaData.maxlevel; List<TableDataRow> dataRows = tableData.getRows(); HashMap<Integer, Integer> counter = new HashMap<Integer, Integer>(); HashMap<Integer, String> word = new HashMap<Integer, String>(); int index = 0; for (TableDataRow dataRow : dataRows) { row = sheet.createRow(rownum); List<TableDataCell> dataCells = dataRow.getCells(); int size = headerMetaData.getColumns().size(); index = -1; for (int i = 0; i < size; i++) { TableColumn tc = headerMetaData.getColumns().get(i); if (!tc.isVisible()) continue; index++; String value = dataCells.get(i).getValue(); if (tc.isGrouped()) { String w = word.get(index); if (w == null) { word.put(index, value); counter.put(index, 1); createCell(row, tc, dataCells, i, index, styles); } else { if (w.equals(value)) { counter.put(index, counter.get(index) + 1); } else { stopGrouping(sheet, word, counter, index, size, rownum, styles.get("STRING")); word.put(index, value); counter.put(index, 1); createCell(row, tc, dataCells, i, index, styles); } } } else { createCell(row, tc, dataCells, i, index, styles); } } rownum++; } stopGrouping(sheet, word, counter, 0, index, rownum, styles.get("STRING")); // 设置前两列根据数据自动列宽 for (int c = 0; c < headerMetaData.getColumns().size(); c++) { sheet.autoSizeColumn((short) c); String t = headerMetaData.getColumns().get(c).getDisplay(); if (sheet.getColumnWidth(c) < t.length() * 256 * 3) sheet.setColumnWidth(c, t.length() * 256 * 3); } sheet.setGridsPrinted(true); return wb; }
/** * 读取xml定义的模板,创建excel,感觉没啥用 * * @author David * @param args */ public static void main(String[] args) { // 获取解析xml文件路径 String path = System.getProperty("user.dir") + "/bin/student2.xml"; File file = new File(path); SAXBuilder builder = new SAXBuilder(); try { // 解析xml文件 Document parse = builder.build(file); // 创建Excel HSSFWorkbook wb = new HSSFWorkbook(); // 创建sheet HSSFSheet sheet = wb.createSheet("Sheet0"); // 获取xml文件跟节点 Element root = parse.getRootElement(); // 获取模板名称 String templateName = root.getAttribute("name").getValue(); int rownum = 0; int column = 0; // 设置列宽 Element colgroup = root.getChild("colgroup"); setColumnWidth(sheet, colgroup); // 设置标题 Element title = root.getChild("title"); List<Element> trs = title.getChildren("tr"); for (int i = 0; i < trs.size(); i++) { Element tr = trs.get(i); List<Element> tds = tr.getChildren("td"); HSSFRow row = sheet.createRow(rownum); HSSFCellStyle cellStyle = wb.createCellStyle(); cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); for (column = 0; column < tds.size(); column++) { Element td = tds.get(column); HSSFCell cell = row.createCell(column); Attribute rowSpan = td.getAttribute("rowspan"); Attribute colSpan = td.getAttribute("colspan"); Attribute value = td.getAttribute("value"); if (value != null) { String val = value.getValue(); cell.setCellValue(val); int rspan = rowSpan.getIntValue() - 1; int cspan = colSpan.getIntValue() - 1; // 设置字体 HSSFFont font = wb.createFont(); font.setFontName("仿宋_GB2312"); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 字体加粗 // font.setFontHeight((short)12); font.setFontHeightInPoints((short) 12); cellStyle.setFont(font); cell.setCellStyle(cellStyle); // 合并单元格居中 sheet.addMergedRegion(new CellRangeAddress(rspan, rspan, 0, cspan)); } } rownum++; } // 设置表头 Element thead = root.getChild("thead"); trs = thead.getChildren("tr"); for (int i = 0; i < trs.size(); i++) { Element tr = trs.get(i); HSSFRow row = sheet.createRow(rownum); List<Element> ths = tr.getChildren("th"); for (column = 0; column < ths.size(); column++) { Element th = ths.get(column); Attribute valueAttr = th.getAttribute("value"); HSSFCell cell = row.createCell(column); if (valueAttr != null) { String value = valueAttr.getValue(); cell.setCellValue(value); } } rownum++; } // 设置数据区域样式 Element tbody = root.getChild("tbody"); Element tr = tbody.getChild("tr"); int repeat = tr.getAttribute("repeat").getIntValue(); List<Element> tds = tr.getChildren("td"); for (int i = 0; i < repeat; i++) { HSSFRow row = sheet.createRow(rownum); for (column = 0; column < tds.size(); column++) { Element td = tds.get(column); HSSFCell cell = row.createCell(column); setType(wb, cell, td); } rownum++; } // 生成Excel导入模板 File tempFile = new File("e:/" + templateName + ".xls"); tempFile.delete(); tempFile.createNewFile(); FileOutputStream stream = FileUtils.openOutputStream(tempFile); wb.write(stream); stream.close(); } catch (Exception e) { e.printStackTrace(); } }
/** * 导入的模板 * * @param request * @param response * @param companyId * @return * @throws Exception */ @RequestMapping("/exportTemplate") public String exportTemplate(HttpServletRequest request, HttpServletResponse response) throws Exception { HSSFWorkbook wb = new HSSFWorkbook(); // excel文件对象 HSSFSheet sheet = wb.createSheet("体检报告物流信息导入模板"); CellStyle style = getStyle(wb); CellRangeAddress range = new CellRangeAddress(0, 2, 0, 9); sheet.addMergedRegion(range); Row row0 = sheet.createRow(0); Cell cell00 = row0.createCell(0, Cell.CELL_TYPE_STRING); cell00.setCellStyle(style); cell00.setCellValue("说明:请正确填写如下信息,如‘HR订单账号’‘兑换子订单号’‘操作时间(2015/5/20)’"); sheet.autoSizeColumn(0); Row row1 = sheet.createRow(3); /* Cell cell0 = row1.createCell(0, Cell.CELL_TYPE_STRING); cell0.setCellStyle(style); cell0.setCellValue("物流面单号"); sheet.autoSizeColumn(0);*/ Cell cell1 = row1.createCell(0, Cell.CELL_TYPE_STRING); cell1.setCellStyle(style); cell1.setCellValue("总订单号"); sheet.autoSizeColumn(0); Cell cell2 = row1.createCell(1, Cell.CELL_TYPE_STRING); cell2.setCellStyle(style); cell2.setCellValue("兑换子订单号*"); sheet.autoSizeColumn(1); Cell cell3 = row1.createCell(2, Cell.CELL_TYPE_STRING); cell3.setCellStyle(style); cell3.setCellValue("物流公司编号*"); sheet.autoSizeColumn(2); Cell cell4 = row1.createCell(3, Cell.CELL_TYPE_STRING); cell4.setCellStyle(style); cell4.setCellValue("物流编号*"); sheet.autoSizeColumn(3); Cell cell6 = row1.createCell(4, Cell.CELL_TYPE_STRING); cell6.setCellStyle(style); cell6.setCellValue("更新人"); sheet.autoSizeColumn(4); Cell cell7 = row1.createCell(5, Cell.CELL_TYPE_STRING); cell7.setCellStyle(style); cell7.setCellValue("更新时间"); sheet.autoSizeColumn(5); Cell cell8 = row1.createCell(6, Cell.CELL_TYPE_STRING); cell8.setCellStyle(style); cell8.setCellValue("操作人"); sheet.autoSizeColumn(6); Cell cell9 = row1.createCell(7, Cell.CELL_TYPE_STRING); cell9.setCellStyle(style); cell9.setCellValue("导入时间"); sheet.autoSizeColumn(7); String fileName = FileUpDownUtils.encodeDownloadFileName(request, "体检物流信息导入模板" + ".xls"); FileUpDownUtils.setDownloadResponseHeaders(response, fileName); wb.write(response.getOutputStream()); return null; }
/** * 写入工作表 * * @param wb Excel工作簿 * @param title Sheet工作表名称 * @param styles 表头样式 * @param creator 创建人 * @param tableData 表格数据 * @throws Exception */ public HSSFWorkbook writeSheet( HSSFWorkbook wb, HashMap<String, HSSFCellStyle> styles, String creator, List<TableData> tableDataLst) throws Exception { SimpleDateFormat formater = new SimpleDateFormat("yyyy年MM月dd日 HH时mm分"); String create_time = formater.format(new Date()); int cnt = 1; for (TableData tableData : tableDataLst) { String sheetTitle = tableData.getSheetTitle(); sheetTitle = sheetTitle == null || sheetTitle.equals("") ? "sheet" + cnt : sheetTitle; cnt++; TableHeaderMetaData headerMetaData = tableData.getTableHeader(); // 获得HTML的表头元素 HSSFSheet sheet = wb.createSheet(sheetTitle); // 在Excel工作簿中建一工作表 sheet.setDisplayGridlines(false); // 设置表标题是否有表格边框 wb.cloneSheet(0); // 创建标题 HSSFRow row = sheet.createRow(0); // 创建新行 HSSFCell cell = row.createCell(0); // 创建新列 int rownum = 0; cell.setCellValue(new HSSFRichTextString(sheetTitle)); HSSFCellStyle style = styles.get("TITLE"); // 设置标题样式 if (style != null) cell.setCellStyle(style); sheet.addMergedRegion( new CellRangeAddress( 0, 0, 0, headerMetaData.getColumnCount() - 1)); // 合并标题行:起始行号,终止行号, 起始列号,终止列号 // 创建副标题 row = sheet.createRow(1); cell = row.createCell(0); cell.setCellValue(new HSSFRichTextString("创建人:")); style = styles.get("SUB_TITLE"); if (style != null) cell.setCellStyle(style); cell = row.createCell(1); cell.setCellValue(new HSSFRichTextString(creator)); style = styles.get("SUB_TITLE2"); if (style != null) cell.setCellStyle(style); cell = row.createCell(2); cell.setCellValue(new HSSFRichTextString("创建时间:")); style = styles.get("SUB_TITLE"); if (style != null) cell.setCellStyle(style); cell = row.createCell(3); style = styles.get("SUB_TITLE2"); cell.setCellValue(new HSSFRichTextString(create_time)); if (style != null) cell.setCellStyle(style); rownum = 3; // 如果rownum = 1,则去掉创建人、创建时间等副标题;如果rownum = 0, 则把标题也去掉 HSSFCellStyle headerstyle = styles.get("TABLE_HEADER"); int colnum = 0; for (int i = 0; i < headerMetaData.getOriginColumns().size(); i++) { TableColumn tc = headerMetaData.getOriginColumns().get(i); if (i != 0) { colnum += headerMetaData.getOriginColumns().get(i - 1).getLength(); } generateColumn(sheet, tc, headerMetaData.maxlevel, rownum, colnum, headerstyle); } rownum += headerMetaData.maxlevel; List<TableDataRow> dataRows = tableData.getRows(); int index = 0; for (TableDataRow dataRow : dataRows) { row = sheet.createRow(rownum); List<TableDataCell> dataCells = dataRow.getCells(); int size = headerMetaData.getColumns().size(); index = -1; for (int i = 0; i < size; i++) { TableColumn tc = headerMetaData.getColumns().get(i); if (!tc.isVisible()) continue; index++; createCell(row, tc, dataCells, i, index, styles); } rownum++; } // 设置前两列根据数据自动列宽 for (int c = 0; c < headerMetaData.getColumns().size(); c++) { sheet.autoSizeColumn((short) c); String t = headerMetaData.getColumns().get(c).getDisplay(); if (sheet.getColumnWidth(c) < t.length() * 256 * 3) sheet.setColumnWidth(c, t.length() * 256 * 3); } sheet.setGridsPrinted(true); } return wb; }
public void generateExcel(OutputStream out, XLSCallBack<T> xlscaCallBack) throws RuntimeException, IOException { HSSFWorkbook workbook = new HSSFWorkbook(); // create a sheet with specified name HSSFSheet sheet = workbook.createSheet(xlscaCallBack.getSheetName()); for (int i = 0; i < xlscaCallBack.getColumnWidth().length; i++) { sheet.setColumnWidth(i, xlscaCallBack.getColumnWidth()[i] * 256); } // create a title for sheet title sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, xlscaCallBack.getHeaders().length - 1)); HSSFCellStyle titleCellStyle = workbook.createCellStyle(); // create titleCellStyle for cell titleCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFFont titleFont = workbook.createFont(); // set font titleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); titleFont.setFontName("宋体"); titleFont.setFontHeight((short) (240)); titleFont.setColor(HSSFColor.AUTOMATIC.index); titleCellStyle.setFont(titleFont); titleCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); // set border titleCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); titleCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); titleCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); titleCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // set background titleCellStyle.setFillForegroundColor(HSSFColor.LIGHT_BLUE.index); titleCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); titleCellStyle.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index); HSSFRow title = sheet.createRow(0); title.setHeight((short) 300); for (int i = 0; i < xlscaCallBack.getHeaders().length; i++) { // create tytle for title HSSFCell cell = title.createCell(i); cell.setCellValue(xlscaCallBack.getTitle()); cell.setCellStyle(titleCellStyle); } HSSFCellStyle headerCellStyle = workbook.createCellStyle(); // create headerCellStyle for cell headerCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFFont headerFont = workbook.createFont(); // set font headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); headerFont.setFontName("幼圆"); headerFont.setColor(HSSFColor.AUTOMATIC.index); headerCellStyle.setFont(headerFont); headerCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); // set border headerCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); headerCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); headerCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); headerCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // set background headerCellStyle.setFillForegroundColor(HSSFColor.LIGHT_BLUE.index); headerCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); headerCellStyle.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index); HSSFRow header = sheet.createRow(1); for (int i = 0; i < xlscaCallBack.getHeaders().length; i++) { // create tytle for header HSSFCell cell = header.createCell(i); cell.setCellValue(xlscaCallBack.getHeaders()[i]); cell.setCellStyle(headerCellStyle); } HSSFCellStyle rowCellStyle = workbook.createCellStyle(); // create headerCellStyle for cell rowCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); HSSFFont rowFont = workbook.createFont(); // set font rowFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL); rowFont.setFontName("幼圆"); rowFont.setColor(HSSFColor.AUTOMATIC.index); rowCellStyle.setFont(rowFont); rowCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); // set border rowCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); rowCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); rowCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); for (int i = 0; i < lists.size(); i++) { HSSFRow row = sheet.createRow(i + 2); String values[] = xlscaCallBack.getValue(lists.get(i)); for (int j = 0; j < values.length; j++) { HSSFCell cell = row.createCell(j); cell.setCellValue(values[j]); cell.setCellStyle(rowCellStyle); } } HSSFCellStyle bottomCellStyle = workbook.createCellStyle(); // create titleCellStyle for cell bottomCellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT); HSSFFont bottomFont = workbook.createFont(); // set font bottomFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL); bottomFont.setFontName("幼圆"); bottomFont.setColor(HSSFColor.AUTOMATIC.index); bottomCellStyle.setFont(bottomFont); bottomCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); // set border bottomCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); bottomCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); bottomCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); bottomCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // set background bottomCellStyle.setFillForegroundColor(HSSFColor.LIGHT_BLUE.index); bottomCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); bottomCellStyle.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index); sheet.addMergedRegion( new CellRangeAddress( lists.size() + 2, lists.size() + 2, 0, xlscaCallBack.getHeaders().length - 1)); // creaet bootom for xls HSSFRow bottomRow = sheet.createRow(lists.size() + 2); for (int i = 0; i < xlscaCallBack.getHeaders().length; i++) { // create tytle for title HSSFCell cell = bottomRow.createCell(i); SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss"); cell.setCellValue("共计导出 " + lists.size() + " 条记录 ,导出日期:" + sf.format(new Date())); cell.setCellStyle(bottomCellStyle); } try { workbook.write(out); } catch (IOException e) { e.printStackTrace(); throw new RuntimeException("create Excel failed due to some unkonw reasion~"); } }
private void createDetailSheet(final IndicatorDTO indicator) { final boolean isQualitative = indicator.getAggregation() == IndicatorDTO.AGGREGATE_MULTINOMIAL; final HSSFSheet sheetEx = wb.createSheet( utils.normalizeAsLink(ExportConstants.INDICATOR_SHEET_PREFIX + indicator.getName())); int rowIndex = -1; final List<PivotTableData.Axis> leaves = data.getEntryMap().get(indicator.getId()).getRootColumn().getLeaves(); int numbOfLeaves = leaves.size(); int numbOfCols = 4; // back to list link row = sheetEx.createRow(++rowIndex); utils.createLinkCell( row.createCell(1), data.getLocalizedVersion("goToIndicatorsList"), data.getLocalizedVersion("flexibleElementIndicatorsList"), false); sheetEx.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 1, numbOfCols)); // title utils.putMainTitle(sheetEx, ++rowIndex, indicator.getName(), numbOfCols); // empty row utils.putEmptyRow(sheetEx, ++rowIndex, ExportConstants.EMPTY_ROW_HEIGHT); sheetEx.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 1, numbOfCols)); // put details putBasicInfo( sheetEx, ++rowIndex, data.getLocalizedVersion("code"), indicator.getCode(), numbOfCols); putBasicInfo( sheetEx, ++rowIndex, data.getLocalizedVersion("group"), data.getGroupMap().get(indicator.getGroupId()), numbOfCols); // type String type = null; ; if (isQualitative) { // qualitative type = data.getLocalizedVersion("qualitative"); } else { // quantitative type = data.getLocalizedVersion("quantitative"); } putBasicInfo(sheetEx, ++rowIndex, data.getLocalizedVersion("type"), type, numbOfCols); // conditional if (isQualitative) { // qualitative // possible values row = sheetEx.createRow(++rowIndex); // key cell = utils.putHeader(row, 1, data.getLocalizedVersion("possibleValues")); cell.getCellStyle().setAlignment(CellStyle.ALIGN_RIGHT); // value final MultiItemText itemText = data.formatPossibleValues(indicator.getLabels()); utils.putBorderedBasicCell(sheetEx, rowIndex, 2, itemText.text); row.setHeightInPoints(itemText.lineCount * defHeight); region = new CellRangeAddress(rowIndex, rowIndex, 2, numbOfCols); sheetEx.addMergedRegion(utils.getBorderedRegion(region, sheetEx, wb)); } else { // quantitative // aggregation method String aggrMethod = null; if (indicator.getAggregation() == IndicatorDTO.AGGREGATE_AVG) aggrMethod = data.getLocalizedVersion("average"); else aggrMethod = data.getLocalizedVersion("sum"); putBasicInfo( sheetEx, ++rowIndex, data.getLocalizedVersion("aggregationMethod"), aggrMethod, numbOfCols); // units putBasicInfo( sheetEx, ++rowIndex, data.getLocalizedVersion("units"), indicator.getUnits(), numbOfCols); // target value putBasicInfo( sheetEx, ++rowIndex, data.getLocalizedVersion("targetValue"), indicator.getObjective(), numbOfCols); } // source of ver putBasicInfo( sheetEx, ++rowIndex, data.getLocalizedVersion("sourceOfVerification"), indicator.getSourceOfVerification(), numbOfCols); row.setHeightInPoints( utils.calculateLineCount(indicator.getSourceOfVerification(), 3 * 18) * defHeight); // comment putBasicInfo( sheetEx, ++rowIndex, data.getLocalizedVersion("indicatorComments"), indicator.getDescription(), numbOfCols); row.setHeightInPoints(utils.calculateLineCount(indicator.getDescription(), 3 * 18) * defHeight); // value putBasicInfo( sheetEx, ++rowIndex, data.getLocalizedVersion("value"), data.getFormattedValue(indicator), numbOfCols); // empty row utils.putEmptyRow(sheetEx, ++rowIndex, ExportConstants.EMPTY_ROW_HEIGHT); sheetEx.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 1, numbOfCols)); // data entry // header row = sheetEx.createRow(++rowIndex); row.setHeightInPoints(defHeight); int cellIndex = 0; utils.putHeader(row, ++cellIndex, data.getLocalizedVersion("sideAndMonth")); Map<String, Integer> columnIndexMap = new HashMap<String, Integer>(); for (PivotTableData.Axis axis : leaves) { utils.putHeader(row, ++cellIndex, axis.getLabel()); columnIndexMap.put(axis.getLabel(), cellIndex); } // rows for (PivotTableData.Axis axis : data.getEntryMap().get(indicator.getId()).getRootRow().getChildren()) { row = sheetEx.createRow(++rowIndex); row.setHeightInPoints(defHeight); utils.putHeader(row, 1, axis.getLabel()); // populate empty cells for (int i = 0; i < numbOfLeaves; i++) { cell = utils.putBorderedBasicCell(sheetEx, rowIndex, i + 2, ""); } // insert values for (Map.Entry<PivotTableData.Axis, PivotTableData.Cell> entry : axis.getCells().entrySet()) { cellIndex = columnIndexMap.get(entry.getKey().getLabel()); Object value = null; boolean rightAligned = false; if (isQualitative) { value = data.getLabelByIndex(indicator.getLabels(), entry.getValue().getValue()); } else { value = new Long(Math.round(entry.getValue().getValue())); rightAligned = true; } putValueCell(sheetEx, rowIndex, cellIndex, value, rightAligned); } } // col width sheetEx.setColumnWidth(0, 256 * 2); sheetEx.autoSizeColumn(1); for (int i = 2; i < 2 + numbOfLeaves; i++) { sheetEx.setColumnWidth(i, 256 * 16); } }