POIExcel导入

如果EXCEL文件中存在公式运算值，请按照以下步骤解析：

if (HSSFCell.CELL_TYPE_FORMULA ==cell.getCellType()) {

FormulaEvaluatorevaluator = cell.getSheet().getWorkbook().

getCreationHelper().createFormulaEvaluator();

   evaluator.evaluateFormulaCell(cell);

   CellValue cellValue = evaluator.evaluate(cell);

}

选择上传文件的jsp页面

<table width="95%">

   <tr style="width: 100%">

         <td width="15%">

                   <bean:messagebundle="fs-budget" key="fs_budget_import_titile"/>

         </td>

         <tdwidth="85%" colspan="3">

             <input name="file"type="file" style="width:90%;height:25"class="upload"> <br/>

             <input type="hidden"name="service" value="${param.service}">

             <img src="style/common/images/loading.gif" border="0"

                  id="loadingImg"align="bottom" style="display: none"/>

         </td>

   </tr>

</table>

form关键代码：

public class FsBudgetImportForm  {

         protectedString fdFilename = null;

         protectedFormFile file = null;

         protectedString service = null;

         publicString getFdFilename() {

                   returnfdFilename;

         }

         publicvoid setFdFilename(String fdFilename) {

                   this.fdFilename= fdFilename;

         }

       

         publicFormFile getFile() {

                   returnfile;

         }

         publicvoid setFile(FormFile file) {

                   this.file= file;

         }

         publicString getService() {

                   returnservice;

         }

 

         publicvoid setService(String service) {

                   this.service= service;

         }

}

action关键代码：

public ActionForwardsaveImport(ActionMapping mapping, ActionForm form,

                            HttpServletRequestrequest, HttpServletResponse response)

                            throwsException {

                   TimeCounter.logCurrentTime("Action-saveImport",true, getClass());

                   KmssMessagesmessages = new KmssMessages();

                   try{

                            if(!request.getMethod().equals("POST"))

                                     thrownew UnexpectedRequestException();

                            Stringservice = request.getParameter("service");

                            FsBudgetImportFormimportForm = (FsBudgetImportForm) form;

                            List<FsBudgetImportMessage>messageList = ((IFsBudgetImportService) SpringBeanUtil

                                               .getBean(service)).saveImport(importForm);

request.setAttribute("messageList",messageList);

                   }catch (Exception e) {

                            messages.addError(e);

                   }

 

                   TimeCounter.logCurrentTime("Action-save",false, getClass());

                   if(messages.hasError()) {

                            KmssReturnPage.getInstance(request).addMessages(messages).save(

                                               request);

                            returngetActionForward("failure", mapping, form, request, response);

                   }else {

                            KmssReturnPage.getInstance(request).addMessages(messages)

                                               .addButton(KmssReturnPage.BUTTON_CLOSE).save(request);

                            returngetActionForward("result", mapping, form, request, response);

                   }

         }

service关键代码

public List<FsBudgetImportMessage>saveImport(FsBudgetImportForm importForm)

                            throwsException {

                   List<FsBudgetImportMessage>messages = new ArrayList<FsBudgetImportMessage>();

                   FormFilefile = importForm.getFile();

                   if(file.getFileSize() == 0) {

                            FsBudgetImportMessagemessage = new FsBudgetImportMessage();

                            message.addFailMsg(ResourceUtil.getString(

                                               "fs.budget.import.error.empty","fs-budget"));

                            messages.add(message);

                   }else {

                            POIFSFileSystemfs = new POIFSFileSystem(file.getInputStream());

                            HSSFWorkbookwb = new HSSFWorkbook(fs);

                            HSSFSheetsheet = wb.getSheetAt(0);

                            //取到工作表里面的数据

                            List<FsBudgetCommon>dataList = getDataList(sheet);

                            introwNum = 1;

                            for(FsBudgetCommon fsBudgetCommon : dataList) {

                                     rowNum++;

                                     //保存并添加信息

                                     messages.add(save(fsBudgetCommon,rowNum));

                            }

                   }

                   returnmessages;

         }

private List<FsBudgetCommon>getDataList(HSSFSheet sheet) throws Exception {

                   List<FsBudgetCommon>dataList = new ArrayList<FsBudgetCommon>();

                   for(int i = 1; i <= sheet.getLastRowNum(); i++) {

                            //判断是否空行

                            if(FsBudgetUtil.isBlankRow(sheet.getRow(i))) {

                                     continue;

                            }

                            FsBudgetCommonfsBudgetCommon = new FsBudgetCommon();

                            fsBudgetCommon

                                               .setFdSource(FsBudgetConstant.FS_BUDGET_SOURCE_BY_IMPORT);

                            //公司代码*

                            HSSFCellcell0 = sheet.getRow(i).getCell(0);

                            if(null != cell0) {

                                     StringfdCompanyCode = FsBudgetUtil.getCellValue(cell0);

                                     fsBudgetCommon.setFdCompany(fsBaseAccountingCompanyService

                                                        .findCompanyByCode(fdCompanyCode));

                            }

                            …

                            dataList.add(fsBudgetCommon);

                   }

                   returndataList;

         }