1 工具类：
2 import com.github.pagehelper.util.StringUtil;
6 import org.apache.poi.hssf.usermodel.HSSFWorkbook;
7 import org.apache.poi.ss.usermodel.*;
8 import org.apache.poi.ss.util.CellRangeAddress;
9 import org.apache.poi.xssf.usermodel.*;
10 import org.springframework.util.StringUtils;
11 import org.springframework.web.multipart.MultipartFile;
12
13 import javax.servlet.http.HttpServletResponse;
14 import java.io.IOException;
15 import java.io.InputStream;
16 import java.io.OutputStream;
17 import java.util.Date;
18 import java.util.List;
19
20
21 public class ExcelUtil {
22
23 private ExcelUtil() { throw new IllegalStateException("Utility class"); }
24
25 public static String getPostfix(String path) {
26 String pathContains = ".";
27 if (StringUtils.isEmpty(path) || !path.contains(pathContains)) {
28 return null;
29 }
30 return path.substring(path.lastIndexOf(pathContains) + 1, path.length()).trim();
31 }
32
33 /**
34 * 解析Excel文件
35 *
36 * @return
37 */
38 public static Workbook analysisExcelFile(MultipartFile file) throws BusinessException {
39 if (file == null || file.getSize() == 0) {
40 throw new BusinessException("不能上传空文件");
41 }
42 //得到文件名称
43 String fileName = file.getOriginalFilename();
44 //获取文件的拓展名
45 String postfix = ExcelUtil.getPostfix(fileName);
46
47 Workbook workbook;
48 try {
49 InputStream is = file.getInputStream();
50 String fileType = "xlsx";
51 if (fileType.equals(postfix)) {
52 workbook = new XSSFWorkbook(is);
53 } else {
54 workbook = new HSSFWorkbook(is);
55 }
56 } catch (IOException e) {
57 throw new BusinessException("文件解析失败");
58 }
59 if (workbook == null || workbook.getSheetAt(0) == null) {
60 throw new BusinessException("不能上传空文件");
61 }
62 return workbook;
63 }
64
65 /**
66 * 将Cell定义为文本类型，取值
67 *
68 * @param cell Cell
69 * @return 字符串格式的值
70 */
71 public static String getCellStringValue(Cell cell) {
72 if (cell == null) {
73 return "";
74 }
75 cell.setCellType(CellType.STRING);
76 return cell.getStringCellValue();
77 }
78
79 /**
80 * 判断上传文件表头是否正确
81 *
82 * @param titleRow 标题行
83 * @param titleValues 模板标题
84 * @return true/false
85 */
86 public static boolean validExcelTitle(Row titleRow, String[] titleValues) {
87 if (titleRow == null) {
88 // 模板不正确
89 return false;
90 }
91 //判断上传文件的标题行是否符合
92 for (int i = 0; i < titleValues.length; i++) {
93 String titleValue = titleValues[i];
94 Cell cell = titleRow.getCell(i);
95 if (cell == null) {
96 return false;
97 }
98 String cellValue = cell.getStringCellValue();
99 if (!titleValue.equals(cellValue)) {
100 return false;
101 }
102 }
103
104 return true;
105 }
106
107 /**
108 * 验证关键数据不为空
109 *
110 * @param rowItem 行号
111 * @param valueList 数据
112 * @param item 验证字段序号
113 */
114 public static String validKeyValue(int rowItem, List<String> valueList, String[] titleNames, int[] item) {
115 boolean isEmpty = true;
116 Integer firstEmptyItem = null;
117 for (int i : item) {
118 if (StringUtil.isEmpty(valueList.get(i))) {
119 if (firstEmptyItem == null) {
120 firstEmptyItem = i;
121 }
122 if (!isEmpty) {
123 break;
124 }
125 } else {
126 isEmpty = false;
127 }
128 }
129
130 if (isEmpty) {
131 return "第" + rowItem + "行为空数据";
132 } else if (firstEmptyItem != null) {
133 return "第" + rowItem + "行的" + titleNames[firstEmptyItem] + "为空";
134 }
135
136 return null;
137 }
138
139 /**
140 * 生成Excel2007
141 *
142 * @param title 报表名称
143 * @param headers 标题行名称
144 * @return XSSFWorkbook
145 */
146 private static XSSFWorkbook exportExcel2007(String sheetName, String title, String[] headers) {
147 // 声明一个工作薄
148 XSSFWorkbook workbook = new XSSFWorkbook();
149 // 生成一个表格
150 XSSFSheet sheet;
151 if (StringUtils.isEmpty(sheetName)) {
152 sheet = workbook.createSheet();
153 } else {
154 sheet = workbook.createSheet(sheetName);
155 }
156
157 // 创建标题样式1
158 XSSFCellStyle headerStyle = workbook.createCellStyle();
159 XSSFFont headerFont = workbook.createFont();
160 headerFont.setBold(true);
161 headerFont.setFontHeightInPoints((short) 20);
162 headerStyle.setFont(headerFont);
163 headerStyle.setAlignment(HorizontalAlignment.CENTER);
164 // 创建标题样式1
165 XSSFCellStyle titleStyle = workbook.createCellStyle();
166 XSSFFont titleFont = workbook.createFont();
167 titleFont.setBold(true);
168 titleFont.setFontHeightInPoints((short) 10);
169 titleStyle.setFont(titleFont);
170 titleStyle.setAlignment(HorizontalAlignment.CENTER);
171 // 产生表格标题行
172 XSSFRow row = sheet.createRow(0);
173 //报表名称
174 sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, headers.length - 1));
175 XSSFCell cellTitle = row.createCell(0);
176 cellTitle.setCellValue(title);
177 cellTitle.setCellStyle(headerStyle);
178
179 XSSFRow rowHeader = sheet.createRow(1);
180 XSSFCell cellHeader;
181 for (int i = 0; i < headers.length; i++) {
182 cellHeader = rowHeader.createCell(i);
183 cellHeader.setCellValue(new XSSFRichTextString(headers[i]));
184 cellHeader.setCellStyle(titleStyle);
185 }
186
187 return workbook;
188 }
189
190
191
192 /**
193 * 生成Excel2007
194 *
195 * @param set Excel生成参数
196 * @param response 服务器响应
197 * @return
198 */
199 public static void exportExcel2007(ExcelSet set, HttpServletResponse response) {
200 // 声明一个工作薄
201 XSSFWorkbook workbook = exportExcel2007(set.getSheetName(), set.getReportName(), set.getTitleName());
202
203 List<String[]> values = set.getValues();
204 if (values != null && !values.isEmpty()) {
205 // 生成一个表格
206 XSSFSheet sheet = workbook.getSheetAt(0);
207 for (int i = 0; i < values.size(); i++) {
208 XSSFRow valueRow = sheet.createRow(i + 2);
209 String[] objs = values.get(i);
210 for (int j = 0; j < objs.length; j++) {
211 Cell cell = valueRow.createCell(j);
212 cell.setCellValue(objs[j]);
213 }
214 }
215 }
216
217 setResponseHeader(response, set.getFileName());
218 try {
219 OutputStream os = response.getOutputStream();
220 workbook.write(os);
221 os.flush();
222 os.close();
223 } catch (IOException e) {
224 e.printStackTrace();
225 }
226 }
227
228 /**
229 * 发送响应流方法
230 *
231 * @param response 服务器响应
232 * @param fileName 下载文件的文件名
233 */
234 private static void setResponseHeader(HttpServletResponse response, String fileName) {
235 try {
236 response.reset();
237 String charsetName = "UTF-8";
238 fileName = new String(fileName.getBytes(), charsetName);
239 //通知客服文件的MIME类型
240 response.setContentType("application/vnd.ms-excel;charset=UTF-8");
241 response.setHeader("Content-disposition", "attachment;filename=" + fileName);
242 response.addHeader("Transfer-Encoding","chunked");
243
244 } catch (Exception ex) {
245 ex.printStackTrace();
246 }
247 }
248
249 /**
250 * 生成导出Excel文件名称
251 * @param did 经销商编码
252 * @return String
253 */
254 public static String getExcelFileName(String did){
255 String nowStr = DateUtil.formartDate(new Date(),"yyyyMMddHHmmss");
256 return did+"_"+nowStr+".xlsx";
257 }
258 }
259
260
261 Service：
262 @Transactional(rollbackFor = Exception.class)
263 public List<String> uploadSupplierExcelFile(String pDid,String pUserId,MultipartFile pExcelFile) throws BusinessException {
264 // 解析文件
265 Workbook workbook = ExcelUtil.analysisExcelFile(pExcelFile);
266 // 获取文件的第一个sheet页
267 Sheet sheet = workbook.getSheetAt(0);
268 //验证模板是否正确
269 //模板表头标题内容
270 String [] titleValues = ExcelTitle.EXCEL_SUPPLIER_TITLE;
271 Row firstRow = sheet.getRow(0);
272 if(!ExcelUtil.validExcelTitle(firstRow,titleValues)){
273 throw new BusinessException("上传模板错误");
274 }
275
276 //错误信息保存集合
277 List<String> templateErrorList = new ArrayList<>();
278 //保存<供应商编号,DID>，用于检测，是否存在相同供应商
279 Map<String,String> cMap = new HashMap<>();
280 //要保存的供应商数据
281 List<BaseSupplier> baseSupplierList = new ArrayList<>();
282 //得到文件中最大行号
283 int lasrRowNum = sheet.getLastRowNum();
284 //最大读取列数
285 int maxColumn = 8;
286 // 开始数据处理
287 for(int rowNum = 1; rowNum <= lasrRowNum; rowNum++){
288 Row row = sheet.getRow(rowNum);
289 if(row == null){
290 continue;
291 }
292 int nowRowNum = rowNum + 1;
293 //得到行数据
294 List<String> valueList = new ArrayList<>(maxColumn);
295 for(int i=0;i<maxColumn;i++){
296 String value = ExcelUtil.getCellStringValue(row.getCell(i));
297 valueList.add(value);
298 }
299 //验证字段的序号
300 int [] validEnptyItem = {0,1,2,3,4,5,6,7};
301 String errorMsg = ExcelUtil.validKeyValue(nowRowNum,valueList,ExcelTitle.EXCEL_SUPPLIER_TITLE,validEnptyItem);
302 if(!StringUtils.isEmpty(errorMsg)){
303 templateErrorList.add(errorMsg);
304 }else{
305 BaseSupplier baseSupplier = new BaseSupplier();
306 baseSupplier.setSupplierName(valueList.get(0));
307 baseSupplier.setSubDept(valueList.get(1));
308 baseSupplier.setSubPerson(valueList.get(2));
309 baseSupplier.setSupplierType(valueList.get(3));
310 baseSupplier.setRate(valueList.get(4));
311 baseSupplier.setLinkman(valueList.get(5));
312 baseSupplier.setPhone(valueList.get(6));
313 baseSupplier.setAddr(valueList.get(7));
314 //判断本条数据是否存在空数据
315 if(validExcelData(nowRowNum,baseSupplier,templateErrorList,cMap)){
316 String newRowId = codeGenerationService.generateCode(CodeCom.CODE_SUPPLIER_PRE);
317 baseSupplier.setRowId(newRowId);
318 baseSupplier.setUserid(pUserId);
319 //将数据加入到供应商列表中
320 baseSupplierList.add(baseSupplier);
321 }
322 }
323
324 }
325
326 if(baseSupplierList.isEmpty()){
327 templateErrorList.add("文件中无有效数据");
328 }
329 //保存导入数据
330 if(templateErrorList.isEmpty()){
331 saveBaseSuppilerrBatch(baseSupplierList);
332 }
333
334
335 return templateErrorList;
336 }
337
338 controller：
339
340 /**
341 * 批量保存供应商信息
342 * @param pBaseSuppliers 供应商信息
343 * @return int
344 */
345 @Transactional(rollbackFor = Exception.class)
346 public int saveBaseSuppilerrBatch(List<BaseSupplier> pBaseSuppliers){
347 //分批次保存信息供应商信息
348 //每批保存200条数据
349 Integer batchCount = 200;
350 int count = 0;
351 for (int i = 0; i < Math.ceil(pBaseSuppliers.size()/batchCount.doubleValue()); i++) {
352 List<BaseSupplier> zBaseCustomers = pBaseSuppliers.subList(i * batchCount,
353 (i + 1) * batchCount > pBaseSuppliers.size() ? pBaseSuppliers.size() : (i + 1) * batchCount);
354 int zCount = baseSupplierMapper.insertBatch(zBaseCustomers);
355 count += zCount;
356 }
357 return count;
358 }
359
360 private boolean validExcelData(int pRowNum,BaseSupplier pSupplier,
361 List<String> pTemplateErrorList,
362 Map<String,String> cMap){
363
364 //判断是否已有重复供应商数据
365 String cNameHave = cMap.get(pSupplier.getSupplierName());
366 if(cNameHave==null){
367 cMap.put(pSupplier.getSupplierName(),pSupplier.getSupplierName());
368 }else{
369 pTemplateErrorList.add("第"+pRowNum+"行的供应商名称重复!");
370 return false;
371 }
372 //查询已存在的数据，判断供应商名称是否已经存在
373 int count = countByAlikeName(null,pSupplier.getSupplierName());
374 if(count>0){
375 pTemplateErrorList.add("第"+pRowNum+"行的供应商名称已存在!");
376 return false;
377 }
378 return true;
379 }
380 /**
381 * 根据名称查询符合条件的数据数量
382 * @param pRowId 查询时不过滤的供应商编码
383 * @param pName 供应商名称
384 * @return 执行行数
385 */
386 public int countByAlikeName(String pRowId,String pName){
387 BaseSupplierExample example = new BaseSupplierExample();
388 BaseSupplierExample.Criteria criteria = example.createCriteria();
389 criteria.andSupplierNameLike(pName);
390 if(pRowId!=null){
391 criteria.andRowIdNotEqualTo(pRowId);
392 }
393 return baseSupplierMapper.countByExample(example);
394 }
395
396 保存方法：
397 /**
398 * 批量导入供应商数据
399 * @param pExcelFile
400 * @return
401 */
402 @ApiOperation(value = "批量导入供应商资料", notes = "批量导入供应商资料")
403 @PostMapping(value = "/uploadSupplierExcelFile")
404 public JsonResult uploadCustomerExcelFile(
405 @ApiParam(name = "pExcelFile", value = "导入文件", required = true)
406 @RequestParam("pExcelFile") MultipartFile pExcelFile) {
407 UserInfo info = this.getUserInfo();
408 String pDid = info.getDid();
409 String pUserId = info.getUserId();
410 logger.info("批量导入供应商资料接口请求参数,pDid:{},pUserId:{}", pDid, pUserId);
411 try {
412 List<String> errorMsgList = baseSupplierService.uploadSupplierExcelFile(pDid, pUserId, pExcelFile);
413 if (errorMsgList != null && !errorMsgList.isEmpty()) {
414 return JsonResult.get(HttpReturnEnums.ParaError, errorMsgList);
415 }
416 } catch (BusinessException e) {
417 return JsonResult.get(HttpReturnEnums.ParaError, null, e.getMessage());
418 }
419 return JsonResult.get(HttpReturnEnums.Success);
420 }
421
422 mapper:
423 int insertBatch(@Param("supplierList") List<BaseSupplier> record);
424
425 xml sql:
426 <insert id="insertBatch" parameterType="com.shinho.dc3.master.models.po.BaseSupplier" >
427 insert into base_supplier (
428 row_id, supplier_name, sub_dept,
429 sub_person, supplier_type, rate,
430 linkman, phone, addr,userid,did)
431 values
432 <foreach collection="supplierList" index="index" item="supplierList" separator=",">
433 (
434 #{supplierList.rowId,jdbcType=VARCHAR}, #{supplierList.supplierName,jdbcType=VARCHAR}, #{supplierList.subDept,jdbcType=VARCHAR},
435 #{supplierList.subPerson,jdbcType=VARCHAR}, #{supplierList.supplierType,jdbcType=VARCHAR}, #{supplierList.rate,jdbcType=VARCHAR},
436 #{supplierList.linkman,jdbcType=VARCHAR}, #{supplierList.phone,jdbcType=VARCHAR}, #{supplierList.addr,jdbcType=VARCHAR},
437 #{supplierList.userid,jdbcType=VARCHAR}, #{supplierList.did,jdbcType=VARCHAR}
438 )
439 </foreach>
440 </insert>
441 public class ExcelTitle {
442 /**
443 * EXCEL导入表头-供应商档案
444 */
445 public static final String [] EXCEL_SUPPLIER_TITLE = {"供应商名称","分管部门","分管人员","供应商类型","税率","联系人","联系电话","供应商地址"};
446 }
浙公网安备 33010602011771号