读取excel数据的方式整理

最新推荐文章于 2024-01-26 14:08:43 发布

SuperBins

最新推荐文章于 2024-01-26 14:08:43 发布

阅读量3.2k

点赞数

分类专栏： java 文章标签： java 数据库 sql

本文链接：https://blog.csdn.net/superbins/article/details/126304767

版权

java 专栏收录该内容

10 篇文章 0 订阅

订阅专栏

读取excel数据的几种方式

1、JDBC-ODBC Excel Driver
2、 jxl.jar
3、jcom.jar
4、poi.jar
5、easypoi
6、easyExcel

下面分别对这几种方法分别进行探讨

1、 JDBC-ODBC Excel Driver
这种方法是将excel看成是数据库进行操作，使用SQL Select语句即可查询excel表格。优点是：不需要第三方的jar包。
如下表样
首先在控制面板进行数据源ODBC登记
具体方法如下：
下面就是代码了。

package xuzhe;

import java.io.*;

import java.sql.*;

//java xuzhe.ExcelJDBC

public class ExcelJDBC {

    public static void main(String[] args)throws SQLException{

        Connection con = null;

        try{

          Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

          con = DriverManager.getConnection("jdbc:odbc:ExcelJDBC" );

          Statement st =con.createStatement();

          ResultSet rs =st.executeQuery( "Select * from[Sheet1$]" );

 

          ResultSetMetaDatarsmd = rs.getMetaData();

          int numberOfColumns =rsmd.getColumnCount();

                   

          System.out.println ("表格列数"+numberOfColumns  );             

          System.out.println(rsmd.getColumnName(1)+"," + rsmd.getColumnName(2) + "," + rsmd.getColumnName(3));

          while (rs.next()) {

              for (int i = 1; i <=numberOfColumns; i++) {

                if (i > 1) System.out.print(", ");

                StringcolumnValue = rs.getString(i);

                System.out.print(columnValue);

                }

                System.out.println("");

              }

          rs.close(); 

          st.close();

          }

        catch(Exception ex) {

          System.err.print("Exception: ");

          System.err.println(ex.getMessage());

          }

        finally {

          con.close();

          }

        }

}

2、 jxl.jar
jxl.jar为开源代码，任何运行java虚拟机的操作系统都能使用这个jar包操作excel表格。优点是：不依赖其他任何第三方的库。下载地址：http://mirrors.ibiblio.org/pub/mirrors/maven2/net/sourceforge/jexcelapi/jxl/2.6.10/jxl-2.6.10.jar
程序如下：

package xuzhe;

 

import java.io.File;

import jxl.Sheet;

import jxl.Workbook;

 

//java -classpath .;E:\eclipse3.6.2\workspace\CrazyJava\lib\jxl.jarxuzhe.ExcelJXL

public class ExcelJXL

{

    static String sourceFile ="c:\\name.xls"; //源文件

    public static void main(String[]args) 

    {

       try

       {

              Workbook book = Workbook.getWorkbook(newFile(sourceFile));

 

              //0代表第一个工作表对象

              Sheet sheet =book.getSheet(0);

              int rows =sheet.getRows();

              int cols =sheet.getColumns();

              String colname1 =sheet.getCell(0, 0).getContents().trim();

              String colname2 =sheet.getCell(1, 0).getContents().trim();

              String colname3 =sheet.getCell(2, 0).getContents().trim();

              System.out.println(colname1+","+colname2+","+colname3);

              for (int z = 1; z <rows; z++)

              {

                  //0代表列数，z代表行数

                  String name =sheet.getCell(0, z).getContents();

                  String sex =sheet.getCell(1, z).getContents();

                  String ID =sheet.getCell(2, z).getContents();

                  System.out.println(name+","+sex+","+ID);

              }

       }

              catch(Exception e)

              {

                  e.printStackTrace();

              }

    }

}

3、 jcom.jar

jcom.jar是日本人开发的，也是一个开源项目，下载地址：http://sourceforge.net/projects/jcom/files/latest/download

将jcom.jar拷贝到classlib目录下，将jcom.dll放到你的JAVA_HOME/bin目录下，否则会出现下面错误。

程序如下：

package xuzhe;

import jp.ne.so_net.ga2.no_ji.jcom.excel8.*;

import jp.ne.so_net.ga2.no_ji.jcom.*;

import java.io.File;

import java.util.Date;

public class ExcelJCOM {

     public static void main(String[]args)throws Exception {

        JCOMReadExcel();

        JCOMCreateExcel();

     }

     

     static void JCOMReadExcel()

     { 

        ReleaseManager rm = new ReleaseManager();

     

        try

        {

            System.out.println("EXCEL startup...");

            // if already started, open new window

            ExcelApplicationexcel = new ExcelApplication(rm);

            excel.Visible(true);

            String Filename = "c:\\name.xls";

            ExcelWorkbooksxlBooks = excel.Workbooks();

            ExcelWorkbookxlBook = xlBooks.Open(Filename);

            

            ExcelWorksheetsxlSheets = xlBook.Worksheets();

            //第一个工作表

            ExcelWorksheetxlSheet = xlSheets.Item(1);

          

            ExcelRange xlRange= xlSheet.Cells();

            int i;

            int j;

            for(j=1;j<=4;j++)

            {

               for(i=1;i<=3;i++)

               {

                   System.out.print(xlRange.Item(j,i).Value());

                   if(i<3)

                   {

                      System.out.print(",");

                   }

                      

               }

               System.out.println("");

            }

          }

          catch(Exception e) {e.printStackTrace(); }

          finally { rm.release(); }

     }

     

     static void JCOMCreateExcel()

     {

        ReleaseManager rm = new ReleaseManager();

          try {

           System.out.println("EXCEL startup...");

           // if already started, open new window

           ExcelApplicationexcel = new ExcelApplication(rm);

           excel.Visible(true);

          

           // display any information

           System.out.println("Version="+excel.Version());

           System.out.println("UserName="+excel.UserName());

           System.out.println("Caption="+excel.Caption());

           System.out.println("Value="+excel.Value());

 

           ExcelWorkbooksxlBooks = excel.Workbooks();

           ExcelWorkbook xlBook= xlBooks.Add();   // create new book

 

           // enumurate all files

           System.out.println

             ("set infomation of files in current directory tocell ...");

           ExcelWorksheetsxlSheets = xlBook.Worksheets();

           ExcelWorksheetxlSheet = xlSheets.Item(1);

           ExcelRange xlRange =xlSheet.Cells();

 

          xlRange.Item(1,1).Value("filename" );

          xlRange.Item(2,1).Value("size" );

          xlRange.Item(3,1).Value("lastmodified time");

          xlRange.Item(4,1).Value("isdirectory");

          xlRange.Item(5,1).Value("isfile");

           xlRange.Item(6,1).Value("can read");

          xlRange.Item(7,1).Value("canwrite");

 

           File path = new File("./");

           String[] filenames =path.list();

           for(int i=0;i<filenames.length; i++) {

               File file = newFile(filenames[i]);

               System.out.println(file);

              xlRange.Item(1,i+2).Value( file.getName() );

              xlRange.Item(2,i+2).Value( (int)file.length() );

              xlRange.Item(3,i+2).Value( new Date(file.lastModified()) );

              xlRange.Item(4,i+2).Value( file.isDirectory()?"Yes":"No" );

              xlRange.Item(5,i+2).Value( file.isFile()?"Yes":"No" );  

              xlRange.Item(6,i+2).Value( file.canRead()?"Yes":"No" ); 

              xlRange.Item(7,i+2).Value( file.canWrite()?"Yes":"No" );

           }

          

           char start ='B';

           char end = (char)((byte)start + filenames.length - 1);

           System.out.println("end=[" + end +"]");

           String expression = "=Sum(B2:" + String.valueOf(end) +"2)";

           System.out.println("expression=[" + expression +"]");

           System.out.println

              ("embed equation, calculate sum of filesize: "+expression);

          xlRange.Item(1,filenames.length+2).Value("sum");

          xlRange.Item(2,filenames.length+2).Formula(expression);

          xlRange.Columns().AutoFit();    // fit columns

 

           // comment out, if print out.

           // output default printer.

           //  System.out.println("print out...");

           //  xlSheet.PrintOut();

 

           // comment out, if book save to file.

           // if no path, save to(My Documents)

           // System.out.println

           //   ("save tofile... (My Documents)\\testExcel.xls");

           xlBook.SaveAs("testExcel.xls");

 

           xlBook.Close(false,null,false);

           excel.Quit();

 

           System.out.println("thank you .");

          }

          catch(Exception e) {e.printStackTrace(); }

          finally { rm.release(); }

     }

}

4、 poi.jar

poi.jar是apache旗下的一个开源项目，下载地址：

http://www.apache.org/dyn/closer.cgi/poi/release/bin/poi-bin-3.8-20120326.zip

程序如下：

package xuzhe;

import org.apache.poi.hssf.usermodel.*;

import java.io.FileInputStream;

import java.io.FileOutputStream;

import java.io.IOException;

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;

 

//

public class ExcelPOI {

 

    /**

     * @param args

     * @throws IOException

     * java xuzhe.ExcelPOI

     */

    @SuppressWarnings("deprecation")

    public static void main(String[] args)throws IOException {

       // TODO Auto-generatedmethod stub

       ExcelPOI.POICreateExcel();

       ExcelPOI.POIReadExcel();

 

    }

   

    public static void POICreateExcel() throws IOException

    {

       HSSFWorkbook wb = new HSSFWorkbook();

       HSSFSheet sheet = wb.createSheet("new sheet");

       //0行

       HSSFRow row = sheet.createRow((short)0);

       //1列

       row.createCell((short)1).setCellValue("HelloWorld");

      

       FileOutputStream fileOut = new FileOutputStream("c:\\workbook.xls");

       wb.write(fileOut);

       fileOut.close();

    }

 

    private static HSSFWorkbook readFile(String filename) throws IOException {

       return new HSSFWorkbook(new FileInputStream(filename));

    }

 

    public static void POIReadExcel() throws IOException

     {

        String fileName = "c:\\name.xls";

        HSSFWorkbook wb =ExcelPOI.readFile(fileName);

 

           System.out.println("Datadump:\n");

 

           for (int k = 0; k < wb.getNumberOfSheets(); k++)

           {

              HSSFSheet sheet = wb.getSheetAt(k);

              int rows = sheet.getPhysicalNumberOfRows();

              System.out.println("Sheet" + k +" \"" +wb.getSheetName(k) + "\" has" + rows

                     + "row(s).");

              for (int r = 0; r < rows; r++)

              {

                  HSSFRow row = sheet.getRow(r);

                  if (row ==null) {

                     continue;

                  }

 

                  int cells = row.getPhysicalNumberOfCells();

                  System.out.println("\nROW" + row.getRowNum() +" has " + cells

                         + "cell(s).");

                  for (int c = 0; c < cells; c++)

                  {

                     HSSFCell cell = row.getCell(c);

                     String value = null;

 

                     switch (cell.getCellType())

                     {

 

                         case HSSFCell.CELL_TYPE_FORMULA:

                            value = "FORMULA value=" +cell.getCellFormula();

                            break;

 

                         case HSSFCell.CELL_TYPE_NUMERIC:

                             value= "NUMERIC value=" + cell.getNumericCellValue();

                            break;

 

                         case HSSFCell.CELL_TYPE_STRING:

                            value = "STRING value=" +cell.getStringCellValue();

                            break;

 

                         default:

                     }

                     System.out.println("CELL col=" + cell.getColumnIndex()+" VALUE="

                            + value);

                  }

              }

           }

     }

}

5、easypoi

easypoi功能如同名字easy,主打的功能就是容易,让一个没见接触过poi的人员

就可以方便的写出Excel导出,Excel模板导出,Excel导入,Word模板导出,通过简单的注解和模板
语言(熟悉的表达式语法),完成以前复杂的写法
一下是一个通过使用模板导入excel的例子：

List<CompanyHasImgModel> list;

    @Before
    public void initData() {
        list = new ArrayList<CompanyHasImgModel>();
        list.add(new CompanyHasImgModel("百度", "imgs/company/baidu.png", "北京市海淀区西北旺东路10号院百度科技园1号楼"));
        list.add(new CompanyHasImgModel("阿里巴巴", "imgs/company/ali.png", "北京市海淀区西北旺东路10号院百度科技园1号楼"));
        list.add(new CompanyHasImgModel("Lemur", "imgs/company/lemur.png", "亚马逊热带雨林"));
        list.add(new CompanyHasImgModel("一众", "imgs/company/one.png", "山东济宁俺家"));


    }

    @Test
    public void exportCompanyImg() throws Exception {

        File savefile = new File("D:/excel/");
        if (!savefile.exists()) {
            savefile.mkdirs();
        }
        Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams(), CompanyHasImgModel.class, list);
        FileOutputStream fos = new FileOutputStream("D:/excel/ExcelExportHasImgTest.exportCompanyImg.xls");
        workbook.write(fos);
        fos.close();
    }

6、easyexcel
github地址:https://github.com/alibaba/easyexcel 原本在项目中使用EasyPoi读取excel,后来为了统一技术方案,改用阿里的EasyExcel.EasyExcel和EasyPoi有一定的相似之处. EasyExcel和EasyPoi效率对比: 因为数据量少,从效率上看几乎没有差别,EasyExcel略胜一筹.

import com.alibaba.excel.annotation.ExcelProperty;
import io.swagger.annotations.ApiModelProperty;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
 
import java.io.Serializable;
import java.math.BigDecimal;
import java.util.Date;
 
/**
 * <p>ClassName：ImportTestDatasReq</p >
 * <p>Description：</p >
 * <p>Date：2021/9/2</p >
 */
@Data
@AllArgsConstructor
@NoArgsConstructor
public class ImportTestDatasReq implements Serializable {
    @ExcelProperty(value = "userName")
    @ApiModelProperty("用户姓名")
    private String userName;
 
    @ExcelProperty(value = "address")
    @ApiModelProperty("地址")
    private String address;
 
    @ExcelProperty(value = "price")
    @ApiModelProperty("价格")
    private BigDecimal price;
 
    @ExcelProperty(value = "createDate")
    @ApiModelProperty("创建时间")
    private Date createDate;
}


@Slf4j
@Api(tags = "Excel文件操作管理")
@RestController
@RequestMapping("/excel")
public class ExcelController extends BaseController {
 
 
    /**
     * <p> MultipartFile 这个类一般是用来接收前台传过来的文件信息 </p >
    */
    @ApiOperation(value = "导入XXX数据")
    @PostMapping("/importTestDatas")
    public CommonResult importTestDatas(@RequestParam("multipartFile") MultipartFile multipartFile) throws BizException, IOException {
        isBlank(multipartFile, "参数");
 
        List<ImportTestDatasReq> ImportTestDatasReqList = EasyExcelUtil.readExcel(multipartFile.getInputStream(), ImportTestDatasReq.class, new EasyExcelUtil.ExcelListener<>());
        ImportTestDatasReqList.forEach(System.out::println);
        return success(null);
    }
}

1、@HeadRowHeight：设置表头高度，作用域在类
2、@ExcelIgnoreUnannotated：忽略没有注解标记的字段，作用域在类
3、@ExcelIgnore：忽略指定的字段，作用域在字段
4、@ExcelProperty：指定导出列名和索引，作用域在字段
5、@ColumnWidth：设置列的宽度，作用域在字段
6、@NumberFormat：设置数值精度，作用域在字段，例：@NumberFormat(value = “#.00”)
7、@DateTimeFormat：格式化日期，作用域在字段，例：@DateTimeFormat(value = “yyyy-MM-dd”)
注意：我们可以使用 EasyExcel.write().registerWriteHandler(new Custemhandler()) 设置导出列的宽度为自适应，但是使用 registerWriteHandler(new Custemhandler()) 方法之后不能再使用注解@ColumnWidth，否则会导致自适应宽度不生效