The Problem
High quality datasheets were not supplied by a manufacturer and had to be made for a large catalog of products
Skills Required
Java
A language with plenty of libraries for loading and interpreting Excel data and generating PDFs
Graphic Manipulation
No datasheet template was supplied and had to be created to accommodate the required information
The Project
Using a manufacturer supplied price book I extracted and labeled all product images and spec drawings. I also extracted product finishes and pricing and created an excel spreadsheet of product specifications. Using Java I wrote a short script to load the excel data, images and spec drawings to create individual datasheets.
PDF Writer
import java.io.*;
import java.util.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import com.itextpdf.awt.geom.AffineTransform;
import com.itextpdf.text.*;
import com.itextpdf.text.pdf.BaseFont;
import com.itextpdf.text.pdf.PdfContentByte;
import com.itextpdf.text.pdf.PdfImage;
import com.itextpdf.text.pdf.PdfName;
import com.itextpdf.text.pdf.PdfReader;
import com.itextpdf.text.pdf.PdfStamper;
import com.itextpdf.text.Font;
import com.itextpdf.text.Font.FontFamily;
import com.itextpdf.text.List;
import com.itextpdf.text.pdf.PdfWriter;
import com.itextpdf.text.pdf.TextField;
public class PDFWrite
{
public static void main(String[] args) throws IOException, DocumentException
{
String cellString = null;
String prevCellString = null;
String[] finishes = new String[17];
ArrayList<String> activeFinishes = new ArrayList<>();
File file = new File("ManufacturerPricebookV1.0.xlsx");
//File file = new File("TestFormat.xlsx");
FileInputStream fIP = new FileInputStream(file);
//Get the workbook and the PDF
XSSFWorkbook workbook = new XSSFWorkbook(fIP);
PdfReader pdfReader = null;
PdfStamper pdfStamper = null;
PdfContentByte pageContentByte = null;
BaseFont baseFont = null;
String prevCollection = null;
String prevDescription = null;
if(file.isFile() && file.exists())
{
System.out.println(
"workbook file open successfully.");
XSSFSheet sheet = workbook.getSheetAt(0);
//Get all finish identifiers
int tempR = 0;
for(int i = 0; i < 17; i++)
{
tempR = i+3;
finishes[i] = sheet.getRow(tempR).getCell(9).getStringCellValue();
}
Iterator<Row> rowIterator = sheet.iterator();
rowIterator.next();
Row row = sheet.getRow(1);
prevCollection = row.getCell(2).getStringCellValue();
prevDescription = row.getCell(1).getStringCellValue();
while (rowIterator.hasNext())
{
//HERE IS WHERE THE ACTION IS
row = rowIterator.next();
Cell cell = row.getCell(0);
try
{
cell.getStringCellValue();
}catch(NullPointerException e){
WritePDF(pdfReader, pdfStamper, baseFont, pageContentByte,
prevCellString, prevCollection, prevDescription, activeFinishes);
break;
}
cellString = cell.getStringCellValue();
//Remove finish identifier
String finishCheck = null;
for(int i = 0; i < finishes.length; i++)
{
finishCheck = " " + finishes[i];
if(cellString.endsWith(finishCheck))
{
cellString = cellString.substring(0, cellString.length() - finishes[i].length() - 1);
}
}
//If cell is different product than previous
if(!cellString.equals(prevCellString))
{
//Check for first element
if(prevCellString != null)
{
WritePDF(pdfReader, pdfStamper, baseFont, pageContentByte,
prevCellString, prevCollection, prevDescription, activeFinishes);
}
//After previous product has been written clear finishes
activeFinishes.clear();
activeFinishes.add(row.getCell(3).getStringCellValue());
prevCellString = cellString;
prevCollection = row.getCell(2).getStringCellValue();
prevDescription = row.getCell(1).getStringCellValue();
}else {
activeFinishes.add(row.getCell(3).getStringCellValue());
}
}
WritePDF(pdfReader, pdfStamper, baseFont, pageContentByte,
prevCellString, prevCollection, prevDescription, activeFinishes);
}
else
{
System.out.println(
"Error to open openworkbook.xlsx file.");
}
}
static void WritePDF(PdfReader pdfReader, PdfStamper pdfStamper,
BaseFont baseFont, PdfContentByte pageContentByte,
String prevCellString, String collection,
String description, ArrayList<String> finishes) throws IOException, DocumentException
{
//Write new PDF
pdfReader = new PdfReader("SpecSheetBase.pdf");
String fileNamePlace = prevCellString;
System.out.println(prevCellString.replaceAll("/", "S"));
fileNamePlace = prevCellString.replaceAll("/", "S");
pdfStamper = new PdfStamper(pdfReader, new FileOutputStream("D:\\SpecPDF\\" + fileNamePlace + ".pdf"));
Map<String, String> info = pdfReader.getInfo();
info.put("Author", "Company");
info.put("Title", prevCellString);
pdfStamper.setMoreInfo(info);
baseFont = BaseFont.createFont(BaseFont.HELVETICA, BaseFont.CP1252, BaseFont.NOT_EMBEDDED);
pageContentByte = pdfStamper.getOverContent(1);
//Product Code Text
pageContentByte.beginText();
pageContentByte.setFontAndSize(baseFont, 17);
pageContentByte.setTextMatrix(130 - prevCellString.length() * 5, 665);
pageContentByte.showText(prevCellString);
pageContentByte.endText();
//Collection
pageContentByte.beginText();
pageContentByte.setFontAndSize(baseFont, 16);
pageContentByte.setTextMatrix(95 - collection.length() * 4, 342);
pageContentByte.showText(collection);
pageContentByte.endText();
//Description
if(description.length() > 40)
{
for(int i = 40; i > 30; i--)
{
if(description.charAt(i) == ' ')
{
pageContentByte.beginText();
pageContentByte.setFontAndSize(baseFont, 12);
pageContentByte.setTextMatrix(40, 285);
pageContentByte.showText(description.substring(0, i));
pageContentByte.endText();
pageContentByte.beginText();
pageContentByte.setFontAndSize(baseFont, 12);
pageContentByte.setTextMatrix(40, 270);
pageContentByte.showText(description.substring(i+1, description.length()));
pageContentByte.endText();
break;
}
}
}else {
pageContentByte.beginText();
pageContentByte.setFontAndSize(baseFont, 12);
pageContentByte.setTextMatrix(40, 270);
pageContentByte.showText(description);
pageContentByte.endText();
}
//Finishes
for(int i = 0; i < finishes.size(); i++)
{
if(i < 5)
{
pageContentByte.beginText();
pageContentByte.setFontAndSize(baseFont, 12);
pageContentByte.setTextMatrix(40, 250 - i * 15);
pageContentByte.showText("- " + finishes.get(i));
pageContentByte.endText();
}else{
pageContentByte.beginText();
pageContentByte.setFontAndSize(baseFont, 12);
pageContentByte.setTextMatrix(150, 325 - i * 15);
pageContentByte.showText("- " + finishes.get(i));
pageContentByte.endText();
}
}
//Add the two images
Image image = null;
Image blueprint = null;
try
{
image = Image.getInstance("C:\\Users\\user\\Documents\\CompanyPictures\\"
+ fileNamePlace + " P.png");
blueprint = Image.getInstance("C:\\Users\\user\\Documents\\CompanyPictures\\"
+ fileNamePlace + " B.png");
}
catch(IOException e)
{
}
if(image != null)
{
AffineTransform at = AffineTransform.getTranslateInstance(40, 500);
at.concatenate(AffineTransform.getScaleInstance(
image.getScaledWidth()/4, image.getScaledHeight()/4));
PdfContentByte canvas = pdfStamper.getOverContent(1);
canvas.addImage(image, at);
at = AffineTransform.getTranslateInstance(320, 200);
at.concatenate(AffineTransform.getScaleInstance(
blueprint.getScaledWidth()/4, blueprint.getScaledHeight()/4));
canvas = pdfStamper.getOverContent(1);
canvas.addImage(blueprint, at);
}
pdfStamper.close();
}
}