java—读取Excel 、导出Excel

小灰灰 2022-06-18 11:57 312阅读 0赞

后台代码：

import java.io.FileInputStream;
    import java.text.SimpleDateFormat;
    import jxl.Cell;
    import jxl.Sheet;
    import jxl.Workbook;
    import jxl.format.Alignment;
    import jxl.format.Border;
    import jxl.format.BorderLineStyle;
    import jxl.format.Colour;
    import jxl.format.UnderlineStyle;
    import jxl.format.VerticalAlignment;
    import jxl.write.WritableCellFormat;
    import jxl.write.WritableFont;
    import jxl.write.WriteException;
    
    
    public class ExcelUtil {
    	/**
    	 * 返回大标题格式
    	 */
    	public static WritableCellFormat createWcfTitle() {
    		Workbook rwb=null;
    		try {
                             //读取Excel文件
                            FileInputStream is = new FileInputStream("C:\\8.8.xls");
    			 rwb=Workbook.getWorkbook(is);
    		} catch (Exception e1) {
    			// TODO Auto-generated catch block
    			e1.printStackTrace();
    		}
    		Sheet rs=rwb.getSheet(0);
    	
    		//获取第一行 ，第一列的值
    		Cell c00=rs.getCell(0,0);
    		
    		String strc00= c00.getContents();
    		
    		//获取第一行，第二列的值
    		
    		Cell c10=rs.getCell(1,0);
    		String strc10=c10.getContents();
    		
    		//获取第二行，第一列的值
    		Cell c01=rs.getCell(0,1);
    		String strc01=c01.getContents();
    		
    		
    		
    		//获取第二行，第一列的值
    		Cell c11=rs.getCell(1,1);
    		String strc11=c11.getContents();
    		
                   //获取时间 strc41=08:30
    		Cell c41=rs.getCell(4,1);
    		String strc41=c41.getContents();
    		//获取时间 strc51=18:20
    		Cell c51=rs.getCell(5,1);
    		String strc51=c51.getContents();
    		
    		try{
    
                     //计算时间差
                     SimpleDateFormat df = new SimpleDateFormat("HH:mm");
    		 System.out.println(strc41);
    		 System.out.println(strc51);
    		   java.util.Date now = df.parse(strc51);
    		   java.util.Date date=df.parse(strc41);
    		   long l=now.getTime()-date.getTime();
    		   System.out.println(l);
    		   long day=l/(24*60*60*1000);
    		   long hour=(l/(60*60*1000)-day*24);
    		   long min=((l/(60*1000))-day*24*60-hour*60);
    		   long s=(l/1000-day*24*60*60-hour*60*60-min*60);
    		   System.out.println(day);
    		   System.out.println(hour);
    		   System.out.println(min);
    		   System.out.println(""+day+"天"+hour+"小时"+min+"分"+s+"秒");
    		   
    		
    		}
    		catch(Exception ex){
    			ex.printStackTrace();
    		}
    		
    		
    		
    		WritableFont wfc_big = new WritableFont(WritableFont.ARIAL, 12, WritableFont.BOLD, false,
    				UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
    		WritableCellFormat wcf_title = new WritableCellFormat(wfc_big);
    		try {
    			wcf_title.setBorder(Border.ALL, BorderLineStyle.THIN);
    			wcf_title.setAlignment(Alignment.CENTRE);
    			wcf_title.setVerticalAlignment(VerticalAlignment.CENTRE);
    			wcf_title.setWrap(true);
    
    		} catch (WriteException e) {
    			e.printStackTrace();
    		}
    		return wcf_title;
    	}
    	
    	
    	/**
    	 * 返回正文格式
    	 */
    	public static WritableCellFormat createWcfText() {
    		WritableFont wfc_small = new WritableFont(WritableFont.ARIAL, 12, WritableFont.NO_BOLD, false,
    				UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
    		WritableCellFormat wcf_text = new WritableCellFormat(wfc_small);
    		try {
    			wcf_text.setBorder(Border.ALL, BorderLineStyle.THIN);
    			wcf_text.setAlignment(Alignment.LEFT);
    			wcf_text.setVerticalAlignment(VerticalAlignment.CENTRE);
    		} catch (WriteException e) {
    			e.printStackTrace();
    		}
    		return wcf_text;
    	}
    }

前台代码：

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
    <%@page import="java.io.*"%>
    <%@page import="jxl.write.*"%>
    <%@page import="jxl.Workbook"%>
    <%@page import="com.ying.ExcelUtil"%>
    
    <%
    String path = request.getContextPath();
    String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
    %>
    
    <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
    <html>
      <head>
        <base href="<%=basePath%>">
        
        <title>My JSP 'exportExcel.jsp' starting page</title>
        
    	<meta http-equiv="pragma" content="no-cache">
    	<meta http-equiv="cache-control" content="no-cache">
    	<meta http-equiv="expires" content="0">    
    	<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
    	<meta http-equiv="description" content="This is my page">
    	
    
      </head>
      
      <body>
        <%
    	response.reset();
    	response.setContentType("application/vnd.ms-excel;charset=UTF-8");
    	response.setHeader("Content-Disposition", "attachment;filename=" + new String("Excel文件.xls".getBytes("utf-8"), "iso8859-1"));
    
    	OutputStream os = response.getOutputStream();
    
    	WritableWorkbook wwb = null;
    	WritableCellFormat wcf_title = ExcelUtil.createWcfTitle();
    	WritableCellFormat wcf_text = ExcelUtil.createWcfText();
    	wwb = Workbook.createWorkbook(os);//将 WritableWorkbook 写入到输出流
    	WritableSheet ws = wwb.createSheet("sheet1", 0);//创建第一个sheet
    	ws.mergeCells(0, 0, 3, 0);//合并单元格
    	
    	
    
    	Label lbl = null;
    	lbl = new Label(0, 0, "大标题" , wcf_title);
    	ws.addCell(lbl);
    
    	lbl = new Label(0, 1, "列标题1", wcf_text);
    	ws.addCell(lbl);
    	lbl = new Label(1, 1, "列标题2", wcf_text);
    	ws.addCell(lbl);
    	lbl = new Label(2, 1, "列标题3", wcf_text);
    	ws.addCell(lbl);
    	lbl = new Label(3, 1, "列标题4", wcf_text);
    	ws.addCell(lbl);
    
    	for (int i = 0; i < 3; i++) //设置宽度
    		ws.setColumnView(i, 15);
    
    	os.flush();
    	wwb.write();
    	wwb.close();
    	os.close();
    	
           //解决异常java.lang.IllegalStateException: getOutputStream() has already been called for this response
    	out.clear();
            out = pageContext.pushBody(); 
    %>
      </body>
    </html>