excel导入导出工具类

于 2020-09-18 23:10:42 发布
阅读量1.6k 收藏
点赞数
分类专栏： poi 文章标签： poi java excel
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_24790827/article/details/108674528
版权

使用java导出导入excel工具类

文章目录

导出

/**
     *
     * @return
     */
    @Override
    public File exportLogInfo() {
        Map<String,Object> resultMap = new HashMap<String, Object>();
        String sqlExceptSelect = "select *  from ZBIMS.IMS_EM_BasicInfo_DD where DELETEFLAG = 'N'";

        Map<String,String> IMS_EM_BasicInfo_DD_Columns = new LinkedHashMap<>();
        IMS_EM_BasicInfo_DD_Columns.put("DDSERIALNUM","xx");
        IMS_EM_BasicInfo_DD_Columns.put("MISSILEMODEL","xx");
        IMS_EM_BasicInfo_DD_Columns.put("DDTYPE","xx");
        IMS_EM_BasicInfo_DD_Columns.put("ZDBTYPE","xxx种类");
        IMS_EM_BasicInfo_DD_Columns.put("UNITS","xx");
        IMS_EM_BasicInfo_DD_Columns.put("COMPLETIONTIME","xx");
        IMS_EM_BasicInfo_DD_Columns.put("DELIVERYDATE","xx");
        IMS_EM_BasicInfo_DD_Columns.put("SPECIFIEDLIFE","xx");
        IMS_EM_BasicInfo_DD_Columns.put("PERIODICCHECKDATE","");
        IMS_EM_BasicInfo_DD_Columns.put("PERIODICCHECKCYCLE","");
        IMS_EM_BasicInfo_DD_Columns.put("PROLONGLIFENUM","");
        IMS_EM_BasicInfo_DD_Columns.put("LIFEEXTENSION","本次xx");
        IMS_EM_BasicInfo_DD_Columns.put("LIFEDATE","xx");
        
        ExcelExportUtil excelExportUtil = new ExcelExportUtil();
        File file = new File(excelExportUtil.getTitle());
        
        try {
            List<Record> ddList = Db.find(sqlExceptSelect);
            excelExportUtil.saveFile(IMS_EM_BasicInfo_DD_Columns, ddList, file);
            resultMap.put("msg","成功");
            resultMap.put("code",0);
        } catch (Exception e) {
            e.printStackTrace();
            resultMap.put("msg","失败");
            resultMap.put("code",1);
        }
        return file;
    }

导出工具类

package com.ims.zb.missile;

import com.jfinal.plugin.activerecord.Record;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.BorderStyle;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;

public class ExcelExportUtil {
    private static final String filePath = "/download/temp/";

    public static String getTitle(){
        File f = new File(filePath);
        if (!f.exists()){
            f.mkdirs();
        }
        Date date = new Date();
        SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
        String title = filePath + df.format(date)+"_xx基本信息.xls";
        System.out.println("********title="+title);
        return title;
    }
    public static String getTitle1(){
        File f = new File(filePath);
        if (!f.exists()){
            f.mkdirs();
        }
        Date date = new Date();
        SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
        String title = filePath + df.format(date)+"_xx设备信息.xls";
        System.out.println("********title="+title);
        return title;
    }

    public static String getTitle2(){
        File f = new File(filePath);
        if (!f.exists()){
            f.mkdirs();
        }
        Date date = new Date();
        SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
        String title = filePath + df.format(date)+"_软件列表信息.xls";
        System.out.println("********title="+title);
        return title;
    }
    public static File saveFile(Map<String,String> headData, List<Record> list, File file){
        //创建工作簿
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
        //创建工作表
        HSSFSheet hssfSheet = hssfWorkbook.createSheet();
        //创建行
        HSSFRow row = hssfSheet.createRow(0);
        //创建单元格
        HSSFCell cell = null;
        //初始化
        int rowIndex = 0;
        int cellIndex = 0;

        /**
         * 单元格 样式
         */
        HSSFCellStyle cellStyle = hssfWorkbook.createCellStyle();
        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setTopBorderColor(HSSFColor.BLACK.index);
        cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
        cellStyle.setLeftBorderColor(HSSFColor.BLACK.index);
        cellStyle.setRightBorderColor(HSSFColor.BLACK.index);
        // cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 水平居中
        // cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 上下居中

        HSSFFont titleFont = hssfWorkbook.createFont();
        // titleFont.setFontHeight();
        titleFont.setBold(true);

        //创建标题行
        row = hssfSheet.createRow(rowIndex);
        rowIndex++;
        //遍历标题
        for (String h : headData.keySet()){
            //创建列
            cell = row.createCell(cellIndex);
            //索引递增
            cellIndex++;
            //逐列插入标题
            cell.setCellValue(headData.get(h));
        }

        Record record = null;

        if (list != null){
            //循环所有记录
            for (int i = 0;i<list.size();i++){
                //获取当前行数据
                row = hssfSheet.createRow(rowIndex);
                record = list.get(i);
                rowIndex++;
                cellIndex = 0;
                for (String h : headData.keySet()){
                    cell = row.createCell(cellIndex);
                    cellIndex++;
                    cell.setCellValue(record.get(h) == null ? "" : record.get(h).toString());
                }
            }
        }
        try {
            FileOutputStream fileOutputStream = new FileOutputStream(file);
            hssfWorkbook.write(fileOutputStream);
            fileOutputStream.flush();
            fileOutputStream.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return file;
    }
}

导入

 /**
     *
     * @param file
     * @param type
     * @param dd_id
     * @return
     */
    @Override
    public Map<String, Object> importInfoUseExcel(UploadFile file, int type,int dd_id) {
        long l = System.currentTimeMillis();
        Date date = new Date(l);
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyyMMddHHmmss");
        String time = dateFormat.format(date);
        Map<String, Object> retMap = new HashMap<String, Object>();

        Map<String,String> columnMap = null;
        Class<? extends Model<?>> modelClass = null;
        switch (type){
            case 0 : {
                columnMap = TableColumn.IMS_EM_BasicInfo_DD_Columns;
                modelClass = ImsEmBasicinfoDd.class;
                break;
            }
            case 1 : {
                columnMap = TableColumn.IMS_EM_BasicInfo_DSSB_Columns;
                modelClass = ImsEmBasicinfoDssb.class;
                break;
            }
            case 2 : {
                columnMap = TableColumn.IMS_EM_State_DD_Columns;
                modelClass = ImsEmStateDd.class;
                break;
            }

            case 3: {
                columnMap = TableColumn.IMS_EM_Software_Columns;
                modelClass = ImsEmSoftware.class;
            }

            default: break;
        }

        try {
            List<Model<?>> modelList = AnalysisExcelFile.analysisExcel(file,columnMap,modelClass);
            boolean flag = true;
            for (int i = 0; i < modelList.size(); i++) {
                if (type!=0) {
                    modelList.get(i).set("DD_ID",dd_id);
                }

                if (type==3) {
                    modelList.get(i).remove("ID");
                }

                if (type==1) {
                    String serialnum = modelList.get(i).getStr("SERIALNUM");
                    modelList.get(i).remove("ID");
                    List<Record> records = Db.find("select * from ZBIMS.IMS_EM_BasicInfo_DSSB where SERIALNUM=? AND DD_ID=? AND DELETEFLAG='N'", serialnum,dd_id);
                    if (records.size()!=0) {
                        retMap.put("msg","导入失败，存在相同的"+serialnum+"设备");
                        retMap.put("code",1);
                        flag = false;
                    }
                }else if (type==0) {
                    String ddserialnum = modelList.get(i).getStr("DDSERIALNUM");
                    List<Record> records = Db.find("select * from ZBIMS.IMS_EM_BasicInfo_DD where DDSERIALNUM=? AND DELETEFLAG='N'", ddserialnum);
                    if (records.size()!=0) {
                        retMap.put("msg","导入失败，存在相同的"+ddserialnum+"编号");
                        retMap.put("code",1);
                        flag = false;
                    }
                }

                //存入时标
                if (type==0 || type==1) {
                    modelList.get(i).set("TIMESCALE",time);
                }

            }
            if (flag) {
                Db.batchSave(modelList, modelList.size());
                retMap.put("msg", "导入成功");
                retMap.put("code", 0);
            }
        } catch (Exception e) {
            e.printStackTrace();
            retMap.put("msg", "导入失败");
            retMap.put("code", 1);
        }
        return retMap;
    }

工具类1

package com.ims.common.utils;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class TableColumn {

    public static final Map<String,String> IMS_EM_BasicInfo_DD_Columns = new HashMap<String, String>(); //基本信息

    static{
        IMS_EM_BasicInfo_DD_Columns.put("xx","DDSERIALNUM");
        IMS_EM_BasicInfo_DD_Columns.put("xx","MISSILEMODEL");
        IMS_EM_BasicInfo_DD_Columns.put("xx种","DDTYPE");
        IMS_EM_BasicInfo_DD_Columns.put("xx类","ZDBTYPE");
        IMS_EM_BasicInfo_DD_Columns.put("xx位","UNITS");
        IMS_EM_BasicInfo_DD_Columns.put("xx总期","COMPLETIONTIME");
        IMS_EM_BasicInfo_DD_Columns.put("xx期","DELIVERYDATE");
        IMS_EM_BasicInfo_DD_Columns.put("xx规命","SPECIFIEDLIFE");
        IMS_EM_BasicInfo_DD_Columns.put("定期","PERIODICCHECKDATE");
        IMS_EM_BasicInfo_DD_Columns.put("定期","PERIODICCHECKCYCLE");
        IMS_EM_BasicInfo_DD_Columns.put("第寿","PROLONGLIFENUM");
        IMS_EM_BasicInfo_DD_Columns.put("本命","LIFEEXTENSION");
        IMS_EM_BasicInfo_DD_Columns.put("xx期","LIFEDATE");

    }
    
}

工具类2

package com.ims.common.utils;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.NumberFormat;
import java.text.SimpleDateFormat;
import java.util.*;

import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Model;
import com.sun.org.apache.bcel.internal.generic.IF_ACMPEQ;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.jfinal.upload.UploadFile;

public class AnalysisExcelFile {

	/**
	 *
	 * @param uploadFile  上传的文件
	 * @param columnMap  excel的列名与model的属性之间的映射关系
	 * @param modelClass  model的类
	 * @return
	 */
	public static List<Model<?>> analysisExcel(UploadFile uploadFile,Map<String,String> columnMap,Class<? extends Model<?>> modelClass){
		List<Model<?>> models = null;  //excel表格的每一行映射为一个model
		List<List<String>> excelData = analysisExcel(uploadFile, columnMap);
		if(excelData !=null){
			models = new ArrayList<Model<?>>();
			List<String> head = excelData.get(0);
			for(int i=1;i<excelData.size();i++){
				try {
					Model<?> model = modelClass.newInstance();
					List<String> cellValueList = excelData.get(i);
					for(int j=0;j<cellValueList.size();j++){
						model.set(head.get(j),cellValueList.get(j));
						ModelUtils.setAttrs(model,ModelUtils.INSERT);
					}

					models.add(model);

				} catch (InstantiationException e) {
					e.printStackTrace();
				} catch (IllegalAccessException e) {
					e.printStackTrace();
				}
			}

		}
		return models;
	}

	/**
	 * 解析excel文件的每一个单元格的内容
	 * @param uploadFile
	 * @return
	 */
	public static List<List<String>> analysisExcel(UploadFile uploadFile,Map<String,String> columnMap){
		Workbook wb = null;
		Sheet sheet = null;
		Row row = null;
		String cellValue = null;
		List<List<String>> list = null;
		List<String> rowList = null;
		wb = readExcel(uploadFile.getUploadPath()+"/"+uploadFile.getFileName());
		if(wb != null){
			list = new ArrayList<List<String>>();
			sheet = wb.getSheetAt(0); //获取第一个工作簿
			int rownum = sheet.getLastRowNum() + 1;  //从0开始
			int colnum = sheet.getRow(0).getLastCellNum();
			for(int i = 0;i<rownum;i++){
				row = sheet.getRow(i);
				if(row != null){
					rowList = new ArrayList<String>();
					for(int j = 0;j<colnum;j++){
					    if (row.getCell(j)==null) {
					        cellValue = null;
                        }else if(row.getCell(j).getCellType()==Cell.CELL_TYPE_BLANK){
					    	cellValue=null;
						}else {
                            cellValue = getCellFormatValue(row.getCell(j)).toString();
							System.out.print(cellValue+",");
                            if(i==0){//如果是第一行，存入的就是事先定义好的表结构
                                cellValue = columnMap.get(cellValue);
                            }
                        }
						rowList.add(cellValue);
					}
				}
				System.out.println();

				list.add(rowList);
			}
		}
		return list;
	}

	
	//读取excel文件

	/**
	 *
	 * @param filepath
	 * @return
	 */
	public static Workbook readExcel(String filepath){
		Workbook wb = null;
		if(filepath == null){
			return null;
		}
		String extString = filepath.substring(filepath.lastIndexOf(".")).toLowerCase();
		InputStream iStream = null;
		try {
			iStream = new FileInputStream(filepath);
			if(".xls".equals(extString)){
				wb = new HSSFWorkbook(iStream);
			}else if(".xlsx".equals(extString)){
				wb = new XSSFWorkbook(iStream);
			}else{
				wb = null;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return wb;
	}

	/**
	 *
	 * @param cell
	 * @return
	 */
	public static String getStringCellValue(Cell cell) {
		if(cell == null){
			return "";
		}
		String strCell = "";
		switch (cell.getCellType()) {
			case HSSFCell.CELL_TYPE_STRING:
				strCell = cell.getStringCellValue();
				break;
			case HSSFCell.CELL_TYPE_NUMERIC:
				if (HSSFDateUtil.isCellDateFormatted(cell)) {  // 处理日期格式、时间格式
					SimpleDateFormat sdf = null;
					if (cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm")) {
						sdf = new SimpleDateFormat("HH:mm");
					} else {// 日期
						sdf = new SimpleDateFormat("yyyy-MM-dd");
					}
					Date date = cell.getDateCellValue();
					strCell = sdf.format(date);
				} else if (cell.getCellStyle().getDataFormat() == 58||cell.getCellStyle().getDataFormat() == 14||cell.getCellStyle().getDataFormat() == 57||cell.getCellStyle().getDataFormat() == 31) {
// 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)
					SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
					double value = cell.getNumericCellValue();
					Date date = org.apache.poi.ss.usermodel.DateUtil
							.getJavaDate(value);
					strCell = sdf.format(date);
				} else {
					double value = cell.getNumericCellValue();
					CellStyle style = cell.getCellStyle();
					DecimalFormat format = new DecimalFormat();
					String temp = style.getDataFormatString();
// 单元格设置成常规
					if (temp.equals("General")) {
						format.applyPattern("#");
					}
					strCell = format.format(value);
				}
				break;
			case HSSFCell.CELL_TYPE_BOOLEAN:
				strCell = String.valueOf(cell.getBooleanCellValue());
				break;
			case HSSFCell.CELL_TYPE_FORMULA://新加的公式类型
				strCell =cell.getCellFormula().toString();
				break;
			case HSSFCell.CELL_TYPE_BLANK:
				strCell = "";
				break;
			default:
				strCell = "";
				break;
		}
		if (strCell.equals("") || strCell == null) {
			return "";
		}
		if (cell == null) {
			return "";
		}
		return strCell;
	}
	
	//获取单元格的数据

	/**
	 *
	 * @param cell
	 * @return
	 */
    public static Object getCellFormatValue(Cell cell){
        Object cellValue = null;
        if(cell != null){
            switch (cell.getCellType()) {
                case Cell.CELL_TYPE_NUMERIC:{   //数字
//                    cellValue = String.valueOf(cell.getNumericCellValue());
                 /*   NumberFormat nf = NumberFormat.getInstance();
                    cellValue = nf.format(cell.getNumericCellValue());
                    if (((String) cellValue).indexOf(",")>=0) {
                        cellValue = ((String) cellValue).replace(",","");
                    }*/

					if (HSSFDateUtil.isCellDateFormatted(cell)) {  // 处理日期格式、时间格式
						SimpleDateFormat sdf = null;
						if (cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm")) {
							sdf = new SimpleDateFormat("HH:mm");
						} else {// 日期
							sdf = new SimpleDateFormat("yyyy-MM-dd");
						}
						Date date = cell.getDateCellValue();
						cellValue = sdf.format(date);
					} else if (cell.getCellStyle().getDataFormat() == 58||cell.getCellStyle().getDataFormat() == 14||cell.getCellStyle().getDataFormat() == 57||cell.getCellStyle().getDataFormat() == 31) {
// 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)
						SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
						double value = cell.getNumericCellValue();
						Date date = org.apache.poi.ss.usermodel.DateUtil
								.getJavaDate(value);
						cellValue = sdf.format(date);
					} else {
						double value = cell.getNumericCellValue();
						CellStyle style = cell.getCellStyle();
						DecimalFormat format = new DecimalFormat();
						String temp = style.getDataFormatString();
// 单元格设置成常规
						if (temp.equals("General")) {
							format.applyPattern("#");
						}
						cellValue = format.format(value);
					}
                    break;
                }
                case Cell.CELL_TYPE_FORMULA:{//公式
                    if(DateUtil.isCellDateFormatted(cell)){
                        cellValue = cell.getDateCellValue();
                    }else{
                        cellValue = String.valueOf(cell.getNumericCellValue());
                    }
                    break;
                }
                case Cell.CELL_TYPE_STRING:{//字符串
                    cellValue = cell.getRichStringCellValue();
                    break;
                }
//				case Cell.CELL_TYPE_BLANK:{
//					cellValue = "";
//				}
                default:
                    break;
            }
        }
        return cellValue;
    }
	
}


![在这里插入图片描述](https://img-blog.csdnimg.cn/f79f3335cf9b45ae8de872461c0de382.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBAQOaYr1FR5ZWK,size_20,color_FFFFFF,t_70,g_se,x_16)
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值