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 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 ExcelReportQuartal {
private ArrayList<Monat> monatList = new ArrayList<Monat>();
private HSSFWorkbook myWorkBook = new HSSFWorkbook();
private HSSFSheet mySheet = myWorkBook.createSheet();
private HSSFRow myRow = null;
private HSSFCell myCell = null;
private HSSFCellStyle myCellStyle = null;
HSSFFont myFont = null;
CellRangeAddress cra = null;
@SuppressWarnings("deprecation")
public ExcelReportQuartal(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 < 12; i++){
monatList.add(new Monat(monatName[i]));
}
int lastMonth = -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, "Quartalsreport");
String[] header = { "Mitarbeiter ID: Name",
"Buchungsdatum",
"Gesamtgehalt",
"Steuerabzug",
"Soz. Vers. AN",
"Gehalt Netto",
"Soz. Vers. AG",
"Gesamtkosten"};
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);
while(rs.next()){
if(rs.getDate("BuchungsDat").getMonth() != lastMonth){
lastMonth = rs.getDate("BuchungsDat").getMonth();
monatList .get(lastMonth)
.getMitarbList()
.add(new Mitarbeiter( rs.getInt("Personen.PersID"),
rs.getString("Personen.Nachname"),
rs.getString("Personen.Vorname")));
int size = monatList .get(lastMonth)
.getMitarbList()
.size()-1;
monatList .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 = monatList.get(lastMonth).getMitarbList().size()-1;
if(rs.getInt("Personen.PersID") != monatList.get(lastMonth).getMitarbList().get(lastMitarb).id){
monatList .get(lastMonth)
.getMitarbList()
.add(new Mitarbeiter( rs.getInt("Personen.PersID"),
rs.getString("Personen.Nachname"),
rs.getString("Personen.Vorname")));
lastMitarb++;
monatList .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{
monatList .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")));
}
}
}
int monat = startCal.get(Calendar.MONTH);
int jahr = startCal.get(Calendar.YEAR);
for(int j = 0; j < 3; 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(monatList.get(monat).getName()+" "+jahr);
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 < monatList.get(monat).getMitarbList().size(); i++){
Mitarbeiter mitarb = monatList.get(monat).getMitarbList().get(i);
for(int k = 0; k < mitarb.getGehaltList().size(); k++){
myRow = mySheet.createRow(mySheet.getLastRowNum()+1);
if(k == 0){
myCell = myRow.createCell(0);
myCell.setCellValue(mitarb.getIdName());
myCellStyle = myWorkBook.createCellStyle();
myCellStyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
myCell.setCellStyle(myCellStyle);
}else{
myCell = myRow.createCell(0);
myCellStyle = myWorkBook.createCellStyle();
myCellStyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
myCell.setCellStyle(myCellStyle);
}
myCell = myRow.createCell(1);
myCell.setCellValue(mitarb.getGehaltList().get(k).buchungsDat);
myCell.setCellStyle(dateCellStyle);
myCell = myRow.createCell(2);
myCell.setCellValue(mitarb.getGehaltList().get(k).gesamtgehalt);
myCell.setCellStyle(euroCellStyle);
myCell = myRow.createCell(3);
myCell.setCellValue(mitarb.getGehaltList().get(k).steuerabzug);
myCell.setCellStyle(euroCellStyle);
myCell = myRow.createCell(4);
myCell.setCellValue(mitarb.getGehaltList().get(k).sozVersAN);
myCell.setCellStyle(euroCellStyle);
myCell = myRow.createCell(5);
myCell.setCellValue(mitarb.getGehaltList().get(k).gehaltNetto);
myCell.setCellStyle(euroCellStyle);
myCell = myRow.createCell(6);
myCell.setCellValue(mitarb.getGehaltList().get(k).sozVersAG);
myCell.setCellStyle(euroCellStyle);
myCell = myRow.createCell(7);
myCell.setCellValue(mitarb.getGehaltList().get(k).gesamtkosten);
myCell.setCellStyle(euroRightCellStyle);
}
createMitarbSumRows(mitarb);
}
createMonthSumRows(monatList.get(monat));
if(monat != 11){
monat++;
}else{
monat = 0;
jahr++;
}
}
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 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);
myRow = mySheet.createRow(mySheet.getLastRowNum()+1);
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(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);
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 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(){
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);
double gesamtgehalt = 0;
double steuerabzug = 0;
double sozVersAN = 0;
double gehaltNetto = 0;
double sozVersAG = 0;
double gesamtkosten = 0;
for(int i = 0; i < monatList.size(); i++){
gesamtgehalt += monatList.get(i).getMonatGesamtgehalt();
steuerabzug += monatList.get(i).getMonatSteuerabzug();
sozVersAN += monatList.get(i).getMonatSozVersAN();
gehaltNetto += monatList.get(i).getMonatGehaltNetto();
sozVersAG += monatList.get(i).getMonatSozVersAG();
gesamtkosten += monatList.get(i).getMonatGesamtkosten();
}
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);
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);
}
}