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;
}
private HSSFCellStyle getHeaderStyle2(
final HSSFWorkbook workbook,
final boolean shouldRed,
final boolean shouldBackground,
final boolean shouldSmall,
final boolean end,
final boolean changeover,
final Font font) {
HSSFCellStyle style = workbook.createCellStyle();
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
style.setWrapText(true);
font.setFontName(HSSFFont.FONT_ARIAL);
font.setBoldweight(Font.BOLDWEIGHT_BOLD);
font.setFontHeightInPoints((short) 9);
font.setColor(HSSFFont.COLOR_NORMAL);
style.setFont(font);
if (shouldRed) {
font.setColor(HSSFFont.COLOR_RED);
style.setFont(font);
}
if (shouldBackground) {
if (changeover) {
style.setFillForegroundColor(HSSFColor.LIGHT_BLUE.index);
} else {
style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
}
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
}
if (shouldSmall) {
font.setFontHeightInPoints((short) 7);
style.setFont(font);
}
if (end) {
style.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
}
return style;
}
public void applyTo(HSSFCellStyle style, HSSFWorkbook workbook) {
style.setAlignment(this.alignment);
style.setBorderBottom(this.borderBottom);
style.setBorderLeft(this.borderLeft);
style.setBorderRight(this.borderRight);
style.setBorderTop(this.borderTop);
style.setBottomBorderColor(this.bottomBorderColor);
style.setDataFormat(this.dataFormat);
style.setFillPattern(this.fillPattern);
style.setFillForegroundColor(this.fillForegroundColor);
style.setFillBackgroundColor(this.fillBackgroundColor);
style.setFont(workbook.getFontAt(this.fontIndex));
style.setHidden(this.hidden);
style.setIndention(this.indention);
style.setLeftBorderColor(this.leftBorderColor);
style.setLocked(this.locked);
style.setRightBorderColor(this.rightBorderColor);
style.setRotation(this.rotation);
style.setTopBorderColor(this.topBorderColor);
style.setVerticalAlignment(this.verticalAlignment);
style.setWrapText(this.wrapText);
}
/**
* 摘要： @说明：Excel样式 @创建：作者:yxy 创建时间：2011-8-17
*
* @param workBook
* @param cellStyle 样式模型
* @return @修改历史： [序号](yxy 2011-8-17)<修改说明>
*/
public static HSSFCellStyle getNewStyle(HSSFWorkbook workBook, CellStyle cellStyle) {
HSSFCellStyle style = workBook.createCellStyle();
// 对齐方式
style.setAlignment(cellStyle.getAlignment());
style.setVerticalAlignment(cellStyle.getVAlignment());
// 设置背景颜色
// 最好的设置Pattern
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
// 单元格背景的显示模式
style.setFillForegroundColor(cellStyle.getColor()); // 单元格背景的显示模式．
// 设置边框
style.setBorderBottom(cellStyle.getBorderBottom()); // 下边框
style.setBorderLeft(cellStyle.getBorderLeft()); // 左边框
style.setBorderTop(cellStyle.getBorderTop()); // 上边框
style.setBorderRight(cellStyle.getBorderRight()); // 右边框
// 设置边框颜色
style.setBottomBorderColor(cellStyle.getBottomBorderColor());
style.setTopBorderColor(cellStyle.getTopBorderColor());
style.setLeftBorderColor(cellStyle.getLeftBorderColor());
style.setRightBorderColor(cellStyle.getRightBorderColor());
// 设置自动换行
style.setWrapText(cellStyle.getWrapText());
style.setHidden(cellStyle.getHidden());
// 数据格式
style.setDataFormat(cellStyle.getDataFormate());
style.setLocked(cellStyle.getLocked());
// 文本旋转 请注意，这里的Rotation取值是从-90到90，而不是0-180度
style.setRotation(cellStyle.getRotation());
// 文本缩进
style.setIndention(cellStyle.getIndention());
return style;
}
private void initEmptyStyle() {
emptyStyle = wb.createCellStyle();
emptyStyle.setFillForegroundColor(HSSFColor.RED.index);
emptyStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
emptyStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
emptyStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
emptyStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
emptyStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
emptyStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
}
private void initNormalStyle() {
normalStyle = wb.createCellStyle();
normalStyle.setFillForegroundColor(HSSFColor.WHITE.index);
normalStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
normalStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
normalStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
normalStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
normalStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
normalStyle.setWrapText(true);
normalStyle.setFont(normalFont);
}
private void initChangedOrEmptyStyle() {
changedOrEmptyStyle = wb.createCellStyle();
changedOrEmptyStyle.setFillForegroundColor(HSSFColor.PINK.index);
changedOrEmptyStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
changedOrEmptyStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
changedOrEmptyStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
changedOrEmptyStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
changedOrEmptyStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
changedOrEmptyStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
changedOrEmptyStyle.setWrapText(true);
changedOrEmptyStyle.setFont(normalFont);
}
public void decorateRowWithBoldCellYellowBackground(int cellId, HSSFRow row, Object value) {
HSSFCellStyle boldStyleBlueBground = workbook.createCellStyle();
boldStyleBlueBground.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);
boldStyleBlueBground.setFont(boldFont);
boldStyleBlueBground.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
boldStyleBlueBground.setFillForegroundColor(new HSSFColor.LIGHT_YELLOW().getIndex());
// boldStyleBlueBground.setBorderBottom(HSSFCellStyle.BORDER_HAIR);
// boldStyleBlueBground.setBorderLeft(HSSFCellStyle.BORDER_HAIR);
// boldStyleBlueBground.setBorderRight(HSSFCellStyle.BORDER_HAIR);
// boldStyleBlueBground.setBorderTop(HSSFCellStyle.BORDER_HAIR);
this.decorateRowWithCell((short) cellId, row, value, boldStyleBlueBground);
}
private HSSFWorkbook getWorkbook() throws IOException {
if (this.workBook == null) {
// create a new workbook
this.workBook = new HSSFWorkbook();
// init default column and create the cell style
style = workBook.createCellStyle();
style.setFillForegroundColor(HSSFColor.LIGHT_ORANGE.index);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
this.rowCount = 0;
}
return workBook;
}
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);
}
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 void postProcessXLS(Object document) {
HSSFWorkbook wb = (HSSFWorkbook) document;
HSSFSheet sheet = wb.getSheetAt(0);
HSSFRow header = sheet.getRow(0);
HSSFCellStyle cellStyle = wb.createCellStyle();
cellStyle.setFillForegroundColor(HSSFColor.GREEN.index);
cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
for (int i = 0; i < header.getPhysicalNumberOfCells(); i++) {
HSSFCell cell = header.getCell(i);
cell.setCellStyle(cellStyle);
}
}
/**
* 文本框的样式
*
* @param wb
* @return
*/
private HSSFCellStyle getTextStyle(HSSFWorkbook wb) {
if (textStyle == null) {
textStyle = wb.createCellStyle();
textStyle.setFillForegroundColor(HSSFColor.WHITE.index);
textStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
textStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
textStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
textStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
textStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
textStyle.setBottomBorderColor(HSSFColor.BLACK.index);
textStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);
textStyle.setWrapText(true);
}
return textStyle;
}
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);
}
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 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++;
}
}
/**
* Método para gerar arquivo excel (xls);
*
* @author Alexandre Martins da Silva
* @param documento - objeto para gerar o arquivo excel (xls)
*/
public void posProcessarXls(Object documento) {
HSSFWorkbook planilha = (HSSFWorkbook) documento;
HSSFSheet folha = planilha.getSheetAt(0);
HSSFRow cabecalho = folha.getRow(0);
HSSFCellStyle estiloDaCelula = planilha.createCellStyle();
Font fonteDoCabecalho = planilha.createFont();
fonteDoCabecalho.setColor(IndexedColors.WHITE.getIndex());
fonteDoCabecalho.setBold(true);
fonteDoCabecalho.setFontHeightInPoints((short) 13);
estiloDaCelula.setFont(fonteDoCabecalho);
estiloDaCelula.setFillForegroundColor(IndexedColors.BLACK.getIndex());
estiloDaCelula.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
for (int i = 0; i < cabecalho.getPhysicalNumberOfCells(); i++) {
cabecalho.getCell(i).setCellStyle(estiloDaCelula);
}
}
private HSSFCellStyle getHeaderStyleChangeoverEnd(final HSSFWorkbook workbook, final Font font) {
HSSFCellStyle style = workbook.createCellStyle();
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
style.setWrapText(true);
font.setFontName(HSSFFont.FONT_ARIAL);
font.setBoldweight(Font.BOLDWEIGHT_BOLD);
font.setFontHeightInPoints((short) 9);
style.setFont(font);
style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
return style;
}
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;
}
/**
* Создает новый стиль в документе Excel который полностью копирует свойства некоторого исходного
* стиля этого же документа. Данный метод используется для того чтобы в последствии поменять в
* новом стиле один или несколько свойств не затрагивая при этом свойства исходного стиля.
*
* @param wb документ Excel.
* @param src стиль ячейки взятый в качестве шаблона.
* @return созданная копия исходного стиля в этом же документе.
*/
public static HSSFCellStyle copyStyle(final HSSFWorkbook wb, final HSSFCellStyle src) {
final HSSFCellStyle dst = wb.createCellStyle();
dst.setAlignment(src.getAlignment());
dst.setBorderBottom(src.getBorderBottom());
dst.setBorderLeft(src.getBorderLeft());
dst.setBorderRight(src.getBorderRight());
dst.setBorderTop(src.getBorderTop());
dst.setBottomBorderColor(src.getBottomBorderColor());
dst.setDataFormat(src.getDataFormat());
dst.setFillForegroundColor(src.getFillForegroundColor());
dst.setFillBackgroundColor(src.getFillBackgroundColor());
dst.setFillPattern(src.getFillPattern());
dst.setFont(src.getFont(wb));
dst.setHidden(src.getHidden());
dst.setIndention(src.getIndention());
dst.setLeftBorderColor(src.getLeftBorderColor());
dst.setLocked(src.getLocked());
dst.setRightBorderColor(src.getRightBorderColor());
dst.setRotation(src.getRotation());
dst.setTopBorderColor(src.getTopBorderColor());
dst.setVerticalAlignment(src.getVerticalAlignment());
dst.setWrapText(src.getWrapText());
return dst;
}
private void setIndexMatrixColor(HSSFWorkbook workbook, HSSFCellStyle indexStyle) {
indexStyle.setFillForegroundColor(HSSFColor.WHITE.index);
HSSFFont font = workbook.getFontAt(indexStyle.getFontIndex());
font.setColor(HSSFColor.BLACK.index);
}
public void generateExcel(OutputStream out, XLSCallBack<T> xlscaCallBack)
throws RuntimeException, IOException {
HSSFWorkbook workbook = new HSSFWorkbook();
// create a sheet with specified name
HSSFSheet sheet = workbook.createSheet(xlscaCallBack.getSheetName());
for (int i = 0; i < xlscaCallBack.getColumnWidth().length; i++) {
sheet.setColumnWidth(i, xlscaCallBack.getColumnWidth()[i] * 256);
}
// create a title for sheet title
sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, xlscaCallBack.getHeaders().length - 1));
HSSFCellStyle titleCellStyle = workbook.createCellStyle(); // create titleCellStyle for cell
titleCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
HSSFFont titleFont = workbook.createFont(); // set font
titleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
titleFont.setFontName("宋体");
titleFont.setFontHeight((short) (240));
titleFont.setColor(HSSFColor.AUTOMATIC.index);
titleCellStyle.setFont(titleFont);
titleCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); // set border
titleCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
titleCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
titleCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
titleCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // set background
titleCellStyle.setFillForegroundColor(HSSFColor.LIGHT_BLUE.index);
titleCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
titleCellStyle.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
HSSFRow title = sheet.createRow(0);
title.setHeight((short) 300);
for (int i = 0; i < xlscaCallBack.getHeaders().length; i++) { // create tytle for title
HSSFCell cell = title.createCell(i);
cell.setCellValue(xlscaCallBack.getTitle());
cell.setCellStyle(titleCellStyle);
}
HSSFCellStyle headerCellStyle = workbook.createCellStyle(); // create headerCellStyle for cell
headerCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
HSSFFont headerFont = workbook.createFont(); // set font
headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
headerFont.setFontName("幼圆");
headerFont.setColor(HSSFColor.AUTOMATIC.index);
headerCellStyle.setFont(headerFont);
headerCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); // set border
headerCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
headerCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
headerCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
headerCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // set background
headerCellStyle.setFillForegroundColor(HSSFColor.LIGHT_BLUE.index);
headerCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
headerCellStyle.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
HSSFRow header = sheet.createRow(1);
for (int i = 0; i < xlscaCallBack.getHeaders().length; i++) { // create tytle for header
HSSFCell cell = header.createCell(i);
cell.setCellValue(xlscaCallBack.getHeaders()[i]);
cell.setCellStyle(headerCellStyle);
}
HSSFCellStyle rowCellStyle = workbook.createCellStyle(); // create headerCellStyle for cell
rowCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
HSSFFont rowFont = workbook.createFont(); // set font
rowFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
rowFont.setFontName("幼圆");
rowFont.setColor(HSSFColor.AUTOMATIC.index);
rowCellStyle.setFont(rowFont);
rowCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); // set border
rowCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
rowCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
rowCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
for (int i = 0; i < lists.size(); i++) {
HSSFRow row = sheet.createRow(i + 2);
String values[] = xlscaCallBack.getValue(lists.get(i));
for (int j = 0; j < values.length; j++) {
HSSFCell cell = row.createCell(j);
cell.setCellValue(values[j]);
cell.setCellStyle(rowCellStyle);
}
}
HSSFCellStyle bottomCellStyle = workbook.createCellStyle(); // create titleCellStyle for cell
bottomCellStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
HSSFFont bottomFont = workbook.createFont(); // set font
bottomFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
bottomFont.setFontName("幼圆");
bottomFont.setColor(HSSFColor.AUTOMATIC.index);
bottomCellStyle.setFont(bottomFont);
bottomCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN); // set border
bottomCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
bottomCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
bottomCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
bottomCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); // set background
bottomCellStyle.setFillForegroundColor(HSSFColor.LIGHT_BLUE.index);
bottomCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
bottomCellStyle.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
sheet.addMergedRegion(
new CellRangeAddress(
lists.size() + 2,
lists.size() + 2,
0,
xlscaCallBack.getHeaders().length - 1)); // creaet bootom for xls
HSSFRow bottomRow = sheet.createRow(lists.size() + 2);
for (int i = 0; i < xlscaCallBack.getHeaders().length; i++) { // create tytle for title
HSSFCell cell = bottomRow.createCell(i);
SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
cell.setCellValue("共计导出 " + lists.size() + " 条记录 ,导出日期:" + sf.format(new Date()));
cell.setCellStyle(bottomCellStyle);
}
try {
workbook.write(out);
} catch (IOException e) {
e.printStackTrace();
throw new RuntimeException("create Excel failed due to some unkonw reasion~");
}
}
@Override
protected void buildExcelDocument(
Map<String, Object> model,
HSSFWorkbook workbook,
HttpServletRequest request,
HttpServletResponse response)
throws Exception {
DataGrid dataGrid = (DataGrid) model.get("dataGrid");
String sheetName = "Sheet 1";
if (StringUtils.isNotBlank(dataGrid.getTitle())) {
sheetName = dataGrid.getTitle();
}
HSSFSheet sheet = workbook.createSheet(sheetName);
Font font = workbook.createFont();
font.setColor(HSSFColor.WHITE.index);
HSSFCellStyle style = workbook.createCellStyle();
style.setFillForegroundColor(HSSFColor.GREY_50_PERCENT.index);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style.setFont(font);
int rowNum = 0;
int maxColumnCount = 0;
if (dataGrid.getHeaderFields().size() > 0) {
HSSFRow header = sheet.createRow(rowNum);
rowNum++;
maxColumnCount = dataGrid.getHeaderFields().size();
int i = 0;
for (String field : dataGrid.getHeaderFields()) {
HSSFCell cell = header.createCell(i);
cell.setCellValue(field);
cell.setCellStyle(style);
i++;
}
}
for (int y = 0; y < dataGrid.getRowCount(); y++) {
HSSFRow row = sheet.createRow(rowNum++);
List<String> rowData = dataGrid.getRowFields(y);
if (rowData.size() > maxColumnCount) {
maxColumnCount = rowData.size();
}
int x = 0;
for (String data : rowData) {
HSSFCell cell = row.createCell(x);
try {
double dbValue = Double.parseDouble(data);
cell.setCellValue(dbValue);
} catch (NumberFormatException nfe) {
cell.setCellValue(data);
}
x++;
}
}
for (int i = 0; i < maxColumnCount; i++) {
sheet.autoSizeColumn(i);
}
}
public void exportExcel(
String headline,
String title,
String[] headers,
List<Object[]> dataset,
OutputStream out,
short[] width) {
// 声明一个工作薄
HSSFWorkbook workbook = new HSSFWorkbook();
// 生成一个表格
HSSFSheet sheet = workbook.createSheet(title);
// 设置表格默认列宽度为15个字节
sheet.setDefaultColumnWidth((short) 15);
// 生成一个样式
HSSFCellStyle style = workbook.createCellStyle();
// 设置这些样式
style.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.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);
// 生成并设置另一个样式
HSSFCellStyle style2 = workbook.createCellStyle();
style2.setFillForegroundColor(HSSFColor.WHITE.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);
// 产生表格标题行
HSSFRow row = sheet.createRow(0);
if (row != null) {
HSSFCell cell = row.createCell(0);
HSSFFont f = workbook.createFont();
f.setColor(HSSFColor.DARK_BLUE.index);
f.setFontHeightInPoints((short) 24);
f.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// 把字体应用到当前的样式
HSSFCellStyle s = workbook.createCellStyle();
s.setAlignment(HSSFCellStyle.ALIGN_CENTER);
s.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
s.setFont(f);
cell.setCellStyle(s);
cell.setCellValue(headline);
}
sheet.addMergedRegion(new Region(0, (short) 0, 0, (short) (headers.length - 1)));
row = sheet.createRow(1);
for (short i = 0; i < headers.length; i++) {
HSSFCell cell = row.createCell(i);
cell.setCellStyle(style);
HSSFRichTextString text = new HSSFRichTextString(headers[i]);
cell.setCellValue(text);
}
for (int j = 0; j < dataset.size(); j++) {
row = sheet.createRow(j + 2);
for (short i = 0; i < dataset.get(j).length; i++) {
HSSFCell cell = row.createCell(i);
cell.setCellStyle(style2);
HSSFRichTextString richString =
new HSSFRichTextString(String.valueOf(dataset.get(j)[i]).replace("null", ""));
// HSSFFont font3 = workbook.createFont();
// font3.setColor(HSSFColor.BLACK.index);
// richString.applyFont(font3);
cell.setCellValue(richString);
}
}
if (width != null) {
for (short i = 0; i < width.length; i++) {
sheet.setColumnWidth(i, width[i]);
}
}
try {
workbook.write(out);
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public void exportJgjExcel(
String title,
List<String[]> header,
List<Object[]> dataset,
OutputStream out,
short[] width,
List<int[]> merge) {
// 声明一个工作薄
HSSFWorkbook workbook = new HSSFWorkbook();
// 生成一个表格
HSSFSheet sheet = workbook.createSheet(title);
// 设置表格默认列宽度为15个字节
sheet.setDefaultColumnWidth((short) 15);
// 生成一个样式
HSSFCellStyle style = workbook.createCellStyle();
// 设置这些样式
style.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.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);
// 生成并设置另一个样式
HSSFCellStyle style2 = workbook.createCellStyle();
style2.setFillForegroundColor(HSSFColor.WHITE.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);
// 产生表格标题行
HSSFRow row = sheet.createRow(0);
for (int i = 0; i < header.size(); i++) {
row = sheet.createRow(i);
for (int m = 0; m < header.get(i).length; m++) {
HSSFCell cell = row.createCell(m);
cell.setCellStyle(style);
String headValue = header.get(i)[m];
// HSSFRichTextString text = new HSSFRichTextString(header.get(i)[m]);
cell.setCellValue(headValue);
}
}
if (merge != null && merge.size() > 0) {
for (int m = 0; m < merge.size(); m++) {
sheet.addMergedRegion(
new CellRangeAddress(
merge.get(m)[0], merge.get(m)[1], merge.get(m)[2], merge.get(m)[3])); // 合并行
}
}
// sheet.addMergedRegion(new CellRangeAddress(0,0,9,10));
// sheet.addMergedRegion(new CellRangeAddress(0,0,11,12));
for (int j = 0; j < dataset.size(); j++) {
row = sheet.createRow(j + header.size());
for (short i = 0; i < dataset.get(j).length; i++) {
HSSFCell cell = row.createCell(i);
cell.setCellStyle(style2);
HSSFRichTextString richString =
new HSSFRichTextString(String.valueOf(dataset.get(j)[i]).replace("null", ""));
// HSSFFont font3 = workbook.createFont();
// font3.setColor(HSSFColor.BLACK.index);
// richString.applyFont(font3);
cell.setCellValue(richString);
}
}
if (width != null) {
for (short i = 0; i < width.length; i++) {
sheet.setColumnWidth(i, width[i]);
}
}
try {
workbook.write(out);
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public void applyFillTo(HSSFCellStyle style) {
style.setFillPattern(this.fillPattern);
style.setFillForegroundColor(this.fillForegroundColor);
style.setFillBackgroundColor(this.fillBackgroundColor);
}
protected Map<Short, HSSFCellStyle> applyStyles(final Report report, final HSSFWorkbook wb) {
final StylePalette palette = report.getPalette();
final Map<Short, HSSFCellStyle> styles = new HashMap<Short, HSSFCellStyle>();
if (report.getTemplate() != null) {
for (final short styleIndex : palette.getStyles().keySet()) {
final HSSFCellStyle style = wb.getCellStyleAt(styleIndex);
if (style == null)
throw new RuntimeException(
"Inconsistent report template. Style not found: " + styleIndex);
styles.put(styleIndex, style);
}
return styles;
}
if (palette.getColors().size() > PaletteRecord.STANDARD_PALETTE_SIZE)
throw new RuntimeException("too many colors on report");
final HSSFPalette pal = wb.getCustomPalette();
for (final Color color : palette.getColors().values()) {
pal.setColorAtIndex(color.getId(), color.getRed(), color.getGreen(), color.getBlue());
}
final Map<Short, HSSFFont> fonts = new HashMap<Short, HSSFFont>();
final HSSFDataFormat formatter = wb.createDataFormat();
for (final Font font : palette.getFonts().values()) {
final HSSFFont f = POIUtils.ensureFontExists(wb, font);
fonts.put(font.getId(), f);
}
for (final CellStyle style : palette.getStyles().values()) {
final short bbc =
style.getBottomBorderColor() != null ? style.getBottomBorderColor().getId() : 0;
final short fbc =
style.getFillBackgroundColor() != null ? style.getFillBackgroundColor().getId() : 0;
final short ffc =
style.getFillForegroundColor() != null ? style.getFillForegroundColor().getId() : 0;
final short lbc = style.getLeftBorderColor() != null ? style.getLeftBorderColor().getId() : 0;
final short rbc =
style.getRightBorderColor() != null ? style.getRightBorderColor().getId() : 0;
final short tbc = style.getTopBorderColor() != null ? style.getTopBorderColor().getId() : 0;
final HSSFCellStyle s = wb.createCellStyle();
s.setAlignment(style.getAlignment());
s.setBorderBottom(style.getBorderBottom());
s.setBorderLeft(style.getBorderLeft());
s.setBorderRight(style.getBorderRight());
s.setBorderTop(style.getBorderTop());
s.setBottomBorderColor(bbc);
s.setDataFormat(formatter.getFormat(style.getDataFormat()));
s.setFillBackgroundColor(fbc);
s.setFillForegroundColor(ffc);
s.setFillPattern(style.getFillPattern());
s.setHidden(style.isHidden());
s.setIndention(style.getIndention());
s.setLeftBorderColor(lbc);
s.setLocked(style.isLocked());
s.setRightBorderColor(rbc);
s.setRotation(style.getRotation());
s.setTopBorderColor(tbc);
s.setVerticalAlignment(style.getVerticalAlignment());
s.setWrapText(style.isWrapText());
s.setFont(fonts.get(style.getFont().getId()));
styles.put(style.getId(), s);
}
return styles;
}
@SuppressWarnings("deprecation")
public Reporteconcepto(
Rgenerador objeto,
Rgenerador objeto2,
Boolean siinicial,
String partida,
String ruta,
String ruta2,
String path)
throws IOException {
this.FILE = path;
// creacion del libro que contedra nuestro reporte
libro = new HSSFWorkbook();
// cracion de la hoja que estara contenida en nuestro libro
hoja = libro.createSheet("new sheet");
// Definicion de estilo que contendra nuestro encabezado
// *****************************************************************************************************************************************************
// definicion estilos de celdas, establecimineto del tipo de fuente
fuenteen = libro.createFont();
fuenteen.setFontHeightInPoints((short) 12);
fuenteen.setFontName(fuenteen.FONT_ARIAL);
fuenteen.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// Creacion del objeto que se encargara de aplicar el estilo a la celda
esceldaen = libro.createCellStyle();
esceldaen.setWrapText(true);
esceldaen.setAlignment(HSSFCellStyle.ALIGN_LEFT);
esceldaen.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);
esceldaen.setFont(fuenteen);
// establecimiento de sombreado de nuestra celda
esceldaen.setFillForegroundColor((short) 44);
esceldaen.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
// Definicion de estilo que contendra los tiulos
// *****************************************************************************************************************************************************
// definicion estilos de celdas, establecimineto del tipo de fuente
fuentet = libro.createFont();
fuentet.setFontHeightInPoints((short) 11);
fuentet.setFontName(fuentet.FONT_ARIAL);
fuentet.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// Creacion del objeto que se encargara de aplicar el estilo a la celda
esceldat = libro.createCellStyle();
esceldat.setWrapText(true);
esceldat.setAlignment(HSSFCellStyle.ALIGN_CENTER);
esceldat.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);
esceldat.setFont(fuentet);
// establecimiento de bordes
esceldat.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
esceldat.setBottomBorderColor((short) 8);
esceldat.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
esceldat.setLeftBorderColor((short) 8);
esceldat.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
esceldat.setRightBorderColor((short) 8);
esceldat.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
esceldat.setRightBorderColor((short) 8);
// establecimiento de sombreado de nuestra celda
esceldat.setFillForegroundColor((short) 22);
esceldat.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
// *****************************************************************************************************************************************************
// Definicion del estilo de la celda de nuestros datos que contendra el
// reporte
// definicion estilos de celdas, establecimineto del tipo de fuente
fuentein = libro.createFont();
fuentein.setFontHeightInPoints((short) 10);
fuentein.setFontName(fuentein.FONT_ARIAL);
fuentein.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// Creacion del objeto que se encargara de aplicar el estilo a la celda
esceldain = libro.createCellStyle();
esceldain.setWrapText(true);
esceldain.setAlignment(HSSFCellStyle.ALIGN_CENTER);
esceldain.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);
esceldain.setFont(fuentet);
// establecimiento de bordes
esceldain.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
esceldain.setBottomBorderColor((short) 8);
esceldain.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
esceldain.setLeftBorderColor((short) 8);
esceldain.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
esceldain.setRightBorderColor((short) 8);
esceldain.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
esceldain.setRightBorderColor((short) 8);
// definimos el numero de filas que contedra nuestro decumento.
encabezado = hoja.createRow((short) 5);
Cencabezado = encabezado.createCell((short) 2);
Cencabezado.setCellValue("Datos Verificados: Partida(" + partida + ")");
hoja.addMergedRegion(new Region(5, (short) 2, 6, (short) 4));
Cencabezado.setCellStyle(esceldaen);
// ********************************************************************************************************************
HSSFRow fila1 = hoja.createRow((short) 2);
HSSFCell ccontrato = fila1.createCell((short) 8);
ccontrato.setCellValue("contrato:");
ccontrato.setCellStyle(esceldain);
HSSFCell rcontrato = fila1.createCell((short) 9);
rcontrato.setCellValue(" ");
rcontrato.setCellStyle(esceldain);
HSSFCell cgenrencia = fila1.createCell((short) 10);
cgenrencia.setCellValue("Gerencia:");
cgenrencia.setCellStyle(esceldain);
HSSFCell rgerencia = fila1.createCell((short) 11);
rgerencia.setCellValue(" ");
rgerencia.setCellStyle(esceldain);
HSSFCell choja = fila1.createCell((short) 12);
choja.setCellValue("Hoja:");
choja.setCellStyle(esceldain);
HSSFCell rhoja = fila1.createCell((short) 13);
rhoja.setCellValue(" ");
rhoja.setCellStyle(esceldain);
// *******************************************************************************************************************************************************
HSSFRow fila2 = hoja.createRow((short) 3);
HSSFCell cnc = fila2.createCell((short) 8);
cnc.setCellValue("N.C. :");
cnc.setCellStyle(esceldain);
HSSFCell rcnc = fila2.createCell((short) 9);
rcnc.setCellValue(" ");
rcnc.setCellStyle(esceldain);
HSSFCell ctipo = fila2.createCell((short) 10);
ctipo.setCellValue("Tipo de obra:");
ctipo.setCellStyle(esceldain);
HSSFCell rtipo = fila2.createCell((short) 11);
rtipo.setCellValue(" ");
rtipo.setCellStyle(esceldain);
HSSFCell cunidad = fila2.createCell((short) 12);
cunidad.setCellValue("Unidad:");
cunidad.setCellStyle(esceldain);
HSSFCell runidad = fila2.createCell((short) 13);
runidad.setCellValue(" ");
runidad.setCellStyle(esceldain);
// *******************************************************************************************************************************************************
HSSFRow fila3 = hoja.createRow((short) 4);
HSSFCell clocalidad = fila3.createCell((short) 8);
clocalidad.setCellValue("localidad:");
clocalidad.setCellStyle(esceldain);
HSSFCell rlocalidad = fila3.createCell((short) 9);
rlocalidad.setCellValue(" ");
rlocalidad.setCellStyle(esceldain);
hoja.addMergedRegion(new Region(4, (short) 9, 4, (short) 11));
rlocalidad.setCellStyle(esceldain);
HSSFCell runo = fila3.createCell((short) 10);
runo.setCellValue(" ");
runo.setCellStyle(esceldain);
HSSFCell rdos = fila3.createCell((short) 11);
rdos.setCellValue(" ");
rdos.setCellStyle(esceldain);
HSSFCell cfecha = fila3.createCell((short) 12);
cfecha.setCellValue("Fecha:");
cfecha.setCellStyle(esceldain);
HSSFCell rfecha = fila3.createCell((short) 13);
rfecha.setCellValue(" ");
rfecha.setCellStyle(esceldain);
// ***************************************************************************************************************************************************************
HSSFRow fila4 = hoja.createRow((short) 5);
HSSFCell ccontratista = fila4.createCell((short) 8);
ccontratista.setCellValue("Contratista:");
ccontratista.setCellStyle(esceldain);
HSSFCell rcontratista = fila4.createCell((short) 9);
rcontratista.setCellValue(" ");
rcontratista.setCellStyle(esceldain);
hoja.addMergedRegion(new Region(5, (short) 9, 5, (short) 13));
rcontratista.setCellStyle(esceldain);
HSSFCell runoc = fila4.createCell((short) 10);
runoc.setCellValue(" ");
runoc.setCellStyle(esceldain);
HSSFCell rdosc = fila4.createCell((short) 11);
rdosc.setCellValue(" ");
rdosc.setCellStyle(esceldain);
HSSFCell rtres = fila4.createCell((short) 12);
rtres.setCellValue(" ");
rtres.setCellStyle(esceldain);
HSSFCell rcuatro = fila4.createCell((short) 13);
rcuatro.setCellValue(" ");
rcuatro.setCellStyle(esceldain);
// ***************************************************************************************************************************************************************
HSSFRow fila5 = hoja.createRow((short) 6);
HSSFCell cperiodo = fila5.createCell((short) 8);
cperiodo.setCellValue("Consultor:");
cperiodo.setCellStyle(esceldain);
HSSFCell rperiodo = fila5.createCell((short) 9);
rperiodo.setCellValue(" ");
rperiodo.setCellStyle(esceldain);
hoja.addMergedRegion(new Region(6, (short) 9, 6, (short) 13));
rperiodo.setCellStyle(esceldain);
HSSFCell runop = fila5.createCell((short) 10);
runop.setCellValue(" ");
runop.setCellStyle(esceldain);
HSSFCell rdosp = fila5.createCell((short) 11);
rdosp.setCellValue(" ");
rdosp.setCellStyle(esceldain);
HSSFCell rtresp = fila5.createCell((short) 12);
rtresp.setCellValue(" ");
rtresp.setCellStyle(esceldain);
HSSFCell rcuatrop = fila5.createCell((short) 13);
rcuatrop.setCellValue(" ");
rcuatrop.setCellStyle(esceldain);
// crear un una columna
HSSFRow row1 = hoja.createRow((short) 7);
// create de las celdas
HSSFCell cc = row1.createCell((short) 2);
HSSFCell cd = row1.createCell((short) 3);
HSSFCell cu = row1.createCell((short) 4);
HSSFCell cx = row1.createCell((short) 5);
HSSFCell cy = row1.createCell((short) 6);
HSSFCell cz = row1.createCell((short) 7);
HSSFCell ca = row1.createCell((short) 8);
HSSFCell cl = row1.createCell((short) 9);
HSSFCell cal = row1.createCell((short) 10);
HSSFCell cca = row1.createCell((short) 11);
HSSFCell cpz = row1.createCell((short) 12);
HSSFCell cim = row1.createCell((short) 13);
// writing data to the cells
cc.setCellValue("Clave");
cc.setCellStyle(esceldat);
cd.setCellValue("Descripción");
cd.setCellStyle(esceldat);
cu.setCellValue("Unidad");
cu.setCellStyle(esceldat);
cx.setCellValue("X");
cx.setCellStyle(esceldat);
cy.setCellValue("Y");
cy.setCellStyle(esceldat);
cz.setCellValue("Z");
cz.setCellStyle(esceldat);
ca.setCellValue("Alto");
ca.setCellStyle(esceldat);
cl.setCellValue("Largo");
cl.setCellStyle(esceldat);
cal.setCellValue("ancho");
cal.setCellStyle(esceldat);
cca.setCellValue("Cantidad");
cca.setCellStyle(esceldat);
cpz.setCellValue("Piezas");
cpz.setCellStyle(esceldat);
cim.setCellValue("Importe");
cim.setCellStyle(esceldat);
// crear un una columna
HSSFRow row = hoja.createRow((short) 8);
// create de las celdas
HSSFCell cc1 = row.createCell((short) 2);
HSSFCell cd1 = row.createCell((short) 3);
HSSFCell cu1 = row.createCell((short) 4);
HSSFCell cx1 = row.createCell((short) 5);
HSSFCell cy1 = row.createCell((short) 6);
HSSFCell cz1 = row.createCell((short) 7);
HSSFCell ca1 = row.createCell((short) 8);
HSSFCell cl1 = row.createCell((short) 9);
HSSFCell cal1 = row.createCell((short) 10);
HSSFCell cca1 = row.createCell((short) 11);
HSSFCell cpz1 = row.createCell((short) 12);
HSSFCell cim1 = row.createCell((short) 13);
cc1.setCellValue(objeto.getClave());
cc1.setCellStyle(esceldain);
cd1.setCellValue(objeto.getDescripcion());
cd1.setCellStyle(esceldain);
cu1.setCellValue(objeto.getUnidad());
cu1.setCellStyle(esceldain);
cx1.setCellValue(objeto.getX());
cx1.setCellStyle(esceldain);
cy1.setCellValue(objeto.getY());
cy1.setCellStyle(esceldain);
cz1.setCellValue(objeto.getZ());
cz1.setCellStyle(esceldain);
ca1.setCellValue(objeto.getAlto());
ca1.setCellStyle(esceldain);
cl1.setCellValue(objeto.getLargo());
cl1.setCellStyle(esceldain);
cal1.setCellValue(objeto.getAncho());
cal1.setCellStyle(esceldain);
cca1.setCellValue(objeto.getCantidad());
cca1.setCellStyle(esceldain);
cpz1.setCellValue(objeto.getPiezas());
cpz1.setCellStyle(esceldain);
cim1.setCellValue(objeto.getImporte());
cim1.setCellStyle(esceldain);
HSSFSheet sheet = libro.getSheetAt(0);
HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
// contendor que contiene las imagenes
HSSFClientAnchor anchor;
if (ruta != null) {
File fis = new File(ruta);
anchor = new HSSFClientAnchor(0, 0, 0, 0, (short) 3, 11, (short) 5, 24);
anchor.setAnchorType(2);
HSSFPicture imagen = patriarch.createPicture(anchor, Cargarimagen(fis, libro));
}
if (ruta2 != null) {
File fis2 = new File(ruta2);
anchor = new HSSFClientAnchor(0, 0, 0, 0, (short) 9, 11, (short) 12, 24);
anchor.setAnchorType(2);
HSSFPicture imagen2 = patriarch.createPicture(anchor, Cargarimagen(fis2, libro));
}
sheet.autoSizeColumn((short) 2);
sheet.autoSizeColumn((short) 3);
sheet.autoSizeColumn((short) 4);
sheet.autoSizeColumn((short) 5);
sheet.autoSizeColumn((short) 6);
sheet.autoSizeColumn((short) 7);
sheet.autoSizeColumn((short) 8);
sheet.autoSizeColumn((short) 9);
sheet.autoSizeColumn((short) 10);
sheet.autoSizeColumn((short) 11);
sheet.autoSizeColumn((short) 12);
sheet.autoSizeColumn((short) 13);
if (siinicial == true) {
// ************************************************************************************
HSSFRow encabezado2 = hoja.createRow((short) 26);
HSSFCell Cencabezado2 = encabezado2.createCell((short) 2);
Cencabezado2.setCellValue("Datos: Partida(" + partida + ")");
hoja.addMergedRegion(new Region(26, (short) 2, 27, (short) 4));
Cencabezado2.setCellStyle(esceldaen);
// crear un una columna
HSSFRow row27 = hoja.createRow((short) 28);
// create de las celdas
HSSFCell cc2 = row27.createCell((short) 2);
HSSFCell cd2 = row27.createCell((short) 3);
HSSFCell cu2 = row27.createCell((short) 4);
HSSFCell cx2 = row27.createCell((short) 5);
HSSFCell cy2 = row27.createCell((short) 6);
HSSFCell cz2 = row27.createCell((short) 7);
HSSFCell ca2 = row27.createCell((short) 8);
HSSFCell cl2 = row27.createCell((short) 9);
HSSFCell cal2 = row27.createCell((short) 10);
HSSFCell cca2 = row27.createCell((short) 11);
HSSFCell cpz2 = row27.createCell((short) 12);
HSSFCell cim2 = row27.createCell((short) 13);
// writing data to the cells
cc2.setCellValue("Clave");
cc2.setCellStyle(esceldat);
cd2.setCellValue("Descripción");
cd2.setCellStyle(esceldat);
cu2.setCellValue("Unidad");
cu2.setCellStyle(esceldat);
cx2.setCellValue("X");
cx2.setCellStyle(esceldat);
cy2.setCellValue("Y");
cy2.setCellStyle(esceldat);
cz2.setCellValue("Z");
cz2.setCellStyle(esceldat);
ca2.setCellValue("Alto");
ca2.setCellStyle(esceldat);
cl2.setCellValue("Largo");
cl2.setCellStyle(esceldat);
cal2.setCellValue("ancho");
cal2.setCellStyle(esceldat);
cca2.setCellValue("Cantidad");
cca2.setCellStyle(esceldat);
cpz2.setCellValue("Piezas");
cpz2.setCellStyle(esceldat);
cim2.setCellValue("Importe");
cim2.setCellStyle(esceldat);
// crear un una columna
HSSFRow row28 = hoja.createRow((short) 29);
// create de las celdas
HSSFCell cc12 = row28.createCell((short) 2);
HSSFCell cd12 = row28.createCell((short) 3);
HSSFCell cu12 = row28.createCell((short) 4);
HSSFCell cx12 = row28.createCell((short) 5);
HSSFCell cy12 = row28.createCell((short) 6);
HSSFCell cz12 = row28.createCell((short) 7);
HSSFCell ca12 = row28.createCell((short) 8);
HSSFCell cl12 = row28.createCell((short) 9);
HSSFCell cal12 = row28.createCell((short) 10);
HSSFCell cca12 = row28.createCell((short) 11);
HSSFCell cpz12 = row28.createCell((short) 12);
HSSFCell cim12 = row28.createCell((short) 13);
cc12.setCellValue(objeto2.getClave());
cc12.setCellStyle(esceldain);
cd12.setCellValue(objeto2.getDescripcion());
cd12.setCellStyle(esceldain);
cu12.setCellValue(objeto2.getUnidad());
cu12.setCellStyle(esceldain);
cx12.setCellValue(objeto2.getX());
cx12.setCellStyle(esceldain);
cy12.setCellValue(objeto2.getY());
cy12.setCellStyle(esceldain);
cz12.setCellValue(objeto2.getZ());
cz12.setCellStyle(esceldain);
ca12.setCellValue(objeto2.getAlto());
ca12.setCellStyle(esceldain);
cl12.setCellValue(objeto2.getLargo());
cl12.setCellStyle(esceldain);
cal12.setCellValue(objeto2.getAncho());
cal12.setCellStyle(esceldain);
cca12.setCellValue(objeto2.getCantidad());
cca12.setCellStyle(esceldain);
cpz12.setCellValue(objeto2.getPiezas());
cpz12.setCellStyle(esceldain);
cim12.setCellValue(objeto2.getImporte());
cim12.setCellStyle(esceldain);
float uno = 0, dos = 0, resultado = 0;
uno = Float.parseFloat(objeto.getImporte());
dos = Float.parseFloat(objeto2.getImporte());
if (uno > dos) {
HSSFRow row31 = hoja.createRow((short) 33);
// create de las celdas
HSSFCell cc131 = row31.createCell((short) 3);
resultado = uno - dos;
cc131.setCellValue("Execedente: " + String.valueOf(resultado));
cc131.setCellStyle(esceldain);
} else {
if (dos > uno) {
HSSFRow row31 = hoja.createRow((short) 33);
// create de las celdas
HSSFCell cc131 = row31.createCell((short) 3);
resultado = dos - uno;
cc131.setCellValue("Restante: " + String.valueOf(resultado));
cc131.setCellStyle(esceldain);
}
}
// ****************************************************************************************
} else {
HSSFRow encabezado2 = hoja.createRow((short) 26);
HSSFCell Cencabezado2 = encabezado2.createCell((short) 2);
Cencabezado2.setCellValue(
"El aspecto:"
+ objeto.getDescripcion()
+ " no esta comtemplado en la estimacion inicial");
hoja.addMergedRegion(new Region(26, (short) 2, 27, (short) 4));
Cencabezado2.setCellStyle(esceldaen);
HSSFRow row28 = hoja.createRow((short) 29);
// create de las celdas
HSSFCell cc12 = row28.createCell((short) 3);
cc12.setCellValue("Execedente: " + objeto.getImporte());
cc12.setCellStyle(esceldain);
}
try {
FileOutputStream elFichero = new FileOutputStream(FILE);
libro.write(elFichero);
elFichero.close();
} catch (Exception e) {
e.printStackTrace();
}
}
