java代码操作Excel表格

本文采用 poi 利用java程序实现excel表格的上传下载操作。

例：

实体类

package test;

/**
* Created by LiuD on 2016/11/18.
*/
public class CompStaffCriteria {

private String staffName;
private String certNum;
private Integer anmeldungProvinceName;
private Integer anmeldungCityName;
private String mobile;
private String anmeldungProperty;
private String bankAcctNo;
private String bankAcctName;
private Long payBasic;
private String firstFlag;
private String email;
public String getStaffName() {
return staffName;
}
public void setStaffName(String staffName) {
this.staffName = staffName;
}
public String getCertNum() {
return certNum;
}
public void setCertNum(String certNum) {
this.certNum = certNum;
}
public Integer getAnmeldungProvinceName() {
return anmeldungProvinceName;
}
public void setAnmeldungProvinceName(Integer anmeldungProvinceName) {
this.anmeldungProvinceName = anmeldungProvinceName;
}
public Integer getAnmeldungCityName() {
return anmeldungCityName;
}
public void setAnmeldungCityName(Integer anmeldungCityName) {
this.anmeldungCityName = anmeldungCityName;
}
public String getMobile() {
return mobile;
}
public void setMobile(String mobile) {
this.mobile = mobile;
}
public String getAnmeldungProperty() {
return anmeldungProperty;
}
public void setAnmeldungProperty(String anmeldungProperty) {
this.anmeldungProperty = anmeldungProperty;
}
public String getBankAcctNo() {
return bankAcctNo;
}
public void setBankAcctNo(String bankAcctNo) {
this.bankAcctNo = bankAcctNo;
}
public String getBankAcctName() {
return bankAcctName;
}
public void setBankAcctName(String bankAcctName) {
this.bankAcctName = bankAcctName;
}
public Long getPayBasic() {
return payBasic;
}
public void setPayBasic(Long payBasic) {
this.payBasic = payBasic;
}
public String getFirstFlag() {
return firstFlag;
}
public void setFirstFlag(String firstFlag) {
this.firstFlag = firstFlag;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {

this.email = email;

}

}

下载 Util

package test;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.usermodel.DataValidationHelper;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;

import java.util.ArrayList;
import java.util.List;

/**
 * Created by LiuD on 2016/12/13.
 */
public class TestExcelDown {

    public static HSSFWorkbook getExcelTemplate(String fileName) {
        //列标题 1
        String[] handerFirst = {"必填项", "必填项", "选择项(请查看下拉框)",  "必填项", "必填项", "必填项", "数字类型", "选择项(请查看下拉框)", "必填项", ""};
        //列标题 2
        String[] handerSecond = {"姓名", "身份证号", "户籍性质",  "户口所在地","银行卡号", "银行账户名", "社保基数", "是否首次参保", "手机号码", "邮箱地址"};
        //下拉框数据
        List<String[]> downData = new ArrayList<>();
        String[] str2 = {"农业", "非农业"};
        String[] str4 = {"是", "否"};
        downData.add(str2);
        downData.add(str4);
        String[] downRows = {"2", "7"};
        if (null != fileName) {
            switch (fileName) {
                case "北京" : return getExcelDownlod(fileName, handerFirst, handerSecond, downData, downRows);
                case "上海" : return getExcelDownlod(fileName, handerFirst, handerSecond, downData, downRows);
                default : break;
            }
        }

        return null;
    }

    public static HSSFWorkbook getExcelDownlod(String fileName, String[] handerFirst, String[] handerSecond, List<String[]> downData, String[] downRows) {
        // 新建一个workbook
        HSSFWorkbook workBook = new HSSFWorkbook();
        //新建sheet
        HSSFSheet sheet = workBook.createSheet();

        //表头字体
        HSSFCellStyle style_head = workBook.createCellStyle();
        HSSFFont hssfFont = workBook.createFont();
// 设置字体名称
        hssfFont.setFontName("华文行楷");
// 设置字号
        hssfFont.setFontHeightInPoints((short) 24);
        style_head.setFont(hssfFont);
// 水平居中
        style_head.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// 垂直居中
        style_head.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

        //设置表头信息
        HSSFRow row = sheet.createRow(0);
        HSSFCell cell = row.createCell(0);
        cell.setCellValue("人员增报模板-"+fileName);
        cell.setCellStyle(style_head);
        CellRangeAddress region = new CellRangeAddress(0, 0, 0, 9);
        sheet.addMergedRegion(region);

        //标题 1 字体
        HSSFCellStyle styleFirst= workBook.createCellStyle();
        HSSFFont fontFirst = workBook.createFont();
        fontFirst.setFontName("微软雅黑");
        fontFirst.setFontHeightInPoints((short) 10);
        fontFirst.setColor(HSSFColor.RED.index);
        styleFirst.setFont(fontFirst);

        //标题 1 信息
        HSSFRow rowFirst =sheet.createRow(1);
        for (int x = 0; x < handerFirst.length; x++) {
            HSSFCell cellFirst = rowFirst.createCell(x);
            sheet.setColumnWidth(x,3000);
            cellFirst.setCellStyle(styleFirst);
            cellFirst.setCellValue(handerFirst[x]);
        }

        //标题 2 字体
        HSSFCellStyle styleSecond = workBook.createCellStyle();
        HSSFFont fontSecond = workBook.createFont();
        fontSecond.setFontName("华文行楷");
        fontSecond.setFontHeightInPoints((short) 12);
        styleSecond.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        fontSecond.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        styleSecond.setFont(fontSecond);

        //标题 2 信息
        HSSFRow rowSecond  = sheet.createRow(2);
        for (int i = 0; i < handerSecond.length; i++) {
            HSSFCell hssfCell = rowSecond.createCell(i);
            sheet.setColumnWidth(i,5000);
            hssfCell.setCellStyle(styleSecond);
            hssfCell.setCellValue(handerSecond[i]);
        }

        //设置下拉框
        for (int r = 0; r < downData.size(); r++) {
            //取出下拉框的值
            String[] dData = downData.get(r);
            int rownum =Integer.parseInt(downRows[r]);
            //下拉框生效位置
            sheet.addValidationData(setDataValidation(sheet, dData, 3, 50000, rownum ,rownum));
         }

        return workBook;
    }


    private static DataValidation setDataValidation(HSSFSheet sheet, String[] dData, int firstRow, int endRow, int firstNum, int endNum) {
        DataValidationHelper helper = sheet.getDataValidationHelper();
        //加载下拉框内容
        DataValidationConstraint constraint= helper.createExplicitListConstraint(dData);
        CellRangeAddressList rangeAddressList = new CellRangeAddressList(firstRow, endRow, firstNum, endNum);
        DataValidation dataValidation = helper.createValidation(constraint, rangeAddressList);

        return dataValidation;
    }
}

测试类

package test;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import java.io.FileOutputStream;

/**
 * Created by LiuD on 2016/12/16.
 */
public class TestDownDemo {

    //下载
    public static void main(String[] args) {

        String fileName = "北京";

        try {
            HSSFWorkbook workbook = ExcelUtil.getExcelTemplate(fileName);
            FileOutputStream file = new FileOutputStream("F:\\10086.xls");
            workbook.write(file);
            file.flush();
            file.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

下载Util

package test;

import java.io.FileInputStream;
import java.io.IOException;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * Created by LiuD on 2016/12/16.
 */
public class TestExcelReader {

    //读入excle表格 并处理 返回需要的criteria
    public static List<CompStaffCriteria> getCompStaff (String filePath) throws Exception{
        List<CompStaffCriteria> criteriaList = new ArrayList<>();
        String[] s;
        Workbook workBook = getWorkBook(filePath);
        //得到工作表
        Sheet sheet = workBook.getSheetAt(0);
        CompStaffCriteria criteria;
        //获得总行数
        int totalRowNum = sheet.getLastRowNum();
        for (int i = 3; i <= totalRowNum; i++) {
            //获取整一行对象
            Row row = sheet.getRow(i);
            //获取整一行有多少列
            int coloumNum=sheet.getRow(i).getPhysicalNumberOfCells();
            s = getExcelRow(row, coloumNum);
            criteria = validate(s, i + 1);
            criteriaList.add(criteria);
        }

        return criteriaList;
    }


    //判断文件类型
    public static Workbook getWorkBook(String filePath) throws Exception{
        //判断是否为excel的类型
        if (!filePath.endsWith(".xls") && !filePath.endsWith(".xlsx")) {
            throw new Exception("上传格式错误");
        }

        FileInputStream fis = null;
        org.apache.poi.ss.usermodel.Workbook workBook = null;
        try {
            fis = new FileInputStream(filePath);
            //2003版本的excel，用.xls结尾
            workBook = new HSSFWorkbook(fis);
        } catch (Exception ex) {

            try {
                //2007版本的excel，用.xlsx结尾
                workBook = new XSSFWorkbook(fis);
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

        return workBook;
    }


    //将取到的每行数据转换成数组存储
    public static String[] getExcelRow(Row row, int coloumNum) {
        String[] s;
        StringBuilder builder = new StringBuilder();
        for (int x = 0; x < coloumNum; x++) {
            //获取每一行对应
            HSSFCell cell = (HSSFCell) row.getCell(x);
            builder.append(getStringCellValue(cell)+",");
        }
        s = builder.toString().split(",");

        return s;
    }


    //获取单元格内有效数据
    private static String getStringCellValue(HSSFCell cell) {
        StringBuilder sb = new StringBuilder();
        //转换数字格式取值跟 E10 问题
        DecimalFormat format = new DecimalFormat("#");
        switch (cell.getCellType()) {
            //数字
            case HSSFCell.CELL_TYPE_NUMERIC:
                sb.append(format.format(cell.getNumericCellValue()));
                break;
            //字符串
            case HSSFCell.CELL_TYPE_STRING:
                sb.append(cell.getStringCellValue());
                break;
            //布尔
            case HSSFCell.CELL_TYPE_BOOLEAN:
                sb.append(cell.getBooleanCellValue());
                break;
            //公式
            case HSSFCell.CELL_TYPE_FORMULA:
                sb.append(cell.getCellFormula());
                break;
            //空值
            case HSSFCell.CELL_TYPE_BLANK:
                sb.append("");
                break;
            //故障
            case HSSFCell.CELL_TYPE_ERROR:
                sb.append("");
                break;
            default:
                sb.append("");
                break;
        }
        if (sb.equals("") || sb == null) {
            return sb.append("").toString();
        }

        return sb.toString();
    }


    //验证输入参数正确性
    public static CompStaffCriteria validate(String[] s, int i) throws Exception{

        CompStaffCriteria criteria = new CompStaffCriteria();
            //用户名
            if (StringUtils.isEmpty(s[0].trim())) {
                throw new Exception("第" + i + "行记录解析失败，用户名不能为空");
            }
            //身份证
            if (StringUtils.isEmpty(s[1].trim())) {
                throw new Exception("第" + i + "行记录解析失败，身份证号未填写");
            }
            //户籍
            if (StringUtils.isEmpty(s[2].trim())) {
                throw new Exception("第" + i + "行记录解析失败，户籍状态未填写");
            }
            if (!s[2].equals("非农业") && !s[2].equals("农业")) {
                throw new Exception("第" + i + "行记录解析失败，户籍性质选择错误");
            }
            //户口所在地
            if (StringUtils.isEmpty(s[3].trim())) {
                throw new Exception("第" + i + "行记录解析失败，户口所在地未填写");
            }
            if (s[3].indexOf("市") == -1) {
                throw new Exception("第" + i + "行记录解析失败，户口所在地填写错误");
            }
            //银行卡号
            if (StringUtils.isEmpty(s[4].trim())) {
                throw new Exception("第" + i + "行记录解析失败，银行卡号未填写");
            }
            //银行账户名
            if (StringUtils.isEmpty(s[5].trim())) {
                throw new Exception("第" + i + "行记录解析失败，银行账户名未填写");
            }
            //社保基数
            if (StringUtils.isEmpty(s[6].trim())) {
                throw new Exception("第" + i + "行记录解析失败，社保基数未填写");
            }
            //是否首次参保
            if (StringUtils.isEmpty(s[7].trim())) {
                throw new Exception("第" + i + "行记录解析失败，是否首次参保未填写");
            }
            if (!s[7].trim().equals("是") && !s[7].trim().equals("否")) {
                throw new Exception("第" + i + "行记录解析失败，是否首次参保状态选择错误");
            }
            //手机号检查
            String regMo = "^((13[0-9])|(15[^4,\\D])|(18[0,5-9])|(177))\\d{8}$";
            if (StringUtils.isEmpty(s[8].trim())) {
                throw new Exception("第" + i + "行记录解析失败，手机号未填写");
            }
            if (!s[8].trim().matches(regMo)) {
                throw new Exception("第" + i + "行记录解析失败，手机号格式不正确");
            }
            //邮箱地址
            if (s.length>9) {
                String regEx = "^([a-zA-Z0-9]*[-_]?[a-zA-Z0-9]+)*@([a-zA-Z0-9]*[-_]?[a-zA-Z0-9]+)+[\\.][A-Za-z]{2,3}([\\.][A-Za-z]{2})?$";
                if (!s[9].trim().matches(regEx)) {
                    throw new Exception("第" + i + "行记录解析失败，邮箱格式不正确");
                }
                criteria.setEmail(s[9]);
            }
            criteria.setStaffName(s[0]);
            
            criteria.setCertNum(s[1]);
            if (s[2].trim().equals("非农业")){
                criteria.setAnmeldungProperty("TOWN");
            }
            if (s[2].trim().equals("农业")){
                criteria.setAnmeldungProperty("COUNTRY");
            }
  
            int numProvince = s[3].indexOf("省");
            if (numProvince > -1) {
                String provinceName = s[3].substring(0, numProvince);
                criteria.setAnmeldungProvinceName(provinceName);
                int numCity = s[3].indexOf("市");
                String cityName = s[3].substring(numProvince +1, numCity);
                criteria.setAnmeldungCityName(cityName);
            } else {
                int numCity = s[3].indexOf("市");
                String cityName = s[3].substring(0, numCity);
                criteria.setAnmeldungProvinceName(cityName);
                criteria.setAnmeldungCityName(cityName);
            }

            criteria.setBankAcctNo(s[4]);
            criteria.setBankAcctName(s[5]);
            criteria.setPayBasic(Long.parseLong(s[6]));
            if (s[7].trim().equals("是")) {
                   criteria.setFirstFlag("Y");
            }
            if (s[7].trim().equals("否")) {
                criteria.setFirstFlag("N");
            }
            criteria.setMobile(s[8]);

        return criteria;
    }
      

      //非空判断

public static boolean isEmpty(String src) {

        if (src == null || "".equals(src)) {

            return true;

        }

        return false;

    }

} 

测试类

package test;

import test.CompStaffCriteria;

/**
 * Created by LiuD on 2016/11/18.
 */
public class TestReaderDemo {

    // 下载  
    public static void main(String[] args) throws Exception {

        List<CompStaffCriteria> u=TestExcelReader.getCompStaff("F:\\10086.xls");

    }
}

以上就是java代码通过poi实现excel文件的上传下载；

数据导入并下载原理基本和以上代码一致；大概思路为 

1、拿到数据库的所有数据并封装成一个list集合

2、将list集合传入下载Util

3、遍历集合，并在表头数量基础上每次蹭加一行row 例： HSSFRow row = sheet.createRow(i+3); //表头有3行内容

4、拿到实体中的每个元素值，并添加到Cell中   例： cell = row.createCell(0);  cell.setCellValue(user.getUserId());

结尾 ：由于工作原因，Lz打算将以前的知识梳理一遍，会不定期更新文档，欢迎大家交流学习。经验方面感觉还比较薄弱，代码内容可能会比较啰嗦，粗浅。如有不妥之处，恳请大家批评指正。谢谢

2016年12月19日凌晨