This post is going to show you how we can export data from Microsoft Access database to PDF as a table, by using Free Spire.PDF for Java. Also, this article addresses some related issues as follows.
- How to paginate a table if it exceeds one page?
- How to format a table, for instance, how to set alternating row colors, and how to align text within a cell?
- How to detect the bottom of a table, so we can add other content below?
Note: The JDBC-ODBC Bridge has been removed from Java SE 8 and is no longer supported. To connect to Access database in Java, you can use Java SE 7 or the lower version, otherwise you’ll need to use something like UCanAccess instead of JDBC. This example uses the JDK 6 along with JDBC without any problem.
Using the code
import com.spire.pdf.graphics.*;
import com.spire.pdf.tables.*;
import com.spire.pdf.tables.table.*;
import com.spire.pdf.tables.table.common.JdbcAdapter;
import java.awt.geom.*;
import java.awt.*;
import java.sql.*;
public class ExportDataToPdf {
public static void main(String[] args) {
//create a PDF document
PdfDocument doc = new PdfDocument();
//set page margins
doc.getPageSettings().setMargins(30f,30f,30f,30f);
//add a page
PdfPageBase page = doc.getPages().add();
//initialize y coordinate
float y = 0;
//create a brush
PdfBrush brush = PdfBrushes.getBlack();
//create four types of fonts
PdfTrueTypeFont titleFont = new PdfTrueTypeFont(new Font("Arial", Font.BOLD, 16));
PdfTrueTypeFont tableFont= new PdfTrueTypeFont(new Font("Arial", 0, 10));
PdfTrueTypeFont headerFont= new PdfTrueTypeFont(new Font("Arial", Font.BOLD, 11));
PdfTrueTypeFont textFont= new PdfTrueTypeFont(new Font("Arial", 0, 12));
//draw title on the center of the page
PdfStringFormat format = new PdfStringFormat(PdfTextAlignment.Center);
page.getCanvas().drawString("Employee Table", titleFont, brush, page.getCanvas().getClientSize().getWidth() / 2, y, format);
//calculate y coordinate
y = y + (float) titleFont.measureString("Employee Table", format).getHeight();
y = y + 5;
//create a PdfTable instance
PdfTable table = new PdfTable();
//set the default cell style and row style
table.getStyle().setCellPadding(2);
table.getStyle().setBorderPen(new PdfPen(brush, 0.75f));
table.getStyle().getDefaultStyle().setBackgroundBrush(PdfBrushes.getWhite());
table.getStyle().getDefaultStyle().setFont(tableFont);
table.getStyle().getDefaultStyle().setStringFormat(new PdfStringFormat(PdfTextAlignment.Center));
table.getStyle().getAlternateStyle().setBackgroundBrush(PdfBrushes.getLightGray());
table.getStyle().getAlternateStyle().setFont(tableFont);
table.getStyle().getAlternateStyle().setStringFormat(new PdfStringFormat(PdfTextAlignment.Center));
//set the header style
table.getStyle().setHeaderSource(PdfHeaderSource.Column_Captions);
table.getStyle().getHeaderStyle().setBackgroundBrush(PdfBrushes.getPurple());
table.getStyle().getHeaderStyle().setFont(headerFont);
table.getStyle().getHeaderStyle().setTextBrush(PdfBrushes.getWhite());
table.getStyle().getHeaderStyle().setStringFormat(new PdfStringFormat(PdfTextAlignment.Center));
//show header at every page
table.getStyle().setShowHeader(true);
//connect to database
String url = "jdbc:odbc:driver={Microsoft Access Driver (*.mdb)};DBQ=" + "C:\\Users\\Administrator\\Documents\\data.mdb";
DataTable dataTable = new DataTable();
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
try {
Connection conn = DriverManager.getConnection(url);
Statement sta = conn.createStatement();
ResultSet resultSet = sta.executeQuery("select * from employee ");
JdbcAdapter jdbcAdapter = new JdbcAdapter();
//export data from database to datatable
jdbcAdapter.fillDataTable(dataTable, resultSet);
table.setDataSourceType(PdfTableDataSourceType.Table_Direct);
//fill the table with datatable
table.setDataSource(dataTable);
} catch (SQLException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
//paginate table
PdfTableLayoutFormat tableLayout = new PdfTableLayoutFormat();
tableLayout.setLayout(PdfLayoutType.Paginate);
//draw table at the specified x, y coordinates
PdfLayoutResult result = table.draw(page, new Point2D.Float(0, y), tableLayout);
//calculate y coordinate
y = (float) result.getBounds().getHeight() + 5;
//draw text under the table
result.getPage().getCanvas().drawString(String.format("* %1$s employees in the list.", table.getRows().getCount()), textFont, brush, 5, y);
//save pdf file.
doc.saveToFile("ExportDataToPdf.pdf");
}
}
Here is the screenshot of the resulting file.
Close shot.
Top comments (0)