fabricequ
Reputation: 1
How can I insert more than one picture in Excel file with Java Apache poi?
I create a java to generate excel file with cells containing picture jpg. A picture by row Only the last picture on the last line appears
A part of my java coding in the second part
Coul you help to obtain all picture ?
Best regards
import java.net.URL;
import java.net.URLConnection;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.*;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
@SuppressWarnings("serial")
public class ExcelCaracAssocModelFileBatch extends AbstractBusinessBatch {
public int rowIdx = 0;
public int rowId1 = 0;
public short cellIdx = 0;
public short cellId2 = 15;
public String _item_codref = ""; /
private HSSFWorkbook wb = new HSSFWorkbook();
private HSSFSheet sheet1 = wb.createSheet("CONTEXT");
private HSSFRow hssfHeader;
private HSSFRow hssfHead2;
private HSSFSheet sheet = wb.createSheet("DATA");
private HSSFCellStyle cellStyle = wb.createCellStyle();
private HSSFCellStyle cellStyle2 = wb.createCellStyle();
public HSSFRow r2;
private HSSFPicture my_picture;
private HSSFCell Celldescarac;
private HSSFCell Cellcodcarac;
public ExcelCaracAssocModelFileBatch(ContexteMetier contexteMetier) {
super(contexteMetier);
}
}
@SuppressWarnings("deprecation")
public RowSetStateHolder select(ParameterAccess parameterAccess) throws CoreBatchException {
HSSFFont font1 = wb.createFont();
font1.setFontHeightInPoints((short)14);
font1.setFontName("Arial");
font1.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
cellStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM);
cellStyle.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
cellStyle.setBorderTop(HSSFCellStyle.BORDER_MEDIUM);
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
cellStyle.setFont(font1);
cellStyle2.setAlignment(HSSFCellStyle.ALIGN_LEFT);
cellStyle2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
cellStyle2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
cellStyle2.setBorderRight(HSSFCellStyle.BORDER_THIN);
cellStyle2.setBorderTop(HSSFCellStyle.BORDER_THIN);
cellStyle2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
SqlBindingHelper sql = new SqlBindingHelper();
sql.appendSql("SELECT w_1, w_2, w_3, w_4, w_5, w_6, w_7, w_8, w_9, w_10,");
sql.appendSql("w_11, w_12, w_13, w_14, w_15, w_16, w_17, w_18, w_19, w_20,");
sql.appendSql("w_21, w_22, w_23, w_24, w_25, w_26, w_27, w_28, w_29, w_30,");
sql.appendSql("num01, num02");
sql.appendSql(" FROM PHX_GDT2_CARAS3 ");
sql.appendSql(" WHERE ");
sql.appendSql(" w_1 = ? "); sql.addParamValue(_w_1);
sql.addParamValue( _w_2 );
} */
return createRowSetStateHolderFromSql(sql);
}
public long execute(RowSetStateHolder rowSetStateHolder) throws CoreBatchException {
int cpt = 0;
Row row;
while(rowSetStateHolder.getRowSet().hasNext())
{
row = rowSetStateHolder.getRowSet().next();
String item_cod = (String)row.getAttribute("w_8");
rowIdx = rowId1++;
r2 = sheet.createRow(rowIdx);
r2.setHeight((short) 1800);
HSSFCell r2c0 = r2.createCell((short) 0);
r2c0.setCellValue((String)row.getAttribute("w_3"));
r2c0.setCellStyle(cellStyle2);
HSSFCell r2c1 = r2.createCell((short) 1);
r2c1.setCellValue((String)row.getAttribute("w_4"));
r2c1.setCellStyle(cellStyle2);
HSSFCell r2c2 = r2.createCell((short) 2);
r2c2.setCellValue((String)row.getAttribute("w_5"));
r2c2.setCellStyle(cellStyle2);
HSSFCell r2c3 = r2.createCell((short) 3);
r2c3.setCellValue((String)row.getAttribute("w_6"));
r2c3.setCellStyle(cellStyle2);
HSSFCell r2c4 = r2.createCell((short) 4);
r2c4.setCellValue((String) row.getAttribute("w_27"));
r2c4.setCellStyle(cellStyle2);
HSSFCell r2c5 = r2.createCell((short) 5);
r2c5.setCellValue((String)row.getAttribute("w_18"));
r2c5.setCellStyle(cellStyle2);
HSSFCell r2c6 = r2.createCell((short) 6);
r2c6.setCellValue((String)row.getAttribute("w_19"));
r2c6.setCellStyle(cellStyle2);
HSSFCell r2c7 = r2.createCell((short) 7);
r2c7.setCellValue((String)row.getAttribute("w_8"));
r2c7.setCellStyle(cellStyle2);
HSSFCell r2c8 = r2.createCell((short) 8);
r2c8.setCellValue((String) row.getAttribute("w_9"));
r2c8.setCellStyle(cellStyle2);
HSSFCell r2c9 = r2.createCell((short) 9);
r2c9.setCellValue((String) row.getAttribute("w_10"));
r2c9.setCellStyle(cellStyle2);
HSSFCell r2c10 = r2.createCell((short) 10);
r2c10.setCellValue((String) row.getAttribute("w_11"));
r2c10.setCellStyle(cellStyle2);
HSSFCell r2c11 = r2.createCell((short) 11);
r2c11.setCellValue((String) row.getAttribute("w_12"));
r2c11.setCellStyle(cellStyle2);
HSSFCell r2c12 = r2.createCell((short) 12);
r2c12.setCellValue((String) row.getAttribute("w_13"));
r2c12.setCellStyle(cellStyle2);
String urlmedia = (String) row.getAttribute("w_30");
try {
//test url image
URL url = new URL((String) row.getAttribute("w_30"));
URLConnection connection = url.openConnection();
int fileLength = connection.getContentLength();
// URL ok
if (fileLength != -1)
{
InputStream input = null;
input = connection.getInputStream();
//Get the contents of an InputStream as a byte[].
//byte[] bytes = IOUtils.toByteArray(input);
ByteArrayOutputStream img_bytes = new ByteArrayOutputStream();
int b;
while ((b = input.read()) != -1)
img_bytes.write(b);
input.close();
//Adds a picture to the workbook
//int pictureIdx = wb.addPicture(bytes, HSSFWorkbook.PICTURE_TYPE_JPEG);
int pictureIdx = wb.addPicture(img_bytes.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG);
//close the input stream
input.close();
/* Create the drawing container */
HSSFPatriarch drawing = sheet.createDrawingPatriarch();
/* Create an anchor point */
/*
dx1 - the x coordinate within the first cell.
dy1 - the y coordinate within the first cell.
dx2 - the x coordinate within the second cell.
dy2 - the y coordinate within the second cell.
col1 - the column (0 based) of the first cell.
row1 - the row (0 based) of the first cell.
col2 - the column (0 based) of the second cell.
row2 - the row (0 based) of the second cell.
*/
HSSFClientAnchor my_anchor = new HSSFClientAnchor();
my_anchor.setDx1(10);
my_anchor.setDx2(10);
my_anchor.setDy1(10);
my_anchor.setDy2(10);
short pictcol1 = (short) rowIdx;
short pictcol2 = (short) (pictcol1 + 1);
my_anchor.setCol1((short) 13);
my_anchor.setCol2((short) 14);
my_anchor.setRow1(pictcol1);
my_anchor.setRow2(pictcol2);
my_anchor.setAnchorType((int) 2);
setMy_picture(drawing.createPicture(my_anchor, pictureIdx));
}
}
catch (IOException e)
{
_log.info("Error while trying to download the file "+ urlmedia, e );
}
cellIdx = 14;
_item_codref = item_cod;
}
cellId2 = cellIdx++;
HSSFCell r2c4 = r2.createCell(cellId2);
r2c4.setCellValue((String)row.getAttribute("w_17"));
r2c4.setCellStyle(cellStyle2);
}
}
cpt++;
}
return cpt;
}
@Override
public synchronized void tearDown() throws CoreBatchException {
try
{
//Write the Excel file
String nomfic = FILE_NAME_PREFIX + "_" + getCtxMetier().getNumedi() + ".xls";
FileOutputStream fileOut = null;
fileOut = new FileOutputStream(nomfic);
wb.write(fileOut);
fileOut.close();
}
catch (Exception e)
{
_log.error(e);
throw new CoreBatchException(e);
}
finally
{
super.tearDown();
}
}
Upvotes: 0
Views: 1781
Answers (1)
rgettman
Reputation: 178243
The createDrawingPatriarch method can have the effect of removing all drawings, including pre-existing images, on the Sheet.
Note that this will normally have the effect of removing any existing drawings on this sheet.
You are calling createDrawingPatriarch inside a while loop in your execute method, so your previous images are getting removed in each iteration, and only the last image survives.
To have all your images intact, create the patriarch object once, before the while loop, and reuse it in each iteration of your while loop.
/* Create the drawing container */
HSSFPatriarch drawing = sheet.createDrawingPatriarch();
while(rowSetStateHolder.getRowSet().hasNext())
{
// Other code...
// Use it here as normal.
setMy_picture(drawing.createPicture(my_anchor, pictureIdx));
}
Upvotes: 2
Related Questions
- How to import multiple image into Excel by Java POI
- First image is getting remove while Inserting multiple Images into the Excel using Apache POI
- Insert image in column to Excel using Apache POI
- How put a image in a cell of excel java?
- Apache POI insert image
- Apache POI write image and text excel
- insert image into excel with Apache-poi
- images are inserted to excel file by Apache POI ,but they are not shown
- Not able to insert image into excelsheet
- Problems with POI when I add more images