本文共 30249 字，大约阅读时间需要 100 分钟。
package com.taobao.service;import java.io.ByteArrayOutputStream;import java.io.File;import java.io.FileInputStream;import java.io.FileNotFoundException;import java.io.FileOutputStream;import java.io.IOException;import java.io.InputStream;import java.text.DecimalFormat;import java.text.SimpleDateFormat;import java.util.ArrayList;import java.util.Calendar;import java.util.Comparator;import java.util.Date;import java.util.HashMap;import java.util.Iterator;import java.util.List;import java.util.Map;import org.apache.log4j.Logger;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.CellStyle;import org.apache.poi.ss.usermodel.IndexedColors;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.XSSFWorkbook;import org.springframework.web.multipart.MultipartFile;import com.taobao.bean.ProductQuery;import com.taobao.bean.SupplyProduct;import com.taobao.bean.User;import com.taobao.dao.ExpressDAO;import com.taobao.dao.OrderDAO;import com.taobao.dao.ProductDAO;import com.taobao.dto.Product;import com.taobao.exception.UploadDownloadException;import com.taobao.listener.CloudPrintManager;import cn.fita.bean.Page;public class ExcelService { private static Logger log = Logger.getLogger(ExcelService.class); private static final String XLS = "xls"; private static final String XLSX = "xlsx"; private ProductService pservice = new ProductService(); private ConstantService cservice = new ConstantService(); private ExpressDAO edao = new ExpressDAO(); private ProductDAO pdao = new ProductDAO(); private OrderDAO dao = new OrderDAO(); /** * 根据文件后缀名类型获取对应的工作簿对象 * * @param inputStream * 读取文件的输入流 * @param fileType * 文件后缀名类型（xls或xlsx） * @return 包含文件数据的工作簿对象 * @throws IOException */ public static Workbook getWorkbook(InputStream inputStream, String fileType) throws IOException { Workbook workbook = null; if (fileType.equalsIgnoreCase(XLS)) { workbook = new HSSFWorkbook(inputStream); } else if (fileType.equalsIgnoreCase(XLSX)) { workbook = new XSSFWorkbook(inputStream); } return workbook; } /** * 读取Excel文件内容 * * @param fileName * 要读取的Excel文件所在路径 * @return 读取结果列表，读取失败时返回null */ public static List readExcel(String fileName) { Workbook workbook = null; FileInputStream inputStream = null; // 获取Excel后缀名 String fileType = fileName.substring(fileName.lastIndexOf(".") + 1, fileName.length()); // 获取Excel文件 File excelFile = new File(fileName); if (!excelFile.exists()) { log.info("指定的Excel文件不存在！"); return null; } return readExcel(inputStream, fileType); } public static List readExcel(InputStream inputStream,String fileType) { Workbook workbook = null; try { workbook = getWorkbook(inputStream, fileType); // 读取excel中的数据 List resultDataList = parseExcel(workbook); return resultDataList; } catch (Exception e) { log.info("解析Excel失败! 错误信息：" + e.getMessage()); return null; } finally { try { if (null != inputStream) { inputStream.close(); } } catch (Exception e) { log.info("关闭数据流出错！错误信息：" + e.getMessage()); return null; } } } /** * 解析Excel数据 * * @param workbook * Excel工作簿对象 * @return 解析结果 */ private static List parseExcel(Workbook workbook) { List resultDataList = new ArrayList<>(); // 解析sheet for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++) { Sheet sheet = workbook.getSheetAt(sheetNum); // 校验sheet是否合法 if (sheet == null) { continue; } // 获取第一行数据 int firstRowNum = sheet.getFirstRowNum(); Row firstRow = sheet.getRow(firstRowNum); if (null == firstRow) { log.info("解析Excel失败，在第一行没有读取到任何数据！"); } // 解析每一行的数据，构造数据对象 int rowStart = firstRowNum + 1; int rowEnd = sheet.getPhysicalNumberOfRows(); for (int rowNum = rowStart; rowNum < rowEnd; rowNum++) { Row row = sheet.getRow(rowNum); if (null == row) { continue; } Product resultData = convertRowToData(row); if (null == resultData) { log.info("第 " + row.getRowNum() + "行数据不合法，已忽略！"); continue; } resultDataList.add(resultData); } } return resultDataList; } /** * 将单元格内容转换为字符串 * * @param cell * @return */ private static String convertCellValueToString(Cell cell) { if (cell == null) { return null; } String returnValue = null; switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: // 数字 Double doubleValue = cell.getNumericCellValue(); // 格式化科学计数法，取一位整数 DecimalFormat df = new DecimalFormat("0"); returnValue = df.format(doubleValue); break; case Cell.CELL_TYPE_STRING: // 字符串 returnValue = cell.getStringCellValue(); break; case Cell.CELL_TYPE_BOOLEAN: // 布尔 Boolean booleanValue = cell.getBooleanCellValue(); returnValue = booleanValue.toString(); break; case Cell.CELL_TYPE_BLANK: // 空值 break; case Cell.CELL_TYPE_FORMULA: // 公式 returnValue = cell.getCellFormula(); break; case Cell.CELL_TYPE_ERROR: // 故障 break; default: break; } return returnValue; } /** * 提取每一行中需要的数据，构造成为一个结果数据对象 * * 当该行中有单元格的数据为空或不合法时，忽略该行的数据 * * @param row * 行数据 * @return 解析后的行数据对象，行数据错误时返回null */ private static Product convertRowToData(Row row) { Product product = new Product(); Cell cell; int cellNum = 0; // 获取编码 cell = row.getCell(cellNum++); String pid = convertCellValueToString(cell); if(pid==null){ return null; } product.setPid(pid); // 获取名称 cell = row.getCell(cellNum++); String name = convertCellValueToString(cell); product.setName(name); // 获取书架号 cell = row.getCell(cellNum++); String bookshelf = convertCellValueToString(cell); product.setBookshelf(bookshelf); // 获取库存 cell = row.getCell(cellNum++); String stock = convertCellValueToString(cell); if(stock==null){ return null; } product.setStock(Integer.parseInt(stock)); return product; } /** * 将数据转换成行 * * @param data * 源数据 * @param row * 行对象 * @return */ private static void convertDataToRow(Product product, Row row, CellStyle cellStyle) { int cellNum = 0; Cell cell; cell = row.createCell(cellNum++); cell.setCellStyle(cellStyle); cell.setCellValue(null == product.getPid() ? "" : product.getPid()); cell = row.createCell(cellNum++); cell.setCellStyle(cellStyle); cell.setCellValue(null == product.getName() ? "" : product.getName()); cell = row.createCell(cellNum++); cell.setCellStyle(cellStyle); cell.setCellValue(null == product.getBookshelf() ? "" : product.getBookshelf()); cell = row.createCell(cellNum++); cell.setCellStyle(cellStyle); cell.setCellValue(product.getStock()); } private static CellStyle buildCellStyle(Workbook workbook) { CellStyle style = workbook.createCellStyle(); // 对齐方式设置 style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); // 边框颜色和宽度设置 style.setBorderBottom(CellStyle.BORDER_THIN); style.setBottomBorderColor(IndexedColors.BLACK.getIndex()); // 下边框 style.setBorderLeft(CellStyle.BORDER_THIN); style.setLeftBorderColor(IndexedColors.BLACK.getIndex()); // 左边框 style.setBorderRight(CellStyle.BORDER_THIN); style.setRightBorderColor(IndexedColors.BLACK.getIndex()); // 右边框 style.setBorderTop(CellStyle.BORDER_THIN); style.setTopBorderColor(IndexedColors.BLACK.getIndex()); // 上边框 return style; } public ByteArrayOutputStream getDownloadData( List @RequestMapping("/uploadaddSeriesExcel") @ResponseBody public String getExcelData(MultipartFile file) throws IOException { System.out.println("进来了"); Map map = new HashMap<>(); map.put("data", AnalysisExcelData.getExcelData(file)); System.out.println(AnalysisExcelData.getExcelData(file)); System.out.println(map); return json(map); }
