- 1
- /**
- 2 * @Description
- 3 * @author zhaomin E-mail:
- 4 * @date 创建时间:2017年2月14日 下午2:13:30
- 5 * @version 1.0
- 6 */
- 7 public class ImportExcelUtil {
- 8 final static String notnullerror = "请填入第{0}行的{1},{2}不能为空";
- 9 final static String errormsg = "第{0}行的{1}数据导入错误";
- 10 11
- /**
- 12 * 导入Excel
- 13 *
- 14 * @param clazz
- 15 * @param xls
- 16 * @return
- 17 * @throws Exception
- 18 */
- 19@SuppressWarnings("rawtypes") 20 public static List importExcel(Class clazz, InputStream xls) throws Exception {
- 21
- try {
- 22 // 取得Excel
- 23 HSSFWorkbook wb = new HSSFWorkbook(xls);
- 24 HSSFSheet sheet = wb.getSheetAt(0);
- 25 Field[] fields = clazz.getDeclaredFields();
- 26 List fieldList = new ArrayList(fields.length);
- 27
- for (Field field: fields) {
- 28
- if (field.isAnnotationPresent(ModelProp.class)) {
- 29 ModelProp modelProp = field.getAnnotation(ModelProp.class);
- 30
- if (modelProp.colIndex() != -1) {
- 31 fieldList.add(field);
- 32
- }
- 33
- }
- 34
- }
- 35 EmployeeDTO employee = new EmployeeDTO();
- 36 // 行循环
- 37 List modelList = new ArrayList(sheet.getPhysicalNumberOfRows() * 2);
- 38
- for (int i = 2; i < sheet.getPhysicalNumberOfRows(); i++) {
- 39 // 数据模型
- 40 ImportModel model = (ImportModel) clazz.newInstance();
- 41 int nullCount = 0;
- 42 Exception nullError = null;
- 43
- for (Field field: fieldList) {
- 44 ModelProp modelProp = field.getAnnotation(ModelProp.class);
- 45 HSSFCell cell = sheet.getRow(i).getCell(modelProp.colIndex());
- 46
- try {
- 47
- if (cell == null || cell.toString().length() == 0) {
- 48 nullCount++;
- 49
- if (!modelProp.nullable()) {
- 50 nullError = new Exception(StringUtil.format(notnullerror, 51 new String[] {
- "" + (1 + i),
- modelProp.name(),
- modelProp.name()
- }));
- 52 53
- }
- 54
- } else if (field.getType().equals(Date.class)) {
- 55
- if (Cell.CELL_TYPE_STRING == cell.getCellType()) {
- 56 BeanUtils.setProperty(model, field.getName(), new Date(parseDate(parseString(cell))));
- 57
- } else {
- 58 BeanUtils.setProperty(model, field.getName(), 59 new Date(cell.getDateCellValue().getTime()));
- 60 61
- }
- 62
- } else if (field.getType().equals(Timestamp.class)) {
- 63
- if (Cell.CELL_TYPE_STRING == cell.getCellType()) {
- 64 BeanUtils.setProperty(model, field.getName(), 65 new Timestamp(parseDate(parseString(cell))));
- 66
- } else {
- 67 BeanUtils.setProperty(model, field.getName(), 68 new Timestamp(cell.getDateCellValue().getTime()));
- 69
- }
- 70 71
- } else if (field.getType().equals(java.sql.Date.class)) {
- 72
- if (Cell.CELL_TYPE_STRING == cell.getCellType()) {
- 73 BeanUtils.setProperty(model, field.getName(), 74 new java.sql.Date(parseDate(parseString(cell))));
- 75
- } else {
- 76 BeanUtils.setProperty(model, field.getName(), 77 new java.sql.Date(cell.getDateCellValue().getTime()));
- 78
- }
- 79
- } else if (field.getType().equals(java.lang.Integer.class)) {
- 80
- if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) {
- 81 BeanUtils.setProperty(model, field.getName(), (int) cell.getNumericCellValue());
- 82
- } else if (Cell.CELL_TYPE_STRING == cell.getCellType()) {
- 83 BeanUtils.setProperty(model, field.getName(), Integer.parseInt(parseString(cell)));
- 84
- }
- 85
- } else if (field.getType().equals(java.math.BigDecimal.class)) {
- 86
- if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) {
- 87 BeanUtils.setProperty(model, field.getName(), 88 new BigDecimal(cell.getNumericCellValue()));
- 89
- } else if (Cell.CELL_TYPE_STRING == cell.getCellType()) {
- 90 BeanUtils.setProperty(model, field.getName(), new BigDecimal(parseString(cell)));
- 91
- }
- 92
- } else {
- 93
- if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) {
- 94 BeanUtils.setProperty(model, field.getName(), 95 new BigDecimal(cell.getNumericCellValue()));
- 96
- } else if (Cell.CELL_TYPE_STRING == cell.getCellType()) {
- 97 BeanUtils.setProperty(model, field.getName(), parseString(cell));
- 98
- }
- 99
- }
- 100
- } catch(Exception e) {
- 101 e.printStackTrace();
- 102
- throw new Exception(StringUtil.format(errormsg, new String[] {
- "" + (1 + i),
- modelProp.name()
- }) 103 + "," + e.getMessage());
- 104
- }
- 105
- }
- 106
- if (nullCount == fieldList.size()) {
- 107
- break;
- 108
- }
- 109
- if (nullError != null) {
- 110
- throw nullError;
- 111
- }
- 112 modelList.add(model);
- 113
- }
- 114
- return modelList;
- 115 116
- } finally {
- 117 xls.close();
- 118
- }
- 119
- }
- 120 121 private final static int colsizeN = 630;
- 122 private final static int colsizeM = 1000;
- 123 124
- /**
- 125 * 下载Excel模版
- 126 *
- 127 * @param clazz
- 128 * @param map
- 129 * @param rowSize
- 130 * @return
- 131 */
- 132 public static InputStream excelModelbyClass(Class clazz, Map map, Integer rowSize) {
- 133
- try {
- 134
- if (!clazz.isAnnotationPresent(ModelTitle.class)) {
- 135
- throw new Exception("请在此类型中加上ModelTitle注解");
- 136
- }
- 137
- if (rowSize == null) {
- 138 rowSize = 1000;
- 139
- }
- 140 HSSFWorkbook wb = new HSSFWorkbook();
- 141 HSSFSheet sheet = wb.createSheet();
- 142
- /**
- 143 * 设置标题样式
- 144 */
- 145 HSSFCellStyle titleStyle = wb.createCellStyle();
- 146 titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
- 147 HSSFFont font = wb.createFont();
- 148 font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
- 149 font.setFontHeight((short) 400);
- 150 titleStyle.setFont(font);
- 151 HSSFCell titleCell = sheet.createRow(0).createCell(0); // 创建第一行,并在该行创建单元格,设置内容,做为标题行
- 152
- /**
- 153 * 获取标题
- 154 */
- 155 ModelTitle modelTitle = clazz.getAnnotation(ModelTitle.class);
- 156 titleCell.setCellValue(new HSSFRichTextString(modelTitle.name()));
- 157 titleCell.setCellStyle(titleStyle);
- 158 159 Field[] fields = clazz.getDeclaredFields();
- 160 HSSFRow headRow = sheet.createRow(1);
- 161 int colSzie = 0;
- 162
- /**
- 163 * 设置表头样式
- 164 */
- 165 HSSFCellStyle headStyle = wb.createCellStyle();
- 166 headStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
- 167 HSSFFont headFont = wb.createFont();
- 168 headFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
- 169 headFont.setFontHeight((short) 240);
- 170 headStyle.setFont(headFont);
- 171 List cells = new ArrayList();
- 172 173
- for (Field field: fields) {
- 174
- if (field.isAnnotationPresent(ModelProp.class)) {
- 175 ModelProp modelProp = field.getAnnotation(ModelProp.class);
- 176
- if (modelProp.colIndex() == -1) 177
- continue;
- 178 cells.add(modelProp.colIndex());
- 179 HSSFCell cell = headRow.createCell(modelProp.colIndex());
- 180 cell.setCellValue(new HSSFRichTextString(modelProp.name()));
- 181 cell.setCellStyle(headStyle);
- 182 colSzie++;
- 183 sheet.autoSizeColumn((short) modelProp.colIndex());
- 184 sheet.setColumnWidth(modelProp.colIndex(), modelProp.name().length() * colsizeN + colsizeM);
- 185 186 // 设置列为下拉框格式
- 187
- if (map != null && map.get(new Integer(modelProp.colIndex())) != null) {
- 188 DVConstraint constraint = DVConstraint 189.createExplicitListConstraint(map.get(modelProp.colIndex()));
- 190 CellRangeAddressList regions = new CellRangeAddressList(2, rowSize, modelProp.colIndex(), 191 modelProp.colIndex());
- 192 HSSFDataValidation dataValidation = new HSSFDataValidation(regions, constraint);
- 193 sheet.addValidationData(dataValidation);
- 194
- }
- 195
- }
- 196
- }
- 197 HSSFCellStyle cellStyle = wb.createCellStyle();
- 198 HSSFDataFormat format = wb.createDataFormat();
- 199 cellStyle.setDataFormat(format.getFormat("@"));
- 200
- for (int i = 2; i < rowSize; i++) {
- 201 HSSFRow row = sheet.createRow(i);
- 202
- for (Integer integer: cells) {
- 203 HSSFCell cell = row.createCell(integer);
- 204 cell.setCellStyle(cellStyle);
- 205
- }
- 206
- }
- 207 sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, colSzie - 1));
- 208
- if (map != null) {
- 209
- for (Integer colIndex: map.keySet()) {
- 210 DVConstraint constraint = DVConstraint.createExplicitListConstraint(map.get(colIndex));
- 211 CellRangeAddressList regions = new CellRangeAddressList(2, 1000, colIndex, colIndex);
- 212 HSSFDataValidation dataValidation = new HSSFDataValidation(regions, constraint);
- 213 sheet.addValidationData(dataValidation);
- 214
- }
- 215
- }
- 216 217 ByteArrayOutputStream os = new ByteArrayOutputStream();
- 218
- try {
- 219 wb.write(os);
- 220
- } catch(IOException e) {
- 221 e.printStackTrace();
- 222
- }
- 223 224 byte[] b = os.toByteArray();
- 225 226 ByteArrayInputStream in =new ByteArrayInputStream(b);
- 227
- return in ;
- 228
- } catch(Exception e) {
- 229 e.printStackTrace();
- 230
- return null;
- 231
- }
- 232
- }
- 233 234 private static String parseString(HSSFCell cell) {
- 235
- return String.valueOf(cell).trim();
- 236
- }
- 237 238 private static long parseDate(String dateString) throws ParseException {
- 239
- if (dateString.indexOf("/") == 4) {
- 240
- return new SimpleDateFormat("yyyy/MM/dd").parse(dateString).getTime();
- 241
- } else if (dateString.indexOf("-") == 4) {
- 242
- return new SimpleDateFormat("yyyy-MM-dd").parse(dateString).getTime();
- 243
- } else if (dateString.indexOf("年") == 4) {
- 244
- return new SimpleDateFormat("yyyy年MM月dd").parse(dateString).getTime();
- 245
- } else if (dateString.length() == 8) {
- 246
- return new SimpleDateFormat("yyyyMMdd").parse(dateString).getTime();
- 247
- } else {
- 248
- return new Date().getTime();
- 249
- }
- 250
- }
- 251 252
- }
来源: http://www.bubuko.com/infodetail-1945474.html