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.jeans.iservlet.controller.impl.ExportController.java
/** * ?????/*from w ww .j a v a 2s . c o m*/ * * @param storedOnly * ??? * @return * @throws IOException */ @RequestMapping(method = RequestMethod.POST, value = "/accessories") public ResponseEntity<byte[]> exportAccessories(@RequestParam boolean storedOnly) throws IOException { StringBuilder fn = new StringBuilder(getCurrentCompany().getName()); Date n = new Date(); String today = (new SimpleDateFormat("yyyyMMdd")).format(n); String now = (new SimpleDateFormat("yyyy-MM-dd HHmmss")).format(n); Workbook wb = new XSSFWorkbook(); fn.append(" - ???(").append(today).append(").xlsx"); Sheet sheet = wb.createSheet(now); // // ?10?? Font font = wb.createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); font.setFontName(""); font.setFontHeightInPoints((short) 10); // ????????? CellStyle cellStyle = wb.createCellStyle(); cellStyle.setAlignment(CellStyle.ALIGN_CENTER); cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); cellStyle.setFont(font); cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("text")); cellStyle.setWrapText(false); // 20 Row row = sheet.createRow(0); row.setHeightInPoints(20); Cell cell = null; for (int i = 0; i < 7; i++) { cell = row.createCell(i, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyle); cell.setCellValue(ACS_HEADERS[i]); sheet.setColumnWidth(i, ACS_HEADERS_WIDTH[i] * 256); } List<Accessory> acs = acsService.listAccessories(getCurrentCompany(), storedOnly); Collections.sort(acs, new Comparator<Accessory>() { @Override public int compare(Accessory o1, Accessory o2) { int ret = o1.getType().compareTo(o2.getType()); if (ret == 0) { ret = Collator.getInstance(java.util.Locale.CHINA).compare(o1.getName(), o2.getName()); if (ret == 0) { ret = Collator.getInstance(java.util.Locale.CHINA).compare(o1.getBrand(), o2.getBrand()); if (ret == 0) { ret = Collator.getInstance(java.util.Locale.CHINA).compare(o1.getModel(), o2.getModel()); } } } return ret; } }); // DataFormat df = wb.createDataFormat(); // ?10? Font dFont = wb.createFont(); dFont.setFontName(""); dFont.setFontHeightInPoints((short) 10); // ?1??????? CellStyle cellStyleString = wb.createCellStyle(); cellStyleString.setAlignment(CellStyle.ALIGN_CENTER); cellStyleString.setVerticalAlignment(CellStyle.VERTICAL_CENTER); cellStyleString.setFont(dFont); cellStyleString.setDataFormat(HSSFDataFormat.getBuiltinFormat("text")); cellStyleString.setWrapText(false); // ?2??????(#)??? CellStyle cellStyleQuantity = sheet.getWorkbook().createCellStyle(); cellStyleQuantity.setAlignment(CellStyle.ALIGN_RIGHT); cellStyleQuantity.setVerticalAlignment(CellStyle.VERTICAL_CENTER); cellStyleQuantity.setFont(dFont); cellStyleQuantity.setDataFormat(df.getFormat("#")); cellStyleQuantity.setWrapText(false); int rowNumber = 1; for (Accessory ac : acs) { // 20 Row dRow = sheet.createRow(rowNumber); dRow.setHeightInPoints(20); Cell dCell = null; dCell = dRow.createCell(0, Cell.CELL_TYPE_STRING); dCell.setCellStyle(cellStyleString); dCell.setCellValue(ac.getType().getTitle()); dCell = dRow.createCell(1, Cell.CELL_TYPE_STRING); dCell.setCellStyle(cellStyleString); dCell.setCellValue(ac.getName()); dCell = dRow.createCell(2, Cell.CELL_TYPE_STRING); dCell.setCellStyle(cellStyleString); dCell.setCellValue(ac.getBrand()); dCell = dRow.createCell(3, Cell.CELL_TYPE_STRING); dCell.setCellStyle(cellStyleString); dCell.setCellValue(ac.getModel()); dCell = dRow.createCell(4, Cell.CELL_TYPE_NUMERIC); dCell.setCellStyle(cellStyleQuantity); dCell.setCellValue(null == ac.getStorage() ? 0 : ac.getStorage().getQuantity()); dCell = dRow.createCell(5, Cell.CELL_TYPE_STRING); dCell.setCellStyle(cellStyleString); dCell.setCellValue(ac.getUnit()); dCell = dRow.createCell(6, Cell.CELL_TYPE_STRING); dCell.setCellStyle(cellStyleString); dCell.setCellValue(ac.getDescription()); rowNumber++; } String filename = null; if (isIE()) { filename = URLEncoder.encode(fn.toString(), "UTF-8").replaceAll("\\+", "%20"); } else { filename = new String(fn.toString().getBytes("UTF-8"), "iso8859-1"); } response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); response.setHeader("Content-disposition", "attachment; filename=" + filename); BufferedOutputStream out = new BufferedOutputStream(response.getOutputStream(), 4096); wb.write(out); wb.close(); out.close(); return null; }
From source file:com.jeans.iservlet.controller.impl.ExportController.java
/** * ????//www . ja v a 2 s . co m * * @param ids * ???(AccessoryEntryInvoice.id)? * @return * @throws IOException */ private void exportAccessoryEntries(List<Long> idList) throws IOException { StringBuilder fn = new StringBuilder(getCurrentCompany().getName()); fn.append(" - ????.xlsx"); Workbook wb = new XSSFWorkbook(); DataFormat df = wb.createDataFormat(); // ?10?? Font bFont = wb.createFont(); bFont.setBoldweight(Font.BOLDWEIGHT_BOLD); bFont.setFontName(""); bFont.setFontHeightInPoints((short) 10); // ?10? Font nFont = wb.createFont(); nFont.setFontName(""); nFont.setFontHeightInPoints((short) 10); // ??1??????? CellStyle csLeftTextBold = wb.createCellStyle(); csLeftTextBold.setAlignment(CellStyle.ALIGN_LEFT); csLeftTextBold.setVerticalAlignment(CellStyle.VERTICAL_CENTER); csLeftTextBold.setFont(bFont); csLeftTextBold.setDataFormat(HSSFDataFormat.getBuiltinFormat("text")); csLeftTextBold.setWrapText(false); // ??2??????? CellStyle csLeftText = wb.createCellStyle(); csLeftText.setAlignment(CellStyle.ALIGN_LEFT); csLeftText.setVerticalAlignment(CellStyle.VERTICAL_CENTER); csLeftText.setFont(nFont); csLeftText.setDataFormat(HSSFDataFormat.getBuiltinFormat("text")); csLeftText.setWrapText(false); // ??3??????? CellStyle csLeftTime = wb.createCellStyle(); csLeftTime.setAlignment(CellStyle.ALIGN_LEFT); csLeftTime.setVerticalAlignment(CellStyle.VERTICAL_CENTER); csLeftTime.setFont(nFont); csLeftTime.setDataFormat(df.getFormat("yyyy-MM-dd HH:mm")); csLeftTime.setWrapText(false); // ??4??????? CellStyle csCenterTextBold = wb.createCellStyle(); csCenterTextBold.setAlignment(CellStyle.ALIGN_CENTER); csCenterTextBold.setVerticalAlignment(CellStyle.VERTICAL_CENTER); csCenterTextBold.setFont(bFont); csCenterTextBold.setDataFormat(HSSFDataFormat.getBuiltinFormat("text")); csCenterTextBold.setWrapText(false); // ??5??????? CellStyle csCenterText = wb.createCellStyle(); csCenterText.setAlignment(CellStyle.ALIGN_CENTER); csCenterText.setVerticalAlignment(CellStyle.VERTICAL_CENTER); csCenterText.setFont(nFont); csCenterText.setDataFormat(HSSFDataFormat.getBuiltinFormat("text")); csCenterText.setWrapText(false); // ??6??????? CellStyle csCenterNumber = wb.createCellStyle(); csCenterNumber.setAlignment(CellStyle.ALIGN_CENTER); csCenterNumber.setVerticalAlignment(CellStyle.VERTICAL_CENTER); csCenterNumber.setFont(nFont); csCenterNumber.setDataFormat(df.getFormat("#")); csCenterNumber.setWrapText(false); // ??7????8???? CellStyle csLeftId = wb.createCellStyle(); csLeftId.setAlignment(CellStyle.ALIGN_LEFT); csLeftId.setVerticalAlignment(CellStyle.VERTICAL_CENTER); csLeftId.setFont(nFont); csLeftId.setDataFormat(df.getFormat("#00000000")); csLeftId.setWrapText(false); // ??8??????? CellStyle csCenterNumberBold = wb.createCellStyle(); csCenterNumberBold.setAlignment(CellStyle.ALIGN_CENTER); csCenterNumberBold.setVerticalAlignment(CellStyle.VERTICAL_CENTER); csCenterNumberBold.setFont(bFont); csCenterNumberBold.setDataFormat(df.getFormat("#")); csCenterNumberBold.setWrapText(false); // ???sheet for (long id : idList) { AccessoryEntryInvoice invoice = arService.loadEntryInvoice(id); if (null == invoice) { continue; } Sheet sheet = wb.createSheet("?#" + id); // ??xxxxxx Row row = sheet.createRow(0); row.setHeightInPoints(20); Cell cell = row.createCell(0, Cell.CELL_TYPE_STRING); cell.setCellStyle(csLeftTextBold); cell.setCellValue("??"); cell = row.createCell(1, Cell.CELL_TYPE_NUMERIC); cell.setCellStyle(csLeftId); cell.setCellValue(id); // yyyy-MM-dd HH:mm row = sheet.createRow(1); row.setHeightInPoints(20); cell = row.createCell(0, Cell.CELL_TYPE_STRING); cell.setCellStyle(csLeftTextBold); cell.setCellValue(""); cell = row.createCell(1, Cell.CELL_TYPE_NUMERIC); cell.setCellStyle(csLeftTime); cell.setCellValue(invoice.getTime()); // xxx row = sheet.createRow(2); row.setHeightInPoints(20); cell = row.createCell(0, Cell.CELL_TYPE_STRING); cell.setCellStyle(csLeftTextBold); cell.setCellValue(""); cell = row.createCell(1, Cell.CELL_TYPE_STRING); cell.setCellStyle(csLeftText); cell.setCellValue(invoice.getOperator().getName()); // row = sheet.createRow(3); row.setHeightInPoints(20); for (int i = 0; i < 8; i++) { cell = row.createCell(i, Cell.CELL_TYPE_STRING); cell.setCellStyle(csCenterTextBold); cell.setCellValue(ENT_HEADERS[i]); sheet.setColumnWidth(i, ENT_HEADERS_WIDTH[i] * 256); } // List<AccessoryEntry> entries = invoice.getEntries(); int rowNumber = 4; int totalQuantity = 0; int totalRemained = 0; for (AccessoryEntry entry : entries) { Accessory acs = entry.getAccessory(); row = sheet.createRow(rowNumber); row.setHeightInPoints(20); cell = row.createCell(0, Cell.CELL_TYPE_STRING); cell.setCellStyle(csCenterText); cell.setCellValue(acs.getType().getTitle()); cell = row.createCell(1, Cell.CELL_TYPE_STRING); cell.setCellStyle(csCenterText); cell.setCellValue(acs.getName()); cell = row.createCell(2, Cell.CELL_TYPE_STRING); cell.setCellStyle(csCenterText); cell.setCellValue(acs.getBrand()); cell = row.createCell(3, Cell.CELL_TYPE_STRING); cell.setCellStyle(csCenterText); cell.setCellValue(acs.getModel()); cell = row.createCell(4, Cell.CELL_TYPE_STRING); cell.setCellStyle(csCenterText); cell.setCellValue(acs.getDescription()); cell = row.createCell(5, Cell.CELL_TYPE_NUMERIC); cell.setCellStyle(csCenterNumber); cell.setCellValue(entry.getQuantity()); totalQuantity += entry.getQuantity(); cell = row.createCell(6, Cell.CELL_TYPE_STRING); cell.setCellStyle(csCenterText); cell.setCellValue(acs.getUnit()); cell = row.createCell(7, Cell.CELL_TYPE_NUMERIC); cell.setCellStyle(csCenterNumber); cell.setCellValue(entry.getRemained()); totalRemained += entry.getRemained(); rowNumber++; } // ? row = sheet.createRow(rowNumber); row.setHeightInPoints(20); cell = row.createCell(0, Cell.CELL_TYPE_STRING); cell.setCellStyle(csCenterTextBold); cell.setCellValue("?"); cell = row.createCell(1, Cell.CELL_TYPE_NUMERIC); cell.setCellStyle(csCenterNumberBold); cell.setCellValue(entries.size()); cell = row.createCell(5, Cell.CELL_TYPE_NUMERIC); cell.setCellStyle(csCenterNumberBold); cell.setCellValue(totalQuantity); cell = row.createCell(7, Cell.CELL_TYPE_NUMERIC); cell.setCellStyle(csCenterNumberBold); cell.setCellValue(totalRemained); } String filename = null; if (isIE()) { filename = URLEncoder.encode(fn.toString(), "UTF-8").replaceAll("\\+", "%20"); } else { filename = new String(fn.toString().getBytes("UTF-8"), "iso8859-1"); } response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); response.setHeader("Content-disposition", "attachment; filename=" + filename); BufferedOutputStream out = new BufferedOutputStream(response.getOutputStream(), 4096); wb.write(out); wb.close(); out.close(); }
From source file:com.jeans.iservlet.controller.impl.ExportController.java
/** * ????//from w w w. ja va 2s . co m * * @param ids * ???(AccessoryDischargeInvoice.id)? * @return * @throws IOException */ private void exportAccessoryDischarges(List<Long> idList) throws IOException { StringBuilder fn = new StringBuilder(getCurrentCompany().getName()); fn.append(" - ????.xlsx"); Workbook wb = new XSSFWorkbook(); DataFormat df = wb.createDataFormat(); // ?10?? Font bFont = wb.createFont(); bFont.setBoldweight(Font.BOLDWEIGHT_BOLD); bFont.setFontName(""); bFont.setFontHeightInPoints((short) 10); // ?10? Font nFont = wb.createFont(); nFont.setFontName(""); nFont.setFontHeightInPoints((short) 10); // ??1??????? CellStyle csLeftTextBold = wb.createCellStyle(); csLeftTextBold.setAlignment(CellStyle.ALIGN_LEFT); csLeftTextBold.setVerticalAlignment(CellStyle.VERTICAL_CENTER); csLeftTextBold.setFont(bFont); csLeftTextBold.setDataFormat(HSSFDataFormat.getBuiltinFormat("text")); csLeftTextBold.setWrapText(false); // ??2??????? CellStyle csLeftText = wb.createCellStyle(); csLeftText.setAlignment(CellStyle.ALIGN_LEFT); csLeftText.setVerticalAlignment(CellStyle.VERTICAL_CENTER); csLeftText.setFont(nFont); csLeftText.setDataFormat(HSSFDataFormat.getBuiltinFormat("text")); csLeftText.setWrapText(false); // ??3??????? CellStyle csLeftTime = wb.createCellStyle(); csLeftTime.setAlignment(CellStyle.ALIGN_LEFT); csLeftTime.setVerticalAlignment(CellStyle.VERTICAL_CENTER); csLeftTime.setFont(nFont); csLeftTime.setDataFormat(df.getFormat("yyyy-MM-dd HH:mm")); csLeftTime.setWrapText(false); // ??4??????? CellStyle csCenterTextBold = wb.createCellStyle(); csCenterTextBold.setAlignment(CellStyle.ALIGN_CENTER); csCenterTextBold.setVerticalAlignment(CellStyle.VERTICAL_CENTER); csCenterTextBold.setFont(bFont); csCenterTextBold.setDataFormat(HSSFDataFormat.getBuiltinFormat("text")); csCenterTextBold.setWrapText(false); // ??5??????? CellStyle csCenterText = wb.createCellStyle(); csCenterText.setAlignment(CellStyle.ALIGN_CENTER); csCenterText.setVerticalAlignment(CellStyle.VERTICAL_CENTER); csCenterText.setFont(nFont); csCenterText.setDataFormat(HSSFDataFormat.getBuiltinFormat("text")); csCenterText.setWrapText(false); // ??6??????? CellStyle csCenterNumber = wb.createCellStyle(); csCenterNumber.setAlignment(CellStyle.ALIGN_CENTER); csCenterNumber.setVerticalAlignment(CellStyle.VERTICAL_CENTER); csCenterNumber.setFont(nFont); csCenterNumber.setDataFormat(df.getFormat("#")); csCenterNumber.setWrapText(false); // ??7????8???? CellStyle csLeftId = wb.createCellStyle(); csLeftId.setAlignment(CellStyle.ALIGN_LEFT); csLeftId.setVerticalAlignment(CellStyle.VERTICAL_CENTER); csLeftId.setFont(nFont); csLeftId.setDataFormat(df.getFormat("#00000000")); csLeftId.setWrapText(false); // ??8??????? CellStyle csCenterNumberBold = wb.createCellStyle(); csCenterNumberBold.setAlignment(CellStyle.ALIGN_CENTER); csCenterNumberBold.setVerticalAlignment(CellStyle.VERTICAL_CENTER); csCenterNumberBold.setFont(bFont); csCenterNumberBold.setDataFormat(df.getFormat("#")); csCenterNumberBold.setWrapText(false); // ???sheet for (long id : idList) { AccessoryDischargeInvoice invoice = arService.loadDischargeInvoice(id); if (null == invoice) { continue; } Sheet sheet = wb.createSheet("?#" + id); // ??xxxxxx Row row = sheet.createRow(0); row.setHeightInPoints(20); Cell cell = row.createCell(0, Cell.CELL_TYPE_STRING); cell.setCellStyle(csLeftTextBold); cell.setCellValue("??"); cell = row.createCell(1, Cell.CELL_TYPE_NUMERIC); cell.setCellStyle(csLeftId); cell.setCellValue(id); // yyyy-MM-dd HH:mm row = sheet.createRow(1); row.setHeightInPoints(20); cell = row.createCell(0, Cell.CELL_TYPE_STRING); cell.setCellStyle(csLeftTextBold); cell.setCellValue(""); cell = row.createCell(1, Cell.CELL_TYPE_NUMERIC); cell.setCellStyle(csLeftTime); cell.setCellValue(invoice.getTime()); // xxx row = sheet.createRow(2); row.setHeightInPoints(20); cell = row.createCell(0, Cell.CELL_TYPE_STRING); cell.setCellStyle(csLeftTextBold); cell.setCellValue(""); cell = row.createCell(1, Cell.CELL_TYPE_STRING); cell.setCellStyle(csLeftText); cell.setCellValue(invoice.getApplicant().getName()); // xxx row = sheet.createRow(3); row.setHeightInPoints(20); cell = row.createCell(0, Cell.CELL_TYPE_STRING); cell.setCellStyle(csLeftTextBold); cell.setCellValue(""); cell = row.createCell(1, Cell.CELL_TYPE_STRING); cell.setCellStyle(csLeftText); cell.setCellValue(invoice.getOperator().getName()); // row = sheet.createRow(4); row.setHeightInPoints(20); for (int i = 0; i < 7; i++) { cell = row.createCell(i, Cell.CELL_TYPE_STRING); cell.setCellStyle(csCenterTextBold); cell.setCellValue(DIS_HEADERS[i]); sheet.setColumnWidth(i, DIS_HEADERS_WIDTH[i] * 256); } // List<AccessoryDischarge> discharges = invoice.getDischarges(); int rowNumber = 5; int totalQuantity = 0; for (AccessoryDischarge discharge : discharges) { Accessory acs = discharge.getAccessory(); row = sheet.createRow(rowNumber); row.setHeightInPoints(20); cell = row.createCell(0, Cell.CELL_TYPE_STRING); cell.setCellStyle(csCenterText); cell.setCellValue(acs.getType().getTitle()); cell = row.createCell(1, Cell.CELL_TYPE_STRING); cell.setCellStyle(csCenterText); cell.setCellValue(acs.getName()); cell = row.createCell(2, Cell.CELL_TYPE_STRING); cell.setCellStyle(csCenterText); cell.setCellValue(acs.getBrand()); cell = row.createCell(3, Cell.CELL_TYPE_STRING); cell.setCellStyle(csCenterText); cell.setCellValue(acs.getModel()); cell = row.createCell(4, Cell.CELL_TYPE_STRING); cell.setCellStyle(csCenterText); cell.setCellValue(acs.getDescription()); cell = row.createCell(5, Cell.CELL_TYPE_NUMERIC); cell.setCellStyle(csCenterNumber); cell.setCellValue(discharge.getQuantity()); totalQuantity += discharge.getQuantity(); cell = row.createCell(6, Cell.CELL_TYPE_STRING); cell.setCellStyle(csCenterText); cell.setCellValue(acs.getUnit()); rowNumber++; } // ? row = sheet.createRow(rowNumber); row.setHeightInPoints(20); cell = row.createCell(0, Cell.CELL_TYPE_STRING); cell.setCellStyle(csCenterTextBold); cell.setCellValue("?"); cell = row.createCell(1, Cell.CELL_TYPE_NUMERIC); cell.setCellStyle(csCenterNumberBold); cell.setCellValue(discharges.size()); cell = row.createCell(5, Cell.CELL_TYPE_NUMERIC); cell.setCellStyle(csCenterNumberBold); cell.setCellValue(totalQuantity); } String filename = null; if (isIE()) { filename = URLEncoder.encode(fn.toString(), "UTF-8").replaceAll("\\+", "%20"); } else { filename = new String(fn.toString().getBytes("UTF-8"), "iso8859-1"); } response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); response.setHeader("Content-disposition", "attachment; filename=" + filename); BufferedOutputStream out = new BufferedOutputStream(response.getOutputStream(), 4096); wb.write(out); wb.close(); out.close(); }
From source file:com.jeans.iservlet.controller.impl.ExportController.java
/** * ?????// w w w .j a va 2 s. c o m * * @param ids * ??(AccessoryInvoiceDetail.id)? * @return * @throws IOException */ private void exportAccessoryRegistries(List<Long> idList) throws IOException { StringBuilder fn = new StringBuilder(getCurrentCompany().getName()); fn.append(" - ???.xlsx"); Workbook wb = new XSSFWorkbook(); DataFormat df = wb.createDataFormat(); // ?10?? Font bFont = wb.createFont(); bFont.setBoldweight(Font.BOLDWEIGHT_BOLD); bFont.setFontName(""); bFont.setFontHeightInPoints((short) 10); // ?10? Font nFont = wb.createFont(); nFont.setFontName(""); nFont.setFontHeightInPoints((short) 10); // ??1??????? CellStyle csLeftTextBold = wb.createCellStyle(); csLeftTextBold.setAlignment(CellStyle.ALIGN_LEFT); csLeftTextBold.setVerticalAlignment(CellStyle.VERTICAL_CENTER); csLeftTextBold.setFont(bFont); csLeftTextBold.setDataFormat(HSSFDataFormat.getBuiltinFormat("text")); csLeftTextBold.setWrapText(false); // ??2??????? CellStyle csLeftText = wb.createCellStyle(); csLeftText.setAlignment(CellStyle.ALIGN_LEFT); csLeftText.setVerticalAlignment(CellStyle.VERTICAL_CENTER); csLeftText.setFont(nFont); csLeftText.setDataFormat(HSSFDataFormat.getBuiltinFormat("text")); csLeftText.setWrapText(false); // ??3??????? CellStyle csCenterTime = wb.createCellStyle(); csCenterTime.setAlignment(CellStyle.ALIGN_CENTER); csCenterTime.setVerticalAlignment(CellStyle.VERTICAL_CENTER); csCenterTime.setFont(nFont); csCenterTime.setDataFormat(df.getFormat("yyyy-MM-dd HH:mm")); csCenterTime.setWrapText(false); // ??4??????? CellStyle csCenterTextBold = wb.createCellStyle(); csCenterTextBold.setAlignment(CellStyle.ALIGN_CENTER); csCenterTextBold.setVerticalAlignment(CellStyle.VERTICAL_CENTER); csCenterTextBold.setFont(bFont); csCenterTextBold.setDataFormat(HSSFDataFormat.getBuiltinFormat("text")); csCenterTextBold.setWrapText(false); // ??5??????? CellStyle csCenterText = wb.createCellStyle(); csCenterText.setAlignment(CellStyle.ALIGN_CENTER); csCenterText.setVerticalAlignment(CellStyle.VERTICAL_CENTER); csCenterText.setFont(nFont); csCenterText.setDataFormat(HSSFDataFormat.getBuiltinFormat("text")); csCenterText.setWrapText(false); // ??6??????? CellStyle csCenterNumber = wb.createCellStyle(); csCenterNumber.setAlignment(CellStyle.ALIGN_CENTER); csCenterNumber.setVerticalAlignment(CellStyle.VERTICAL_CENTER); csCenterNumber.setFont(nFont); csCenterNumber.setDataFormat(df.getFormat("#")); csCenterNumber.setWrapText(false); // ??7????8???? CellStyle csLeftId = wb.createCellStyle(); csLeftId.setAlignment(CellStyle.ALIGN_LEFT); csLeftId.setVerticalAlignment(CellStyle.VERTICAL_CENTER); csLeftId.setFont(nFont); csLeftId.setDataFormat(df.getFormat("#00000000")); csLeftId.setWrapText(false); // ??8????8???? CellStyle csCenterId = wb.createCellStyle(); csCenterId.setAlignment(CellStyle.ALIGN_CENTER); csCenterId.setVerticalAlignment(CellStyle.VERTICAL_CENTER); csCenterId.setFont(nFont); csCenterId.setDataFormat(df.getFormat("#00000000")); csCenterId.setWrapText(false); // ??9??????? CellStyle csCenterNumberBold = wb.createCellStyle(); csCenterNumberBold.setAlignment(CellStyle.ALIGN_CENTER); csCenterNumberBold.setVerticalAlignment(CellStyle.VERTICAL_CENTER); csCenterNumberBold.setFont(bFont); csCenterNumberBold.setDataFormat(df.getFormat("#")); csCenterNumberBold.setWrapText(false); // ????sheet long currAcsId = 0; Sheet sheet = null; int rowNumber = 0, totalIn = 0, totalOut = 0; for (long id : idList) { AccessoryInvoiceDetail registry = arService.loadRegistry(id); if (null == registry) { continue; } Accessory acs = registry.getAccessory(); if (acs.getId() != currAcsId) { // ???sheet?? if (null != sheet) { Row row = sheet.createRow(rowNumber); row.setHeightInPoints(20); Cell cell = row.createCell(0, Cell.CELL_TYPE_STRING); cell.setCellStyle(csCenterTextBold); cell.setCellValue("?"); cell = row.createCell(3, Cell.CELL_TYPE_NUMERIC); cell.setCellStyle(csCenterNumberBold); cell.setCellValue(totalIn); cell = row.createCell(4, Cell.CELL_TYPE_NUMERIC); cell.setCellStyle(csCenterNumberBold); cell.setCellValue(totalOut); } // ?sheet? currAcsId = acs.getId(); sheet = wb.createSheet("???#" + currAcsId); // ??xxxxxx Row row = sheet.createRow(0); row.setHeightInPoints(20); Cell cell = row.createCell(1, Cell.CELL_TYPE_STRING); cell.setCellStyle(csLeftTextBold); cell.setCellValue("??"); cell = row.createCell(2, Cell.CELL_TYPE_NUMERIC); cell.setCellStyle(csLeftId); cell.setCellValue(currAcsId); // xxxxxx row = sheet.createRow(1); row.setHeightInPoints(20); cell = row.createCell(1, Cell.CELL_TYPE_STRING); cell.setCellStyle(csLeftTextBold); cell.setCellValue(""); cell = row.createCell(2, Cell.CELL_TYPE_STRING); cell.setCellStyle(csLeftText); cell.setCellValue(acs.getType().getTitle()); // ??xxxxxx row = sheet.createRow(2); row.setHeightInPoints(20); cell = row.createCell(1, Cell.CELL_TYPE_STRING); cell.setCellStyle(csLeftTextBold); cell.setCellValue("??"); cell = row.createCell(2, Cell.CELL_TYPE_STRING); cell.setCellStyle(csLeftText); cell.setCellValue(acs.getName()); // ?xxxxxx row = sheet.createRow(3); row.setHeightInPoints(20); cell = row.createCell(1, Cell.CELL_TYPE_STRING); cell.setCellStyle(csLeftTextBold); cell.setCellValue("?"); cell = row.createCell(2, Cell.CELL_TYPE_STRING); cell.setCellStyle(csLeftText); cell.setCellValue(acs.getBrand()); // ?xxxxxx row = sheet.createRow(4); row.setHeightInPoints(20); cell = row.createCell(1, Cell.CELL_TYPE_STRING); cell.setCellStyle(csLeftTextBold); cell.setCellValue("?"); cell = row.createCell(2, Cell.CELL_TYPE_STRING); cell.setCellStyle(csLeftText); cell.setCellValue(acs.getModel()); // ??xxxxxx row = sheet.createRow(5); row.setHeightInPoints(20); cell = row.createCell(1, Cell.CELL_TYPE_STRING); cell.setCellStyle(csLeftTextBold); cell.setCellValue("???"); cell = row.createCell(2, Cell.CELL_TYPE_STRING); cell.setCellStyle(csLeftText); cell.setCellValue(acs.getDescription()); // ???xxxxxx row = sheet.createRow(6); row.setHeightInPoints(20); cell = row.createCell(1, Cell.CELL_TYPE_STRING); cell.setCellStyle(csLeftTextBold); cell.setCellValue("???"); cell = row.createCell(2, Cell.CELL_TYPE_STRING); cell.setCellStyle(csLeftText); cell.setCellValue(acs.getUnit()); // row = sheet.createRow(7); row.setHeightInPoints(20); for (int i = 0; i < 6; i++) { cell = row.createCell(i, Cell.CELL_TYPE_STRING); cell.setCellStyle(csCenterTextBold); cell.setCellValue(REG_HEADERS[i]); sheet.setColumnWidth(i, REG_HEADERS_WIDTH[i] * 256); } rowNumber = 8; totalIn = 0; totalOut = 0; } // ? Row row = sheet.createRow(rowNumber); row.setHeightInPoints(20); Cell cell = row.createCell(0, Cell.CELL_TYPE_NUMERIC); cell.setCellStyle(csCenterNumber); cell.setCellValue(rowNumber - 7); cell = row.createCell(1, Cell.CELL_TYPE_NUMERIC); cell.setCellStyle(csCenterTime); cell.setCellValue(registry.getInvoice().getTime()); cell = row.createCell(2, Cell.CELL_TYPE_NUMERIC); cell.setCellStyle(csCenterId); cell.setCellValue(registry.getInvoice().getId()); cell = row.createCell(3, Cell.CELL_TYPE_NUMERIC); cell.setCellStyle(csCenterNumber); cell.setCellValue(id > 0 ? registry.getQuantity() : 0); cell = row.createCell(4, Cell.CELL_TYPE_NUMERIC); cell.setCellStyle(csCenterNumber); cell.setCellValue(id < 0 ? registry.getQuantity() : 0); cell = row.createCell(5, Cell.CELL_TYPE_STRING); cell.setCellStyle(csCenterText); cell.setCellValue( id < 0 ? ((AccessoryDischargeInvoice) registry.getInvoice()).getApplicant().getName() : ""); if (id > 0) { totalIn += registry.getQuantity(); } else { totalOut += registry.getQuantity(); } rowNumber++; } String filename = null; if (isIE()) { filename = URLEncoder.encode(fn.toString(), "UTF-8").replaceAll("\\+", "%20"); } else { filename = new String(fn.toString().getBytes("UTF-8"), "iso8859-1"); } response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); response.setHeader("Content-disposition", "attachment; filename=" + filename); BufferedOutputStream out = new BufferedOutputStream(response.getOutputStream(), 4096); wb.write(out); wb.close(); out.close(); }
From source file:com.jeans.iservlet.controller.impl.ExportController.java
/** * ???/*from w ww . jav a 2 s.co m*/ * * @return * @throws IOException */ @RequestMapping(method = RequestMethod.POST, value = "/systems") public ResponseEntity<byte[]> exportITSystems() throws IOException { StringBuilder fn = new StringBuilder(getCurrentCompany().getName()); fn.append(" - ??("); Workbook wb = new XSSFWorkbook(); Sheet sheet = wb.createSheet("?"); // ? // // ?10?? Font tFont = sheet.getWorkbook().createFont(); tFont.setBoldweight(Font.BOLDWEIGHT_BOLD); tFont.setFontName(""); tFont.setFontHeightInPoints((short) 10); // ????????? CellStyle cellStyleTitle = sheet.getWorkbook().createCellStyle(); cellStyleTitle.setAlignment(CellStyle.ALIGN_CENTER); cellStyleTitle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); cellStyleTitle.setFont(tFont); cellStyleTitle.setDataFormat(HSSFDataFormat.getBuiltinFormat("text")); cellStyleTitle.setWrapText(false); // 20 Row row = sheet.createRow(0); row.setHeightInPoints(20); Cell cell = null; for (int i = 0; i < ITSYSTEM_HEADERS.length; i++) { cell = row.createCell(i, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleTitle); cell.setCellValue(ITSYSTEM_HEADERS[i]); sheet.setColumnWidth(i, ITSYSTEM_HEADERS_WIDTH[i] * 256); } // ?????->?->???? List<ITSystem> systems = new ArrayList<ITSystem>(systService.listSystems(getCurrentCompany(), null, null)); Collections.sort(systems, new Comparator<ITSystem>() { @Override public int compare(ITSystem o1, ITSystem o2) { int ret = o1.getType().ordinal() - o2.getType().ordinal(); if (ret == 0) { ret = Long.compare(o1.getOwner().getId(), o2.getOwner().getId()); if (ret == 0) { ret = Collator.getInstance(java.util.Locale.CHINA).compare(o1.getName(), o2.getName()); } } return ret; } }); // ?? DataFormat df = sheet.getWorkbook().createDataFormat(); // ?10? Font font = sheet.getWorkbook().createFont(); font.setFontName(""); font.setFontHeightInPoints((short) 10); // ?1??????? CellStyle cellStyleString = sheet.getWorkbook().createCellStyle(); cellStyleString.setAlignment(CellStyle.ALIGN_CENTER); cellStyleString.setVerticalAlignment(CellStyle.VERTICAL_CENTER); cellStyleString.setFont(font); cellStyleString.setDataFormat(HSSFDataFormat.getBuiltinFormat("text")); cellStyleString.setWrapText(false); // ?2????(yyyyMMdd)??? CellStyle cellStyleDate = sheet.getWorkbook().createCellStyle(); cellStyleDate.setAlignment(CellStyle.ALIGN_CENTER); cellStyleDate.setVerticalAlignment(CellStyle.VERTICAL_CENTER); cellStyleDate.setFont(font); cellStyleDate.setDataFormat(df.getFormat("yyyyMMdd")); cellStyleDate.setWrapText(false); // ?3??????(#)??? CellStyle cellStyleQuantity = sheet.getWorkbook().createCellStyle(); cellStyleQuantity.setAlignment(CellStyle.ALIGN_CENTER); cellStyleQuantity.setVerticalAlignment(CellStyle.VERTICAL_CENTER); cellStyleQuantity.setFont(font); cellStyleQuantity.setDataFormat(df.getFormat("0")); cellStyleQuantity.setWrapText(false); // ?4?????(#,##0.00_ )??? CellStyle cellStyleCost = sheet.getWorkbook().createCellStyle(); cellStyleCost.setAlignment(CellStyle.ALIGN_RIGHT); cellStyleCost.setVerticalAlignment(CellStyle.VERTICAL_CENTER); cellStyleCost.setFont(font); cellStyleCost.setDataFormat(df.getFormat("#,##0.00_ ")); cellStyleCost.setWrapText(false); // sheet int rowNumber = 1; for (ITSystem system : systems) { // 20 row = sheet.createRow(rowNumber); row.setHeightInPoints(20); // ? Set<SystemBranch> branches = system.getBranches(); SystemBranch localBranch = null; // ?? long localId = 0; if (getCurrentCompany().getLevel() == Company.BRANCH) { localId = getCurrentCompany().getSuperior().getId(); } else { localId = getCurrentCompany().getId(); } BigDecimal cost = new BigDecimal("0.0"); // for (SystemBranch branch : branches) { cost.add(branch.getCost()); if (branch.getCompany().getId() == localId) { localBranch = branch; } } boolean branched = (localBranch != null); // ? boolean owned = system.getOwner().getId() == getCurrentCompany().getId(); // ????? // // cell = row.createCell(0, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(system.getType().getTitle()); // ?? cell = row.createCell(1, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(system.getName()); // cell = row.createCell(2, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(system.getAlias()); // /? cell = row.createCell(3, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(system.getModelOrVersion()); // cell = row.createCell(4, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(system.getBrief()); // ? cell = row.createCell(5, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(ITSYSTEM_sLevel[system.getSecurityLevel()]); // ??? cell = row.createCell(6, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(system.getSecurityCode()); // ? cell = row.createCell(7, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(system.getUsersBrief()); // cell = row.createCell(8, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(null == system.getProvider() ? "" : system.getProvider().getAlias()); // ? cell = row.createCell(9, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(system.getOwner().getAlias()); // cell = row.createCell(10, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(system.getScope().getTitle(system.getOwner().getLevel())); // cell = row.createCell(11, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(system.getDeploy().getTitle()); // cell = row.createCell(12, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(branched ? "" : ""); if (branched) { // ?() cell = row.createCell(13, Cell.CELL_TYPE_NUMERIC); cell.setCellStyle(cellStyleCost); cell.setCellValue(localBranch.getCost().doubleValue()); // ? cell = row.createCell(14, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(localBranch.getStage().getTitle()); // ? cell = row.createCell(15, Cell.CELL_TYPE_NUMERIC); cell.setCellStyle(cellStyleDate); Date ct = localBranch.getConstructedTime(); if (null != ct) { cell.setCellValue(ct); } // ? cell = row.createCell(16, Cell.CELL_TYPE_NUMERIC); cell.setCellStyle(cellStyleDate); Date at = localBranch.getAbandonedTime(); if (null != at) { cell.setCellValue(at); } } // ?? cell = row.createCell(17, Cell.CELL_TYPE_NUMERIC); cell.setCellStyle(cellStyleQuantity); cell.setCellValue(system.getFreeMaintainMonths()); if (owned) { // cell = row.createCell(18, Cell.CELL_TYPE_NUMERIC); cell.setCellStyle(cellStyleQuantity); cell.setCellValue(branches.size()); // ?) cell = row.createCell(19, Cell.CELL_TYPE_NUMERIC); cell.setCellStyle(cellStyleCost); cell.setCellValue(cost.doubleValue()); // ?? cell = row.createCell(20, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(system.getStage().getTitle()); // ? cell = row.createCell(21, Cell.CELL_TYPE_NUMERIC); cell.setCellStyle(cellStyleDate); Date ct = system.getConstructedTime(); if (null != ct) { cell.setCellValue(ct); } // ? cell = row.createCell(22, Cell.CELL_TYPE_NUMERIC); cell.setCellStyle(cellStyleDate); Date at = system.getAbandonedTime(); if (null != at) { cell.setCellValue(at); } } rowNumber++; } fn.append((new SimpleDateFormat("yyyyMMdd")).format(new Date())).append(").xlsx"); String filename = null; if (isIE()) { filename = URLEncoder.encode(fn.toString(), "UTF-8").replaceAll("\\+", "%20"); } else { filename = new String(fn.toString().getBytes("UTF-8"), "iso8859-1"); } response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); response.setHeader("Content-disposition", "attachment; filename=" + filename); BufferedOutputStream out = new BufferedOutputStream(response.getOutputStream(), 4096); wb.write(out); wb.close(); out.close(); return null; }
From source file:com.jfinal.ext.kit.PoiKit.java
License:Apache License
public Workbook export() { if (headers == null) { System.out.println("headers can not be null"); return null; }// w ww .ja va2 s . c om if (columns == null) { System.out.println("columns can not be null"); return null; } if (cellWidth < 0) { System.out.println("cellWidth < 0"); return null; } Workbook wb; wb = new HSSFWorkbook(); if (data.length > 1) { for (List<?> item : data) { if (item.size() >= MAX_ROWS) { System.out .println("Invalid data size (" + item.size() + ") outside allowable range (0..65535)"); } } } else if (data[0].size() > MAX_ROWS) { data = dice(data[0], MAX_ROWS).toArray(new List<?>[] {}); } if (data.length == 0) { return wb; } for (int i = 0; i < data.length; i++) { Sheet sheet = wb.createSheet(sheetName + (i == 0 ? "" : (i + 1))); Row row; Cell cell; if (headers.length > 0) { row = sheet.createRow(0); if (headerRow <= 0) { headerRow = HEADER_ROW; } headerRow = Math.min(headerRow, MAX_ROWS); for (int h = 0, lenH = headers.length; h < lenH; h++) { if (cellWidth > 0) { sheet.setColumnWidth(h, cellWidth); } cell = row.createCell(h); cell.setCellValue(headers[h]); } } for (int j = 0, len = data[i].size(); j < len; j++) { row = sheet.createRow(j + headerRow); Object obj = data[i].get(j); if (obj == null) { continue; } if (obj instanceof Map) { processAsMap(columns, row, obj); } else if (obj instanceof Model) { processAsModel(columns, row, obj); } else if (obj instanceof Record) { processAsRecord(columns, row, obj); } } } return wb; }
From source file:com.kafeidev.test.BusinessPlan.java
License:Apache License
@Test public static void main(String[] args) throws Exception { Workbook wb;// w ww . j a v a2 s. c om if (args.length > 0 && args[0].equals("-xls")) wb = new HSSFWorkbook(); else wb = new XSSFWorkbook(); Map<String, CellStyle> styles = createStyles(wb); Sheet sheet = wb.createSheet("Business Plan"); //turn off gridlines sheet.setDisplayGridlines(false); sheet.setPrintGridlines(false); sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); PrintSetup printSetup = sheet.getPrintSetup(); printSetup.setLandscape(true); //the following three statements are required only for HSSF sheet.setAutobreaks(true); printSetup.setFitHeight((short) 1); printSetup.setFitWidth((short) 1); //the header row: centered text in 48pt font Row headerRow = sheet.createRow(0); headerRow.setHeightInPoints(12.75f); for (int i = 0; i < titles.length; i++) { Cell cell = headerRow.createCell(i); cell.setCellValue(titles[i]); cell.setCellStyle(styles.get("header")); } //columns for 11 weeks starting from 9-Nov Calendar calendar = Calendar.getInstance(); int year = calendar.get(Calendar.YEAR); // { // String inputDate = "2010-Nov-04 01:32:27"; // Date date = new SimpleDateFormat("yyyy-MMM-dd HH:mm:ss", new Locale("en,EN")).parse(inputDate); // String str= new SimpleDateFormat("dd.MMM.yyyy HH:mm:ss").format(date); // System.out.println("data:"+str); // // } calendar.setTime(fmt.parse("19-Nov")); calendar.set(Calendar.YEAR, year); for (int i = 0; i < 11; i++) { Cell cell = headerRow.createCell(titles.length + i); cell.setCellValue(calendar); cell.setCellStyle(styles.get("header_date")); calendar.roll(Calendar.WEEK_OF_YEAR, true); } //freeze the first row sheet.createFreezePane(0, 1); Row row; Cell cell; int rownum = 1; for (int i = 0; i < data.length; i++, rownum++) { row = sheet.createRow(rownum); if (data[i] == null) continue; for (int j = 0; j < data[i].length; j++) { cell = row.createCell(j); String styleName; boolean isHeader = i == 0 || data[i - 1] == null; switch (j) { case 0: if (isHeader) { styleName = "cell_b"; cell.setCellValue(Double.parseDouble(data[i][j])); } else { styleName = "cell_normal"; cell.setCellValue(data[i][j]); } break; case 1: if (isHeader) { styleName = i == 0 ? "cell_h" : "cell_bb"; } else { styleName = "cell_indented"; } cell.setCellValue(data[i][j]); break; case 2: styleName = isHeader ? "cell_b" : "cell_normal"; cell.setCellValue(data[i][j]); break; case 3: styleName = isHeader ? "cell_b_centered" : "cell_normal_centered"; cell.setCellValue(Integer.parseInt(data[i][j])); break; case 4: { calendar.setTime(fmt.parse(data[i][j])); calendar.set(Calendar.YEAR, year); cell.setCellValue(calendar); styleName = isHeader ? "cell_b_date" : "cell_normal_date"; break; } case 5: { int r = rownum + 1; String fmla = "IF(AND(D" + r + ",E" + r + "),E" + r + "+D" + r + ",\"\")"; cell.setCellFormula(fmla); styleName = isHeader ? "cell_bg" : "cell_g"; break; } default: styleName = data[i][j] != null ? "cell_blue" : "cell_normal"; } cell.setCellStyle(styles.get(styleName)); } } //group rows for each phase, row numbers are 0-based sheet.groupRow(4, 6); sheet.groupRow(9, 13); sheet.groupRow(16, 18); //set column widths, the width is measured in units of 1/256th of a character width sheet.setColumnWidth(0, 256 * 6); sheet.setColumnWidth(1, 256 * 33); sheet.setColumnWidth(2, 256 * 20); sheet.setZoom(3, 4); // Write the output to a file String file = "businessplan.xls"; if (wb instanceof XSSFWorkbook) file += "x"; FileOutputStream out = new FileOutputStream(file); wb.write(out); out.close(); }
From source file:com.linus.excel.poi.AligningCells.java
License:Apache License
public static void main(String[] args) throws IOException { Workbook wb = new XSSFWorkbook(); //or new HSSFWorkbook(); Sheet sheet = wb.createSheet(); Row row = sheet.createRow((short) 2); row.setHeightInPoints(30);//from w w w. j ava 2s . com for (int i = 0; i < 8; i++) { //column width is set in units of 1/256th of a character width sheet.setColumnWidth(i, 256 * 15); } createCell(wb, row, (short) 0, CellStyle.ALIGN_CENTER, CellStyle.VERTICAL_BOTTOM); createCell(wb, row, (short) 1, CellStyle.ALIGN_CENTER_SELECTION, CellStyle.VERTICAL_BOTTOM); createCell(wb, row, (short) 2, CellStyle.ALIGN_FILL, CellStyle.VERTICAL_CENTER); createCell(wb, row, (short) 3, CellStyle.ALIGN_GENERAL, CellStyle.VERTICAL_CENTER); createCell(wb, row, (short) 4, CellStyle.ALIGN_JUSTIFY, CellStyle.VERTICAL_JUSTIFY); createCell(wb, row, (short) 5, CellStyle.ALIGN_LEFT, CellStyle.VERTICAL_TOP); createCell(wb, row, (short) 6, CellStyle.ALIGN_RIGHT, CellStyle.VERTICAL_TOP); // Write the output to a file FileOutputStream fileOut = new FileOutputStream("excel/ss-example-align.xlsx"); wb.write(fileOut); fileOut.close(); wb.close(); }
From source file:com.lufs.java.apache.poi.example.CalendarDemo.java
License:Apache License
public static void main(String[] args) throws Exception { Calendar calendar = Calendar.getInstance(); boolean xlsx = true; for (String arg : args) { if (arg.charAt(0) == '-') { xlsx = arg.equals("-xlsx"); } else {/* w ww .jav a 2 s . c o m*/ calendar.set(Calendar.YEAR, Integer.parseInt(arg)); } } int year = calendar.get(Calendar.YEAR); try (Workbook wb = xlsx ? new XSSFWorkbook() : new HSSFWorkbook()) { Map<String, CellStyle> styles = createStyles(wb); for (int month = 0; month < 12; month++) { calendar.set(Calendar.MONTH, month); calendar.set(Calendar.DAY_OF_MONTH, 1); //create a sheet for each month Sheet sheet = wb.createSheet(months[month]); //turn off gridlines sheet.setDisplayGridlines(false); sheet.setPrintGridlines(false); sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); PrintSetup printSetup = sheet.getPrintSetup(); printSetup.setLandscape(true); //the following three statements are required only for HSSF sheet.setAutobreaks(true); printSetup.setFitHeight((short) 1); printSetup.setFitWidth((short) 1); //the header row: centered text in 48pt font Row headerRow = sheet.createRow(0); headerRow.setHeightInPoints(80); Cell titleCell = headerRow.createCell(0); titleCell.setCellValue(months[month] + " " + year); titleCell.setCellStyle(styles.get("title")); sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$N$1")); //header with month titles Row monthRow = sheet.createRow(1); for (int i = 0; i < days.length; i++) { //set column widths, the width is measured in units of 1/256th of a character width sheet.setColumnWidth(i * 2, 5 * 256); //the column is 5 characters wide sheet.setColumnWidth(i * 2 + 1, 13 * 256); //the column is 13 characters wide sheet.addMergedRegion(new CellRangeAddress(1, 1, i * 2, i * 2 + 1)); Cell monthCell = monthRow.createCell(i * 2); monthCell.setCellValue(days[i]); monthCell.setCellStyle(styles.get("month")); } int cnt = 1, day = 1; int rownum = 2; for (int j = 0; j < 6; j++) { Row row = sheet.createRow(rownum++); row.setHeightInPoints(100); for (int i = 0; i < days.length; i++) { Cell dayCell_1 = row.createCell(i * 2); Cell dayCell_2 = row.createCell(i * 2 + 1); int day_of_week = calendar.get(Calendar.DAY_OF_WEEK); if (cnt >= day_of_week && calendar.get(Calendar.MONTH) == month) { dayCell_1.setCellValue(day); calendar.set(Calendar.DAY_OF_MONTH, ++day); if (i == 0 || i == days.length - 1) { dayCell_1.setCellStyle(styles.get("weekend_left")); dayCell_2.setCellStyle(styles.get("weekend_right")); } else { dayCell_1.setCellStyle(styles.get("workday_left")); dayCell_2.setCellStyle(styles.get("workday_right")); } } else { dayCell_1.setCellStyle(styles.get("grey_left")); dayCell_2.setCellStyle(styles.get("grey_right")); } cnt++; } if (calendar.get(Calendar.MONTH) > month) break; } } // Write the output to a file String file = "calendar.xls"; if (wb instanceof XSSFWorkbook) file += "x"; try (FileOutputStream out = new FileOutputStream(file)) { wb.write(out); } } }
From source file:com.lushapp.common.excel.ExportExcel.java
License:Apache License
private void exportExcelInUserModel(String title, Class<T> pojoClass, Collection<T> dataSet, OutputStream out) { try {//from w ww. j a v a2 s . c o m // ?? if (dataSet == null || dataSet.size() == 0) { throw new Exception("??"); } if (title == null || out == null || pojoClass == null) { throw new Exception("???"); } // Workbook 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>(); // Field fileds[] = pojoClass.getDeclaredFields(); // ??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) { StringBuffer getConvertMethodName = new StringBuffer("get"); getConvertMethodName.append(fieldname.substring(0, 1).toUpperCase()); getConvertMethodName.append(fieldname.substring(1)); getConvertMethodName.append("Convert"); Method getConvertMethod = pojoClass.getMethod(getConvertMethodName.toString(), new Class[] {}); convertMethod.put(getMethodName.toString(), getConvertMethod); } } } int index = 0; // Row row = sheet.createRow(index); 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); } // ? 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); 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()); } } workbook.write(out); } catch (Exception e) { e.printStackTrace(); } }