java servlet 导入 导出excel

/**
 * 导出excel
 */
@WebServlet("/ExportEnrollExamine")
public class ExportAchieveExamineServlet extends HttpServlet {
    private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public ExportAchieveExamineServlet() {
        super();
        // TODO Auto-generated constructor stub
    }

    /**
     * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
     */
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        // TODO Auto-generated method stub
    }

    /**
     * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
     */
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        response.setContentType("text/html;charset=utf-8");
        String asd = request.getParameter("jsonData");
        JSONObject jsonData = JSONObject.fromObject(request.getParameter("jsonData"));
        String sqlString = BasicExamineeInfo.queryAchievementBycondition(jsonData.getString("examId"),
                                                                         jsonData.getString("name"),
                                                                         jsonData.getString("IDcard"),
                                                                         jsonData.getString("release_begin_date"),
                                                                         jsonData.getString("release_end_date"),
                                                                         jsonData.getString("apply_operate_type"),
                                                                         jsonData.getString("phone"),
                                                                         jsonData.getString("examinee_exam_result"),"","");
        LinkedList<HashMap<String,Object>> list = AccessDataBaseUtil.getDataTableListKeyValue(sqlString,
                                                                    "name;IDcard;"+
                                                                    "enrollTime;apply_operate_type;phone;examinee_exam_result");
        
        String sqlStringTitle =BasicExaminationInfo.selectExamInfoById(jsonData.getString("examId"));
        LinkedList<HashMap<String,Object>> titleList = AccessDataBaseUtil.getDataTableListKeyValue(sqlStringTitle,"exam_title");
        try {
            HSSFWorkbook wb = new HSSFWorkbook();
            fillExcelData(list,titleList,wb);
            export(response,wb,titleList.get(0).get("exam_title").toString()+"成绩列表"+".xls");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    public void fillExcelData(LinkedList<HashMap<String, Object>> rs,LinkedList<HashMap<String, Object>> title,HSSFWorkbook wb) throws Exception{
        
        HSSFSheet sheet = wb.createSheet("考试成绩");
        //设置成绩为下拉选择
        String[] strs = new String[] { "合格", "不合格" ,"缺考"};
        @SuppressWarnings("deprecation")
        CellRangeAddressList regions = new CellRangeAddressList(3, rs.size()+2, 6, 6);
        DVConstraint constraint = DVConstraint.createExplicitListConstraint(strs);
        HSSFDataValidation dataValidation = new HSSFDataValidation(regions, constraint);
        sheet.addValidationData(dataValidation);
        //设置标题为合并单元格
        HSSFCellStyle style = wb.createCellStyle();
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        sheet.addMergedRegion(new Region(0, (short)0, 1, (short)6));
        HSSFRow row = sheet.createRow((int) 0);
        HSSFCell cell = row.createCell((short) 0);
        cell.setCellValue(title.get(0).get("exam_title").toString());
        cell.setCellStyle(style);
        //设置表头信息
        row = sheet.createRow((int) 2);
        cell = row.createCell((short) 0);  
        cell.setCellValue("序号");  
        cell.setCellStyle(style);
        
        cell = row.createCell((short) 1);  
        cell.setCellValue("姓名");  
        cell.setCellStyle(style);
        
        cell = row.createCell((short) 2);  
        cell.setCellValue("身份证号");  
        cell.setCellStyle(style);
        
        cell = row.createCell((short) 3);  
        cell.setCellValue("报名时间");  
        cell.setCellStyle(style);
        
        cell = row.createCell((short) 4);  
        cell.setCellValue("考试类别");  
        cell.setCellStyle(style);
        
        cell = row.createCell((short) 5);  
        cell.setCellValue("联系电话");  
        cell.setCellStyle(style);
        
        cell = row.createCell((short) 6);  
        cell.setCellValue("考试成绩");  
        cell.setCellStyle(style);
        
        int rowIndex =3;
        for (int i = 0; i <rs.size() ; i++) {
            sheet.autoSizeColumn(i, true);
            
            row = sheet.createRow(rowIndex);  
            cell = row.createCell((short) 0);  
            cell.setCellValue(i+1);
            cell.setCellStyle(style);
            style.setLocked(true);
            cell = row.createCell((short) 1);
            cell.setCellValue((String) rs.get(i).get("name"));
            cell.setCellStyle(style);
            
            cell = row.createCell((short) 2);
            cell.setCellValue((String) rs.get(i).get("IDcard"));
            cell.setCellStyle(style);
            
            cell = row.createCell((short) 3);
            cell.setCellValue((String) rs.get(i).get("enrollTime"));
            cell.setCellStyle(style);
            
            cell = row.createCell((short) 4);
            cell.setCellValue((String) rs.get(i).get("apply_operate_type"));
            cell.setCellStyle(style);
            
            cell = row.createCell((short) 5);
            cell.setCellValue((String) rs.get(i).get("phone"));
            cell.setCellStyle(style);
            
            cell = row.createCell((short) 6);
            cell.setCellValue((String) rs.get(i).get("examinee_exam_result"));
            cell.setCellStyle(style);
            
            sheet.setColumnWidth(1, rs.get(i).get("name").toString().getBytes().length*2*256);
            sheet.setColumnWidth(4, rs.get(i).get("apply_operate_type").toString().getBytes().length*2*256);
            sheet.setColumnWidth(6, rs.get(i).get("examinee_exam_result").toString().getBytes().length*2*256);
            rowIndex++;
        }
    }
    public void export(HttpServletResponse response, Workbook wb,String fileName) throws Exception
    {
        response.setHeader("Content-Disposition", "attachment;filename="+ new String(fileName.getBytes("utf-8"), "iso8859-1"));// 设置头信息
        response.setContentType("application/ynd.ms-excel;charset=UTF-8");
        OutputStream out = response.getOutputStream();
        wb.write(out);
        out.flush();
        out.close();
    }

}

/**
 * 导入excel 判断后缀是否是xls and xlsx
 * @author chengshizhen
 *
 */
public class ImportAchieveExamineServlet extends HttpServlet {

    public void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        doPost(request, response);
    }

    protected  void doPost(HttpServletRequest request,
            HttpServletResponse response) throws ServletException, IOException {
        if (!ServletFileUpload.isMultipartContent(request)) {
            // 如果不是则停止
            PrintWriter writer = response.getWriter();
            writer.println("Error: 表单必须包含 enctype=multipart/form-data");
            writer.flush();
            return;
        }
        PrintWriter out = response.getWriter();
        String sRe = ConstantInfoHelper.generateJSONString(new String[] {
                        ConstantInfoHelper.KEY_MESSAGE_RESULT,
                        ConstantInfoHelper.KEY_MESSAGE_RESULTMESSAGE,
                        ConstantInfoHelper.KEY_MESSAGE_RESULT_RECORD }, new String[] {
                        ConstantInfoHelper.VALUE_MESSAGE_RESULT_FALSE,
                        ConstantInfoHelper.VALUE_MESSAGE_CALL_FAIL,
                        ConstantInfoHelper.VALUE_MESSAGE_CALL_RECORD }, false);
        String examId = request.getParameter("examId");
        DiskFileItemFactory factory = new DiskFileItemFactory();
        ServletFileUpload upload = new ServletFileUpload(factory);
        try {
            List<FileItem> formItems = upload.parseRequest(request);
            for (int i = 0; i < formItems.size(); i++) {
                FileItem item = formItems.get(i);
                if (item.getName().endsWith(".xls")||item.getName().endsWith(".xlsx")) {
                    importXlsx(item.getInputStream(),examId,out);
                } else {
                    sRe = ConstantInfoHelper.generateJSONString(new String[] {
                            ConstantInfoHelper.KEY_MESSAGE_RESULT,
                            ConstantInfoHelper.KEY_MESSAGE_RESULTMESSAGE,
                            ConstantInfoHelper.KEY_MESSAGE_RESULT_RECORD }, new String[] {
                            ConstantInfoHelper.VALUE_MESSAGE_RESULT_FALSE,
                            ConstantInfoHelper.VALUE_MESSAGE_CALL_FAIL,
                            "formatError" }, false);
                    out.print(sRe);
                    return;
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        out.flush();
        out.close();
    }
    /**
     * 读取excel数据
     * @param is
     * @param examId
     * @param out
     */
    public void importXlsx(InputStream is,String examId,PrintWriter out) {
        String sRe = ConstantInfoHelper.generateJSONString(new String[] {
                ConstantInfoHelper.KEY_MESSAGE_RESULT,
                ConstantInfoHelper.KEY_MESSAGE_RESULTMESSAGE,
                ConstantInfoHelper.KEY_MESSAGE_RESULT_RECORD }, new String[] {
                ConstantInfoHelper.VALUE_MESSAGE_RESULT_FALSE,
                ConstantInfoHelper.VALUE_MESSAGE_CALL_FAIL,
                ConstantInfoHelper.VALUE_MESSAGE_CALL_RECORD }, false);
        try {
            Workbook wb = WorkbookFactory.create(is);
            HSSFSheet sheet = (HSSFSheet) wb.getSheetAt(0);  
           
            int rowNum=sheet.getLastRowNum();
            for (int i = 0; i < rowNum-2; i++) {
                HSSFCell name = sheet.getRow(i+3).getCell(1);
                HSSFCell IDcard = sheet.getRow(i+3).getCell(2);
                HSSFCell examinee_exam_result = sheet.getRow(i+3).getCell(6);
                String sqlString = BasicExamineeInfo.selectExamineeInfoByName(name.toString(),IDcard.toString());
                LinkedList<HashMap<String,Object>> examineeId = AccessDataBaseUtil.getDataTableListKeyValue(sqlString,"id");
                if(examineeId.size()!=0){
                    String updateSql = BasicExamineeInfo.updateExamineeResultById(examineeId.get(0).get("id").toString(),examinee_exam_result.toString(),examId);
                    String[] sqlArray = new String[1];
                    sqlArray[0] = updateSql;
                    int[] iRe = AccessDataBaseUtil.executeSQLUpdate(sqlArray);
                    if (iRe == null || iRe.length <= 0) {
                        out.print(sRe);
                        return;
                    }
                }
            }
            sRe = ConstantInfoHelper.generateJSONString(
                    new String[] {
                            ConstantInfoHelper.KEY_MESSAGE_RESULT,
                            ConstantInfoHelper.KEY_MESSAGE_RESULTMESSAGE,
                            ConstantInfoHelper.KEY_MESSAGE_RESULT_RECORD },
                    new String[] {
                            ConstantInfoHelper.VALUE_MESSAGE_RESULT_TRUE,
                            ConstantInfoHelper.VALUE_MESSAGE_CALL_SUCCESS,
                            ConstantInfoHelper.VALUE_MESSAGE_CALL_RECORD },
                    false);
            out.print(sRe);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}