Export data and images to an Excel file in Java

Public ActionForward picExcel (ActionMapping mapping, ActionForm form,
HttpServletRequest request, HttpServletResponse response) throws Exception {
Response. reset ();
Response. setContentType ("application/vnd. ms-excel ");
ServletContext context = this. getServlet (). getServletContext ();
Try {
String path = context. getRealPath ("/reports/hab/apsfa/PicStat.xls"); // output path of the exported Excel file on the server
Java. io. File reportFile1 =
New File (context. getRealPath ("/reports/hab/apsfa/statPic.xls"); // This Excel File is on the server, an Excel template containing a Statistical Image
Java. io. FileInputStream fos = new FileInputStream (reportFile1); // file input stream
Java. io. FileOutputStream OS = new FileOutputStream (path); // file output stream

String barName =
FileHelper. getUploadDir () + "/" + "collentResult2.jpg"; // path of the image to be exported to the Excel file

Org. apache. poi. poifs. filesystem. POIFSFileSystem fs = new POIFSFileSystem (fos );
Org. apache. poi. hssf. usermodel. HSSFWorkbook wb = new HSSFWorkbook (fs );
Org. apache. poi. hssf. usermodel. HSSFSheet sheet = wb. getSheetAt (0); // The first worksheet in the Excel file
Org. apache. poi. hssf. usermodel. HSSFPatriarch patriarch = sheet. createDrawingPatriarch ();
Org. apache. poi. hssf. usermodel. HSSFClientAnchor anchor = new HSSFClientAnchor (
350,100, (short), (short );

HkSfaStatBC bc = new HkSfaStatBCImpl (); // background implementation class -- Result Statistics
HkSfaActivityBC abc = new HkSfaActivityBCImpl (); // background implementation class-audit activity
String activitypk = request. getParameter ("activitypk"); // activity PK
List childGroups = abc. getChildgroup (activitypk); // obtain all groups for this activity

Double [] [] data = bc. problemCollect (activitypk); // get the statistical result, a two-dimensional array

Org. apache. poi. hssf. usermodel. HSSFRow row = sheet. createRow (11); // create a row (12th rows) in the first worksheet)
For (int k = 0; k <childGroups. size (); k ++ ){
ApSfaGroupactivity activityGroup = (ApSfaGroupactivity) childGroups. get (k); // ApSfaGroupactivity is the Pojo of the activity group
Org. apache. poi. hssf. usermodel. HSSFCell cell = row. createCell (short) (k + 1); // create a cell in row
Cell. setEncoding (HSSFCell. ENCODING_UTF_16); // sets the encoding.
Cell. setCellValue (activityGroup. getName (); // set the value for the cell.
}

/* Add data to excel in the same cycle as the previous one */
String [] STRs = {"", "", "selected total "};
For (INT I = 12; I <STRs. Length + 12; I ++ ){
Org. Apache. Poi. hssf. usermodel. hssfrow row1 = sheet. createrow (I );
For (Int J = 0; j <data [0]. Length + 1; j ++ ){
Org. Apache. Poi. hssf. usermodel. hssfcell cell = row1.createcell (short) J );
Cell. setencoding (hssfcell. encoding_utf_16 );
If (j = 0 ){
Cell. setcellvalue (STRs [I-12]);
} Else {
Int A = (INT) data [I-12] [J-1];
Cell. setCellValue ();
}
}
}

Java. io. File jpgfile = new File (barName); // barName indicates the path of the statistics image on the server.
Org. apache. commons. io. output. ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream (); // byte output stream, used to write binary files
Java. awt. image. BufferedImage bufferImg = ImageIO. read (jpgfile );
Javax. imageio. ImageIO. write (bufferImg, "jpg", byteArrayOut );

Patriarch. createPicture (anchor, wb. addPicture (
ByteArrayOut. toByteArray (), HSSFWorkbook. PICTURE_TYPE_JPEG); // Add the statistical image to the Excel file.
Wb. write (OS );
OS. close ();
Net. sf. excelutils. ExcelUtils. addValue ("actionServlet", this );
String config = null;

Config = "/reports/hab/apsfa/PicStat.xls ";
Response. setHeader ("Content-Disposition", "attachment; filename =/" "+" PicStat.xls /"");
Net. sf. excelutils. ExcelUtils. export (getServlet (). getServletContext (), config, response. getOutputStream ());
} Catch (FileNotFoundException e ){
E. printStackTrace ();
} Catch (IOException e ){
E. printStackTrace ();
}
Return null;
}

/*

Jar packages and excel files are available in my resources. download them,

Address: http://download.csdn.net/source/313015

*/