List of usage examples for org.apache.poi.ss.util CellRangeAddress CellRangeAddress
public CellRangeAddress(int firstRow, int lastRow, int firstCol, int lastCol)
From source file:com.helger.poi.excel.WorkbookCreationHelper.java
License:Apache License
/** * @param nRowIndex//from ww w . j av a2s . c o m * The 0-based index of the row, where to set the filter. Add an auto * filter on all columns in the current sheet. */ public void autoFilterAllColumns(@Nonnegative final int nRowIndex) { // Set auto filter on all columns // Use the specified row (param1, param2) // From first column to last column (param3, param4) m_aLastSheet.setAutoFilter(new CellRangeAddress(nRowIndex, nRowIndex, 0, m_nMaxCellIndex - 1)); }
From source file:com.hp.autonomy.frontend.reports.powerpoint.PowerPointServiceImpl.java
License:MIT License
/** * Internal implementation to add a sunburst chart (actually a doughnut chart) to a slide, based on a template. * @param template the parsed template information. * @param slide the slide to add to.//from ww w .j a v a 2 s . co m * @param anchor optional bounding rectangle to draw onto, in PowerPoint coordinates. * If null, we'll use the bounds from the original template chart. * @param data the sunburst data. * @param shapeId the slide shape ID, should be unique within the slide. * @param relId the relation ID to the chart data. * @throws TemplateLoadException if we can't create the sunburst; most likely due to an invalid template. */ private static void addSunburst(final SlideShowTemplate template, final XSLFSlide slide, final Rectangle2D.Double anchor, final SunburstData data, final int shapeId, final String relId) throws TemplateLoadException { final String[] categories = data.getCategories(); final double[] values = data.getValues(); final String title = data.getTitle(); slide.getXmlObject().getCSld().getSpTree().addNewGraphicFrame() .set(template.getDoughnutChartShapeXML(relId, shapeId, "chart" + shapeId, anchor)); final XSSFWorkbook workbook = new XSSFWorkbook(); final XSSFSheet sheet = workbook.createSheet(); final XSLFChart baseChart = template.getDoughnutChart(); final CTChartSpace chartSpace = (CTChartSpace) baseChart.getCTChartSpace().copy(); final CTChart ctChart = chartSpace.getChart(); final CTPlotArea plotArea = ctChart.getPlotArea(); if (StringUtils.isEmpty(title)) { if (ctChart.getAutoTitleDeleted() != null) { ctChart.getAutoTitleDeleted().setVal(true); } ctChart.unsetTitle(); } final CTDoughnutChart donutChart = plotArea.getDoughnutChartArray(0); final CTPieSer series = donutChart.getSerArray(0); final CTStrRef strRef = series.getTx().getStrRef(); strRef.getStrCache().getPtArray(0).setV(title); sheet.createRow(0).createCell(1).setCellValue(title); strRef.setF(new CellReference(sheet.getSheetName(), 0, 1, true, true).formatAsString()); final CTStrRef categoryRef = series.getCat().getStrRef(); final CTStrData categoryData = categoryRef.getStrCache(); final CTNumRef numRef = series.getVal().getNumRef(); final CTNumData numericData = numRef.getNumCache(); final String[] fillColors = data.getColors(); final String[] strokeColors = data.getStrokeColors(); final boolean overrideFill = ArrayUtils.isNotEmpty(fillColors); final boolean overrideStroke = ArrayUtils.isNotEmpty(strokeColors); final boolean overrideColors = overrideFill || overrideStroke; final List<CTDPt> dPtList = series.getDPtList(); final CTDPt templatePt = (CTDPt) dPtList.get(0).copy(); if (overrideColors) { dPtList.clear(); final CTShapeProperties spPr = templatePt.getSpPr(); final CTLineProperties ln = spPr.getLn(); // We need to unset any styles on the existing template if (overrideFill) { unsetSpPrFills(spPr); } if (overrideStroke) { unsetLineFills(ln); } } categoryData.setPtArray(null); numericData.setPtArray(null); CTLegend legend = null; final int[] showInLegend = data.getShowInLegend(); int nextLegendToShow = 0, nextLegendToShowIdx = -1; if (showInLegend != null) { // We need to write legendEntry elements to hide the legend for chart series we don't want. // Note this only works in PowerPoint, and not OpenOffice. legend = ctChart.isSetLegend() ? ctChart.getLegend() : ctChart.addNewLegend(); Arrays.sort(showInLegend); nextLegendToShow = showInLegend[++nextLegendToShowIdx]; } for (int idx = 0; idx < values.length; ++idx) { final CTStrVal categoryPoint = categoryData.addNewPt(); categoryPoint.setIdx(idx); categoryPoint.setV(categories[idx]); final CTNumVal numericPoint = numericData.addNewPt(); numericPoint.setIdx(idx); numericPoint.setV(Double.toString(values[idx])); if (overrideColors) { final CTDPt copiedPt = (CTDPt) templatePt.copy(); copiedPt.getIdx().setVal(idx); if (overrideFill) { final Color color = Color.decode(fillColors[idx % fillColors.length]); final CTSolidColorFillProperties fillClr = copiedPt.getSpPr().addNewSolidFill(); fillClr.addNewSrgbClr().setVal( new byte[] { (byte) color.getRed(), (byte) color.getGreen(), (byte) color.getBlue() }); } if (overrideStroke) { final Color strokeColor = Color.decode(strokeColors[idx % strokeColors.length]); final CTSolidColorFillProperties strokeClr = copiedPt.getSpPr().getLn().addNewSolidFill(); strokeClr.addNewSrgbClr().setVal(new byte[] { (byte) strokeColor.getRed(), (byte) strokeColor.getGreen(), (byte) strokeColor.getBlue() }); } dPtList.add(copiedPt); } if (legend != null) { // We're hiding some legend elements. Should we show this index? if (nextLegendToShow == idx) { // We show this index, find the next one to show. ++nextLegendToShowIdx; if (nextLegendToShowIdx < showInLegend.length) { nextLegendToShow = showInLegend[nextLegendToShowIdx]; } } else { // We hide this index. If there's already a matching legend entry in the XML, update it, // otherwise we create a new legend entry. boolean found = false; for (int ii = 0, max = legend.sizeOfLegendEntryArray(); ii < max; ++ii) { final CTLegendEntry legendEntry = legend.getLegendEntryArray(ii); final CTUnsignedInt idxLegend = legendEntry.getIdx(); if (idxLegend != null && idxLegend.getVal() == idx) { found = true; if (legendEntry.isSetDelete()) { legendEntry.getDelete().setVal(true); } else { legendEntry.addNewDelete().setVal(true); } } } if (!found) { final CTLegendEntry idxLegend = legend.addNewLegendEntry(); idxLegend.addNewIdx().setVal(idx); idxLegend.addNewDelete().setVal(true); } } } XSSFRow row = sheet.createRow(idx + 1); row.createCell(0).setCellValue(categories[idx]); row.createCell(1).setCellValue(values[idx]); } categoryData.getPtCount().setVal(categories.length); numericData.getPtCount().setVal(values.length); categoryRef.setF(new CellRangeAddress(1, values.length, 0, 0).formatAsString(sheet.getSheetName(), true)); numRef.setF(new CellRangeAddress(1, values.length, 1, 1).formatAsString(sheet.getSheetName(), true)); try { writeChart(template.getSlideShow(), slide, baseChart, chartSpace, workbook, relId); } catch (IOException | InvalidFormatException e) { throw new TemplateLoadException("Error writing chart in loaded template", e); } }
From source file:com.huateng.struts.query.action.T50202Action.java
License:Open Source License
public String download() { String brhBelow = InformationUtil.getBrhGroupString(brhId); System.out.println(brhBelow); String thisMonStart = date + "01"; String thisMonEnd = date + "31"; String lastYear = String.valueOf(Integer.parseInt(date.substring(0, 4)) - 1) + "1231"; String lastMon = thisMonStart;//start? String thisYearStart = date.substring(0, 4) + "0101"; String thisYearEnd = date.substring(0, 4) + "1231"; try {/*from w w w. ja v a2 s . c o m*/ //? String[][] data = new String[40][9]; //?data for (int i = 0; i < data.length; i++) { for (int j = 0; j < data[i].length; j++) { if (j < 4) { data[i][j] = ""; } else { data[i][j] = "0"; } } } Iterator it = null; //??? //?data data[0][0] = "1"; data[0][1] = "?"; data[0][3] = ""; data[1][1] = ""; data[1][2] = "?"; data[1][3] = ""; data[2][2] = "?"; data[2][3] = ""; String sql = "select CONN_TYPE, " + "sum(case when apply_date <= '" + lastYear + "' then 1 else 0 end) as c1," + "sum(case when apply_date < '" + lastMon + "' then 1 else 0 end) as c2," + "sum(case when (apply_date >= '" + thisMonStart + "' and apply_date <= '" + thisMonEnd + "') then 1 else 0 end) as c3," + "sum(case when (apply_date >= '" + thisYearStart + "' and apply_date <= '" + thisYearEnd + "') then 1 else 0 end) as c4 " + "from TBL_MCHT_BASE_INF where ACQ_INST_ID in " + brhBelow + " group by CONN_TYPE"; List list = commQueryDAO.findBySQLQuery(sql); if (null != list && !list.isEmpty()) { it = list.iterator(); while (it.hasNext()) { Object[] obj = (Object[]) it.next(); int index = 0; if ("J".equals(obj[0])) { index = 1; } else { index = 2; } data[index][4] = obj[1].toString(); data[index][5] = obj[2].toString(); data[index][6] = obj[3].toString(); data[index][7] = obj[4].toString(); data[index][8] = String .valueOf(Integer.valueOf(obj[1].toString()) + Integer.valueOf(obj[4].toString())); } data[0][4] = String.valueOf(Integer.valueOf(data[1][4]) + Integer.valueOf(data[2][4])); data[0][5] = String.valueOf(Integer.valueOf(data[1][5]) + Integer.valueOf(data[2][5])); data[0][6] = String.valueOf(Integer.valueOf(data[1][6]) + Integer.valueOf(data[2][6])); data[0][7] = String.valueOf(Integer.valueOf(data[1][7]) + Integer.valueOf(data[2][7])); data[0][8] = String.valueOf(Integer.valueOf(data[1][8]) + Integer.valueOf(data[2][8])); } //??? //?data data[3][0] = "2"; data[3][1] = "?POS"; data[3][3] = "?"; data[4][1] = ""; data[4][2] = "?"; data[4][3] = "?"; data[5][2] = "?"; data[5][3] = "?"; sql = "select CONN_TYPE ," + "sum(case when t.REC_CRT_TS <= '" + lastYear + "' then 1 else 0 end) as c1," + "sum(case when t.REC_CRT_TS < '" + lastMon + "' then 1 else 0 end) as c2," + "sum(case when (t.REC_CRT_TS >= '" + thisMonStart + "' and t.REC_CRT_TS <= '" + thisMonEnd + "') then 1 else 0 end) as c3," + "sum(case when (t.REC_CRT_TS >= '" + thisYearStart + "' and t.REC_CRT_TS <= '" + thisYearEnd + "') then 1 else 0 end) as c4 " + "from TBL_TERM_INF t,TBL_MCHT_BASE_INF m where t.MCHT_CD = m.MCHT_NO " + "and m.ACQ_INST_ID in " + brhBelow + " group by CONN_TYPE"; list = commQueryDAO.findBySQLQuery(sql); if (null != list && !list.isEmpty()) { it = list.iterator(); while (it.hasNext()) { Object[] obj = (Object[]) it.next(); int index = 0; if ("J".equals(obj[0])) { index = 4; } else { index = 5; } data[index][4] = obj[1].toString(); data[index][5] = obj[2].toString(); data[index][6] = obj[3].toString(); data[index][7] = obj[4].toString(); data[index][8] = String .valueOf(Integer.valueOf(obj[1].toString()) + Integer.valueOf(obj[4].toString())); } data[3][4] = String.valueOf(Integer.valueOf(data[4][4]) + Integer.valueOf(data[5][4])); data[3][5] = String.valueOf(Integer.valueOf(data[4][5]) + Integer.valueOf(data[5][5])); data[3][6] = String.valueOf(Integer.valueOf(data[4][6]) + Integer.valueOf(data[5][6])); data[3][7] = String.valueOf(Integer.valueOf(data[4][7]) + Integer.valueOf(data[5][7])); data[3][8] = String.valueOf(Integer.valueOf(data[4][8]) + Integer.valueOf(data[5][8])); } //??? //?data data[6][0] = "3"; data[6][1] = "?"; sql = "SELECT DESCR,nvl(mchnt1.c,0),nvl(mchnt2.c,0),nvl(mchnt3.c,0),nvl(mchnt4.c,0) FROM TBL_INF_MCHNT_TP_GRP grp " + "left outer join (select MCHT_GRP,COUNT(*) as c from TBL_MCHT_BASE_INF where ACQ_INST_ID in " + brhBelow + " and apply_date <= '" + lastYear + "' group by MCHT_GRP) mchnt1 on (grp.MCHNT_TP_GRP = mchnt1.MCHT_GRP) " + "left outer join (select MCHT_GRP,COUNT(*) as c from TBL_MCHT_BASE_INF where ACQ_INST_ID in " + brhBelow + " and apply_date < '" + lastMon + "' group by MCHT_GRP) mchnt2 on (grp.MCHNT_TP_GRP = mchnt2.MCHT_GRP)" + "left outer join (select MCHT_GRP,COUNT(*) as c from TBL_MCHT_BASE_INF where ACQ_INST_ID in " + brhBelow + " and apply_date >= '" + thisMonStart + "' and apply_date <= '" + thisMonEnd + "' group by MCHT_GRP) mchnt3 on (grp.MCHNT_TP_GRP = mchnt3.MCHT_GRP)" + "left outer join (select MCHT_GRP,COUNT(*) as c from TBL_MCHT_BASE_INF where ACQ_INST_ID in " + brhBelow + " and apply_date >= '" + thisYearStart + "' and apply_date <= '" + thisYearEnd + "' group by MCHT_GRP) mchnt4 on (grp.MCHNT_TP_GRP = mchnt4.MCHT_GRP) " + "order by MCHNT_TP_GRP "; list = commQueryDAO.findBySQLQuery(sql); int index = 6; int len = 0; if (null != list && !list.isEmpty()) { it = list.iterator(); while (it.hasNext()) { Object[] obj = (Object[]) it.next(); if (!StringUtil.isNull(obj[0]) && (obj[0].toString().indexOf("(") != -1 || obj[0].toString().indexOf("") != -1)) { int a = obj[0].toString().indexOf("("); int b = obj[0].toString().indexOf(""); if (a > 0 && b > 0) { data[index][2] = obj[0].toString().substring(0, a < b ? a : b); } else { data[index][2] = obj[0].toString().substring(0, a + b + 1); } } else { data[index][2] = obj[0].toString(); } data[index][3] = ""; data[index][4] = obj[1].toString(); data[index][5] = obj[2].toString(); data[index][6] = obj[3].toString(); data[index][7] = obj[4].toString(); data[index][8] = String .valueOf(Integer.valueOf(obj[1].toString()) + Integer.valueOf(obj[4].toString())); index++; len++; } } data[index][0] = "4"; data[index][1] = "?POS"; sql = "SELECT DESCR,nvl(term1.c,0),nvl(term2.c,0),nvl(term3.c,0),nvl(term4.c,0) FROM TBL_INF_MCHNT_TP_GRP grp " + "left outer join (select MCHT_GRP,COUNT(*) as c FROM TBL_TERM_INF t,TBL_MCHT_BASE_INF m where m.ACQ_INST_ID in " + brhBelow + " and t.MCHT_CD = m.MCHT_NO AND t.REC_CRT_TS <= '" + lastYear + "' group by MCHT_GRP) term1 on (grp.MCHNT_TP_GRP = term1.MCHT_GRP) " + "left outer join (select MCHT_GRP,COUNT(*) as c FROM TBL_TERM_INF t,TBL_MCHT_BASE_INF m where m.ACQ_INST_ID in " + brhBelow + " and t.MCHT_CD = m.MCHT_NO AND t.REC_CRT_TS < '" + lastMon + "' group by MCHT_GRP) term2 on (grp.MCHNT_TP_GRP = term2.MCHT_GRP)" + "left outer join (select MCHT_GRP,COUNT(*) as c FROM TBL_TERM_INF t,TBL_MCHT_BASE_INF m where m.ACQ_INST_ID in " + brhBelow + " and t.MCHT_CD = m.MCHT_NO AND t.REC_CRT_TS >= '" + thisMonStart + "' and t.REC_CRT_TS <= '" + thisMonEnd + "' group by MCHT_GRP) term3 on (grp.MCHNT_TP_GRP = term3.MCHT_GRP)" + "left outer join (select MCHT_GRP,COUNT(*) as c FROM TBL_TERM_INF t,TBL_MCHT_BASE_INF m where m.ACQ_INST_ID in " + brhBelow + " and t.MCHT_CD = m.MCHT_NO AND t.REC_CRT_TS >= '" + thisYearStart + "' and t.REC_CRT_TS <= '" + thisYearEnd + "' group by MCHT_GRP) term4 on (grp.MCHNT_TP_GRP = term4.MCHT_GRP) " + "order by MCHNT_TP_GRP "; list = commQueryDAO.findBySQLQuery(sql); if (null != list && !list.isEmpty()) { it = list.iterator(); while (it.hasNext()) { Object[] obj = (Object[]) it.next(); if (!StringUtil.isNull(obj[0]) && (obj[0].toString().indexOf("(") != -1 || obj[0].toString().indexOf("") != -1)) { int a = obj[0].toString().indexOf("("); int b = obj[0].toString().indexOf(""); if (a > 0 && b > 0) { data[index][2] = obj[0].toString().substring(0, a < b ? a : b); } else { data[index][2] = obj[0].toString().substring(0, a + b + 1); } } else { data[index][2] = obj[0].toString(); } data[index][3] = "?"; data[index][4] = obj[1].toString(); data[index][5] = obj[2].toString(); data[index][6] = obj[3].toString(); data[index][7] = obj[4].toString(); data[index][8] = String .valueOf(Integer.valueOf(obj[1].toString()) + Integer.valueOf(obj[4].toString())); index++; } } HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet(); HSSFRow row = null; HSSFCell cell = null; sheet.setDefaultColumnWidth(10); sheet.setColumnWidth(1, 3000); sheet.setColumnWidth(2, 6000); //? HSSFCellStyle styleTitle = ExcelUtil.createStyleTitle(workbook); HSSFCellStyle styleBold = ExcelUtil.createStyleBold(workbook); HSSFCellStyle styleCenter = ExcelUtil.createStyleCenter(workbook); HSSFCellStyle styleRight = ExcelUtil.createStyleRight(workbook); HSSFCellStyle styleThin = ExcelUtil.createStyleThinCenter(workbook); short rowIndex = 0;// // row = sheet.createRow(rowIndex++); row.setHeight((short) 800); cell = row.createCell(0); cell.setCellStyle(styleTitle); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellValue("?"); sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 8));// ?? rowIndex++; row = sheet.createRow(rowIndex++); cell = row.createCell(0); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellStyle(styleThin); cell.setCellValue(""); cell = row.createCell(1); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellStyle(styleThin); cell.setCellValue(date.substring(0, 4) + "" + date.substring(4) + ""); cell = row.createCell(3); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellStyle(styleThin); cell.setCellValue(""); cell = row.createCell(4); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellStyle(styleThin); cell.setCellValue(InformationUtil.getBrhName(brhId)); String[] titles = { "??", " ", "", "??", "", "", "", "", "" }; row = sheet.createRow(rowIndex++); for (int i = 0; i < titles.length; i++) { cell = row.createCell(i); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellValue(titles[i]); cell.setCellStyle(styleBold); } for (int i = 0; i < index; i++) { row = sheet.createRow(rowIndex++); for (int j = 0; j < data[i].length; j++) { cell = row.createCell(j); if (j < 4) { cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellStyle(styleCenter); cell.setCellValue(data[i][j]); } else { cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cell.setCellStyle(styleRight); cell.setCellValue(Double.valueOf(data[i][j])); } } } int start = 1; sheet.addMergedRegion(new CellRangeAddress(start + 1, start + 1, 4, 8)); sheet.addMergedRegion(new CellRangeAddress(start + 2, start + 2, 1, 2)); sheet.addMergedRegion(new CellRangeAddress(start + 3, start + 3, 1, 2)); sheet.addMergedRegion(new CellRangeAddress(start + 3, start + 5, 0, 0)); sheet.addMergedRegion(new CellRangeAddress(start + 4, start + 5, 1, 1)); sheet.addMergedRegion(new CellRangeAddress(start + 6, start + 6, 1, 2)); sheet.addMergedRegion(new CellRangeAddress(start + 6, start + 8, 0, 0)); sheet.addMergedRegion(new CellRangeAddress(start + 7, start + 8, 1, 1)); sheet.addMergedRegion(new CellRangeAddress(start + 9, start + 9 + len - 1, 0, 0)); sheet.addMergedRegion(new CellRangeAddress(start + 9, start + 9 + len - 1, 1, 1)); sheet.addMergedRegion(new CellRangeAddress(start + 9 + len, start + 9 + len + len - 1, 0, 0)); sheet.addMergedRegion(new CellRangeAddress(start + 9 + len, start + 9 + len + len - 1, 1, 1)); String path = ExcelUtil.writeFiles(workbook, "RN50202RN_" + brhId + "_" + CommonFunction.getCurrentDateTime() + ".xls"); return returnService(Constants.SUCCESS_CODE_CUSTOMIZE + path); } catch (Exception e) { e.printStackTrace(); return returnService("?,?", e); } }
From source file:com.inet.web.service.spi.download.ExportEmailWriterSpiService.java
License:Open Source License
/** * write group header//from w w w. j av a 2 s .c om * @param sheet * @param group */ private void writeGroupHeader(Sheet sheet, LdapGroup group, CellStyle style) { Row headerRow = sheet.createRow(index); headerRow.setHeightInPoints(12); Cell cell = headerRow.createCell(STT); cell.setCellValue(group.getName() + " - " + group.getDescription()); cell.setCellStyle(style); sheet.addMergedRegion(new CellRangeAddress(index, index++, STT, MOBILE)); }
From source file:com.insoul.ti.controller.ContestProjectEntryController.java
@RequestMapping("/download") public ModelAndView download(HttpServletResponse response) throws Exception { HSSFWorkbook workbook = new HSSFWorkbook();// Excel HSSFSheet sheet = workbook.createSheet();// ExcelSheet sheet.createFreezePane(1, 2);// // // w ww .j a v a 2 s . c o m sheet.setColumnWidth(0, 1000); sheet.setColumnWidth(1, 3500); sheet.setColumnWidth(2, 3500); sheet.setColumnWidth(3, 6500); sheet.setColumnWidth(4, 6500); sheet.setColumnWidth(5, 6500); sheet.setColumnWidth(6, 6500); sheet.setColumnWidth(7, 2500); // Sheet? // HSSFCellStyle sheetStyle = workbook.createCellStyle(); // // sheetStyle.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index); // ? // sheetStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); // ? // sheetStyle.setFillPattern(HSSFCellStyle.FINE_DOTS); // ? // for (int i = 0; i <= 14; i++) { // sheet.setDefaultColumnStyle((short) i, sheetStyle); // } // HSSFFont headfont = workbook.createFont(); headfont.setFontName(""); headfont.setFontHeightInPoints((short) 22);// ? headfont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// // ?? HSSFCellStyle headstyle = workbook.createCellStyle(); headstyle.setFont(headfont); headstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// ? headstyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// headstyle.setLocked(true); headstyle.setWrapText(true);// ? // ?? HSSFFont columnHeadFont = workbook.createFont(); columnHeadFont.setFontName(""); columnHeadFont.setFontHeightInPoints((short) 10); columnHeadFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // ? HSSFCellStyle columnHeadStyle = workbook.createCellStyle(); columnHeadStyle.setFont(columnHeadFont); columnHeadStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// ? columnHeadStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// columnHeadStyle.setLocked(true); columnHeadStyle.setWrapText(true); columnHeadStyle.setLeftBorderColor(HSSFColor.BLACK.index);// columnHeadStyle.setBorderLeft((short) 1);// ? columnHeadStyle.setRightBorderColor(HSSFColor.BLACK.index);// ? columnHeadStyle.setBorderRight((short) 1);// ? columnHeadStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // ? columnHeadStyle.setBottomBorderColor(HSSFColor.BLACK.index); // ? // ???? columnHeadStyle.setFillForegroundColor(HSSFColor.WHITE.index); HSSFFont font = workbook.createFont(); font.setFontName(""); font.setFontHeightInPoints((short) 10); // ?? HSSFCellStyle style = workbook.createCellStyle(); style.setFont(font); style.setAlignment(HSSFCellStyle.ALIGN_LEFT);// ? style.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);// style.setWrapText(true); style.setLeftBorderColor(HSSFColor.BLACK.index); style.setBorderLeft((short) 1); style.setRightBorderColor(HSSFColor.BLACK.index); style.setBorderRight((short) 1); style.setBorderBottom(HSSFCellStyle.BORDER_THIN); // ? style.setBottomBorderColor(HSSFColor.BLACK.index); // ? style.setFillForegroundColor(HSSFColor.WHITE.index);// ? // ?? HSSFCellStyle centerstyle = workbook.createCellStyle(); centerstyle.setFont(font); centerstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// ? centerstyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// centerstyle.setWrapText(true); centerstyle.setLeftBorderColor(HSSFColor.BLACK.index); centerstyle.setBorderLeft((short) 1); centerstyle.setRightBorderColor(HSSFColor.BLACK.index); centerstyle.setBorderRight((short) 1); centerstyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // ? centerstyle.setBottomBorderColor(HSSFColor.BLACK.index); // ? centerstyle.setFillForegroundColor(HSSFColor.WHITE.index);// ? try { // HSSFRow row0 = sheet.createRow(0); // row0.setHeight((short) 900); // HSSFCell cell0 = row0.createCell(0); cell0.setCellValue(new HSSFRichTextString("???")); cell0.setCellStyle(headstyle); /** * ?? ??0 ??0 ??0 ??0 */ CellRangeAddress range = new CellRangeAddress(0, 0, 0, 14); sheet.addMergedRegion(range); // HSSFRow row1 = sheet.createRow(1); row1.setHeight((short) 750); HSSFCell cell = row1.createCell(0); cell.setCellValue(new HSSFRichTextString("??")); cell.setCellStyle(columnHeadStyle); cell = row1.createCell(1); cell.setCellValue(new HSSFRichTextString("")); cell.setCellStyle(columnHeadStyle); cell = row1.createCell(2); cell.setCellValue(new HSSFRichTextString("??")); cell.setCellStyle(columnHeadStyle); cell = row1.createCell(3); cell.setCellValue(new HSSFRichTextString("")); cell.setCellStyle(columnHeadStyle); cell = row1.createCell(4); cell.setCellValue(new HSSFRichTextString("??")); cell.setCellStyle(columnHeadStyle); cell = row1.createCell(5); cell.setCellValue(new HSSFRichTextString("")); cell.setCellStyle(columnHeadStyle); cell = row1.createCell(6); cell.setCellValue(new HSSFRichTextString("?")); cell.setCellStyle(columnHeadStyle); cell = row1.createCell(7); cell.setCellValue(new HSSFRichTextString("")); cell.setCellStyle(columnHeadStyle); cell = row1.createCell(8); cell.setCellValue(new HSSFRichTextString("")); cell.setCellStyle(columnHeadStyle); cell = row1.createCell(9); cell.setCellValue(new HSSFRichTextString("??")); cell.setCellStyle(columnHeadStyle); cell = row1.createCell(10); cell.setCellValue(new HSSFRichTextString("??")); cell.setCellStyle(columnHeadStyle); cell = row1.createCell(11); cell.setCellValue(new HSSFRichTextString("")); cell.setCellStyle(columnHeadStyle); cell = row1.createCell(12); cell.setCellValue(new HSSFRichTextString("??")); cell.setCellStyle(columnHeadStyle); cell = row1.createCell(13); cell.setCellValue(new HSSFRichTextString("?")); cell.setCellStyle(columnHeadStyle); cell = row1.createCell(14); cell.setCellValue(new HSSFRichTextString("??")); cell.setCellStyle(columnHeadStyle); List<ContestEntry> projectList = contestEntryDAO.findAll(); int m = 2; int len = projectList.size(); for (int i = 0; i < len; i++) { ContestEntry c = projectList.get(i); HSSFRow row = sheet.createRow(m + i); cell = row.createCell(0); cell.setCellValue(new HSSFRichTextString(c.getId() + "")); cell.setCellStyle(style); cell = row.createCell(1); cell.setCellValue(new HSSFRichTextString(c.getLocation())); cell.setCellStyle(style); cell = row.createCell(2); cell.setCellValue(new HSSFRichTextString(c.getInstance())); cell.setCellStyle(style); cell = row.createCell(3); cell.setCellValue(new HSSFRichTextString(c.getIndustry())); cell.setCellStyle(style); cell = row.createCell(4); cell.setCellValue(new HSSFRichTextString(c.getLegalFormation())); cell.setCellStyle(style); cell = row.createCell(5); cell.setCellValue(new HSSFRichTextString(c.getRegtime())); cell.setCellStyle(style); cell = row.createCell(6); cell.setCellValue(new HSSFRichTextString(c.getEmployqty() + "")); cell.setCellStyle(style); cell = row.createCell(7); cell.setCellValue(new HSSFRichTextString(c.getLegalPerson())); cell.setCellStyle(style); cell = row.createCell(8); cell.setCellValue(new HSSFRichTextString(c.getUserCategory())); cell.setCellStyle(style); cell = row.createCell(9); cell.setCellValue(new HSSFRichTextString(c.getContact())); cell.setCellStyle(style); cell = row.createCell(10); cell.setCellValue(new HSSFRichTextString(c.getIdNumber())); cell.setCellStyle(style); cell = row.createCell(11); cell.setCellValue(new HSSFRichTextString(c.getBankName())); cell.setCellStyle(style); cell = row.createCell(12); cell.setCellValue(new HSSFRichTextString(c.getBankUserName())); cell.setCellStyle(style); cell = row.createCell(13); cell.setCellValue(new HSSFRichTextString(c.getBankAccount())); cell.setCellStyle(style); cell = row.createCell(14); cell.setCellValue(new HSSFRichTextString(c.getSupportMoney())); cell.setCellStyle(style); } String filename = System.nanoTime() + ".xls";// Excel?? response.setContentType("application/vnd.ms-excel"); response.setHeader("Content-disposition", "attachment;filename=" + filename); OutputStream ouputStream = response.getOutputStream(); workbook.write(ouputStream); ouputStream.flush(); ouputStream.close(); } catch (Exception e) { log.error("download excel Error.", e); } return null; }
From source file:com.jmc.jfxxlsdiff.task.GetWorkSheetContent.java
@Override protected GetWorkSheetContent.Result call() throws Exception { logger.log(Level.INFO, "call()"); updateProgress(-1L, 1L);/*ww w . java 2 s . c o m*/ GetWorkSheetContent.Result res = new GetWorkSheetContent.Result(); res.range = new CellRangeAddress(sheet.getFirstRowNum(), sheet.getLastRowNum(), findFirstColumn(), findLastColumn()); res.colNames = FXCollections.observableArrayList(findColumnNames(res.range.getLastColumn())); res.rows = FXCollections.observableArrayList(findRows()); res.log(); updateProgress(1L, 1L); return res; }
From source file:com.joeyturczak.jtscanner.utils.CreateExcelSpreadsheet.java
License:Apache License
public CreateExcelSpreadsheet(List<List> rowLists) { mDate = Utility.getTodayDateString(); mFileName = Utility.getTodayDateAndTimeString(); mWorkbook = new HSSFWorkbook(); mSheet = mWorkbook.createSheet(mDate); HSSFCellStyle dateRowStyle = mWorkbook.createCellStyle(); HSSFCellStyle headerRowStyle = mWorkbook.createCellStyle(); HSSFCellStyle defaultStyle = mWorkbook.createCellStyle(); HSSFCellStyle defaultAltStyle = mWorkbook.createCellStyle(); HSSFFont headerFont = mWorkbook.createFont(); HSSFFont defaultFont = mWorkbook.createFont(); headerFont.setFontName("Arial"); headerFont.setFontHeightInPoints((short) 14); headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); defaultFont.setFontName("Arial"); defaultFont.setFontHeightInPoints((short) 14); dateRowStyle.setFont(headerFont);// w w w .j a va2s .c o m headerRowStyle.setFont(headerFont); defaultStyle.setFont(defaultFont); defaultAltStyle.setFont(defaultFont); dateRowStyle.setFillForegroundColor(HSSFColor.GREY_40_PERCENT.index); dateRowStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); dateRowStyle.setAlignment(CellStyle.ALIGN_CENTER); headerRowStyle.setFillForegroundColor(HSSFColor.GREY_40_PERCENT.index); headerRowStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); headerRowStyle.setBorderLeft(HSSFBorderFormatting.BORDER_THIN); headerRowStyle.setBorderRight(HSSFBorderFormatting.BORDER_THIN); headerRowStyle.setBorderBottom(HSSFBorderFormatting.BORDER_THIN); headerRowStyle.setBorderTop(HSSFBorderFormatting.BORDER_THIN); headerRowStyle.setAlignment(CellStyle.ALIGN_CENTER); defaultStyle.setBorderLeft(HSSFBorderFormatting.BORDER_THIN); defaultStyle.setBorderRight(HSSFBorderFormatting.BORDER_THIN); defaultStyle.setBorderBottom(HSSFBorderFormatting.BORDER_THIN); defaultStyle.setBorderTop(HSSFBorderFormatting.BORDER_THIN); defaultStyle.setAlignment(CellStyle.ALIGN_CENTER); defaultAltStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); defaultAltStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); defaultAltStyle.setBorderLeft(HSSFBorderFormatting.BORDER_THIN); defaultAltStyle.setBorderRight(HSSFBorderFormatting.BORDER_THIN); defaultAltStyle.setBorderBottom(HSSFBorderFormatting.BORDER_THIN); defaultAltStyle.setBorderTop(HSSFBorderFormatting.BORDER_THIN); defaultAltStyle.setAlignment(CellStyle.ALIGN_CENTER); try { Row dateRow = mSheet.createRow(0); dateRow.setHeight((short) 500); Cell dateCell = dateRow.createCell(0); dateCell.setCellValue(mDate); dateCell.setCellStyle(dateRowStyle); mSheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 3)); for (int colIndex = 0; colIndex < rowLists.size(); colIndex++) { Row row = mSheet.createRow(mRowNum); mSheet.setColumnWidth(0, 1200); List<String> nextRowList = rowLists.get(colIndex); for (int rowIndex = 0; rowIndex < nextRowList.size(); rowIndex++) { Cell cell = row.createCell(rowIndex); cell.setCellValue(nextRowList.get(rowIndex)); row.setHeight((short) 400); cell.setCellStyle(defaultStyle); if (colIndex % 2 == 0) { cell.setCellStyle(defaultAltStyle); } if (colIndex == 0) { row.setHeight((short) 500); cell.setCellStyle(headerRowStyle); } if (rowIndex > 0) { mSheet.setColumnWidth(rowIndex, 4400); } } mRowNum++; } } catch (Exception e) { e.printStackTrace(); } }
From source file:com.kcs.action.ReportCompareUtilizeFwdAction.java
public String export() throws Exception { logger.debug("= = = = = EXPORT = = = = ="); logger.debug("PALM : processDate >>> " + DateUtil.convertDateFromJsp(processDate) + " reportType >>> " + reportType);//from w ww . j a v a 2 s. c o m list = reportCompareUtilizeFWDService.findWithCriteriaService(reportType, DateUtil.convertDateFromJsp(processDate)); fileType = "XLS"; HSSFWorkbook myWorkBook = new HSSFWorkbook(); HSSFSheet mySheet = myWorkBook.createSheet("Utilize_FWD"); if ("RPT_TYP1_41".equals(reportType)) { fileName = "Report_FWD_not_found_EES_(Trade_Finance)_CBS_(Remittance)_" + hidDate + ".xls"; Row rowHeader1 = mySheet.createRow(0); rowHeader1.createCell(0).setCellValue( "? FWD ? EES (Trade Finance) & CBS (Remittance)"); } else if ("RPT_TYP2_41".equals(reportType)) { fileName = "Report_EES_(Trade_Finance)_CBS_(Remittance)_not_found_FWD_" + hidDate + ".xls"; Row rowHeader1 = mySheet.createRow(0); rowHeader1.createCell(0).setCellValue( "? EES (Trade Finance) & CBS (Remittance)? FWD"); } // Cell cell1 = CellUtil.createCell(rowHeader1, 0,"? FWD ? EES (Trade Finance) & CBS (Remittance)"); // CellUtil.setAlignment(cell1, myWorkBook, CellStyle.ALIGN_CENTER); Row rowHeader2 = mySheet.createRow(1); rowHeader2.createCell(0).setCellValue(processDate); // Cell cell2 = CellUtil.createCell(rowHeader2, 1,processDate); // CellUtil.setAlignment(cell2, myWorkBook, CellStyle.ALIGN_CENTER); mySheet.addMergedRegion(new CellRangeAddress(0, //first row (0-based) 0, //last row (0-based) 0, //first column (0-based) 2 //last column (0-based) )); mySheet.addMergedRegion(new CellRangeAddress(1, //first row (0-based) 1, //last row (0-based) 0, //first column (0-based) 2 //last column (0-based) )); if (null != list && list.size() > 0) { int rownum = 2; Row row = mySheet.createRow(rownum++); int cellnum = 0; row.createCell(cellnum++).setCellValue("Curr"); row.createCell(cellnum++).setCellValue("Fi Arrg No"); row.createCell(cellnum++).setCellValue("Amount"); for (ReportCompareUtilizeFwd obj : list) { Row rowData = mySheet.createRow(rownum++); cellnum = 0; rowData.createCell(cellnum++).setCellValue(obj.getCURR() + ""); rowData.createCell(cellnum++).setCellValue(obj.getFI_ARRG_NO() + ""); rowData.createCell(cellnum++).setCellValue(obj.getAMT() + ""); } for (int j = 0; j < cellnum; j++) { mySheet.autoSizeColumn(j); } } ByteArrayOutputStream boas = new ByteArrayOutputStream(); myWorkBook.write(boas); setExcelStream(new ByteArrayInputStream(boas.toByteArray())); logger.debug("exportExcel : end..."); return "excel"; // return REPORT; }
From source file:com.khartec.waltz.web.endpoints.extracts.BaseDataExtractor.java
License:Open Source License
private Object writeAsExcel(String suggestedFilenameStem, Select<?> qry, Response response) throws IOException { XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet sheet = workbook.createSheet(sanitizeSheetName(suggestedFilenameStem)); writeExcelHeader(qry, sheet);//from ww w. ja v a 2s . c o m writeExcelBody(qry, sheet); sheet.setAutoFilter(new CellRangeAddress(0, 0, 0, qry.fields().length)); sheet.createFreezePane(0, 1); byte[] bytes = convertExcelToByteArray(workbook); HttpServletResponse httpResponse = response.raw(); httpResponse.setHeader("Content-Type", "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); httpResponse.setHeader("Content-Disposition", "attachment; filename=" + suggestedFilenameStem + ".xlsx"); httpResponse.setHeader("Content-Transfer-Encoding", "7bit"); httpResponse.setContentLength(bytes.length); httpResponse.getOutputStream().write(bytes); httpResponse.getOutputStream().flush(); httpResponse.getOutputStream().close(); return httpResponse; }
From source file:com.kybelksties.excel.ExcelSheetTableModel.java
License:Open Source License
/** * Insert a row at a given index./* w ww . ja v a2 s . c o m*/ * * @param createAtIndex row-number of the cell at which to create a new row * @param sourceRow the row to insert */ public void insertRowAt(int createAtIndex, Row sourceRow) { Row newRow = getRow(createAtIndex); if (newRow != null) { // shift all rows >= createAtIndex up by one getSheet().shiftRows(createAtIndex, getSheet().getLastRowNum(), 1); } else { newRow = getSheet().createRow(createAtIndex); } // Loop through source columns to add to new row for (int i = 0; i < sourceRow.getLastCellNum(); i++) { // Grab a copy of the old/new cell Cell oldCell = sourceRow.getCell(i); Cell newCell = newRow.createCell(i); // If the old cell is null jump to next cell if (oldCell == null) { continue; } // Copy style from old cell and apply to new cell CellStyle newCellStyle = workbook.createCellStyle(); newCellStyle.cloneStyleFrom(oldCell.getCellStyle()); newCell.setCellStyle(newCellStyle); // If there is a cell comment, copy if (oldCell.getCellComment() != null) { newCell.setCellComment(oldCell.getCellComment()); } // If there is a cell hyperlink, copy if (oldCell.getHyperlink() != null) { newCell.setHyperlink(oldCell.getHyperlink()); } // Set the cell data type newCell.setCellType(oldCell.getCellType()); // Set the cell data value switch (oldCell.getCellType()) { case Cell.CELL_TYPE_BLANK: newCell.setCellValue(oldCell.getStringCellValue()); break; case Cell.CELL_TYPE_BOOLEAN: newCell.setCellValue(oldCell.getBooleanCellValue()); break; case Cell.CELL_TYPE_ERROR: newCell.setCellErrorValue(oldCell.getErrorCellValue()); break; case Cell.CELL_TYPE_FORMULA: newCell.setCellFormula(oldCell.getCellFormula()); break; case Cell.CELL_TYPE_NUMERIC: newCell.setCellValue(oldCell.getNumericCellValue()); break; case Cell.CELL_TYPE_STRING: newCell.setCellValue(oldCell.getRichStringCellValue()); break; } } // If there are are any merged regions in the source row, copy to new row for (int i = 0; i < getSheet().getNumMergedRegions(); i++) { CellRangeAddress cellRangeAddress = getSheet().getMergedRegion(i); if (cellRangeAddress.getFirstRow() == sourceRow.getRowNum()) { CellRangeAddress newCellRangeAddress = new CellRangeAddress(newRow.getRowNum(), (newRow.getRowNum() + (cellRangeAddress.getLastRow() - cellRangeAddress.getFirstRow())), cellRangeAddress.getFirstColumn(), cellRangeAddress.getLastColumn()); getSheet().addMergedRegion(newCellRangeAddress); } } }