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

代码

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);
            }
        }
    }
}