Excel导入导出封装工具类

ExcelImportUtil.javaimport lombok.extern.slf4j.Slf4j;import org.apache.commons.lang3.StringUtils;import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.WorkbookFactory;import java.io.File;import java.io.FileInputStream;import java.io.IOException;import java.lang.reflect.Field;import java.text.DecimalFormat;import java.util.ArrayList;import java.util.List;/** * Created by chl * Excel导入公共工具类 */@Slf4j@SuppressWarnings("all")public class ExcelImportUtil {    public static String getCellValue(Cell cell) {        String returnValue = "";        if (cell == null) {            return returnValue;        } else {            switch (cell.getCellType()) {                case 0:                    if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) {                        returnValue = cell.getDateCellValue().toString();                    } else {                        returnValue = String.valueOf(cell.getNumericCellValue());                    }                    break;                case 1:                    returnValue = cell.getRichStringCellValue().getString();                case 2:                case 3:                case 4:            }            return returnValue.trim();        }    }    /*        获取Excel数据     */    public static <T> List<T> GetDataFromExcel(Class<T> c, String filePath, String[] dataColumns, List<String> titles) {        List<T> listData = new ArrayList<>();        FileInputStream myxls = null;        try {            myxls = new FileInputStream(new File(filePath));            org.apache.poi.ss.usermodel.Workbook wb = WorkbookFactory.create(myxls);            org.apache.poi.ss.usermodel.Sheet sheet = wb.getSheetAt(0);            int rowNum = sheet.getLastRowNum();            //获取标题信息，用于验证            for (int i = 0; i < dataColumns.length; i++) {                String cellValue = ExcelImportUtil.getCellValue(sheet.getRow(0).getCell(i));                if(!StringUtils.isEmpty(cellValue))                {                    titles.add(cellValue);                }            }            //读取Excel数据行            for (int i = 1; i <= rowNum; ++i) {                org.apache.poi.ss.usermodel.Row row = sheet.getRow(i);                if (row == null) {                    continue;                }                T item = c.newInstance();                for (int j = 0; j < dataColumns.length; j++) {                    String cellValue = ExcelImportUtil.getCellValue(row.getCell(j));                    Field field = c.getDeclaredField(dataColumns[j]);                    field.setAccessible(true); //设置些属性是可以访问的                   /* if( field.getName().equals("onepercent") || field.getName().equals("twopercent") ||                        field.getName().equals("threepercent") || field.getName().equals("fourpercent") ||                        field.getName().equals("oneaward") || field.getName().equals("onepunish") ||                        field.getName().equals("twoaward") || field.getName().equals("twopunish") ||                        field.getName().equals("threeaward") || field.getName().equals("threepunish")||                        field.getName().equals("fouraward") || field.getName().equals("fourpunish") ||field.getName().equals("warntime")){                        field.set(item, new BigDecimal(cellValue));                        continue;                    }*/                    field.set(item, cellValue); //给属性设值                }                listData.add(item);            }            myxls.close();            return listData;        } catch (Exception ex) {            try {                if (myxls != null) {                    myxls.close();                }            } catch (IOException ex2) {                log.error(ex2.getMessage(), ex2);            }            return null;        }    }}ExcelExportUtil.javaimport jxl.format.UnderlineStyle;import jxl.write.Label;import jxl.write.WritableFont;import jxl.write.WritableSheet;import jxl.write.WritableWorkbook;import lombok.extern.slf4j.Slf4j;import org.apache.commons.lang.exception.ExceptionUtils;import org.apache.commons.lang3.StringUtils;import org.springframework.web.context.request.RequestContextHolder;import org.springframework.web.context.request.ServletRequestAttributes;import java.io.File;import java.io.FileOutputStream;import java.lang.reflect.Field;import java.text.SimpleDateFormat;import java.util.Date;import java.util.HashMap;/** * Created by chl * Excel导出公共工具类 */@Slf4j@SuppressWarnings("all")public class ExcelExportUtil {    //region 根据导出信息导出Excel文件    /*        根据导出信息导出Excel文件     */    public static <T> String Export(ExcelExportInfo exportInfo) {        String filePath = File.separator + "uploadFolder" + File.separator;        if (!StringUtils.isEmpty(exportInfo.getFilePath())) {            filePath = exportInfo.getFilePath();        }        String path = ((ServletRequestAttributes) RequestContextHolder                .getRequestAttributes()).getRequest().getServletContext().getRealPath("");        String nameTimestamp = new SimpleDateFormat("yyyyMMddhhmmss")                .format(new Date());        String tmpFileName = exportInfo.getFileName() + nameTimestamp + ".xls";        try {            File pathFile = new File(path + filePath);            // 如果路径不存在，则创建路径            if (!pathFile.exists()) {                pathFile.mkdirs();            }            File excelFile = new File(path + filePath + tmpFileName);            excelFile.createNewFile();            // 打开文件            WritableWorkbook book = jxl.Workbook.createWorkbook(excelFile);            // 生成名为“第一页”的工作表，参数0表示这是第一页            WritableSheet sheet = book.createSheet(exportInfo.getSheetName(), 0);            // 文字样式            jxl.write.WritableFont wfc = new jxl.write.WritableFont(                    WritableFont.ARIAL, 12, WritableFont.BOLD, false,                    UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK);            jxl.write.WritableCellFormat wcfFC = new jxl.write.WritableCellFormat(wfc);            // 设置单元格样式            wcfFC.setBackground(jxl.format.Colour.YELLOW);// 单元格颜色            wcfFC.setAlignment(jxl.format.Alignment.CENTRE);// 单元格居中            // 加边框            wcfFC.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);            //输出标题            for (int i = 0; i < exportInfo.getTitles().length; i++) {                Label label = new Label(i, 0, exportInfo.getTitles()[i], wcfFC);                sheet.setColumnView(i, exportInfo.getTitleWidths()[i]);                sheet.addCell(label);            }            jxl.write.WritableCellFormat wcfAll = new jxl.write.WritableCellFormat();            wcfAll.setAlignment(jxl.format.Alignment.CENTRE);            wcfAll.setBorder(jxl.format.Border.ALL,                    jxl.format.BorderLineStyle.THIN);            //数据行            Field field = null;            Label tmpLabel = null;            //输出数据            for (int i = 0; i < exportInfo.getDataList().size(); i++) {                T item = (T) exportInfo.getDataList().get(i);                //数据列                for (int j = 0; j < exportInfo.getDataColumns().length; j++) {                    field = item.getClass().getDeclaredField(exportInfo.getDataColumns()[j]);                    field.setAccessible(true); // 设置些属性是可以访问的                    String columnValue = field.get(item) == null ? "" : field.get(item).toString();                    tmpLabel = new Label(j, i + 1, columnValue, wcfAll);                    sheet.addCell(tmpLabel);                }            }            // -------------------------------------------------------------            // 写入数据并关闭文件            book.write();            book.close();            return tmpFileName;        } catch (Exception e) {            log.error("exportFile exception!\n" + ExceptionUtils.getFullStackTrace(e));            return "";        }    }    //endreigon    //region 根据导出信息导出Excel文件    /*        根据导出信息导出Excel文件     */    public static <T> String ExportError(ExcelExportInfo exportInfo) {        String nameTimestamp = (new SimpleDateFormat("yyyyMMddhhmmss")).format(new Date());        String fileName = exportInfo.getFileName() + nameTimestamp + ".xls";        FileOutputStream os = null;        WritableWorkbook wwb = null;        try {            File e = new File(exportInfo.getFilePath() + fileName);            e.createNewFile();            os = new FileOutputStream(e);            wwb = jxl.Workbook.createWorkbook(os);            WritableSheet sheet = wwb.createSheet("sheet1", 0);            // 文字样式            jxl.write.WritableFont wfc = new jxl.write.WritableFont(                    WritableFont.ARIAL, 12, WritableFont.BOLD, false,                    UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK);            jxl.write.WritableCellFormat wcfFC = new jxl.write.WritableCellFormat(wfc);            // 设置单元格样式            wcfFC.setBackground(jxl.format.Colour.YELLOW);// 单元格颜色            wcfFC.setAlignment(jxl.format.Alignment.CENTRE);// 单元格居中            // 加边框            wcfFC.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);            //输出标题            Label  lableColumn = null;            for (int i = 0; i < exportInfo.getTitles().length; i++) {                lableColumn = new Label(i, 0, exportInfo.getTitles()[i]);                sheet.addCell(lableColumn);                sheet.setColumnView(i, exportInfo.getTitleWidths()[i]);            }            //数据行            Field field = null;            Label tmpLabel = null;            //输出数据            for (int i = 0; i < exportInfo.getDataList().size(); i++) {                T item = (T) exportInfo.getDataList().get(i);                //数据列                for (int j = 0; j < exportInfo.getDataColumns().length; j++) {                    field = item.getClass().getDeclaredField(exportInfo.getDataColumns()[j]);                    field.setAccessible(true); // 设置些属性是可以访问的                    String columnValue = field.get(item) == null ? "" : field.get(item).toString();                    tmpLabel = new Label(j, i + 1, columnValue);                    sheet.addCell(tmpLabel);                }            }            wwb.write();            os.flush();            return fileName;        } catch (Exception var22) {            fileName = "";        } finally {            try {                if (wwb != null) {                    wwb.close();                }                if (os != null) {                    os.close();                }            } catch (Exception ignore) {            }        }        return fileName;    }    //endreigon}