package com.zbkj.common.excel; import org.apache.poi.hssf.usermodel.*; import org.apache.poi.hssf.util.HSSFColor; import org.apache.poi.ss.usermodel.*; import org.apache.poi.ss.util.CellRangeAddress; import org.apache.poi.ss.util.CellRangeAddressList; import org.apache.poi.xssf.usermodel.*; import java.io.IOException; import java.io.InputStream; import java.io.OutputStream; import java.lang.reflect.Field; import java.text.DecimalFormat; import java.text.SimpleDateFormat; import java.util.*; /* * ExcelUtil工具类实现功能: * 导出时传入list,即可实现导出为一个excel,其中每个对象T为Excel中的一条记录. * 导入时读取excel,得到的结果是一个list.T是自己定义的对象. * 需要导出的实体对象只需简单配置注解就能实现灵活导出,通过注解您可以方便实现下面功能: * 1.实体属性配置了注解就能导出到excel中,每个属性都对应一列. * 2.列名称可以通过注解配置. * 3.导出到哪一列可以通过注解配置. * 4.鼠标移动到该列时提示信息可以通过注解配置. * 5.用注解设置只能下拉选择不能随意填写功能. * 6.用注解设置是否只导出标题而不导出内容,这在导出内容作为模板以供用户填写时比较实用. */ public class ExcelUtil { Class clazz; private StringBuffer errorMsg = new StringBuffer(""); // 错误信息提示 private int importCount; // 导入条数 private int importError; // 导入失败条数 private int firstRow; // 导入起始行 private List successRowList = new ArrayList(); public int getFirstRow() { return firstRow; } public void setFirstRow(int firstRow) { this.firstRow = firstRow; } public List getSuccessRowList() { return successRowList; } public void setSuccessRowList(List successRowList) { this.successRowList = successRowList; } public int getImportCount() { return importCount; } public StringBuffer getErrorMsg() { return errorMsg; } public void setErrorMsg(StringBuffer errorMsg) { this.errorMsg = errorMsg; } public void setImportCount(int importCount) { this.importCount = importCount; } public int getImportError() { return importError; } public void setImportError(int importError) { this.importError = importError; } public ExcelUtil(Class clazz) { this.clazz = clazz; } public Workbook getWorkbook(InputStream input, String ext) throws IOException { Workbook wb = null; if (ext.toLowerCase().equals("xls")) { wb = new HSSFWorkbook(input); } else if (ext.toLowerCase().equals("xlsx")) { wb = new XSSFWorkbook(input); } return wb; } public List importExcel(InputStream input, String ext) throws IOException { Workbook wb = getWorkbook(input, ext); return importExcel(wb); } public List importExcelWithHeader(InputStream input, String ext) throws IOException { Workbook wb = getWorkbook(input, ext); return importExcelWithHeader(wb); } /** * Excel批量导入,数据导入起始行为第2行 * * @param * @param * @return */ @SuppressWarnings("unchecked") public List importExcel(Workbook wb) { int maxCol = 0; List list = new ArrayList(); try { Sheet sheet = wb.getSheetAt(0); int totalRows = sheet.getPhysicalNumberOfRows(); if (totalRows > 1) { importCount = totalRows - 1; List columnList = new ArrayList(); List allFields = getMappedFiled(clazz, null); Map fieldsMap = new HashMap();// 定义一个map用于存放列的序号和field for (Field field : allFields) { // 将有注解的field存放到map中. if (field.isAnnotationPresent(ExcelVOAttribute.class)) { ExcelVOAttribute attr = field.getAnnotation(ExcelVOAttribute.class); int col = getExcelCol(attr.column());// 获得列号 columnList.add(attr); maxCol = Math.max(col, maxCol); field.setAccessible(true);// 设置类的私有字段属性可访问. fieldsMap.put(col, field); } } int totalCells = columnList.size(); /** 循环Excel的行 */ for (int r = 1; r < totalRows; r++) { Row row = sheet.getRow(r); if (row == null) { continue; } T entity = null; /** 循环Excel的列 */ for (short c = 0; c < totalCells; c++) { Cell cell = row.getCell(c); if (cell == null || cell.toString() == null || "".equals(cell.toString().trim())) { // 校验上传单元格是否是必填项 ExcelVOAttribute attribute = (ExcelVOAttribute) columnList.get(c); if (attribute.isRequest()) { entity = null; this.importError++; this.errorMsg.append("【不能为空】:第" + String.valueOf((r + 1)) + "行:【" + ((ExcelVOAttribute) (columnList.get(c))).name() + "】
"); break; } continue; } entity = (entity == null ? clazz.newInstance() : entity);// 如果不存在实例则新建 Field field = fieldsMap.get(new Integer(c));// 从map中得到对应列的field. if (field == null) { continue; } // 取得类型,并根据对象类型设置值. Class fieldType = field.getType(); try { if (String.class == fieldType) { // 如果是转换成科学计算法则执行catch的内容 try { DecimalFormat df = new DecimalFormat("0"); field.set(entity, null != cell.toString() ? df.format(cell.getNumericCellValue()) : null); } catch (Exception e) { field.set(entity, null != cell.toString() ? String.valueOf(cell.toString().trim()) : null); } } else if ((Integer.TYPE == fieldType) || (Integer.class == fieldType)) { try { Double d = cell.getNumericCellValue(); field.set(entity, d.intValue()); } catch (Exception e) { throw new Exception("Integer"); } } else if ((Double.TYPE == fieldType) || (Double.class == fieldType)) { try { Double d = cell.getNumericCellValue(); field.set(entity, d); } catch (Exception e) { throw new Exception("Double"); } } else if (Date.class == fieldType) { try { Date date = cell.getDateCellValue(); field.set(entity, date); } catch (Exception e) { throw new Exception("Date"); } } } catch (Exception e) { String errmsg = e.getMessage(); if (errmsg.equals("Integer")) { errmsg = ",类型应为整数。例如:20"; } else if (errmsg.equals("Double")) { errmsg = ",类型应为小数。例如:20.12"; } else if (errmsg.equals("Date")) { errmsg = ",类型应为日期。例如:2017/01/01"; } entity = null; this.importError++; this.errorMsg.append("【格式错误】:第" + String.valueOf((r + 1)) + "行:【" + ((ExcelVOAttribute) (columnList.get(c))).name() + "】" + errmsg + "
"); break; } } if (entity != null) { list.add(entity); successRowList.add(String.valueOf(r + 1)); // 记录导入成功行号 } } } } catch (InstantiationException e) { e.printStackTrace(); } catch (IllegalAccessException e) { e.printStackTrace(); } catch (IllegalArgumentException e) { e.printStackTrace(); } return list; } /** * Excel批量导入,数据导入起始行为第3行 * * @param * @param * @return */ @SuppressWarnings("unchecked") public List importExcelWithHeader(Workbook wb) { int maxCol = 0; List list = new ArrayList(); try { Sheet sheet = wb.getSheetAt(0); int totalRows = sheet.getPhysicalNumberOfRows(); if (totalRows > 2) { importCount = totalRows - 2; List columnList = new ArrayList(); List allFields = getMappedFiled(clazz, null); Map fieldsMap = new HashMap();// 定义一个map用于存放列的序号和field for (Field field : allFields) { // 将有注解的field存放到map中. if (field.isAnnotationPresent(ExcelVOAttribute.class)) { ExcelVOAttribute attr = field.getAnnotation(ExcelVOAttribute.class); int col = getExcelCol(attr.column());// 获得列号 columnList.add(attr); maxCol = Math.max(col, maxCol); field.setAccessible(true);// 设置类的私有字段属性可访问. fieldsMap.put(col, field); } } int totalCells = columnList.size(); /** 循环Excel的行 */ for (int r = 2; r < totalRows; r++) { Row row = sheet.getRow(r); if (row == null) { continue; } T entity = null; /** 循环Excel的列 */ for (short c = 0; c < totalCells; c++) { Cell cell = row.getCell(c); if (cell == null || cell.toString() == null || "".equals(cell.toString().trim())) { // 校验上传单元格是否是必填项 ExcelVOAttribute attribute = (ExcelVOAttribute) columnList.get(c); if (attribute.isRequest()) { entity = null; this.importError++; this.errorMsg.append("【不能为空】:第" + String.valueOf((r + 1)) + "行:【" + ((ExcelVOAttribute) (columnList.get(c))).name() + "】
"); break; } continue; } entity = (entity == null ? clazz.newInstance() : entity);// 如果不存在实例则新建 Field field = fieldsMap.get(new Integer(c));// 从map中得到对应列的field. if (field == null) { continue; } // 取得类型,并根据对象类型设置值. Class fieldType = field.getType(); try { if (String.class == fieldType) { // 如果是转换成科学计算法则执行catch的内容 try { DecimalFormat df = new DecimalFormat("0"); field.set(entity, null != cell.toString() ? df.format(cell.getNumericCellValue()) : null); } catch (Exception e) { field.set(entity, null != cell.toString() ? String.valueOf(cell.toString().trim()) : null); } } else if ((Integer.TYPE == fieldType) || (Integer.class == fieldType)) { try { Double d = cell.getNumericCellValue(); field.set(entity, d.intValue()); } catch (Exception e) { throw new Exception("Integer"); } } else if ((Double.TYPE == fieldType) || (Double.class == fieldType)) { try { Double d = cell.getNumericCellValue(); field.set(entity, d); } catch (Exception e) { throw new Exception("Double"); } } else if (Date.class == fieldType) { try { Date date = cell.getDateCellValue(); field.set(entity, date); } catch (Exception e) { throw new Exception("Date"); } } } catch (Exception e) { String errmsg = e.getMessage(); if (errmsg.equals("Integer")) { errmsg = ",类型应为整数。例如:20"; } else if (errmsg.equals("Double")) { errmsg = ",类型应为小数。例如:20.12"; } else if (errmsg.equals("Date")) { errmsg = ",类型应为日期。例如:2017/01/01"; } entity = null; this.importError++; this.errorMsg.append("【格式错误】:第" + String.valueOf((r + 1)) + "行:【" + ((ExcelVOAttribute) (columnList.get(c))).name() + "】" + errmsg + "
"); break; } } if (entity != null) { list.add(entity); successRowList.add(String.valueOf(r + 1)); // 记录导入成功行号 } } } } catch (InstantiationException e) { e.printStackTrace(); } catch (IllegalAccessException e) { e.printStackTrace(); } catch (IllegalArgumentException e) { e.printStackTrace(); } return list; } /** * 将EXCEL中A,B,C,D,E列映射成0,1,2,3 * * @param col */ public static int getExcelCol(String col) { col = col.toUpperCase(); // 从-1开始计算,字母重1开始运算。这种总数下来算数正好相同。 int count = -1; char[] cs = col.toCharArray(); for (int i = 0; i < cs.length; i++) { count += (cs[i] - 64) * Math.pow(26, cs.length - 1 - i); } return count; } /** * 得到实体类所有通过注解映射了数据表的字段 * * @param * @return */ private List getMappedFiled(Class clazz, List fields) { if (fields == null) { fields = new ArrayList(); } Field[] allFields = clazz.getDeclaredFields();// 得到所有定义字段 // 得到所有field并存放到一个list中. for (Field field : allFields) { if (field.isAnnotationPresent(ExcelVOAttribute.class)) { fields.add(field); } } if (clazz.getSuperclass() != null && !clazz.getSuperclass().equals(Object.class)) { getMappedFiled(clazz.getSuperclass(), fields); } return fields; } /** * 对list数据源将其里面的数据导入到excel表单 * * @param sheetName * 工作表的名称 * @param sheetSize * 每个sheet中数据的行数,此数值必须小于65536 * @param output * java输出流 */ public boolean exportExcel(List list, String sheetName, int sheetSize, OutputStream output) { Field[] allFields = clazz.getDeclaredFields();// 得到所有定义字段 List fields = new ArrayList(); // 得到所有field并存放到一个list中. for (Field field : allFields) { if (field.isAnnotationPresent(ExcelVOAttribute.class)) { fields.add(field); } } HSSFWorkbook workbook = new HSSFWorkbook();// 产生工作薄对象 // excel2003中每个sheet中最多有65536行,为避免产生错误所以加这个逻辑. if (sheetSize > 65536 || sheetSize < 1) { sheetSize = 65536; } double sheetNo; if (list == null) { sheetNo = 0; } else { sheetNo = Math.ceil(list.size() / sheetSize);// 取出一共有多少个sheet. } for (int index = 0; index <= sheetNo; index++) { HSSFSheet sheet = workbook.createSheet();// 产生工作表对象 sheet.setDefaultColumnWidth(20); if (sheetNo == 0) { workbook.setSheetName(index, sheetName); } else { workbook.setSheetName(index, sheetName + index);// 设置工作表的名称. } HSSFRow row; HSSFCell cell;// 产生单元格 row = sheet.createRow(0);// 产生一行 // 写入各个字段的列头名称 for (int i = 0; i < fields.size(); i++) { Field field = fields.get(i); ExcelVOAttribute attr = field.getAnnotation(ExcelVOAttribute.class); int col = getExcelCol(attr.column());// 获得列号 cell = row.createCell(col);// 创建列 cell.setCellType(HSSFCell.CELL_TYPE_STRING);// 设置列中写入内容为String类型 cell.setCellValue(attr.name());// 写入列名 // 如果设置了提示信息则鼠标放上去提示. if (!attr.prompt().trim().equals("")) { setHSSFPrompt(sheet, "", attr.prompt(), 1, 100, col, col);// 这里默认设了2-101列提示. } // 如果设置了combo属性则本列只能选择不能输入 if (attr.combo().length > 0) { setHSSFValidation(sheet, attr.combo(), 1, 100, col, col);// 这里默认设了2-101列只能选择不能输入. } } if (list != null && list.size() > 0) { int startNo = index * sheetSize; int endNo = Math.min(startNo + sheetSize, list.size()); // 写入各条记录,每条记录对应excel表中的一行 for (int i = startNo; i < endNo; i++) { row = sheet.createRow(i + 1 - startNo); T vo = (T) list.get(i); // 得到导出对象. for (int j = 0; j < fields.size(); j++) { Field field = fields.get(j);// 获得field. Class fieldType = field.getType(); field.setAccessible(true);// 设置实体类私有属性可访问 ExcelVOAttribute attr = field.getAnnotation(ExcelVOAttribute.class); try { // 根据ExcelVOAttribute中设置情况决定是否导出,有些情况需要保持为空,希望用户填写这一列. if (attr.isExport()) { cell = row.createCell(getExcelCol(attr.column()));// 创建cell if (String.class == fieldType) { cell.setCellType(HSSFCell.CELL_TYPE_STRING);// 设置列中写入内容为String类型 cell.setCellValue(field.get(vo) == null ? "" : String.valueOf(field.get(vo))); } else if ((Integer.TYPE == fieldType) || (Integer.class == fieldType)) { if (field.get(vo) != null) { cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);// 设置列中写入内容为Integer类型 cell.setCellValue(Integer.valueOf(field.get(vo).toString())); } } else if ((Double.TYPE == fieldType) || (Double.class == fieldType)) { if (field.get(vo) != null) { cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);// 设置列中写入内容为Double类型 cell.setCellValue(Double.valueOf(field.get(vo).toString())); } } else if (Date.class == fieldType) { if (field.get(vo) != null) { SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd"); // HSSFCellStyle cellStyle = // workbook.createCellStyle(); // HSSFDataFormat format = // workbook.createDataFormat(); // cellStyle.setDataFormat(format.getFormat("yyyy/mm/dd")); // cell.setCellStyle(cellStyle); // cell.setCellValue(formatter.parse(field.get(vo).toString())); cell.setCellType(HSSFCell.CELL_TYPE_STRING);// 设置列中写入内容为String类型 cell.setCellValue(field.get(vo) == null ? "" : formatter.format(field.get(vo))); } } } } catch (Exception e) { e.printStackTrace(); } } } } } try { output.flush(); workbook.write(output); output.close(); return true; } catch (Exception e) { e.printStackTrace(); System.out.println("Output is closed "); return false; } finally { if (output != null) { try { output.close(); } catch (IOException e) { } } } } /** * 对list数据源将其里面的数据导入到带表头的excel表单 * * @param sheetName * 工作表的名称 * @param sheetSize * 每个sheet中数据的行数,此数值必须小于65536 * @param output * java输出流 * @param headerHeight * 表头高度 * @param headerTitle * 表头标题 */ public boolean exportExcelWithHeader(List list, String sheetName, int sheetSize, OutputStream output, int headerHeight, String headerTitle) { Field[] allFields = clazz.getDeclaredFields();// 得到所有定义字段 List fields = new ArrayList(); // 得到所有field并存放到一个list中. for (Field field : allFields) { if (field.isAnnotationPresent(ExcelVOAttribute.class)) { fields.add(field); } } HSSFWorkbook workbook = new HSSFWorkbook();// 产生工作薄对象 // excel2003中每个sheet中最多有65536行,为避免产生错误所以加这个逻辑. if (sheetSize > 65536 || sheetSize < 1) { sheetSize = 65536; } double sheetNo; if (list == null) { sheetNo = 0; } else { sheetNo = Math.ceil(list.size() / sheetSize);// 取出一共有多少个sheet. } for (int index = 0; index <= sheetNo; index++) { HSSFSheet sheet = workbook.createSheet();// 产生工作表对象 sheet.setDefaultColumnWidth(20); if (sheetNo == 0) { workbook.setSheetName(index, sheetName); } else { workbook.setSheetName(index, sheetName + index);// 设置工作表的名称. } HSSFCellStyle cellStyle = workbook.createCellStyle(); cellStyle.setAlignment(HorizontalAlignment.LEFT); cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); cellStyle.setWrapText(true); HSSFFont font = workbook.createFont(); font.setFontHeightInPoints((short) 11); font.setColor(HSSFColor.BLACK.index); font.setFontName("黑体"); cellStyle.setFont(font); HSSFRow row; HSSFCell cell;// 产生单元格 row = sheet.createRow(0);// 产生一行 row.setHeight((short) (headerHeight)); sheet.addMergedRegion(new CellRangeAddress(0, (short) 0, 0, (short) (fields.size() - 1))); HSSFCell titleCell = row.createCell(0); titleCell.setCellValue(headerTitle); titleCell.setCellStyle(cellStyle); row = sheet.createRow(1); // 写入各个字段的列头名称 for (int i = 0; i < fields.size(); i++) { Field field = fields.get(i); ExcelVOAttribute attr = field.getAnnotation(ExcelVOAttribute.class); int col = getExcelCol(attr.column());// 获得列号 cell = row.createCell(col);// 创建列 cell.setCellType(HSSFCell.CELL_TYPE_STRING);// 设置列中写入内容为String类型 cell.setCellValue(attr.name());// 写入列名 // 如果设置了提示信息则鼠标放上去提示. if (!attr.prompt().trim().equals("")) { setHSSFPrompt(sheet, "", attr.prompt(), 1, 100, col, col);// 这里默认设了2-101列提示. } // 如果设置了combo属性则本列只能选择不能输入 if (attr.combo().length > 0) { setHSSFValidation(sheet, attr.combo(), 1, 100, col, col);// 这里默认设了2-101列只能选择不能输入. } } if (list != null && list.size() > 0) { int startNo = index * sheetSize; int endNo = Math.min(startNo + sheetSize, list.size()); // 写入各条记录,每条记录对应excel表中的一行 for (int i = startNo; i < endNo; i++) { row = sheet.createRow(i + 2 - startNo); T vo = (T) list.get(i); // 得到导出对象. for (int j = 0; j < fields.size(); j++) { Field field = fields.get(j);// 获得field. Class fieldType = field.getType(); field.setAccessible(true);// 设置实体类私有属性可访问 ExcelVOAttribute attr = field.getAnnotation(ExcelVOAttribute.class); try { // 根据ExcelVOAttribute中设置情况决定是否导出,有些情况需要保持为空,希望用户填写这一列. if (attr.isExport()) { cell = row.createCell(getExcelCol(attr.column()));// 创建cell if (String.class == fieldType) { cell.setCellType(HSSFCell.CELL_TYPE_STRING);// 设置列中写入内容为String类型 cell.setCellValue(field.get(vo) == null ? "" : String.valueOf(field.get(vo))); } else if ((Integer.TYPE == fieldType) || (Integer.class == fieldType)) { if (field.get(vo) != null) { cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);// 设置列中写入内容为Integer类型 cell.setCellValue(Integer.valueOf(field.get(vo).toString())); } } else if ((Double.TYPE == fieldType) || (Double.class == fieldType)) { if (field.get(vo) != null) { cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);// 设置列中写入内容为Double类型 cell.setCellValue(Double.valueOf(field.get(vo).toString())); } } else if (Date.class == fieldType) { if (field.get(vo) != null) { SimpleDateFormat formatter = new SimpleDateFormat("EEE MMM dd HH:mm:ss z yyyy", Locale.ENGLISH); cellStyle = workbook.createCellStyle(); HSSFDataFormat format = workbook.createDataFormat(); cellStyle.setDataFormat(format.getFormat("yyyy/mm/dd")); cell.setCellStyle(cellStyle); cell.setCellValue(formatter.parse(field.get(vo).toString())); } } } } catch (Exception e) { e.printStackTrace(); } } } } } try { output.flush(); workbook.write(output); output.close(); return true; } catch (IOException e) { e.printStackTrace(); System.out.println("Output is closed "); return false; } } /** * 设置单元格上提示 * * @param sheet * 要设置的sheet. * @param promptTitle * 标题 * @param promptContent * 内容 * @param firstRow * 开始行 * @param endRow * 结束行 * @param firstCol * 开始列 * @param endCol * 结束列 * @return 设置好的sheet. */ public static HSSFSheet setHSSFPrompt(HSSFSheet sheet, String promptTitle, String promptContent, int firstRow, int endRow, int firstCol, int endCol) { // 构造constraint对象 DVConstraint constraint = DVConstraint.createCustomFormulaConstraint("DD1"); // 四个参数分别是:起始行、终止行、起始列、终止列 CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol); // 数据有效性对象 HSSFDataValidation data_validation_view = new HSSFDataValidation(regions, constraint); data_validation_view.createPromptBox(promptTitle, promptContent); sheet.addValidationData(data_validation_view); return sheet; } /** * 设置某些列的值只能输入预制的数据,显示下拉框. * * @param sheet * 要设置的sheet. * @param textlist * 下拉框显示的内容 * @param firstRow * 开始行 * @param endRow * 结束行 * @param firstCol * 开始列 * @param endCol * 结束列 * @return 设置好的sheet. */ public static HSSFSheet setHSSFValidation(HSSFSheet sheet, String[] textlist, int firstRow, int endRow, int firstCol, int endCol) { // 加载下拉列表内容 DVConstraint constraint = DVConstraint.createExplicitListConstraint(textlist); // 设置数据有效性加载在哪个单元格上,四个参数分别是:起始行、终止行、起始列、终止列 CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol); // 数据有效性对象 HSSFDataValidation data_validation_list = new HSSFDataValidation(regions, constraint); sheet.addValidationData(data_validation_list); return sheet; } /*** * excel 字段必录样式 * * @param wb * @return */ public static CellStyle getNecessaryTitleStyle(HSSFWorkbook wb) { CellStyle style = wb.createCellStyle(); HSSFFont font = wb.createFont(); font.setFontName("黑体"); font.setFontHeightInPoints((short) 12); font.setBold(true); font.setColor(HSSFColor.RED.index); style.setFont(font); style.setAlignment(HorizontalAlignment.CENTER);// 水平居中 style.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中 HSSFDataFormat format = wb.createDataFormat(); style.setDataFormat(format.getFormat("@")); return style; } /*** * excel 字段必录样式 2007 XLSX * * @param wb * @return */ public static CellStyle getNecessaryTitleStyle2007(XSSFWorkbook wb) { CellStyle style = wb.createCellStyle(); XSSFFont font = wb.createFont(); font.setFontName("黑体"); font.setFontHeightInPoints((short) 12); font.setBold(true); font.setColor(HSSFColor.RED.index); style.setFont(font); style.setAlignment(HorizontalAlignment.CENTER);// 水平居中 style.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中 XSSFDataFormat format = wb.createDataFormat(); style.setDataFormat(format.getFormat("@")); return style; } /*** * excel 普通样式 2007 xlxs * * @param wb * @return */ public static CellStyle getCommonTitleStyle2007(XSSFWorkbook wb) { CellStyle style = wb.createCellStyle(); XSSFFont font = wb.createFont(); font.setFontName("黑体"); font.setFontHeightInPoints((short) 11); font.setBold(true); font.setColor(HSSFColor.BLACK.index); style.setFont(font); style.setAlignment(HorizontalAlignment.CENTER);// 水平居中 style.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中 XSSFDataFormat format = wb.createDataFormat(); style.setDataFormat(format.getFormat("@")); return style; } /*** * excel 普通样式 * * @param wb * @return */ public static CellStyle getCommonTitleStyle(HSSFWorkbook wb) { CellStyle style = wb.createCellStyle(); HSSFFont font = wb.createFont(); font.setFontName("黑体"); font.setFontHeightInPoints((short) 11); font.setBold(true); font.setColor(HSSFColor.BLACK.index); style.setFont(font); style.setAlignment(HorizontalAlignment.CENTER);// 水平居中 style.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中 HSSFDataFormat format = wb.createDataFormat(); style.setDataFormat(format.getFormat("@")); return style; } /*** * excel 文本样式 * * @param wb * @return */ public static CellStyle getStringStyle(HSSFWorkbook wb) { CellStyle style = wb.createCellStyle(); HSSFDataFormat format = wb.createDataFormat(); style.setAlignment(HorizontalAlignment.CENTER);// 水平居中 style.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中 style.setDataFormat(format.getFormat("@")); return style; } /*** * excel 文本样式 2007 XLSX * * @param wb * @return */ public static CellStyle getStringStyle2007(XSSFWorkbook wb) { CellStyle style = wb.createCellStyle(); XSSFDataFormat format = wb.createDataFormat(); style.setAlignment(HorizontalAlignment.CENTER);// 水平居中 style.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中 style.setDataFormat(format.getFormat("@")); return style; } /*** * excel 文本橙色样式 * * @param wb * @return */ public static CellStyle getOrangeStyle(HSSFWorkbook wb) { CellStyle style = wb.createCellStyle(); HSSFDataFormat format = wb.createDataFormat(); HSSFFont font = wb.createFont(); font.setColor(IndexedColors.ORANGE.index); font.setFontHeightInPoints((short) 11); font.setFontName("黑体"); style.setFont(font); style.setAlignment(HorizontalAlignment.CENTER);// 水平居中 style.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中 style.setDataFormat(format.getFormat("@")); return style; } /*** * excel 获取颜色样式 * * @param wb * @return */ public static CellStyle getColorStyle(HSSFWorkbook wb,short color) { CellStyle style = wb.createCellStyle(); HSSFDataFormat format = wb.createDataFormat(); HSSFFont font = wb.createFont(); font.setColor(color); font.setFontHeightInPoints((short) 11); font.setFontName("黑体"); style.setFont(font); style.setAlignment(HorizontalAlignment.CENTER);// 水平居中 style.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直居中 style.setDataFormat(format.getFormat("@")); return style; } public static HSSFDataValidation setDataValidation(String strFormula, int firstRow, int endRow, int firstCol, int endCol) { // 设置数据有效性加载在哪个单元格上。四个参数分别是:起始行、终止行、起始列、终止列 CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol); DVConstraint constraint = DVConstraint.createFormulaListConstraint(strFormula); HSSFDataValidation dataValidation = new HSSFDataValidation(regions, constraint); dataValidation.createErrorBox("Error", "Error"); dataValidation.createPromptBox("", null); return dataValidation; } //将提示信息和提示内容重载,并改为XLSX OFFICE2007 public static XSSFDataValidation setDataValidation2007(XSSFSheet sheet,String strFormula, int firstRow, int endRow, int firstCol, int endCol, String tipTitle, String tipText) { // 设置数据有效性加载在哪个单元格上。四个参数分别是:起始行、终止行、起始列、终止列 XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet); XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper .createFormulaListConstraint(strFormula); CellRangeAddressList addressList = new CellRangeAddressList(firstRow, endRow, firstCol, endCol); XSSFDataValidation validation = (XSSFDataValidation) dvHelper.createValidation(dvConstraint, addressList); validation.setSuppressDropDownArrow(true); validation.setShowErrorBox(true); validation.createErrorBox(tipTitle, tipText); validation.createPromptBox("", null); return validation; } /** * excel获取列值 * * @param cell * @return by llc 2018-09-17 */ public static String getCellValue(Cell cell) { String cellValue = ""; DataFormatter formatter = new DataFormatter(); if (cell != null) { switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { cellValue = formatter.formatCellValue(cell); } else { double value = cell.getNumericCellValue(); int intValue = (int) value; cellValue = value - intValue == 0 ? String.valueOf(intValue) : String.valueOf(value); } break; case Cell.CELL_TYPE_STRING: cellValue = cell.getStringCellValue(); break; case Cell.CELL_TYPE_BOOLEAN: cellValue = String.valueOf(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_FORMULA: cellValue = String.valueOf(cell.getCellFormula()); break; case Cell.CELL_TYPE_BLANK: cellValue = ""; break; case Cell.CELL_TYPE_ERROR: cellValue = ""; break; default: cellValue = cell.toString().trim(); break; } } return cellValue.trim(); } }