package org.sprimaudi.zkspring.service;
import com.djbc.utilities.StringUtil;
import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.write.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.sprimaudi.zkspring.entity.UserActivity;
import org.sprimaudi.zkspring.entity.UserUser;
import org.springframework.stereotype.Service;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.util.*;
/**
* Created with IntelliJ IDEA.
* User: lenovo
* Date: 2/15/13
* Time: 5:24 PM
* To change this template use File | Settings | File Templates.
*/
@Service("excelService")
public class ExcelService {
String[] abjadColumn = new String[]{"A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z"};
Logger logger = LoggerFactory.getLogger(ExcelService.class);
public ByteArrayOutputStream createUserActivity(String patternFilePath, List<UserActivity> acitivities) {
WorkbookSettings wb = new WorkbookSettings();
wb.setLocale(new Locale("en", "US"));
ByteArrayOutputStream boas = new ByteArrayOutputStream();
Workbook wr = null;
Map<String, UserUser> m = new HashMap<String, UserUser>();
try {
wr = Workbook.getWorkbook(new File(patternFilePath), wb);
WritableWorkbook ww = Workbook.createWorkbook(boas, wr);
WritableSheet ws = ww.getSheet(0);
int irow = 3;
Integer rowPatter = 3;
for (Iterator<UserActivity> iterator = acitivities.iterator(); iterator.hasNext(); ) {
UserActivity user = iterator.next();
//write unit;
if (user.getUser() != null && user.getUser().getUsername() != null) {
writeLabel(ws, "A", irow, rowPatter, user.getUser().getUsername());
writeLabel(ws, "B", irow, rowPatter, user.getUser().getPegawai().getNama());
writeLabel(ws, "C", irow, rowPatter, user.getUser().getPegawai().getNip());
}
writeLabel(ws, "D", irow, rowPatter, (user.getGrup()));
writeLabel(ws, "E", irow, rowPatter, StringUtil.nvl(user.getDescription()));
writeLabel(ws, "F", irow, rowPatter, StringUtil.nvl(user.getIp()));
writeLabel(ws, "G", irow, rowPatter, StringUtil.fromSecond(user.getCreated(), ""));
irow++;
}
ww.write();
ww.close();
} catch (Exception e) {
logger.error("Error menulis ke excel", e);
e.printStackTrace(); //To change body of catch statement use File | Settings | File Templates.
}
return boas;
}
private void writeLabel(WritableSheet sheet, String column, int row, Integer rowPattern, String content) {
int icolumn = getColumn(column);
WritableCell wcPatern = null;
if (rowPattern != null) {
wcPatern = sheet.getWritableCell(icolumn, rowPattern);
}
Label l = null;
if (wcPatern != null && wcPatern.getCellFormat() != null && rowPattern != null) {
l = new Label(icolumn, row, content, wcPatern.getCellFormat());
} else {
l = new Label(icolumn, row, content);
}
try {
sheet.addCell(l);
} catch (WriteException e) {
e.printStackTrace(); //To change body of catch statement use File | Settings | File Templates.
logger.error("Error write to excel", e);
throw new RuntimeException("Error write to excel");
}
}
private int getColumn(String col) {
if (col == null) {
throw new RuntimeException("Column is not recognized " + col);
}
for (int i = 0; i < abjadColumn.length; i++) {
String s = abjadColumn[i].toLowerCase();
if (s.equals(col.toLowerCase())) {
return i;
}
}
return 0;
}
}