poi实现excel的导入导出功能
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
