Struts2 Excel导入，导出！支持hashMap和Model作为参数
导入包
dom4j-1.6.1.jar
poi-3.9-20121203.jar
poi-ooxml-3.9-20121203.jar
poi-ooxml-schemas-3.9-20121203.jar
stax-api-1.0.1.jar
xmlbeans-2.3.0.jar
------------Excel导出------------------
Excel接口类
package com.XXX.lib.global.excel;import java.util.ArrayList;import java.util.List;import java.util.Map;import org.apache.poi.ss.usermodel.Sheet;import org.apache.poi.ss.usermodel.Workbook;import com.XXXX.lib.fintface.IdentiIntface;public interface ExcelObjIntface {/** * excel下载 */public void excelDownLoad();/** * 创建一个Excel * @return */public Workbook creatWorkBook();/** * 创建一个sheet数组 * @param sheetNames * @return */public ArrayList<Sheet> creatSheet(ArrayList<String> sheetNames);/** * 创建一个sheet * @param sheetName * @return */public Sheet creatSheet(String sheetName);/** * 创建一个简单头部 * @param sheet * @param headValues * @return */public int creatSheetHeads(Sheet sheet,List<String> headValues);/** * 根据HashMap生成数据 * @param sheet sheet * @param cellValues * @return */public int creatSheetCellsByMap(Sheet sheet, List<Map<Object, Object>> cellValues);/** * 根据模型生成数据 * @param sheet sheet * @param model 模型列表 * @return 生成最后一条数据所在的行数 */public int creatSheetCellsByModel(Sheet sheet,List<IdentiIntface> model);/** * 结束并生成Excel * @param ExcelName，Excel的名称 */public void finishWorkBook(String ExcelName);/** * 获得一个workBook; * @param ExcelFullName * @return */public Workbook getReadExcel(String ExcelFullName);/** * 获得一个sheet * @param readwb * @return */public Sheet getReadSheet(int sheetAt);/** * 获得一个制定的值 * @param sheet * @param row * @param cell * @return */public String getReadCellValue(Sheet sheet,int rowNum,int cellNum);public void init();}Excel抽象类
package com.XXX.lib.global.excel;import java.io.FileInputStream;import java.io.FileOutputStream;import java.io.InputStream;import java.util.ArrayList;import java.util.HashMap;import java.util.List;import java.util.Map;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.ss.usermodel.WorkbookFactory;import com.XXX.lib.fintface.IdentiIntface;import com.XXX.lib.global.constant.ExcelConstant;import com.XXX.lib.global.util.ClassUtil;/** * http://poi.apache.org/spreadsheet/quick-guide.html#NewSheet * Excel父类,抽象类 */public abstract class ExcelObjAbstract implements ExcelObjIntface{protected Workbook wb;private Workbook readwb;private List<Object> heads;private int startCols;//输出开始列private int startRows;//输出开始行private int headHeight;//设置头部高度private int bodyHeight;//设置身体高度private String Suffix;public ExcelObjAbstract(){this.excelDownLoad();}@Overridepublic ArrayList<Sheet> creatSheet(ArrayList<String> sheetNames) {ArrayList<Sheet> sheetArray = new ArrayList<Sheet>();for (String sheetName : sheetNames) {Sheet tmpsheet = wb.createSheet(sheetName);sheetArray.add(tmpsheet);}return sheetArray;}@Overridepublic Sheet creatSheet(String sheetName) {Sheet sheet = wb.createSheet(sheetName);return sheet;}@Overridepublic int creatSheetHeads(Sheet sheet, List<String> headValues) {HashMap<String, CellStyle> styles = this.headStyle();heads = new ArrayList<Object>();Row row = sheet.createRow(startRows);//第一行if(headHeight!=0){row.setHeight((short)headHeight);}for(int i=0;i<headValues.size();i++) {String[] keys = headValues.get(i).split(",");heads.add(keys[0]);//把列名放到集合中Cell cell = row.createCell(i+startCols);cell.setCellStyle(styles.get("headBlack4")); if(keys.length>=2){//第二项存在[并且语言为中文]cell.setCellValue(keys[1]);}//else if(keys.length>=3){//第三项存在[并且语言为韩文]//cell.setCellValue(keys[2]);//}if(keys.length>=4){//第四项存在,就设置列宽[注:第四个字段为最大字符串长度]sheet.setColumnWidth(i+startCols, Integer.parseInt(keys[3])*256);}//默认}return headValues.size();}@Overridepublic int creatSheetCellsByMap(Sheet sheet, List<Map<Object, Object>> cellValues) {HashMap<String, CellStyle> styles = this.bodyStyle();for(int i=0;i<cellValues.size();i++) {Row row = sheet.createRow(startRows+i+1);//因为第一行为题目，故从第二行开始输出正文if(bodyHeight!=0){row.setHeight((short)bodyHeight);}for(int j=0;j<cellValues.get(i).size();j++) {Cell cell = row.createCell(j);Object eValue = cellValues.get(i).get(heads.get(j));if(eValue==null){eValue = " ";}cell.setCellValue(eValue.toString());cell.setCellStyle(styles.get("bodyBlack4"));}}return cellValues.size();}@Overridepublic int creatSheetCellsByModel(Sheet sheet,List<IdentiIntface> modelList){HashMap<String, CellStyle> styles = this.bodyStyle();for(int i=0;i<modelList.size();i++) {Row row = sheet.createRow(startRows+i+1);//因为第一行为题目，故从第二行开始输出正文if(bodyHeight!=0){row.setHeight((short)bodyHeight);}for(int j=0;j<heads.size();j++) {Cell cell = row.createCell(startCols+j);//通过字符串获得属性值Object eValue = ClassUtil.getFieldValueByName(heads.get(j).toString(),modelList.get(i));if(eValue==null){eValue = " ";}cell.setCellValue(eValue.toString());cell.setCellStyle(styles.get("bodyBlack4"));}}return modelList.size();}@Overridepublic void finishWorkBook(String ExcelName) {String realExcelName = ExcelConstant.DEFAULT_OUT_PATH+ExcelName+Suffix;try { FileOutputStream fileOut = new FileOutputStream(realExcelName); wb.write(fileOut); fileOut.close();} catch (Exception e) {e.printStackTrace();}}//内容样式集合private HashMap<String, CellStyle> bodyStyle(){ HashMap<String, CellStyle> styles = new HashMap<String, CellStyle>();//内容样式一： 四个边框 黑色,无底色,上下居中，左右居中 CellStyle style = wb.createCellStyle(); //设置字体 Font headFont = wb.createFont(); headFont.setFontHeightInPoints((short)9); headFont.setColor(IndexedColors.BLACK.getIndex()); headFont.setFontName("宋体"); style.setFont(headFont); //设置边框 style.setBorderBottom(CellStyle.BORDER_THIN); style.setBottomBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderLeft(CellStyle.BORDER_THIN); style.setLeftBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderRight(CellStyle.BORDER_THIN); style.setRightBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderTop(CellStyle.BORDER_THIN); style.setTopBorderColor(IndexedColors.BLACK.getIndex()); //设置居中 style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); styles.put("bodyBlack4", style); return styles;}//头部样式集合private HashMap<String, CellStyle> headStyle(){HashMap<String, CellStyle> styles = new HashMap<String, CellStyle>();//头部样式一： 四个边框 黑色,深灰色底色,上下居中，左右居中 CellStyle style = wb.createCellStyle(); //设置字体 Font headFont = wb.createFont(); headFont.setFontHeightInPoints((short)11); headFont.setBoldweight(Font.BOLDWEIGHT_BOLD); headFont.setColor(IndexedColors.BLACK.getIndex()); headFont.setFontName("宋体"); style.setFont(headFont); //设置边框 style.setBorderBottom(CellStyle.BORDER_THIN); style.setBottomBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderLeft(CellStyle.BORDER_THIN); style.setLeftBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderRight(CellStyle.BORDER_THIN); style.setRightBorderColor(IndexedColors.BLACK.getIndex()); style.setBorderTop(CellStyle.BORDER_THIN); style.setTopBorderColor(IndexedColors.BLACK.getIndex()); //设置背景颜色 style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); style.setFillPattern(CellStyle.SOLID_FOREGROUND); //设置居中 style.setAlignment(CellStyle.ALIGN_CENTER); style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); styles.put("headBlack4", style); return styles;}@Overridepublic Workbook getReadExcel(String ExcelFullName) {try{InputStream inp = new FileInputStream(ExcelFullName);readwb = WorkbookFactory.create(inp);}catch (Exception e) {e.printStackTrace();}return readwb;}@Overridepublic Sheet getReadSheet(int sheetAt) {return readwb.getSheetAt(sheetAt);}@Overridepublic String getReadCellValue(Sheet sheet, int rowNum, int cellNum) {Row row = sheet.getRow(rowNum);Cell cell = row.getCell(cellNum);return cell.toString();}@Overridepublic void excelDownLoad() {this.init();this.creatWorkBook();this.setStartColsRows(1, 1);}/** * 集中设置开始行列 * @param startCols * @param startRows */public void setStartColsRows(int startCols,int startRows){this.setStartCols(startCols);this.setStartRows(startRows);}/** * 集中设置高度 * @param headHeight * @param bodyHeight */public void setHeadBodyHeight(int headHeight,int bodyHeight){this.setHeadHeight(headHeight);this.setBodyHeight(bodyHeight);}public int getStartCols() {return startCols;}public void setStartCols(int startCols) {this.startCols = startCols;}public int getStartRows() {return startRows;}public void setStartRows(int startRows) {this.startRows = startRows;}public String getSuffix() {return Suffix;}public void setSuffix(String suffix) {Suffix = suffix;}public int getHeadHeight() {return headHeight;}public void setHeadHeight(int headHeight) {this.headHeight = headHeight;}public int getBodyHeight() {return bodyHeight;}public void setBodyHeight(int bodyHeight) {this.bodyHeight = bodyHeight;}}Excel2007
package com.XXX.lib.global.excel;import org.apache.poi.ss.usermodel.Workbook;import org.apache.poi.xssf.usermodel.XSSFWorkbook;public class Excel2007 extends ExcelObjAbstract{public Excel2007(){super();}@Overridepublic Workbook creatWorkBook() {wb = new XSSFWorkbook();return wb;}public void init(){super.setSuffix(".xlsx");}}Excel2003
package com.XXX.lib.global.excel;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.ss.usermodel.Workbook;public class Excel2003 extends ExcelObjAbstract{public Excel2003(){super();}@Overridepublic Workbook creatWorkBook() {wb = new HSSFWorkbook();return wb;}public void init(){super.setSuffix(".xls");}}调用
String fj = excelDate.format(new Date());List<String> headMas= new ArrayList<String>();headMas.add("lodging_id,xx,i,20");headMas.add("room_count,xx,i");String fileName = "xx"+fj;Excel2007 excel2007 = new Excel2007();Sheet sheet = excel2007.creatSheet("xx");excel2007.creatSheetHeads(sheet,headMas);excel2007.creatSheetCellsByModel(sheet, result);excel2007.finishWorkBook(fileName);------------Excel导入------------------
上传Excel文件
public String fileUpload(){ File[] srcfiles = updExcel;if( srcfiles != null ){for(int i=0;i<srcfiles.length;i++){int fileId = lodgService.seqkey();//唯一id SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd"); String tmpStr = sdf.format(new Date()); String tmpYyyymm = tmpStr;String fileName = updExcelFileName[i];//文件名称String extension = FileUtil.getFileExtension(fileName);//类型if(extension.equals(".xlsm")){String newFileName = "xxxx"+fileId+".xlsm";//新文件名String filePath = Constant.FILE_DEFAULT_UPLOAD + tmpYyyymm + "_" + newFileName;File file = new File(filePath);FileUtil.copy(updExcel[i], file);if(excelInAppoint(filePath)){this.setInfoString("Excel导入成功");}else{this.setInfoString("Excel导入失败");}}}}search();return "fileUpload";}读取Excel文件
private boolean excelInAppoint(String filePath){Excel2007 excel2007 = new Excel2007();excel2007.getReadExcel(filePath);Sheet sheet = excel2007.getReadSheet(0);//TODO 生成服务器端校验码String testStr = excel2007.getReadCellValue(sheet, 1, 2);//获得客户端校验码String[] testArr = testStr.split("-"); SimpleDateFormat sdf=new SimpleDateFormat("yyyyMMdd"); String tmpStr = sdf.format(new Date()); int totleCols = Integer.parseInt(testArr[4]);if( tmpStr.equals(testArr[0]) && testArr[1].equals("0")){for(int i=4;i<totleCols;i++){//遍历所有行//TODO 插入数据lodgModel.setLodging_id(excel2007.getReadCellValue(sheet, i, 1));lodgModel.setAp_date(excel2007.getReadCellValue(sheet, i, 2));lodgModel.setAp_code(excel2007.getReadCellValue(sheet, i, 3));lodgModel.setCuser(excel2007.getReadCellValue(sheet, i, 4));lodgModel.setStatus(excel2007.getReadCellValue(sheet, i, 5));lodgModel.setEmpno(excel2007.getReadCellValue(sheet, i, 6));empInto();}return true;}else{return false;}}