Java使用poi实现excel的导入导出功能：
工具类ExcelUtil，用于解析和初始化excel的数据：代码如下
package com.raycloud.kmmp.item.service.util;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory; import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*; /**
* @author yuanshi.fu
* @description: excel操作工具类
* @date 2018/5/23 下午1:52
*/
public class ExcelUtil { /**
* 日志
*/
private static final Logger LOGGER = LoggerFactory.getLogger(ExcelUtil.class); /**
* 总行数
*/
private static int totalRows = 0; /**
* 总列数
*/
private static int totalCells = 0; /**
* 无参构造方法
*/
public ExcelUtil() {
} public static int getTotalRows() {
return totalRows;
} public static int getTotalCells() {
return totalCells;
} /**
* @param is 输入流
* @return List<List < String>>
* @throws
* @description: 导入excel数据
* @author yuanshi.fu
* @date 2018/5/23 下午1:56
*/
public static List<List<String>> importExcel(InputStream is) { //定义excel工作薄
Workbook wb = null;
try {
//创建excel工作薄
wb = WorkbookFactory.create(is);
} catch (IOException | InvalidFormatException e) {
LOGGER.error("[" + ExcelUtil.class.getName() + "] importExcel e ", e);
}
List<List<String>> dataList = readData(wb);
return dataList;
} /**
* @param wb excel工作薄
* @return List<List < String>>
* @throws
* @description: 读取excel数据
* @author yuanshi.fu
* @date 2018/5/23 下午2:07
*/
public static List<List<String>> readData(Workbook wb) {
List<List<String>> dataLst = new ArrayList<List<String>>();
if (null == wb) {
LOGGER.warn("[" + ExcelUtil.class.getName() + "] readData wb is null");
return Collections.EMPTY_LIST;
}
//获取第一个sheet
Sheet sheet = wb.getSheetAt(0);
//获取excel的行数
totalRows = sheet.getPhysicalNumberOfRows(); if (totalRows >= 1 && null != sheet.getRow(0)) {
totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
} //循环excel的行
for (int r = 1; r < totalRows; r++) {
Row row = sheet.getRow(r);
if (null == row) {
continue;
} //循环excel的列
List<String> rowLst = new ArrayList<String>();
for (int c = 0; c < totalCells; c++) {
Cell cell = row.getCell(c);
String cellValue = getCellValue(cell);
rowLst.add(cellValue);
}
//保存第r行的数据
dataLst.add(rowLst);
} return dataLst;
} /**
* @param is 输入流
* @return Map<String , ItemDTO>
* @throws
* @description: 导入excel数据
* @author yuanshi.fu
* @date 2018/5/23 下午1:56
*/
public static Map<String, ItemDTO> importExcelData(InputStream is) { //定义excel工作薄
Workbook wb = null;
try {
//创建excel工作薄
wb = WorkbookFactory.create(is);
} catch (IOException | InvalidFormatException e) {
LOGGER.error("[" + ExcelUtil.class.getName() + "] importExcelData e ", e);
}
Map<String, ItemDTO> dataList = readExcel2ItemDTO(wb);
return dataList;
} /**
* @param wb excel工作薄
* @return Map<String , ItemDTO>
* @throws
* @description: 读取excel数据并转换为ItemDTO
* @author yuanshi.fu
* @date 2018/5/23 下午2:07
*/
public static Map<String, ItemDTO> readExcel2ItemDTO(Workbook wb) { Map<String, ItemDTO> dataMap = new HashMap<String, ItemDTO>();
if (null == wb) {
LOGGER.warn("[" + ExcelUtil.class.getName() + "] readExcel wb is null");
return dataMap;
}
//获取第一个sheet
Sheet sheet = wb.getSheetAt(0);
//获取excel的行数
totalRows = sheet.getPhysicalNumberOfRows(); //循环excel的行
for (int r = 1; r < totalRows; r++) {
Row row = sheet.getRow(r);
if (null == row) {
continue;
} //excel的每行内容
String title = getCellValue(row.getCell(0));
String price = getCellValue(row.getCell(1));
//商品库存数量
String num = getCellValue(row.getCell(2));
//商品编号
String outerId = getCellValue(row.getCell(3));
// 商品图片数据,封装成itemImageDTOList
String mainImageUrl = getCellValue(row.getCell(4));
String imageUrl2 = getCellValue(row.getCell(5));
String imageUrl3 = getCellValue(row.getCell(6));
String imageUrl4 = getCellValue(row.getCell(7));
String imageUrl5 = getCellValue(row.getCell(8));
//相同的商品id的详情只取一个，需进行保存生成id
String itemDes = getCellValue(row.getCell(9));
//sku 封装成skuDTOList
String hasSku = getCellValue(row.getCell(10));
String firstSkuP = getCellValue(row.getCell(11));
String secondSkuP = getCellValue(row.getCell(12));
String thirdSkuP = getCellValue(row.getCell(13));
String firstSkuV = getCellValue(row.getCell(14));
String secondSkuV = getCellValue(row.getCell(15));
String thirdSkuV = getCellValue(row.getCell(16));
String skuPrise = getCellValue(row.getCell(17));
String skuQuantity = getCellValue(row.getCell(18)); //商品图片信息
List<String> imageUrls = new ArrayList<String>(5);
if (StringUtils.isNotEmpty(mainImageUrl)) {
imageUrls.add(mainImageUrl);
}
if (StringUtils.isNotEmpty(imageUrl2)) {
imageUrls.add(imageUrl2);
}
if (StringUtils.isNotEmpty(imageUrl3)) {
imageUrls.add(imageUrl3);
}
if (StringUtils.isNotEmpty(imageUrl4)) {
imageUrls.add(imageUrl4);
}
if (StringUtils.isNotEmpty(imageUrl5)) {
imageUrls.add(imageUrl5);
} //sku属性值
List<String> skuPropValue = new ArrayList<String>(3);
if (StringUtils.isNotEmpty(firstSkuP)) {
StringBuffer propValueSB = new StringBuffer(3);
propValueSB.append(firstSkuP).append(":").append(firstSkuV);
skuPropValue.add(propValueSB.toString());
}
if (StringUtils.isNotEmpty(secondSkuP)) {
StringBuffer propValueSB = new StringBuffer(3);
propValueSB.append(secondSkuP).append(":").append(secondSkuV);
skuPropValue.add(propValueSB.toString());
}
if (StringUtils.isNotEmpty(thirdSkuP)) {
StringBuffer propValueSB = new StringBuffer(3);
propValueSB.append(thirdSkuP).append(":").append(thirdSkuV);
skuPropValue.add(propValueSB.toString());
} //处理同一样商品
if (dataMap.containsKey(outerId)) {
//获取
ItemDTO itemDTO = dataMap.get(outerId);
List<SkuDTO> skuDTOList = itemDTO.getSkuDTOList();
if (CollectionUtils.isNotEmpty(skuDTOList)) {
//封装sku
SkuDTO skuDTO = new SkuDTO();
skuDTO.setCostPrice(new BigDecimal(0));
skuDTO.setOuterId(outerId);
skuDTO.setPrice(StringUtils.isEmpty(skuPrise) ? null : new BigDecimal(skuPrise)); skuDTO.setProperties("");
skuDTO.setPropertiesName(StringUtils.join(skuPropValue, ";"));
skuDTO.setQuantity(StringUtils.isEmpty(skuPrise) ? null : Long.valueOf(skuQuantity));
skuDTOList.add(skuDTO);
itemDTO.setSkuDTOList(skuDTOList);
}
//保存
dataMap.put(outerId, itemDTO);
} else {
//封装ItemDTO
ItemDTO itemDTO = new ItemDTO();
//商品目录
itemDTO.setCategoryId(-1L);
//商品销量
itemDTO.setCustomVolume(0L);
itemDTO.setTitle(StringUtils.trimToEmpty(title));
if (StringUtils.isNotEmpty(hasSku) && StringUtils.equals("是", hasSku) && CollectionUtils.isNotEmpty(skuPropValue)) {
//sku数据
List<SkuDTO> skuDTOList = new ArrayList<SkuDTO>(3);
//封装sku
SkuDTO skuDTO = new SkuDTO();
skuDTO.setCostPrice(new BigDecimal(0));
skuDTO.setOuterId(outerId);
skuDTO.setPrice(StringUtils.isEmpty(skuPrise) ? null : new BigDecimal(skuPrise));
skuDTO.setProperties("");
skuDTO.setPropertiesName(StringUtils.join(skuPropValue, ";"));
skuDTO.setQuantity(StringUtils.isEmpty(skuPrise) ? null : Long.valueOf(skuQuantity));
skuDTOList.add(skuDTO);
itemDTO.setSkuDTOList(skuDTOList);
} if (CollectionUtils.isNotEmpty(itemDTO.getSkuDTOList())) {
itemDTO.setHasSku(1);
itemDTO.setPrice(new BigDecimal(0));
} else {
itemDTO.setHasSku(0);
itemDTO.setPrice(StringUtils.isEmpty(price) ? null : new BigDecimal(price));
}
itemDTO.setNum(StringUtils.isEmpty(num) ? 0 : Long.valueOf(num));
itemDTO.setOuterId(StringUtils.trimToEmpty(outerId));
itemDTO.setMainImageId(0L);
itemDTO.setMainImageUrl(mainImageUrl);
List<ItemImageDTO> itemImageDTOList = new ArrayList<ItemImageDTO>(5); for (int i = 0; i < imageUrls.size(); i++) {
ItemImageDTO itemImageDTO = new ItemImageDTO();
itemImageDTO.setImageId(0L);
itemImageDTO.setImageUrl(imageUrls.get(i));
itemImageDTOList.add(itemImageDTO);
}
itemDTO.setItemImageDTOList(itemImageDTOList);
//商品描述
itemDTO.setDescription(itemDes);
itemDTO.setOriginPrice(new BigDecimal(0));
itemDTO.setSellerCategoryId("-1");
itemDTO.setSortId(0L);
itemDTO.setStatus(1);
itemDTO.setSubStock(1); //保存当前的商品信息
dataMap.put(outerId, itemDTO);
}
}
return dataMap;
} /**
* @param cell excel单元
* @return String
* @throws
* @description: 根据excel单元数据类型获取内容
* @author yuanshi.fu
* @date 2018/5/23 下午2:27
*/
public static String getCellValue(Cell cell) {
String cellValue = "";
if (null != cell) {
// 以下是判断数据的类型
switch (cell.getCellType()) {
case HSSFCell.CELL_TYPE_NUMERIC: // 数字
if (DateUtil.isCellDateFormatted(cell)) {
Date theDate = cell.getDateCellValue();
//todo 时间格式可修改
SimpleDateFormat dff = new SimpleDateFormat("yyyy-MM-dd");
cellValue = dff.format(theDate);
} else {
DecimalFormat df = new DecimalFormat("0");
cellValue = df.format(cell.getNumericCellValue());
}
break;
case HSSFCell.CELL_TYPE_STRING: // 字符串
cellValue = cell.getStringCellValue();
break; case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean
cellValue = cell.getBooleanCellValue() + "";
break; case HSSFCell.CELL_TYPE_FORMULA: // 公式
cellValue = cell.getCellFormula() + "";
break; case HSSFCell.CELL_TYPE_BLANK: // 空值
cellValue = "";
break; case HSSFCell.CELL_TYPE_ERROR: // 故障
cellValue = "非法字符";
break; default:
cellValue = "未知类型";
break;
} }
return cellValue;
} /**
* @param
* @return
* @throws
* @description: 导出excel表格数据
* @author yuanshi.fu
* @date 2018/5/28 下午5:17
*/
public static void exportExcel(String title, String[] rowName, List<Object[]> dataList, OutputStream os) {
try {
//创建工作薄对象
HSSFWorkbook workbook = new HSSFWorkbook();
//创建工作表
HSSFSheet sheet = workbook.createSheet(title);
//产生表格标题行
// HSSFRow rowm = sheet.createRow(0);
// HSSFCell cellTitle = rowm.createCell(0); //sheet样式定义【getColumnTopStyle()/getStyle()均为自定义方法 - 在下面 - 可扩展】
//获取列头样式对象
HSSFCellStyle columnTopStyle = getColumnTopStyle(workbook);
//单元格样式对象
HSSFCellStyle style = getStyle(workbook); //设置标题列的单元格数
// sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, (rowName.length - 1)));
// cellTitle.setCellStyle(columnTopStyle);
// cellTitle.setCellValue(title); //定义所需列数
int columnNum = rowName.length;
// 在索引0的位置创建行(最顶端的行开始的第二行）
HSSFRow hssfRow = sheet.createRow(0); for (int n = 0; n < columnNum; n++) {
//创建列头对应个数的单元格
HSSFCell hssfCell = hssfRow.createCell(n);
//设置列头单元格的数据类型
hssfCell.setCellType(HSSFCell.CELL_TYPE_STRING);
HSSFRichTextString text = new HSSFRichTextString(rowName[n]);
//设置列头单元格的值
hssfCell.setCellValue(text);
//设置列头单元格的样式
hssfCell.setCellStyle(style);
} for (int i = 0; i < dataList.size(); i++) {
//填充数据
Object[] obj = dataList.get(i);
//创建所在的行数
HSSFRow row = sheet.createRow(i + 1); for (int j = 0; j < obj.length; j++) {
HSSFCell cell = row.createCell(j, HSSFCell.CELL_TYPE_STRING);
if (obj[j] != null && StringUtils.isNotEmpty(obj[j].toString())) {
//设置单元格的值
cell.setCellValue(obj[j].toString());
} else {
cell.setCellValue("");
}
//设置单元格格式
cell.setCellStyle(style);
}
} //让列宽随着导出的列长自动适应
for (int colNum = 0; colNum < columnNum; colNum++) {
int columnWidth = sheet.getColumnWidth(colNum) / 256;
for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {
HSSFRow currentRow;
//当前行未被使用过
if (sheet.getRow(rowNum) == null) {
currentRow = sheet.createRow(rowNum);
} else {
currentRow = sheet.getRow(rowNum);
}
if (currentRow.getCell(colNum) != null) {
HSSFCell currentCell = currentRow.getCell(colNum);
String cellValue = currentCell.getStringCellValue();
if (StringUtils.isNotEmpty(cellValue)
&& currentCell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
int length = cellValue.getBytes().length;
if (columnWidth < length) {
columnWidth = length;
}
}
}
}
if(colNum == 0){
sheet.setColumnWidth(colNum, (columnWidth-2) * 256);
}else{
sheet.setColumnWidth(colNum, (columnWidth+4) * 256);
}
} //让列宽随着导出的列长自动适应
workbook.write(os);
} catch (Exception e) {
LOGGER.error("[" + ExcelUtil.class.getName() + "] excel导出失败,", e);
throw new KmmpException("excel导出失败!");
}
} /*
* 列头单元格样式
*/
public static HSSFCellStyle getColumnTopStyle(HSSFWorkbook workbook) { // 设置字体
HSSFFont font = workbook.createFont();
//设置字体大小
font.setFontHeightInPoints((short) 16);
//字体加粗
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
//设置字体名字
font.setFontName("Courier New");
//设置样式;
HSSFCellStyle style = workbook.createCellStyle();
//设置底边框
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
//设置底边框颜色
style.setBottomBorderColor(HSSFColor.BLACK.index);
//设置左边框
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
//设置左边框颜色
style.setLeftBorderColor(HSSFColor.BLACK.index);
//设置右边框
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
//设置右边框颜色
style.setRightBorderColor(HSSFColor.BLACK.index);
//设置顶边框
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
//设置顶边框颜色
style.setTopBorderColor(HSSFColor.BLACK.index);
//在样式用应用设置的字体
style.setFont(font);
//设置自动换行
style.setWrapText(false);
//设置水平对齐的样式为居中对齐
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//设置垂直对齐的样式为居中对齐
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); return style; } /*
* 列数据信息单元格样式
*/
public static HSSFCellStyle getStyle(HSSFWorkbook workbook) {
// 设置字体
HSSFFont font = workbook.createFont();
//设置字体大小
//font.setFontHeightInPoints((short)10);
//字体加粗
//font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
//设置字体名字
font.setFontName("Courier New");
//设置样式
HSSFCellStyle style = workbook.createCellStyle();
//设置底边框
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
//设置底边框颜色
style.setBottomBorderColor(HSSFColor.BLACK.index);
//设置左边框
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
//设置左边框颜色
style.setLeftBorderColor(HSSFColor.BLACK.index);
//设置右边框
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
//设置右边框颜色
style.setRightBorderColor(HSSFColor.BLACK.index);
//设置顶边框
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
//设置顶边框颜色
style.setTopBorderColor(HSSFColor.BLACK.index);
//在样式用应用设置的字体
style.setFont(font);
//设置自动换行
style.setWrapText(false);
//设置水平对齐的样式为居中对齐
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
//设置垂直对齐的样式为居中对齐
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); return style; }
}
导出伪代码：
//HttpServletResponse设置
String fileName = simpleDateFormat.format(new Date()) + ".xls";
response.setContentType("application/octet-stream");
response.setHeader("Content-Disposition", "attachment;filename=" +
new String(fileName.getBytes(), "utf-8"));
//列数
String[] columnNames = new String[]{"成交时间","订单编号","订单状态","物流方式","订单商品总金额","订单运费","订单总金额",
"卖家微信昵称","收货人","联系电话","收货地址","物流公司","物流单号","商品名称","商品编码","SKU信息","商品单价","购买数量"};
//封装数据
...dataList
//调用
ExcelUtil.exportExcel(fileName, columnNames, dataList, response.getOutputStream());
导入功能主要是将excel的数据解析出来，之后做自己的业务处理，即ExcelUtil.importExcelData方法
参照：
https://www.cnblogs.com/coprince/p/5757714.html