需求: 1,提供客户填写数据的EXCEL模板下载. 2,对客户上传的EXCEL中的数据进行效验
技术: 1,Struts做web的表示层,提供上传,下载 2,利用jxl 对 EXCEL 进行操作.
代码: EXCEL模板的实现.
一,struts中的action提供模板下载:action
- /**
- * 模版下载页面
- * */
- public class ExportTemplateAction extends Action {
- public ActionForward execute(ActionMapping mapping, ActionForm form,
- HttpServletRequest request, HttpServletResponse response)
- throws Exception {
- javax.servlet.http.HttpSession session=request.getSession();
- HMITSession hmitsession= (HMITSession)session.getAttribute( " hmitsession" );
- //获取企业代码,业务规则,用于设置文件目录
- String entCode=hmitsession.getEntCode();
- //设置文件路径,业务规则设置了存于服务器上 的文件路径
- File templateFile = new File(AppConst.getDownloadFileDirectory(entCode)+File.separator+ " Template.xls" );
- if (!templateFile.exists()){
- //设置excel的标题,假设只有一行标题
- String []titles={ " 司机编号(必填)" , " 司机名称(必填)" , " 驾照" , " 身份证" , " 手机" } ;
- //设置sheet名称
- String sheetName= " 司机数据" ;
- //获取excel文件
- templateFile=Util.getExcel(templateFile, sheetName, titles);
- }
- //写入response
- Util.downLoadData(response, templateFile, " Template.xls" );
- return null ;
- }
二,获取EXCEL文件的工具类
- public class Util {
- /**
- * @param 需要校验的字符串
- * @return true为null,false 为非null
- * */
- public static boolean checkNull(String temp){
- return temp== null ? true : false ;
- }
- /**
- * @param temp
- * @return 将null 返回为" " ;
- * */
- public static String notNull(String temp){
- return checkNull(temp)? " " : temp;
- }
- /**
- * 创建一个规定格式的excel,第一行为标题行,每个标题为一个cell
- * @param templateFile 一个文件
- * @param sheetName excel的sheet的名字
- * @param titles 标题列
- * */
- public static File getExcel(File templateFile,String sheetName,String []titles) throws Exception{
- //根据现有文件创建一个可写excel
- WritableWorkbook ww = Workbook.createWorkbook(templateFile);
- /**在这里可以设置编码格式
- WorkbookSettings setting=new WorkbookSettings();
- java.util.Locale locale = new java.util.Locale(" zh" ," CN" );
- setting.setLocale(locale);
- setting.setEncoding(" UFT-8" );
- */
- //创建一个可写的sheet文件 createSheet(sheetName,0),sheet名称,0代表第几个sheet
- WritableSheet ws =ww.createSheet(sheetName, 0 );
- //字体设置
- WritableFont arial10ptBold = new WritableFont (WritableFont.ARIAL, 10 , WritableFont.NO_BOLD);
- arial10ptBold.setColour(Colour.RED);
- //设置格式,这里可以根据不同需要,设置多个 格式,需要WritableFont进行构造
- WritableCellFormat arial10BoldFormat = new WritableCellFormat (arial10ptBold);
- //这里可以设置字体对齐方式等等
- arial10BoldFormat .setAlignment(jxl.format.Alignment.CENTRE);
- //定义单元格
- Label cell= null ;
- /**合并单元格
- ws.mergeCells(0, 0, 8, 0);
- cell=new Label(0,0," 产品信息" ,fontCenter);
- ws.addCell(cell);
- */
- for ( int i = 0 ; i < titles.length; i++) {
- //第一个参数为列,第二个参数为 行,第三个参数为cell的内容,第四个为字体的格式
- cell= new Label(i, 0 ,titles[i],arial10BoldFormat);
- ws.addCell(cell);
- }
- ww.write(); //写入到当前文件
- ww.close();
- ww= null ;
- return templateFile;
- }
- }
- /**
- * 下载设置
- * */
- public static void downLoadData(HttpServletResponse response,File returnFile,String uploadedFileName){
- response.setHeader( " Content-disposition" , " attachment; filename=" +uploadedFileName);
- response.setHeader( " Content-Type" , " application/octet-stream" );
- BufferedInputStream bis = null ; //读excel
- BufferedOutputStream bos = null ; //输出
- try {
- //读取excel文件
- bis = new BufferedInputStream( new FileInputStream(returnFile));
- //写入response的输出流中
- bos= new java.io.BufferedOutputStream(response.getOutputStream());
- byte [] buff = new byte [ 2048 ]; /*设置缓存*/
- int bytesRead;
- while (- 1 != (bytesRead = bis.read(buff, 0 , buff.length))){
- bos.write(buff, 0 , bytesRead);
- }
- } catch (Exception e){
- e.printStackTrace();
- } finally {
- if (bis != null )
- try {
- bis.close();
- } catch (IOException e) {
- e.printStackTrace();
- }
- if (bos != null )
- try {
- bos.close();
- } catch (IOException e) {
- e.printStackTrace();
- }
- }
- }
三,文件上传
- public class ExportAction extends Action {
- public ActionForward execute(ActionMapping mapping, ActionForm form,
- HttpServletRequest request, HttpServletResponse response)
- throws Exception {
- HttpSession session= request.getSession();
- HMITSession hmitsession= (HMITSession)session.getAttribute( " hmitsession" );
- //用户上传数据信息是否正确的标志位
- boolean flag= true ;
- ChauffeurExportForm cef=(ChauffeurExportForm) form;
- /**
- * 获取提交表单信息
- * */
- //获取上传文件
- InputStream uploadedFileStream =cef.getUploadFile().getInputStream();
- String ansy = ChauffeurUtil.notNull(request.getParameter( " asynchronism" )); /*如果需要开启多线程处理 则为1*/
- String email= ChauffeurUtil.notNull(request.getParameter( " emailAddress" )); /*开启线程发送邮件*/
- //上传文件名称
- String fileName=getTempExcelName(cef.getUploadFile().getFileName());
- //写入上传文件夹目录+名称
- String uploadedFileName=AppConst.getUploadFileDirectory(hmitsession.getEntCode())+File.separator+fileName;
- //获取到excel对象
- Workbook workbook = Workbook.getWorkbook(uploadedFileStream);
- //将上传文件放置上传文件目录中
- writeExcel(workbook,uploadedFileName);
- //写入文件
- File returnFile = new File(AppConst.getUploadTempDirectory(hmitsession.getEntCode())+File.separator+fileName);
- //写入临时文件
- WritableWorkbook rww = Workbook.createWorkbook(returnFile,workbook);
- //从临时文件中取得sheet
- Sheet sheet= workbook.getSheet( 0 );
- //设置字体
- WritableFont arial10ptBold = new WritableFont (WritableFont.ARIAL, 10 , WritableFont.NO_BOLD);
- arial10ptBold.setColour(Colour.RED); //红色错误字体
- WritableCellFormat arial10BoldFormat = new WritableCellFormat (arial10ptBold);
- /**
- * 以下为业务判断和操作
- * Label为一个单元格
- * sheet.getRows 获取所有行
- * rww.getSheet(0).addCell(cell) 将cell放入对应的sheet中
- *
- * 获取关系应为 sheet(index) --> sheet(index).getCell(col,row);
- * */
- Label cell= null ;
- StringBuffer error= new StringBuffer( " " );
- for ( int i = 0 ; i < sheet.getRows() ; i++) {
- error.delete( 0 , error.length());
- String chauffeurNo=sheet.getCell( 0 , i).getContents();
- String chauffeurName=sheet.getCell( 1 , i).getContents();
- if (chauffeurNo.equals( " " )){
- cell= new Label( 8 ,i,error.append( " ~司机编号没有填写" ).toString(),arial10BoldFormat);
- rww.getSheet( 0 ).addCell(cell);
- flag= false ;
- }
- if (chauffeurName.equals( " " )){
- cell= new Label( 8 ,i,error.append( " ~司机名称没有填写" ).toString(),arial10BoldFormat);
- rww.getSheet( 0 ).addCell(cell);
- flag= false ;
- }
- }
- // -------------------------------------若干业务逻辑操作省略
- uploadedFileStream.close();
- uploadedFileStream= null ;
- rww.write();
- rww.close();
- rww= null ;
- //记录日志
- if (flag) Util.setLog(request,hmitsession, " wms_car_dirver_add" , " 司机批量新增" );
- //异步
- if (ansy.equals( " 1" )){
- //异步处理
- AsynchronousHandle thread = new AsynchronousHandle( this .getServlet().getServletContext(),
- email,returnFile,flag,uploadedFileName);
- thread.start();
- } else {
- //同步处理
- if (flag){ //无错误
- request.setAttribute( " flag" , " 1" );
- request.setAttribute( " msg" , " 更新顺利完成" );
- } else { //出现错误数据
- request.setAttribute( " flag" , " 2" );
- request.setAttribute( " msg" , " 上传的数据有错误!请检验核对后再次进行上传" );
- //设置下载
- Util.downLoadData( response,returnFile , cef.getUploadFile().getFileName());
- return null ;
- }
- }
- return null ;
- }
- public static String getTempExcelName(String excelName){
- String time = HMITStr.encodeHTML( new Date(), " yyyyMMddHHmmssSS" ); //获取服务器时间
- int posi = excelName.lastIndexOf( " ." );
- String fileName=excelName.substring( 0 ,posi)+time+excelName.substring(posi);
- return fileName;
- }
- public static void writeExcel(Workbook workbook,String uploadedFileName) throws Exception, IOException{
- WritableWorkbook ww = Workbook.createWorkbook( new File(uploadedFileName),workbook);
- ww.write();
- ww.close();
- ww= null ;
- }
- }