一 excle导出：

所需要jar包

<dependency>  
        <groupId>org.apache.poi</groupId>  
        <artifactId>poi</artifactId>  
        <version>4.0.</version>  
    </dependency>  
    <!-- https://mvnrepository.com/artifact/commons-io/commons-io -->  
    <dependency>  
        <groupId>commons-io</groupId>  
        <artifactId>commons-io</artifactId>  
        <version>2.6</version>  
    </dependency>

代码：

package com.example.demo.controller;

import javafx.scene.chart.XYChart;
import org.apache.commons.io.FileUtils;
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;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;

import java.sql.ResultSet;
import java.sql.SQLException;

/**
* Created by Administrator on 2019/2/19.
*/
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();
HSSFCell cell=null;
//4.插入第一行数据
for (int i = ; 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();
cell2.setCellValue(rs.getString());
cell2=row2.createCell();
cell2.setCellValue(rs.getString());
cell2=row2.createCell();
cell2.setCellValue(rs.getString());
}
//创建一个文件,将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();
//获取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();
}
}
}

过高版本使用：

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();  
    Cell cell = null;  
    //插入第一行数据 id,name,sex  
    for (int i = ; i < title.length; i++) {  
        cell = row.createCell(i);  
        cell.setCellValue(title\[i\]);  
    }  
    //追加数据  
    for (int i = ; i <= ; i++) {  
        Row nextrow = sheet.createRow(i);  
        Cell cell2 = nextrow.createCell();  
        cell2.setCellValue("a" + i);  
        cell2 = nextrow.createCell();  
        cell2.setCellValue("user" + i);  
        cell2 = nextrow.createCell();  
        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();  
    }

}

}

3 定制版本导出：

org.apache.commons commons-lang3 3.3.

org.jdom jdom 1.1.

org.apache.poi poi 4.0.

commons-io commons-io 2.6

xml文件：

¨C17C ¨C18C ¨C19C ¨C20C ¨C21C ¨C22C

导出代码：

//准备工作:导入相关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 = ;
int column = ;
Element title=root.getChild("title");
List trs=title.getChildren("tr");
for (int i = ; i tds=tr.getChildren("td");
HSSFRow row=sheet.createRow(rownum);
HSSFCellStyle cellStyle=workbook.createCellStyle();//创建单元格格式
cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//标题居中
for (int j = ; 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()-;  
            int cspan=colspan.getIntValue()-;  
            //设置字体  
            HSSFFont font=workbook.createFont();  
            font.setFontName("仿宋\_GB2312");  
            font.setBoldweight(HSSFFont.BOLDWEIGHT\_BOLD);//字体加粗  

// font.setFontHeight((short)12);
font.setFontHeightInPoints((short));
cellStyle.setFont(font);
cell.setCellStyle(cellStyle);
//合并单元格居中
sheet.addMergedRegion(new CellRangeAddress(rspan, rspan, , cspan));
}

        }  
        rownum++;

    }  
    //设置表头  
    Element thead=root.getChild("thead");  
    trs=thead.getChildren("tr");  
    for (int i = ; i < trs.size(); i++) {  
        Element tr=trs.get(i);  
        HSSFRow row=sheet.createRow(rownum);  
         List<Element> ths=tr.getChildren("th");  
         for (int j = ; 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 = ; i < repeat; i++) {  
            HSSFRow row=sheet.createRow(rownum);  
            for (int j = ; 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().addValidationData(dataValidation);  
}  
cell.setCellStyle(cellStyle);

}

private static void setColumnWidth(HSSFSheet sheet, Element colgroup) {
List cols=colgroup.getChildren("col");//获取col的节点
for (int i = ; 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=;
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);
}

}
}

4 springmvc后台解析代码：

// excel导入  
@RequestMapping("/export")  
public void export(VivoIMEIUp zipRequest,  
        @RequestParam("file") MultipartFile file,  
        HttpServletRequest request, HttpServletResponse response) {  
    try {  
        // @RequestParam("file") MultipartFile file 是用来接收前端传递过来的文件  
        // 1.创建workbook对象，读取整个文档  
        InputStream inputStream = file.getInputStream();  
        POIFSFileSystem poifsFileSystem = new POIFSFileSystem(inputStream);  
        HSSFWorkbook wb = new HSSFWorkbook(poifsFileSystem);  
        // 2.读取页脚sheet  
        HSSFSheet sheetAt = wb.getSheetAt();  
        // 3.循环读取某一行  
        for (Row row : sheetAt) {  
            // 4.读取每一行的单元格  
            String stringCellValue = row.getCell().getStringCellValue(); // 第一列数据  
            String stringCellValue2 = row.getCell().getStringCellValue();// 第二列  
            // 写多少个具体看大家上传的文件有多少列.....  
            // 测试是否读取到数据,及数据的正确性  
            System.out.println(stringCellValue);  
        }  
    } catch (Exception e) {  
        // TODO Auto-generated catch block  
        e.printStackTrace();  
    }

}  

}