Friday, 6 July 2018

PDF and Excel

Reading and Writing  in PDF and Exel
============================
Excel File Location :   /Users/10641768/Desktop/BookData.xlsx

Excel Data
------------ 

SNO Name  salary
1 Rajesh  1000
2 suresh  2000
3 ramesh  3000
4 siva  4000
5 ravi  5000

Excel Read and Write Required Jars
----------------------------------------------------

dom4j-1.6.1.jar
poi-3.11.jar
poi-ooxml-3.11-20141221.jar
poi-ooxml-schemas-3.11-20141221.jar

Pom.xml maven Dependencies
------------------------------------------
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.17</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml-schemas -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml-schemas</artifactId>
    <version>3.17</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml-schemas -->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml-schemas</artifactId>
    <version>3.17</version>
</dependency>

Reading the Excel file (All sheets) and Display on console
----------------------------------------------------------------------
import org.apache.poi.ss.usermodel.*;

import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.Iterator;

public class ReadExel {

    private static final String FILE_NAME = "/Users/10641768/Desktop/BookData.xlsx";

    public static void main(String[] args) throws IOException {
    File myFile = new File(FILE_NAME);
    FileInputStream fis = new FileInputStream(myFile);

    // Finds the workbook instance for XLSX file
    XSSFWorkbook myWorkBook = new XSSFWorkbook (fis);
   
    int numberOfSheets = myWorkBook.getNumberOfSheets();
for (int i = 0; i < numberOfSheets; i++) {
    // Return first sheet from the XLSX workbook
    XSSFSheet mySheet = myWorkBook.getSheetAt(i);
   
    // Get iterator to all the rows in current sheet
    Iterator<Row> rowIterator = mySheet.iterator();
   
    // Traversing over each row of XLSX file
    while (rowIterator.hasNext()) {
        Row row = rowIterator.next();

        // For each row, iterate through each columns
        Iterator<Cell> 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 the Data into Excel 
---------------------------------------
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Date;
import java.util.HashMap;
import java.util.LinkedHashMap;
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 WriteExcel {
public static void main(String[] args) throws IOException {
File myFile = new File("/Users/10641768/Desktop/BookData.xlsx");
FileInputStream fis = new FileInputStream(myFile);
// Finds the workbook instance for XLSX file
XSSFWorkbook myWorkBook = new XSSFWorkbook(fis);
// Return first sheet from the XLSX workbook
XSSFSheet mySheet = myWorkBook.getSheetAt(0);

Map<String, Object[]> data = new LinkedHashMap<String, Object[]>();
data.put("15", new Object[] { 11d, "IndianArmy", 11000d});
data.put("16", new Object[] { 12d, "IndianNavy", 12000d });
data.put("17", new Object[] { 13d, "HAL", 1300d});
// Set to Iterate and add rows into XLS file
Set<String> newRows = data.keySet();
// get the last row number to append new data
int rownum = mySheet.getLastRowNum();
for (String key : newRows) {
// Creating a new Row in existing XLSX sheet
Row row = mySheet.createRow(++rownum);
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 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 XLSX file
FileOutputStream os = new FileOutputStream(myFile);
myWorkBook.write(os);
System.out.println("Writing on XLSX file Finished ...");
}

}
Reading and Writing into PDF
-------------------------------------------
Referance : https://www.concretepage.com/itext/create-pdf-with-text-list-table-in-java-using-itext
Jar Required to Manipulate PDF : itextpdf-5.5.13.jar

Maven Dependency for PDF

<!-- https://mvnrepository.com/artifact/com.itextpdf/itextpdf -->
<dependency>
    <groupId>com.itextpdf</groupId>
    <artifactId>itextpdf</artifactId>
    <version>5.5.13</version>

</dependency>

Creating the PDF with Table values
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import com.itextpdf.text.BaseColor;
import com.itextpdf.text.Document;
import com.itextpdf.text.DocumentException;
import com.itextpdf.text.Element;
import com.itextpdf.text.pdf.PdfPCell;
import com.itextpdf.text.pdf.PdfPTable;
import com.itextpdf.text.pdf.PdfWriter;
public class CreatePDFTable {
       public static void main(String[] args) throws FileNotFoundException, DocumentException {
  Document document = new Document();
  PdfPTable table = new PdfPTable(new float[] { 2, 1, 2 });
  table.getDefaultCell().setHorizontalAlignment(Element.ALIGN_CENTER);
  table.addCell("Name");
          table.addCell("Age");
          table.addCell("Location");
  table.setHeaderRows(1);
  PdfPCell[] cells = table.getRow(0).getCells(); 
  for (int j=0;j<cells.length;j++){
     cells[j].setBackgroundColor(BaseColor.GRAY);
  }
          for (int i=1;i<5;i++){
         table.addCell("Name:"+i);
             table.addCell("Age:"+i);
             table.addCell("Location:"+i);
          }
  PdfWriter.getInstance(document, new FileOutputStream("/Users/10641768/Desktop/Book.pdf"));
  document.open();
          document.add(table);
  document.close();
  System.out.println("Done");
      }
=======================================
Reading PDF File 
Referance :  https://www.mkyong.com/java/itext-read-and-write-pdf-in-java/

import com.itextpdf.text.pdf.PdfReader;
import com.itextpdf.text.pdf.parser.PdfTextExtractor;

import java.io.IOException;

public class PdfReadExample {

    private static final String FILE_NAME = "/Users/10641768/Desktop/Book.pdf";

    public static void main(String[] args) {

        PdfReader reader;

        try {

            reader = new PdfReader(FILE_NAME);

            // pageNumber = 1
            String textFromPage = PdfTextExtractor.getTextFromPage(reader, 1);

            System.out.println(textFromPage);

            reader.close();

        } catch (IOException e) {
            e.printStackTrace();
        }

    }

}

Set password to the PDF files 
----------------------------------------
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;

public class CopyThePDFFile {


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


        File f=new File("/Users/10641768/Desktop/Book.pdf");

        OutputStream oos = new FileOutputStream("/Users/10641768/Desktop/test.pdf");

        byte[] buf = new byte[8192];

        InputStream is = new FileInputStream(f);

        int c = 0;

        while ((c = is.read(buf, 0, buf.length)) > 0) {
//        System.out.println(is.read(buf, 0, buf.length));
            oos.write(buf, 0, c);
            oos.flush();
        }

        oos.close();
        System.out.println("stop");
        is.close();

    }

}

you can have some more to jar for PDF Reading and manuplation
<dependency>
 <groupId>org.apache.pdfbox</groupId>
<artifactId>pdfbox</artifactId>
<version>2.0.1</version>
 </dependency>

 <dependency>
 <groupId>org.apache.pdfbox</groupId>
<artifactId>fontbox</artifactId>
<version>2.0.0</version>
 </dependency> 

 <dependency>
 <groupId>org.apache.pdfbox</groupId>
<artifactId>jempbox</artifactId>
<version>1.8.11</version>
 </dependency>

 <dependency>
 <groupId>org.apache.pdfbox</groupId>
<artifactId>xmpbox</artifactId>
<version>2.0.0</version>
 </dependency>

 <dependency>
 <groupId>org.apache.pdfbox</groupId>
<artifactId>preflight</artifactId>
<version>2.0.0</version>
 </dependency>

 <dependency>
 <groupId>org.apache.pdfbox</groupId>
 <artifactId>pdfbox-tools</artifactId>
<version>2.0.0</version>
 </dependency>

No comments:

Post a Comment