List of usage examples for org.apache.poi.ss.usermodel Sheet setColumnWidth
void setColumnWidth(int columnIndex, int width);
The maximum column width for an individual cell is 255 characters.
From source file:com.lushapp.common.excel.ExportExcel.java
License:Apache License
private HSSFWorkbook exportExcelInUserModel2File(String title, Class<T> pojoClass, Collection<T> dataSet) { // /* w w w . j a va2 s.c o m*/ HSSFWorkbook workbook = null; try { // workbook = new HSSFWorkbook(); // ? Sheet sheet = workbook.createSheet(title); // List<String> exportFieldTitle = new ArrayList<String>(); List<Integer> exportFieldWidth = new ArrayList<Integer>(); // ???get List<Method> methodObj = new ArrayList<Method>(); Map<String, Method> convertMethod = new HashMap<String, Method>(); Class superClazz = null; Field fileds[] = new Field[0]; boolean flag = true; while (flag) { if (superClazz != null) { superClazz = superClazz.getSuperclass(); } else { superClazz = pojoClass.getSuperclass(); } if (superClazz.isInstance(Object.class)) { flag = false; } else { Field[] sf = superClazz.getDeclaredFields(); if (sf != null && sf.length > 0) { for (int m = 0; m < sf.length; m++) { fileds = ArrayUtils.addAll(fileds, sf[m]); } } } } // Field cfileds[] = pojoClass.getDeclaredFields(); if (cfileds != null && cfileds.length > 0) { for (int n = 0; n < cfileds.length; n++) { fileds = ArrayUtils.addAll(fileds, cfileds[n]); } } // ??filed for (int i = 0; i < fileds.length; i++) { Field field = fileds[i]; Excel excel = field.getAnnotation(Excel.class); // annottion if (excel != null) { // exportFieldTitle.add(excel.exportName()); // exportFieldWidth.add(excel.exportFieldWidth()); // ? String fieldname = field.getName(); // System.out.println(i+""+excel.exportName()+" "+excel.exportFieldWidth()); StringBuffer getMethodName = new StringBuffer("get"); getMethodName.append(fieldname.substring(0, 1).toUpperCase()); getMethodName.append(fieldname.substring(1)); Method getMethod = pojoClass.getMethod(getMethodName.toString(), new Class[] {}); methodObj.add(getMethod); if (excel.exportConvert() == true) { //---------------------------------------------------------------- //update-begin--Author:Quainty Date:20130524 for[8]excel // get/setXxxxConvert??? ?Entity? StringBuffer getConvertMethodName = new StringBuffer("convertGet"); getConvertMethodName.append(fieldname.substring(0, 1).toUpperCase()); getConvertMethodName.append(fieldname.substring(1)); //getConvertMethodName.append("Convert"); //update-end--Author:Quainty Date:20130524 for[8]excel //---------------------------------------------------------------- // System.out.println("convert: "+getConvertMethodName.toString()); Method getConvertMethod = pojoClass.getMethod(getConvertMethodName.toString(), new Class[] {}); convertMethod.put(getMethodName.toString(), getConvertMethod); } } } int index = 0; // Row row = sheet.createRow(index); row.setHeight((short) 450); CellStyle titleStyle = getTitleStyle(workbook); for (int i = 0, exportFieldTitleSize = exportFieldTitle.size(); i < exportFieldTitleSize; i++) { Cell cell = row.createCell(i); // cell.setCellStyle(style); RichTextString text = new HSSFRichTextString(exportFieldTitle.get(i)); cell.setCellValue(text); cell.setCellStyle(titleStyle); } // ? for (int i = 0; i < exportFieldWidth.size(); i++) { // 256=65280/255 sheet.setColumnWidth(i, 256 * exportFieldWidth.get(i)); } Iterator its = dataSet.iterator(); // ?? while (its.hasNext()) { // index++; row = sheet.createRow(index); row.setHeight((short) 350); Object t = its.next(); for (int k = 0, methodObjSize = methodObj.size(); k < methodObjSize; k++) { Cell cell = row.createCell(k); Method getMethod = methodObj.get(k); Object value = null; if (convertMethod.containsKey(getMethod.getName())) { Method cm = convertMethod.get(getMethod.getName()); value = cm.invoke(t, new Object[] {}); } else { value = getMethod.invoke(t, new Object[] {}); } cell.setCellValue(value == null ? "" : value.toString()); if (index % 2 == 0) cell.setCellStyle(getTwoStyle(workbook)); else cell.setCellStyle(getOneStyle(workbook)); } } } catch (Exception e) { e.printStackTrace(); } return workbook; }
From source file:com.lw.common.utils.ExcelUtil.java
/** * excel//from w ww . ja v a2s. c om * @param list ? * @param keys listmapkey? * @param columnNames excel?? * */ public static Workbook createWorkBook(List<Map<String, Object>> list, String[] keys, String columnNames[]) { // excel Workbook wb = new HSSFWorkbook(); // sheet?? Sheet sheet = wb.createSheet(list.get(0).get("sheetName").toString()); // ???n? for (int i = 0; i < keys.length; i++) { sheet.setColumnWidth((short) i, (short) (35.7 * 150)); } // Row row = sheet.createRow((short) 0); // ??? CellStyle cs = wb.createCellStyle(); CellStyle cs2 = wb.createCellStyle(); // ? Font f = wb.createFont(); Font f2 = wb.createFont(); // ???? f.setFontHeightInPoints((short) 10); f.setColor(IndexedColors.BLACK.getIndex()); f.setBoldweight(Font.BOLDWEIGHT_BOLD); // ?? f2.setFontHeightInPoints((short) 10); f2.setColor(IndexedColors.BLACK.getIndex()); // Font f3=wb.createFont(); // f3.setFontHeightInPoints((short) 10); // f3.setColor(IndexedColors.RED.getIndex()); // ????? cs.setFont(f); cs.setBorderLeft(CellStyle.BORDER_THIN); cs.setBorderRight(CellStyle.BORDER_THIN); cs.setBorderTop(CellStyle.BORDER_THIN); cs.setBorderBottom(CellStyle.BORDER_THIN); cs.setAlignment(CellStyle.ALIGN_CENTER); // ??? cs2.setFont(f2); cs2.setBorderLeft(CellStyle.BORDER_THIN); cs2.setBorderRight(CellStyle.BORDER_THIN); cs2.setBorderTop(CellStyle.BORDER_THIN); cs2.setBorderBottom(CellStyle.BORDER_THIN); cs2.setAlignment(CellStyle.ALIGN_CENTER); //?? for (int i = 0; i < columnNames.length; i++) { Cell cell = row.createCell(i); cell.setCellValue(columnNames[i]); cell.setCellStyle(cs); } //?? for (short i = 1; i < list.size(); i++) { // Row ,Cell , Row Cell 0 // sheet Row row1 = sheet.createRow((short) i); // row for (short j = 0; j < keys.length; j++) { Cell cell = row1.createCell(j); cell.setCellValue(list.get(i).get(keys[j]) == null ? " " : list.get(i).get(keys[j]).toString()); cell.setCellStyle(cs2); } } return wb; }
From source file:com.miraisolutions.xlconnect.Workbook.java
License:Open Source License
public void setColumnWidth(int sheetIndex, int columnIndex, int width) { Sheet sheet = getSheet(sheetIndex); if (width >= 0) sheet.setColumnWidth(columnIndex, width); else if (width == -1) sheet.autoSizeColumn(columnIndex); else/*from ww w. j a va 2 s . c om*/ sheet.setColumnWidth(columnIndex, sheet.getDefaultColumnWidth() * 256); }
From source file:com.nc.common.utils.ExcelUtil.java
License:Open Source License
/** * <pre>//from ww w .j ava2 s . c om * 1. : POI UTIL * 2. : POI WORKBOOK ? / String , List<Map<String, Object>> ? , List<Map<String, Object>> * </pre> * * @method Name : createWorkBook * @param workbook, sheetName, list, colNames * @return Workbook * @throws Exception * */ @SuppressWarnings("deprecation") public static Workbook createWorkBook(Workbook workbook, String sheetName, List<Map<String, Object>> list, List<Map<String, Object>> colNames) throws Exception { Row row; Cell cell; /* ? ? START */ /* Workbook workbook = new Workbook(); */ CellStyle titleStyle = workbook.createCellStyle(); CellStyle cellStyle = workbook.createCellStyle(); CellStyle contentStyle = workbook.createCellStyle(); CellStyle contentStyle_2 = workbook.createCellStyle(); /* ? */ /* ? ? */ Font titleFont = workbook.createFont(); titleFont.setFontHeightInPoints((short) 13); titleFont.setFontName("? "); /* ? */ Font colNameFont = workbook.createFont(); colNameFont.setFontHeightInPoints((short) 10); colNameFont.setFontName("? "); /* ? */ Font contentFont = workbook.createFont(); /* ? ? ? */ titleStyle.setFont(titleFont); /* ? / ? ? */ cellStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); /* ? */ cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//? cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); cellStyle.setFont(colNameFont); /* ? / ? */ contentStyle.setBorderRight(HSSFCellStyle.BORDER_THIN); /* ? */ contentStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); contentStyle.setBorderTop(HSSFCellStyle.BORDER_THIN); contentStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); contentStyle.setFont(contentFont); /* ? / ? */ contentStyle_2.setBorderRight(HSSFCellStyle.BORDER_THIN); /* ? */ contentStyle_2.setBorderLeft(HSSFCellStyle.BORDER_THIN); contentStyle_2.setBorderTop(HSSFCellStyle.BORDER_THIN); contentStyle_2.setBorderBottom(HSSFCellStyle.BORDER_THIN); contentStyle_2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); contentStyle_2.setAlignment(HSSFCellStyle.ALIGN_LEFT); contentStyle_2.setFont(contentFont); /* ? */ Sheet sheet = workbook.createSheet(sheetName); /* ?? */ int sheet1_row = 0; List<Map<String, Object>> colList = colNames; /* ? START */ row = sheet.createRow(sheet1_row); for (int i = 0; i < colList.size(); i++) { cell = row.createCell(i); cell.setCellValue(String.valueOf(colList.get(i).get("item" + i))); cell.setCellStyle(cellStyle); } sheet1_row++; /* ? END */ for (Map<String, Object> map : list) { row = sheet.createRow(sheet1_row); for (int j = 0; j < colList.size(); j++) { cell = row.createCell(j); cell.setCellStyle(contentStyle); if (null != map.get(colList.get(j).get("item" + j))) { cell.setCellValue((double) map.get(String.valueOf(colList.get(j).get("item" + j)))); } else { cell.setCellValue(""); } /* ? ?? */ sheet.autoSizeColumn((short) j); sheet.setColumnWidth(j, (sheet.getColumnWidth(j)) + 312); } sheet1_row++; } return workbook; }
From source file:com.netxforge.netxstudio.server.logic.reporting.ResourceReportingEngine.java
License:Open Source License
public void writeRange(List<Marker> markers, Sheet sheet, Row valueRow, List<Value> range) { CreationHelper createHelper = this.getWorkBook().getCreationHelper(); CellStyle cellStyle = this.getWorkBook().createCellStyle(); cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("m-d-yy h:mm")); // Styles for markers. // CellStyle markerStyleRed = this.getWorkBook().createCellStyle(); // markerStyleRed.setFillForegroundColor(IndexedColors.RED.getIndex()); // markerStyleRed.setFillPattern(CellStyle.SOLID_FOREGROUND); CellStyle markerStyleRed = createRedBorderStyle(); CellStyle markerStyleAmber = createAmberBorderStyle(); // CellStyle markerStyleAmber = this.getWorkBook().createCellStyle(); // markerStyleAmber.setFillPattern(CellStyle.SOLID_FOREGROUND); // markerStyleAmber // .setFillForegroundColor(IndexedColors.ORANGE.getIndex()); // Write the values. for (Value v : range) { // lookup the value. int valueIndex = tsColumnForValue(v); if (valueIndex == -1) { continue; }//www.ja va2 s . c o m Cell valueCell = valueRow.createCell(valueIndex); valueCell.setCellValue(v.getValue()); // Adapt the width of the column for this value. sheet.setColumnWidth(valueIndex, 14 * 256); // Set the markers. if (markers != null) { Marker m; if ((m = StudioUtils.markerForValue(markers, v)) != null) { if (m instanceof ToleranceMarker) { switch (((ToleranceMarker) m).getLevel().getValue()) { case LevelKind.RED_VALUE: { valueCell.setCellStyle(markerStyleRed); } break; case LevelKind.AMBER_VALUE: { valueCell.setCellStyle(markerStyleAmber); } break; } } } } } }
From source file:com.netxforge.netxstudio.server.logic.reporting.RFSServiceDashboardReportingLogic.java
License:Open Source License
/** * Write each Node per NodeType column, starting *//*from w w w . j av a2 s .co m*/ @Override protected void writeContent(Sheet sheet, Service service, Node node, int row, int column) { // Write the NODE.ID box. int newRow = NODE_ROW + (row * NODE_HEIGHT); int nodeColumn = NODE_COLUMN + (column * NODE_WIDTH); sheet.setColumnWidth(nodeColumn, 10 * 256); CellStyle nodeStyle = this.getWorkBook().createCellStyle(); nodeStyle.setBorderTop(CellStyle.BORDER_MEDIUM); nodeStyle.setBorderBottom(CellStyle.BORDER_MEDIUM); nodeStyle.setBorderLeft(CellStyle.BORDER_MEDIUM); nodeStyle.setBorderRight(CellStyle.BORDER_MEDIUM); nodeStyle.setAlignment(CellStyle.ALIGN_CENTER); nodeStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); { Row cellRow = sheet.getRow(newRow); if (cellRow == null) { cellRow = sheet.createRow(newRow); } Cell c1 = cellRow.createCell(nodeColumn); c1.setCellValue(node.getNodeID()); c1.setCellStyle(nodeStyle); } { Row cellRow = sheet.getRow(newRow + 1); if (cellRow == null) { cellRow = sheet.createRow(newRow + 1); } Cell c1 = cellRow.createCell(nodeColumn); c1.setCellStyle(nodeStyle); } { Row cellRow = sheet.getRow(newRow + 2); if (cellRow == null) { cellRow = sheet.createRow(newRow + 2); } Cell c1 = cellRow.createCell(nodeColumn); c1.setCellStyle(nodeStyle); } sheet.addMergedRegion(new CellRangeAddress(newRow, newRow + NODE_HEIGHT - 2, nodeColumn, nodeColumn)); // In between column. sheet.setColumnWidth(nodeColumn + 1, 2 * 256); // Write the RAG CellStyle ragStyle = this.getWorkBook().createCellStyle(); ragStyle.setBorderTop(CellStyle.BORDER_THIN); ragStyle.setBorderBottom(CellStyle.BORDER_THIN); ragStyle.setBorderLeft(CellStyle.BORDER_THIN); ragStyle.setBorderRight(CellStyle.BORDER_THIN); ragStyle.setAlignment(CellStyle.ALIGN_CENTER); ragStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); ragStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); int ragColumn = nodeColumn + 2; sheet.setColumnWidth(ragColumn, 2 * 256); IMonitoringSummary summary = monStateModel.summary(new NullProgressMonitor(), node, new IComputationContext[] { new ObjectContext<Service>(service), new ObjectContext<DateTimeRange>(getPeriod()) }); if (summary == null) { return; } int[] rag = summary.rag(); { Row cellRow = sheet.getRow(newRow); if (cellRow == null) { cellRow = sheet.createRow(newRow); } Cell c1 = cellRow.createCell(ragColumn); c1.setCellValue("R"); CellStyle rStyle = this.getWorkBook().createCellStyle(); rStyle.cloneStyleFrom(ragStyle); rStyle.setFillForegroundColor(IndexedColors.RED.getIndex()); c1.setCellStyle(rStyle); if (rag != null) { c1.setCellValue(rag[0]); } } { Row cellRow = sheet.getRow(newRow + 1); if (cellRow == null) { cellRow = sheet.createRow(newRow + 1); } Cell c1 = cellRow.createCell(ragColumn); c1.setCellValue("A"); CellStyle aStyle = this.getWorkBook().createCellStyle(); aStyle.cloneStyleFrom(ragStyle); aStyle.setFillForegroundColor(IndexedColors.ORANGE.getIndex()); c1.setCellStyle(aStyle); if (rag != null) { c1.setCellValue(rag[1]); } } { Row cellRow = sheet.getRow(newRow + 2); if (cellRow == null) { cellRow = sheet.createRow(newRow + 2); } Cell c1 = cellRow.createCell(ragColumn); c1.setCellValue("G"); CellStyle gStyle = this.getWorkBook().createCellStyle(); gStyle.cloneStyleFrom(ragStyle); gStyle.setFillForegroundColor(IndexedColors.GREEN.getIndex()); c1.setCellStyle(gStyle); if (rag != null) { c1.setCellValue(rag[2]); } } // Clean our adapted summary. node.eAdapters().remove(summary); }
From source file:com.primovision.lutransport.core.util.TollCompanyTagUploadUtil.java
private static void createColumnHeaders(LinkedList<String> expectedColumnList, Sheet sheet, CellStyle style) { Row headerRow = sheet.createRow(0);//from ww w.j a va 2 s . c o m int columnHeaderIndex = 0; for (String columnHeader : expectedColumnList) { // TODO redundant, use actualColumnListMap.keys instead sheet.setColumnWidth(columnHeaderIndex, 256 * 20); Cell cell = headerRow.createCell(columnHeaderIndex++); cell.setCellValue(columnHeader); style.setAlignment(CellStyle.ALIGN_CENTER); style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); cell.setCellStyle(style); } }
From source file:com.primovision.lutransport.core.util.TollCompanyTagUploadUtil.java
private static Cell createExcelCell(Sheet sheet, Row row, int columnIndex, int width) { Cell cell = row.createCell(columnIndex); sheet.setColumnWidth(columnIndex, width); return cell;// w w w .j a v a 2 s .co m }
From source file:com.qihang.winter.poi.excel.export.base.ExcelExportBase.java
License:Apache License
public void setCellWith(List<com.qihang.winter.poi.excel.entity.params.ExcelExportEntity> excelParams, Sheet sheet) { int index = 0; for (int i = 0; i < excelParams.size(); i++) { if (excelParams.get(i).getList() != null) { List<com.qihang.winter.poi.excel.entity.params.ExcelExportEntity> list = excelParams.get(i) .getList();//w ww. j a v a 2 s. co m for (int j = 0; j < list.size(); j++) { sheet.setColumnWidth(index, (int) (256 * list.get(j).getWidth())); index++; } } else { sheet.setColumnWidth(index, (int) (256 * excelParams.get(i).getWidth())); index++; } } }
From source file:com.qihang.winter.poi.util.PoiSheetUtility.java
License:Apache License
/** * Given a sheet, this method deletes a column from a sheet and moves * all the columns to the right of it to the left one cell. * //from ww w .jav a 2 s . co m * Note, this method will not update any formula references. * * @param sheet * @param column */ public static void deleteColumn(Sheet sheet, int columnToDelete) { int maxColumn = 0; for (int r = 0; r < sheet.getLastRowNum() + 1; r++) { Row row = sheet.getRow(r); // if no row exists here; then nothing to do; next! if (row == null) continue; // if the row doesn't have this many columns then we are good; next! int lastColumn = row.getLastCellNum(); if (lastColumn > maxColumn) maxColumn = lastColumn; if (lastColumn < columnToDelete) continue; for (int x = columnToDelete + 1; x < lastColumn + 1; x++) { Cell oldCell = row.getCell(x - 1); if (oldCell != null) row.removeCell(oldCell); Cell nextCell = row.getCell(x); if (nextCell != null) { Cell newCell = row.createCell(x - 1, nextCell.getCellType()); cloneCell(newCell, nextCell); } } } // Adjust the column widths for (int c = 0; c < maxColumn; c++) { sheet.setColumnWidth(c, sheet.getColumnWidth(c + 1)); } }