poi进行excle操作

一 excle导出：

所需要jar包

    <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.0.1</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(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();
    }
}
}

过高版本使用：

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();
        }
        
    }
 
}

3 定制版本导出：

<dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-lang3</artifactId>
            <version>3.3.1</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.jdom/jdom -->
        <dependency>
            <groupId>org.jdom</groupId>
            <artifactId>jdom</artifactId>
            <version>1.1.3</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.0.1</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/commons-io/commons-io -->
        <dependency>
            <groupId>commons-io</groupId>
            <artifactId>commons-io</artifactId>
            <version>2.6</version>
        </dependency>

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
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);
    }
    
}
}

 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(0);
            // 3.循环读取某一行
            for (Row row : sheetAt) {
                // 4.读取每一行的单元格
                String stringCellValue = row.getCell(0).getStringCellValue(); // 第一列数据
                String stringCellValue2 = row.getCell(1).getStringCellValue();// 第二列
                // 写多少个具体看大家上传的文件有多少列.....
                // 测试是否读取到数据,及数据的正确性
                System.out.println(stringCellValue);
            }
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
 
    }
}