日常笔记

1.数据库创建时间和自动更新

‘create_time’ timestamp not null default current_timestamp comment ‘创建时间’’

‘update_time’ timestamp not null default current_timestamp on update current_timestamp comment ‘修改时间’

2.pagehelper拿total与正确结果不一致

public QueryByPage getAllName(Integer currentPage, Integer pagesize) {
    Page page1=PageHelper.startPage(currentPage,pagesize);
    List<XXXX> list=new ArrayList<>();
    list=creditEnhancementAccountRepository.getAllAccountInUsed(accountName);
    //此处拿到total
  	Long total=page1.getTotal();
    List<QueryBean> List=new ArrayList<>();
    PageInfo<QueryBean> page = new PageInfo<QueryBean>(List);
    QueryByPage resultpage=new QueryByPage();
    resultpage.setQueryBeanList(page.getList());
    resultpage.setCurrentPage(page.getPageNum());
    resultpage.setPagesize(page.getPageSize());
    resultpage.setTotal(total);
    return resultpage;
}

3.导出CSV

CSV工具：

public class CsvUtils {


    public static String getCSVPath(List<QueryResponse> queryResponseList){
        List exportData = new ArrayList<Map>();
        for(QueryResponse query:queryResponseList){
            Map row=new LinkedHashMap<String, String>();
            row.put("1",query.getId()+"");
            String channelNo=query.getChannelNo();
            System.out.println(channelNo);

            row.put("2",channelNo+"\t");
            row.put("3",query.getName()+"");
            exportData.add(row);
        }
        LinkedHashMap map = new LinkedHashMap();

        //设置列名
        map.put("1", "列名111");
        map.put("2", "列名222");
        //这个文件上传到路径，可以配置在数据库从数据库读取，这样方便一些！
        String path = "/data/timer/tmp/";

        //文件名=生产的文件名称+时间戳
        String fileName = "文件导出";
        File file = CsvUtils.createCSVFile(exportData, map, path, fileName);
        String fileName2 = file.getName();
        System.out.println("文件名称：" + fileName2);
        return path+"/"+fileName2;
    }



    /**
     * 生成为CVS文件
     *
     * @param exportData 源数据List
     * @param map        csv文件的列表头map
     * @param outPutPath 文件路径
     * @param fileName   文件名称
     * @return
     */
    @SuppressWarnings("rawtypes")
    public static File createCSVFile(List exportData, LinkedHashMap map, String outPutPath,
                                     String fileName) {
        File csvFile = null;
        BufferedWriter csvFileOutputStream = null;
        try {
            File file = new File(outPutPath);
            if (!file.exists()) {
                file.mkdir();
            }
            //定义文件名格式并创建
            csvFile = File.createTempFile(fileName, ".csv", new File(outPutPath));
            // UTF-8使正确读取分隔符","
            //如果生产文件乱码，windows下用gbk，linux用UTF-8
            csvFileOutputStream = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(
                    csvFile), "UTF-8"), 1024);
            // 写入文件头部
            for (Iterator propertyIterator = map.entrySet().iterator(); propertyIterator.hasNext(); ) {
                Map.Entry propertyEntry = (Map.Entry) propertyIterator.next();
                csvFileOutputStream.write((String) propertyEntry.getValue() != null ? (String) propertyEntry.getValue() : "");
                if (propertyIterator.hasNext()) {
                    csvFileOutputStream.write(",");
                }
            }
            csvFileOutputStream.newLine();
            // 写入文件内容
            for (Iterator iterator = exportData.iterator(); iterator.hasNext(); ) {
                Object row = (Object) iterator.next();
                for (Iterator propertyIterator = map.entrySet().iterator(); propertyIterator
                        .hasNext(); ) {
                    Map.Entry propertyEntry = (Map.Entry) propertyIterator
                            .next();
                    csvFileOutputStream.write((String) BeanUtils.getProperty(row,
                            (String) propertyEntry.getKey()));
                    if (propertyIterator.hasNext()) {
                        csvFileOutputStream.write(",");
                    }
                }
                if (iterator.hasNext()) {
                    csvFileOutputStream.newLine();
                }
            }
            csvFileOutputStream.flush();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                csvFileOutputStream.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return csvFile;
    }



    /**
     * 删除该目录filePath下的所有文件
     *
     * @param filePath 文件目录路径
     */
    public static void deleteFiles(String filePath) {
        File file = new File(filePath);
        if (file.exists()) {
            File[] files = file.listFiles();
            for (int i = 0; i < files.length; i++) {
                if (files[i].isFile()) {
                    files[i].delete();
                }
            }
        }
    }

    /**
     * 删除单个文件
     *
     * @param filePath 文件目录路径
     * @param fileName 文件名称
     */
    public static void deleteFile(String filePath, String fileName) {
        File file = new File(filePath);
        if (file.exists()) {
            File[] files = file.listFiles();
            for (int i = 0; i < files.length; i++) {
                if (files[i].isFile()) {
                    if (files[i].getName().equals(fileName)) {
                        files[i].delete();
                        return;
                    }
                }
            }
        }
    }
}

导出csv时若碰到大于16位的精度丢失问题 加"“是没用的 还是会被识别为数字 可以加”\t"在末尾

4.下载文件

    @RequestMapping(value = "/download")
   @ResponseBody
   public void downloadExcel(HttpServletResponse res, HttpServletRequest req) throws Exception {
      String fileName = "模板" + ".xlsx";
      ServletOutputStream out;
      res.setContentType("multipart/form-data");
      res.setCharacterEncoding("UTF-8");
      res.setContentType("text/html");
      String filePath = getClass().getResource("/template/excel/" + fileName).getPath();
      String userAgent = req.getHeader("User-Agent");
      if (userAgent.contains("MSIE") || userAgent.contains("Trident")) {
         fileName = java.net.URLEncoder.encode(fileName, "UTF-8");
      } else {
// 非IE浏览器的处理：
         fileName = new String((fileName).getBytes("UTF-8"), "ISO-8859-1");
      }
      filePath = URLDecoder.decode(filePath, "UTF-8");
      res.setHeader("Content-Disposition", "attachment;fileName=" + fileName);
      FileInputStream inputStream = new FileInputStream(filePath);
      out = res.getOutputStream();
      int b = 0;
      byte[] buffer = new byte[1024];
      while ((b = inputStream.read(buffer)) != -1) {
// 4.写到输出流(out)中
         out.write(buffer, 0, b);
      }
      inputStream.close();

      if (out != null) {
         out.flush();
         out.close();
      }

   }

5.上传并rename

@RequestMapping(value="upload", method = RequestMethod.POST)
@ResponseBody
public ObjectResponse fileUpload(@RequestParam("file") MultipartFile srcFile,String uid) {

   if (srcFile.isEmpty()) {
      return new ObjectResponse("文件为空");
   }
       ExcelResultMap excelResultMap=new ExcelResultMap();
   String res="";
   try {
      File destFile = new File(ResourceUtils.getURL("classpath:").getPath());
      if (!destFile.exists()) {
         destFile = new File("");
      }
      //输出目标文件的绝对路径
      SimpleDateFormat sf_ = new SimpleDateFormat("yyyyMMddHHmmss");
      String times = sf_.format(new Date());
      File upload = new File(destFile.getAbsolutePath(), "picture/" + times);
      if (!upload.exists()) {
         upload.mkdirs();
      }
      String storePath=upload.getAbsolutePath();
      byte[] bytes = srcFile.getBytes();
      Path path = Paths.get(upload.getAbsolutePath() + "/" + srcFile.getOriginalFilename());
      Files.write(path, bytes);
      String uuid = UUID.randomUUID().toString().replaceAll("-", "");
      String fileName = srcFile.getOriginalFilename();
      String suffixName = fileName.substring(fileName.lastIndexOf(".") + 1).toLowerCase();
      String newFileName = uuid + "." + suffixName;

      File oldFile = new File(storePath+"/"+srcFile.getOriginalFilename());
      // new file
      File newFile = new File(storePath+"/"+newFileName);
      oldFile.renameTo(newFile);
      String filePath=storePath+"/"+newFileName;
      excelResultMap =XXXX;
   } catch (IOException e) {
      e.printStackTrace();
   }
   return new ObjectResponse(excelResultMap);
}

6.excel处理

阿里EasyExcel

读：

想要从listener中获得数据 比如成功条数 失败条数

外层：

final QueryFromExcelListener queryFromExcelListener=new QueryFromExcelListener(XXXX);

listener中可以加属性和getter setter 完成处理后外层取即可

写：

easyexcel需要3.1.7版本的poi 可能导致版本冲突

若无法改变poi版本

老的基于poi3.14的写excel方法：

@Override
public String writeSuccessExcel (List<XXXX> XXXXList)throws Exception {
    UUID uuid=UUID.randomUUID();
    File file=new File("/data/timer/tmp/order/"+uuid+".xls");
    // 构造List<Student>,实际开发从数据库里面获取

    Map<String, String> convertMap = new LinkedHashMap<String, String>();
    convertMap.put("列名1", "capitalOrderNo");
    convertMap.put("列名2", "channelOrderNo");
    // 获取要填充的数据
    Map<String, Object> excelData = ExcelUtils.fillExcelData(convertMap,XXXXList);
    // 获取头信息
    List<String> heads = (List<String>) excelData.get("heads");
    // 获取数据信息
    List<List<String>> dataList = (List<List<String>>) excelData.get("dataList");
    // 创建Excel文件
    HSSFWorkbook workbook = ExcelUtils.createExcelFile(uuid.toString(), heads,dataList);
    // 输出Excel文件
    OutputStream out = new FileOutputStream(file);
    workbook.write(out);
    return file.getAbsolutePath();
}

用到的ExcelUtils

import com.alibaba.excel.util.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.text.ParsePosition;
import java.text.SimpleDateFormat;
import java.util.*;

public class ExcelUtils {

    /**
     *
     * @Title: createExcelFile
     * @Description: 在填充sheet数据的时候,会需要一个空的Excel文件,用于设置Sheet信息的时候用到
     * @return 一个不带有头信息,数据信息的空的excel文件
     * @return: HSSFWorkbook
     */
    public static HSSFWorkbook createExcelFile() {
        HSSFWorkbook wb = new HSSFWorkbook();
        return wb;
    }

    /**
     *
     * @Title: createExcelFile
     * @Description: 创建一个空的带有头信息的excel
     * @param fileName
     * @param heads
     * @return
     * @return: HSSFWorkbook
     */
    public static HSSFWorkbook createExcelFile(String fileName,
                                               List<String> heads) {
        HSSFWorkbook wb = new HSSFWorkbook();
        if (StringUtils.isEmpty(fileName) || null == heads) {
            return null;
        } else {
            HSSFSheet sheet = wb.createSheet(fileName);
            HSSFRow row = sheet.createRow(0);
            // 封装头信息
            for (int index = 0; index < heads.size(); index++) {
                row.createCell(index).setCellValue(heads.get(index));
            }
        }
        return wb;
    }

    /**
     * @Title: createExcelFile
     * @Description: 创建excel,带有头信息和数据
     * @param fileName
     *            excel表格文件名称
     * @param heads
     *            excel表格的头信息
     * @param dataList
     *            excel表格要填充的数据
     * @return
     * @throws IOException
     * @return: HSSFWorkbook
     */
    public static HSSFWorkbook createExcelFile(String fileName,
                                               List<String> heads, List<List<String>> dataList) {
        HSSFWorkbook wb = new HSSFWorkbook();
        if (StringUtils.isEmpty(fileName) || null == heads || null == dataList) {
            return null;
        } else {
            HSSFSheet sheet = wb.createSheet(fileName);
            HSSFRow row = sheet.createRow(0);

            // 手动设置列宽。第一个参数表示要为第几列设；，第二个参数表示列的宽度，n为列高的像素数。
            for(int i=0;i<heads.size();i++){
                sheet.setColumnWidth((short) i, (short) (50 * 200));
            }
            // 创建两种单元格格式
            CellStyle cs = wb.createCellStyle();
            CellStyle cs2 = wb.createCellStyle();

            // 创建两种字体
            Font f = wb.createFont();
            Font f2 = wb.createFont();

            // 创建第一种字体样式（用于列名）
            f.setFontHeightInPoints((short) 20);
            f.setColor(IndexedColors.BLACK.getIndex());
            f.setBoldweight(Font.BOLDWEIGHT_BOLD);
            // 创建第二种字体样式（用于值）
            f2.setFontHeightInPoints((short) 12);
            f2.setColor(IndexedColors.BLACK.getIndex());

            // 设置第一种单元格的样式（用于列名）
            cs.setFont(f);
            cs.setBorderLeft(CellStyle.BORDER_THIN);
            cs.setBorderRight(CellStyle.BORDER_THIN);
            cs.setBorderTop(CellStyle.BORDER_THIN);
            cs.setBorderBottom(CellStyle.BORDER_THIN);
            cs.setAlignment(CellStyle.ALIGN_CENTER);

            for(int i=0;i<heads.size();i++){
                Cell cell = row.createCell(i);
                cell.setCellValue(heads.get(i));
                cell.setCellStyle(cs);
            }

//            // 封装头信息
//            for (int index = 0; index < heads.size(); index++) {
//                row.createCell(index).setCellValue(heads.get(index));
//            }
            // 填充数据信息
            for (int i = 0; i < dataList.size(); i++) {
                HSSFRow row_data = sheet.createRow(i + 1);
                for (int j = 0; j < dataList.get(i).size(); j++) {
                    row_data.createCell(j).setCellValue(dataList.get(i).get(j));
                }
            }
        }
        return wb;
    }

    /**
     *
     * @Title: produceCellType
     * @Description: 获取excel单元格里面内容的格式,来获取数据
     * @param cell
     *            单元格
     * @return
     * @return: String 单元格的内容
     */
    private static String produceCellType(Cell cell) {
        String cellStrData = null;
        if (null == cell) {
            return null;
        } else {
            switch (cell.getCellType()) {
                case HSSFCell.CELL_TYPE_NUMERIC: // 日期或者数字
                    // 处理日期格式、时间格式
                    if (HSSFDateUtil.isCellDateFormatted(cell)) {
                        SimpleDateFormat sdf = null;
                        // 时间格式的处理
                        if (cell.getCellStyle().getDataFormat() == HSSFDataFormat
                                .getBuiltinFormat("h:mm")) {
                            sdf = new SimpleDateFormat("HH:mm");
                        } else {// 日期格式的处理
                            sdf = new SimpleDateFormat("yyyy-MM-dd");
                        }
                        Date date = cell.getDateCellValue();
                        cellStrData = sdf.format(date);
                    } else {
                        // 数字的处理
                        double cellData = cell.getNumericCellValue();
                        cellStrData = String.valueOf(cellData);
                    }
                    break;
                case HSSFCell.CELL_TYPE_STRING: // 字符串
                    cellStrData = cell.getStringCellValue();
                    break;
                case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean
                    cellStrData = String.valueOf(cell.getBooleanCellValue());
                    break;
                case HSSFCell.CELL_TYPE_FORMULA: // 公式
                    cellStrData = String.valueOf(cell.getCellFormula());
                    break;
                case HSSFCell.CELL_TYPE_BLANK: // 空值
                    break;
                case HSSFCell.CELL_TYPE_ERROR: // 故障
                    break;
                default:
                    break;
            }
        }
        return cellStrData;
    }

    /**
     *
     * @Title: convertExcelDataToMapDataWithPrimaryKey
     * @Description: 将excel文件的每一行数据,转换为HashMap的形式.只转换第一个sheet的数据内容
     * @param convertMap
     *            转换的准则,例如 Map<String, String> headMap = new
     *            LinkedHashMap<String, String>(); headMap.put("指标ID",
     *            "indicatorId"); headMap.put("指标名称", "indicatorName");
     * @param filePath
     *            excel文件
     * @return
     * @throws IOException
     * @return: List<HashMap<String, String>>
     */
    public static List<HashMap<String, String>> convertExcelDataToMapData(
            Map<String, String> convertMap, String filePath) throws IOException {
        List<HashMap<String, String>> dataList = new ArrayList<HashMap<String, String>>();
        Map<String, Integer> headMap = new HashMap<String, Integer>();
        if (null == convertMap || convertMap.size() == 0
                || StringUtils.isEmpty(filePath)) {
            return dataList;
        } else {
            InputStream input = new FileInputStream(filePath); // 建立输入流
            Workbook wb = new HSSFWorkbook(input);
            Sheet sheet = wb.getSheetAt(0);
            Row rowIndexs = sheet.getRow(0);
            int cellSize = rowIndexs.getLastCellNum();
            Set<String> keys = convertMap.keySet();
            // 将对应的字段和excel的head的下标对应起来
            for (String key : keys) {
                for (int i = 0; i < cellSize; i++) {
                    Cell cell = rowIndexs.getCell(i);
                    if (cell != null
                            && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
                        if (key.equals(cell.getStringCellValue())) {
                            headMap.put(key, rowIndexs.getCell(i)
                                    .getColumnIndex());
                        }
                    }
                }
            }
            // 处理数据
            int rowSize = sheet.getLastRowNum();
            for (int i = 1; i < rowSize; i++) { // 第一行默认是表头数据,不算入计算结果
                HashMap<String, String> resultMap = new HashMap<String, String>(); // 用于保存每一行的转换结果
                Row row = sheet.getRow(i);
                for (Map.Entry<String, Integer> entry : headMap.entrySet()) {
                    Cell cell = row.getCell(entry.getValue());
                    String data = produceCellType(cell);
                    resultMap.put(convertMap.get(entry.getKey()), data);
                }
                dataList.add(resultMap);
            }
        }

        return dataList;
    }

    /**
     *
     * @Title: convertExcelDataToMapDataWithPrimaryKey
     * @Description: excel的转换,带有主键的原则。如果excel的那一行数据的表示的主键为null或者没填写。那么这一行不转换。
     *               例如,下面的 指标ID可以理解为主键.//默认第一行的第一列为主键
     *               将excel文件的每一行数据,转换为HashMap的形式.只转换第一个sheet的数据内容.
     * @param convertMap
     *            转换的准则,例如 Map<String, String> headMap = new
     *            LinkedHashMap<String, String>(); headMap.put("指标ID",
     *            "indicatorId"); headMap.put("指标名称", "indicatorName");
     * @param filePath
     *            excel文件
     * @return
     * @throws IOException
     * @return: List<HashMap<String, String>>
     */
    public static List<HashMap<String, String>> convertExcelDataToMapDataWithPrimaryKey(
            Map<String, String> convertMap, String filePath) throws IOException {
        List<HashMap<String, String>> dataList = new ArrayList<HashMap<String, String>>();
        Map<String, Integer> headMap = new HashMap<String, Integer>();
        if (null == convertMap || convertMap.size() == 0
                || StringUtils.isEmpty(filePath)) {
            return dataList;
        } else {
            InputStream input = new FileInputStream(filePath); // 建立输入流
            Workbook wb = new HSSFWorkbook(input);
            Sheet sheet = wb.getSheetAt(0);
            Row rowIndexs = sheet.getRow(0);
            int cellSize = rowIndexs.getLastCellNum();
            Set<String> keys = convertMap.keySet();
            // 将对应的字段和excel的head的下标对应起来
            for (String key : keys) {
                for (int i = 0; i < cellSize; i++) {
                    Cell cell = rowIndexs.getCell(i);
                    if (cell != null
                            && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
                        if (key.equals(cell.getStringCellValue())) {
                            headMap.put(key, rowIndexs.getCell(i)
                                    .getColumnIndex());
                        }
                    }
                }
            }
            // 处理数据
            int rowSize = sheet.getLastRowNum();
            for (int i = 1; i < rowSize; i++) { // 第一行默认是表头数据,不算入计算结果
                HashMap<String, String> resultMap = new HashMap<String, String>(); // 用于保存每一行的转换结果
                Row row = sheet.getRow(i);
                Cell flagCell = row.getCell(0); // 默认第0列是每一行的主键
                if (null != row && null != flagCell
                        && HSSFCell.CELL_TYPE_BLANK != flagCell.getCellType()) {
                    for (Map.Entry<String, Integer> entry : headMap.entrySet()) {
                        Cell cell = row.getCell(entry.getValue());
                        if (null != cell) {
                            String data = produceCellType(cell);
                            resultMap.put(convertMap.get(entry.getKey()), data);
                        }
                    }
                }
                // 将数据加入到,返回数值里面
                dataList.add(resultMap);
            }
        }
        return dataList;
    }

    /**
     * @param <T>
     * @Title: convertExcelDataToClassData
     * @Description: 解析excel已有的数据,以Class的形式返回.
     * @param
     *            headNameMap.put("CID", "customerId")
     * @param fileName
     *            excel文件
     * @param class1
     *            要转换的Class的类型
     * @return
     * @throws InstantiationException
     * @throws IllegalAccessException
     * @throws IOException
     * @throws NoSuchFieldException
     * @throws SecurityException
     * @return: List<T>
     */
    public static <T> List<T> convertExcelDataToClassData(
            Map<String, String> convertMap, String fileName, Class<T> class1)
            throws InstantiationException, IllegalAccessException, IOException,
            NoSuchFieldException, SecurityException {

        List<T> objects = new ArrayList<T>(); // 返回结果集
        Map<String, Integer> indexHashMap = new HashMap<String, Integer>(); // 定位excel头文件cell位置
        if (null == convertMap || convertMap.size() == 0
                || StringUtils.isEmpty(fileName)) {
            return objects;
        } else {
            InputStream input = new FileInputStream(fileName); // 建立输入流
            Workbook wb = new HSSFWorkbook(input);
            Sheet sheet = wb.getSheetAt(0);
            Row rowIndexs = sheet.getRow(0);
            int cellSize = rowIndexs.getLastCellNum();
            // 将对应的字段和excel的head的下标对应起来
            Set<String> keys = convertMap.keySet();
            for (String key : keys) {
                for (int i = 0; i < cellSize; i++) {
                    Cell cell = rowIndexs.getCell(i);
                    if (cell != null
                            && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
                        if (key.equals(produceCellType(cell))) {
                            indexHashMap.put(key, rowIndexs.getCell(i)
                                    .getColumnIndex()); // 头文件push 下标位置
                        }
                    }
                }
            }
            // 数据的封装
            for (int i = 1; i <= sheet.getLastRowNum(); i++) { // 第一行默认是下标，不算入计算结果
                Row row = sheet.getRow(i);
                T object = class1.newInstance();
                for (Map.Entry<String, Integer> entry : indexHashMap.entrySet()) {
                    Cell cell = row.getCell(entry.getValue());
                    String data = produceCellType(cell);
                    String fieldName = convertMap.get(entry.getKey());
                    Field field = object.getClass().getDeclaredField(fieldName);
                    field.setAccessible(true);
                    // 根据Field的类型,来设置Field的内容
                    // 以便于适应除了String外的int,long,double,float等类型的属性
                    setFieldValue(object, data, field);
                }
                objects.add(object);
            }

        }
        return objects;
    }

    /**
     * @param <T>
     * @Title: convertExcelDataToClassData
     * @Description: 解析excel已有的数据,以Class的形式返回.
     * @param
     *            headNameMap.put("CID", "customerId")
     * @param
     *            excel文件
     * @param class1
     *            要转换的Class的类型
     * @return
     * @throws InstantiationException
     * @throws IllegalAccessException
     * @throws IOException
     * @throws NoSuchFieldException
     * @throws SecurityException
     * @return: List<T>
     */
    public static <T> List<T> convertExcelDataToClassData(
            Map<String, String> convertMap, InputStream input, Class<T> class1)
            throws InstantiationException, IllegalAccessException, IOException,
            NoSuchFieldException, SecurityException {

        List<T> objects = new ArrayList<T>(); // 返回结果集
        Map<String, Integer> indexHashMap = new HashMap<String, Integer>(); // 定位excel头文件cell位置
        if (null == convertMap || convertMap.size() == 0 || null == input) {
            return objects;
        } else {
            Workbook wb = new HSSFWorkbook(input);
            Sheet sheet = wb.getSheetAt(0);
            Row rowIndexs = sheet.getRow(0);
            int cellSize = rowIndexs.getLastCellNum();
            // 将对应的字段和excel的head的下标对应起来
            Set<String> keys = convertMap.keySet();
            for (String key : keys) {
                for (int i = 0; i < cellSize; i++) {
                    Cell cell = rowIndexs.getCell(i);
                    if (cell != null
                            && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
                        if (key.equals(produceCellType(cell))) {
                            indexHashMap.put(key, rowIndexs.getCell(i)
                                    .getColumnIndex()); // 头文件push 下标位置
                        }
                    }
                }
            }
            // 数据的封装
            for (int i = 1; i <= sheet.getLastRowNum(); i++) { // 第一行默认是下标，不算入计算结果
                Row row = sheet.getRow(i);
                T object = class1.newInstance();
                for (Map.Entry<String, Integer> entry : indexHashMap.entrySet()) {
                    Cell cell = row.getCell(entry.getValue());
                    String data = produceCellType(cell);
                    String fieldName = convertMap.get(entry.getKey());
                    Field field = object.getClass().getDeclaredField(fieldName);
                    field.setAccessible(true);
                    // 根据Field的类型,来设置Field的内容
                    // 以便于适应除了String外的int,long,double,float等类型的属性
                    setFieldValue(object, data, field);
                }
                objects.add(object);
            }

        }
        return objects;
    }

    /**
     * @Title: matcheExcelIndexToDataForm
     * @Description: 解析excel已数组的形式返回
     * @param headNameMap
     *            headNameMap.put("CID", "customerId")
     * @param fileName
     * @param class1
     * @return
     * @throws InstantiationException
     * @throws IllegalAccessException
     * @throws IOException
     * @throws NoSuchFieldException
     * @throws SecurityException
     * @return: List<Object>
     */
    public static <T> List<T> convertExcelDataToClassDataWithPrimaryKey(
            Map<String, String> headNameMap, InputStream ips, Class<T> class1)
            throws InstantiationException, IllegalAccessException, IOException,
            NoSuchFieldException, SecurityException {

        List<T> objects = new ArrayList<T>(); // 返回结果集
        Map<String, Integer> indexHashMap = new HashMap<String, Integer>(); // 定位excel头文件cell位置
        if (null == headNameMap || headNameMap.size() == 0 || null == ips) {
            return objects;
        } else {
            Workbook wb = null;
            wb = new HSSFWorkbook(ips);
            Sheet sheet = wb.getSheetAt(0);
            Row rowIndexs = sheet.getRow(0);
            int cellSize = rowIndexs.getLastCellNum();
            Set<String> keys = headNameMap.keySet();
            for (String key : keys) {
                for (int i = 0; i < cellSize; i++) {
                    Cell cell = rowIndexs.getCell(i);
                    if (cell != null
                            && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
                        if (key.equals(cell.getStringCellValue())) {
                            indexHashMap.put(key, rowIndexs.getCell(i)
                                    .getColumnIndex()); // 头文件push 下标位置
                        }

                    }

                }
            }

            for (int i = 1; i <= sheet.getLastRowNum(); i++) { // 第一行默认是下标，不算入计算结果
                Row row = sheet.getRow(i);
                T object = class1.newInstance();
                Cell flagCell = row.getCell(0);
                if (null != row && null != flagCell
                        && HSSFCell.CELL_TYPE_BLANK != flagCell.getCellType()) {
                    for (Map.Entry<String, Integer> entry : indexHashMap.entrySet()) {
                        Cell cell = row.getCell(entry.getValue());
                        if (null != cell) {
                            String data = produceCellType(cell);
                            String fieldName = headNameMap.get(entry.getKey());
                            Field field = object.getClass().getDeclaredField(
                                    fieldName);
                            field.setAccessible(true);
                            // 根据Field的类型,来设置Field的内容
                            // 以便于适应除了String外的int,long,double,float等类型的属性
                            setFieldValue(object, data, field);
                        } else {
                            continue;
                        }
                    }
                    objects.add(object);
                } else {
                    break;
                }
            }

        }
        return objects;
    }

    /**
     *
     * @Title: setFieldValue 设置JavaBean属性的数据,以便于支持除了String类型外的其他数据类型
     * @Description: setFieldValue
     *               设置JavaBean属性的数据,以便于支持除了String类型外的其他数据类型,例如int,
     *               long,double,date
     * @param object
     *            JavaBean
     * @param data
     *            要设置的数据
     * @param field
     *            JavaBean的Field字段
     * @throws IllegalAccessException
     * @return: void
     */
    private static <T> void setFieldValue(T object, String data, Field field)
            throws IllegalAccessException {
        // 对field的类型进行判断,以便于支持String外的其它类型
        String fieldType = field.getType().getName();
        if (fieldType.equals("java.lang.Double") || fieldType.equals("double")) {
            // Double类型的处理
            double doubleValue = Double.parseDouble(data);
            field.set(object, doubleValue);
        } else if (fieldType.equals("java.lang.Float")
                || fieldType.equals("float")) {
            // Float类型的处理
            float folatValue = Float.parseFloat(data);
            field.set(object, folatValue);
        } else if (fieldType.equals("java.lang.Integer")
                || fieldType.equals("int")) {
            // Integer类型的处理
            int intValue = Integer.parseInt(data);
            field.set(object, intValue);
        } else if (fieldType.equals("java.lang.Long")
                || fieldType.equals("long")) {
            // Long类型的处理
            long longValue = Long.parseLong(data);
            field.set(object, longValue);
        } else if (field.getType().getName().equals("java.util.Date")) {
            // Date类型的处理
            // "yyyy-MM-dd HH:mm:ss",根据具体的格式来处理
            Date dateValue = strToDate(data);
            field.set(object, dateValue);
        } else {
            // String的处理
            field.set(object, data);
        }
    }

    /**
     * @Title: matcheExcelIndexToDataForm
     * @Description: 解析excel已数组的形式返回
     * @param headNameMap
     *            headNameMap.put("CID", "customerId")
     * @param fileName
     * @param class1
     * @return
     * @throws InstantiationException
     * @throws IllegalAccessException
     * @throws IOException
     * @throws NoSuchFieldException
     * @throws SecurityException
     * @return: List<Object>
     */
    public static <T> List<T> convertExcelDataToClassDataWithPrimaryKey(
            Map<String, String> headNameMap, String fileName, Class<T> class1)
            throws InstantiationException, IllegalAccessException, IOException,
            NoSuchFieldException, SecurityException {

        List<T> objects = new ArrayList<T>(); // 返回结果集
        Map<String, Integer> indexHashMap = new HashMap<String, Integer>(); // 定位excel头文件cell位置
        if (null == headNameMap || headNameMap.size() == 0 || null == fileName) {
            return objects;
        } else {
            Workbook wb = null;
            InputStream ips = new FileInputStream(fileName);
            wb = new HSSFWorkbook(ips);
            Sheet sheet = wb.getSheetAt(0);
            Row rowIndexs = sheet.getRow(0);
            int cellSize = rowIndexs.getLastCellNum();
            Set<String> keys = headNameMap.keySet();
            for (String key : keys) {
                for (int i = 0; i < cellSize; i++) {
                    Cell cell = rowIndexs.getCell(i);
                    if (cell != null
                            && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
                        if (key.equals(cell.getStringCellValue())) {
                            indexHashMap.put(key, rowIndexs.getCell(i)
                                    .getColumnIndex()); // 头文件push 下标位置
                        }

                    }

                }
            }

            for (int i = 1; i <= sheet.getLastRowNum(); i++) { // 第一行默认是下标，不算入计算结果
                Row row = sheet.getRow(i);
                T object = class1.newInstance();
                Cell flagCell = row.getCell(0);
                if (null != row && null != flagCell
                        && HSSFCell.CELL_TYPE_BLANK != flagCell.getCellType()) {
                    for (Map.Entry<String, Integer> entry : indexHashMap.entrySet()) {
                        Cell cell = row.getCell(entry.getValue());
                        if (null != cell) {
                            String data = produceCellType(cell);
                            String fieldName = headNameMap.get(entry.getKey());
                            Field field = object.getClass().getDeclaredField(
                                    fieldName);
                            field.setAccessible(true);
                            // 根据Field的类型,来设置Field的内容
                            // 以便于适应除了String外的int,long,double,float等类型的属性
                            setFieldValue(object, data, field);
                        } else {
                            continue;
                        }
                    }
                    objects.add(object);
                } else {
                    break;
                }
            }

        }
        return objects;
    }

    /**
     *
     * @Title: exportExcel
     * @Description: 需要先创建好excel文件,调用一次添加一次sheet信息
     * @param workbook
     *            要添加sheet信息的excel
     * @param sheetNum
     *            sheet的编号位置,从0开始
     * @param sheetTitle
     *            要添加sheet信息
     * @param heads
     *            头信息
     * @param dataList
     *            要填充的数据
     * @return
     * @throws Exception
     * @return: HSSFWorkbook 返回天填充数据后的excel
     */
    public static HSSFWorkbook fillExcelWithSheetInfo(HSSFWorkbook workbook,
                                                      int sheetNum, String sheetTitle, List<String> heads,
                                                      List<List<String>> dataList) throws IOException {
        if (StringUtils.isEmpty(sheetTitle) || null == heads
                || null == dataList || workbook == null) {
            return null;
        } else {
            // create sheet
            HSSFSheet sheet = workbook.createSheet();
            workbook.setSheetName(sheetNum, sheetTitle);
            // 头信息
            HSSFRow row = sheet.createRow(0);
            for (int index = 0; index < heads.size(); index++) {
                row.createCell(index).setCellValue(heads.get(index));
            }
            // 填充信息
            for (int i = 0; i < dataList.size(); i++) {
                HSSFRow row_data = sheet.createRow(i + 1);
                for (int j = 0; j < dataList.get(i).size(); j++) {
                    row_data.createCell(j).setCellValue(dataList.get(i).get(j));
                }
            }
        }
        return workbook;
    }

    /**
     * 使用一个List数组,来填充要显示的excel数据
     *
     * @param userList
     *            headMap.put("顾问ID", "userId");
     * @return
     * @throws NoSuchFieldException
     * @throws SecurityException
     * @throws IllegalArgumentException
     * @throws IllegalAccessException
     */
    public static Map<String, Object> fillExcelData(
            Map<String, String> headMap, List<?> objects)
            throws NoSuchFieldException, SecurityException,
            IllegalArgumentException, IllegalAccessException {
        Map<String, Object> resMap = new HashMap<String, Object>();
        List<String> heads = new ArrayList<String>();
        Set<String> keySet = headMap.keySet();
        List<List<String>> dataList = new ArrayList<List<String>>();
        boolean flag = true;
        if (null == objects || objects.size() < 1) {
            for (String key : keySet) {
                heads.add(key);
            }
        }
        for (Object object : objects) {
            List<String> data = new ArrayList<String>();
            for (String key : keySet) {
                if (flag == true) {
                    heads.add(key);
                }
                Field userField = object.getClass().getDeclaredField(
                        headMap.get(key));
                userField.setAccessible(true);
                String userData = String.valueOf(userField.get(object));
                // 一些特殊的判断
                if ("状态".equals(key)) {
                    if ("1".equals(userData)) {
                        userData = "有效";
                    } else {
                        userData = "无效";
                    }
                }
                data.add(userData);
            }
            flag = false;
            dataList.add(data);
        }
        resMap.put("heads", heads);
        resMap.put("dataList", dataList);
        return resMap;
    }

    /**
     * 将长时间格式字符串转换为时间 yyyy-MM-dd HH:mm:ss
     *
     * @param strDate
     * @return
     */
    private static Date strToDate(String strDate) {
        SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
        ParsePosition pos = new ParsePosition(0);
        Date strtodate = formatter.parse(strDate, pos);
        return strtodate;
    }

}

7.捕获指定数据库异常

catch (DataAccessException ex){
    SQLException sqle = (SQLException) ex.getCause();
    logger.error("",ex);
    if(sqle.getErrorCode()==1062)
        return 2;
    //else exception
}

比如1062是有重复的错误码

8.对象复制

BeanUtils.copyProperties（来源bean,目标bean）

来自org.springframework.beans