Spring使用POI实现Excel导入导出.docx

1、Spring使用POI实现Excel导入导出Spring使用POI实现Excel导入导出 Apache POI 是创建和维护操作各种符合Office Open XML（OOXML）标准和微软的OLE 2复合文档格式（OLE2）的Java API。用它可以使用Java读取和创建,修改MS Excel文件.而且,还可以使用Java读取和创建MS Word和MSPowerPoint文件。Apache POI 提供Java操作Excel解决方案（适用于Excel97-2008）。 简单理解就是通过POI，java可以与office建立联系。 本次项目实践基于SSM框架，简单封装了Excel批量导入导出

2、功能，实现过程如下 1. maven导入java包：html view plain copy <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.5-FINAL</version> </dependency> 2. 建立Excel实体-ExcelBeanjava view plain copy /* * * Description: 导入导出excel * autho

3、r haipeng * date 2017年4月11日 */ public class ExcelBean implements java.io.Serializable private String headTextName;/列头（标题）名 private String propertyName;/对应字段名 private Integer cols;/合并单元格数 private XSSFCellStyle cellStyle; public ExcelBean() public ExcelBean(String headTextName, String propertyName) th

4、is.headTextName = headTextName; this.propertyName = propertyName; public ExcelBean(String headTextName, String propertyName, Integer cols) super(); this.headTextName = headTextName; this.propertyName = propertyName; this.cols = cols; public String getHeadTextName() return headTextName; public void s

5、etHeadTextName(String headTextName) this.headTextName = headTextName; public String getPropertyName() return propertyName; public void setPropertyName(String propertyName) this.propertyName = propertyName; public Integer getCols() return cols; public void setCols(Integer cols) this.cols = cols; publ

6、ic XSSFCellStyle getCellStyle() return cellStyle; public void setCellStyle(XSSFCellStyle cellStyle) this.cellStyle = cellStyle; 3. 封装Excel工具类-ExcelUtilsjava view plain copy public class ExcelUtils private final static String excel2003L =.xls; /2003- 版本的excel private final static String excel2007U =.

7、xlsx; /2007+ 版本的excel /*文件上传*/ public static List<List<Object>> getBankListByExcel(InputStream in,String fileName) throws Exception List<List<Object>> list = null; /创建Excel工作薄 Workbook work = getWorkbook(in,fileName); if(null = work) throw new Exception(创建Excel工作薄为空！); Sheet

8、sheet = null; Row row = null; Cell cell = null; list = new ArrayList<List<Object>>(); /遍历Excel中所有的sheet for (int i = 0; i < work.getNumberOfSheets(); i+) sheet = work.getSheetAt(i); if(sheet=null)continue; /遍历当前sheet中的所有行 for (int j = sheet.getFirstRowNum(); j < sheet.getLastRowNum

9、(); j+) row = sheet.getRow(j); if(row=null|row.getFirstCellNum()=j)continue; /遍历所有的列 List<Object> li = new ArrayList<Object>(); for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y+) cell = row.getCell(y); li.add(getCellValue(cell); list.add(li); / work.close(); return list

10、; /* * 描述：根据文件后缀，自适应上传文件的版本 * param inStr,fileName * return * throws Exception */ public static Workbook getWorkbook(InputStream inStr,String fileName) throws Exception Workbook wb = null; String fileType = fileName.substring(fileName.lastIndexOf(.); if(excel2003L.equals(fileType) wb = new HSSFWorkb

11、ook(inStr); /2003- else if(excel2007U.equals(fileType) wb = new XSSFWorkbook(inStr); /2007+ else throw new Exception(解析的文件格式有误！); return wb; /* * 描述：对表格中数值进行格式化 * param cell * return */ public static Object getCellValue(Cell cell) Object value = null; DecimalFormat df = new DecimalFormat(0); /格式化num

12、ber String字符 SimpleDateFormat sdf = new SimpleDateFormat(yyy-MM-dd); /日期格式化 DecimalFormat df2 = new DecimalFormat(0.00); /格式化数字 switch (cell.getCellType() case Cell.CELL_TYPE_STRING: value = cell.getRichStringCellValue().getString(); break; case Cell.CELL_TYPE_NUMERIC: if(General.equals(cell.getCell

13、Style().getDataFormatString() value = df.format(cell.getNumericCellValue(); else if(m/d/yy.equals(cell.getCellStyle().getDataFormatString() value = sdf.format(cell.getDateCellValue(); else value = df2.format(cell.getNumericCellValue(); break; case Cell.CELL_TYPE_BOOLEAN: value = cell.getBooleanCellV

14、alue(); break; case Cell.CELL_TYPE_BLANK: value = ; break; default: break; return value; /*上传结束* /* * 多列头创建EXCEL * * param sheetName 工作簿名称 * param clazz 数据源model类型 * param objs excel标题列以及对应model字段名 * param map 标题列行数以及cell字体样式 * return * throws IllegalArgumentException * throws IllegalAccessException

15、 * throws InvocationTargetException * throws ClassNotFoundException * throws IntrospectionException * throws ParseException */ public static XSSFWorkbook createExcelFile(Class clazz, List objs,Map<Integer, List<ExcelBean>> map,String sheetName) throws IllegalArgumentException,IllegalAcce

16、ssException, InvocationTargetException, ClassNotFoundException, IntrospectionException, ParseException / 创建新的Excel 工作簿 XSSFWorkbook workbook = new XSSFWorkbook(); / 在Excel工作簿中建一工作表，其名为缺省值, 也可以指定Sheet名称 XSSFSheet sheet = workbook.createSheet(sheetName); / 以下为excel的字体样式以及excel的标题与内容的创建，下面会具体分析; create

17、Font(workbook);/字体样式 createTableHeader(sheet, map);/创建标题（头） createTableRows(sheet, map, objs, clazz);/创建内容 return workbook; private static XSSFCellStyle fontStyle; private static XSSFCellStyle fontStyle2; public static void createFont(XSSFWorkbook workbook) / 表头 fontStyle = workbook.createCellStyle(

18、); XSSFFont font1 = workbook.createFont(); font1.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD); font1.setFontName(黑体); font1.setFontHeightInPoints(short) 14);/ 设置字体大小 fontStyle.setFont(font1); fontStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN); / 下边框 fontStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);/

19、左边框 fontStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);/ 上边框 fontStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);/ 右边框 fontStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER); / 居中 / 内容 fontStyle2=workbook.createCellStyle(); XSSFFont font2 = workbook.createFont(); font2.setFontName(宋体); font2.setFontHeigh

20、tInPoints(short) 10);/ 设置字体大小 fontStyle2.setFont(font2); fontStyle2.setBorderBottom(XSSFCellStyle.BORDER_THIN); / 下边框 fontStyle2.setBorderLeft(XSSFCellStyle.BORDER_THIN);/ 左边框 fontStyle2.setBorderTop(XSSFCellStyle.BORDER_THIN);/ 上边框 fontStyle2.setBorderRight(XSSFCellStyle.BORDER_THIN);/ 右边框 fontStyl

21、e2.setAlignment(XSSFCellStyle.ALIGN_CENTER); / 居中 /* * 根据ExcelMapping 生成列头（多行列头） * * param sheet * 工作簿 * param map * 每行每个单元格对应的列头信息 */ public static final void createTableHeader(XSSFSheet sheet, Map<Integer, List<ExcelBean>> map) int startIndex=0;/cell起始位置 int endIndex=0;/cell终止位置 for (M

22、ap.Entry<Integer, List<ExcelBean>> entry : map.entrySet() XSSFRow row = sheet.createRow(entry.getKey(); List<ExcelBean> excels = entry.getValue(); for (int x = 0; x < excels.size(); x+) /合并单元格 if(excels.get(x).getCols()>1) if(x=0) endIndex+=excels.get(x).getCols()-1; CellRang

23、eAddress range=new CellRangeAddress(0,0,startIndex,endIndex); sheet.addMergedRegion(range); startIndex+=excels.get(x).getCols(); else endIndex+=excels.get(x).getCols(); CellRangeAddress range=new CellRangeAddress(0,0,startIndex,endIndex); sheet.addMergedRegion(range); startIndex+=excels.get(x).getCo

24、ls(); XSSFCell cell = row.createCell(startIndex-excels.get(x).getCols(); cell.setCellValue(excels.get(x).getHeadTextName();/ 设置内容 if (excels.get(x).getCellStyle() != null) cell.setCellStyle(excels.get(x).getCellStyle();/ 设置格式 cell.setCellStyle(fontStyle); else XSSFCell cell = row.createCell(x); cell

25、.setCellValue(excels.get(x).getHeadTextName();/ 设置内容 if (excels.get(x).getCellStyle() != null) cell.setCellStyle(excels.get(x).getCellStyle();/ 设置格式 cell.setCellStyle(fontStyle); /* * * param sheet * param map * param objs * param clazz */ SuppressWarnings(rawtypes) public static void createTableRow

26、s(XSSFSheet sheet, Map<Integer, List<ExcelBean>> map, List objs, Class clazz) throws IllegalArgumentException, IllegalAccessException, InvocationTargetException, IntrospectionException, ClassNotFoundException, ParseException int rowindex = map.size(); int maxKey = 0; List<ExcelBean>

27、; ems = new ArrayList<>(); for (Map.Entry<Integer, List<ExcelBean>> entry : map.entrySet() if (entry.getKey() > maxKey) maxKey = entry.getKey(); ems = map.get(maxKey); List<Integer> widths = new ArrayList<Integer>(ems.size(); for (Object obj : objs) XSSFRow row = she

28、et.createRow(rowindex); for (int i = 0; i < ems.size(); i+) ExcelBean em = (ExcelBean) ems.get(i); / 获得get方法 PropertyDescriptor pd = new PropertyDescriptor(em.getPropertyName(), clazz); Method getMethod = pd.getReadMethod(); Object rtn = getMethod.invoke(obj); String value = ; / 如果是日期类型 进行 转换 if

29、(rtn != null) if (rtn instanceof Date) value = DateUtils.date2String(Date) rtn,yyyy-MM-dd); else if(rtn instanceof BigDecimal) NumberFormat nf = new DecimalFormat(#,#0.00); value=nf.format(BigDecimal)rtn).toString(); else if(rtn instanceof Integer) && (Integer.valueOf(rtn.toString()<0 ) v

30、alue=-; else value = rtn.toString(); XSSFCell cell = row.createCell(i); cell.setCellValue(value); cell.setCellType(XSSFCell.CELL_TYPE_STRING); cell.setCellStyle(fontStyle2); / 获得最大列宽 int width = value.getBytes().length * 300; / 还未设置，设置当前 if (widths.size() <= i) widths.add(width); continue; / 比原来大

31、，更新数据 if (width > widths.get(i) widths.set(i, width); rowindex+; / 设置列宽 for (int index = 0; index < widths.size(); index+) Integer width = widths.get(index); width = width < 2500 ? 2500 : width + 300; width = width > 10000 ? 10000 + 300 : width + 300; sheet.setColumnWidth(index, width); 4. 在HTML页面导入需要的jshtml view plain copy <script type=text/javascript src=$ctxPath/js/jquery-form.js></script> 5. 在HTML添加测试控件java view plain copy <