2018/7/12 23:00:18当前位置媒体热门新闻热点浏览文章

Excel导出

import java.io.FileOutputStream;import java.io.IOException;import java.lang.reflect.Field;import java.lang.reflect.InvocationTargetException;import java.lang.reflect.Method;import java.text.SimpleDateFormat;import java.util.Collection;import java.util.Date;import java.util.Iterator;import java.util.regex.Matcher;import java.util.regex.Pattern;import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFCellStyle;import org.apache.poi.hssf.usermodel.HSSFFont;import org.apache.poi.hssf.usermodel.HSSFRichTextString;import org.apache.poi.hssf.usermodel.HSSFRow;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.hssf.util.HSSFColor;/  Excel工具类  注意T的字段命名需要符合驼峰命名   @author Archie Lin  @date 2018-06-27 /public class ExcelUtil<T> {    /      时间格式     /    private String TIME_PATTERN = "yyyy-MM-dd hh:mm:ss";    /      默认列的宽度     /    private int COLUMN_WIDTH = 20;    /      表头字体大小     /    private int TITLE_FONT_SIZE = 11;    /      表头单元格     /    private int TITLE_CELL = 0;    /      内容单元格     /    private int CONTENT_CELL = 1;    /      导出      2003版本的xls           @param title       表格标题名      @param headers     表格头部标题集合      @param headerWords 表格头部标题的字段名      @param filePath    文件的绝对路径      @param dataSet     需要显示的数据集合     /    @SuppressWarnings({"unchecked", "rawtypes"})    public String exportExcel(String title, String[] headers, String[] headerWords, Collection<T> dataSet, String            filePath) {        // 公告一个工作薄        HSSFWorkbook workbook = new HSSFWorkbook();        // 生成一个sheet        HSSFSheet sheet = workbook.createSheet(title);        // 设置表格默认列宽度        sheet.setDefaultColumnWidth(COLUMN_WIDTH);        // 生成标题样式        HSSFCellStyle titleStyle = setCellStyle(workbook, TITLE_CELL);        // 产生表格标题行        HSSFRow row = sheet.createRow(0);        HSSFCell cellHeader;        for (int i = 0; i < headers.length; i++) {            cellHeader = row.createCell(i);            cellHeader.setCellStyle(titleStyle);            cellHeader.setCellValue(new HSSFRichTextString(headers[i]));        }        //填充内容        fillContent(headerWords, dataSet, workbook, sheet);        File file = new File(filePath);        if (!CommonController.isHasFolder(file)) {            CommonController.createFolder(file);        }        //文件的路劲        StringBuffer fileUrl = new StringBuffer();        //文件名称        String fileName = TimeUtils.getTimeStamp() + ".xls";        fileUrl.append(filePath);        fileUrl.append(fileName);        String fileUrlStr = fileUrl.toString();        FileOutputStream fileOutputStream = null;        try {            fileOutputStream = new FileOutputStream(new File(fileUrlStr));            workbook.write(fileOutputStream);        } catch (IOException e) {            e.printStackTrace();        } finally {            try {                if (null != fileOutputStream) {                    fileOutputStream.close();                }            } catch (IOException e) {                e.printStackTrace();            }        }        return fileName;    }    /      设置样式     /    private HSSFCellStyle setCellStyle(HSSFWorkbook workbook, int cellType) {        HSSFCellStyle style = null;        if (cellType == TITLE_CELL) {            style = workbook.createCellStyle();            style.setFillForegroundColor(HSSFColor.GREY_50_PERCENT.index);            style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);            style.setBorderBottom(HSSFCellStyle.BORDER_THIN);            style.setBorderLeft(HSSFCellStyle.BORDER_THIN);            style.setBorderRight(HSSFCellStyle.BORDER_THIN);            style.setBorderTop(HSSFCellStyle.BORDER_THIN);            style.setAlignment(HSSFCellStyle.ALIGN_CENTER);            style.setTopBorderColor(HSSFColor.BLACK.index);            style.setLeftBorderColor(HSSFColor.BLACK.index);            style.setRightBorderColor(HSSFColor.BLACK.index);            style.setBottomBorderColor(HSSFColor.BLACK.index);            // 生成标题字体            HSSFFont font = workbook.createFont();            font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);            font.setFontName("宋体");            font.setColor(HSSFColor.WHITE.index);            font.setFontHeightInPoints((short) TITLE_FONT_SIZE);            // 把字体应使用到当前的样式            style.setFont(font);        } else if (cellType == CONTENT_CELL) {            style = workbook.createCellStyle();            style.setFillForegroundColor(HSSFColor.WHITE.index);            style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);            style.setBorderBottom(HSSFCellStyle.BORDER_THIN);            style.setBorderLeft(HSSFCellStyle.BORDER_THIN);            style.setBorderRight(HSSFCellStyle.BORDER_THIN);            style.setBorderTop(HSSFCellStyle.BORDER_THIN);            style.setAlignment(HSSFCellStyle.ALIGN_CENTER);            style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);            // 生成内容字体            HSSFFont font = workbook.createFont();            font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);            style.setFont(font);        }        return style;    }    /      填充sheet内容     /    private void fillContent(String[] headerWords, Collection<T> dataSet, HSSFWorkbook workbook, HSSFSheet sheet) {        // 内容样式        HSSFCellStyle contentStyle = setCellStyle(workbook, CONTENT_CELL);        Iterator<T> it = dataSet.iterator();        int index = 0;        T t;        HSSFRichTextString richString;        //字段名        String fieldName;        //get方法名称        String getMethodName;        //单元格        HSSFCell cell;        Class tCls;        Method getMethod;        Object value;        //单元格的文本        String textValue;        HSSFRow row;        SimpleDateFormat sdf = new SimpleDateFormat(TIME_PATTERN);        while (it.hasNext()) {            index++;            row = sheet.createRow(index);            t = (T) it.next();            for (int i = 0; i < headerWords.length; i++) {                cell = row.createCell(i);                cell.setCellStyle(contentStyle);                fieldName = headerWords[i];                getMethodName = "get" + fieldName.substring(0, 1).toUpperCase()                        + fieldName.substring(1);                try {                    tCls = t.getClass();                    getMethod = tCls.getMethod(getMethodName, new Class[]{});                    value = getMethod.invoke(t, new Object[]{});                    // 判断值的类型后进行强制类型转换                    textValue = null;                    if (value instanceof Integer) {                        cell.setCellValue((Integer) value);                    } else if (value instanceof Float) {                        textValue = String.valueOf((Float) value);                        cell.setCellValue(textValue);                    } else if (value instanceof Double) {                        textValue = String.valueOf((Double) value);                        cell.setCellValue(textValue);                    } else if (value instanceof Long) {                        cell.setCellValue((Long) value);                    }                    if (value instanceof Boolean) {                        textValue = "是";                        if (!(Boolean) value) {                            textValue = "否";                        }                    } else if (value instanceof Date) {                        textValue = sdf.format((Date) value);                    } else {                        // 其它数据类型都当作字符串简单解决                        if (value != null) {                            textValue = value.toString();                        }                    }                    if (textValue != null) {                        richString = new HSSFRichTextString(textValue);                        cell.setCellValue(richString);                    }                } catch (SecurityException e) {                    e.printStackTrace();                } catch (NoSuchMethodException e) {                    e.printStackTrace();                } catch (IllegalAccessException e) {                    e.printStackTrace();                } catch (InvocationTargetException e) {                    e.printStackTrace();                }            }        }    }}
网友评论