读取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,否则会导致自适应宽度不生效