1、生成excel的方法有两种:使用poi.jar和jxl.jar,本章介绍jxl使用
jxl.jar是通过java操作excel表格的工具类库,功能如下:
1)支持Excel 97-2003的所有版本
2)生成Excel 2003标准格式
3)支持字体、数字、日期操作
4)能够修饰单元格属性
5)支持图像(仅png格式)和图表
说明:
Excel 2003及以下的版本:一张表最大支持65536行数据,256列。也就是说excel2003完全不可能实现单sheet页,百万数据导出的需求,但是通过创建多sheet页实现见:示列4
Excel 2007-2010版本:一张表最大支持1048576行,16384列;
使用poi.jar生成exlce请参看另一篇博文:
简单示列1:直接创建
import java.io.File;
import jxl.Workbook;
import jxl.format.Colour;
import jxl.format.ScriptStyle;
import jxl.format.UnderlineStyle;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
/**
*
* @classDesc: 功能描述:使用jxl工具excle生成
* 1)在本地 D:\ceshiZJB\createExcle目录下 创建新文件:excel文件示例.xls
* 2)标题行:字体格式为宋体,13号字,加粗 3)其他行:字体格式为宋体,11号字,不加粗 4)奇数列:字体为红色
* @author: zjb
* @createTime: 创建时间:2018-8-3 下午4:09:53
* @version: v1.0
* @copyright:pactera
*/
public class JxlTest1_New {
/**
* java创建excel简单示例
*/
public static void main(String args[]) {
createExcel();
}
public static void createExcel() {
try {
String fileNameAndPath = "D:\\ceshiZJB\\createExcle\\JxlExcel文件示例.xls";
// 1.要创建一个可读写的工作簿
WritableWorkbook book = Workbook.createWorkbook(new File(
fileNameAndPath));
// 2.生成名为"测试"的sheet页,参数0表示这是第一页
WritableSheet sheet = book.createSheet("测试1", 0);
WritableSheet sheet2 = book.createSheet("测试2", 1);
WritableSheet sheet3 = book.createSheet("测试3", 2);
// 3.1设置首行单元格内容:字体格式为宋体,13号字,加粗
WritableFont firtRowFont = new WritableFont(
WritableFont.createFont("宋体"), 13, WritableFont.BOLD);
WritableCellFormat firstRowFormat = new WritableCellFormat(
firtRowFont);
firstRowFormat.setAlignment(jxl.format.Alignment.CENTRE);
firstRowFormat
.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
WritableFont normalFont = new WritableFont(
WritableFont.createFont("宋体"),// 字体
11, // 字号
WritableFont.NO_BOLD, // 不加粗
false, // 斜体
UnderlineStyle.NO_UNDERLINE, // 下划线
Colour.BLUE2, // 字体颜色
ScriptStyle.NORMAL_SCRIPT);
WritableCellFormat normalFormat = new WritableCellFormat(normalFont);
normalFormat.setAlignment(jxl.format.Alignment.CENTRE);
normalFormat
.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
// 3.2设置部分单元格内容:字体格式为宋体,11号字,不加粗,颜色为红色
WritableFont diffFont = new WritableFont(
WritableFont.createFont("宋体"), 11, WritableFont.NO_BOLD);
diffFont.setColour(Colour.RED);
WritableCellFormat diffFormat = new WritableCellFormat(diffFont);
diffFormat.setAlignment(jxl.format.Alignment.CENTRE);
diffFormat
.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
// 4.1 创建单元格 new Label(列,行,内容,格式);
Label labelA = new Label(0, 0, "第一列标题", firstRowFormat);
Label labelB = new Label(1, 0, "第二列标题", firstRowFormat);
Label labelC = new Label(2, 0, "第三列标题", firstRowFormat);
Label labelD = new Label(3, 0, "第四列标题", firstRowFormat);
// 4.2 将定义好的单元格添加到工作表中
sheet.addCell(labelA);
sheet.addCell(labelB);
sheet.addCell(labelC);
sheet.addCell(labelD);
// i:行
for (int i = 1; i <= 10; i++) {
Label lab1 = new Label(0, i, "第" + i + "行第1列", normalFormat);
Label lab2 = new Label(1, i, "第" + i + "行第2列", diffFormat);
Label lab3 = new Label(2, i, "第" + i + "行第3列", normalFormat);
Label lab4 = new Label(3, i, "第" + i + "行第4列", diffFormat);
sheet.addCell(lab1);
sheet.addCell(lab2);
sheet.addCell(lab3);
sheet.addCell(lab4);
}
// 5.1写入sheet工作簿
book.write();
book.close();
System.out.println("创建文件成功!");
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
}
}
}
View Code
简单示例2:读取模板excle后新建
import jxl.Workbook;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
/**
*
* @classDesc: 功能描述:
* 1)在本地 D:\ceshiZJB\createExcle目录下 读取原文件:excel文件示例.xls 创建JxlExcel文件示例_new1.xls,
* 并对文件做相应的修改
* @author: zjb
* @createTime: 创建时间:2018-8-3 下午4:09:53
* @version: v1.0
* @copyright:pactera
*/
public class JxlTest2_modifySimple {
/**
* java创建excel简单示例
*/
public static void main(String args[]) {
createExcel();
}
public static void createExcel() {
try {
String sourceFile = "D:\\ceshiZJB\\createExcle\\JxlExcel文件示例.xls";
String newFile = "D:\\ceshiZJB\\createExcle\\JxlExcel文件示例_new1.xls";
//1.1先获得原始的xls工作簿
Workbook sourceWorkBook= Workbook.getWorkbook(new File(sourceFile));
//1.2要创建一个可读写的xls工作簿副本
WritableWorkbook book = Workbook.createWorkbook(new File(newFile),sourceWorkBook);
//2.1获取要操作的sheet页
WritableSheet sheet = book.getSheet("测试1");
sheet.setName("复制后修改测试");
book.removeSheet(1); // 移除多余的标签页
book.removeSheet(2);
//mergeCells(int col1, int row1, int col2, int row2)
sheet.mergeCells(0, 0, 3, 0); // 合并单元格
sheet.setRowView(0, 600); // 设置行的高度
sheet.setColumnView(0, 10); // 设置第一列的宽度
sheet.setColumnView(1, 20); // 设置第二列的宽度
sheet.setColumnView(2, 30); // 设置第三列的宽度
sheet.setColumnView(3, 40); // 设置第四列的宽度
//5.1写入sheet工作簿
book.write();
book.close();
System.out.println("创建文件成功!");
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
}
}
}
View Code
示例3:读取原excle后进行修改(包含常用功能实现)
import java.io.File;
import java.io.IOException;
import java.net.MalformedURLException;
import java.net.URL;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import jxl.CellType;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.ScriptStyle;
import jxl.format.UnderlineStyle;
import jxl.format.VerticalAlignment;
import jxl.read.biff.BiffException;
import jxl.write.Blank;
import jxl.write.DateFormat;
import jxl.write.DateFormats;
import jxl.write.DateTime;
import jxl.write.Formula;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.NumberFormat;
import jxl.write.WritableCell;
import jxl.write.WritableCellFeatures;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableHyperlink;
import jxl.write.WritableImage;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
/**
*
* @classDesc: 功能描述:
* 1)在本地 D:\ceshiZJB\createExcle目录下 读取原文件:excel文件示例.xls 创建JxlExcel文件示例_new2.xls,
* 并对文件做相应的修改:插入图片、生成下拉列表,下划线,斜体字
* @author: zjb
* @createTime: 创建时间:2018-8-3 下午5:31:11
* @version: v1.0
* @copyright:pactera
*/
public class JxlTest3_modifyComplex1 {
/**
* @param args
* @throws IOException
* @throws BiffException
* @throws WriteException
*/
public static void main(String[] args) throws IOException, BiffException, WriteException {
String sourceFile = "D:\\ceshiZJB\\createExcle\\JxlExcel文件示例.xls";
String newFile = "D:\\ceshiZJB\\createExcle\\JxlExcel文件示例_new2.xls";
Workbook wb = Workbook.getWorkbook(new File(sourceFile)); // 获得原始文档
WritableWorkbook workbook = Workbook.createWorkbook(new File(newFile),wb); // 创建一个可读写的副本
/**
* 定义与设置Sheet
*/
WritableSheet sheet = workbook.getSheet(0);
sheet.setName("修改后"); // 给sheet页改名
/* workbook.removeSheet(1); // 移除多余的标签页
workbook.removeSheet(2); */
sheet.mergeCells(0, 0, 4, 0); // 合并单元格
sheet.setRowView(0, 600); // 设置行的高度
sheet.setColumnView(0, 30); // 设置列的宽度
sheet.setColumnView(1, 20); // 设置列的宽度
WritableCell cell = null;
WritableCellFormat wcf = null;
Label label = null;
WritableCellFeatures wcfeatures = null;
// 更改标题字体
cell = sheet.getWritableCell(0,0);
WritableFont titleWf = new WritableFont(WritableFont.createFont("仿宋_GB2312"),// 字体
20,//WritableFont.DEFAULT_POINT_SIZE, // 字号
WritableFont.NO_BOLD, // 粗体
false, // 斜体
UnderlineStyle.NO_UNDERLINE, // 下划线
Colour.BLUE2, // 字体颜色
ScriptStyle.NORMAL_SCRIPT);
wcf = new WritableCellFormat(titleWf);
wcf.setBackground(Colour.GRAY_25);// 设置单元格的背景颜色
wcf.setAlignment(Alignment.CENTRE); // 设置对齐方式
wcf.setBorder(Border.ALL, BorderLineStyle.THICK); // 添加边框
cell.setCellFormat(wcf);
// 将B3的字体改为仿宋_GB2312
cell = sheet.getWritableCell(1,2);
WritableFont fs = new WritableFont(WritableFont.createFont("仿宋_GB2312"),
11);
wcf = new WritableCellFormat(fs);
cell.setCellFormat(wcf);
// 将B4的字号改为20
cell = sheet.getWritableCell(1,3);
WritableFont size20 = new WritableFont(WritableFont.createFont("宋体"),
20);
wcf = new WritableCellFormat(size20);
cell.setCellFormat(wcf);
// 将B5的字体改为加粗
cell = sheet.getWritableCell(1,4);
WritableFont bold = new WritableFont(WritableFont.createFont("宋体"),
11,
WritableFont.BOLD);
wcf = new WritableCellFormat(bold);
cell.setCellFormat(wcf);
// 将B6的字体改为倾斜
cell = sheet.getWritableCell(1,5);
WritableFont italic = new WritableFont(WritableFont.createFont("宋体"),
11,
WritableFont.NO_BOLD,
true);
wcf = new WritableCellFormat(italic);
cell.setCellFormat(wcf);
// 将B7字体加下划线
cell = sheet.getWritableCell(1,6);
WritableFont underline = new WritableFont(WritableFont.createFont("宋体"),
11,
WritableFont.NO_BOLD,
false,
UnderlineStyle.SINGLE);
wcf = new WritableCellFormat(underline);
cell.setCellFormat(wcf);
// 将B8的文字改为“待修改文字-已修改”
cell = sheet.getWritableCell(1,7);
if (cell.getType() == CellType.LABEL)
{
Label lc = (Label) cell;
lc.setString(lc.getString() + " - 已修改");
}
// 将B9文字对齐方式改为垂直居中、右对齐
cell = sheet.getWritableCell(1,8);
WritableFont align = new WritableFont(WritableFont.createFont("宋体"),
11);
wcf = new WritableCellFormat(align);
wcf.setAlignment(Alignment.RIGHT); // 设置为右对齐
wcf.setVerticalAlignment(VerticalAlignment.CENTRE); // 设置为垂直居中
cell.setCellFormat(wcf);
// 将E3文字改为自动换行
cell = sheet.getWritableCell(4,2);
WritableFont justify = new WritableFont(WritableFont.createFont("宋体"),
11);
wcf = new WritableCellFormat(justify);
wcf.setAlignment(Alignment.JUSTIFY);
cell.setCellFormat(wcf);
// 将B12的数字有效位数从5位改为7位
cell = sheet.getWritableCell(1,11);
NumberFormat sevendps = new NumberFormat("#.0000000");
wcf = new WritableCellFormat(sevendps);
cell.setCellFormat(wcf);
// 将B13改为4位科学计数法表示
cell = sheet.getWritableCell(1,12);
NumberFormat exp4 = new NumberFormat("0.####E0");
wcf = new WritableCellFormat(exp4);
cell.setCellFormat(wcf);
// 将B14改为默认数字表示
cell = sheet.getWritableCell(1,13);
cell.setCellFormat(WritableWorkbook.NORMAL_STYLE);
// 将B15数字类型的值17改为22
cell = sheet.getWritableCell(1,14);
if (cell.getType() == CellType.NUMBER)
{
Number n = (Number) cell;
n.setValue(42);
}
// 将B16的值2.71进行加法运算2.71 + 0.1
cell = sheet.getWritableCell(1,15);
if (cell.getType() == CellType.NUMBER)
{
Number n = (Number) cell;
n.setValue(n.getValue() + 0.1);
}
// 将B19日期格式改为默认
cell = sheet.getWritableCell(1,18);
wcf = new WritableCellFormat(DateFormats.FORMAT9);
cell.setCellFormat(wcf);
// 将B20日期格式改为dd MMM yyyy HH:mm:ss
cell = sheet.getWritableCell(1,19);
DateFormat df = new DateFormat("dd MMM yyyy HH:mm:ss");
wcf = new WritableCellFormat(df);
cell.setCellFormat(wcf);
// 将B21的日期设置为 2011-6-1 11:18:50
cell = sheet.getWritableCell(1,20);
if (cell.getType() == CellType.DATE)
{
DateTime dt = (DateTime) cell;
Calendar cal = Calendar.getInstance();
cal.set(2011, 5, 1, 11, 18, 50);
Date d = cal.getTime();
dt.setDate(d);
}
// 将B24文字添加链接http://www.baidu.com
WritableHyperlink link = new WritableHyperlink(1, 23, new URL("http://www.baidu.com"));
sheet.addHyperlink(link);
// 更改URL链接
WritableHyperlink hyperlinks[] = sheet.getWritableHyperlinks();
for (int i = 0; i < hyperlinks.length; i++) {
WritableHyperlink wh = hyperlinks[i];
if (wh.getColumn() == 1 && wh.getRow() == 24) {
// 将B25文字链接取消
sheet.removeHyperlink(wh,true);//true:保留文字;false:删除文字
}else if(wh.getColumn() == 1 && wh.getRow() == 25){
try {
// 将B26链接更改为http://wuhongyu.javaeye.com
wh.setURL(new URL("http://wuhongyu.javaeye.com"));
} catch (MalformedURLException e) {
e.printStackTrace();
}
}
}
// 利用公式取得B29、B30的值
Formula f1 = new Formula(1, 28, "SUM(C29:D29)");
sheet.addCell(f1);
Formula f2 = new Formula(1, 29, "AVERAGE(C30:G30)");
sheet.addCell(f2);
// 在B32处添加图片,图片大小占10行3列,只支持png格式
File file = new File("D:\\ceshiZJB\\createExcle\\clientServer.png");
WritableImage image = new WritableImage(1, 31, 3, 10, file);
sheet.addImage(image);
// 在A44出添加内容"Added drop down validation",并为其添加注释
label = new Label(0, 43, "Added drop down validation");
wcfeatures = new WritableCellFeatures();
wcfeatures.setComment("右边列是个下拉列表");
label.setCellFeatures(wcfeatures);
sheet.addCell(label);
// 在B44处添加一个下拉列表并添加注释
Blank b = new Blank(1, 43);
wcfeatures = new WritableCellFeatures();
ArrayList al = new ArrayList();
al.add("why");
al.add("landor");
al.add("tjm");
wcfeatures.setDataValidationList(al);
wcfeatures.setComment("这是一个注释");
b.setCellFeatures(wcfeatures);
sheet.addCell(b);
// 为A46添加注释。
// 此处比较麻烦,试了多次发现必须将cell强制类型转换、添加CellFeatures再修改注释才可用,不知有没有更好的办法。
cell = sheet.getWritableCell(0,45);
wcfeatures = new WritableCellFeatures();
wcfeatures.setComment("这个注释不会被显示,删了这行还不行,MD");
cell.setCellFeatures(wcfeatures);
//label = (Label) cell;
// label.setCellFeatures(wcfeatures);// 直接这样写会报一个警告(“注释已存在”),但那个注释仍会被显示。
label.addCellFeatures();
label.getWritableCellFeatures().setComment("终于加上注释了,哈哈哈哈");
workbook.write();
workbook.close();
wb.close();
}
}
View Code
示例4:通过excle分页实现百万数据生成excle
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import makeExcleToZipDep.testbean;
import jxl.read.biff.BiffException;
import jxl.write.WriteException;
/**
*
* @classDesc: 功能描述:
* 1)对list中的数据写入到excle中,每个Sheet页最多5万条数据,每个excle 最多5个Sheet页
* 2)sheet页以:起始行-结束行为命令规范
* @author: zjb
* @createTime: 创建时间:2018-8-3 下午5:31:11
* @version: v1.0
* @copyright:pactera
*/
public class JxlTest4_modifyComplex2 {
/**
* @param args
* @throws IOException
* @throws BiffException
* @throws WriteException
*/
public static void main(String[] args) throws IOException, BiffException, WriteException {
List listInfo=new ArrayList();
List headList=new ArrayList();
headList.add("第一列");
headList.add("第二列");
headList.add("第三列");
headList.add("第四列");
headList.add("第五列");
headList.add("第六列");
headList.add("第七列");
headList.add("第八列");
headList.add("第九列");
headList.add("第十列");
for(int i=0;i<1000100;i++){
listInfo.add(new testbean(i+"就这样", 1, 11D, "开始练习"));
}
ExcleTools tools=new ExcleTools();
//1.会生成excle的数量
Integer excleNum= tools.getMakeExcleNum(listInfo);
//2.将大数据量拆分成多个innerList 放在spileList中
ArrayList spileList = new ArrayList();
int baseRow;//首行号
if (excleNum > 1) {
for (int i = 0; i < excleNum; i++) {
ArrayList innerList = new ArrayList();
baseRow = (i+1)*tools.getXlsSheetNum()* tools.getSheetSize();
//不足下一个xls文件时
for (int j=i*tools.getXlsSheetNum()*tools.getSheetSize(); j< baseRow; j++) {// 每个spileList元素存放多少条记录
if(j<listInfo.size()){
innerList.add(listInfo.get(j));
}
}
spileList.add(innerList);
}
} else {
spileList.add(0, listInfo);
}
//3.生成excle文件
for(int i=0;i<spileList.size();i++){
FileOutputStream fos= new FileOutputStream(new File("D:/ceshiZJB/TEMP/测试"+i+".xls"));
tools.makeExcel(fos,(List)spileList.get(i), headList, "测试+"+i+".xls");// 压缩
fos.close();
}
}
}
import java.io.IOException;
import java.io.OutputStream;
import java.util.List;
import makeExcleToZipDep.Getable;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.NumberFormat;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
/**
*
* @classDesc: 功能描述:生成excle的工具类
* @author: zjb
* @createTime: 创建时间:2018-8-4 上午9:52:46
* @version: v1.0
* @copyright:pactera
*/
public class ExcleTools {
private int sheetSize = 50000;// 每个sheet页多少条数据
private int xlsSheetNum = 5;// 每个excle文件存在多少个sheet页
private static WritableFont wf = new WritableFont(WritableFont.ARIAL, 10,
WritableFont.BOLD, false);
private static WritableCellFormat wcfF = new WritableCellFormat(wf);
// 设置内容字体、字号等
private static WritableFont wft = new WritableFont(WritableFont.ARIAL, 10,
WritableFont.NO_BOLD, false);
private static WritableCellFormat wcfFt = new WritableCellFormat(wft);
// 设置合计字段字体、字号等
private static WritableCellFormat wcfFtotal = new WritableCellFormat(wf);
// 设置保费数值类型
private static NumberFormat nf = new NumberFormat("#,##0.00");
private static WritableCellFormat wcfN = new WritableCellFormat(nf);
// 设置其他费用数值类型
private static NumberFormat nft = new NumberFormat("#,##0");
private static WritableCellFormat wcfNt = new WritableCellFormat(nft);
/**
*
* @Title: getMakeExcleNum
* @Description: 数据量够生成多少个excle文件
* @param @param datas
* @param @return
* @return int
* @throws
*/
public int getMakeExcleNum(List datas) {
int cycle = datas.size() % (sheetSize * xlsSheetNum) >= 1 ? (datas
.size() / (sheetSize * xlsSheetNum) + 1) : datas.size()
/ (sheetSize * xlsSheetNum);
return cycle;
}
/**
* @Title: simpleMakeExcel
* @Description:
* @param @return
* @return int
* @throws
*/
@SuppressWarnings("unchecked")
public void makeExcel(OutputStream out, List xlsDatas, List headInfo,
String xlsName) throws IOException, WriteException {
WritableWorkbook wwb = Workbook.createWorkbook(out);
// 一个excle生成多少个sheet页
int cycle = (xlsDatas.size() + (sheetSize - 1)) / sheetSize;
for (int i = 0; i < cycle; i++) {
makeSheet(wwb, xlsDatas, headInfo, i);
}
wwb.write();
wwb.close();
}
/**
* @Title: makeSheet
* @Description:
* @param @param wwb
* @param @param datas
* @param @param head
* @param @param index
* @param @throws WriteException
* @return void
* @throws
*/
@SuppressWarnings("unchecked")
public void makeSheet(WritableWorkbook wwb, List datas, List head, int index)
throws WriteException {
int baseRow = index * sheetSize;
int endRow = (baseRow + sheetSize > datas.size()) ? datas.size()
: (baseRow + sheetSize);
// excle的Sheet页的命名:起始行-结束行
WritableSheet ws = wwb.createSheet((baseRow + 1) + "-" + endRow, index);
// 写入每个Sheet页的第一行单元格内容
for (int i = 0; i < head.size(); i++) {
ws.addCell(new Label(i, 0, head.get(i).toString(), wcfF));// (列,行,数据,单元格格式)
}
// 写入每个Sheet页第二行及之后的内容
for (int i = 0; i + baseRow < datas.size() && i < sheetSize; i++) {
Getable obj = (Getable) datas.get(i + baseRow);
for (int j = 0; j < head.size(); j++) {
Object o = obj.get(j);
if (o != null) {
if (o instanceof String) {
ws.addCell(new Label(j, i + 1, o.toString(), wcfFt));
} else if (o instanceof Double) {
ws.addCell(new Number(j, i + 1, ((Double) o)
.doubleValue(), wcfN));
} else if (o instanceof Integer) {
ws.addCell(new Number(j, i + 1, ((Integer) o)
.doubleValue(), wcfFt));
}
} else {
ws.addCell(new Label(j, i + 1, "", wcfFt));
}
}
}
}
public int getSheetSize() {
return sheetSize;
}
public void setSheetSize(int sheetSize) {
this.sheetSize = sheetSize;
}
public int getXlsSheetNum() {
return xlsSheetNum;
}
public void setXlsSheetNum(int xlsSheetNum) {
this.xlsSheetNum = xlsSheetNum;
}
}
View Code
细水长流,打磨濡染,渐趋极致,才是一个人最好的状态。