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.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.GregorianCalendar;
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 ExcelReportFuenfJahre {
private ArrayList<Jahr> jahrList = new ArrayList<Jahr>();
private HSSFWorkbook myWorkBook = new HSSFWorkbook();
private HSSFSheet mySheet = myWorkBook.createSheet();
private HSSFRow myRow = null;
private HSSFCell myCell = null;
private HSSFCellStyle myCellStyle = null;
private HSSFFont myFont = null;
private CellRangeAddress cra = null;
private String[] header = { "Mitarbeiter ID: Name",
"Buchungsdatum",
"Gesamtgehalt",
"Steuerabzug",
"Soz. Vers. AN",
"Gehalt Netto",
"Soz. Vers. AG",
"Gesamtkosten"};
public ExcelReportFuenfJahre(ResultSet rs, String fileName, Calendar startCal, Calendar endCal) throws SQLException {
String[] monatName = { "Januar",
"Februar",
"M�rz",
"April",
"Mai",
"Juni",
"Juli",
"August",
"September",
"Oktober",
"November",
"Dezember"};
for(int i = 0; i <= endCal.get(Calendar.YEAR) - startCal.get(Calendar.YEAR); i++){
jahrList.add(new Jahr(startCal.get(Calendar.YEAR)+i));
for(int j = 0; j < 12; j++){
jahrList.get(i).add(new Monat(monatName[j]));
}
}
int lastMonth = -1;
int lastJahr = -1;
SimpleDateFormat df = new SimpleDateFormat("MMMM yyyy");
Date startDat = new Date(startCal.getTimeInMillis());
Date endDat = new Date(endCal.getTimeInMillis());
mySheet.setDefaultColumnWidth(15);
mySheet.setColumnWidth(0, 7000);
myWorkBook.setSheetName(0, "F�nfjahresreport");
cra = new CellRangeAddress(0, 0, 0, 7);
mySheet.addMergedRegion( cra );
myRow = mySheet.createRow(0);
myCell = myRow.createCell(0);
myCell.setCellValue("�bersicht aller Abrechnungen zwichen "+df.format(startDat)+" und "+df.format(endDat));
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);
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);
Calendar buchungCal = new GregorianCalendar();
while(rs.next()){
buchungCal.setTimeInMillis(rs.getDate("BuchungsDat").getTime());
lastJahr = buchungCal.get(Calendar.YEAR) - startCal.get(Calendar.YEAR);
if(buchungCal.get(Calendar.MONTH) != lastMonth){
lastMonth = buchungCal.get(Calendar.MONTH);
jahrList .get(lastJahr)
.get(lastMonth)
.getMitarbList()
.add(new Mitarbeiter( rs.getInt("Personen.PersID"),
rs.getString("Personen.Nachname"),
rs.getString("Personen.Vorname")));
int size = jahrList .get(lastJahr)
.get(lastMonth)
.getMitarbList()
.size()-1;
jahrList .get(lastJahr)
.get(lastMonth)
.getMitarbList()
.get(size)
.getGehaltList()
.add(new Gehalt( rs.getDate("BuchungsDat"),
rs.getDouble("gehaltausgezahlt.GehBrutto")+
rs.getDouble("gehaltausgezahlt.sonstBezug")-
rs.getDouble("gehaltausgezahlt.sonstAbzug"),
rs.getDouble("gehaltausgezahlt.LSt")+
rs.getDouble("gehaltausgezahlt.KiSt")+
rs.getDouble("gehaltausgezahlt.Soli"),
rs.getDouble("gehaltausgezahlt.RVAN")+
rs.getDouble("gehaltausgezahlt.KVAN")+
rs.getDouble("gehaltausgezahlt.ALVAN")+
rs.getDouble("gehaltausgezahlt.PVAN"),
rs.getDouble("gehaltausgezahlt.GehNetto"),
rs.getDouble("gehaltausgezahlt.RVAG")+
rs.getDouble("gehaltausgezahlt.KVAG")+
rs.getDouble("gehaltausgezahlt.ALVAG")+
rs.getDouble("gehaltausgezahlt.PVAG")));
}else{
int lastMitarb = jahrList.get(lastJahr).get(lastMonth).getMitarbList().size()-1;
if(rs.getInt("Personen.PersID") != jahrList.get(lastJahr).get(lastMonth).getMitarbList().get(lastMitarb).id){
jahrList.get(lastJahr) .get(lastMonth)
.getMitarbList()
.add(new Mitarbeiter( rs.getInt("Personen.PersID"),
rs.getString("Personen.Nachname"),
rs.getString("Personen.Vorname")));
lastMitarb++;
jahrList.get(lastJahr)
.get(lastMonth)
.getMitarbList()
.get(lastMitarb)
.getGehaltList()
.add(new Gehalt( rs.getDate("BuchungsDat"),
rs.getDouble("gehaltausgezahlt.GehBrutto")+
rs.getDouble("gehaltausgezahlt.sonstBezug")-
rs.getDouble("gehaltausgezahlt.sonstAbzug"),
rs.getDouble("gehaltausgezahlt.LSt")+
rs.getDouble("gehaltausgezahlt.KiSt")+
rs.getDouble("gehaltausgezahlt.Soli"),
rs.getDouble("gehaltausgezahlt.RVAN")+
rs.getDouble("gehaltausgezahlt.KVAN")+
rs.getDouble("gehaltausgezahlt.ALVAN")+
rs.getDouble("gehaltausgezahlt.PVAN"),
rs.getDouble("gehaltausgezahlt.GehNetto"),
rs.getDouble("gehaltausgezahlt.RVAG")+
rs.getDouble("gehaltausgezahlt.KVAG")+
rs.getDouble("gehaltausgezahlt.ALVAG")+
rs.getDouble("gehaltausgezahlt.PVAG")));
}else{
jahrList.get(lastJahr)
.get(lastMonth)
.getMitarbList()
.get(lastMitarb)
.getGehaltList()
.add(new Gehalt( rs.getDate("BuchungsDat"),
rs.getDouble("gehaltausgezahlt.GehBrutto")+
rs.getDouble("gehaltausgezahlt.sonstBezug")-
rs.getDouble("gehaltausgezahlt.sonstAbzug"),
rs.getDouble("gehaltausgezahlt.LSt")+
rs.getDouble("gehaltausgezahlt.KiSt")+
rs.getDouble("gehaltausgezahlt.Soli"),
rs.getDouble("gehaltausgezahlt.RVAN")+
rs.getDouble("gehaltausgezahlt.KVAN")+
rs.getDouble("gehaltausgezahlt.ALVAN")+
rs.getDouble("gehaltausgezahlt.PVAN"),
rs.getDouble("gehaltausgezahlt.GehNetto"),
rs.getDouble("gehaltausgezahlt.RVAG")+
rs.getDouble("gehaltausgezahlt.KVAG")+
rs.getDouble("gehaltausgezahlt.ALVAG")+
rs.getDouble("gehaltausgezahlt.PVAG")));
}
}
}
for(int k = 0; k <= endCal.get(Calendar.YEAR) - startCal.get(Calendar.YEAR); k++){
if(k == 0){
for(int j = startCal.get(Calendar.MONTH); j < 12; j++){
writeMonth(k, j);
}
}else if(k == endCal.get(Calendar.YEAR) - startCal.get(Calendar.YEAR)){
for(int j = 0; j <= endCal.get(Calendar.MONTH); j++){
writeMonth(k, j);
}
}else{
for(int j = 0; j < 12; j++){
writeMonth(k, j);
}
}
createYearSumRows(k);
}
createGesSumRows();
try {
FileOutputStream out = new FileOutputStream(fileName);
myWorkBook.write(out);
out.close();
Desktop.getDesktop().open(new File(fileName));
} catch (Exception e) {
e.printStackTrace();
}
}
private void createMitarbSumRows(Mitarbeiter mitarb){
HSSFCellStyle sumCellStyle = myWorkBook.createCellStyle();
sumCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
sumCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
sumCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
sumCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
HSSFCellStyle sumLeftCellStyle = myWorkBook.createCellStyle();
sumLeftCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
sumLeftCellStyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
sumLeftCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
sumLeftCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
HSSFCellStyle sumEuroCellStyle = myWorkBook.createCellStyle();
sumEuroCellStyle.setDataFormat(myWorkBook.createDataFormat().getFormat("#,##0.00 �"));
sumEuroCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
sumEuroCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
sumEuroCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
sumEuroCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
HSSFCellStyle sumRightEuroCellStyle = myWorkBook.createCellStyle();
sumRightEuroCellStyle.setDataFormat(myWorkBook.createDataFormat().getFormat("#,##0.00 �"));
sumRightEuroCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
sumRightEuroCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
sumRightEuroCellStyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
sumRightEuroCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
myRow = mySheet.createRow(mySheet.getLastRowNum()+1);
myCell = myRow.createCell(0);
myCell.setCellValue(mitarb.getIdName());
myCell.setCellStyle(sumLeftCellStyle);
myCell = myRow.createCell(1);
myCell.setCellValue("Mitarbeitersumme");
myCell.setCellStyle(sumCellStyle);
myCell = myRow.createCell(2);
myCell.setCellValue(mitarb.getMitarbGesamtgehalt());
myCell.setCellStyle(sumEuroCellStyle);
myCell = myRow.createCell(3);
myCell.setCellValue(mitarb.getMitarbSteuerabzug());
myCell.setCellStyle(sumEuroCellStyle);
myCell = myRow.createCell(4);
myCell.setCellValue(mitarb.getMitarbSozVersAN());
myCell.setCellStyle(sumEuroCellStyle);
myCell = myRow.createCell(5);
myCell.setCellValue(mitarb.getMitarbGehaltNetto());
myCell.setCellStyle(sumEuroCellStyle);
myCell = myRow.createCell(6);
myCell.setCellValue(mitarb.getMitarbSozVersAG());
myCell.setCellStyle(sumEuroCellStyle);
myCell = myRow.createCell(7);
myCell.setCellValue(mitarb.getMitarbGesamtkosten());
myCell.setCellStyle(sumRightEuroCellStyle);
}
private void createMonthSumRows(Monat month){
HSSFCellStyle emptyLeftCellStyle = myWorkBook.createCellStyle();
emptyLeftCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
emptyLeftCellStyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
HSSFCellStyle emptyRightCellStyle = myWorkBook.createCellStyle();
emptyRightCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
emptyRightCellStyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
HSSFCellStyle emptyCellStyle = myWorkBook.createCellStyle();
emptyCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
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("Monatssumme");
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(month.getMonatGesamtgehalt());
myCell.setCellStyle(gesSumEuroCellStyle);
myCell = myRow.createCell(3);
myCell.setCellValue(month.getMonatSteuerabzug());
myCell.setCellStyle(gesSumEuroCellStyle);
myCell = myRow.createCell(4);
myCell.setCellValue(month.getMonatSozVersAN());
myCell.setCellStyle(gesSumEuroCellStyle);
myCell = myRow.createCell(5);
myCell.setCellValue(month.getMonatGehaltNetto());
myCell.setCellStyle(gesSumEuroCellStyle);
myCell = myRow.createCell(6);
myCell.setCellValue(month.getMonatSozVersAG());
myCell.setCellStyle(gesSumEuroCellStyle);
myCell = myRow.createCell(7);
myCell.setCellValue(month.getMonatGesamtkosten());
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);
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);
}
private void createGesSumRows(){
double gesamtgehalt = 0;
double steuerabzug = 0;
double sozVersAN = 0;
double gehaltNetto = 0;
double sozVersAG = 0;
double gesamtkosten = 0;
cra = new CellRangeAddress(mySheet.getLastRowNum()+1, mySheet.getLastRowNum()+1, 0, 7);
mySheet.addMergedRegion( cra );
myFont = myWorkBook.createFont();
myFont.setFontHeight((short) 240);
myFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
myRow = mySheet.createRow(mySheet.getLastRowNum()+1);
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);
for(int i = 0; i < jahrList.size(); i++){
gesamtgehalt += jahrList.get(i).getJahrGesamtgehalt();
steuerabzug += jahrList.get(i).getJahrSteuerabzug();
sozVersAN += jahrList.get(i).getJahrSozVersAN();
gehaltNetto += jahrList.get(i).getJahrGehaltNetto();
sozVersAG += jahrList.get(i).getJahrSozVersAG();
gesamtkosten += jahrList.get(i).getJahrGesamtkosten();
}
myRow = mySheet.createRow(mySheet.getLastRowNum()+1);
HSSFFont myFont = myWorkBook.createFont();
myFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
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);
gesSumEuroCellStyle.setFont(myFont);
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);
myCellStyle.setFont(myFont);
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);
myCellStyle.setFont(myFont);
myCell.setCellStyle(myCellStyle);
myCell = myRow.createCell(2);
myCell.setCellValue(gesamtgehalt);
myCell.setCellStyle(gesSumEuroCellStyle);
myCell = myRow.createCell(3);
myCell.setCellValue(steuerabzug);
myCell.setCellStyle(gesSumEuroCellStyle);
myCell = myRow.createCell(4);
myCell.setCellValue(sozVersAN);
myCell.setCellStyle(gesSumEuroCellStyle);
myCell = myRow.createCell(5);
myCell.setCellValue(gehaltNetto);
myCell.setCellStyle(gesSumEuroCellStyle);
myCell = myRow.createCell(6);
myCell.setCellValue(sozVersAG);
myCell.setCellStyle(gesSumEuroCellStyle);
myCell = myRow.createCell(7);
myCell.setCellValue(gesamtkosten);
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);
myCellStyle.setFont(myFont);
myCell.setCellStyle(myCellStyle);
}
public void createYearSumRows(int k){
HSSFCellStyle emptyLeftCellStyle = myWorkBook.createCellStyle();
emptyLeftCellStyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
HSSFCellStyle emptyRightCellStyle = myWorkBook.createCellStyle();
emptyRightCellStyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
HSSFCellStyle emptyCellStyle = myWorkBook.createCellStyle();
cra = new CellRangeAddress(mySheet.getLastRowNum()+1, mySheet.getLastRowNum()+1, 0, 7);
mySheet.addMergedRegion( cra );
myFont = myWorkBook.createFont();
myFont.setFontHeight((short) 240);
myFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
myRow = mySheet.createRow(mySheet.getLastRowNum()+1);
myCell = myRow.createCell(0);
myCell.setCellValue("Summe "+jahrList.get(k).getJahr());
myCellStyle = myWorkBook.createCellStyle();
myCellStyle.setFillForegroundColor(HSSFColor.BLUE_GREY.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.BLUE_GREY.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.BLUE_GREY.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);
HSSFFont myFont = myWorkBook.createFont();
myFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
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);
gesSumEuroCellStyle.setFont(myFont);
myCell = myRow.createCell(0);
myCell.setCellValue("Jahressumme "+jahrList.get(k).getJahr());
myCellStyle = myWorkBook.createCellStyle();
myCellStyle.setBorderBottom(HSSFCellStyle.BORDER_DOUBLE);
myCellStyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
myCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
myCellStyle.setBorderTop(HSSFCellStyle.BORDER_THICK);
myCellStyle.setFont(myFont);
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);
myCellStyle.setFont(myFont);
myCell.setCellStyle(myCellStyle);
myCell = myRow.createCell(2);
myCell.setCellValue(jahrList.get(k).getJahrGesamtgehalt());
myCell.setCellStyle(gesSumEuroCellStyle);
myCell = myRow.createCell(3);
myCell.setCellValue(jahrList.get(k).getJahrSteuerabzug());
myCell.setCellStyle(gesSumEuroCellStyle);
myCell = myRow.createCell(4);
myCell.setCellValue(jahrList.get(k).getJahrSozVersAN());
myCell.setCellStyle(gesSumEuroCellStyle);
myCell = myRow.createCell(5);
myCell.setCellValue(jahrList.get(k).getJahrGehaltNetto());
myCell.setCellStyle(gesSumEuroCellStyle);
myCell = myRow.createCell(6);
myCell.setCellValue(jahrList.get(k).getJahrSozVersAG());
myCell.setCellStyle(gesSumEuroCellStyle);
myCell = myRow.createCell(7);
myCell.setCellValue(jahrList.get(k).getJahrGesamtkosten());
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);
myCellStyle.setFont(myFont);
myCell.setCellStyle(myCellStyle);
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);
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);
}
public void writeMonth(int k, int j){
cra = new CellRangeAddress(mySheet.getLastRowNum()+1, mySheet.getLastRowNum()+1, 0, 7);
mySheet.addMergedRegion( cra );
myFont = myWorkBook.createFont();
myFont.setFontHeight((short) 240);
myFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
myRow = mySheet.createRow(mySheet.getLastRowNum()+1);
myCell = myRow.createCell(0);
myCell.setCellValue(jahrList.get(k).get(j).getName()+" "+jahrList.get(k).getJahr());
myCellStyle = myWorkBook.createCellStyle();
myCellStyle.setFillForegroundColor(HSSFColor.GREY_40_PERCENT.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.GREY_40_PERCENT.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.GREY_40_PERCENT.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);
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_25_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);
}
for(int i = 0; i < jahrList.get(k).get(j).getMitarbList().size(); i++){
Mitarbeiter mitarb = jahrList.get(k).get(j).getMitarbList().get(i);
createMitarbSumRows(mitarb);
}
createMonthSumRows(jahrList.get(k).get(j));
}
}