/**
* 获取Excel2003图片
*
* @param sheetNum 当前sheet编号
* @param sheet 当前sheet对象
* @param workbook 工作簿对象
* @return Map key:图片单元格索引（0_1_1）String，value:图片流PictureData
* @throws IOException
*/
public static Map<String, PictureData> getSheetPictrues03(
int sheetNum, HSSFSheet sheet, HSSFWorkbook workbook) {
Map<String, PictureData> sheetIndexPicMap = new HashMap<String, PictureData>();
List<HSSFPictureData> pictures = workbook.getAllPictures();
if (pictures.size() != 0) {
for (HSSFShape shape : sheet.getDrawingPatriarch().getChildren()) {
HSSFClientAnchor anchor = (HSSFClientAnchor) shape.getAnchor();
if (shape instanceof HSSFPicture) {
if (null != shape) {
HSSFPicture pic = (HSSFPicture) shape;
int pictureIndex = pic.getPictureIndex() - 1;
HSSFPictureData picData = pictures.get(pictureIndex);
String picIndex =
String.valueOf(sheetNum)
+ UNDER_LINE
+ String.valueOf(anchor.getRow1())
+ UNDER_LINE
+ String.valueOf(anchor.getCol1());
sheetIndexPicMap.put(picIndex, picData);
}
}
}
return sheetIndexPicMap;
} else {
return null;
}
}
protected void drawImage(File reportPNG, HSSFSheet sheet, int columnStart, int columnEnd)
throws IOException {
HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
HSSFClientAnchor anchor =
new HSSFClientAnchor(0, 0, 240, 0, (short) columnStart, 5, (short) columnEnd, 23);
anchor.setAnchorType(3);
patriarch.createPicture(anchor, loadPicture(workbook, reportPNG));
}
/**
* 输出图片到指定的单元格，参考POI例子中的ReportImageUtil类。
*
* @param cell -- 单元格
* @param bytes -- 图片内容
*/
public static void addImageToSheet(HSSFCell cell, byte[] bytes) {
if (cell == null) {
_log.showError("-----insertImageToSheet: cell is null!");
return;
}
if (bytes == null || bytes.length == 0) {
_log.showError("-----insertImageToSheet: bytes is null!");
return;
}
// 取所在表单对象
HSSFSheet sheet = cell.getSheet();
// 取图片输出行与列
int firstRow = cell.getRowIndex();
int lastRow = cell.getRowIndex();
int firstCol = cell.getColumnIndex();
int lastCol = cell.getColumnIndex();
// 取单元格所在的区域
CellRangeAddress range = getMergedRegion(cell);
if (range != null) {
firstRow = range.getFirstRow();
lastRow = range.getLastRow();
firstCol = range.getFirstColumn();
lastCol = range.getLastColumn();
}
_log.showDebug(
"---------image cells=[" + firstRow + "," + firstCol + "," + lastRow + "," + lastCol + "]");
// 图片输出要比单元格的高与宽偏5个值，保留单元的边框，宽度1023表示填充满，高度255表示填充满
HSSFClientAnchor anchor =
new HSSFClientAnchor(5, 5, 1023, 255, (short) firstCol, firstRow, (short) lastCol, lastRow);
anchor.setAnchorType(HSSFClientAnchor.MOVE_AND_RESIZE);
// 取图片管理器，如果没有则创建
HSSFPatriarch draw = sheet.getDrawingPatriarch();
if (draw == null) {
draw = sheet.createDrawingPatriarch();
}
// 插入新图片，返回的新图片序号无效
sheet.getWorkbook().addPicture(bytes, HSSFWorkbook.PICTURE_TYPE_JPEG);
// 上面代码中新建图片的序号没有考虑原有图片数量，所以取原图片数量+1作为新图片的序号
List<HSSFPicture> lsPicture = getAllPicture(sheet);
int index = lsPicture.size() + 1;
_log.showDebug("---------new image index=" + index);
draw.createPicture(anchor, index);
}
public void testBug45312() throws Exception {
HSSFWorkbook wb = new HSSFWorkbook();
try {
HSSFSheet sheet = wb.createSheet();
HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
{
HSSFClientAnchor a1 = new HSSFClientAnchor();
a1.setAnchor((short) 1, 1, 0, 0, (short) 1, 1, 512, 100);
HSSFSimpleShape shape1 = patriarch.createSimpleShape(a1);
shape1.setShapeType(HSSFSimpleShape.OBJECT_TYPE_LINE);
}
{
HSSFClientAnchor a1 = new HSSFClientAnchor();
a1.setAnchor((short) 1, 1, 512, 0, (short) 1, 1, 1024, 100);
HSSFSimpleShape shape1 = patriarch.createSimpleShape(a1);
shape1.setFlipVertical(true);
shape1.setShapeType(HSSFSimpleShape.OBJECT_TYPE_LINE);
}
{
HSSFClientAnchor a1 = new HSSFClientAnchor();
a1.setAnchor((short) 2, 2, 0, 0, (short) 2, 2, 512, 100);
HSSFSimpleShape shape1 = patriarch.createSimpleShape(a1);
shape1.setShapeType(HSSFSimpleShape.OBJECT_TYPE_LINE);
}
{
HSSFClientAnchor a1 = new HSSFClientAnchor();
a1.setAnchor((short) 2, 2, 0, 100, (short) 2, 2, 512, 200);
HSSFSimpleShape shape1 = patriarch.createSimpleShape(a1);
shape1.setFlipHorizontal(true);
shape1.setShapeType(HSSFSimpleShape.OBJECT_TYPE_LINE);
}
/*OutputStream stream = new FileOutputStream("/tmp/45312.xls");
try {
wb.write(stream);
} finally {
stream.close();
}*/
checkWorkbookBack(wb);
} finally {
wb.close();
}
}
/**
* 复制来源表格中第1个SHEET中的图片到目标表格中的第1个SHEET中。
*
* @param destBook -- 目标表格
* @param srcBook -- 来源表格
*/
private static void copySheetImage(HSSFWorkbook destBook, HSSFWorkbook srcBook) {
// 来源表单
HSSFSheet srcSheet = srcBook.getSheetAt(0);
// 目标表单
HSSFSheet destSheet = destBook.getSheetAt(0);
// 需要偏移的行数
int endRowNum = destSheet.getPhysicalNumberOfRows();
// 取来源表单中的图片对象
List<HSSFPicture> lsSrcPicture = getAllPicture(srcSheet);
_log.showDebug("----------source picture size:" + lsSrcPicture.size());
// 取所有子图形数据，如果是主从报表且明细数据占多页时，则会报空指针错误
List<HSSFPictureData> lsPicData = null;
try {
lsPicData = srcBook.getAllPictures();
} catch (Exception e) {
// e.printStackTrace();
_log.showWarn("由于表单明细有多页，造成临时表的图片数据取不到，只能采用原表第1个图替代！");
// 原表中也没有图片，则不处理图片复制了
lsPicData = destBook.getAllPictures();
if (lsPicData == null || lsPicData.isEmpty()) return;
// 只取原表中第1个图片信息
List<HSSFPictureData> destData = FactoryUtil.newList();
for (int i = 0, n = lsSrcPicture.size(); i < n; i++) {
destData.add(lsPicData.get(0));
}
lsPicData = destData;
}
if (lsPicData == null || lsPicData.isEmpty()) return;
_log.showDebug("----------source data size:" + lsPicData.size());
// data数量可能大于图片数量
if (lsSrcPicture.size() > lsPicData.size()) {
_log.showWarn("图片数量与数据数量不符！");
return;
}
// 取图片管理器
HSSFPatriarch destDraw = destSheet.getDrawingPatriarch();
if (destDraw == null) {
destDraw = destSheet.createDrawingPatriarch();
}
// 取原目标表单中的图片对象
List<HSSFPicture> lsDestPicture = getAllPicture(destSheet);
int index = lsDestPicture.size();
for (int i = 0, n = lsSrcPicture.size(); i < n; i++) {
// 取图片对象
HSSFPicture picture = lsSrcPicture.get(i);
// 根据图片序号取图片数据
HSSFPictureData picdata = lsPicData.get(i);
// 取图片字节信息
byte[] datas = picdata.getData();
// 取图片位置信息
HSSFClientAnchor anchor = (HSSFClientAnchor) picture.getAnchor();
// 添加行偏移值
anchor.setRow1(anchor.getRow1() + endRowNum);
anchor.setRow2(anchor.getRow2() + endRowNum);
// 插入新图片，返回的新图片序号无效
destBook.addPicture(datas, picdata.getFormat());
// 上面代码中新建图片的序号没有考虑原有图片数量，所以取原图片数量+1作为新图片的序号
index++;
_log.showDebug("---------copy new image index=" + index);
destDraw.createPicture(anchor, index);
}
}
/**
* 这是一个通用的方法，利用了JAVA的反射机制，可以将放置在JAVA集合中并且符号一定条件的数据以EXCEL 的形式输出到指定IO设备上
*
* @param headers 表格属性列名数组
* @param dataset 需要显示的数据集合,集合中一定要放置符合javabean风格的类的对象。此方法支持的
* javabean属性的数据类型有基本数据类型及String,Date,byte[](图片数据)
*/
@SuppressWarnings("unchecked")
public Workbook exportExcel(String[] headers, Collection<T> dataset) {
// 生成一个表格
HSSFSheet sheet = workbook.createSheet(sheetName);
// 设置表格默认列宽度为15个字节
if (useStyle) sheet.setDefaultColumnWidth(defaultColumnWidth);
// 生成一个样式
// 声明一个画图的顶级管理器
HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
// 定义注释的大小和位置,详见文档
HSSFComment comment =
patriarch.createComment(new HSSFClientAnchor(0, 0, 0, 0, (short) 4, 2, (short) 6, 5));
// 设置注释内容
comment.setString(new HSSFRichTextString(docPrse));
// 设置注释作者，当鼠标移动到单元格上是可以在状态栏中看到该内容.
comment.setAuthor(author);
// 产生表格标题行
HSSFRow row = sheet.createRow(startRow);
int colNum1 = startCol;
for (short i = 0; i < headers.length; i++) {
HSSFCell cell = row.createCell(colNum1);
if (useStyle) cell.setCellStyle(style);
HSSFRichTextString text = new HSSFRichTextString(headers[i]);
cell.setCellValue(text);
colNum1++;
}
// 遍历集合数据，产生数据行
Iterator<T> it = dataset.iterator();
int index = startRow;
while (it.hasNext()) {
index++;
row = sheet.createRow(index);
T t = (T) it.next();
// 利用反射，根据javabean属性的先后顺序，动态调用getXxx()方法得到属性值
Field[] fields = t.getClass().getDeclaredFields();
int colRow2 = startCol;
for (short i = 0; i < fields.length; i++) {
HSSFCell cell = row.createCell(colRow2);
if (useStyle) cell.setCellStyle(style2);
Field field = fields[i];
String fieldName = field.getName();
String getMethodName =
"get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
try {
Class tCls = t.getClass();
Method getMethod = tCls.getMethod(getMethodName, new Class[] {});
Object value = getMethod.invoke(t, new Object[] {});
// 判断值的类型后进行强制类型转换
String textValue = null;
if (value instanceof Boolean) {
boolean bValue = (Boolean) value;
textValue = "男";
if (!bValue) {
textValue = "女";
}
} else if (value instanceof Date) {
Date date = (Date) value;
SimpleDateFormat sdf = new SimpleDateFormat(dateFormat);
textValue = sdf.format(date);
} else if (value instanceof byte[]) {
// 有图片时，设置行高为60px;
row.setHeightInPoints(picCellHeight);
// 设置图片所在列宽度为80px,注意这里单位的一个换算
sheet.setColumnWidth(i, picCellWidth);
// sheet.autoSizeColumn(i);
byte[] bsValue = (byte[]) value;
HSSFClientAnchor anchor =
new HSSFClientAnchor(
0, 0, 1023, 255, (short) colRow2, index, (short) colRow2, index);
anchor.setAnchorType(2);
patriarch.createPicture(
anchor, workbook.addPicture(bsValue, HSSFWorkbook.PICTURE_TYPE_JPEG));
} else {
// 其它数据类型都当作字符串简单处理
textValue = value.toString();
}
// 如果不是图片数据，就利用正则表达式判断textValue是否全部由数字组成
if (textValue != null) {
Pattern p = Pattern.compile("^//d+(//.//d+)?$");
Matcher matcher = p.matcher(textValue);
if (matcher.matches()) {
// 是数字当作double处理
cell.setCellValue(Double.parseDouble(textValue));
} else {
HSSFRichTextString richString = new HSSFRichTextString(textValue);
HSSFFont font3 = workbook.createFont();
font3.setColor(HSSFColor.BLUE.index);
richString.applyFont(font3);
cell.setCellValue(richString);
}
}
} catch (SecurityException e) {
e.printStackTrace();
} catch (NoSuchMethodException e) {
e.printStackTrace();
} catch (IllegalArgumentException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
} finally {
// 清理资源
}
colRow2++;
}
}
return workbook;
}
@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();
}
}
/**
* Discription:[写一个单元格]
*
* @param cell 单元格
* @param value 写入的值
* @param valueType 写入的值的类型
* @param dateFormat 日期格式，默认yyyy-MM-dd
* @author:[代超]
* @update:[日期YYYY-MM-DD] [更改人姓名][变更描述]
*/
public void writeCell(Cell cell, Object value, String valueType, String dateFormat) {
if (cell == null || value == null) {
return;
}
if (dateFormat == null || "".equals(dateFormat.trim())) {
dateFormat = "yyyy-MM-dd";
}
String cellValue = "";
if ("String".equals(valueType)) {
cellValue = value.toString();
} else if ("int".equals(valueType)) {
cellValue = String.valueOf(value);
} else if ("float".equals(valueType)) {
cellValue = String.valueOf(value);
} else if ("double".equals(valueType)) {
cellValue = String.valueOf(value);
} else if ("Number".equals(valueType)) {
cellValue = String.valueOf(value);
} else if ("BigDecimal".equals(valueType)) {
cellValue = String.valueOf(value);
} else if ("byte[]".equals(valueType)) {
// 有图片时，设置行高为60px;
cell.getRow().setHeightInPoints(60);
// 设置图片所在列宽度为80px,注意这里单位的一个换算
cell.getSheet().setColumnWidth(cell.getColumnIndex(), (short) (35.7 * 80));
// sheet.autoSizeColumn(i);
byte[] bsValue = (byte[]) value;
HSSFClientAnchor anchor =
new HSSFClientAnchor(
0, 0, 1023, 255, (short) 6, cell.getRowIndex(), (short) 6, cell.getRowIndex());
anchor.setAnchorType(2);
// 声明一个画图的顶级管理器
cell.getSheet()
.createDrawingPatriarch()
.createPicture(
anchor,
cell.getSheet().getWorkbook().addPicture(bsValue, HSSFWorkbook.PICTURE_TYPE_JPEG));
return;
} else if ("Date".equals(valueType)) {
SimpleDateFormat sdf = new SimpleDateFormat(dateFormat);
cellValue = sdf.format(value);
} else if ("boolean".equals(valueType)) {
boolean bool = (Boolean) value;
if (bool) {
cellValue = "是";
} else {
cellValue = "否";
}
} else if ("Boolean".equals(valueType)) {
boolean bool = (Boolean) value;
if (bool) {
cellValue = "是";
} else {
cellValue = "否";
}
} else {
cellValue = String.valueOf(value);
}
cell.setCellValue(cellValue);
return;
}
private void checkWorkbookBack(HSSFWorkbook wb) {
HSSFWorkbook wbBack = HSSFTestDataSamples.writeOutAndReadBack(wb);
assertNotNull(wbBack);
HSSFSheet sheetBack = wbBack.getSheetAt(0);
assertNotNull(sheetBack);
HSSFPatriarch patriarchBack = sheetBack.getDrawingPatriarch();
assertNotNull(patriarchBack);
List<HSSFShape> children = patriarchBack.getChildren();
assertEquals(4, children.size());
HSSFShape hssfShape = children.get(0);
assertTrue(hssfShape instanceof HSSFSimpleShape);
HSSFAnchor anchor = hssfShape.getAnchor();
assertTrue(anchor instanceof HSSFClientAnchor);
assertEquals(0, anchor.getDx1());
assertEquals(512, anchor.getDx2());
assertEquals(0, anchor.getDy1());
assertEquals(100, anchor.getDy2());
HSSFClientAnchor cAnchor = (HSSFClientAnchor) anchor;
assertEquals(1, cAnchor.getCol1());
assertEquals(1, cAnchor.getCol2());
assertEquals(1, cAnchor.getRow1());
assertEquals(1, cAnchor.getRow2());
hssfShape = children.get(1);
assertTrue(hssfShape instanceof HSSFSimpleShape);
anchor = hssfShape.getAnchor();
assertTrue(anchor instanceof HSSFClientAnchor);
assertEquals(512, anchor.getDx1());
assertEquals(1024, anchor.getDx2());
assertEquals(0, anchor.getDy1());
assertEquals(100, anchor.getDy2());
cAnchor = (HSSFClientAnchor) anchor;
assertEquals(1, cAnchor.getCol1());
assertEquals(1, cAnchor.getCol2());
assertEquals(1, cAnchor.getRow1());
assertEquals(1, cAnchor.getRow2());
hssfShape = children.get(2);
assertTrue(hssfShape instanceof HSSFSimpleShape);
anchor = hssfShape.getAnchor();
assertTrue(anchor instanceof HSSFClientAnchor);
assertEquals(0, anchor.getDx1());
assertEquals(512, anchor.getDx2());
assertEquals(0, anchor.getDy1());
assertEquals(100, anchor.getDy2());
cAnchor = (HSSFClientAnchor) anchor;
assertEquals(2, cAnchor.getCol1());
assertEquals(2, cAnchor.getCol2());
assertEquals(2, cAnchor.getRow1());
assertEquals(2, cAnchor.getRow2());
hssfShape = children.get(3);
assertTrue(hssfShape instanceof HSSFSimpleShape);
anchor = hssfShape.getAnchor();
assertTrue(anchor instanceof HSSFClientAnchor);
assertEquals(0, anchor.getDx1());
assertEquals(512, anchor.getDx2());
assertEquals(100, anchor.getDy1());
assertEquals(200, anchor.getDy2());
cAnchor = (HSSFClientAnchor) anchor;
assertEquals(2, cAnchor.getCol1());
assertEquals(2, cAnchor.getCol2());
assertEquals(2, cAnchor.getRow1());
assertEquals(2, cAnchor.getRow2());
}
/**
* create a rectangle, save the workbook, read back and verify that all shape properties are there
*/
public void testReadWriteRectangle() throws IOException {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet();
HSSFPatriarch drawing = sheet.createDrawingPatriarch();
HSSFClientAnchor anchor = new HSSFClientAnchor(10, 10, 50, 50, (short) 2, 2, (short) 4, 4);
anchor.setAnchorType(2);
assertEquals(anchor.getAnchorType(), 2);
HSSFSimpleShape rectangle = drawing.createSimpleShape(anchor);
rectangle.setShapeType(HSSFSimpleShape.OBJECT_TYPE_RECTANGLE);
rectangle.setLineWidth(10000);
rectangle.setFillColor(777);
assertEquals(rectangle.getFillColor(), 777);
assertEquals(10000, rectangle.getLineWidth());
rectangle.setLineStyle(10);
assertEquals(10, rectangle.getLineStyle());
assertEquals(rectangle.getWrapText(), HSSFSimpleShape.WRAP_SQUARE);
rectangle.setLineStyleColor(1111);
rectangle.setNoFill(true);
rectangle.setWrapText(HSSFSimpleShape.WRAP_NONE);
rectangle.setString(new HSSFRichTextString("teeeest"));
assertEquals(rectangle.getLineStyleColor(), 1111);
assertEquals(
((EscherSimpleProperty)
((EscherOptRecord)
HSSFTestHelper.getEscherContainer(rectangle)
.getChildById(EscherOptRecord.RECORD_ID))
.lookup(EscherProperties.TEXT__TEXTID))
.getPropertyValue(),
"teeeest".hashCode());
assertEquals(rectangle.isNoFill(), true);
assertEquals(rectangle.getWrapText(), HSSFSimpleShape.WRAP_NONE);
assertEquals(rectangle.getString().getString(), "teeeest");
wb = HSSFTestDataSamples.writeOutAndReadBack(wb);
sheet = wb.getSheetAt(0);
drawing = sheet.getDrawingPatriarch();
assertEquals(1, drawing.getChildren().size());
HSSFSimpleShape rectangle2 = (HSSFSimpleShape) drawing.getChildren().get(0);
assertEquals(HSSFSimpleShape.OBJECT_TYPE_RECTANGLE, rectangle2.getShapeType());
assertEquals(10000, rectangle2.getLineWidth());
assertEquals(10, rectangle2.getLineStyle());
assertEquals(anchor, rectangle2.getAnchor());
assertEquals(rectangle2.getLineStyleColor(), 1111);
assertEquals(rectangle2.getFillColor(), 777);
assertEquals(rectangle2.isNoFill(), true);
assertEquals(rectangle2.getString().getString(), "teeeest");
assertEquals(rectangle.getWrapText(), HSSFSimpleShape.WRAP_NONE);
rectangle2.setFillColor(3333);
rectangle2.setLineStyle(9);
rectangle2.setLineStyleColor(4444);
rectangle2.setNoFill(false);
rectangle2.setLineWidth(77);
rectangle2.getAnchor().setDx1(2);
rectangle2.getAnchor().setDx2(3);
rectangle2.getAnchor().setDy1(4);
rectangle2.getAnchor().setDy2(5);
rectangle.setWrapText(HSSFSimpleShape.WRAP_BY_POINTS);
rectangle2.setString(new HSSFRichTextString("test22"));
wb = HSSFTestDataSamples.writeOutAndReadBack(wb);
sheet = wb.getSheetAt(0);
drawing = sheet.getDrawingPatriarch();
assertEquals(1, drawing.getChildren().size());
rectangle2 = (HSSFSimpleShape) drawing.getChildren().get(0);
assertEquals(HSSFSimpleShape.OBJECT_TYPE_RECTANGLE, rectangle2.getShapeType());
assertEquals(rectangle.getWrapText(), HSSFSimpleShape.WRAP_BY_POINTS);
assertEquals(77, rectangle2.getLineWidth());
assertEquals(9, rectangle2.getLineStyle());
assertEquals(rectangle2.getLineStyleColor(), 4444);
assertEquals(rectangle2.getFillColor(), 3333);
assertEquals(rectangle2.getAnchor().getDx1(), 2);
assertEquals(rectangle2.getAnchor().getDx2(), 3);
assertEquals(rectangle2.getAnchor().getDy1(), 4);
assertEquals(rectangle2.getAnchor().getDy2(), 5);
assertEquals(rectangle2.isNoFill(), false);
assertEquals(rectangle2.getString().getString(), "test22");
HSSFSimpleShape rect3 = drawing.createSimpleShape(new HSSFClientAnchor());
rect3.setShapeType(HSSFSimpleShape.OBJECT_TYPE_RECTANGLE);
wb = HSSFTestDataSamples.writeOutAndReadBack(wb);
drawing = wb.getSheetAt(0).getDrawingPatriarch();
assertEquals(drawing.getChildren().size(), 2);
}
