三、jxl.jar生成excle
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 { } } }
简单示例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 { } } }
示例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(); } }
示例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; } }
细水长流，打磨濡染，渐趋极致，才是一个人最好的状态。
浙公网安备 33010602011771号