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;
}
/**
* 摘要： @说明：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;
}
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);
}
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;
}
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;
}
/**
* Создает новый стиль в документе 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;
}
@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();
}
}
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;
}
private Map initStyles(HSSFWorkbook wb, short fontHeight) {
Map result = new HashMap();
HSSFCellStyle titleStyle = wb.createCellStyle();
HSSFCellStyle textStyle = wb.createCellStyle();
HSSFCellStyle boldStyle = wb.createCellStyle();
HSSFCellStyle numericStyle = wb.createCellStyle();
HSSFCellStyle numericStyleBold = wb.createCellStyle();
HSSFCellStyle moneyStyle = wb.createCellStyle();
HSSFCellStyle moneyStyleBold = wb.createCellStyle();
HSSFCellStyle percentStyle = wb.createCellStyle();
HSSFCellStyle percentStyleBold = wb.createCellStyle();
result.put("titleStyle", titleStyle);
result.put("textStyle", textStyle);
result.put("boldStyle", boldStyle);
result.put("numericStyle", numericStyle);
result.put("numericStyleBold", numericStyleBold);
result.put("moneyStyle", moneyStyle);
result.put("moneyStyleBold", moneyStyleBold);
result.put("percentStyle", percentStyle);
result.put("percentStyleBold", percentStyleBold);
HSSFDataFormat format = wb.createDataFormat();
// Global fonts
HSSFFont font = wb.createFont();
font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
font.setColor(HSSFColor.BLACK.index);
font.setFontName(HSSFFont.FONT_ARIAL);
font.setFontHeightInPoints(fontHeight);
HSSFFont fontBold = wb.createFont();
fontBold.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
fontBold.setColor(HSSFColor.BLACK.index);
fontBold.setFontName(HSSFFont.FONT_ARIAL);
fontBold.setFontHeightInPoints(fontHeight);
// Money Style
moneyStyle.setFont(font);
moneyStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
moneyStyle.setDataFormat(format.getFormat(moneyFormat));
// Money Style Bold
moneyStyleBold.setFont(fontBold);
moneyStyleBold.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
moneyStyleBold.setDataFormat(format.getFormat(moneyFormat));
// Percent Style
percentStyle.setFont(font);
percentStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
percentStyle.setDataFormat(format.getFormat(percentFormat));
// Percent Style Bold
percentStyleBold.setFont(fontBold);
percentStyleBold.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
percentStyleBold.setDataFormat(format.getFormat(percentFormat));
// Standard Numeric Style
numericStyle.setFont(font);
numericStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
// Standard Numeric Style Bold
numericStyleBold.setFont(fontBold);
numericStyleBold.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
// Title Style
titleStyle.setFont(font);
titleStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
titleStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
titleStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
titleStyle.setBottomBorderColor(HSSFColor.BLACK.index);
titleStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
titleStyle.setLeftBorderColor(HSSFColor.BLACK.index);
titleStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
titleStyle.setRightBorderColor(HSSFColor.BLACK.index);
titleStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
titleStyle.setTopBorderColor(HSSFColor.BLACK.index);
titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
// Standard Text Style
textStyle.setFont(font);
textStyle.setWrapText(true);
// Standard Text Style
boldStyle.setFont(fontBold);
boldStyle.setWrapText(true);
return result;
}
/** Create a library of cell styles */
private HashMap<String, CellStyle> createStyles(HSSFWorkbook wb) {
HashMap<String, CellStyle> styles = new HashMap<String, CellStyle>();
HSSFCellStyle style;
Font titleFont = wb.createFont();
titleFont.setFontHeightInPoints((short) 18);
titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
style.setFont(titleFont);
styles.put("title", style);
Font monthFont = wb.createFont();
monthFont.setFontHeightInPoints((short) 10);
monthFont.setColor(IndexedColors.WHITE.getIndex());
// header in first sheet
style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_RIGHT);
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
style.setFont(monthFont);
style.setWrapText(true);
styles.put("header", style);
// header in second sheet
style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
setBorder(style, 1);
style.setFont(monthFont);
styles.put("header1", style);
style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
setBorder(style, 1, 0);
style.setFont(monthFont);
styles.put("header1_b", style);
style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
setBorder(style, 1, 1);
style.setFont(monthFont);
styles.put("header1_t", style);
style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
setBorder(style, 1, 2);
style.setFont(monthFont);
styles.put("header1_l", style);
style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
setBorder(style, 1, 3);
style.setFont(monthFont);
styles.put("header1_r", style);
style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
setBorder(style, 1, 4);
style.setFont(monthFont);
styles.put("header1_a", style);
style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setWrapText(true);
style.setBorderRight(CellStyle.BORDER_THIN);
style.setRightBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderLeft(CellStyle.BORDER_THIN);
style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderTop(CellStyle.BORDER_THIN);
style.setTopBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderBottom(CellStyle.BORDER_THIN);
style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
styles.put("cell", style);
style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
style.setDataFormat(wb.createDataFormat().getFormat("0.00"));
styles.put("formula", style);
style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
style.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
style.setDataFormat(wb.createDataFormat().getFormat("0.00"));
styles.put("formula_2", style);
return styles;
}
