Search...
FAQs
Subscribe
Pie
FAQs
Recent topics
Flagged topics
Hot topics
Best topics
Search...
Search Coderanch
Advance search
Google search
Register / Login
Mahantesh Halakatti
Greenhorn
+ Follow
1
Posts
0
Threads
0
Cows
since Apr 14, 2015
Merit badge:
grant badges
For More
Cows and Likes
Cows
Total received
0
In last 30 days
0
Total given
0
Likes
Total received
0
Received in last 30 days
0
Total given
0
Given in last 30 days
0
Forums and Threads
Scavenger Hunt
Ranch Hand Scavenger Hunt
Number Posts (1/100)
Number Threads Started (0/100)
Number Cows Received (0/5)
Number Likes Received (0/10)
Number Likes Granted (0/20)
Set bumper stickers in profile (0/3)
Report a post to the moderators (0/1)
Edit a wiki page (0/1)
Create a post with an image (0/2)
Greenhorn Scavenger Hunt
First Post
Number Posts (1/10)
Number Threads Started (0/10)
Number Likes Received (0/3)
Number Likes Granted (0/3)
Set bumper stickers in profile (0/1)
Set signature in profile
Search for a post/thread (0/3)
Set a watch on a thread
Save thread as a bookmark
Create a post with an image (0/1)
Recent posts by Mahantesh Halakatti
Copying a sheet from an excel file to another excel file using Apache POI
*******Sample Client Code for Util class which copies multiple sheets from different XLS files and create a single XLS file with the all the sheets in it.******
I just added client code to invoke the Util.class
import java.io.File; import java.io.FileInputStream; import java.io.FileNotFoundException; import java.io.FileOutputStream; import java.io.IOException; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; import java.util.Set; import java.util.TreeSet; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.util.CellRangeAddress; public final class Util { public static void main(String[] args) { mergeFiles(); } public static void mergeFiles() { try { List<String> testFileNames = new ArrayList<String>(); testFileNames.add("c:\\File1.xls"); testFileNames.add("c:\\File2.xls"); testFileNames.add("c:\\File3.xls"); HSSFWorkbook destinationWorkbook = new HSSFWorkbook(); for (String testFileName : testFileNames) { File sourceFile = new File(testFileName); if (sourceFile.exists()) { System.out.println("\n\nStart executing : " + sourceFile.getAbsolutePath()); FileInputStream excelSourceTestFile = new FileInputStream(sourceFile); HSSFWorkbook sourceWorkbook = new HSSFWorkbook(excelSourceTestFile); HSSFSheet sourceSheet = sourceWorkbook.getSheet(testFileName); HSSFSheet destinationSheet = destinationWorkbook.createSheet(testFileName); copySheets(destinationSheet, sourceSheet); writeToFile(destinationWorkbook, destinationSheet); } else { System.out.println("File doesn't exists: " + testFileName); } } } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } catch (ClassNotFoundException e) { e.printStackTrace(); } } private static String destinationFile = "c:\\Merged_File.xls"; private static void writeToFile(HSSFWorkbook workbook, HSSFSheet sheet) { if (workbook != null && sheet.getPhysicalNumberOfRows() > 0) { try { FileOutputStream out = new FileOutputStream(new File(destinationFile)); workbook.write(out); out.close(); System.out.println(destinationFile + " is written successfully.."); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } } } /** * DEFAULT CONSTRUCTOR. */ private Util() { } /** * @param newSheet * the sheet to create from the copy. * @param sheet * the sheet to copy. */ public static void copySheets(HSSFSheet newSheet, HSSFSheet sheet) { copySheets(newSheet, sheet, true); } /** * @param newSheet * the sheet to create from the copy. * @param sheet * the sheet to copy. * @param copyStyle * true copy the style. */ public static void copySheets(HSSFSheet newSheet, HSSFSheet sheet, boolean copyStyle) { int maxColumnNum = 0; Map<Integer, HSSFCellStyle> styleMap = (copyStyle) ? new HashMap<Integer, HSSFCellStyle>() : null; for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) { HSSFRow srcRow = sheet.getRow(i); HSSFRow destRow = newSheet.createRow(i); if (srcRow != null) { // System.out.println("srcRow = " + srcRow.toString()); ExcelFileMergeUtil.copyRow(sheet, newSheet, srcRow, destRow, styleMap); if (srcRow.getLastCellNum() > maxColumnNum) { maxColumnNum = srcRow.getLastCellNum(); } } } for (int i = 0; i <= maxColumnNum; i++) { newSheet.setColumnWidth(i, sheet.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, HSSFSheet destSheet, HSSFRow srcRow, HSSFRow 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); // old cell HSSFCell newCell = destRow.getCell(j); // new cell if (oldCell != null) { if (newCell == null) { newCell = destRow.createCell(j); } copyCell(oldCell, newCell, styleMap); // System.out.println("row num: " + srcRow.getRowNum() + " , col: " + (short) oldCell.getColumnIndex()); try { CellRangeAddress mergedRegion = getMergedRegion(srcSheet, srcRow.getRowNum(), (short) oldCell .getColumnIndex()); // System.out.println("mergedRegion: " + mergedRegion); 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)) { // System.out.println("Adding new region"); mergedRegions.add(wrapper); destSheet.addMergedRegion(wrapper.range); } } } catch (Exception e) { e.printStackTrace(); } } } } /** * @param oldCell * @param newCell * @param styleMap */ public static void copyCell(HSSFCell oldCell, HSSFCell newCell, Map<Integer, HSSFCellStyle> styleMap) { if (styleMap != null) { if (oldCell.getSheet().getWorkbook() == newCell.getSheet().getWorkbook()) { newCell.setCellStyle(oldCell.getCellStyle()); } else { int stHashCode = oldCell.getCellStyle().hashCode(); HSSFCellStyle newCellStyle = styleMap.get(stHashCode); if (newCellStyle == null) { newCellStyle = newCell.getSheet().getWorkbook().createCellStyle(); newCellStyle.cloneStyleFrom(oldCell.getCellStyle()); styleMap.put(stHashCode, newCellStyle); } newCell.setCellStyle(newCellStyle); } } switch (oldCell.getCellType()) { case HSSFCell.CELL_TYPE_STRING: newCell.setCellValue(oldCell.getStringCellValue()); // System.out.println("oldCell = " + oldCell.getStringCellValue()); // System.out.println("newCell = " + newCell.getStringCellValue()); break; case HSSFCell.CELL_TYPE_NUMERIC: newCell.setCellValue(oldCell.getNumericCellValue()); // System.out.println("oldCell = " + oldCell.getNumericCellValue()); // System.out.println("newCell = " + newCell.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; } } /** * * @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); } }
show more
8 years ago
Other Open Source Projects