Jexcel实现按一定规则分割excel文件的方法
程序员文章站
2024-03-04 18:03:17
本文实例讲述了jexcel实现按一定规则分割excel文件的方法。分享给大家供大家参考。具体如下:
现有一个excel文档,需要读取它并按照一定的规则,分割之,分割出来的...
本文实例讲述了jexcel实现按一定规则分割excel文件的方法。分享给大家供大家参考。具体如下:
现有一个excel文档,需要读取它并按照一定的规则,分割之,分割出来的每一段记录需要单独创建一个excel文档并写入其中,一定要保证单元格格式的一致性。
package edu.bjut.zhutong.excelparser; import java.io.file; import java.io.fileinputstream; import java.io.filenotfoundexception; import java.io.ioexception; import java.io.inputstream; import jxl.cell; import jxl.celltype; 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.verticalalignment; import jxl.read.biff.biffexception; import jxl.write.label; import jxl.write.writablecellformat; import jxl.write.writablefont; import jxl.write.writablesheet; import jxl.write.writableworkbook; import jxl.write.writeexception; import jxl.write.biff.rowsexceededexception; public class excelparser { public static void main(string[] args) { workbook wb = null; try { //获得了workbook对象之后,就可以通过它得到sheet(工作表)对象了 inputstream is = new fileinputstream("c:/excel/excel.xls"); wb = workbook.getworkbook(is); // 获得第一个工作表对象 sheet sheet = wb.getsheet(0); //获得工作表的行数和列数 int rows = sheet.getrows(); int cols = sheet.getcolumns(); system.out.println("一共 " + rows + " 行"); system.out.println("一共 " + cols + " 列"); int counter = 0; //工作表行游标 int filecounts = 1; //用来标识创建的excel文档数目 while(counter<rows-1) { //得到counter行的所有单元格 cell[] rowcells = sheet.getrow(counter); cell cell0 = rowcells[0]; //判断单元格内容的类型 if(cell0.gettype() == celltype.label) { system.out.println("正在解析第 " + filecounts + " 个文件...."); //新建一个excel文档 file file = new file("c:/excel/excel" + filecounts + ".xls"); writableworkbook wwb = workbook.createworkbook(file); //设置excel文档的工作表 writablesheet ws = wwb.createsheet("sheet1", 0); //第一行合并第0到第8列 ws.mergecells(0, 0, 8, 0); //设置第7,8,9列的列宽 ws.setcolumnview(6, 10); ws.setcolumnview(7, 45); ws.setcolumnview(8, 27); //向新建的表中写入数据,首先第一行先写入标题 for(int k=0; k<rowcells.length; k++) { //创建writablefont对象用来格式化字体,这里是20号宋体,加粗 writablefont wf = new writablefont(writablefont.createfont("宋体"), 20, writablefont.bold, false); //使用writablefont创建单元格格式化对象 writablecellformat wcf = new writablecellformat(wf); //设置水平对齐方式 wcf.setalignment(alignment.centre); //设置垂直对齐方式 wcf.setverticalalignment(verticalalignment.centre); //设置边框和颜色 wcf.setborder(border.all, borderlinestyle.thin, colour.black); cell cell = rowcells[k]; label label = new label(k,0,cell.getcontents(),wcf); //添加单元格到表中 ws.addcell(label); //设置第一行的行高 ws.setrowview(0, 30*20, false); } //向新建的表中写入数据,第二行写入表头 for(int c=0; c<cols; c++) { string colcon = sheet.getcell(c, 1).getcontents(); writablefont wf = new writablefont(writablefont.createfont("宋体"), 12, writablefont.bold, false); writablecellformat wcf = new writablecellformat(wf); wcf.setalignment(alignment.centre); wcf.setverticalalignment(verticalalignment.centre); wcf.setborder(border.all, borderlinestyle.thin, colour.black); ws.addcell(new label(c,1,colcon,wcf)); ws.setrowview(1, 18*20, false); } int rowcounts = 1; //用来遍历50 counter++; //将游标移动到下一行 if(counter == 1) //如果游标到了第二行 ,就自动把游标移动到第三行,第二行不需要处理 counter = 2; int rowindex = 2; //每篇excel文档的游标 rowcells = sheet.getrow(counter); cell0 = rowcells[0]; while(cell0.gettype() == celltype.number && counter<rows-1) { rowcells = sheet.getrow(counter); for(int k=0; k<rowcells.length; k++) { writablefont wf = new writablefont(writablefont.createfont("宋体"), 12, writablefont.no_bold, false); writablecellformat wcf = new writablecellformat(wf); wcf.setalignment(alignment.centre); wcf.setverticalalignment(verticalalignment.centre); wcf.setborder(border.all, borderlinestyle.thin, colour.black); label label = new label(k,rowindex,rowcells[k].getcontents(),wcf); ws.addcell(label); } //用来处理备注列的边框 { writablefont wf = new writablefont(writablefont.createfont("宋体"), 12, writablefont.no_bold, false); writablecellformat wcf = new writablecellformat(wf); wcf.setalignment(alignment.centre); wcf.setverticalalignment(verticalalignment.centre); wcf.setborder(border.all, borderlinestyle.thin, colour.black); label label = new label(8,rowindex,"",wcf); ws.addcell(label); } ws.setrowview(rowindex, 18*20, false); rowindex++; counter++; cell0 = sheet.getrow(counter)[0]; } wwb.write(); wwb.close(); filecounts++; } } system.out.println("程序执行结束...."); } catch (filenotfoundexception e) { e.printstacktrace(); } catch (biffexception e) { e.printstacktrace(); } catch (ioexception e) { e.printstacktrace(); } catch (rowsexceededexception e) { e.printstacktrace(); } catch (writeexception e) { e.printstacktrace(); } finally { wb.close(); //关闭workbook对象 } } }
希望本文所述对大家的java程序设计有所帮助。