• java读写excel文件


      近期处理的数据规模比较大,正好又是统计合并的事情,想着借助excel就可以完成了,然后就了解了下java读取excel的事情。

      读取的文件主要分两类:xls文件、xlsx文件。xls文件的相关操作用的是jxl.jar包,只要将这个包导入即可。xlsx文件的相关操作是利用apache的poi包。

    一、xls文件(一个jar包:jxl.jar)

    1)创建

    package jexcel;  
      
    import java.io.*;   
    import jxl.*;   
    import jxl.write.*;   
      
    /** 
     * @author Ken 
     * 
     * To change the template for this generated type comment go to 
     * Window>Preferences>Java>Code Generation>Code and Comments 
     */  
    public class CreateXLS {  
      
        public static void main(String[] args) {  
            try {  
                //open file.  
                WritableWorkbook book = Workbook.createWorkbook(new File("d:/Test.xls"));  
                  
                //create Sheet named "Sheet_1". 0 means this is 1st page.  
                WritableSheet sheet = book.createSheet("Sheet_1", 0);  
                  
                //define cell column and row in Label Constructor, and cell content write "test".  
                //cell is 1st-Column,1st-Row. value is "test".  
                Label label = new Label(0, 0, "test");  
                //add defined cell above to sheet instance.  
                sheet.addCell(label);  
                  
                //create cell using add numeric. WARN:necessarily use integrated package-path, otherwise will be throws path-error.  
                //cell is 2nd-Column, 1st-Row. value is 789.123.  
                jxl.write.Number number = new jxl.write.Number(1, 0, 789.123);  
                //add defined cell above to sheet instance.  
                sheet.addCell(number);  
                  
                //add defined all cell above to case.  
                book.write();  
                //close file case.  
                book.close();  
            } catch (Exception e) {  
                e.printStackTrace();  
            }  
        }  
    }  

    2)读取

    package jexcel;
    import java.io.*;  
    import jxl.*;  
      
    /** 
     * @author Ken 
     * 
     * To change the template for this generated type comment go to 
     * Window>Preferences>Java>Code Generation>Code and Comments 
     */  
    public class ReadXLS {  
      
        public static void main(String[] args) {  
            try {  
                Workbook book = Workbook.getWorkbook(new File("e:/pos/rule.xls"));  
                //get a Sheet object.   
                Sheet sheet = book.getSheet(0);  
                //get 1st-Column,1st-Row content.  
               // Cell cell = sheet.getCell(1, 2); //先是列序号,然后是行序号,都是从0开始算起
                Cell[] cell=sheet.getColumn(0);
                for(int i=0;i<cell.length;i++){
                    String result = cell[i].getContents();  
                    System.out.println(result);  
                }
                
                book.close();  
            } catch (Exception e) {  
                e.printStackTrace();  
            }  
      
        }  
    }  

    3)修改

    package jexcel;
    
    import java.io.*;  
    import jxl.*;  
    import jxl.write.*;  
      
    /** 
     * @author Ken 
     * 
     * To change the template for this generated type comment go to 
     * Window>Preferences>Java>Code Generation>Code and Comments 
     */  
    public class UpdateXLS {  
      
        public static void main(String[] args) {  
            try {  
                //get file.  
                Workbook wb = Workbook.getWorkbook(new File("d:/Test.xls"));  
                //open a copy file(new file), then write content with same content with Test.xls.    
                WritableWorkbook book =  
                    Workbook.createWorkbook(new File("d:/Test.xls"), wb);  
                //add a Sheet.  
                WritableSheet sheet = book.createSheet("Sheet_2", 1);  
                sheet.addCell(new Label(0, 0, "test2"));  
                book.write();  
                book.close();  
            } catch (Exception e) {  
                e.printStackTrace();  
            }  
        }  
    }  

    二、xlsx文件(五个jar包:poi-3.8-20120326.jar; poi-ooxml-3.8-20120326.jar; poi-ooxml-schemas-3.8-20120326.jar; xmlbeans-2.3.0.jar; dom4j-1.6.1.jar)

    import java.io.File;
    import java.io.FileInputStream;
    import java.io.IOException;
    import java.io.InputStream;
    import java.sql.Date;
    import java.text.SimpleDateFormat;
    import java.util.TimeZone;
    import java.util.logging.Logger;
    
    import org.apache.poi.xssf.usermodel.XSSFCell;
    import org.apache.poi.xssf.usermodel.XSSFRow;
    import org.apache.poi.xssf.usermodel.XSSFSheet;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    
    
    public class xlsx {
    
        public static void main(String[] args) {
            // TODO Auto-generated method stub
            poiExcel("e:/pos/rules.xlsx");
        }
    
        
        private static void poiExcel(String saveName){  
                   try{  
                        String realPath =saveName;  
                        File fileDes = new File(realPath);  
                        InputStream str = new FileInputStream(fileDes);  
                        XSSFWorkbook xwb = new XSSFWorkbook(str);  //利用poi读取excel文件流  
                        XSSFSheet st = xwb.getSheetAt(0);  //读取sheet的第一个工作表  
                        int rows=st.getLastRowNum();//总行数  
                        int cols;//总列数  
                        //log.info("========行========"+rows);  
                        for(int i=0;i<rows;i++){  
                            XSSFRow row=st.getRow(i);//读取某一行数据  
                            if(row!=null){  
                                //获取行中所有列数据  
                                cols=row.getLastCellNum();  
                                //log.info("========行========"+rows+"=====列========"+cols);  
                                
                                
                              
                                for(int j=0;j<cols;j++){  
                                    XSSFCell cell=row.getCell(j);  
                                    if(cell==null){  
                                        System.out.print("   ");    
                                    }else{  
                                //判断单元格的数据类型  
                                        switch (cell.getCellType()) {    
                                        case XSSFCell.CELL_TYPE_NUMERIC: // 数字    
                                            System.out.print(cell.getNumericCellValue() + "   ");    
                                            break;    
                                        case XSSFCell.CELL_TYPE_STRING: // 字符串    
                                            System.out.print(cell.getStringCellValue() + "   ");    
                                            break;    
                                        case XSSFCell.CELL_TYPE_BOOLEAN: // Boolean    
                                            System.out.println(cell.getBooleanCellValue() + "   ");    
                                            break;    
                                        case XSSFCell.CELL_TYPE_FORMULA: // 公式    
                                            System.out.print(cell.getCellFormula() + "   ");    
                                            break;    
                                        case XSSFCell.CELL_TYPE_BLANK: // 空值    
                                            System.out.println("");    
                                            break;    
                                        case XSSFCell.CELL_TYPE_ERROR: // 故障    
                                            System.out.println("故障");    
                                            break;    
                                        default:    
                                            System.out.print("未知类型   ");    
                                            break;    
                                        }    
                                    }  
                                }  
                                
                            }  
                        }  
                    }catch(IOException e){  
                        e.printStackTrace();     
                    }  
                      
                }  
    
    }

    代码全是网上贴来的,大家一起分享。。

    想把jar包也贴上来的,但是不知道是不支持还是我自己摸索不出来,反正最后是没贴出来。。。

  • 相关阅读:
    第一次冲次(补)
    软件工程概论个人总结
    第16周进度条
    读《梦断代码》第2章有感
    java.lang.ClassNotFoundException: com.microsoft.sqlserver.jdbc.SQLServerDriver
    传入的表格格式数据流(TDS)远程过程调用(RPC)协议流不正确。参数 1 (""): 数据类型 0x38 未知。
    怎么卸载VS2005呢?控制面板里内容太多,找不出哪些是属于VS2005?我的VS2005出问题了
    常用正则表达式的写法
    Http响应头字段详解,MyEclipse配置tomcat,servlet运行方式
    html框架 字体颜色 列表 表格 图片 定义列表 a标签
  • 原文地址:https://www.cnblogs.com/zidiancao/p/3578642.html
Copyright © 2020-2023  润新知