java读取excel

java读取excel

1、引用依赖

     <!--java读取excel-->
     <dependency>
         <groupId>net.sourceforge.jexcelapi</groupId>
         <artifactId>jxl</artifactId>
         <version>2.6.12</version>
     </dependency>

2、完整代码

import java.io.*;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

public class Test {
	public static void main(String[] args) {
		analysisExcel();
	}

	public static void analysisExcel() {

		Workbook workbook = null;
		//创建File 读取文件
		File Inputfile = new File("D:\\projectMZT\\files\\养老服务站名录.xls");
		File outputfile =new File("D:\\projectMZT\\files\\养老服务站名录.txt");
		try {
			//使用字符流接File的数据
			FileInputStream fileInputStream = new FileInputStream(Inputfile);
			//workbook去接fileInputStream,读取到excel文件
			workbook = Workbook.getWorkbook(fileInputStream);
			FileOutputStream fileOutputStream = new FileOutputStream(outputfile);
			BufferedOutputStream bw = new BufferedOutputStream(fileOutputStream); //输出语句
			//判断是哪一个工作簿，用Sheet类
			Sheet readfirst = workbook.getSheet(0);
			//获取有效行数
			int rows = readfirst.getRows();
			//获取有效列数
			int clomns = readfirst.getColumns();
			System.out.println("row:" + rows);
			System.out.println("clomns:" + clomns);
			for(int i =1;i<rows;i++) {
				//循环得到每一行的单元格对象
				Cell[] cells = readfirst.getRow(i);
				//根据每一个单元格对象获取里面的值
				String city= cells[0].getContents();
				String county = cells[1].getContents();
				String companyName =cells[2].getContents();
				String address =cells[3].getContents();
				String contact =cells[4].getContents();
				String phone =cells[5].getContents();
				String area =cells[6].getContents();
				String buildTime =cells[7].getContents();
				String isOperate =cells[8].getContents();
				String operateMode =cells[9].getContents();
				String operateManagers =cells[10].getContents();
				String type =cells[11].getContents();
				String remark =cells[12].getContents();
				System.out.println("行："+ (i+1) +"，city:"+city+",county:"+county+",companyName:"+companyName+"address:"+address+"contact:"+contact+"phone:"+phone
						+"area:"+area+"buildTime:"+buildTime+"isOperate:"+isOperate+"operateMode:"+operateMode+"operateManagers:"+operateManagers+"type:"+type+"remark:"+remark);
				//将得到的值放在一个我需要的格式的string对象中
				String output = "\n"+city+"\n" +
						"{\n" +
						" \"county\": \""+county+"\",\n" +
						" \"companyName\": \""+companyName+"\",\n" +
						" \"city\": \""+city+"\"\n" +
						"}"+
						"\n";
				//write and flush到需要的文件中去，flush后成功
				byte[] outputbyte = new String(output).getBytes();
				bw.write(outputbyte);
				bw.flush();
			}
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} catch (BiffException e) {
			e.printStackTrace();
		}
	}
}

3、可能出现的错误（读取excel时报的错误）

jxl.read.biff.BiffException: Unable to recognize OLE stream
	at jxl.read.biff.CompoundFile.<init>(CompoundFile.java:116)
	at jxl.read.biff.File.<init>(File.java:127)
	at jxl.Workbook.getWorkbook(Workbook.java:268)
	at jxl.Workbook.getWorkbook(Workbook.java:253)
	at com.sk.framework.Test.analysisExcel(Test.java:30)
	at com.sk.framework.Test.main(Test.java:16)

解决办法：

jxl包只支持excel——2003版，文件可能是其他版本，将excel格式另存为2003版即可。