本文目录一览：
Java操作excel的问题
Java创建excel表格的基本步骤：
1.创建一个WritableWorkbook对象(用Workbook的createWorkbook方法创建)，要指定创建一个文件；
2.创建一个工作表WritableSheet(用workbook对象的createSheet方法创建)，注意要是WritableSheet，说明可以对其写；
3.创建单元格，再将单元格加入到sheet里；
4.执行workbook的write()方法进行写操作最后关闭workbook。
浅谈JAVA读写Excel的几种途径
需要对Excel中的数据进行读取操作。一、在开始进行Java读写Excel前，需要先下一个jxl的jar包，这个jar包中提供了相关读写Excel的方法，将jxl.jar放到classpath下或者在工程的buildpath中添加jxl.jar后，便可以开始Java读写Excel了。二、Java读取Excel数据,首先，创建一个xls文件（如：jxltest.xls），然后在文件中添加一些数据，Excel文件创建完成后，便可以开始写代码读取了。三、进行一个小小的扩展，读取一个目录下的所有Excel文件，读取的每个Excel文件的数据写入到不同的txt中。四、生成EXCEL需要手动写查询语句把ORACLE数据库中的数据查询出来，再通过操作写到EXCEL文件里面。通过EXCEL把数据读取到ORACLE，同样需要去读取EXCEL工作薄里面的内容，再通过INSERT语句去插入数据库操作。
Java对Excel解析(求助)
这篇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 ListStudent 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 ListStudent readXlsx(String path) throws IOException {
System.out.println(Common.PROCESSING + path);
InputStream is = new FileInputStream(path);
XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);
Student student = null;
ListStudent list = new ArrayListStudent();
// 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 ListStudent readXls(String path) throws IOException {
System.out.println(Common.PROCESSING + path);
InputStream is = new FileInputStream(path);
HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
Student student = null;
ListStudent list = new ArrayListStudent();
// 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
ListStudent 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
ListStudent 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;
}
}
java怎么读取excel数据
引入poi的jar包，大致如下：
读取代码如下，应该能看得明白吧
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
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 ExcelUtil2007 {
/**读取excel文件流的指定索引的sheet
* @param inputStream excel文件流
* @param sheetIndex 要读取的sheet的索引
* @return
* @throws FileNotFoundException
* @throws IOException
*/
public static XSSFSheet readExcel(InputStream inputStream,int sheetIndex) throws FileNotFoundException, IOException
{
return readExcel(inputStream).getSheetAt(sheetIndex);
}
/**读取excel文件的指定索引的sheet
* @param filePath excel文件路径
* @param sheetIndex 要读取的sheet的索引
* @return
* @throws IOException
* @throws FileNotFoundException
*/
public static XSSFSheet readExcel(String filePath,int sheetIndex) throws FileNotFoundException, IOException
{
return readExcel(filePath).getSheetAt(sheetIndex);
}
/**读取excel文件的指定索引的sheet
* @param filePath excel文件路径
* @param sheetName 要读取的sheet的名称
* @return
* @throws IOException
* @throws FileNotFoundException
*/
public static XSSFSheet readExcel(String filePath,String sheetName) throws FileNotFoundException, IOException
{
return readExcel(filePath).getSheet(sheetName);
}
/**读取excel文件，返回XSSFWorkbook对象
* @param filePath excel文件路径
* @return
* @throws FileNotFoundException
* @throws IOException
*/
public static XSSFWorkbook readExcel(String filePath) throws FileNotFoundException, IOException
{
XSSFWorkbook wb=new XSSFWorkbook(new FileInputStream(filePath));
return wb;
}
/**读取excel文件流，返回XSSFWorkbook对象
* @param inputStream excel文件流
* @return
* @throws FileNotFoundException
* @throws IOException
*/
public static XSSFWorkbook readExcel(InputStream inputStream) throws FileNotFoundException, IOException
{
XSSFWorkbook wb=new XSSFWorkbook(inputStream);
return wb;
}
/***读取excel中指定的单元格，并返回字符串形式的值
* 1.数字
* 2.字符
* 3.公式（返回的为公式内容，非单元格的值）
* 4.空
* @param st 要读取的sheet对象
* @param rowIndex 行索引
* @param colIndex 列索引
* @param isDate 是否要取的是日期（是则返回yyyy-MM-dd格式的字符串）
* @return
*/
public static String getCellString(XSSFSheet st,int rowIndex,int colIndex,boolean isDate){
String s="";
XSSFRow row=st.getRow(rowIndex);
if(row == null) return "";
XSSFCell cell=row.getCell(colIndex);
if(cell == null) return "";
if (cell.getCellType() == 0) {//数字
if(isDate)s=new SimpleDateFormat("yyyy-MM-dd").format(cell.getDateCellValue());
else s = trimPointO(String.valueOf(getStringValue(cell)).trim());
}else if (cell.getCellType() == 1){//字符（excel中的空格，不是全角，也不是半角，不知道是神马,反正就是" "这个）
s=cell.getRichStringCellValue().getString().replaceAll(" ", " ").trim();
// s=cell.getStringCellValue();//07API新增，好像跟上一句一致
}
else if (cell.getCellType() == 2){//公式
s=cell.getCellFormula();
}
else if (cell.getCellType() == 3){//空
s="";
}
return s;
}
/**如果数字以 .0 结尾，则去掉.0
* @param s
* @return
*/
public static String trimPointO(String s) {
if (s.endsWith(".0"))
return s.substring(0, s.length() - 2);
else
return s;
}
/**处理科学计数法和百分比模式的数字单元格
* @param cell
* @return
*/
public static String getStringValue(XSSFCell cell) {
String sValue = null;
short dataFormat = cell.getCellStyle().getDataFormat();
double d = cell.getNumericCellValue();
BigDecimal b = new BigDecimal(Double.toString(d));
//百分比样式的
if (dataFormat == 0xa || dataFormat == 9) {
b=b.multiply(new BigDecimal(100));
//String temp=b.toPlainString();
DecimalFormat df=new DecimalFormat("0.00");//保留两位小数的百分比格式
sValue = df.format(b) + "%";
}else{
sValue = b.toPlainString();
}
return sValue;
}
}
java怎样输出excel文件
//java生成简单的Excel文件
package beans.excel;
import java.io.IOException;
import java.io.OutputStream;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
public class SimpleExcelWrite {
public void createExcel(OutputStream os) throws WriteException,IOException{
//创建工作薄
WritableWorkbook workbook = Workbook.createWorkbook(os);
//创建新的一页
WritableSheet sheet = workbook.createSheet("First Sheet",0);
//创建要显示的内容,创建一个单元格，第一个参数为列坐标，第二个参数为行坐标，第三个参数为内容
Label xuexiao = new Label(0,0,"学校");
sheet.addCell(xuexiao);
Label zhuanye = new Label(1,0,"专业");
sheet.addCell(zhuanye);
Label jingzhengli = new Label(2,0,"专业竞争力");
sheet.addCell(jingzhengli);
Label qinghua = new Label(0,1,"清华大学");
sheet.addCell(qinghua);
Label jisuanji = new Label(1,1,"计算机专业");
sheet.addCell(jisuanji);
Label gao = new Label(2,1,"高");
sheet.addCell(gao);
Label beida = new Label(0,2,"北京大学");
sheet.addCell(beida);
Label falv = new Label(1,2,"法律专业");
sheet.addCell(falv);
Label zhong = new Label(2,2,"中");
sheet.addCell(zhong);
Label ligong = new Label(0,3,"北京理工大学");
sheet.addCell(ligong);
Label hangkong = new Label(1,3,"航空专业");
sheet.addCell(hangkong);
Label di = new Label(2,3,"低");
sheet.addCell(di);
//把创建的内容写入到输出流中，并关闭输出流
workbook.write();
workbook.close();
os.close();
}
}
