Excel导入导出工具类

最新推荐文章于 2024-02-13 14:26:06 发布

king_qc

最新推荐文章于 2024-02-13 14:26:06 发布

阅读量273

点赞数

分类专栏： java 文章标签： ExcelUtil

本文链接：https://blog.csdn.net/king_qc/article/details/85079805

版权

java 专栏收录该内容

12 篇文章 0 订阅

订阅专栏

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.1.2</version>
</dependency>

Excel导出：

package com.sf.common.util;

import java.io.IOException;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;

import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.math.NumberUtils;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
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.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFDataValidation;

/**
 * 功能:导出工具类
 * 
 * @author 小新
 *
 */
public abstract class ExportExcelUtil {

	private ExportExcelUtil() {
	}

	/**
	 * Excel后缀：xlsx
	 */
	public static final String SUFFIX_XLSX = "xlsx";
	/**
	 * 	字体：Times new roman
	 */
	public static final String TIMES_NEW_ROMAN = "Times new roman";
	private static final String DataValidationError1 = "提示信息：";
    private static final String DataValidationError2 = "数据不规范，请选择表格下拉列表中的数据！";
	/**
	 * 导出excel
	 * 
	 * @param response
	 *            HttpServletResponse响应对象
	 * @param dataList
	 *            数据列表,列表中的项支持实体对象类型或Map类型
	 * @param fileName
	 *            导出Excel的下载文件名称
	 * @param sheetName
	 *   sheet页名称
	 * @param keys
	 *            数据的属性名称:可以是map的key值、或对象的属性名称
	 * @param cellTitles
	 *            第一行的列标题，不用标题的话可传值为null
	 * @param typeface
	 *           字体
	 * @param columnMap
	 *           列宽
	 * @param selectList
	 *           下拉列表值
	 * @throws IOException
	 */
	public static void exportExcel(HttpServletResponse response, List<Object> dataList, String fileName, String sheetName, String[] keys,
			String[] cellTitles, String typeface, HashMap<Integer, Integer> columnMap, List<String[]> selectList) throws IOException {
		exportExcelAddRow(response, dataList, fileName, sheetName, keys, cellTitles, typeface, null, columnMap, selectList);
	}

	/**
	 * 导出excel需要替换值的
	 * 
	 * @param response
	 *            HttpServletResponse响应对象
	 * @param dataList
	 *            数据列表,列表中的项支持实体对象类型或Map类型
	 * @param fileName
	 *            导出Excel的下载文件名称
	 * @param keys
	 *            数据的属性名称:可以是map的key值、或对象的属性名称
	 * @param cellTitles
	 *            第一行的列标题，不用标题的话可传值为null
	 * @param typeface
	 *            字体
	 * @param replaceMap
	 *            替换值的map,第一层map的key为要替换的字段名，value中的map则为原值与替换值的对应关系。
	 *            不用可传此参数为null或调用exportExcel方法
	 * @throws IOException
	 */
	public static void exportExcelForReplace(HttpServletResponse response, List<Object> dataList, String fileName,
			String[] keys, String[] cellTitles, String typeface, Map<String, Map<String, String>> replaceMap) throws IOException {
		exportExcelAddRow(response, dataList, fileName, null, keys, cellTitles, typeface, replaceMap, null, null);
	}
	/**
	 * 导出excel
	 * 
	 * @param response
	 *            HttpServletResponse响应对象
	 * @param dataList
	 *            数据列表,列表中的项支持实体对象类型或Map类型
	 * @param fileName
	 *            导出Excel的下载文件名称
	 * @param sheetName
	 *   sheet页名称
	 * @param keys
	 *            数据的属性名称:可以是map的key值、或对象的属性名称
	 * @param cellTitles
	 *            第一行的列标题，不用标题的话可传值为null
	 * @param typeface
	 *           字体
	 * @param columnMap
	 *           列宽
	 * @param selectList
	 *           下拉列表值
	 * @throws IOException
	 */
	public static void exportExcelAddRow(HttpServletResponse response, List<Object> dataList, String fileName, String sheetName,
			String[] keys, String[] cellTitles, String typeface, Map<String, Map<String, String>> replaceMap, 
			HashMap<Integer, Integer> columnMap, List<String[]> selectList)
			throws IOException {
		if (CollectionUtils.isNotEmpty(dataList)) {
			OutputStream os = null;
			// 把XSSFWorkbook升级为SXSSFWorkbook，通过设置内存中的行数上限，超过这个上限的行就把它刷到硬盘，减少内存占用
			try (SXSSFWorkbook outWb = new SXSSFWorkbook(100)) {
				response.reset();
				response.setContentType("application/vnd.ms-excel;charset=utf-8");
				response.addHeader("Content-Disposition",
						"attachment; filename=\"" + new String(fileName.getBytes(), "ISO8859-1") + "\"");
				os = response.getOutputStream();

				Sheet outSheet = outWb.createSheet(StringUtils.isNotBlank(sheetName) ? sheetName : "sheet1");
				Font columnFont = outWb.createFont();
				CellStyle columnStyle = outWb.createCellStyle();
				columnFont.setFontName(StringUtils.isNotBlank(typeface) ? typeface : "宋体");
				columnFont.setFontHeightInPoints((short) 10);
				columnStyle.setFont(columnFont);
				columnStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);// 左右居中
				columnStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中
				int rowIndex = 0;
				if (cellTitles != null) {
					addArrayToRow(outSheet, 0, cellTitles, columnStyle);
					rowIndex = 1;
				}
				if(null != columnMap) {
					setColumnWidth(outSheet, columnMap);
				}
				if(null != selectList) {//设置下拉列表
					setDataValidation(outSheet, selectList, dataList.size());
				}
				for (int k = 0; k < dataList.size(); k++, rowIndex++) {
					addDataToRow(outSheet, rowIndex, dataList.get(k), keys, replaceMap, columnStyle);
				}

				outWb.write(os);
				outWb.dispose();

			} finally {
				if (os != null) {
					os.close();
					os = null;
				}
			}
		} else {
			// 显示无数据页面
			response.setContentType("text/html;charset=utf-8");
			response.getOutputStream().write("无数据显示!".getBytes("utf-8"));
		}
	}
	
	/**
     * 功能描述: 自定义列宽
     * 使用的方法：
     * HashMap<Integer,HashMap<Integer,Integer>>  mapSheet = new HashMap();
     * HashMap<Integer,Integer> mapColumn = new HashMap();
     * mapColumn.put(0,5);             //第一列，列宽为5
     * mapColumn.put(3,5);             //第四列，列宽为5
     * mapSheet.put(1, mapColumn);     //第一个元格列宽
     *
     * @param sheet
     * @param map
     */
    private static void setColumnWidth(Sheet sheet, HashMap<Integer, Integer> map) {
        if (map != null) {
            Iterator<Map.Entry<Integer, Integer>> iterator = map.entrySet().iterator();
            while (iterator.hasNext()) {
                Map.Entry<Integer, Integer> entry = (Map.Entry<Integer, Integer>) iterator.next();
                sheet.setColumnWidth(entry.getKey(), (int) entry.getValue() * 512);
            }
        }
    }
	
	/**
     * 功能描述:下拉列表
     * 使用的方法：
     * HashMap hashMap = new HashMap();
     * List<String[]> sheet1 = new ArrayList<>();                  //第一个表格设置。
     * String[] sheetColumn1 = new String[]{"1", "2", "4"};        //必须放第一：设置下拉列表的列（excel从零行开始数）
     * String[] sex = {"男","女"};                                  //下拉的值放在 sheetColumn1 后面。
     * sheet1.add(sheetColumn1);
     * sheet1.add(sex);
     * hashMap.put(1,sheet1);                                      //第一个表格的下拉列表值
     *
     * @param sheet
     * @param dropDownListData
     * @param dataListSize
     */
    private static void setDataValidation(Sheet sheet, List<String[]> selectList, int dataListSize) {
        if (selectList.size() > 0) {
            for (int col = 0; col < selectList.get(0).length; col++) {
                Integer colv = Integer.parseInt(selectList.get(0)[col]);
                if(selectList.size() > col + 1) {
                	setDataValidation(sheet, selectList.get(col + 1), 1, dataListSize < 100 ? 500 : dataListSize, colv, colv);
                }
            }
        }
    }


    /**
     * 功能描述:下拉列表
     *
     * @param xssfWsheet
     * @param list
     * @param firstRow
     * @param lastRow
     * @param firstCol
     * @param lastCol
     */
    private static void setDataValidation(Sheet xssfWsheet, String[] list, Integer firstRow, Integer lastRow, Integer firstCol, Integer lastCol) {
        DataValidationHelper helper = xssfWsheet.getDataValidationHelper();
        CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
        DataValidationConstraint constraint = helper.createExplicitListConstraint(list);
        DataValidation dataValidation = helper.createValidation(constraint, addressList);
        dataValidation.createErrorBox(DataValidationError1, DataValidationError2);
        //  处理Excel兼容性问题
        if (dataValidation instanceof XSSFDataValidation) {
            dataValidation.setSuppressDropDownArrow(true);
            dataValidation.setShowErrorBox(true);
        } else {
            dataValidation.setSuppressDropDownArrow(false);
        }
        xssfWsheet.addValidationData(dataValidation);
    }

	/**
	 * 从Map初始化为Excel行Row
	 * 
	 * @param sheet
	 * @param index
	 * @param map
	 * @param keys
	 */
	private static void addDataToRow(Sheet sheet, int index, Object data, String[] keys,
			Map<String, Map<String, String>> replaceMap, CellStyle columnStyle) {
		Row row = sheet.createRow(index);
		if (data.getClass().isAssignableFrom(Map.class)) {
			@SuppressWarnings("unchecked")
			Map<String, Object> dataMap = (Map<String, Object>) data;

			for (int i = 0; i < keys.length; i++) {
				Cell cell = row.createCell(i);
				Object obj = dataMap.get(keys[i]);
				// 如果是数值类型，把所在的单元格的格式设置为数值型
				cellSetValue(keys, replaceMap, i, cell, obj);
				cell.setCellStyle(columnStyle);
			}
		} else {
			for (int i = 0; i < keys.length; i++) {
				Cell cell = row.createCell(i);
				Object obj = ReflectUtil.invokeGet(data, keys[i]);

				cellSetValue(keys, replaceMap, i, cell, obj);
				cell.setCellStyle(columnStyle);
			}
		}

	}

	/**
	 * @param keys
	 * @param replaceMap
	 * @param i
	 * @param cell
	 * @param obj
	 */
	private static void cellSetValue(String[] keys, Map<String, Map<String, String>> replaceMap, int i, Cell cell,
			Object obj) {
		if (isNumber(obj)) {
			cell.setCellType(Cell.CELL_TYPE_NUMERIC);
			cell.setCellValue(NumberUtils.toDouble(obj.toString(), 0));
		} else if (obj instanceof Date) {
			Date dateValue = (Date) obj;
			cell.setCellValue(SysDateUtils.formatDateTime(dateValue));
		} else {
			String value = SysUtil.valueOfReplaceNull(obj);
			// 值替换操作
			value = replaceValue(value, keys[i], replaceMap);
			cell.setCellValue(value);
		}
	}

	/**
	 * @param obj
	 * @return
	 */
	private static boolean isNumber(Object obj) {
		return obj instanceof BigDecimal || obj instanceof Double || obj instanceof Integer || obj instanceof Long;
	}

	/**
	 * 转换数组为excel中的行
	 * @param sheet
	 * @param index
	 * @param keys
	 * @param columnStyle	单元格样式
	 */
	private static void addArrayToRow(Sheet sheet, int index, String[] keys, CellStyle columnStyle) {
		Row row = sheet.createRow(index);
		for (int i = 0; i < keys.length; i++) {
			Cell cell = row.createCell(i);
			cell.setCellValue(keys[i]);
			cell.setCellStyle(columnStyle);
		}
	}

	/**
	 * 功能：值替换，如状态数字1,2,3转换为对应状态的文本或其他特殊值替换处理
	 * 
	 * @param value
	 * @param key
	 * @param replaceMap
	 * @return
	 */
	private static String replaceValue(String value, String key, Map<String, Map<String, String>> replaceMap) {
		// 值替换操作
		if (replaceMap == null) {
			return value;
		}
		Map<String, String> map = replaceMap.get(key);
		if (map == null) {
			return value;
		}
		String replaceValue = map.get(value);
		if (replaceValue != null) {
			return replaceValue;
		}
		return value;
	}

	/**
	 * 获取Excel版本号
	 * @param filePath
	 * @return
	 */
	public static int getExcelVersion(String filePath){
		if(isExcel2003(filePath)){
			return com.com.sf.utils.ExcelUtil.EXCEL2003TYPE;
		}
		if(isExcel2007(filePath)){
			return com.com.sf.utils.ExcelUtil.EXCEL2007TYPE;
		}
		return NumberUtils.INTEGER_ZERO;
	}

	/**
	 * 校验是否是2003版的excel
	 * @param filePath
	 * @return	返回true是2003
	 */
	public static boolean isExcel2003(String filePath)  {
		return filePath.matches("^.+\\.(?i)(xls)$");
	}

	/**
	 * 校验是否是2007版的excel
	 * @param filePath
	 * @return	返回true是2007
	 */
	public static boolean isExcel2007(String filePath)  {
		return filePath.matches("^.+\\.(?i)(xlsx)$");
	}
}

使用：

List<Object> data = Lists.newArrayList(new ForecastAllocationDto());
			String[] keys = {"constraintFlag", "division","divisionName","brand","material","materialNameEn","materialName","totalQty",
					"tmallQty", "brandSiteQty","totalQty", "baozunNcoQty", "leqeeNcoQty","ucoNcoQty"};
			String[] heads = {"Constraint Flag", "Division", "Division Name", "Brand", "Material", "English Name", "Chinese Name", 
					"online TTL", "Tmall", "Brand Site", "CMB", "BAOZUN NCO", "LEQEE NCO", "UCO NCO"};
			String dateStr = TimeUtil.parseTime(LocalDateTime.now(), TimeFormat.LONG_DATE_PATTERN_HHMM_NOT_LINE);
			String fileName = String.format("template_forecast_%s.xlsx", dateStr);
			String sheetName = "Forecast";
			//自定义列宽
			HashMap<Integer, Integer> columnMap = Maps.newHashMap();
			for(int i = 0;i < heads.length; i ++) {
				//自定义列宽
				columnMap.put(i, 8);
			}
			
		    List<String[]> selectList = Lists.newArrayList();               				//设置下拉列表
		    String[] columns = new String[]{"0","1"};        								//必须放第一：设置下拉列表的列（excel从零行开始数）
		    String[] values = {"Y,N"};                                   					//下拉的值放在 columns 后面。
		    selectList.add(columns);
		    selectList.add(values);
			try {
				ExportExcelUtil.exportExcel(ServletActionContext.getResponse(), data, fileName, sheetName, keys, heads, "Times new roman", columnMap, selectList);
			} catch (Exception e) {
				logger.error("download allocation Forecast Exception.username:{} ,error：{}", UserContext.getCurrentUser().getUsername(), e);
				throw new RuntimeException("download allocation Forecast template fail！");
			}

Excel导入：https://download.csdn.net/download/king_qc/10859796

使用：

List<ForecastAllocationDto> list = ExcelUtil.importAllExcelToBeans(new FileInputStream(uploadfile), excelVersion, ForecastAllocationDto.class);

实体类：

package com.sf.cwsp.elc.dto;

import java.io.Serializable;
import java.math.BigDecimal;

import com.com.sf.annotation.ExcelTar;
import com.com.sf.convert.BigDecimalConvert;

/**
 * 	描述：Forecast导入DTO
 *  @author 小新
 */
@SuppressWarnings("serial")
public class ForecastAllocationDto implements Serializable {
	/** 货主 */
	private String customerid;
	/** 月份 */
	private String period;
	
	@ExcelTar(value="Constraint Flag")
	private String constraintFlag;

	@ExcelTar(value="Division")
	private String division;

	@ExcelTar(value="Division Name")
	private String divisionName;
	
	@ExcelTar(value="Brand")
	private String brand;
	
	@ExcelTar(value="Material")
	private String material;
	
	@ExcelTar(value="English Name")
	private String materialNameEn;
	
	@ExcelTar(value="Chinese Name")
	private String materialName;
	
	@ExcelTar(value="online TTL",convert=BigDecimalConvert.class)
	private BigDecimal totalQty;
	
	@ExcelTar(value="Tmall",convert=BigDecimalConvert.class)
	private BigDecimal tmallQty;
	
	@ExcelTar(value="Brand Site",convert=BigDecimalConvert.class)
	private BigDecimal brandSiteQty;

	@ExcelTar(value="CMB",convert=BigDecimalConvert.class)
	private BigDecimal cmbQty;
	
	@ExcelTar(value="BAOZUN NCO",convert=BigDecimalConvert.class)
	private BigDecimal baozunNcoQty;
	
	@ExcelTar(value="LEQEE NCO",convert=BigDecimalConvert.class)
	private BigDecimal leqeeNcoQty;
	
	@ExcelTar(value="UCO NCO",convert=BigDecimalConvert.class)
	private BigDecimal ucoNcoQty;

	public String getConstraintFlag() {
		return constraintFlag;
	}

	public void setConstraintFlag(String constraintFlag) {
		this.constraintFlag = constraintFlag;
	}

	public BigDecimal getBaozunNcoQty() {
		return baozunNcoQty;
	}

	public void setBaozunNcoQty(BigDecimal baozunNcoQty) {
		this.baozunNcoQty = baozunNcoQty;
	}

	public BigDecimal getLeqeeNcoQty() {
		return leqeeNcoQty;
	}

	public void setLeqeeNcoQty(BigDecimal leqeeNcoQty) {
		this.leqeeNcoQty = leqeeNcoQty;
	}

	public BigDecimal getUcoNcoQty() {
		return ucoNcoQty;
	}

	public void setUcoNcoQty(BigDecimal ucoNcoQty) {
		this.ucoNcoQty = ucoNcoQty;
	}

	public String getCustomerid() {
		return customerid;
	}

	public void setCustomerid(String customerid) {
		this.customerid = customerid;
	}

	public String getPeriod() {
		return period;
	}

	public void setPeriod(String period) {
		this.period = period;
	}

	public String getDivision() {
		return division;
	}

	public void setDivision(String division) {
		this.division = division;
	}

	public String getDivisionName() {
		return divisionName;
	}

	public void setDivisionName(String divisionName) {
		this.divisionName = divisionName;
	}

	public String getBrand() {
		return brand;
	}

	public void setBrand(String brand) {
		this.brand = brand;
	}

	public String getMaterial() {
		return material;
	}

	public void setMaterial(String material) {
		this.material = material;
	}

	public String getMaterialNameEn() {
		return materialNameEn;
	}

	public void setMaterialNameEn(String materialNameEn) {
		this.materialNameEn = materialNameEn;
	}

	public String getMaterialName() {
		return materialName;
	}

	public void setMaterialName(String materialName) {
		this.materialName = materialName;
	}

	public BigDecimal getTotalQty() {
		return totalQty;
	}

	public void setTotalQty(BigDecimal totalQty) {
		this.totalQty = totalQty;
	}

	public BigDecimal getTmallQty() {
		return tmallQty;
	}

	public void setTmallQty(BigDecimal tmallQty) {
		this.tmallQty = tmallQty;
	}

	public BigDecimal getBrandSiteQty() {
		return brandSiteQty;
	}

	public void setBrandSiteQty(BigDecimal brandSiteQty) {
		this.brandSiteQty = brandSiteQty;
	}

	public BigDecimal getCmbQty() {
		return cmbQty;
	}

	public void setCmbQty(BigDecimal cmbQty) {
		this.cmbQty = cmbQty;
	}

	@Override
	public String toString() {
		return "ForecastAllocationDto [customerid=" + customerid + ", period=" + period + ", division=" + division
				+ ", divisionName=" + divisionName + ", brand=" + brand + ", material=" + material + ", materialNameEn="
				+ materialNameEn + ", materialName=" + materialName + ", totalQty=" + totalQty + ", tmallQty="
				+ tmallQty + ", brandSiteQty=" + brandSiteQty + ", cmbQty=" + cmbQty + ", baozunNcoQty=" + baozunNcoQty
				+ ", leqeeNcoQty=" + leqeeNcoQty + ", ucoNcoQty=" + ucoNcoQty + "]";
	}
}

附上一位老哥的ExcelUtil开源代码：https://github.com/andyczy/czy-nexus-commons-utils/blob/master/README-3.2.md