List<T>转为Excel和Excel解析Map

package methods;import java.io.File;import java.io.FileInputStream;import java.io.FileNotFoundException;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.ParseException;import java.text.SimpleDateFormat;import java.util.ArrayList;import java.util.Calendar;import java.util.Date;import java.util.HashMap;import java.util.List;import java.util.Map;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.DateUtil;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;public class ExcelParse {public <T> void WriteExcel(List<T> list) throws NoSuchMethodException, SecurityException, IllegalAccessException,IllegalArgumentException, InvocationTargetException, ParseException {XSSFWorkbook workbook = new XSSFWorkbook();Sheet sheet = workbook.createSheet(list.get(0).getClass().getName());Field[] fields = null;for (int i = 0; i < list.size(); i++) {fields = list.get(i).getClass().getDeclaredFields();System.out.println(list.get(i));if (i == 0) {Row row = sheet.createRow(i);for (int j = 0; j < fields.length; j++) {Cell cell = row.createCell(j);cell.setCellValue(fields[j].getName());System.out.println(fields[j].getName().toString());}}Row row = sheet.createRow(i + 1);for (int j = 0; j < fields.length; j++) {/*截取方法名的第一个字符变大写*/String firstLetter = fields[j].getName().substring(0, 1).toUpperCase();/*拼接get方法名*/String getter = "get" + firstLetter + fields[j].getName().substring(1);/*变成get方法*/Method method = list.get(i).getClass().getMethod(getter, new Class[] {});/*获取value值*/Object value = method.invoke(list.get(i), new Object[]{});System.out.println(fields[j].getType().toString());Cell cell=row.createCell(j);SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");CellStyle dateCellStyle=workbook.createCellStyle(); short df=workbook.createDataFormat().getFormat("yyyy-mm-dd"); dateCellStyle.setDataFormat(df);switch(fields[j].getType().toString()){case "int":case "short":case "long":case "double":cell.setCellType(Cell.CELL_TYPE_NUMERIC);cell.setCellValue(Double.parseDouble(value.toString()));break;case "class java.util.Date":String temp = sdf.format((Date)value);Date date=sdf.parse(temp);cell.setCellType(Cell.CELL_TYPE_NUMERIC);cell.setCellValue(date);cell.setCellStyle(dateCellStyle);break;case "class java.util.Calendar":row.createCell(j).setCellValue((Calendar)value);break;default:row.createCell(j).setCellValue(value.toString());break; }}}try {FileOutputStream fileOut = new FileOutputStream("D:/Result.xlsx");//BufferedOutputStream bos = new BufferedOutputStream(fileOut);workbook.write(fileOut);fileOut.close();//JOptionPane.showMessageDialog(null, "导出成功");} catch (FileNotFoundException e) {//JOptionPane.showMessageDialog(null, "导出失败");e.printStackTrace();} catch (IOException e) {//JOptionPane.showMessageDialog(null, "导出失败");e.printStackTrace();}}/*解析excel 参数是文件地址

返回值是：list*/

public List<Map<String, String>> ReadExcel(String filepath) {List<Map<String, String>> sheetData = new ArrayList<Map<String, String>>();Workbook workbook = checkFile(filepath);Sheet sheet = workbook.getSheetAt(0);Map<Integer, String> rowMenu = setRowMap(sheet);for(int rowNum = 1;rowNum<=sheet.getLastRowNum();rowNum++){Row row = sheet.getRow(rowNum);Map<String, String> rowData = getRowData(row, rowMenu);sheetData.add(rowData);}return sheetData;}/*判断是什么文件*/private Workbook checkFile(String filePath){FileInputStream inStream=null;try {inStream = new FileInputStream(new File(filePath));} catch (FileNotFoundException e) {e.printStackTrace();}if(filePath.matches("^.+.xls$")){try {System.out.println("this is xls");return new HSSFWorkbook(inStream);} catch (IOException e) {e.printStackTrace();}}if(filePath.matches("^.+.xlsx$")){try {System.out.println("this id xlsx");return new XSSFWorkbook(inStream);} catch (IOException e) {e.printStackTrace();}}else{try {throw new FileNotFoundException("文件不对");} catch (FileNotFoundException e) {e.printStackTrace();}}return null;}private Map<Integer, String> setRowMap(Sheet sheet) {Map<Integer, String> rowMenu = new HashMap<Integer, String>();Row row = sheet.getRow(0);for(int colNum = 0;colNum<row.getLastCellNum();colNum++){String menuValue = row.getCell(colNum).getStringCellValue();rowMenu.put(colNum, menuValue);}return rowMenu;}private Map<String,String> getRowData(Row row,Map<Integer,String> rowMenu){Map<String,String> rowData=new HashMap<String,String>();for(int colNum=0;colNum<row.getLastCellNum();colNum++){Cell cell=row.getCell(colNum);String cellValue=null;switch (cell.getCellType()) {//数值类型case Cell.CELL_TYPE_NUMERIC://进一步判断 ，单元格格式是日期格式if (DateUtil.isCellDateFormatted(cell)) {SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");cellValue = sdf.format(cell.getDateCellValue());} 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:{try{cellValue = String.valueOf(cell.getNumericCellValue());}catch(IllegalStateException e){cellValue = String.valueOf(cell.getRichStringCellValue());}}break;case Cell.CELL_TYPE_BLANK:cellValue = "";break;case Cell.CELL_TYPE_ERROR:cellValue = "";break;default:cellValue=cell.toString().trim();break;}rowData.put(rowMenu.get(colNum), cellValue);}return rowData;}}

text测试程序

将excel中的数据导出到控制面板

数据输入表中的结果：