Sometimes while doing programming, I need to download some jars, but the network settings blocks it. That is really embarrassing So I came with a java program which downloads file for me using third party server.  To implement this program you need to download apache-commons-io library. This program is simple servlet.

Program flow

  1. Get file URL from user
  2. Send it to proxy servlet
  3. Servlet will open input stream for that URL and write content to servlet output stream
  4. User will get file downloaded

Get file URL from user

String fileURL = request.getParameter("url");

Open Input Stream 

URL url = new URL(fileURL);
InputStream is = url.openStream();

Write Into Output stream

ServletOutputStream out = response.getOutputStream();
out.write(IOUtils.toByteArray(is));
out.flush();
out.close()

Complete Servlet Code

import java.io.IOException;
import java.io.InputStream;
import java.net.MalformedURLException;
import java.net.URL;
import javax.servlet.ServletException;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.io.IOUtils;


/**
 * Servlet implementation class ProxyFile
 */
public class ProxyFile extends HttpServlet {
    private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public ProxyFile() {
        super();
    }

    /**
     * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
     */
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        // get file name from parameter
        String fileURL = request.getParameter("url");
        URL url;
        InputStream is = null;
        try {
            // get name of the file
            String[] names = fileURL.split("/");
            String fileName = names[names.length - 1];
            
            // open URL stream
            url = new URL(fileURL);
            is = url.openStream();
            
            // set response header
            response.setContentType("application/download");
            response.setHeader("Content-Transfer-Encoding", "binary");
            response.setHeader("Content-Disposition","attachment; filename=\"" + fileName+"\"");
            
            // write into servlet output stream  
            ServletOutputStream out = response.getOutputStream();
            out.write(IOUtils.toByteArray(is));
            out.flush();
            out.close();
        } catch (MalformedURLException mue) {
            mue.printStackTrace();
        } catch (IOException ioe) {
            ioe.printStackTrace();
        } finally {
            try {
                if (is != null)
                    is.close();
            } catch (IOException ioe) {
            }
        }

    }

    /**
     * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
     */
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        // forward to doGet
        doGet(request, response);
    }

}

web.xml Configuration

  <servlet>
    <description></description>
    <display-name>ProxyFile</display-name>
    <servlet-name>ProxyFile</servlet-name>
    <servlet-class>demo.sl.ProxyFile</servlet-class>
  </servlet>
  <servlet-mapping>
    <servlet-name>ProxyFile</servlet-name>
    <url-pattern>/ProxyFile</url-pattern>
  </servlet-mapping>
Read More
When you provide an option to select an item in list of items then you must show the difference between selected items and un-selected items. To show this difference, changing in the background color or text color of selected item is necessary. The user must recognize the items that are select-able, To implement this feature we need to change the background-color or text-color on mouse over. Lets do this with example.

CSS

Here selectedItem class will give background color to selected item
.item {
  padding:12px;
  border:1px solid #ccc;
  cursor:pointer;
}

.hoverItem:hover {
  background-color:#eeeeee;
}

.selectedItem {
  background-color:#dfdfdf;
}

Markup

record.classes holds the array of classes.  You can learn about adding and removing classes from here
<html ng-app="" ng-controller="myCtrl">
.....
<div ng-click="selectItem($index)" ng-class="record.classes" ng-repeat="record in records track by $index">
  {{record.value}}
</div>
.....
</html>

Script

The logic is simple. When user clicks on an item we will add selected class and remove hover class
function myCtrl($scope) {
    // initialize data
    $scope.records = [];
    for(var i=0;i<5;i++) {
       $scope.records.push(
         { 
           value: "record "+i,
           classes : ["item", "hoverItem"]
         }
       );     
    }

    // when user selected item
    $scope.selectItem = function(index) {
       for(var i=0;i<$scope.records.length;i++) {
          if(index === i) {
            $scope.records[i].classes.pop('hoverItem');
            $scope.records[i].classes.push('selectedItem');
          } else {
            $scope.records[i].classes.pop('selectedItem');
            $scope.records[i].classes.push('hoverItem');
          }
       } 
    };
}
Read More
While we write code for a project, we used to keep all utility functions in one file and access them from all other files. Here in AngularJS we need to write those utility functions in a Provider or Factory or Service. We will go with example. Find HTML markup below for example

Markup

Here angular app is myApp and controller is MyCtrl and hellos is variable contains the utility function output
<html ng-app="myApp" ng-controller="MyCtrl">
....
<div>
    {{hellos}}
</div>
....
</html>

Utility Functions In Service 

When you’re using Service, AngularJS instantiates it behind the scenes with the ‘new’ keyword. Because of that, you’ll add properties to ‘this’ and the service will return ‘this’. When you pass the service into your controller, those properties on ‘this’ will now be available on that controller through your service. So write your utility functions using 'this', those functions will be available to controllers. Take a look at below code 
var myApp = angular.module('myApp', []);

//service style, the simplest one
myApp.service('mathUtilFromService', function() {
    this.findSquare = function(num) {
        return num * num;
    };
});

function MyCtrl($scope, mathUtilFromService) {
      $scope.hellos = mathUtilFromService.findSquare(4);
}
You can assign values to variable in service, the utility functions can make use of those variables.
var myApp = angular.module('myApp', []);

//service style, probably the simplest one
myApp.service('mathUtilFromService', function() {
    this.factor=0;
    this.multiplyWithFactor = function(num) {
        return this.factor * num;
    };
});

function MyCtrl($scope, mathUtilFromService) {
      mathUtilFromService.factor = 2;
      $scope.hellos = mathUtilFromService.findSquare(4);
}

Utility Functions In Factory

When you’re using a Factory you create an object, add properties to it, then return that same object. When you pass this factory into your controller, those properties on the object will now be available in that controller through your factory. 
var myApp = angular.module('myApp', []);

//factory style, more involved but more sophisticated
myApp.factory('mathUtilFromFactory', function() {
    return {
        findSquare: function(num) {
            return num * num;
        }
    };
});

function MyCtrl($scope, mathUtilFromFactory) {
      $scope.hellos = mathUtilFromFactory.findSquare(4);
}

Utility Functions In Provider

Providers are the only service you can pass into your .config() function. Use a provider when you want to provide module-wide configuration for your service object before making it available.
var myApp = angular.module('myApp', []);

//provider style, full blown, configurable version     
myApp.provider('mathUtil', function() {
    this.$get = function() {
        return {
             findSquare: function(num) {
                return num * num;
             }
        }
    };
});

function MyCtrl($scope, mathUtil) {
      $scope.hellos = mathUtil.findSquare(4);
}
Provider can be configured at the time of loading the application
var myApp = angular.module('myApp', []);

//provider style, full blown, configurable version     
myApp.provider('mathUtil', function() {
    this.factor=0;
    this.$get = function() {
        var factor = this.factor;
        return {
             multiplyWithFactor : function(num) {
                return factor * num;
             }
        }
    };
    this.setFactor =function(num) {
       this.factor = num;
    };
});

//hey, we can configure a provider!            
myApp.config(function(mathUtilProvider){
    mathUtilProvider.setFactor(2);
});

function MyCtrl($scope, mathUtil) {
      $scope.hellos = mathUtil.multiplyWithFactor(4);
}
Read More
When you have plenty of records to show, you don't need to show them all at a time. We should show them based on user interest. Here I implemented the program to show the records with " Load more records " feature.

Markup

Here We are using limitTo filter control the number of records to display. Whenever user clicks on below button, record limit will be increased and more records will be displayed.
<div ng-repeat="record in records | limitTo:recordLimit">
  {{record}}
</div>
<button ng-show="records.length > recordLimit" ng-click="recordLimit = recordLimit + 5">show more records</button>

Script

Here we are initiating the records and record limit
$scope.records = [];
$scope.recordLimit = 5;
for(var i=0;i<20;i++) {
   $scope.records.push('record '+i);     
}
Read More
To implement this program you need to download Apache POI library and have it in your build path (copy POI jars to lib folder).

Program Flow

  1. Get input excel files and get workbook instances
  2. Get sheets from those workbooks
  3. Get rows from each sheets with same index and compare them
  4. Get columns from two selected above rows and compare them

Example

Observe below input files data. Here Row 3 - Column 2 is not matching so the two excel files are not equal. 

Expected Output 

Comparing Row 0
       Cell 0 - Equal
       Cell 1 - Equal
       Cell 2 - Equal
       Cell 3 - Equal
Row 0 - Equal


Comparing Row 1
       Cell 0 - Equal
       Cell 1 - Equal
       Cell 2 - Equal
       Cell 3 - Equal
Row 1 - Equal


Comparing Row 2
       Cell 0 - Equal
       Cell 1 - Equal
       Cell 2 - Equal
       Cell 3 - Equal
Row 2 - Equal


Comparing Row 3
       Cell 0 - Equal
       Cell 1 - Equal
Row 3 - Not Equal


The two excel sheets are Not Equal
       Cell 2 - NOt Equal

Java Code

import java.io.File;
import java.io.FileInputStream;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class CompareExcel {

    public static void main(String[] args) {
        try {
            // get input excel files
            FileInputStream excellFile1 = new FileInputStream(new File(
                    "C:\\sheet1.xlsx"));
            FileInputStream excellFile2 = new FileInputStream(new File(
                    "C:\\sheet2.xlsx"));

            // Create Workbook instance holding reference to .xlsx file
            XSSFWorkbook workbook1 = new XSSFWorkbook(excellFile1);
            XSSFWorkbook workbook2 = new XSSFWorkbook(excellFile2);

            // Get first/desired sheet from the workbook
            XSSFSheet sheet1 = workbook1.getSheetAt(0);
            XSSFSheet sheet2 = workbook2.getSheetAt(0);

            // Compare sheets
            if(compareTwoSheets(sheet1, sheet2)) {
                System.out.println("\n\nThe two excel sheets are Equal");
            } else {
                System.out.println("\n\nThe two excel sheets are Not Equal");
            }
            
            //close files
            excellFile1.close();
            excellFile2.close();

        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    
    // Compare Two Sheets
    public static boolean compareTwoSheets(XSSFSheet sheet1, XSSFSheet sheet2) {
        int firstRow1 = sheet1.getFirstRowNum();
        int lastRow1 = sheet1.getLastRowNum();
        boolean equalSheets = true;
        for(int i=firstRow1; i <= lastRow1; i++) {
            
            System.out.println("\n\nComparing Row "+i);
            
            XSSFRow row1 = sheet1.getRow(i);
            XSSFRow row2 = sheet2.getRow(i);
            if(!compareTwoRows(row1, row2)) {
                equalSheets = false;
                System.out.println("Row "+i+" - Not Equal");
                break;
            } else {
                System.out.println("Row "+i+" - Equal");
            }
        }
        return equalSheets;
    }

    // Compare Two Rows
    public static boolean compareTwoRows(XSSFRow row1, XSSFRow row2) {
        if((row1 == null) && (row2 == null)) {
            return true;
        } else if((row1 == null) || (row2 == null)) {
            return false;
        }
        
        int firstCell1 = row1.getFirstCellNum();
        int lastCell1 = row1.getLastCellNum();
        boolean equalRows = true;
        
        // Compare all cells in a row
        for(int i=firstCell1; i <= lastCell1; i++) {
            XSSFCell cell1 = row1.getCell(i);
            XSSFCell cell2 = row2.getCell(i);
            if(!compareTwoCells(cell1, cell2)) {
                equalRows = false;
                System.err.println("       Cell "+i+" - NOt Equal");
                break;
            } else {
                System.out.println("       Cell "+i+" - Equal");
            }
        }
        return equalRows;
    }

    // Compare Two Cells
    public static boolean compareTwoCells(XSSFCell cell1, XSSFCell cell2) {
        if((cell1 == null) && (cell2 == null)) {
            return true;
        } else if((cell1 == null) || (cell2 == null)) {
            return false;
        }
        
        boolean equalCells = false;
        int type1 = cell1.getCellType();
        int type2 = cell2.getCellType();
        if (type1 == type2) {
            if (cell1.getCellStyle().equals(cell2.getCellStyle())) {
                // Compare cells based on its type
                switch (cell1.getCellType()) {
                case HSSFCell.CELL_TYPE_FORMULA:
                    if (cell1.getCellFormula().equals(cell2.getCellFormula())) {
                        equalCells = true;
                    }
                    break;
                case HSSFCell.CELL_TYPE_NUMERIC:
                    if (cell1.getNumericCellValue() == cell2
                            .getNumericCellValue()) {
                        equalCells = true;
                    }
                    break;
                case HSSFCell.CELL_TYPE_STRING:
                    if (cell1.getStringCellValue().equals(cell2
                            .getStringCellValue())) {
                        equalCells = true;
                    }
                    break;
                case HSSFCell.CELL_TYPE_BLANK:
                    if (cell2.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
                        equalCells = true;
                    }
                    break;
                case HSSFCell.CELL_TYPE_BOOLEAN:
                    if (cell1.getBooleanCellValue() == cell2
                            .getBooleanCellValue()) {
                        equalCells = true;
                    }
                    break;
                case HSSFCell.CELL_TYPE_ERROR:
                    if (cell1.getErrorCellValue() == cell2.getErrorCellValue()) {
                        equalCells = true;
                    }
                    break;
                default:
                    if (cell1.getStringCellValue().equals(
                            cell2.getStringCellValue())) {
                        equalCells = true;
                    }
                    break;
                }
            } else {
                return false;
            }
        } else {
            return false;
        }
        return equalCells;
    }
}
Read More
Pagination with more number of page buttons looks good for desktop. For mobile screens smaller number of page buttons looks good . If you want to show same page in desktop and mobile then this program will be helpful.
Here I am using Bootstrap Angular UI  for Pagination component. Get to know about Bootstrap UI from here. The attribute max-size which effects the number of pages in bootstrap pagination component. Here we are going to change max-size attribute value based on window size which gives responsive behavior.

Markup 

Here observe maxSize attribute. We are going to change this maxSize attribute value on window resize
<html ng-app="ui.bootstrap.demo" ng-controller="PaginationDemoCtrl">
. . .
<script src="https://ajax.googleapis.com/ajax/libs/angularjs/1.2.15/angular.min.js"></script>
<script src="/js/ui-bootstrap-tpls-0.12.1.min.js"></script>
<link type='text/css' rel='stylesheet' href='/css/bootstrap.min.css'/>
. . .
 <pagination items-per-page="itemsPerPage" total-items="totalItems" ng-model="currentPage" max-size="maxSize" class="pagination-sm" boundary-links="true">
</pagination>
. . .

Script

// Initiate Angular App
angular.module('ui.bootstrap.demo', ['ui.bootstrap']);

// Initiate Controller
angular.module('ui.bootstrap.demo').controller('PaginationDemoCtrl',
function ($scope,  $window) {
        // Pagination variables
        $scope.totalItems = 100;
        $scope.currentPage = 2;
        $scope.maxSize = 20;
        $scope.itemsPerPage = 3;
        $scope.windowWidth = '';
        
         // Window resize event
         var w = angular.element($window);
         w.bind('resize', function () {
             
             // Get window width
             $scope.windowWidth = "innerWidth" in window ? window.innerWidth : document.documentElement.offsetWidth;
             
             // Change maxSize based on window width
             if($scope.windowWidth > 1000) {
                 $scope.maxSize = 20;        
             } else if($scope.windowWidth > 800) {
                 $scope.maxSize = 15;
             } else if($scope.windowWidth > 600) {
                 $scope.maxSize = 8;
             } else if($scope.windowWidth > 400) {
                 $scope.maxSize = 5;
             } else {
                 $scope.maxSize = 2;
             }
             $scope.$apply();
         });
});
Read More
If you re-arrange the columns in Excel File of more than 30 columns then you will feel this program will be so much helpful to make process automate. I have re-arranged the columns in Excel file of 40 columns then I wrote this program. I hope it helps you.

To implement this program you need to download Apache POI library and have it in your build path (copy POI jars to lib folder).

Program Flow

  1. Get required column headers in order
  2. Generate Map with column headers and equivalent column number in input excel sheet
  3. Create new Workbook and Sheet
  4. Create rows in newly created sheet based on Map in 2nd step
  5. Write resultant workbook into output file

Java Code

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.Map;
import java.util.Set;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ReArrangeExcel {

    public static void main(String[] args) {
        try {

            // list required column headers in order
            String[] outColumns = { "ID", "BRANCH", "SECTION", "YEAR", "NAME" };
            // get input excel file
            FileInputStream excellFile = new FileInputStream(new File(
                    "C:\\inputExcel.xlsx"));

            // Create Workbook instance holding reference to .xlsx file
            XSSFWorkbook workbook1 = new XSSFWorkbook(excellFile);

            // Get first/desired sheet from the workbook
            XSSFSheet mainSheet = workbook1.getSheetAt(0);

            // re-arrange the sheet based on headers
            XSSFWorkbook outWorkBook = reArrange(mainSheet, mapHeaders(outColumns, mainSheet));
            excellFile.close();
            

            // write workbook into output file
            File mergedFile = new File("C:\\outExcel.xlsx");
            if (!mergedFile.exists()) {
                mergedFile.createNewFile();
            }
            FileOutputStream out = new FileOutputStream(mergedFile);
            outWorkBook.write(out);
            out.close();
            System.out.println("File Columns Were Re-Arranged Successfully");
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    public static XSSFWorkbook reArrange(XSSFSheet mainSheet,
            LinkedHashMap<String, Integer> map) {

        // get column headers
        Set<String> colNumbs = map.keySet();

        // Create New Workbook instance
        XSSFWorkbook outWorkbook = new XSSFWorkbook();
        XSSFSheet outSheet = outWorkbook.createSheet();

        // map for cell styles
        Map<Integer, XSSFCellStyle> styleMap = new HashMap<Integer, XSSFCellStyle>();
        
        int colNum = 0;
        XSSFRow hrow = outSheet.createRow(0);
        for (String col : colNumbs) {
            XSSFCell cell = hrow.createCell(colNum);
            cell.setCellValue(col);
            colNum++;
        }

        // This parameter is for appending sheet rows to mergedSheet in the end
        for (int j = mainSheet.getFirstRowNum() + 1; j <= mainSheet.getLastRowNum(); j++) {

            XSSFRow row = mainSheet.getRow(j);

            // Create row in main sheet
            XSSFRow mrow = outSheet.createRow(j);
            int num = -1;
            for (String k : colNumbs) {
                Integer cellNum = map.get(k);
                num++;
                if (cellNum != null) {
                    XSSFCell cell = row.getCell(cellNum.intValue());

                    // if cell is null then continue with next cell
                    if(cell == null) {
                        continue;
                    }
                    // Create column in main sheet
                    XSSFCell mcell = mrow.createCell(num);

                    if (cell.getSheet().getWorkbook() == mcell.getSheet()
                            .getWorkbook()) {
                        mcell.setCellStyle(cell.getCellStyle());
                    } else {
                        int stHashCode = cell.getCellStyle().hashCode();
                        XSSFCellStyle newCellStyle = styleMap.get(stHashCode);
                        if (newCellStyle == null) {
                            newCellStyle = mcell.getSheet().getWorkbook()
                                    .createCellStyle();
                            newCellStyle.cloneStyleFrom(cell.getCellStyle());
                            styleMap.put(stHashCode, newCellStyle);
                        }
                        mcell.setCellStyle(newCellStyle);
                    }

                    // set value based on cell type
                    switch (cell.getCellType()) {
                    case HSSFCell.CELL_TYPE_FORMULA:
                        mcell.setCellFormula(cell.getCellFormula());
                        break;
                    case HSSFCell.CELL_TYPE_NUMERIC:
                        mcell.setCellValue(cell.getNumericCellValue());
                        break;
                    case HSSFCell.CELL_TYPE_STRING:
                        mcell.setCellValue(cell.getStringCellValue());
                        break;
                    case HSSFCell.CELL_TYPE_BLANK:
                        mcell.setCellType(HSSFCell.CELL_TYPE_BLANK);
                        break;
                    case HSSFCell.CELL_TYPE_BOOLEAN:
                        mcell.setCellValue(cell.getBooleanCellValue());
                        break;
                    case HSSFCell.CELL_TYPE_ERROR:
                        mcell.setCellErrorValue(cell.getErrorCellValue());
                        break;
                    default:
                        mcell.setCellValue(cell.getStringCellValue());
                        break;
                    }

                }
            }
        }
        return outWorkbook;
    }

    // get Map of Required Headers and its equivalent column number 
    public static LinkedHashMap<String, Integer> mapHeaders(String[] outColumns,
            XSSFSheet sheet) {
        LinkedHashMap<String, Integer> map = new LinkedHashMap<String, Integer>();
        XSSFRow row = sheet.getRow(0);
        for (String outColumn : outColumns) {
            Integer icol = null;
            for (int i = row.getFirstCellNum(); i < row.getLastCellNum(); i++) {
                if (row.getCell(i).getStringCellValue().equals(outColumn)) {
                    icol = new Integer(i);
                }
            }
            map.put(outColumn, icol);
        }
        return map;
    }
}
Read More
This Example is about Filtering Excel File. If you want to create a selected with Particular Rows and Columns then you can make use this program. To implement this program you need to download Apache POI library and have it in your build path (copy POI jars to lib folder).

Program Flow

  1. Get Input Excel file
  2. Get Work Book of input excel file
  3. Get sheet to be filtered from above workbook
  4. Create New Workbook
  5. Create New Sheet in above workbook
  6. Get Rows from the input sheet with given row numbers and create new rows in new sheet.
  7. Get Columns from input rows with given column numbers and create new columns in above newly created rows. 
  8. Write created workbook to output file

Java Code

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.HashMap;
import java.util.Map;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class CreateExcelFromSelectedRowsAndColumns {

    public static void main(String[] args) {
        try {
            // excel files
            FileInputStream excellFile1 = new FileInputStream(new File(
                    "C:\\inputExcel.xlsx"));
            
            // input row numbers and column numbers
            int[] irows = { 0, 1, 5, 6, 10 };
            int[] icols = { 0, 2, 3, 6 };
            
            // Create Workbook instance holding reference to .xlsx file
            XSSFWorkbook workbook = new XSSFWorkbook(excellFile1);

            // Get first/desired sheet from the workbook
            XSSFSheet sheet = workbook.getSheetAt(0);

            // add sheet2 to sheet1
            XSSFWorkbook outWorkbook = getFilteredWorkBook(sheet, irows, icols);
            excellFile1.close();

            // save merged file
            File outFile = new File("C:\\filtered.xlsx");
            if (!outFile.exists()) {
                outFile.createNewFile();
            }
            FileOutputStream out = new FileOutputStream(outFile);
            outWorkbook.write(out);
            out.close();
            System.out.println("Files were merged succussfully");
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    private static XSSFWorkbook getFilteredWorkBook(XSSFSheet sheet,
            int[] irows, int[] icols) {
        // create New workbook 
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet outSheet = workbook.createSheet();
        Map<Integer, XSSFCellStyle> styleMap = new HashMap<Integer, XSSFCellStyle>();
        int i = 0;
        
        // get rows with given row numbers 
        for (int rowNum : irows) {
            if (rowNum >= sheet.getFirstRowNum()
                    && rowNum <= sheet.getLastRowNum()) {
                
                // create new row
                XSSFRow outRow = outSheet.createRow(i);
                XSSFRow row = sheet.getRow(rowNum);
                int j = 0;
                
                // get columns with given column numbers
                for (int colNum : icols) {
                    if (colNum >= sheet.getRow(0).getFirstCellNum()
                            && colNum <= sheet.getRow(0).getLastCellNum()) {
                        
                        // create new column
                        XSSFCell outCell = outRow.createCell(j);
                        XSSFCell cell = row.getCell(colNum);
                        if (cell != null) {
                            j++;
                            if (cell.getSheet().getWorkbook() == outCell
                                    .getSheet().getWorkbook()) {
                                outCell.setCellStyle(cell.getCellStyle());
                            } else {
                                int stHashCode = cell.getCellStyle().hashCode();
                                XSSFCellStyle newCellStyle = styleMap
                                        .get(stHashCode);
                                if (newCellStyle == null) {
                                    newCellStyle = outCell.getSheet()
                                            .getWorkbook().createCellStyle();
                                    newCellStyle.cloneStyleFrom(cell
                                            .getCellStyle());
                                    styleMap.put(stHashCode, newCellStyle);
                                }
                                outCell.setCellStyle(newCellStyle);
                            }

                            switch (cell.getCellType()) {
                            case HSSFCell.CELL_TYPE_FORMULA:
                                outCell.setCellFormula(cell.getCellFormula());
                                break;
                            case HSSFCell.CELL_TYPE_NUMERIC:
                                outCell.setCellValue(cell.getNumericCellValue());
                                break;
                            case HSSFCell.CELL_TYPE_STRING:
                                outCell.setCellValue(cell.getStringCellValue());
                                break;
                            case HSSFCell.CELL_TYPE_BLANK:
                                outCell.setCellType(HSSFCell.CELL_TYPE_BLANK);
                                break;
                            case HSSFCell.CELL_TYPE_BOOLEAN:
                                outCell.setCellValue(cell.getBooleanCellValue());
                                break;
                            case HSSFCell.CELL_TYPE_ERROR:
                                outCell.setCellErrorValue(cell
                                        .getErrorCellValue());
                                break;
                            default:
                                outCell.setCellValue(cell.getStringCellValue());
                                break;
                            }

                        }

                    }
                }
                i++;
            }
        }
        return workbook;

    }

}
Read More

Blogroll


Follow this blog by Email

Popular Posts