Java tutorial
package com.jeans.iservlet.action.asset; import java.io.BufferedOutputStream; import java.io.IOException; import java.net.URLEncoder; import java.text.SimpleDateFormat; import java.util.Date; import java.util.List; import javax.servlet.http.HttpServletResponse; import org.apache.commons.lang3.StringUtils; import org.apache.poi.hssf.usermodel.HSSFDataFormat; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.DataFormat; import org.apache.poi.ss.usermodel.Font; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.apache.struts2.ServletActionContext; import org.apache.struts2.convention.annotation.Action; import org.springframework.beans.factory.annotation.Autowired; import com.jeans.iservlet.action.TinyAction; import com.jeans.iservlet.model.AssetConstants; import com.jeans.iservlet.model.asset.Asset; import com.jeans.iservlet.model.asset.Hardware; import com.jeans.iservlet.model.asset.Software; import com.jeans.iservlet.model.hr.Company; import com.jeans.iservlet.model.hr.Employee; import com.jeans.iservlet.service.asset.AssetService; /** * ?ExcelAction * * @author majorli * */ public class AssetExportAction extends TinyAction { private AssetService service; @Autowired public void setService(AssetService service) { this.service = service; } private String type; private String filename; public String getType() { return type; } public void setType(String type) { this.type = type; } public String getFilename() { return filename; } public void setFilename(String filename) { this.filename = filename; } private boolean isIE() { String ua = ServletActionContext.getRequest().getHeader("user-agent").toUpperCase(); if ((ua != null) && (ua.indexOf("MSIE") != -1 || ua.indexOf("TRIDENT") != -1)) { return true; } else { return false; } } private void export(HttpServletResponse resp) throws IOException { StringBuilder fn = new StringBuilder(getCurrentCompany().getName()); Workbook wb = new XSSFWorkbook(); if ("_hard".equals(type)) { fn.append(" - ?("); generateSheet(wb.createSheet(AssetConstants.getAssetCatalogName(AssetConstants.NETWORK_EQUIPMENT)), AssetConstants.NETWORK_EQUIPMENT); generateSheet(wb.createSheet(AssetConstants.getAssetCatalogName(AssetConstants.SECURITY_EQUIPMENT)), AssetConstants.SECURITY_EQUIPMENT); generateSheet(wb.createSheet(AssetConstants.getAssetCatalogName(AssetConstants.SERVER_EQUIPMENT)), AssetConstants.SERVER_EQUIPMENT); generateSheet(wb.createSheet(AssetConstants.getAssetCatalogName(AssetConstants.STORAGE_EQUIPMENT)), AssetConstants.STORAGE_EQUIPMENT); generateSheet( wb.createSheet(AssetConstants.getAssetCatalogName(AssetConstants.INFRASTRUCTURE_EQUIPMENT)), AssetConstants.INFRASTRUCTURE_EQUIPMENT); generateSheet(wb.createSheet(AssetConstants.getAssetCatalogName(AssetConstants.TERMINATOR_EQUIPMENT)), AssetConstants.TERMINATOR_EQUIPMENT); generateSheet(wb.createSheet(AssetConstants.getAssetCatalogName(AssetConstants.MOBILE_EQUIPMENT)), AssetConstants.MOBILE_EQUIPMENT); generateSheet(wb.createSheet(AssetConstants.getAssetCatalogName(AssetConstants.PRINTER_EQUIPMENT)), AssetConstants.PRINTER_EQUIPMENT); generateSheet(wb.createSheet(AssetConstants.getAssetCatalogName(AssetConstants.OTHER_EQUIPMENT)), AssetConstants.OTHER_EQUIPMENT); } else if ("_soft".equals(type)) { fn.append(" - ?("); generateSheet( wb.createSheet(AssetConstants.getAssetCatalogName(AssetConstants.OPERATING_SYSTEM_SOFTWARE)), AssetConstants.OPERATING_SYSTEM_SOFTWARE); generateSheet( wb.createSheet(AssetConstants.getAssetCatalogName(AssetConstants.DATABASE_SYSTEM_SOFTWARE)), AssetConstants.DATABASE_SYSTEM_SOFTWARE); generateSheet(wb.createSheet(AssetConstants.getAssetCatalogName(AssetConstants.MIDDLEWARE_SOFTWARE)), AssetConstants.MIDDLEWARE_SOFTWARE); generateSheet( wb.createSheet(AssetConstants.getAssetCatalogName(AssetConstants.STORAGE_SYSTEM_SOFTWARE)), AssetConstants.STORAGE_SYSTEM_SOFTWARE); generateSheet(wb.createSheet(AssetConstants.getAssetCatalogName(AssetConstants.SECURITY_SOFTWARE)), AssetConstants.SECURITY_SOFTWARE); generateSheet(wb.createSheet(AssetConstants.getAssetCatalogName(AssetConstants.OFFICE_SOFTWARE)), AssetConstants.OFFICE_SOFTWARE); generateSheet(wb.createSheet(AssetConstants.getAssetCatalogName(AssetConstants.APPLICATION_SOFTWARE)), AssetConstants.APPLICATION_SOFTWARE); generateSheet(wb.createSheet(AssetConstants.getAssetCatalogName(AssetConstants.OTHER_SOFTWARE)), AssetConstants.OTHER_SOFTWARE); } else { fn.append(" - IT?("); generateSheet(wb.createSheet(AssetConstants.getAssetTypeName(AssetConstants.HARDWARE_ASSET)), AssetConstants.HARDWARE_ASSET); generateSheet(wb.createSheet(AssetConstants.getAssetTypeName(AssetConstants.SOFTWARE_ASSET)), AssetConstants.SOFTWARE_ASSET); } fn.append((new SimpleDateFormat("yyyyMMdd")).format(new Date())).append(").xlsx"); if (isIE()) { filename = URLEncoder.encode(fn.toString(), "UTF-8").replaceAll("\\+", "%20"); } else { filename = new String(fn.toString().getBytes("UTF-8"), "iso8859-1"); } resp.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"); resp.setHeader("Content-disposition", "attachment; filename=" + filename); BufferedOutputStream out = new BufferedOutputStream(resp.getOutputStream(), 4096); wb.write(out); wb.close(); out.close(); } private void generateSheet(Sheet sheet, byte type) { // sheet if (type == AssetConstants.HARDWARE_ASSET) { // generateSheetHeader(sheet, true); } else if (type == AssetConstants.SOFTWARE_ASSET) { // generateSheetHeader(sheet, false); } else if (type >= AssetConstants.NETWORK_EQUIPMENT && type <= AssetConstants.OTHER_EQUIPMENT) { // ? generateSheetHeader(sheet, true); } else if (type >= AssetConstants.OPERATING_SYSTEM_SOFTWARE && type <= AssetConstants.OTHER_SOFTWARE) { // ? generateSheetHeader(sheet, false); } List<Asset> assets = service.loadAssets(getCurrentCompany(), type); int rowNumber = 1; for (Asset asset : assets) { appendRow(sheet, asset, rowNumber++); } } private static final String[] HARDWARE_HEADERS = new String[] { "?", "?", "?", "", "??", "/?", "?", "?", "??", "??", "", "?", "", "", "??", "??", "?", "??", "", "" }; private static final int[] HARDWARE_HEADERS_WIDTH = new int[] { 12, 12, 12, 12, 20, 12, 24, 60, 24, 60, 11, 5, 13, 9, 9, 30, 16, 9, 9, 40 }; private static String[] SOFTWARE_HEADERS = new String[] { "?", "", "??", "", "", "?", "", "?", "", "", "", "??", "??", "" }; private static final int[] SOFTWARE_HEADERS_WIDTH = new int[] { 12, 12, 20, 12, 24, 60, 11, 5, 13, 9, 14, 20, 11, 40 }; private void appendRow(Sheet sheet, Asset asset, int rowNumber) { // 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????(yyyyMM)??? CellStyle cellStyleDate = sheet.getWorkbook().createCellStyle(); cellStyleDate.setAlignment(CellStyle.ALIGN_CENTER); cellStyleDate.setVerticalAlignment(CellStyle.VERTICAL_CENTER); cellStyleDate.setFont(font); cellStyleDate.setDataFormat(df.getFormat("yyyyMM")); cellStyleDate.setWrapText(false); // ?3??????(#)??? CellStyle cellStyleQuantity = sheet.getWorkbook().createCellStyle(); cellStyleQuantity.setAlignment(CellStyle.ALIGN_RIGHT); cellStyleQuantity.setVerticalAlignment(CellStyle.VERTICAL_CENTER); cellStyleQuantity.setFont(font); cellStyleQuantity.setDataFormat(df.getFormat("#")); 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); // 20 Row row = sheet.createRow(rowNumber); row.setHeightInPoints(20); Cell cell = null; if (asset instanceof Hardware) { cell = row.createCell(0, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(((Hardware) asset).getCode()); cell = row.createCell(1, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(((Hardware) asset).getFinancialCode()); cell = row.createCell(2, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); Company company = asset.getCompany(); if (null != company) { cell.setCellValue(company.getAlias()); } cell = row.createCell(3, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(AssetConstants.getAssetCatalogName(asset.getCatalog())); cell = row.createCell(4, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(asset.getName()); cell = row.createCell(5, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(asset.getVendor()); cell = row.createCell(6, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(asset.getModelOrVersion()); cell = row.createCell(7, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(asset.getAssetUsage()); cell = row.createCell(8, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(((Hardware) asset).getSn()); cell = row.createCell(9, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(((Hardware) asset).getConfiguration()); cell = row.createCell(10, Cell.CELL_TYPE_NUMERIC); cell.setCellStyle(cellStyleDate); Date pt = asset.getPurchaseTime(); if (null != pt) { cell.setCellValue(pt); } cell = row.createCell(11, Cell.CELL_TYPE_NUMERIC); cell.setCellStyle(cellStyleQuantity); cell.setCellValue(asset.getQuantity()); cell = row.createCell(12, Cell.CELL_TYPE_NUMERIC); cell.setCellStyle(cellStyleCost); cell.setCellValue(asset.getCost().doubleValue()); cell = row.createCell(13, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(AssetConstants.getAssetStateName(asset.getState())); cell = row.createCell(14, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(AssetConstants.getHardwareWarrantyName(((Hardware) asset).getWarranty())); cell = row.createCell(15, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(((Hardware) asset).getLocation()); cell = row.createCell(16, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(((Hardware) asset).getIp()); cell = row.createCell(17, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(AssetConstants.getHardwareImportanceName(((Hardware) asset).getImportance())); cell = row.createCell(18, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); Employee owner = ((Hardware) asset).getOwner(); if (null != owner) { cell.setCellValue(owner.getName()); } cell = row.createCell(19, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(asset.getComment()); } else if (asset instanceof Software) { cell = row.createCell(0, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); Company company = asset.getCompany(); if (null != company) { cell.setCellValue(company.getAlias()); } cell = row.createCell(1, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(AssetConstants.getAssetCatalogName(asset.getCatalog())); cell = row.createCell(2, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(asset.getName()); cell = row.createCell(3, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(asset.getVendor()); cell = row.createCell(4, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(asset.getModelOrVersion()); cell = row.createCell(5, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(asset.getAssetUsage()); cell = row.createCell(6, Cell.CELL_TYPE_NUMERIC); cell.setCellStyle(cellStyleDate); Date pt = asset.getPurchaseTime(); if (null != pt) { cell.setCellValue(pt); } cell = row.createCell(7, Cell.CELL_TYPE_NUMERIC); cell.setCellStyle(cellStyleQuantity); cell.setCellValue(asset.getQuantity()); cell = row.createCell(8, Cell.CELL_TYPE_NUMERIC); cell.setCellStyle(cellStyleCost); cell.setCellValue(asset.getCost().doubleValue()); cell = row.createCell(9, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(AssetConstants.getAssetStateName(asset.getState())); cell = row.createCell(10, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(AssetConstants.getSoftwareTypeName(((Software) asset).getSoftwareType())); cell = row.createCell(11, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(((Software) asset).getLicense()); cell = row.createCell(12, Cell.CELL_TYPE_NUMERIC); cell.setCellStyle(cellStyleDate); Date et = ((Software) asset).getExpiredTime(); if (null != et) { cell.setCellValue(et); } cell = row.createCell(13, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyleString); cell.setCellValue(asset.getComment()); } } private void generateSheetHeader(Sheet sheet, boolean hardware) { // // ?10?? Font font = sheet.getWorkbook().createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); font.setFontName(""); font.setFontHeightInPoints((short) 10); // ????????? CellStyle cellStyle = sheet.getWorkbook().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; if (hardware) { for (int i = 0; i < 20; i++) { cell = row.createCell(i, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyle); cell.setCellValue(HARDWARE_HEADERS[i]); sheet.setColumnWidth(i, HARDWARE_HEADERS_WIDTH[i] * 256); } } else { for (int i = 0; i < 14; i++) { cell = row.createCell(i, Cell.CELL_TYPE_STRING); cell.setCellStyle(cellStyle); cell.setCellValue(SOFTWARE_HEADERS[i]); sheet.setColumnWidth(i, SOFTWARE_HEADERS_WIDTH[i] * 256); } } } @Action(value = "export-assets") public String exportAssets() throws Exception { if (StringUtils.isBlank(type)) { type = "_all"; } HttpServletResponse resp = ServletActionContext.getResponse(); export(resp); return null; } }