Java tutorial
/* * To change this license header, choose License Headers in Project Properties. * To change this template file, choose Tools | Templates * and open the template in the editor. */ package se.vgregion.alfresco.repo.scripts; import java.io.BufferedReader; import java.io.File; import java.io.InputStream; import java.util.Date; import java.util.HashMap; import java.util.Map; import java.io.Serializable; import java.util.List; import org.alfresco.model.ContentModel; import org.alfresco.repo.content.MimetypeMap; import org.alfresco.repo.model.Repository; import org.alfresco.repo.security.permissions.AccessDeniedException; import org.alfresco.repo.web.scripts.content.ContentGet; import org.alfresco.service.cmr.repository.ContentReader; import org.alfresco.service.cmr.repository.ContentService; import org.alfresco.service.cmr.repository.ContentWriter; import org.alfresco.service.cmr.repository.NodeRef; import org.alfresco.service.cmr.repository.NodeService; import org.alfresco.service.cmr.repository.StoreRef; import org.alfresco.service.cmr.search.SearchService; import org.alfresco.service.namespace.NamespaceService; import org.alfresco.service.namespace.QName; import org.apache.commons.io.FileUtils; import org.apache.commons.io.FilenameUtils; import org.apache.log4j.Logger; import org.json.JSONArray; import org.json.JSONException; import org.json.JSONObject; import org.springframework.extensions.webscripts.Status; import org.springframework.extensions.webscripts.WebScriptException; import org.springframework.extensions.webscripts.WebScriptRequest; import org.springframework.extensions.webscripts.WebScriptResponse; import se.vgregion.alfresco.repo.storage.StorageService; import se.vgregion.alfresco.repo.utils.impl.ServiceUtilsImpl; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import java.io.FileOutputStream; import java.io.FileReader; import java.io.IOException; /** * * @author jimmie */ public class ExportJsonToExcel extends ContentGet { protected ContentService contentService; protected NodeService nodeService; protected NamespaceService namespaceService; protected ServiceUtilsImpl serviceUtils; protected StorageService storageService; protected SearchService searchService; protected Repository repository; protected String filename; protected File file; protected NodeRef tempFileNodeRef; public final String FOLDER_NAME_VGR = "VGR"; private static final Logger LOG = Logger.getLogger(ExportJsonToExcel.class); public final String XPATH_DATA_DICTIONARY = "/app:company_home/app:dictionary"; final String newLine = System.getProperty("line.separator"); @Override public void execute(final WebScriptRequest req, final WebScriptResponse res) throws IOException { final Map<String, String> templateVars = req.getServiceMatch().getTemplateVars(); String storeType = templateVars.get("store_type"); String storeId = templateVars.get("store_id"); String id = templateVars.get("id"); StoreRef storeRef = new StoreRef(storeType, storeId); NodeRef nodeRef = new NodeRef(storeRef, id); NodeRef fileNodeRef = exportJsonToExcel(nodeRef); LOG.debug("Stream file: " + filename); streamContentLocalCopied(req, res, fileNodeRef, true, ContentModel.PROP_CONTENT); nodeService.deleteNode(tempFileNodeRef); } private NodeRef exportJsonToExcel(NodeRef jsonNodeRef) { Date createdDate = (Date) nodeService.getProperty(jsonNodeRef, ContentModel.PROP_CREATED); filename = createdDate.toString(); ContentReader reader = contentService.getReader(jsonNodeRef, ContentModel.PROP_CONTENT); InputStream contentInputStream = reader.getContentInputStream(); String jsonString = convertStreamToString(contentInputStream); char firstChar = jsonString.charAt(0); String firstString = Character.toString(firstChar); // Find out if it is a JSONobject or JSONArray if (firstString.equals("{")) { JSONObject output; try { output = new JSONObject(jsonString); JSONArray externalJsonArray = output.getJSONArray("external"); JSONArray internalJsonArray = output.getJSONArray("internal"); file = new File(filename + ".csv"); String externalString = toString(externalJsonArray); String internalString = toString(internalJsonArray); FileUtils.writeStringToFile(file, "External" + newLine); FileUtils.writeStringToFile(file, externalString + newLine, true); FileUtils.writeStringToFile(file, "Internal" + newLine, true); FileUtils.writeStringToFile(file, internalString, true); } catch (JSONException e) { e.printStackTrace(); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } } else { JSONArray output; try { output = new JSONArray(jsonString); file = new File(filename + ".xls"); String xls = toString(output); FileUtils.writeStringToFile(file, xls); } catch (JSONException e) { e.printStackTrace(); } catch (IOException e) { // TODO Auto-generated catch block e.printStackTrace(); } } File csvToXLSX = csvToXLSX(file); List<NodeRef> selectResult = searchService.selectNodes(repository.getRootHome(), XPATH_DATA_DICTIONARY, null, namespaceService, false); NodeRef dataDictionaryNodeRef = selectResult.get(0); NodeRef vgrNodeRef = nodeService.getChildByName(dataDictionaryNodeRef, ContentModel.ASSOC_CONTAINS, FOLDER_NAME_VGR); tempFileNodeRef = createContentNode(vgrNodeRef, filename + ".xlsx", csvToXLSX); return tempFileNodeRef; } private NodeRef createContentNode(NodeRef parent, String name, File file) { // Create a map to contain the values of the properties of the node Map<QName, Serializable> props = new HashMap<QName, Serializable>(1); props.put(ContentModel.PROP_NAME, name); // use the node service to create a new node NodeRef node = this.nodeService.createNode(parent, ContentModel.ASSOC_CONTAINS, QName.createQName(NamespaceService.CONTENT_MODEL_1_0_URI, name), ContentModel.TYPE_CONTENT, props) .getChildRef(); // Use the content service to set the content onto the newly created node ContentWriter writer = this.contentService.getWriter(node, ContentModel.PROP_CONTENT, true); writer.setMimetype(MimetypeMap.MIMETYPE_EXCEL); writer.setEncoding("UTF-8"); writer.putContent(file); // Return a node reference to the newly created node return node; } private static String convertStreamToString(java.io.InputStream is) { java.util.Scanner s = new java.util.Scanner(is).useDelimiter("\\A"); return s.hasNext() ? s.next() : ""; } private void streamContentLocalCopied(WebScriptRequest req, WebScriptResponse res, NodeRef nodeRef, boolean attach, QName propertyQName) throws IOException { String userAgent = req.getHeader("User-Agent"); userAgent = userAgent != null ? userAgent.toLowerCase() : ""; boolean rfc5987Supported = (userAgent.contains("msie") || userAgent.contains(" trident/") || userAgent.contains(" chrome/") || userAgent.contains(" firefox/")); try { if (attach && rfc5987Supported) { String name = (String) nodeService.getProperty(nodeRef, ContentModel.PROP_NAME); //IE use file extension to get mimetype //So we set correct extension. see MNT-11246 if (userAgent.contains("msie") || userAgent.contains(" trident/")) { String mimeType = contentService.getReader(nodeRef, propertyQName).getMimetype(); // If we have an unknown mime type (usually marked as binary) // it is better to stay with the file extension we currently have // see MNT-14412 if (!mimeType.toLowerCase().equals(MimetypeMap.MIMETYPE_BINARY) && !mimetypeService.getMimetypes(FilenameUtils.getExtension(name)).contains(mimeType)) { name = FilenameUtils.removeExtension(name) + FilenameUtils.EXTENSION_SEPARATOR_STR + mimetypeService.getExtension(mimeType); } } streamContent(req, res, nodeRef, propertyQName, attach, name, null); } else { streamContent(req, res, nodeRef, propertyQName, attach, null, null); } } catch (AccessDeniedException e) { throw new WebScriptException(Status.STATUS_FORBIDDEN, e.getMessage()); } } public File csvToXLSX(File csv) { File xlsx = new File(filename + ".xlsx"); try { XSSFWorkbook workBook = new XSSFWorkbook(); XSSFSheet sheet = workBook.createSheet("sheet1"); String currentLine = null; int RowNum = 0; BufferedReader br = new BufferedReader(new FileReader(csv)); while ((currentLine = br.readLine()) != null) { String str[] = currentLine.split(";"); RowNum++; XSSFRow currentRow = sheet.createRow(RowNum); for (int i = 0; i < str.length; i++) { currentRow.createCell(i).setCellValue(str[i]); } } FileOutputStream fileOutputStream = new FileOutputStream(xlsx); workBook.write(fileOutputStream); fileOutputStream.close(); } catch (Exception ex) { System.out.println(ex.getMessage() + "Exception in try"); } return xlsx; } public static String toString(JSONArray ja) throws JSONException { JSONObject jo = ja.optJSONObject(0); if (jo != null) { JSONArray names = jo.names(); if (names != null) { return rowToString(names) + toString(names, ja); } } return null; } public static String toString(JSONArray names, JSONArray ja) throws JSONException { if (names == null || names.length() == 0) { return null; } StringBuffer sb = new StringBuffer(); for (int i = 0; i < ja.length(); i += 1) { JSONObject jo = ja.optJSONObject(i); if (jo != null) { sb.append(rowToString(jo.toJSONArray(names))); } } return sb.toString(); } /** * Produce a semi-colon delimited text row from a JSONArray. Values containing the semi-colon character will be * quoted. Troublesome characters may be removed. * * @param ja A JSONArray of strings. * @return A string ending in NEWLINE. */ public static String rowToString(JSONArray ja) { StringBuilder sb = new StringBuilder(); for (int i = 0; i < ja.length(); i += 1) { if (i > 0) { sb.append(';'); } Object object = ja.opt(i); if (object != null) { String string = object.toString(); if (string.length() > 0 && (string.indexOf(';') >= 0 || string.indexOf('\n') >= 0 || string.indexOf('\r') >= 0 || string.indexOf(0) >= 0 || string.charAt(0) == '"')) { sb.append('"'); int length = string.length(); for (int j = 0; j < length; j += 1) { char c = string.charAt(j); if (c >= ' ' && c != '"') { sb.append(c); } } sb.append('"'); } else { sb.append(string); } } } sb.append('\n'); return sb.toString(); } public void setContentService(ContentService contentService) { this.contentService = contentService; } public void setNodeService(NodeService nodeService) { this.nodeService = nodeService; } public void setNamespaceService(final NamespaceService namespaceService) { this.namespaceService = namespaceService; } public void setServiceUtils(final ServiceUtilsImpl serviceUtils) { this.serviceUtils = serviceUtils; } public void setStorageService(StorageService storageService) { this.storageService = storageService; } public void setSearchService(SearchService searchService) { this.searchService = searchService; } public void setRepository(Repository repository) { this.repository = repository; } }