org.hsh.bfr.db/src/org/hsh/bfr/db/exports/ExcelExport.java
/*******************************************************************************
* Copyright (c) 2015 Federal Institute for Risk Assessment (BfR), Germany
*
* This program is free software: you can redistribute it and/or modify
* it under the terms of the GNU General Public License as published by
* the Free Software Foundation, either version 3 of the License, or
* (at your option) any later version.
*
* This program is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
* GNU General Public License for more details.
*
* You should have received a copy of the GNU General Public License
* along with this program. If not, see <http://www.gnu.org/licenses/>.
*
* Contributors:
* Department Biological Safety - BfR
*******************************************************************************/
package org.hsh.bfr.db.exports;
import java.io.File;
import java.io.FileOutputStream;
import java.sql.ResultSet;
import java.util.Hashtable;
import javax.swing.JOptionPane;
import javax.swing.JProgressBar;
import javax.swing.filechooser.FileFilter;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.hsh.bfr.db.DBKernel;
import org.hsh.bfr.db.MyLogger;
import org.hsh.bfr.db.MyTable;
import org.hsh.bfr.db.gui.dbtable.MyDBTable;
/**
* @author Armin
*
*/
public class ExcelExport extends FileFilter {
/**
This is the one of the methods that is declared in
the abstract class
*/
private Hashtable<String, Integer> kzS = new Hashtable<>();
private int colLfd = 0;
private HSSFCellStyle cs = null;
public boolean accept(File f) {
if (f.isDirectory()) return true;
String extension = getExtension(f);
if ((extension.equals("xls"))) return true;
return false;
}
public String getDescription() {
return "Excel Datei (*.xls)";
}
private String getExtension(File f) {
String s = f.getName();
int i = s.lastIndexOf('.');
if (i > 0 && i < s.length() - 1) return s.substring(i+1).toLowerCase();
return "";
}
public void doExport(final String filename, final MyDBTable myDB, final JProgressBar progress, final boolean exportFulltext, final String zeilen2Do) {
//filename = "C:/Users/Armin/Documents/private/freelance/BfR/Data/100716/Matrices_BLS-Liste.xls";
Runnable runnable = new Runnable() {
public void run() {
try {
if (progress != null) {
progress.setVisible(true);
progress.setStringPainted(true);
progress.setString("Exporting Excel Datei...");
progress.setMinimum(0);
progress.setMaximum(myDB.getRowCount());
progress.setValue(0);
}
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet(myDB.getActualTable().getTablename());
// Create Titel
cs = wb.createCellStyle();
cs.setAlignment(HSSFCellStyle.ALIGN_CENTER);
HSSFFont font = wb.createFont();
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
cs.setFont(font);
HSSFRow row0 = sheet.createRow(0);
//row0.setRowStyle(cs);
colLfd = 0;
for (int j=0;j < myDB.getColumnCount();j++) {
if (myDB.getColumn(j).isVisible()) {
HSSFCell cell = row0.createCell(colLfd); colLfd++;
cell.setCellValue(myDB.getColumn(j).getColumnName()); cell.setCellStyle(cs);
}
}
//String[] mnTable = myDB.getActualTable().getMNTable();
MyTable[] myFs = myDB.getActualTable().getForeignFields();
for (int i = 1; i <= myDB.getRowCount(); i++) {
if (progress != null) progress.setValue(i);
//System.out.println(myDB.getValueAt(i, 0) + "_" + myDB.isVisible());
HSSFRow rowi = sheet.createRow(i);
for (int j = 0; j < myDB.getColumnCount(); j++) {
Object res = null;
if (j>0 && myFs != null && myFs.length > j-1 && myFs[j-1] != null && myFs[j-1].getTablename().equals("DoubleKennzahlen")) {
//if (j > 0 && mnTable != null && j-1 < mnTable.length && mnTable[j - 1] != null && mnTable[j - 1].equals("DBL")) {
getDblVal(myDB, i-1, j, row0, rowi);
/*
getDblVal(myDB, i-1, j, "Einzelwert", row0, rowi);
getDblVal(myDB, i-1, j, "Wiederholungen", row0, rowi);
getDblVal(myDB, i-1, j, "Mittelwert", row0, rowi);
getDblVal(myDB, i-1, j, "Median", row0, rowi);
getDblVal(myDB, i-1, j, "Minimum", row0, rowi);
getDblVal(myDB, i-1, j, "Maximum", row0, rowi);
getDblVal(myDB, i-1, j, "Standardabweichung", row0, rowi);
getDblVal(myDB, i-1, j, "LCL95", row0, rowi);
getDblVal(myDB, i-1, j, "UCL95", row0, rowi);
getDblVal(myDB, i-1, j, "Verteilung", row0, rowi);
getDblVal(myDB, i-1, j, "Funktion (Zeit)", row0, rowi);
getDblVal(myDB, i-1, j, "Funktion (?)", row0, rowi);
getDblVal(myDB, i-1, j, "Undefiniert (n.d.)", row0, rowi);
*/
}
else {
if (exportFulltext) {
res = myDB.getVisibleCellContent(i-1, j);
}
else {
res = myDB.getValueAt(i-1, j);
}
//MyLogger.handleMessage(res);
if (res != null) rowi.createCell(j).setCellValue(res.toString());
else rowi.createCell(j);
}
}
}
try {
FileOutputStream fileOut = new FileOutputStream(filename);
wb.write(fileOut);
fileOut.close();
}
catch (Exception e) {
JOptionPane.showMessageDialog(progress, e.getMessage(), "Export Problem", JOptionPane.OK_OPTION);
}
if (progress != null) {
progress.setValue(myDB.getRowCount());
progress.setVisible(false);
}
}
catch (Exception e) {MyLogger.handleException(e);}
}
};
Thread thread = new Thread(runnable);
thread.start();
}
private void getDblVal(MyDBTable myDBTable, int row, int col, HSSFRow row0, HSSFRow rowi) {
Object key = myDBTable.getValueAt(row, col);
if (key != null) {
try {
ResultSet rs = DBKernel.getResultSet("SELECT * FROM " + DBKernel.delimitL("DoubleKennzahlen") +
" WHERE " + DBKernel.delimitL("ID") + "=" + key, false);
if (rs != null && rs.first()) {
String columnName = myDBTable.getActualTable().getFieldNames()[col-1];
for (int i=2;i<=rs.getMetaData().getColumnCount();i++) {
if (rs.getObject(i) != null) {
if (row0 != null) {
String kennzahl = rs.getMetaData().getColumnName(i);
String colStr = columnName + "-" + kennzahl;
int theCol;
if (kennzahl.equals("Wert")) {//if (kennzahl.equals("Einzelwert")) {
theCol = col;
}
else if (kzS.containsKey(colStr)) {
theCol = kzS.get(colStr);
}
else {
theCol = colLfd;
kzS.put(colStr, theCol);
HSSFCell cell = row0.createCell(theCol); colLfd++;
cell.setCellValue(colStr); cell.setCellStyle(cs);
}
boolean is = DBKernel.kzIsString(kennzahl);
boolean ib = DBKernel.kzIsBoolean(kennzahl);
if (is) {
rowi.createCell(theCol).setCellValue(rs.getString(i));
}
else if (ib) {
rowi.createCell(theCol).setCellValue(rs.getBoolean(i));
}
else {
rowi.createCell(theCol).setCellValue(DBKernel.getDoubleStr(rs.getObject(i)));
}
}
}
}
}
}
catch (Exception e) {
MyLogger.handleException(e);
}
}
}
}