Java tutorial
package com.lw.common.utils; import java.beans.PropertyDescriptor; import java.io.*; import java.lang.reflect.Field; import java.lang.reflect.Method; import java.math.BigDecimal; import java.util.ArrayList; import java.util.HashMap; import java.util.Iterator; import java.util.List; import java.util.Map; import javax.servlet.http.HttpServletResponse; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CellStyle; import org.apache.poi.ss.usermodel.Font; import org.apache.poi.ss.usermodel.IndexedColors; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Sheet; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.xssf.usermodel.XSSFWorkbook; /** * Excel * */ @SuppressWarnings("serial") public class ExcelUtil { private final static String SUFFIX_XLS = ".xls"; private final static String SUFFIX_XLSX = ".xlsx"; // public final static String RESIDENCE_MODEL = FileUtil.fileResourcePath() + FileUtil.FILE_SEPARATOR+ "excelmodel" // + FileUtil.FILE_SEPARATOR + "residenceModel.xlsx"; // public final static String ENTERPRISE_MODEL = FileUtil.fileResourcePath() + FileUtil.FILE_SEPARATOR + "excelmodel" // + FileUtil.FILE_SEPARATOR + "enterpriseModel.xlsx"; // public final static String PERSON_MODEL = FileUtil.fileResourcePath() + FileUtil.FILE_SEPARATOR+ "excelmodel" // + FileUtil.FILE_SEPARATOR + "personModel.xlsx"; // // public final static String CORRECTION_MODEL = FileUtil.fileResourcePath() + FileUtil.FILE_SEPARATOR+ "excelmodel" // + FileUtil.FILE_SEPARATOR + "correctionModel.xlsx"; // public final static String DRUG_MODEL = FileUtil.fileResourcePath() + FileUtil.FILE_SEPARATOR+ "excelmodel" // + FileUtil.FILE_SEPARATOR + "drugModel.xlsx"; // public final static String COMMUNITY_MODEL = FileUtil.fileResourcePath() + FileUtil.FILE_SEPARATOR+ "excelmodel" // + FileUtil.FILE_SEPARATOR + "communityModel.xlsx"; // public final static String PSYCHOSIS_MODEL = FileUtil.fileResourcePath() + FileUtil.FILE_SEPARATOR+ "excelmodel" // + FileUtil.FILE_SEPARATOR + "psychosisModel.xlsx"; // public final static String FOCUSTEENAGER_MODEL = FileUtil.fileResourcePath() + FileUtil.FILE_SEPARATOR+ "excelmodel" // + FileUtil.FILE_SEPARATOR + "focusteenagerModel.xlsx"; // public final static String FOCUSPETITION_MODEL = FileUtil.fileResourcePath() + FileUtil.FILE_SEPARATOR+ "excelmodel" // + FileUtil.FILE_SEPARATOR + "focuspetitionModel.xlsx"; // public final static String CULT_MODEL = FileUtil.fileResourcePath() + FileUtil.FILE_SEPARATOR+ "excelmodel" // + FileUtil.FILE_SEPARATOR + "cultModel.xlsx"; /** * ???map */ public final static Map<String, String> ResidenceMap = new HashMap<String, String>() { { put("??", "id"); put("?", "townshipName"); put("", "communityName"); put("", "gridName"); put("?", "address"); put("???", "contactName"); put("??", "contactIdcard"); put("??", "contactPhone"); put("??", "householderName"); put("?", "householderIdcard"); put("??", "householderPhone"); put("?", "isRentingHouseName"); put("??", "isDangerousName"); put("?", "dangerousLevel"); } }; /** * ???map */ public final static Map<String, String> UserMap = new HashMap<String, String>() { { put("??", "id"); put("", "nickName"); put("", "gender"); put("?", "avatar"); put("", "note"); put("", "completeness"); } }; public final static Map<String, String> EnterpriseMap = new HashMap<String, String>() { { put("??", "id"); put("?", "townshipName"); put("", "communityName"); put("", "gridName"); put("???", "name"); put("?", "categoryName"); put("?", "licenseNo"); put("?", "organizationNo"); put("?", "registAddress"); put("???", "phone"); put("?", "fax"); put("", "employeeNum"); put("??", "legalPersonName"); put("??", "legalPersonIdcard"); put("??", "legalPersonPhone"); put("?", "guardName"); put("?", "safetyChargeName"); put("??", "businessScope"); put("???", "isChemistryName"); put("??", "chemistrySituation"); put("?", "hiddenDangerCategoryName"); put("???", "hiddenDangerSituation"); put("", "attentionDegreeName"); } }; public final static Map<String, String> PersonMap = new HashMap<String, String>() { { put("??", "id"); put("?", "townshipName"); put("", "communityName"); put("", "gridName"); put("??", "name"); put("?", "certificateTypeName"); put("???", "certificateNo"); put("?", "categoryName"); put("", "sexName"); put("?", "nationName"); put("", "birthDateTemp"); put("?", "currentAddress"); put("", "householderRelationName"); put("?", "nativePlaceName"); put("??", "phone"); put("?", "jobName"); put("??", "company"); put("?", "politicalStatusName"); put("", "maritalStatusName"); put("?", "healthStatusName"); } }; public final static Map<String, String> CorrectionMap = new HashMap<String, String>() { { put("??", "id"); put("?", "townshipName"); put("", "communityName"); put("", "gridName"); put("??", "name"); put("??", "certificateNo"); put("", "correctionTypeName"); put("??", "crimeName"); put("", "originalPenaltyTerm"); put("", "originalPenaltyBeginTmp"); put("", "originalPenaltyEndTmp"); put("", "correctionTerm"); put("", "correctionBeginTmp"); put("", "correctionEndTmp"); put("?", "correctionNo"); put("??", "managerName"); put("?", "managerPhone"); put("", "manageLevelName"); put("?", "mainCriminalFacts"); } }; public final static Map<String, String> DrugMap = new HashMap<String, String>() { { put("??", "id"); put("?", "townshipName"); put("", "communityName"); put("", "gridName"); put("??", "name"); put("??", "certificateNo"); put("", "drugTreatmentSituationName"); put("?", "drugReasonName"); put("???", "drugSourceName"); put("??", "drugStatusName"); put("??", "managerName"); put("?", "managerPhone"); put("", "manageLevelName"); } }; public final static Map<String, String> CommunitySentenceMap = new HashMap<String, String>() { { put("??", "id"); put("?", "townshipName"); put("", "communityName"); put("", "gridName"); put("??", "name"); put("??", "certificateNo"); put("??", "crime"); put("", "originalPenaltyTerm"); put("", "originalPenaltyBeginTmp"); put("", "originalPenaltyEndTmp"); put("", "correctionTerm"); put("", "correctionBeginTmp"); put("", "correctionEndTmp"); put("?", "correctionNo"); put("??", "managerName"); put("?", "managerPhone"); put("", "manageLevelName"); put("?", "mainCriminalFacts"); } }; public final static Map<String, String> PsychosisMap = new HashMap<String, String>() { { put("??", "id"); put("?", "townshipName"); put("", "communityName"); put("", "gridName"); put("??", "name"); put("??", "certificateNo"); put("", "psychosisTypeName"); put("?", "dangerLevelName"); put("??", "sicknessName"); put("", "treatmentHospital"); put("??", "managerName"); put("?", "managerPhone"); put("", "manageLevelName"); put("", "diagnosisResult"); put("", "treatmentResult"); } }; public final static Map<String, String> FocusTeenagerMap = new HashMap<String, String>() { { put("??", "id"); put("?", "townshipName"); put("", "communityName"); put("", "gridName"); put("??", "name"); put("??", "certificateNo"); put("?", "teenagerTypeName"); put("?", "isCrimeName"); put("??", "helperName"); put("?", "helperPhone"); put("??", "managerName"); put("?", "managerPhone"); put("", "focusLevelName"); put("", "homeSituation"); } }; public final static Map<String, String> FocusPetitionMap = new HashMap<String, String>() { { put("??", "id"); put("?", "townshipName"); put("", "communityName"); put("", "gridName"); put("??", "name"); put("??", "certificateNo"); put("", "petitionTypeName"); put("", "petitionDescription"); put("", "petitionResult"); put("??", "managerName"); put("?", "managerPhone"); put("", "focusLevelName"); } }; public final static Map<String, String> CultMap = new HashMap<String, String>() { { put("??", "id"); put("?", "townshipName"); put("", "communityName"); put("", "gridName"); put("??", "name"); put("??", "certificateNo"); put("", "cultTypeName"); put("", "startTimeTmp"); put("?", "endTimeTmp"); put("?", "introducer"); put("??", "managerName"); put("?", "managerPhone"); put("", "manageLevelName"); } }; /** * ??? * @param <T> * @throws Exception */ public <T> void parseImportFile(InputStream inputStream, String fileName, List<T> modelList, Class<T> modelClass, Map<String, String> columnMap) throws Exception { Workbook workbook = null; // ????Workbook??HSSFWorkbookXSSFWorkbook if (fileName.endsWith(SUFFIX_XLS)) { workbook = new HSSFWorkbook(inputStream); } else if (fileName.endsWith(SUFFIX_XLSX)) { workbook = new XSSFWorkbook(inputStream); } Sheet sheet = workbook.getSheetAt(0); if (sheet != null) { parseSheet(sheet, modelList, modelClass, columnMap); } } private <T> void parseSheet(Sheet sheet, List<T> modelList, Class<T> modelClass, Map<String, String> columnMap) throws Exception { Row row; int count = 0; List<Method> methods = null; // ??model Iterator<Row> iterator = sheet.iterator(); while (iterator.hasNext()) { row = iterator.next(); if (count == 0) { // ?? methods = new ArrayList<>(); parseRowAndFindMethod(row, methods, modelClass, columnMap); } else { // ?? parseRowAndFillData(row, modelList, methods, modelClass); } count++; } } private <T> void parseRowAndFindMethod(Row row, List<Method> methods, Class<T> modelClass, Map<String, String> columnMap) throws Exception { List<String> rst = parseTitleRow(row); String methodName; for (Object str : rst) { String fieldName = columnMap.get(str); methodName = "set" + upperFirstLetter(fieldName); //Field field = modelClass.getDeclaredField(fieldName); // ?? //methods.add(modelClass.getDeclaredMethod(methodName, field.getType())); // ?????set // Field field = modelClass.getField(fieldName); // ?? Field field = modelClass.getDeclaredField(fieldName); methods.add(modelClass.getMethod(methodName, field.getType())); // ?????set } } private <T> void parseRowAndFillData(Row row, List<T> modelList, List<Method> methods, Class<T> modelClass) throws Exception { // ? if (row.getCell(0) == null) { return; } List<String> rst = parseDataRow(row, methods.size()); T obj = (T) modelClass.newInstance(); // ??Excel?????? if (methods.size() != rst.size()) { throw new Exception("Excel?"); } else { for (int i = 0; i < methods.size(); i++) { Method m = methods.get(i); String str = rst.get(i); if (Utils.judgeStr(str)) { // null""?? Class<?>[] paramTypes = m.getParameterTypes(); String typeName = paramTypes[0].getName(); // ??rst? switch (typeName) { case "java.math.BigDecimal": m.invoke(obj, new BigDecimal(str)); break; case "java.lang.Integer": m.invoke(obj, new Integer(str)); break; case "java.lang.Byte": m.invoke(obj, new Byte(str)); break; default: m.invoke(obj, str); break; } } } modelList.add(obj); } } private List<String> parseTitleRow(Row row) { List<String> rst = new ArrayList<>(); Cell cell; Iterator<Cell> iterator = row.iterator(); while (iterator.hasNext()) { cell = iterator.next(); cell.setCellType(Cell.CELL_TYPE_STRING); rst.add(cell.getStringCellValue()); } return rst; } private List<String> parseDataRow(Row row, int size) { List<String> rst = new ArrayList<>(); Cell cell; for (int i = 0; i < size; i++) { cell = row.getCell(i, Row.RETURN_BLANK_AS_NULL); if (cell == null) { rst.add(""); } else { cell.setCellType(Cell.CELL_TYPE_STRING); rst.add(cell.getStringCellValue().trim()); } } return rst; } /** * ? * @param str * @return */ private String upperFirstLetter(String str) { String firstLetter = "" + str.charAt(0); return firstLetter.toUpperCase() + str.substring(1); } // public static void main(String[] args) throws Exception { // ResidenceInfo info1 = new ResidenceInfo(); // info1.setAddress("address5"); // ResidenceInfo info2 = new ResidenceInfo(); // info2.setAddress("address6"); // List<ResidenceInfo> list = new ArrayList<>(); // list.add(info1); // list.add(info2); // // String modelPath = "D:\\zy7037\\Desktop\\excel\\??.xlsx"; // Workbook book = new ExcelUtil().batchImportFailList(modelPath, list, ResidenceInfo.class, ResidenceMap); // //? // OutputStream stream = new FileOutputStream("D:\\zy7037\\Desktop\\excel\\test.xlsx"); // //? // book.write(stream); // //? // stream.close(); // } //?excel public <T> void returnFailImportExcel(HttpServletResponse response, String modelPath, List<T> objectList, Class<T> modelClass, Map<String, String> columnMap) { // result.setData("D:\\lw7068\\Desktop\\ (4)\\??.xlsx"); // result.setMessage("shibai",0); // return result; OutputStream outputStream = null; try { String fileName = "??" + TimeUtil.currTime(); Workbook workbook = batchImportFailList(modelPath, objectList, modelClass, columnMap); excelName(response, fileName); outputStream = response.getOutputStream(); workbook.write(outputStream); } catch (Exception e) { e.printStackTrace(); } finally { if (outputStream != null) try { outputStream.close(); } catch (IOException e) { e.printStackTrace(); } } } public HttpServletResponse excelName(HttpServletResponse response, String fileName) { response.setContentType("application/octet-stream"); response.setHeader("Content-Disposition", "attachment;filename=" + handleFileName(fileName) + ".xlsx"); return response; } private String handleFileName(String fileName) { try { return new String(fileName.getBytes("GBK"), "ISO-8859-1"); } catch (Exception e) { return ""; } } public <T> Workbook batchImportFailList(String modelPath, List<T> objectList, Class<T> modelClass, Map<String, String> columnMap) throws Exception { //??excel File localfile = new File("D:\\lw7068\\Desktop\\ (4)\\??.xlsx"); InputStream in = new FileInputStream(localfile); // ? Workbook book = new XSSFWorkbook(in); // ?sheet Sheet sheet = book.getSheetAt(0); // Row titleRow = sheet.getRow(0); //???index Map<String, Integer> rowIndex = getTitleRowIndex(titleRow); //???method Map<Integer, Method> methodsIndex = getRowIndexAndGetMethod(titleRow, rowIndex, modelClass, columnMap); for (int i = 0; i < objectList.size(); i++) { //excelindex ??? Map<Integer, String> dataMap = getRowIndexAndData(titleRow, objectList.get(i), methodsIndex); // Row row = sheet.createRow(i + 1); // for (Map.Entry<Integer, String> entry : dataMap.entrySet()) { Cell cell = row.createCell(entry.getKey()); cell.setCellValue(entry.getValue()); } } return book; } private Map<String, Integer> getTitleRowIndex(Row row) { Map<String, Integer> rowIndex = new HashMap<String, Integer>(); Cell cell; Iterator<Cell> iterator = row.iterator(); int index = 0; while (iterator.hasNext()) { cell = iterator.next(); cell.setCellType(Cell.CELL_TYPE_STRING); rowIndex.put(cell.getStringCellValue(), index); index++; } //???index return rowIndex; } private <T> Map<Integer, Method> getRowIndexAndGetMethod(Row row, Map<String, Integer> titleRowIndex, Class<T> modelClass, Map<String, String> columnMap) throws Exception { Map<Integer, Method> methodIndex = new HashMap<Integer, Method>();//??index for (Map.Entry<String, Integer> entry : titleRowIndex.entrySet()) { String fieldName = columnMap.get(entry.getKey()); PropertyDescriptor pd = new PropertyDescriptor(fieldName, modelClass);//get methodIndex.put(entry.getValue(), pd.getReadMethod()); } return methodIndex; } private <T> Map<Integer, String> getRowIndexAndData(Row row, Object object, Map<Integer, Method> methodMap) throws Exception { Map<Integer, String> dataMap = new HashMap<Integer, String>(); for (Map.Entry<Integer, Method> entry : methodMap.entrySet()) { Object invoke = entry.getValue().invoke(object); String string = invoke == null ? "" : invoke.toString(); dataMap.put(entry.getKey(), string); } return dataMap; } /** * excel * @param list ? * @param keys listmapkey? * @param columnNames excel?? * */ public static Workbook createWorkBook(List<Map<String, Object>> list, String[] keys, String columnNames[]) { // excel Workbook wb = new HSSFWorkbook(); // sheet?? Sheet sheet = wb.createSheet(list.get(0).get("sheetName").toString()); // ???n? for (int i = 0; i < keys.length; i++) { sheet.setColumnWidth((short) i, (short) (35.7 * 150)); } // Row row = sheet.createRow((short) 0); // ??? CellStyle cs = wb.createCellStyle(); CellStyle cs2 = wb.createCellStyle(); // ? Font f = wb.createFont(); Font f2 = wb.createFont(); // ???? f.setFontHeightInPoints((short) 10); f.setColor(IndexedColors.BLACK.getIndex()); f.setBoldweight(Font.BOLDWEIGHT_BOLD); // ?? f2.setFontHeightInPoints((short) 10); f2.setColor(IndexedColors.BLACK.getIndex()); // Font f3=wb.createFont(); // f3.setFontHeightInPoints((short) 10); // f3.setColor(IndexedColors.RED.getIndex()); // ????? cs.setFont(f); cs.setBorderLeft(CellStyle.BORDER_THIN); cs.setBorderRight(CellStyle.BORDER_THIN); cs.setBorderTop(CellStyle.BORDER_THIN); cs.setBorderBottom(CellStyle.BORDER_THIN); cs.setAlignment(CellStyle.ALIGN_CENTER); // ??? cs2.setFont(f2); cs2.setBorderLeft(CellStyle.BORDER_THIN); cs2.setBorderRight(CellStyle.BORDER_THIN); cs2.setBorderTop(CellStyle.BORDER_THIN); cs2.setBorderBottom(CellStyle.BORDER_THIN); cs2.setAlignment(CellStyle.ALIGN_CENTER); //?? for (int i = 0; i < columnNames.length; i++) { Cell cell = row.createCell(i); cell.setCellValue(columnNames[i]); cell.setCellStyle(cs); } //?? for (short i = 1; i < list.size(); i++) { // Row ,Cell , Row Cell 0 // sheet Row row1 = sheet.createRow((short) i); // row for (short j = 0; j < keys.length; j++) { Cell cell = row1.createCell(j); cell.setCellValue(list.get(i).get(keys[j]) == null ? " " : list.get(i).get(keys[j]).toString()); cell.setCellStyle(cs2); } } return wb; } }