下载时利用Excel模板来生成Excel

淡淡的烟草味﹌ 2022-05-25 01:41 282阅读 0赞

代码

import com.lenovo.pcsd.bp.businesspartner.bean.LenovoShopProductInfo;
    import com.lenovo.pcsd.bp.businesspartner.bean.ShopProductInfoPageData;
    import com.lenovo.pcsd.bp.businesspartner.bean.ShopProductInfoSearchParam;
    import com.lenovo.pcsd.bp.businesspartner.mapper.LenovoShopProductInfoMapper;
    import com.lenovo.pcsd.bp.businesspartner.service.LenovoShopProductInfoService;
    import org.apache.commons.io.FileUtils;
    import org.apache.commons.io.IOUtils;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.ss.usermodel.Cell;
    import org.apache.poi.ss.usermodel.Row;
    import org.apache.poi.ss.usermodel.Sheet;
    import org.apache.poi.ss.usermodel.Workbook;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.stereotype.Service;
    import org.springframework.util.ClassUtils;
    import org.springframework.util.ResourceUtils;
    
    import java.beans.IntrospectionException;
    import java.beans.PropertyDescriptor;
    import java.io.*;
    import java.lang.reflect.Field;
    import java.lang.reflect.InvocationTargetException;
    import java.lang.reflect.Method;
    import java.text.SimpleDateFormat;
    import java.util.ArrayList;
    import java.util.Date;
    import java.util.List;
    
    @Service
    public class LenovoShopProductInfoServiceImpl implements LenovoShopProductInfoService {
        @Autowired
        private LenovoShopProductInfoMapper lenovoShopProductInfoInfoMapper;
    
        @Override
        public ShopProductInfoPageData getLenovoShopProductInfoList(ShopProductInfoSearchParam param){
            if(null != param){
                Integer dataCount = lenovoShopProductInfoInfoMapper.getLenovoShopProductInfoDataCount(param);
                Integer pageCount = dataCount%param.getPageSize()==0 ?
                        (dataCount/param.getPageSize()) : (dataCount/param.getPageSize() + 1);
                ShopProductInfoPageData pageData = new ShopProductInfoPageData();
                pageData.setDataCount(dataCount);
                pageData.setPageSize(param.getPageSize());
                pageData.setCurPageNo(param.getCurPageNo());
                pageData.setPageCount(pageCount);
                pageData.setProductInfoList(lenovoShopProductInfoInfoMapper.getLenovoShopProductInfoList(param));
                return pageData;
            } else {
                return null;
            }
        }
    
    
        @Override
        public File getLenovoShopProductInfoFile(ShopProductInfoSearchParam param) throws IOException, NoSuchFieldException, IntrospectionException, InvocationTargetException, IllegalAccessException {
            if(null != param){
                List<LenovoShopProductInfo> dataList = lenovoShopProductInfoInfoMapper.getLenovoShopProductInfoList(param);
                // 将模板文件复制一份出来作为数据导出文件,
    //            String templateFileName = Thread.currentThread().getContextClassLoader()
    //                    .getResource("excelTemplate/export_shop_product_info.xls").getPath();
    //            File excelTemplate = ResourceUtils.getFile("classpath:excelTemplate/export_shop_product_info.xls");
                // 项目打包成jar包运行后无法读取模板，只能以输入流方式读取,故复制模板改成此写法
                InputStream in = ClassUtils.class.getClassLoader().getResourceAsStream("excelTemplate/export_shop_product_info.xls");
    
                SimpleDateFormat sdf1 = new SimpleDateFormat("yyyyMMdd");
                SimpleDateFormat sdf2 = new SimpleDateFormat("yyyyMMddHHmmss");
                Date date = new Date(System.currentTimeMillis());
                String parentDirName = sdf1.format(date);
                String dataFileEnd = sdf2.format(date)+"_"+System.currentTimeMillis()+".xls";
                String dataFileName = "/lenovo-store-export/shop-product-info/"+parentDirName+"/"+dataFileEnd;
                File dataFile = new File(dataFileName);
                //FileUtils.copyFile(excelTemplate, dataFile);
                FileOutputStream fileOut1 = new FileOutputStream(dataFileName);
                IOUtils.copy(in, fileOut1);
                fileOut1.close();
    
                // 将数据写入到dataFile 内
                Workbook wb = new HSSFWorkbook(new FileInputStream(dataFile));
                for (int t = 0; t < wb.getNumberOfSheets(); t++) {
                    Sheet sheet = wb.getSheetAt(t);
                    int lastRowNum = sheet.getLastRowNum();
                    if(lastRowNum <0){
                        continue;
                    }
                    Row row = sheet.getRow(0);
                    List<String> fieldList = new ArrayList<String>();
                    if (row != null) {
                        for (int j = 0; j < row.getLastCellNum(); j++) {
                            Cell cell = row.getCell(j);
                            String value = getCellValue(cell) ;
                            if(!value.equals("")){
                                fieldList.add(value);
                            }
                        }
                    }
                    if(fieldList.isEmpty()){
                        continue;
                    }
                    // 删除数据文件的配置字段行
                    removeRow(sheet, 0);
    
                    //循环data数据，按照excel文件需要的字段，逐列数据填入
                    if(null != dataList && !dataList.isEmpty()){
                        Class clazz = dataList.get(0).getClass();
                        for(int i=0; i<dataList.size(); i++){
                            LenovoShopProductInfo productInfo = dataList.get(i);
                            Row newRow = sheet.createRow(i+1);
                            for(int x=0; x < fieldList.size(); x++ ){
                                String fName = fieldList.get(x);
                                Field f = clazz.getDeclaredField(fName);
                                PropertyDescriptor pd = new PropertyDescriptor(f.getName(), clazz);
                                Method rM = pd.getReadMethod();//获得读方法
                                Object val = (Object) rM.invoke(productInfo);
                                Cell tempCell = newRow.createCell(x);
                                tempCell.setCellValue(String.valueOf(val));
                            }
                        }
                    }
                }
                // 保存
                FileOutputStream fileOut2 = new FileOutputStream(dataFileName);
                wb.write(fileOut2);
                fileOut2.close();
                return dataFile;
            } else {
                return null;
            }
        }
    
    
        /***
         * 读取单元格的值
         * @param cell
         * @return
         */
        private String getCellValue(Cell cell) {
            Object result = "";
            if (cell != null) {
                switch (cell.getCellType()) {
                    case Cell.CELL_TYPE_STRING:
                        result = cell.getStringCellValue();
                        break;
                    case Cell.CELL_TYPE_NUMERIC:
                        result = cell.getNumericCellValue();
                        break;
                    case Cell.CELL_TYPE_BOOLEAN:
                        result = cell.getBooleanCellValue();
                        break;
                    case Cell.CELL_TYPE_FORMULA:
                        result = cell.getCellFormula();
                        break;
                    case Cell.CELL_TYPE_ERROR:
                        result = cell.getErrorCellValue();
                        break;
                    case Cell.CELL_TYPE_BLANK:
                        break;
                    default:
                        break;
                }
            }
            return result.toString();
        }
    
        /**
         * 删除sheet页中行记录
         * @param sheet
         * @param rowIndex
         */
        private void removeRow(Sheet sheet, int rowIndex) {
            int lastRowNum = sheet.getLastRowNum();
            if(rowIndex>=0 && rowIndex<lastRowNum) {
                //将行号为rowIndex+1一直到行号为lastRowNum的单元格全部上移一行，以便删除rowIndex行
                sheet.shiftRows(rowIndex + 1, lastRowNum, -1);
            }
            if(rowIndex == lastRowNum){
                Row removingRow = sheet.getRow(rowIndex);
                if(removingRow!=null){
                    sheet.removeRow(removingRow);
                }
            }
        }
    }