ssm poi导入mysql_SSM使用poi excel导入mysql 从页面到控制器（工具类util中有到导出方法）...

1 packagecom.website.system.utils;2 importorg.apache.http.client.utils.DateUtils;3 importorg.apache.poi.hssf.usermodel.HSSFWorkbook;4 importorg.apache.poi.ss.usermodel.Cell;5 importorg.apache.poi.ss.usermodel.Row;6 importorg.apache.poi.ss.usermodel.Sheet;7 importorg.apache.poi.ss.usermodel.Workbook;8 importorg.apache.poi.ss.util.CellRangeAddress;9 import org.apache.poi.xssf.usermodel.*;10

11 importjava.beans.IntrospectionException;12 importjava.beans.PropertyDescriptor;13 import java.io.*;14 importjava.lang.reflect.InvocationTargetException;15 importjava.lang.reflect.Method;16 importjava.math.BigDecimal;17 importjava.text.DecimalFormat;18 importjava.text.NumberFormat;19 importjava.text.ParseException;20 importjava.text.SimpleDateFormat;21 importjava.util.ArrayList;22 importjava.util.Date;23 importjava.util.List;24 importjava.util.Map;25

26 public classExcelUtil {27 private final static String excel2003L =".xls"; //2003- 版本的excel

28 private final static String excel2007U =".xlsx"; //2007+ 版本的excel

29 /**

30 * Excel导入31 */

32 public static List> getBankListByExcel(InputStream in, String fileName) throwsException{33 List> list = null;34 //创建Excel工作薄

35 Workbook work =getWorkbook(in,fileName);36 if(null ==work){37 throw new Exception("创建Excel工作薄为空！");38 }39 Sheet sheet = null;40 Row row = null;41 Cell cell = null;42 list = new ArrayList>();43 //遍历Excel中所有的sheet

44 for (int i = 0; i < work.getNumberOfSheets(); i++) {45 sheet =work.getSheetAt(i);46 if(sheet==null){continue;}47 //遍历当前sheet中的所有行48 //包涵头部，所以要小于等于最后一列数,这里也可以在初始值加上头部行数，以便跳过头部

49 for (int j = sheet.getFirstRowNum(); j <= sheet.getLastRowNum(); j++) {50 //读取一行

51 row =sheet.getRow(j);52 //去掉空行和表头

53 if(row==null||row.getFirstCellNum()==j){continue;}54 //遍历所有的列

55 List li = new ArrayList();56 for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {57 cell =row.getCell(y);58 li.add(getCellValue(cell));59 }60 list.add(li);61 }62 }63 returnlist;64 }65

66 private static Workbook getWorkbook(InputStream inStr,String fileName) throwsException {67 Workbook wb = null;68 String fileType = fileName.substring(fileName.lastIndexOf("."));69 if(excel2003L.equals(fileType)){70 wb = new HSSFWorkbook(inStr); //2003-

71 }else if(excel2007U.equals(fileType)){72 wb = new XSSFWorkbook(inStr); //2007+

73 }else{74 throw new Exception("解析的文件格式有误！");75 }76 returnwb;77 }78 /**

79 * 描述：对表格中数值进行格式化80 */

81 public staticObject getCellValue(Cell cell){82 Object value = null;83 DecimalFormat df = new DecimalFormat("0"); //格式化字符类型的数字

84 SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd"); //日期格式化

85 DecimalFormat df2 = new DecimalFormat("0.00"); //格式化数字

86 switch(cell.getCellType()) {87 caseCell.CELL_TYPE_STRING:88 value =cell.getRichStringCellValue().getString();89 break;90 caseCell.CELL_TYPE_NUMERIC:91 if("General".equals(cell.getCellStyle().getDataFormatString())){92 value =df.format(cell.getNumericCellValue());93 }else if("m/d/yy".equals(cell.getCellStyle().getDataFormatString())){94 value =sdf.format(cell.getDateCellValue());95 }else{96 value =df2.format(cell.getNumericCellValue());97 }98 break;99 caseCell.CELL_TYPE_BOOLEAN:100 value =cell.getBooleanCellValue();101 break;102 caseCell.CELL_TYPE_BLANK:103 value = "";104 break;105 default:106 break;107 }108 returnvalue;109 }110

111 /**

112 * 导入Excel表结束113 * 导出Excel表开始114 *@paramsheetName 工作簿名称115 *@paramclazz 数据源model类型116 *@paramobjs excel标题列以及对应model字段名117 *@parammap 标题列行数以及cell字体样式118 */

119 public static XSSFWorkbook createExcelFile(Class clazz, List objs, Map> map, String sheetName) throws

120 IllegalArgumentException,IllegalAccessException, InvocationTargetException,121 ClassNotFoundException, IntrospectionException, ParseException {122 //创建新的Excel工作簿

123 XSSFWorkbook workbook = newXSSFWorkbook();124 //在Excel工作簿中建一工作表，其名为缺省值, 也可以指定Sheet名称

125 XSSFSheet sheet =workbook.createSheet(sheetName);126 //以下为excel的字体样式以及excel的标题与内容的创建，下面会具体分析;

127 createFont(workbook); //字体样式

128 createTableHeader(sheet, map); //创建标题(头)

129 createTableRows(sheet, map, objs, clazz); //创建内容

130 returnworkbook;131 }132 private staticXSSFCellStyle fontStyle;133 private staticXSSFCellStyle fontStyle2;134 public static voidcreateFont(XSSFWorkbook workbook) {135 //表头

136 fontStyle =workbook.createCellStyle();137 XSSFFont font1 =workbook.createFont();138 font1.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);139 font1.setFontName("黑体");140 font1.setFontHeightInPoints((short) 14);//设置字体大小

141 fontStyle.setFont(font1);142 fontStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN); //下边框

143 fontStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);//左边框

144 fontStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);//上边框

145 fontStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);//右边框

146 fontStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER); //居中147 //内容

148 fontStyle2=workbook.createCellStyle();149 XSSFFont font2 =workbook.createFont();150 font2.setFontName("宋体");151 font2.setFontHeightInPoints((short) 10);//设置字体大小

152 fontStyle2.setFont(font2);153 fontStyle2.setBorderBottom(XSSFCellStyle.BORDER_THIN); //下边框

154 fontStyle2.setBorderLeft(XSSFCellStyle.BORDER_THIN);//左边框

155 fontStyle2.setBorderTop(XSSFCellStyle.BORDER_THIN);//上边框

156 fontStyle2.setBorderRight(XSSFCellStyle.BORDER_THIN);//右边框

157 fontStyle2.setAlignment(XSSFCellStyle.ALIGN_CENTER); //居中

158 }159

160 /**

161 * 根据ExcelMapping 生成列头(多行列头)162 *163 *@paramsheet 工作簿164 *@parammap 每行每个单元格对应的列头信息165 */

166 public static final void createTableHeader(XSSFSheet sheet, Map>map) {167 int startIndex=0;//cell起始位置

168 int endIndex=0;//cell终止位置

169 for (Map.Entry>entry : map.entrySet()) {170 XSSFRow row =sheet.createRow(entry.getKey());171 List excels =entry.getValue();172 for (int x = 0; x < excels.size(); x++) {173 //合并单元格

174 if(excels.get(x).getCols()>1){175 if(x==0){176 endIndex+=excels.get(x).getCols()-1;177 CellRangeAddress range=new CellRangeAddress(0,0,startIndex,endIndex);178 sheet.addMergedRegion(range);179 startIndex+=excels.get(x).getCols();180 }else{181 endIndex+=excels.get(x).getCols();182 CellRangeAddress range=new CellRangeAddress(0,0,startIndex,endIndex);183 sheet.addMergedRegion(range);184 startIndex+=excels.get(x).getCols();185 }186 XSSFCell cell = row.createCell(startIndex-excels.get(x).getCols());187 cell.setCellValue(excels.get(x).getHeadTextName());//设置内容

188 if (excels.get(x).getCellStyle() != null) {189 cell.setCellStyle(excels.get(x).getCellStyle());//设置格式

190 }191 cell.setCellStyle(fontStyle);192 }else{193 XSSFCell cell =row.createCell(x);194 cell.setCellValue(excels.get(x).getHeadTextName());//设置内容

195 if (excels.get(x).getCellStyle() != null) {196 cell.setCellStyle(excels.get(x).getCellStyle());//设置格式

197 }198 cell.setCellStyle(fontStyle);199 }200 }201 }202 }203 public static void createTableRows(XSSFSheet sheet, Map>map, List objs, Class clazz)204 throwsIllegalArgumentException, IllegalAccessException, InvocationTargetException, IntrospectionException,205 ClassNotFoundException, ParseException {206 int rowindex =map.size();207 int maxKey = 0;208 List ems = new ArrayList<>();209 for (Map.Entry>entry : map.entrySet()) {210 if (entry.getKey() >maxKey) {211 maxKey =entry.getKey();212 }213 }214 ems =map.get(maxKey);215 List widths = new ArrayList(ems.size());216 for(Object obj : objs) {217 XSSFRow row =sheet.createRow(rowindex);218 for (int i = 0; i < ems.size(); i++) {219 ExcelBean em =(ExcelBean) ems.get(i);220 //获得get方法

221 PropertyDescriptor pd = newPropertyDescriptor(em.getPropertyName(), clazz);222 Method getMethod =pd.getReadMethod();223 Object rtn =getMethod.invoke(obj);224 String value = "";225 //如果是日期类型进行转换

226 if (rtn != null) {227 if (rtn instanceofDate) {228 value = DateUtils.formatDate((Date)rtn,"yyyy-MM-dd");229 } else if(rtn instanceofBigDecimal){230 NumberFormat nf = new DecimalFormat("#,##0.00");231 value=nf.format((BigDecimal)rtn).toString();232 } else if((rtn instanceof Integer) && (Integer.valueOf(rtn.toString())<0)){233 value="--";234 }else{235 value =rtn.toString();236 }237 }238 XSSFCell cell =row.createCell(i);239 cell.setCellValue(value);240 cell.setCellType(XSSFCell.CELL_TYPE_STRING);241 cell.setCellStyle(fontStyle2);242 //获得最大列宽

243 int width = value.getBytes().length * 300;244 //还未设置，设置当前

245 if (widths.size() <=i) {246 widths.add(width);247 continue;248 }249 //比原来大，更新数据

250 if (width >widths.get(i)) {251 widths.set(i, width);252 }253 }254 rowindex++;255 }256 //设置列宽

257 for (int index = 0; index < widths.size(); index++) {258 Integer width =widths.get(index);259 width = width < 2500 ? 2500 : width + 300;260 width = width > 10000 ? 10000 + 300 : width + 300;261 sheet.setColumnWidth(index, width);262 }263 }264 }