java excel导出

Java excel导出

本篇文章是利用poi+反射+文件流技术做出的excel导出

读文件指定行的方法：

/**
     *功能描述:读文件指定行的内容 字符之间用,隔开
     * @author lichangchun
     * @date 2020/6/5
     * @param filePath 路径 n代表第几行
     * @return java.lang.String
     */

    public static String readTxt(String filePath,int n) {
        StringBuilder result = new StringBuilder();
        try {

            File file = new File(filePath);//文件路径
            if (!file.exists()) {
                file.mkdirs();
            }
           // LineNumberReader bfr = new LineNumberReader(new InputStreamReader(new FileInputStream(new File(filePath)), "UTF-8"));
            FileReader fileReader = new FileReader(file);
            //缓冲字符输入流
            LineNumberReader reader = new LineNumberReader(fileReader);

            String lineTxt = null;
            String txt = "";
            int lines=0;
            while (txt!=null) {
                lines++;
                txt=reader.readLine();

                if(lines==n) {//获取第n行的信息 然后添加到结果集中
                    result.append(txt);
                }
            }
            reader.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result.toString();
    }

反射-根据参数名获取方法返回的值

public static String getFieldValueByFieldName(String fieldName, Object object) {
        try {
           /**
           之所以下面这样写是因为 我这边自动生成的实体类get set方法
           有的如admId 是getAdmId()
           而想这种第二个字母大写的如vNo 就是getvNo()
           */
            String firstLetter = fieldName.substring(0, 1);//获取参数第一个字母
            String twoLetter = fieldName.substring(1, 2);//获取参数第二个字母
            char firestchar=twoLetter.charAt(0);
           boolean flag= Character.isUpperCase(firestchar);//判断第二个字母是否大写
            String getter="";
           if(flag){
               getter= "get" + firstLetter + fieldName.substring(1);//获取该参数的get方法名
           }else{
               getter = "get" + firstLetter.toUpperCase() + fieldName.substring(1);//获取该参数的get方法名
           }
        Method method = object.getClass().getMethod(getter, new Class[] {});
           String typeName=method.getReturnType().toString();//获取方法类型

            Object value = method.invoke(object, new Object[] {});
            if(null==value||" ".equals(value)){
                return "";
            }
            if(typeName.equals("class java.util.Date")){//如果类型是date则转为yyyy-MM-dd HH:mm:ss的字符串
                String  date=zoneToLocalTime(value.toString());
                   return date;

            }

            return value.toString();
        } catch (Exception e) {
            return null;
        }
    }

Date类型的字符串如Thu Jul 09 15:37:16 CST 2020转yyyy-MM-dd HH:mm:ss的字符串

 public static String zoneToLocalTime(String dateString) throws Exception {

       String pictureCapturedTime = null;
        SimpleDateFormat sdf = new SimpleDateFormat("EEE MMM dd HH:mm:ss z yyyy", java.util.Locale.US);
        Date f = sdf.parse(dateString);


        SimpleDateFormat sdf1 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            pictureCapturedTime = sdf1.format(f);

        return pictureCapturedTime;
    }

导出核心方法

/**
list:要导出的数据
rowsName：导出表格第一行表格头组
num:要读取的文件行数
flag:1代表正式环境 2代表测试环境
title:表格头
*/
 public static  void getReflex(List<?> list,  String[] rowsName,int num,String flag,String title)throws Exception{
      HSSFWorkbook workbook = new HSSFWorkbook();


      String path="";
      if(flag.equals("1")){
          path="/software/*8/8t/reflex.txt";//这是写reflex.txt在liux服务器的地址
      }else{
          path = ResourceUtils.getURL("classpath:").getPath()+"/static/reflex.txt";//本地reflex.txt的地址
      }

      String str= ReflexUtil.readTxt(path,num);
      String []stringArray=str.split(",");

      DegreeVo degreeVo=new DegreeVo();
      for (int x = 0; x < 1; x++) {
          HSSFSheet sheet = workbook.createSheet(title + x);                     // 创建工作表

          List<HashMap<String, Object>> listMap =getlistMap(list,stringArray);
       List<Object[]> dataList = new ArrayList<Object[]>();
          Object[] objs = null;
          for (int i = 0; i < listMap.size(); i++) {
              HashMap<String, Object> data = listMap.get(i);
              objs = new Object[rowsName.length];
              objs[0] = i;
              for(int k=0;k<stringArray.length;k++){
              //反射-根据参数名获取参数值
                  String value=ReflexUtil.getFieldValueByFieldName(stringArray[k], list.get(i));

                  objs[k+1]=value;
              }
              dataList.add(objs);
          }
          ExportExcel ex = new ExportExcel(title, rowsName, dataList);
          ex.export(workbook, sheet, x,title);
      }
  }
  /**	
  stringArray--表格第一行 列数组
  list--要导入的数据

*/
public static List<HashMap<String, Object>> getlistMap (List<?> list, String []stringArray){

      List<HashMap<String, Object>> listMap = new ArrayList<HashMap<String, Object>>();
      for(int i=0;i<list.size();i++){
          LinkedHashMap<String, Object> dataMap = new LinkedHashMap<String, Object>();

          for(int k=0;k<stringArray.length;k++){

              String value=ReflexUtil.getFieldValueByFieldName(stringArray[k], list.get(i));

              dataMap.put(stringArray[k],value);
          }

          listMap.add(dataMap);
      }
      return listMap;
  }

exportExcel工具类

package pcsystem.excel;

import com.google.common.base.Strings;


import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.util.CellRangeAddress;


import javax.servlet.http.HttpServletResponse;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;

import static pcsystem.util.CurrentRequestUtil.getResponse;

public class ExportExcel {
    //显示的导出表的标题
    private String title;
    //导出表的列名
    private String[] rowName ;

    private List<Object[]> dataList = new ArrayList<Object[]>();

    HttpServletResponse response;

    //构造方法，传入要导出的数据
    public ExportExcel(String title,String[] rowName,List<Object[]>  dataList){
        this.dataList = dataList;
        this.rowName = rowName;
        this.title = title;
    }


    public void  export(HSSFWorkbook workbook, HSSFSheet sheet, int x,String title) throws Exception{
        try{

            // 产生表格标题行
            HSSFRow rowm = sheet.createRow(0);
            HSSFCell cellTiltle = rowm.createCell(0);
             rowm.setHeight((short) (26 * 35)); //设置高度
           //sheet样式定义【getColumnTopStyle()/getStyle()均为自定义方法 - 在下面  - 可扩展】
            HSSFCellStyle columnTopStyle = this.getColumnTopStyle(workbook);//获取列头样式对象
            HSSFCellStyle style = this.getStyle(workbook);                    //单元格样式对象

            sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, (rowName.length-1)));

            cellTiltle.setCellStyle(columnTopStyle);

            cellTiltle.setCellValue(title);

            exprtTwo(workbook,sheet,columnTopStyle, style);



        }catch(Exception e){
            e.printStackTrace();
        }

    }

    /*
     * 列头单元格样式
     */
    public HSSFCellStyle getColumnTopStyle(HSSFWorkbook workbook) {

        // 设置字体
        HSSFFont font = workbook.createFont();
        //设置字体大小
        font.setFontHeightInPoints((short)11);
        //字体加粗
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        //设置字体名字
        font.setFontName("Courier New");
        //设置样式;
        HSSFCellStyle style = workbook.createCellStyle();
        //设置底边框;
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        //设置底边框颜色;
        style.setBottomBorderColor(HSSFColor.BLACK.index);
        //设置左边框;
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        //设置左边框颜色;
        style.setLeftBorderColor(HSSFColor.BLACK.index);
        //设置右边框;
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        //设置右边框颜色;
        style.setRightBorderColor(HSSFColor.BLACK.index);
        //设置顶边框;
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        //设置顶边框颜色;
        style.setTopBorderColor(HSSFColor.BLACK.index);
        //在样式用应用设置的字体;
        style.setFont(font);
        //设置自动换行;
        style.setWrapText(false);
        //设置水平对齐的样式为居中对齐;
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        //设置垂直对齐的样式为居中对齐;
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

        //设置单元格背景颜色
        style.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

        return style;

    }

    /*
     * 列数据信息单元格样式
     */
    public HSSFCellStyle getStyle(HSSFWorkbook workbook) {
        // 设置字体
        HSSFFont font = workbook.createFont();
        //设置字体大小
        //font.setFontHeightInPoints((short)10);
        //字体加粗
        //font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        //设置字体名字
        font.setFontName("Courier New");
        //设置样式;
        HSSFCellStyle style = workbook.createCellStyle();
        //设置底边框;
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        //设置底边框颜色;
        style.setBottomBorderColor(HSSFColor.BLACK.index);
        //设置左边框;
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        //设置左边框颜色;
        style.setLeftBorderColor(HSSFColor.BLACK.index);
        //设置右边框;
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        //设置右边框颜色;
        style.setRightBorderColor(HSSFColor.BLACK.index);
        //设置顶边框;
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        //设置顶边框颜色;
        style.setTopBorderColor(HSSFColor.BLACK.index);
        //在样式用应用设置的字体;
        style.setFont(font);
        //设置自动换行;
        style.setWrapText(false);
        //设置水平对齐的样式为居中对齐;
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        //设置垂直对齐的样式为居中对齐;
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

        return style;
    }

    public void exprtTwo(HSSFWorkbook workbook, HSSFSheet sheet, HSSFCellStyle columnTopStyle, HSSFCellStyle style){
        // 定义所需列数
        int columnNum = rowName.length;
        HSSFRow rowRowName = sheet.createRow(1);                // 在索引2的位置创建行(最顶端的行开始的第二行)

        rowRowName.setHeight((short) (25 * 25)); //设置高度

        // 将列头设置到sheet的单元格中
        for(int n=0;n<columnNum;n++){
            HSSFCell  cellRowName = rowRowName.createCell(n);
            //创建列头对应个数的单元格
            cellRowName.setCellType(CellType.STRING);                //设置列头单元格的数据类型
            HSSFRichTextString text = new HSSFRichTextString(rowName[n]);
            cellRowName.setCellValue(text);                                    //设置列头单元格的值
            cellRowName.setCellStyle(columnTopStyle);                        //设置列头单元格样式
        }

        //将查询出的数据设置到sheet对应的单元格中
        for(int i=0;i<dataList.size();i++){

            Object[] obj = dataList.get(i);//遍历每个对象
            HSSFRow row = sheet.createRow(i+2);//创建所需的行数

            row.setHeight((short) (25 * 20)); //设置高度

            for(int j=0; j<obj.length; j++){
                HSSFCell  cell = null;   //设置单元格的数据类型

                    cell = row.createCell(j,HSSFCell.CELL_TYPE_STRING);
                    if( obj[j] != null){
                        cell.setCellValue(obj[j].toString());                        //设置单元格的值
                    }


                cell.setCellStyle(style);                                    //设置单元格样式
            }
        }
        //让列宽随着导出的列长自动适应
        for (int colNum = 0; colNum < columnNum; colNum++) {
            int columnWidth = sheet.getColumnWidth(colNum) / 256;
            for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {
                HSSFRow currentRow;
                //当前行未被使用过
                if (sheet.getRow(rowNum) == null) {
                    currentRow = sheet.createRow(rowNum);
                } else {
                    currentRow = sheet.getRow(rowNum);
                }
                if (currentRow.getCell(colNum) != null) {
                    HSSFCell currentCell = currentRow.getCell(colNum);
                    currentCell.setCellType(CellType.STRING);
                    if (currentCell.getCellType() == HSSFCell.CELL_TYPE_STRING) {

                        int length = currentCell.getStringCellValue().getBytes().length;
//                            System.out.println(columnWidth);
//                            System.out.println(length);
                        if (columnWidth < length) {
                            columnWidth = currentCell.getStringCellValue().getBytes().length;
                        }

                    }
                }
            }
            if(colNum == 0){
                sheet.setColumnWidth(colNum, (columnWidth-2) * 128);
            }else{
                sheet.setColumnWidth(colNum, (columnWidth+4) * 256);
            }
        }

        if(workbook !=null){
            try
            {
//                    String fileName = "\""+ new String((title + ".xls").getBytes("gb2312"),  "iso8859-1") + "\"";
//                    System.out.println("名字"+fileName);
//                    String headStr = "attachment; filename=\"" + fileName + "\"";
                response = getResponse();
                response.setContentType("APPLICATION/OCTET-STREAM");
                //excel名字中文处理
                response.setHeader("Content-Disposition", "attachment;filename="
                        + new String(title.getBytes(),"iso-8859-1") + ".xls");
                OutputStream out = response.getOutputStream();

                //   FileOutputStream out = new FileOutputStream("C:\\Users\\" + new SimpleDateFormat("yyyyMMddHHmmss").format(new Date()).toString() +".xls");
                workbook.write(out);
                out.close();
            }
            catch (IOException e)
            {
                e.printStackTrace();
            }
        }
    }

}

控制器层

    /**
     *功能描述:学员导出
     * @author lixiansheng
     * @date 2020/6/10
     * @param userBean
     * @return void
     */

    @GetMapping("getUserListExportExcel")
    public void getUserListExportExcel(UserBean userBean) {

        List<User> userList= userMapper.getUserList(userBean);//获取要导出的页面数据

        String[] rowsName = new String[]{"序号", "账号", "学员姓名","昵称", "手机号", "注册时间"};
        ReflexUtil reflexUtil=new ReflexUtil();
        String title="学员管理表";
        try {
            ReflexUtil.getReflex(userList,rowsName,2,flag,title);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }