logic.Export.java Source code

Java tutorial

Introduction

Here is the source code for logic.Export.java

Source

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package logic;

import java.awt.Color;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.NumberFormat;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.Locale;
import javax.swing.JFileChooser;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.commons.io.FilenameUtils;
import org.apache.poi.hssf.usermodel.HSSFPrintSetup;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;

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

/**
 *
 * @author Lars
 */
public class Export {

    private JFileChooser newPath;
    private String templatePath;

    public Export(JFileChooser path, String templatePath) {
        this.newPath = path;
        this.templatePath = templatePath;
    }

    public boolean convertXls()
            throws IOException, FileNotFoundException, IllegalArgumentException, ParseException {
        FileInputStream tamplateFile = new FileInputStream(templatePath);
        XSSFWorkbook workbook = new XSSFWorkbook(tamplateFile);

        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setDataFormat(workbook.getCreationHelper().createDataFormat().getFormat("#,##"));
        double hours = 0.0;
        NumberFormat format = NumberFormat.getInstance(Locale.FRANCE);
        Number number;
        XSSFSheet sheet;
        XSSFSheet sheet2;
        Cell cell = null;
        ConvertData cd = new ConvertData();
        for (int i = 0; i < cd.getSheetnames().size(); i++) {
            sheet2 = workbook.cloneSheet(0, cd.sheetnames.get(i));
            sheet = workbook.getSheetAt(i + 1);
            //formate sheets
            sheet.getPrintSetup().setLandscape(true);
            sheet.getPrintSetup().setPaperSize(HSSFPrintSetup.A4_PAPERSIZE);

            cell = sheet.getRow(0).getCell(1);
            cell.setCellValue(cd.sheetnames.get(i));
            ArrayList<String[]> convert = cd.convert(cd.sheetnames.get(i));
            //setPrintArea 
            workbook.setPrintArea(i + 1, //sheet index
                    0, //start column Spalte
                    6, //end column
                    0, //start row zeile
                    convert.size() + 8 //end row
            );
            for (int Row = 0; Row < convert.size(); Row++) {
                for (int Cell = 0; Cell < convert.get(Row).length; Cell++) {
                    cell = sheet.getRow(9 + Row).getCell(Cell);
                    if (Cell == 3) {
                        if ("true".equals(convert.get(Row)[Cell])) {
                            XSSFCellStyle style1 = workbook.createCellStyle();
                            style1 = (XSSFCellStyle) cell.getCellStyle();
                            style1 = (XSSFCellStyle) style1.clone();
                            style1.setFillBackgroundColor(HSSFColor.RED.index);
                            style1.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
                            XSSFColor myColor = new XSSFColor(Color.RED);
                            style1.setFillForegroundColor(myColor);
                            sheet.getRow(9 + Row).getCell(6).setCellStyle(style1);
                        }
                    } else {
                        cell.setCellValue(convert.get(Row)[Cell]);
                    }
                }
            }
        }

        workbook.removeSheetAt(0);
        tamplateFile.close();
        File exportFile = newPath.getSelectedFile();
        if (FilenameUtils.getExtension(exportFile.getName()).equalsIgnoreCase("xlsx")) {

        } else {
            exportFile = new File(exportFile.getParentFile(),
                    FilenameUtils.getBaseName(exportFile.getName()) + ".xlsx");
        }

        FileOutputStream outFile = new FileOutputStream(exportFile);
        workbook.write(outFile);
        outFile.close();
        tamplateFile.close();
        return true;

    }

    public boolean convertXls2()
            throws IOException, FileNotFoundException, IllegalArgumentException, ParseException {
        FileInputStream tamplateFile = new FileInputStream(templatePath);
        XSSFWorkbook workbook = new XSSFWorkbook(tamplateFile);

        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setDataFormat(workbook.getCreationHelper().createDataFormat().getFormat("#,##"));
        double hours = 0.0;
        NumberFormat format = NumberFormat.getInstance(Locale.FRANCE);
        Number number;
        XSSFSheet sheet;
        XSSFSheet sheet2;
        Cell cell = null;
        ConvertData cd = new ConvertData();
        for (int i = 0; i < cd.getSheetnames().size(); i++) {
            sheet2 = workbook.cloneSheet(0, cd.sheetnames.get(i));
            sheet = workbook.getSheetAt(i + 1);
            cell = sheet.getRow(0).getCell(1);
            cell.setCellValue(cd.sheetnames.get(i));
            ArrayList<String[]> convert = cd.convert(cd.sheetnames.get(i));
            for (int Row = 0; Row < convert.size(); Row++) {
                for (int Cell = 0; Cell < convert.get(Row).length; Cell++) {
                    cell = sheet.getRow(9 + Row).getCell(Cell + 1);
                    String name;
                    switch (Cell) {
                    case 3:
                        name = convert.get(Row)[Cell];
                        int parseInt = Integer.parseInt(name);
                        cell.setCellValue(parseInt);
                        cell.setCellType(CellType.NUMERIC);
                        break;
                    case 4:
                        number = format.parse(convert.get(Row)[Cell]);
                        cell.setCellValue(number.doubleValue());
                        //  cell.setCellStyle(cellStyle);
                        cell.setCellType(CellType.NUMERIC);

                        break;
                    default:
                        cell.setCellValue(convert.get(Row)[Cell]);
                        break;
                    }
                }
            }

            for (String[] sa : convert) {
                number = format.parse(sa[4]);
                hours = hours + number.doubleValue();
            }
            cell = sheet.getRow(6).getCell(5);
            cell.setCellValue(hours);
            cell = sheet.getRow(2).getCell(8);
            XSSFCell cellHourlyRate = sheet.getRow(1).getCell(8);
            double numericCellValue = cellHourlyRate.getNumericCellValue();
            cell.setCellValue(hours * numericCellValue);
        }
        workbook.removeSheetAt(0);
        tamplateFile.close();
        File exportFile = newPath.getSelectedFile();
        if (FilenameUtils.getExtension(exportFile.getName()).equalsIgnoreCase("xlsx")) {

        } else {
            exportFile = new File(exportFile.getParentFile(),
                    FilenameUtils.getBaseName(exportFile.getName()) + ".xlsx");
        }

        FileOutputStream outFile = new FileOutputStream(exportFile);
        workbook.write(outFile);
        outFile.close();
        tamplateFile.close();
        return true;

    }
}