Java tutorial
package org.bizbundles.forward; import jxl.*; import org.apache.commons.lang.StringUtils; import org.apache.commons.logging.Log; import org.apache.commons.logging.LogFactory; import org.bizbundles.support.FileNameHelper; import java.io.File; import java.io.FileOutputStream; import java.io.IOException; import java.text.SimpleDateFormat; import java.util.*; /** * Copyright 2007 BizBundle.org * <p/> * Licensed under the Apache License, Version 2.0 (the "License"); * you may not use this file except in compliance with the License. * You may obtain a copy of the License at * <p/> * http://www.apache.org/licenses/LICENSE-2.0 * <p/> * Unless required by applicable law or agreed to in writing, software * distributed under the License is distributed on an "AS IS" BASIS, * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. * See the License for the specific language governing permissions and * limitations under the License. * <p/> * This class will convert an MS Excel Spreadsheet into resource bundles * one for each tab in the excel spreadsheet * * @author Steve Roach * @since: Nov 1, 2007 * 7:17:54 PM * * todo display duplicate keys */ public class ExcelToResourceBundle { private final Log logger = LogFactory.getLog(ExcelToResourceBundle.class); private static final List<String> DATE_FORMAT_LIST = Arrays.asList("dd/mm/yyyy", "None", "Solid"); private static final String PROPERTIES_EXTENSION = ".properties"; private List<Set<String>> listOfKeys = new LinkedList<Set<String>>(); private MetaInfo metaInfo = new MetaInfo(); public void generateResources(File excelFile) { assert excelFile != null : "The excel file passed in was NULL"; assert excelFile.exists() : "file does not exists."; assert excelFile.isFile() : "The excelFile " + excelFile.getName() + " is not a file, it i a directory."; assert excelFile.canRead() : "Unable to read file"; File generated = new File( excelFile.getParent() + File.separator + "generated" + FileNameHelper.getDateTimeString()); generated.mkdir(); try { Workbook workbook = Workbook.getWorkbook(excelFile); String[] sheetNames = workbook.getSheetNames(); if (logger.isInfoEnabled()) { logger.info("Total of " + sheetNames.length + " worksheets found in excel file"); } for (String sheetName : sheetNames) { convertSheetToMap(generated, workbook, sheetName); } workbook.close(); warnMatchingNumberOfKeys(); metaInfo.showKeyInfo(); } catch (Exception e) { throw new RuntimeException(e); } } /** * reads each sheet and store it in a Map in memory * @param generated pointer to generated File object * @param workbook pointer to jxl workbook * @param sheetName the name of the sheet to copy from * @throws IOException exception for IO operations */ private void convertSheetToMap(File generated, Workbook workbook, String sheetName) throws IOException { Sheet sheet = workbook.getSheet(sheetName); Map<String, String> props = new HashMap<String, String>(); int maxRows = sheet.getRows(); for (int i = 0; i < maxRows; i++) { Cell cell1 = sheet.getCell(0, i); Cell cell2 = sheet.getCell(1, i); Cell cell3 = sheet.getCell(2, i); String cell0Text = getCellContent(cell1); String cell1Text = getCellContent(cell2); String cell3Text = getCellContent(cell3); if (!"N".equalsIgnoreCase(cell3Text)) { props.put(cell0Text, cell1Text); } } storeMapAsResourceBundle(generated, sheetName, props); } /** * private method to copy the in memory map into a resource bundle using a Properties class * @param generated pointer to generated File object * @param sheetName the name of the sheet to name the resource bundle with. * @param props the map of all properties * @throws IOException any io operations issues while creating resource bundle */ private void storeMapAsResourceBundle(File generated, String sheetName, Map<String, String> props) throws IOException { Set<String> set = new TreeSet<String>(props.keySet()); listOfKeys.add(set); Properties properties = new Properties(); properties.putAll(props); if (sheetName.endsWith(PROPERTIES_EXTENSION)) { sheetName = StringUtils.remove(sheetName, PROPERTIES_EXTENSION); } properties.store( new FileOutputStream( generated.getAbsolutePath() + File.separator + sheetName + PROPERTIES_EXTENSION), "Generated By org.bizbundles.ExcelToResourceBundle"); metaInfo.add(sheetName, props.keySet()); } private String getCellContent(Cell currCell) { String cellText; if (null != currCell && CellType.DATE.equals(currCell.getType())) { String formatPattern = currCell.getCellFormat().getPattern().getDescription(); if (DATE_FORMAT_LIST.contains(formatPattern)) { TimeZone gmtZone = TimeZone.getTimeZone("GMT"); SimpleDateFormat format = new SimpleDateFormat("MM/dd/yyyy"); format.setTimeZone(gmtZone); DateCell dateCell = (DateCell) currCell; cellText = format.format(dateCell.getDate()); } else { cellText = currCell.getContents(); cellText = cellText.replace('"', ' '); } } else if (null != currCell && CellType.NUMBER.equals(currCell.getType())) { if ((currCell.getCellFormat().getFormat().getFormatString()).startsWith("[<=9999999]")) { String val = StringUtils.remove(currCell.getContents(), "[<=9999999]"); val = StringUtils.remove(val, '-'); cellText = val; } else { cellText = currCell.getContents(); cellText = cellText.replace('"', ' '); } } else { cellText = currCell != null ? currCell.getContents() : ""; cellText = cellText.replace('"', ' '); } //logger.info(cellText); return cellText.trim(); } private void warnMatchingNumberOfKeys() { boolean equals = true; if (listOfKeys.size() == 1) { equals = true; } else { Set<String> set1 = listOfKeys.get(0); Set<String> other; for (int i = 1; i < listOfKeys.size(); i++) { other = listOfKeys.get(i); if (!set1.equals(other)) { equals = false; } } } if (!equals) { logger.warn("There are keys that do not have an equivalent in other sheets"); } } private class MetaInfo { private Map<String, Integer> totalKeysPerSheet = new HashMap<String, Integer>(); private void add(String sheetName, Set<String> keys) { totalKeysPerSheet.put(sheetName, keys.size()); } public Map<String, Integer> getTotalKeysPerSheet() { return totalKeysPerSheet; } private void showKeyInfo() { for (String sheetName : totalKeysPerSheet.keySet()) { logger.info("Sheet " + sheetName + " has " + totalKeysPerSheet.get(sheetName) + " number of resource entries."); } } } }