Wednesday 5 February 2014

Exporting SQLite database into pdf with PDFJET

In this tutorial I will explain how to convert an SQLite database into pdf using a third party library, PDFJET. You can download the library, as well as the documentation, from this site. Make sure to download the open source library, which is free but relatively complete (there is also a more complete version, which you can use only for evaluation purposes, but you have to pay for it for developing your apps).



Why using PDFJET library

There are 3 main reasons for using PDFJET library in my opinion:
  1. there is an open source version of the library (FREE);
  2. it’s relatively complete (it has all the main features you need to create a pdf file);
  3. it’s light (about 350 kb).
Of course if you need more advanced features you should better use the pay version of the library, or other librarries that you prefer.

Setting up your project (Eclipse) to use the library

To use PDFJET library in your project (Eclipse) follow these steps:
  1. download the library from this page (open source version);
  2. extract the files to a different folder;
  3. in Eclipse create the package com.pdfjet in the src folder;
  4. copy alla java files that you find in the folder open source\PDFjet-Open-Source\com\pdfjet in the package com.pdfjet that you have created;
  5. import the package in your class with import com.pdfjet.
Now you can use the PDFJET library in your class.

Using the library

If you visit the PDFJET official site you’ll find dozens of examples about how to use the library and a complete documentation of all the classes available (the documentation can also be found in the library you download from the website: look at the folder open source\PDFjet-Open-Source\docs\java\com\pdfjet).
In this tutorial I will only give you a few examples of the instructions you can use to quickly convert an SQLite database into a pdf file.

Creating the pdf file

Let’s suppose we want to create a pdf file, Budget.pdf, in the external storage directory of our device. Here is the code:

String state = Environment.getExternalStorageState();
//check if the external directory is availabe for writing
if (!Environment.MEDIA_MOUNTED.equals(state)) {
return;
}
else {
File exportDir = Environment.getExternalStoragePublicDirectory(Environment.DIRECTORY_DOWNLOADS);
}

//if the external storage directory does not exists, we create it
if (!exportDir.exists()) {
exportDir.mkdirs();
}
File file;
file = new File(exportDir, "Budget.pdf");

//PDF is a class of the PDFJET library
PDF pdf = new PDF(new FileOutputStream(file));

//instructions to create the pdf file content

pdf.flush();

Title

Now suppose that we want to create a title and center it on the page (horizontally):

//first we create a page with portrait orientation
Page page = new Page(pdf, Letter.PORTRAIT);

//font of the title
Font f1 = new Font(pdf, CoreFont.HELVETICA_BOLD);
f1.setSize(7.0f);

//title: font f1 and color blue
TextLine title = new TextLine(f1, “TABLE’S TITLE”);
title.setFont(f1);
title.setColor(Color.blue);

//center the title horizontally on the page
title.setPosition(page.getWidth()/2 – title.getWidth()/2, 40f);

//draw the title on the page
title.drawOn(page);

Links

Here is the code to create a link to our website:

//font of the link
Font f2 = new Font(pdf, CoreFont.HELVETICA);
f2.setSize(7.0f);

//text of the link
TextLine website = new TextLine(f2, "Visit Website");
website.setColor(Color.blue);
website.setUnderline(true);
website.setURIAction("https://sites.google.com/site/flingsoftware/");

//position of the link
website.setPosition(40f, 40f);

//draw the link on the page
website.drawOn(page);

Creating a table

To convert an SQLite database into pdf we need of course a table. Here is the code to create it:

Table table = new Table();
List<List<Cell>> tableData = new ArrayList<List<Cell>>();

As you can see to create a table we use an ArrayList where each element is represented by another ArrayList containing Cell objects. Cell is a class of the PDFJET library used to create a single cell in the table.

Adding columns’ titles

List<Cell> columnTitles = new ArrayList<Cell>();
columnTitles.add(new Cell(f1, “COLUMN 1”);
columnTitles.add(new Cell(f1, “COLUMN 2”);
columnTitles.add(new Cell(f1, “COLUMN 3”);
columnTitles.add(new Cell(f1, “COLUMN 4”);
columnTitles.add(new Cell(f1, “COLUMN 5”);
columnTitles.add(new Cell(f1, “COLUMN 6”);

//light gray background and center alignment
for(int i=0; i<6; i++) {
   ((Cell) columnTitles.get(i)).setBgColor(Color.lightyellow);
   ((Cell) columnTitles.get(i)).setTextAlignment(Align.CENTER);
}
tableData.add(columnTitles);

In this example we created an ArrayList of Cells, each of which representing the title of a column, we set the color background and alignment of each Cell, and finally added the ArrayList to the “ArrayList of ArrayLists” tableData, our complete table.

Adding database records to the table

DateFormat df = DateFormat.getDateInstance(DateFormat.SHORT, Locale.getDefault());
NumberFormat nf = NumberFormat.getCurrencyInstance(Locale.getDefault());

int i = 1;
while(myCursor.moveToNext()) {
   //retrieve data from cursor  
   Long date = myCursor.getLong(myCursor.getColumnIndex("date"));
   String item = myCursor.getString(myCursor.getColumnIndex("item"));
   Double amount = myCursor.getDouble(myCursor.getColumnIndex("amount"));
   String currency = myCursor.getString(myCursor.getColumnIndex("currency"));
   Double convAmount = myCursor.getDouble(myCursor.getColumnIndex("converted_amount"));
   String description = myCursor.getString(myCursor.getColumnIndex("description"));

   //next record in the table
   List<Cell> record = new ArrayList<Cell>();

   //create Cells and add them to the record
   Cell dateCell = new Cell(f2, df.format(new Date(date)));
   dateCell.setTextAlignment(Align.CENTER);
   record.add(dataCell);

   record.add(new Cell(f2, item));

   Cell amountCell = new Cell(f2, nf.format(amount));
   amountCell.setTextAlignment(Align.RIGHT);
   record.add(amountCell);

   Cell currencyCell = new Cell(f2, currency);
   currencyCell.setTextAlignment(Align.CENTER);
   record.add(currencyCell);

   Cell convAmountCell = new Cell(f2, nf.format(convAmount));
   convAmountCell.setTextAlignment(Align.RIGHT);
   record.add(convAmountCell);
   record.add(new Cell(f2, description));
   
   //add the record to the table
   tableData.add(record);

   //one line gray and one line white...
   if(i%2 == 0) {
dateCell.setBgColor(Color.lightgray);
amountCell.setBgColor(Color.lightgray);
        ...
   }
   i++;
}

In the previous example we iterate over the Cursor retrieved from the database and write each record in the table, field (each represented by a Cell object of the PDFJET library) after field.

Printing the table on the page

//populate the table with our tableData ArrayList
table.setData(tableData, Table.DATA_HAS_1_HEADER_ROWS);
//autoadjust column widths to fit the content
table.autoAdjustColumnWidths();
//let’s suppose we want to manually set the width of column 0
table.setColumnWidth(0, 40.0f);
//each cell can contain more rows
table.wrapAroundCellText();

table.setPosition(page.getWidth()/2 – table.getWidth()/2, 40f);
table.drawOn(page);

Printing the table on more than one page

If your table is particularly long and you need more than one page to print it, you can use the following loop:

int numOfPages = table.getNumberOfPages(page);
int currentPage = 0;
while (true) {
point = table.drawOn(page);

   //print page number
   TextLine numPag = new TextLine(f2, ++currentPage + “ of “ + numOfPages);
   numPag.setPosition(largPag - 30.0f - numPag.getWidth(), page.getHeight() - 20.0f);
   numPag.drawOn(page);

   //scrivo il nome tabella a fondo pagina
   TextLine tabella = new TextLine(f2,    mioContext.getString(R.string.menu_esporta_tabellaPdf_fondoPaginaSpese));
   tabella.setPosition(30.0f, page.getHeight() - 20.0f);
   tabella.drawOn(page);
   if (!table.hasMoreData()) {
      // Allow the table to be drawn again later:
      table.resetRenderedPagesCount();
      break;
   }
   page = new Page(pdf, Letter.PORTRAIT);
}

11 comments:

  1. Nice post :) but I want to ask to you for looping step of writing data from database cursor to table. I had tried but I got noticed that index out of bounds. Can you help me how to change rows of writing data on table ? thank you

    ReplyDelete
    Replies
    1. In my example I used 6 columns (with 6 titles) because I had a Cursor where each record had 6 fields.
      In your app are you sure you have created the table with the correct number of column/fields?
      After checking this, try to post your code and the Logcat with the error, otherwise it is difficult to find out where the error might be.

      Delete
  2. Thanks for your reply, yes I'am sure that I have a fix table column, this is my code for writing data to table :
    cr = dslaporan.fetchLaporan(start, end, String.valueOf(spinner2.getSelectedItemPosition()+1));
    List record = new ArrayList();
    for(cr.moveToFirst(); !cr.isAfterLast(); cr.moveToNext()){
    String tgl = cr.getString(cr.getColumnIndex("tanggal"));
    com.pdfjet.Cell tanggal = new com.pdfjet.Cell(f2,tgl);

    String no = cr.getString(cr.getColumnIndex("nomor"));
    com.pdfjet.Cell telpon = new com.pdfjet.Cell(f2,no);

    String nom = cr.getString(cr.getColumnIndex("nominal"));
    com.pdfjet.Cell nominal = new com.pdfjet.Cell(f2,nom);

    String sts = cr.getString(cr.getColumnIndex("status"));
    com.pdfjet.Cell status = new com.pdfjet.Cell(f2,sts);

    String hrg = cr.getString(cr.getColumnIndex("harga"));
    com.pdfjet.Cell harga = new com.pdfjet.Cell(f2,hrg);
    record.add(tanggal);
    record.add(telpon);
    record.add(nominal);
    record.add(status);
    record.add(harga);
    tableData.add(record);
    }

    the result of my code will be return index out of bounds, the correctly must be like this :

    tanggal | telpon | nominal | status | harga
    2014 | 0271xxx | 5000 Reg | 2 | 6000
    2014 | 0291xxx | 5000 SMS | 1 | 6500

    but in my analysis that code will be return table like this
    tanggal | telpon | nominal | status | harga
    2014 | 0271xxx | 5000 Reg | 2 | 6000 | 2014 | 0291xxx | 5000 SMS | 1 | 6500

    Can you help me how to change row of column if the result like that?
    thanks fir your help :)

    ReplyDelete
    Replies
    1. Ok, in your example you have to put the line:
      List record = new ArrayList(); (better List record = new ArrayList();)

      inside the loop as the first instruction.
      In your example you continue to use the same ArrayList, progressively increasing its size (6, 12, 18, ecc.) beyond the table's size.

      Delete
  3. This comment has been removed by the author.

    ReplyDelete
  4. Hi,

    Can i get complete source code of the above sample project?

    ReplyDelete
  5. Hi MyKnowledge,
    in the tutorial I extrapolated all the relevant code needed to create the pdf. I left out other parts that are not relevant because they refer to specific classes that I used in my app: including them would only create unnecessary confusion.

    ReplyDelete
  6. Please upload complete exmaple on gitHub or give us Link.. This is really very Usefull project

    ReplyDelete
  7. The market that exists in a new security just after the new issue, is often referred to as the aftermarket.

    fax blast

    ReplyDelete
  8. This is used in Eclips.. i am using android studoi.. please help

    ReplyDelete
  9. Wonderful beat ! I would like to apprentice while you amend your web site, how can i subscribe for a blog website? The account helped me a acceptable deal. I had been a little bit acquainted of this your broadcast provided bright clear concept. macbook air reparatur berlin

    ReplyDelete