Excel的导出模板和导入数据库

桃扇骨 2022-04-18 01:13 386阅读 0赞

## Excel的导出模板和导入数据库 ##

\*\*  
自己记录自己的学习足迹。

更新：数据量太大的话导入失败，可以将数据分开进行导入插入。将存放所有数据的集合分开，比如每2000条数据放入一个集合中，将存放两千条数据的集合在存放入一个List<List> 的集合，循环遍历插入该集合.（啧啧，这个复制了这点东西不会报错吧！！！）
    		本次使用的poi
    		首先导出Excel模板（只是导出一个模板，有标题和列名）：
    			
    			首先需要获得你要导出的数据，然后创建 HSSFWorkbook 对象（就是相当于一个Excel文件），将数据按照行列来进行放入数据，创建 OutputStream 流，调用workBook.write(out) 将文件输出，out.flush()清空缓冲区的数据流（就是水管中的水弄出来），out.close()最后关闭流。完成
    			
    			其实导出Excel就是往相应的Excel表格放入数据，每个小方格都有一个编号将对应的数据放入对应的编号中。
    			
    			每一个小表格都有对应的编号。
    			必用的一些：
    			HSSFWorkbook workBook = new HSSFWorkbook();			创建Excel
    			HSSFSheet sheet = workBook.createSheet("物流订单"); 		创建Excel中的Sheet并取一个名字
    			HSSFCellStyle style = workBook.createCellStyle(); 				设置样式的
    			sheet.addMergedRegion(new CellRangeAddress(0, 0, 0,excel.length-1)); 合并单元格
    			（起始行号，终止行号， 起始列号，终止列号）	（不要导错包否则会有横线）
    			HSSFRow row = sheet.createRow(0);															创建行（下标为0，第1行的字段）
    			HSSFCell cell = row.createCell(0).setCellValue(当前字段的数据);			创建列（下标为0，第1列的字段）
    			response.setHeader("Content-Disposition", 
    								"attachment; filename="+ new String("OrderPSD.xls".getBytes("utf-8"), "iso-8859-1"));
    			下载文件	
    			Content-Disposition 的作用：当Content-Type 的类型为要下载的类型时 , 这个信息头会告诉浏览器这个文件的名字和类型
    			response.setContentType("application/vnd.ms-excel;charset=UTF-8"); 
    			
    			下面记录代码

public void export(HttpServletResponse response) throws IOException {
    		String[] excel = Constants.ORDER_EXCEL.split(",");
    		HSSFWorkbook workBook = new HSSFWorkbook();
    		HSSFSheet sheet = workBook.createSheet("物流订单");
    		HSSFCellStyle style = workBook.createCellStyle();
    		style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
    		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    		sheet.addMergedRegion(new CellRangeAddress(0, 0, 0,excel.length-1));
    		HSSFRow row1 = sheet.createRow(0);
    		HSSFCell cell = row1.createCell(0);
    		cell.setCellValue("请严格按照模板填写，并选择相应的分类导入！");
    		cell.setCellStyle(style);
    		HSSFRow row = sheet.createRow(1);
    		OutputStream out = null;
    		for (int i = 0; i < excel.length; i++) {
    			row.createCell(i).setCellValue(excel[i]);
    		}
    		try {
    			out = response.getOutputStream();
    			response.reset();
    			response.setHeader("Content-Disposition", "attachment; filename="+ new String("OrderPSD.xls".getBytes("utf-8"), "iso-8859-1"));
    			response.setContentType("application/vnd.ms-excel;charset=UTF-8");
    			workBook.write(out);
    		} catch (Exception e) {
    			e.printStackTrace();
    		}finally {	
    			out.flush();
    			out.close();
    		}
    		
    	}

下面是导入Excel（将本地Excel文件导入数据库）：
    			***需要注意的是，Excel 都有对应的属性类型，在向对应的对象属性中放入参数时如果对应不上会出现错误***
    			就是比导出模板多了一个，需要MultipartFile 来接收Excel文件
    			首先创建 HSSFWorkbook workBook = new HSSFWorkbook(file.getInputStream());   创建HSSFWorkbook对象参数为放入流中的Excel
    			Sheet sheet = workBook.getSheetAt(0);	获得第1个Sheet
    			for (int i = 2; i <= sheet.getLastRowNum(); i++) {		//sheet.getLastRowNum()   遍历获取最后一行
    						Row row = sheet.getRow(i);				//遍历出的当前行数据
    						delivery = new Delivery();
    						if(row.getCell(0) != null)
    							delivery.setOderNumber(ExportExcelUtils.getCellValue(row.getCell(0)));	//将当前行第1列的参数放入对象
    			}
    			最后直接 sql插入
    			
    			上代码：
    			首先Controller   这里请求方式使用的@RequestMapping

@RequestMapping("/toLeadExcel")
    	@ResponseBody
    	public JsonResult toLeadExcel(HttpServletResponse response,@RequestParam(value = "file", required = false)MultipartFile file,
    					@RequestParam("categoryId") Integer categoryId) throws CcException, FileNotFoundException {
    		log.debug("OrderManageController toLeadExcel");
    		JsonResult js = new JsonResult();
    		js.setSuccess(false);
    		DeliveryDTO errorOrder = orderManageService.toLeadExcel(response,file,categoryId);
    		if(errorOrder.getListDe() == null || errorOrder.getListDe().size() == 0) {
    			log.debug("订单导入成功");
    			js.setMessage("订单成功导入:"+errorOrder.getLeadIn()+"条");
    			js.setData(errorOrder);
    			js.setSuccess(true);
    		}else {
    			log.debug("以下订单导入失败"+errorOrder.getListDe());
    			js.setMessage("订单导入失败:"+errorOrder.getListDe().size()+"条");
    			js.setData(errorOrder);
    		}
    		return js;
    	}

下面service

@Override
    	@Transactional(rollbackFor={CcException.class})
    	public DeliveryDTO toLeadExcel(HttpServletResponse response,
    							MultipartFile file, Integer categoryId) throws CcException {
    		if(file.isEmpty())
    			throw new CcException("文件不存在!");
    		if(categoryId == null)
    			throw new CcException("请选择物流类别!");
    		String[] files = file.getOriginalFilename().split("\\.");
    		if(!("xls").equals(files[1]) && !("xlsx").equals(files[1]))
    			throw new CcException("请选择正确的Excel文件!");
    		DeliveryDTO deliveryDTO = new DeliveryDTO();
    		List<Delivery> orderList = new LinkedList<Delivery>();
    		SimpleDateFormat simpl = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    		Integer count = 0;
    		Map<String, String>shipNetMap = getCodeAndName();//将code和名字放入一个map中
    		List<String> orderNumber = orderManageDao.findOrderNumber();//订单编号
    		List<Delivery> errorOrder = new ArrayList<>();
    		try {
    			HSSFWorkbook workBook = new HSSFWorkbook(file.getInputStream());
    			Sheet sheet = workBook.getSheetAt(0);
    				for (int i = 2; i <= sheet.getLastRowNum(); i++) {//遍历获取最后一行
    						Row row = sheet.getRow(i);
    					if(shipNetMap.containsKey(ExportExcelUtils.getCellValue(row.getCell(9))) && 
    												!orderNumber.contains(ExportExcelUtils.getCellValue(row.getCell(0)))) {
    						String cause = "";
    						orderList.add(this.addShipNet(row,simpl,categoryId ,cause));
    						count += 1;
    					}else if(!shipNetMap.containsKey(ExportExcelUtils.getCellValue(row.getCell(9)))){
    						String cause = "该订单不存在对应的物流编码！";
    						errorOrder.add(this.addShipNet(row, simpl, categoryId, cause));
    					}else if(orderNumber.contains(ExportExcelUtils.getCellValue(row.getCell(0)))) {
    						String cause = "该订单编号已存在！";
    						errorOrder.add(this.addShipNet(row, simpl, categoryId ,cause));
    					}else
    						throw new CcException("请重新查看当前Excel文件信息！");
    				}
    				if(deliveryDTO.getLeadIn() == null)
    					deliveryDTO.setLeadIn(0);
    				deliveryDTO.setListDe(errorOrder);
    			if(orderList.size()>0&&orderList.size()>2000) {
    				deliveryDTO.setLeadIn(count);
    				int limit =this.countStep((orderList.size()));
    				List<List<Delivery>> mglist = new ArrayList<>();
    				 //这是网上搜索的JDK1.8将当前集合切分每2000条数据为一个集合，将分好的集合放入mglist 集合中
    				  Stream.iterate(0, n -> n + 1).limit(limit).forEach(i -> {
    			          mglist.add(orderList.stream().skip(i * 2000).limit(2000).collect(Collectors.toList()));
    			      });
    				  for (List<Delivery> list : mglist) {//遍历mglist 集合循环插入
    					  orderManageDao.save(list);
    				}
    			}else if(orderList.size()>0) {
    				deliveryDTO.setLeadIn(count);
    				 orderManageDao.save(orderList);
    			}else
    				return deliveryDTO;
    		} catch (Exception e) {
    			e.printStackTrace();
    			throw new CcException("导入失败！");
    		}
    		return deliveryDTO;
    	}
    	
    private  Integer countStep(Integer size) {
    		return (size + 2000- 1)/2000;
    	}
    
    	private Delivery addShipNet(Row row, SimpleDateFormat simpl, Integer categoryId , String cause) throws ParseException {
    		Delivery delivery = new Delivery();
    		if(row.getCell(0) != null)
    			delivery.setOderNumber(ExportExcelUtils.getCellValue(row.getCell(0)));
    		if(row.getCell(1) != null)
    			delivery.setDeliveryTime(simpl.parse(ExportExcelUtils.getCellValue(row.getCell(1))));
    		if(row.getCell(2) != null)
    			delivery.setDeliveryCity(ExportExcelUtils.getCellValue(row.getCell(2)));
    		if(row.getCell(3) != null)
    			delivery.setArriveCity(ExportExcelUtils.getCellValue(row.getCell(3)));
    		if(row.getCell(4) != null)
    			delivery.setMaterielDetail(ExportExcelUtils.getCellValue(row.getCell(4)));
    		if(row.getCell(5) != null)
    			delivery.setSendAddress(ExportExcelUtils.getCellValue(row.getCell(5)));
    		if(row.getCell(6) != null)
    			delivery.setArrivePersonname(ExportExcelUtils.getCellValue(row.getCell(6)));
    		if(row.getCell(7) != null)
    			delivery.setArrivePersontel(ExportExcelUtils.getCellValue(row.getCell(7)));
    		if(row.getCell(8) != null)
    			delivery.setShippingCode(ExportExcelUtils.getCellValue(row.getCell(8)));
    		if(row.getCell(9) != null)
    			delivery.setLogcompanyName(ExportExcelUtils.getCellValue(row.getCell(9)));
    		if(Integer.valueOf(categoryId) != null)
    			delivery.setCategoryId(Integer.valueOf(categoryId));
    		delivery.setFailingMess(cause);
    		delivery.setLeadIn(0);
    		return delivery;
    	}
    	/**
    	 * 查询物流管理的Code和名字
    	 * @return 
    	 */
    	private Map<String, String> getCodeAndName() {
    		List<ShipNetRelation> shipNetRelation = orderManageDao.findDeliveryCodeByCode();
    		Map<String, String> map = new HashMap<>();
    		for (ShipNetRelation shipNetRelation2 : shipNetRelation) {
    			map.put(shipNetRelation2.getLogcompanyName(), shipNetRelation2.getLogcompanyCode());
    		}
    		return map;
    	}

下面贴一个工具类，这个工具类是网上一个前辈写的，但是找不到链接了，如果看到请与我联系，我会把代码换成您的链接

package net.ninehkj.logistics.util;
    
    import java.awt.Color;
    import java.io.OutputStream;
    import java.net.URLEncoder;
    import java.text.SimpleDateFormat;
    import java.util.List;
    
    import javax.servlet.http.HttpServletResponse;
    
    import org.apache.poi.hssf.usermodel.HSSFFont;
    import org.apache.poi.ss.usermodel.BorderStyle;
    import org.apache.poi.ss.usermodel.Cell;
    import org.apache.poi.ss.usermodel.DataFormatter;
    import org.apache.poi.ss.usermodel.Font;
    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.xssf.usermodel.XSSFCellStyle;
    import org.apache.poi.xssf.usermodel.XSSFColor;
    import org.apache.poi.xssf.usermodel.XSSFSheet;
    import org.apache.poi.xssf.usermodel.XSSFWorkbook;
    import org.apache.poi.xssf.usermodel.extensions.XSSFCellBorder.BorderSide;
    
    /**
     * Created by zouLu on 2017-12-14.
     */
    public class ExportExcelUtils {
        public static void exportExcel(HttpServletResponse response, String fileName, ExcelData data) throws Exception {
            // 告诉浏览器用什么软件可以打开此文件
            response.setHeader("content-Type", "application/vnd.ms-excel");
            // 下载文件的默认名称
            response.setHeader("Content-Disposition", "attachment;filename="+URLEncoder.encode(fileName, "utf-8"));
            exportExcel(data, response.getOutputStream());
        }
    
        public static void exportExcel(ExcelData data, OutputStream out) throws Exception {
    
            XSSFWorkbook wb = new XSSFWorkbook();
            try {
                String sheetName = data.getName();
                if (null == sheetName) {
                    sheetName = "Sheet1";
                }
                XSSFSheet sheet = wb.createSheet(sheetName);
                writeExcel(wb, sheet, data);
    
                wb.write(out);
            } finally {
            	out.close();
            }
        }
    
        private static void writeExcel(XSSFWorkbook wb, Sheet sheet, ExcelData data) {
    
            int rowIndex = 0;
    
            rowIndex = writeTitlesToExcel(wb, sheet, data.getTitles());
            writeRowsToExcel(wb, sheet, data.getRows(), rowIndex);
            autoSizeColumns(sheet, data.getTitles().size() + 1);
    
        }
    
        private static int writeTitlesToExcel(XSSFWorkbook wb, Sheet sheet, List<String> titles) {
            int rowIndex = 0;
            int colIndex = 0;
    
            Font titleFont = wb.createFont();
            titleFont.setFontName("simsun");
            titleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
            // titleFont.setFontHeightInPoints((short) 14);
            titleFont.setColor(IndexedColors.BLACK.index);
    
            XSSFCellStyle titleStyle = wb.createCellStyle();
            titleStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
            titleStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
            titleStyle.setFillForegroundColor(new XSSFColor(new Color(182, 184, 192)));
            titleStyle.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
            titleStyle.setFont(titleFont);
            setBorder(titleStyle, BorderStyle.THIN, new XSSFColor(new Color(0, 0, 0)));
    
            Row titleRow = sheet.createRow(rowIndex);
            // titleRow.setHeightInPoints(25);
            colIndex = 0;
    
            for (String field : titles) {
                Cell cell = titleRow.createCell(colIndex);
                cell.setCellValue(field);
                cell.setCellStyle(titleStyle);
                colIndex++;
            }
    
            rowIndex++;
            return rowIndex;
        }
    
        private static int writeRowsToExcel(XSSFWorkbook wb, Sheet sheet, List<List<Object>> rows, int rowIndex) {
            int colIndex = 0;
    
            Font dataFont = wb.createFont();
            dataFont.setFontName("simsun");
            // dataFont.setFontHeightInPoints((short) 14);
            dataFont.setColor(IndexedColors.BLACK.index);
    
            XSSFCellStyle dataStyle = wb.createCellStyle();
            dataStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
            dataStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
            dataStyle.setFont(dataFont);
            setBorder(dataStyle, BorderStyle.THIN, new XSSFColor(new Color(0, 0, 0)));
    
            for (List<Object> rowData : rows) {
                Row dataRow = sheet.createRow(rowIndex);
                // dataRow.setHeightInPoints(25);
                colIndex = 0;
    
                for (Object cellData : rowData) {
                    Cell cell = dataRow.createCell(colIndex);
                    if (cellData != null) {
                        cell.setCellValue(cellData.toString());
                    } else {
                        cell.setCellValue("");
                    }
    
                    cell.setCellStyle(dataStyle);
                    colIndex++;
                }
                rowIndex++;
            }
            return rowIndex;
        }
    
        private static void autoSizeColumns(Sheet sheet, int columnNumber) {
    
            for (int i = 0; i < columnNumber; i++) {
                int orgWidth = sheet.getColumnWidth(i);
                sheet.autoSizeColumn(i, true);
                int newWidth = (int) (sheet.getColumnWidth(i) + 100);
                if (newWidth > orgWidth) {
                    sheet.setColumnWidth(i, newWidth);
                } else {
                    sheet.setColumnWidth(i, orgWidth);
                }
            }
        }
    
        private static void setBorder(XSSFCellStyle style, BorderStyle border, XSSFColor color) {
            style.setBorderTop(border);
            style.setBorderLeft(border);
            style.setBorderRight(border);
            style.setBorderBottom(border);
            style.setBorderColor(BorderSide.TOP, color);
            style.setBorderColor(BorderSide.LEFT, color);
            style.setBorderColor(BorderSide.RIGHT, color);
            style.setBorderColor(BorderSide.BOTTOM, color);
        }
        
        public static String getCellValue(Cell cell) {
            String cellValue = "";
            // 以下是判断数据的类型
            switch (cell.getCellType()) {
                case Cell.CELL_TYPE_NUMERIC: // 数字
                    if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) {
                        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                        cellValue = sdf.format(org.apache.poi.ss.usermodel.DateUtil.getJavaDate(cell.getNumericCellValue())).toString();
                    } else {
                        DataFormatter dataFormatter = new DataFormatter();
                        cellValue = dataFormatter.formatCellValue(cell);
                    }
                    break;
                case Cell.CELL_TYPE_STRING: // 字符串
                    cellValue = cell.getStringCellValue();
                    break;
                case Cell.CELL_TYPE_BOOLEAN: // Boolean
                    cellValue = cell.getBooleanCellValue() + "";
                    break;
                case Cell.CELL_TYPE_FORMULA: // 公式
                    cellValue = cell.getCellFormula() + "";
                    break;
                case Cell.CELL_TYPE_BLANK: // 空值
                    cellValue = "";
                    break;
                case Cell.CELL_TYPE_ERROR: // 故障
                    cellValue = "非法字符";
                    break;
                default:
                    cellValue = "未知类型";
                    break;
            }
            return cellValue;
        }
    }