public void testShapeIds() {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet1 = wb.createSheet();
HSSFPatriarch patriarch1 = sheet1.createDrawingPatriarch();
for (int i = 0; i < 2; i++) {
patriarch1.createSimpleShape(new HSSFClientAnchor());
}
wb = HSSFTestDataSamples.writeOutAndReadBack(wb);
sheet1 = wb.getSheetAt(0);
patriarch1 = sheet1.getDrawingPatriarch();
EscherAggregate agg1 = HSSFTestHelper.getEscherAggregate(patriarch1);
// last shape ID cached in EscherDgRecord
EscherDgRecord dg1 = agg1.getEscherContainer().getChildById(EscherDgRecord.RECORD_ID);
assertEquals(1026, dg1.getLastMSOSPID());
// iterate over shapes and check shapeId
EscherContainerRecord spgrContainer = agg1.getEscherContainer().getChildContainers().get(0);
// root spContainer + 2 spContainers for shapes
assertEquals(3, spgrContainer.getChildRecords().size());
EscherSpRecord sp0 =
((EscherContainerRecord) spgrContainer.getChild(0)).getChildById(EscherSpRecord.RECORD_ID);
assertEquals(1024, sp0.getShapeId());
EscherSpRecord sp1 =
((EscherContainerRecord) spgrContainer.getChild(1)).getChildById(EscherSpRecord.RECORD_ID);
assertEquals(1025, sp1.getShapeId());
EscherSpRecord sp2 =
((EscherContainerRecord) spgrContainer.getChild(2)).getChildById(EscherSpRecord.RECORD_ID);
assertEquals(1026, sp2.getShapeId());
}
public void testRotation() {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet();
HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
HSSFSimpleShape rectangle =
patriarch.createSimpleShape(
new HSSFClientAnchor(0, 0, 100, 100, (short) 0, 0, (short) 5, 5));
rectangle.setShapeType(HSSFSimpleShape.OBJECT_TYPE_RECTANGLE);
assertEquals(rectangle.getRotationDegree(), 0);
rectangle.setRotationDegree((short) 45);
assertEquals(rectangle.getRotationDegree(), 45);
rectangle.setFlipHorizontal(true);
wb = HSSFTestDataSamples.writeOutAndReadBack(wb);
sheet = wb.getSheetAt(0);
patriarch = sheet.getDrawingPatriarch();
rectangle = (HSSFSimpleShape) patriarch.getChildren().get(0);
assertEquals(rectangle.getRotationDegree(), 45);
rectangle.setRotationDegree((short) 30);
assertEquals(rectangle.getRotationDegree(), 30);
patriarch.setCoordinates(0, 0, 10, 10);
rectangle.setString(new HSSFRichTextString("1234"));
}
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));
}
/**
* HSSFShape tree bust be built correctly Check file with such records structure: -patriarch
* --shape --group ---group ----shape ----shape ---shape ---group ----shape ----shape
*/
public void testDrawingGroups() {
HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("drawings.xls");
HSSFSheet sheet = wb.getSheet("groups");
HSSFPatriarch patriarch = sheet.getDrawingPatriarch();
assertEquals(patriarch.getChildren().size(), 2);
HSSFShapeGroup group = (HSSFShapeGroup) patriarch.getChildren().get(1);
assertEquals(3, group.getChildren().size());
HSSFShapeGroup group1 = (HSSFShapeGroup) group.getChildren().get(0);
assertEquals(2, group1.getChildren().size());
group1 = (HSSFShapeGroup) group.getChildren().get(2);
assertEquals(2, group1.getChildren().size());
}
/* assert shape properties when reading shapes from a existing workbook */
public void testReadExistingRectangle() {
HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("drawings.xls");
HSSFSheet sheet = wb.getSheet("rectangles");
HSSFPatriarch drawing = sheet.getDrawingPatriarch();
assertEquals(1, drawing.getChildren().size());
HSSFSimpleShape shape = (HSSFSimpleShape) drawing.getChildren().get(0);
assertEquals(shape.isNoFill(), false);
assertEquals(shape.getLineStyle(), HSSFShape.LINESTYLE_DASHDOTGEL);
assertEquals(shape.getLineStyleColor(), 0x616161);
assertEquals(HexDump.toHex(shape.getFillColor()), shape.getFillColor(), 0x2CE03D);
assertEquals(shape.getLineWidth(), HSSFShape.LINEWIDTH_ONE_PT * 2);
assertEquals(shape.getString().getString(), "POItest");
assertEquals(shape.getRotationDegree(), 27);
}
/**
* 输出图片到指定的单元格，参考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();
}
}
public void testOpt() throws Exception {
HSSFWorkbook wb = new HSSFWorkbook();
try {
// create a sheet with a text box
HSSFSheet sheet = wb.createSheet();
HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
HSSFTextbox textbox = patriarch.createTextbox(new HSSFClientAnchor());
EscherOptRecord opt1 = HSSFTestHelper.getOptRecord(textbox);
EscherOptRecord opt2 =
HSSFTestHelper.getEscherContainer(textbox).getChildById(EscherOptRecord.RECORD_ID);
assertSame(opt1, opt2);
} finally {
wb.close();
}
}
public void testReadExistingImage() {
HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("drawings.xls");
HSSFSheet sheet = wb.getSheet("pictures");
HSSFPatriarch drawing = sheet.getDrawingPatriarch();
assertEquals(1, drawing.getChildren().size());
HSSFPicture picture = (HSSFPicture) drawing.getChildren().get(0);
assertEquals(picture.getPictureIndex(), 2);
assertEquals(picture.getLineStyleColor(), HSSFShape.LINESTYLE__COLOR_DEFAULT);
assertEquals(picture.getFillColor(), 0x5DC943);
assertEquals(picture.getLineWidth(), HSSFShape.LINEWIDTH_DEFAULT);
assertEquals(picture.getLineStyle(), HSSFShape.LINESTYLE_DEFAULT);
assertEquals(picture.isNoFill(), false);
picture.setPictureIndex(2);
assertEquals(picture.getPictureIndex(), 2);
}
public void testClearShapesForPatriarch() {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet();
HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
patriarch.createSimpleShape(new HSSFClientAnchor());
patriarch.createSimpleShape(new HSSFClientAnchor());
patriarch.createCellComment(new HSSFClientAnchor());
EscherAggregate agg = HSSFTestHelper.getEscherAggregate(patriarch);
assertEquals(agg.getShapeToObjMapping().size(), 6);
assertEquals(agg.getTailRecords().size(), 1);
assertEquals(patriarch.getChildren().size(), 3);
patriarch.clear();
assertEquals(agg.getShapeToObjMapping().size(), 0);
assertEquals(agg.getTailRecords().size(), 0);
assertEquals(patriarch.getChildren().size(), 0);
wb = HSSFTestDataSamples.writeOutAndReadBack(wb);
sheet = wb.getSheetAt(0);
patriarch = sheet.getDrawingPatriarch();
assertEquals(agg.getShapeToObjMapping().size(), 0);
assertEquals(agg.getTailRecords().size(), 0);
assertEquals(patriarch.getChildren().size(), 0);
}
/**
* 取表单中的所有图片元素。
*
* @param sheet -- 表单对象
* @return
*/
private static List<HSSFPicture> getAllPicture(HSSFSheet sheet) {
List<HSSFPicture> lsPicture = FactoryUtil.newList();
HSSFPatriarch draw = sheet.getDrawingPatriarch();
if (draw == null) return lsPicture;
// 取所有子图形位置
List<HSSFShape> lsShape = draw.getChildren();
if (lsShape == null || lsShape.isEmpty()) return lsPicture;
for (int i = 0, n = lsShape.size(); i < n; i++) {
HSSFShape shape = lsShape.get(i);
if (shape instanceof HSSFPicture) {
lsPicture.add((HSSFPicture) shape);
}
}
return lsPicture;
}
public void testShapeContainerImplementsIterable() throws IOException {
HSSFWorkbook wb = new HSSFWorkbook();
try {
HSSFSheet sheet = wb.createSheet();
HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
patriarch.createSimpleShape(new HSSFClientAnchor());
patriarch.createSimpleShape(new HSSFClientAnchor());
int i = 2;
for (HSSFShape shape : patriarch) {
i--;
}
assertEquals(i, 0);
} finally {
wb.close();
}
}
public void testTextForSimpleShape() {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet();
HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
HSSFSimpleShape shape = patriarch.createSimpleShape(new HSSFClientAnchor());
shape.setShapeType(HSSFSimpleShape.OBJECT_TYPE_RECTANGLE);
EscherAggregate agg = HSSFTestHelper.getEscherAggregate(patriarch);
assertEquals(agg.getShapeToObjMapping().size(), 2);
wb = HSSFTestDataSamples.writeOutAndReadBack(wb);
sheet = wb.getSheetAt(0);
patriarch = sheet.getDrawingPatriarch();
shape = (HSSFSimpleShape) patriarch.getChildren().get(0);
agg = HSSFTestHelper.getEscherAggregate(patriarch);
assertEquals(agg.getShapeToObjMapping().size(), 2);
shape.setString(new HSSFRichTextString("string1"));
assertEquals(shape.getString().getString(), "string1");
assertNotNull(
HSSFTestHelper.getEscherContainer(shape).getChildById(EscherTextboxRecord.RECORD_ID));
assertEquals(agg.getShapeToObjMapping().size(), 2);
wb = HSSFTestDataSamples.writeOutAndReadBack(wb);
wb = HSSFTestDataSamples.writeOutAndReadBack(wb);
sheet = wb.getSheetAt(0);
patriarch = sheet.getDrawingPatriarch();
shape = (HSSFSimpleShape) patriarch.getChildren().get(0);
assertNotNull(HSSFTestHelper.getTextObjRecord(shape));
assertEquals(shape.getString().getString(), "string1");
assertNotNull(
HSSFTestHelper.getEscherContainer(shape).getChildById(EscherTextboxRecord.RECORD_ID));
assertEquals(agg.getShapeToObjMapping().size(), 2);
}
public void testShapeFlip() {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet();
HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
HSSFSimpleShape rectangle = patriarch.createSimpleShape(new HSSFClientAnchor());
rectangle.setShapeType(HSSFSimpleShape.OBJECT_TYPE_RECTANGLE);
assertEquals(rectangle.isFlipVertical(), false);
assertEquals(rectangle.isFlipHorizontal(), false);
rectangle.setFlipVertical(true);
assertEquals(rectangle.isFlipVertical(), true);
rectangle.setFlipHorizontal(true);
assertEquals(rectangle.isFlipHorizontal(), true);
wb = HSSFTestDataSamples.writeOutAndReadBack(wb);
sheet = wb.getSheetAt(0);
patriarch = sheet.getDrawingPatriarch();
rectangle = (HSSFSimpleShape) patriarch.getChildren().get(0);
assertEquals(rectangle.isFlipHorizontal(), true);
rectangle.setFlipHorizontal(false);
assertEquals(rectangle.isFlipHorizontal(), false);
assertEquals(rectangle.isFlipVertical(), true);
rectangle.setFlipVertical(false);
assertEquals(rectangle.isFlipVertical(), false);
wb = HSSFTestDataSamples.writeOutAndReadBack(wb);
sheet = wb.getSheetAt(0);
patriarch = sheet.getDrawingPatriarch();
rectangle = (HSSFSimpleShape) patriarch.getChildren().get(0);
assertEquals(rectangle.isFlipVertical(), false);
assertEquals(rectangle.isFlipHorizontal(), false);
}
@Test
public void test_poi() {
final int rowNum = 27;
final int colNum = 15;
HSSFWorkbook wb = null;
Sheet sheet = null;
String today = "2013/8/31";
String sign = "Month to date";
String[] titles = {
"",
"",
"",
"Chinapay eMail\r\n 商城总计",
"Japan Page\r\n 日本馆首页",
"Taiwan Page\r\n 台湾馆首页",
"USA Page\r\n 美国馆首页",
"Anhui Page\r\n 安徽馆首页",
"China Page\r\n 中国馆首页"
};
String[] colNames = {
"",
"Page View (PV)\r\n 浏览量",
"Unique Visitor (UV)\r\n 独立访客",
"Completed Orders\r\n 确认订单",
"Transaction Amount\r\n 交易金额",
"1st Top Seller\r\n 最佳销量",
"Unit Price 单价",
"Qty Sold 销量",
"2nd Top Seller\r\n 第二销量",
"Unit Price 单价",
"Qty Sold 销量",
"3rd Top Seller\r\n 第三销量",
"Unit Price 单价",
"Qty Sold 销量",
"1st Top Seller\r\n 最佳销量",
"Unit Price 单价",
"Qty Sold 销量",
"2nd Top Seller\r\n 第二销量",
"Unit Price 单价",
"Qty Sold 销量",
"3rd Top Seller\r\n 第三销量",
"Unit Price 单价",
"Qty Sold 销量"
};
int n = 0;
int len = 1;
String fileName = "D:/日报.xls";
File f = new File(fileName);
ByteArrayOutputStream byteArrayOut = null;
BufferedImage bufferImg = null;
String[] jpgUrls = {
"http://img.chinapay.com/data/files/store_37452/goods_93/small_201303271804531386.jpg",
"http://img.chinapay.com/data/files/store_44066/goods_37/201308280953576580.jpg",
"http://img.chinapay.com/data/files/store_289253/goods_95/small_201309031434558044.jpg",
"http://img.chinapay.com/data/files/store_289253/goods_180/small_201309031403003861.jpg",
"http://img.chinapay.com/data/files/store_37452/goods_98/small_201309121508186810.jpg",
"http://img.chinapay.com/data/files/store_37452/goods_24/small_201301241133447193.jpg"
};
String[] https = {
"http://emall.chinapay.com/goods/37452/1010000109792.html",
"http://emall.chinapay.com/goods/44066/1010000119323.html",
"http://emall.chinapay.com/goods/289253/1010000119621.html?jpsv=laoxcashback6",
"http://emall.chinapay.com/goods/289253/1010000119627.html?jpsv=laoxcashback6",
"http://emall.chinapay.com/goods/37452/1010000120588.html",
"http://emall.chinapay.com/goods/37452/1010000107096.html"
};
URL url = null;
HSSFHyperlink link = null;
HSSFPatriarch patri = null;
HSSFClientAnchor anchor = null;
try {
if (!f.exists()) {
wb = new HSSFWorkbook();
} else {
FileInputStream in = new FileInputStream(fileName);
wb = new HSSFWorkbook(in);
}
CellStyle style = wb.createCellStyle();
style.setFillForegroundColor(HSSFCellStyle.THIN_BACKWARD_DIAG);
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 垂直居中
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// style.setLeftBorderColor(HSSFColor.RED.index);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
style.setBorderLeft(HSSFCellStyle.BORDER_THIN); // 左边框
style.setBorderTop(HSSFCellStyle.BORDER_THIN); // 上边框
style.setBorderRight(HSSFCellStyle.BORDER_THIN); // 右边框
style.setWrapText(true);
sheet = wb.createSheet("sheet " + ((int) (100000 * Math.random())));
// 设置列的宽度
sheet.setDefaultColumnWidth(20);
sheet.setDefaultRowHeight((short) 400);
Row row = null;
Cell cell = null;
for (int r = 0; r < rowNum; r++) {
row = sheet.createRow(r);
// 设置第1行当高度
if (r == 0) {
row.setHeightInPoints(30);
}
// 设置第2列以后的宽度(即列号>=2的列，列号从0开始)
if (r >= 2) {
sheet.setColumnWidth(r, 3020);
}
for (int c = 0; c < colNum; c++) {
cell = row.createCell(c);
cell.setCellStyle(style);
// 处理第一行
if (r == 0) {
sheet.addMergedRegion(new CellRangeAddress(r, r, 3, 4));
sheet.addMergedRegion(new CellRangeAddress(r, r, 5, 6));
sheet.addMergedRegion(new CellRangeAddress(r, r, 7, 8));
sheet.addMergedRegion(new CellRangeAddress(r, r, 9, 10));
sheet.addMergedRegion(new CellRangeAddress(r, r, 11, 12));
sheet.addMergedRegion(new CellRangeAddress(r, r, 13, 14));
if (c < 3) {
cell.setCellValue(titles[n++]);
} else {
if ((c & 1) == 1) {
System.out.println("c===" + c);
cell.setCellValue(titles[n++]);
}
}
}
// 处理第2~8行
if (r > 0 && r <= 8) {
if (c == 0) {
if (r < 8 && (r & 1) == 1) {
sheet.addMergedRegion(new CellRangeAddress(r, r + 1, 0, 0));
System.err.println("row----->" + r + " len----->" + (len));
cell.setCellValue(colNames[len++]);
} else if (r > 8) {
System.out.println("len+++++++++>" + (len));
cell.setCellValue(colNames[len++]);
}
} else if (c == 1) {
cell.setCellValue((r & 1) == 1 ? today : sign);
System.err.println("r---->" + r);
} else if (c == 2) {
cell.setCellValue((r & 1) == 1 ? "当天" : "当月");
} else {
if ((c & 1) == 1) {
sheet.addMergedRegion(new CellRangeAddress(r, r, c, c + 1));
cell.setCellValue("26.55");
}
}
}
// 处理第8行以后的数据(不包括第8行)
if (r > 8) {
// 设置列高(图片的高度)
if (r % 3 == 0) {
sheet.getRow(r).setHeightInPoints(110);
}
if (c == 0) {
System.err.println("r---->" + r);
cell.setCellValue(colNames[r - 4]);
} else if (c == 1) {
cell.setCellValue((r % 3) == 0 ? today : (r % 3 == 1 ? "PV 浏览量" : "Total Sales 总额"));
} else if (c == 2) {
if (r % 9 == 0) {
sheet.addMergedRegion(new CellRangeAddress(r, r + 8, c, c));
if (r / 9 == 1) cell.setCellValue("当天");
else cell.setCellValue("当月");
cell.setCellStyle(style);
}
} else {
if (r % 3 == 0) {
if ((c & 1) == 1) {
sheet.addMergedRegion(new CellRangeAddress(r, r, c, c + 1));
// 添加远程图片信息
url = new URL(jpgUrls[(c - 3) / 2]);
bufferImg = ImageIO.read(url.openStream());
byteArrayOut = new ByteArrayOutputStream();
ImageIO.write(bufferImg, "jpg", byteArrayOut);
patri = (HSSFPatriarch) sheet.createDrawingPatriarch();
anchor = new HSSFClientAnchor(10, 2, 0, 0, (short) c, r, (short) (c + 2), r + 1);
patri.createPicture(
anchor,
wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
bufferImg.flush();
// link = new HSSFHyperlink(HSSFHyperlink.LINK_URL);
// System.out.println(https[(c-3)/2]);
// link.setAddress("fetion/"+https[(c-3)/2]);
// cell.setHyperlink(link);
// link = (HSSFHyperlink) cell.getHyperlink();
// link = new HSSFHyperlink(HSSFHyperlink.LINK_URL);
// link.setAddress(https[(c-3)/2]);
// cell.setHyperlink(link);
}
} else {
if ((c & 1) == 0) {
link = wb.getCreationHelper().createHyperlink(Hyperlink.LINK_URL);
link.setAddress(https[(c - 3) / 2]);
cell.setHyperlink(link); // 设定单元格的链接
cell.setCellValue("图片超链接");
} else {
cell.setCellValue("Number");
}
}
}
}
}
}
// 备注
row = sheet.createRow(27);
cell = row.createCell(0);
sheet.addMergedRegion(new CellRangeAddress(27, 27, 0, colNum - 1));
cell.setCellValue("* 销量排名不以销售金额计算，如相同销量者，则以PV量少者为优胜");
FileOutputStream out = new FileOutputStream(fileName);
wb.write(out);
out.close();
} catch (Exception e) {
e.printStackTrace();
}
System.out.println("++++++++++++ EXCEl文件 success +++++++++++++");
}
/**
* 复制来源表格中第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();
}
}
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());
}
public void testRemoveShapes() {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet();
HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
HSSFSimpleShape rectangle = patriarch.createSimpleShape(new HSSFClientAnchor());
rectangle.setShapeType(HSSFSimpleShape.OBJECT_TYPE_RECTANGLE);
int idx = wb.addPicture(new byte[] {1, 2, 3}, Workbook.PICTURE_TYPE_JPEG);
patriarch.createPicture(new HSSFClientAnchor(), idx);
patriarch.createCellComment(new HSSFClientAnchor());
HSSFPolygon polygon = patriarch.createPolygon(new HSSFClientAnchor());
polygon.setPoints(new int[] {1, 2}, new int[] {2, 3});
patriarch.createTextbox(new HSSFClientAnchor());
HSSFShapeGroup group = patriarch.createGroup(new HSSFClientAnchor());
group.createTextbox(new HSSFChildAnchor());
group.createPicture(new HSSFChildAnchor(), idx);
assertEquals(patriarch.getChildren().size(), 6);
assertEquals(group.getChildren().size(), 2);
assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getShapeToObjMapping().size(), 12);
assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getTailRecords().size(), 1);
wb = HSSFTestDataSamples.writeOutAndReadBack(wb);
sheet = wb.getSheetAt(0);
patriarch = sheet.getDrawingPatriarch();
assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getShapeToObjMapping().size(), 12);
assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getTailRecords().size(), 1);
assertEquals(patriarch.getChildren().size(), 6);
group = (HSSFShapeGroup) patriarch.getChildren().get(5);
group.removeShape(group.getChildren().get(0));
assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getShapeToObjMapping().size(), 10);
assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getTailRecords().size(), 1);
wb = HSSFTestDataSamples.writeOutAndReadBack(wb);
sheet = wb.getSheetAt(0);
patriarch = sheet.getDrawingPatriarch();
assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getShapeToObjMapping().size(), 10);
assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getTailRecords().size(), 1);
group = (HSSFShapeGroup) patriarch.getChildren().get(5);
patriarch.removeShape(group);
assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getShapeToObjMapping().size(), 8);
assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getTailRecords().size(), 1);
wb = HSSFTestDataSamples.writeOutAndReadBack(wb);
sheet = wb.getSheetAt(0);
patriarch = sheet.getDrawingPatriarch();
assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getShapeToObjMapping().size(), 8);
assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getTailRecords().size(), 1);
assertEquals(patriarch.getChildren().size(), 5);
HSSFShape shape = patriarch.getChildren().get(0);
patriarch.removeShape(shape);
assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getShapeToObjMapping().size(), 6);
assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getTailRecords().size(), 1);
assertEquals(patriarch.getChildren().size(), 4);
wb = HSSFTestDataSamples.writeOutAndReadBack(wb);
sheet = wb.getSheetAt(0);
patriarch = sheet.getDrawingPatriarch();
assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getShapeToObjMapping().size(), 6);
assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getTailRecords().size(), 1);
assertEquals(patriarch.getChildren().size(), 4);
HSSFPicture picture = (HSSFPicture) patriarch.getChildren().get(0);
patriarch.removeShape(picture);
assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getShapeToObjMapping().size(), 5);
assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getTailRecords().size(), 1);
assertEquals(patriarch.getChildren().size(), 3);
wb = HSSFTestDataSamples.writeOutAndReadBack(wb);
sheet = wb.getSheetAt(0);
patriarch = sheet.getDrawingPatriarch();
assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getShapeToObjMapping().size(), 5);
assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getTailRecords().size(), 1);
assertEquals(patriarch.getChildren().size(), 3);
HSSFComment comment = (HSSFComment) patriarch.getChildren().get(0);
patriarch.removeShape(comment);
assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getShapeToObjMapping().size(), 3);
assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getTailRecords().size(), 0);
assertEquals(patriarch.getChildren().size(), 2);
wb = HSSFTestDataSamples.writeOutAndReadBack(wb);
sheet = wb.getSheetAt(0);
patriarch = sheet.getDrawingPatriarch();
assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getShapeToObjMapping().size(), 3);
assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getTailRecords().size(), 0);
assertEquals(patriarch.getChildren().size(), 2);
polygon = (HSSFPolygon) patriarch.getChildren().get(0);
patriarch.removeShape(polygon);
assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getShapeToObjMapping().size(), 2);
assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getTailRecords().size(), 0);
assertEquals(patriarch.getChildren().size(), 1);
wb = HSSFTestDataSamples.writeOutAndReadBack(wb);
sheet = wb.getSheetAt(0);
patriarch = sheet.getDrawingPatriarch();
assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getShapeToObjMapping().size(), 2);
assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getTailRecords().size(), 0);
assertEquals(patriarch.getChildren().size(), 1);
HSSFTextbox textbox = (HSSFTextbox) patriarch.getChildren().get(0);
patriarch.removeShape(textbox);
assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getShapeToObjMapping().size(), 0);
assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getTailRecords().size(), 0);
assertEquals(patriarch.getChildren().size(), 0);
wb = HSSFTestDataSamples.writeOutAndReadBack(wb);
sheet = wb.getSheetAt(0);
patriarch = sheet.getDrawingPatriarch();
assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getShapeToObjMapping().size(), 0);
assertEquals(HSSFTestHelper.getEscherAggregate(patriarch).getTailRecords().size(), 0);
assertEquals(patriarch.getChildren().size(), 0);
}
/**
* 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);
}
public void testCorrectOrderInOptRecord() throws IOException {
HSSFWorkbook wb = new HSSFWorkbook();
try {
HSSFSheet sheet = wb.createSheet();
HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
HSSFTextbox textbox = patriarch.createTextbox(new HSSFClientAnchor());
EscherOptRecord opt = HSSFTestHelper.getOptRecord(textbox);
String opt1Str = opt.toXml();
textbox.setFillColor(textbox.getFillColor());
EscherContainerRecord container = HSSFTestHelper.getEscherContainer(textbox);
EscherOptRecord optRecord = container.getChildById(EscherOptRecord.RECORD_ID);
assertEquals(opt1Str, optRecord.toXml());
textbox.setLineStyle(textbox.getLineStyle());
assertEquals(opt1Str, optRecord.toXml());
textbox.setLineWidth(textbox.getLineWidth());
assertEquals(opt1Str, optRecord.toXml());
textbox.setLineStyleColor(textbox.getLineStyleColor());
assertEquals(opt1Str, optRecord.toXml());
} finally {
wb.close();
}
}
