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
In this article, I am going to explain how to merge two excel files based their header values. If excel file is having large number of rows and columns with disordered column types  then it will be difficult to merge them. If you want to do any modification by logic in merging, you can do using this program easily. To implement this program you need to download Apache POI library and have it in your build path (copy POI jars to lib folder).
There is no direct method to merge excel files using java. You need to follow the below flow.

Flow Of The Program

  1. Get Excel files
  2. Get Work Books of those excel files
  3. Get sheets to be merged in those excel files
  4. Create Map based on headers of given excel sheets
  5. Read every row and add it to other sheet
  6. Read every cell and add it to the row based on Map
  7. Write merged workbook to output file

Input Excel Files

Output Excel File


Java Code

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.HashMap;
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 MergeExcelOnHeaders {

    public static void main(String[] args) {
        try {
            // get input excel files
            FileInputStream excellFile1 = new FileInputStream(new File(
                    "C:\\inputExcel1.xlsx"));
            FileInputStream excellFile2 = new FileInputStream(new File(
                    "C:\\inputExcel2.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 mainSheet = workbook1.getSheetAt(0);
            XSSFSheet sheet2 = workbook2.getSheetAt(0);

            // add sheet2 to mainSheet
            addSheet(mainSheet, sheet2, mapHeaders(sheet2, mainSheet));
            excellFile1.close();
            excellFile2.close();
            
            // save merged file
            File mergedFile = new File("C:\\merged.xlsx");
            if (!mergedFile.exists()) {
                mergedFile.createNewFile();
            }
            FileOutputStream out = new FileOutputStream(mergedFile);
            workbook1.write(out);
            out.close();
            System.out.println("Files were merged succussfully");
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    public static void addSheet(XSSFSheet mainSheet, XSSFSheet sheet, HashMap<Integer, Integer> map) {
        
        //get column number
        Set<Integer> colNumbs = map.keySet();
        // map for cell styles
        Map<Integer, XSSFCellStyle> styleMap = new HashMap<Integer, XSSFCellStyle>();

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

            XSSFRow row = sheet.getRow(j);
            
            // Create row in main sheet
            XSSFRow mrow = mainSheet.createRow(len + j);

            for (Integer k : colNumbs) {
                XSSFCell cell = row.getCell(k.intValue());
                
                // Create column in main sheet
                XSSFCell mcell = mrow.createCell(map.get(k).intValue());

                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;
                }
            }
        }
    }

    // results MAP of <secondary sheet, main sheet>
    public static HashMap<Integer, Integer> mapHeaders(XSSFSheet sheet1,
            XSSFSheet sheet2) {
        HashMap<Integer, Integer> map = new HashMap<Integer, Integer>();
        XSSFRow row1 = sheet1.getRow(0);
        XSSFRow row2 = sheet2.getRow(0);
        for (int i = row1.getFirstCellNum(); i < row1.getLastCellNum(); i++) {
            for (int j = row2.getFirstCellNum(); j < row2.getLastCellNum(); j++) {
                if (row1.getCell(i).getStringCellValue()
                        .equals(row2.getCell(j).getStringCellValue())) {
                    map.put(new Integer(i), new Integer(j));
                }
            }
        }
        return map;
    }
}
Read More
You might have seen this effect in many website. Initially header looks big, when user scroll the page then the header will shrink with animation. Find the following example to implement this feature with AngularJS.

Here find below two classes big and small. Specify required width in these classes. CSS3 transition property  gives proper animation. Changing class with ng-class will trigger the header size change with animation.

CSS

#header_nav {
    width:100%;
    background-color:#666;
    position:fixed;
    top:0;
    left:0;
   -webkit-transition:0.5s linear all;
   -moz-transition:0.5s linear all;
   -o-transition:0.5s linear all;
   transition:0.5s linear all;
}

.big {
    height:100px;   
}

.small {
   height:40px;
}

Script

function myCtrl($scope, $window) {
   $scope.navClass = 'big';
   angular.element($window).bind(
    "scroll", function() {
         console.log(window.pageYOffset);
         if(window.pageYOffset > 0) {
           $scope.navClass = 'small';
         } else {
           $scope.navClass = 'big';
         }
         $scope.$apply();
   });  
}
Read More
We can solve this problem by Adding delay to window scroll event. If user is not scrolling for last one second, then We can confirm that the user stopped scrolling.

Script 

In this below script window was bonded to scroll event
Scroll event will be triggered after 1 second ( 1000 milli seconds)
function myCtrl($scope, $window, $timeout) {
   $scope._timeout  = null;
   angular.element($window).bind(
    "scroll", function() {
         if($scope._timeout){ //if there is already a timeout in process cancel it
          $timeout.cancel($scope._timeout);
         }
         $scope._timeout = $timeout(function(){
           $scope._timeout = null;
           alert('scroll stopped');
         },1000);
   });  
}
Read More
This is simple functionality. You can use this script when you want get updates in ajax call periodically like update cricket or football score.

With $interval

  1. Define one function with ajax call with $http
  2. Call above function with $interval and time in milliseconds

Script

function myCtrl($scope, $http, $timeout, $interval) {

    $scope.count = 0;
    $scope.ajaxPeriodicall = function() {
        
        $http.get('/angular/sample.csv').
         success(function(data, status, headers, config) {
            // this callback will be called asynchronously
            // when the response is available
            $scope.count = $scope.count + 1;
         }); 
       
    };

    $scope.start = function() {
       $scope.myCall = $interval($scope.ajaxPeriodicall, 1000);        
    };

    $scope.stop = function() {
       $interval.cancel($scope.myCall);   
    };    

}

With $timeout

  1. Define one function with ajax call with $http
  2. In response function call that function again with $timeout

Script

$scope.count = 0;
$scope.ajaxPeriodicall = function() {
       
     $http.get('/angular/sample.csv').
       success(function(data, status, headers, config) {
         // this callback will be called asynchronously
         // when the response is available
         // call ajaxPeriodicall with timeout
         $scope.count = $scope.count + 1;
         $timeout($scope.ajaxPeriodicall, 1000);
      }); 
     
};
Read More
If you want display your own customized context menu on right mouse click, this program will help you.

Disable Context Menu

Whenever user right click on HTML document, The browser will show one default menu. We have to disable this first to display your own menu. You can do this by using below code.
<body oncontextmenu="return false">

Design  Your Own Context Menu

Design a DIV with absolute position and limited width. Find the following basic design.
.rightClickPanel {
  width:200px;
  padding:20px;
  position:absolute;
  border:1px solid #ccc;
  background:#ccc;
  display:none;
}

<div class="rightClickPanel" ng-style="rightPanelStyle">
My right panel
</div>

Set Style to Context Menu based on Mouse Position

  1. $event.which - Key code of event ( 3 for right mouse click )
  2. $event.clientX - X coordinate of mouse click
  3. $event.clientY - Y coordinate of mouse click
$scope.detectRightMouseClick = function($event) {
     if($event.which === 3) {
         $scope.rightPanelStyle = {'display':'block','left':$event.clientX + 'px','top':$event.clientY + 'px'}; 
          return false;
     } 
};
Read More
When user entered some data in form and tried to close browser window accidentally, system has to show alert message to save their data. This example is to implement this functionality using AngularJS. Observe below two functions
<input ng-change="setConfirmUnload(true)" ng-model="data"/>
<button ng-click="saveData()">Save Data</button>
<div ng-bind="data1"></div>
$window.onbeforeunload will trigger the message from assigned function. Here you need call $scope.setConfirmUnload(true) whenever user enters data in form and call $scope.setConfirmUnload(false)  whenever user save the form data.
$scope.setConfirmUnload = function(on) {
    window.onbeforeunload = (on) ? $scope.unloadMessage : null;
}

$scope.unloadMessage = function() {
    return "You have unsaved changes";
}
    
$scope.saveData = function() {
    $scope.data1 = $scope.data;
    $scope.setConfirmUnload(false);
}
Read More
In some cases, System has to provide suggestions to user while user typing. Sending request for each and every request to server will be overhead. So sending request to server when user finishes typing will give good performance. We can detect whether user typing or finished his typing by delaying the ng-change event.

Markup

Call function with ng-change. displayName function will be triggered when user changes input
<input ng-change="displayName()" ng-model="name"/>
<div ng-bind="name1"></div>

Script

Use $timeout to delay the function
function myCtrl($scope, $timeout) {
     $scope._timeout  = null;
     $scope.name = 'sri';
     $scope.FilterByName = function () {
        if($scope._timeout){ //if there is already a timeout in process cancel it
          $timeout.cancel($scope._timeout);
        }
        $scope._timeout = $timeout(function(){
           $scope._timeout = null;
           $scope.name1 = $scope.name;
        },1000);
      };
}
Read More
Whenever user scroll to the bottom, we can show Go to top button to make user reached to top of the page. This program will help you to find out bottom of the page.

Program Flow

  1. Bind scroll event to window
  2. Calculate whole document height
  3. Get height of the window
  4. Calculate bottom of window
  5. If window bottom is greater than or equal to document height then user reached to bottom
angular.element($window).bind("scroll", function() {
    var windowHeight = "innerHeight" in window ? window.innerHeight : document.documentElement.offsetHeight;
    var body = document.body, html = document.documentElement;
    var docHeight = Math.max(body.scrollHeight, body.offsetHeight, html.clientHeight,  html.scrollHeight, html.offsetHeight);
    windowBottom = windowHeight + window.pageYOffset;
    if (windowBottom >= docHeight) {
        alert('bottom reached');
    }
});
Read More
If you want to submit Form data to different URLs based on selection of select box, You have to do some lengthy code in jQuery or Javascript. But it is very easy build this logic using AngularJS.

Markup

<form action="{{action}}" ng-submit="alertAction($event)">
  <select ng-model="action">
    <option value="action1">Action 1</option>
    <option value="action2">Action 2</option>
    <option value="action3">Action 3</option>
  </select>
  <h4>Action attribute value</h4>
  <div ng-bind="action"></div><br/>
  <button>Submit</button>
</form>

Script

$scope.action = 'action2';
$scope.alertAction = function($event) {
    alert($scope.action);
    $event.preventDefault();
};
Read More
We can select content of HTML element using element.select(). But onmouseup action the selected text by script will be deselected. So we have to disable onmouseup action.

Markup

<input ng-focus="selectAllContent($event)" ng-mouseup="$event.preventDefault();" value="sample value"/>

Script

function myCtrl($scope) {
    $scope.selectAllContent= function($event) {
       $event.target.select();
    };
}
Read More
AngularJS provides ng-model to bind values to input fields. Binding radio buttons to scope variables is tricky.

Markup

Here you can find HTML markup of Gender radio buttons. 
<input type="radio" ng-model="gender" value="male" name="gender"/> Male
<input type="radio" ng-model="gender" value="female" name="gender"/> Female

Script

This is script to access radio input variables in scope controllers

function myCtrl($scope) {
    $scope.gender = 'male';
}
Read More

Search This Blog

Loading...

Blogroll

Srinivas Dasari
find me on facebook
follow me on twitter

Follow this blog by Email

Popular Posts