poi实现excel的导入和导出

import com.pro.telecom.bean.back.ChuZhangBack;
import com.pro.telecom.bean.back.ChuZhangBack2;
import com.pro.telecom.bean.entity.*;
import com.pro.telecom.dao.*;
import com.pro.telecom.util.CodeUtil;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Cell;
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.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * 文件的导入和导出
 */
@Service
public class ChuZhang {

    @Resource
    AccountFeeRecordMapper accountFeeRecordMapper;
    @Resource
    AccountGatherMapper accountGatherMapper;
    @Resource
    CityCodeMapper cityCodeMapper;
    @Resource
    ProductCodeMapper productCodeMapper;
    @Resource
    AccountTypeCodeMapper accountTypeCodeMapper;


//    导入excel
    public Integer importExcel(MultipartFile myfile) throws Exception{
        //获得文件名
        Workbook workbook = null;
        String filename = myfile.getOriginalFilename();
        System.out.println("fileName:"+filename);
        if(filename.endsWith(".xls")){
            workbook = new HSSFWorkbook(myfile.getInputStream());
        }else if (filename.endsWith(".xlsx")) {
            workbook = new XSSFWorkbook(myfile.getInputStream());
        }else {
            throw new Exception("该文件不是excel文件");
        }

        Sheet sheet = workbook.getSheet("sheet1");
        int rows = sheet.getLastRowNum();   //一共多少行
        if (rows == 0) {
            throw new Exception("无数据");
        }

        for (int i=1;i<=rows;i++) {
            //读取左上单元格
            Row row = sheet.getRow(i);
            //行不为空
            if (row!=null) {
                //读取cell
                AccountFeeRecord record = new AccountFeeRecord();
                //录入月份
                SimpleDateFormat sdf = new SimpleDateFormat("yyyyMM");
                record.setAccountRecordMonth(sdf.parse(getCellValue(row.getCell(1))));
                //城市编码
                record.setCityCode(getCityCode(getCellValue(row.getCell(2))));
                //产品类型
                record.setProductCode(getProductCode(getCellValue(row.getCell(3))));
                //出账类型
                record.setAccountFeeTypeCode(getAccountCode(getCellValue(row.getCell(4))));
                //录入金额
                double v = row.getCell(5).getNumericCellValue();
                System.out.println("金额："+v);
                record.setAccountFee(BigDecimal.valueOf(v));
                record.setAccountOperator(UserInfo.getUserContext().getUsername());
                record.setCheckStatus("未稽核");
                record.setId("AFR"+ CodeUtil.random10Digit());
                System.out.println(record);
                accountFeeRecordMapper.insertSelective(record);
            }else{
                System.out.println("这一行为空");
            }
        }
        System.out.println("rows:"+rows);
        return rows-1;
    }

    //获得cell内容
    public String getCellValue(Cell cell) {
        String value = "";
        if(cell != null) {
            switch (cell.getCellType()) {
                case HSSFCell.CELL_TYPE_NUMERIC:
                    value = cell.getNumericCellValue()+"";
                    if(HSSFDateUtil.isCellDateFormatted(cell)) {
                        Date date = cell.getDateCellValue();
                        if(date != null) {
                            value = new SimpleDateFormat("yyyy-MM-dd").format(date);
                        }else {
                            value = "";
                        }
                    }else {
                        value = new DecimalFormat("0").format(cell.getNumericCellValue());
                    }
                    break;
                case HSSFCell.CELL_TYPE_STRING:
                    value = cell.getStringCellValue();
                    break;
                case HSSFCell.CELL_TYPE_BOOLEAN:
                    value = cell.getStringCellValue()+"";
                    break;
                case HSSFCell.CELL_TYPE_FORMULA:
                    value = cell.getStringCellValue()+"";
                    break;
                case HSSFCell.CELL_TYPE_BLANK:
                    value = "";
                    break;
                case HSSFCell.CELL_TYPE_ERROR:
                    value = "非法字符";
                    break;
                default:
                    value = "未知类型";
                    break;
            }
        }
        return value.trim();
    }

    //导出excel
    public void createExcel(HttpServletResponse response) throws Exception {

        String excelName = "出账";
        //headList：excel中的单元格标题
        String headList[] = {"录入月份","城市编码","产品编码","出账类型编码","录入金额（元）"};
        //fieldList：单元格对应的属性
        String fieldList[] = {"accountRecordMonth","cityName","productName","accountName","accountFee"};
        //dataList：所有行单元格对应的所有的数据
        List<Map<String,Object>> dataList = new ArrayList<>();
        List<ChuZhangBack> list = accountFeeRecordMapper.queryAll(null);
        for (ChuZhangBack p : list) {
            Map<String,Object> map = new HashMap<>();
            SimpleDateFormat sdf = new SimpleDateFormat("yyyyMM");
            map.put("accountRecordMonth",sdf.format(p.getAccountRecordMonth()));
            map.put("cityName",p.getCityName());
            map.put("productName",p.getProductName());
            map.put("accountName",p.getAccountName());
            map.put("accountFee",p.getAccountFee());
            dataList.add(map);
        }
//        开始存

        try {
            // 创建新的Excel 工作簿
            XSSFWorkbook workbook = new XSSFWorkbook();
            // 告诉浏览器用什么软件可以打开此文件
            response.setHeader("Content-Type", "application/vnd.ms-excel");
            // 下载文件的默认名称
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(excelName + ".xlsx", "utf-8"));
            OutputStream os = response.getOutputStream();
            try {

                // 在Excel工作簿中建一工作表，其名为缺省值
                XSSFSheet sheet = workbook.createSheet();
                // 在索引0的位置创建行（最顶端的行）
                XSSFRow row = sheet.createRow(0);
                // 设置excel头（第一行）的头名称
                for (int i = 0; i < headList.length; i++) {

                    // 在索引0的位置创建单元格（左上端）
                    XSSFCell cell = row.createCell(i);
                    // 定义单元格为字符串类型
                    cell.setCellType(XSSFCell.CELL_TYPE_STRING);
                    // 在单元格中输入一些内容
                    cell.setCellValue(headList[i]);
                }
                // ===============================================================
                // 添加数据
                for (int n = 0; n < dataList.size(); n++) {
                    // 在索引1的位置创建行（最顶端的行）
                    XSSFRow row_value = sheet.createRow(n + 1);
                    Map<String, Object> dataMap = dataList.get(n);
                    // ===============================================================
                    for (int i = 0; i < fieldList.length; i++) {

                        // 在索引0的位置创建单元格（左上端）
                        XSSFCell cell = row_value.createCell(i);
                        // 定义单元格为字符串类型
                        cell.setCellType(XSSFCell.CELL_TYPE_STRING);
                        // 在单元格中输入一些内容
                        cell.setCellValue((dataMap.get(fieldList[i])) != null ? (dataMap.get(fieldList[i])).toString() : "");
                    }
                    // ===============================================================
                }
                // 新建一输出文件流
                //FileOutputStream fos = new FileOutputStream(excel_name);
                // 把相应的Excel 工作簿存盘
                workbook.write(os);

            } finally {
                os.flush();
                // 操作结束，关闭文件
                os.close();
                // 关闭workbook
                workbook.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw e;
        }
    }
}