java通过JXL生成EXCEL XLS 文件下载

最新推荐文章于 2021-03-19 19:36:52 发布

原创最新推荐文章于 2021-03-19 19:36:52 发布 · 272 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#java

web 同时被 2 个专栏收录

12 篇文章

订阅专栏

java

5 篇文章

订阅专栏

本文介绍如何使用JXL库创建并导出Excel文件，包括创建工作簿、工作表，设置表头样式及填充具体数据，以及如何处理中文文件名和输出流的关闭。

写道

需要导入jxl.jar 包

public class GenExcel {
  public void createExcel(OutputStream os) throws WriteException,IOException{
	  //创建工作薄
	  WritableWorkbook workbook = Workbook.createWorkbook(os);
	  //创建新的一页
	  WritableSheet sheet = workbook.createSheet("First Sheet", 0);
	  //创建要显示的内容,创建一个单元格, 第一个参数为列坐标,第二个为行坐标,第三个参数为内容
	  Label xuexiao = new Label(0,0,"学校");
	  sheet.addCell(xuexiao);
	   Label zhuanye = new Label(1,0,"专业");
       sheet.addCell(zhuanye);
       Label jingzhengli = new Label(2,0,"专业竞争力");
       sheet.addCell(jingzhengli);
       
       Label qinghua = new Label(0,1,"清华大学");
       sheet.addCell(qinghua);
       Label jisuanji = new Label(1,1,"计算机专业");
       sheet.addCell(jisuanji);
       Label gao = new Label(2,1,"高");
       sheet.addCell(gao);
       
       Label beida = new Label(0,2,"北京大学");
       sheet.addCell(beida);
       Label falv = new Label(1,2,"法律专业");
       sheet.addCell(falv);
       Label zhong = new Label(2,2,"中");
       sheet.addCell(zhong);
       
       Label ligong = new Label(0,3,"北京理工大学");
       sheet.addCell(ligong);
       Label hangkong = new Label(1,3,"航空专业");
       sheet.addCell(hangkong);
       Label di = new Label(2,3,"低");
       sheet.addCell(di);
       
       //把创建的内容写入到输出流中
       
       workbook.write();
       workbook.close();
       os.close();
  }
}

import java.io.IOException;
import java.io.OutputStream;
import java.util.Calendar;
import java.util.Date;

import jxl.Workbook;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.write.Boolean;
import jxl.write.DateFormats;
import jxl.write.DateTime;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
public class MutiStyleExcelWrite {
	public void createExcel(OutputStream os) throws WriteException,IOException{
		 //创建工作薄
        WritableWorkbook workbook = Workbook.createWorkbook(os);
        //创建新的一页
        WritableSheet sheet = workbook.createSheet("First Sheet", 0);
        //构造表头
        sheet.mergeCells(0, 0, 4, 0);//添加合并单元格，第一个参数是起始列，第二个参数是起始行，第三个参数是终止列，第四个参数是终止行
        WritableFont bold = new WritableFont(WritableFont.ARIAL,10,WritableFont.BOLD);//设置字体种类和黑体显示,字体为Arial,字号大小为10,采用黑体显示
        WritableCellFormat titleFormate = new WritableCellFormat(bold);//生成一个单元格样式控制对象
        titleFormate.setAlignment(jxl.format.Alignment.CENTRE);//单元格中的内容水平方向居中
        titleFormate.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);//单元格的内容垂直方向居中
        Label title = new Label(0,0,"JExcelApi支持数据类型详细说明",titleFormate);
        sheet.setRowView(0, 600, false);//设置第一行的高度
        sheet.addCell(title);
        
        //创建要显示的具体内容
        WritableFont color = new WritableFont(WritableFont.ARIAL);//选择字体
        color.setColour(Colour.GOLD);//设置字体颜色为金黄色
        WritableCellFormat colorFormat = new WritableCellFormat(color);
        Label formate = new Label(0,1,"数据格式",colorFormat);
        sheet.addCell(formate);
        Label floats = new Label(1,1,"浮点型");
        sheet.addCell(floats);
        Label integers = new Label(2,1,"整型");
        sheet.addCell(integers);
        Label booleans = new Label(3,1,"布尔型");
        sheet.addCell(booleans);
        Label dates = new Label(4,1,"日期格式");
        sheet.addCell(dates);
        
        Label example = new Label(0,2,"数据示例",colorFormat);
        sheet.addCell(example);
        //浮点数据
        //设置下划线
        WritableFont underline= new WritableFont(WritableFont.ARIAL,WritableFont.DEFAULT_POINT_SIZE,WritableFont.NO_BOLD,false,UnderlineStyle.SINGLE);
        WritableCellFormat greyBackground = new WritableCellFormat(underline);
        greyBackground.setBackground(Colour.GRAY_25);//设置背景颜色为灰色
        Number number = new Number(1,2,3.1415926535,greyBackground);
        sheet.addCell(number);
        //整形数据
        WritableFont boldNumber = new WritableFont(WritableFont.ARIAL,10,WritableFont.BOLD);//黑体
        WritableCellFormat boldNumberFormate = new WritableCellFormat(boldNumber);
        Number ints = new Number(2,2,15042699,boldNumberFormate);
        sheet.addCell(ints);
        //布尔型数据
        Boolean bools = new Boolean(3,2,true);
        sheet.addCell(bools);
        //日期型数据
        //设置黑体和下划线
        WritableFont boldDate = new WritableFont(WritableFont.ARIAL,WritableFont.DEFAULT_POINT_SIZE,WritableFont.BOLD,false,UnderlineStyle.SINGLE);
        WritableCellFormat boldDateFormate = new WritableCellFormat(boldDate,DateFormats.FORMAT1);
        Calendar c = Calendar.getInstance();
        Date date = c.getTime();
        DateTime dt = new DateTime(4,2,date,boldDateFormate);
        sheet.addCell(dt);
        //把创建的内容写入到输出流中，并关闭输出流
        workbook.write();
        workbook.close();
        os.close();
        
    }
}

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%@ page import="java.io.*" %>
<%@ page import="com.jxl.excel.*" %>
<% 
    String fname = "学校竞争力情况";
    OutputStream os = response.getOutputStream();//取得输出流
    response.reset();//清空输出流
    
    //下面是对中文文件名的处理
    response.setCharacterEncoding("UTF-8");//设置相应内容的编码格式
    fname = java.net.URLEncoder.encode(fname,"UTF-8");
    response.setHeader("Content-Disposition","attachment;filename="+new String(fname.getBytes("UTF-8"),"GBK")+".xls");
    response.setContentType("application/msexcel");//定义输出类型
    GenExcel ge = new GenExcel();
    ge.createExcel(os);

 %>
<html>
  <head>
    <title></title>
  </head>
  
  <body>
  </body>
</html>

<%@ page language="java" import="java.util.*" pageEncoding="gbk"%>
<%@ page import="java.io.File"%>
<%@ page import="jxl.Cell"%>
<%@ page import="jxl.Sheet"%>
<%@ page import="jxl.Workbook"%>
<%
	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 'read.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>
		<font size="2"> <%
 	String fileName = "C:/Users/ZEN/Downloads/学校竞争力情况.xls";
 	//根据文件名创建一个文件对象
 	File file = new File(fileName);
 	Workbook wb = Workbook.getWorkbook(file);//从文件流中取得Excel工作区对象
 	//从工作区中取得页，取得这个对象的时候既可以用名称来获得，也可以用序号。
 	Sheet sheet = wb.getSheet(0);
 	String outPut = "";

 	outPut = outPut + "<b>" + fileName + "</b><br>";
 	outPut = outPut + "第一个sheet的名称为：" + sheet.getName() + "<br>";
 	outPut = outPut + "第一个sheet共有：" + sheet.getRows() + "行"
 			+ sheet.getColumns() + "列<br>";
 	outPut = outPut + "具体内容如下：<br>";
 	for (int i = 0; i < sheet.getRows(); i++) {
 		for (int j = 0; j < sheet.getColumns(); j++) {
 			Cell cell = sheet.getCell(j, i);
 			outPut = outPut + cell.getContents() + " ";
 		}
 		outPut = outPut + "<br>";
 	}
 	out.println(outPut);
 %> </font>
	</body>
</html>