java—读取Excel 、导出Excel

来源：互联网发布：香港hr软件排名编辑：程序博客网时间：2024/06/05 11:19

后台代码：

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 blocke1.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:30Cell c41=rs.getCell(4,1);String strc41=c41.getContents();//获取时间 strc51=18:20Cell 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"><!--<link rel="stylesheet" type="text/css" href="styles.css">-->  </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);//创建第一个sheetws.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 responseout.clear();        out = pageContext.pushBody(); %>  </body></html>