package com.goafanti.common.utils; import java.io.File; import java.io.FileInputStream; import java.io.IOException; import java.io.InputStream; import java.math.BigDecimal; import java.text.DecimalFormat; import java.text.SimpleDateFormat; import java.util.Date; import java.util.HashSet; import java.util.Iterator; import java.util.List; import java.util.Set; import java.util.regex.Pattern; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.springframework.web.multipart.MultipartFile; import com.goafanti.common.constant.AFTConstants; import com.goafanti.customer.bo.CustomerExcelBo; import com.goafanti.order.bo.OrderReportBo; import org.apache.commons.lang3.StringUtils; import org.apache.poi.hssf.usermodel.*; import org.apache.poi.hssf.util.HSSFColor; import org.apache.poi.ss.formula.functions.T; import org.apache.poi.ss.usermodel.*; import org.apache.poi.xssf.usermodel.*; import javax.servlet.http.HttpServletResponse; import java.awt.Color; import java.io.OutputStream; import java.lang.reflect.Field; import java.lang.reflect.InvocationTargetException; import java.lang.reflect.Method; import java.net.URLEncoder; import java.util.*; import java.util.regex.Matcher; public class ExcelUtils { private Set vacantRows = new HashSet(); //数据不完整的 private final String DEFAULT_MOBILE = "0000-0000000"; DecimalFormat df = new DecimalFormat("0"); @SuppressWarnings ("resource") public Set parseExcel(String excelPath) throws IOException{ InputStream excelFile = new FileInputStream(new File(excelPath)); XSSFWorkbook wb = new XSSFWorkbook(excelFile); Set result = new HashSet(); XSSFSheet sheet = wb.getSheetAt(0); Iterator rowIterator = sheet.iterator(); Row currentRow = sheet.getRow(1); //表头 Cell configCell = currentRow.getCell(1); //配置 int totalNumber = 2; if(configCell.getCellTypeEnum() == CellType.NUMERIC){ totalNumber = Integer.valueOf(df.format(configCell.getNumericCellValue())); }else if(configCell.getCellTypeEnum() == CellType.STRING){ totalNumber = Integer.valueOf(configCell.getStringCellValue()); } int rowNumber = 2; rowIterator.next(); rowIterator.next(); while(rowIterator.hasNext() && rowNumber < totalNumber){ rowNumber++; currentRow = rowIterator.next(); Iterator cellIterator = currentRow.iterator(); CustomerExcelBo bo = new CustomerExcelBo(); bo.setCustomerType(checkCustomerType(cellIterator.next().getStringCellValue(), rowNumber)); bo.setIdentifyName(checkOther(cellIterator.next().getStringCellValue(),rowNumber,64)); bo.setFollowSituation(checkFollowSituation(cellIterator.next().getStringCellValue(), rowNumber)); bo.setCustomerStatus(checkCustomerStatus(cellIterator.next().getStringCellValue(), rowNumber)); // bo.setBusinessGlossoryId(checkCustomerIntention(cellIterator.next().getStringCellValue(), rowNumber)); bo.setContacts(checkOther(cellIterator.next().getStringCellValue(),rowNumber,32)); Cell phoneCell = cellIterator.next(); if(phoneCell.getCellTypeEnum() == CellType.NUMERIC) { bo.setMobile(checkMobile(df.format(phoneCell.getNumericCellValue()), rowNumber)); }else if(phoneCell.getCellTypeEnum() == CellType.STRING){ bo.setMobile(checkMobile(phoneCell.getStringCellValue(), rowNumber)); } bo.setRowNumber(rowNumber); result.add(bo); } return result; } private String checkCustomerType(String customerType,int rowNumber){ if(StringUtils.isEmpty(customerType)) { vacantRows.add(rowNumber); return ""; } if(customerType.equals("个人客户")) return "0"; if(customerType.equals("单位客户")) return "1"; vacantRows.add(rowNumber); return ""; } private String checkFollowSituation(String followSituation,int rowNumber){ if(StringUtils.isEmpty(followSituation)){ vacantRows.add(rowNumber); return ""; } if(followSituation.equals("已发项目介绍资料")) return "0"; if(followSituation.equals("已约面谈")) return "1"; if(followSituation.equals("已发合同计划书")) return "2"; if(followSituation.equals("已报价")) return "3"; if(followSituation.equals("已发合同")) return "4"; if(followSituation.equals("已签合同")) return "5"; if(followSituation.equals("面谈中")) return "6"; if(followSituation.equals("已面签")) return "7"; if(followSituation.equals("无进度")) return "8"; vacantRows.add(rowNumber); return ""; } private String checkCustomerStatus(String customerStatus,int rowNumber){ if(StringUtils.isEmpty(customerStatus)) { vacantRows.add(rowNumber); return ""; } if(customerStatus.equals("新客户")) return "0"; if(customerStatus.equals("意向客户")) return "1"; if(customerStatus.equals("重点客户")) return "2"; if(customerStatus.equals("面谈客户")) return "3"; if(customerStatus.equals("签单客户")) return "4"; if(customerStatus.equals("被拒绝客户")) return "5"; if(customerStatus.equals("停止跟进")) return "6"; vacantRows.add(rowNumber); return ""; } public boolean checkRepeat(CustomerExcelBo cin,List cinList){ boolean flag = false; return flag; } /*private String checkCustomerIntention(String companyIntention,int rowNumber){ if(StringUtils.isEmpty(companyIntention)){ vacantRows.add(rowNumber); return ""; } if(companyIntention.equals("发明专利")) return "0"; if(companyIntention.equals("实用型新型专利")) return "1"; if(companyIntention.equals("外观专利")) return "2"; if(companyIntention.equals("软件著作权")) return "3"; if(companyIntention.equals("知识产权贯标")) return "4"; if(companyIntention.equals("高企认定")) return "5"; if(companyIntention.equals("技术成果")) return "6"; if(companyIntention.equals("技术需求")) return "7"; if(companyIntention.equals("专家咨询")) return "8"; if(companyIntention.equals("团单合作")) return "9"; if(companyIntention.equals("商标")) return "10"; if(companyIntention.equals("系统集成")) return "11"; vacantRows.add(rowNumber); return ""; }*/ private String checkOther(String cellValue,int rowNumber,int maxLength){ if(StringUtils.isBlank(cellValue) || cellValue.length() > maxLength){ vacantRows.add(rowNumber); return ""; }else{ return String.valueOf(cellValue).trim(); } } public String checkMobile(String mobile, int rowNumber){ mobile = mobile.trim(); String mobileRegex = "^1[3|4|5|7|8][0-9]{9}$"; String telRegex = "^\\d{3,4}-\\d{7,8}$"; if(StringUtils.isBlank(mobile)) return ""; if(DEFAULT_MOBILE.equals(mobile)) return mobile; if(!Pattern.matches(mobileRegex, mobile) && !Pattern.matches(telRegex, mobile)){ vacantRows.add(rowNumber); return ""; } return mobile; } public String getVacantRows(){ if(vacantRows.size()>0) return StringUtils.join(vacantRows.toArray(),",")+" 行数据不全或格式有误;"; else return ""; } @SuppressWarnings("resource") public Set parseOrderExcel(String excelPath) throws IOException{ InputStream excelFile = new FileInputStream(new File(excelPath)); XSSFWorkbook wb = new XSSFWorkbook(excelFile); Set result = new HashSet(); XSSFSheet sheet = wb.getSheetAt(0); Iterator rowIterator = sheet.iterator(); Row currentRow = sheet.getRow(1); //表头 int totalNumber = sheet.getLastRowNum(); int rowNumber = 1; rowIterator.next(); while(rowIterator.hasNext() && rowNumber <= totalNumber){ rowNumber++; currentRow = rowIterator.next(); Iterator cellIterator = currentRow.iterator(); OrderReportBo bo = new OrderReportBo(); bo.setSaleName(checkEmptyStr(cellIterator.next().getStringCellValue(),rowNumber)); //电话 Cell cell = cellIterator.next(); if(cell.getCellTypeEnum() == CellType.NUMERIC) { bo.setMobile(checkMobile(df.format(cell.getNumericCellValue()), rowNumber)); }else if(cell.getCellTypeEnum() == CellType.STRING){ bo.setMobile(checkMobile(cell.getStringCellValue(), rowNumber)); } //月份 cell = cellIterator.next(); if(cell.getCellTypeEnum() == CellType.NUMERIC){ bo.setMonth(checkOrderMonth(df.format(cell.getNumericCellValue()),rowNumber)); }else if(cell.getCellTypeEnum() == CellType.STRING){ bo.setMonth(checkOrderMonth(cell.getStringCellValue(),rowNumber)); } bo.setType(checkOrderType(cellIterator.next().getStringCellValue(),rowNumber)); //数量 cell = cellIterator.next(); if(cell.getCellTypeEnum() == CellType.NUMERIC){ bo.setProofCount(Float.valueOf(checkEmptyStr(df.format(cell.getNumericCellValue()),rowNumber))); }else if(cell.getCellTypeEnum() == CellType.STRING){ bo.setProofCount(Float.valueOf(checkEmptyStr(cell.getStringCellValue(),rowNumber))); } //金额 cell = cellIterator.next(); if(cell.getCellTypeEnum() == CellType.NUMERIC){ bo.setAmount(new BigDecimal(checkEmptyStr(df.format(cell.getNumericCellValue()),rowNumber))); }else if(cell.getCellTypeEnum() == CellType.STRING){ bo.setAmount(new BigDecimal(checkEmptyStr(cell.getStringCellValue(),rowNumber))); } bo.setRowNumber(rowNumber); result.add(bo); } return result; } private Integer checkOrderType(String OrderType,int rowNumber){ if(checkEmpty(OrderType, rowNumber)){ return null; } if(OrderType.equals("认证项目")) return 0; if(OrderType.equals("科技项目")) return 1; vacantRows.add(rowNumber); return null; } private Integer checkOrderMonth(String month,int rowNumber){ if(checkEmpty(month, rowNumber)){ return null; } Integer m = null; if(month.length() == 2 || month.length() == 6){ try { m = Integer.valueOf(month.substring(month.length()-2, month.length())); } catch (Exception e) { vacantRows.add(rowNumber); return null; } }else{ if(month.length() == 1){ try { m = Integer.valueOf(month); } catch (Exception e) { vacantRows.add(rowNumber); return null; } }else{ vacantRows.add(rowNumber); } } if(m>0 && m<=12){ Integer re = Integer.valueOf(month); if(re>m){ return re; }else{ String ym = new SimpleDateFormat("yyyy").format(new Date()) + (m>10?m:"0"+m); return Integer.valueOf(ym); } }else{ vacantRows.add(rowNumber); } return null; } private Boolean checkEmpty(String str,int rowNumber){ if(StringUtils.isEmpty(str)) { vacantRows.add(rowNumber); return true; }else{ return false; } } private String checkEmptyStr(String str,int rowNumber){ if(StringUtils.isEmpty(str)) { vacantRows.add(rowNumber); return ""; }else{ return str; } } //检查表头 // 2007 版本以上 最大支持1048576行 public final static String EXCEL_FILE_2007 = "2007"; // 2003 版本 最大支持65536 行 public final static String EXCEL_FILE_2003 = "2003"; /** * 功能描述 导出无头部标题行Excel * * @param title 表格sheet标题 * @param dataset 数据集合 * @param out 输出流 * @param version 2003 或者 2007,不传时默认生成2003版本 * @return void * @author * @date 2018-8-2 */ public void exportExcel(String title, Collection dataset, OutputStream out, String version) { if (StringUtils.isEmpty(version) || EXCEL_FILE_2003.equals(version.trim())) { exportExcel2003(title, null, dataset, out, "yyyy-MM-dd hh:mm:ss"); } else { exportExcel2007(title, null, dataset, out, "yyyy-MM-dd hh:mm:ss"); } } /** * 功能描述 导出带有头部标题行的Excel * * @param title 表格sheet标题 * @param headers 头部标题集合 * @param dataset 数据集合 * @param out 输出流 * @param version 2003 或者 2007,不传时默认生成2003版本 * @return void * @author * @date 2018-8-2 */ public void exportExcel(String title, String[] headers, Collection dataset, OutputStream out, String version) { if (StringUtils.isBlank(version) || EXCEL_FILE_2003.equals(version.trim())) { exportExcel2003(title, headers, dataset, out, "yyyy-MM-dd hh:mm:ss"); } else { exportExcel2007(title, headers, dataset, out, "yyyy-MM-dd hh:mm:ss"); } } /** * 功能描述 * 通用Excel导出方法,利用反射机制遍历对象的所有字段,将数据写入Excel文件中 * 此版本生成2007以上版本的文件 (文件后缀:xlsx) * * @param title 表格sheet标题名 * @param headers 表格头部标题集合 * @param dataset 需要显示的数据集合,集合中一定要放置符合JavaBean风格的类的对象。此方法支持的 * JavaBean属性的数据类型有基本数据类型及String,Date * @param out 与输出设备关联的流对象,可以将EXCEL文档导出到本地文件或者网络中 * @param pattern 如果有时间数据,设定输出格式。默认为"yyyy-MM-dd hh:mm:ss" * @return void * @author * @date 2018-8-2 */ @SuppressWarnings({"unchecked", "rawtypes"}) public void exportExcel2007(String title, String[] headers, Collection dataset, OutputStream out, String pattern) { // 声明一个工作薄 XSSFWorkbook workbook = new XSSFWorkbook(); // 生成一个表格 XSSFSheet sheet = workbook.createSheet(title); // 设置表格默认列宽度为20个字节 sheet.setDefaultColumnWidth(20); // 生成一个样式 XSSFCellStyle style = workbook.createCellStyle(); // 设置这些样式(表头) style.setFillPattern(FillPatternType.SOLID_FOREGROUND); style.setFillForegroundColor(new XSSFColor(Color.GRAY)); style.setBorderBottom(BorderStyle.THIN); style.setBorderLeft(BorderStyle.THIN); style.setBorderRight(BorderStyle.THIN); style.setBorderTop(BorderStyle.THIN); style.setAlignment(HorizontalAlignment.CENTER); // 生成一个字体 XSSFFont font = workbook.createFont(); font.setBold(true); font.setFontName("宋体"); font.setColor(new XSSFColor(Color.BLACK)); font.setFontHeightInPoints((short) 11); // 把字体应用到当前的样式 style.setFont(font); // 生成并设置另一个样式(内容) XSSFCellStyle style2 = workbook.createCellStyle(); style2.setFillPattern(FillPatternType.SOLID_FOREGROUND); style2.setFillForegroundColor(new XSSFColor(Color.WHITE)); style2.setBorderBottom(BorderStyle.THIN); style2.setBorderLeft(BorderStyle.THIN); style2.setBorderRight(BorderStyle.THIN); style2.setBorderTop(BorderStyle.THIN); style2.setAlignment(HorizontalAlignment.CENTER); style2.setVerticalAlignment(VerticalAlignment.CENTER); // 生成另一个字体 XSSFFont font2 = workbook.createFont(); font2.setBold(false); font2.setFontName("宋体"); font2.setColor(new XSSFColor(Color.BLACK)); font2.setFontHeightInPoints((short) 11); // 把字体应用到当前的样式 style2.setFont(font2); // 产生表格标题行 XSSFRow row = sheet.createRow(0); XSSFCell cellHeader; for (int i = 0; i < headers.length; i++) { cellHeader = row.createCell(i); cellHeader.setCellStyle(style); cellHeader.setCellValue(new XSSFRichTextString(headers[i])); } // 遍历集合数据,产生数据行 Iterator it = dataset.iterator(); int index = 0; T t; Field[] fields; Field field; XSSFRichTextString richString; Pattern p = Pattern.compile("^//d+(//.//d+)?$"); Matcher matcher; String fieldName; String getMethodName; XSSFCell cell; Class tCls; Method getMethod; Object value; String textValue; SimpleDateFormat sdf = new SimpleDateFormat(pattern); while (it.hasNext()) { index++; row = sheet.createRow(index); t = (T) it.next(); // 利用反射,根据JavaBean属性的先后顺序,动态调用getXxx()方法得到属性值 //如果实体类里有serialVersionUID,请将serialVersionUID放在所有属性之后声明,否则会出现第一列为空 fields = t.getClass().getDeclaredFields(); for (int i = 0; i < fields.length; i++) { field = fields[i]; fieldName = field.getName(); if ("serialVersionUID".equals(fieldName)) { continue; } cell = row.createCell(i); cell.setCellStyle(style2); getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1); try { tCls = t.getClass(); getMethod = tCls.getMethod(getMethodName, new Class[]{}); value = getMethod.invoke(t, new Object[]{}); // 判断值的类型后进行强制类型转换 textValue = null; if (value instanceof Integer) { cell.setCellValue((Integer) value); } else if (value instanceof Float) { textValue = String.valueOf((Float) value); cell.setCellValue(textValue); } else if (value instanceof Double) { textValue = String.valueOf((Double) value); cell.setCellValue(textValue); } else if (value instanceof Long) { cell.setCellValue((Long) value); } if (value instanceof Boolean) { textValue = "是"; if (!(Boolean) value) { textValue = "否"; } } else if (value instanceof Date) { textValue = sdf.format((Date) value); } else { // 其它数据类型都当作字符串简单处理 if (value != null) { textValue = value.toString(); } } if (textValue != null) { matcher = p.matcher(textValue); if (matcher.matches()) { // 是数字当作double处理 cell.setCellValue(Double.parseDouble(textValue)); } else { richString = new XSSFRichTextString(textValue); cell.setCellValue(richString); } } } catch (NoSuchMethodException e) { e.printStackTrace(); } catch (IllegalAccessException e) { e.printStackTrace(); } catch (InvocationTargetException e) { e.printStackTrace(); } catch (SecurityException e) { e.printStackTrace(); } catch (IllegalArgumentException e) { e.printStackTrace(); } finally { // 清理资源 } } } try { workbook.write(out); workbook.close(); } catch (IOException e) { e.printStackTrace(); } } /** * 功能描述 通用Excel导出方法,利用反射机制遍历对象的所有字段,将数据写入Excel文件中
* 此方法生成2003版本的excel,文件名后缀:xls * * @param title 表格sheet标题名 * @param headers 表格头部标题集合 * @param dataset 需要显示的数据集合,集合中一定要放置符合JavaBean风格的类的对象。此方法支持的 * JavaBean属性的数据类型有基本数据类型及String,Date * @param out 与输出设备关联的流对象,可以将EXCEL文档导出到本地文件或者网络中 * @param pattern 如果有时间数据,设定输出格式。默认为"yyyy-MM-dd hh:mm:ss" * @return void * @author * @date 2018-8-2 */ @SuppressWarnings({"unchecked", "rawtypes", "resource"}) public void exportExcel2003(String title, String[] headers, Collection dataset, OutputStream out, String pattern) { // 声明一个工作薄 HSSFWorkbook workbook = new HSSFWorkbook(); // 生成一个表格 HSSFSheet sheet = workbook.createSheet(title); // 设置表格默认列宽度为15个字节 sheet.setDefaultColumnWidth(20); // 生成一个样式 HSSFCellStyle style = workbook.createCellStyle(); // 设置这些样式 style.setFillForegroundColor(HSSFColor.HSSFColorPredefined.GREY_50_PERCENT.getIndex()); style.setFillPattern(FillPatternType.SOLID_FOREGROUND); style.setBorderBottom(BorderStyle.THIN); style.setBorderLeft(BorderStyle.THIN); style.setBorderRight(BorderStyle.THIN); style.setBorderTop(BorderStyle.THIN); style.setAlignment(HorizontalAlignment.CENTER); // 生成一个字体 HSSFFont font = workbook.createFont(); font.setBold(true); font.setFontName("宋体"); font.setColor(HSSFColor.HSSFColorPredefined.WHITE.getIndex()); font.setFontHeightInPoints((short) 11); // 把字体应用到当前的样式 style.setFont(font); // 生成并设置另一个样式 HSSFCellStyle style2 = workbook.createCellStyle(); style2.setFillForegroundColor(HSSFColor.HSSFColorPredefined.WHITE.getIndex()); style2.setFillPattern(FillPatternType.SOLID_FOREGROUND); style2.setBorderBottom(BorderStyle.THIN); style2.setBorderLeft(BorderStyle.THIN); style2.setBorderRight(BorderStyle.THIN); style2.setBorderTop(BorderStyle.THIN); style2.setAlignment(HorizontalAlignment.CENTER); style2.setVerticalAlignment(VerticalAlignment.CENTER); // 生成另一个字体 HSSFFont font2 = workbook.createFont(); font2.setBold(false); // 把字体应用到当前的样式 style2.setFont(font2); // 产生表格标题行 HSSFRow row = sheet.createRow(0); HSSFCell cellHeader; for (int i = 0; i < headers.length; i++) { cellHeader = row.createCell(i); cellHeader.setCellStyle(style); cellHeader.setCellValue(new HSSFRichTextString(headers[i])); } // 遍历集合数据,产生数据行 Iterator it = dataset.iterator(); int index = 0; T t; Field[] fields; Field field; HSSFRichTextString richString; Pattern p = Pattern.compile("^//d+(//.//d+)?$"); Matcher matcher; String fieldName; String getMethodName; HSSFCell cell; Class tCls; Method getMethod; Object value; String textValue; SimpleDateFormat sdf = new SimpleDateFormat(pattern); while (it.hasNext()) { index++; row = sheet.createRow(index); t = (T) it.next(); // 利用反射,根据JavaBean属性的先后顺序,动态调用getXxx()方法得到属性值 //如果实体类里有serialVersionUID,请将serialVersionUID放在所有属性之后声明,否则会出现第一列为空 fields = t.getClass().getDeclaredFields(); for (int i = 0; i < fields.length; i++) { field = fields[i]; fieldName = field.getName(); if ("serialVersionUID".equals(fieldName)) { continue; } cell = row.createCell(i); cell.setCellStyle(style2); getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1); try { tCls = t.getClass(); getMethod = tCls.getMethod(getMethodName, new Class[]{}); value = getMethod.invoke(t, new Object[]{}); // 判断值的类型后进行强制类型转换 textValue = null; if (value instanceof Integer) { cell.setCellValue((Integer) value); } else if (value instanceof Float) { textValue = String.valueOf((Float) value); cell.setCellValue(textValue); } else if (value instanceof Double) { textValue = String.valueOf((Double) value); cell.setCellValue(textValue); } else if (value instanceof Long) { cell.setCellValue((Long) value); } if (value instanceof Boolean) { textValue = "是"; if (!(Boolean) value) { textValue = "否"; } } else if (value instanceof Date) { textValue = sdf.format((Date) value); } else { // 其它数据类型都当作字符串简单处理 if (value != null) { textValue = value.toString(); } } if (textValue != null) { matcher = p.matcher(textValue); if (matcher.matches()) { // 是数字当作double处理 cell.setCellValue(Double.parseDouble(textValue)); } else { richString = new HSSFRichTextString(textValue); cell.setCellValue(richString); } } } catch (SecurityException e) { e.printStackTrace(); } catch (NoSuchMethodException e) { e.printStackTrace(); } catch (IllegalArgumentException e) { e.printStackTrace(); } catch (IllegalAccessException e) { e.printStackTrace(); } catch (InvocationTargetException e) { e.printStackTrace(); } finally { // 清理资源 } } } try { workbook.write(out); } catch (IOException e) { e.printStackTrace(); } } /** * 导出Excel * * @param titles 数据头 * @param valueList 数据体 * @param fileName Excel文件名 * @param sheetName 首个sheet页名 * @param response */ @SuppressWarnings({ "resource", "unused" }) public void listToExcel(String[] titles, List> valueList, String fileName, String sheetName, HttpServletResponse response) { try { response.setContentType("application/x-download"); fileName = URLEncoder.encode(fileName, "UTF-8"); response.addHeader("Content-Disposition", "attachement;filename=" + fileName); HSSFWorkbook workbook = new HSSFWorkbook(); HSSFCellStyle cellStyle = workbook.createCellStyle(); HSSFDataFormat format = workbook.createDataFormat(); HSSFSheet sheet = workbook.createSheet(); if (sheetName != null && sheetName.trim().length() >0 ) { workbook.setSheetName(0, sheetName); } HSSFRow row = sheet.createRow(0); HSSFCell cell; Set set = new HashSet<>(); // 构建表头 for (int i = 0; i < titles.length; i++) { String title = titles[i]; // cell = row.createCell[i]; cell = row.createCell(i); cell.setCellType(CellType.STRING); cell.setCellValue(title); } // 构建表格 for (int j = 0; j < valueList.size(); j++) { List values = valueList.get(j); row = sheet.createRow(j + 1); for (int m = 0; m < values.size(); m++) { cell = row.createCell(m); cell.setCellType(CellType.STRING); if (values.get(m) != null) { cell.setCellValue(values.get(m).toString()); } else { cell.setCellValue(""); } } } OutputStream out = response.getOutputStream(); workbook.write(out); out.flush(); out.close(); } catch (Exception e) { e.printStackTrace(); } } /** * 获取Excel单元格的值 * * @param cell * @return */ public static String getCellValue(Cell cell) { String value = ""; if (cell != null) { switch (cell.getCellTypeEnum()) { case NUMERIC: //数字 value = cell.getNumericCellValue() + ""; if (HSSFDateUtil.isCellDateFormatted(cell)) { Date date = cell.getDateCellValue(); if (date != null) { value = new SimpleDateFormat("yyyy/MM/dd").format(date);//日期格式化 } else { value = ""; } } else { //在解析cell的时候,数字类型默认是double的,但是想要的的整数的类型,需要格式化,很重要 value = new DecimalFormat("0").format(cell.getNumericCellValue()); } break; case STRING://字符串 value = cell.getStringCellValue(); break; case BOOLEAN://boolean类型 value = cell.getBooleanCellValue() + ""; break; case BLANK://空值 value = ""; break; case ERROR://错误类型 value = "非法字符"; break; default: value = "未知类型"; } } return value.trim(); } public static Date getCellDate(Cell cell) { Date date=new Date(); if (cell != null) { switch (cell.getCellTypeEnum()) { case NUMERIC: //数字 date = cell.getDateCellValue(); break; case STRING://字符串 date = DateUtils.StringToDate(cell.getStringCellValue(), AFTConstants.YYYYMMDD); default: date = new Date(); } } return date; } public static Workbook getWorkBook(MultipartFile file) { //创建Workbook工作薄对象,表示整个excel Workbook workbook = null; try { //获取excel文件的io流 InputStream is = file.getInputStream(); //如果是xls,使用HSSFWorkbook;如果是xlsx,使用XSSFWorkbook if(file.getOriginalFilename().matches("^.+\\.(?i)(xls)$"))workbook = new HSSFWorkbook(is); if(file.getOriginalFilename().matches("^.+\\.(?i)(xlsx)$"))workbook = new XSSFWorkbook(is); } catch (IOException e) { e.getLocalizedMessage(); } return workbook; } }