0

I need java code to read data for specific column from excel sheet. – (lo number, line, voucher no, stloc , quantity ,activity.) These set of values for a particular column will be used for sql query (jdbc-odbc connection done). The output for the query will be matched with a column in this sheet (this part ll be done later) Kindly help. sample excel sheet

1
  • You can use apache poi for working with excel sheets. It's not tough to code with that API. Commented Feb 23, 2012 at 14:33

2 Answers 2

1
/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package excelfilereading;

/**
 *
 * @author vkantiya
 */
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFCell;

import java.io.FileInputStream;
import java.io.IOException;
import java.util.Iterator;
import java.util.List;
import java.util.ArrayList;

public class Main {

    @SuppressWarnings("unchecked")
    public static void main(String[] args) throws Exception {
//
// An excel file name. You can create a file name with a full
// path information.
//
        String filename = "FirstExcel.xls";


// Create an ArrayList to store the data read from excel sheet.
//
        List sheetData = new ArrayList();

        FileInputStream fis = null;
        try {
//
// Create a FileInputStream that will be use to read the
// excel file.
//
            fis = new FileInputStream(filename);

//
// Create an excel workbook from the file system.
//
            HSSFWorkbook workbook = new HSSFWorkbook(fis);
//
// Get the first sheet on the workbook.
//
            HSSFSheet sheet = workbook.getSheetAt(0);

//
// When we have a sheet object in hand we can iterator on
// each sheet's rows and on each row's cells. We store the
// data read on an ArrayList so that we can printed the
// content of the excel to the console.
//
            Iterator rows = sheet.rowIterator();
            while (rows.hasNext()) {
                HSSFRow row = (HSSFRow) rows.next();
                Iterator cells = row.cellIterator();

                List data = new ArrayList();
                while (cells.hasNext()) {
                    HSSFCell cell = (HSSFCell) cells.next();
                    data.add(cell);
                }

                sheetData.add(data);
            }
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (fis != null) {
                fis.close();
            }
        }

        showExelData(sheetData);
    }

    private static void showExelData(List sheetData) {
//
// Iterates the data and print it out to the console.
//
        for (int i = 0; i < sheetData.size(); i++) {
            List list = (List) sheetData.get(i);
            for (int j = 0; j < list.size(); j++) {
                HSSFCell cell = (HSSFCell) list.get(j);
                System.out.print(
                        cell.getRichStringCellValue().getString());
                if (j < list.size() - 1) {
                    System.out.print(", ");
                }
            }
            System.out.println("");
        }
    }
}
Sign up to request clarification or add additional context in comments.

Comments

1

Have a look at Apache POI - the Java API for Microsoft Documents.

It covers

  • Excel (SS=HSSF+XSSF)
  • Word (HWPF+XWPF)
  • PowerPoint (HSLF+XSLF)
  • OpenXML4J (OOXML)
  • OLE2 Filesystem (POIFS)
  • OLE2 Document Props (HPSF)
  • Outlook (HSMF)
  • Visio (HDGF) TNEF (HMEF)
  • Publisher (HPBF)

1 Comment

There's also jxl. Discussion of the two: stackoverflow.com/questions/4763624/…

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.