java Excel的导入导出

导入的action ：

package avicit.mro.module.maintainplan.maintainmonthplan.mroorgmonthplan.action;


import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


import javax.servlet.http.HttpServletRequest;


import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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;


import avicit.mro.module.maintainplan.maintainmonthplan.mroorgmonthplan.domain.MroOrgMonthPlanService;
import avicit.mro.orm.MroOrgMonthPlan;


import com.bstek.dorado.common.DoradoContext;
import com.cape.framework.util.DateUtils;
import com.cape.platform.framework.exception.DaoException;
import com.cape.platform.framework.view.base.ActionBase;
import com.cape.platform.util.PojoUtil;
import com.opensymphony.xwork2.ActionSupport;


public class ImportExcelAction extends ActionSupport {
private MroOrgMonthPlanService mroOrgMonthPlanService;
private File excelFile;// 上传的文件
private String excelFileFileName;// 原始文件名
private String savePath;// 保存的路径
private String excelFileContentType;// 文件类型
private String message;
private int saveCount;// 正确导入的数据的条数
private int wrongCount;// 错误数据的条数


/**
* 判断是xls文件还是xlsx文件
*/
private Workbook createWorkBook(InputStream is) throws IOException {
if (excelFileFileName.toLowerCase().endsWith("xls")) {
return new HSSFWorkbook(is);
}
if (excelFileFileName.toLowerCase().endsWith("xlsx")) {
return new XSSFWorkbook(is);
}
return null;
}


public String execute() throws Exception {
if (excelFile != null) {
Workbook workbook = createWorkBook(new FileInputStream(excelFile));
Sheet sheet = workbook.getSheetAt(0);
List<MroOrgMonthPlan> insertList = new ArrayList<MroOrgMonthPlan>();
List<Map> wrongList = new ArrayList<Map>();


// 拿到前台传过来的计划类型planType
ActionBase actionBase = new ActionBase();
HttpServletRequest request = actionBase.getRequest();
double planType = Double.parseDouble(request
.getParameter("planType"));


if (sheet != null) {
for (int i = 1; i <= sheet.getLastRowNum(); i++) {
Map mapError = new HashMap();
Row row = sheet.getRow(i);
MroOrgMonthPlan mroOrgMonthPlan = new MroOrgMonthPlan();
Map wrongAccoutData = new HashMap();
String wrongMessage = "";
// 不能为空
if (row.getCell(0) == null) {
wrongMessage += "部门代号不能为空";
addWrongAccoutData(wrongAccoutData, wrongMessage, i,
row);
wrongList.add(wrongAccoutData);
continue;
}
if (row.getCell(3) == null) {
wrongMessage += "计划起始日期不能为空";
addWrongAccoutData(wrongAccoutData, wrongMessage, i,
row);
wrongList.add(wrongAccoutData);
continue;
}
if (row.getCell(4) == null) {
wrongMessage += "计划截止日期不能为空";
addWrongAccoutData(wrongAccoutData, wrongMessage, i,
row);
wrongList.add(wrongAccoutData);
continue;
}
if (row.getCell(5) == null) {
wrongMessage += "维修类型不能为空";
addWrongAccoutData(wrongAccoutData, wrongMessage, i,
row);
wrongList.add(wrongAccoutData);
continue;
}


// 取到单元格的值
String deptNo = "";
Double planYear = null;
Double planMonth = null;
Date planBeginDate = null;
Date planEndDate = null;
String maintainType = "";
String modelSeriesNo = "";
String factNo = "";
String planMemo = "";


if (row.getCell(0).getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
deptNo = (int) row.getCell(0).getNumericCellValue()
+ "";
} else if (row.getCell(0).getCellType() == HSSFCell.CELL_TYPE_STRING) {
deptNo = row.getCell(0).getStringCellValue();
}
// 验证数据类型错误
if (row.getCell(1) != null) {
if (row.getCell(1).getCellType() != HSSFCell.CELL_TYPE_NUMERIC) {
wrongMessage += "计划年度数据类型错误";
addWrongAccoutData(wrongAccoutData, wrongMessage,
i, row);
wrongList.add(wrongAccoutData);
continue;
}
planYear = row.getCell(1).getNumericCellValue();
}
if (row.getCell(2) != null) {
if (row.getCell(2).getCellType() != HSSFCell.CELL_TYPE_NUMERIC) {
wrongMessage += "计划月份数据类型错误";
addWrongAccoutData(wrongAccoutData, wrongMessage,
i, row);
wrongList.add(wrongAccoutData);
continue;
}
planMonth = row.getCell(2).getNumericCellValue();
}
if (row.getCell(3).getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
planBeginDate = row.getCell(3).getDateCellValue();
} else if (row.getCell(3).getCellType() == HSSFCell.CELL_TYPE_STRING) {
wrongMessage += "计划起始日期格式错误！";
addWrongAccoutData(wrongAccoutData, wrongMessage, i,
row);
wrongList.add(wrongAccoutData);
continue;
}
if (row.getCell(4).getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
planEndDate = row.getCell(4).getDateCellValue();
} else if (row.getCell(4).getCellType() == HSSFCell.CELL_TYPE_STRING) {
wrongMessage += "计划截止日期格式错误！";
addWrongAccoutData(wrongAccoutData, wrongMessage, i,
row);
wrongList.add(wrongAccoutData);
continue;
}
if (row.getCell(5).getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
maintainType = (int) row.getCell(5)
.getNumericCellValue()
+ "";
} else if (row.getCell(5).getCellType() == HSSFCell.CELL_TYPE_STRING) {
maintainType = row.getCell(5).getStringCellValue();
}
if (row.getCell(6) != null) {
if (row.getCell(6).getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
modelSeriesNo = (int) row.getCell(6)
.getNumericCellValue()
+ "";
} else if (row.getCell(6).getCellType() == HSSFCell.CELL_TYPE_STRING) {
modelSeriesNo = row.getCell(6).getStringCellValue();
}
}
if (row.getCell(7) != null) {
if (row.getCell(7).getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
factNo = (int) row.getCell(7).getNumericCellValue()
+ "";
} else if (row.getCell(7).getCellType() == HSSFCell.CELL_TYPE_STRING) {
factNo = row.getCell(7).getStringCellValue();
}
}
if (row.getCell(8) != null) {
if (row.getCell(8).getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
planMemo = (int) row.getCell(8)
.getNumericCellValue()
+ "";
} else if (row.getCell(8).getCellType() == HSSFCell.CELL_TYPE_STRING) {
planMemo = row.getCell(8).getStringCellValue();
}
}


// 验证未定义
if (mroOrgMonthPlanService.getDeptNoCount(deptNo) == 0) {
wrongMessage += "部门编号取值未定义";
addWrongAccoutData(wrongAccoutData, wrongMessage, i,
row);
wrongList.add(wrongAccoutData);
continue;
}
if (mroOrgMonthPlanService.getMaintainCount(maintainType) == 0) {
wrongMessage += "维修类型取值未定义";
addWrongAccoutData(wrongAccoutData, wrongMessage, i,
row);
wrongList.add(wrongAccoutData);
continue;
}


if (mroOrgMonthPlanService
.getModelSeriesNoCount(modelSeriesNo) == 0) {
wrongMessage += "装备系列编号取值未定义";
addWrongAccoutData(wrongAccoutData, wrongMessage, i,
row);
wrongList.add(wrongAccoutData);
continue;
}


if (mroOrgMonthPlanService.getFactNoCount(factNo) == 0) {
wrongMessage += "装备工厂编号取值未定义";
addWrongAccoutData(wrongAccoutData, wrongMessage, i,
row);
wrongList.add(wrongAccoutData);
continue;
}
// 数据重复
if (mroOrgMonthPlanService.getMonthPlanCount(deptNo,
planBeginDate, planEndDate, maintainType, factNo) > 0) {
wrongMessage += "该记录数据重复";
addWrongAccoutData(wrongAccoutData, wrongMessage, i,
row);
wrongList.add(wrongAccoutData);
continue;
}


mroOrgMonthPlan.setDeptNo(deptNo);
mroOrgMonthPlan.setPlanYear(planYear);
mroOrgMonthPlan.setPlanMonth(planMonth);
mroOrgMonthPlan.setPlanBeginDate(planBeginDate);
mroOrgMonthPlan.setPlanEndDate(planEndDate);
mroOrgMonthPlan.setMaintainType(Double
.parseDouble(maintainType));
mroOrgMonthPlan.setModelSeriesNo(modelSeriesNo);
mroOrgMonthPlan.setFactNo(factNo);
mroOrgMonthPlan.setPlanMemo(planMemo);
mroOrgMonthPlan.setPlanType(planType);
mroOrgMonthPlan.setPlanState("已编制");


// 给创建人等6个必填字段赋值
PojoUtil.copyProperties(mroOrgMonthPlan, mroOrgMonthPlan);
// 将对象装入对象集合当中
insertList.add(mroOrgMonthPlan);
// 添加效验通过的数据
mroOrgMonthPlanService.insertRightData(mroOrgMonthPlan);
}
}
saveCount = insertList.size();
wrongCount = wrongList.size();


// 将错误信息放到session中，用来导出excel
DoradoContext.getContext().setAttribute(DoradoContext.SESSION,
"wrongData", wrongList);
message = "success";
}
return SUCCESS;
}


// 将错误信息加到Map中
public void addWrongAccoutData(Map wrongAccoutData, String wrongMessage,
int i, Row row) {
// 取到错误信息单元格的值
String wdeptNo = "";
String wplanYear = "";
String wplanMonth = "";
String wplanBeginDate = "";
String wplanEndDate = "";
String wmaintainType = "";
String wmodelSeriesNo = "";
String wfactNo = "";
String wplanMemo = "";
if (row.getCell(0) != null) {
if (row.getCell(0).getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
wdeptNo = (int) row.getCell(0).getNumericCellValue() + "";
} else if (row.getCell(0).getCellType() == HSSFCell.CELL_TYPE_STRING) {
wdeptNo = row.getCell(0).getStringCellValue();
}
}
if (row.getCell(1) != null) {
if (row.getCell(1).getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
wplanYear = (int) row.getCell(1).getNumericCellValue() + "";
} else if (row.getCell(1).getCellType() == HSSFCell.CELL_TYPE_STRING) {
wplanYear = row.getCell(1).getStringCellValue();
}
}
if (row.getCell(2) != null) {
if (row.getCell(2).getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
wplanMonth = (int) row.getCell(2).getNumericCellValue() + "";
} else if (row.getCell(2).getCellType() == HSSFCell.CELL_TYPE_STRING) {
wplanMonth = row.getCell(2).getStringCellValue();
}
}
if (row.getCell(3) != null) {
if (row.getCell(3).getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
wplanBeginDate = DateUtils.Date2String(row.getCell(3)
.getDateCellValue(), "yyyy-MM-dd");
} else if (row.getCell(3).getCellType() == HSSFCell.CELL_TYPE_STRING) {
wplanBeginDate = row.getCell(3).getStringCellValue();
}
}
if (row.getCell(4) != null) {
if (row.getCell(4).getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
wplanEndDate = DateUtils.Date2String(row.getCell(4)
.getDateCellValue(), "yyyy-MM-dd");
} else if (row.getCell(4).getCellType() == HSSFCell.CELL_TYPE_STRING) {
wplanEndDate = row.getCell(4).getStringCellValue();
}
}
if (row.getCell(5) != null) {
if (row.getCell(5).getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
wmaintainType = (int) row.getCell(5).getNumericCellValue() + "";
} else if (row.getCell(5).getCellType() == HSSFCell.CELL_TYPE_STRING) {
wmaintainType = row.getCell(5).getStringCellValue();
}
}
if (row.getCell(6) != null) {
if (row.getCell(6).getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
wmodelSeriesNo = (int) row.getCell(6).getNumericCellValue()
+ "";
} else if (row.getCell(6).getCellType() == HSSFCell.CELL_TYPE_STRING) {
wmodelSeriesNo = row.getCell(6).getStringCellValue();
}
}
if (row.getCell(7) != null) {
if (row.getCell(7).getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
wfactNo = (int) row.getCell(7).getNumericCellValue() + "";
} else if (row.getCell(7).getCellType() == HSSFCell.CELL_TYPE_STRING) {
wfactNo = row.getCell(7).getStringCellValue();
}
}
if (row.getCell(8) != null) {
if (row.getCell(8).getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
wplanMemo = (int) row.getCell(8).getNumericCellValue() + "";
} else if (row.getCell(8).getCellType() == HSSFCell.CELL_TYPE_STRING) {
wplanMemo = row.getCell(8).getStringCellValue();
}
}
// 将错误信息加到Map中
wrongAccoutData.put("rowNum", i + 1);
wrongAccoutData.put("wrongMessage", wrongMessage);
wrongAccoutData.put("deptNo", wdeptNo);
wrongAccoutData.put("planYear", wplanYear);
wrongAccoutData.put("planMonth", wplanMonth);
wrongAccoutData.put("planBeginDate", wplanBeginDate);
wrongAccoutData.put("planEndDate", wplanEndDate);
wrongAccoutData.put("maintainType", wmaintainType);
wrongAccoutData.put("modelSeriesNo", wmodelSeriesNo);
wrongAccoutData.put("factNo", wfactNo);
wrongAccoutData.put("planMemo", wplanMemo);
}


public MroOrgMonthPlanService getMroOrgMonthPlanService() {
return mroOrgMonthPlanService;
}


public void setMroOrgMonthPlanService(
MroOrgMonthPlanService mroOrgMonthPlanService) {
this.mroOrgMonthPlanService = mroOrgMonthPlanService;
}


public File getExcelFile() {
return excelFile;
}


public void setExcelFile(File excelFile) {
this.excelFile = excelFile;
}


public String getExcelFileFileName() {
return excelFileFileName;
}


public void setExcelFileFileName(String excelFileFileName) {
this.excelFileFileName = excelFileFileName;
}


public String getSavePath() {
return savePath;
}


public void setSavePath(String savePath) {
this.savePath = savePath;
}


public String getExcelFileContentType() {
return excelFileContentType;
}


public void setExcelFileContentType(String excelFileContentType) {
this.excelFileContentType = excelFileContentType;
}


public int getSaveCount() {
return saveCount;
}


public void setSaveCount(int saveCount) {
this.saveCount = saveCount;
}


public int getWrongCount() {
return wrongCount;
}


public void setWrongCount(int wrongCount) {
this.wrongCount = wrongCount;
}


public String getMessage() {
return message;
}


public void setMessage(String message) {
this.message = message;
}


}

导出的action ：

 package avicit.mro.module.mainsupport.maintoolequipmentquery.action;
import java.io.IOException;
import java.util.List;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import jxl.Workbook;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.write.Label;
import jxl.write.NumberFormat;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;


import avicit.mro.module.maintainplan.equipability.domain.MroToolEquAbilityService;


import com.cape.platform.framework.view.base.ActionBase;
import com.cape.platform.module.system.loader.systemloader.impl.SysLookupLoader;
import com.opensymphony.xwork2.ActionSupport;


public class ExportExcelDataAction extends ActionSupport {


MroToolEquAbilityService mroToolEquAbilityService;


public void exportExcelData() throws Exception {
ActionBase actionBase = new ActionBase();
HttpServletRequest request = actionBase.getRequest();
HttpServletResponse response = actionBase.getResponse();
String equType = request.getParameter("EQU_TYPE");
String equListType = request.getParameter("EQU_LIST_TYPE");
String deptName = request.getParameter("DEPT_NAME");
// 设置单元格表头
response.reset();
response.setContentType("application/msxls");
response.setHeader("Content-Disposition", "attachment; filename="
+ new String("维修工具设备查询统计.xls".getBytes("GBK"), "ISO8859-1"));
response.setHeader("Cache-Control", "no-cache");
// 设置列文字格式
WritableFont fontTitle = new WritableFont(
WritableFont.createFont("宋体"), 11, WritableFont.NO_BOLD, false,
UnderlineStyle.NO_UNDERLINE);
WritableCellFormat formatTitle = new WritableCellFormat(fontTitle);
formatTitle.setAlignment(jxl.format.Alignment.CENTRE);
formatTitle.setBorder(Border.ALL, BorderLineStyle.THIN);
formatTitle.setBackground(Colour.LIGHT_GREEN);


// 设置单元格文字格式
WritableFont fontBody = new WritableFont(WritableFont.createFont("宋体"),
10, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE);
WritableCellFormat formatBody = new WritableCellFormat(fontBody);
formatBody.setBorder(Border.ALL, BorderLineStyle.THIN);
formatBody.setAlignment(jxl.format.Alignment.CENTRE);
NumberFormat nf = new NumberFormat("0.00");
WritableCellFormat wcfN = new WritableCellFormat(fontBody, nf);
wcfN.setBorder(Border.ALL, BorderLineStyle.THIN);
wcfN.setAlignment(jxl.format.Alignment.CENTRE);


// 获取集合数据
List list = mroToolEquAbilityService.findAllByMap(equType, equListType,
deptName);


WritableWorkbook book = null;
try {
book = Workbook.createWorkbook(response.getOutputStream());
// 生成名为“保密柜清单错误信息列表”的工作表，参数0表示这是第一页
WritableSheet sheet = book.createSheet("维修工具设备统计列表", 0);
Label label = new Label(0, 0, "行数", formatTitle);
sheet.addCell(label);
label = new Label(1, 0, "部门代号", formatTitle);
sheet.addCell(label);
label = new Label(2, 0, "部门名称", formatTitle);
sheet.addCell(label);
label = new Label(3, 0, "设备种类", formatTitle);
sheet.addCell(label);
label = new Label(4, 0, "设备小类", formatTitle);
sheet.addCell(label);
label = new Label(5, 0, "设备规格", formatTitle);
sheet.addCell(label);
label = new Label(6, 0, "设备定额数量", formatTitle);
sheet.addCell(label);
label = new Label(7, 0, "设备实际数量", formatTitle);
sheet.addCell(label);
label = new Label(8, 0, "设备可用数量", formatTitle);
sheet.addCell(label);
label = new Label(9, 0, "管理方式", formatTitle);
sheet.addCell(label);


// 设置单元格宽度
sheet.setColumnView(0, 10);
sheet.setColumnView(1, 20);
sheet.setColumnView(2, 20);
sheet.setColumnView(3, 20);
sheet.setColumnView(4, 10);
sheet.setColumnView(5, 20);
sheet.setColumnView(6, 20);
sheet.setColumnView(7, 20);
sheet.setColumnView(8, 20);
sheet.setColumnView(9, 20);


if (list != null && list.size() > 0) {
for (int i = 0; i < list.size(); i++) {
Object[] obj = (Object[]) list.get(i);
label = new Label(0, i + 1, i + 1 + "", formatBody);
sheet.addCell(label);
label = new Label(1, i + 1, obj[0] + "", formatBody);
sheet.addCell(label);
label = new Label(2, i + 1, obj[1] + "", formatBody);
sheet.addCell(label);
label = new Label(3, i + 1, SysLookupLoader
.getLookupValueByCode("MRO_DEV_KIND", obj[2] + ""),
formatBody);
sheet.addCell(label);
label = new Label(4, i + 1, SysLookupLoader
.getLookupValueByCode("MRO_DEV_LIST_TYPE", obj[3]
+ ""), formatBody);
sheet.addCell(label);
label = new Label(5, i + 1, obj[4] + "", formatBody);
sheet.addCell(label);
label = new Label(6, i + 1, obj[5] + "", formatBody);
sheet.addCell(label);
label = new Label(7, i + 1, obj[6] + "", formatBody);
sheet.addCell(label);
label = new Label(8, i + 1, obj[7] + "", formatBody);
sheet.addCell(label);
label = new Label(9, i + 1, obj[8] + "", formatBody);
sheet.addCell(label);
}
}
} catch (Exception e) {
e.printStackTrace();
} finally {
// 写出并关闭流
if (book != null) {
try {
book.write();
book.close();


} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (WriteException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}


public void setMroToolEquAbilityService(
MroToolEquAbilityService mroToolEquAbilityService) {
this.mroToolEquAbilityService = mroToolEquAbilityService;
}
}

jsp 页面:

   <form name="monthlyUploadForm" id="monthlyUploadForm"
enctype="multipart/form-data" action='importMonthly.action?planType=0' method="post">
<d:Layout type="hflow" width="100%">
<br>
                        &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;选择导入的excel文件 
                        <input type="file" class="TextEditor" id="uploadfile"
name="excelFile" style="width: 50%">
                        &nbsp;<d:Button id="buttonSubmit" />
</d:Layout>
 </form>