POI操作Excel（xls、xlsx）

阿帕奇官网：http://poi.apache.org/

POI3.17下载:http://poi.apache.org/download.html#POI-3.17

POI操作Excel教程（易百教程）：https://www.yiibai.com/apache_poi/

1.数据库连接：https://www.cnblogs.com/feipengting/p/7606042.html

 1 package com.gdin.util;
 2 
 3 import java.io.InputStream;
 4 import java.sql.Connection;
 5 import java.sql.DriverManager;
 6 import java.sql.PreparedStatement;
 7 import java.sql.ResultSet;
 8 import java.sql.SQLException;
 9 import java.sql.Statement;
10 import java.util.Properties;
11 
12 public class DBUtil {
13 
14      private static Connection con;
15         private static String url;
16         private static String user;
17         private static String pwd;
18 
19         public DBUtil() {
20 
21         }
22         static {
23             try {
24                 Class.forName("oracle.jdbc.driver.OracleDriver");/*如果是MySQL就改为Class.forName("com.mysql.jdbc.Driver");*/
25                 InputStream is = DBUtil.class.getResourceAsStream("/db.properties");//db.properties 是一个用户配置文件传用户名密码
26                 Properties prop=new Properties();
27                 prop.load(is);
28                 url=prop.getProperty("url");
29                 user=prop.getProperty("user");
30                 pwd=prop.getProperty("password");
31                 con = DriverManager.getConnection(url, user, pwd);
32             }catch (Exception e){
33                 System.out.println("数据库连接失败！");
34             }
35         }
36         public static ResultSet find(String sql){
37             con=getCon();
38             try {
39                 Statement smt=con.createStatement();
40                 ResultSet rs=smt.executeQuery(sql);
41                 return rs;
42             } catch (SQLException e) {
43                 e.printStackTrace();
44                 return null;
45             }
46         }
47         public static ResultSet find(String sql,Object ...pram){//...pram数组
48             con=getCon();
49             try {
50                 PreparedStatement smt=con.prepareStatement(sql);
51                 for (int i=0;i<pram.length;i++){
52                     smt.setObject(i+1,pram[i]);
53                 }
54                 ResultSet rs=smt.executeQuery();
55                 return rs;
56             } catch (SQLException e) {
57                 e.printStackTrace();
58                 return null;
59             }
60         }
61         public static void insert(String sql,Object ...pram){//...pram数组
62             con=getCon();
63             try {
64                 PreparedStatement smt=con.prepareStatement(sql);
65                 for (int i=0;i<pram.length;i++){
66                     smt.setObject(i+1,pram[i]);
67                 }
68                 smt.executeUpdate();
69             } catch (SQLException e) {
70                 e.printStackTrace();
71             }
72         }
73         public static Connection getCon(){
74             try {
75                 if(con==null||con.isClosed())
76                     con = DriverManager.getConnection(url, user, pwd);
77             } catch (SQLException e) {
78                 e.printStackTrace();
79             }
80             return con;
81         }
82 }
View Code

重要的类以及接口：Workbook、Sheet、Row、Cell（对应HSSF和XSSF具体的类）

2.读取本地两种格式的Excel文件：

 1     public static void main(String[] args) throws Exception, IOException {
 2         File file=new File("C:\\Users\\Administrator\\Desktop\\rjl.xlsx");
 3 //        File file=new File("C:\\Users\\Administrator\\Desktop\\123.xls");
 4         
 5         Workbook workBook=null;
 6         System.out.println(file.getName());
 7         if(file.getName().endsWith("xlsx")){
 8             workBook = new XSSFWorkbook(file);
 9         }else if(file.getName().endsWith("xls")){
10             workBook=new HSSFWorkbook(new FileInputStream(file));
11         }
12         
13         Sheet sheet =  workBook.getSheetAt(0);
14         int lastRowNum = sheet.getLastRowNum();
15         System.out.println(lastRowNum);
16         for(int i=0;i<=lastRowNum;i++){
17             Row row = sheet.getRow(i);
18             short lastCellNum = row.getLastCellNum();
19             for(int j=0;j<lastCellNum;j++){
20                 if(row.getCell(j).getCellTypeEnum().equals(CellType.NUMERIC)){
21                     System.out.print(row.getCell(j).getNumericCellValue()+"###");
22                 }else if(row.getCell(j).getCellTypeEnum().equals(CellType.STRING)){
23                     System.out.print(row.getCell(j).getStringCellValue()+"###");
24                 }
25             }
26             System.out.println();
27         }
28         
29     }

3.poi操作Excel

　　poi执行Excel中的公式：https://blog.csdn.net/l362696422/article/details/41700057

 1     public static void ExportExcel(){
 2         Workbook wb=new XSSFWorkbook();
 3         try {
 4             FileOutputStream fos = new FileOutputStream(new File("C:\\Users\\Administrator\\Desktop\\信息.xlsx"));
 5             //单元格样式
 6             CellStyle cellStyle = wb.createCellStyle();
 7             //字体
 8             Font font = wb.createFont();
 9             font.setItalic(true);
10             cellStyle.setBorderBottom(BorderStyle.DASHED);
11             cellStyle.setFont(font);
12             //设置旋转角度
13             cellStyle.setRotation((short) 270);
14             Sheet sheet = wb.createSheet("info");
15             Row row = sheet.createRow(0);
16             //设置该行的行高
17             row.setHeight((short) 800);
18             Cell cell = row.createCell(0);
19             cell.setCellValue("myvalue");
20             cell.setCellStyle(cellStyle);
21             //同上
22             CellStyle cellStyle2 = wb.createCellStyle();
23             Row row2 = sheet.createRow(1);
24             row2.setHeight((short) 1000);
25             Cell cell2 = row2.createCell(1);
26             cellStyle2.setRotation((short) 180);
27             cell2.setCellValue(true);
28             cell2.setCellStyle(cellStyle2);
29             //row行和cell列都是从0开始的
30             Cell cell3 = row2.createCell(7);
31             Cell cell4 = row2.createCell(8);
32             Cell cell5 = row2.createCell(9);
33             Cell cell6 = row2.createCell(10);
34             cell3.setCellValue(11);
35             cell4.setCellValue(22);
36             cell5.setCellValue(33);
37             //设置单元格的类型为：公式类型FORMULA
38             cell6.setCellType(CellType.FORMULA);
39             cell6.setCellFormula("SUM(H2:J2)");
40             //求出工作部所有的已设置使用公式的值
41             wb.getCreationHelper().createFormulaEvaluator().evaluateAll();
42             //在确定xls或xlsx对象类型时也可这样使用求公式值
43             //XSSFFormulaEvaluator.evaluateAllFormulaCells(wb);
44             //超链接
45             CellStyle style = wb.createCellStyle();
46             Font font2 = wb.createFont();
47             //设置字体格式为单下划线
48             font2.setUnderline(Font.U_SINGLE);
49             //font2.setColor(Font.COLOR_RED);
50             font2.setColor(HSSFColor.HSSFColorPredefined.BLUE.getIndex());
51             style.setFont(font2);
52             Cell cell12 = row2.createCell(12);
53             cell12.setCellValue("跳转网站");
54             cell12.setCellStyle(style);
55             CreationHelper creationHelper = wb.getCreationHelper();
56             Hyperlink hyperlink = creationHelper.createHyperlink(HyperlinkType.URL);
57             hyperlink.setAddress("http://www.baidu.com");
58             //单元格设置超链接
59             cell12.setHyperlink(hyperlink);
60             //////////设置打印区域/////////
61             wb.setPrintArea(0, 0, 8, 0, 5);
62             sheet.getPrintSetup().setPaperSize(PrintSetup.A4_PAPERSIZE);
63             sheet.setDisplayGridlines(true);
64             sheet.setPrintGridlines(true);
65             
66             wb.write(fos);
67             fos.close();
68             wb.close();
69         } catch (Exception e) {
70             // TODO Auto-generated catch block
71             e.printStackTrace();
72         }
73         
74     }

4.poi导入Excel文件的兼容性以及数字自动加小数点的问题解决方案

　　http://www.cnblogs.com/qiujiababy/p/9371786.html

　　https://www.jianshu.com/p/a7eca64237bd

    /**
     * 处理导入小数点
     */
    public  static String  numOfImport(Cell cell) {
        String value = cell.toString();
        int i = cell.getCellType();
        if (i == 1) {//字符串类型
            return value;
        } else {
            String[] str = value.split("\\.");
            if (str.length > 1) {
                String str1 = str[1];
                int m = Integer.parseInt(str1);
                if (m == 0) {
                    return str[0];
                } else {
                    return value;
                }
            }else{
                return value;
            }
        }
    }
<form name="form1" action="handle.jsp" method="post" enctype="multipart/form-data">
    <input type="file" name="excel">
    <input type="submit" value="上传">

</form>
 1 public static String ImportExcel(InputStream is){
 2         Workbook workBook=null;
 3         /*try {
 4                 System.out.println("create1");
 5                 workBook = new XSSFWorkbook(is);
 6                 
 7             
 8         } catch (Exception e) {
 9             System.out.println("create2");
10             try{
11                 System.out.println("create22222");
12                 workBook=new HSSFWorkbook(is);
13                 System.out.println("nonono");
14             }catch(Exception e2){
15                 System.out.println("无法处理！");
16             }
17                 
18         }*/
19         
20         try {
21             workBook=WorkbookFactory.create(is);
22         } catch (EncryptedDocumentException e1) {
23             // TODO Auto-generated catch block
24             e1.printStackTrace();
25         } catch (InvalidFormatException e1) {
26             // TODO Auto-generated catch block
27             e1.printStackTrace();
28         } catch (IOException e1) {
29             // TODO Auto-generated catch block
30             e1.printStackTrace();
31         }
32         
33         Sheet sheet =  workBook.getSheetAt(0);
34         int lastRowNum = sheet.getLastRowNum();
35         System.out.println(lastRowNum);
36         for(int i=0;i<=lastRowNum;i++){
37             Row row = sheet.getRow(i);
38             short lastCellNum = row.getLastCellNum();
39             for(int j=0;j<lastCellNum;j++){
40                 if(row.getCell(j).getCellTypeEnum().equals(CellType.NUMERIC)){
41                     //System.out.print(row.getCell(j).getNumericCellValue()+"###");
42                     System.out.print(numOfImport(row.getCell(j))+"###");
43                 }else if(row.getCell(j).getCellTypeEnum().equals(CellType.STRING)){
44                     System.out.print(row.getCell(j).getStringCellValue()+"###");
45                 }
46             }
47             System.out.println();
48         }
49         System.out.println("nono33333333333");
50         try {
51             workBook.close();
52         } catch (IOException e) {
53             // TODO Auto-generated catch block
54             e.printStackTrace();
55         }
56         System.out.println("nono44444444444444");
57         return "ok";
58     }

使用apach的fileupload.jar包上传Excel，主要处理流（直接request.getInputStream()的流是没经过处理的，所以采用该jar包处理流问题）

<%
    if(request.getMethod().equalsIgnoreCase("post")){
        
        try {
            FileItemFactory factory = new DiskFileItemFactory();
            // 文件上传核心工具类
            ServletFileUpload upload = new ServletFileUpload(factory);
            upload.setFileSizeMax(10 * 1024 * 1024); // 单个文件大小限制
            upload.setSizeMax(50 * 1024 * 1024); // 总文件大小限制
            upload.setHeaderEncoding("UTF-8"); // 对中文文件编码处理
            
            if (ServletFileUpload.isMultipartContent(request)) {
                List<FileItem> list = upload.parseRequest(request);
                // 遍历
                for (FileItem item : list) {
                    if (!item.isFormField()) {
                        TestExcel.ImportExcel(item.getInputStream());
                    }
                }
            }
            out.print("成功");
        } catch (Exception e) {
            out.print("失败");
        }
        
        
    }
    
%>

5.导出（下载）Excel（jsp需要使用 response.reset() 来清除首部的空白行）

　　https://www.cnblogs.com/zml-java/p/6146421.html

　　https://blog.csdn.net/xingkong22star/article/details/39207015

<button><a href="exportExcel.jsp">下载</a></button>
<body>
<%
TestExcel.xiazai(response);
%>
</body>
 1 public static void xiazai(HttpServletResponse response) throws IOException{
 2         Workbook wb = new XSSFWorkbook();
 3         Sheet sheet = wb.createSheet();
 4         Row row = sheet.createRow(0);
 5         Cell cell = row.createCell(0);
 6         cell.setCellValue("well down 我的测试结果");
 7         
 8         ServletOutputStream outputStream = response.getOutputStream();
 9      response.reset();
10         response.setHeader("Content-disposition", "attachment; filename="+URLEncoder.encode("容杰龙测试", "UTF-8")+".xlsx");
11         response.setContentType("application/msexcel");
12         wb.write(outputStream);
13        
14         outputStream.flush();
15         outputStream.close();
16         wb.close();
17         
18     }

6.XSSFWorkbook、HSSFWorkbook导出EXCEL数据和图片

　　https://blog.csdn.net/sun_cherish/article/details/79712975

　　https://blog.csdn.net/chenssy/article/details/20524563

　　https://blog.csdn.net/joyous/article/details/8780112

　　https://blog.csdn.net/joyous/article/details/9664739

 1     public static void ExportPic() throws IOException{
 2         XSSFWorkbook wb = new XSSFWorkbook();
 3          String sheetName = "excel导出图片测试";
 4         XSSFSheet RZ_TXSheet = wb.createSheet(sheetName);
 5         
 6         ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
 7         //图片绝对路径   
 8         BufferedImage user_headImg = ImageIO.read(new File("C:\\Users\\Administrator\\Desktop\\boy.jpg"));  
 9         ImageIO.write(user_headImg, "jpg", byteArrayOut);
10          //sheet只能获取一个
11         XSSFDrawing patriarch = RZ_TXSheet.createDrawingPatriarch();
12         //设置图片的属性
13         XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 255, 255,(short) 0, 0, (short) 5, 8);   
14         anchor.setAnchorType(ClientAnchor.AnchorType.MOVE_DONT_RESIZE);  
15         //插入图片 
16         patriarch.createPicture(anchor,wb.addPicture(byteArrayOut.toByteArray(), XSSFWorkbook.PICTURE_TYPE_JPEG));
17         
18          FileOutputStream fileOut = new FileOutputStream("C:\\Users\\Administrator\\Desktop\\测试Excel.xlsx");   
19          // 写入excel文件   
20           wb.write(fileOut);  
21           wb.close();
22     }

 

 1     public static void ExportTest() throws FileNotFoundException, IOException{
 2         XSSFWorkbook workbook = new XSSFWorkbook();
 3         XSSFSheet xssfSheet = workbook.createSheet("abc");
 4         XSSFDrawing patriarch = xssfSheet.createDrawingPatriarch();
 5         XSSFClientAnchor anchor=new XSSFClientAnchor(0, 0, 255, 255, 2, 2, 8, 8);
 6         byte[] by=IOUtils.toByteArray(new FileInputStream(new File("C:\\Users\\Administrator\\Desktop\\boy.jpg")));
 7         int pictureIndex = workbook.addPicture(by, Workbook.PICTURE_TYPE_JPEG);
 8         patriarch.createPicture(anchor, pictureIndex);
 9         
10         FileOutputStream fileOutputStream = new FileOutputStream(new File("C:\\Users\\Administrator\\Desktop\\tupian.xlsx"));
11         workbook.write(fileOutputStream);
12         fileOutputStream.close();
13         workbook.close();
14     }

　　多张图片示例：

 1     public static void ExportPic() throws IOException{
 2         XSSFWorkbook wb = new XSSFWorkbook();
 3          String sheetName = "excel导出图片测试";
 4         XSSFSheet RZ_TXSheet = wb.createSheet(sheetName);
 5         
 6         ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
 7         //图片绝对路径   
 8         BufferedImage user_headImg = ImageIO.read(new File("C:\\Users\\Administrator\\Desktop\\boy.jpg"));  
 9         ImageIO.write(user_headImg, "jpg", byteArrayOut);
10          //sheet只能获取一个
11         XSSFDrawing patriarch = RZ_TXSheet.createDrawingPatriarch();
12         //设置图片的属性
13         XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 255, 255,(short) 0, 0, (short) 5, 8);   
14         anchor.setAnchorType(ClientAnchor.AnchorType.MOVE_DONT_RESIZE);  
15         //插入图片 
16         patriarch.createPicture(anchor,wb.addPicture(byteArrayOut.toByteArray(), XSSFWorkbook.PICTURE_TYPE_JPEG));
17         //插入第二张图片
18         int pictureIndex = wb.addPicture(byteArrayOut.toByteArray(), Workbook.PICTURE_TYPE_JPEG);
19         XSSFClientAnchor anchor2 = patriarch.createAnchor(0, 0, 255, 255, 1, 10, 10, 15);
20         patriarch.createPicture(anchor2, pictureIndex);
21         
22         
23         
24          FileOutputStream fileOut = new FileOutputStream("C:\\Users\\Administrator\\Desktop\\测试Excel.xlsx");   
25          // 写入excel文件   
26           wb.write(fileOut);  
27           wb.close();
28     }
View Code

 7.poi获取excel中的图片

　　https://blog.csdn.net/delongcpp/article/details/8833995

　　https://www.cnblogs.com/colaclicken/p/8058719.html

    public static void ReadPictureFromExcel() throws Exception{
        FileInputStream fileInputStream = new FileInputStream("C:\\Users\\Administrator\\Desktop\\tupian.xlsx");
        Workbook workbook = WorkbookFactory.create(fileInputStream);
        List<? extends PictureData> pictures = workbook.getAllPictures();
        int i=1;
        for (PictureData pictureData : pictures) {
            byte[] bs = pictureData.getData();
            FileOutputStream fileOutputStream = new FileOutputStream("C:\\Users\\Administrator\\Desktop\\"+i+".jpg");
            fileOutputStream.write(bs);
            fileOutputStream.close();
            i++;
        }
    }
View Code

 8.poi读取一个excel中的图片到另一个excel中

 1     public static void main(String[] args) throws Exception, IOException {
 2 
 3         WritePicToExcel(ReadPictureFromExcel2());
 4     }
 5 
 6     public static Map<String, XSSFPictureData> ReadPictureFromExcel2() throws Exception {
 7         Map<String, XSSFPictureData> map = new HashMap<String, XSSFPictureData>();
 8         FileInputStream fileInputStream = new FileInputStream("C:\\Users\\Administrator\\Desktop\\tupian.xlsx");
 9         XSSFWorkbook workbook = (XSSFWorkbook) new XSSFWorkbook(fileInputStream);
10         // List<? extends PictureData> pictures = workbook.getAllPictures();
11         XSSFSheet sheet = workbook.getSheetAt(0);
12         List<POIXMLDocumentPart> list = sheet.getRelations();
13         System.out.println(list.size());
14         for (POIXMLDocumentPart poixmlDocumentPart : list) {
15             System.out.println(poixmlDocumentPart instanceof XSSFDrawing);
16             if (poixmlDocumentPart instanceof XSSFDrawing) {
17                 XSSFDrawing drawing = (XSSFDrawing) poixmlDocumentPart;
18                 List<XSSFShape> shapes = drawing.getShapes();
19                 System.out.println("图的数量："+shapes.size());
20                 for (XSSFShape shape : shapes) {
21                     XSSFPicture picture = (XSSFPicture) shape;
22                     XSSFClientAnchor anchor = picture.getPreferredSize();
23                     //CTMarker marker = anchor.getFrom();
24                     //String key = marker.getRow() + "-" + marker.getCol();
25                     String key =anchor.getDx1()+"-"+anchor.getDy1()+"-"+anchor.getDx2()+"-"+anchor.getDy2()+"-"+anchor.getCol1()+"-"+anchor.getRow1()+"-"+anchor.getCol2()+"-"+anchor.getRow2();
26                     System.out.println(key);
27                     //System.out.println(anchor.getDx1()+"-"+anchor.getDy1()+"-"+anchor.getDx2()+"-"+anchor.getDy2()+"-"+anchor.getCol1()+"-"+anchor.getRow1()+"-"+anchor.getCol2()+"-"+anchor.getRow2());
28                     map.put(key, picture.getPictureData());
29                 }
30             }
31         }
32         workbook.close();
33         return map;
34     }
35     
36     public static void WritePicToExcel(Map<String, XSSFPictureData> map) throws Exception{
37         FileOutputStream fileOutputStream = new FileOutputStream("C:\\Users\\Administrator\\Desktop\\图片print.xlsx");
38         XSSFWorkbook xssfWorkbook = new XSSFWorkbook();
39         XSSFSheet xssfSheet = xssfWorkbook.createSheet("print");
40         Set<Entry<String, XSSFPictureData>> entrySet = map.entrySet();
41         //注意！！！
42         XSSFDrawing patriarch = xssfSheet.createDrawingPatriarch();
43         for (Entry<String, XSSFPictureData> entry : entrySet) {
44             String key = entry.getKey();
45             System.out.println(key);
46             String[] strings = key.split("-");
47             //int row = Integer.parseInt(strings[0]);
48             //int column = Integer.parseInt(strings[1]);
49             //System.out.println("row:"+row+" column:"+column);
50             int dx1=Integer.parseInt(strings[0]);
51             int dy1=Integer.parseInt(strings[1]);
52             int dx2=Integer.parseInt(strings[2]);
53             int dy2=Integer.parseInt(strings[3]);
54             int col1=Integer.parseInt(strings[4]);
55             int row1=Integer.parseInt(strings[5]);
56             int col2=Integer.parseInt(strings[6]);
57             int row2=Integer.parseInt(strings[7]);
58             
59             XSSFPictureData xssfPictureData = entry.getValue();
60             //XSSFRow xssfRow = xssfSheet.createRow(row);
61             
62             //XSSFClientAnchor anchor=new XSSFClientAnchor();
63             XSSFClientAnchor anchor = new XSSFClientAnchor(dx1, dy1, dx2, dy2, col1, row1, col2, row2);
64             anchor.setAnchorType(ClientAnchor.AnchorType.MOVE_DONT_RESIZE); 
65             //anchor.setRow1(row);
66             //anchor.setCol1(column);
67             int pictureIndex=xssfWorkbook.addPicture(xssfPictureData.getData(), Workbook.PICTURE_TYPE_JPEG);
68             patriarch.createPicture(anchor, pictureIndex);
69             //FileOutputStream outputStream = new FileOutputStream("C:\\Users\\Administrator\\Desktop\\图片print"+row+".jpg");
70             //outputStream.write(xssfPictureData.getData());
71             //outputStream.close();
72         }
73         xssfWorkbook.write(fileOutputStream);
74         fileOutputStream.close();
75         xssfWorkbook.close();
76     }

 

posted @ 2018-08-12 00:49  57容杰龙  阅读(2133)  评论(1)  编辑  收藏  举报