Cum de a citi și scrie fișiere excel

try {
    POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(file));
    HSSFWorkbook wb = new HSSFWorkbook(fs);
    HSSFSheet sheet = wb.getSheetAt(0);
    HSSFRow row;
    HSSFCell cell;

    int rows; // No of rows
    rows = sheet.getPhysicalNumberOfRows();

    int cols = 0; // No of columns
    int tmp = 0;

    // This trick ensures that we get the data properly even if it doesn't start from first few rows
    for(int i = 0; i < 10 || i < rows; i++) {
        row = sheet.getRow(i);
        if(row != null) {
            tmp = sheet.getRow(i).getPhysicalNumberOfCells();
            if(tmp > cols) cols = tmp;
        }
    }

    for(int r = 0; r < rows; r++) {
        row = sheet.getRow(r);
        if(row != null) {
            for(int c = 0; c < cols; c++) {
                cell = row.getCell((short)c);
                if(cell != null) {
                    // Your code here
                }
            }
        }
    }
} catch(Exception ioe) {
    ioe.printStackTrace();
}

Workbook wb = new HSSFWorkbook();
//Workbook wb = new XSSFWorkbook();
CreationHelper createHelper = wb.getCreationHelper();
Sheet sheet = wb.createSheet("new sheet");

// Create a row and put some cells in it. Rows are 0 based.
Row row = sheet.createRow((short)0);
// Create a cell and put a value in it.
Cell cell = row.createCell(0);
cell.setCellValue(1);

// Or do it on one line.
row.createCell(1).setCellValue(1.2);
row.createCell(2).setCellValue(
createHelper.createRichTextString("This is a string"));
row.createCell(3).setCellValue(true);

// Write the output to a file
FileOutputStream fileOut = new FileOutputStream("workbook.xls");
wb.write(fileOut);
fileOut.close();

public static void main(String[] args) {
    //Blank workbook
    XSSFWorkbook workbook = new XSSFWorkbook();

    //Create a blank sheet
    XSSFSheet sheet = workbook.createSheet("Employee Data");

    //This data needs to be written (Object[])
    Map data = new TreeMap();
    data.put("1", new Object[]{"ID", "NAME", "LASTNAME"});
    data.put("2", new Object[]{1, "Amit", "Shukla"});
    data.put("3", new Object[]{2, "Lokesh", "Gupta"});
    data.put("4", new Object[]{3, "John", "Adwards"});
    data.put("5", new Object[]{4, "Brian", "Schultz"});

    //Iterate over data and write to sheet
    Set keyset = data.keySet();

    int rownum = 0;
    for (String key : keyset) 
    {
        //create a row of excelsheet
        Row row = sheet.createRow(rownum++);

        //get object array of prerticuler key
        Object[] objArr = data.get(key);

        int cellnum = 0;

        for (Object obj : objArr) 
        {
            Cell cell = row.createCell(cellnum++);
            if (obj instanceof String) 
            {
                cell.setCellValue((String) obj);
            }
            else if (obj instanceof Integer) 
            {
                cell.setCellValue((Integer) obj);
            }
        }
    }
    try 
    {
        //Write the workbook in file system
        FileOutputStream out = new FileOutputStream(new File("C:\\Documents and Settings\\admin\\Desktop\\imp data\\howtodoinjava_demo.xlsx"));
        workbook.write(out);
        out.close();
        System.out.println("howtodoinjava_demo.xlsx written successfully on disk.");
    } 
    catch (Exception e)
    {
        e.printStackTrace();
    }
}

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

public static void main(String[] args) {
    try {
        FileInputStream file = new FileInputStream(new File("C:\\Documents and Settings\\admin\\Desktop\\imp data\\howtodoinjava_demo.xlsx"));

        //Create Workbook instance holding reference to .xlsx file
        XSSFWorkbook workbook = new XSSFWorkbook(file);

        //Get first/desired sheet from the workbook
        XSSFSheet sheet = workbook.getSheetAt(0);

        //Iterate through each rows one by one
        Iterator rowIterator = sheet.iterator();
        while (rowIterator.hasNext())
        {
            Row row = rowIterator.next();
            //For each row, iterate through all the columns
            Iterator cellIterator = row.cellIterator();

            while (cellIterator.hasNext()) 
            {
                Cell cell = cellIterator.next();
                //Check the cell type and format accordingly
                switch (cell.getCellType()) 
                {
                    case Cell.CELL_TYPE_NUMERIC:
                        System.out.print(cell.getNumericCellValue() + "\t");
                        break;
                    case Cell.CELL_TYPE_STRING:
                        System.out.print(cell.getStringCellValue() + "\t");
                        break;
                }
            }
            System.out.println("");
        }
        file.close();
    } catch (Exception e) {
        e.printStackTrace();
    }
}

public class User { 

  @Column (name="Firstname")
  private String firstName;

  @Column (name="Lastname")
  private String lastName;

  @Column
  private long id; 

  @Column
  private Date birthDate; 
}

Xcelite xcelite = new Xcelite();    
XceliteSheet sheet = xcelite.createSheet("users");
SheetWriter writer = sheet.getBeanWriter(User.class);
List users = new ArrayList();
// ...fill up users
writer.write(users); 
xcelite.write(new File("users_doc.xlsx"));

Xcelite xcelite = new Xcelite(new File("users_doc.xlsx"));
XceliteSheet sheet = xcelite.getSheet("users");
SheetReader reader = sheet.getBeanReader(User.class);
Collection users = reader.read();

public static void readXLSXFile() throws IOException
    {
        InputStream ExcelFileToRead = new FileInputStream("C:/Test.xlsx");
        XSSFWorkbook  wb = new XSSFWorkbook(ExcelFileToRead);

        XSSFWorkbook test = new XSSFWorkbook(); 

        XSSFSheet sheet = wb.getSheetAt(0);
        XSSFRow row; 
        XSSFCell cell;

        Iterator rows = sheet.rowIterator();

        while (rows.hasNext())
        {
            row=(XSSFRow) rows.next();
            Iterator cells = row.cellIterator();
            while (cells.hasNext())
            {
                cell=(XSSFCell) cells.next();

                if (cell.getCellType() == XSSFCell.CELL_TYPE_STRING)
                {
                    System.out.print(cell.getStringCellValue()+" ");
                }
                else if(cell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC)
                {
                    System.out.print(cell.getNumericCellValue()+" ");
                }
                else
                {
                    //U Can Handel Boolean, Formula, Errors
                }
            }
            System.out.println();
        }

    }

String path="C:\\Book2.xlsx";
try {

        File f = new File( path );
        Workbook wb = WorkbookFactory.create(f);
        Sheet mySheet = wb.getSheetAt(0);
        Iterator rowIter = mySheet.rowIterator();
        for ( Iterator rowIterator = mySheet.rowIterator() ;rowIterator.hasNext(); )
        {
            for (  Iterator cellIterator = ((Row)rowIterator.next()).cellIterator() ; cellIterator.hasNext() ;  ) 
            {
                System.out.println ( ( (Cell)cellIterator.next() ).toString() );
            }
            System.out.println( " **************************************************************** ");
        }
    } catch ( Exception e )
    {
        System.out.println( "exception" );
        e.printStackTrace();
    }

     public class ExcelUtils {
     private static XSSFSheet ExcelWSheet;
     private static XSSFWorkbook ExcelWBook;
     private static XSSFCell Cell;
     private static XSSFRow Row;
     File fileName = new File("C:\\Users\\satekuma\\Pro\\Fund.xlsx");
     public void setExcelFile(File Path, String SheetName) throws Exception                

    try {
        FileInputStream ExcelFile = new FileInputStream(Path);
        ExcelWBook = new XSSFWorkbook(ExcelFile);
        ExcelWSheet = ExcelWBook.getSheet(SheetName);
    } catch (Exception e) {
        throw (e);
    }

}

      public static String getCellData(int RowNum, int ColNum) throws Exception {

    try {
        Cell = ExcelWSheet.getRow(RowNum).getCell(ColNum);
        String CellData = Cell.getStringCellValue();
        return CellData;
    } catch (Exception e) {

        return "";

    }

}
public static void setCellData(String Result, int RowNum, int ColNum, File Path) throws Exception {

    try {
        Row = ExcelWSheet.createRow(RowNum - 1);
        Cell = Row.createCell(ColNum - 1);
        Cell.setCellValue(Result);
        FileOutputStream fileOut = new FileOutputStream(Path);
        ExcelWBook.write(fileOut);
        fileOut.flush();
        fileOut.close();
    } catch (Exception e) {

        throw (e);

    }

}

}

if (fileName.endsWith(".xls")) {

File myFile = new File("file location" + fileName);
                FileInputStream fis = new FileInputStream(myFile);

                org.apache.poi.ss.usermodel.Workbook workbook = null;
                try {
                    workbook = WorkbookFactory.create(fis);
                } catch (InvalidFormatException e) {

                    e.printStackTrace();
                }

                org.apache.poi.ss.usermodel.Sheet sheet = workbook.getSheetAt(0);

                Iterator rowIterator = sheet.iterator();

                while (rowIterator.hasNext()) {
                    Row row = rowIterator.next();

                    Iterator cellIterator = row.cellIterator();
                    while (cellIterator.hasNext()) {

                        Cell cell = cellIterator.next();
                        switch (cell.getCellType()) {
                        case Cell.CELL_TYPE_STRING:
                            System.out.print(cell.getStringCellValue());
                            break;
                        case Cell.CELL_TYPE_BOOLEAN:
                            System.out.print(cell.getBooleanCellValue());
                            break;
                        case Cell.CELL_TYPE_NUMERIC:
                            System.out.print(cell.getNumericCellValue());
                            break;
                        }
                        System.out.print(" - ");
                    }
                    System.out.println();
                }
            }

    import java.io.File;
    import java.io.FileInputStream;
    import java.io.FileNotFoundException;
    import java.io.FileOutputStream;
    import java.io.IOException;
    import java.util.ArrayList;
    import java.util.List;
    import java.util.Iterator;
    //*************************************************************
    import org.apache.poi.ss.usermodel.Sheet;
    import org.apache.poi.ss.usermodel.Cell;
    import org.apache.poi.ss.usermodel.Row;
    import org.apache.poi.ss.usermodel.Workbook;
    import org.apache.poi.xssf.usermodel.XSSFSheet;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;

    //*************************************************************
   public class AdvUse {

    private static Workbook wb ; 
    private static Sheet sh ; 
    private static FileInputStream fis ; 
    private static FileOutputStream fos  ; 
    private static Row row  ; 
    private static Cell cell  ;
    private static String ExcelPath ; 

    //*************************************************************
    public static void setEcxelFile(String ExcelPath, String SheetName) throws Exception {
    try {
   File f= new File(ExcelPath); 
   if(!f.exists()){
       f.createNewFile();
       System.out.println("File not Found so created");
   }

    fis = new FileInputStream("./testData.xlsx");
    wb = WorkbookFactory.create(fis); 
    sh = wb.getSheet("SheetName");
    if(sh == null){
        sh = wb.getSheet(SheetName); 
    }
    }catch(Exception e)
    {System.out.println(e.getMessage());
    }
    }

      //*************************************************************
      public static void setCellData(String text , int rowno , int colno){
    try{
        row = sh.getRow(rowno);
        if(row == null){
            row = sh.createRow(rowno);
        }
        cell = row.getCell(colno);
        if(cell!=null){
            cell.setCellValue(text);

        }
        else{
            cell = row.createCell(colno);
            cell.setCellValue(text);

        }
        fos = new FileOutputStream(ExcelPath);
        wb.write(fos);
        fos.flush();
        fos.close();
    }catch(Exception e){
        System.out.println(e.getMessage());
    }
    }

      //*************************************************************
      public static String getCellData(int rowno , int colno){
        try{

            cell = sh.getRow(rowno).getCell(colno); 
            String CellData = null ;
            switch(cell.getCellType()){
            case  STRING :
                CellData = cell.getStringCellValue();
               break ; 
            case NUMERIC : 
                CellData = Double.toString(cell.getNumericCellValue());
                if(CellData.contains(".o")){
                    CellData = CellData.substring(0,CellData.length()-2);

                }
            break ; 
            case BLANK : 
            CellData = ""; break ; 

            }
            return CellData;
        }catch(Exception e){return ""; }
    }

       //*************************************************************
      public static int getLastRow(){
        return sh.getLastRowNum();
    }

  try {
POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(Dir));
HSSFWorkbook wb = new HSSFWorkbook(fs);
HSSFSheet sheet = wb.getSheetAt(0);
HSSFRow row;
HSSFCell cell;

int rows; // No of rows
rows = sheet.getPhysicalNumberOfRows();

int cols = 0; // No of columns
int tmp = 0;
int cblacks=0;

// This trick ensures that we get the data properly even if it doesn't start from first few rows
for(int i = 0; i 

package com.org.tests;
import org.apache.poi.xssf.usermodel.*;
import java.io.FileInputStream;
import java.io.IOException;

public class ExcelSimpleTest 
{   
    String path;
    public FileInputStream fis = null;
    private XSSFWorkbook workbook = null;
    private XSSFSheet sheet = null;
    private XSSFRow row   =null;
    private XSSFCell cell = null;

    public ExcelSimpleTest() throws IOException
    {
        path = System.getProperty("user.dir")+"\\resources\\Book1.xlsx";
        fis = new FileInputStream(path); 
        workbook = new XSSFWorkbook(fis);
        sheet = workbook.getSheetAt(0);
    }
    public void ExelWorks()
    {
        int index = workbook.getSheetIndex("Sheet1");
        sheet = workbook.getSheetAt(index);
        int rownumber=sheet.getLastRowNum()+1;  

        for (int i=1; i

public class TakingDataFromExcel {
   public static void main(String[] args) {
    try
    {
        FileInputStream x = new FileInputStream(new File("/Users/rajesh/Documents/rajesh.xls"));

        //Create Workbook instance holding reference to .xlsx file
        Workbook workbook = new HSSFWorkbook(x);

        //Get first/desired sheet from the workbook
        Sheet sheet = workbook.getSheetAt(0);

        //Iterate through each rows one by one
        for (Iterator iterator = sheet.iterator(); iterator.hasNext();) {
            Row row = (Row) iterator.next();
            for (Iterator iterator2 = row.iterator(); iterator2
                    .hasNext();) {
                Cell cell = (Cell) iterator2.next();
                System.out.println(cell.getStringCellValue());              
            }               
        }         
        x.close();
    }
    catch (Exception e)
    {
        e.printStackTrace();
    }
   }
}

try (Stream rowStream = Windmill.parse(FileSource.of(new FileInputStream("myFile.xlsx")))) {
  rowStream
    // skip the header row that contains the column names
    .skip(1)
    .forEach(row -> {
      System.out.println(
        "row n°" + row.rowIndex()
        + " column 'User login' value : " + row.cell("User login").asString()
        + " column n°3 number value : " + row.cell(2).asDouble().value() // index is zero-based
      );
    });
}

Windmill
  .export(Arrays.asList(bean1, bean2, bean3))
  .withHeaderMapping(
    new ExportHeaderMapping()
      .add("Name", Bean::getName)
      .add("User login", bean -> bean.getUser().getLogin())
  )
  .asExcel()
  .writeTo(new FileOutputStream("Export.xlsx"));

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;
import java.util.Set;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class XLSXReaderWriter {

    public static void main(String[] args) {

        try {
            File excel = new File("D://raju.xlsx");
            FileInputStream fis = new FileInputStream(excel);
            XSSFWorkbook book = new XSSFWorkbook(fis);
            XSSFSheet sheet = book.getSheetAt(0);

            Iterator itr = sheet.iterator();

            // Iterating over Excel file in Java
            while (itr.hasNext()) {
                Row row = itr.next();

                // Iterating over each column of Excel file
                Iterator cellIterator = row.cellIterator();
                while (cellIterator.hasNext()) {

                    Cell cell = cellIterator.next();

                    switch (cell.getCellType()) {
                    case Cell.CELL_TYPE_STRING:
                        System.out.print(cell.getStringCellValue() + "\t");
                        break;
                    case Cell.CELL_TYPE_NUMERIC:
                        System.out.print(cell.getNumericCellValue() + "\t");
                        break;
                    case Cell.CELL_TYPE_BOOLEAN:
                        System.out.print(cell.getBooleanCellValue() + "\t");
                        break;
                    default:

                    }
                }
                System.out.println("");
            }

            // writing data into XLSX file
            Map newData = new HashMap();
            newData.put("1", new Object[] { 1d, "Raju", "75K", "dev",
                    "SGD" });
            newData.put("2", new Object[] { 2d, "Ramesh", "58K", "test",
                    "USD" });
            newData.put("3", new Object[] { 3d, "Ravi", "90K", "PMO",
                    "INR" });

            Set newRows = newData.keySet();
            int rownum = sheet.getLastRowNum();

            for (String key : newRows) {
                Row row = sheet.createRow(rownum++);
                Object[] objArr = newData.get(key);
                int cellnum = 0;
                for (Object obj : objArr) {
                    Cell cell = row.createCell(cellnum++);
                    if (obj instanceof String) {
                        cell.setCellValue((String) obj);
                    } else if (obj instanceof Boolean) {
                        cell.setCellValue((Boolean) obj);
                    } else if (obj instanceof Date) {
                        cell.setCellValue((Date) obj);
                    } else if (obj instanceof Double) {
                        cell.setCellValue((Double) obj);
                    }
                }
            }

            // open an OutputStream to save written data into Excel file
            FileOutputStream os = new FileOutputStream(excel);
            book.write(os);
            System.out.println("Writing on Excel file Finished ...");

            // Close workbook, OutputStream and Excel file to prevent leak
            os.close();
            book.close();
            fis.close();

        } catch (FileNotFoundException fe) {
            fe.printStackTrace();
        } catch (IOException ie) {
            ie.printStackTrace();
        }
    }
}

import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import javax.swing.JTable;
import javax.swing.table.TableModel;

public class excel {
    String columnNames[] = { "Column 1", "Column 2", "Column 3" };

    // Create some data
    String dataValues[][] =
    {
        { "12", "234", "67" },
        { "-123", "43", "853" },
        { "93", "89.2", "109" },
        { "279", "9033", "3092" }
    };

    JTable table;

    excel() {
        table = new JTable( dataValues, columnNames );
    }

    public void toExcel(JTable table, File file){
        try{
            TableModel model = table.getModel();
            FileWriter excel = new FileWriter(file);

            for(int i = 0; i < model.getColumnCount(); i++){
                excel.write(model.getColumnName(i) + "\t");
            }

            excel.write("\n");

            for(int i=0; i< model.getRowCount(); i++) {
                for(int j=0; j < model.getColumnCount(); j++) {
                    excel.write(model.getValueAt(i,j).toString()+"\t");
                }
                excel.write("\n");
            }

            excel.close();

        }catch(IOException e){ System.out.println(e); }
    }

    public static void main(String[] o) {
        excel cv = new excel();
        cv.toExcel(cv.table,new File("C:\\Users\\itpr13266\\Desktop\\cs.tbv"));
    }
}

import java.io.BufferedReader;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.IOException;
import java.io.PrintWriter;
import java.util.Scanner;

public class CsvWriter {
 public static void main(String args[]) throws IOException {

  String fileName = "test.xls";

  PrintWriter out = new PrintWriter(new FileWriter(fileName));
  out.println("a,b,c,d");
  out.println("e,f,g,h");
  out.println("i,j,k,l");
  out.close();

  BufferedReader in = new BufferedReader(new FileReader(fileName));
  String line = null;
  while ((line = in.readLine()) != null) {

   Scanner scanner = new Scanner(line);
   String sep = "";
   while (scanner.hasNext()) {
    System.out.println(sep + scanner.next());
    sep = ",";
   }
  }
  in.close();
 }
}