推荐使用poi，这是用的最多的，导入导出都非常方便。
JAVA 使用POI制作表格，而且都是开源的。POI是Apace公司开发的，对中文的支持比较弱一些；而JExcelAPI是韩国公司开发的，不仅对中文的支持好，而且由于是纯JAVA编写的，所以可以跨平台 *** 作。
HSSFCell cell = row.createCell((short) 0)
//设置此单元格的格式为文本，此句可以省略，Excel会自动识别。
//其他还有几种常用的格式，请参考本文底部的补充部分。
cell.setCellType(HSSFCell.CELL_TYPE_STRING)
//此处是3.0.1版的改进之处，上一版可以直接setCellValue("Hello, World!")，
//但是在3.0.1里，被deprecated了。
cell.setCellValue(new HSSFRichTextString("Hello, World!"))
//创建一个文件输出流，指定到C盘根目录下（C盘都有吧？）
//xls是Excel97-2003的标准扩展名，2007是xlsx，目前的POI能直接生产的还是xls格式，
//如果此处把扩展名改成xlsx，在用Excel2007打开此文件时会报错。
小技巧，快捷 *** 作：可以用鼠标左键选中poi-3.0.1-FINAL-20070705.jar但不松开，拖到任务栏的Eclipse图标上等候1秒左右，Eclipse会自动d起来，依然不松开移动到lib文件夹上，这个时候鼠标后面跟个十字符号，松开左键，就完成了复制动作。这个是对整个windows系统都好用的快捷复制方式，视源盘符和目标盘符的不同偶尔会用到Ctrl键。
这篇blog主要是讲述java中poi读取excel，而excel的版本包括：2003-2007和2010两个版本， 即excel的后缀名为：xls和xlsx。
读取excel和MySQL相关： java的poi技术读取Excel数据到MySQL
代码如下
/***
*/
package com.b510.common
/**
* @author Hongten
* @created 2014-5-21
*/
public class Common {
public static final String OFFICE_EXCEL_2003_POSTFIX = "xls"
public static final String OFFICE_EXCEL_2010_POSTFIX = "xlsx"
public static final String EMPTY = ""
public static final String POINT = "."
public static final String LIB_PATH = "lib"
public static final String STUDENT_INFO_XLS_PATH = LIB_PATH + "/student_info" + POINT + OFFICE_EXCEL_2003_POSTFIX
public static final String STUDENT_INFO_XLSX_PATH = LIB_PATH + "/student_info" + POINT + OFFICE_EXCEL_2010_POSTFIX
public static final String NOT_EXCEL_FILE = " : Not the Excel file!"
public static final String PROCESSING = "Processing..."
}
/**
*
*/
package com.b510.excel
import java.io.FileInputStream
import java.io.IOException
import java.io.InputStream
import java.util.ArrayList
import java.util.List
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
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
import com.b510.common.Common
import com.b510.excel.util.Util
import com.b510.excel.vo.Student
/**
* @author Hongten
* @created 2014-5-20
*/
public class ReadExcel {
/**
* read the Excel file
* @param path the path of the Excel file
* @return
* @throws IOException
*/
public List<Student> readExcel(String path) throws IOException {
if (path == null || Common.EMPTY.equals(path)) {
return null
} else {
String postfix = Util.getPostfix(path)
if (!Common.EMPTY.equals(postfix)) {
if (Common.OFFICE_EXCEL_2003_POSTFIX.equals(postfix)) {
return readXls(path)
} else if (Common.OFFICE_EXCEL_2010_POSTFIX.equals(postfix)) {
return readXlsx(path)
}
} else {
System.out.println(path + Common.NOT_EXCEL_FILE)
}
}
return null
}
/**
* Read the Excel 2010
* @param path the path of the excel file
* @return
* @throws IOException
*/
public List<Student> readXlsx(String path) throws IOException {
System.out.println(Common.PROCESSING + path)
InputStream is = new FileInputStream(path)
XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is)
Student student = null
List<Student> list = new ArrayList<Student>()
// Read the Sheet
for (int numSheet = 0 numSheet < xssfWorkbook.getNumberOfSheets() numSheet++) {
XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet)
if (xssfSheet == null) {
continue
}
// Read the Row
for (int rowNum = 1 rowNum <= xssfSheet.getLastRowNum() rowNum++) {
XSSFRow xssfRow = xssfSheet.getRow(rowNum)
if (xssfRow != null) {
student = new Student()
XSSFCell no = xssfRow.getCell(0)
XSSFCell name = xssfRow.getCell(1)
XSSFCell age = xssfRow.getCell(2)
XSSFCell score = xssfRow.getCell(3)
student.setNo(getValue(no))
student.setName(getValue(name))
student.setAge(getValue(age))
student.setScore(Float.valueOf(getValue(score)))
list.add(student)
}
}
}
return list
}
/**
* Read the Excel 2003-2007
* @param path the path of the Excel
* @return
* @throws IOException
*/
public List<Student> readXls(String path) throws IOException {
System.out.println(Common.PROCESSING + path)
InputStream is = new FileInputStream(path)
HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is)
Student student = null
List<Student> list = new ArrayList<Student>()
// Read the Sheet
for (int numSheet = 0 numSheet < hssfWorkbook.getNumberOfSheets() numSheet++) {
HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet)
if (hssfSheet == null) {
continue
}
// Read the Row
for (int rowNum = 1 rowNum <= hssfSheet.getLastRowNum() rowNum++) {
HSSFRow hssfRow = hssfSheet.getRow(rowNum)
if (hssfRow != null) {
student = new Student()
HSSFCell no = hssfRow.getCell(0)
HSSFCell name = hssfRow.getCell(1)
HSSFCell age = hssfRow.getCell(2)
HSSFCell score = hssfRow.getCell(3)
student.setNo(getValue(no))
student.setName(getValue(name))
student.setAge(getValue(age))
student.setScore(Float.valueOf(getValue(score)))
list.add(student)
}
}
}
return list
}
@SuppressWarnings("static-access")
private String getValue(XSSFCell xssfRow) {
if (xssfRow.getCellType() == xssfRow.CELL_TYPE_BOOLEAN) {
return String.valueOf(xssfRow.getBooleanCellValue())
} else if (xssfRow.getCellType() == xssfRow.CELL_TYPE_NUMERIC) {
return String.valueOf(xssfRow.getNumericCellValue())
} else {
return String.valueOf(xssfRow.getStringCellValue())
}
}
@SuppressWarnings("static-access")
private String getValue(HSSFCell hssfCell) {
if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {
return String.valueOf(hssfCell.getBooleanCellValue())
} else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {
return String.valueOf(hssfCell.getNumericCellValue())
} else {
return String.valueOf(hssfCell.getStringCellValue())
}
}
}
/**
*
*/
package com.b510.excel.client
import java.io.IOException
import java.util.List
import com.b510.common.Common
import com.b510.excel.ReadExcel
import com.b510.excel.vo.Student
/**
* @author Hongten
* @created 2014-5-21
*/
public class Client {
public static void main(String[] args) throws IOException {
String excel2003_2007 = Common.STUDENT_INFO_XLS_PATH
String excel2010 = Common.STUDENT_INFO_XLSX_PATH
// read the 2003-2007 excel
List<Student> list = new ReadExcel().readExcel(excel2003_2007)
if (list != null) {
for (Student student : list) {
System.out.println("No. : " + student.getNo() + ", name : " + student.getName() + ", age : " + student.getAge() + ", score : " + student.getScore())
}
}
System.out.println("======================================")
// read the 2010 excel
List<Student> list1 = new ReadExcel().readExcel(excel2010)
if (list1 != null) {
for (Student student : list1) {
System.out.println("No. : " + student.getNo() + ", name : " + student.getName() + ", age : " + student.getAge() + ", score : " + student.getScore())
}
}
}
}
/**
*
*/
package com.b510.excel.util
import com.b510.common.Common
/**
* @author Hongten
* @created 2014-5-21
*/
public class Util {
/**
* get postfix of the path
* @param path
* @return
*/
public static String getPostfix(String path) {
if (path == null || Common.EMPTY.equals(path.trim())) {
return Common.EMPTY
}
if (path.contains(Common.POINT)) {
return path.substring(path.lastIndexOf(Common.POINT) + 1, path.length())
}
return Common.EMPTY
}
}
/**
*
*/
package com.b510.excel.vo
/**
* Student
*
* @author Hongten
* @created 2014-5-18
*/
public class Student {
/**
* id
*/
private Integer id
/**
* 学号
*/
private String no
/**
* 姓名
*/
private String name
/**
* 学院
*/
private String age
/**
* 成绩
*/
private float score
public Integer getId() {
return id
}
public void setId(Integer id) {
this.id = id
}
public String getNo() {
return no
}
public void setNo(String no) {
this.no = no
}
public String getName() {
return name
}
public void setName(String name) {
this.name = name
}
public String getAge() {
return age
}
public void setAge(String age) {
this.age = age
}
public float getScore() {
return score
}
public void setScore(float score) {
this.score = score
}
}
欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）