本文目录一览：
Java操作excel的问题
Java创建excel表格的基本步骤：
- 创建一个WritableWorkbook对象(用Workbook的createWorkbook方法创建)，要指定创建一个文件；
- 创建一个工作表WritableSheet(用workbook对象的createSheet方法创建)，注意要是WritableSheet，说明可以对其写；
- 创建单元格，再将单元格加入到sheet里；
- 执行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;
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;
public class ReadExcel {
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;
}
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>();
for (int numSheet = 0; numSheet < xssfWorkbook.getNumberOfSheets(); numSheet++) {
XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(numSheet);
if (xssfSheet == null) {
continue;
}
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;
}
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>();
for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
if (hssfSheet == null) {
continue;
}
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;
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;
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("======================================");
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;
public class Util {
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;
public class Student {
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 {
public static XSSFSheet readExcel(InputStream inputStream, int sheetIndex) throws FileNotFoundException, IOException {
return readExcel(inputStream).getSheetAt(sheetIndex);
}
public static XSSFSheet readExcel(String filePath, int sheetIndex) throws FileNotFoundException, IOException {
return readExcel(filePath).getSheetAt(sheetIndex);
}
public static XSSFSheet readExcel(String filePath, String sheetName) throws FileNotFoundException, IOException {
return readExcel(filePath).getSheet(sheetName);
}
public static XSSFWorkbook readExcel(String filePath) throws FileNotFoundException, IOException {
XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(filePath));
return wb;
}
public static XSSFWorkbook readExcel(InputStream inputStream) throws FileNotFoundException, IOException {
XSSFWorkbook wb = new XSSFWorkbook(inputStream);
return wb;
}
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) {
s = cell.getRichStringCellValue().getString().replaceAll(" ", " ").trim();
} else if (cell.getCellType() == 2) {
s = cell.getCellFormula();
} else if (cell.getCellType() == 3) {
s = "";
}
return s;
}
public static String trimPointO(String s) {
if (s.endsWith(".0")) return s.substring(0, s.length() - 2);
else return s;
}
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));
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();
}
}
