apache-poi

Best language to parse extremely large Excel 2007 files [closed]

I kept getting all kinds of weird errors when working with .xlsx files. Here’s a simple example of using Apache POI to traverse an .xlsx file. See also Upgrading to POI 3.5, including converting existing HSSF Usermodel code to SS Usermodel (for XSSF and HSSF). import java.io.FileInputStream; import java.io.IOException; import java.io.InputStream; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.DateUtil; … Read more

Writing a large resultset to an Excel file using POI

Using SXSSF poi 3.8 package example; import java.io.FileInputStream; import java.io.FileOutputStream; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.util.CellReference; import org.apache.poi.xssf.streaming.SXSSFSheet; import org.apache.poi.xssf.streaming.SXSSFWorkbook; import org.apache.poi.xssf.usermodel.XSSFWorkbook; public class SXSSFexample { public static void main(String[] args) throws Throwable { FileInputStream inputStream = new FileInputStream(“mytemplate.xlsx”); XSSFWorkbook wb_template = new XSSFWorkbook(inputStream); inputStream.close(); SXSSFWorkbook wb = new SXSSFWorkbook(wb_template); wb.setCompressTempFiles(true); SXSSFSheet sh = (SXSSFSheet) … Read more

Get Cell Value from Excel Sheet with Apache Poi

You have to use the FormulaEvaluator, as shown here. This will return a value that is either the value present in the cell or the result of the formula if the cell contains such a formula : FileInputStream fis = new FileInputStream(“/somepath/test.xls”); Workbook wb = new HSSFWorkbook(fis); //or new XSSFWorkbook(“/somepath/test.xls”) Sheet sheet = wb.getSheetAt(0); FormulaEvaluator … Read more

What does the statement Object[][] data = new Object [][] mean?

In this statement Object[][] data = new Object[sheet.getLastRowNum()][sheet.getRow(0).getLastCellNum()]; It is creating an array of references to arrays of references to Objects. The first array is for rows and the leaf arrays are for column values. In reality, only String objects are added so I would write it like this. int rows = sheet.getLastRowNum(); int columns … Read more

Reading Specific rows of an excel [closed]

You should try reading the documentation that comes with Apache POI! Taken straight from there: // Decide which rows to process int rowStart = Math.min(1, sheet.getFirstRowNum()); // 0 based not 1 based rows int rowEnd = Math.max(12, sheet.getLastRowNum()); for (int rowNum = rowStart; rowNum < rowEnd; rowNum++) { Row r = sheet.getRow(rowNum); int lastColumn = … Read more