示例#1
0
 /**
  * POI:解析Excel文件中的数据并把每行数据封装成一个实体
  *
  * @param fis 文件输入流
  * @return List<EmployeeInfo> Excel中数据封装实体的集合
  */
 public static List<String> importControlsByPoi(InputStream fis) {
   // 这里是解析出来的Excel的数据存放的List集合
   List<String> controls = new ArrayList<String>();
   try {
     // 创建Excel工作薄
     HSSFWorkbook hwb = new HSSFWorkbook(fis);
     // 得到第一个工作表
     HSSFSheet sheet = hwb.getSheetAt(0);
     HSSFRow row = null;
     // 遍历该表格中所有的工作表,i表示工作表的数量 getNumberOfSheets表示工作表的总数
     for (int i = 0; i < hwb.getNumberOfSheets(); i++) {
       sheet = hwb.getSheetAt(i);
       // 遍历该行所有的行,j表示行数 getPhysicalNumberOfRows行的总数
       for (int j = 1; j < sheet.getPhysicalNumberOfRows(); j++) {
         row = sheet.getRow(j);
         // 此方法调用getCellValue(HSSFCell cell)对解析出来的数据进行判断,并做相应的处理
         if (ImportExcelByPoi.getCellValue(row.getCell(0)) != null
             && !"".equals(ImportExcelByPoi.getCellValue(row.getCell(0)))) {
           controls.add(String.valueOf(ImportExcelByPoi.getCellValue(row.getCell(0))));
         }
       }
     }
   } catch (IOException e) {
     e.printStackTrace();
   }
   return controls;
 }
 private void autoSizeColumns() {
   int sheetCount = workbook.getNumberOfSheets();
   for (int i = 0; i < sheetCount; i++) {
     HSSFSheet sheet = workbook.getSheetAt(i);
     for (int colnum = 0; colnum <= sheet.getLastRowNum(); colnum++) sheet.autoSizeColumn(colnum);
   }
 }
  public void addSheet(String name) {
    if (name.length() > 30) {
      name = name.substring(0, 25);
    }

    boolean found = false;

    String tempName = name.replace("/", "-");
    int i = 0;
    while (true) {

      if (i > 1000) {
        break;
      }

      for (int j = 0; j < workbook.getNumberOfSheets(); j++) {
        found = workbook.getSheetName(j).equalsIgnoreCase(tempName);
        if (found) {
          break;
        }
      }

      if (!found) {
        break;
      } else {
        i++;
        tempName = name + " (" + i + ")";
      }
    }

    activeSheet = workbook.createSheet(tempName);

    nextRowId = 0;
  }
  /**
   * Reads userdata from all sheets in workbook.
   *
   * @return {@link List} of {@link User}.
   */
  public List<User> getUsers() {
    List<User> vUsers = new ArrayList<User>();

    if (mFileType == FileTypes.EXCEL && mWorkbook != null) {

      // go through every sheet as house
      logger.info("Importing user data from excel file");
      int vNumberOfSheets = mWorkbook.getNumberOfSheets();
      for (int i = 0; i < vNumberOfSheets; i++) {
        HSSFSheet vSheet = mWorkbook.getSheetAt(i);
        String vSheetName = vSheet.getSheetName();
        logger.info("Sheet name: " + vSheetName);

        // try to figure out columns to use
        int[] vColumns = getUserdataColumnsFromSheet(vSheet);

        // get userdata from sheet
        vUsers.addAll(getUsers(vSheet, vColumns));
      }

    } else if (mFileType == FileTypes.CSV && mExcelFile != null) {
      logger.info("Importing user data from csv file.");
      vUsers.addAll(getUsers(mExcelFile));
    } else {
      logger.error("Cannot get userdata from workbook. No workbook loaded!");
    }

    return vUsers;
  }
示例#5
0
 @Override
 public void readFile() {
   // TODO Auto-generated method stub
   ReadFile temp = new ReadFile(mfileName);
   mworkBook = temp.readFile(mworkBook);
   msheetCount = mworkBook.getNumberOfSheets();
   getDataFromSheet();
 }
 public int getLastSheet() {
   if (FileType.getFileType() == 1) {
     return wb_xls.getNumberOfSheets() - 1;
   } else if (FileType.getFileType() == 2) {
     return wb_xlsx.getNumberOfSheets() - 1;
   }
   return -1;
 }
示例#7
0
  @Override
  public String executeAction(HttpServletRequest request, List<FileItem> sessionFiles)
      throws UploadActionException {
    StringBuffer response = new StringBuffer();

    for (FileItem item : sessionFiles) {
      if (false == item.isFormField()) {

        try {

          if (item.getName().endsWith(".xls")) {
            POIFSFileSystem fs = new POIFSFileSystem(item.getInputStream());
            HSSFWorkbook wb = new HSSFWorkbook(fs);
            System.out.println("Sheet Num:" + wb.getNumberOfSheets());
            // only get first sheet,ignore others
            HSSFSheet sheet = wb.getSheetAt(0);

            Iterator<Row> rows = sheet.rowIterator();
            while (rows.hasNext()) {
              HSSFRow row = (HSSFRow) rows.next();
              Iterator<Cell> cells = row.cellIterator();
              while (cells.hasNext()) {
                HSSFCell cell = (HSSFCell) cells.next();
                response.append(cell.toString() + ":");
              }
              response.append("\n");
            }

          } else if (item.getName().endsWith(".xlsx")) {
            // POIFSFileSystem fs = new POIFSFileSystem(item.getInputStream());
            XSSFWorkbook wb = new XSSFWorkbook(item.getInputStream());
            System.out.println("Sheet Num:" + wb.getNumberOfSheets());
            // only get first sheet,ignore others
            XSSFSheet sheet = wb.getSheetAt(0);

            Iterator<Row> rows = sheet.rowIterator();
            while (rows.hasNext()) {
              XSSFRow row = (XSSFRow) rows.next();
              Iterator<Cell> cells = row.cellIterator();
              while (cells.hasNext()) {
                XSSFCell cell = (XSSFCell) cells.next();
                response.append(cell.toString() + ":");
              }
              response.append("\n");
            }
          }

        } catch (Exception e) {
          throw new UploadActionException(e);
        }
      }
    }
    // / Remove files from session because we have a copy of them
    removeSessionFileItems(request);
    // / Send your customized message to the client.
    return response.toString();
  }
示例#8
0
  @Override
  public List<String> getAnchors() {
    List<String> anchors = new ArrayList<>();

    for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
      anchors.add(workbook.getSheetName(i));
    }

    return anchors;
  }
示例#9
0
  /** Creates a new XlsDataSet object that loads the specified Excel document. */
  public XlsDataSet(InputStream in) throws IOException, DataSetException {
    _tables = super.createTableNameMap();

    HSSFWorkbook workbook = new HSSFWorkbook(in);
    int sheetCount = workbook.getNumberOfSheets();
    for (int i = 0; i < sheetCount; i++) {
      ITable table = new XlsTable(workbook.getSheetName(i), workbook.getSheetAt(i));
      _tables.add(table.getTableMetaData().getTableName(), table);
    }
  }
 private HSSFSheet getFirstSheet() throws IOException {
   HSSFSheet sheet = null;
   HSSFWorkbook wb = getWorkbook();
   if (wb.getNumberOfSheets() == 0) {
     sheet = wb.createSheet("Sheet1");
     writeHeader(sheet);
   } else {
     sheet = wb.getSheetAt(0);
   }
   return sheet;
 }
示例#11
0
 static HSSFSheet[] readExcel(File file) {
   try {
     HSSFWorkbook hssfWorkbook = new HSSFWorkbook(new FileInputStream(file));
     int num = hssfWorkbook.getNumberOfSheets();
     HSSFSheet[] sheets = new HSSFSheet[num];
     for (int i = 0; i < num; i++) sheets[i] = hssfWorkbook.getSheetAt(i);
     return sheets;
   } catch (IOException e) {
     e.printStackTrace();
   }
   return null;
 }
 /**
  * Формирует отчет на основании его модели и указанных пользователем в контексте параметров.
  *
  * @param report модель формируемого отчета.
  * @param ctx данные необходимые для формирования данного отчета.
  * @return сформированный отчет.
  * @throws ReportProcessingException в случае каких-либо проблем.
  */
 public HSSFWorkbook process(Report report, final ELContext ctx) throws ReportProcessingException {
   ExecutionContext ectx = null;
   try {
     report =
         new Report(
             null,
             report); // копируем модель отчета, т.к. в процессе формирования отчета она может
                      // измениться.
     final HSSFWorkbook wb = makeWorkbook(report, ctx);
     final Map<Short, HSSFCellStyle> styles = applyStyles(report, wb);
     ectx = new ExecutionContext(report, ctx, wb, styles);
     ctx.getVariables().put(VAR_CONTEXT, ectx);
     final String user = report.getUser() != null ? (String) report.getUser().getValue(ctx) : null;
     final String password =
         report.getPassword() != null ? (String) report.getPassword().getValue(ctx) : null;
     if (user != null && password != null) {
       wb.writeProtectWorkbook(password, user);
     }
     for (final ReportEventListener listener : ectx.listeners) {
       listener.beforeReport(ectx);
     }
     for (final Sheet sheet : report.getSheets()) {
       processSheet(ectx, sheet);
     }
     boolean activeSheetSpecified = false;
     for (int i = 0, cnt = wb.getNumberOfSheets(); i < cnt; i++) {
       if (!wb.isSheetHidden(i) && !wb.isSheetVeryHidden(i)) {
         wb.setActiveSheet(i);
         wb.setSelectedTab(i);
         activeSheetSpecified = true;
         break;
       }
     }
     if (!activeSheetSpecified) {
       final HSSFSheet sheet = wb.createSheet();
       final int index = wb.getSheetIndex(sheet);
       wb.setActiveSheet(index);
       wb.setSelectedTab(index);
     }
     for (final ReportEventListener listener : ectx.listeners) {
       listener.afterReport(ectx);
     }
     return wb;
   } catch (Exception e) {
     throw new ReportProcessingException(e.getMessage() + "\n" + ectx, e, ectx);
   }
 }
  /** @param xls */
  private static TreeSet<String> getSheetNameList(File xls) throws Exception {

    TreeSet<String> sheetNameList = new TreeSet<String>();

    if (!xls.exists()) {
      return sheetNameList;
    }

    POIFSFileSystem input = new POIFSFileSystem(new FileInputStream(xls));
    HSSFWorkbook wb = new HSSFWorkbook(input);

    int numberOfSheets = wb.getNumberOfSheets();

    for (int i = 0; i < numberOfSheets; i++) {
      String sheetName = wb.getSheetName(i);
      sheetNameList.add(sheetName);
    }

    return sheetNameList;
  }
示例#14
0
    private boolean searchInExcel(String file) throws Exception {
      Row row;
      Cell cell;
      String text;
      boolean found = false;
      InputStream in = null;

      try {
        in = new FileInputStream(file);
        HSSFWorkbook wb = new HSSFWorkbook(in);
        int sheets = wb.getNumberOfSheets();
        OUTERMOST:
        for (int i = 0; i < sheets; i++) {
          HSSFSheet sheet = wb.getSheetAt(i);
          Iterator<Row> rowIterator = sheet.rowIterator();
          while (rowIterator.hasNext()) {
            row = (Row) rowIterator.next();
            Iterator<Cell> cellIterator = row.cellIterator();
            while (cellIterator.hasNext()) {
              cell = cellIterator.next();
              int type = cell.getCellType();
              if (type == HSSFCell.CELL_TYPE_STRING) {
                text = cell.getStringCellValue();
                found = searchText(text);
                break OUTERMOST;
              }
            }
          }
        }
        return found;
      } finally {
        if (in != null)
          try {
            in.close();
          } catch (Exception e) {
          }
      }
    }
  public static void genUpdate() throws Exception {
    String dir = "C:\\java\\elie\\new contracts\\importtemplate";

    FileOutputStream fout = new FileOutputStream(new java.io.File("c:\\java\\elie\\update.sql"));

    for (java.io.File f : new java.io.File(dir).listFiles()) {
      if (!f.isDirectory()) {
        HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(f));

        for (int s = 0; s < wb.getNumberOfSheets(); s++) {
          Sheet sheet = wb.getSheetAt(s);

          for (int r = 1; r < sheet.getLastRowNum(); r++) {
            try {
              Row row = sheet.getRow(r);

              String fs = row.getCell(2).getStringCellValue().replace(" ", "");
              double installment = row.getCell(14).getNumericCellValue();
              String line =
                  "update WFS_FILE set installment = "
                      + installment
                      + " where code = '"
                      + fs
                      + "' and dtype='Order';\n";
              fout.write(line.getBytes());

            } catch (Exception e) {
              // e.printStackTrace();
            }
          }
        }
      }
    }
    fout.flush();
    fout.close();
  }
示例#16
0
文件: Vender.java 项目: humin11/bnbmg
  public static void imp(File file) {
    String result = "";
    String profiles = "";
    int imported = 0;
    int missed = 0;

    if (file != null) {

      Config config = Config.find("1=1").first();

      FileInputStream fileInputStream = null;
      HSSFWorkbook workbook = null;
      try {
        fileInputStream = new FileInputStream(file);
        workbook = new HSSFWorkbook(fileInputStream);
      } catch (FileNotFoundException e) {
        e.printStackTrace();
      } catch (IOException e) {
        e.printStackTrace();
      }

      Profile profile = null;
      User user = null;
      String value = "";
      Material material = null;

      SendMessage m = new SendMessage();
      for (int x = 0; x < workbook.getNumberOfSheets(); x++) {

        HSSFSheet worksheet = workbook.getSheetAt(x);
        HSSFRow row = null;
        HSSFCell cell = null;
        DecimalFormat df = new DecimalFormat("#");
        df.setMaximumFractionDigits(0);

        for (int i = 1; i < worksheet.getLastRowNum(); i++) {
          String unit = "万元";
          String business = "0";
          try {
            row = worksheet.getRow(i);
            if (row.getLastCellNum() < 22) {
              continue;
            }
            cell = row.getCell(0);
            value = cell.getStringCellValue();
            user = User.find("username=?", value).first();
            if (user == null) {
              user = new User(value, getRandomPwd(), ApplicationRole.getByName("user"));
              user.save();
            }

            profile = Profile.find("user.id=?", user.id).first();
            if (profile == null) {
              profile = new Profile();
              profile.user = user;
            }

            profile.is_audit = 1;
            cell = row.getCell(1);
            value = cell.getStringCellValue();
            profile.name = value;

            cell = row.getCell(2);
            value = cell.getStringCellValue();
            material = Material.find("name=?", value).first();
            if (material == null) {
              material = new Material();
              material.name = value;
              material.save();
            }
            if (!profile.materials.contains(material)) profile.materials.add(material);

            cell = row.getCell(3);
            if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
              value = String.valueOf(cell.getNumericCellValue());
            } else {
              value = cell.getStringCellValue();
            }

            cell = row.getCell(4);
            if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
              value = String.valueOf(cell.getNumericCellValue());
            } else {
              value = cell.getStringCellValue();
            }

            cell = row.getCell(5);
            if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
              value = String.valueOf(cell.getNumericCellValue());
            } else {
              value = cell.getStringCellValue();
            }
            profile.registration_number = value;

            cell = row.getCell(6);
            if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
              df.setMaximumFractionDigits(2);
              value = String.valueOf(df.format(cell.getNumericCellValue()));
            } else {
              value = cell.getStringCellValue();
            }
            if (value.contains("美元")) {
              value = value.replace("(美元)", "").trim();
              unit = "万美元";
            }
            profile.registration_assets = value;
            profile.registration_assets_unit = unit;

            df.setMaximumFractionDigits(0);
            cell = row.getCell(7);
            if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
              value = String.valueOf(cell.getNumericCellValue());
            } else {
              value = cell.getStringCellValue();
            }
            profile.registration_address = value;

            cell = row.getCell(8);
            if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
              value = String.valueOf(cell.getNumericCellValue());
            } else {
              value = cell.getStringCellValue();
            }
            profile.bank_name = value;

            cell = row.getCell(9);
            if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
              value = String.valueOf(cell.getNumericCellValue());
            } else {
              value = cell.getStringCellValue();
            }
            profile.account_name = value;

            cell = row.getCell(10);
            if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
              value = String.valueOf(cell.getNumericCellValue());
            } else {
              value = cell.getStringCellValue();
            }
            profile.tfn = value;

            cell = row.getCell(11);
            if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
              value = String.valueOf(cell.getNumericCellValue());
            } else {
              value = cell.getStringCellValue();
            }
            profile.factory_name = value;

            cell = row.getCell(12);
            if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
              value = String.valueOf(cell.getNumericCellValue());
            } else {
              value = cell.getStringCellValue();
            }
            profile.factory_address = value;

            cell = row.getCell(13);
            if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
              value = String.valueOf(df.format(cell.getNumericCellValue()));
            } else {
              value = cell.getStringCellValue();
            }
            profile.first_supply = value.replaceAll("年", "");

            cell = row.getCell(14);
            if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
              value = String.valueOf(cell.getNumericCellValue());
            } else {
              value = cell.getStringCellValue();
            }
            profile.legal_person = value;

            cell = row.getCell(15);
            if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
              value = String.valueOf(cell.getNumericCellValue());
            } else {
              value = cell.getStringCellValue();
            }
            profile.contact_name = value;

            cell = row.getCell(16);
            if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
              value = String.valueOf(cell.getNumericCellValue());
            } else {
              value = cell.getStringCellValue();
            }
            profile.contact_job = value;

            cell = row.getCell(17);
            if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
              value = String.valueOf(df.format(cell.getNumericCellValue()));
            } else {
              value = cell.getStringCellValue();
            }
            profile.contact_phone = value;

            cell = row.getCell(18);
            if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
              value = String.valueOf(cell.getNumericCellValue());
            } else {
              value = cell.getStringCellValue();
            }
            profile.sales_name = value;

            cell = row.getCell(19);
            if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
              value = String.valueOf(cell.getNumericCellValue());
            } else {
              value = cell.getStringCellValue();
            }
            profile.sales_job = value;

            cell = row.getCell(20);
            if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
              value = String.valueOf(df.format(cell.getNumericCellValue()));
            } else {
              value = cell.getStringCellValue();
            }
            profile.sales_phone = value;

            cell = row.getCell(21);
            if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
              value = String.valueOf(cell.getNumericCellValue());
            } else {
              value = cell.getStringCellValue();
            }
            if (value.equals("自营")) {
              business = "1";
            } else if (value.equals("经销")) {
              business = "2";
            } else if (value.equals("挂靠")) {
              business = "3";
            } else {
              business = "0";
            }
            profile.business_model = business;
            profile.save();
            imported += 1;

            if (profile.contact_phone != null) {
              String message =
                  "您的信息已导入,用户名:" + user.username + ",密码:" + user.password + ",请登录比价平台上传资质文件";
              if (config.msg_import != null && !"".equals(config.msg_import)) {
                message =
                    config
                        .msg_import
                        .replace("{username}", user.username)
                        .replace("{passowrd}", user.password);
              }
              m.sendSms(profile.contact_phone, message, "0000001");
              if (profile.contact_email != null && !"".equals(profile.contact_phone)) {
                m.sendMail(
                    profile.contact_email,
                    "[" + Messages.get("application.name") + "]信息导入",
                    message);
              }
            }
          } catch (Exception e) {
            missed += 1;
            if (!"".equals(profiles)) {
              profiles += "," + profile.name;
            } else {
              profiles += profile.name;
            }
            e.printStackTrace();
          }
        }
      }

      result = "成功导入" + imported + "条记录,丢失" + missed + "条记录。";
      if (!"".equals(profiles)) {
        result += "丢失导入的供应商为:" + profiles;
      }
    }
    redirect("/admin/profiles?result=" + URLEncoder.encode(result));
  }
示例#17
0
  /**
   * Construct Statements based on given Excel File.
   *
   * @param file Given Excel File
   * @return the List of constructed Statements. Empty list if fail.
   */
  public static List<Statements> newInstanceFromExcelFile(File file) {
    FileInputStream fileInputStream = null;
    final List<Statements> statementsList = new ArrayList<Statements>();
    try {
      fileInputStream = new FileInputStream(file);
      final POIFSFileSystem fs = new POIFSFileSystem(fileInputStream);
      final HSSFWorkbook wb = new HSSFWorkbook(fs);
      final int numberOfSheets = wb.getNumberOfSheets();
      for (int k = 0; k < numberOfSheets; k++) {
        final HSSFSheet sheet = wb.getSheetAt(k);
        final int startRow = sheet.getFirstRowNum();
        final int endRow = sheet.getLastRowNum();
        // If there are 3 rows, endRow will be 2.
        // We must have at least 2 rows. (endRow = 1)
        if (startRow != 0 || endRow <= startRow) {
          continue;
        }

        final HSSFRow row = sheet.getRow(startRow);
        if (row == null) {
          continue;
        }

        final int startCell = row.getFirstCellNum();
        final int endCell = row.getLastCellNum();
        // If there are 2 cols, endCell will be 2.
        // We must have at least 1 col. (endCell = 1)
        if (startCell != 0 || endCell <= startCell) {
          continue;
        }

        final List<String> types = new ArrayList<String>();
        for (int i = startCell; i < endCell; i++) {
          final HSSFCell cell = row.getCell(i);
          if (cell == null) {
            continue;
          }

          // Exception may be thrown here, as cell may be numerical value.
          final String type = cell.getRichStringCellValue().getString();
          if (type != null) {
            types.add(type);
          }
        }

        if (types.isEmpty()) {
          continue;
        }

        if (types.size() != (endCell - startCell)) {
          continue;
        }

        final Statement.What what = Statement.what(types);
        Statements s = new Statements(what.type, what.guiBundleWrapper);
        for (int i = startRow + 1; i <= endRow; i++) {
          final HSSFRow r = sheet.getRow(i);
          if (r == null) {
            continue;
          }
          final List<Atom> atoms = new ArrayList<Atom>();
          for (int j = startCell; j < endCell; j++) {
            final HSSFCell cell = r.getCell(j);
            if (cell == null) {
              continue;
            }
            Object value = null;
            if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
              final HSSFRichTextString richString = cell.getRichStringCellValue();
              if (richString != null) {
                value = richString.getString();
              } else {
                value = "";
              }
            } else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
              try {
                value = new Double(cell.getNumericCellValue());
              } catch (NumberFormatException ex) {
                log.error(null, ex);
                value = new Double(0.0);
              }
            } else {
            }

            if (null == value) {
              continue;
            }
            atoms.add(new Atom(value, types.get(j - startCell)));
          }
          final Statement statement = new Statement(atoms);

          if (s.getType() != statement.getType()) {
            // Give up.
            s = null;
            break;
          }
          s.statements.add(statement);
        } // for (int i = startRow + 1; i <= endRow; i++)

        if (s != null) {
          statementsList.add(s);
        }
      } /* for(int k = 0; k < numberOfSheets; k++) */
    } catch (Exception ex) {
      log.error(null, ex);
    } finally {
      org.yccheok.jstock.gui.Utils.close(fileInputStream);
    }
    return statementsList;
  }
示例#18
0
  /** Retrieves the text contents of the file */
  public String getText() {
    StringBuffer text = new StringBuffer();

    // We don't care about the difference between
    //  null (missing) and blank cells
    _wb.setMissingCellPolicy(HSSFRow.RETURN_BLANK_AS_NULL);

    // Process each sheet in turn
    for (int i = 0; i < _wb.getNumberOfSheets(); i++) {
      HSSFSheet sheet = _wb.getSheetAt(i);
      if (sheet == null) {
        continue;
      }

      if (_includeSheetNames) {
        String name = _wb.getSheetName(i);
        if (name != null) {
          text.append(name);
          text.append("\n");
        }
      }

      // Header text, if there is any
      if (_includeHeadersFooters) {
        text.append(_extractHeaderFooter(sheet.getHeader()));
      }

      int firstRow = sheet.getFirstRowNum();
      int lastRow = sheet.getLastRowNum();
      for (int j = firstRow; j <= lastRow; j++) {
        HSSFRow row = sheet.getRow(j);
        if (row == null) {
          continue;
        }

        // Check each cell in turn
        int firstCell = row.getFirstCellNum();
        int lastCell = row.getLastCellNum();
        if (_includeBlankCells) {
          firstCell = 0;
        }

        for (int k = firstCell; k < lastCell; k++) {
          HSSFCell cell = row.getCell(k);
          boolean outputContents = true;

          if (cell == null) {
            // Only output if requested
            outputContents = _includeBlankCells;
          } else {
            switch (cell.getCellType()) {
              case HSSFCell.CELL_TYPE_STRING:
                text.append(cell.getRichStringCellValue().getString());
                break;
              case HSSFCell.CELL_TYPE_NUMERIC:
                text.append(_formatter.formatCellValue(cell));
                break;
              case HSSFCell.CELL_TYPE_BOOLEAN:
                text.append(cell.getBooleanCellValue());
                break;
              case HSSFCell.CELL_TYPE_ERROR:
                text.append(ErrorEval.getText(cell.getErrorCellValue()));
                break;
              case HSSFCell.CELL_TYPE_FORMULA:
                if (!_shouldEvaluateFormulas) {
                  text.append(cell.getCellFormula());
                } else {
                  switch (cell.getCachedFormulaResultType()) {
                    case HSSFCell.CELL_TYPE_STRING:
                      HSSFRichTextString str = cell.getRichStringCellValue();
                      if (str != null && str.length() > 0) {
                        text.append(str.toString());
                      }
                      break;
                    case HSSFCell.CELL_TYPE_NUMERIC:
                      HSSFCellStyle style = cell.getCellStyle();
                      if (style == null) {
                        text.append(cell.getNumericCellValue());
                      } else {
                        text.append(
                            _formatter.formatRawCellContents(
                                cell.getNumericCellValue(),
                                style.getDataFormat(),
                                style.getDataFormatString()));
                      }
                      break;
                    case HSSFCell.CELL_TYPE_BOOLEAN:
                      text.append(cell.getBooleanCellValue());
                      break;
                    case HSSFCell.CELL_TYPE_ERROR:
                      text.append(ErrorEval.getText(cell.getErrorCellValue()));
                      break;
                  }
                }
                break;
              default:
                throw new RuntimeException("Unexpected cell type (" + cell.getCellType() + ")");
            }

            // Output the comment, if requested and exists
            HSSFComment comment = cell.getCellComment();
            if (_includeCellComments && comment != null) {
              // Replace any newlines with spaces, otherwise it
              //  breaks the output
              String commentText = comment.getString().getString().replace('\n', ' ');
              text.append(" Comment by " + comment.getAuthor() + ": " + commentText);
            }
          }

          // Output a tab if we're not on the last cell
          if (outputContents && k < (lastCell - 1)) {
            text.append("\t");
          }
        }

        // Finish off the row
        text.append("\n");
      }

      // Finally Footer text, if there is any
      if (_includeHeadersFooters) {
        text.append(_extractHeaderFooter(sheet.getFooter()));
      }
    }

    return text.toString();
  }