ExcelUtil

import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.List;

/**
 * @version: 1.00.00
 * @description: 
 * @company: 
 * @author: 
 * @date: 2021-10-18 17:06
 */
@Slf4j
public class ExcelUtils {

    /**
     * Excel表格导出
     * @param response HttpServletResponse对象
     * @param excelData Excel表格的数据，封装为List>
     * @param sheetName sheet的名字
     * @param fileName 导出Excel的文件名
     * @param columnWidth Excel表格的宽度，建议为15
     * @throws IOException 抛IO异常
     */
    public static void exportExcel(HttpServletResponse response,
                                   List> excelData,
                                   String sheetName,
                                   String fileName,
                                   int columnWidth) throws IOException {
        //声明一个工作簿
        HSSFWorkbook workbook = new HSSFWorkbook();
        //生成一个表格，设置表格名称
        HSSFSheet sheet = workbook.createSheet(sheetName);
        //设置表格列宽度
        sheet.setDefaultColumnWidth(columnWidth);
        //写入List>中的数据
        int rowIndex = 0;
        for(List data : excelData){
            //创建一个row行，然后自增1
            HSSFRow row = sheet.createRow(rowIndex++);
            //遍历添加本行数据
            for (int i = 0; i < data.size(); i++) {
                //创建一个单元格
                HSSFCell cell = row.createCell(i);
                //创建一个内容对象
                HSSFRichTextString text = new HSSFRichTextString(data.get(i).toString());
                //将内容对象的文字内容写入到单元格中
                cell.setCellValue(text);
            }
        }
        //准备将Excel的输出流通过response输出到页面下载
        //八进制输出流
        response.setContentType("application/octet-stream");
        //设置导出Excel的名称
        response.setHeader("Content-disposition", "attachment;filename=" + fileName);
        //刷新缓冲
        response.flushBuffer();
        //workbook将Excel写入到response的输出流中，供页面下载该Excel文件
        workbook.write(response.getOutputStream());
        //关闭workbook
        workbook.close();
    }

    /**
     * 根据文件名字服务器直接下载
     * @param fileName:文件名称
     */
    public static void downloadFile(HttpServletResponse response,String fileName) throws IOException {
        String path = BusinessConstants.LINUXTEMPLATE;
        // TODO 本地测试的,提交代码时注意
//        path = "E:\\test\\";
        File file = new File(path+fileName);
        // 判断文件是否存在
        File fileParent = file.getParentFile();
        if (!fileParent.exists()) {
            throw new BusinessException(ResultCodeEnum.FILE_ERROR_EXISTS.getCode(),ResultCodeEnum.FILE_ERROR_EXISTS.getMsg(),ResultCodeEnum.FILE_ERROR_EXISTS.getMsgEn());
        }
        //八进制输出流
        response.setContentType("application/octet-stream");
        //设置导出Excel的名称
        response.setHeader("Content-disposition", "attachment;filename=" + fileName);
        //刷新缓冲
        response.flushBuffer();
        byte[] buffer = new byte[1024];
        FileInputStream fis = null;
        BufferedInputStream bis = null;
        try {
            fis = new FileInputStream(file);
            bis = new BufferedInputStream(fis);
            OutputStream os = response.getOutputStream();
            int i = bis.read(buffer);
            while (i != -1) {
                os.write(buffer, 0, i);
                i = bis.read(buffer);
            }
            log.info(fileName + "下载成功!");
        } catch (Exception e) {
            log.info(fileName + "下载失败!");
        } finally {
            if (bis != null) {
                try {
                    bis.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            if (fis != null) {
                try {
                    fis.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    /**
     *
     * @param file 导入Excel的信息
     *             格式：execl文件第一行为:实体类对应的属性(英文、大小写也要一致)
     *                         从第二行开始:就是对应的属性值了
     * @param clz 泛型的class
     * @param  泛型
     * @return
     * @throws IllegalAccessException
     * @throws InstantiationException
     * @throws NoSuchFieldException
     * @throws IOException
     */
    public static  List importExcel(MultipartFile file,Class clz) throws IllegalAccessException, InstantiationException, NoSuchFieldException,IOException {
        if(file == null || file.getSize() == 0){
            throw new BusinessException(ResultCodeEnum.EXCEL_ERROR_IMPORT.getCode(),ResultCodeEnum.EXCEL_ERROR_IMPORT.getMsg(),ResultCodeEnum.EXCEL_ERROR_IMPORT.getMsgEn());
        }
        String fileName = file.getOriginalFilename();
        InputStream inputStream = file.getInputStream();
//        Field[] fields = clz.getFields();
        if(!(fileName.endsWith(".xls") || fileName.endsWith(".xlsx"))){
            throw new BusinessException(ResultCodeEnum.EXCEL_ERROR_IMPORT.getCode(),ResultCodeEnum.EXCEL_ERROR_IMPORT.getMsg(),ResultCodeEnum.EXCEL_ERROR_IMPORT.getMsgEn());
        }else if(fileName.endsWith(".xls")){
            log.info(fileName + ":开始解析转换");
            return translateXls(inputStream,clz);
        }else{
            log.info(fileName + ":开始解析转换");
            return translateXlsx(inputStream,clz);
        }
    }


    /**
     * 处理.xls文件
     * @param inputStream
     * @param clz
     * @param 
     * @return
     * @throws IllegalAccessException
     * @throws InstantiationException
     * @throws NoSuchFieldException
     * @throws IOException
     */
    private static  List translateXls(InputStream inputStream,Class clz) throws IllegalAccessException, InstantiationException, NoSuchFieldException, IOException {
        List list = new ArrayList<>();
        HSSFWorkbook workbook = new HSSFWorkbook(inputStream);
        //读取第一张sheet
        HSSFSheet sheet = workbook.getSheetAt(0);
        // 取第一行的表头对应
        HSSFRow firstRow = sheet.getRow(0);
        List headerList = new ArrayList<>();
        for (int i = 0; i < firstRow.getLastCellNum(); i++) {
            headerList.add(firstRow.getCell(i).getStringCellValue());
        }
        for (int i = 1; i < sheet.getLastRowNum(); i++) {
            HSSFRow row = sheet.getRow(i);
            // 从第二行开始进行数据属性的赋值封装
            T t = (T)clz.newInstance();
            for (int j = 0; j < headerList.size(); j++) {
                // 反射赋值
                Field field = clz.getDeclaredField(headerList.get(j));
                field.setAccessible(true);
                // 当数据类型非字符串的时候,我们需要进行指定类型,强制转换
//                row.getCell(0).setCellType(CellType.STRING);
                field.set(t, row.getCell(j).getStringCellValue());
            }
            list.add(t);
        }
        log.info("解析转换完成");
        return list;
    }

    /**
     * 处理.xlsx格式
     * @param inputStream
     * @param clz
     * @param 
     * @return
     * @throws IllegalAccessException
     * @throws InstantiationException
     * @throws NoSuchFieldException
     * @throws IOException
     */
    private static  List translateXlsx(InputStream inputStream,Class clz) throws IllegalAccessException, InstantiationException, NoSuchFieldException, IOException {
        List list = new ArrayList<>();
        XSSFWorkbook workbook = new XSSFWorkbook (inputStream);
        //读取第一张sheet
        XSSFSheet sheet = workbook.getSheetAt(0);
        // 取第一行的表头对应
        XSSFRow firstRow = sheet.getRow(0);
        List headerList = new ArrayList<>();
        for (int i = 0; i < firstRow.getLastCellNum(); i++) {
            headerList.add(firstRow.getCell(i).getStringCellValue());
        }
        for (int i = 1; i < sheet.getLastRowNum(); i++) {
            XSSFRow row = sheet.getRow(i);
            // 从第二行开始进行数据属性的赋值封装
            T t = (T)clz.newInstance();
            for (int j = 0; j < headerList.size(); j++) {
                // 反射赋值
                Field field = clz.getDeclaredField(headerList.get(j));
                field.setAccessible(true);
                // 当数据类型非字符串的时候,我们需要进行指定类型,强制转换
//                row.getCell(0).setCellType(CellType.STRING);
                field.set(t, row.getCell(j).getStringCellValue());
            }
            list.add(t);
        }
        log.info("解析转换完成");
        return list;
    }


}



						  
					  
						
							


						

							
							
			JavaUtil							
						



						

						
						


						

						
					
					

					
					

						
相关

						

				
						
			            
            
            
                                    
           
  			
						

					

					
				

				

														
					
					

					
									

					
									   
标签

										

										
    
										

									

										
										

									

					

					
				


			
		
		
		
			


						





	

	
一品网 冀ICP备14022925号-6