JAVA操作Excel那些事儿

前段时间接了一个Excel的数据处理单子后，小生百度千寻，终于先后使用JXL，POI两个包的后，最终在老师的见一下，使用ODBC访问EXCEL的形式，对处理，对于几种处理EXCEKL的数据方法有了大致的了解和感受，现总结如下： 目标数据：固定格式的EXCEL数据从10W条到100W条不等，根据不同的要求，添加新的记录。即原来一条ABCD数据，变成ABCDE，对于EXCEL的使用上，主要分xls和xlsx2中。 总所周知，xlsx的压缩率比xls提高了不少，而xls每个sheet的数据条数不能超过 65536条，而xlsx的则是 1048576条，所以超 过65536条的大数据量xlsx转换成xls时 ，大家可要注意数据会丢失的哦，下附说明图 但是，值得一提的是，在把EXCEL用JAVA读到内存时，相同数据的xls和xlsx 文件，xlsx占的内存要比xls大不少【jxl不支持xlsx的，小生使用xls测试的，ODBC访问未做太多测试】

JXL 官网下载地址：http://sourceforge.net/projects/jxl/ 个人网盘分享： http://pan.baidu.com/share/link?shareid=903370199&uk=1493356468【单纯的jxl包】 http://pan.baidu.com/share/link?shareid=908184674&uk=1493356468【官网下载jxl所有内容，含文档和示例】 JXL 目前只能操作xls版的Excel，但是胜在速度快，而且没什么附加的包，直接导入一个jxl.jar即可。网上也有叫jexcel。 再jxl中，每个cell读取是都可以用getContents()方法读取，获得的均为String,码农们可以根据需要转换成各种类型。 下附基本代码 public class ReadXLS {     public static void main(String args[]) {        try {            Workbook book = Workbook.getWorkbook(new File("D:\\test.xls "));            // 获得第一个工作表对象            Sheet sheet = book.getSheet(0);            // 得到第一列第一行的单元格            Cell cell1 = sheet.getCell(0, 0);            String result = cell1.getContents();            System.out.println(result);            book.close();        }catch(FileNotFoundException e)        {            System.out.println("系统找不到指定文件");        } catch (Exception e) {            // System.out.println(e);            e.printStackTrace();        }     } }

POI 官方下载网址：http://poi.apache.org/ 个人网盘分享：http://pan.baidu.com/share/link?shareid=912865935&uk=1493356468 dom4j.jar 官方下载网址：http://www.dom4j.org 个人网盘分享：http://pan.baidu.com/share/link?shareid=929504518&uk=1493356468 xbean.jar 官方下载网址：http://xmlbeans.apache.org 个人网盘分享：http://pan.baidu.com/share/link?shareid=938514187&uk=1493356468【单独的jar包】 http://pan.baidu.com/share/link?shareid=940958543&uk=1493356468【官方完整包】   POI是一个比较全的类，对于03到07版的office均能操作 ，带x的类库就均是操作07的类库   其中需要注意的是，sheet在不少包中都有，而我们需要的是 而对于03和07的区别上 仅需要在new一个Workbook加以区分即可。 真正需要注意的,调用POI的包时，需要引入各种包， 其中，dom4j和xbean.jar是官方包中所没有的，需要自己另外下载。   在POI中数据类型识别上有了新的划分，原Excel中为数值型的必须用getNumericCellValue()方法获取，返回的为Double型，字符串值的则须用getStringCellValue()获取，否则会出现数值类型不对的Exception，另外可以通过 String d;                      if(cell.getCellType()!=1)                      {                          int date = (int)cell.getNumericCellValue();                          long timec = (date-25569) * 60 * 24 *60*1000L;                          SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");                          d = format.format(timec);                      }                      else d = cell.getStringCellValue(); 获取cell的类型，其中返回int型的对应的类型如下 CellType 类型 值 CELL_TYPE_NUMERIC 数值型 0 CELL_TYPE_STRING 字符串型 1 CELL_TYPE_FORMULA 公式型 2 CELL_TYPE_BLANK 空值 3 CELL_TYPE_BOOLEAN 布尔型 4 CELL_TYPE_ERROR 错误 5 改变cell的类型后，在用getStringCellValue(),读取。   下附基本代码   package cn.com.office.test;   import java.io.BufferedReader; import java.io.FileInputStream; import java.io.FileOutputStream; import java.io.IOException; import java.io.InputStream; import java.io.InputStreamReader; import java.io.OutputStream;   import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.xssf.usermodel.XSSFWorkbook;   public class TestReadAndWrite {           public static void main(String[] args) throws IOException {        // TODO Auto-generated method stub        String path = "F:\\";        String fileName = "存量合计";        String fileType = "xlsx";        writer(path,fileName,fileType);        read(path,fileName,fileType);        System.out.println("System Stop");            }       private static void writer(String path, String fileName, String fileType) throws IOException{        // TODO Auto-generated method stub        Workbook wb = null;        if(fileType.equals("xls"))        {            wb = new HSSFWorkbook();        }        else if (fileType.equals("xlsx"))        {            wb = new XSSFWorkbook();        }        else{            System.out.println("您的文档格式不正确！");        }        Sheet sheet1 = (Sheet)wb.createSheet();        for(int i = 0; i < 5; i++)        {            Row row = (Row)sheet1.createRow(i);            for(int j = 0; j < 8 ; j++)            {               Cell cell = row.createCell(j);               cell.setCellValue(i);            }        }        OutputStream stream = new FileOutputStream(path + fileName + "." + fileType);        wb.write(stream);        stream.close();     }       private static void read(String path, String fileName, String fileType)throws IOException {        // TODO Auto-generated method stub        InputStream stream = new FileInputStream(path + fileName + "." + fileType);        Workbook wb = null;        if(fileType.equals("xls"))        {            wb = new HSSFWorkbook(stream);        }        else if (fileType.equals("xlsx"))        {            wb = new XSSFWorkbook(stream);        }else{            System.out.println("您的文档格式不正确！");        }     Sheet sheet1 = wb.getSheetAt(0);        for(Row row2:sheet1)        {            for(Cell cell2:row2){               System.out.print((int)cell2.getNumericCellValue() + "");            }            System.out.println();        }          }   }

用微软的ODBC的连接Excel从而实现操作，着实让我眼前一亮，但配置方面却，让我比较无语，在用ODBC连接Excel前需要实现配置驱动，具体操作如下 控制面变->管理工具->ODBC 数据源 –>系统DSN->添加->Microsoft Excel Driver(*.xls, *.xlsx，*.xlsm, *.xlsb)，添加的时候，数据源名称类同数据库名 这边设定的时候就会出现64位系统和32位系统的差别 这是我本机中两个ODBC 数据源，但是 32位中有相应的驱动，而64位的系统中却没有 度娘后，有说ODBC连接office 不支持64的操作系统，   如果数据管理工具中没有ODBC 数据源(32 位) 用虚拟机搭个32位的系统吧0.0或者直接装32位的office   具体的代码如下 import java.sql.Connection;   import java.sql.DriverManager;   import java.sql.ResultSet;  import java.sql.SQLException; import java.sql.Statement;  public class test9 {                   public static void main(String[] args) {                   // TODO Auto-generated method stub                    try{                             Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");                             Connection conn = DriverManager.getConnection("jdbc:odbc:DB;DBQ=D:\\test.xls");                             Statement stmt = conn.createStatement();                    //      int rs = stmt.executeUpdate("INSERT INTO [sheet1$] values (12,'xinli',21, 22);");                    //      System.out.println(rs);                             ResultSet rs = stmt.executeQuery("SELECT * FROM [sheet1$]"); while(rs.next()) {          System.out.println(rs.getString(1)); }                             stmt.close();                             conn.close();                    }                    catch(SQLException e){                             e.printStackTrace();                             System.out.println("");                    } catch (ClassNotFoundException e) {                             // TODO Auto-generated catch block                             System.out.println("");                    }          } }   不难发现 除了加载驱动的时候用Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); 连接中Connection conn = DriverManager.getConnection("jdbc:odbc:DB;DBQ=D:\\test.xls"); DB为数据库名，即之前添加驱动的时候所用的名字，DBQ=后面部分则是需要操作的Excel文件的路径。 其他部分均与JDBC相同，表名即为sheet名，但需表示[sheet1$]，另外所有SQL部分与Access相同，但是不方便用limit语句，而小生也为接触过Access，一切以MySQL语句撰写，大部均可以使用， 注Excel表的第一行，默认为表的字段~