Example usage for org.apache.poi.ss.usermodel Workbook getSheet

List of usage examples for org.apache.poi.ss.usermodel Workbook getSheet

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Workbook
  6. getSheet

Introduction

In this page you can find the example usage for org.apache.poi.ss.usermodel Workbook getSheet.

Prototype

Sheet getSheet(String name);

Source Link

Document

Get sheet with the given name

Usage

From source file:com.gtja.qh.TransferCtrl.TransferCtrl.java

private boolean transferToDbf(String inputFilePath, String outFileDir) throws DBFException {

    //DBF,?//from  w  ww  .  jav  a  2 s  .c o m
    DBFField[] fields = new DBFField[6];

    fields[0] = new DBFField();
    fields[0].setName("ACCOUNTID");
    fields[0].setDataType(DBFField.FIELD_TYPE_C);
    fields[0].setFieldLength(6);

    fields[1] = new DBFField();
    fields[1].setName("PARTID");
    fields[1].setDataType(DBFField.FIELD_TYPE_C);
    fields[1].setFieldLength(4);

    fields[2] = new DBFField();
    fields[2].setName("CLIENTID");
    fields[2].setDataType(DBFField.FIELD_TYPE_C);
    fields[2].setFieldLength(8);

    fields[3] = new DBFField();
    fields[3].setName("AMOUNT");
    fields[3].setDataType(DBFField.FIELD_TYPE_N);
    fields[3].setFieldLength(23);
    fields[3].setDecimalCount(2);

    fields[4] = new DBFField();
    fields[4].setName("MONEYTYPE");
    fields[4].setDataType(DBFField.FIELD_TYPE_C);
    fields[4].setFieldLength(4);

    fields[5] = new DBFField();
    fields[5].setName("TYPEMEMO");
    fields[5].setDataType(DBFField.FIELD_TYPE_C);
    fields[5].setFieldLength(40);
    DBFWriter writer = new DBFWriter();
    try {
        writer.setFields(fields);
    } catch (Exception e) {
        e.printStackTrace();
        return false;
    }
    //excel??
    File inputFile = new File(inputFilePath);
    String inputFileName = inputFile.getName();
    String extension = inputFileName.lastIndexOf(".") == -1 ? ""
            : inputFileName.substring(inputFileName.lastIndexOf(".") + 1);
    if ("xls".equals(extension)) {
        //JXL?excel 2003??xlsx?
        try {
            //?excel
            InputStream is = new FileInputStream(inputFilePath);
            jxl.Workbook rwb = jxl.Workbook.getWorkbook(is);
            jxl.Sheet rs = rwb.getSheet(0);
            int rsRows = rs.getRows();
            for (int i = 1; i < rsRows; i++) {
                if (rs.getCell(5, i).getContents().equals("")) {
                    Object[] rowData = new Object[6];
                    rowData[0] = "000101";
                    rowData[1] = "0001";
                    rowData[4] = "A999";
                    String tradeCode = rs.getCell(4, i).getContents();
                    Double amount = null;
                    if (rs.getCell(6, i).getType() == CellType.NUMBER) {
                        NumberCell numberCell = (NumberCell) rs.getCell(6, i);
                        amount = numberCell.getValue();
                    } else {
                        if (rs.getCell(6, i).getContents().length() == 0) {
                            amount = null;
                        } else {
                            amount = new DecimalFormat("0.00").parse(rs.getCell(6, i).getContents())
                                    .doubleValue(); //String?Double 
                        }

                    }
                    String typeMemo = rs.getCell(7, i).getContents();
                    rowData[2] = tradeCode;
                    rowData[3] = amount;
                    rowData[5] = typeMemo;
                    writer.addRecord(rowData);
                } else {
                    continue;
                }

            }
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    } else if ("xlsx".equals(extension)) {
        //POI ?excel 2007,??excel 2003
        try {
            InputStream fs = new FileInputStream(inputFilePath);
            XSSFWorkbook wb;
            wb = new XSSFWorkbook(fs);
            //wb = new XSSFWorkbook(inputFilePath);
            XSSFSheet sheet = wb.getSheetAt(0);
            int rows = sheet.getPhysicalNumberOfRows();
            for (int i = 1; i < rows; i++) {

                Row row = sheet.getRow(i);
                if (row == null) {
                    continue;
                }
                if (row.getCell(5).getStringCellValue().equals("")) {
                    Object[] rowData = new Object[6];
                    rowData[0] = "000101";
                    rowData[1] = "0001";
                    rowData[4] = "A999";
                    if (row.getCell(4) == null) {
                        row.createCell(4);
                        row.getCell(4).setCellValue("");
                    }
                    if (row.getCell(6) == null) {
                        row.createCell(6);
                        row.getCell(6).setCellValue("");
                    }
                    if (row.getCell(7) == null) {
                        row.createCell(7);
                        row.getCell(7).setCellValue("");
                    }
                    String tradeCode = row.getCell(4).getStringCellValue();
                    Double amount = null;
                    if (row.getCell(6).getCellType() == CELL_TYPE_NUMERIC) {
                        amount = row.getCell(6).getNumericCellValue();
                    } else {
                        row.getCell(6).setCellType(CELL_TYPE_STRING);
                        if (row.getCell(6).getStringCellValue().length() == 0) {
                            amount = null;
                        } else {
                            amount = new DecimalFormat("0.00").parse(row.getCell(6).getStringCellValue())
                                    .doubleValue();
                        }
                    }
                    String typeMemo = row.getCell(7).getStringCellValue();
                    rowData[2] = tradeCode;
                    rowData[3] = amount;
                    rowData[5] = typeMemo;
                    writer.addRecord(rowData);
                } else {
                    continue;
                }

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

    try {
        //
        String fileName = "0001_SG01_" + frame.getDate().getText() + "_1_ClientCapitalDetail";
        String outFile = outFileDir + "\\" + fileName + ".dbf";
        File file = new File(outFile);
        if (!file.exists()) {
            file.createNewFile();
        }
        OutputStream os = new FileOutputStream(file);
        writer.write(os); //? 
        os.close();
        return true;
    } catch (Exception e) {
        e.printStackTrace();
        JOptionPane.showMessageDialog(null, "???",
                "?", JOptionPane.ERROR_MESSAGE);
        return false;
    }
}

From source file:com.helger.peppol.supplementary.tools.MainCreateCodelistsFilesFromExcel.java

License:Mozilla Public License

public static void main(final String[] args) throws IOException, URISyntaxException {
    // Where is the Excel?
    final IReadableResource aXls = new FileSystemResource(EXCEL_FILE);
    if (!aXls.exists())
        throw new IllegalStateException("The Excel file could not be found!");

    // Interprete as Excel
    final Workbook aWB = new HSSFWorkbook(aXls.getInputStream());

    // Check whether all required sheets are present
    final Sheet aParticipantSheet = aWB.getSheet(SHEET_PARTICIPANT);
    if (aParticipantSheet == null)
        throw new IllegalStateException("The " + SHEET_PARTICIPANT + " sheet could not be found!");
    final Sheet aDocumentSheet = aWB.getSheet(SHEET_DOCUMENT);
    if (aDocumentSheet == null)
        throw new IllegalStateException("The " + SHEET_DOCUMENT + " sheet could not be found!");
    final Sheet aProcessSheet = aWB.getSheet(SHEET_PROCESS);
    if (aProcessSheet == null)
        throw new IllegalStateException("The " + SHEET_PROCESS + " sheet could not be found!");

    // Convert participants
    _emitIdentifierIssuingAgency(aParticipantSheet);

    // Convert document identifiers
    _emitDocumentIdentifiers(aDocumentSheet);

    // Convert processes identifiers
    _emitProcessIdentifier(aProcessSheet);

    // Write all Java source files
    final FileCodeWriter aWriter = new FileCodeWriter(new File("src/main/java"), CCharset.CHARSET_UTF_8_OBJ);
    s_aCodeModel.build(aWriter);/*from   w w w.  j a v  a  2s.  c om*/

    s_aLogger.info("Done creating code");
}

From source file:com.helger.peppol.supplementary.tools.MainCreatePredefinedEnumsFromExcel.java

License:Mozilla Public License

public static void main(final String[] args) throws IOException, URISyntaxException {
    // Where is the Excel?
    final IReadableResource aXls = new FileSystemResource(EXCEL_FILE);
    if (!aXls.exists())
        throw new IllegalStateException("The Excel file could not be found!");

    // Interprete as Excel
    final Workbook aWB = new HSSFWorkbook(aXls.getInputStream());

    // Check whether all required sheets are present
    final Sheet aParticipantSheet = aWB.getSheet(SHEET_PARTICIPANT);
    if (aParticipantSheet == null)
        throw new IllegalStateException("The " + SHEET_PARTICIPANT + " sheet could not be found!");
    final Sheet aDocumentSheet = aWB.getSheet(SHEET_DOCUMENT);
    if (aDocumentSheet == null)
        throw new IllegalStateException("The " + SHEET_DOCUMENT + " sheet could not be found!");
    final Sheet aProcessSheet = aWB.getSheet(SHEET_PROCESS);
    if (aProcessSheet == null)
        throw new IllegalStateException("The " + SHEET_PROCESS + " sheet could not be found!");

    // Convert participants
    _emitIdentifierIssuingAgency(aParticipantSheet);

    // Convert document identifiers
    _emitDocumentTypeIdentifiers(aDocumentSheet);

    // Convert processes identifiers
    _emitProcessIdentifier(aProcessSheet);

    // Write all Java source files
    final FileCodeWriter aWriter = new FileCodeWriter(new File("src/main/java"), CCharset.CHARSET_UTF_8_OBJ);
    s_aCodeModel.build(aWriter);//  ww  w . j  a v  a2  s.c  om

    s_aLogger.info("Done creating code");
}

From source file:com.helger.poi.excel.ExcelReadUtilsTest.java

License:Apache License

/**
 * Validate reference sheets/*from ww  w.  ja  v  a  2  s.  com*/
 * 
 * @param aWB
 *        Workbook to use
 */
private void _validateWorkbook(@Nonnull final Workbook aWB) {
    final Sheet aSheet1 = aWB.getSheet("Sheet1");
    assertNotNull(aSheet1);
    assertNotNull(aWB.getSheet("Sheet2"));
    final Sheet aSheet3 = aWB.getSheet("Sheet3");
    assertNotNull(aSheet3);
    assertNull(aWB.getSheet("Sheet4"));

    Cell aCell = aSheet1.getRow(0).getCell(0);
    assertNotNull(aCell);
    assertEquals(Cell.CELL_TYPE_STRING, aCell.getCellType());
    assertEquals("A1", aCell.getStringCellValue());

    aCell = aSheet1.getRow(1).getCell(1);
    assertNotNull(aCell);
    assertEquals(Cell.CELL_TYPE_STRING, aCell.getCellType());
    assertEquals("B2", aCell.getStringCellValue());

    aCell = aSheet1.getRow(2).getCell(2);
    assertNotNull(aCell);
    assertEquals(Cell.CELL_TYPE_STRING, aCell.getCellType());
    assertEquals("C\n3", aCell.getStringCellValue());

    aCell = aSheet1.getRow(3).getCell(3);
    assertNotNull(aCell);
    assertEquals(Cell.CELL_TYPE_NUMERIC, aCell.getCellType());
    assertEquals(0.00001, 4.4, aCell.getNumericCellValue());

    for (int i = 0; i < 6; ++i) {
        aCell = aSheet3.getRow(i).getCell(i);
        assertNotNull(aCell);
        assertEquals(Cell.CELL_TYPE_NUMERIC, aCell.getCellType());
        assertEquals(0.00001, i + 1, aCell.getNumericCellValue());
    }

    // ="abc"
    aCell = aSheet1.getRow(4).getCell(0);
    assertNotNull(aCell);
    assertEquals(Cell.CELL_TYPE_FORMULA, aCell.getCellType());
    assertEquals("\"abc\"", aCell.getCellFormula());
    assertEquals("abc", aCell.getStringCellValue());
    CellValue aEvaluated = new ExcelFormulaEvaluator(aWB, IStabilityClassifier.TOTALLY_IMMUTABLE)
            .evaluate(aCell);
    assertEquals(Cell.CELL_TYPE_STRING, aEvaluated.getCellType());
    assertEquals("abc", aEvaluated.getStringValue());

    // =4711
    aCell = aSheet1.getRow(5).getCell(1);
    assertNotNull(aCell);
    assertEquals(Cell.CELL_TYPE_FORMULA, aCell.getCellType());
    assertEquals("4711", aCell.getCellFormula());
    assertEquals(0.00001, 4711, aCell.getNumericCellValue());
    aEvaluated = new ExcelFormulaEvaluator(aWB, IStabilityClassifier.TOTALLY_IMMUTABLE).evaluate(aCell);
    assertEquals(Cell.CELL_TYPE_NUMERIC, aEvaluated.getCellType());
    assertEquals(0.00001, 4711, aEvaluated.getNumberValue());

    // =TRUE
    aCell = aSheet1.getRow(6).getCell(2);
    assertNotNull(aCell);
    assertEquals(Cell.CELL_TYPE_FORMULA, aCell.getCellType());
    assertEquals("TRUE", aCell.getCellFormula());
    assertTrue(aCell.getBooleanCellValue());
    aEvaluated = new ExcelFormulaEvaluator(aWB, IStabilityClassifier.TOTALLY_IMMUTABLE).evaluate(aCell);
    assertEquals(Cell.CELL_TYPE_BOOLEAN, aEvaluated.getCellType());
    assertTrue(aEvaluated.getBooleanValue());

    // Refers to cell at 6/2
    aCell = aSheet1.getRow(7).getCell(3);
    assertNotNull(aCell);
    assertEquals(Cell.CELL_TYPE_FORMULA, aCell.getCellType());
    assertEquals("C7", aCell.getCellFormula());
    assertTrue(aCell.getBooleanCellValue());
    aEvaluated = new ExcelFormulaEvaluator(aWB, IStabilityClassifier.TOTALLY_IMMUTABLE).evaluate(aCell);
    assertEquals(Cell.CELL_TYPE_BOOLEAN, aEvaluated.getCellType());
    assertTrue(aEvaluated.getBooleanValue());
}

From source file:com.jmc.jfxxlsdiff.task.GetWorkSheetContent.java

public GetWorkSheetContent(Workbook wb, String sheetName) {
    this(wb.getSheet(sheetName));
}

From source file:com.kysoft.cpsi.audit.service.SelfCheckServiceImpl.java

private void validateExcel(String hcrwId, Workbook workbook) throws Exception {
    Sheet sheetZCFZB = workbook.getSheet("");
    Sheet sheetLRB = workbook.getSheet("");
    String errorMsg = "";

    /*if(parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(48).getCell(4))).compareTo(new BigDecimal("0"))==0
        && parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(48).getCell(5))).compareTo(new BigDecimal("0"))==0
        && parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(48).getCell(9))).compareTo(new BigDecimal("0"))==0
        && parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(48).getCell(10))).compareTo(new BigDecimal("0"))==0){
    errorMsg=errorMsg+"[E49 F49 J49 K49?0] ";
    }else{/* w ww.  j  a v  a 2 s. c  om*/
    }*/

    if (Math.abs(parseFloat(POIUtils.getCellFormatValue(sheetZCFZB.getRow(48).getCell(4)))
            - parseFloat(POIUtils.getCellFormatValue(sheetZCFZB.getRow(48).getCell(9)))) < 0.00000001) {

    } else {
        errorMsg = errorMsg
                + "?E49??(?)?J49";
    }

    if (Math.abs(parseFloat(POIUtils.getCellFormatValue(sheetZCFZB.getRow(48).getCell(5)))
            - parseFloat(POIUtils.getCellFormatValue(sheetZCFZB.getRow(48).getCell(10)))) < 0.00000001) {

    } else {
        errorMsg = errorMsg
                + "F49??(?)K49";
    }

    if (Math.abs(parseFloat(POIUtils.getCellFormatValue(sheetLRB.getRow(40).getCell(3)))
            - parseFloat(POIUtils.getCellFormatValue(sheetZCFZB.getRow(46).getCell(10)))) < 0.00000001
            || parseBigDecimal(POIUtils.getCellFormatValue(sheetLRB.getRow(40).getCell(3)))
                    .compareTo(parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(46).getCell(10)))
                            .subtract(parseBigDecimal(
                                    POIUtils.getCellFormatValue(sheetZCFZB.getRow(46).getCell(9))))) == 0) {

    } else {
        errorMsg = errorMsg
                + "???D41??K47";//  ???D41?K47-??J47]
    }

    if (Math.abs(parseFloat(POIUtils.getCellFormatValue(sheetLRB.getRow(40).getCell(4)))
            - parseFloat(POIUtils.getCellFormatValue(sheetZCFZB.getRow(46).getCell(9)))) < 0.00000001) {

    } else {
        errorMsg = errorMsg
                + "???E41???J47";
    }

    /*if(parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(20).getCell(4))).compareTo(getSumValue(sheetZCFZB,4,7,19))==0){
            
    }else{
    errorMsg=errorMsg+"E8-E20??E21";
    }*/

    /*if(parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(20).getCell(5))).compareTo(getSumValue(sheetZCFZB,5,7,19))==0){
            
    }else{
    errorMsg=errorMsg+"[F8-F20??F21] ";
    }*/

    /*if(parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(39).getCell(4))).compareTo(getSumValue(sheetZCFZB,4,22,38))==0){
            
    }else{
    errorMsg=errorMsg+"[E23-E39??E40] ";
    }*/

    /*if(parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(39).getCell(5))).compareTo(getSumValue(sheetZCFZB,5,22,38))==0){
            
    }else{
    errorMsg=errorMsg+"[F23-F39??F40] ";
    }*/

    /*if(parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(48).getCell(4))).compareTo(
        parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(20).getCell(4))).add(parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(39).getCell(4)))))==0){
            
    }else{
    errorMsg=errorMsg+"[E49?E21+E40] ";
    }*/

    /*if(parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(48).getCell(5))).compareTo(
        parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(20).getCell(5))).add(parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(39).getCell(5)))))==0){
            
    }else{
    errorMsg=errorMsg+"[F49?F21+F40] ";
    }*/

    /*if(parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(21).getCell(9))).compareTo(getSumValue(sheetZCFZB,9,7,20))==0){
            
    }else{
    errorMsg=errorMsg+"[J8-J21??J22] ";
    }*/

    /* if(parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(21).getCell(10))).compareTo(getSumValue(sheetZCFZB,10,7,20))==0){
            
     }else{
    errorMsg=errorMsg+"[k8-k21??k22] ";
     }*/

    /*if(parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(34).getCell(9))).compareTo(getSumValue(sheetZCFZB,9,27,33).add(getSumValue(sheetZCFZB,9,23,24)))==0){
            
    }else{
    errorMsg=errorMsg+"[J24-J25J28-J34?J35] ";
    }*/

    /*if(parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(34).getCell(10))).compareTo(getSumValue(sheetZCFZB,10,27,33).add(getSumValue(sheetZCFZB,10,23,24)))==0){
            
    }else{
    errorMsg=errorMsg+"[K24-K25K28-K34?K35] ";
    }*/

    /*if(parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(35).getCell(9))).compareTo(
        parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(21).getCell(9))).add(parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(34).getCell(9)))))==0){
            
    }else{
    errorMsg=errorMsg+"[J22+J35?J36] ";
    }*/

    /* if(parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(35).getCell(10))).compareTo(
        parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(21).getCell(10))).add(parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(34).getCell(10)))))==0){
            
     }else{
    errorMsg=errorMsg+"[K22+K35?K36] ";
     }*/

    /*if(parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(47).getCell(9))).compareTo(
        parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(37).getCell(9))).add(
                parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(38).getCell(9)))).add(
                parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(41).getCell(9)))).add(
                parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(43).getCell(9)))).add(
                parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(44).getCell(9)))).add(
                parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(45).getCell(9)))).add(
                parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(46).getCell(9)))))==0){
    }else{
    errorMsg=errorMsg+"[J38+J39+J42+J44+J45+J46+J47?J48] ";
    }*/

    /*if(parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(47).getCell(10))).compareTo(
        parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(37).getCell(10))).add(
                parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(38).getCell(10)))).add(
                parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(41).getCell(10)))).add(
                parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(43).getCell(10)))).add(
                parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(44).getCell(10)))).add(
                parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(45).getCell(10)))).add(
                parseBigDecimal(POIUtils.getCellFormatValue(sheetZCFZB.getRow(46).getCell(10)))))==0){
    }else{
    errorMsg=errorMsg+"[K38+K39+K42+K44+K45+K46+K47?K48] ";
    }*/

    /*if(Math.abs(parseFloat(POIUtils.getCellFormatValue(sheetZCFZB.getRow(48).getCell(4)))-parseFloat(POIUtils.getCellFormatValue(sheetZCFZB.getRow(48).getCell(9))))<0.00000001){
            
    }else{
    errorMsg=errorMsg+"[?E49??J49] ";
    }*/

    /*if(Math.abs(parseFloat(POIUtils.getCellFormatValue(sheetZCFZB.getRow(48).getCell(5)))-parseFloat(POIUtils.getCellFormatValue(sheetZCFZB.getRow(48).getCell(10))))<0.00000001){
            
    }else{
    errorMsg=errorMsg+"[F49?K49] ";
    }*/

    /*if(!errorMsg.equals("")){
    throw new RuntimeException(errorMsg);
    }*/
    if (null != errorMsg && !errorMsg.equals("")) {
        hcrwMapper.updateZcbResultByPrimaryKey(hcrwId,
                "      ??" + errorMsg);
    } else {
        hcrwMapper.updateZcbResultByPrimaryKey(hcrwId, "");
    }
}

From source file:com.kysoft.cpsi.audit.service.SelfCheckServiceImpl.java

@Override
@Transactional/*from   www.  ja  v  a 2 s .  c  om*/
public void uploadSelfCheckData(InputStream is, String hcrwId, String fileName, Integer nd) throws Exception {
    Hcrw hcrw = hcrwMapper.selectByPrimaryKey(hcrwId);
    Workbook workbook = null;
    if (fileName.endsWith("xls")) {
        POIFSFileSystem fs = new POIFSFileSystem(is);
        workbook = new HSSFWorkbook(fs);
    } else if (fileName.endsWith("xlsx")) {
        workbook = new XSSFWorkbook(is);
    }

    //?
    validateExcel(hcrwId, workbook);
    if (importFlag.equals("1")) {
        //?
        nianbaoWangzhiwangdian(hcrw, workbook.getSheet("??"), nd);
        //?
        nianbao(hcrw, workbook.getSheet(""), workbook.getSheet("??"),
                workbook.getSheet(""), nd);

        //
        gudongchuzi(hcrw, workbook.getSheet("??"), nd);

        //??
        guquanbiangeng(hcrw, workbook.getSheet("????"), nd);

        //
        duiwaitouzi(hcrw, workbook.getSheet("?????"), nd);

        //?
        duiwandanbao(hcrw, workbook.getSheet("??"), nd);

        //?
        xingzhengxuke(hcrw, workbook.getSheet("??????"), nd);

        jsGudongchuzhi(hcrw, workbook.getSheet("??"));
        jsGuquanbiangeng(hcrw, workbook.getSheet("????"));
        jsXingzhengxuke(hcrw, workbook.getSheet("??????"));
        jsZhishichanquan(hcrw, workbook.getSheet("??"));
        jsXingzhengchufa(hcrw, workbook.getSheet("??"));
    }
    workbook.close();
}

From source file:com.medicaid.mmis.util.CodeMappingLoader.java

License:Apache License

private static void importSheet(EntityManager em, SequenceGeneratorBean sequence, Workbook workbook,
        String sheetName) {/*w  w w . java2  s.  co m*/
    logger.info("Importing legacy mapping from worksheet: " + sheetName);
    Sheet sheet = workbook.getSheet(sheetName);
    Iterator<Row> rowIterator = sheet.rowIterator();
    int total = 0;
    while (rowIterator.hasNext()) {
        Row row = (Row) rowIterator.next();
        if (row.getRowNum() < 1) {
            continue;
        }

        Cell systemIdCell = row.getCell(0);
        Cell codeTypeCell = row.getCell(1);
        Cell internalCodeCell = row.getCell(2);
        Cell externalCodeCell = row.getCell(4);
        if (systemIdCell == null || StringUtils.isBlank(systemIdCell.getStringCellValue())) {
            continue;
        }
        if (codeTypeCell == null || StringUtils.isBlank(codeTypeCell.getStringCellValue())) {
            continue;
        }
        if (internalCodeCell == null || StringUtils.isBlank(internalCodeCell.getStringCellValue())) {
            continue;
        }
        if (externalCodeCell == null || StringUtils.isBlank(externalCodeCell.getStringCellValue())) {
            continue;
        }

        LegacySystemMapping mapping = new LegacySystemMapping();
        mapping.setId(sequence.getNextValue("LEGACY_MAPPING"));
        mapping.setSystemName(systemIdCell.getStringCellValue());
        mapping.setExternalCode(externalCodeCell.getStringCellValue());
        mapping.setInternalCode(internalCodeCell.getStringCellValue());
        mapping.setCodeType(codeTypeCell.getStringCellValue());
        logger.debug("Inserting mapping: " + mapping);
        em.persist(mapping);
        total++;
    }
    logger.info("Total records imported from sheet: " + total);
    System.out.println("Total records imported from sheet[" + sheetName + "] : " + total);
}

From source file:com.movielabs.availslib.AvailSS.java

License:Open Source License

/**
 * Add a sheet from an Excel spreadsheet to a spreadsheet object
 * @param sheetName name of the sheet to add
 * @return created sheet object/*from  www.j a  v  a2  s. c  o  m*/
 * @throws IllegalArgumentException if the sheet does not exist in the Excel spreadsheet
 * @throws Exception other error conditions may also throw exceptions
 */
public AvailsSheet addSheet(String sheetName) throws Exception {
    Workbook wb = new XSSFWorkbook(new FileInputStream(file));
    Sheet sheet = wb.getSheet(sheetName);
    if (sheet == null) {
        wb.close();
        throw new IllegalArgumentException(file + ":" + sheetName + " not found");
    }
    AvailsSheet as = addSheetHelper(wb, sheet);
    wb.close();
    return as;
}

From source file:com.ncc.excel.ExcelUtil.java

License:Apache License

public List<Row> readExcel(Workbook wb) {
    Sheet sheet = null;//from w  ww .  ja v a 2  s  . c o m

    if (onlyReadOneSheet) {//??sheet

        System.out.println("selectedSheetName:" + selectedSheetName);
        // ??sheet(?????)  
        sheet = selectedSheetName.equals("") ? wb.getSheetAt(selectedSheetIdx) : wb.getSheet(selectedSheetName);
        System.out.println(sheet.getSheetName());

    } else {
        for (int i = 0; i < wb.getNumberOfSheets(); i++) {//??Sheet

            sheet = wb.getSheetAt(i);
            logger.info(sheet.getSheetName());

            for (int j = 0; j < sheet.getPhysicalNumberOfRows(); j++) {//??
                Row row = sheet.getRow(j);
                for (int k = 0; k < row.getPhysicalNumberOfCells(); k++) {//???
                    System.out.print(row.getCell(k) + "\t");
                }
                System.out.println("---Sheet" + i + "?---");
            }
        }
    }
    return null;
}