private void createStyles(HSSFWorkbook workBook, HSSFSheet workSheet) {
/*Font bold = workBook.createFont();
bold.setBoldweight(Font.BOLDWEIGHT_BOLD);
bold.setFontHeightInPoints((short) 10);
bold.setColor(Font.COLOR_RED);*/
HSSFFont hssfFont = workBook.createFont();
hssfFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
hssfFont.setColor(Font.COLOR_RED);
boldStyle = workBook.createCellStyle();
boldStyle.setBorderBottom(CellStyle.BORDER_THIN);
boldStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
boldStyle.setFont(hssfFont);
boldStyle.setFillBackgroundColor(HSSFColor.BLUE.index);
defaultFont = workBook.createFont();
defaultFont.setFontHeightInPoints((short) 10);
defaultFont.setFontName("Arial");
defaultFont.setColor(IndexedColors.BLACK.getIndex());
defaultFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
defaultFont.setItalic(true);
newStyle = workBook.createCellStyle();
// newStyle.setFillBackgroundColor(IndexedColors.DARK_GREEN.getIndex());
// newStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
newStyle.setAlignment(CellStyle.ALIGN_CENTER);
newStyle.setFont(defaultFont);
/* hssfCellStyle = workBook.createCellStyle();
hssfCellStyle.setBorderBottom(CellStyle.BORDER_THIN);
hssfCellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
hssfCellStyle.setFont(bold);*/
}
public HSSFCellStyle createLastCellStyle(boolean white) {
HSSFCellStyle style = workbook.createCellStyle();
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setRightBorderColor(HSSFColor.DARK_BLUE.index);
style.setLeftBorderColor(HSSFColor.DARK_BLUE.index);
style.setBottomBorderColor(HSSFColor.DARK_BLUE.index);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
if (white) {
style.setFillBackgroundColor(HSSFColor.WHITE.index);
style.setFillForegroundColor(HSSFColor.WHITE.index);
} else {
style.setFillBackgroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index);
style.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index);
}
HSSFFont font = workbook.createFont();
font.setFontName("Arial");
font.setFontHeightInPoints((short) 12);
style.setFont(font);
return style;
}
/**
* ************************************************************************* Public methods
* ************************************************************************
*/
@Override
public void open(String filepath) throws Exception {
this.filepath = filepath;
String author = ini.getValue("Excel", "Author", "Toël Hartmann");
String keywords = ini.getValue("Excel", "Keywords", "");
wb = new HSSFWorkbook();
// Set some properties
wb.createInformationProperties();
wb.getSummaryInformation().setAuthor(author);
wb.getSummaryInformation().setKeywords(keywords);
wb.getSummaryInformation().setCreateDateTime(new Date());
// Create the cell style for column titles
titleStyle = wb.createCellStyle();
HSSFFont font = wb.createFont();
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
titleStyle.setFont(font);
String sheetName = getConfigValue("Excel", "sheet", null);
if (sheetName == null || sheetName.isEmpty())
sheetName = FileUtils.getFileNameWithoutExtention(filepath);
sheet = wb.createSheet(sheetName);
if (getConfigValue("Excel", "freezeFirstRow", "true").equalsIgnoreCase("true")) {
sheet.createFreezePane(0, 1);
}
}
/**
* Fills given row with headers
*
* @param wb
* @param sheet
* @param row
*/
private void fillHeaderRow(HSSFWorkbook wb, HSSFSheet sheet, HSSFRow row) {
// create style of header font
HSSFFont font = wb.createFont();
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
font.setFontHeightInPoints((short) 11);
HSSFCellStyle style = wb.createCellStyle();
style.setFont(font);
// here we could create array of strings, I mean headers
String[] headers = {
this.iwrb.getLocalizedString("school.child", "Child"),
this.iwrb.getLocalizedString("school.personal_id", "Personal ID"),
this.iwrb.getLocalizedString("school.e-mail", "Email"),
this.iwrb.getLocalizedString("school.address", "Address"),
this.iwrb.getLocalizedString("school.zip_code", "Zip code"),
this.iwrb.getLocalizedString("school.city", "City"),
this.iwrb.getLocalizedString("school.phone", "Phone"),
this.iwrb.getLocalizedString("school.gender", "Gender"),
this.iwrb.getLocalizedString("school.last_provider", "Last provider"),
this.iwrb.getLocalizedString("school.rejection_date", "Rejection date")
};
int[] headerWidths = {30, 14, 25, 25, 10, 16, 16, 8, 30, 16};
HSSFCell cell;
for (int i = 0; i < headers.length; i++) {
cell = row.createCell((short) i);
cell.setCellValue(headers[i]);
cell.setCellStyle(style);
sheet.setColumnWidth((short) i, (short) (headerWidths[i] * 256));
}
}
/**
* Метод проверяет наличие в рабочей книге зарегистрированного фонта с указанными характеристиками
* и если он отсутствует то регистрирует его.
*
* @param wb рабочая книга в которой должен быть зарегистрирован требуемый фонт.
* @param font характеристики требуемого фонта.
* @return зарегистрированный в рабочей книге фонт с требуемыми характеристиками.
*/
public static HSSFFont ensureFontExists(final HSSFWorkbook wb, final Font font) {
final short colorId = font.getColor() != null ? font.getColor().getId() : 0;
HSSFFont f =
wb.findFont(
font.getBoldWeight(),
colorId,
font.getFontHeight(),
font.getFontName(),
font.isItalic(),
font.isStrikeout(),
font.getTypeOffset(),
font.getUnderline());
if (f == null) {
f = wb.createFont();
f.setBoldweight(font.getBoldWeight());
f.setCharSet(font.getCharSet());
f.setColor(colorId);
f.setFontHeight(font.getFontHeight());
f.setFontName(font.getFontName());
f.setItalic(font.isItalic());
f.setStrikeout(font.isStrikeout());
f.setTypeOffset(font.getTypeOffset());
f.setUnderline(font.getUnderline());
}
return f;
}
private CoverageResults() {
fileName = "Coverage-results.xls";
workbook = new HSSFWorkbook();
allElementsSheet = workbook.createSheet("All Elements");
packagesSheet = workbook.createSheet("Packages");
boundClassesSheet = workbook.createSheet("Bound Classes");
allClassesSheet = workbook.createSheet("All Classes");
boundMethodsSheet = workbook.createSheet("Bound Methods");
allMethodsSheet = workbook.createSheet("All Methods");
normalFont = workbook.createFont();
normalFont.setFontName(HSSFFont.FONT_ARIAL);
normalFont.setFontHeightInPoints((short) 10);
boldFont = workbook.createFont();
boldFont.setFontName(HSSFFont.FONT_ARIAL);
boldFont.setFontHeightInPoints((short) 10);
boldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
centerAlignmentCellStyle = workbook.createCellStyle();
centerAlignmentCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
leftAlignmentCellStyle = workbook.createCellStyle();
leftAlignmentCellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
rightAlignmentCellStyle = workbook.createCellStyle();
rightAlignmentCellStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
dataFormatCellStyle = workbook.createCellStyle();
CreationHelper createHelper = workbook.getCreationHelper();
dataFormatCellStyle.setDataFormat(createHelper.createDataFormat().getFormat("dd/MM/yyyy"));
}
public void createExcelFile(String filename) throws Exception {
out = new FileOutputStream(filename);
wb = new HSSFWorkbook();
ws = this.wb.createSheet();
cs1 = this.wb.createCellStyle();
cs2 = this.wb.createCellStyle();
cs3 = this.wb.createCellStyle();
df = this.wb.createDataFormat();
f1 = this.wb.createFont();
f2 = this.wb.createFont();
f1.setFontHeightInPoints((short) 10);
f1.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
f2.setFontHeightInPoints((short) 13);
f2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
f2.setColor(HSSFFont.COLOR_RED);
cs1.setFont(f1);
cs1.setDataFormat(df.getFormat("text"));
cs2.setFont(f2);
cs2.setDataFormat(HSSFDataFormat.getBuiltinFormat("text"));
cs3.setFont(f1);
cs3.setDataFormat(df.getFormat("#,##0.0"));
wb.setSheetName(0, "QryDetail", HSSFWorkbook.ENCODING_UTF_16);
}
public void setValueAndColor(int rowIndex, int columnIndex, String value, ExcelColor color) {
HSSFFont font = createFont();
font.setColor(ExcelColor.getColorIndex(color));
HSSFCellStyle cellStyle = createCellStyle();
cellStyle.cloneStyleFrom(getCellStyle(rowIndex, columnIndex));
cellStyle.setFont(font);
setValueAndStyle(rowIndex, columnIndex, value, cellStyle);
}
public HSSFCellStyle createScriptStyle() {
HSSFCellStyle style = workbook.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
HSSFFont font = workbook.createFont();
font.setFontName("Arial");
font.setFontHeightInPoints((short) 12);
font.setColor(HSSFColor.BLACK.index);
style.setFont(font);
return style;
}
public HSSFCellStyle createInfoCellStyle() {
HSSFCellStyle style = workbook.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
HSSFFont font = workbook.createFont();
font.setFontName("Arial");
font.setFontHeightInPoints((short) 12);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
font.setColor(HSSFColor.BLACK.index);
style.setFont(font);
return style;
}
public Object setStyle(Object cellVal, HSSFCell cell, StyleVO styleVal) {
HSSFWorkbook workbook = cell.getRow().getSheet().getWorkbook();
if (cellVal instanceof BigDecimal) {
cellVal = ((BigDecimal) cellVal).doubleValue();
} else if (cellVal instanceof Float) {
cellVal = new Double(cellVal.toString());
}
HSSFFont font = fonts.getOrCreateFont(workbook, styleVal.fontVal);
styleVal.fontIndex = font.getIndex();
cell.setCellStyle(styles.getOrCreateStyle(workbook, styleVal));
return cellVal;
}
// for return output stream
public XLSExport() {
this.workbook = new HSSFWorkbook();
styles = createStyles(workbook);
this.sheet = workbook.createSheet();
HSSFFont font = workbook.createFont();
createSummerySheet();
font.setFontHeight((short) 10);
// this.sheet.setDisplayGridlines(true);
initStyles();
createRow(_rowcnt++);
for (int i = 0; i < keys.length; i++) {
setHeaderCell(i, lbls[i]);
}
}
public HSSFCellStyle createSubTitleCellStyle() {
HSSFCellStyle style = workbook.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style.setFillBackgroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index);
style.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index);
HSSFFont font = workbook.createFont();
font.setFontName("Arial");
font.setFontHeightInPoints((short) 14);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
font.setColor(HSSFColor.DARK_BLUE.index);
style.setFont(font);
return style;
}
public ExcelUtils(List items, ItemSearch itemSearch) {
this.wb = new HSSFWorkbook();
this.sheet = wb.createSheet("jtrac");
this.sheet.setDefaultColumnWidth((short) 12);
HSSFFont fBold = wb.createFont();
fBold.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
this.csBold = wb.createCellStyle();
this.csBold.setFont(fBold);
this.csDate = wb.createCellStyle();
this.csDate.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy"));
this.items = items;
this.itemSearch = itemSearch;
}
public void initStyles() {
// HSSFDataFormat format = workbook.createDataFormat();
redStyle = workbook.createCellStyle();
setBorder(redStyle, 1);
redStyle.setFillForegroundColor(HSSFColor.RED.index);
yellowStyle = workbook.createCellStyle();
setBorder(yellowStyle, 1);
yellowStyle.setFillForegroundColor(HSSFColor.YELLOW.index);
blueStyle = workbook.createCellStyle();
setBorder(blueStyle, 1);
blueStyle.setFillForegroundColor(HSSFColor.BLUE_GREY.index);
whiteStyle = workbook.createCellStyle();
setBorder(whiteStyle, 1);
whiteStyle.setFillForegroundColor(HSSFColor.WHITE.index);
normalStyle = workbook.createCellStyle();
setBorder(normalStyle, 0);
// normalStyle.setFillForegroundColor(HSSFColor.WHITE.index);
normalPStyle = workbook.createCellStyle();
setBorder(normalPStyle, 0);
normalPStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00%"));
// normalPStyle.setFillForegroundColor(HSSFColor.WHITE.index);
headerStyle = workbook.createCellStyle();
setBorder(headerStyle, 0);
HSSFFont font = workbook.createFont();
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
headerStyle.setFont(font);
redPStyle = workbook.createCellStyle();
setBorder(redPStyle, 1);
redPStyle.setFillForegroundColor(HSSFColor.RED.index);
// redPStyle.setDataFormat(format.getFormat(NUMBER_FORMAT));
redPStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00%"));
yellowPStyle = workbook.createCellStyle();
setBorder(yellowPStyle, 1);
yellowPStyle.setFillForegroundColor(HSSFColor.YELLOW.index);
// yellowPStyle.setDataFormat(format.getFormat(NUMBER_FORMAT));
yellowPStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00%"));
_fonts = new HashMap<String, HSSFFont>(2);
HSSFFont t13y_red = workbook.createFont();
t13y_red.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
t13y_red.setColor(HSSFFont.COLOR_RED);
_fonts.put("t13yr", t13y_red);
HSSFFont t13y_blue = workbook.createFont();
t13y_blue.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
t13y_blue.setColor(HSSFColor.BLUE_GREY.index);
_fonts.put("t13yb", t13y_blue);
}
private static Map readFonts(NodeList fontList, HSSFWorkbook workbook)
throws ExcelTransformerException {
if (LOG.isLoggable(Level.FINE)) {
LOG.entering(
SimpleExcelRenderer.class.getName(), "readFonts", String.valueOf(fontList.getLength()));
}
Map results = new HashMap();
for (int i = 0; i < fontList.getLength(); i++) {
Element fontNode = (Element) fontList.item(i);
String name = fontNode.getAttribute("name");
HSSFFont font = workbook.createFont();
NodeList children = fontNode.getChildNodes();
for (int j = 0; j < children.getLength(); j++) {
Node child = children.item(j);
if (child.getNodeType() == Node.ELEMENT_NODE) {
Element childE = (Element) child;
String value = XmlUtils.getElementText(childE);
if (childE.getNodeName().equals("family")) {
font.setFontName(value);
} else {
try {
Field field = HSSFFont.class.getField(value);
if (childE.getNodeName().equals("boldweight")) {
font.setBoldweight(field.getShort(null));
}
} catch (Throwable th) {
throw new ExcelTransformerException(
"An error occurred while processing " + value + " on the excel font.");
}
}
}
}
results.put(name, font);
}
if (LOG.isLoggable(Level.FINE)) {
LOG.exiting(SimpleExcelRenderer.class.getName(), "readFonts");
}
return results;
}
protected HSSFCellStyle createInternalCellStyle() {
HSSFCellStyle style = workbook.createCellStyle();
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setRightBorderColor(HSSFColor.DARK_BLUE.index);
style.setLeftBorderColor(HSSFColor.DARK_BLUE.index);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style.setFillBackgroundColor(HSSFColor.WHITE.index);
style.setFillForegroundColor(HSSFColor.WHITE.index);
HSSFFont font = workbook.createFont();
font.setFontName("Arial");
font.setFontHeightInPoints((short) 12);
style.setFont(font);
return style;
}
private void initContentCellStyle() {
// 生成并设置另一个样式
style2 = workbook.createCellStyle();
style2.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
style2.setBorderTop(HSSFCellStyle.BORDER_THIN);
style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
// 生成另一个字体
HSSFFont font2 = workbook.createFont();
font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
// 把字体应用到当前的样式
style2.setFont(font2);
}
public void applyTo(HSSFFont font) {
font.setFontName(fontName);
font.setFontHeight(fontHeight);
font.setBoldweight(boldweight);
font.setItalic(italic);
font.setStrikeout(strikeout);
font.setTypeOffset(typeOffset);
font.setUnderline(underline);
font.setColor(color);
}
public FontVO(HSSFFont font) {
fontName = font.getFontName();
fontHeight = font.getFontHeight();
boldweight = font.getBoldweight();
italic = font.getItalic();
strikeout = font.getStrikeout();
typeOffset = font.getTypeOffset();
underline = font.getUnderline();
color = font.getColor();
}
public void exportToExcel(OutputStream fileOut) throws IOException {
HSSFWorkbook wb;
try {
wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("table-colums");
HSSFFont boldFont = wb.createFont();
boldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
HSSFCellStyle boldStyle = wb.createCellStyle();
boldStyle.setFont(boldFont);
HSSFRow row = sheet.createRow(0);
HSSFCell cell0 = row.createCell(0);
cell0.setCellValue("字段名称");
cell0.setCellStyle(boldStyle);
HSSFCell cell1 = row.createCell(1);
cell1.setCellValue("列名");
cell1.setCellStyle(boldStyle);
HSSFCell cell2 = row.createCell(2);
cell2.setCellValue("字段别名");
cell2.setCellStyle(boldStyle);
HSSFCell cell3 = row.createCell(3);
cell3.setCellValue("字段类型");
cell3.setCellStyle(boldStyle);
HSSFCell cell4 = row.createCell(4);
cell4.setCellValue("默认值");
cell4.setCellStyle(boldStyle);
HSSFCell cell5 = row.createCell(5);
cell5.setCellValue("最大长度");
cell5.setCellStyle(boldStyle);
HSSFCell cell6 = row.createCell(6);
cell6.setCellValue("显示顺序");
cell6.setCellStyle(boldStyle);
String value = Struts2Utils.getParameter("subTableVals");
String[] arr = value.split("=");
String jsonString = arr[1];
if (jsonString != null && StringUtils.isNotEmpty(jsonString.toString())) {
getSheetContent(jsonString, sheet);
}
wb.write(fileOut);
} catch (IOException exception) {
}
}
/**
* 创建头文件
*
* @param headNames 对象数组的描述
*/
private void createSheet(String sheetname, String[] headNames) {
try {
HSSFSheet sheet = wb.createSheet(sheetname);
HSSFRow row = sheet.createRow(0);
HSSFFont font = wb.createFont();
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setFont(font);
for (int i = 0; i < headNames.length; i++) {
HSSFCell cell = row.createCell((short) i);
cell.setCellStyle(cellStyle);
HSSFRichTextString hts = new HSSFRichTextString(headNames[i]);
cell.setCellValue(hts);
}
} catch (Exception ex) {
System.out.print(ex);
}
}
private void initHeadCellStyle() {
style = workbook.createCellStyle();
// 设置这些样式
style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// 生成一个字体
HSSFFont font = workbook.createFont();
font.setColor(HSSFColor.VIOLET.index);
font.setFontHeightInPoints((short) 12);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// 把字体应用到当前的样式
style.setFont(font);
}
private void writeSheet(
List<Map<String, String>> valueMaps, String worksheetName, HSSFWorkbook wb) {
// Set column widths
HSSFSheet sheet = wb.createSheet(worksheetName);
sheet.setColumnWidth(Short.parseShort("0"), Short.parseShort("15000"));
sheet.setColumnWidth(Short.parseShort("1"), Short.parseShort("30000"));
// header style
HSSFCellStyle headerStyle;
HSSFFont headerFont = wb.createFont();
headerFont.setFontHeightInPoints((short) 11);
headerStyle = wb.createCellStyle();
headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
headerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
headerStyle.setFillForegroundColor(HSSFColor.GREY_50_PERCENT.index);
headerStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
headerStyle.setFont(headerFont);
headerStyle.setWrapText(true);
// header row
HSSFRow headerRow = sheet.createRow(0);
headerRow.setHeightInPoints(30);
HSSFCell headerCell0 = headerRow.createCell((short) 0);
HSSFCell headerCell1 = headerRow.createCell((short) 1);
headerCell0.setCellStyle(headerStyle);
setText(headerCell0, "Layer Name");
headerCell1.setCellStyle(headerStyle);
setText(headerCell1, "Message");
int counter = 1;
for (Map<String, String> valueMap : valueMaps) {
HSSFRow dataRow = sheet.createRow(counter);
String layer = valueMap.get("layer");
String status = valueMap.get("status");
status = HtmlUtils.htmlUnescape(status);
HSSFCell currentCell0 = dataRow.createCell((short) 0);
HSSFCell currentCell1 = dataRow.createCell((short) 1);
setText(currentCell0, layer);
setText(currentCell1, status);
counter++;
}
}
public ExcelSupport() {
super();
workbook = new HSSFWorkbook();
numericCellStyle = workbook.createCellStyle();
numericData = workbook.createDataFormat();
numericCellStyle.setDataFormat(numericData.getFormat("#,##0.00"));
percentageCellStyle = workbook.createCellStyle();
percentageCellStyle.setDataFormat((short) 4);
decimalCellStyle = workbook.createCellStyle();
decimalCellStyle.setDataFormat(workbook.createDataFormat().getFormat("#,##0.0#####"));
multiLineTextStyle = workbook.createCellStyle();
multiLineTextStyle.setWrapText(true);
multiLineTextStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);
genericStyle = workbook.createCellStyle();
genericStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);
dollarStyle = workbook.createCellStyle();
dollarStyle.setDataFormat((short) 8);
headlineStyle = workbook.createCellStyle();
HSSFFont headlineFont = workbook.createFont();
// set font 1 to 12 point type
headlineFont.setFontHeightInPoints((short) 24);
// make it bold
// arial is the default font
headlineFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
headlineStyle.setFont(headlineFont);
boldFont = workbook.createFont();
boldFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
boldStyle = workbook.createCellStyle();
boldStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);
boldStyle.setFont(boldFont);
}
/**
* runs a xls file where the user insert a row within a worksheet where two fields are set: CODE,
* NAME.
*
* @param document: Document to modify the name and code field.
*/
public void postProcessXLS(Object document) {
HSSFWorkbook book = (HSSFWorkbook) document;
HSSFSheet sheet = book.getSheetAt(0); // Se toma hoja del libro
HSSFRow row;
HSSFCellStyle cellStyle = book.createCellStyle();
HSSFFont font = book.createFont();
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
cellStyle.setFont(font);
row = sheet.createRow(0); // Se crea una fila dentro de la hoja
createCell(cellStyle, row, 0, "CODIGO"); // "100">#{rowX.column1}</p:column>
createCell(
cellStyle, row, 1,
"NOMBRE"); // "100">#{rowX.column23}</p:column>
countriesList = countriesFacade.findAll();
for (int i = 0; i < countriesList.size(); i++) {
row = sheet.createRow(i + 1);
createCell(row, 0, countriesList.get(i).getIdCountry().toString()); // CODIGO
createCell(row, 1, countriesList.get(i).getName()); // NOMBRE
}
}
public void setComment(int rowIndex, int columnIndex, int rows, int cols, String text) {
HSSFCell cell = getCell(rowIndex, columnIndex);
if (cell.getCellComment() != null) {
cell.removeCellComment();
}
ClientAnchor anchor = getCreationHelper().createClientAnchor();
anchor.setCol1(columnIndex + 2);
anchor.setCol2(columnIndex + 2 + cols);
anchor.setRow1(rowIndex + 1);
anchor.setRow2(rowIndex + 1 + rows);
RichTextString str = getCreationHelper().createRichTextString(text);
HSSFFont font = createFont();
font.setFontHeightInPoints((short) 10);
str.applyFont(font);
Comment comment = getDrawing().createCellComment(anchor);
comment.setString(str);
cell.setCellComment(comment);
}
protected HSSFCellStyle createSummaryCellStyle() {
HSSFCellStyle style = workbook.createCellStyle();
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setRightBorderColor(HSSFColor.DARK_BLUE.index);
style.setLeftBorderColor(HSSFColor.DARK_BLUE.index);
style.setTopBorderColor(HSSFColor.DARK_BLUE.index);
style.setBottomBorderColor(HSSFColor.DARK_BLUE.index);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index);
style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
HSSFFont font = workbook.createFont();
font.setFontName("Courier New");
font.setFontHeightInPoints((short) 15);
font.setColor(HSSFColor.BLACK.index);
style.setFont(font);
return style;
}
/**
* Находит или создает новый стиль на основе указанного в аргументе исходного стиля в котором были
* изменены цвета шрифта и/или фона. Информация о всех созданных данным методом стилях сохраняется
* в контексте выполнения в пространстве имен {@link
* org.echosoft.framework.reports.model.el.ELContext.Scope#VAR}.
*
* @param ectx контекст выполнения. Используется для получения информации о текущей ячейке и для
* кэширования информации о вновь созданных в документе стилях.
* @param originalStyle исходный стиль, который должен послужить основой для создаваемого стиля.
* @param color Определяет цвет шрифта в данной ячейке. Аргумент содержит идентификатор цвета в
* документе или <code>-1</code> если цвет шрифта должен остаться без изменений.
* @param bgColor Определяет цвет фона в данной ячейке. Аргумент содержит идентификатор цвета в
* документе или <code>-1</code> если цвет шрифта должен остаться без изменений.
* @return копия оригинального стиля указанной ячейки с измененными цветами фона и шрифта. Этот
* стиль уже зарегистрирован в рабочей книге.
*/
public static HSSFCellStyle getAltColorStyle(
final ExecutionContext ectx,
final HSSFCellStyle originalStyle,
final int color,
final int bgColor) {
final String styleName =
"alt.color.style:" + originalStyle.getIndex() + ':' + color + ':' + bgColor;
HSSFCellStyle style = (HSSFCellStyle) ectx.elctx.getVariables().get(styleName);
if (style == null) {
style = POIUtils.copyStyle(ectx.wb, originalStyle);
if (color >= 0) {
final HSSFFont of = ectx.wb.getFontAt(originalStyle.getFontIndex());
HSSFFont nf =
ectx.wb.findFont(
of.getBoldweight(),
(short) color,
of.getFontHeight(),
of.getFontName(),
of.getItalic(),
of.getStrikeout(),
of.getTypeOffset(),
of.getUnderline());
if (nf == null) {
nf = POIUtils.copyFont(ectx.wb, of);
nf.setColor((short) color);
}
style.setFont(nf);
}
if (bgColor >= 0) {
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style.setFillForegroundColor((short) bgColor);
}
ectx.elctx.getVariables().put(styleName, style);
}
return style;
}
/** 單元格的字体 workbook excel font --字体模型 */
public static HSSFFont getHSSFFont(HSSFWorkbook workbook, Font f) {
HSSFFont font = workbook.createFont();
// 字体名称
font.setFontName(f.getFontNm());
// 字号
font.setFontHeightInPoints(f.getFontSize());
// 字体颜色
font.setColor(f.getFontColor());
// 下划线
font.setUnderline(f.getUnderline());
// 上标下标
font.setTypeOffset(f.getOffset());
// 删除线
font.setStrikeout(f.getDelLine());
// 加粗
font.setBoldweight(f.getBold());
// 斜线
font.setItalic(f.getItalic());
// 字体高度
// font.setFontHeight(arg0);
return font;
}