Generate simple Excel files
In real offices, we often have a requirement: we require the report to be opened directly in Excel. There is such a need during internships. Query all the information of the attachment provided by the user based on the selected resource and generate excel for download. However, when querying, we need to check whether there is any error in the information in the attachment provided by the user (ID card). There is an error generating error message Excel.
Apache's POI project is currently a relatively mature HSSF interface used to process Excel objects. In fact, POI not only handles excel, it can also handle word, PowerPoint, Visio, and even Outlook.
Here I will first introduce how to generate excel using POI.
First of all, before generating Excel, we need to understand the organization of Excel files. In POI, it is understood as follows: an Excel file corresponds to a workbook, and a workerbook consists of several sheets. A sheet has multiple rows, and a row generally has multiple cells.
For the above four nouns we can understand in the figure below
For generating Excel, POI provides the following basic objects:
From the above picture and Excel organizational structure, we can understand the steps to create Excel.
1. Generate document object HSSHWorkbook.
2. Generate form HSSFSheet through HSSFWorkbook.
3. Generate rows through HSSFSheet
4. Generate cell HSSFCell through HSSFRow.
Here is the display code:
Identity card error Bean (ErrorCondition.java)
public class ErrorCondition { private String name; // Name private String idCard; // ID card private String status; // Error status private String message; // Error message ErrorCondition(String name,String idCard,String status,String message){ this.name = name; this.idCard = idCard; this.status = status; this.message = message; } public String getName() { return name; } public void setName(String name) { this.name = name; } public String getIdCard() { return idCard; } public void setIdCard(String idCard) { this.idCard = idCard; } public String getStatus() { return status; } public void setStatus(String status) { this.status = status; } public String getMessage() { return message; } public void setMessage(String message) { this.message = message; } }Processing class (ExportErrorExcel.java)
public class ExportErrorExcel { public static void main(String[] args) { //Step 1 create workbook HSSFWorkbook wb = new HSSFWorkbook(); //Step 2 create sheet HSSFSheet sheet = wb.createSheet("ID card error message"); //Step 3 create row row: add 0 rows of table header HSSFRow row = sheet.createRow(0); HSSFCellStyle style = wb.createCellStyle(); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); //Center//Step 4: Create cell HSSFCell cell = row.createCell(0); //The first cell cell.setCellValue("name"); //Set value cell.setCellStyle(style); //Center content cell = row.createCell(1); //The second cell cell.setCellValue("ID card"); cell.setCellStyle(style); cell = row.createCell(2); //The third cell cell.setCellValue("Error status"); cell.setCellStyle(style); cell = row.createCell(3); //The fourth cell cell.setCellValue("Error message"); cell.setCellStyle(style); //The fifth step inserts data List<ErrorCondition> list = ExportErrorExcel.getErrorCondition(); for (int i = 0; i < list.size(); i++) { ErrorCondition errorCondition = list.get(i); //Create row = sheet.createRow(i+1); //Create cell and add data row.createCell(0).setCellValue(errorCondition.getName()); row.createCell(1).setCellValue(errorCondition.getIdCard()); row.createCell(2).setCellValue(errorCondition.getStatus()); row.createCell(3).setCellValue(errorCondition.getMessage()); } //Step 6 Save the generated excel file to the specified path try { FileOutputStream fout = new FileOutputStream("D://errorCondition.xls"); wb.write(fout); fout.close(); } catch (IOException e) { e.printStackTrace(); } System.out.println("Excel file generation successfully..."); } public static List<ErrorCondition> getErrorCondition(){ List<ErrorCondition> list = new ArrayList<ErrorCondition>(); ErrorCondition r1 = new ErrorCondition("Zhang San", "4306821989021611", "L", "Length Error"); ErrorCondition r2 = new ErrorCondition("Li Si", "430682198902191112","X", "Check error"); ErrorCondition r3 = new ErrorCondition("Wang Wu", "", "N", "Identity card information is empty"); list.add(r1); list.add(r2); list.add(r3); return list; } } Through the above six steps, you can generate Excel files in the specified location.
Java POI implementation inserts pictures into Excel
Doing web development is inevitable to deal with Excel. Today, the boss gave me a task - export Excel. What I thought at first was quite simple, it was just searching, building Excel, and downloading response. But there is one difference, that is, you need to add pictures, it’s this one that has been added for a long time. At the same time, no good information was found on the Internet, so I wrote this blog post to record it for my own and my blog friends to query and reference.
There is an HSSFPatriarch object in POI, which is the top manager for drawing. Its createPicture(anchor, pictureIndex) method can insert a picture in Excel. So you need to insert a picture in Excel and you can do it in three steps. 1. Get the HSSFPatriarch object, 2. New HSSFClientAnchor object, 3. Just call the createPicture method. Implementation is very easy to implement, but it is still a bit difficult to do it well. Here we first insert a picture:
public class ExcelImageTest { public static void main(String[] args) { FileOutputStream fileOut = null; BufferedImage bufferImg = null; //First put the read in the picture into a ByteArrayOutputStream to generate ByteArray try { ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream(); bufferImg = ImageIO.read(new File("F:/Picture/Anounty/Xiaozhao11.jpg")); ImageIO.write(bufferImg, "jpg", byteArrayOut); HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet1 = wb.createSheet("test picture"); //The top manager for drawing can only get one sheet (be sure to pay attention to this point) HSSFPatriarch patriarch = sheet1.createDrawingPatriarch(); //anchor is mainly used to set the attributes of pictures HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 255, 255,(short) 1, 1, (short) 5, 8); anchor.setAnchorType(3); //Insert the picture patriarch.createPicture(anchor, wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG)); fileOut = new FileOutputStream("D:/Test Excel.xls"); // Write to the excel file wb.write(fileOut); System.out.println("--------"); } catch (Exception e) { e.printStackTrace(); } finally{ if(fileOut != null){ try { fileOut.close(); } catch (IOException e) { e.printStackTrace(); } } } } } } } } } } The following are the results after execution:
As for why this result is mainly because of HSSFClientAnchor(0, 0, 255, 255, (short) 1, 1, (short) 5, 8) constructor. Let me explain this constructor below: HSSFClientAnchor(int dx1,int dy1,int dx2,int dy2, short col1,int row1,short col2, int row2); The meanings of each parameter are as follows:
Here dx1 and dy1 define the starting position of the image at the beginning cell, and dx2 and dy2 define the end position of the end cell. col1 and row1 define the start cell, col2 and row2 define the end cell.
The following is created with two different constructors. From this picture, we can clearly see the meaning and differences of the above eight parameters.
The above is to insert one picture, so what about inserting multiple pictures? In fact, it is very simple. Construct multiple different HSSFClientAnchor objects and control those eight parameters, as follows:
HSSFClientAnchor anchor1 = new HSSFClientAnchor(0, 0, 1023,100,(short) 1, 1, (short)5, 8); HSSFClientAnchor anchor2 = new HSSFClientAnchor(0, 0, 1023,100,(short) 1, 9, (short)5, 16); //Insert the picture patriarch.createPicture(anchor1, wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG)); patriarch.createPicture(anchor2, wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
The rest of the code is the same, and the following results are obtained: