Monday, November 5, 2012

Create a copy of excel using POI


package com.lenin.util;

import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class ExcelReadAndWrite {

      public static void main(String[] args) throws IOException {
            ExcelReadAndWrite excel = new ExcelReadAndWrite();
            excel.process("D:/LNN/My Workspace/POI/src/tables.xls");
      }
     
      public void process(String fileName) throws IOException {
            BufferedInputStream bis = new BufferedInputStream(new FileInputStream(fileName));
            HSSFWorkbook workbook = new HSSFWorkbook(bis);
            HSSFWorkbook myWorkBook = new HSSFWorkbook();
            HSSFSheet sheet = null;
            HSSFRow row = null;
            HSSFCell cell = null;
            HSSFSheet mySheet = null;
            HSSFRow myRow = null;
            HSSFCell myCell = null;
            int sheets = workbook.getNumberOfSheets();
            int fCell = 0;
            int lCell = 0;
            int fRow = 0;
            int lRow = 0;
            for (int iSheet = 0; iSheet < sheets; iSheet++) {
                  sheet = workbook.getSheetAt(iSheet);
                  if (sheet != null) {
                        mySheet = myWorkBook.createSheet(sheet.getSheetName());
                        fRow = sheet.getFirstRowNum();
                        lRow = sheet.getLastRowNum();
                        for (int iRow = fRow; iRow <= lRow; iRow++) {
                              row = sheet.getRow(iRow);
                              myRow = mySheet.createRow(iRow);
                              if (row != null) {
                                    fCell = row.getFirstCellNum();
                                    lCell = row.getLastCellNum();
                                    for (int iCell = fCell; iCell < lCell; iCell++) {
                                          cell = row.getCell(iCell);
                                          myCell = myRow.createCell(iCell);
                                          if (cell != null) {
                                                myCell.setCellType(cell.getCellType());
                                                switch (cell.getCellType()) {
                                                case HSSFCell.CELL_TYPE_BLANK:
                                                      myCell.setCellValue("");
                                                      break;

                                                case HSSFCell.CELL_TYPE_BOOLEAN:
                                                      myCell.setCellValue(cell.getBooleanCellValue());
                                                      break;

                                                case HSSFCell.CELL_TYPE_ERROR:
                                                      myCell.setCellErrorValue(cell.getErrorCellValue());
                                                      break;

                                                case HSSFCell.CELL_TYPE_FORMULA:
                                                      myCell.setCellFormula(cell.getCellFormula());
                                                      break;

                                                case HSSFCell.CELL_TYPE_NUMERIC:
                                                      myCell.setCellValue(cell.getNumericCellValue());
                                                      break;

                                                case HSSFCell.CELL_TYPE_STRING:
                                                      myCell.setCellValue(cell.getStringCellValue());
                                                      break;
                                                default:
                                                      myCell.setCellFormula(cell.getCellFormula());
                                                }
                                          }
                                    }
                              }
                        }
                  }
            }
            bis.close();
            BufferedOutputStream bos = new BufferedOutputStream(
                        new FileOutputStream("workbook.xls", true));
            myWorkBook.write(bos);
            bos.close();
      }
}

No comments:

Post a Comment