Example usage for org.apache.poi.xssf.usermodel XSSFSheet createRow

List of usage examples for org.apache.poi.xssf.usermodel XSSFSheet createRow

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.xssf.usermodel.*
  5. XSSFSheet
  6. createRow

Introduction

In this page you can find the example usage for org.apache.poi.xssf.usermodel XSSFSheet createRow.

Prototype

@Override
public XSSFRow createRow(int rownum)

Source Link

Document

Create a new row within the sheet and return the high level representation Note: If a row already exists at this position, it is removed/overwritten and any existing cell is removed!

Usage

From source file:com.vodafone.poms.ii.helpers.ExportManager.java

public void exportCustomerPO() {
    List<VendorPo> vendorPOs = vendorPOController.getExportItems(fromDate, toDate);

    XSSFWorkbook workbook = new XSSFWorkbook();
    XSSFSheet sheet = workbook.createSheet("All POs");
    Row row = sheet.createRow(0);
    for (int i = 0; i < customerPOHeaders.length; i++) {
        row.createCell(i).setCellValue(customerPOHeaders[i]);
    }/*  w  w w  .j av a2s .c om*/
    int innerRow = 0;
    for (int i = 0; i < vendorPOs.size(); i++) {
        row = sheet.createRow(i + 1 + innerRow);
        //po#
        row.createCell(0).setCellValue(vendorPOs.get(i).getPoNumber());
        //poDate
        row.createCell(1).setCellValue(vendorPOs.get(i).getPoDate());
        //domain
        row.createCell(2).setCellValue(vendorPOs.get(i).getDomainName().getDomainName());
        //type
        row.createCell(3).setCellValue(vendorPOs.get(i).getPoType().getTypeName());
        //description
        row.createCell(4).setCellValue(vendorPOs.get(i).getPoDescription());
        //factor
        row.createCell(5).setCellValue(vendorPOs.get(i).getFactor());
        //service_value
        row.createCell(6).setCellValue(vendorPOs.get(i).getServiceValue().toString());
        //po_value
        row.createCell(7).setCellValue(vendorPOs.get(i).getPoValue().toString());
        //po_value with taxes
        row.createCell(8).setCellValue(vendorPOs.get(i).getPoValueTaxes().toString());
        //work done
        row.createCell(9).setCellValue(vendorPOs.get(i).getWorkDone());
        //remaining in po
        row.createCell(10).setCellValue(vendorPOs.get(i).getRemainingInPo().toString());
        //taxes
        row.createCell(11).setCellValue(vendorPOs.get(i).getTaxes());
        Object[] mds = vendorPOs.get(i).getVendorMdCollection().toArray();
        for (int j = 0; j < mds.length; j++) {
            if (j > 1) {
                row = sheet.createRow(i + 1 + innerRow);
                innerRow++;
            }
            //md_deserved
            row.createCell(12).setCellValue(((VendorMd) mds[j]).getMdDeserved().toString());
            //md_value
            row.createCell(13)
                    .setCellValue(((VendorMd) mds[j]).getMdValue() != null
                            ? ((VendorMd) mds[j]).getMdValue().toString()
                            : "");
            //md_date
            row.createCell(14).setCellValue(
                    ((VendorMd) mds[j]).getMdDate() != null ? ((VendorMd) mds[j]).getMdDate() : null);
            //md_number
            row.createCell(15).setCellValue(
                    ((VendorMd) mds[j]).getMdNumber() != null ? ((VendorMd) mds[j]).getMdNumber() : "");
            //invoiced
            row.createCell(16).setCellValue(
                    ((VendorMd) mds[j]).getInvoiced() != null ? ((VendorMd) mds[j]).getInvoiced() : false);
            //remaining in md
            row.createCell(17)
                    .setCellValue(((VendorMd) mds[j]).getRemainingInMd() != null
                            ? ((VendorMd) mds[j]).getRemainingInMd().toString()
                            : "");

        }

    }
    FacesContext facesContext = FacesContext.getCurrentInstance();
    ExternalContext externalContext = facesContext.getExternalContext();
    externalContext.setResponseContentType("application/vnd.ms-excel");
    externalContext.setResponseHeader("Content-Disposition", "attachment; filename=\"Customer POs.xlsx\"");

    try {
        workbook.write(externalContext.getResponseOutputStream());
        externalContext.getResponseOutputStream().close();
    } catch (IOException ex) {
        Logger.getLogger(ExportManager.class.getName()).log(Level.SEVERE, null, ex);
    }
    facesContext.responseComplete();
    JsfUtil.addSuccessMessage("Customer PO Report is now exported");

}

From source file:com.vodafone.poms.ii.helpers.ExportManager.java

public void exportASPPO() {
    List<AspPo> aspPOs = aspPOController.getExportItems(fromDate, toDate);

    XSSFWorkbook workbook = new XSSFWorkbook();
    XSSFSheet sheet = workbook.createSheet("All POs");
    Row row = sheet.createRow(0);
    for (int i = 0; i < aspPOHeaders.length; i++) {
        row.createCell(i).setCellValue(aspPOHeaders[i]);
    }//from   ww w . ja  v a2s. co m
    int innerRow = 0;
    for (int i = 0; i < aspPOs.size(); i++) {
        row = sheet.createRow(i + 1 + innerRow);
        //po#
        row.createCell(0).setCellValue(aspPOs.get(i).getPoNumber());
        //poDate
        row.createCell(1).setCellValue(aspPOs.get(i).getPoDate());
        //domain
        row.createCell(2).setCellValue(aspPOs.get(i).getDomainName().getDomainName());
        //type
        row.createCell(3).setCellValue(aspPOs.get(i).getPoType().getTypeName());
        //description
        row.createCell(4).setCellValue(aspPOs.get(i).getPoDescription());
        //factor
        row.createCell(5).setCellValue(aspPOs.get(i).getFactor());
        //service_value
        row.createCell(6).setCellValue(aspPOs.get(i).getServiceValue().toString());
        //po_value
        row.createCell(7).setCellValue(aspPOs.get(i).getPoValue().toString());
        //po_value with taxes
        row.createCell(8).setCellValue(aspPOs.get(i).getPoValueTaxes().toString());
        //work done
        row.createCell(9).setCellValue(aspPOs.get(i).getWorkDone());
        //remaining in po
        row.createCell(10).setCellValue(aspPOs.get(i).getRemainingInPo().toString());
        //taxes
        row.createCell(11).setCellValue(aspPOs.get(i).getTaxes());
        //ASP
        row.createCell(12).setCellValue(aspPOs.get(i).getAsp().getSubcontractorName());
        //VendorPO
        row.createCell(13).setCellValue((aspPOs.get(i).getVendorPoCollection().isEmpty() ? "Uncorrelated"
                : ((VendorPo) aspPOs.get(i).getVendorPoCollection().toArray()[0]).getPoNumber()));
        Object[] grns = aspPOs.get(i).getAspGrnCollection().toArray();
        for (int j = 0; j < grns.length; j++) {
            if (j > 1) {
                row = sheet.createRow(i + 1 + innerRow);
                innerRow++;
            }
            //md_deserved
            row.createCell(14).setCellValue(((AspGrn) grns[j]).getGrnDeserved().toString());
            //md_value
            row.createCell(15)
                    .setCellValue(((AspGrn) grns[j]).getGrnValue() != null
                            ? ((AspGrn) grns[j]).getGrnValue().toString()
                            : "");
            //md_date
            row.createCell(16).setCellValue(
                    ((AspGrn) grns[j]).getGrnDate() != null ? ((AspGrn) grns[j]).getGrnDate() : null);
            //md_number
            row.createCell(17).setCellValue(
                    ((AspGrn) grns[j]).getGrnNumber() != null ? ((AspGrn) grns[j]).getGrnNumber() : "");
            //invoiced
            row.createCell(18).setCellValue(
                    ((AspGrn) grns[j]).getInvoiced() != null ? ((AspGrn) grns[j]).getInvoiced() : false);
            //remaining in md
            row.createCell(19)
                    .setCellValue(((AspGrn) grns[j]).getRemainingInGrn() != null
                            ? ((AspGrn) grns[j]).getRemainingInGrn().toString()
                            : "");

        }

    }
    FacesContext facesContext = FacesContext.getCurrentInstance();
    ExternalContext externalContext = facesContext.getExternalContext();
    externalContext.setResponseContentType("application/vnd.ms-excel");
    externalContext.setResponseHeader("Content-Disposition", "attachment; filename=\"ASP POs.xlsx\"");

    try {
        workbook.write(externalContext.getResponseOutputStream());
        externalContext.getResponseOutputStream().close();
    } catch (IOException ex) {
        Logger.getLogger(ExportManager.class.getName()).log(Level.SEVERE, null, ex);
    }
    facesContext.responseComplete();
    JsfUtil.addSuccessMessage("Customer PO Report is now exported");

}

From source file:com.vsquaresystem.safedeals.amenity.AmenityService.java

public Boolean exportExcel() throws IOException {

    List<Amenity> rs = amenityDAL.findAllAmenities();
    System.out.println("result set excel sop" + rs);
    XSSFWorkbook workbook = new XSSFWorkbook();
    XSSFSheet sheet = workbook.createSheet("Amenity Master");
    XSSFRow xssfrow = sheet.createRow((short) 0);
    xssfrow.createCell(1);//from   ww w  . j  ava  2s  .c o  m
    xssfrow.createCell(2);
    xssfrow.createCell(3);
    xssfrow.getCell(1).setCellValue("Id");
    xssfrow.getCell(2).setCellValue("Amenity Name");
    xssfrow.getCell(3).setCellValue("Amenity Code Id");
    //        List<Book> listBook = excelWriter.getListBook();
    String fileName = "/AmenityMasterData.xls";
    String exportPath = attachmentUtils.getAmenityExportAttachmentRootDirectory() + fileName;

    System.out.println("exportPath" + exportPath);

    int rowCount = 0;

    for (Amenity aAmenity : rs) {
        Row row = sheet.createRow(++rowCount);
        writeBook(aAmenity, row);
    }

    try (FileOutputStream outputStream = new FileOutputStream(exportPath)) {
        workbook.write(outputStream);
    }

    return true;
}

From source file:com.vsquaresystem.safedeals.city.CityService.java

public Boolean exportExcel() throws IOException {
    logger.info("getExportExcel method is working");
    List<City> rs = cityDAL.findAllCities();
    System.out.println("result set excel sop" + rs);
    XSSFWorkbook workbook = new XSSFWorkbook();
    XSSFSheet sheet = workbook.createSheet("City Master");
    XSSFRow xssfrow = sheet.createRow((short) 0);
    xssfrow.createCell(1);/*from w w  w  .  ja v a 2 s . c  o  m*/
    xssfrow.createCell(2);
    xssfrow.createCell(3);
    xssfrow.createCell(4);
    xssfrow.createCell(5);
    xssfrow.createCell(6);
    xssfrow.getCell(1).setCellValue("City Id");
    xssfrow.getCell(2).setCellValue("Name");
    xssfrow.getCell(3).setCellValue("Country Id");
    xssfrow.getCell(4).setCellValue("State Id");
    xssfrow.getCell(5).setCellValue("Latitude Id");
    xssfrow.getCell(6).setCellValue("Longitude Id");
    String fileName = "/CityMasterData.xls";
    String exportPath = attachmentUtils.getCityExportAttachmentRootDirectory() + fileName;
    System.out.println("exportPath" + exportPath);
    int rowCount = 0;

    for (City aLocation : rs) {
        Row row = sheet.createRow(++rowCount);
        writeBook(aLocation, row);
    }

    try (FileOutputStream outputStream = new FileOutputStream(exportPath)) {
        workbook.write(outputStream);
    }

    return true;
}

From source file:com.vsquaresystem.safedeals.location.LocationService.java

public Boolean exportExcel() throws IOException {
    logger.info("getExportExcel method is working");
    List<Location> rs = locationDAL.findAllLocations();
    System.out.println("result set excel sop" + rs);
    XSSFWorkbook workbook = new XSSFWorkbook();
    XSSFSheet sheet = workbook.createSheet("Location Master");
    XSSFRow xssfrow = sheet.createRow((short) 0);
    xssfrow.createCell(1);//from   w w w.j  ava 2  s.c o m
    xssfrow.createCell(2);
    xssfrow.createCell(3);
    xssfrow.getCell(1).setCellValue("Location Id");
    xssfrow.getCell(2).setCellValue("Location Name");
    xssfrow.getCell(3).setCellValue("City Id");
    String fileName = "/LocationMasterData.xls";
    String exportPath = attachmentUtils.getLocationExportAttachmentRootDirectory() + fileName;
    System.out.println("exportPath" + exportPath);
    int rowCount = 0;
    for (Location aLocation : rs) {
        Row row = sheet.createRow(++rowCount);
        writeBook(aLocation, row);
    }
    try (FileOutputStream outputStream = new FileOutputStream(exportPath)) {
        workbook.write(outputStream);
    }
    return true;
}

From source file:com.vsquaresystem.safedeals.rawmarketprice.RawMarketPriceService.java

public Boolean exportExcel() throws IOException {
    logger.info("getExportExcel method is working for raw market price");
    List<RawMarketPrice> rmp = rawMarketPriceDAL.findAllRawMarketPrice();
    //        logger.info("result set excel sop" + rmp);
    XSSFWorkbook workbook = new XSSFWorkbook();
    XSSFSheet sheet = workbook.createSheet("Raw Market Price Master");
    XSSFRow xssfrow = sheet.createRow((short) 0);
    xssfrow.createCell(1);/*from w w w  .  j  av  a 2 s  .  c om*/
    xssfrow.createCell(2);
    xssfrow.createCell(3);
    xssfrow.createCell(4);
    xssfrow.createCell(5);
    xssfrow.createCell(6);
    xssfrow.createCell(7);
    xssfrow.createCell(8);
    xssfrow.createCell(9);
    xssfrow.createCell(10);
    xssfrow.createCell(11);
    xssfrow.createCell(12);
    xssfrow.createCell(13);
    xssfrow.createCell(14);
    xssfrow.createCell(15);
    xssfrow.createCell(16);
    xssfrow.createCell(17);
    xssfrow.createCell(18);
    xssfrow.createCell(19);
    xssfrow.createCell(20);
    xssfrow.createCell(21);
    xssfrow.createCell(22);
    xssfrow.createCell(23);
    xssfrow.createCell(24);
    xssfrow.createCell(25);
    xssfrow.getCell(1).setCellValue("Id");
    xssfrow.getCell(2).setCellValue("City Name");
    xssfrow.getCell(3).setCellValue("Location Name");
    xssfrow.getCell(4).setCellValue("Year");
    xssfrow.getCell(5).setCellValue("Month");
    xssfrow.getCell(6).setCellValue("MP Agri Land Lowest");
    xssfrow.getCell(7).setCellValue("MP Agri Land Highest");
    xssfrow.getCell(8).setCellValue("MP Plot Lowest");
    xssfrow.getCell(9).setCellValue("MP Plot Highest");
    xssfrow.getCell(10).setCellValue("MP Residential Lowest");
    xssfrow.getCell(11).setCellValue("MP Residential Highest");
    xssfrow.getCell(12).setCellValue("MP Commercial Lowest");
    xssfrow.getCell(13).setCellValue("MP Commercial Highest");
    xssfrow.getCell(14).setCellValue("Safedeal Zone Id");
    xssfrow.getCell(15).setCellValue("Location Type Id");
    xssfrow.getCell(16).setCellValue("Location Categories");
    xssfrow.getCell(17).setCellValue("Description");
    xssfrow.getCell(18).setCellValue("Major Approach Road");
    xssfrow.getCell(19).setCellValue("Source of Water");
    xssfrow.getCell(20).setCellValue("Public Transport");
    xssfrow.getCell(21).setCellValue("Advantage");
    xssfrow.getCell(22).setCellValue("Disadvantage");
    xssfrow.getCell(23).setCellValue("Population");
    xssfrow.getCell(24).setCellValue("Migration Rate");
    xssfrow.getCell(25).setCellValue("Commercial Center");

    String fileName = "/RawMarketPriceMasterData.xls";
    String exportPath = attachmentUtils.getRawMarketPriceExportAttachmentRootDirectory() + fileName;

    logger.info("exportPath" + exportPath);

    int rowCount = 0;

    for (RawMarketPrice aRawMarketPrice : rmp) {
        Row row = sheet.createRow(++rowCount);
        writeBook(aRawMarketPrice, row);
    }

    try (FileOutputStream outputStream = new FileOutputStream(exportPath)) {
        workbook.write(outputStream);
    }

    return true;
}

From source file:com.xidu.framework.common.view.documenthandler.AbstractExcel2007DocumentHandler.java

License:Open Source License

/**
 * Convenient method to obtain the cell in the given sheet, row and column.
 * <p>Creates the row and the cell if they still doesn't already exist.
 * Thus, the column can be passed as an int, the method making the needed downcasts.
 * @param sheet a sheet object. The first sheet is usually obtained by workbook.getSheetAt(0)
 * @param row thr row number//  w w w  . j a  v  a 2  s .co m
 * @param col the column number
 * @return the HSSFCell
 */
protected XSSFCell getCell(XSSFSheet sheet, int row, int col) {
    XSSFRow sheetRow = sheet.getRow(row);
    if (sheetRow == null) {
        sheetRow = sheet.createRow(row);
    }
    XSSFCell cell = sheetRow.getCell(col);
    if (cell == null) {
        cell = sheetRow.createCell(col);
    }
    return cell;
}

From source file:com.yyl.common.utils.excel.ExcelTools.java

/**
 * ?xlsxExcel//  w  w w.j  a  va  2  s .co m
 * @param heads 
 * @param data ?
 * @param sheetName Excel?
 * @param out ?
 * @return ByteArrayOutputStream
 * @throws IOException
 */
public static void writeToXLSX(ExcelRow heads, ExcelData data, String sheetName, ByteArrayOutputStream out)
        throws IOException {
    XSSFWorkbook wb = new XSSFWorkbook();
    XSSFSheet sheet1 = wb.createSheet(sheetName);
    XSSFRow row = sheet1.createRow(0);
    XSSFCellStyle style = wb.createCellStyle();
    style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    XSSFCell cell;
    for (int i = 0; i < heads.size(); i++) {
        cell = row.createCell(i);
        cell.setCellValue(heads.get(i));
        cell.setCellStyle(style);
    }

    if (data != null && data.size() != 0) {
        for (int n = 0; n < data.size(); n++) {
            row = sheet1.createRow(n + 1);
            ExcelRow datarow = data.get(n);
            for (int m = 0; m < datarow.size(); m++) {
                cell = row.createCell(m);
                cell.setCellValue(datarow.get(m));
                cell.setCellStyle(style);
            }
        }
    }
    wb.write(out);
}

From source file:comparator.Comparator.java

public static void delta_MVC_MTC() throws IOException {
    //Get the input files
    //FileInputStream mtcFile = new FileInputStream(new File("\\\\hes-nas-drize.hes.adhes.hesge.ch\\home\\luc.mottin\\Documents\\Expand\\Catalogues\\workingMTC.xlsx"));
    //FileInputStream mvcFile = new FileInputStream(new File("\\\\hes-nas-drize.hes.adhes.hesge.ch\\home\\luc.mottin\\Documents\\Expand\\Catalogues\\Informal_epSOS-MVC_V1_9.xlsx"));
    FileInputStream mtcFile = new FileInputStream(new File(
            "\\\\hes-nas-drize.hes.adhes.hesge.ch\\home\\luc.mottin\\Documents\\Expand\\MVC2.0\\MTC_2.0.xlsx"));
    FileInputStream mtcFile2 = new FileInputStream(new File(
            "\\\\hes-nas-drize.hes.adhes.hesge.ch\\home\\luc.mottin\\Documents\\Expand\\MVC2.0\\MTC_2.0.xlsx"));
    FileInputStream mvcFile = new FileInputStream(new File(
            "\\\\hes-nas-drize.hes.adhes.hesge.ch\\home\\luc.mottin\\Documents\\Expand\\MVC2.0\\Informal_epSOS-MVC_V2_0_(DRAFT)_03.xlsx"));

    //Prepare the output file
    //Writer csvW = new BufferedWriter(new OutputStreamWriter(new FileOutputStream("\\\\hes-nas-drize.hes.adhes.hesge.ch\\home\\luc.mottin\\Documents\\Expand\\Catalogues\\delta_Mtc-Mvc.csv"), "UTF-8"));
    Writer csvW = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(
            "\\\\hes-nas-drize.hes.adhes.hesge.ch\\home\\luc.mottin\\Documents\\Expand\\MVC2.0\\delta_Mtc-Mvc2.1.csv"),
            "UTF-8"));
    csvW.write('\ufeff');

    csvW.write("Expand Project;");
    csvW.write("\n\n");

    //Get the workbook instance for XLS file 
    XSSFWorkbook mtcWorkbook = new XSSFWorkbook(mtcFile);
    XSSFWorkbook mtcWorkbook2 = new XSSFWorkbook(mtcFile2);
    XSSFWorkbook mvcWorkbook = new XSSFWorkbook(mvcFile);

    //Output//  w  w  w.ja v  a  2 s .  co  m
    csvW.write("One MTC sheet is missing in MVC : VS16_epSOSErrorCodes;");
    csvW.write("\n");
    csvW.write("********************;");
    csvW.write("\n");
    csvW.write("Set name;");
    csvW.write("\n");
    csvW.write("MTC mismatches;List of the codes missing in MVC");
    csvW.write("\n");
    csvW.write("MVC mismatches;List of the codes missing in MTC");
    csvW.write("\n");
    csvW.write("********************;");

    XSSFSheet mtcSheet;
    XSSFSheet mtcSheet2;
    Iterator<Row> mtcRowIterator;
    Iterator<Row> mtcRowIterator2;
    Iterator<Row> mvcRowIterator;
    Iterator<Cell> mtcCellIterator;
    Iterator<Cell> mvcCellIterator;
    int mtcCol;
    int mvcCol;
    boolean mtcColFound;
    boolean mvcColFound;
    ArrayList mtcCodes;
    ArrayList mvcCodes;
    ArrayList mtcEnglishNames;
    ArrayList mvcEnglishNames;
    ArrayList englishNamesdifferences;
    Row mtcRow;
    Row mtcRow2;
    Row mvcRow;
    Row mvcRow2;
    Row newRow;
    Cell newCell;
    CellStyle myStyle;
    String mtcSplit[];
    String mvcSplit[];
    String mtcSheetName;
    String mvcSheetName;

    //Get the sheet from the MTC workbook
    for (int i = 0; i < mtcWorkbook.getNumberOfSheets(); i++) {
        mtcSheet = mtcWorkbook.getSheetAt(i);
        mtcSheet2 = mtcWorkbook2.getSheetAt(i);

        //Get iterator to all the rows in current MTC sheet
        mtcRowIterator = mtcSheet.iterator();
        mtcRowIterator2 = mtcSheet2.iterator();

        //Get the sheet from the MVC workbook
        for (int j = 0; j < mvcWorkbook.getNumberOfSheets(); j++) {
            XSSFSheet mvcSheet = mvcWorkbook.getSheetAt(j);

            //Get iterator to all the rows in current MVC sheet
            mvcRowIterator = mvcSheet.iterator();

            //Get the name of MTC sheet and MVC sheet, compare them if they contain data
            //MTC data files are called "VSX_sheetName"
            //MVC data files are called "epSOSsheetName"
            mtcSplit = mtcSheet.getSheetName().split("_");
            mvcSplit = mvcSheet.getSheetName().split("SOS");
            mtcSheetName = mtcSplit[mtcSplit.length - 1];
            mvcSheetName = mvcSplit[mvcSplit.length - 1];

            //And process the file matching or throw out the file that has no equivalent
            if (mtcSheetName.equals(mvcSheetName)) {

                mtcCol = 0;
                mvcCol = 0;
                mtcColFound = false;
                mvcColFound = false;
                mtcCodes = new ArrayList();
                mvcCodes = new ArrayList();
                mtcEnglishNames = new ArrayList();
                mvcEnglishNames = new ArrayList();
                englishNamesdifferences = new ArrayList();

                //For each row, iterate through each columns
                //Get iterator to all cells of current row
                //In MTC
                while (mtcRowIterator.hasNext()) {
                    mtcRow = mtcRowIterator.next();
                    mtcRow2 = mtcRow;

                    if (mtcColFound == false) {
                        mtcCellIterator = mtcRow.cellIterator();

                        while (mtcCellIterator.hasNext()) {
                            Cell mtcCell = mtcCellIterator.next();
                            if (mtcCell.getCellType() == 1 && (mtcCell.getStringCellValue().equals("Code")
                                    || mtcCell.getStringCellValue().equals("epSOS Code"))) {
                                mtcCol = mtcCell.getColumnIndex();
                                mtcColFound = true;
                                break;
                            }
                        }
                    } else {
                        mtcRow.getCell(mtcCol, Row.CREATE_NULL_AS_BLANK).setCellType(Cell.CELL_TYPE_STRING);
                        mtcRow2.getCell(mtcCol + 1, Row.CREATE_NULL_AS_BLANK)
                                .setCellType(Cell.CELL_TYPE_STRING);
                        mtcCodes.add(mtcRow.getCell(mtcCol).getStringCellValue().trim());
                        mtcEnglishNames.add(mtcRow2.getCell(mtcCol + 1).getStringCellValue().trim());
                    }
                }

                //In MVC
                while (mvcRowIterator.hasNext()) {
                    mvcRow = mvcRowIterator.next();
                    mvcRow2 = mvcRow;
                    if (mvcColFound == false) {
                        mvcCellIterator = mvcRow.cellIterator();

                        while (mvcCellIterator.hasNext()) {
                            Cell mvcCell = mvcCellIterator.next();

                            if (mvcCell.getCellType() == 1 && (mvcCell.getStringCellValue().equals("epSOS Code")
                                    || mvcCell.getStringCellValue().equals("Code"))) {
                                mvcCol = mvcCell.getColumnIndex();
                                mvcColFound = true;
                                break;
                            }
                        }
                    } else {
                        mvcRow.getCell(mvcCol, Row.CREATE_NULL_AS_BLANK).setCellType(Cell.CELL_TYPE_STRING);
                        mvcRow2.getCell(mvcCol + 1, Row.CREATE_NULL_AS_BLANK)
                                .setCellType(Cell.CELL_TYPE_STRING);
                        mvcCodes.add(mvcRow.getCell(mvcCol).getStringCellValue().trim());
                        mvcEnglishNames.add(mvcRow2.getCell(mvcCol + 1).getStringCellValue().trim());
                    }
                }

                //Processing
                colCompare(mtcCodes, mvcCodes, mvcEnglishNames, mtcEnglishNames, englishNamesdifferences);

                //Output
                //if((!mtcCodes.isEmpty()) || (!mvcCodes.isEmpty())) {}
                csvW.write("\n\n");
                csvW.write(mtcSheetName + ";");
                csvW.write("\n");
                csvW.write("MTC mismatches;");
                for (int a = 0; a < mtcCodes.size(); a++) {
                    csvW.write(mtcCodes.get(a) + ";");
                }
                csvW.write("\n");
                csvW.write("MVC mismatches\n");
                for (int b = 0; b < mvcCodes.size(); b++) {
                    csvW.write(mvcCodes.get(b) + ";" + mvcEnglishNames.get(b) + "\n");
                }

                csvW.write("english names differences\n");
                if (!englishNamesdifferences.isEmpty()) {
                    csvW.write("code;MTC 2.0;MVC 2.0.1\n");
                    for (int c = 0; c < englishNamesdifferences.size(); c = c + 3) {
                        csvW.write(englishNamesdifferences.get(c) + ";" + englishNamesdifferences.get(c + 1)
                                + ";" + englishNamesdifferences.get(c + 2) + "\n");
                    }
                }

                /* work on currents MTC2.0 sheet */
                mtcColFound = false;
                mtcCol = 0;
                List<Integer> delRows = new ArrayList();

                //recreate iterator to all the rows in current MTC sheet
                while (mtcRowIterator2.hasNext()) {
                    mtcRow = mtcRowIterator2.next();
                    mtcRow2 = mtcRow;
                    if (mtcColFound == false) {
                        mtcCellIterator = mtcRow.cellIterator();

                        while (mtcCellIterator.hasNext()) {
                            Cell mtcCell = mtcCellIterator.next();
                            if (mtcCell.getCellType() == 1 && (mtcCell.getStringCellValue().equals("Code")
                                    || mtcCell.getStringCellValue().equals("epSOS Code"))) {
                                mtcCol = mtcCell.getColumnIndex();
                                mtcColFound = true;
                                break;
                            }
                        }
                    } else {
                        mtcRow.getCell(mtcCol, Row.RETURN_NULL_AND_BLANK).setCellType(Cell.CELL_TYPE_STRING);
                        mtcRow2.getCell(mvcCol + 1, Row.CREATE_NULL_AS_BLANK)
                                .setCellType(Cell.CELL_TYPE_STRING);

                        for (int a = 0; a < mtcCodes.size(); a++) {
                            if (mtcRow.getCell(mtcCol).getStringCellValue().trim().equals(mtcCodes.get(a))) {
                                // delete row corresponding to useless code
                                delRows.add(mtcRow.getRowNum());
                                break;
                            }
                        }

                        if (!englishNamesdifferences.isEmpty()) {
                            for (int c = 0; c < englishNamesdifferences.size(); c = c + 3) {
                                if (mtcRow2.getCell(mtcCol + 1).getStringCellValue().trim()
                                        .equals(englishNamesdifferences.get(c + 1))) {
                                    mtcRow2.getCell(mtcCol + 1)
                                            .setCellValue(englishNamesdifferences.get(c + 2).toString());
                                    break;
                                }
                            }
                        }
                    }
                }
                for (int d = delRows.size() - 1; d >= 0; d--) {
                    mtcSheet2.shiftRows(delRows.get(d) + 1, mtcSheet2.getLastRowNum() + 1, -1);
                }
                myStyle = mtcSheet2.getRow(0).getCell(0).getCellStyle();
                for (int b = 0; b < mvcCodes.size(); b++) {
                    newRow = mtcSheet2.createRow(mtcSheet2.getLastRowNum() + 1);
                    for (int bb = 0; bb < mtcSheet2.getRow(0).getLastCellNum(); bb++) {
                        newCell = newRow.createCell(bb);
                        newCell.setCellStyle(myStyle);
                        if (bb == mtcCol) {
                            newCell.setCellValue(mvcCodes.get(b).toString());
                        } else if (bb == mtcCol + 1) {
                            newCell.setCellValue(mvcEnglishNames.get(b).toString());
                        }
                    }
                }
            }
        }
    }
    //close InputStream
    mtcFile.close();
    mtcFile2.close();
    mvcFile.close();
    //close OutputStream
    csvW.close();

    //Open FileOutputStream to write updates
    FileOutputStream output_file = new FileOutputStream(new File(
            "\\\\hes-nas-drize.hes.adhes.hesge.ch\\home\\luc.mottin\\Documents\\Expand\\MVC2.0\\MTC_2.0_new.xlsx"));
    //write changes
    mtcWorkbook2.write(output_file);
    //close the stream
    output_file.close();
}

From source file:controller.application.employee.ViewEmployeController.java

private void toExcel(ArrayList<ToExcelEmployee> lst, ArrayList<RFIDTimestamp> times, File file, Timestamp from,
        Timestamp to) throws IOException {

    try {/*from   w  ww.j av  a 2 s  .c  o m*/
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet spreadsheet = workbook.createSheet("Total arbetad tid " + file.getName());
        XSSFRow row = spreadsheet.createRow(0);
        XSSFCell cell;
        cell = row.createCell(0);
        cell.setCellValue("Frnamn:");
        cell = row.createCell(1);
        cell.setCellValue("Efternamn:");
        cell = row.createCell(2);
        cell.setCellValue("Anstllningsnummer:");
        cell = row.createCell(3);
        cell.setCellValue("Individuell tid (h) from.: " + (from.toString() + " tom.: " + to.toString())
                .replaceAll("(\\s)((\\p{Digit}{2}:){2}\\p{Digit}{2})\\..", ""));
        Double totAllTime = 0.0;
        for (int i = 1; i <= lst.size(); i++) {
            row = spreadsheet.createRow(i);
            cell = row.createCell(0);
            cell.setCellValue(lst.get(i - 1).surname);
            cell = row.createCell(1);
            cell.setCellValue(lst.get(i - 1).lastname);
            cell = row.createCell(2);
            cell.setCellValue(lst.get(i - 1).id);
            cell = row.createCell(3);
            cell.setCellValue(lst.get(i - 1).time);
            totAllTime += lst.get(i - 1).time;
            if (i == lst.size()) {
                row = spreadsheet.createRow(i + 1);
                cell = row.createCell(3);
                cell.setCellValue("Sammanstllning:");
                row = spreadsheet.createRow(i + 2);
                cell = row.createCell(3);
                cell.setCellValue(totAllTime);
            }

        }

        XSSFSheet spreadsheetTimes = workbook.createSheet("Tider");
        row = spreadsheetTimes.createRow(0);
        cell = row.createCell(0);
        cell.setCellValue("RFID");
        cell = row.createCell(1);
        cell.setCellValue("IN/UT");
        cell = row.createCell(2);
        cell.setCellValue("Datum/Tid");

        for (int i = 1; i <= times.size(); i++) {
            row = spreadsheetTimes.createRow(i);
            cell = row.createCell(0);
            cell.setCellValue(times.get(i - 1).getRFID().toString());
            cell = row.createCell(1);
            cell.setCellValue(times.get(i - 1).getInOut());
            cell = row.createCell(2);
            cell.setCellValue(times.get(i - 1).getTime());
        }
        for (int k = 0; k < spreadsheet.getRow(0).getLastCellNum(); k++) {
            spreadsheet.autoSizeColumn(k);
        }
        for (int j = 0; j < spreadsheetTimes.getRow(0).getLastCellNum(); j++) {
            spreadsheetTimes.autoSizeColumn(j);
        }

        try (FileOutputStream out = new FileOutputStream(file)) {
            workbook.write(out);
        }
        System.out.println(file.getName() + " written successfully");
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }
}