package AlMaGe.export;
import java.awt.Desktop;
import java.io.File;
import java.io.FileOutputStream;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
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.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.util.CellRangeAddress;
public class ExcelReportMonat {
private double mitarbGesamtgehalt = 0;
private double mitarbSteuerabzug = 0;
private double mitarbSozVersAN = 0;
private double mitarbGehaltNetto = 0;
private double mitarbSozVersAG = 0;
private double mitarbGesamtkosten = 0;
private HSSFWorkbook myWorkBook = new HSSFWorkbook();
private HSSFSheet mySheet = myWorkBook.createSheet();
private HSSFRow myRow = null;
private HSSFCell myCell = null;
private HSSFCellStyle myCellStyle = null;
public ExcelReportMonat(ResultSet rs, String fileName, Calendar startCal) throws SQLException {
int persID = 0;
double gesGesamtgehalt = 0;
double gesSteuerabzug = 0;
double gesSozVersAN = 0;
double gesGehaltNetto = 0;
double gesSozVersAG = 0;
double gesGesamtkosten = 0;
boolean firstEntry = true;
SimpleDateFormat df = new SimpleDateFormat("MMMM yyyy");
Date startDat = new Date(startCal.getTimeInMillis());
mySheet.setDefaultColumnWidth(15);
mySheet.setColumnWidth(0, 7000);
myWorkBook.setSheetName(0, "Monatsreport");
HSSFFont myFont;
String[] header = { "Mitarbeiter ID: Name",
"Buchungsdatum",
"Gesamtgehalt",
"Steuerabzug",
"Soz. Vers. AN",
"Gehalt Netto",
"Soz. Vers. AG",
"Gesamtkosten"};
CellRangeAddress cra = new CellRangeAddress(0, 0, 0, 7);
mySheet.addMergedRegion( cra );
myRow = mySheet.createRow(0);
myCell = myRow.createCell(0);
myCell.setCellValue("�bersicht aller Abrechnungen im Monat "+df.format(startDat));
myFont = myWorkBook.createFont();
myFont.setFontHeight((short) 350);
myFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
myCellStyle = myWorkBook.createCellStyle();
myCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THICK);
myCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THICK);
myCellStyle.setBorderTop(HSSFCellStyle.BORDER_THICK);
myCellStyle.setFillForegroundColor(HSSFColor.GREY_80_PERCENT.index);
myCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
myCellStyle.setFont(myFont);
myCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
myCell.setCellStyle(myCellStyle);
for(int i = 1; i < 7; i++){
myCell = myRow.createCell(i);
myCellStyle = myWorkBook.createCellStyle();
myCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THICK);
myCellStyle.setBorderTop(HSSFCellStyle.BORDER_THICK);
myCell.setCellStyle(myCellStyle);
}
myCell = myRow.createCell(7);
myCellStyle = myWorkBook.createCellStyle();
myCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THICK);
myCellStyle.setBorderRight(HSSFCellStyle.BORDER_THICK);
myCellStyle.setBorderTop(HSSFCellStyle.BORDER_THICK);
myCell.setCellStyle(myCellStyle);
myRow = mySheet.createRow(mySheet.getLastRowNum()+1);
myFont = myWorkBook.createFont();
myFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
myCellStyle = myWorkBook.createCellStyle();
myCellStyle.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
myCellStyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
myCellStyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
myCellStyle.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
myCellStyle.setFillForegroundColor(HSSFColor.GREY_40_PERCENT.index);
myCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
myCellStyle.setFont(myFont);
for(int i = 0; i < header.length; i++){
myCell = myRow.createCell(i);
myCell.setCellValue(header[i]);
myCell.setCellStyle(myCellStyle);
}
HSSFCellStyle dateCellStyle = myWorkBook.createCellStyle();
dateCellStyle.setDataFormat(HSSFDataFormat
.getBuiltinFormat("m/d/yy"));
dateCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
dateCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
HSSFCellStyle euroCellStyle = myWorkBook.createCellStyle();
euroCellStyle.setDataFormat(myWorkBook.createDataFormat().getFormat("#,##0.00 �"));
euroCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
euroCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
HSSFCellStyle euroRightCellStyle = myWorkBook.createCellStyle();
euroRightCellStyle.setDataFormat(myWorkBook.createDataFormat().getFormat("#,##0.00 �"));
euroRightCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
euroRightCellStyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
while(rs.next()){
myRow = mySheet.createRow(mySheet.getLastRowNum()+1);
if(rs.getInt("PersID") != persID){
if(!firstEntry){
createSumRows();
}
myCell = myRow.createCell(0);
myCell.setCellValue(rs.getString("PersID")+": "+rs.getString("Nachname")+", "+rs.getString("Vorname"));
myCellStyle = myWorkBook.createCellStyle();
myCellStyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
myCell.setCellStyle(myCellStyle);
myCell = myRow.createCell(1);
myCell.setCellValue(rs.getDate("BuchungsDat"));
myCell.setCellStyle(dateCellStyle);
myCell = myRow.createCell(2);
myCell.setCellValue(rs.getDouble("gehaltausgezahlt.GehBrutto")+
rs.getDouble("gehaltausgezahlt.sonstBezug")-
rs.getDouble("gehaltausgezahlt.sonstAbzug"));
myCell.setCellStyle(euroCellStyle);
gesGesamtgehalt += myCell.getNumericCellValue();
mitarbGesamtgehalt += myCell.getNumericCellValue();
myCell = myRow.createCell(3);
myCell.setCellValue(rs.getDouble("gehaltausgezahlt.LSt")+
rs.getDouble("gehaltausgezahlt.KiSt")+
rs.getDouble("gehaltausgezahlt.Soli"));
myCell.setCellStyle(euroCellStyle);
gesSteuerabzug += myCell.getNumericCellValue();
mitarbSteuerabzug += myCell.getNumericCellValue();
myCell = myRow.createCell(4);
myCell.setCellValue(rs.getDouble("gehaltausgezahlt.RVAN")+
rs.getDouble("gehaltausgezahlt.KVAN")+
rs.getDouble("gehaltausgezahlt.ALVAN")+
rs.getDouble("gehaltausgezahlt.PVAN"));
myCell.setCellStyle(euroCellStyle);
gesSozVersAN += myCell.getNumericCellValue();
mitarbSozVersAN += myCell.getNumericCellValue();
myCell = myRow.createCell(5);
myCell.setCellValue(rs.getDouble("gehaltausgezahlt.GehNetto"));
myCell.setCellStyle(euroCellStyle);
gesGehaltNetto += myCell.getNumericCellValue();
mitarbGehaltNetto += myCell.getNumericCellValue();
myCell = myRow.createCell(6);
myCell.setCellValue(rs.getDouble("gehaltausgezahlt.RVAG")+
rs.getDouble("gehaltausgezahlt.KVAG")+
rs.getDouble("gehaltausgezahlt.ALVAG")+
rs.getDouble("gehaltausgezahlt.PVAG"));
myCell.setCellStyle(euroCellStyle);
gesSozVersAG += myCell.getNumericCellValue();
mitarbSozVersAG += myCell.getNumericCellValue();
myCell = myRow.createCell(7);
myCell.setCellValue(rs.getDouble("gehaltausgezahlt.GehBrutto")+
rs.getDouble("gehaltausgezahlt.sonstBezug")-
rs.getDouble("gehaltausgezahlt.sonstAbzug")+
rs.getDouble("gehaltausgezahlt.RVAG")+
rs.getDouble("gehaltausgezahlt.KVAG")+
rs.getDouble("gehaltausgezahlt.ALVAG")+
rs.getDouble("gehaltausgezahlt.PVAG"));
myCell.setCellStyle(euroRightCellStyle);
gesGesamtkosten += myCell.getNumericCellValue();
mitarbGesamtkosten += myCell.getNumericCellValue();
firstEntry = false;
persID = rs.getInt("PersID");
}else{
myCell = myRow.createCell(0);
myCellStyle = myWorkBook.createCellStyle();
myCellStyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
myCell.setCellStyle(myCellStyle);
myCell = myRow.createCell(1);
myCell.setCellValue(rs.getDate("BuchungsDat"));
myCell.setCellStyle(dateCellStyle);
myCell = myRow.createCell(2);
myCell.setCellValue(rs.getDouble("gehaltausgezahlt.GehBrutto")+
rs.getDouble("gehaltausgezahlt.sonstBezug")-
rs.getDouble("gehaltausgezahlt.sonstAbzug"));
myCell.setCellStyle(euroCellStyle);
gesGesamtgehalt += myCell.getNumericCellValue();
mitarbGesamtgehalt += myCell.getNumericCellValue();
myCell = myRow.createCell(3);
myCell.setCellValue(rs.getDouble("gehaltausgezahlt.LSt")+
rs.getDouble("gehaltausgezahlt.KiSt")+
rs.getDouble("gehaltausgezahlt.Soli"));
myCell.setCellStyle(euroCellStyle);
gesSteuerabzug += myCell.getNumericCellValue();
mitarbSteuerabzug += myCell.getNumericCellValue();
myCell = myRow.createCell(4);
myCell.setCellValue(rs.getDouble("gehaltausgezahlt.RVAN")+
rs.getDouble("gehaltausgezahlt.KVAN")+
rs.getDouble("gehaltausgezahlt.ALVAN")+
rs.getDouble("gehaltausgezahlt.PVAN"));
myCell.setCellStyle(euroCellStyle);
gesSozVersAN += myCell.getNumericCellValue();
mitarbSozVersAN += myCell.getNumericCellValue();
myCell = myRow.createCell(5);
myCell.setCellValue(rs.getDouble("gehaltausgezahlt.GehNetto"));
myCell.setCellStyle(euroCellStyle);
gesGehaltNetto += myCell.getNumericCellValue();
mitarbGehaltNetto += myCell.getNumericCellValue();
myCell = myRow.createCell(6);
myCell.setCellValue(rs.getDouble("gehaltausgezahlt.RVAG")+
rs.getDouble("gehaltausgezahlt.KVAG")+
rs.getDouble("gehaltausgezahlt.ALVAG")+
rs.getDouble("gehaltausgezahlt.PVAG"));
myCell.setCellStyle(euroCellStyle);
gesSozVersAG += myCell.getNumericCellValue();
mitarbSozVersAG += myCell.getNumericCellValue();
myCell = myRow.createCell(7);
myCell.setCellValue(rs.getDouble("gehaltausgezahlt.GehBrutto")+
rs.getDouble("gehaltausgezahlt.sonstBezug")-
rs.getDouble("gehaltausgezahlt.sonstAbzug")+
rs.getDouble("gehaltausgezahlt.RVAG")+
rs.getDouble("gehaltausgezahlt.KVAG")+
rs.getDouble("gehaltausgezahlt.ALVAG")+
rs.getDouble("gehaltausgezahlt.PVAG"));
myCell.setCellStyle(euroRightCellStyle);
gesGesamtkosten += myCell.getNumericCellValue();
mitarbGesamtkosten += myCell.getNumericCellValue();
}
}
createSumRows();
cra = new CellRangeAddress(mySheet.getLastRowNum(), mySheet.getLastRowNum(), 0, 7);
mySheet.addMergedRegion( cra );
myFont = myWorkBook.createFont();
myFont.setFontHeight((short) 240);
myFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
myCell = myRow.createCell(0);
myCell.setCellValue("Gesamtsumme");
myCellStyle = myWorkBook.createCellStyle();
myCellStyle.setFillForegroundColor(HSSFColor.TEAL.index);
myCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
myCellStyle.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
myCellStyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
myCellStyle.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
myCellStyle.setFont(myFont);
myCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
myCell.setCellStyle(myCellStyle);
for(int i = 1; i < 7; i++){
myCell = myRow.createCell(i);
myCellStyle = myWorkBook.createCellStyle();
myCellStyle.setFillForegroundColor(HSSFColor.TEAL.index);
myCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
myCellStyle.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
myCellStyle.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
myCell.setCellStyle(myCellStyle);
}
myCell = myRow.createCell(7);
myCellStyle = myWorkBook.createCellStyle();
myCellStyle.setFillForegroundColor(HSSFColor.TEAL.index);
myCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
myCellStyle.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
myCellStyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
myCellStyle.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
myCell.setCellStyle(myCellStyle);
myRow = mySheet.createRow(mySheet.getLastRowNum()+1);
HSSFCellStyle gesSumEuroCellStyle = myWorkBook.createCellStyle();
gesSumEuroCellStyle.setDataFormat(myWorkBook.createDataFormat().getFormat("#,##0.00 �"));
gesSumEuroCellStyle.setBorderBottom(HSSFCellStyle.BORDER_DOUBLE);
gesSumEuroCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
gesSumEuroCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
gesSumEuroCellStyle.setBorderTop(HSSFCellStyle.BORDER_THICK);
myCell = myRow.createCell(0);
myCell.setCellValue("Gesamtsumme");
myCellStyle = myWorkBook.createCellStyle();
myCellStyle.setBorderBottom(HSSFCellStyle.BORDER_DOUBLE);
myCellStyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
myCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
myCellStyle.setBorderTop(HSSFCellStyle.BORDER_THICK);
myCell.setCellStyle(myCellStyle);
myCell = myRow.createCell(1);
myCell.setCellValue("");
myCellStyle = myWorkBook.createCellStyle();
myCellStyle.setBorderBottom(HSSFCellStyle.BORDER_DOUBLE);
myCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
myCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
myCellStyle.setBorderTop(HSSFCellStyle.BORDER_THICK);
myCell.setCellStyle(myCellStyle);
myCell = myRow.createCell(2);
myCell.setCellValue(gesGesamtgehalt);
myCell.setCellStyle(gesSumEuroCellStyle);
myCell = myRow.createCell(3);
myCell.setCellValue(gesSteuerabzug);
myCell.setCellStyle(gesSumEuroCellStyle);
myCell = myRow.createCell(4);
myCell.setCellValue(gesSozVersAN);
myCell.setCellStyle(gesSumEuroCellStyle);
myCell = myRow.createCell(5);
myCell.setCellValue(gesGehaltNetto);
myCell.setCellStyle(gesSumEuroCellStyle);
myCell = myRow.createCell(6);
myCell.setCellValue(gesSozVersAG);
myCell.setCellStyle(gesSumEuroCellStyle);
myCell = myRow.createCell(7);
myCell.setCellValue(gesGesamtkosten);
myCellStyle = myWorkBook.createCellStyle();
myCellStyle.setDataFormat(myWorkBook.createDataFormat().getFormat("#,##0.00 �"));
myCellStyle.setBorderBottom(HSSFCellStyle.BORDER_DOUBLE);
myCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
myCellStyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
myCellStyle.setBorderTop(HSSFCellStyle.BORDER_THICK);
myCell.setCellStyle(myCellStyle);
try {
FileOutputStream out = new FileOutputStream(fileName);
myWorkBook.write(out);
out.close();
Desktop.getDesktop().open(new File(fileName));
} catch (Exception e) {
e.printStackTrace();
}
}
private void createSumRows(){
HSSFCellStyle emptyLeftCellStyle = myWorkBook.createCellStyle();
emptyLeftCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
emptyLeftCellStyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
emptyLeftCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
HSSFCellStyle emptyRightCellStyle = myWorkBook.createCellStyle();
emptyRightCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
emptyRightCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
emptyRightCellStyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
HSSFCellStyle emptyCellStyle = myWorkBook.createCellStyle();
emptyCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
emptyCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
emptyCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
HSSFCellStyle sumCellStyle = myWorkBook.createCellStyle();
sumCellStyle.setBorderBottom(HSSFCellStyle.BORDER_DOUBLE);
sumCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
sumCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
sumCellStyle.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
HSSFCellStyle sumLeftCellStyle = myWorkBook.createCellStyle();
sumLeftCellStyle.setBorderBottom(HSSFCellStyle.BORDER_DOUBLE);
sumLeftCellStyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
sumLeftCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
sumLeftCellStyle.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
HSSFCellStyle sumEuroCellStyle = myWorkBook.createCellStyle();
sumEuroCellStyle.setDataFormat(myWorkBook.createDataFormat().getFormat("#,##0.00 �"));
sumEuroCellStyle.setBorderBottom(HSSFCellStyle.BORDER_DOUBLE);
sumEuroCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
sumEuroCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
sumEuroCellStyle.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
HSSFCellStyle sumRightEuroCellStyle = myWorkBook.createCellStyle();
sumRightEuroCellStyle.setDataFormat(myWorkBook.createDataFormat().getFormat("#,##0.00 �"));
sumRightEuroCellStyle.setBorderBottom(HSSFCellStyle.BORDER_DOUBLE);
sumRightEuroCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
sumRightEuroCellStyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
sumRightEuroCellStyle.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
myCell = myRow.createCell(0);
myCell.setCellValue("Mitarbeitersumme");
myCell.setCellStyle(sumLeftCellStyle);
myCell = myRow.createCell(1);
myCell.setCellValue("");
myCell.setCellStyle(sumCellStyle);
myCell = myRow.createCell(2);
myCell.setCellValue(mitarbGesamtgehalt);
myCell.setCellStyle(sumEuroCellStyle);
myCell = myRow.createCell(3);
myCell.setCellValue(mitarbSteuerabzug);
myCell.setCellStyle(sumEuroCellStyle);
myCell = myRow.createCell(4);
myCell.setCellValue(mitarbSozVersAN);
myCell.setCellStyle(sumEuroCellStyle);
myCell = myRow.createCell(5);
myCell.setCellValue(mitarbGehaltNetto);
myCell.setCellStyle(sumEuroCellStyle);
myCell = myRow.createCell(6);
myCell.setCellValue(mitarbSozVersAG);
myCell.setCellStyle(sumEuroCellStyle);
myCell = myRow.createCell(7);
myCell.setCellValue(mitarbGesamtkosten);
myCell.setCellStyle(sumRightEuroCellStyle);
mitarbGesamtgehalt = 0;
mitarbSteuerabzug = 0;
mitarbSozVersAN = 0;
mitarbGehaltNetto = 0;
mitarbSozVersAG = 0;
mitarbGesamtkosten = 0;
myRow = mySheet.createRow(mySheet.getLastRowNum()+1);
myCell = myRow.createCell(0);
myCell.setCellValue("");
myCell.setCellStyle(emptyLeftCellStyle);
for(int i = 1; i < 7; i++){
myCell = myRow.createCell(i);
myCell.setCellValue("");
myCell.setCellStyle(emptyCellStyle);
}
myCell = myRow.createCell(7);
myCell.setCellValue("");
myCell.setCellStyle(emptyRightCellStyle);
myRow = mySheet.createRow(mySheet.getLastRowNum()+1);
}
}