Sun River
          Topics about Java SE, Servlet/JSP, JDBC, MultiThread, UML, Design Pattern, CSS, JavaScript, Maven, JBoss, Tomcat, ...
          posts - 78,comments - 0,trackbacks - 0

           

          如何將JSP中將查詢結果導出為Excel,其實可以利用jakarta提供的POI接口將查詢結果導出到excel。POI接口是jakarta組織的一個子項目,它包括POIFS,HSSF,HWSF,HPSF,HSLF,目前比較成熟的是HSSF,它是一組操作微軟的excel文檔的API,現在到達3.0版本,已經能夠支持將圖片插入到excel里面。java 代碼
          import java.io.*;   
          1. import org.apache.poi.hssf.usermodel.*;   
          2. import org.apache.poi.hssf.util.*;   
          3. import org.apache.poi.hssf.usermodel.contrib.*;   
          4. import javax.imageio.ImageIO;   
          5.   
          6. public class Main {   
          7.     public Main() {   
          8.     }   
          9.     //演示如何創建一個工作簿   
          10.     //用這種方法創建的工作簿有問題,用Excel打開以后發現下面Tab沒有選中   
          11.     public static void ex1() {   
          12.         try {   
          13.             org.apache.poi.hssf.usermodel.HSSFWorkbook wb = new org.apache.poi.   
          14.                 hssf.usermodel.HSSFWorkbook();   
          15.             java.io.FileOutputStream fileOut = new java.io.FileOutputStream(   
          16.                 "ex1.xls");   
          17.             wb.write(fileOut);   
          18.             fileOut.close();   
          19.         }   
          20.         catch (Exception eee) {   
          21.             eee.printStackTrace();   
          22.         }   
          23.     }   
          24.     //演示如何創建一個工作表   
          25.     public static void ex2() {   
          26.         try {   
          27.             org.apache.poi.hssf.usermodel.HSSFWorkbook wb = new org.apache.poi.   
          28.                 hssf.usermodel.HSSFWorkbook();   
          29.             org.apache.poi.hssf.usermodel.HSSFSheet sheet1 = wb.createSheet(   
          30.                 "new sheet");   
          31.             org.apache.poi.hssf.usermodel.HSSFSheet sheet2 = wb.createSheet(   
          32.                 "second sheet");   
          33.             java.io.FileOutputStream fileOut = new java.io.FileOutputStream(   
          34.                 "ex2.xls");   
          35.             wb.write(fileOut);   
          36.             fileOut.close();   
          37.         }   
          38.         catch (Exception eee) {   
          39.             eee.printStackTrace();   
          40.         }   
          41.     }   
          42.     //演示如何創建一個單元格   
          43.     public static void ex3() {   
          44.         try {   
          45.             org.apache.poi.hssf.usermodel.HSSFWorkbook wb = new org.apache.poi.   
          46.                 hssf.usermodel.HSSFWorkbook();   
          47.             org.apache.poi.hssf.usermodel.HSSFSheet sheet = wb.createSheet(   
          48.                 "new sheet");   
          49.             // Create a row and put some cells in it. Rows are 0 based.   
          50.             org.apache.poi.hssf.usermodel.HSSFRow row = sheet.createRow( (short)   
          51.                 0);   
          52.             // Create a cell and put a value in it.   
          53.             org.apache.poi.hssf.usermodel.HSSFCell cell = row.createCell( (short)   
          54.                 0);   
          55.             cell.setCellValue(1);   
          56.             // Or do it on one line.   
          57.             row.createCell( (short1).setCellValue(1.2);   
          58.             row.createCell( (short2).setCellValue("This is a string");   
          59.             row.createCell( (short3).setCellValue(true);   
          60.             // Write the output to a file   
          61.             java.io.FileOutputStream fileOut = new java.io.FileOutputStream(   
          62.                 "ex3.xls");   
          63.             wb.write(fileOut);   
          64.             fileOut.close();   
          65.         }   
          66.         catch (Exception eee) {   
          67.             eee.printStackTrace();   
          68.         }   
          69.     }   
          70.     //演示如何操作自選圖形   
          71.     public static void ex27() {   
          72.         try {   
          73.             HSSFWorkbook wb = new HSSFWorkbook();   
          74.             HSSFSheet sheet = wb.createSheet("new sheet");   
          75.             HSSFPatriarch patriarch = sheet.createDrawingPatriarch();   
          76.             HSSFClientAnchor a = new HSSFClientAnchor(001023255,   
          77.                 (short10, (short10);   
          78.             HSSFSimpleShape shape1 = patriarch.createSimpleShape(a);   
          79.             shape1.setShapeType(HSSFSimpleShape.OBJECT_TYPE_LINE);   
          80.             HSSFTextbox textbox1 = patriarch.createTextbox(   
          81.                 new HSSFClientAnchor(0000, (short11, (short22));   
          82.             textbox1.setString(new HSSFRichTextString("This is a test"));   
          83.             HSSFFont font = wb.createFont();   
          84.             font.setItalic(true);   
          85.             font.setUnderline(HSSFFont.U_DOUBLE);   
          86.             HSSFRichTextString string = new HSSFRichTextString("Woo!!!");   
          87.             string.applyFont(25, font);   
          88.             textbox1.setString(string);   
          89.             // Create a shape group.   
          90.             HSSFShapeGroup group = patriarch.createGroup(   
          91.                 new HSSFClientAnchor(00900200, (short22, (short22));   
          92.             // Create a couple of lines in the group.   
          93.             shape1 = group.createShape(new HSSFChildAnchor(33500500));   
          94.             shape1.setShapeType(HSSFSimpleShape.OBJECT_TYPE_LINE);   
          95.             ( (HSSFChildAnchor) shape1.getAnchor()).setAnchor( (short33,   
          96.                 500500);   
          97.             HSSFSimpleShape shape2 = group.createShape(new HSSFChildAnchor( (short)   
          98.                 1200400600));   
          99.             shape2.setShapeType(HSSFSimpleShape.OBJECT_TYPE_LINE);   
          100.             group.setCoordinates(10102020); // top-left, bottom-right   
          101.             FileOutputStream fileOut = new FileOutputStream("ex27.xls");   
          102.             wb.write(fileOut);   
          103.             fileOut.close();   
          104.         }   
          105.         catch (Exception eee) {   
          106.             eee.printStackTrace();   
          107.         }   
          108.     }   
          109.     //演示如何設定outline   
          110.     public static void ex28() {   
          111.         try {   
          112.             HSSFWorkbook wb = new HSSFWorkbook();   
          113.             HSSFSheet sheet1 = wb.createSheet("new sheet");   
          114.             sheet1.groupRow(514);   
          115.             sheet1.groupRow(714);   
          116.             sheet1.groupRow(1619);   
          117.             sheet1.groupColumn( (short4, (short7);   
          118.             sheet1.groupColumn( (short9, (short12);   
          119.             sheet1.groupColumn( (short10, (short11);   
          120.             FileOutputStream fileOut = new FileOutputStream("ex28.xls");   
          121.             wb.write(fileOut);   
          122.             fileOut.close();   
          123.         }   
          124.         catch (Exception eee) {   
          125.             eee.printStackTrace();   
          126.         }   
          127.     }   
          128.     //演示如何設定outline   
          129.     public static void ex29() {   
          130.         try {   
          131.             HSSFWorkbook wb = new HSSFWorkbook();   
          132.             HSSFSheet sheet = wb.createSheet("new sheet");   
          133.             HSSFPatriarch patriarch = sheet.createDrawingPatriarch();   
          134.             HSSFClientAnchor a = new HSSFClientAnchor(001023255,   
          135.                 (short10, (short10);   
          136.             HSSFShapeGroup group = patriarch.createGroup(a);   
          137.             group.setCoordinates(0080 * 412 * 23);   
          138.             float verticalPointsPerPixel = a.getAnchorHeightInPoints(sheet) /   
          139.                 (float) Math.abs(group.getY2() - group.getY1());   
          140.             EscherGraphics g = new EscherGraphics(group, wb,   
          141.                                                   java.awt.Color.black,   
          142.                                                   verticalPointsPerPixel);   
          143.             EscherGraphics2d g2d = new EscherGraphics2d(g);   
          144.             //drawChemicalStructure( g2d );   
          145.             FileOutputStream fileOut = new FileOutputStream("ex29.xls");   
          146.             wb.write(fileOut);   
          147.             fileOut.close();   
          148.         }   
          149.         catch (Exception eee) {   
          150.             eee.printStackTrace();   
          151.         }   
          152.     }   
          153.     //演示如何處理圖片   
          154.     public static void ex30() {   
          155.         try {   
          156.             HSSFWorkbook wb = new HSSFWorkbook();   
          157.             HSSFSheet sheet = wb.createSheet("new sheet");   
          158.             HSSFPatriarch patriarch = sheet.createDrawingPatriarch();   
          159.             HSSFClientAnchor anchor;   
          160.             anchor = new HSSFClientAnchor(000255, (short22, (short4,   
          161.                                           7);   
          162.             anchor.setAnchorType(2);   
          163.             patriarch.createPicture(anchor,   
          164.                                     loadPicture(   
          165.                 "1.jpg",   
          166.                wb));   
          167.             FileOutputStream fileOut = new FileOutputStream("ex30.xls");   
          168.             wb.write(fileOut);   
          169.             fileOut.close();   
          170.         }   
          171.         catch (Exception eee) {   
          172.             eee.printStackTrace();   
          173.         }   
          174.     }   
          175.     private static int loadPicture(String filePath, HSSFWorkbook wb) {   
          176.         int result = 0;   
          177.         try {   
          178.             ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();   
          179.             java.awt.image.BufferedImage bufferImg = ImageIO.read(new File(   
          180.                 filePath));   
          181.             ImageIO.write(bufferImg,"jpg",byteArrayOut);   
          182.             result = wb.addPicture(byteArrayOut.toByteArray(),HSSFWorkbook.PICTURE_TYPE_JPEG);   
          183.         }   
          184.         catch (Exception e) {   
          185.             e.printStackTrace();   
          186.         }   
          187.         return result;   
          188.     }   
          189. }  

           

          java中利用poi創建和讀取excel文件

                                            

              前面做了個項目,要求利用java動態讀取數據庫中的數據到excel文件供客戶下載,同時把客戶上傳的excel文件讀入到數據庫中。

              查了些資料,最后用的是poi-2.5.1-final-20040804.jar,完全可以滿足要求。下面以一段程序為例,說明poi的用法:

          /*
           * Created on 2006-8-4
           *
           * TODO To change the template for this generated file go to
           * Window - Preferences - Java - Code Style - Code Templates
           */
          package com.crm.web.action;

          import java.io.File;
          import java.io.FileInputStream;
          import java.io.FileOutputStream;
          import java.util.Calendar;
          import java.util.Vector;

          import javax.servlet.http.HttpServletRequest;
          import javax.servlet.http.HttpServletResponse;

          /*這一塊導入相關類*/

          import org.apache.poi.hssf.usermodel.HSSFCell;
          import org.apache.poi.hssf.usermodel.HSSFRow;
          import org.apache.poi.hssf.usermodel.HSSFSheet;
          import org.apache.poi.hssf.usermodel.HSSFWorkbook;

          /*這一塊導入相關類*/
          import org.apache.struts.action.Action;
          import org.apache.struts.action.ActionForm;
          import org.apache.struts.action.ActionForward;
          import org.apache.struts.action.ActionMapping;

          import com.crm.dao.CompanyAccountsDAO;
          import com.crm.dao.CustomerDAO;
          import com.crm.dao.CustomerLinkmanDAO;
          import com.crm.dao.MarketOpportunityDAO;
          import com.crm.dao.MembersDAO;
          import com.crm.dao.OrderDAO;
          import com.crm.dao.QuotePriceDAO;
          import com.crm.dao.ScheduleDAO;
          import com.crm.dto.CustomerDTO;
          import com.crm.web.form.CustomerForm;

          /**
           * @author new
           *
           * TODO To change the template for this generated type comment go to Window -
           * Preferences - Java - Code Style - Code Templates
           */
          public class CustomerAction extends Action {
           public ActionForward execute(ActionMapping mapping, ActionForm form,
             HttpServletRequest request, HttpServletResponse response) {
            CustomerForm customerForm = (CustomerForm) form;
            if (customerForm.getOperType() != null
              && customerForm.getOperType().equals("exportCustomer")) {
             /* 導出客戶 */
             String companyID = (String) request.getSession().getValue(
               "companyID");
             CustomerDAO customerDAO = new CustomerDAO();
             String accountsType = (String) request.getSession().getValue(
               "accountsType");
             String accounts = (String) request.getSession()
               .getValue("accounts");
             String department = (String) request.getSession().getValue(
               "department");
             Vector customerList = new Vector();
             if (accountsType.equals("1")) {
              customerList = ((Vector) customerDAO.findEmployeeCustomer(
                Integer.parseInt(accounts), "0"));
             } else if (accountsType.equals("2")) {
              customerList = ((Vector) customerDAO.findDepartmentCustomer(
                Integer.parseInt(companyID), Integer
                  .parseInt(department), "0"));
             } else if (accountsType.equals("3")) {
              customerList = ((Vector) customerDAO.findCompanyCustomer(
                Integer.parseInt(companyID), "0"));
             }
             try {
              // 創建新的Excel 工作簿
              HSSFWorkbook workbook = new HSSFWorkbook();
              // 在Excel工作簿中建一工作表,其名為缺省值
              // 如要新建一名為"效益指標"的工作表,其語句為:
              // HSSFSheet sheet = workbook.createSheet("效益指標");

          /*或者不提供參數,創建一個無名工作表*/
              HSSFSheet sheet = workbook.createSheet();
              // 在索引0的位置創建行(最頂端的行)
              HSSFRow row = sheet.createRow((short) 0);
              //在索引0的位置創建單元格(左上端)
              HSSFCell cell;
              //= row.createCell((short) 0);
              // 定義單元格為字符串類型
              //cell.setCellType(HSSFCell.CELL_TYPE_STRING);
              //設置cell編碼解決中文高位字節截斷
              //cell.setEncoding(HSSFCell.ENCODING_UTF_16);
              // 在單元格中輸入一些內容
              int i = 0;
              cell = row.createCell((short) i++);
              cell.setCellType(HSSFCell.CELL_TYPE_STRING);
              cell.setEncoding(HSSFCell.ENCODING_UTF_16);
              cell.setCellValue("客戶信息ID(customerInformationID)");
              cell = row.createCell((short) i++);
              cell.setCellType(HSSFCell.CELL_TYPE_STRING);
              cell.setEncoding(HSSFCell.ENCODING_UTF_16);
              cell.setCellValue("公司名稱(我的公司)companyName");
              cell = row.createCell((short) i++);
              cell.setCellType(HSSFCell.CELL_TYPE_STRING);
              cell.setEncoding(HSSFCell.ENCODING_UTF_16);
              cell.setCellValue("客戶名稱customerName");
              cell = row.createCell((short) i++);
              cell.setCellType(HSSFCell.CELL_TYPE_STRING);
              cell.setEncoding(HSSFCell.ENCODING_UTF_16);
              cell.setCellValue("客戶簡稱nameForShort");
              cell = row.createCell((short) i++);
              cell.setCellType(HSSFCell.CELL_TYPE_STRING);
              cell.setEncoding(HSSFCell.ENCODING_UTF_16);
              cell.setCellValue("公司地址companyAddress");

             // 下面是不斷從數據庫中讀數據到sheet中

                for (int j = 0; j < customerList.size(); j++) {
               CustomerDTO customerDTO = (CustomerDTO) customerList.get(j);
               i = 0;
               row = sheet.createRow((short) j + 1);
               cell = row.createCell((short) i++);
               cell.setCellType(HSSFCell.CELL_TYPE_STRING);
               cell.setEncoding(HSSFCell.ENCODING_UTF_16);
               cell.setCellValue(customerDTO.getCustomerInformationID());
               cell = row.createCell((short) i++);
               cell.setCellType(HSSFCell.CELL_TYPE_STRING);
               cell.setEncoding(HSSFCell.ENCODING_UTF_16);
               cell.setCellValue(customerDTO.getCompanyName());
               cell = row.createCell((short) i++);
               cell.setCellType(HSSFCell.CELL_TYPE_STRING);
               cell.setEncoding(HSSFCell.ENCODING_UTF_16);
               cell.setCellValue(customerDTO.getCustomerName());
               
              }
              // 新建一輸出文件流
              String outputFile = request.getRealPath("/")
                + "customer\\excel\\" + accounts + ".xls";
              /* 如果文件存在則刪除它 */
              File oldFile = new File(outputFile);
              if (oldFile.exists()) {
               oldFile.delete();
              }
              FileOutputStream fOut = new FileOutputStream(outputFile);
              // 把相應的Excel 工作簿存盤
              workbook.write(fOut);
              fOut.flush();
              // 操作結束,關閉文件
              fOut.close();
              customerForm.setExcelFilePath(outputFile);
              System.out.println("文件生成...");

             } catch (Exception e) {
              System.out.println("已運行 xlCreate() : " + e);
             }
             return mapping.findForward("exportCustomerSuccess");
            } else if (customerForm.getOperType() != null
              && customerForm.getOperType().equals("importCustomer")) {

          /*下面是從excel中讀取數據的例子*/
             CustomerDAO customerDAO = new CustomerDAO();
             String accounts = (String) request.getSession()
               .getValue("accounts");

             String fileToBeRead = request.getRealPath("/")
               + "customer\\excel\\upload_" + accounts + ".xls";
             try {
              HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(
                fileToBeRead));
              HSSFSheet sheet = workbook.getSheetAt(0);
              /* 讀取文件中的信息,并把數據存入數據庫 */
              for (int j = 1;; j++) {
               HSSFRow row = sheet.getRow(j);
               if (row != null) {
                CustomerDTO customerDTO = new CustomerDTO();
                HSSFCell cell;
                int i = 0;
                /* 跳過第一列的customerInformationID */
                i++;
                cell = row.getCell((short) i++);
                customerDTO.setCompanyName(cell.getStringCellValue());
                cell = row.getCell((short) i++);
                customerDTO.setCustomerName(cell.getStringCellValue());
                cell = row.getCell((short) i++);
                customerDTO.setNameForShort(cell.getStringCellValue());
                cell = row.getCell((short) i++);
                customerDTO
                  .setCompanyAddress(cell.getStringCellValue());
                cell = row.getCell((short) i++);
                customerDTO.setCountry(cell.getStringCellValue());
                cell = row.getCell((short) i++);
                customerDTO.setProvince(cell.getStringCellValue());
                cell = row.getCell((short) i++);
                customerDTO.setCity(cell.getStringCellValue());
                cell = row.getCell((short) i++);
                customerDTO.setPostalcode(cell.getStringCellValue());
                cell = row.getCell((short) i++);
                customerDTO.setCompanyHomePage(cell
                  .getStringCellValue());
                
                customerDAO.insertCustomer(customerDTO);
               } else {
                break;
               }
              }
             } catch (Exception e) {
              e.printStackTrace();
             }

             /* 設置返回目標 */
             String companyID = (String) request.getSession().getValue(
               "companyID");

             String accountsType = (String) request.getSession().getValue(
               "accountsType");

             String department = (String) request.getSession().getValue(
               "department");
             CompanyAccountsDAO companyAccountsDAO = new CompanyAccountsDAO();
             if (accountsType.equals("1")) {
              customerForm.setCustomerList((Vector) customerDAO
                .findEmployeeCustomer(Integer.parseInt(accounts), "0"));
             } else if (accountsType.equals("2")) {
              customerForm.setCustomerList((Vector) customerDAO
                .findDepartmentCustomer(Integer.parseInt(companyID),
                  Integer.parseInt(department), "0"));
              customerForm.setAccountsList((Vector) companyAccountsDAO
                .findDepartmentAccounts(companyID, department));
             } else if (accountsType.equals("3")) {
              customerForm.setCustomerList((Vector) customerDAO
                .findCompanyCustomer(Integer.parseInt(companyID), "0"));
              customerForm.setAccountsList((Vector) companyAccountsDAO
                .findAllAccounts(companyID));
             }
             return mapping.findForward("allCustomer");
            }

            return mapping.findForward("");
           }
          }

          posted on 2007-08-09 12:26 Sun River 閱讀(1715) 評論(0)  編輯  收藏 所屬分類: Struts
          主站蜘蛛池模板: 固始县| 长宁县| 乳山市| 寿阳县| 米林县| 肥西县| 淮安市| 宝清县| 方山县| 承德县| 乌海市| 石嘴山市| 施甸县| 上饶县| 乌恰县| 弋阳县| 富平县| 常山县| 彝良县| 翼城县| 上高县| 尼木县| 武邑县| 伊川县| 灵山县| 临潭县| 繁峙县| 葫芦岛市| 钟祥市| 荔波县| 晴隆县| 静海县| 阿坝| 莎车县| 新竹市| 安龙县| 鹿邑县| 宝清县| 青海省| 惠州市| 穆棱市|