Java操作excel

一，xls和xlsx的区别

• xls是excel2003及以前版本所生成的文件格式，xlsx是excel2007及以后版本所生成的文件格式
• excel2007可以打开上述两个格式的文件，而excel2003只能打开xls格式
• excel2007最大行和列比excel2003多

二，poi操作excel

	<dependency>
	    <groupId>org.apache.poi</groupId>
	    <artifactId>poi</artifactId>
	    <version>3.17</version>
	</dependency>

2.1 创建xls或xlsx

		// 1,创建一个excel文件
		HSSFWorkbook book = new HSSFWorkbook();

		// 2,创建表格
		HSSFSheet sheet1 = book.createSheet("Sheet1");

		// 3,创建一行数据
		HSSFRow row0 = sheet1.createRow(0);

		// 4,单元格格式，居中
		HSSFCellStyle cellStype = book.createCellStyle();
		cellStype.setAlignment(HorizontalAlignment.CENTER);

		// 5,创建单元格，并设置单元格的格式并写入数据
		HSSFCell cell = row0.createCell(0);
		cell.setCellStyle(cellStype);
		cell.setCellValue("学号");
		cell = row0.createCell(1);
		cell.setCellStyle(cellStype);
		cell.setCellValue("姓名");
		cell = row0.createCell(2);
		cell.setCellStyle(cellStype);
		cell.setCellValue("生日");
		cell = row0.createCell(3);
		cell.setCellStyle(cellStype);
		cell.setCellValue("分数");

		// 创建一行数据
		HSSFRow row1 = sheet1.createRow(1);

		// 单元格格式，居左
		HSSFCellStyle cellStype1 = book.createCellStyle();
		cellStype1.setAlignment(HorizontalAlignment.LEFT);

		// 创建单元格，并设置单元格的格式并写入数据
		HSSFCell cellData = row1.createCell(0);
		cellData.setCellStyle(cellStype1);
		cellData.setCellValue(1);
		cellData = row1.createCell(1);
		cellData.setCellStyle(cellStype1);
		cellData.setCellValue("李飘");
		//设置日期格式
		HSSFCellStyle hssfCellStyleDate = book.createCellStyle();
		HSSFDataFormat format = book.createDataFormat();
        hssfCellStyleDate.setDataFormat(format.getFormat("yyyy-MM-dd"));
		cellData = row1.createCell(2);
		cellData.setCellStyle(hssfCellStyleDate);
		cellData.setCellValue("1997-08-07");
		
		cellData = row1.createCell(3);
		cellData.setCellStyle(cellStype1);
		cellData.setCellValue(99.5);

		// 6，生成文件并关闭流
		book.write(new File("D:/hssfgenerate.xls"));// 这里根据需要，改成xls或者xlsx后缀
		book.close();

2.3 读取xls或xlsx

		HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream("D:/hssfgenerate.xls"));
		HSSFSheet sheet = wb.getSheetAt(0);
		HSSFRow row = sheet.getRow(1);
		
		double id = row.getCell(0).getNumericCellValue();
		String name = row.getCell(1).getStringCellValue();
		String birthday = row.getCell(2).getStringCellValue();
		double grade = row.getCell(3).getNumericCellValue();
		
		wb.close();

三，jxl操作excel

	<!-- https://mvnrepository.com/artifact/net.sourceforge.jexcelapi/jxl -->
	<dependency>
	    <groupId>net.sourceforge.jexcelapi</groupId>
	    <artifactId>jxl</artifactId>
	    <version>2.6.12</version>
	</dependency>

3.1，创建xls或xlsx

		//1，创建excel
		WritableWorkbook wb = Workbook.createWorkbook(new FileOutputStream("D:/hssfgenerate.xls"));
		//2,创建表格
		WritableSheet sheet = wb.createSheet("Sheet1", 0);
		
		//3，添加单元格
		//添加标题行单元格
		WritableCell cell01 = new Label(0, 0, "学号");
		WritableCell cell02 = new Label(1, 0, "姓名");
		WritableCell cell03 = new Label(2, 0, "生日");
		WritableCell cell04 = new Label(3, 0, "分数");
		sheet.addCell(cell01);
		sheet.addCell(cell02);
		sheet.addCell(cell03);
		sheet.addCell(cell04);
		
		//添加一行数据单元格
		WritableCell cell11 = new jxl.write.Number(0, 1, 1);
		WritableCell cell12 = new Label(1, 1, "李飘");
		WritableCell cell13 = new DateTime(2, 1, new SimpleDateFormat("yyyy-MM-dd").parse("1997-08-07"));
		WritableCell cell14 = new jxl.write.Number(3, 1, 99.5);
		sheet.addCell(cell11);
		sheet.addCell(cell12);
		sheet.addCell(cell13);
		sheet.addCell(cell14);
		
		//4,写入文件并关闭流
		wb.write();
		wb.close();

3.2，读取xls或xlsx

package exceloperate;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.ParseException;
import java.text.SimpleDateFormat;

import jxl.Cell;
import jxl.NumberCell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import jxl.write.DateFormat;
import jxl.write.DateTime;
import jxl.write.Label;
import jxl.write.WritableCell;
import jxl.write.WritableCellFormat;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

public class JXLExcel {

	public static void main(String[] args) throws RowsExceededException, FileNotFoundException, WriteException, IOException, ParseException, BiffException {
//		createExcel();
		readExcel();
	}

	private static void readExcel() throws BiffException, FileNotFoundException, IOException {
		Workbook wb = Workbook.getWorkbook(new FileInputStream("D:/hssfgenerate.xls"));
		Sheet sheet = wb.getSheet(0);
		Cell[] cells = sheet.getRow(1);
		
		double id = ((NumberCell)cells[0]).getValue();
		String name = cells[1].getContents();
		String birthday = cells[2].getContents();
		double grade = ((NumberCell)cells[3]).getValue();
		
		System.out.println("id:"+id);
		System.out.println("name:"+name);
		System.out.println("birthday:"+birthday);
		System.out.println("grade:"+grade);
	}

	private static void createExcel() throws FileNotFoundException, IOException, RowsExceededException, WriteException, ParseException {
		//1，创建excel
		WritableWorkbook wb = Workbook.createWorkbook(new FileOutputStream("D:/hssfgenerate.xls"));
		//2,创建表格
		WritableSheet sheet = wb.createSheet("Sheet1", 0);
		
		//3，添加单元格
		//添加标题行单元格
		WritableCell cell01 = new Label(0, 0, "学号");
		WritableCell cell02 = new Label(1, 0, "姓名");
		WritableCell cell03 = new Label(2, 0, "生日");
		WritableCell cell04 = new Label(3, 0, "分数");
		sheet.addCell(cell01);
		sheet.addCell(cell02);
		sheet.addCell(cell03);
		sheet.addCell(cell04);
		
		//添加一行数据单元格
		WritableCell cell11 = new jxl.write.Number(0, 1, 1);
		WritableCell cell12 = new Label(1, 1, "李飘");
		DateTime cell13 = new DateTime(2, 1, new SimpleDateFormat("yyyy-MM-dd").parse("1997-08-07"),new WritableCellFormat(new DateFormat("yyyy-MM-dd")));
		WritableCell cell14 = new jxl.write.Number(3, 1, 99.5);
		sheet.addCell(cell11);
		sheet.addCell(cell12);
		sheet.addCell(cell13);
		sheet.addCell(cell14);
		
		//4,写入文件并关闭流
		wb.write();
		wb.close();
	}
	
	
}