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 Import.Utils; import java.io.FileNotFoundException; import java.io.FileOutputStream; import java.io.IOException; import java.util.HashMap; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFCellStyle; import java.util.Map; import java.util.Set; import java.util.TreeSet; import java.util.logging.Level; import java.util.logging.Logger; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.CreationHelper; import org.apache.poi.ss.usermodel.DataFormat; import org.apache.poi.ss.usermodel.Font; import org.apache.poi.ss.usermodel.IndexedColors; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.xssf.usermodel.XSSFCell; import org.apache.poi.xssf.usermodel.XSSFWorkbook; /** * * @author tanet-t */ public class XSSFConvert { public void convert() throws IOException { Workbook[] wbs = new Workbook[] { new HSSFWorkbook(), new XSSFWorkbook() }; for (int i = 0; i < wbs.length; i++) { Workbook wb = wbs[i]; CreationHelper createHelper = wb.getCreationHelper(); // create a new sheet org.apache.poi.ss.usermodel.Sheet s = wb.createSheet(); // declare a row object reference Row r = null; // declare a cell object reference Cell c = null; // create 2 cell styles CellStyle cs = wb.createCellStyle(); CellStyle cs2 = wb.createCellStyle(); DataFormat df = wb.createDataFormat(); // create 2 fonts objects Font f = wb.createFont(); Font f2 = wb.createFont(); // Set font 1 to 12 point type, blue and bold f.setFontHeightInPoints((short) 12); f.setColor(IndexedColors.RED.getIndex()); f.setBoldweight(Font.BOLDWEIGHT_BOLD); // Set font 2 to 10 point type, red and bold f2.setFontHeightInPoints((short) 10); f2.setColor(IndexedColors.RED.getIndex()); f2.setBoldweight(Font.BOLDWEIGHT_BOLD); // Set cell style and formatting cs.setFont(f); cs.setDataFormat(df.getFormat("#,##0.0")); // Set the other cell style and formatting cs2.setBorderBottom(cs2.BORDER_THIN); cs2.setDataFormat(df.getFormat("text")); cs2.setFont(f2); // Define a few rows for (int rownum = 0; rownum < 30; rownum++) { r = s.createRow(rownum); for (int cellnum = 0; cellnum < 10; cellnum += 2) { c = r.createCell(cellnum); Cell c2 = r.createCell(cellnum + 1); c.setCellValue((double) rownum + (cellnum / 10)); c2.setCellValue(createHelper.createRichTextString("Hello! " + cellnum)); } } // Save String filename = "workbook.xls"; if (wb instanceof XSSFWorkbook) { filename = filename + "x"; } FileOutputStream out = null; try { out = new FileOutputStream(filename); } catch (FileNotFoundException ex) { Logger.getLogger(XSSFConvert.class.getName()).log(Level.SEVERE, null, ex); } wb.write(out); out.close(); } } public static XSSFWorkbook convertWorkbookHSSFToXSSF(HSSFWorkbook source) { XSSFWorkbook retVal = new XSSFWorkbook(); for (int i = 0; i < source.getNumberOfSheets(); i++) { XSSFSheet xssfSheet = retVal.createSheet(); HSSFSheet hssfsheet = source.getSheetAt(i); copySheets(hssfsheet, xssfSheet); } return retVal; } public static void copySheets(HSSFSheet source, XSSFSheet destination) { copySheets(source, destination, true); } /** * @param destination the sheet to create from the copy. * @param the sheet to copy. * @param copyStyle true copy the style. */ public static void copySheets(HSSFSheet source, XSSFSheet destination, boolean copyStyle) { int maxColumnNum = 0; Map<Integer, HSSFCellStyle> styleMap = (copyStyle) ? new HashMap<Integer, HSSFCellStyle>() : null; for (int i = source.getFirstRowNum(); i <= source.getLastRowNum(); i++) { HSSFRow srcRow = source.getRow(i); XSSFRow destRow = destination.createRow(i); if (srcRow != null) { copyRow(source, destination, srcRow, destRow, styleMap); if (srcRow.getLastCellNum() > maxColumnNum) { maxColumnNum = srcRow.getLastCellNum(); } } } for (int i = 0; i <= maxColumnNum; i++) { destination.setColumnWidth(i, source.getColumnWidth(i)); } } /** * @param srcSheet the sheet to copy. * @param destSheet the sheet to create. * @param srcRow the row to copy. * @param destRow the row to create. * @param styleMap - */ public static void copyRow(HSSFSheet srcSheet, XSSFSheet destSheet, HSSFRow srcRow, XSSFRow destRow, Map<Integer, HSSFCellStyle> styleMap) { // manage a list of merged zone in order to not insert two times a // merged zone Set<CellRangeAddressWrapper> mergedRegions = new TreeSet<CellRangeAddressWrapper>(); destRow.setHeight(srcRow.getHeight()); // pour chaque row for (int j = srcRow.getFirstCellNum(); j <= srcRow.getLastCellNum(); j++) { HSSFCell oldCell = srcRow.getCell(j); // ancienne cell XSSFCell newCell = destRow.getCell(j); // new cell if (oldCell != null) { if (newCell == null) { newCell = destRow.createCell(j); } // copy chaque cell copyCell(oldCell, newCell, styleMap); // copy les informations de fusion entre les cellules // System.out.println("row num: " + srcRow.getRowNum() + // " , col: " + (short)oldCell.getColumnIndex()); CellRangeAddress mergedRegion = getMergedRegion(srcSheet, srcRow.getRowNum(), (short) oldCell.getColumnIndex()); if (mergedRegion != null) { // System.out.println("Selected merged region: " + // mergedRegion.toString()); CellRangeAddress newMergedRegion = new CellRangeAddress(mergedRegion.getFirstRow(), mergedRegion.getLastRow(), mergedRegion.getFirstColumn(), mergedRegion.getLastColumn()); // System.out.println("New merged region: " + // newMergedRegion.toString()); CellRangeAddressWrapper wrapper = new CellRangeAddressWrapper(newMergedRegion); if (isNewMergedRegion(wrapper, mergedRegions)) { mergedRegions.add(wrapper); destSheet.addMergedRegion(wrapper.range); } } } } } /** * @param oldCell * @param newCell * @param styleMap */ public static void copyCell(HSSFCell oldCell, XSSFCell newCell, Map<Integer, HSSFCellStyle> styleMap) { if (styleMap != null) { int stHashCode = oldCell.getCellStyle().hashCode(); HSSFCellStyle sourceCellStyle = styleMap.get(stHashCode); XSSFCellStyle destnCellStyle = newCell.getCellStyle(); if (sourceCellStyle == null) { sourceCellStyle = oldCell.getSheet().getWorkbook().createCellStyle(); } destnCellStyle.cloneStyleFrom(oldCell.getCellStyle()); styleMap.put(stHashCode, sourceCellStyle); newCell.setCellStyle(destnCellStyle); } switch (oldCell.getCellType()) { case HSSFCell.CELL_TYPE_STRING: newCell.setCellValue(oldCell.getStringCellValue()); break; case HSSFCell.CELL_TYPE_NUMERIC: newCell.setCellValue(oldCell.getNumericCellValue()); break; case HSSFCell.CELL_TYPE_BLANK: newCell.setCellType(HSSFCell.CELL_TYPE_BLANK); break; case HSSFCell.CELL_TYPE_BOOLEAN: newCell.setCellValue(oldCell.getBooleanCellValue()); break; case HSSFCell.CELL_TYPE_ERROR: newCell.setCellErrorValue(oldCell.getErrorCellValue()); break; case HSSFCell.CELL_TYPE_FORMULA: newCell.setCellFormula(oldCell.getCellFormula()); break; default: break; } } /** * Rcupre les informations de fusion des cellules dans la sheet source * pour les appliquer la sheet destination... Rcupre toutes les zones * merged dans la sheet source et regarde pour chacune d'elle si elle se * trouve dans la current row que nous traitons. Si oui, retourne l'objet * CellRangeAddress. * * @param sheet the sheet containing the data. * @param rowNum the num of the row to copy. * @param cellNum the num of the cell to copy. * @return the CellRangeAddress created. */ public static CellRangeAddress getMergedRegion(HSSFSheet sheet, int rowNum, short cellNum) { for (int i = 0; i < sheet.getNumMergedRegions(); i++) { CellRangeAddress merged = sheet.getMergedRegion(i); if (merged.isInRange(rowNum, cellNum)) { return merged; } } return null; } /** * Check that the merged region has been created in the destination sheet. * * @param newMergedRegion the merged region to copy or not in the * destination sheet. * @param mergedRegions the list containing all the merged region. * @return true if the merged region is already in the list or not. */ private static boolean isNewMergedRegion(CellRangeAddressWrapper newMergedRegion, Set<CellRangeAddressWrapper> mergedRegions) { return !mergedRegions.contains(newMergedRegion); } }