神刀安全网

Excel工具类

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

转载本站任何文章请注明:转载至神刀安全网,谢谢神刀安全网 » Excel工具类

分享到:更多 ()