| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813 |
- 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<Integer> vacantRows = new HashSet<Integer>(); //数据不完整的
- private final String DEFAULT_MOBILE = "0000-0000000";
- DecimalFormat df = new DecimalFormat("0");
- @SuppressWarnings ("resource")
- public Set<CustomerExcelBo> parseExcel(String excelPath) throws IOException{
- InputStream excelFile = new FileInputStream(new File(excelPath));
- XSSFWorkbook wb = new XSSFWorkbook(excelFile);
- Set<CustomerExcelBo> result = new HashSet<CustomerExcelBo>();
- XSSFSheet sheet = wb.getSheetAt(0);
- Iterator<Row> 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<Cell> 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<CustomerExcelBo> 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<OrderReportBo> parseOrderExcel(String excelPath) throws IOException{
- InputStream excelFile = new FileInputStream(new File(excelPath));
- XSSFWorkbook wb = new XSSFWorkbook(excelFile);
- Set<OrderReportBo> result = new HashSet<OrderReportBo>();
- XSSFSheet sheet = wb.getSheetAt(0);
- Iterator<Row> 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<Cell> 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<T> 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<T> 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<T> 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<T> 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文件中 <br>
- * 此方法生成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<T> 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<T> 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<List<Object>> 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<String> 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<Object> 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;
- }
- }
|