Prosdac report spreadsheet reader

App.java

package net.tardivo.prosoft.excel.reader;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.regex.Pattern;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

public class App {

  public static final Pattern NF_PATTERN = Pattern.compile("^\\d{10}$");

	public static void main(String[] args) throws Exception {
		InputStream is = new FileInputStream("/Users/matheustardivo/code/ruby/planilha.xls");

		Workbook wb = WorkbookFactory.create(is);
		Sheet sheet = wb.getSheetAt(0);

		List<Line> lines = new ArrayList<Line>();

		String actualNf = null;

		for (int i = 0; i < 10987; i++) {
			Row row = sheet.getRow(i);
			if (row == null) {
				continue;
			}

			Cell cell = row.getCell(0);
			// New nf
			if (cell != null && Cell.CELL_TYPE_STRING == cell.getCellType()
					&& NF_PATTERN.matcher(cell.getStringCellValue()).matches()) {
				System.out.println("NF starts at row " + (i + 1));
				actualNf = cell.getStringCellValue();
				continue;
			}

			// Ends nf
			if (cell != null && Cell.CELL_TYPE_STRING == cell.getCellType()
					&& "Totais dos Itens:".equalsIgnoreCase(cell.getStringCellValue())) {
				System.out.println("NF ends at row " + (i + 1));
				actualNf = null;
				continue;
			}

			// Lookup for column B
			cell = row.getCell(1);
			if (cell != null && Cell.CELL_TYPE_STRING == cell.getCellType()
					&& "22021000".equals(cell.getStringCellValue())) {
				// New line
				Line line = new Line(actualNf, row.getCell(0).getStringCellValue(),
						row.getCell(1).getStringCellValue(), row.getCell(2).getStringCellValue(), row.getCell(4)
								.getNumericCellValue(), row.getCell(5).getNumericCellValue(), row.getCell(7)
								.getNumericCellValue(), row.getCell(10).getNumericCellValue());

				lines.add(line);
				System.out.println("At line: " + (i + 1) + " Added new line: " + line);
			}
		}

		System.out.println("Number of lines added: " + lines.size());
		writeWorkbook(lines);
	}

	public static void writeWorkbook(List<Line> lines) throws Exception {
		Workbook wb = new HSSFWorkbook();
		FileOutputStream fileOut = new FileOutputStream("/Users/matheustardivo/code/ruby/output_workbook.xls");

		CreationHelper createHelper = wb.getCreationHelper();
		Sheet sheet = wb.createSheet("sheet");

		int i = 0;
		for (Line line : lines) {
			Row row = sheet.createRow((short) i++);
			row.createCell(0).setCellValue(createHelper.createRichTextString(line.getNf()));
			row.createCell(1).setCellValue(createHelper.createRichTextString(line.getItem()));
			row.createCell(2).setCellValue(createHelper.createRichTextString(line.getCode()));
			row.createCell(3).setCellValue(createHelper.createRichTextString(line.getType()));

			row.createCell(4).setCellValue(line.getQuantity());
			row.createCell(5).setCellValue(line.getUnitValue());
			row.createCell(6).setCellValue(line.getTotalValue());
			row.createCell(7).setCellValue(line.getIpiValue());
		}

		wb.write(fileOut);
		fileOut.close();
	}
}

Line.java

package net.tardivo.prosoft.excel.reader;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.NoArgsConstructor;

@Data
@AllArgsConstructor
@NoArgsConstructor
@EqualsAndHashCode(doNotUseGetters = true, of = { "nf" })
public class Line {

  private String nf;

	private String item;

	private String code;

	private String type;

	private double quantity;

	private double unitValue;

	private double totalValue;

	private double ipiValue;
}