JAVA实现Excel的导入导出

一.概述
1.excel读写技术

2.区别

二.jxl读写基础代码
1.从数据库将数据导出到excel表格

public class JxlExcel {
public static void main(String[] args) {
	//创建Excel文件
	String[] title= {"姓名","课程名","分数"};
	File file=new File("f:/sheet1.xls");
	try {
		file.createNewFile();
		//创建工作簿
		WritableWorkbook  workbook=Workbook.createWorkbook(file);
		//创建Sheet
		WritableSheet sheet=workbook.createSheet("表格一", 20);
		//第一行设置列名
		Label label=null;
		for (int i = 0; i < title.length; i++) {
			label=new Label(i, 0, title[i]);//第一个参数为列,第二个为行
			sheet.addCell(label);
		}
		Data data=new Data();
		ResultSet rs=data.getString();
		while(rs.next()) {
			System.out.println(rs.getString(1));
			label=new Label(0,rs.getRow(),rs.getString(1));
			sheet.addCell(label);
			label=new Label(1,rs.getRow(),rs.getString(2));
			sheet.addCell(label);
			label=new Label(2,rs.getRow(),rs.getString(3));
			sheet.addCell(label);
		}
		workbook.write();
		workbook.close();
	} catch (Exception e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
}
 
}

2,从Excel表格中读取数据

public class JxlRead {
public static void main(String[] args) {
	//创建workbook
	try {
		Workbook workbook=Workbook.getWorkbook(new File("f:/sheet1.xls"));
   //获取第一个表格
		Sheet sheet=workbook.getSheet(0);
	//获取数据
		for (int i = 0; i < sheet.getRows(); i++) {
			for (int j = 0; j < sheet.getColumns(); j++) {
				Cell cell=sheet.getCell(j, i);
				System.out.print(cell.getContents()+" ");
			}
			System.out.println();
		}
	}  catch (Exception e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	
}
}

三.Poi读写基础代码

//所需jar包:commons-io-2.2.jar;poi-3.11-20141221.jar
//通过poi进行excel导入数据
public class PoiExcel {
public static void main(String[] args) throws SQLException {
	String title[]= {"名字","课程","分数"};
	//1.创建Excel工作簿
	HSSFWorkbook workbook=new HSSFWorkbook();
	//2.创建一个工作表
	HSSFSheet sheet=workbook.createSheet("sheet2");
	//3.创建第一行
	HSSFRow row=sheet.createRow(0);
	HSSFCell cell=null;
	//4.插入第一行数据
	for (int i = 0; i < title.length; i++) {
		cell=row.createCell(i);
		cell.setCellValue(title[i]);
	}
	//5.追加数据
	Data data=new Data();
	ResultSet rs=data.getString();
	while(rs.next()) {
		HSSFRow row2=sheet.createRow(rs.getRow());
		HSSFCell cell2=row2.createCell(0);
		cell2.setCellValue(rs.getString(1));
		cell2=row2.createCell(1);
		cell2.setCellValue(rs.getString(2));
		cell2=row2.createCell(2);
		cell2.setCellValue(rs.getString(3));
	}
	//创建一个文件,将Excel内容存盘
	File file=new File("e:/sheet2.xls");
	try {
		file.createNewFile();
		FileOutputStream stream=FileUtils.openOutputStream(file);
		workbook.write(stream);
		stream.close();
	} catch (IOException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	
}
}

//将Excel表中内容读取
public class PoiRead {
public static void main(String[] args) {
	//需要解析的Excel文件
	File file=new  File("e:/sheet2.xls");
	try {
		//获取工作簿
		FileInputStream fs=FileUtils.openInputStream(file);
		HSSFWorkbook workbook=new HSSFWorkbook(fs);
	    //获取第一个工作表
		HSSFSheet hs=workbook.getSheetAt(0);
		//获取Sheet的第一个行号和最后一个行号
	   int last=hs.getLastRowNum();
	   int first=hs.getFirstRowNum();
	   //遍历获取单元格里的信息
	   for (int i = first; i <last; i++) {
		HSSFRow row=hs.getRow(i);
		int firstCellNum=row.getFirstCellNum();//获取所在行的第一个行号
		int lastCellNum=row.getLastCellNum();//获取所在行的最后一个行号
		for (int j = firstCellNum; j <lastCellNum; j++) {
			HSSFCell cell=row.getCell(j);
			String value=cell.getStringCellValue();
			System.out.print(value+" ");
		}
		System.out.println();
	}
	} catch (IOException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
}
}

如果Excel版本过高则需要改写用XSSF

public class PoiExpExcel2 {
 
	/**
	 * POI生成Excel文件
	 * @author David
	 * @param args
	 */
	public static void main(String[] args) {
 
		String[] title = {"id","name","sex"};
		
		//创建Excel工作簿
		XSSFWorkbook workbook = new XSSFWorkbook();
		//创建一个工作表sheet
		Sheet sheet = workbook.createSheet();
		//创建第一行
		Row row = sheet.createRow(0);
		Cell cell = null;
		//插入第一行数据 id,name,sex
		for (int i = 0; i < title.length; i++) {
			cell = row.createCell(i);
			cell.setCellValue(title[i]);
		}
		//追加数据
		for (int i = 1; i <= 10; i++) {
			Row nextrow = sheet.createRow(i);
			Cell cell2 = nextrow.createCell(0);
			cell2.setCellValue("a" + i);
			cell2 = nextrow.createCell(1);
			cell2.setCellValue("user" + i);
			cell2 = nextrow.createCell(2);
			cell2.setCellValue("男");
		}
		//创建一个文件
		File file = new File("e:/poi_test.xlsx");
		try {
			file.createNewFile();
			//将Excel内容存盘
			FileOutputStream stream = FileUtils.openOutputStream(file);
			workbook.write(stream);
			stream.close();
		} catch (IOException e) {
			e.printStackTrace();
		}
		
	}
 
}

四.定制导入模板
1.首先准备好模板的.xml文件,然后导入所需的jar包
例子:student.xml文件

<?xml version="1.0" encoding="UTF-8"?>
<excel id="student" code="student" name="学生信息导入">
    <colgroup>
        <col index="A" width='17em'></col>
        <col index="B" width='17em'></col>
        <col index="C" width='17em'></col>
        <col index="D" width='17em'></col>
        <col index="E" width='17em'></col>
        <col index="F" width='17em'></col>        
    </colgroup>
    <title>
        <tr height="16px">
            <td rowspan="1" colspan="6" value="学生信息导入" />
        </tr>
    </title>
    <thead>
        <tr height="16px">
        	<th value="编号" />
            <th value="姓名" />
            <th value="年龄" />
            <th value="性别" />
            <th value="出生日期" />
            <th value=" 爱好" />            
        </tr>
    </thead>
    <tbody>
        <tr height="16px" firstrow="2" firstcol="0" repeat="5">
            <td type="string" isnullable="false" maxlength="30" /><!--用户编号 -->
            <td type="string" isnullable="false" maxlength="50" /><!--姓名 -->
            <td type="numeric" format="##0" isnullable="false" /><!--年龄 -->
            <td type="enum" format="男,女" isnullable="true" /><!--性别 -->
            <td type="date" isnullable="false" maxlength="30" /><!--出生日期 -->
            <td type="enum" format="足球,篮球,乒乓球" isnullable="true" /><!--爱好 -->
        </tr>
    </tbody>
</excel>

所需jar包:
commons-lang3-3.1.jar
jdom.jar
poi-3.11-20141221.jar
commons-io-2.2.jar
java代码:

//准备工作:导入相关jar包commons-lang3-3.1.jar,jdom.jar,poi-3.11-20141221.jar
public class CreateTemp {
public static void main(String[] args) {
	//获取解析Xml路径
	String path=System.getProperty("user.dir")+"/student.xml";
	File file=new File(path);
	SAXBuilder builder=new SAXBuilder();
	//解析xml文件
	try {
		Document document=builder.build(file);
	//创建Excel
		HSSFWorkbook workbook=new HSSFWorkbook();
	//创建表格
		HSSFSheet sheet=workbook.createSheet("sheet0");
	//获取Xml文件的根节点
		Element root=document.getRootElement();
	//获取模板名称
		String tempName=root.getAttributeValue("name");
	//设置列宽
		Element colgroup=root.getChild("colgroup");
		setColumnWidth(sheet,colgroup);
	//设置标题
		int rownum = 0;
		int column = 0;
		Element title=root.getChild("title");
		List<Element> trs=title.getChildren("tr");
		for (int i = 0; i <trs.size(); i++) {
			Element tr=trs.get(i);
			List<Element> tds=tr.getChildren("td");
			HSSFRow row=sheet.createRow(rownum);
			HSSFCellStyle cellStyle=workbook.createCellStyle();//创建单元格格式
			cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//标题居中
			for (int j = 0; j < tds.size(); j++) {
			   Element td=tds.get(j);
			   HSSFCell cell=row.createCell(j);
			   Attribute rowspan=td.getAttribute("rowspan");
			   Attribute colspan=td.getAttribute("colspan");
			   Attribute value=td.getAttribute("value");
			   if (value!=null) {
				String content=value.getValue();
			
				cell.setCellValue(content);
				int rspan=rowspan.getIntValue()-1;
				int cspan=colspan.getIntValue()-1;
				//设置字体
				HSSFFont font=workbook.createFont();
				font.setFontName("仿宋_GB2312");
				font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//字体加粗
//				font.setFontHeight((short)12);
				font.setFontHeightInPoints((short)12);
				cellStyle.setFont(font);
				cell.setCellStyle(cellStyle);
				//合并单元格居中
				sheet.addMergedRegion(new CellRangeAddress(rspan, rspan, 0, cspan));
			}
			   
			}
			rownum++;
			
		}
		//设置表头
		Element thead=root.getChild("thead");
		trs=thead.getChildren("tr");
		for (int i = 0; i < trs.size(); i++) {
			Element tr=trs.get(i);
			HSSFRow row=sheet.createRow(rownum);
			 List<Element> ths=tr.getChildren("th");
			 for (int j = 0; j <ths.size(); j++) {
				Element th=ths.get(j);
				HSSFCell cell=row.createCell(j);
			     Attribute value=th.getAttribute("value");
			     if (value!=null) {
			    	 String content=value.getValue();
                    cell.setCellValue(content);	
                   
				}
			 }
			 rownum++;
		}
		
		//设置数据区域样式
		     Element  tbody = root.getChild("tbody");
		     Element tr=tbody.getChild("tr");
		     int repeat=tr.getAttribute("repeat").getIntValue();
		     List<Element> tds=tr.getChildren("td");
		     for (int i = 0; i < repeat; i++) {
				HSSFRow row=sheet.createRow(rownum);
				for (int j = 0; j < tds.size(); j++) {
					Element td=tds.get(j);
					HSSFCell cell=row.createCell(j);
					setType(workbook,cell,td);
				}
			}
		     rownum++;
		//生成Excel导入模板
		     File tempFile=new File("e:/"+tempName+".xls");
		     tempFile.delete();
		     tempFile.createNewFile();
		     FileOutputStream fos=FileUtils.openOutputStream(tempFile);
		     workbook.write(fos);
		     fos.close();
		     
	}  catch (Exception e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
}
 
private static void setType(HSSFWorkbook workbook, HSSFCell cell, Element td) {
	Attribute typeAttr = td.getAttribute("type");
	String type = typeAttr.getValue();
	HSSFDataFormat format = workbook.createDataFormat();
	HSSFCellStyle cellStyle = workbook.createCellStyle();
	if("NUMERIC".equalsIgnoreCase(type)){
		cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
		Attribute formatAttr = td.getAttribute("format");
		String formatValue = formatAttr.getValue();
		formatValue = StringUtils.isNotBlank(formatValue)? formatValue : "#,##0.00";
		cellStyle.setDataFormat(format.getFormat(formatValue));
	}else if("STRING".equalsIgnoreCase(type)){
		cell.setCellValue("");
		cell.setCellType(HSSFCell.CELL_TYPE_STRING);
		cellStyle.setDataFormat(format.getFormat("@"));
	}else if("DATE".equalsIgnoreCase(type)){
		cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
		cellStyle.setDataFormat(format.getFormat("yyyy-m-d"));
	}else if("ENUM".equalsIgnoreCase(type)){
		CellRangeAddressList regions = 
			new CellRangeAddressList(cell.getRowIndex(), cell.getRowIndex(), 
					cell.getColumnIndex(), cell.getColumnIndex());
		
		Attribute enumAttr = td.getAttribute("format");
		String enumValue = enumAttr.getValue();
		//加载下拉列表内容
		DVConstraint constraint = 
			DVConstraint.createExplicitListConstraint(enumValue.split(","));
		//数据有效性对象
		HSSFDataValidation dataValidation = new HSSFDataValidation(regions, constraint);
		workbook.getSheetAt(0).addValidationData(dataValidation);
	}
	cell.setCellStyle(cellStyle);
	
}
 
private static void setColumnWidth(HSSFSheet sheet, Element colgroup) {
	List<Element> cols=colgroup.getChildren("col");//获取col的节点
	for (int i = 0; i < cols.size(); i++) {
		Element col=cols.get(i);
		Attribute width=col.getAttribute("width");//获取每列中的width属性
		String unit = width.getValue().replaceAll("[0-9,\\.]", "");//单位
		String value = width.getValue().replaceAll(unit, "");//数值
		int v=0;
		if(StringUtils.isBlank(unit) || "px".endsWith(unit)){
			v = Math.round(Float.parseFloat(value) * 37F);
		}else if ("em".endsWith(unit)){
			v = Math.round(Float.parseFloat(value) * 267.5F);
		}//对单位进行判断
		sheet.setColumnWidth(i, v);
	}
	
}
}