List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet createRow
@Override public HSSFRow createRow(int rownum)
From source file:com.smi.travel.migration.MainMigrate.java
public static void ExportAgentReport(List repAgent) { UtilityExcelFunction excelFunction = new UtilityExcelFunction(); HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet("Agent"); HSSFCellStyle styleC1 = wb.createCellStyle(); // Set align Text HSSFCellStyle styleC21 = wb.createCellStyle(); styleC21.setAlignment(styleC21.ALIGN_RIGHT); HSSFCellStyle styleC22 = wb.createCellStyle(); styleC22.setAlignment(styleC22.ALIGN_LEFT); HSSFRow row1 = sheet.createRow(0); HSSFCell cellStart = row1.createCell(0); cellStart.setCellValue("Agent Report"); styleC1.setFont(excelFunction.getHeaderFont(wb.createFont())); cellStart.setCellStyle(styleC1);/*from ww w. j a v a2s. c o m*/ sheet.addMergedRegion(CellRangeAddress.valueOf("A1:F1")); // Header Table HSSFCellStyle styleC3Center = wb.createCellStyle(); styleC3Center.setFont(excelFunction.getHeaderTable(wb.createFont())); styleC3Center.setAlignment(styleC3Center.ALIGN_CENTER); styleC3Center.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleC3Center.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleC3Center.setBorderRight(HSSFCellStyle.BORDER_THIN); styleC3Center.setBorderTop(HSSFCellStyle.BORDER_THIN); HSSFRow row2 = sheet.createRow(2); HSSFCell cell20 = row2.createCell(0); cell20.setCellValue("ID"); cell20.setCellStyle(styleC3Center); sheet.autoSizeColumn(0); HSSFCell cell21 = row2.createCell(1); cell21.setCellValue("SYSTEM DATE"); cell21.setCellStyle(styleC3Center); sheet.autoSizeColumn(1); HSSFCell cell22 = row2.createCell(2); cell22.setCellValue("SYSTEM STAFF"); cell22.setCellStyle(styleC3Center); sheet.autoSizeColumn(2); HSSFCell cell23 = row2.createCell(3); cell23.setCellValue("CODE"); cell23.setCellStyle(styleC3Center); sheet.autoSizeColumn(3); HSSFCell cell24 = row2.createCell(4); cell24.setCellValue("NAME"); cell24.setCellStyle(styleC3Center); sheet.autoSizeColumn(4); HSSFCell cell25 = row2.createCell(5); cell25.setCellValue("ADDRESS"); cell25.setCellStyle(styleC3Center); sheet.autoSizeColumn(5); HSSFCell cell26 = row2.createCell(6); cell26.setCellValue("TEL"); cell26.setCellStyle(styleC3Center); sheet.autoSizeColumn(6); HSSFCell cell27 = row2.createCell(7); cell27.setCellValue("FAX"); cell27.setCellStyle(styleC3Center); sheet.autoSizeColumn(7); HSSFCell cell28 = row2.createCell(8); cell28.setCellValue("DESCRIPTION"); cell28.setCellStyle(styleC3Center); sheet.autoSizeColumn(8); HSSFCell cell29 = row2.createCell(9); cell29.setCellValue("NAME T"); cell29.setCellStyle(styleC3Center); sheet.autoSizeColumn(9); HSSFCell cell30 = row2.createCell(10); cell30.setCellValue("DESCRIPTION T"); cell30.setCellStyle(styleC3Center); sheet.autoSizeColumn(10); HSSFCell cell31 = row2.createCell(11); cell31.setCellValue("ADDRESS T"); cell31.setCellStyle(styleC3Center); sheet.autoSizeColumn(11); HSSFCell cell32 = row2.createCell(12); cell32.setCellValue("EMAIL"); cell32.setCellStyle(styleC3Center); sheet.autoSizeColumn(12); HSSFCell cell33 = row2.createCell(13); cell33.setCellValue("WEB"); cell33.setCellStyle(styleC3Center); sheet.autoSizeColumn(13); HSSFCell cell34 = row2.createCell(14); cell34.setCellValue("REMARKS"); cell34.setCellStyle(styleC3Center); sheet.autoSizeColumn(14); HSSFCell cell35 = row2.createCell(15); cell35.setCellValue("WARNING"); cell35.setCellStyle(styleC3Center); sheet.autoSizeColumn(15); HSSFCell cell36 = row2.createCell(16); cell36.setCellValue("REF ID"); cell36.setCellStyle(styleC3Center); sheet.autoSizeColumn(16); HSSFCell cell37 = row2.createCell(17); cell37.setCellValue("BRANCH"); cell37.setCellStyle(styleC3Center); sheet.autoSizeColumn(17); HSSFCell cell38 = row2.createCell(18); cell38.setCellValue("BRANCH NO"); cell38.setCellStyle(styleC3Center); sheet.autoSizeColumn(18); HSSFCell cell39 = row2.createCell(19); cell39.setCellValue("TAX NO"); cell39.setCellStyle(styleC3Center); sheet.autoSizeColumn(19); int count = 3; HSSFDataFormat currency = wb.createDataFormat(); HSSFCellStyle styleC23 = wb.createCellStyle(); styleC23.setAlignment(styleC23.ALIGN_CENTER); styleC23.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleC23.setBorderRight(HSSFCellStyle.BORDER_THIN); HSSFCellStyle styleC24 = wb.createCellStyle(); styleC24.setAlignment(styleC24.ALIGN_LEFT); styleC24.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleC24.setBorderRight(HSSFCellStyle.BORDER_THIN); HSSFCellStyle styleC25 = wb.createCellStyle(); styleC25.setAlignment(styleC25.ALIGN_RIGHT); styleC25.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleC25.setBorderRight(HSSFCellStyle.BORDER_THIN); styleC25.setDataFormat(currency.getFormat("#,##0.00")); for (int i = 0; i < repAgent.size(); i++) { ReportAgent data = (ReportAgent) repAgent.get(i); HSSFRow row = sheet.createRow(count + i); HSSFCell cell0 = row.createCell(0); cell0.setCellValue(data.getId()); cell0.setCellStyle(styleC23); HSSFCell cell1 = row.createCell(1); cell1.setCellValue(String.valueOf(data.getSystemdate())); cell1.setCellStyle(styleC23); HSSFCell cell13 = row.createCell(2); cell13.setCellValue(data.getSystemstaff()); cell13.setCellStyle(styleC24); HSSFCell cell2 = row.createCell(3); cell2.setCellValue(data.getCode()); cell2.setCellStyle(styleC24); HSSFCell cell3 = row.createCell(4); cell3.setCellValue(data.getName()); cell3.setCellStyle(styleC24); HSSFCell cell4 = row.createCell(5); cell4.setCellValue(data.getAddress().trim()); cell4.setCellStyle(styleC24); HSSFCell cell5 = row.createCell(6); cell5.setCellValue(data.getTel()); cell5.setCellStyle(styleC24); HSSFCell cell6 = row.createCell(7); cell6.setCellValue(data.getFax()); cell6.setCellStyle(styleC24); HSSFCell cell7 = row.createCell(8); cell7.setCellValue(data.getDescription().trim()); cell7.setCellStyle(styleC24); HSSFCell cell8 = row.createCell(9); cell8.setCellValue(data.getNameT()); cell8.setCellStyle(styleC24); HSSFCell cell9 = row.createCell(10); cell9.setCellValue(data.getDescriptionT().trim()); cell9.setCellStyle(styleC24); HSSFCell cell10 = row.createCell(11); cell10.setCellValue(data.getAddressT().trim()); cell10.setCellStyle(styleC24); HSSFCell cell11 = row.createCell(12); cell11.setCellValue(data.getEmail()); cell11.setCellStyle(styleC24); HSSFCell cell12 = row.createCell(13); cell12.setCellValue(data.getWeb()); cell12.setCellStyle(styleC24); HSSFCell cell14 = row.createCell(14); cell14.setCellValue(data.getRemarks()); cell14.setCellStyle(styleC24); HSSFCell cell15 = row.createCell(15); cell15.setCellValue(data.getWarning()); cell15.setCellStyle(styleC24); HSSFCell cell16 = row.createCell(16); cell16.setCellValue(data.getRefid()); cell16.setCellStyle(styleC24); HSSFCell cell17 = row.createCell(17); cell17.setCellValue(data.getBranch()); cell17.setCellStyle(styleC24); HSSFCell cell18 = row.createCell(18); cell18.setCellValue(data.getBranchno()); cell18.setCellStyle(styleC24); HSSFCell cell19 = row.createCell(19); cell19.setCellValue(data.getTaxno()); cell19.setCellStyle(styleC23); } for (int j = 0; j < 20; j++) { sheet.autoSizeColumn(j); } for (int k = 4; k < 21; k++) { if (k != 6 && k != 7 && k != 12 && k != 13 && k < 16) { sheet.setColumnWidth(k, 256 * 35);//27 } else { sheet.setColumnWidth(k, 256 * 20);//27 } } exportFileExcel("AgentReport", wb); }
From source file:com.smi.travel.migration.MainMigrate.java
public static void ExportStaffReport(List repStaff) { UtilityExcelFunction excelFunction = new UtilityExcelFunction(); HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet("Staff"); HSSFCellStyle styleC1 = wb.createCellStyle(); // Set align Text HSSFCellStyle styleC21 = wb.createCellStyle(); styleC21.setAlignment(styleC21.ALIGN_RIGHT); HSSFCellStyle styleC22 = wb.createCellStyle(); styleC22.setAlignment(styleC22.ALIGN_LEFT); HSSFRow row1 = sheet.createRow(0); HSSFCell cellStart = row1.createCell(0); cellStart.setCellValue("Staff Report"); styleC1.setFont(excelFunction.getHeaderFont(wb.createFont())); cellStart.setCellStyle(styleC1);//from w ww.j a v a 2 s . com sheet.addMergedRegion(CellRangeAddress.valueOf("A1:F1")); // Header Table HSSFCellStyle styleC3Center = wb.createCellStyle(); styleC3Center.setFont(excelFunction.getHeaderTable(wb.createFont())); styleC3Center.setAlignment(styleC3Center.ALIGN_CENTER); styleC3Center.setBorderBottom(HSSFCellStyle.BORDER_THIN); styleC3Center.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleC3Center.setBorderRight(HSSFCellStyle.BORDER_THIN); styleC3Center.setBorderTop(HSSFCellStyle.BORDER_THIN); HSSFRow row2 = sheet.createRow(2); HSSFCell cell20 = row2.createCell(0); cell20.setCellValue("ID"); cell20.setCellStyle(styleC3Center); sheet.autoSizeColumn(0); HSSFCell cell21 = row2.createCell(1); cell21.setCellValue("SYSTEM DATE"); cell21.setCellStyle(styleC3Center); sheet.autoSizeColumn(1); HSSFCell cell22 = row2.createCell(2); cell22.setCellValue("SYSTEM STAFF"); cell22.setCellStyle(styleC3Center); sheet.autoSizeColumn(2); HSSFCell cell23 = row2.createCell(3); cell23.setCellValue("CODE"); cell23.setCellStyle(styleC3Center); sheet.autoSizeColumn(3); HSSFCell cell24 = row2.createCell(4); cell24.setCellValue("NAME"); cell24.setCellStyle(styleC3Center); sheet.autoSizeColumn(4); HSSFCell cell25 = row2.createCell(5); cell25.setCellValue("PASSWD"); cell25.setCellStyle(styleC3Center); sheet.autoSizeColumn(5); HSSFCell cell26 = row2.createCell(6); cell26.setCellValue("POSITION"); cell26.setCellStyle(styleC3Center); sheet.autoSizeColumn(6); HSSFCell cell27 = row2.createCell(7); cell27.setCellValue("DEPARTMENT ID"); cell27.setCellStyle(styleC3Center); sheet.autoSizeColumn(7); HSSFCell cell28 = row2.createCell(8); cell28.setCellValue("TEL"); cell28.setCellStyle(styleC3Center); sheet.autoSizeColumn(8); HSSFCell cell29 = row2.createCell(9); cell29.setCellValue("CAR"); cell29.setCellStyle(styleC3Center); sheet.autoSizeColumn(9); HSSFCell cell30 = row2.createCell(10); cell30.setCellValue("STATUS"); cell30.setCellStyle(styleC3Center); sheet.autoSizeColumn(10); HSSFCell cell31 = row2.createCell(11); cell31.setCellValue("SIGNATURE"); cell31.setCellStyle(styleC3Center); sheet.autoSizeColumn(11); int count = 3; HSSFDataFormat currency = wb.createDataFormat(); HSSFCellStyle styleC23 = wb.createCellStyle(); styleC23.setAlignment(styleC23.ALIGN_CENTER); styleC23.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleC23.setBorderRight(HSSFCellStyle.BORDER_THIN); HSSFCellStyle styleC24 = wb.createCellStyle(); styleC24.setAlignment(styleC24.ALIGN_LEFT); styleC24.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleC24.setBorderRight(HSSFCellStyle.BORDER_THIN); HSSFCellStyle styleC25 = wb.createCellStyle(); styleC25.setAlignment(styleC25.ALIGN_RIGHT); styleC25.setBorderLeft(HSSFCellStyle.BORDER_THIN); styleC25.setBorderRight(HSSFCellStyle.BORDER_THIN); styleC25.setDataFormat(currency.getFormat("#,##0.00")); for (int i = 0; i < repStaff.size(); i++) { ReportStaff data = (ReportStaff) repStaff.get(i); HSSFRow row = sheet.createRow(count + i); HSSFCell cell0 = row.createCell(0); cell0.setCellValue(data.getId()); cell0.setCellStyle(styleC23); HSSFCell cell1 = row.createCell(1); cell1.setCellValue(String.valueOf(data.getSystemdate())); cell1.setCellStyle(styleC23); HSSFCell cell13 = row.createCell(2); cell13.setCellValue(data.getSystemstaff()); cell13.setCellStyle(styleC24); HSSFCell cell2 = row.createCell(3); cell2.setCellValue(data.getCode()); cell2.setCellStyle(styleC24); HSSFCell cell3 = row.createCell(4); cell3.setCellValue(data.getName()); cell3.setCellStyle(styleC24); HSSFCell cell4 = row.createCell(5); cell4.setCellValue(data.getPasswd()); cell4.setCellStyle(styleC24); HSSFCell cell5 = row.createCell(6); cell5.setCellValue(data.getPosition()); cell5.setCellStyle(styleC24); HSSFCell cell6 = row.createCell(7); cell6.setCellValue(data.getDepartmentid()); cell6.setCellStyle(styleC24); HSSFCell cell7 = row.createCell(8); cell7.setCellValue(data.getTel()); cell7.setCellStyle(styleC24); HSSFCell cell8 = row.createCell(9); cell8.setCellValue(data.getCar()); cell8.setCellStyle(styleC24); HSSFCell cell9 = row.createCell(10); cell9.setCellValue(data.getStatus()); cell9.setCellStyle(styleC24); HSSFCell cell10 = row.createCell(11); cell10.setCellValue(data.getSignature()); cell10.setCellStyle(styleC24); } for (int j = 0; j < 12; j++) { sheet.autoSizeColumn(j); } // sheet.setColumnWidth(8, 256*40);//27 // sheet.setColumnWidth(10, 256*40);//27 exportFileExcel("StaffReport", wb); }
From source file:com.softtek.mdm.web.admin.IndexController.java
private void exportExcel(String sheetName, String[] headNames, List<OrganizationModel> lists, HttpServletResponse response) {//from w w w.j av a 2 s. com OutputStream out = null; try { HSSFWorkbook workbook = new HSSFWorkbook(); // HSSFSheet sheet = workbook.createSheet(sheetName); // // HSSFRow rowm = sheet.createRow(0); HSSFCell cellTiltle = rowm.createCell(0); //sheet??getColumnTopStyle()/getStyle()? - ? - ? HSSFCellStyle columnTopStyle = CommUtil.getColumnTopStyle(workbook);//?? sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, (headNames.length - 1))); cellTiltle.setCellStyle(columnTopStyle); cellTiltle.setCellValue(sheetName); // int columnNum = headNames.length; HSSFRow rowRowName = sheet.createRow(2); // 2?() // sheet? for (int n = 0; n < columnNum; n++) { HSSFCell cellRowName = rowRowName.createCell(n); //? cellRowName.setCellType(HSSFCell.CELL_TYPE_STRING); //?? HSSFRichTextString text = new HSSFRichTextString(headNames[n]); cellRowName.setCellValue(text); //? cellRowName.setCellStyle(columnTopStyle); //?? } //?sheet? HSSFDataFormat format = workbook.createDataFormat(); short formatDate = format.getFormat("yyyy-MM-dd hh:mm:ss"); for (int i = 0; i < lists.size(); i++) { HSSFRow row = sheet.createRow(i + 3);// OrganizationModel obj = lists.get(i);//??? /*row.createCell(0).setCellValue(obj.getOrgType());*/ row.createCell(0).setCellValue(obj.getName()); row.createCell(1).setCellValue(obj.getCreateName()); row.createCell(2).setCellValue(obj.getTotalUsers() == null ? 0 : obj.getTotalUsers()); row.createCell(3).setCellValue(obj.getTotalDevices() == null ? 0 : obj.getTotalDevices()); row.createCell(4).setCellValue(obj.getLicenseCount() == null ? 0 : obj.getLicenseCount()); row.createCell(5).setCellValue(obj.getUseUsers() == null ? 0 : obj.getUseUsers()); HSSFCell cell = row.createCell(6); cell.setCellValue(obj.getCreateTime()); HSSFCellStyle cellStyle = workbook.createCellStyle(); cellStyle.setDataFormat(formatDate); cell.setCellStyle(cellStyle); } //?? for (int colNum = 0; colNum < columnNum; colNum++) { int columnWidth = sheet.getColumnWidth(colNum) / 256; for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) { HSSFRow currentRow; //? currentRow = (sheet.getRow(rowNum) == null) ? sheet.createRow(rowNum) : sheet.getRow(rowNum); if (currentRow.getCell(colNum) != null) { HSSFCell currentCell = currentRow.getCell(colNum); if (currentCell.getCellType() == HSSFCell.CELL_TYPE_STRING) { int length = currentCell.getStringCellValue().getBytes().length; if (columnWidth < length) { columnWidth = length; } } } } sheet.setColumnWidth(colNum, (colNum == 0) ? (columnWidth * 256) : ((columnWidth + 10) * 256)); } if (workbook != null) { try { String fileName = "Excel-" + String.valueOf(System.currentTimeMillis()).substring(4, 13) + ".xls"; String headStr = "attachment; filename=\"" + fileName + "\""; response.setContentType("application/octet-stream"); response.setHeader("Content-Disposition", headStr); out = response.getOutputStream(); workbook.write(out); } catch (IOException e) { logger.error(e.getMessage()); } finally { if (out != null) { out.close(); } } } } catch (Exception e) { logger.error(e.getMessage()); } }
From source file:com.square.core.util.poi.DocumentXls.java
License:Open Source License
/** * Recupere la ligne courante. * @return la ligne */ private HSSFRow recupererLigne(HSSFSheet page) { return page.createRow(row++); }
From source file:com.ssic.education.provider.controller.ProSupplierController.java
@RequestMapping(value = "/excel") @ResponseBody/* www . ja va 2s. c o m*/ public ModelAndView exportExcel(SupplierDto supplierDto, HttpServletRequest request, HttpServletResponse response) { SessionInfo info = (SessionInfo) request.getSession().getAttribute(ConfigUtil.SESSIONINFONAME); if (info == null) { return null; } supplierDto.setReceiverId(info.getSupplierId()); SimpleDateFormat sdf = new SimpleDateFormat("yyyy.M.d"); Date date = new Date(); String filename = Tools.date2Str(date, "yyyyMMddHHmmss"); HSSFSheet sheet; HSSFCell cell; response.setContentType("application/octet-stream"); response.setHeader("Content-Disposition", "attachment;filename=" + filename + ".xls"); Workbook workbook = new HSSFWorkbook(); sheet = (HSSFSheet) workbook.createSheet(""); try { List<String> titles = new ArrayList<String>(); titles.add("????"); titles.add("???"); // titles.add("????"); // titles.add("??????"); titles.add("????"); titles.add("???"); // titles.add("?"); // titles.add("???"); titles.add("?"); titles.add("?"); int len = titles.size(); HSSFCellStyle headerStyle = (HSSFCellStyle) workbook.createCellStyle(); // ? headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); headerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); HSSFFont headerFont = (HSSFFont) workbook.createFont(); // headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); headerFont.setFontHeightInPoints((short) 11); headerStyle.setFont(headerFont); short width = 20, height = 25 * 20; sheet.setDefaultColumnWidth(width); HSSFRow sheetRow = sheet.createRow(0); for (int i = 0; i < len; i++) { // String title = titles.get(i); cell = sheetRow.createCell(i); cell.setCellStyle(headerStyle); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellValue(title); } sheet.getRow(0).setHeight(height); HSSFCellStyle contentStyle = (HSSFCellStyle) workbook.createCellStyle(); // ? contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); DataGrid dg = supplierService.findProSupplier(supplierDto, null); List<SupplierDto> expList = dg.getRows(); List<PageData> varList = new ArrayList<PageData>(); if (!CollectionUtils.isEmpty(expList)) { for (int i = 0; i < expList.size(); i++) { PageData vpd = new PageData(); vpd.put("var1", expList.get(i).getSupplierName()); vpd.put("var2", expList.get(i).getAddress()); // vpd.put("var3", expList.get(i).getFoodServiceCode()); // vpd.put("var4", expList.get(i).getFoodBusinessCode()); vpd.put("var3", expList.get(i).getFoodCirculationCode()); vpd.put("var4", expList.get(i).getFoodProduceCode()); // vpd.put("var7", expList.get(i).getBusinessLicense()); // vpd.put("var8", expList.get(i).getSupplierCode()); vpd.put("var5", expList.get(i).getCorporation()); vpd.put("var6", expList.get(i).getContactWay()); varList.add(vpd); } } for (int i = 0; i < varList.size(); i++) { HSSFRow row = sheet.createRow(i + 1); PageData vpd = varList.get(i); for (int j = 0; j < len; j++) { String varstr = vpd.getString("var" + (j + 1)) != null ? vpd.getString("var" + (j + 1)) : ""; cell = row.createCell(j); HSSFCellStyle cellStyle2 = (HSSFCellStyle) workbook.createCellStyle(); HSSFDataFormat format = (HSSFDataFormat) workbook.createDataFormat(); cellStyle2.setDataFormat(format.getFormat("@")); cell.setCellStyle(cellStyle2); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellValue(varstr); } } OutputStream os = response.getOutputStream(); workbook.write(os); os.flush(); os.close(); } catch (Exception e) { } return null; }
From source file:com.ssic.education.provider.controller.WaresController.java
@RequestMapping(value = "/excel") @ResponseBody//w w w.j a va 2 s.c o m public ModelAndView exportExcel(ProWaresDto proWaresDto, HttpServletRequest request, HttpServletResponse response) { SessionInfo info = (SessionInfo) request.getSession().getAttribute(ConfigUtil.SESSIONINFONAME); if (info == null) { return null; } proWaresDto.setSupplierId(info.getSupplierId()); SimpleDateFormat sdf = new SimpleDateFormat("yyyy.M.d"); Date date = new Date(); String filename = Tools.date2Str(date, "yyyyMMddHHmmss"); HSSFSheet sheet; HSSFCell cell; response.setContentType("application/octet-stream"); response.setHeader("Content-Disposition", "attachment;filename=" + filename + ".xls"); Workbook workbook = new HSSFWorkbook(); sheet = (HSSFSheet) workbook.createSheet(""); try { List<String> titles = new ArrayList<String>(); titles.add("??"); titles.add("???"); titles.add(""); titles.add("?"); titles.add("?"); // titles.add("??"); // titles.add("???"); // titles.add("??"); titles.add("?"); titles.add("???"); titles.add(""); int len = titles.size(); HSSFCellStyle headerStyle = (HSSFCellStyle) workbook.createCellStyle(); // ? headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); headerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); HSSFFont headerFont = (HSSFFont) workbook.createFont(); // headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); headerFont.setFontHeightInPoints((short) 11); headerStyle.setFont(headerFont); short width = 20, height = 25 * 20; sheet.setDefaultColumnWidth(width); HSSFRow sheetRow = sheet.createRow(0); for (int i = 0; i < len; i++) { // String title = titles.get(i); cell = sheetRow.createCell(i); cell.setCellStyle(headerStyle); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellValue(title); } sheet.getRow(0).setHeight(height); HSSFCellStyle contentStyle = (HSSFCellStyle) workbook.createCellStyle(); // ? contentStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); List<ProWaresDto> expList = waresService.findAllWares(proWaresDto, null); List<PageData> varList = new ArrayList<PageData>(); if (!CollectionUtils.isEmpty(expList)) { for (int i = 0; i < expList.size(); i++) { PageData vpd = new PageData(); vpd.put("var1", expList.get(i).getWaresName()); vpd.put("var2", expList.get(i).getAmountUnit()); vpd.put("var3", expList.get(i).getSpec()); vpd.put("var4", ProductClass.getName(expList.get(i).getWaresType())); vpd.put("var5", expList.get(i).getManufacturer()); // vpd.put("var5", expList.get(i).getEnName()); // vpd.put("var6", expList.get(i).getBarCode()); // vpd.put("var7", expList.get(i).getCustomCode()); vpd.put("var6", expList.get(i).getShelfLife()); vpd.put("var7", expList.get(i).getUnit()); vpd.put("var8", expList.get(i).getPlace()); varList.add(vpd); } } for (int i = 0; i < varList.size(); i++) { HSSFRow row = sheet.createRow(i + 1); PageData vpd = varList.get(i); for (int j = 0; j < len; j++) { String varstr = vpd.getString("var" + (j + 1)) != null ? vpd.getString("var" + (j + 1)) : ""; cell = row.createCell(j); HSSFCellStyle cellStyle2 = (HSSFCellStyle) workbook.createCellStyle(); HSSFDataFormat format = (HSSFDataFormat) workbook.createDataFormat(); cellStyle2.setDataFormat(format.getFormat("@")); cell.setCellStyle(cellStyle2); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellValue(varstr); } } OutputStream os = response.getOutputStream(); workbook.write(os); os.flush(); os.close(); } catch (Exception e) { } return null; }
From source file:com.sx.finance.OldYahooFinanceJFrame.java
public void writeExcelFile(ArrayList<List<Object>> listDataRaw) { // webbookExcel HSSFWorkbook wb = new HSSFWorkbook(); // webbooksheet,Excelsheet HSSFSheet sheet = wb.createSheet("Data"); // sheet0,poiExcelshort HSSFRow row = sheet.createRow((int) 0); // // w w w . j av a2 s . com HSSFCellStyle style = wb.createCellStyle(); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // for (int i = 0; i < title.length; i++) { HSSFCell cell = row.createCell(i); cell.setCellValue(title[i]); cell.setCellStyle(style); } for (int i = 0; i < listDataRaw.size(); i++) { row = sheet.createRow(i + 1); for (int j = 0; j < listDataRaw.get(i).size(); j++) { Object cellData = listDataRaw.get(i).get(j); if (cellData instanceof Long) { row.createCell(j).setCellValue(((Long) cellData).longValue()); } else if (cellData instanceof Double) { row.createCell(j).setCellValue(((Double) cellData).doubleValue()); } else if (cellData instanceof Date) { row.createCell(j).setCellValue((Date) cellData); } else if (cellData instanceof Integer) { row.createCell(j).setCellValue(((Integer) cellData).intValue()); } else { row.createCell(j).setCellValue((String) cellData); } } } try { FileOutputStream fout = new FileOutputStream("D:/work/myprojects/yahooFinance/output/data.xls"); wb.write(fout); fout.close(); } catch (Exception e) { e.printStackTrace(); } }
From source file:com.taobao.itest.dbunit.dataset.excel.XlsDataSetWriter.java
License:Open Source License
/** * Write the specified dataset to the specified Excel document. *///from w ww . j a v a 2 s . com public void write(IDataSet dataSet, OutputStream out) throws IOException, DataSetException { logger.debug("write(dataSet={}, out={}) - start", dataSet, out); HSSFWorkbook workbook = new HSSFWorkbook(); this.dateCellStyle = createDateCellStyle(workbook); int index = 0; ITableIterator iterator = dataSet.iterator(); while (iterator.next()) { // create the table i.e. sheet ITable table = iterator.getTable(); ITableMetaData metaData = table.getTableMetaData(); HSSFSheet sheet = workbook.createSheet(metaData.getTableName()); // write table metadata i.e. first row in sheet workbook.setSheetName(index, metaData.getTableName()); HSSFRow headerRow = sheet.createRow(0); Column[] columns = metaData.getColumns(); for (int j = 0; j < columns.length; j++) { Column column = columns[j]; HSSFCell cell = headerRow.createCell(j); cell.setCellValue(new HSSFRichTextString(column.getColumnName())); } // write table data for (int j = 0; j < table.getRowCount(); j++) { HSSFRow row = sheet.createRow(j + 1); for (int k = 0; k < columns.length; k++) { Column column = columns[k]; Object value = table.getValue(j, column.getColumnName()); if (value != null) { HSSFCell cell = row.createCell(k); if (value instanceof Date) { setDateCell(cell, (Date) value, workbook); } else if (value instanceof BigDecimal) { setNumericCell(cell, (BigDecimal) value, workbook); } else if (value instanceof Long) { setDateCell(cell, new Date(((Long) value).longValue()), workbook); } else { cell.setCellValue(new HSSFRichTextString(DataType.asString(value))); } } } } index++; } // write xls document workbook.write(out); out.flush(); }
From source file:com.technoetic.xplanner.importer.spreadsheet.SpreadsheetStoryWriter.java
License:Open Source License
/** * Write story./*from w w w . j a v a 2s. co m*/ * * @param sheet * the sheet * @param spreadsheetStory * the spreadsheet story * @param i * the i */ private void writeStory(final HSSFSheet sheet, final SpreadsheetStory spreadsheetStory, final int i) { final HSSFRow row = sheet.createRow(i); this.setCellValue(row, CookbookFields.STORY_END_DATE_COL, spreadsheetStory.getEndDate()); this.setCellValue(row, CookbookFields.TITLE_COL, spreadsheetStory.getTitle()); this.setCellValue(row, CookbookFields.STATUS_COL, spreadsheetStory.getStatus()); this.setCellValue(row, CookbookFields.STORY_PRIORITY_COL, spreadsheetStory.getPriority()); this.setCellValue(row, CookbookFields.ESTIMATE_NUMBER_COL, spreadsheetStory.getEstimate()); }
From source file:com.technoetic.xplanner.importer.spreadsheet.SpreadsheetStoryWriter.java
License:Open Source License
/** * Write header.//from ww w. j ava 2s. c o m * * @param sheet * the sheet */ private void writeHeader(final HSSFSheet sheet) { final HSSFRow row = sheet.createRow(0); this.setCellValue(row, CookbookFields.STORY_END_DATE_COL, SpreadsheetStoryWriter.END_DATE_HEADER); this.setCellValue(row, CookbookFields.TITLE_COL, SpreadsheetStoryWriter.TITLE_HEADER); this.setCellValue(row, CookbookFields.STATUS_COL, SpreadsheetStoryWriter.STATUS_HEADER); this.setCellValue(row, CookbookFields.STORY_PRIORITY_COL, SpreadsheetStoryWriter.PRIORITY_HEADER); this.setCellValue(row, CookbookFields.ESTIMATE_NUMBER_COL, SpreadsheetStoryWriter.ESTIMATE_HEADER); }