近期处理的数据规模比较大，正好又是统计合并的事情，想着借助excel就可以完成了，然后就了解了下java读取excel的事情。
读取的文件主要分两类：xls文件、xlsx文件。xls文件的相关操作用的是jxl.jar包，只要将这个包导入即可。xlsx文件的相关操作是利用apache的poi包。
一、xls文件（一个jar包：jxl.jar）
1）创建
package jexcel; import java.io.*;
import jxl.*;
import jxl.write.*; /**
* @author Ken
*
* To change the template for this generated type comment go to
* Window>Preferences>Java>Code Generation>Code and Comments
*/
public class CreateXLS { public static void main(String[] args) {
try {
//open file.
WritableWorkbook book = Workbook.createWorkbook(new File("d:/Test.xls")); //create Sheet named "Sheet_1". 0 means this is 1st page.
WritableSheet sheet = book.createSheet("Sheet_1", ); //define cell column and row in Label Constructor, and cell content write "test".
//cell is 1st-Column,1st-Row. value is "test".
Label label = new Label(, , "test");
//add defined cell above to sheet instance.
sheet.addCell(label); //create cell using add numeric. WARN:necessarily use integrated package-path, otherwise will be throws path-error.
//cell is 2nd-Column, 1st-Row. value is 789.123.
jxl.write.Number number = new jxl.write.Number(, , 789.123);
//add defined cell above to sheet instance.
sheet.addCell(number); //add defined all cell above to case.
book.write();
//close file case.
book.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
2）读取
package jexcel;
import java.io.*;
import jxl.*; /**
* @author Ken
*
* To change the template for this generated type comment go to
* Window>Preferences>Java>Code Generation>Code and Comments
*/
public class ReadXLS { public static void main(String[] args) {
try {
Workbook book = Workbook.getWorkbook(new File("e:/pos/rule.xls"));
//get a Sheet object.
Sheet sheet = book.getSheet();
//get 1st-Column,1st-Row content.
// Cell cell = sheet.getCell(1, 2); //先是列序号，然后是行序号，都是从0开始算起
Cell[] cell=sheet.getColumn();
for(int i=;i<cell.length;i++){
String result = cell[i].getContents();
System.out.println(result);
} book.close();
} catch (Exception e) {
e.printStackTrace();
} }
}
3）修改
package jexcel; import java.io.*;
import jxl.*;
import jxl.write.*; /**
* @author Ken
*
* To change the template for this generated type comment go to
* Window>Preferences>Java>Code Generation>Code and Comments
*/
public class UpdateXLS { public static void main(String[] args) {
try {
//get file.
Workbook wb = Workbook.getWorkbook(new File("d:/Test.xls"));
//open a copy file(new file), then write content with same content with Test.xls.
WritableWorkbook book =
Workbook.createWorkbook(new File("d:/Test.xls"), wb);
//add a Sheet.
WritableSheet sheet = book.createSheet("Sheet_2", );
sheet.addCell(new Label(, , "test2"));
book.write();
book.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
二、xlsx文件（五个jar包：poi-3.8-20120326.jar; poi-ooxml-3.8-20120326.jar; poi-ooxml-schemas-3.8-20120326.jar; xmlbeans-2.3.0.jar; dom4j-1.6.1.jar）
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Date;
import java.text.SimpleDateFormat;
import java.util.TimeZone;
import java.util.logging.Logger; 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 xlsx { public static void main(String[] args) {
// TODO Auto-generated method stub
poiExcel("e:/pos/rules.xlsx");
} private static void poiExcel(String saveName){
try{
String realPath =saveName;
File fileDes = new File(realPath);
InputStream str = new FileInputStream(fileDes);
XSSFWorkbook xwb = new XSSFWorkbook(str); //利用poi读取excel文件流
XSSFSheet st = xwb.getSheetAt(); //读取sheet的第一个工作表
int rows=st.getLastRowNum();//总行数
int cols;//总列数
//log.info("========行========"+rows);
for(int i=;i<rows;i++){
XSSFRow row=st.getRow(i);//读取某一行数据
if(row!=null){
//获取行中所有列数据
cols=row.getLastCellNum();
//log.info("========行========"+rows+"=====列========"+cols); for(int j=;j<cols;j++){
XSSFCell cell=row.getCell(j);
if(cell==null){
System.out.print(" ");
}else{
//判断单元格的数据类型
switch (cell.getCellType()) {
case XSSFCell.CELL_TYPE_NUMERIC: // 数字
System.out.print(cell.getNumericCellValue() + " ");
break;
case XSSFCell.CELL_TYPE_STRING: // 字符串
System.out.print(cell.getStringCellValue() + " ");
break;
case XSSFCell.CELL_TYPE_BOOLEAN: // Boolean
System.out.println(cell.getBooleanCellValue() + " ");
break;
case XSSFCell.CELL_TYPE_FORMULA: // 公式
System.out.print(cell.getCellFormula() + " ");
break;
case XSSFCell.CELL_TYPE_BLANK: // 空值
System.out.println("");
break;
case XSSFCell.CELL_TYPE_ERROR: // 故障
System.out.println("故障");
break;
default:
System.out.print("未知类型 ");
break;
}
}
} }
}
}catch(IOException e){
e.printStackTrace();
} } }
代码全是网上贴来的，大家一起分享。。
想把jar包也贴上来的，但是不知道是不支持还是我自己摸索不出来，反正最后是没贴出来。。。