Example usage for org.apache.poi.xssf.usermodel XSSFWorkbook getSheetAt

List of usage examples for org.apache.poi.xssf.usermodel XSSFWorkbook getSheetAt

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.xssf.usermodel.*
  5. XSSFWorkbook
  6. getSheetAt

Introduction

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

Prototype

@Override
public XSSFSheet getSheetAt(int index)

Source Link

Document

Get the XSSFSheet object at the given index.

Usage

From source file:com.heimaide.server.common.utils.excel.ExportExcel.java

License:Open Source License

private void exportFile(List<String> list, String rootPath, HttpServletResponse response,
        List<List<Object>> bodyList) throws FileNotFoundException, IOException {
    String importFileName = rootPath + "WEB-INF" + File.separator + "xlsx" + File.separator + "order.xlsx";
    String tmpDir = rootPath + "tmpDir" + File.separator;
    XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(importFileName));
    this.styles = createStyles(wb);
    //?excel?  /*w  ww .  j  av  a 2  s .c o m*/
    //??sheet  
    XSSFSheet sheet = wb.getSheetAt(0);
    XSSFCell cell1 = sheet.getRow(1).getCell(0);
    String cell1str = cell1.getStringCellValue();
    //????
    cell1str = cell1str.replace("X", list.get(0));
    cell1.setCellValue(cell1str);
    XSSFCell cell2 = sheet.getRow(2).getCell(0);
    String cell2str = cell2.getStringCellValue();
    //??
    cell2str = cell2str.replace("X", list.get(1));
    //?
    cell2str = cell2str.replace("Y", list.get(2));
    //???
    cell2str = cell2str.replace("Z", list.get(3));
    cell2.setCellValue(cell2str);
    XSSFCell cell3 = sheet.getRow(3).getCell(12);
    String cell3str = cell3.getStringCellValue();
    cell3str = cell3str.replace("XX", list.get(4));
    cell3.setCellValue(cell3str);

    XSSFCell cell4 = sheet.getRow(sheet.getLastRowNum() - 1).getCell(0);
    String cell4str = cell4.getStringCellValue();
    cell4str = cell4str.replace("X", list.get(5));
    cell4.setCellValue(cell4str);

    XSSFCell cell5 = sheet.getRow(sheet.getLastRowNum() - 4).getCell(12);
    XSSFCell cell6 = sheet.getRow(sheet.getLastRowNum() - 4).getCell(13);
    String str1 = String.valueOf(list.get(6));
    String str2 = String.valueOf(list.get(7));
    str1 = str1.substring(0, str1.indexOf("."));
    str2 = str2.substring(0, str2.indexOf("."));
    cell5.setCellValue(str1);

    cell6.setCellValue(str2);

    XSSFCell cell7 = sheet.getRow(sheet.getLastRowNum() - 3).getCell(12);
    XSSFCell cell8 = sheet.getRow(sheet.getLastRowNum() - 3).getCell(13);
    cell7.setCellValue(Double.parseDouble(list.get(6)) / 10);
    cell8.setCellValue(Double.parseDouble(list.get(7)) / 10);
    //                 int columnNum =sheet.getRow(3).getLastCellNum();
    //                 int column =0;
    //                 for(int i=0;i<columnNum;i++)
    //                 {
    //                    System.out.println("==="+sheet.getRow(3).getCell(i).getStringCellValue());
    //                    column =i;
    //                 }

    int num = 5;
    for (int i = 0; i < bodyList.size(); i++) {
        List<Object> strList = bodyList.get(i);
        sheet.shiftRows(num, bodyList.size() + num, 1, true, false);
        sheet.createRow(num);

        for (int j = 0; j < strList.size(); j++) {
            Row row = sheet.getRow(num);
            addCell(row, j, strList.get(j));
            //                       Cell cell = row.createCell(j);
            //                       cell.setCellValue(strList.get(j));
        }
        num++;
    }

    //??  
    String tmpName = tmpDir + "tmp_hmd.xlsx";
    File dirFile = new File(tmpDir);
    if (!dirFile.exists()) {
        dirFile.mkdir();
    }
    File tmpFile = new File(tmpName);
    FileOutputStream fos = new FileOutputStream(tmpFile);
    wb.write(fos);

    XSSFWorkbook wb1 = new XSSFWorkbook(new FileInputStream(tmpName));
    response.reset();
    response.setContentType("application/octet-stream; charset=utf-8");
    response.setHeader("Content-Disposition",
            "attachment; filename=" + Encodes.urlEncode("?" + list.get(0) + "?.xlsx"));
    wb1.write(response.getOutputStream());

    tmpFile.delete();
}

From source file:com.hp.autonomy.frontend.reports.powerpoint.PowerPointServiceImpl.java

License:MIT License

/**
 * Internal implementation to add a date graph (aka xy scatterplot chart with time-series x-axis) to a slide, based on a template.
 * @param template the parsed template information.
 * @param slide the slide to add to.//from w  ww .  j a v a 2 s  . co  m
 * @param anchor optional bounding rectangle to draw onto, in PowerPoint coordinates.
 *               If null, we'll use the bounds from the original template chart.
 * @param data the date graph data.
 * @param shapeId the slide shape ID, should be unique within the slide.
 * @param relId the relation ID to the chart data.
 * @throws TemplateLoadException if we can't create the date graph; most likely due to an invalid template.
 */
private static void addDategraph(final SlideShowTemplate template, final XSLFSlide slide,
        final Rectangle2D.Double anchor, final DategraphData data, final int shapeId, final String relId)
        throws TemplateLoadException {
    if (!data.validateInput()) {
        throw new IllegalArgumentException("Invalid data provided");
    }

    final List<DategraphData.Row> rows = data.getRows();
    boolean useSecondaryAxis = rows.stream().anyMatch(DategraphData.Row::isSecondaryAxis);

    if (rows.stream().allMatch(DategraphData.Row::isSecondaryAxis)) {
        // If everything is on the secondary axis; just use the primary axis
        rows.forEach(row -> row.setSecondaryAxis(false));
        useSecondaryAxis = false;
    }

    final XSSFWorkbook wb = writeChart(data);

    final XMLSlideShow ppt = template.getSlideShow();

    slide.getXmlObject().getCSld().getSpTree().addNewGraphicFrame()
            .set(template.getGraphChartShapeXML(relId, shapeId, "chart" + shapeId, anchor));

    XSLFChart baseChart = template.getGraphChart();
    final CTChartSpace chartSpace = (CTChartSpace) baseChart.getCTChartSpace().copy();

    final CTChart ctChart = chartSpace.getChart();
    final CTPlotArea plotArea = ctChart.getPlotArea();
    final XSSFSheet sheet = wb.getSheetAt(0);

    // In the template, we have two <c:scatterChart> objects, one for the primary axis, one for the secondary.
    if (!useSecondaryAxis) {
        // Discard the extra chart and its two axes.
        // OpenOffice is happy enough if you remove the scatterplot chart, but PowerPoint will complain it's a corrupt
        //   file and unhelpfully delete the entire chart when you choose 'repair' if any orphan axes remain.
        plotArea.removeScatterChart(1);
        plotArea.removeValAx(3);
        plotArea.removeValAx(2);
    }

    for (CTScatterChart ctScatterChart : plotArea.getScatterChartArray()) {
        for (final CTScatterSer ser : ctScatterChart.getSerArray()) {
            ser.getDPtList().clear();
        }
    }

    int primarySeriesCount = 0;
    int secondarySeriesCount = 0;

    for (int seriesIdx = 0; seriesIdx < rows.size(); ++seriesIdx) {
        final DategraphData.Row row = rows.get(seriesIdx);

        final CTScatterChart tgtChart = plotArea.getScatterChartArray(row.isSecondaryAxis() ? 1 : 0);

        final CTScatterSer[] serArray = tgtChart.getSerArray();
        final int createdSeriesIdx = row.isSecondaryAxis() ? secondarySeriesCount++ : primarySeriesCount++;

        final CTScatterSer curSeries;

        if (createdSeriesIdx < serArray.length) {
            curSeries = serArray[createdSeriesIdx];
        } else {
            curSeries = tgtChart.addNewSer();
            curSeries.set(serArray[0].copy());
        }

        updateCTScatterSer(data, sheet, seriesIdx, curSeries);
    }

    try {
        writeChart(ppt, slide, baseChart, chartSpace, wb, relId);
    } catch (IOException | InvalidFormatException e) {
        throw new TemplateLoadException("Unexpected error writing files from loaded template", e);
    }
}

From source file:com.huawei.gsm.controller.AdminController.java

private void parseExcelFile(File file) throws IOException, InvalidFormatException {

    XSSFWorkbook workbook = new XSSFWorkbook(file);
    XSSFSheet sheet = workbook.getSheetAt(0);

    Iterator<Row> rowIterator = sheet.iterator();

    Map<String, Site> sites = new HashMap<>();
    Set<Cell> cells = new HashSet<>();

    if (rowIterator.hasNext()) {
        rowIterator.next();//from w  w w .j  a  v  a 2  s  .  c o m
    }

    int i = 0;

    while (rowIterator.hasNext()) {

        Row row = rowIterator.next();
        String siteId;
        String cellIndex;
        String cellId;

        try {
            row.getCell(1).getStringCellValue();
        } catch (NullPointerException ex) {
            break;
        }

        if (row.getCell(0).getCellType() == XSSFCell.CELL_TYPE_NUMERIC) {
            double sId = row.getCell(0).getNumericCellValue();
            siteId = String.valueOf(sId).replaceAll("\\.?0*$", "");
        } else {
            siteId = row.getCell(0).getStringCellValue();
        }

        if (row.getCell(6).getCellType() == XSSFCell.CELL_TYPE_NUMERIC) {
            double cId = row.getCell(6).getNumericCellValue();
            cellIndex = String.valueOf(cId).replaceAll("\\.?0*$", "");
        } else {
            cellIndex = row.getCell(6).getStringCellValue();
        }

        if (row.getCell(8).getCellType() == XSSFCell.CELL_TYPE_NUMERIC) {
            double ceId = row.getCell(8).getNumericCellValue();
            cellId = String.valueOf(ceId).replaceAll("\\.?0*$", "");
        } else {
            cellId = row.getCell(8).getStringCellValue();
        }

        Site site = new Site();
        site.setAddress(row.getCell(2).getStringCellValue());
        site.setGroup((row.getCell(5).getStringCellValue().toLowerCase().equalsIgnoreCase("GOLDEN_SITE")
                ? SiteGroup.GOLDEN_SITE
                : SiteGroup.EVENT_SITE));
        site.setSiteId(siteId);
        site.setSiteName(row.getCell(1).getStringCellValue());
        site.setLatitude(row.getCell(4).getNumericCellValue());
        site.setLongitude(row.getCell(3).getNumericCellValue());
        site.setCells(new ArrayList<>());

        sites.put(siteId, site);

        Cell cell = new Cell();
        cell.setCellId(cellId);
        cell.setCellIndex(cellIndex);
        cell.setCellName(row.getCell(7).getStringCellValue());
        cell.setFrequency(row.getCell(9).getStringCellValue());
        cell.setSite(site);

        cells.add(cell);

        i++;

    }

    Iterator<Site> iter = sites.values().iterator();
    while (iter.hasNext()) {
        Site site = iter.next();

        cells.stream().filter((Cell s) -> {
            return s.getSite().getSiteId().equalsIgnoreCase(site.getSiteId());
        }).forEach(s -> {
            site.getCells().add(s);
        });

    }

    List<Site> sitesX = new ArrayList<>(sites.values());

    siteService.saveSiteBatch(sitesX);

}

From source file:com.iana.boesc.utility.BOESCUtil.java

License:Open Source License

public static XSSFSheet getWorkSheet(File incoming_file) throws IOException {
    FileInputStream file = new FileInputStream(incoming_file);
    //Create Workbook instance holding reference to .xlsx file
    XSSFWorkbook workbook = new XSSFWorkbook(file);
    //Get first/desired sheet from the workbook
    XSSFSheet sheet = workbook.getSheetAt(0);
    return sheet;
}

From source file:com.infovity.iep.loader.util.SupplierLoaderUtil.java

public static ArrayList<String[]> getDataFromFile(File inputFile, String sheetName) {
    ArrayList<String[]> data = new ArrayList<String[]>();
    File selectedFile = null;//from   ww  w  .j  a v  a 2s  .c o  m
    FileInputStream fis = null;
    ;
    XSSFWorkbook workbook = null;
    //inputFile = new File("C:\\Users\\INFOVITY-USER-029\\Desktop\\DataLoader\\Consolidated Supplier Data Capture Template v4.0.xlsx");
    boolean sheetFound = false;
    XSSFSheet sheet = null;
    try {

        int sheetIndex = -1;
        fis = new FileInputStream(inputFile);
        workbook = new XSSFWorkbook(fis);

        int noOfSheets = workbook.getNumberOfSheets();
        for (int i = 0; i < noOfSheets; i++) {
            sheet = workbook.getSheetAt(i);
            if (sheet.getSheetName().equals(sheetName)) {
                sheetFound = true;
                sheetIndex = i;
                selectedFile = inputFile;
                break;
            }
        }
        XSSFWorkbook myWorkBook;

        try {
            myWorkBook = new XSSFWorkbook(selectedFile);
            // Return first sheet from the XLSX workbook
            // XSSFSheet mySheet = myWorkBook.getSheetAt(0);
            // Get iterator to all the rows in current sheet
            Iterator<Row> rowIterator = sheet.iterator();
            String query;
            String[] values = null;
            // Traversing over each row of XLSX file
            while (rowIterator.hasNext()) {
                Row row = rowIterator.next();
                if (rowHasData(row) && (row.getRowNum() >= 9)) {
                    int endColumn = row.getLastCellNum();
                    int startColumn = row.getFirstCellNum();
                    // For each row, iterate through each columns
                    values = new String[endColumn + 2];
                    for (int i = startColumn; i < endColumn; i++) {
                        String cellValue;
                        Cell cell = row.getCell(i);
                        values[0] = Integer.toString(row.getRowNum() + 1);
                        if (cell != null) {
                            if (cell.getCellType() == cell.CELL_TYPE_NUMERIC
                                    && DateUtil.isCellDateFormatted(cell)) {
                                DateFormat df = new SimpleDateFormat("yyyy/MM/dd");
                                Date cellDateValue = cell.getDateCellValue();
                                cellValue = df.format(cellDateValue);
                            } else {
                                cell.setCellType(cell.CELL_TYPE_STRING);
                                cellValue = cell.getStringCellValue().replaceAll("'", "");
                            }
                            if (!"".equals(cellValue) && cellValue != null) {
                                values[i + 1] = cellValue;
                            } else if (cellValue.isEmpty() || "".equals(cellValue)) {
                                values[i + 1] = "";
                            }
                        } else {
                            values[i + 1] = "";
                        }
                    }
                    data.add(values);
                }

            }
        } catch (InvalidFormatException ex) {
            Logger.getLogger(SupplierLoaderUtil.class.getName()).log(Level.ERROR, null, ex);
        }
    } catch (IOException ex) {
        Logger.getLogger(SupplierLoaderUtil.class.getName()).log(Level.ERROR, null, ex);
    } finally {
        try {
            fis.close();
            workbook.close();
        } catch (IOException ex) {
            Logger.getLogger(SupplierLoaderUtil.class.getName()).log(Level.ERROR, null, ex);
        }
    }

    return data;
}

From source file:com.joalgoca.validatorLayout.layoutDefinition.XLSXDocumentLayout.java

@Override
public ResponseValidator validateDocument(InputStream inputStream) {
    ResponseValidator response;/*from   w  w  w .  j  av a  2 s. co m*/
    StringBuilder stringBuilder = new StringBuilder();
    if (isReadyToValidate() && inputStream != null) {
        HashMap rowsType = new HashMap();
        for (int i = 0; i < documentValidator.getListRowValidator().size(); i++) {
            rowsType.put(documentValidator.getListRowValidator().get(i).getName(), i);
        }
        try {
            int rownum = 0;
            int wrong = 0;
            int right = 0;
            int skip = 0;
            OPCPackage pkg = OPCPackage.open(inputStream);
            XSSFWorkbook workBook = new XSSFWorkbook(pkg);
            FormulaEvaluator evaluator = workBook.getCreationHelper().createFormulaEvaluator();
            XSSFSheet xssfSheet = workBook.getSheetAt(0);
            for (Row row : xssfSheet) {
                String rowType = row.getCell(0).getStringCellValue();
                if (rowsType.containsKey(rowType)) {
                    RowValidator rowValidator = documentValidator.getListRowValidator()
                            .get((int) rowsType.get(rowType));
                    int columnNum = rowValidator.getListItemValidator().size();
                    if (row.getLastCellNum() == columnNum) {
                        String[] values = new String[columnNum];
                        int i = 0;
                        for (Cell cell : row) {
                            switch (evaluator.evaluateInCell(cell).getCellType()) {
                            case Cell.CELL_TYPE_NUMERIC:
                                values[i] = cell.getNumericCellValue() + "";
                                break;
                            case Cell.CELL_TYPE_STRING:
                                values[i] = cell.getStringCellValue();
                                break;
                            case Cell.CELL_TYPE_FORMULA:
                                values[i] = "";
                                break;
                            case Cell.CELL_TYPE_BLANK:
                                values[i] = "";
                                break;
                            }
                            i++;
                        }
                        ResponseValidator responseValidator = rowValidator.validate(values);
                        if (!responseValidator.isSuccess()) {
                            wrong++;
                            stringBuilder.append("{\"row\":").append(rownum).append(",\"message\":")
                                    .append(responseValidator.getMessage()).append(",");
                        } else
                            right++;
                    } else {
                        wrong++;
                        stringBuilder.append("{\"row\":").append(rownum)
                                .append(",\"success\":false,\"message\":\"Line wrong size\"},").toString();
                    }
                } else {
                    skip++;
                    stringBuilder.append("{\"row\":").append(rownum)
                            .append(",\"success\":false,\"message\":\"Unknow row type\"},").toString();
                }
                rownum++;

            }

            response = new ResponseValidator(wrong == 0,
                    "{\"skip\":" + skip + ",\"wrong\":" + wrong + ",\"right\":" + right + ",\"count\":" + rownum
                            + ",\"errorMessages\":["
                            + (stringBuilder.toString().length() > 0
                                    ? stringBuilder.substring(0, stringBuilder.toString().length() - 1)
                                    : "")
                            + "]}");
        } catch (Exception ex) {
            Logger.getLogger(FlatFixedDocumentLayout.class.getName()).log(Level.SEVERE, null, ex);
            response = new ResponseValidator(false, stringBuilder.append("\"success\":false,\"message\":\"")
                    .append(ex.getMessage()).append("\"}").toString());
        } finally {
            try {
                inputStream.close();
            } catch (IOException ex) {
                response = new ResponseValidator(false, stringBuilder.append("\"success\":false,\"message\":\"")
                        .append(ex.getMessage()).append("\"}").toString());
            }
        }
    } else {
        response = new ResponseValidator(false,
                stringBuilder.append("\"success\":false,\"message\":\"No configuration loaded\"}").toString());
    }
    return response;
}

From source file:com.krawler.spring.importFunctionality.ImportUtil.java

License:Open Source License

/**
 * Generate the preview of the xls grid/*w w  w.j a v  a 2  s  . c  o  m*/
 * @param filename
 * @param sheetNo
 * @return
 * @throws FileNotFoundException
 * @throws IOException
 * @throws JSONException
 */
public static JSONObject parseXLSX(String filename, int sheetNo)
        throws FileNotFoundException, IOException, JSONException {
    JSONObject jobj = new JSONObject();
    FileInputStream fs = new FileInputStream(filename);
    XSSFWorkbook wb = new XSSFWorkbook(fs);
    XSSFSheet sheet = wb.getSheetAt(sheetNo);
    //DateFormat sdf = new SimpleDateFormat(df);

    int startRow = 0;
    int maxRow = sheet.getLastRowNum();
    int maxCol = 0;
    int noOfRowsDisplayforSample = 20;
    if (noOfRowsDisplayforSample > sheet.getLastRowNum()) {
        noOfRowsDisplayforSample = sheet.getLastRowNum();
    }

    JSONArray jArr = new JSONArray();
    try {
        for (int i = 0; i <= noOfRowsDisplayforSample; i++) {
            XSSFRow row = sheet.getRow(i);
            JSONObject obj = new JSONObject();
            JSONObject jtemp1 = new JSONObject();
            if (row == null) {
                continue;
            }
            if (i == 0) {
                maxCol = row.getLastCellNum();
            }
            for (int cellcount = 0; cellcount < maxCol; cellcount++) {
                XSSFCell cell = row.getCell(cellcount);
                CellReference cref = new CellReference(i, cellcount);
                String colHeader = cref.getCellRefParts()[2];
                String val = null;

                if (cell != null) {
                    switch (cell.getCellType()) {
                    case XSSFCell.CELL_TYPE_NUMERIC:
                        if (DateUtil.isCellDateFormatted(cell)) {
                            val = Long.toString(cell.getDateCellValue().getTime());
                        } else {
                            val = dfmt.format(cell.getNumericCellValue());
                        }
                        break;
                    case XSSFCell.CELL_TYPE_STRING:
                        val = ImportUtil.cleanHTML(cell.getRichStringCellValue().getString());
                        break;
                    }
                }

                if (i == 0) { // List of Headers (Consider first row as Headers)
                    if (val != null) {
                        jtemp1 = new JSONObject();
                        jtemp1.put("header", val == null ? "" : val);
                        jtemp1.put("index", cellcount);
                        jobj.append("Header", jtemp1);
                    }
                }
                obj.put(colHeader, val);
            }
            //                    if(obj.length()>0){ //Don't show blank row in preview grid[SK]
            jArr.put(obj);
            //                    }
        }
    } catch (Exception ex) {
        Logger.getLogger(ImportHandler.class.getName()).log(Level.SEVERE, null, ex);
    }
    jobj.put("startrow", startRow);
    jobj.put("maxrow", maxRow);
    jobj.put("maxcol", maxCol);
    jobj.put("index", sheetNo);
    jobj.put("data", jArr);
    jobj.put("filename", filename);

    jobj.put("msg", "XLSX has been successfully uploaded");
    jobj.put("lsuccess", true);
    jobj.put("valid", true);
    return jobj;
}

From source file:com.krawler.spring.importFunctionality.ImportUtil.java

License:Open Source License

/**
 * @param filename/*from w  ww.  j  a v  a  2  s  .  c  o m*/
 * @param sheetNo
 * @param startindex
 * @param importDao
 * @return
 * @throws ServiceException
 */
public static void dumpXLSXFileData(String filename, int sheetNo, int startindex, ImportDAO importDao,
        HibernateTransactionManager txnManager) throws ServiceException {
    boolean commitedEx = false;
    DefaultTransactionDefinition def = new DefaultTransactionDefinition();
    def.setName("import_Tx");
    def.setPropagationBehavior(TransactionDefinition.PROPAGATION_REQUIRED);
    TransactionStatus status = txnManager.getTransaction(def);
    Session session = txnManager.getSessionFactory().getCurrentSession();
    try {
        String destinationDirectory = storageHandlerImpl.GetDocStorePath() + "xlsfiles";
        FileInputStream fs = new FileInputStream(destinationDirectory + "/" + filename);
        XSSFWorkbook wb = new XSSFWorkbook(fs);
        XSSFSheet sheet = wb.getSheetAt(sheetNo);
        //DateFormat sdf = new SimpleDateFormat(df_full);
        int maxRow = sheet.getLastRowNum();
        int maxCol = 0;
        String tableName = importDao.getTableName(filename);
        int flushCounter = 0;
        for (int i = startindex; i <= maxRow; i++) {
            XSSFRow row = sheet.getRow(i);
            if (row == null) {
                continue;
            }
            if (i == startindex) {
                maxCol = row.getLastCellNum(); //Column Count
            }
            ArrayList<String> dataArray = new ArrayList<String>();
            JSONObject dataObj = new JSONObject();
            for (int j = 0; j < maxCol; j++) {
                XSSFCell cell = row.getCell(j);
                String val = null;
                if (cell == null) {
                    dataArray.add(val);
                    continue;
                }
                String colHeader = new CellReference(i, j).getCellRefParts()[2];
                switch (cell.getCellType()) {
                case XSSFCell.CELL_TYPE_NUMERIC:
                    if (DateUtil.isCellDateFormatted(cell)) {
                        val = Long.toString(cell.getDateCellValue().getTime());
                    } else {
                        val = dfmt.format(cell.getNumericCellValue());
                    }
                    break;
                case XSSFCell.CELL_TYPE_STRING:
                    val = ImportUtil.cleanHTML(cell.getRichStringCellValue().getString());
                    break;
                }
                dataObj.put(colHeader, val);
                dataArray.add(val); //Collect row data
            }
            //Insert Query
            if (dataObj.length() > 0) { // Empty row check (if lenght==0 then all columns are empty)
                importDao.dumpFileRow(tableName, dataArray.toArray());
                if (flushCounter % 30 == 0) {
                    session.flush();
                    session.clear();
                }
                flushCounter++;
            }

        }
        try {
            txnManager.commit(status);
        } catch (Exception ex) {
            commitedEx = true;
            throw ex;
        }
    } catch (IOException ex) {
        throw ServiceException.FAILURE("dumpXLSXFileData: " + ex.getMessage(), ex);
    } catch (Exception ex) {
        if (!commitedEx) { //if exception occurs during commit then dont call rollback
            txnManager.rollback(status);
        }
        throw ServiceException.FAILURE("dumpXLSXFileData: " + ex.getMessage(), ex);
    }
}

From source file:com.larasolution.serverlts.FileUploadHandler.java

protected void processRequest(HttpServletRequest request, HttpServletResponse response)
        throws ServletException, IOException {
    //     tablename=request.getParameter(tablename)
    //process only if its multipart content
    FileOutputStream fos = new FileOutputStream("C:\\uploads\\data.csv");
    String list = "";
    List<List> allData = new ArrayList<List>();

    List<String> parameters = new ArrayList<String>();
    if (ServletFileUpload.isMultipartContent(request)) {

        try {/*from ww  w  . j a v  a  2s .  co  m*/

            StringBuilder data = new StringBuilder();
            List<FileItem> multiparts = new ServletFileUpload(new DiskFileItemFactory()).parseRequest(request);
            System.out.println(multiparts);
            for (FileItem item : multiparts) {
                if (item.isFormField()) {
                    parameters.add(item.getFieldName());
                    System.out.println(parameters);
                }
                if (!item.isFormField()) {
                    String name = new File(item.getName()).getName();

                    item.write(new File(UPLOAD_DIRECTORY + File.separator + name));
                    //System.out.println(File.separator);
                    // Get the workbook object for XLSX file
                    XSSFWorkbook wBook = new XSSFWorkbook(
                            new FileInputStream(UPLOAD_DIRECTORY + File.separator + name));

                    XSSFSheet zz = wBook.getSheetAt(0);
                    FormulaEvaluator formulaEval = wBook.getCreationHelper().createFormulaEvaluator();

                    Row row;
                    Cell cell;

                    // Iterate through each rows from first sheet
                    Iterator<Row> rowIterator = zz.iterator();
                    while (rowIterator.hasNext()) {
                        row = rowIterator.next();

                        // For each row, iterate through each columns
                        Iterator<Cell> cellIterator = row.cellIterator();

                        while (cellIterator.hasNext()) {

                            cell = cellIterator.next();

                            switch (cell.getCellType()) {
                            case Cell.CELL_TYPE_BOOLEAN:
                                data.append(cell.getBooleanCellValue()).append(",");
                                break;
                            case Cell.CELL_TYPE_NUMERIC:
                                if (DateUtil.isCellDateFormatted(cell)) {
                                    data.append(
                                            com.larasolution.modle.getDate.getDate5(cell.getDateCellValue()))
                                            .append(",");
                                } else {
                                    data.append(cell.getNumericCellValue()).append(",");
                                }

                                break;
                            case Cell.CELL_TYPE_STRING:
                                data.append(cell.getStringCellValue()).append(",");
                                break;
                            case Cell.CELL_TYPE_BLANK:
                                data.append("" + ",");
                                break;
                            case Cell.CELL_TYPE_FORMULA:
                                Double value = Double.parseDouble(formulaEval.evaluate(cell).formatAsString());

                                data.append(String.format("%.2f", value)).append(",");
                                break;
                            default:
                                data.append(cell).append("");

                            }

                        }
                        data.append("\r\n");
                        //String k = data.substring(0, data.length() - 3);
                        //ls.add(k);

                        // data.setLength(0);
                    }

                    fos.write(data.toString().getBytes());
                    fos.close();

                    //
                }
            }

            savetosql();
            request.setAttribute("message", "successfully uploaded ");
        } catch (Exception ex) {
            request.setAttribute("message", "File Upload Failed due to " + ex);
        }

    } else {
        request.setAttribute("message", "Sorry this Servlet only handles file upload request");
    }

    request.setAttribute("arrayfile", allData);
    request.setAttribute("names", parameters);
    RequestDispatcher disp = getServletContext().getRequestDispatcher("/FileUploadResult.jsp");
    disp.forward(request, response);

    // System.out.println(allData.size());
    // response.sendRedirect("send.jsp?arrayfile=" + list + "");
    //request.getRequestDispatcher("/send.jsp?arrayfile='"+ls+"'").forward(request, response);
}

From source file:com.maxl.java.aips2sqlite.DispoParse.java

License:Open Source License

private XSSFSheet getSheetsFromFile(String filename, int n) {
    XSSFSheet sheet = null;// w ww.  j  a v a2  s .co m
    try {
        FileInputStream file = new FileInputStream(filename);
        // Get workbook
        XSSFWorkbook workbook = new XSSFWorkbook(file);
        // Get sheet
        sheet = workbook.getSheetAt(n);
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    }

    return sheet;
}