List of usage examples for org.apache.poi.xssf.usermodel XSSFCell setCellStyle
@Override public void setCellStyle(CellStyle style)
Set the style for the cell.
From source file:com.appdynamics.jrbronet.projectplan.ExcelManager.java
private static void copyRow(XSSFWorkbook workbook, XSSFSheet sourceWorksheet, int sourceRowNum, XSSFSheet destinationWorksheet, int destinationRowNum) { // Get the source / new row XSSFRow newRow = destinationWorksheet.getRow(destinationRowNum); XSSFRow sourceRow = sourceWorksheet.getRow(sourceRowNum); // If the row exist in destination, push down all rows by 1 else create a new row if (newRow != null) { destinationWorksheet.shiftRows(destinationRowNum, destinationWorksheet.getLastRowNum(), 1); } else {/*from w w w . ja va 2 s . c om*/ newRow = destinationWorksheet.createRow(destinationRowNum); } // 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 XSSFCell oldCell = sourceRow.getCell(i); XSSFCell newCell = newRow.createCell(i); // If the old cell is null jump to next cell if (oldCell == null) { newCell = null; continue; } // Copy style from old cell and apply to new cell XSSFCellStyle 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()); //newCell.setCellValue(oldCell.getRawValue()); //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 < worksheet.getNumMergedRegions(); i++) { CellRangeAddress cellRangeAddress = worksheet.getMergedRegion(i); if (cellRangeAddress.getFirstRow() == sourceRow.getRowNum()) { CellRangeAddress newCellRangeAddress = new CellRangeAddress(newRow.getRowNum(), (newRow.getRowNum() + (cellRangeAddress.getLastRow() - cellRangeAddress.getFirstRow() )), cellRangeAddress.getFirstColumn(), cellRangeAddress.getLastColumn()); worksheet.addMergedRegion(newCellRangeAddress); } } */ }
From source file:com.automaster.autoview.server.servlet.ExcelServlet.java
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { /*response.setContentType("application/vnd.ms-excel"); response.setHeader("Content-Disposition", "attachment; filename=filename.xls"); HSSFWorkbook workbook = new HSSFWorkbook(); // ...//ww w. java2 s . c om // Now populate workbook the usual way. // ... OutputStream arqSaida = response.getOutputStream(); workbook.write(arqSaida); // Write workbook to response. arqSaida.flush(); arqSaida.close();*/ //getServletContext().getRealPath("/") String tempoDecorrido = " 0"; String url = getServletContext().getRealPath("/"); String placa = request.getParameter("placa"); //TimeZone timeZoneMundial = TimeZone.getTimeZone(ZoneId.ofOffset("UTC", ZoneOffset.UTC)); Timestamp dataInicio = new Timestamp(Long.parseLong(request.getParameter("dataInicio"))); Timestamp dataFim = new Timestamp(Long.parseLong(request.getParameter("dataFim"))); //String timeZoneInterface = request.getParameter("timeZone"); /*String timeZone = "Z"; if(timeZoneInterface.equalsIgnoreCase("0")){ timeZone = "Z"; } else { timeZone = String.valueOf((-1) * (Integer.parseInt(timeZoneInterface) / 60)); } */ TimeZone timeZonePadrao = TimeZone.getTimeZone(ZoneId.of("-3")); System.out.println("Time zone Cliente : " + timeZonePadrao); //System.out.println("timeZoneInterface: "+timeZoneInterface); //System.out.println("timeZone: "+timeZone); //String ign = request.getParameter("ign"); ZzzPosPlacaVeiculoDAO zzzPosPlacaVeiculoDAO = new ZzzPosPlacaVeiculoDAO(); ArrayList<TreeMap<String, String>> posicoes = zzzPosPlacaVeiculoDAO.buscarPosicoesPorIntervaloData(placa, dataInicio, dataFim); response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); response.setHeader("Content-Disposition", "attachment; filename=Historico-" + placa + ".xlsx"); Workbook wb = new XSSFWorkbook(); Sheet sheet = wb.createSheet("Histrico - " + placa); int pictureIdx; try ( //add picture data to this workbook. InputStream is = new FileInputStream(url + "/imagens/logo.jpg")) { byte[] bytes = IOUtils.toByteArray(is); pictureIdx = wb.addPicture(bytes, Workbook.PICTURE_TYPE_JPEG); } CreationHelper helper = wb.getCreationHelper(); // Create the drawing patriarch. This is the top level container for all shapes. Drawing drawing = sheet.createDrawingPatriarch(); //add a picture shape ClientAnchor anchor = helper.createClientAnchor(); //set top-left corner of the picture, //subsequent call of Picture#resize() will operate relative to it anchor.setCol1(1); anchor.setRow1(0); Picture pict = drawing.createPicture(anchor, pictureIdx); //auto-size picture relative to its top-left corner pict.resize(3, 3); //pict.resize(); //sheet.setColumnWidth(0, 200); Font fonte = wb.createFont(); fonte.setFontHeightInPoints((short) 24); fonte.setFontName("Arial"); fonte.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); fonte.setItalic(true); CellStyle estiloTitulo = wb.createCellStyle(); estiloTitulo.setAlignment(HSSFCellStyle.ALIGN_CENTER); estiloTitulo.setFont(fonte); Font fonteCabecalho = wb.createFont(); fonteCabecalho.setFontHeightInPoints((short) 14); fonteCabecalho.setFontName("Arial"); fonteCabecalho.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); //fonteCabecalho.setItalic(true); CellStyle estiloCabecalho = wb.createCellStyle(); estiloCabecalho.setAlignment(HSSFCellStyle.ALIGN_CENTER); estiloCabecalho.setFont(fonteCabecalho); Font fonteTituloTabela = wb.createFont(); //fonteTituloTabela.setFontHeightInPoints((short) 14); fonteTituloTabela.setFontName("Arial"); fonteTituloTabela.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); CellStyle estilo = wb.createCellStyle(); estilo.setAlignment(HSSFCellStyle.ALIGN_CENTER); estilo.setFont(fonteTituloTabela); CellStyle estiloCorpo = wb.createCellStyle(); estiloCorpo.setAlignment(HSSFCellStyle.ALIGN_CENTER); //estiloCorpo.setFillBackgroundColor(HSSFColor.WHITE.index); //estiloCorpo.setFont(fonteTituloTabela); XSSFRow linha6 = (XSSFRow) sheet.createRow(6); XSSFCell cell046 = linha6.createCell(3); cell046.setCellValue("Relatrio de Posies"); cell046.setCellStyle(estiloTitulo); //sheet.addMergedRegion(new CellRangeAddress(6, 6, 0, 3)); XSSFRow linha7 = (XSSFRow) sheet.createRow(7); XSSFCell cell047 = linha7.createCell(3); cell047.setCellValue("Veculo : " + placa); cell047.setCellStyle(estiloCabecalho); //sheet.addMergedRegion(new CellRangeAddress(7, 7, 0, 3)); XSSFRow linha8 = (XSSFRow) sheet.createRow(8); XSSFCell cell038 = linha8.createCell(3); //TimeZone.setDefault(timeZoneMundial); //Date dataHoraInicio0 = new Date(Long.parseLong(request.getParameter("dataInicio"))); //TimeZone.setDefault(timeZoneCliente); //Date dataHoraInicio = new Date(dataHoraInicio0.getTime()); SimpleDateFormat dataFormatadaCabecalho = new SimpleDateFormat("dd/MM/yyyy HH:mm:ss"); dataFormatadaCabecalho.setTimeZone(timeZonePadrao); cell038.setCellValue("Perodo: De: " + dataFormatadaCabecalho.format(dataInicio) + " at: " + dataFormatadaCabecalho.format(dataFim)); cell038.setCellStyle(estiloCabecalho); //sheet.addMergedRegion(new CellRangeAddress(8, 8, 0, 3)); sheet.setColumnWidth(0, 5000); sheet.setColumnWidth(1, 3000); sheet.setColumnWidth(2, 3500); //sheet.setColumnWidth(3, 4000); sheet.setColumnWidth(3, 30000); //sheet.setColumnWidth(4, 4000); //sheet.setColumnWidth(4, 30000); sheet.setColumnWidth(5, 3000); sheet.setColumnWidth(6, 3000); sheet.setColumnWidth(7, 3000); sheet.setColumnWidth(8, 3000); sheet.setColumnWidth(9, 3000); //sheet.setColumnWidth(10, 20000); sheet.setColumnWidth(11, 3000); XSSFRow linha9 = (XSSFRow) sheet.createRow(10); XSSFCell cell0 = linha9.createCell(0); cell0.setCellValue("Data e hora"); cell0.setCellStyle(estilo); XSSFCell cell1 = linha9.createCell(1); cell1.setCellValue("Velocidade"); cell1.setCellStyle(estilo); XSSFCell cell2 = linha9.createCell(2); cell2.setCellValue("Ignio"); cell2.setCellStyle(estilo); //XSSFCell cell3 = linha9.createCell(3); //cell3.setCellValue("Latitude"); //cell3.setCellStyle(estilo); //XSSFCell cell4 = linha9.createCell(4); //cell4.setCellValue("Longitude"); //cell4.setCellStyle(estilo); //XSSFCell cell5 = linha9.createCell(5); //cell5.setCellValue("Satlite"); //cell5.setCellStyle(estilo); //XSSFCell cell6 = linha9.createCell(6); //cell6.setCellValue("GPS"); //cell6.setCellStyle(estilo); //XSSFCell cell7 = linha9.createCell(7); //cell7.setCellValue("Entrada"); //cell7.setCellStyle(estilo); //XSSFCell cell8 = linha9.createCell(8); //cell8.setCellValue("Sada"); //cell8.setCellStyle(estilo); //XSSFCell cell9 = linha9.createCell(9); //cell9.setCellValue("Evento"); //cell9.setCellStyle(estilo); XSSFCell cell10 = linha9.createCell(3); cell10.setCellValue("Endereo"); cell10.setCellStyle(estilo); //sheet.addMergedRegion(new CellRangeAddress(11, 11, 4, 8)); //XSSFCell cell11 = linha9.createCell(11); //cell11.setCellValue("Direo"); //cell11.setCellStyle(estilo); int linha = 0; int j = 11; double latAnt = 0; double lonAnt = 0; double latAtual = 0; double lonAtual = 0; double distancia = 0; double distanciaTotal = 0; for (int i = 0; i < posicoes.size(); i++) { XSSFRow row = (XSSFRow) sheet.createRow(j); if (i == 0) { distancia = 0; //System.out.println("linha 00 - EXCEL"); } else { //System.out.println("linha 01 - EXCEL"); latAnt = Double.parseDouble(posicoes.get(i - 1).get("lat")); lonAnt = Double.parseDouble(posicoes.get(i - 1).get("lon")); latAtual = Double.parseDouble(posicoes.get(i).get("lat")); lonAtual = Double.parseDouble(posicoes.get(i).get("lon")); //System.out.println("linha 02 - PDF"); if (latAnt == latAtual && lonAnt == lonAtual) { distancia = 0; } else { distancia = caculaDistanciaEntreDoisPontos(latAnt, lonAnt, latAtual, lonAtual); //System.out.println("linha 03 - PDF"); } } distanciaTotal = distanciaTotal + distancia; /*if(i==0) { latAnt = Double.parseDouble(posicoes.get(i).get("lat")); lonAnt = Double.parseDouble(posicoes.get(i).get("lon")); } else{ latAnt = Double.parseDouble(posicoes.get(i-1).get("lat")); lonAnt = Double.parseDouble(posicoes.get(i-1).get("lon")); } double latAtual = Double.parseDouble(posicoes.get(i).get("lat")); double lonAtual = Double.parseDouble(posicoes.get(i).get("lon")); double distancia = caculaDistanciaEntreDoisPontos(latAnt, lonAnt, latAtual, lonAtual); distanciaTotal = distanciaTotal + distancia;*/ for (int col = 0; col < posicoes.get(linha).size(); col++) { XSSFCell cell = row.createCell(col); cell.setCellStyle(estiloCorpo); switch (col) { case 0: Date dataHora0 = new Date(Long.parseLong(posicoes.get(i).get("dataHora"))); Date dataHora = new Date(dataHora0.getTime()); SimpleDateFormat dataFormatada = new SimpleDateFormat("dd/MM/yyyy HH:mm:ss"); dataFormatada.setTimeZone(timeZonePadrao); cell.setCellValue(dataFormatada.format(dataHora)); break; case 1: cell.setCellValue(posicoes.get(linha).get("vel")); break; case 2: cell.setCellValue( posicoes.get(linha).get("ign").equalsIgnoreCase("True") ? "Ligada" : "Desligada"); break; /*case 3: cell.setCellValue(posicoes.get(linha).get("lat")); break; case 4: cell.setCellValue(posicoes.get(linha).get("lon")); break; case 5: cell.setCellValue(posicoes.get(linha).get("sat")); break; case 6: cell.setCellValue(posicoes.get(linha).get("gps")); break; case 7: cell.setCellValue(posicoes.get(linha).get("entrada")); break; case 8: cell.setCellValue(posicoes.get(linha).get("saida")); break; case 9: cell.setCellValue(posicoes.get(linha).get("evento")); break;*/ case 3: cell.setCellValue(posicoes.get(linha).get("endereco") == null ? "Sem endereo" : posicoes.get(linha).get("endereco")); break; /*case 11: cell.setCellValue(posicoes.get(linha).get("direcao")); break;*/ } } j = j + 1; linha = linha + 1; } tempoDecorrido = calculaDatas(Long.parseLong(posicoes.get(0).get("dataHora")), Long.parseLong(posicoes.get(posicoes.size() - 1).get("dataHora"))); int index = 0; String kms = "0"; String m = ""; double metros = 0; if (distanciaTotal > 0) { BigDecimal decimalFormatado = new BigDecimal(distanciaTotal).setScale(2, RoundingMode.HALF_EVEN); index = String.valueOf(decimalFormatado).indexOf("."); kms = String.valueOf(decimalFormatado).substring(0, index); m = "0" + (String.valueOf(decimalFormatado).substring(index)); metros = Double.parseDouble(m) * 1000; } //String formatted = NumberFormat.getFormat("000.00").format(metros); //System.out.println("Percorridos aproximadamente : "+kms+" KM e "+metros+" metros"); XSSFRow linhaX = (XSSFRow) sheet.createRow(linha + 12); XSSFCell cellX = linhaX.createCell(0); sheet.addMergedRegion(new CellRangeAddress(linha + 12, linha + 12, 0, 3)); cellX.setCellStyle(estilo); cellX.setCellValue( "Percorridos: " + kms + " KM e " + String.valueOf(metros) + " metros. Tempo: " + tempoDecorrido); ByteArrayOutputStream outByteStream = new ByteArrayOutputStream(); wb.write(outByteStream); byte[] outArray = outByteStream.toByteArray(); OutputStream outStream = response.getOutputStream(); outStream.write(outArray); outStream.flush(); }
From source file:com.axelor.apps.admin.service.ViewDocExportService.java
License:Open Source License
private int writeCell(XSSFRow row, String[] values, int count, XSSFCellStyle cellStyle) { int cellCount = 0; for (String value : values) { if (onlyPanel) { cellCount++;// ww w.ja v a2 s. co m if (cellCount == 4) { continue; } if (cellCount > 7) { break; } } XSSFCell cell = row.createCell(count); cell.setCellStyle(cellStyle); cell.setCellValue(value); count++; } return count; }
From source file:com.cms.utils.DataUtil.java
License:Open Source License
/** * * @param file/*from www . j a v a 2s . co m*/ * @param iSheet * @param iBeginRow * @param iFromCol * @param iToCol * @param rowBack * @param lstValidateCells * @return */ public static List isValidExcells(String mimeType, File file, int iSheet, int iBeginRow, int iFromCol, int iToCol, int rowBack, List<ValidateCells> lstValidateCells) { String fileName = file.getName(); fileName = removeDotInteger(fileName); boolean isCopySheet = true; File fileError = null; Map<String, String> mapsNameError = new HashMap<>(); List lst = null; try { if (mimeType.equalsIgnoreCase(Constants.FORMATFILE.EXCEL_XLSX)) { // xlsx lst = ExcelReaderXLSX.importExcel(file, iSheet, iBeginRow, iFromCol, iToCol, rowBack); } else if (mimeType.equalsIgnoreCase(Constants.FORMATFILE.EXCEL_XLS)) { // xls lst = ExcelReader.importExcel(file, iSheet, iBeginRow, iFromCol, iToCol, rowBack); } //If lst null return if (lst == null) { return lst; } String error = ""; ValidateCells validateCells = null; int index = 0; if (iBeginRow == 0) { index = 1; } else { index = 0; } int rowErr = 0; Object[] temp; List<String> lstReturn = Lists.newArrayList(); for (int i = index, size = lst.size(); i < size; i++) { temp = (Object[]) lst.get(i); if (checkObjectNull(temp)) { // lst.remove(temp); // i--; // continue; if (i == 0) { lst = Lists.newArrayList(); } break; } error = ""; for (int j = 0; j < lstValidateCells.size(); j++) { validateCells = lstValidateCells.get(j); if (validateCells.getPattern() != null) { error += DataUtil.validCell(i, j, (String) temp[j], validateCells.getType(), validateCells.isIsNotNull(), validateCells.getPattern()); } else { lstReturn = DataUtil.validCell(i, j, (String) temp[j], validateCells.getType(), validateCells.isIsNotNull(), validateCells.getLength()); error += lstReturn.get(0); temp[j] = lstReturn.get(1); } } if (!isStringNullOrEmpty(error)) { rowErr = i + iBeginRow; mapsNameError.put(rowErr + "", error); } } if (!mapsNameError.isEmpty()) { // FileInputStream flieInput = new FileInputStream(file); XSSFWorkbook workbookIp = null; String fileCreate = fileName + "_Error.xlsx"; FileOutputStream fileOut = new FileOutputStream(fileCreate); XSSFWorkbook workbookEp = new XSSFWorkbook(); XSSFSheet worksheetEp = workbookEp.createSheet("Thong_Tin_Loi"); XSSFCellStyle cellStyle = null; // if (isCopySheet) { if (mimeType.equalsIgnoreCase(Constants.FORMATFILE.EXCEL_XLSX)) { // xlsx workbookIp = new XSSFWorkbook(flieInput); } else if (mimeType.equalsIgnoreCase(Constants.FORMATFILE.EXCEL_XLS)) { // xls HSSFWorkbook hSSFWorkbook = new HSSFWorkbook(flieInput); workbookIp = ExcelReaderXLSX.convertWorkbookHSSFToXSSF(hSSFWorkbook); } XSSFSheet worksheetIp = workbookIp.getSheetAt(iSheet); ExcelReaderXLSX.copySheets(worksheetEp, worksheetIp, iToCol); // cellStyle = CommonUtils.styleCell(workbookEp); isCopySheet = false; } for (Map.Entry<String, String> entrySet : mapsNameError.entrySet()) { String key = entrySet.getKey(); String value = entrySet.getValue(); int row = Integer.valueOf(key); XSSFRow row5 = worksheetEp.getRow(row); if (row5 != null) { XSSFCell cellB1 = row5.createCell(iToCol + 1); cellB1.setCellValue(value); cellB1.setCellStyle(cellStyle); } } workbookEp.write(fileOut); fileOut.flush(); fileOut.close(); fileError = new File(fileCreate); Resource resource = new FileResource(fileError); Page.getCurrent().open(resource, null, false); lst = null; fileError.deleteOnExit(); } } catch (Exception e) { e.printStackTrace(); ; lst = null; } return lst; }
From source file:com.envision.envservice.report.template.PraiseReportExcel.java
License:Open Source License
private static XSSFCell[] createCells(XSSFRow row, int sum, XSSFCellStyle style) { XSSFCell[] cells = new XSSFCell[sum]; for (int i = 0; i < sum; i++) { XSSFCell cell = row.createCell(i); cells[i] = cell;// w w w . java 2s . co m if (style != null) { cell.setCellStyle(style); } } return cells; }
From source file:com.envision.envservice.report.template.PraiseReportExcel.java
License:Open Source License
private static void setStyle(XSSFCellStyle style, XSSFCell... cells) { for (XSSFCell cell : cells) { cell.setCellStyle(style); }/*from w w w. j a v a 2s . c om*/ }
From source file:com.frameworkset.platform.sanylog.common.POIExcelUtil2007.java
License:Open Source License
/** * Excel Workbook?./*from w w w . j a v a 2 s . c om*/ * * @param colDesc 1717?:user_id,??:user_name,:type_name" * @param dataList * @return * @author gw_liaozh * @throws InvocationTargetException * @throws IllegalAccessException * @throws IllegalArgumentException */ public static XSSFWorkbook createHSSFWorkbook(String colDesc, List<?> dataList) throws IllegalArgumentException, IllegalAccessException, InvocationTargetException { //???1717 //TODO: ? List<String> colTitleList = getColumnTitleList(colDesc); List<String> colFieldList = getColumnFieldList(colDesc); XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet sheet = wb.createSheet(); XSSFFont font = getBaseFont(wb); XSSFCellStyle headCellStyle = getHeadCellStyle(wb, font); //? CellStyle dateCellStyle = getDateTimeCellStyle(wb); //CellStyle strCellStyle = getStringCellStyle(wb); //??1717 XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet); Map<String, Class<?>> fieldTypeMap = new HashMap<String, Class<?>>(); // XSSFRow titleRow = sheet.createRow(0); for (int i = 0; i < colTitleList.size(); i++) { XSSFCell cell = titleRow.createCell(i); cell.setCellStyle(headCellStyle); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellValue(colTitleList.get(i)); } //?1717 for (int i = 0; i < dataList.size(); i++) { Object obj = dataList.get(i); XSSFRow row = sheet.createRow(i + 1); for (int j = 0; j < colFieldList.size(); j++) { String fieldName = colFieldList.get(j); XSSFCell cell = row.createCell(j); if (obj == null) { continue; } Object value = BeanConvertUtil.getProperty(obj, fieldName); //ClassInfo classInfo = ClassUtil.getClassInfo(obj.getClass()); //Object value = classInfo.getPropertyDescriptor(fieldName).getValue(obj); if (value == null) { continue; } //?? if (value instanceof Number) { cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(((Number) value).doubleValue()); } else if (value instanceof Date) { cell.setCellStyle(dateCellStyle); cell.setCellValue((Date) value); } else { cell.setCellType(HSSFCell.CELL_TYPE_STRING); //cell.setCellStyle(strCellStyle); cell.setCellValue(value.toString()); } fieldTypeMap.put(fieldName, value.getClass()); } } //?? for (int i = 0; i < colFieldList.size(); i++) { String fieldName = colFieldList.get(i); Class<?> fieldClass = fieldTypeMap.get(fieldName); if (fieldClass == null) { continue; } CellRangeAddressList range = new CellRangeAddressList(1, 65535, i, i); DataValidationConstraint constraint = null; if (Integer.class.isAssignableFrom(fieldClass)) { constraint = dvHelper.createIntegerConstraint(DataValidationConstraint.OperatorType.NOT_BETWEEN, "0", "-1"); sheet.addValidationData(dvHelper.createValidation(constraint, range)); } else if (Number.class.isAssignableFrom(fieldClass)) { constraint = dvHelper.createNumericConstraint(DataValidationConstraint.ValidationType.DECIMAL, DataValidationConstraint.OperatorType.NOT_BETWEEN, "0", "-1"); sheet.addValidationData(dvHelper.createValidation(constraint, range)); } else if (Date.class.isAssignableFrom(fieldClass)) { constraint = dvHelper.createDateConstraint(DataValidationConstraint.OperatorType.NOT_BETWEEN, "0000-01-02", "0000-01-01", "yyyy-MM-dd"); sheet.addValidationData(dvHelper.createValidation(constraint, range)); } } // for (int i = 0; i < colTitleList.size(); i++) { //?? //sheet.autoSizeColumn(i); } return wb; }
From source file:com.frameworkset.platform.util.POIExcelUtil.java
License:Open Source License
/** * Excel Workbook?./*from w w w .j a v a2 s . co m*/ * * @param colDesc * "?:user_id,??:user_name,:type_name" * @param dataList * @return * @author gw_liaozh * @throws InvocationTargetException * @throws IllegalAccessException * @throws IllegalArgumentException */ public static XSSFWorkbook createHSSFWorkbook(List<String> titlesList, List<?> dataList) throws IllegalArgumentException, IllegalAccessException, InvocationTargetException { // ??? List<String> colFieldList = getColumnFieldList(titlesList); XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet sheet = wb.createSheet(); XSSFFont font = getBaseFont(wb); XSSFCellStyle headCellStyle = getHeadCellStyle(wb, font); // ? CellStyle dateCellStyle = getDateTimeCellStyle(wb); // ??1717 XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet); Map<String, Class<?>> fieldTypeMap = new HashMap<String, Class<?>>(); SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd"); // XSSFRow titleRow = sheet.createRow(0); for (int i = 0; i < titlesList.size(); i++) { XSSFCell cell = titleRow.createCell(i); cell.setCellStyle(headCellStyle); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellValue(titlesList.get(i)); } ClassInfo classInfo = null; // ? for (int i = 0; i < dataList.size(); i++) { Object obj = dataList.get(i); if (classInfo == null) classInfo = ClassUtil.getClassInfo(obj.getClass()); XSSFRow row = sheet.createRow(i + 1); for (int j = 0; j < colFieldList.size(); j++) { String fieldName = colFieldList.get(j); XSSFCell cell = row.createCell(j); if (obj == null) { continue; } PropertieDescription reflexField = classInfo.getPropertyDescriptor(fieldName); Object value = reflexField.getValue(obj); // ClassInfo classInfo = ClassUtil.getClassInfo(obj.getClass()); // Object value = classInfo.getPropertyDescriptor(fieldName).getValue(obj); if (value == null) { continue; } // ?? if (value instanceof Number) { cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(((Number) value).doubleValue()); } else if (value instanceof Date || value instanceof Timestamp) { cell.setCellStyle(dateCellStyle); cell.setCellValue(sdf.format((Date) value)); } else { cell.setCellType(HSSFCell.CELL_TYPE_STRING); // cell.setCellStyle(strCellStyle); cell.setCellValue(value.toString()); } fieldTypeMap.put(fieldName, value.getClass()); } } // ?? for (int i = 0; i < colFieldList.size(); i++) { String fieldName = colFieldList.get(i); Class<?> fieldClass = fieldTypeMap.get(fieldName); if (fieldClass == null) { continue; } CellRangeAddressList range = new CellRangeAddressList(1, 65535, i, i); DataValidationConstraint constraint = null; if (Integer.class.isAssignableFrom(fieldClass)) { constraint = dvHelper.createIntegerConstraint(DataValidationConstraint.OperatorType.NOT_BETWEEN, "0", "-1"); sheet.addValidationData(dvHelper.createValidation(constraint, range)); } else if (Number.class.isAssignableFrom(fieldClass)) { constraint = dvHelper.createNumericConstraint(DataValidationConstraint.ValidationType.DECIMAL, DataValidationConstraint.OperatorType.NOT_BETWEEN, "0", "-1"); sheet.addValidationData(dvHelper.createValidation(constraint, range)); } else if (Date.class.isAssignableFrom(fieldClass)) { constraint = dvHelper.createDateConstraint(DataValidationConstraint.OperatorType.NOT_BETWEEN, "0000-01-02", "0000-01-01", "yyyy-MM-dd"); sheet.addValidationData(dvHelper.createValidation(constraint, range)); } } // for (int i = 0; i < titlesList.size(); i++) { // ?? sheet.autoSizeColumn(i); } return wb; }
From source file:com.frameworkset.platform.util.POIExcelUtil2007.java
License:Open Source License
/** * Excel Workbook?./*from ww w . jav a 2 s . co m*/ * * @param colDesc ?:user_id,??:user_name,:type_name" * @param dataList * @return * @author gw_liaozh * @throws InvocationTargetException * @throws IllegalAccessException * @throws IllegalArgumentException */ public static XSSFWorkbook createHSSFWorkbook(String colDesc, List<?> dataList) throws IllegalArgumentException, IllegalAccessException, InvocationTargetException { //??? //TODO: ? List<String> colTitleList = getColumnTitleList(colDesc); List<String> colFieldList = getColumnFieldList(colDesc); XSSFWorkbook wb = new XSSFWorkbook(); XSSFSheet sheet = wb.createSheet(); XSSFFont font = getBaseFont(wb); XSSFCellStyle headCellStyle = getHeadCellStyle(wb, font); //? CellStyle dateCellStyle = getDateTimeCellStyle(wb); //CellStyle strCellStyle = getStringCellStyle(wb); //?? XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet); Map<String, Class<?>> fieldTypeMap = new HashMap<String, Class<?>>(); // XSSFRow titleRow = sheet.createRow(0); for (int i = 0; i < colTitleList.size(); i++) { XSSFCell cell = titleRow.createCell(i); cell.setCellStyle(headCellStyle); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellValue(colTitleList.get(i)); } //? for (int i = 0; i < dataList.size(); i++) { Object obj = dataList.get(i); XSSFRow row = sheet.createRow(i + 1); for (int j = 0; j < colFieldList.size(); j++) { String fieldName = colFieldList.get(j); XSSFCell cell = row.createCell(j); if (obj == null) { continue; } Object value = BeanConvertUtil.getProperty(obj, fieldName); //ClassInfo classInfo = ClassUtil.getClassInfo(obj.getClass()); //Object value = classInfo.getPropertyDescriptor(fieldName).getValue(obj); if (value == null) { continue; } //?? if (value instanceof Number) { cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cell.setCellValue(((Number) value).doubleValue()); } else if (value instanceof Date) { cell.setCellStyle(dateCellStyle); cell.setCellValue((Date) value); } else { cell.setCellType(HSSFCell.CELL_TYPE_STRING); //cell.setCellStyle(strCellStyle); cell.setCellValue(value.toString()); } fieldTypeMap.put(fieldName, value.getClass()); } } //?? for (int i = 0; i < colFieldList.size(); i++) { String fieldName = colFieldList.get(i); Class<?> fieldClass = fieldTypeMap.get(fieldName); if (fieldClass == null) { continue; } CellRangeAddressList range = new CellRangeAddressList(1, 65535, i, i); DataValidationConstraint constraint = null; if (Integer.class.isAssignableFrom(fieldClass)) { constraint = dvHelper.createIntegerConstraint(DataValidationConstraint.OperatorType.NOT_BETWEEN, "0", "-1"); sheet.addValidationData(dvHelper.createValidation(constraint, range)); } else if (Number.class.isAssignableFrom(fieldClass)) { constraint = dvHelper.createNumericConstraint(DataValidationConstraint.ValidationType.DECIMAL, DataValidationConstraint.OperatorType.NOT_BETWEEN, "0", "-1"); sheet.addValidationData(dvHelper.createValidation(constraint, range)); } else if (Date.class.isAssignableFrom(fieldClass)) { constraint = dvHelper.createDateConstraint(DataValidationConstraint.OperatorType.NOT_BETWEEN, "0000-01-02", "0000-01-01", "yyyy-MM-dd"); sheet.addValidationData(dvHelper.createValidation(constraint, range)); } } // for (int i = 0; i < colTitleList.size(); i++) { //?? //sheet.autoSizeColumn(i); } return wb; }
From source file:com.hp.autonomy.frontend.reports.powerpoint.PowerPointServiceImpl.java
License:MIT License
/** * Utility function to write the date graph data as a Excel workbook; required since PowerPoint charts actually * embed an Excel file with corresponding data. If invalid, it'll open in OpenOffice fine, but PowerPoint will * complain that the presentation is corrupted. * @param data the date graph data.// w ww .j a v a2s .c o m * @return a new Excel workbook with specified data on a new sheet. */ private static XSSFWorkbook writeChart(final DategraphData data) { final XSSFWorkbook wb = new XSSFWorkbook(); final XSSFSheet sheet = wb.createSheet("Sheet1"); final CellStyle cellStyle = wb.createCellStyle(); cellStyle.setDataFormat((short) 14); final List<DategraphData.Row> rows = data.getRows(); final long[] timestamps = data.getTimestamps(); final XSSFRow header = sheet.createRow(0); header.createCell(0).setCellValue("Timestamp"); for (int ii = 0; ii < rows.size(); ++ii) { header.createCell(ii + 1).setCellValue(rows.get(ii).getLabel()); } for (int rowIdx = 0; rowIdx < timestamps.length; ++rowIdx) { final XSSFRow row = sheet.createRow(rowIdx + 1); final XSSFCell cell = row.createCell(0); cell.setCellStyle(cellStyle); cell.setCellValue(new Date(timestamps[rowIdx] * 1000)); for (int ii = 0; ii < rows.size(); ++ii) { row.createCell(ii + 1).setCellValue(rows.get(ii).getValues()[rowIdx]); } } return wb; }