Struts2 Excel导入，导出！支持hashMap跟Model作为参数
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接口类
Excel抽象类
Excel2007
Excel2003
调用
------------Excel导入------------------
上传Excel文件
读取Excel文件
导入包
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();
}
@Override
public 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;
}
@Override
public Sheet creatSheet(String sheetName) {
Sheet sheet = wb.createSheet(sheetName);
return sheet;
}
@Override
public 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();
}
@Override
public 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();
}
@Override
public 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();
}
@Override
public 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;
}
@Override
public Workbook getReadExcel(String ExcelFullName) {
try{
InputStream inp = new FileInputStream(ExcelFullName);
readwb = WorkbookFactory.create(inp);
}catch (Exception e) {
e.printStackTrace();
}
return readwb;
}
@Override
public Sheet getReadSheet(int sheetAt) {
return readwb.getSheetAt(sheetAt);
}
@Override
public String getReadCellValue(Sheet sheet, int rowNum, int cellNum) {
Row row = sheet.getRow(rowNum);
Cell cell = row.getCell(cellNum);
return cell.toString();
}
@Override
public 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();
}
@Override
public 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();
}
@Override
public 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;
}
}