java jxl解析excel_java解析excel两种（jxl、poi)

jxl：重量级的，现在很少用了。

jxl.jar

CreateXLS.java

package com.sec.excel.jxl;

import java.io.*;

import jxl.*;

import jxl.write.*;

public class CreateXLS {

public static void main(String[] args) {

try {

WritableWorkbook book = Workbook.createWorkbook(new

File("d:/Test.xls"));

WritableSheet sheet = book.createSheet("Sheet_1", 0);

// 主要是在sheet中加入一行字段

sheet.addCell(new Label(0, 0, "student_id"));

sheet.addCell(new Label(1, 0, "student_name"));

sheet.addCell(new Label(2, 0, "student_age"));

sheet.addCell(new Label(3, 0, "student_score"));

// 下面添加两行数据

sheet.addCell(new Label(0, 1, "10010"));

sheet.addCell(new Label(1, 1, "张三"));

sheet.addCell(new Label(2, 1, "20"));

sheet.addCell(new Label(3, 1, "89"));

book.write();

book.close();

System.out.println("文件写入成功");

} catch (Exception e) {

e.printStackTrace();

}

}

}

ReadXLS.java

package com.sec.excel.jxl;

import java.io.*;

import jxl.*;

public class ReadXLS {

public static void main(String[] args) {

Workbook book = null;

try {

book = Workbook.getWorkbook(new File("d:/Test.xls"));

Sheet sheet = book.getSheet(0);  //book.getSheet(Sheet_1)

// 循环输出excel中的数据

for (int row = 0; row < 2; row++) { // 第一行

for (int col = 0; col < 4; col++) { // 第一列

System.out.print(sheet.getCell(col, row).getContents()

+ "\t");

}

System.out.println();

}

} catch (Exception e) {

e.printStackTrace();

} finally {

book.close();

}

}

}

UpdateXLS.java

package com.sec.excel.jxl;

import java.io.File;

import java.io.IOException;

import jxl.CellType;

import jxl.Workbook;

import jxl.read.biff.BiffException;

import jxl.write.Label;

import jxl.write.WritableCell;

import jxl.write.WritableSheet;

import jxl.write.WritableWorkbook;

import jxl.write.WriteException;

public class UpdateXLS {

public static void main(String[] args) {

Workbook workBook = null;

WritableWorkbook copy = null;

WritableSheet sheet = null;

try {

System.out.println("开始修改文件...");

//得到workbook

workBook =

Workbook.getWorkbook(new File("d:/Test.xls"));

//复制workbook并保存到student_copy.xls

copy =

Workbook.createWorkbook(new File("D:\\Test_copy.xls"),

workBook);

//得到Student

sheet =

copy.getSheet("Sheet_1");

//拿到指定的cell(这里是第2行，第2列的数据)

WritableCell cell = sheet.getWritableCell(1,

1);

//修改数据

if

(cell.getType() == CellType.LABEL) {

Label l = (Label) cell;

l.setString("王五");

}

copy.write();

copy.close();

System.out.println("文件修改成功，并已保存至student_copy.xls");

} catch (BiffException e) {

e.printStackTrace();

} catch (IOException e) {

e.printStackTrace();

} catch (WriteException e) {

e.printStackTrace();

}

}

}

poi:轻量级的。

poi.jar

poi-ooxml.jar

CreateExcel.java

package com.sec.excel.poi;

import java.io.FileOutputStream;

import java.io.IOException;

import java.util.Random;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import

org.apache.poi.openxml4j.exceptions.InvalidFormatException;

import org.apache.poi.ss.usermodel.Row;

import org.apache.poi.ss.usermodel.Sheet;

import org.apache.poi.ss.usermodel.Workbook;

public class CreateExcel {

public static void main(String[] args) throws

InvalidFormatException {

Random rd = new Random();

try {

Workbook book = new HSSFWorkbook();

Sheet sheet = book.createSheet("学生信息");

Row row = sheet.createRow(0);

row.createCell(0).setCellValue("班级");

row.createCell(1).setCellValue("编号");

row.createCell(2).setCellValue("年龄");

row.createCell(3).setCellValue("成绩");

for (int i = 1; i < 12; i++) {

//随机生成年龄  成绩

int age = rd.nextInt(10)+17;

int score = rd.nextInt(101);

row = sheet.createRow(i);

row.createCell(0).setCellValue("T001"+i);

row.createCell(1).setCellValue(i+"号");

row.createCell(2).setCellValue(age);

row.createCell(3).setCellValue(score);

}

FileOutputStream out = new FileOutputStream("d:/student.xls");

//2003及以前版本

//FileOutputStream out = new

FileOutputStream("d:/student.xlsx");

//2007版本

book.write(out);

out.close();

System.out.println("搞定.......噢啦.......");

} catch (IOException e) {

e.printStackTrace();

}

}

}

ReaderExcel.java

package com.sec.excel.poi;

import java.io.File;

import java.io.FileInputStream;

import java.text.DecimalFormat;

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 ReaderExcel {

public static void main(String[] args) {

try {

//工作薄  写入内存

HSSFWorkbook work = new HSSFWorkbook(new FileInputStream(new

File("d:/student.xls")));

//工作表  相当于 xml的根节点

HSSFSheet sheet = work.getSheet("学生信息");

//work.getSheetAt(0);

//双重循环输出

for(int i=0;i

//得到行

HSSFRow row = sheet.getRow(i);

for(int j=0;j

//根据不同类型 解析

if(row.getCell(j).getCellType() ==

HSSFCell.CELL_TYPE_NUMERIC){

double data = row.getCell(j).getNumericCellValue();

//格式化

java.text.DecimalFormat df = new DecimalFormat("#");

String cdata = df.format(data);

System.out.print(cdata+"\t");

}

else if(row.getCell(j).getCellType() ==

HSSFCell.CELL_TYPE_STRING){

String data = row.getCell(j).getStringCellValue();

System.out.print(data+"\t");

}

else{

System.out.print("未知类型");

}

}

System.out.println();

}

} catch (Exception e) {

e.printStackTrace();

}

}

}

ReaderExcel2007.java

package com.sec.excel.poi;

import java.io.File;

import java.io.FileInputStream;

import java.text.DecimalFormat;

import org.apache.poi.hssf.usermodel.HSSFCell;

import org.apache.poi.xssf.usermodel.XSSFRow;

import org.apache.poi.xssf.usermodel.XSSFSheet;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ReaderExcel2007 {

public static void main(String[] args) {

try {

//工作薄  写入内存

XSSFWorkbook work = new XSSFWorkbook(new FileInputStream(new

File("d:/student.xlsx")));

//工作表  相当于 xml的根节点

XSSFSheet sheet = work.getSheet("学生信息");

//work.getSheetAt(0);

//双重循环输出

for(int i=0;i

//得到行

XSSFRow row = sheet.getRow(i);

for(int j=0;j

//根据不同类型 解析

if(row.getCell(j).getCellType() ==

HSSFCell.CELL_TYPE_NUMERIC){

double data = row.getCell(j).getNumericCellValue();

//格式化

java.text.DecimalFormat df = new DecimalFormat("#");

String cdata = df.format(data);

System.out.print(cdata+"\t");

}

else if(row.getCell(j).getCellType() ==

HSSFCell.CELL_TYPE_STRING){

String data = row.getCell(j).getStringCellValue();

System.out.print(data+"\t");

}

else{

System.out.print("未知类型");

}

}

System.out.println();

}

} catch (Exception e) {

e.printStackTrace();

}

}

}

UpdateExcel.java

package com.sec.excel.poi;

import java.io.File;

import java.io.FileInputStream;

import java.io.FileOutputStream;

import org.apache.poi.ss.usermodel.Row;

import org.apache.poi.ss.usermodel.Sheet;

import org.apache.poi.ss.usermodel.Workbook;

import org.apache.poi.ss.usermodel.WorkbookFactory;

public class UpdateExcel {

@SuppressWarnings("static-access")

public static void main(String[] args) {

try {

//工作薄  将要操作的文件  加载到内存

Workbook work = new WorkbookFactory().create(new

FileInputStream(new File("d:/student.xls")));

//工作表

Sheet sheet = work.getSheet("学生信息");  //也可以通过下标得到work.getSheetAt(0);

Row row = sheet.createRow(sheet.getLastRowNum()+1);

//在最后追加一行数据

row.createCell(0).setCellValue("T001");

//第一列

row.createCell(1).setCellValue("赵六");  //第二列

row.createCell(2).setCellValue("21");

row.createCell(3).setCellValue("78");

FileOutputStream fout = new

FileOutputStream("d:/student_add.xls");

work.write(fout);

fout.close();

System.out.println("搞定..............");

} catch (Exception e) {

e.printStackTrace();

}

}

}

注：Apache中的poi包中的XSSFWorkbook与HSSFWorkbook的区别

HSSFWorkbook:是操作Excel2003以前(包括2003)的版本，扩展名是.xls

XSSFWorkbook:是操作Excel2007的版本，扩展名是.xlsx

操作图片

Java代码

public static void write()throws Exception{

WritableWorkbook wwb=Workbook.createWorkbook(new

File("c:/1.xls"));

WritableSheet ws=wwb.createSheet("Test Sheet

1",0);

File file=new

File("C://jbproject//PVS//WebRoot//weekhit//1109496996281.png");

WritableImage image=new WritableImage(1, 4, 6,

18,file);

ws.addImage(image);

wwb.write();

wwb.close();

}

public static void write()throws Exception{

WritableWorkbook wwb=Workbook.createWorkbook(new

File("c:/1.xls"));

WritableSheet ws=wwb.createSheet("Test Sheet

1",0);

File file=new

File("C://jbproject//PVS//WebRoot//weekhit//1109496996281.png");

WritableImage image=new WritableImage(1, 4, 6,

18,file);

ws.addImage(image);

wwb.write();

wwb.close();

}

读:

读的时候是这样的一个思路,先用一个输入流(InputStream)得到Excel文件,然后用jxl中的Workbook得到工作薄,用Sheet从工作薄中得到工作表,用Cell得到工作表中得某个单元格.

InputStream->Workbook->Sheet->Cell,就得到了excel文件中的单元格

Java代码

String path="c://excel.xls";//Excel文件URL

InputStream is = new

FileInputStream(path);//写入到FileInputStream

jxl.Workbook wb = Workbook.getWorkbook(is); //得到工作薄

jxl.Sheet st = wb.getSheet(0);//得到工作薄中的第一个工作表

Cell cell=st.getCell(0,0);//得到工作表的第一个单元格,即A1

String

content=cell.getContents();//getContents()将Cell中的字符转为字符串

wb.close();//关闭工作薄

is.close();//关闭输入流

String path="c://excel.xls";//Excel文件URL

InputStream is = new

FileInputStream(path);//写入到FileInputStream

jxl.Workbook wb =

Workbook.getWorkbook(is); //得到工作薄

jxl.Sheet st =

wb.getSheet(0);//得到工作薄中的第一个工作表

Cell cell=st.getCell(0,0);//得到工作表的第一个单元格,即A1

String

content=cell.getContents();//getContents()将Cell中的字符转为字符串

wb.close();//关闭工作薄

is.close();//关闭输入流

通过Sheet的getCell(x,y)方法得到任意一个单元格,x,y和excel中的坐标对应.

例如A1对应(0,0),A2对应(0,1),D3对应(3,2).Excel中坐标从A,1开始,jxl中全部是从0开始.

还可以通过Sheet的getRows(),getColumns()方法得到行数列数,并用于循环控制,输出一个sheet中的所有内容.

写:

往Excel中写入内容主要是用jxl.write包中的类.

思路是这样的:

OutputStream

这里面Label代表的是写入Sheet的Cell位置及内容.

Java代码

OutputStream os=new

FileOutputStream("c://test.xls");//输出的Excel文件URL

WritableWorkbook wwb = Workbook.createWorkbook(os);//创建可写工作薄

WritableSheet ws = wwb.createSheet("sheet1", 0);//创建可写工作表

Label labelCF=new Label(0, 0, "hello");//创建写入位置和内容

ws.addCell(labelCF);//将Label写入sheet中

Label的构造函数Label(int x, int y,String

aString)xy意同读的时候的xy,aString是写入的内容.

WritableFont wf = new WritableFont(WritableFont.TIMES, 12,

WritableFont.BOLD, false);//设置写入字体

WritableCellFormat wcfF = new

WritableCellFormat(wf);//设置CellFormat

Label labelCF=new Label(0, 0, "hello");//创建写入位置,内容和格式

Label的另一构造函数Label(int c, int r, String cont, CellFormat

st)可以对写入内容进行格式化,设置字体及其它的属性.

现在可以写了

wwb.write();

写完后关闭

wwb.close();

输出流也关闭吧

os.close;

OutputStream os=new

FileOutputStream("c://test.xls");//输出的Excel文件URL

WritableWorkbook wwb = Workbook.createWorkbook(os);//创建可写工作薄

WritableSheet ws = wwb.createSheet("sheet1", 0);//创建可写工作表

Label labelCF=new Label(0, 0, "hello");//创建写入位置和内容

ws.addCell(labelCF);//将Label写入sheet中

Label的构造函数Label(int x, int y,String

aString)xy意同读的时候的xy,aString是写入的内容.

WritableFont wf = new WritableFont(WritableFont.TIMES, 12,

WritableFont.BOLD, false);//设置写入字体

WritableCellFormat wcfF = new

WritableCellFormat(wf);//设置CellFormat

Label labelCF=new Label(0, 0, "hello");//创建写入位置,内容和格式

Label的另一构造函数Label(int c, int r, String cont, CellFormat

st)可以对写入内容进行格式化,设置字体及其它的属性.

现在可以写了

wwb.write();

写完后关闭

wwb.close();

输出流也关闭吧

os.close;